Index: db.scm ================================================================== --- db.scm +++ db.scm @@ -932,19 +932,21 @@ test_id INTEGER, update_time TIMESTAMP, cpuload INTEGER DEFAULT -1, diskfree INTEGER DEFAULT -1, diskusage INTGER DEFAULT -1, - run_duration INTEGER DEFAULT 0);") + run_duration INTEGER DEFAULT 0, + last_update INTEGER DEFAULT (strftime('%s','now')));") (sqlite3:execute db "CREATE TABLE IF NOT EXISTS archives ( id INTEGER PRIMARY KEY, test_id INTEGER, state TEXT DEFAULT 'new', status TEXT DEFAULT 'n/a', archive_type TEXT DEFAULT 'bup', du INTEGER, - archive_path TEXT);"))) + archive_path TEXT, + last_update INTEGER DEFAULT (strftime('%s','now')));"))) (db:create-triggers db) db)) ;; ) ;;====================================================================== ;; A R C H I V E S Index: dbfile.scm ================================================================== --- dbfile.scm +++ dbfile.scm @@ -43,10 +43,11 @@ ) (define keep-age-param (make-parameter 10)) ;; qif file age, if over move to attic (define num-run-dbs (make-parameter 10)) ;; number of db's in .megatest (define dbfile:testsuite-name (make-parameter #f)) +(define dbfile:sync-method (make-parameter 'attach)) ;; 'attach or 'original ;; 'original - use old condition code ;; 'suicide-mode - create mtrah/stop-the-train with info on what went wrong ;; else use no condition code (should be production mode) ;; Index: dbmod.scm ================================================================== --- dbmod.scm +++ dbmod.scm @@ -139,16 +139,34 @@ (dbr:dbstruct-inmem-set! dbstruct inmem) (dbr:dbstruct-ondiskdb-set! dbstruct db) (dbr:dbstruct-dbfile-set! dbstruct dbfullname) (dbr:dbstruct-sync-proc-set! dbstruct (lambda (last-update) - (if (eq? syncdir 'todisk) ;; sync to disk normally, sync from in dashboard - (dbmod:sync-tables tables last-update inmem db) - (dbmod:sync-tables tables last-update db inmem)))) - (dbmod:sync-tables tables #f db inmem) + (sync-gasket tables last-update inmem db + dbfullname syncdir))) + ;; (dbmod:sync-tables tables #f db inmem) + (sync-gasket tables #f inmem db dbfullname 'fromdest) (dbr:dbstruct-last-update-set! dbstruct (current-seconds)) ;; should this be offset back in time by one second? dbstruct)) + +;; (if (eq? syncdir 'todisk) ;; sync to disk normally, sync from in dashboard +;; (dbmod:sync-tables tables last-update inmem db) +;; (dbmod:sync-tables tables last-update db inmem)))) + +;; direction: 'fromdest 'todest +;; +(define (sync-gasket tables last-update inmem dbh dbfname direction) + (case (dbfile:sync-method) + ((attach) + (dbmod:attach-sync tables inmem dbfname direction)) + (else + (case direction + ((todest) + (dbmod:sync-tables tables last-update inmem dbh)) + (else + (dbmod:sync-tables tables last-update dbh inmem)))))) + (define (dbmod:close-db dbstruct) ;; do final sync to disk file ;; (do-sync ...) (sqlite3:finalize! (dbr:dbstruct-ondiskdb dbstruct))) @@ -307,24 +325,46 @@ (count (cdr dat))) (set! tot-count (+ tot-count count)))) (sort (hash-table->alist numrecs)(lambda (a b)(> (cdr a)(cdr b)))))) tot-count)) +(define (has-last-update dbh tablename) + (let* ((has-last #f)) + (sqlite3:for-each-row + (lambda (name) + (if (equal? name "last_update") + (set! has-last #t))) + dbh + (conc "SELECT name FROM pragma_table_info('"tablename"') as tblInfo;")) + has-last)) + +;; tbls is ( ("tablename" ( "field1" [#f|proc1] ) ( "field2" [#f|proc2] ) .... ) ) +;; ;; direction = fromdest, todest ;; mode = 'full, 'incr ;; -(define (dbmod:attach-sync tables dbh destdbfile direction #!key (mode 'full)) - (let* ((dest-exists (file-exists? destdbfile))) +(define (dbmod:attach-sync tables dbh destdbfile direction #!key + (mode 'full) + (no-update '("keys")) ;; do + ) + (debug:print 0 *default-log-port* "Doing sync "direction" "destdbfile) + (let* ((table-names (map car tables)) + (dest-exists (file-exists? destdbfile))) (assert dest-exists "FATAL: sync called with non-existant file, "destdbfile) ;; attach the destdbfile ;; for each table ;; insert into dest. select * from src.
where last_update>last_update ;; done - (sqlite3:execute dbh "ATTACH ? AS auxdb;" destdbfile) + (debug:print 0 *default-log-port* "Attaching "destdbfile" as auxdb") + (sqlite3:execute dbh (conc "ATTACH '"destdbfile"' AS auxdb;")) (for-each (lambda (table) - (let* ((dir (eq? direction 'todest)) + (debug:print 0 *default-log-port* "Syncing table "table) + (let* ((tbldat (alist-ref table tables equal?)) + (fields (map car tbldat)) + (fields-str (string-intersperse fields ",")) + (dir (eq? direction 'todest)) (fromdb (if dir "" "auxdb.")) (todb (if dir "auxdb." "")) (stmt1 (conc "INSERT OR IGNORE INTO "todb table " SELECT * FROM "fromdb table";")) (stmt2 (conc "INSERT OR REPLACE INTO "todb table @@ -332,23 +372,35 @@ fromdb table".last_update > " todb table".last_update;")) (stmt3 (conc "INSERT OR REPLACE INTO "todb"."table " SELECT * FROM "fromdb table";")) (stmt4 (conc "DELETE FROM "todb table" WHERE "fromdb - "tests.last_update > "todb table".last_update;"))) - ;; (print "stmt1: "stmt1) - ;; (print "stmt2: "stmt2) - ;; (print "stmt3: "stmt4) - ;; (print "stmt1: "stmt1) - (sqlite3:execute dbh stmt4) - (sqlite3:execute dbh stmt1) - ;; (sqlite3:execute dbh stmt1) - ;; (sqlite3:execute dbh stmt2) + table ".last_update > "todb table".last_update;")) + (stmt5 (conc "DELETE FROM "todb table";")) + (stmt6 (conc "INSERT OR REPLACE INTO "todb table" ("fields-str") SELECT "fields-str" FROM "fromdb table";")) + ) + ;; (if (not (has-last-update dbh table)) + ;; (sqlite3:execute dbh (conc "ALTER TABLE "table" ADD COLUMN last_update INTEGER;"))) + ;; (if (not (has-last-update dbh (conc "auxdb."table))) + ;; (sqlite3:execute dbh (conc "ALTER TABLE auxdb."table" ADD COLUMN last_update INTEGER;"))) + (sqlite3:with-transaction + dbh + (lambda () + (sqlite3:execute dbh stmt5) + ;; (sqlite3:execute dbh stmt4) ;; if it worked this would be better for incremental up + ;; (sqlite3:execute dbh stmt1) + (sqlite3:execute dbh stmt6) + )) (sqlite3:execute dbh "DETACH auxdb;"))) - tables))) + table-names))) +;; prefix is "" or "auxdb." +;; +;; (define (dbmod:last-update-patch dbh prefix) +;; (let (( + ;;====================================================================== ;; Moved from dbfile ;;====================================================================== )