Index: dashboard.scm ================================================================== --- dashboard.scm +++ dashboard.scm @@ -1089,11 +1089,11 @@ ;; - not appropriate for where all the runs are needed ;; (define (update-buttons tabdat uidat numruns numtests) (let* ((runs (if (> (length (dboard:tabdat-allruns tabdat)) numruns) (take-right (dboard:tabdat-allruns tabdat) numruns) - (pad-list (dboard:tabdat-allruns tabdat) numruns))) + (pad-list (dboard:tabdat-allruns tabdat) numruns))) (lftcol (dboard:uidat-get-lftcol uidat)) (tableheader (dboard:uidat-get-header uidat)) (table (dboard:uidat-get-runsvec uidat)) (coln 0) (all-test-names (make-hash-table)) Index: dbmod.scm ================================================================== --- dbmod.scm +++ dbmod.scm @@ -172,10 +172,12 @@ (define (sync-gasket tables last-update inmem dbh dbfname direction) (case (dbfile:sync-method) ((none) #f) ((attach) (dbmod:attach-sync tables inmem dbfname direction)) + ((newsync) + (dbmod:new-sync tables inmem dbh dbfname direction)) (else (case direction ((todest) (dbmod:sync-tables tables last-update inmem dbh)) (else @@ -378,53 +380,136 @@ (sqlite3:execute dbh (conc "ATTACH '"destdbfile"' AS auxdb;")) (for-each (lambda (table) (let* ((tbldat (alist-ref table tables equal?)) (fields (map car tbldat)) + (no-id-fields (filter (lambda (x)(not (equal? x "id"))) fields)) (fields-str (string-intersperse fields ",")) + (no-id-fields-str (string-intersperse no-id-fields ",")) (dir (eq? direction 'todest)) (fromdb (if dir "" "auxdb.")) (todb (if dir "auxdb." "")) + (set-str (string-intersperse + (map (lambda (field) + (conc fromdb field"="todb field)) + fields) + ",")) (stmt1 (conc "INSERT OR IGNORE INTO "todb table " SELECT * FROM "fromdb table";")) - (stmt2 (conc "INSERT OR REPLACE INTO "todb table - " SELECT * FROM "fromdb table" WHERE " - 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 - 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";")) + (stmt8 (conc "UPDATE "todb table" SET ("no-id-fields-str") = (SELECT "no-id-fields-str" FROM "fromdb table" WHERE "todb table".id="fromdb table".id" + (if (member "last_update" fields) + (conc " AND "fromdb table".last_update > "todb table".last_update);") + ");"))) (start-ms (current-milliseconds))) - ;; (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;"))) + (debug:print 0 *default-log-port* "stmt8="stmt8) (if (sqlite3:auto-committing? dbh) (begin (sqlite3:with-transaction dbh (lambda () - (sqlite3:execute dbh stmt5) + (sqlite3:execute dbh stmt1) ;; get all new rows + + #;(if (member "last_update" fields) + (sqlite3:execute dbh stmt8)) ;; get all updated rows + ;; (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 stmt6) )) (debug:print 0 *default-log-port* "Synced table "table " in "(- (current-milliseconds) start-ms)"ms")) (debug:print 0 *default-log-port* "Skipping sync of table "table" due to transaction in flight.")))) table-names) (sqlite3:execute dbh "DETACH auxdb;")))) +;; FAILED ATTEMPTS + + ;; (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;"))) + + ;; (stmt2 (conc "INSERT OR REPLACE INTO "todb table + ;; " SELECT * FROM "fromdb table" WHERE " + ;; 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 + ;; 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";")) + ;; (stmt7 (conc "UPDATE "todb table" SET "set-str (if (member "last_update" fields) + ;; (conc " WHERE "fromdb table".last_update > "todb table".last_update;") + ;; ";"))) + ;; prefix is "" or "auxdb." ;; ;; (define (dbmod:last-update-patch dbh prefix) ;; (let (( +;; tbls is ( ("tablename" ( "field1" [#f|proc1] ) ( "field2" [#f|proc2] ) .... ) ) +;; +;; direction = fromdest, todest +;; mode = 'full, 'incr +;; +;; Idea: youngest in dest is last_update time +;; +(define (dbmod:new-sync tables dbh1 dbh2 destdbfile direction #!key + (mode 'full)) + (debug:print 0 *default-log-port* "Doing sync "direction" "destdbfile) + (if (not (sqlite3:auto-committing? dbh1)) + (debug:print 0 *default-log-port* "Skipping sync due to transaction in flight.") + (let* ((table-names (map car tables)) + (dest-exists (file-exists? destdbfile))) + (assert dest-exists "FATAL: sync called with non-existant file, "destdbfile) + (for-each + (lambda (table) + (let* ((tbldat (alist-ref table tables equal?)) + (fields (map car tbldat)) + (no-id-fields (filter (lambda (x)(not (equal? x "id"))) fields)) + (questionmarks (string-intersperse (make-list (length no-id-fields) "?") ",")) + (fields-str (string-intersperse fields ",")) + (no-id-fields-str (string-intersperse no-id-fields ",")) + (dir (eq? direction 'todest)) + (fromdb (if dir dbh1 dbh2)) + (todb (if dir dbh2 dbh1)) + (set-str (string-intersperse + (map (lambda (field) + (conc fromdb field"="todb field)) + fields) + ",")) + ;; (stmt1 (conc "INSERT OR IGNORE INTO "todb table + ;; " SELECT * FROM "fromdb table";")) + ;; (stmt8 (conc "UPDATE "todb table" SET ("no-id-fields-str") = (SELECT "no-id-fields-str" FROM "fromdb table " WHERE "todb table".id="fromdb table".id" + ;; (if (member "last_update" fields) + ;; (conc " AND "fromdb table".last_update > "todb table".last_update);") + ;; ");"))) + (stmt1 (conc "SELECT MAX(last_update) FROM "table";")) ;; use the highest last_update as your time reference + (stmt2 (conc "SELECT no-id-fields-str FROM "table" WHERE last_update>?;")) + (stmt3 (conc "UPDATE "table" SET ("no-id-fields-str") = ("questionmarks") WHERE id=?;")) + (start-ms (current-milliseconds))) + (debug:print 0 *default-log-port* "stmt3="stmt3) + (if (sqlite3:auto-committing? dbh1) + (begin + (sqlite3:with-transaction + dbh1 + (lambda () + (sqlite3:execute dbh1 stmt1) ;; get all new rows + + #;(if (member "last_update" fields) + (sqlite3:execute dbh1 stmt8)) ;; get all updated rows + ;; (sqlite3:execute dbh stmt5) + ;; (sqlite3:execute dbh stmt4) ;; if it worked this would be better for incremental up + ;; (sqlite3:execute dbh stmt6) + )) + (debug:print 0 *default-log-port* "Synced table "table + " in "(- (current-milliseconds) start-ms)"ms")) + (debug:print 0 *default-log-port* "Skipping sync of table "table" due to transaction in flight.")))) + table-names) + (sqlite3:execute dbh1 "DETACH auxdb;")))) + ;;====================================================================== ;; Moved from dbfile ;;====================================================================== )