135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
|
;; given a target spec id, target and run-name return the run-id
;; if no run found return #f
;;
(define (pgdb:get-run-id dbh spec-id target run-name area-id)
(dbi:get-one dbh "SELECT id FROM runs WHERE ttype_id=? AND target=? AND run_name=? and area_id=?;"
spec-id target run-name area-id))
;; given a run-id return all the run info
;;
(define (pgdb:get-run-info dbh run-id ) ;; to join ttype or not?
(dbi:get-one-row
dbh ;; 0 1 2 3 4 5 6 7 8 9 10 11 12
"SELECT id,target,ttype_id,run_name,state,status,owner,event_time,comment,fail_count,pass_count,last_update,area_id
FROM runs WHERE id=? ;" run-id ))
;; refresh the data in a run record
;;
(define (pgdb:refresh-run-info dbh run-id state status owner event-time comment fail-count pass-count area-id) ;; area-id)
(dbi:exec
dbh
"UPDATE runs SET
state=?,status=?,owner=?,event_time=?,comment=?,fail_count=?,pass_count=?
WHERE id=? and area_id=?;"
state status owner event-time comment fail-count pass-count run-id area-id))
;; given all needed info create run record
;;
(define (pgdb:insert-run dbh ttype-id target run-name state status owner event-time comment fail-count pass-count area-id)
(dbi:exec
dbh
"INSERT INTO runs (ttype_id,target,run_name,state,status,owner,event_time,comment,fail_count,pass_count,area_id )
VALUES (?,?,?,?,?,?,?,?,?,?,?);"
ttype-id target run-name state status owner event-time comment fail-count pass-count area-id))
;;======================================================================
;; T E S T - S T E P S
;;======================================================================
(define (pgdb:get-test-step-id dbh test-id stepname state)
(dbi:get-one
dbh
"SELECT id FROM test_steps WHERE test_id=? AND stepname=? and state = ? ;"
test-id stepname state))
(define (pgdb:insert-test-step dbh test-id stepname state status event_time comment logfile)
(dbi:exec
dbh
"INSERT INTO test_steps (test_id,stepname,state,status,event_time,logfile,comment)
VALUES (?,?,?,?,?,?,?);"
test-id stepname state status event_time logfile comment))
(define (pgdb:update-test-step dbh step-id test-id stepname state status event_time comment logfile)
(dbi:exec
dbh
"UPDATE test_steps SET
test_id=?,stepname=?,state=?,status=?,event_time=?,logfile=?,comment=?
WHERE id=?;"
test-id stepname state status event_time logfile comment step-id))
;;======================================================================
;; T E S T - D A T A
;;======================================================================
(define (pgdb:get-test-data-id dbh test-id category variable)
(dbi:get-one
dbh
"SELECT id FROM test_data WHERE test_id=? AND category=? and variable = ? ;"
test-id category variable))
(define (pgdb:insert-test-data dbh test-id category variable value expected tol units comment status type)
; (print "INSERT INTO test_data (test_id, category, variable, value, expected, tol, units, comment, status, type)
; VALUES (?,?,?,?,?,?,?,?,?,?) " test-id " " category " " variable " " value " " expected " " tol " " units " " comment " " status " " type)
(if (not (string? units))
(set! units "" ))
(if (not (string? variable))
(set! variable "" ))
(if (not (real? value))
(set! value 0 ))
(if (not (real? expected))
(set! expected 0 ))
(if (not (real? tol))
(set! tol 0 ))
(dbi:exec
dbh
"INSERT INTO test_data (test_id, category, variable, value, expected, tol, units, comment, status, type)
VALUES (?,?,?,?,?,?,?,?,?,?);"
test-id category variable value expected tol units comment status type))
(define (pgdb:update-test-data dbh data-id test-id category variable value expected tol units comment status type)
(dbi:exec
dbh
"UPDATE test_data SET
test_id=?, category=?, variable=?, value=?, expected=?, tol=?, units=?, comment=?, status=?, type=?
WHERE id=?;"
test-id category variable value expected tol units comment status type data-id ))
;;======================================================================
;; T E S T S
;;======================================================================
;; given run-id, test_name and item_path return test-id
;;
(define (pgdb:get-test-id dbh run-id test-name item-path)
(dbi:get-one
dbh
"SELECT id FROM tests WHERE run_id=? AND test_name=? AND item_path=?;"
run-id test-name item-path))
;; create new test record
;;
(define (pgdb:insert-test dbh run-id test-name item-path state status host cpuload diskfree uname run-dir log-file run-duration comment event-time archived)
(dbi:exec
dbh
"INSERT INTO tests (run_id,test_name,item_path,state,status,host,cpuload,diskfree,uname,rundir,final_logf,run_duration,comment,event_time,archived)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
run-id test-name item-path state status host cpuload diskfree uname
run-dir log-file run-duration comment event-time archived))
;; update existing test record
;;
(define (pgdb:update-test dbh test-id run-id test-name item-path state status host cpuload diskfree uname run-dir log-file run-duration comment event-time archived)
(dbi:exec
dbh
"UPDATE tests SET
run_id=?,test_name=?,item_path=?,state=?,status=?,host=?,cpuload=?,diskfree=?,uname=?,rundir=?,final_logf=?,run_duration=?,comment=?,event_time=?,archived=?
WHERE id=?;"
run-id test-name item-path state status host cpuload diskfree uname
run-dir log-file run-duration comment event-time archived test-id))
(define (pgdb:get-tests dbh target-patt)
(dbi:get-rows
dbh
"SELECT t.id,t.run_id,t.test_name,t.item_path,t.state,t.status,t.host,t.cpuload,t.diskfree,t.uname,t.rundir,t.final_logf,t.run_duration,t.comment,t.event_time,t.archived,
r.id,r.target,r.ttype_id,r.run_name,r.state,r.status,r.owner,r.event_time,r.comment
FROM tests AS t INNER JOIN runs AS r ON t.run_id=r.id
|
>
>
>
>
>
>
>
|
|
|
|
|
|
|
>
>
>
>
>
>
|
|
|
|
|
|
|
>
>
>
>
>
>
|
|
|
|
|
|
|
>
>
>
>
>
>
>
|
|
|
|
|
|
|
|
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
|
;; given a target spec id, target and run-name return the run-id
;; if no run found return #f
;;
(define (pgdb:get-run-id dbh spec-id target run-name area-id)
(dbi:get-one dbh "SELECT id FROM runs WHERE ttype_id=? AND target=? AND run_name=? and area_id=?;"
spec-id target run-name area-id))
;; given a target spec id, target and run-name return the run-id
;; if no run found return #f
;;
(define (pgdb:get-run-last-update dbh id )
(dbi:get-one dbh "SELECT last_update FROM runs WHERE id=?;"
id))
;; given a run-id return all the run info
;;
(define (pgdb:get-run-info dbh run-id ) ;; to join ttype or not?
(dbi:get-one-row
dbh ;; 0 1 2 3 4 5 6 7 8 9 10 11 12
"SELECT id,target,ttype_id,run_name,state,status,owner,event_time,comment,fail_count,pass_count,last_update,area_id
FROM runs WHERE id=? ;" run-id ))
;; refresh the data in a run record
;;
(define (pgdb:refresh-run-info dbh run-id state status owner event-time comment fail-count pass-count area-id last_update) ;; area-id)
(dbi:exec
dbh
"UPDATE runs SET
state=?,status=?,owner=?,event_time=?,comment=?,fail_count=?,pass_count=?,last_update=?
WHERE id=? and area_id=?;"
state status owner event-time comment fail-count pass-count last_update run-id area-id ))
;; given all needed info create run record
;;
(define (pgdb:insert-run dbh ttype-id target run-name state status owner event-time comment fail-count pass-count area-id last-update)
(dbi:exec
dbh
"INSERT INTO runs (ttype_id,target,run_name,state,status,owner,event_time,comment,fail_count,pass_count,area_id,last_update)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?);"
ttype-id target run-name state status owner event-time comment fail-count pass-count area-id last-update))
;;======================================================================
;; T E S T - S T E P S
;;======================================================================
(define (pgdb:get-test-step-id dbh test-id stepname state)
(dbi:get-one
dbh
"SELECT id FROM test_steps WHERE test_id=? AND stepname=? and state = ? ;"
test-id stepname state))
(define (pgdb:get-test-step-last-update dbh id )
(dbi:get-one
dbh
"SELECT last_update FROM test_steps WHERE id=? ;"
id))
(define (pgdb:insert-test-step dbh test-id stepname state status event_time comment logfile last-update)
(dbi:exec
dbh
"INSERT INTO test_steps (test_id,stepname,state,status,event_time,logfile,comment,last_update)
VALUES (?,?,?,?,?,?,?, ? );"
test-id stepname state status event_time logfile comment last-update))
(define (pgdb:update-test-step dbh step-id test-id stepname state status event_time comment logfile last-update)
(dbi:exec
dbh
"UPDATE test_steps SET
test_id=?,stepname=?,state=?,status=?,event_time=?,logfile=?,comment=?,last_update=?
WHERE id=?;"
test-id stepname state status event_time logfile comment last-update step-id))
;;======================================================================
;; T E S T - D A T A
;;======================================================================
(define (pgdb:get-test-data-id dbh test-id category variable)
(dbi:get-one
dbh
"SELECT id FROM test_data WHERE test_id=? AND category=? and variable = ? ;"
test-id category variable))
(define (pgdb:get-test-data-last-update dbh test-data-id )
(dbi:get-one
dbh
"SELECT last_update FROM test_data WHERE id=? ;"
test-data-id))
(define (pgdb:insert-test-data dbh test-id category variable value expected tol units comment status type last-update)
; (print "INSERT INTO test_data (test_id, category, variable, value, expected, tol, units, comment, status, type)
; VALUES (?,?,?,?,?,?,?,?,?,?) " test-id " " category " " variable " " value " " expected " " tol " " units " " comment " " status " " type)
(if (not (string? units))
(set! units "" ))
(if (not (string? variable))
(set! variable "" ))
(if (not (real? value))
(set! value 0 ))
(if (not (real? expected))
(set! expected 0 ))
(if (not (real? tol))
(set! tol 0 ))
(dbi:exec
dbh
"INSERT INTO test_data (test_id, category, variable, value, expected, tol, units, comment, status, type, last_update)
VALUES (?,?,?,?,?,?,?,?,?,?, ?);"
test-id category variable value expected tol units comment status type last-update))
(define (pgdb:update-test-data dbh data-id test-id category variable value expected tol units comment status type last-update)
(dbi:exec
dbh
"UPDATE test_data SET
test_id=?, category=?, variable=?, value=?, expected=?, tol=?, units=?, comment=?, status=?, type=?, last_update=?
WHERE id=?;"
test-id category variable value expected tol units comment status type last-update data-id ))
;;======================================================================
;; T E S T S
;;======================================================================
;; given run-id, test_name and item_path return test-id
;;
(define (pgdb:get-test-id dbh run-id test-name item-path)
(dbi:get-one
dbh
"SELECT id FROM tests WHERE run_id=? AND test_name=? AND item_path=?;"
run-id test-name item-path))
(define (pgdb:get-test-last-update dbh id)
(dbi:get-one
dbh
"SELECT last_update FROM tests WHERE id=? ;"
id ))
;; create new test record
;;
(define (pgdb:insert-test dbh run-id test-name item-path state status host cpuload diskfree uname run-dir log-file run-duration comment event-time archived last-update)
(dbi:exec
dbh
"INSERT INTO tests (run_id,test_name,item_path,state,status,host,cpuload,diskfree,uname,rundir,final_logf,run_duration,comment,event_time,archived,last_update)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
run-id test-name item-path state status host cpuload diskfree uname
run-dir log-file run-duration comment event-time archived last-update))
;; update existing test record
;;
(define (pgdb:update-test dbh test-id run-id test-name item-path state status host cpuload diskfree uname run-dir log-file run-duration comment event-time archived last-update)
(dbi:exec
dbh
"UPDATE tests SET
run_id=?,test_name=?,item_path=?,state=?,status=?,host=?,cpuload=?,diskfree=?,uname=?,rundir=?,final_logf=?,run_duration=?,comment=?,event_time=?,archived=?,last_update=?
WHERE id=?;"
run-id test-name item-path state status host cpuload diskfree uname
run-dir log-file run-duration comment event-time archived last-update test-id))
(define (pgdb:get-tests dbh target-patt)
(dbi:get-rows
dbh
"SELECT t.id,t.run_id,t.test_name,t.item_path,t.state,t.status,t.host,t.cpuload,t.diskfree,t.uname,t.rundir,t.final_logf,t.run_duration,t.comment,t.event_time,t.archived,
r.id,r.target,r.ttype_id,r.run_name,r.state,r.status,r.owner,r.event_time,r.comment
FROM tests AS t INNER JOIN runs AS r ON t.run_id=r.id
|