Overview
| Comment: | Initial code for more efficient db data sync |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
3f472063ed7f26703a6b7c27540e18bd |
| User & Date: | matt on 2013-11-13 23:50:18 |
| Other Links: | manifest | tags |
Context
|
2013-11-15
| ||
| 04:11 | Made Megatest philosophy spell STIRRED. Thanks to Dan Burres for pointing this out check-in: bd5e571a01 user: matt tags: trunk | |
|
2013-11-13
| ||
| 23:50 | Initial code for more efficient db data sync check-in: 3f472063ed user: matt tags: trunk | |
|
2013-11-12
| ||
| 23:26 | 90% done with migration to inmem db check-in: 6b749d9f51 user: matt tags: trunk | |
Changes
Modified db.scm from [f82ccb1ffc] to [e43737b0ee].
| ︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 111 112 113 114 |
(sqlite3:open-database ":memory:")))
(handler (make-busy-timeout 3600)))
(if (or (not path)
(not exists))
(db:initialize db))
(sqlite3:set-busy-handler! db handler)
db))
(define (db:sync-to fromdb todb)
;; strategy
;; 1. Get all run-ids
;; 2. For each run-id
;; a. Sync that run in a transaction
(let ((trecchgd 0)
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 |
(sqlite3:open-database ":memory:")))
(handler (make-busy-timeout 3600)))
(if (or (not path)
(not exists))
(db:initialize db))
(sqlite3:set-busy-handler! db handler)
db))
;; (define (db:sync-table tblname fields fromdb todb)
(define (db:tbls db)
(let ((keys (db:get-keys db)))
(list
(list "metadat" '("var" #f) '("val" #f))
(append (list "runs"
'("id" #f))
(map (lambda (k)(list k #f))
(append keys
(list "runname" "state" "status" "owner" "event_time" "comment" "fail_count" "pass_count")))))))
;; tbls is ( ("tablename" ( "field1" [#f|proc1] ) ( "field2" [#f|proc2] ) .... ) )
(define (db:sync-tables tbls fromdb todb)
(let ((stmts (make-hash-table)) ;; table-field => stmt
(all-stmts '())) ;; ( ( stmt1 value1 ) ( stml2 value2 ))
(for-each ;; table
(lambda (tabledat)
(let* ((tablename (car tabledat))
(fields (cdr tabledat))
(num-fields (length fields))
(field->num (make-hash-table))
(num->field (apply vector (map car fields)))
(full-sel (conc "SELECT " (string-intersperse (map car fields) ",")
" FROM " tablename ";"))
(full-ins (conc "INSERT OR REPLACE INTO " tablename " ( " (string-intersperse (map car fields) ",") " ) "
" VALUES ( " (string-intersperse (make-list num-fields "?") ",") " );"))
(fromdat '())
(todat (make-hash-table))
(count 0))
;; set up the field->num table
(for-each
(lambda (field)
(hash-table-set! field->num field count)
(set! count (+ count 1)))
fields)
;; read the source table
(sqlite3:for-each-row
(lambda (a . b)
(set! fromdat (cons (apply vector a b) fromdat)))
fromdb
full-sel)
;; read the target table
(sqlite3:for-each-row
(lambda (a . b)
(hash-table-set! todat a (apply vector a b)))
todb
full-sel)
;; first pass implementation, just insert all changed rows
(let ((stmth (sqlite3:prepare todb full-ins)))
(sqlite3:with-transaction
todb
(lambda ()
(for-each ;;
(lambda (fromrow)
(let* ((a (vector-ref fromrow 0))
(curr (hash-table-ref todat a))
(same #t))
(let loop ((i 0))
(if (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)(apply sqlite3:execute full-ins (vector->list fromrow)))))
fromdat)))
(sqlite3:finalize! stmth))))
tbls)))
(define (db:sync-to fromdb todb)
;; strategy
;; 1. Get all run-ids
;; 2. For each run-id
;; a. Sync that run in a transaction
(let ((trecchgd 0)
|
| ︙ | ︙ |