Megatest

	 default
	 (begin
	   (debug:print-error 0 *default-log-port* " query " stmt " failed, params: " params ", error: " ((condition-property-accessor 'exn 'message) exn) ", exn=" exn)
	   (print-call-chain (current-error-port))
	   default)))
   (apply sqlite3:first-result db stmt params)))

(define (db:setup do-sync)
(define (db:setup)
  (assert *toppath* "FATAL: db:setup called before launch:setup has been run.")
  (let* ((tmpdir (common:make-tmpdir-name *toppath* "")))
    (if (not *dbstruct-dbs*)
	(dbfile:setup do-sync *toppath* tmpdir)
	(dbfile:setup (conc *toppath* "/.mtdb") tmpdir)
	*dbstruct-dbs*)))

;; moved from dbfile
;;
;; ADD run-id SUPPORT
;;
(define (db:create-all-triggers dbstruct)

;;      dbfiles)
;;     ;; WHY does the dbdat need to be added back?
;;     (if dbdat (dbfile:add-dbdat dbstruct #f dbdat))
;;     )
;;   #t)

(define (db:kill-servers)
  (let* ((tl (launch:setup)) ;; need this to initialize *toppath*
        (servdir (conc *toppath* "/.servinfo"))
        (servfiles (glob (conc servdir "/*:*.db")))
        (fmtstr  "~10a~22a~10a~25a~25a~8a\n")
        (dbfiles (append (glob (conc *toppath* "/.mtdb/main.db")) (glob (conc *toppath* "/.mtdb/?.db"))(glob (conc *toppath* "/.mtdb/??.db"))))
  (let* ((servers (server:choose-server *toppath* 'all-valid))) ;; (server:get-list *toppath*))
    (for-each
     (lambda (server)
       (handle-exceptions
           exn
         (begin 
        (ttdat (make-tt areapath: *toppath*))
     )
     (format #t fmtstr "DB" "host:port" "PID" "age" "last mod" "state")
     (for-each
        (lambda (dbfile)
          (let* (
            (dbfname (conc (pathname-file dbfile) ".db"))
            (sfiles   (tt:find-server *toppath* dbfname))
            )
            (for-each 
              (lambda (sfile)
                (let (
                  (sinfos (tt:get-server-info-sorted ttdat dbfname))
                  )
                  (for-each 
           (debug:print-info 0 *default-log-port*  "Unable to get host and/or port from " server ", exn=" exn)     
           #f)
                     (lambda (sinfo)
	 (match-let (((mod-time host port start-time server-id pid) server))
		    (if (and host pid)
			(tasks:kill-server host pid)))))
     servers)
    (delete-file* (common:get-sync-lock-filepath))))
                       (let* (
                         (db (list-ref sinfo 5))
                         (pid (list-ref sinfo 4))
                         (host (list-ref sinfo 0))
                         (port (list-ref sinfo 1))
                         (server-id (list-ref sinfo 3))
                         (age (seconds->hr-min-sec (- (current-seconds) (list-ref sinfo 2))))
                         (last-mod (seconds->string (list-ref sinfo 2)))
                         (killed (system (conc "ssh " host " kill " pid " > /dev/null")))
                         (dummy2 (sleep 1))
                         (state (if (> (system (conc "ssh " host " ps " pid " > /dev/null")) 0) "dead" "alive"))
                            )
                         (format #t fmtstr db (conc host ":" port) pid age last-mod state)
                         (system (conc "rm " sfile))
                       )
                     )
                     sinfos
                  )
                ) 
              )
              sfiles
            )
          )
       )
       dbfiles
     )
     ;; remove this db, because otherwise metadata contains records for old servers, and this causes a problem with db:no-sync-get-lock-with-id.
     (if (file-exists? (conc *toppath* "/.mtdb/no-sync.db"))
       (delete-file (conc *toppath* "/.mtdb/no-sync.db"))
     )
  )
)

;; options:
;;
;;  'killservers  - kills all servers
;;  'dejunk       - removes junk records
;;  'adj-testids  - move test-ids into correct ranges
;;  'old2new      - sync megatest.db to /tmp/.../megatest.db and /tmp/.../megatest_ref.db
;;  'new2old      - sync /tmp/.../megatest.db to megatest.db and /tmp/.../megatest_ref.db (and update data_synced)
;;  'closeall     - close all opened dbs
;;  'schema       - attempt to apply schema changes
;;  run-ids: '(1 2 3 ...) or #f (for all)
;;
(define (db:multi-db-sync dbstruct . options)
  (let* (;; (dbdat       (db:open-db dbstruct #f dbfile:db-init-proc))
	 (data-synced 0) ;; count of changed records
	 (tmp-area       (common:make-tmpdir-name *toppath* ""))
	 (old2new (member 'old2new options))
	 (dejunk (member 'dejunk options))
	 (killservers (member 'killservers options))
	 (src-area (if old2new *toppath* tmp-area))
	 (dest-area (if old2new tmp-area *toppath*))
	 (dest-area (if old2new tmp-area (conc *toppath* "/.mtdb")))
	 (dbfiles        (if old2new (glob (conc *toppath* "/.mtdb/*.db"))
			     (glob (conc tmp-area "/.mtdb/*.db"))))
			     (glob (conc tmp-area "/*.db"))))
	 (keys (db:get-keys dbstruct))
	 (sync-durations (make-hash-table)))

    ;; kill servers
    (if killservers (db:kill-servers))
    ;; (if killservers (db:kill-servers))
    
    (if (not dbfiles)
	(debug:print-error 0 *default-log-port* "no dbfiles found in " (conc *toppath* "/.mtdb"))
	(for-each
	 (lambda (srcfile)
	   (debug:print-info 3 *default-log-port* "file: " srcfile)
	   (let* ((fname    (conc (pathname-file srcfile) ".db"))
		  (basename (pathname-file srcfile))
		  (run-id   (if (string= basename "main") #f (string->number basename)))
		  (destfile (conc dest-area "/.mtdb/" fname))
		  (dest-directory  (conc dest-area "/.mtdb/"))
		  (destfile (conc dest-area "/" fname))
		  (dest-directory  dest-area)
		  (time1    (file-modification-time srcfile))
		  (time2    (if (file-exists? destfile)
				(begin
				  (debug:print-info 2 *default-log-port* "destfile " destfile " exists")
				  (file-modification-time destfile))
				(begin
				  (debug:print-info 0 *default-log-port* "Sync - I do not see file " destfile)

			       #t)
			      (changed ;; (and changed
			       #t)
			      ((and changed *time-to-exit*) ;; last sync
			       #t)
			      (else
			       #f))))
             
             (if (or dejunk do-cp)
               (if (or dejunk do-cp)
		 (let* ((start-time (current-milliseconds))
			;; subdb is misnamed - should be dbdat (I think...)
			(subdb    (dbfile:open-db dbstruct run-id dbfile:db-init-proc))
			;;        (or (dbfile:get-subdb dbstruct run-id)
			;;            (dbfile:init-subdb dbstruct run-id dbfile:db-init-proc)))
			(subdb    (or (dbfile:get-subdb dbstruct run-id) (dbfile:init-subdb dbstruct run-id dbfile:db-init-proc)))
			(dbdat    (or (dbfile:get-dbdat dbstruct run-id) (dbfile:open-db dbstruct run-id dbfile:db-init-proc)))
			(mtdb      (dbr:subdb-mtdbdat subdb))
			;;
			;; BUG: -mrw- I think this next line is wrong. run-id should be the path to .mtdb/<runid>.db
			;; 
			(tmpdb     (dbfile:open-db dbstruct run-id dbfile:db-init-proc)))
		   
                   (if dejunk
                     (begin
                       (debug:print 0 *default-log-port* "Cleaning tmp DB")
                       (db:clean-up run-id tmpdb)
                       (debug:print 0 *default-log-port* "Cleaning nfs DB")
                       (db:clean-up run-id mtdb)
                     )
                   )
		   (debug:print-info 2 *default-log-port* "delta syncing file: " srcfile ", time diff: " (- time1 time2) " seconds")
		   (if old2new
                       (begin
			 (if dejunk (db:clean-up run-id mtdb))
			 (db:sync-tables (db:sync-all-tables-list
					  dbstruct
					  (db:get-keys dbstruct))
					 #f mtdb tmpdb))
                       (begin
			 (if dejunk (db:clean-up run-id tmpdb))
			 (db:sync-tables (db:sync-all-tables-list dbstruct (db:get-keys dbstruct)) #f tmpdb mtdb)))
			 (db:sync-tables (db:sync-all-tables-list (db:get-keys dbstruct)) #f tmpdb mtdb)))
		   (hash-table-set! sync-durations (conc srcfile ".db") (- (current-milliseconds) start-time)))
		 (debug:print-info 2 *default-log-port* "skipping delta sync. " srcfile " is up to date"))))
	 dbfiles))
    data-synced))

;; Sync all changed db's
;;
(define (db:tmp->megatest.db-sync dbstruct run-id last-update)
  (let* ((subdbs (hash-table-values (dbr:dbstruct-subdbs dbstruct)))
	 (res    '()))
    (for-each
     (lambda (subdb)
       (let* ((mtdb   (dbr:subdb-mtdb subdb))
	      (tmpdb  (db:get-subdb dbstruct run-id))
	      (refndb (dbr:subdb-refndb subdb))
	      (newres (db:sync-tables (db:sync-all-tables-list dbstruct (db:get-keys dbstruct)) last-update tmpdb refndb mtdb)))
	      (newres (db:sync-tables (db:sync-all-tables-list (db:get-keys dbstruct)) last-update tmpdb refndb mtdb)))
	 ;; (stack-push! (dbr:subdb-dbstack subdb) tmpdb)
	 ;; BUG: verify this is really needed
	 (dbfile:add-dbdat dbstruct run-id tmpdb)
	 (set! res (cons newres res))))
     subdbs)
    res))

;;    a. If test dir exists, set the the test to state='UNKNOWN', Set the run to 'unknown'
;;    b. If test dir gone, delete the test record
;; 2. Look at run records
;;    a. If have tests that are not deleted, set state='unknown'
;;    b. ....
;;
(define (db:clean-up run-id dbdat)
  (debug:print 2 *default-log-port* "db:clean-up")


  (if run-id
    (begin
      (debug:print 0 *default-log-port* "Cleaning run DB " run-id)
    (db:clean-up-rundb dbdat)
    (db:clean-up-maindb dbdat)
      (db:clean-up-rundb dbdat run-id)
    )
    (begin
      (debug:print 0 *default-log-port* "Cleaning main DB ")
      (db:clean-up-maindb dbdat)
    )
  )
)


;; Clean out old junk and vacuum the database
;;
;; Ultimately do something like this:
;;
;; 1. Look at test records either deleted or part of deleted run:
;;    a. If test dir exists, set the the test to state='UNKNOWN', Set the run to 'unknown'
;;    b. If test dir gone, delete the test record
;; 2. Look at run records
;;    a. If have tests that are not deleted, set state='unknown'
;;    b. ....
;;
(define (db:clean-up-rundb dbdat)
(define (db:clean-up-rundb dbdat run-id)
  ;; (debug:print 0 *default-log-port* "WARNING: db clean up not fully ported to v1.60, cleanup action will be on megatest.db")
  (let* ((db         (dbr:dbdat-dbh dbdat))
	 (count-stmt (sqlite3:prepare db "SELECT (SELECT count(id) FROM tests);"))
	 (test-count-stmt (sqlite3:prepare db "SELECT (SELECT count(id) FROM tests);"))
       	 (step-count-stmt (sqlite3:prepare db "SELECT (SELECT count(id) FROM test_steps);"))
	(statements
	 (map (lambda (stmt)
		(sqlite3:prepare db stmt))
	      (list
	       ;; delete all tests that belong to runs that are 'deleted'
	       ;; (conc "DELETE FROM tests WHERE run_id NOT IN (" (string-intersperse (map conc valid-runs) ",") ");")
	       ;; delete all tests that are 'DELETED'
	       "DELETE FROM tests WHERE state='DELETED';"
	       "DELETE FROM tests WHERE state='DELETED';"
               "DELETE FROM test_steps WHERE status = 'DELETED';"
               "DELETE FROM tests WHERE run_id IN (SELECT id FROM runs WHERE state = 'deleted');"
	       ))))
    ;; (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))
			     count-stmt)
			       (debug:print-info 0 *default-log-port* "Test records count before clean: " tot))
			     test-count-stmt)
       (sqlite3:for-each-row (lambda (tot)
			       (debug:print-info 0 *default-log-port* "Test_step records count before clean: " tot))
			     step-count-stmt)
       (map sqlite3:execute statements)
       (sqlite3:for-each-row (lambda (tot)
			       (debug:print-info 0 *default-log-port* "Records count after  clean: " tot))
			     count-stmt)))
			       (debug:print-info 0 *default-log-port* "Test records count after clean: " tot))
			     test-count-stmt)
       (sqlite3:for-each-row (lambda (tot)
			       (debug:print-info 0 *default-log-port* "Test_step records count after clean: " tot))
			     step-count-stmt)))
    (map sqlite3:finalize! statements)
    (sqlite3:finalize! count-stmt)
    ;; (db:find-and-mark-incomplete db)
    (sqlite3:finalize! test-count-stmt)
    (sqlite3:finalize! step-count-stmt)
    ;; (db:delay-if-busy dbdat)
    (sqlite3:execute db "VACUUM;")))

;; Clean out old junk and vacuum the database
;;
;; Ultimately do something like this:
;;
;; 1. Look at test records either deleted or part of deleted run:

       db
       "SELECT id FROM runs WHERE state='deleted';")
    ;; (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))
			       (debug:print-info 0 *default-log-port* "Run records count before clean: " tot))
			     count-stmt)
       (map sqlite3:execute statements)
       (sqlite3:for-each-row (lambda (tot)
			       (debug:print-info 0 *default-log-port* "Records count after  clean: " tot))
			       (debug:print-info 0 *default-log-port* "Run records count after clean: " tot))
			     count-stmt)))
    (map sqlite3:finalize! statements)
    (sqlite3:finalize! count-stmt)
    ;; (db:find-and-mark-incomplete db)
    ;; (db:delay-if-busy dbdat)
    (sqlite3:execute db "VACUUM;")
    dead-runs))

  (let ((runs (db:simple-get-runs dbstruct runname #f #f target #f))) ;; runpatt count offset target last-update
    (if (null? runs)
	#f
	(simple-run-id (car runs)))))

;; called with run-id=#f so will operate on main.db
;;
(define (db:insert-run dbstruct target runname run-meta)
(define (db:insert-run dbstruct run-id target runname run-meta)
  (let* ((keys (db:get-keys dbstruct))
     	 (runs (db:simple-get-runs dbstruct runname #f #f target #f))) ;; runpatt count offset target last-update
    ;; need to insert run based on target and runname
    (let* ((targvals (string-split target "/"))
	   (keystr   (string-intersperse keys ","))
	   (key?str  (string-intersperse (make-list (length targvals) "?") ","))
	   (qrystr   (conc "INSERT INTO runs (runname,"keystr") VALUES (?,"key?str")"))
	   (qrystr   (conc "INSERT INTO runs (id,runname,"keystr") VALUES (?,?,"key?str")"))
	   (get-var  (lambda (db qrystr)
		       (let* ((res #f))
			 (sqlite3:for-each-row
			  (lambda row
			    (set res (car row)))
			  db qrystr runname)
			  db qrystr run-id runname)
			 res))))
      (if (null? runs)
        (begin
	  (db:create-initial-run-record dbstruct runname target))
      (let* ((run-id (db:get-run-id dbstruct runname target)))
	(db:with-db
	  (db:create-initial-run-record dbstruct run-id runname target)
        )
      )
      (let* ()
        ;;(debug:print 0 *default-log-port* "db:insert-run: Calling db:with-db to update the run record")
        (debug:print 0 *default-log-port* "db:insert-run: runid = " run-id)
#; (db:with-db
	 dbstruct
	 #f #t
	 (lambda (dbdat db)
           (debug:print 0 *default-log-port* "In the lambda proc for " dbdat " " db)
	   (for-each
	    (lambda (keyval)
              (debug:print 0 *default-log-port* "In the lambda proc for " keyval)
	      (let* ((fieldname (car keyval))
		     (getqry    (conc "SELECT "fieldname" FROM runs WHERE id=?;"))
		     (setqry    (conc "UPDATE runs SET "fieldname"=? WHERE id=?;"))
		     (val       (cdr keyval))
		     (valnum    (if (number? val)
				    val
				    (if (string? val)
					(string->number val)
					#f))))
                (debug:print 0 *default-log-port* "fieldname " fieldname " val " val " valnum " valnum)
		(if (not (member fieldname (cons "runname" keys))) ;; don't attempt to tweak these
		    (let* ((curr-val (get-var db getqry))
			   (have-it  (or (equal? curr-val val)
					 (equal? curr-val valnum))))
                      (debug:print 0 *default-log-port* "have-it = " have-it)
		      (if (not have-it)
                        (begin
                          (debug:print 0 *default-log-port* "Do sqlite3:execute")
			  (sqlite3:execute db setqry (or valnum val) run-id))))))
			  ;; (sqlite3:execute db setqry (or valnum val) run-id)
                        )
                      )
                    )
                 )
                (debug:print 0 *default-log-port* "Done with update")
              )
              (debug:print 0 *default-log-port* "next keyval")
            )
	    run-meta)))
	run-id))))
  
(define (db:create-initial-run-record dbstruct runname target)	  
(define (db:create-initial-run-record dbstruct run-id runname target)	  
  (let* ((keys     (db:get-keys dbstruct))
     	 (targvals (string-split target "/"))
	 (keystr   (string-intersperse keys ","))
	 (key?str  (string-intersperse (make-list (length targvals) "?") ","))
	 (qrystr   (conc "INSERT INTO runs (runname,"keystr") VALUES (?,"key?str")")))
	 (key?str  (string-intersperse (make-list (length targvals) "?") ",")) ;; a string with the same length as targvals, where each element is "?" and interspersed with commas.
	 (qrystr   (conc "INSERT INTO runs (id,runname,"keystr") VALUES (?,?,"key?str")")))
    (debug:print 0 *default-log-port* "db:create-initial-run-record")
    (debug:print 0 *default-log-port* "qrystr = " qrystr)

    (db:with-db
     dbstruct #f #t
     dbstruct #f #t ;; run-id writable
     (lambda (dbdat db)
       (debug:print 0 *default-log-port* "lambda proc: dbdat: " dbdat " db: " db) 
       (apply sqlite3:execute db qrystr runname targvals)))))
       (apply sqlite3:execute db qrystr run-id runname targvals)))))

(define (db:insert-test dbstruct run-id test-rec)
  (let* ((testname  (alist-ref "testname" test-rec equal?))
	 (item-path (alist-ref "item_path" test-rec equal?))
	 (id        (db:get-test-id dbstruct run-id testname item-path))
	 (fieldvals (filter (lambda (x)(not (member (car x) '("id" "last_update")))) test-rec))
	 (setqry    (conc "UPDATE tests SET "(string-intersperse
					      (map (lambda (dat)
						     (conc (car dat)"=?"))
						   fieldvals)
					      ",")" WHERE id=?;"))
	 (insqry   (conc "INSERT INTO tests ("(string-intersperse (map (lambda (x) (car x)) fieldvals) ",")
			 ") VALUES ("(string-intersperse (make-list (length fieldvals) "?") ",")");")))
    (debug:print 0 *default-log-port* "id: "id"\nset: "setqry"\ninsqry: "insqry)
    ;; (debug:print 0 *default-log-port* "id: "id"\nset: "setqry"\ninsqry: "insqry)
    (db:with-db
     dbstruct
     run-id #t
     (lambda (dbdat db)
       (if id
	   (apply sqlite3:execute db setqry (append (map cdr fieldvals) (list id)))
	   (apply sqlite3:execute db insqry (map cdr fieldvals)))))))

		   '()
		   db 
		   qry
		   run-id
		   (or last-update 0))))))

(define (db:get-testinfo-state-status dbstruct run-id test-id)
  (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=?;" 
		   test-id run-id)))
    res))
  (db:with-db
   dbstruct run-id #f
   (lambda (dbdat db)
     (let* ((res   #f)
	    (stmth (db:get-cache-stmth dbdat db "SELECT run_id,testname,item_path,state,status FROM tests WHERE id=? and run_id=?;")))
       (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=?;"
	stmth
	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)
  (db:get-tests-for-run dbstruct run-id testpatt states statuses #f #f not-in #f #f "id,run_id,testname,state,status,event_time,item_path" 0 #f))

  (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
(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
    (db:with-db
     dbstruct
	 (mtdbfile (dbmod:run-id->full-dbfname dbstruct run-id))
     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)))))))
	 (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)

;; 
(define (db:get-test-state-status-by-id dbstruct run-id test-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (dbdat db)
     (let ((res   (cons #f #f)))
;;	   (stmth (db:get-cache-stmth dbdat db "SELECT state,status FROM tests WHERE id=?;")))
       (sqlite3:for-each-row ;; attemptnum added to hold pid of top process (not Megatest) controlling a test
	(lambda (state status)
	  (cons state status))
	db
	"SELECT state,status FROM tests WHERE id=? AND run_id=?;" ;; stmth try not compiling this one - yes, this fixed the bind issue
	test-id run-id)
     (let ((res   (cons #f #f))
	   (stmth (db:get-cache-stmth dbdat db "SELECT state,status FROM tests WHERE id=? AND run_id=?;")))
	  (sqlite3:for-each-row ;; attemptnum added to hold pid of top process (not Megatest) controlling a test
	   (lambda (state status)
	     (cons state status))
	   ;; db
	   stmth ;;"SELECT state,status FROM tests WHERE id=? AND run_id=?;" ;; stmth try not compiling this one - yes, this fixed the bind issue
	   test-id run-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)
  (db:with-db

				(delete-duplicates
				 (cons testname (hash-table-ref/default res tag '())))))
	     tags)))
	db
	"SELECT testname,tags FROM test_meta")
       (hash-table->alist res)))))

;; testmeta doesn't change, we can cache it for up too an hour

(define *db:testmeta-cache* (make-hash-table))
(define *db:testmeta-last-update* 0)

;; read the record given a testname
(define (db:testmeta-get-record dbstruct testname)
  (if (and (< (- (current-seconds) *db:testmeta-last-update*) 600)
	   (hash-table-exists? *db:testmeta-cache* testname))
      (hash-table-ref *db:testmeta-cache* 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=?;"
	testname)
       res))))
      (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=?;"
	    testname)))
	(hash-table-set! *db:testmeta-cache* testname res)
	(set! *db:testmeta-last-update* (current-seconds))
	res)))

;; create a new record for a given testname
(define (db:testmeta-add-record dbstruct testname)
  (db:with-db dbstruct #f #t
	      (lambda (dbdat db)
		(sqlite3:execute 
		 db

		     (if (not *time-to-exit*) (loop))))
	       
;; 	       ;; time to exit, close the no-sync db here
;; 	       (db:no-sync-close-db no-sync-db stmt-cache)
	       (if (common:low-noise-print 30)
		   (debug:print-info 0 *default-log-port* "Exiting watchdog timer, *time-to-exit* = " *time-to-exit*" pid="(current-process-id) )))) 
))


(define (std-exit-procedure)
  ;;(common:telemetry-log-close)
  (on-exit (lambda () 0)) ;; why is this here?
  ;;(debug:print-info 13 *default-log-port* "std-exit-procedure called; *time-to-exit*="*time-to-exit*)
  (let ((no-hurry  (if *time-to-exit* ;; hurry up
		       #f