856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
|
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
|
-
+
+
+
+
+
+
+
+
+
|
count INTEGER,
last_update INTEGER DEFAULT (strftime('%s','now')))")
(sqlite3:execute maindb "CREATE TRIGGER IF NOT EXISTS update_run_stats_trigger AFTER UPDATE ON run_stats
FOR EACH ROW
BEGIN
UPDATE run_stats SET last_update=(strftime('%s','now'))
WHERE id=old.id;
END;"))
END;")
(sqlite3:execute maindb "CREATE TABLE IF NOT EXISTS test_rundat (
id INTEGER PRIMARY KEY,
test_id INTEGER,
update_time TIMESTAMP,
cpuload INTEGER DEFAULT -1,
diskfree INTEGER DEFAULT -1,
diskusage INTGER DEFAULT -1,
run_duration INTEGER DEFAULT 0);"))
(define (db:adj-target db)
(let ((fields (configf:get-section *configdat* "fields"))
(field-num 0))
;; because we will be refreshing the keys table it is best to clear it here
(sqlite3:execute db "DELETE FROM keys;")
(for-each
|
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
|
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
|
+
+
+
+
|
"DELETE FROM tests WHERE state='DELETED';"
;; delete all tests that have no run
"DELETE FROM tests WHERE run_id NOT IN (SELECT DISTINCT id FROM runs);"
;; delete all runs that are state='deleted'
"DELETE FROM runs WHERE state='deleted';"
;; delete empty runs
"DELETE FROM runs WHERE id NOT IN (SELECT DISTINCT r.id FROM runs AS r INNER JOIN tests AS t ON t.run_id=r.id);"
;; remove orphaned test_rundat entries
"DELETE FROM test_rundat where test_id NOT IN (SELECT id FROM tests);"
;;
"DELETE FROM test_steps WHERE test_id NOT IN (SELECT id FROM tests);"
))))
;; (db:delay-if-busy dbdat)
(sqlite3:with-transaction
db
(lambda ()
(sqlite3:for-each-row (lambda (tot)
(debug:print-info 0 *default-log-port* "Records count before clean: " tot))
|