Megatest

Check-in [bcf8145611]
Login
Overview
Comment:Most sqlite3: calls now prepared and cached.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | v1.80-refactor
Files: files | file ages | folders
SHA1: bcf814561173f2685b27970b7841541c26069efe
User & Date: matt on 2023-01-16 15:53:43
Other Links: branch diff | manifest | tags
Context
2023-01-16
21:42
v1.80-notbetter Leaf check-in: 5287e50105 user: matt tags: v1.80-refactor
15:53
Most sqlite3: calls now prepared and cached. check-in: bcf8145611 user: matt tags: v1.80-refactor
14:09
Cleaned up prepared db statements handling. check-in: 2639de81a8 user: matt tags: v1.80-refactor
Changes

Modified db.scm from [6457b2d810] to [5a3c35a5ba].

90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
90
91
92
93
94
95
96













97
98
99
100
101
102
103







-
-
-
-
-
-
-
-
-
-
-
-
-







	  (db:hoh-set! dat key1 key2 val)))))

(define (db:hoh-get dat key1 key2)
  (let* ((subhash (hash-table-ref/default dat key1 #f)))
    (and subhash
	 (hash-table-ref/default subhash key2 #f))))

;; the stmt-cache is associated with a single db handle
;; so there is no need for the hoh stuff. 
(define (db:get-cache-stmth dbdat run-id db stmt)
  (let* ((stmt-cache  (dbr:dbdat-stmt-cache dbdat))
	 ;; (stmth       (db:hoh-get stmt-cache db stmt))
	 (stmth       (hash-table-ref/default stmt-cache stmt #f))
	 )
    (or stmth
	(let* ((newstmth (sqlite3:prepare db stmt)))
	  ;; (db:hoh-set! stmt-cache db stmt newstmth)
	  (hash-table-set! stmt-cache stmt newstmth)
	  newstmth))))

;;======================================================================
;; SQLITE3 HELPERS
;;======================================================================

(define (db:general-sqlite-error-dump exn stmt . params)
  (let ((err-status ((condition-property-accessor 'sqlite3 'status #f) exn))) ;; RADT ... how does this work?
    ;; check for (exn sqlite3) ((condition-property-accessor 'exn 'message) exn)
1049
1050
1051
1052
1053
1054
1055
1056
1057


1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071


1072
1073
1074
1075
1076
1077
1078
1036
1037
1038
1039
1040
1041
1042


1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056


1057
1058
1059
1060
1061
1062
1063
1064
1065







-
-
+
+












-
-
+
+







          (if (and (equal? uname "n/a")
                   (equal? item-path "")) ;; this is a toplevel test
              ;; what to do with toplevel? call rollup?
              (begin
                (set! toplevels   (cons (list test-id run-dir uname testname item-path run-id) toplevels))
                (debug:print-info 0 *default-log-port* "Found old toplevel test in RUNNING state, test-id=" test-id))
              (set! incompleted (cons (list test-id run-dir uname testname item-path run-id) incompleted))))
        db
        "SELECT id,rundir,uname,testname,item_path FROM tests WHERE run_id=? AND (strftime('%s','now') - event_time) > (run_duration + ?) AND state IN ('RUNNING','REMOTEHOSTSTART');"
        (db:get-cache-stmth dbdat db
        "SELECT id,rundir,uname,testname,item_path FROM tests WHERE run_id=? AND (strftime('%s','now') - event_time) > (run_duration + ?) AND state IN ('RUNNING','REMOTEHOSTSTART');")
        run-id deadtime)

       ;; in LAUNCHED for more than one day. Could be long due to job queues TODO/BUG: Need override for this in config
       ;;
       ;; (db:delay-if-busy dbdat)
       (sqlite3:for-each-row
        (lambda (test-id run-dir uname testname item-path)
          (if (and (equal? uname "n/a")
                   (equal? item-path "")) ;; this is a toplevel test
              ;; what to do with toplevel? call rollup?
              (set! toplevels   (cons (list test-id run-dir uname testname item-path run-id) toplevels))
              (set! oldlaunched (cons (list test-id run-dir uname testname item-path run-id) oldlaunched))))
        db
        "SELECT id,rundir,uname,testname,item_path FROM tests WHERE run_id=? AND (strftime('%s','now') - event_time) > 86400 AND state IN ('LAUNCHED');"
        (db:get-cache-stmth dbdat db
        "SELECT id,rundir,uname,testname,item_path FROM tests WHERE run_id=? AND (strftime('%s','now') - event_time) > 86400 AND state IN ('LAUNCHED');")
        run-id)
       
       (debug:print-info 18 *default-log-port* "Found " (length oldlaunched) " old LAUNCHED items, " (length toplevels) " old LAUNCHED toplevel tests and " (length incompleted) " tests marked RUNNING but apparently dead.")
       (if (and (null? incompleted)
                (null? oldlaunched)
                (null? toplevels))
           #f
1115
1116
1117
1118
1119
1120
1121
1122

1123
1124
1125
1126
1127

1128
1129
1130
1131
1132

1133
1134
1135
1136
1137
1138
1139
1102
1103
1104
1105
1106
1107
1108

1109
1110
1111
1112
1113

1114
1115
1116
1117
1118

1119
1120
1121
1122
1123
1124
1125
1126







-
+




-
+




-
+







    (debug:print-info 4  *default-log-port* "running-deadtime = " running-deadtime)
    (debug:print-info 4  *default-log-port* "deadtime-trim = " deadtime-trim)

    (db:with-db 
     dbstruct run-id #f
     (lambda (dbdat db)
       (let* ((stmth1 (db:get-cache-stmth
		       dbdat run-id db
		       dbdat db
		       "SELECT id,rundir,uname,testname,item_path,event_time,run_duration FROM tests 
                           WHERE run_id=? AND (strftime('%s','now') - event_time) > (run_duration + ?)
                                          AND state IN ('RUNNING');"))
	      (stmth2 (db:get-cache-stmth
		       dbdat run-id db
		       dbdat db
		       "SELECT id,rundir,uname,testname,item_path,event_time,run_duration FROM tests 
                           WHERE run_id=? AND (strftime('%s','now') - event_time) > (run_duration + ?)
                                          AND state IN ('REMOTEHOSTSTART');"))
	      (stmth3 (db:get-cache-stmth
		       dbdat run-id db
		       dbdat db
		       "SELECT id,rundir,uname,testname,item_path FROM tests
                           WHERE run_id=? AND (strftime('%s','now') - event_time) > 86400
                                          AND state IN ('LAUNCHED');")))
	 ;; in RUNNING or REMOTEHOSTSTART for more than 10 minutes
	 ;;
	 ;; HOWEVER: this code in run:test seems to work fine
	 ;;              (> (- (current-seconds)(+ (db:test-get-event_time testdat)
1361
1362
1363
1364
1365
1366
1367
1368
1369


1370
1371
1372
1373
1374
1375
1376
1377
1378
1379

1380
1381
1382
1383
1384

1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401

1402
1403
1404
1405
1406

1407
1408
1409
1410
1411

1412
1413
1414
1415
1416
1417
1418
1348
1349
1350
1351
1352
1353
1354


1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365

1366
1367
1368
1369
1370

1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387

1388
1389
1390
1391
1392

1393
1394
1395
1396
1397

1398
1399
1400
1401
1402
1403
1404
1405







-
-
+
+









-
+




-
+
















-
+




-
+




-
+







  (let* ((res      #f))
    (db:with-db
     dbstruct #f #f  ;; for the moment vars are only stored in main.db
     (lambda (dbdat db)
       (sqlite3:for-each-row
        (lambda (val)
          (set! res val))
        db
        "SELECT val FROM metadat WHERE var=?;" var)
        (db:get-cache-stmth dbdat db
        "SELECT val FROM metadat WHERE var=?;") var)
       ;; convert to number if can
       (if (string? res)
           (let ((valnum (string->number res)))
             (if valnum (set! res valnum))))
       res))))

(define (db:inc-var dbstruct var)
  (db:with-db dbstruct #f #t 
	      (lambda (dbdat db)
		(sqlite3:execute db "UPDATE metadat SET val=val+1 WHERE var=?;" var))))
		(sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE metadat SET val=val+1 WHERE var=?;") var))))

(define (db:dec-var dbstruct var)
  (db:with-db dbstruct #f #t 
	      (lambda (dbdat db)
		(sqlite3:execute db "UPDATE metadat SET val=val-1 WHERE var=?;" var))))
		(sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE metadat SET val=val-1 WHERE var=?;") var))))

;; This was part of db:get-var. It was used to estimate the load on
;; the database files.
;;
;; scale by 10, average with current value.
;;     (set! *global-delta* (/ (+ *global-delta* (* (- (current-milliseconds) start-ms)
;; 						 (if throttle throttle 0.01)))
;; 			    2))
;;     (if (> (abs (- *last-global-delta-printed* *global-delta*)) 0.08) ;; don't print all the time, only if it changes a bit
;; 	(begin
;; 	  (debug:print-info 4 *default-log-port* "launch throttle factor=" *global-delta*)
;; 	  (set! *last-global-delta-printed* *global-delta*)))

(define (db:set-var dbstruct var val)
  (db:with-db dbstruct #f #t 
	      (lambda (dbdat db)
		(sqlite3:execute db "INSERT OR REPLACE INTO metadat (var,val) VALUES (?,?);" var val))))
		(sqlite3:execute (db:get-cache-stmth dbdat db "INSERT OR REPLACE INTO metadat (var,val) VALUES (?,?);") var val))))

(define (db:add-var dbstruct var val)
  (db:with-db dbstruct #f #t 
	      (lambda (dbdat db)
		(sqlite3:execute db "UPDATE metadat SET val=val+? WHERE var=?;" val var))))
		(sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE metadat SET val=val+? WHERE var=?;") val var))))

(define (db:del-var dbstruct var)
  (db:with-db dbstruct #f #t 
	      (lambda (dbdat db)
		(sqlite3:execute db "DELETE FROM metadat WHERE var=?;" var))))
		(sqlite3:execute (db:get-cache-stmth dbdat db "DELETE FROM metadat WHERE var=?;") var))))

;;======================================================================
;; no-sync.db - small bits of data to be shared between servers
;;======================================================================

(define (db:no-sync-db db-in)
  (if db-in
1521
1522
1523
1524
1525
1526
1527
1528
1529


1530
1531
1532
1533
1534
1535
1536
1508
1509
1510
1511
1512
1513
1514


1515
1516
1517
1518
1519
1520
1521
1522
1523







-
-
+
+







   #f ;; this is for the main runs db
   #f ;; does not modify db
   (lambda (dbdat db)
     (let ((res #f))
       (sqlite3:for-each-row
	(lambda (runname)
	  (set! res runname))
	db
	"SELECT runname FROM runs WHERE id=?;"
	(db:get-cache-stmth dbdat db
	"SELECT runname FROM runs WHERE id=?;")
	run-id)
       res))))

(define (db:get-run-key-val dbstruct run-id key)
  (db:with-db
   dbstruct
   #f
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593




1594
1595
1596
1597
1598
1599
1600
1571
1572
1573
1574
1575
1576
1577



1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588







-
-
-
+
+
+
+







    (debug:print 2 *default-log-port* "NOTE: using target " (string-intersperse (map cadr keyvals) "/") " for this run")
    (if (and runname (null? (filter (lambda (x)(not x)) keyvals))) ;; there must be a better way to "apply and"
	(db:with-db
	 dbstruct #f #f
	 (lambda (dbdat db)
	   ;; (debug:print 0 *default-log-port* "Got here 1.")
	   (let ((res #f))
	     (apply sqlite3:execute db
		    (conc "INSERT OR IGNORE INTO runs (runname,state,status,owner,event_time,contour"
			  comma keystr ") VALUES (?,?,?,?,strftime('%s','now'),?" comma valslots ");")
	     (apply sqlite3:execute
		    (db:get-cache-stmth dbdat db
					(conc "INSERT OR IGNORE INTO runs (runname,state,status,owner,event_time,contour"
					      comma keystr ") VALUES (?,?,?,?,strftime('%s','now'),?" comma valslots ");"))
		    allvals)
	     (apply sqlite3:for-each-row 
		    (lambda (id)
		      (set! res id))
		    db
		    (let ((qry (conc "SELECT id FROM runs WHERE (runname=? " andstr key=?str ");")))
		      qry)
1745
1746
1747
1748
1749
1750
1751
1752
1753


1754
1755
1756
1757
1758
1759
1760
1733
1734
1735
1736
1737
1738
1739


1740
1741
1742
1743
1744
1745
1746
1747
1748







-
-
+
+







   #f
   (lambda (dbdat db)
     (let ((numruns 0))
       (debug:print-info 11 *default-log-port* "db:get-num-runs START " runpatt)
       (sqlite3:for-each-row 
	(lambda (count)
	  (set! numruns count))
	db
	"SELECT COUNT(id) FROM runs WHERE runname LIKE ? AND state != 'deleted';" runpatt)
	(db:get-cache-stmth dbdat db
	"SELECT COUNT(id) FROM runs WHERE runname LIKE ? AND state != 'deleted';" )runpatt)
       (debug:print-info 11 *default-log-port* "db:get-num-runs END " runpatt)
       numruns))))

;; just get count of runs
(define (db:get-runs-cnt-by-patt dbstruct runpatt targetpatt keys)
  (db:with-db
   dbstruct
1799
1800
1801
1802
1803
1804
1805
1806
1807


1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824


1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850


1851
1852
1853
1854
1855
1856
1857
1787
1788
1789
1790
1791
1792
1793


1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810


1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821



1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833


1834
1835
1836
1837
1838
1839
1840
1841
1842







-
-
+
+















-
-
+
+









-
-
-












-
-
+
+







   run-id
   #f
   (lambda (dbdat db)
     (sqlite3:fold-row
	(lambda (res state status count)
	  (cons (list state status count) res))
	'()
	db
	"SELECT state,status,count(id) AS count FROM tests WHERE run_id=? AND NOT(uname='n/a' AND item_path='') GROUP BY state,status;"
	(db:get-cache-stmth dbdat db
	"SELECT state,status,count(id) AS count FROM tests WHERE run_id=? AND NOT(uname='n/a' AND item_path='') GROUP BY state,status;")
	run-id))))

;; Update run_stats for given run_id
;; input data is a list (state status count)
;;
(define (db:update-run-stats dbstruct run-id stats)
  ;; (mutex-lock! *db-transaction-mutex*)
  (db:with-db
   dbstruct
   #f
   #f

   (lambda (dbdat db)
     ;; remove previous data
     
     (let* ((stmt1 (sqlite3:prepare db "DELETE FROM run_stats WHERE run_id=? AND state=? AND status=?;"))
	    (stmt2 (sqlite3:prepare db "INSERT INTO run_stats (run_id,state,status,count) VALUES (?,?,?,?);"))
     (let* ((stmt1 (db:get-cache-stmth dbdat db "DELETE FROM run_stats WHERE run_id=? AND state=? AND status=?;"))
	    (stmt2 (db:get-cache-stmth dbdat db "INSERT INTO run_stats (run_id,state,status,count) VALUES (?,?,?,?);"))
	    (res
	     (sqlite3:with-transaction
	      db
	      (lambda ()
		(for-each
		 (lambda (dat)
		   (sqlite3:execute stmt1 run-id (car dat)(cadr dat))
		   (apply sqlite3:execute stmt2 run-id dat))
		 stats)))))
       (sqlite3:finalize! stmt1)
       (sqlite3:finalize! stmt2)
       ;; (mutex-unlock! *db-transaction-mutex*)
       res))))

(define (db:get-main-run-stats dbstruct run-id)
  (db:with-db
   dbstruct
   #f ;; this data comes from main
   #f
   (lambda (dbdat db)
     (sqlite3:fold-row
	(lambda (res state status count)
	  (cons (list state status count) res))
	'()
	db
	"SELECT state,status,count FROM run_stats WHERE run_id=? AND run_id IN (SELECT id FROM runs WHERE state NOT IN ('DELETED','deleted'));"
	(db:get-cache-stmth dbdat db
	"SELECT state,status,count FROM run_stats WHERE run_id=? AND run_id IN (SELECT id FROM runs WHERE state NOT IN ('DELETED','deleted'));")
	run-id))))

(define (db:print-current-query-stats)
  ;; generate stats from *db-api-call-time*
  (let ((ordered-keys (sort (hash-table-keys *db-api-call-time*)
			    (lambda (a b)
			      (let ((sum-a (common:sum (hash-table-ref *db-api-call-time* a)))
1875
1876
1877
1878
1879
1880
1881
1882
1883


1884
1885
1886
1887
1888
1889
1890
1860
1861
1862
1863
1864
1865
1866


1867
1868
1869
1870
1871
1872
1873
1874
1875







-
-
+
+







   #f
   #f
   (lambda (dbdat db)
     (let ((run-ids '()))
       (sqlite3:for-each-row
	(lambda (run-id)
	  (set! run-ids (cons run-id run-ids)))
	db
	"SELECT id FROM runs WHERE state != 'deleted' ORDER BY event_time DESC;")
	(db:get-cache-stmth dbdat db
	"SELECT id FROM runs WHERE state != 'deleted' ORDER BY event_time DESC;"))
    (reverse run-ids)))))

;; get some basic run stats
;;
;; data structure:
;;
;; ( (runname (( state  count ) ... ))
1898
1899
1900
1901
1902
1903
1904
1905
1906


1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926


1927
1928
1929
1930
1931
1932
1933
1883
1884
1885
1886
1887
1888
1889


1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909


1910
1911
1912
1913
1914
1915
1916
1917
1918







-
-
+
+


















-
-
+
+







    ;; First get all the runname/run-ids
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row
	(lambda (run-id runname)
	  (set! runs-info (cons (list run-id runname) runs-info)))
	db
	"SELECT id,runname FROM runs WHERE state != 'deleted' ORDER BY event_time DESC;"))) ;; If you change this to the more logical ASC please adjust calls to db:get-run-stats
	(db:get-cache-stmth dbdat db
	"SELECT id,runname FROM runs WHERE state != 'deleted' ORDER BY event_time DESC;")))) ;; If you change this to the more logical ASC please adjust calls to db:get-run-stats
    ;; for each run get stats data
    (for-each
     (lambda (run-info)
       ;; get the net state/status counts for this run
       (let* ((run-id   (car  run-info))
	      (run-name (cadr run-info)))
	 (db:with-db
	  dbstruct
	  run-id
	  #f
	  (lambda (dbdat db)
	    (sqlite3:for-each-row
	     (lambda (state status count)
	       (let ((netstate (if (equal? state "COMPLETED") status state)))
		 (if (string? netstate)
		     (begin
		       (hash-table-set! totals netstate (+ (hash-table-ref/default totals netstate 0) count))
		       (hash-table-set! curr   netstate (+ (hash-table-ref/default curr   netstate 0) count))))))
	     db
	     "SELECT state,status,count(id) FROM tests AS t WHERE run_id=? GROUP BY state,status ORDER BY state,status DESC;"
	     (db:get-cache-stmth dbdat db
	     "SELECT state,status,count(id) FROM tests AS t WHERE run_id=? GROUP BY state,status ORDER BY state,status DESC;")
             run-id)
	    ;; add the per run counts to res
	    (for-each (lambda (state)
			(set! res (cons (list run-name state (hash-table-ref curr state)) res)))
		      (sort (hash-table-keys curr) string>=))
	    (set! curr (make-hash-table))))))
     runs-info)
2014
2015
2016
2017
2018
2019
2020
2021

2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042

2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063


2064
2065
2066


2067
2068
2069
2070
2071
2072

2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083


2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096


2097
2098
2099
2100
2101
2102
2103
1999
2000
2001
2002
2003
2004
2005

2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026

2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046


2047
2048
2049


2050
2051
2052
2053
2054
2055
2056

2057
2058
2059
2060
2061
2062
2063
2064
2065
2066


2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079


2080
2081
2082
2083
2084
2085
2086
2087
2088







-
+




















-
+



















-
-
+
+

-
-
+
+





-
+









-
-
+
+











-
-
+
+







      ;; (hash-table-set! *run-info-cache* run-id finalres)
      finalres)))

(define (db:set-comment-for-run dbstruct run-id comment)
  (db:with-db
   dbstruct #f #f
   (lambda (dbdat db)
     (sqlite3:execute db "UPDATE runs SET comment=? WHERE id=?;" comment ;; (sdb:qry 'getid comment)
     (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE runs SET comment=? WHERE id=?;") comment ;; (sdb:qry 'getid comment)
		      run-id))))

;; does not (obviously!) removed dependent data. But why not!!?
(define (db:delete-run dbstruct run-id)
  (db:with-db
   dbstruct #f #f
   (lambda (dbdat db)
     (sqlite3:with-transaction
      db
      (lambda ()
        (sqlite3:execute db "DELETE FROM test_steps WHERE test_id IN (SELECT id FROM tests WHERE run_id=?);" run-id)
        (sqlite3:execute db "DELETE FROM test_data WHERE test_id IN (SELECT id FROM tests WHERE run_id=?);"  run-id)
        (sqlite3:execute db "UPDATE tests SET state='DELETED',comment='' WHERE run_id=?;" run-id)
        ;; (db:delay-if-busy dbdat)
        (sqlite3:execute db "UPDATE runs SET state='deleted',comment='' WHERE id=?;" run-id))))))

(define (db:update-run-event_time dbstruct run-id)
  (db:with-db
   dbstruct #f #t
   (lambda (dbdat db)
     (sqlite3:execute db "UPDATE runs SET event_time=strftime('%s','now') WHERE id=?;" run-id))))
     (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE runs SET event_time=strftime('%s','now') WHERE id=?;") run-id))))

(define (db:lock/unlock-run dbstruct run-id lock unlock user)
  (db:with-db
   dbstruct #f #t
   (lambda (dbdat db)
     (let ((newlockval (if lock "locked"
			   (if unlock
			       "unlocked"
			       "locked")))) ;; semi-failsafe
       (sqlite3:execute db "UPDATE runs SET state=? WHERE id=?;" newlockval run-id)
       (sqlite3:execute db "INSERT INTO access_log (user,accessed,args) VALUES(?,strftime('%s','now'),?);"
			user (conc newlockval " " run-id))
       (debug:print-info 1 *default-log-port* "" newlockval " run number " run-id)))))

(define (db:set-run-status dbstruct run-id status msg)
  (db:with-db
   dbstruct #f #f
   (lambda (dbdat db)
       (if msg
         (sqlite3:execute db "UPDATE runs SET status=?,comment=? WHERE id=?;" status msg run-id)
         (sqlite3:execute db "UPDATE runs SET status=? WHERE id=?;" status run-id)))))
         (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE runs SET status=?,comment=? WHERE id=?;") status msg run-id)
         (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE runs SET status=? WHERE id=?;") status run-id)))))

(define (db:set-run-state-status-db db run-id state status )
  (sqlite3:execute db "UPDATE runs SET status=?,state=? WHERE id=?;" status state run-id))
(define (db:set-run-state-status-db dbdat db run-id state status )
  (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE runs SET status=?,state=? WHERE id=?;") status state run-id))

(define (db:set-run-state-status dbstruct run-id state status )
  (db:with-db
   dbstruct #f #f
   (lambda (dbdat db)
     (db:set-run-state-status-db db run-id state status))))
     (db:set-run-state-status-db dbdat db run-id state status))))
     
(define (db:get-run-status dbstruct run-id)
  (let ((res "n/a"))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row 
	(lambda (status)
	  (set! res status))
	db
	"SELECT status FROM runs WHERE id=?;" 
	(db:get-cache-stmth dbdat db
	"SELECT status FROM runs WHERE id=?;" )
	run-id)
       res))))

(define (db:get-run-state dbstruct run-id)
  (let ((res "n/a"))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row 
	(lambda (status)
	  (set! res status))
	db
	"SELECT state FROM runs WHERE id=?;" 
	(db:get-cache-stmth dbdat db
	"SELECT state FROM runs WHERE id=?;" )
	run-id)
       res))))


;;======================================================================
;; K E Y S
;;======================================================================
2112
2113
2114
2115
2116
2117
2118
2119

2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137

2138
2139
2140
2141
2142
2143
2144
2097
2098
2099
2100
2101
2102
2103

2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121

2122
2123
2124
2125
2126
2127
2128
2129







-
+

















-
+







     (lambda (dbdat db)
       (for-each 
	(lambda (key)
	  (let ((qry (conc "SELECT " key " FROM runs WHERE id=?;")))
	    (sqlite3:for-each-row 
	     (lambda (key-val)
	       (set! res (cons (list key (if (string? key-val) key-val "")) res))) ;; replace non-string bad values with empty string to prevent crashes. This scenario can happen when Megatest is killed on updating the db
	     db qry run-id)))
	     (db:get-cache-stmth dbdat db qry) run-id)))
	keys)))
       (reverse res)))

;; get key vals for a given run-id
(define (db:get-key-vals dbstruct run-id)
  (let* ((keys (db:get-keys dbstruct))
	 (res  '()))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (for-each 
	(lambda (key)
	  (let ((qry (conc "SELECT " key " FROM runs WHERE id=?;")))
	    ;; (db:delay-if-busy dbdat)
	    (sqlite3:for-each-row 
	     (lambda (key-val)
	       (set! res (cons (if (string? key-val) key-val "") res))) ;; check that the key-val is a string for cases where a crash injected bad data in the megatest.db
	     db qry run-id)))
	     (db:get-cache-stmth dbdat db qry) run-id)))
	keys)))
    (let ((final-res (reverse res)))
      (hash-table-set! *keyvals* run-id final-res)
      final-res)))

;; The target is keyval1/keyval2..., cached in *target* as it is used often
(define (db:get-target dbstruct run-id)
2158
2159
2160
2161
2162
2163
2164
2165
2166


2167
2168
2169
2170
2171
2172
2173
2143
2144
2145
2146
2147
2148
2149


2150
2151
2152
2153
2154
2155
2156
2157
2158







-
-
+
+







          '()
          (begin
            (db:with-db dbstruct #f #f ;; #f means work with the zeroth db - i.e. the runs db
                        (lambda (dbdat db)
                          (apply sqlite3:for-each-row
                                 (lambda (id)
                                   (set! prev-run-ids (cons id prev-run-ids)))
                                 db
                                 (conc "SELECT id FROM runs WHERE " qrystr " AND state != 'deleted' AND id != ?;")
                                 (db:get-cache-stmth dbdat db
                                 (conc "SELECT id FROM runs WHERE " qrystr " AND state != 'deleted' AND id != ?;"))
                                 (append kvalues (list run-id)))))
            prev-run-ids)))))

;;======================================================================
;;  T E S T S
;;======================================================================

2335
2336
2337
2338
2339
2340
2341
2342
2343


2344
2345
2346
2347
2348
2349
2350
2320
2321
2322
2323
2324
2325
2326


2327
2328
2329
2330
2331
2332
2333
2334
2335







-
-
+
+







  (let ((res            #f))
    (db:with-db dbstruct run-id #f
		(lambda (dbdat db)
		  (sqlite3:for-each-row
		   (lambda (run-id testname item-path state status)
		     ;; id,run_id,testname,state,status,event_time,host,cpuload,diskfree,uname,rundir,item_path,run_duration,final_logf,comment
		     (set! res (vector test-id run-id testname state status -1 "" -1 -1 "" "-" item-path -1 "-" "-")))
		   db 
		   "SELECT run_id,testname,item_path,state,status FROM tests WHERE id=? and run_id=?;" 
		   (db:get-cache-stmth dbdat db 
		   "SELECT run_id,testname,item_path,state,status FROM tests WHERE id=? and run_id=?;" )
		   test-id run-id)))
    res))

;; get a useful subset of the tests data (used in dashboard
;; use db:mintest-get-{id ,run_id,testname ...}
;;
(define (db:get-tests-for-run-mindata dbstruct run-id testpatt states statuses not-in)
2370
2371
2372
2373
2374
2375
2376
2377

2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394



2395
2396
2397
2398
2399
2400
2401
2355
2356
2357
2358
2359
2360
2361

2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376



2377
2378
2379
2380
2381
2382
2383
2384
2385
2386







-
+














-
-
-
+
+
+








(define (db:delete-test-records dbstruct run-id test-id)
  (db:general-call dbstruct run-id 'delete-test-step-records (list test-id))
  (db:general-call dbstruct run-id 'delete-test-data-records (list test-id))
  (db:with-db
   dbstruct run-id #f
   (lambda (dbdat db)
     (sqlite3:execute db "UPDATE tests SET state='DELETED',status='n/a',comment='' WHERE id=?;" test-id))))
     (sqlite3:execute (db:get-cache-stmth dbdat 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)))))))
	  (sqlite3:execute (db:get-cache-stmth dbdat db "DELETE FROM test_steps WHERE test_id IN (SELECT id FROM tests WHERE state='DELETED' AND event_time<?);") targtime)
	  (sqlite3:execute (db:get-cache-stmth dbdat db "DELETE FROM test_data WHERE test_id IN (SELECT id FROM tests WHERE state='DELETED' AND event_time<?);") targtime)
	  (sqlite3:execute (db:get-cache-stmth dbdat 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)
2434
2435
2436
2437
2438
2439
2440
2441

2442
2443

2444
2445
2446

2447
2448
2449

2450
2451
2452
2453



2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469

2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481

2482
2483
2484

2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499

2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512

2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525


2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539


2540
2541
2542
2543
2544
2545
2546
2419
2420
2421
2422
2423
2424
2425

2426
2427

2428
2429
2430

2431
2432
2433

2434
2435



2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453

2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465

2466
2467
2468

2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483

2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496

2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508


2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522


2523
2524
2525
2526
2527
2528
2529
2530
2531







-
+

-
+


-
+


-
+

-
-
-
+
+
+















-
+











-
+


-
+














-
+












-
+











-
-
+
+












-
-
+
+







;;      NOTE: run-id is not used
;; ;;
(define (db:test-set-state-status dbstruct run-id test-id newstate newstatus newcomment)
  (db:with-db
   dbstruct
   run-id #f
   (lambda (dbdat db)
     (db:test-set-state-status-db db run-id test-id newstate newstatus newcomment))))
     (db:test-set-state-status-db dbdat db run-id test-id newstate newstatus newcomment))))

(define (db:test-set-state-status-db db run-id test-id newstate newstatus newcomment)
(define (db:test-set-state-status-db dbdat db run-id test-id newstate newstatus newcomment)
  (cond
   ((and newstate newstatus newcomment)
    (sqlite3:execute db "UPDATE tests SET state=?,status=?,comment=? WHERE id=?;" newstate newstatus newcomment ;; (sdb:qry 'getid newcomment)
    (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET state=?,status=?,comment=? WHERE id=?;") newstate newstatus newcomment ;; (sdb:qry 'getid newcomment)
		     test-id))
   ((and newstate newstatus)
    (sqlite3:execute db "UPDATE tests SET state=?,status=? WHERE id=?;" newstate newstatus test-id))
    (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET state=?,status=? WHERE id=?;") newstate newstatus test-id))
   (else
    (if newstate   (sqlite3:execute db "UPDATE tests SET state=?   WHERE id=?;" newstate   test-id))
    (if newstatus  (sqlite3:execute db "UPDATE tests SET status=?  WHERE id=?;" newstatus  test-id))
    (if newcomment (sqlite3:execute db "UPDATE tests SET comment=? WHERE id=?;" newcomment ;; (sdb:qry 'getid newcomment)
    (if newstate   (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET state=?   WHERE id=?;") newstate   test-id))
    (if newstatus  (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET status=?  WHERE id=?;") newstatus  test-id))
    (if newcomment (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET comment=? WHERE id=?;") newcomment ;; (sdb:qry 'getid newcomment)
				    test-id))))
  ;; (mt:process-triggers dbstruct run-id test-id newstate newstatus)) ;; NOTE: Moved into calling function
  )

;; NEW BEHAVIOR: Count tests running in all runs!
;;
(define (db:get-count-tests-running dbstruct run-id) ;; fastmode)
  (let* ((qry ;; (if fastmode
		;;   "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND NOT (uname = 'n/a' AND item_path = '') LIMIT 1;"
		  "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND NOT (uname = 'n/a' AND item_path = '');")) ;; )
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (let* ((stmth (db:get-cache-stmth dbdat run-id db qry)))
     (let* ((stmth (db:get-cache-stmth dbdat db qry)))
       (sqlite3:first-result stmth))))))

;; NEW BEHAVIOR: Count tests running in only one run!
;;
(define (db:get-count-tests-actually-running dbstruct run-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (sqlite3:first-result
      db
      (db:get-cache-stmth dbdat db
      ;; WARNING BUG EDIT ME - merged from v1.55 - not sure what is right here ...
      ;; "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND run_id NOT IN (SELECT id FROM runs WHERE state='deleted') AND NOT (uname = 'n/a' AND item_path = '');")
      "SELECT count(id) FROM tests WHERE state in ('RUNNING','REMOTEHOSTSTART','LAUNCHED') AND run_id=?;" 
      "SELECT count(id) FROM tests WHERE state in ('RUNNING','REMOTEHOSTSTART','LAUNCHED') AND run_id=?;" )
      run-id)))) ;; NOT IN (SELECT id FROM runs WHERE state='deleted');")

;; NEW BEHAVIOR: Look only at single run with run-id
;; 
;; (define (db:get-running-stats dbstruct run-id)
(define (db:get-count-tests-running-for-run-id dbstruct run-id) ;; fastmode)
  (let* ((qry ;; (if fastmode
		 ;;  "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND run_id=? LIMIT 1;"
		  "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND run_id=?;")) ;; )
    (db:with-db
     dbstruct
     run-id
     #f
     (lambda (dbdat db)
       (let* ((stmth (db:get-cache-stmth dbdat run-id db qry)))
       (let* ((stmth (db:get-cache-stmth dbdat db qry)))
	 (sqlite3:first-result stmth run-id))))))

;; For a given testname how many items are running? Used to determine
;; probability for regenerating html
;;
(define (db:get-count-tests-running-for-testname dbstruct run-id testname)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (let* ((stmt "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND run_id=? AND NOT (uname = 'n/a' AND item_path = '') AND testname=?;")
	    (stmth (db:get-cache-stmth dbdat run-id db stmt)))
	    (stmth (db:get-cache-stmth dbdat db stmt)))
       (sqlite3:first-result
	stmth run-id testname)))))

(define (db:get-not-completed-cnt dbstruct run-id)
(db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
      ;(print "SELECT count(id) FROM tests WHERE state not in ('COMPLETED', 'DELETED') AND run_id=" run-id)  
     (sqlite3:first-result
      db
      "SELECT count(id) FROM tests WHERE state not in ('COMPLETED', 'DELETED') AND run_id=?;" run-id))))
      (db:get-cache-stmth dbdat db
      "SELECT count(id) FROM tests WHERE state not in ('COMPLETED', 'DELETED') AND run_id=?;") run-id))))

(define (db:get-count-tests-running-in-jobgroup dbstruct run-id jobgroup)
  (if (not jobgroup)
      0 ;; 
      (let ((testnames '()))
	;; get the testnames
	(db:with-db
	 dbstruct #f #f
	 (lambda (dbdat db)
	   (sqlite3:for-each-row
	    (lambda (testname)
	      (set! testnames (cons testname testnames)))
	    db
	    "SELECT testname FROM test_meta WHERE jobgroup=?"
	    (db:get-cache-stmth dbdat db
	    "SELECT testname FROM test_meta WHERE jobgroup=?")
	    jobgroup)))
	;; get the jobcount NB// EXTEND THIS TO OPPERATE OVER ALL RUNS?
	(if (not (null? testnames))
	    (db:with-db
	     dbstruct
	     run-id
	     #f
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570



2571
2572
2573
2574
2575
2576
2577
2546
2547
2548
2549
2550
2551
2552



2553
2554
2555
2556
2557
2558
2559
2560
2561
2562







-
-
-
+
+
+







(define (db:estimated-tests-remaining dbstruct run-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (sqlite3:first-result
      db
      "SELECT count(id) FROM tests WHERE state in ('LAUNCHED','NOT_STARTED','REMOTEHOSTSTART','RUNNING','KILLREQ') AND run_id=?;")
     run-id)))
      (db:get-cache-stmth dbdat db
			  "SELECT count(id) FROM tests WHERE state in ('LAUNCHED','NOT_STARTED','REMOTEHOSTSTART','RUNNING','KILLREQ') AND run_id=?;")
      run-id))))

;; map run-id, testname item-path to test-id
(define (db:get-test-id dbstruct run-id testname item-path)
  (db:with-db
   dbstruct
   run-id
   #f
2587
2588
2589
2590
2591
2592
2593
2594

2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605


2606
2607
2608
2609
2610
2611
2612
2572
2573
2574
2575
2576
2577
2578

2579
2580
2581
2582
2583
2584
2585
2586
2587
2588


2589
2590
2591
2592
2593
2594
2595
2596
2597







-
+









-
-
+
+







;;
(define (db:test-set-top-process-pid dbstruct run-id test-id pid)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (sqlite3:execute db "UPDATE tests SET attemptnum=? WHERE id=?;"
     (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET attemptnum=? WHERE id=?;")
		      pid test-id))))

(define (db:test-get-top-process-pid dbstruct run-id test-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (db:first-result-default 
      db
      "SELECT attemptnum FROM tests WHERE id=?;"
      (db:get-cache-stmth dbdat db
      "SELECT attemptnum FROM tests WHERE id=?;")
      #f
      test-id))))

(define db:test-record-fields '("id"           "run_id"        "testname"  "state"      "status"      "event_time"
				"host"         "cpuload"       "diskfree"  "uname"      "rundir"      "item_path"
                                "run_duration" "final_logf"    "comment"   "shortdir"   "attemptnum"  "archived" "last_update"))

2626
2627
2628
2629
2630
2631
2632
2633

2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649


2650
2651
2652
2653
2654
2655
2656
2657
2658

2659
2660
2661
2662
2663
2664
2665
2666
2667
2668


2669
2670
2671
2672
2673
2674
2675
2611
2612
2613
2614
2615
2616
2617

2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632


2633
2634
2635
2636
2637
2638
2639
2640
2641
2642

2643
2644
2645
2646
2647
2648
2649
2650
2651
2652

2653
2654
2655
2656
2657
2658
2659
2660
2661







-
+














-
-
+
+








-
+









-
+
+








(define db:test-record-qry-selector (string-intersperse db:test-record-fields ","))

(define (db:update-tesdata-on-repilcate-db dbstruct old-lt new-lt)
  (db:with-db
   dbstruct   #f   #f
   (lambda (dbdat db)
     (sqlite3:execute db "UPDATE tests SET rundir= replace(rundir,?,?), shortdir=replace(shortdir,?,?);"
     (sqlite3:execute (db:get-cache-stmth dbdat db "UPDATE tests SET rundir= replace(rundir,?,?), shortdir=replace(shortdir,?,?);")
		      old-lt new-lt  old-lt new-lt))))

;; NOTE: Use db:test-get* to access records
;; NOTE: This needs rundir decoding? Decide, decode here or where used? For the moment decode where used.
(define (db:get-all-tests-info-by-run-id dbstruct run-id)
  (let* ((res '()))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row
	(lambda (id run-id testname state status event-time host cpuload diskfree uname rundir item-path run-duration final-logf comment shortdir attemptnum archived)
	  ;;                 0    1       2      3      4        5       6      7        8     9     10      11          12          13       14     15        16
	  (set! res (cons (vector id run-id testname state status event-time host cpuload diskfree uname rundir item-path run-duration final-logf comment shortdir attemptnum archived)
			  res)))
	db
	(conc "SELECT " db:test-record-qry-selector " FROM tests WHERE state != 'DELETED' AND run_id=?;")
	(db:get-cache-stmth dbdat db
	(conc "SELECT " db:test-record-qry-selector " FROM tests WHERE state != 'DELETED' AND run_id=?;"))
	run-id)))
    res))

(define (db:replace-test-records dbstruct run-id testrecs)
  (db:with-db dbstruct run-id #t 
	      (lambda (dbdat db)
		(let* ((qmarks (string-intersperse (make-list (length db:test-record-fields) "?") ","))
		       (qrystr (conc "INSERT OR REPLACE INTO tests (" db:test-record-qry-selector ") VALUES (" qmarks ") WHERE run_id=?;"))
		       (qry    (sqlite3:prepare db qrystr)))
		       (qry    (db:get-cache-stmth dbdat db qrystr)))
		  (debug:print 0 *default-log-port* "INFO: migrating test records for run with id " run-id)
		  (sqlite3:with-transaction
		   db
		   (lambda ()
		     (for-each 
		      (lambda (rec)
			;; (debug:print 0 *default-log-port* "INFO: Inserting values: " (string-intersperse (map conc (vector->list rec)) ",") "\n")
			(apply sqlite3:execute qry (append (vector->list rec)(list run-id))))
		      testrecs)))
		  (sqlite3:finalize! qry)))))
		  ;; (sqlite3:finalize! qry)
		  ))))

;; map a test-id into the proper range
;;
(define (db:adj-test-id mtdb min-test-id test-id)
  (if (>= test-id min-test-id)
      test-id
      (let loop ((new-id min-test-id))
2718
2719
2720
2721
2722
2723
2724
2725
2726


2727
2728
2729
2730
2731
2732
2733
2704
2705
2706
2707
2708
2709
2710


2711
2712
2713
2714
2715
2716
2717
2718
2719







-
-
+
+







   #f
   (lambda (dbdat db)
     (let ((res #f))
       (sqlite3:for-each-row ;; attemptnum added to hold pid of top process (not Megatest) controlling a test
	(lambda (id run-id testname state status event-time host cpuload diskfree uname rundir-id item-path run_duration final-logf-id comment short-dir-id attemptnum archived last-update)
	  ;;                0    1       2      3      4        5       6      7        8     9     10      11          12          13           14         15          16
	  (set! res (vector id run-id testname state status event-time host cpuload diskfree uname rundir-id item-path run_duration final-logf-id comment short-dir-id attemptnum archived last-update)))
	db
	(conc "SELECT " db:test-record-qry-selector " FROM tests WHERE id=?;")
	(db:get-cache-stmth dbdat db
	(conc "SELECT " db:test-record-qry-selector " FROM tests WHERE id=?;"))
	test-id)
       res))))

;; Use db:test-get* to access
;; Get test data using test_ids. NB// Only works within a single run!!
;;
(define (db:get-test-info-by-ids dbstruct run-id test-ids)
2748
2749
2750
2751
2752
2753
2754
2755

2756
2757

2758
2759
2760
2761
2762
2763


2764
2765
2766
2767
2768
2769
2770
2734
2735
2736
2737
2738
2739
2740

2741
2742

2743
2744
2745
2746
2747


2748
2749
2750
2751
2752
2753
2754
2755
2756







-
+

-
+




-
-
+
+








(define (db:get-test-info dbstruct run-id test-name item-path)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (db:get-test-info-db db run-id test-name item-path))))
     (db:get-test-info-db dbdat db run-id test-name item-path))))

(define (db:get-test-info-db db run-id test-name item-path)
(define (db:get-test-info-db dbdat db run-id test-name item-path)
  (let ((res #f))
    (sqlite3:for-each-row
     (lambda (a . b)
       (set! res (apply vector a b)))
     db
     (conc "SELECT " db:test-record-qry-selector " FROM tests WHERE testname=? AND item_path=? AND run_id=?;")
     (db:get-cache-stmth dbdat db
     (conc "SELECT " db:test-record-qry-selector " FROM tests WHERE testname=? AND item_path=? AND run_id=?;"))
     test-name item-path run-id)
    res))

(define (db:test-get-rundir-from-test-id dbstruct run-id test-id)
  (db:with-db
   dbstruct
   run-id
2785
2786
2787
2788
2789
2790
2791
2792
2793


2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809


2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825


2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841


2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856


2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871


2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886

2887
2888
2889
2890
2891
2892
2893
2771
2772
2773
2774
2775
2776
2777


2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793


2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809


2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825


2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840


2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855


2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871

2872
2873
2874
2875
2876
2877
2878
2879







-
-
+
+














-
-
+
+














-
-
+
+














-
-
+
+













-
-
+
+













-
-
+
+














-
+







    dbstruct
    #f ;; this is for the main runs db
    #f ;; does not modify db
    (lambda (dbdat db)
            (sqlite3:for-each-row
	(lambda (test-name item-path test-time target )
	  (set! res (cons (vector test-name item-path test-time) res)))
	db
        qry 
	(db:get-cache-stmth dbdat db
        qry )
	run-name target)
       res))))

;;======================================================================
;; S T E P S
;;======================================================================

(define (db:teststep-set-status! dbstruct run-id test-id teststep-name state-in status-in comment logfile)
  (db:with-db
   dbstruct
   run-id
   #t
   (lambda (dbdat db)
     (sqlite3:execute 
      db
      "INSERT OR REPLACE into test_steps (test_id,stepname,state,status,event_time,comment,logfile) VALUES(?,?,?,?,?,?,?);"
      (db:get-cache-stmth dbdat db
      "INSERT OR REPLACE into test_steps (test_id,stepname,state,status,event_time,comment,logfile) VALUES(?,?,?,?,?,?,?);")
      test-id teststep-name state-in status-in (current-seconds)
      (if comment comment "")
      (if logfile logfile "")))))



(define (db:delete-steps-for-test! dbstruct run-id test-id)
  ;; TODO: figure out why status is the key field rather than state (note:  CONSTRAINT test_steps_constraint UNIQUE (test_id,stepname,state) )
  (db:with-db
   dbstruct
   run-id
   #t
   (lambda (dbdat db)
     (sqlite3:execute 
      db
      "UPDATE test_steps set status='DELETED' where test_id=?" ;;  and run_id=? !! - run_id not in table (bummer) TODO: get run_id into schema for test_steps
      (db:get-cache-stmth dbdat db
      "UPDATE test_steps set status='DELETED' where test_id=?") ;;  and run_id=? !! - run_id not in table (bummer) TODO: get run_id into schema for test_steps
      test-id))))


;; db-get-test-steps-for-run
(define (db:get-steps-for-test dbstruct run-id test-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (let* ((res '()))
       (sqlite3:for-each-row 
	(lambda (id test-id stepname state status event-time logfile comment)
	  (set! res (cons (vector id test-id stepname state status event-time (if (string? logfile) logfile "") comment) res)))
	db
	"SELECT id,test_id,stepname,state,status,event_time,logfile,comment FROM test_steps WHERE status != 'DELETED' AND test_id=? ORDER BY id ASC;" ;; event_time DESC,id ASC;
	(db:get-cache-stmth dbdat db
	"SELECT id,test_id,stepname,state,status,event_time,logfile,comment FROM test_steps WHERE status != 'DELETED' AND test_id=? ORDER BY id ASC;") ;; event_time DESC,id ASC;
	test-id)
       (reverse res)))))

 (define (db:get-steps-info-by-id dbstruct run-id test-step-id)
   (db:with-db
    dbstruct
    run-id
    #f
    (lambda (dbdat db)
      (let* ((res (vector #f #f #f #f #f #f #f #f #f)))
        (sqlite3:for-each-row 
       (lambda (id test-id stepname state status event-time logfile comment last-update)
         (set! res (vector id test-id stepname state status event-time (if (string? logfile) logfile "") comment last-update)))
       db
       "SELECT id,test_id,stepname,state,status,event_time,logfile,comment,last_update FROM test_steps WHERE id=? ORDER BY id ASC;" ;; event_time DESC,id ASC;
       (db:get-cache-stmth dbdat db
       "SELECT id,test_id,stepname,state,status,event_time,logfile,comment,last_update FROM test_steps WHERE id=? ORDER BY id ASC;") ;; event_time DESC,id ASC;
       test-step-id)
        res))))

(define (db:get-steps-data dbstruct run-id test-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (let ((res '()))
       (sqlite3:for-each-row 
	(lambda (id test-id stepname state status event-time logfile)
	  (set! res (cons (vector id test-id stepname state status event-time (if (string? logfile) logfile "")) res)))
	db
	"SELECT id,test_id,stepname,state,status,event_time,logfile FROM test_steps WHERE status != 'DELETED' AND test_id=? ORDER BY id ASC;" ;; event_time DESC,id ASC;
	(db:get-cache-stmth dbdat db
	"SELECT id,test_id,stepname,state,status,event_time,logfile FROM test_steps WHERE status != 'DELETED' AND test_id=? ORDER BY id ASC;") ;; event_time DESC,id ASC;
	test-id)
       (reverse res)))))

;;======================================================================
;; T E S T  D A T A 
;;======================================================================

(define (db:get-data-info-by-id dbstruct run-id test-data-id)
  (let* ((stmt        "SELECT id,test_id, category, variable, value, expected, tol, units, comment, status, type, last_update FROM test_data WHERE id=? ORDER BY id ASC;")) ;; event_time DESC,id ASC;
    (db:with-db
     dbstruct
     run-id
     #f
     (lambda (dbdat db)
       (let* ((stmth (db:get-cache-stmth dbdat #f db stmt))
       (let* ((stmth (db:get-cache-stmth dbdat db stmt))
	      (res   (sqlite3:fold-row
		      (lambda (res id test-id  category variable value expected tol units comment status type last-update)
			(vector id test-id  category variable value expected tol units comment status type last-update))
		      (vector #f #f #f #f #f #f #f #f #f #f #f #f)
		      stmth
		      test-data-id)))
        res)))))
2903
2904
2905
2906
2907
2908
2909
2910

2911
2912

2913
2914
2915
2916
2917
2918
2919
2889
2890
2891
2892
2893
2894
2895

2896
2897

2898
2899
2900
2901
2902
2903
2904
2905







-
+

-
+







    (db:with-db
     dbstruct run-id #f
     (lambda (dbdat db)
       (sqlite3:for-each-row
	(lambda (fcount pcount)
	  (set! fail-count fcount)
	  (set! pass-count pcount))
	db 
	(db:get-cache-stmth dbdat db 
	"SELECT (SELECT count(id) FROM test_data WHERE test_id=? AND status like 'fail') AS fail_count,
             (SELECT count(id) FROM test_data WHERE test_id=? AND status like 'pass') AS pass_count;"
             (SELECT count(id) FROM test_data WHERE test_id=? AND status like 'pass') AS pass_count;")
	test-id test-id)
       ;; Now rollup the counts to the central megatest.db
       (db:general-call dbstruct run-id 'pass-fail-counts (list pass-count fail-count test-id))
       ;; if the test is not FAIL then set status based on the fail and pass counts.
       (db:general-call dbstruct run-id 'test_data-pf-rollup (list test-id test-id test-id test-id))))))

;; each section is a rule except "final" which is the final result
3064
3065
3066
3067
3068
3069
3070
3071
3072


3073
3074
3075
3076
3077
3078
3079
3080
3081
3082
3083
3084
3085
3086


3087
3088
3089
3090
3091
3092
3093
3050
3051
3052
3053
3054
3055
3056


3057
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070


3071
3072
3073
3074
3075
3076
3077
3078
3079







-
-
+
+












-
-
+
+







  (let* ((res '()))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row 
	(lambda (id test_id category variable value expected tol units comment status type)
	  (set! res (cons (vector id test_id category variable value expected tol units comment status type) res)))
	db
	"SELECT id,test_id,category,variable,value,expected,tol,units,comment,status,type FROM test_data WHERE test_id=? AND category LIKE ? ORDER BY category,variable;" test-id categorypatt)
	(db:get-cache-stmth dbdat db
	"SELECT id,test_id,category,variable,value,expected,tol,units,comment,status,type FROM test_data WHERE test_id=? AND category LIKE ? ORDER BY category,variable;") test-id categorypatt)
       (reverse res)))))

;; This routine moved from tdb.scm, :read-test-data
;;
(define (db:read-test-data-varpatt dbstruct run-id test-id categorypatt varpatt)
  (let* ((res '()))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row 
	(lambda (id test_id category variable value expected tol units comment status type)
	  (set! res (cons (vector id test_id category variable value expected tol units comment status type) res)))
	db
	"SELECT id,test_id,category,variable,value,expected,tol,units,comment,status,type FROM test_data WHERE test_id=? AND category LIKE ? AND variable LIKE ? ORDER BY category,variable;" test-id categorypatt varpatt)
	(db:get-cache-stmth dbdat db
	"SELECT id,test_id,category,variable,value,expected,tol,units,comment,status,type FROM test_data WHERE test_id=? AND category LIKE ? AND variable LIKE ? ORDER BY category,variable;") test-id categorypatt varpatt)
       (reverse res)))))


;;======================================================================
;; Misc. test related queries
;;======================================================================

3136
3137
3138
3139
3140
3141
3142
3143
3144


3145
3146
3147
3148
3149
3150
3151
3122
3123
3124
3125
3126
3127
3128


3129
3130
3131
3132
3133
3134
3135
3136
3137







-
-
+
+







   run-id
   #f
   (lambda (dbdat db)
     (let ((res 0))
       (sqlite3:for-each-row
	(lambda (num-items)
	  (set! res num-items))
	db
	"SELECT count(id) FROM tests WHERE run_id=? AND testname=? AND item_path != '' AND state NOT IN ('DELETED');"
	(db:get-cache-stmth dbdat db
	"SELECT count(id) FROM tests WHERE run_id=? AND testname=? AND item_path != '' AND state NOT IN ('DELETED');")
	run-id
	testname)
       res))))

;;======================================================================
;; QUEUE UP META, TEST STATUS AND STEPS REMOTE ACCESS
;;======================================================================
3227
3228
3229
3230
3231
3232
3233
3234

3235
3236

3237
3238
3239
3240
3241
3242
3243
3244
3245
3246
3247
3248
3249

3250
3251
3252
3253
3254
3255
3256
3213
3214
3215
3216
3217
3218
3219

3220
3221

3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234

3235
3236
3237
3238
3239
3240
3241
3242







-
+

-
+












-
+







     dbstruct run-id #f
     (lambda (dbdat db)
       (let ((tr-res
              (sqlite3:with-transaction
               db
               (lambda ()
                 ;; NB// Pass the db so it is part fo the transaction
                 (db:test-set-state-status-db db run-id test-id state status comment) ;; this call sets the item state/status
                 (db:test-set-state-status-db dbdat db run-id test-id state status comment) ;; this call sets the item state/status
                 (if (not (equal? item-path "")) ;; only roll up IF incoming test is an item
                     (let* ((state-status-counts (db:get-all-state-status-counts-for-test db run-id test-name item-path state status)) ;; item-path is used to exclude current state/status of THIS test
                     (let* ((state-status-counts (db:get-all-state-status-counts-for-test dbdat db run-id test-name item-path state status)) ;; item-path is used to exclude current state/status of THIS test
			    (state-statuses      (db:roll-up-rules state-status-counts state status))
                            (newstate            (car state-statuses))
                            (newstatus           (cadr state-statuses)))
		       (set! new-state-eh newstate)
		       (set! new-status-eh newstatus)
                       (debug:print 4 *default-log-port* "BB> tl-test-id="tl-test-id" ; "test-name":"item-path" newstate="newstate" newstatus="newstatus" len(sscs)="(length state-status-counts)  " state-status-counts: "
				    (apply conc
					   (map (lambda (x)
						  (conc
                     				   (with-output-to-string (lambda () (pp (dbr:counts->alist x)))) " | "))
						state-status-counts))); end debug:print
		       (if tl-test-id
			   (db:test-set-state-status-db db run-id tl-test-id newstate newstatus #f)) ;; we are still in the transaction - must access the db and not the dbstruct
			   (db:test-set-state-status-db dbdat db run-id tl-test-id newstate newstatus #f)) ;; we are still in the transaction - must access the db and not the dbstruct
		       ))))))
         (mutex-unlock! *db-transaction-mutex*)
         (if (and test-id state status (equal? status "AUTO")) 
             (db:test-data-rollup dbstruct run-id test-id status))
	 (if new-state-eh ;; moved from db:test-set-state-status
	      (mt:process-triggers dbstruct run-id test-id new-state-eh new-status-eh))
         tr-res)))))
3321
3322
3323
3324
3325
3326
3327
3328

3329
3330
3331
3332
3333
3334
3335
3336
3337


3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351


3352
3353
3354
3355
3356
3357

3358
3359

3360
3361
3362
3363
3364
3365
3366
3307
3308
3309
3310
3311
3312
3313

3314
3315
3316
3317
3318
3319
3320
3321


3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335


3336
3337
3338
3339
3340
3341
3342

3343
3344

3345
3346
3347
3348
3349
3350
3351
3352







-
+







-
-
+
+












-
-
+
+





-
+

-
+







               db
               (lambda ()
                   (let* ((state-status-counts (db:get-all-state-status-counts-for-run-db db run-id))
			  (state-statuses      (db:roll-up-rules state-status-counts #f #f ))
                          (newstate            (car state-statuses))
                          (newstatus           (cadr state-statuses))) 
		     (if (or (not (eq? newstate curr-state)) (not (eq?  newstatus curr-status)))
			 (db:set-run-state-status-db db run-id newstate newstatus )))))))
			 (db:set-run-state-status-db dbdat db run-id newstate newstatus )))))))
         (mutex-unlock! *db-transaction-mutex*)
         tr-res))))

(define (db:get-all-state-status-counts-for-run-db db run-id)
  (sqlite3:map-row
   (lambda (state status count)
     (make-dbr:counts state: state status: status count: count))
   db
   "SELECT state,status,count(id) FROM tests WHERE run_id=?  GROUP BY state,status;"
   (db:get-cache-stmth dbdat db
   "SELECT state,status,count(id) FROM tests WHERE run_id=?  GROUP BY state,status;")
   run-id ))

(define (db:get-all-state-status-counts-for-run dbstruct run-id)
  (db:with-db
   dbstruct #f #f
   (lambda (dbdat db)
     (db:get-all-state-status-counts-for-run-db dbstruct run-id))))

;; BBnote: db:get-all-state-status-counts-for-test returns dbr:counts object aggregating state and status of items of a given test, *not including rollup state/status*
;;
;; NOTE: This is called within a transaction
;;
(define (db:get-all-state-status-counts-for-test db run-id test-name item-path item-state-in item-status-in)
  (let* ((test-info   (db:get-test-info-db db run-id test-name item-path))
(define (db:get-all-state-status-counts-for-test dbdat db run-id test-name item-path item-state-in item-status-in)
  (let* ((test-info   (db:get-test-info-db dbdat db run-id test-name item-path))
         (item-state  (or item-state-in (db:test-get-state test-info))) 
         (item-status (or item-status-in (db:test-get-status test-info)))
         (other-items-count-recs (sqlite3:map-row
                                  (lambda (state status count)
                                    (make-dbr:counts state: state status: status count: count))
                                  db
                                  (db:get-cache-stmth dbdat db
                                  ;; ignore current item because we have changed its value in the current transation so this select will see the old value.
                                  "SELECT state,status,count(id) FROM tests WHERE run_id=? AND testname=? AND item_path != '' AND item_path !=? GROUP BY state,status;"
                                  "SELECT state,status,count(id) FROM tests WHERE run_id=? AND testname=? AND item_path != '' AND item_path !=? GROUP BY state,status;")
                                  run-id test-name item-path))
         ;; add current item to tally outside of sql query
	 (match-countrec-lambda (lambda (countrec) 
				  (and (equal? (dbr:counts-state  countrec) item-state)
                                       (equal? (dbr:counts-status countrec) item-status))))
	 
         (already-have-count-rec-list
3404
3405
3406
3407
3408
3409
3410
3411
3412


3413
3414
3415
3416
3417
3418
3419
3390
3391
3392
3393
3394
3395
3396


3397
3398
3399
3400
3401
3402
3403
3404
3405







-
-
+
+







	  ;; (let ((path       (sdb:qry 'getstr path-id))
	  ;;       (final_logf (sdb:qry 'getstr final_logf-id)))
	  (set! logf final_logf)
	  (set! res (list path final_logf))
	  (if (directory? path)
	      (debug:print 2 *default-log-port* "Found path: " path)
	      (debug:print 2 *default-log-port* "No such path: " path))) ;; )
	db
	"SELECT rundir,final_logf FROM tests WHERE testname=? AND item_path='' AND run_id=?;"
	(db:get-cache-stmth dbdat db
	"SELECT rundir,final_logf FROM tests WHERE testname=? AND item_path='' AND run_id=?;")
	test-name run-id)
       res))))

;;======================================================================
;; A G R E G A T E D   T R A N S A C T I O N   D B   W R I T E S 
;;======================================================================

3589
3590
3591
3592
3593
3594
3595
3596

3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607
3608
3609
3610


3611
3612
3613
3614
3615
3616
3617
3618
3619
3620
3621
3622
3623


3624
3625
3626
3627
3628
3629
3630
3575
3576
3577
3578
3579
3580
3581

3582
3583
3584
3585
3586
3587
3588
3589
3590
3591
3592
3593
3594


3595
3596
3597
3598
3599
3600
3601
3602
3603
3604
3605
3606
3607


3608
3609
3610
3611
3612
3613
3614
3615
3616







-
+












-
-
+
+











-
-
+
+







				       (string->symbol stmtname)
				       stmtname)
				   db:queries)))
 		 (if q (car q) #f))))
    (db:with-db
     dbstruct run-id #f
     (lambda (dbdat db)
       (apply sqlite3:execute db query params)
       (apply sqlite3:execute (db:get-cache-stmth dbdat db query) params)
       #t))))

;; get a summary of state and status counts to calculate a rollup
;;
(define (db:get-state-status-summary dbstruct run-id testname)
  (let ((res   '()))
    (db:with-db
     dbstruct run-id #f
     (lambda (dbdat db)
       (sqlite3:for-each-row
	(lambda (state status count)
	  (set! res (cons (vector state status count) res)))
	db
	"SELECT state,status,count(state) FROM tests WHERE run_id=? AND testname=? AND item_path='' GROUP BY state,status;"
	(db:get-cache-stmth dbdat db
	"SELECT state,status,count(state) FROM tests WHERE run_id=? AND testname=? AND item_path='' GROUP BY state,status;")
	run-id testname)
       res))))

(define (db:get-latest-host-load dbstruct raw-hostname)
  (let* ((hostname (string-substitute "\\..*$" "" raw-hostname))
         (res  (cons -1 0)))
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row
        (lambda (cpuload update-time)  (set! res (cons cpuload update-time)))
        db
        "SELECT tr.cpuload, tr.update_time FROM test_rundat tr, tests t WHERE t.host=? AND tr.cpuload != -1  AND tr.test_id=t.id ORDER BY tr.update_time DESC LIMIT 1;"
        (db:get-cache-stmth dbdat db
        "SELECT tr.cpuload, tr.update_time FROM test_rundat tr, tests t WHERE t.host=? AND tr.cpuload != -1  AND tr.test_id=t.id ORDER BY tr.update_time DESC LIMIT 1;")
        hostname))) res ))

(define (db:set-top-level-from-items dbstruct run-id testname)
  (let* ((summ  (db:get-state-status-summary dbstruct run-id testname))
	 (find  (lambda (state status)
		  (if (null? summ) 
		      #f
3661
3662
3663
3664
3665
3666
3667
3668
3669


3670
3671
3672
3673
3674
3675
3676
3677
3678
3679
3680


3681
3682
3683
3684
3685
3686
3687
3647
3648
3649
3650
3651
3652
3653


3654
3655
3656
3657
3658
3659
3660
3661
3662
3663
3664


3665
3666
3667
3668
3669
3670
3671
3672
3673







-
-
+
+









-
-
+
+







    ;; first look up the key values from the run selected by run-id
    (db:with-db
     dbstruct #f #f
     (lambda (dbdat db)
       (sqlite3:for-each-row 
	(lambda (a . b)
	  (set! keyvals (cons a b)))
	db
	(conc "SELECT " selstr " FROM runs WHERE id=? ORDER BY event_time DESC;") run-id)))
	(db:get-cache-stmth dbdat db
	(conc "SELECT " selstr " FROM runs WHERE id=? ORDER BY event_time DESC;")) run-id)))
    (if (not keyvals)
	'()
	(let ((prev-run-ids '()))
	  (db:with-db
	   dbstruct #f #f
	   (lambda (dbdat db)
	     (apply sqlite3:for-each-row
		    (lambda (id)
		      (set! prev-run-ids (cons id prev-run-ids)))
		    db
		    (conc "SELECT id FROM runs WHERE " qrystr " AND id != ?;") (append keyvals (list run-id)))))
		    (db:get-cache-stmth dbdat db
		    (conc "SELECT id FROM runs WHERE " qrystr " AND id != ?;")) (append keyvals (list run-id)))))
	  ;; collect all matching tests for the runs then
	  ;; extract the most recent test and return that.
	  (debug:print 4 *default-log-port* "selstr: " selstr ", qrystr: " qrystr ", keyvals: " keyvals 
		       ", previous run ids found: " prev-run-ids)
	  (if (null? prev-run-ids) '()  ;; no previous runs? return null
	      (let loop ((hed (car prev-run-ids))
			 (tal (cdr prev-run-ids)))
3751
3752
3753
3754
3755
3756
3757
3758
3759


3760
3761
3762
3763
3764
3765
3766
3737
3738
3739
3740
3741
3742
3743


3744
3745
3746
3747
3748
3749
3750
3751
3752







-
-
+
+







     dbstruct
     run-id
     #f
     (lambda (dbdat db)
       (sqlite3:for-each-row 
	(lambda (id itempath state status run_duration logf comment)
	  (set! res (cons (vector id itempath state status run_duration logf comment) res)))
	db
	"SELECT id,item_path,state,status,run_duration,final_logf,comment FROM tests WHERE testname=? AND item_path != '' AND run_id=?;" ;; BUG! WHY NO run_id?
	(db:get-cache-stmth dbdat db
	"SELECT id,item_path,state,status,run_duration,final_logf,comment FROM tests WHERE testname=? AND item_path != '' AND run_id=?;") ;; BUG! WHY NO run_id?
	test-name
	run-id)
       res))))

;;======================================================================
;; Tests meta data
;;======================================================================
3777
3778
3779
3780
3781
3782
3783
3784
3785


3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800


3801
3802
3803
3804
3805
3806
3807
3808
3809
3810


3811
3812
3813
3814
3815
3816
3817
3818


3819
3820
3821
3822
3823
3824
3825
3826
3827
3828


3829
3830
3831
3832
3833
3834
3835
3763
3764
3765
3766
3767
3768
3769


3770
3771
3772
3773
3774
3775
3776
3777
3778
3779
3780
3781
3782
3783
3784


3785
3786
3787
3788
3789
3790
3791
3792
3793
3794


3795
3796
3797
3798
3799
3800
3801
3802


3803
3804
3805
3806
3807
3808
3809
3810
3811
3812


3813
3814
3815
3816
3817
3818
3819
3820
3821







-
-
+
+













-
-
+
+








-
-
+
+






-
-
+
+








-
-
+
+







	  (let ((tags (string-split tags-in ",")))
	    (for-each
	     (lambda (tag)
	       (hash-table-set! res tag
				(delete-duplicates
				 (cons testname (hash-table-ref/default res tag '())))))
	     tags)))
	db
	"SELECT testname,tags FROM test_meta")
	(db:get-cache-stmth dbdat db
	"SELECT testname,tags FROM test_meta"))
       (hash-table->alist res)))))

;; read the record given a testname
(define (db:testmeta-get-record dbstruct testname)
  (let ((res   #f))
    (db:with-db
     dbstruct
     #f
     #f
     (lambda (dbdat db)
       (sqlite3:for-each-row
	(lambda (id testname author owner description reviewed iterated avg_runtime avg_disk tags jobgroup)
	  (set! res (vector id testname author owner description reviewed iterated avg_runtime avg_disk tags jobgroup)))
	db
	"SELECT id,testname,author,owner,description,reviewed,iterated,avg_runtime,avg_disk,tags,jobgroup FROM test_meta WHERE testname=?;"
	(db:get-cache-stmth dbdat db
	"SELECT id,testname,author,owner,description,reviewed,iterated,avg_runtime,avg_disk,tags,jobgroup FROM test_meta WHERE testname=?;")
	testname)
       res))))

;; create a new record for a given testname
(define (db:testmeta-add-record dbstruct testname)
  (db:with-db dbstruct #f #f 
	      (lambda (dbdat db)
		(sqlite3:execute 
		 db
		 "INSERT OR IGNORE INTO test_meta (testname,author,owner,description,reviewed,iterated,avg_runtime,avg_disk,tags) VALUES (?,'','','','','','','','');" testname))))
		 (db:get-cache-stmth dbdat db
		 "INSERT OR IGNORE INTO test_meta (testname,author,owner,description,reviewed,iterated,avg_runtime,avg_disk,tags) VALUES (?,'','','','','','','','');") testname))))

;; update one of the testmeta fields
(define (db:testmeta-update-field dbstruct testname field value)
  (db:with-db dbstruct #f #f 
	      (lambda (dbdat db)
		(sqlite3:execute 
		 db
		 (conc "UPDATE test_meta SET " field "=? WHERE testname=?;") value testname))))
		 (db:get-cache-stmth dbdat db
		 (conc "UPDATE test_meta SET " field "=? WHERE testname=?;")) value testname))))

(define (db:testmeta-get-all dbstruct)
  (db:with-db dbstruct #f #f 
	      (lambda (dbdat db)
		(let ((res '()))
		  (sqlite3:for-each-row
		   (lambda (a . b)
		     (set! res (cons (apply vector a b) res)))
		   db
		   "SELECT id,testname,author,owner,description,reviewed,iterated,avg_runtime,avg_disk,tags,jobgroup FROM test_meta;")
		   (db:get-cache-stmth dbdat db
		   "SELECT id,testname,author,owner,description,reviewed,iterated,avg_runtime,avg_disk,tags,jobgroup FROM test_meta;"))
		  res))))

;;======================================================================
;; M I S C   M A N A G E M E N T   I T E M S 
;;======================================================================

;; A routine to map itempaths using a itemmap

Modified dbfile.scm from [20e94c199d] to [ae6932a247].

99
100
101
102
103
104
105













106
107
108
109
110
111
112
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125







+
+
+
+
+
+
+
+
+
+
+
+
+







  (print-call-chain (current-error-port))
  (apply dbfile:print-err message)
  (dbfile:print-err
    ", error: "     ((condition-property-accessor 'exn 'message)   exn)
    ", arguments: " ((condition-property-accessor 'exn 'arguments) exn)
    ", location: "  ((condition-property-accessor 'exn 'location)  exn)
    ))

;; the stmt-cache is associated with a single db handle
;; so there is no need for the hoh stuff. 
(define (db:get-cache-stmth dbdat db stmt)
  (let* ((stmt-cache  (dbr:dbdat-stmt-cache dbdat))
	 ;; (stmth       (db:hoh-get stmt-cache db stmt))
	 (stmth       (hash-table-ref/default stmt-cache stmt #f))
	 )
    (or stmth
	(let* ((newstmth (sqlite3:prepare db stmt)))
	  ;; (db:hoh-set! stmt-cache db stmt newstmth)
	  (hash-table-set! stmt-cache stmt newstmth)
	  newstmth))))

(define (dbfile:run-id->key run-id)
  (or run-id 'main))

(define (db:safely-close-sqlite3-db db stmt-cache #!key (try-num 3))
  (if (<= try-num 0)
      #f
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410



411
412
413




414
415

416
417
418

419
420
421
422
423
424
425
426
427

428
429
430
431
432
433
434
400
401
402
403
404
405
406

407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433

434
435
436

437
438
439
440
441
442
443



444
445
446
447
448
449
450
451







-
















+
+
+



+
+
+
+

-
+


-
+






-
-
-
+







  (let* ((backupfname (conc fname"-"(current-process-id)".bak"))
	 (cmd (conc "cp "fname" "backupfname";mv "fname" "(conc fname ".delme;")
		    "cp "backupfname" "fname)))
    (dbfile:print-err "WARNING: attempting recovery of file "fname" by running commands:\n"
		      "  "cmd)
    (system cmd)))


(define (dbfile:open-no-sync-db dbpath)
  (if *no-sync-db*
      *no-sync-db*
      (begin
	(if (not (file-exists? dbpath))
	    (create-directory dbpath #t))
	(let* ((dbname    (conc dbpath "/no-sync.db"))
	       (db-exists (file-exists? dbname))
	       (init-proc (lambda (db)
			    (if (not db-exists)
				(begin
				  (sqlite3:execute db "CREATE TABLE IF NOT EXISTS no_sync_metadat (var TEXT,val TEXT, CONSTRAINT no_sync_metadat_constraint UNIQUE (var));"))
				)))
	       (db        (dbfile:cautious-open-database dbname init-proc 0 "WAL"))) ;; (sqlite3:open-database dbname)))
	  ;; (sqlite3:execute db "PRAGMA synchronous = 0;")
	  ;; (sqlite3:set-busy-handler! db (sqlite3:make-busy-timeout 136000)) ;; done in cautious-open-database
	  (set! *no-sync-set-prep* (sqlite3:prepare db "INSERT OR REPLACE INTO no_sync_metadat (var,val) VALUES (?,?);"))
	  (set! *no-sync-del-prep* (sqlite3:prepare db "DELETE FROM no_sync_metadat WHERE var=?;"))
	  (set! *no-sync-get-prep* (sqlite3:prepare db "SELECT val FROM no_sync_metadat WHERE var=?;"))
	  (set! *no-sync-db* db)
	  db))))

(define *no-sync-set-prep* #f)
(define *no-sync-del-prep* #f)
(define *no-sync-get-prep* #f)

(define (db:no-sync-set db var val)
  (sqlite3:execute db "INSERT OR REPLACE INTO no_sync_metadat (var,val) VALUES (?,?);" var val))
  (sqlite3:execute *no-sync-set-prep* var val))

(define (db:no-sync-del! db var)
  (sqlite3:execute db "DELETE FROM no_sync_metadat WHERE var=?;" var))
  (sqlite3:execute  *no-sync-del-prep* var))

(define (db:no-sync-get/default db var default)
  (let ((res default))
    (sqlite3:for-each-row
     (lambda (val)
       (set! res val))
     db
     "SELECT val FROM no_sync_metadat WHERE var=?;"
     var)
     *no-sync-get-prep* var)
    (if res
        (let ((newres (if (string? res)
			  (string->number res)
			  #f)))
          (if newres
              newres
              res))
996
997
998
999
1000
1001
1002

1003
1004
1005
1006
1007
1008
1009
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027







+







;; (db:with-db dbstruct run-id sqlite3:exec "select blah fgrom blaz;")
;; r/w is a flag to indicate if the db is modified by this query #t = yes, #f = no
;;
(define (db:with-db dbstruct run-id r/w proc . params)
  (assert dbstruct "FATAL: db:with-db called with dbstruct "#f)
  (assert (dbr:dbstruct? dbstruct) "FATAL: dbstruct is "dbstruct)
  (let* ((use-mutex (> *api-process-request-count* 25)) ;; risk of db corruption
	 (load-delay  (/ *api-process-request-count* 25))
	 (have-struct (dbr:dbstruct? dbstruct))
         (dbdat     (if have-struct                ;; this stuff just allows us to call with a db handle directly
			(db:open-db dbstruct run-id (dbfile:db-init-proc)) ;; (dbfile:get-subdb dbstruct run-id)
			#f))
	 (db        (if have-struct                ;; this stuff just allows us to call with a db handle directly
			(dbr:dbdat-dbh dbdat)
			dbstruct))
1017
1018
1019
1020
1021
1022
1023
1024

1025
1026


1027
1028



1029

1030
1031
1032
1033
1034
1035
1036
1035
1036
1037
1038
1039
1040
1041

1042
1043

1044
1045
1046
1047
1048
1049
1050

1051
1052
1053
1054
1055
1056
1057
1058







-
+

-
+
+


+
+
+
-
+







	 ) ;; was 25
    (assert (sqlite3:database? db) "FATAL: db:with-db, db is not a database, db="db", fname="fname)
    (if (file-exists? jfile)
	(begin
	  (dbfile:print-err "INFO: "jfile" exists, delaying to reduce database load")
	  (thread-sleep! 0.2)))
    (if (and use-mutex
	     (common:low-noise-print 120 "over-50-parallel-api-requests"))
	     (common:low-noise-print 120 "over-25-parallel-api-requests"))
	(dbfile:print-err *api-process-request-count* " parallel api requests being processed in process "
			  (current-process-id))) ;;  ", throttling access"))
			  (current-process-id)
			  ", throttling access for "load-delay" seconds."))
    (condition-case
	(begin
	  (if use-mutex
	      (begin
		(thread-sleep! load-delay) ;; time to slow everything down
	  (if use-mutex (mutex-lock! *db-with-db-mutex*))
		(mutex-lock! *db-with-db-mutex*)))
	  (let ((res (apply proc dbdat db params))) ;; the actual call is here.
	    (if use-mutex (mutex-unlock! *db-with-db-mutex*))
	    ;; (if (vector? dbstruct)(db:done-with dbstruct run-id r/w))
	    (if dbdat
		(dbfile:add-dbdat dbstruct run-id dbdat))
	    res))
      (exn (io-error)