Megatest

(declare (uses csv-xml))
(declare (uses keysmod))
(declare (uses mtmod))
(declare (uses pkts))
(declare (uses dbi))

(module dbmod
	   (
dbr:dbstruct-get-dbdat
dbr:dbstruct-dbdat-put!
db:run-id->first-num
db:run-id->path
db:dbname->path
db:run-id->dbname
db:get-dbdat
db:get-inmem
db:get-ddb
db:open-dbdat
db:open-run-db
db:open-inmem-db
db:setup
db:get-main-lock
db:with-lock-db
db:get-iam-server-lock
db:get-locker
db:take-lock
db:release-lock
db:general-sqlite-error-dump
db:first-result-default
db:generic-error-printout
db:with-db
db:set-sync
db:get-last-update-time
db:sync-inmem->disk
db:sync-main-list
db:sync-all-tables-list
db:move-and-recreate-db
db:repair-db
db:sync-one-table
db:sync-tables
db:patch-schema-rundb
db:patch-schema-maindb
db:adj-target
db:get-access-mode
db:dispatch-query
db:create-all-triggers
db:create-triggers
db:drop-all-triggers
db:is-trigger-dropped
db:drop-triggers
db:initialize-db
db:archive-get-allocations
db:archive-register-disk
db:archive-register-block-name
db:test-set-archive-block-id
db:test-get-archive-block-info
open-logging-db
db:log-local-event
db:log-event
db:have-incompletes?
db:get-status-from-final-status-file
db:find-and-mark-incomplete
db:top-test-set-per-pf-counts
db:clean-up
db:clean-up-rundb
db:get-var
db:inc-var
db:dec-var
db:set-var
db:add-var
db:del-var
db:open-no-sync-db
db:no-sync-db
db:no-sync-set
db:no-sync-del!
db:no-sync-get/default
db:no-sync-get-lock
db:get-keys
db:get-index-by-header
db:get-value-by-header
db:get-header
db:get-rows
db:get-run-times
db:get-run-name-from-id
db:get-run-key-val
runs:get-std-run-fields
db:register-run
db:insert-run
db:get-runs
db:simple-get-runs
db:get-changed-run-ids
db:get-targets
db:get-num-runs
db:get-runs-cnt-by-patt
db:get-raw-run-stats
db:update-run-stats
db:get-main-run-stats
db:print-current-query-stats
db:get-all-run-ids
db:get-run-stats
db:get-runs-by-patt
db:get-run-info
db:set-comment-for-run
db:delete-run
db:update-run-event_time
db:lock/unlock-run
db:set-run-status
db:set-run-state-status
db:get-run-status
db:get-run-state
db:get-key-val-pairs
db:get-key-vals
db:get-target
db:get-prev-run-ids
db:get-tests-for-run
db:test-short-record->norm
db:get-tests-for-run-state-status
db:get-testinfo-state-status
db:get-tests-for-run-mindata
db:get-tests-for-runs
db:delete-test-records
db:delete-old-deleted-test-records
db:set-tests-state-status
db:test-set-state-status
db:get-count-tests-running
db:get-count-tests-actually-running
db:get-count-tests-running-for-run-id
db:get-count-tests-running-for-testname
db:get-not-completed-cnt
db:get-count-tests-running-in-jobgroup
db:estimated-tests-remaining
db:get-test-id
db:test-set-top-process-pid
db:test-get-top-process-pid
db:field->number
db:update-tesdata-on-repilcate-db
db:get-all-tests-info-by-run-id
db:replace-test-records
db:get-test-info-by-id
db:get-test-info-by-ids
db:get-test-info
db:test-get-rundir-from-test-id
db:get-test-times
db:teststep-set-status!
db:delete-steps-for-test!
db:get-steps-for-test
db:get-steps-data
db:get-data-info-by-id
db:test-data-rollup
db:logpro-dat->csv
db:csv->test-data
db:read-test-data
db:read-test-data-varpatt
db:get-run-ids-matching-target
db:test-get-paths-matching-keynames-target-new
db:test-toplevel-num-items
db:obj->string
db:string->obj
db:set-state-status-and-roll-up-items
db:roll-up-rules
db:set-state-status-and-roll-up-run
db:get-all-state-status-counts-for-run
db:get-all-state-status-counts-for-test
db:test-get-logfile-info
db:lookup-query
db:login
db:general-call
db:get-state-status-summary
db:get-latest-host-load
db:set-top-level-from-items
db:get-matching-previous-test-run-records
db:test-get-records-for-index-file
db:get-tests-tags
db:testmeta-get-record
db:testmeta-add-record
db:testmeta-update-field
db:testmeta-get-all
db:compare-itempaths
db:convert-test-itempath
db:multi-pattern-apply
db:get-prereqs-not-met
db:get-run-record-ids
db:get-changed-record-ids
tdb:read-test-data
tdb:get-prev-tol-for-test
tdb:step-get-time-as-string
tdb:get-steps-table
tdb:get-steps-table-list
tdb:get-compressed-steps
mt:run-trigger
mt:process-triggers
mt:lazy-read-test-config
mt:get-run-stats
server:reply
common:with-queue-db
common:load-pkts-to-db
db:hoh-set!
db:hoh-get
db:get-cache-stmth
db:register-server
db:deregister-server
db:get-server-info
db:get-count-servers
db:get-steps-info-by-id

make-dbr:dbdat
dbr:dbdat-db
dbr:dbdat-inmem
dbr:dbdat-last-sync
dbr:dbdat-last-write
dbr:dbdat-run-id
dbr:dbdat-fname
dbr:dbdat-db-set!
dbr:dbdat-inmem-set!
dbr:dbdat-last-sync-set!
dbr:dbdat-last-write-set!
dbr:dbdat-run-id-set!
dbr:dbdat-fname-set!

make-dbr:dbstruct
dbr:dbstruct-mtdb
dbr:dbstruct-dbdats
dbr:dbstruct-read-only
dbr:dbstruct-stmt-cache
dbr:dbstruct-mtdb-set!
dbr:dbstruct-dbdats-set!
dbr:dbstruct-read-only-set!
dbr:dbstruct-stmt-cache-set!

make-simple-run
simple-run-target
simple-run-id
simple-run-runname
simple-run-state
simple-run-status
simple-run-owner
simple-run-event_time
simple-run-target-set!
simple-run-id-set!
simple-run-runname-set!
simple-run-state-set!
simple-run-status-set!
simple-run-owner-set!
simple-run-event_time-set!

;; fix naming on these ones
db:test-record-fields
db:test-record-qry-selector
)

(import scheme
	(prefix sqlite3 sqlite3:)
	chicken.base
	chicken.condition
	chicken.eval
	chicken.file
	chicken.file.posix

	chicken.time
	chicken.time.posix
	system-information
	
	(prefix base64 base64:)
	csv-xml
	directory-utils
	format
	matchable
	regex
	s11n
	srfi-1
	srfi-13
	srfi-18
	srfi-69

  (db          #f)    ;; should rename this to oddb for on disk db
  (inmem       #f)
  (last-sync   0)
  (last-write  (current-seconds))
  (run-id      #f)
  (fname       #f))
  
;; Returns the dbdat for a particular dbfile inside the area
;;
(define (dbr:dbstruct-get-dbdat dbstruct dbfile)
  (hash-table-ref/default (dbr:dbstruct-dbdats dbstruct) dbfile #f))

(define (dbr:dbstruct-dbdat-put! dbstruct dbfile db)
  (hash-table-set! (dbr:dbstruct-dbdats dbstruct) dbfile db))

(define (db:run-id->first-num run-id)
  (let* ((s (number->string run-id))
	 (l (string-length s)))
    (substring s (- l 1) l)))

;; 1234 => 4/1234.db

;;    if run-id => get run specific db
;;    if #f     => get main.db
;;    if db already open - return inmem
;;    if db not open, open inmem, rundb and sync then return inmem
;;    inuse gets set automatically for rundb's
;;
(define (db:get-dbdat dbstruct apath dbfile)
  (let ((dbdat (dbr:dbstruct-get-dbdat dbstruct dbfile)))
    (if dbdat
	dbdat

	(let* ((newdbdat (db:open-dbdat apath dbfile db:initialize-db)))
	  (dbr:dbstruct-dbdat-put! dbstruct dbfile newdbdat)
	  newdbdat))))

;; get the inmem db for actual db operations
;;
(define (db:get-inmem dbstruct apath dbfile)
  (dbr:dbdat-inmem (db:get-dbdat dbstruct apath dbfile)))

;; get the handle for the on-disk db
;;
(define (db:get-ddb dbstruct apath dbfile)
  (dbr:dbdat-db (db:get-dbdat dbstruct apath dbfile)))

;; open or create the disk db file
;; create and fill the inmemory db
;; assemble into dbr:dbdat struct and return
;; 
(define (db:open-dbdat apath dbfile dbinit-proc)

  (let* ((db       (db:open-run-db dbfile dbinit-proc))
	 ;; (inmem    (db:open-inmem-db dbinit-proc))
	 (dbdat    (make-dbr:dbdat
		    db:     #f ;; db
		    inmem:  db ;; inmem
		    ;; run-id: run-id  ;; no can do, there are many run-id values that point to single db
		    fname:  dbfile)))
    ;; now sync the disk file data into the inmemory db
    ;; (db:sync-tables (db:sync-all-tables-list) '("last_update" . 0) db inmem)
    ;; (sqlite3:finalize! db) ;; open and close every sync
    dbdat))
;; (define (db:open-dbdat apath dbfile dbinit-proc)
;;   (let* ((db       (db:open-run-db dbfile dbinit-proc))
;; 	 (inmem    (db:open-inmem-db dbinit-proc))
;; 	 (dbdat    (make-dbr:dbdat
;; 		    db:     #f ;; db
;; 		    inmem:  inmem
;; 		    ;; run-id: run-id  ;; no can do, there are many run-id values that point to single db
;; 		    fname:  dbfile)))
;;     ;; now sync the disk file data into the inmemory db
;;     (db:sync-tables (db:sync-all-tables-list) '("last_update" . 0) db inmem)
;;     (sqlite3:finalize! db) ;; open and close every sync
;;     dbdat))

;; open the disk database file
;; NOTE: May need to add locking to file create process here
;; returns an sqlite3 database handle
;;
(define (db:open-run-db dbfile dbinit-proc)
  (let* ((parent-dir (pathname-directory dbfile)))
    (if (not (directory-exists? parent-dir))
	(create-directory parent-dir #t))
    (let* ((exists  (file-exists? dbfile))
	   (db      (sqlite3:open-database dbfile))
	   (handler (sqlite3:make-busy-timeout 3600)))
      (sqlite3:set-busy-handler! db handler)
      ;; (db:set-sync db) ;; we don't mind that this is slow?
      (if (not exists)
	  (dbinit-proc db))
      db)))
    
;; open and initialize the inmem db
;; NOTE: Does NOT sync in the data from the disk db
;;
(define (db:open-inmem-db dbinit-proc)
  (let* ((db      (sqlite3:open-database ":memory:"))
	 (handler (sqlite3:make-busy-timeout 3600)))
    (sqlite3:set-busy-handler! db handler)
    (dbinit-proc db) ;; NOTE: inmem must always be initialized (db:initialize-db db)
    db))

;; ;; for debugging we have a local mode. these routines support that mode
;; (define *dbcache* (make-hash-table))
;; 
;; (define (db:cache-get-dbstruct rid apath)
;;   (let* ((dbname (db:run-id->dbname rid))
;; 	 (dbfile (db:dbname->path apath dbname)))
;;     (or (hash-table-ref/default *dbcache* dbfile #f)
;; 	(let* ((dbstruct (db:setup dbfile))) ;; (db:open-dbdat apath dbfile db:initialize-db)))
;; 	  (hash-table-set! *dbcache* dbfile dbstruct)
;; 	  dbstruct))))
;; 
;; (define (db:finalize-all-cache-dbstruct)
;;   #f)
;; 	  

;; get and initalize dbstruct for a given run-id
;;
;;  - uses db:initialize-db to create the schema
;;
;; Make the dbstruct, call for main db at least once
;; sync disk db to inmem
;;
;; called in http-transport and replicated in rmt.scm for *local* access. 
;;
(define (db:setup db-file) ;; run-id)
  (assert *toppath* "FATAL: db:setup called before toppath is available.")
  (let* ((dbstruct (or *dbstruct-db* (make-dbr:dbstruct))))

    (db:get-dbdat dbstruct *toppath* db-file)
    (if (not *dbstruct-db*)(set! *dbstruct-db* dbstruct))
    dbstruct))

;;======================================================================
;; setting/getting a lock on the db for only one server per db
;;

  (db:with-lock-db dbfile
		(lambda (dbh dbfile)
		  (db:get-iam-server-lock dbh dbfile))))

(define (db:with-lock-db dbfile proc)
  (let* ((dbh (db:open-run-db dbfile db:initialize-db))
	 (res (proc dbh dbfile)))
    ;; (sqlite3:finalize! dbh)
    res))

;; called before db is open?
;;
(define (db:get-iam-server-lock dbh dbfname)
  (sqlite3:with-transaction
   dbh

;; ;;     (mutex-unlock! *db-multi-sync-mutex*)
;; ;;     (stack-push! (dbr:dbstruct-dbstack dbstruct) tmpdb)))

;; NOTE: touched logic is disabled/not done
;; sync run to disk if touched
;;
(define (db:sync-inmem->disk dbstruct apath dbfile #!key (force-sync #f))
  #f) ;; disabled
;;   (let* ((dbdat       (db:get-dbdat dbstruct apath dbfile))
;; 	 (dbfullname  (conc apath "/" dbfile))
;; 	 (db          (db:open-run-db dbfullname db:initialize-db)) ;; (dbr:dbdat-db dbdat))
;; 	 (inmem       (dbr:dbdat-inmem dbdat))
;; 	 (start-t     (current-seconds))
;; 	 (last-update (dbr:dbdat-last-write dbdat))
;; 	 (last-sync   (dbr:dbdat-last-sync dbdat)))
;;     (debug:print-info 0 *default-log-port* "Syncing for dbfile: "dbfile", last-update: "last-update", last-sync: "last-sync)
;;     (mutex-lock! *db-multi-sync-mutex*)
;;     (let* ((update_info (cons "last_update" (if force-sync 0 last-update))) ;;  "last_update"))
;;     	   (need-sync   (or force-sync (>= last-update last-sync))))
;;       (if need-sync
;; 	  (begin
;; 	    (db:sync-tables (db:sync-all-tables-list) update_info inmem db)
;; 	    (dbr:dbdat-last-sync-set! dbdat start-t))
;; 	  (debug:print 0 *default-log-port* "Skipping sync as nothing touched.")))
;;     (sqlite3:finalize! db)
;;     (mutex-unlock! *db-multi-sync-mutex*)))

;; TODO: Add final sync to this
;;
#;(define (db:safely-close-sqlite3-db db stmt-cache #!key (try-num 3))
  (if (<= try-num 0)
      #f
      (handle-exceptions
	  exn
	(begin
	  (print "Attempt to safely close sqlite3 db failed. Trying again. exn=" exn)
	  (thread-sleep! 3)
	  (sqlite3:interrupt! db)
	  (db:safely-close-sqlite3-db db stmt-cache try-num: (- try-num 1)))
	(if (sqlite3:database? db)
	    (let* ((stmts (and stmt-cache (hash-table-ref/default stmt-cache db #f))))
	      (if stmts (map sqlite3:finalize! (hash-table-values stmts)))
	      (sqlite3:finalize! db)
	      #t)
	    #f))))

;; close all opened run-id dbs
#;(define (db:close-all dbstruct)
  (assert (dbr:dbstruct? dbstruct) "FATAL: db:close-all called with dbstruct not set up.")
  (handle-exceptions
   exn
   (begin
     (debug:print 0 *default-log-port* "WARNING: Finalizing failed, "  ((condition-property-accessor 'exn 'message) exn) ", note - exn=" exn)
     (print-call-chain *default-log-port*))
   ;; (db:sync-touched dbstruct 0 force-sync: #t) ;; NO. Do not do this here. Instead we rely on a server to be started when there are writes, even if the server itself is not going to be used as a server.

	   (sqlite3:execute "DELETE FROM servers WHERE state LIKE 'defunct%';"))
	  (else
	   (sqlite3:execute db "vacuum;")))
	 
	 (sqlite3:finalize! db)
	 #t))))))

;; last-update is *always* a pair ( fieldname|#f . last-update-seconds|#f)
(define (db:sync-one-table fromdb todb tabledat last-update numrecs)
  (assert (pair? last-update) "FATAL: last-update must always be a pair.")
  (let* ((tablename        (car tabledat))
	 (fields           (cdr tabledat))
	 (has-last-update  (member "last_update" fields))
	 (last-update-field (or (car last-update)
				(if has-last-update
				    "last_update"










				    #f)))


	 (has-field         (member last-update-field fields))
	 (last-update-value (cdr last-update))

	 (use-last-update   (and has-field last-update-field last-update-value))




	 (num-fields (length fields))
	 (field->num (make-hash-table))
	 (num->field (apply vector (map car fields))) ;; BBHERE
	 (full-sel   (conc "SELECT " (string-intersperse (map car fields) ",") 
			   " FROM " tablename (if use-last-update ;; apply last-update criteria
						  (conc " WHERE " last-update-field " >= " last-update-value)
						  "")

			   (not (equal? (vector-ref fromrow i)(vector-ref curr i))))
		       (set! same #f))
		   (if (and same
			    (< i (- num-fields 1)))
		       (loop (+ i 1))))
		 (if (not same)
		     (begin
		       (debug:print 0 *default-log-port* "applying data "fromrow"to table "tablename", numrecs="numrecs)
		       (apply sqlite3:execute stmth (vector->list fromrow))
		       (hash-table-set! numrecs tablename (+ 1 (hash-table-ref/default numrecs tablename 0)))))))
	     fromdat-lst))))
       fromdats)
      (sqlite3:finalize! stmth)
      (if (member "last_update" field-names)
	  (db:create-trigger db tablename)))))

;; tbls is ( ("tablename" ( "field1" [#f|proc1] ) ( "field2" [#f|proc2] ) .... ) )
;; db's are sqlite3 handles
;;
;; if last-update specified ("field-name" . time-in-seconds)
;;    then sync only records where field-name >= time-in-seconds
;;    IFF field-name exists
;;
(define (db:sync-tables tbls last-update fromdb todb)
  (assert (pair? last-update) "FATAL: last-update must always be a pair")
  ;; NOTE: I'm moving all the checking OUT of this routine. Check for read/write access, existance, etc
  ;;       BEFORE calling this sync
  
  (let ((stmts       (make-hash-table)) ;; table-field => stmt
	(all-stmts   '())              ;; ( ( stmt1 value1 ) ( stml2 value2 ))
	(numrecs     (make-hash-table))
	(start-time  (current-milliseconds))

	   (list "update_test_data_trigger"  "CREATE TRIGGER  IF NOT EXISTS update_test_data_trigger AFTER UPDATE ON test_data
                             FOR EACH ROW
                               BEGIN 
                                 UPDATE test_data SET last_update=(strftime('%s','now'))
                                   WHERE id=old.id;
                               END;" )))

(define (db:create-all-triggers dbstruct run-id)
(db:with-db
   dbstruct run-id #f
   (lambda (db)
(db:create-triggers db))))
; "SELECT count(id) FROM tests WHERE state in ('RUNNING','LAUNCHED','REMOTEHOSTSTART') AND run_id=
(define (db:create-triggers db)
    (for-each (lambda (key)
              (sqlite3:execute db (cadr key)))
          db:trigger-list))

(define (db:drop-all-triggers dbstruct run-id)
  (db:with-db
   dbstruct run-id #f
   (lambda (db)
     (db:drop-triggers db))))

(define (db:is-trigger-dropped db tbl-name)
  (let* ((trigger-name (if (equal? tbl-name "test_steps")
			   "update_teststeps_trigger" 
                           (conc "update_" tbl-name "_trigger")))

	 (toplevels   '())
	 (deadtime-str (configf:lookup *configdat* "setup" "deadtime"))
	 (deadtime     (if (and deadtime-str
				(string->number deadtime-str))
			   (string->number deadtime-str)
			   72000))) ;; twenty hours
    (db:with-db
     dbstruct run-id #f
     (lambda (db)
       (if (number? ovr-deadtime)(set! deadtime ovr-deadtime))
       
       ;; 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)

         (running-deadtime-default (+ server-start-allowance (* 2 launch-monitor-period)))
         (running-deadtime (or deadtime-trim running-deadtime-default)) ;; two minutes (30 seconds between updates, this leaves 3x grace period)
         )
    (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 (db)
       (let* ((stmth1 (db:get-cache-stmth
		       dbstruct 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

		 ;; MATT: Moving this to rmt.scm - call right after calling find-and-mark-complete
		 ;; (launch:end-of-run-check run-id)
		 )))))))

;; BUG: Probably broken - does not explicitly use run-id in the query
;;
(define (db:top-test-set-per-pf-counts dbstruct run-id test-name)
  (db:general-call dbstruct 'top-test-set-per-pf-counts run-id (list test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name test-name)))

;; 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'

;;     ;; (db:delay-if-busy dbdat)
;;     (sqlite3:execute db "VACUUM;")
;;     dead-runs))

;;======================================================================
;; M E T A   G E T   A N D   S E T   V A R S
;;======================================================================
   
;; returns number if string->number is successful, string otherwise
;; also updates *global-delta*
;;
(define (db:get-var dbstruct run-id var)
  (let* ((res      #f))
    (db:with-db
     dbstruct run-id #f
     (lambda (db)
       (sqlite3:for-each-row
        (lambda (val)
          (set! res val))
        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 run-id var)
  (db:with-db dbstruct run-id #t 
	      (lambda (db)
		(sqlite3:execute db "UPDATE metadat SET val=val+1 WHERE var=?;" var))))

(define (db:dec-var dbstruct run-id var)
  (db:with-db dbstruct run-id #t 
	      (lambda (db)
		(sqlite3:execute 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 run-id var val)
  (db:with-db dbstruct run-id #t 
	      (lambda (db)
		(sqlite3:execute db "INSERT OR REPLACE INTO metadat (var,val) VALUES (?,?);" var val))))

(define (db:add-var dbstruct run-id var val)
  (db:with-db dbstruct run-id #t 
	      (lambda (db)
		(sqlite3:execute db "UPDATE metadat SET val=val+? WHERE var=?;" val var))))

(define (db:del-var dbstruct run-id var)
  (db:with-db dbstruct run-id #t 
	      (lambda (db)
		(sqlite3:execute db "DELETE FROM metadat WHERE var=?;" var))))

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

			  (string->number res)
			  #f)))
          (if newres
              newres
              res))
        res)))

#;(define (db:no-sync-close-db db stmt-cache)
  (db:safely-close-sqlite3-db db stmt-cache))

;; transaction protected lock aquisition
;; either:
;;    fails    returns  (#f . lock-creation-time)
;;    succeeds (returns (#t . lock-creation-time)
;; use (db:no-sync-del! db keyname) to release the lock

    (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 (db)
	   (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 ");")
		    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)
		    qryvals)
	     (sqlite3:execute db "UPDATE runs SET state=?,status=?,event_time=strftime('%s','now') WHERE id=? AND state='deleted';" state status res)
	     res))) 
	(begin
	  (debug:print-error 0 *default-log-port* "Called without all necessary keys")
	  #f))))

;; register a run with the db
;;
(define (db:insert-run dbstruct run-id keyvals runname state status user contour-in)
  (let* ((keys      (map car keyvals))
	 (keystr    (keys->keystr keys))
	 (contour   (or contour-in ""))  ;; empty string to force no hierarcy and be backwards compatible.
	 (comma     (if (> (length keys) 0) "," ""))
	 (andstr    (if (> (length keys) 0) " AND " ""))
	 (valslots  (keys->valslots keys)) ;; ?,?,? ...
	 (allvals   (append (list runname state status user contour) (map cadr keyvals)))
	 (qryvals   (append (list runname) (map cadr keyvals)))
	 (key=?str  (string-intersperse (map (lambda (k)(conc k "=?")) keys) " AND ")))
    (debug:print 3 *default-log-port* "keys: " keys " allvals: " allvals " keyvals: " keyvals " key=?str is " key=?str)
    (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 run-id #f
	 (lambda (db)
	   (let ((res #f))
	     (apply sqlite3:execute db (conc "INSERT OR IGNORE INTO runs (id,runname,state,status,owner,event_time,contour" comma keystr ") VALUES (?,?,?,?,?,strftime('%s','now'),?" comma valslots ");")
		    run-id
		    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)

;; 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

   run-id
   #f

   (lambda (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 (?,?,?,?);"))

	 (db:get-all-run-ids dbstruct)))
    res))

;; Convert calling routines to get list of run-ids and loop, do not use the get-tests-for-runs
;;

(define (db:delete-test-records dbstruct run-id test-id)
  (db:general-call dbstruct 'delete-test-step-records run-id (list test-id))
  (db:general-call dbstruct 'delete-test-data-records run-id (list test-id))
  (db:with-db
   dbstruct run-id #f
   (lambda (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")

	       (mt:process-triggers dbstruct run-id test-id newstate newstatus)))))
     testnames)
    test-ids))

;; ;; speed up for common cases with a little logic
;; ;; NB// Ultimately this will be deprecated in deference to mt:test-set-state-status-by-id
;;
;; NOTE: processing triggers was called here - moved upstream
;;
;;      NOTE: run-id is not used
;; ;;
(define (db:db-test-set-state-status 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)
		     test-id))
   ((and newstate newstatus)
    (sqlite3:execute 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 test-id)))))

(define (db:test-set-state-status dbstruct run-id test-id newstate newstatus newcomment)
  (db:with-db
   dbstruct
   run-id

   #t
   (lambda (db)

     (db:db-test-set-state-status db run-id test-id newstate newstatus newcomment))))











;; 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 = '');")) ;; )

	(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;
	test-id)
       (reverse res)))))

(define (db:get-steps-info-by-id dbstruct  test-step-id)
  (db:with-db
   dbstruct
   #f 
   #f
   (lambda (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;
	test-step-id)
       res))))

(define (db:get-steps-data dbstruct run-id test-id)
  (db:with-db
   dbstruct
   run-id
   #f
   (lambda (db)

	  (set! fail-count fcount)
	  (set! pass-count pcount))
	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;"
	test-id test-id)
       ;; Now rollup the counts to the central megatest.db
       (db:general-call dbstruct 'pass-fail-counts run-id (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 'test_data-pf-rollup run-id (list test-id test-id test-id test-id))))))

;; each section is a rule except "final" which is the final result
;;
;; [rule-5]
;; operator in
;; section LogFileBody
;; desc Output voltage

			   test-name))
	 (item-path    (db:test-get-item-path testdat))
         (tl-testdat   (db:get-test-info dbstruct run-id test-name ""))
         (tl-test-id   (if tl-testdat
			   (db:test-get-id tl-testdat)
			   #f)))
    (if (member state '("LAUNCHED" "REMOTEHOSTSTART")) 
	(db:general-call dbstruct 'set-test-start-time run-id (list test-id)))
    ;; (mutex-lock! *db-transaction-mutex*)
    (db:with-db
     dbstruct run-id #f
     (lambda (db)
       (let ((tr-res
              (sqlite3:with-transaction
               db
               (lambda ()
                 ;; NB// Pass the db so it is part fo the transaction
		  ;; this call sets the item state/status
                 (db:db-test-set-state-status db run-id test-id state status comment)
                 (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 dbstruct 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)))
                       (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:db-test-set-state-status 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))
         tr-res)))
    ;; this was moved out of test-set-state-status
    (mt:process-triggers dbstruct run-id test-id state status)))


(define (db:roll-up-rules state-status-counts state status)
  (if (null? state-status-counts)
      '(#f #f)
      (let* ((running              (length (filter (lambda (x)
						     (member (dbr:counts-state x) *common:running-states*))
						   state-status-counts)))
             (bad-not-started      (length (filter (lambda (x)
						     (and (equal? (dbr:counts-state x) "NOT_STARTED") 
							  (not (member (dbr:counts-status x)  *common:not-started-ok-statuses*))))
						   state-status-counts)))
             (all-curr-states      (common:special-sort  ;; worst -> best (sort of)
                                    (delete-duplicates
                                     (if (and state (not (member state *common:dont-roll-up-states*)))
					 (cons state (map dbr:counts-state state-status-counts))
					 (map dbr:counts-state state-status-counts)))
                                    *common:std-states* >))
             (all-curr-statuses    (common:special-sort  ;; worst -> best
                                    (delete-duplicates
                                     (if (and state status (not (member state *common:dont-roll-up-states*)))
					 (cons status (map dbr:counts-status state-status-counts))
					 (map dbr:counts-status state-status-counts)))
                                    *common:std-statuses* >))
             (non-completes        (filter (lambda (x)
					     (not (member x (cons "COMPLETED" *common:dont-roll-up-states*))))
					   all-curr-states))
	     (preq-fails        (filter (lambda (x)
					  (equal? x "PREQ_FAIL"))
					all-curr-statuses))
             (num-non-completes (length non-completes))
 	     (newstate          (cond
				 ((> running 0)           "RUNNING")            ;; anything running, call the situation running
				 ((> (length preq-fails) 0) "NOT_STARTED")
				 ((> bad-not-started 0)   "COMPLETED")          ;; we have an ugly situation, it is completed in the sense we cannot do more.
				 ((> num-non-completes 0) (car non-completes))  ;;  (remove (lambda (x)(equal? "COMPLETED" x)) all-curr-states))) ;; only rollup DELETED if all DELETED
				 (else                    (car all-curr-states))))
             (newstatus         (cond
				 ((> (length preq-fails) 0)  "PREQ_FAIL")
				 ((or (> bad-not-started 0)
                                      (and (equal? newstate "NOT_STARTED")
					   (> num-non-completes 0)))
				  "STARTED")
				 (else (car all-curr-statuses)))))
	(debug:print-info 2 *default-log-port*
			  "\n--> probe db:set-state-status-and-roll-up-items: "
			  "\n--> state-status-counts: "(map dbr:counts->alist state-status-counts)
			  "\n--> running:             "running
			  "\n--> bad-not-started:     "bad-not-started
			  "\n--> non-non-completes:   "num-non-completes
			  "\n--> non-completes:       "non-completes
			  "\n--> all-curr-states:     "all-curr-states
			  "\n--> all-curr-statuses:     "all-curr-statuses
			  "\n--> newstate              "newstate
			  "\n--> newstatus            "newstatus
			  "\n\n")

	;; NB// Pass the db so it is part of the transaction
	(list newstate newstatus))))

(define (db:set-state-status-and-roll-up-run dbstruct run-id curr-state curr-status)
  ;; (mutex-lock! *db-transaction-mutex*)
    (db:with-db
     dbstruct #f #f
     (lambda (db)
       (let ((tr-res
              (sqlite3:with-transaction
               db
               (lambda ()
                 (let* ((state-status-counts  (db:get-all-state-status-counts-for-run dbstruct 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 dbstruct run-id newstate newstatus )))))))
         ;; (mutex-unlock! *db-transaction-mutex*)
         tr-res))))


(define (db:get-all-state-status-counts-for-run dbstruct run-id)
 (let* ((test-count-recs (db:with-db
                          dbstruct #f #f
                          (lambda (db)
                            (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;"
                             run-id )))))
   test-count-recs))


;; 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
;;

   ((not (equal? megatest-version calling-version))
    (list #t (conc "Login warning due to mismatch megatest version: " calling-version ", " megatest-version)))
   
    (else
    (hash-table-set! *logged-in-clients* client-signature (current-seconds))
    '(#t "successful login"))))

(define (db:general-call dbstruct stmtname run-id params)
  (let ((query (let ((q (alist-ref (if (string? stmtname)
				       (string->symbol stmtname)
				       stmtname)
				   db:queries)))
 		 (if q (car q) #f))))
    (db:with-db
     dbstruct run-id #f
     (lambda (db)
       (apply sqlite3:execute 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)

			test-name     " "
			item-path     " " ;; has / prepended to deal with toplevel tests
			actual-state  " "
			actual-status " "
			event-time
			))
	 (prev-nbfake-log (get-environment-variable "NBFAKE_LOG")))
    (set-environment-variable! "NBFAKE_LOG" (conc (cond
				((and (directory-exists? test-rundir)
				      (file-writable? test-rundir))
				 test-rundir)
				((and (directory-exists? *toppath*)
				      (file-writable? *toppath*))
				 *toppath*)
				(else (conc "/tmp/" (current-user-name))))
			       "/" logname))
    (debug:print-info 0 *default-log-port* "TRIGGERED on " trigger ", running command " fullcmd " output at " (get-environment-variable "NBFAKE_LOG"))
    ;; (call-with-environment-variables
    ;;  `(("NBFAKE_LOG" . ,(conc test-rundir "/" logname)))
    ;;  (lambda ()
    (process-run fullcmd)
    (if prev-nbfake-log
	(set-environment-variable! "NBFAKE_LOG" prev-nbfake-log)
	(unset-environment-variable! "NBFAKE_LOG"))
    )) ;; ))

(define (mt:process-triggers dbstruct run-id test-id newstate newstatus)
  (if test-id 
      (let* ((test-dat      (db:get-test-info-by-id dbstruct run-id test-id)))
	(if test-dat
	    (let* ((test-rundir   (db:test-get-rundir       test-dat)) ;; ) ;; )

	    ;; Setting MT_LINKTREE here is almost certainly unnecessary. 
	    (let ((tconfig-file (conc hed "/" test-name "/testconfig")))
	      (if (and (common:file-exists? tconfig-file)
		       (file-readable? tconfig-file))
		  (let ((link-tree-path (common:get-linktree)) ;; (configf:lookup *configdat* "setup" "linktree"))
			(old-link-tree  (get-environment-variable "MT_LINKTREE"))
			(bigmodenv      (module-environment 'bigmod)))
		    (if link-tree-path (set-environment-variable! "MT_LINKTREE" link-tree-path))
		    (let ((newtcfg (configf:read-config tconfig-file #f #f env-to-use: bigmodenv))) ;; NOTE: Does NOT run [system ...]
		      (hash-table-set! *testconfigs* test-name newtcfg)
		      (if old-link-tree 
			  (set-environment-variable! "MT_LINKTREE" old-link-tree)
			  (unset-environment-variable! "MT_LINKTREE"))
		      newtcfg))
		  (if (null? tal)
		      (begin
			(debug:print-error 0 *default-log-port* "No readable testconfig found for " test-name)
			#f)
		      (loop (car tal)(cdr tal))))))))))

		(debug:print-info 0 *default-log-port* "Server already running at "sinfo ", while trying to register server " host":"port)
		#f) ;; server already registered
	      (begin
		(sqlite3:execute db "INSERT INTO servers (host,port,servkey,pid,ipaddr,apath,dbname) VALUES (?,?,?,?,?,?,?);"
				 host port servkey pid ipaddr apath dbname)
		(db:get-server-info dbstruct apath dbname)))))))))
  
;; run this one in a transaction where first check if host:port is taken
(define (db:deregister-server dbstruct host port servkey pid ipaddr apath dbname)
  (db:with-db
   dbstruct
   #f #f
   (lambda (db)
     (sqlite3:with-transaction
      db
      (lambda ()
	(let* ((sinfo      (db:get-server-info dbstruct apath dbname)))
	  (if (not sinfo)
	      (begin
		(debug:print-info 0 *default-log-port* "Server already removed for "apath", "dbname) ;; at "sinfo ", while trying to register server " host":"port)
		#f) ;; server already deregistered
	      (begin
		(sqlite3:execute db "DELETE FROM servers WHERE apath=? AND dbname=?;" ;; (host,port,servkey,pid,ipaddr,apath,dbname) VALUES (?,?,?,?,?,?,?);"
				 ;; host port servkey pid ipaddr
				 apath dbname)
		#;(db:get-server-info dbstruct apath dbname)))))))))

(define (db:get-server-info dbstruct apath dbname)
  (db:with-db
   dbstruct
   #f #f
   (lambda (db)
     (sqlite3:fold-row
      (lambda (res host port servkey pid ipaddr apath dbpath)
	(list host port servkey pid ipaddr apath dbpath))
      #f
      db
      "SELECT host,port,servkey,pid,ipaddr,apath,dbname FROM servers WHERE apath=? AND dbname=?;"
      apath dbname))))

(define (db:get-count-servers dbstruct apath)
  (db:with-db
   dbstruct
   #f #f
   (lambda (db)
     (sqlite3:fold-row
      (lambda (res count)
	(max res count))
      0
      db
      "SELECT count(*) FROM servers WHERE apath=?;"
      apath))))

)