2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
|
(db:general-call dbstruct run-id 'delete-test-data-records (list test-id))
(db:with-db
dbstruct run-id #t
(lambda (dbdat db)
(sqlite3:execute db "UPDATE tests SET state='DELETED',status='n/a',comment='' WHERE id=?;" test-id))))
;;
(define (db:delete-old-deleted-test-records dbstruct)
(let ((targtime (- (current-seconds)
(or (configf:lookup-number *configdat* "setup" "keep-deleted-records")
(* 30 24 60 60))))) ;; one month in the past
(db:with-db
dbstruct
0
#t
(lambda (dbdat db)
(sqlite3:with-transaction
db
(lambda ()
(sqlite3:execute db "DELETE FROM test_steps WHERE test_id IN (SELECT id FROM tests WHERE state='DELETED' AND event_time<?);" targtime)
(sqlite3:execute db "DELETE FROM test_data WHERE test_id IN (SELECT id FROM tests WHERE state='DELETED' AND event_time<?);" targtime)
(sqlite3:execute db "DELETE FROM tests WHERE state='DELETED' AND event_time<?;" targtime)))))))
;; set tests with state currstate and status currstatus to newstate and newstatus
;; use currstate = #f and or currstatus = #f to apply to any state or status respectively
;; WARNING: SQL injection risk. NB// See new but not yet used "faster" version below
;;
;; AND NOT (item_path='' AND testname in (SELECT DISTINCT testname FROM tests WHERE testname=? AND item_path != ''));")))
;; (debug:print 0 *default-log-port* "QRY: " qry)
|
|
|
|
|
<
|
<
>
>
|
|
|
|
|
|
|
|
>
>
>
>
>
>
>
>
>
>
>
>
|
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
|
(db:general-call dbstruct run-id 'delete-test-data-records (list test-id))
(db:with-db
dbstruct run-id #t
(lambda (dbdat db)
(sqlite3:execute db "UPDATE tests SET state='DELETED',status='n/a',comment='' WHERE id=?;" test-id))))
;;
(define (db:delete-old-deleted-test-records dbstruct run-id)
(let* ((targtime (- (current-seconds)
(or (configf:lookup-number *configdat* "setup" "keep-deleted-records")
(* 7 24 60 60)))) ;; cleanup if over one week old
(mtdbfile (dbmod:run-id->full-dbfname dbstruct run-id))
(qry1 "DELETE FROM test_steps WHERE test_id IN (SELECT id FROM tests WHERE state='DELETED' AND event_time<?);")
(qry2 "DELETE FROM test_data WHERE test_id IN (SELECT id FROM tests WHERE state='DELETED' AND event_time<?);")
(qry3 "DELETE FROM tests WHERE state='DELETED' AND event_time<?;")
(delproc (lambda (db)
(sqlite3:with-transaction
db
(lambda ()
(sqlite3:execute db qry1 targtime)
(sqlite3:execute db qry2 targtime)
(sqlite3:execute db qry3 targtime))))))
;; first the /tmp db
(db:with-db
dbstruct
run-id
#t
(lambda (dbdat db)
(delproc db)))
(if (and (file-exists? mtdbfile)
(file-write-access? mtdbfile))
(let* ((db (sqlite3:open-database mtdbfile)))
(delproc db)
(sqlite3:finalize! db)))))
;; set tests with state currstate and status currstatus to newstate and newstatus
;; use currstate = #f and or currstatus = #f to apply to any state or status respectively
;; WARNING: SQL injection risk. NB// See new but not yet used "faster" version below
;;
;; AND NOT (item_path='' AND testname in (SELECT DISTINCT testname FROM tests WHERE testname=? AND item_path != ''));")))
;; (debug:print 0 *default-log-port* "QRY: " qry)
|