Megatest

;; mode = 'full, 'incr
;;
(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)
  (if (not (sqlite3:auto-committing? dbh))
      (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)
    ;; attach the destdbfile
    ;; for each table
    ;;    insert into dest.<table> select * from src.<table> where last_update>last_update
    ;; done
    (debug:print 0 *default-log-port* "Attaching "destdbfile" as auxdb")
    (sqlite3:execute dbh (conc "ATTACH '"destdbfile"' AS auxdb;"))
    (for-each
     (lambda (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
			   " 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";"))
	      (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;")))
	 (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)
	    ))
	 (debug:print 0 *default-log-port* "Synced table "table" in "(- (current-milliseconds) start-ms)"ms")
      (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.<table> select * from src.<table> where last_update>last_update
	;; done
	(debug:print 0 *default-log-port* "Attaching "destdbfile" as auxdb")
	(sqlite3:execute dbh (conc "ATTACH '"destdbfile"' AS auxdb;"))
	(for-each
	 (lambda (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
			       " 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";"))
		  (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;")))
	     (if (sqlite3:auto-committing? dbh)
		 (begin
		   (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)
		      ))
		   (debug:print 0 *default-log-port* "Synced table "table
	 ))
     table-names)
    (sqlite3:execute dbh "DETACH auxdb;")))
				" 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;"))))

;; prefix is "" or "auxdb."
;;
;; (define (dbmod:last-update-patch dbh prefix)
;;   (let ((
  
;;======================================================================