Overview
Comment: | changed more sqlite3 calls to dbi |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | v1.63-gasket |
Files: | files | file ages | folders |
SHA1: |
750e8df937a3d41a50dd165217e5875e |
User & Date: | srehman on 2016-12-20 15:50:46 |
Other Links: | branch diff | manifest | tags |
Context
2016-12-20
| ||
16:36 | fixed issues with single columns being fetched from db check-in: 484ab17aff user: srehman tags: v1.63-gasket | |
15:50 | changed more sqlite3 calls to dbi check-in: 750e8df937 user: srehman tags: v1.63-gasket | |
15:01 | fixed sync-tables bug check-in: 1d0bf66aa6 user: srehman tags: v1.63-gasket | |
Changes
Modified datashare.scm from [ff237ff27e] to [b0df4daba9].
︙ | ︙ | |||
148 149 150 151 152 153 154 155 | (define (datashare:register-data db area version-name store-type submitter quality source-path comment) (let ((iter-qry (dbi:prepare db "SELECT max(iteration) FROM pkgs WHERE area=? AND version_name=?;")) (next-iteration 0)) (dbi:with-transaction db (lambda () (dbi:for-each-row (lambda (iteration) | > | | | | | | 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 189 190 191 192 193 194 195 | (define (datashare:register-data db area version-name store-type submitter quality source-path comment) (let ((iter-qry (dbi:prepare db "SELECT max(iteration) FROM pkgs WHERE area=? AND version_name=?;")) (next-iteration 0)) (dbi:with-transaction db (lambda () (dbi:for-each-row (lambda (output) (lambda (iteration) (if (and (number? (vector-refiteration) (>= iteration next-iteration)) (set! next-iteration (+ iteration 1)))) iter-qry area version-name)) ;; now store the data (dbi:exec db "INSERT INTO pkgs (area,version_name,iteration,store_type,submitter,source_path,quality,comment) VALUES (?,?,?,?,?,?,?,?);" area version-name next-iteration (conc store-type) submitter source-path quality comment))) (dbi:close iter-qry) next-iteration)) (define (datashare:get-id db area version-name iteration) (let ((res #f)) (dbi:for-each-row (lambda (id) (set! res (vector-ref id 0))) db "SELECT id FROM pkgs WHERE area=? AND version_name=? AND iteration=?;" area version-name iteration) res)) (define (datashare:set-stored-path db id path) (dbi:exec db "UPDATE pkgs SET stored_path=? WHERE id=?;" path id)) (define (datashare:set-copied db id value) (dbi:exec db "UPDATE pkgs SET copied=? WHERE id=?;" value id)) (define (datashare:get-pkg-record db area version-name iteration) (let ((res #f)) (dbi:for-each-row (lambda (output) (set! res output)) db "SELECT * FROM pkgs WHERE area=? AND version_name=? AND iteration=?;" area version-name iteration) res)) |
︙ | ︙ | |||
212 213 214 215 216 217 218 | (define (datashare:record-pkg-ref db pkg-id dest-link) (dbi:exec db "INSERT INTO refs (pkg_id,destlink) VALUES (?,?);" pkg-id dest-link)) (define (datashare:count-refs db pkg-id) (let ((res 0)) (dbi:for-each-row (lambda (count) | | | 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 | (define (datashare:record-pkg-ref db pkg-id dest-link) (dbi:exec db "INSERT INTO refs (pkg_id,destlink) VALUES (?,?);" pkg-id dest-link)) (define (datashare:count-refs db pkg-id) (let ((res 0)) (dbi:for-each-row (lambda (count) (set! res (vector-ref count 0)) db "SELECT count(id) FROM refs WHERE pkg_id=?;" pkg-id) res)) ;; Create the sqlite db (define (datashare:open-db configdat) |
︙ | ︙ | |||
279 280 281 282 283 284 285 | res)) (define open-run-close open-run-close-no-exception-handling) (define (datashare:get-pkgs db area-filter version-filter iter-filter) (let ((res '())) (dbi:for-each-row ;; replace with fold ... | | | | | | 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 | res)) (define open-run-close open-run-close-no-exception-handling) (define (datashare:get-pkgs db area-filter version-filter iter-filter) (let ((res '())) (dbi:for-each-row ;; replace with fold ... (lambda (output) (set! res (cons output res))) db (conc "SELECT id,area,version_name,store_type,copied,source_path,iteration,submitter,datetime,storegrp,datavol,quality,disk_id,comment,stored_path " " FROM pkgs WHERE area like ? AND version_name LIKE ? AND iteration " iter-filter ";") area-filter version-filter) (reverse res))) (define (datashare:get-pkg db area-name version-name #!key (iteration #f)) (let ((dat '()) (res #f)) (dbi:for-each-row ;; replace with fold ... (lambda (output) (set! dat (cons output dat))) db (conc "SELECT id,area,version_name,store_type,copied,source_path,iteration,submitter,datetime,storegrp,datavol,quality,disk_id,comment,stored_path " " FROM pkgs WHERE area=? AND version_name=? ORDER BY iteration ASC;") area-name version-name) ;; now filter for iteration, either max if #f or specific one (if (null? dat) #f |
︙ | ︙ | |||
314 315 316 317 318 319 320 321 322 | hed (loop (car tal)(cdr tal))))))))) (define (datashare:get-versions-for-area db area-name #!key (version-patt #f)) (let ((res '()) (data (make-hash-table))) (dbi:for-each-row (lambda (version-name submitter iteration submitted-time comment) ;; 0 1 2 3 4 | > | | 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 | hed (loop (car tal)(cdr tal))))))))) (define (datashare:get-versions-for-area db area-name #!key (version-patt #f)) (let ((res '()) (data (make-hash-table))) (dbi:for-each-row (lambda (output) (lambda (version-name submitter iteration submitted-time comment) ;; 0 1 2 3 4 (hash-table-set! data version-name (vector version-name submitter iteration submitted-time comment)))) db "SELECT version_name,submitter,iteration,datetime,comment FROM pkgs WHERE area='megatest' AND version_name != 'latest' AND version_name LIKE ? ORDER BY datetime asc;" (or version-patt "%")) (map (lambda (x)(hash-table-ref data x))(sort (hash-table-keys data) string-ci>=)))) ;;====================================================================== ;; DATA IMPORT/EXPORT |
︙ | ︙ |
Modified filedb.scm from [40227ed70e] to [9cc1ddbcdd].
︙ | ︙ | |||
57 58 59 60 61 62 63 | (define (filedb:finalize-db! fdb) (dbi:close (filedb:fdb-get-db fdb))) (define (filedb:get-current-time-string) (string-chomp (time->string (seconds->local-time (current-seconds))))) (define (filedb:get-base-id db path) | | | | | | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | (define (filedb:finalize-db! fdb) (dbi:close (filedb:fdb-get-db fdb))) (define (filedb:get-current-time-string) (string-chomp (time->string (seconds->local-time (current-seconds))))) (define (filedb:get-base-id db path) (let ((stmt (dbi:prepare db "SELECT id FROM bases WHERE base=?;")) (id-num #f)) (dbi:for-each-row (lambda (num) (set! id-num (vector-ref num 0))) stmt path) (dbi:close stmt) id-num)) (define (filedb:get-path-id db path parent) (let ((stmt (dbi:prepare db "SELECT id FROM paths WHERE path=? AND parent_id=?;")) (id-num #f)) (dbi:for-each-row (lambda (num) (set! id-num (vector-ref num 0))) stmt path parent) (dbi:close stmt) id-num)) (define (filedb:add-base db path) (let ((existing (filedb:get-base-id db path))) (if existing #f (begin |
︙ | ︙ | |||
107 108 109 110 111 112 113 | (vector-ref statinfo 4) ;; gid (vector-ref statinfo 5) ;; size (vector-ref statinfo 8) ;; mtime ) (dbi:close stmt))) ;; (filedb:get-current-time-string)))) (define (filedb:add-path db path parent) | | | 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 | (vector-ref statinfo 4) ;; gid (vector-ref statinfo 5) ;; size (vector-ref statinfo 8) ;; mtime ) (dbi:close stmt))) ;; (filedb:get-current-time-string)))) (define (filedb:add-path db path parent) (let ((stmt (dbi:prepare db "INSERT INTO paths (path,parent_id) VALUES (?,?);"))) (dbi:exec stmt path parent) (dbi:close stmt))) (define (filedb:register-path fdb path #!key (save-stat #f)) (let* ((db (filedb:fdb-get-db fdb)) (pathcache (filedb:fdb-get-pathcache fdb)) (stat (if save-stat (file-stat path #t))) |
︙ | ︙ | |||
173 174 175 176 177 178 179 | pth)))) (define (filedb:drop-base fdb path) (print "Sorry, I don't do anything yet")) (define (filedb:find-all fdb pattern action) (let* ((db (filedb:fdb-get-db fdb)) | | | | | | 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 | pth)))) (define (filedb:drop-base fdb path) (print "Sorry, I don't do anything yet")) (define (filedb:find-all fdb pattern action) (let* ((db (filedb:fdb-get-db fdb)) (stmt (dbi:prepare db "SELECT id FROM paths WHERE path like ?;")) (result '())) (dbi:for-each-row (lambda (num) (action (vector-ref num 0)) (set! result (cons num result))) stmt pattern) (dbi:close stmt) result)) (define (filedb:get-path-record fdb id) (let* ((db (filedb:fdb-get-db fdb)) (partcache (filedb:fdb-get-partcache fdb)) (dat (hash-table-ref/default partcache id #f))) (if dat dat (let ((stmt (dbi:prepare db "SELECT path,parent_id FROM paths WHERE id=?;")) (result #f)) (dbi:for-each-row (lambda (output) (lambda (path parent_id)(set! result (list path parent_id)))) stmt id) (hash-table-set! partcache id result) (dbi:close stmt) result)))) (define (filedb:get-children fdb parent-id) (let* ((db (filedb:fdb-get-db fdb)) (res '())) |
︙ | ︙ | |||
212 213 214 215 216 217 218 219 | ;; retrieve all that have children and those without ;; children that match patt (define (filedb:get-children-patt fdb parent-id search-patt) (let* ((db (filedb:fdb-get-db fdb)) (res '())) ;; first get the children that have no children (dbi:for-each-row (lambda (id path parent-id) | > | | 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 | ;; retrieve all that have children and those without ;; children that match patt (define (filedb:get-children-patt fdb parent-id search-patt) (let* ((db (filedb:fdb-get-db fdb)) (res '())) ;; first get the children that have no children (dbi:for-each-row (lambda (output) (lambda (id path parent-id) (set! res (cons (vector id path parent-id) res)))) db "SELECT id,path,parent_id FROM paths WHERE parent_id=? AND (id IN (SELECT parent_id FROM paths) OR path LIKE ?);" parent-id search-patt) res)) (define (filedb:get-path fdb id) (let* ((db (filedb:fdb-get-db fdb)) |
︙ | ︙ |
Modified lock-queue.scm from [95a0581694] to [d0d9e7e805].
︙ | ︙ | |||
101 102 103 104 105 106 107 108 109 110 | (lock-queue:delete-lock-db dbdat) (lock-queue:any-younger? dbdat mystart test-id remtries: (- remtries 1))) (begin (debug:print-error 0 *default-log-port* " Failed to find younger locks for test with id " test-id ", error: " ((condition-property-accessor 'exn 'message) exn) ", giving up.") #f)) (let ((res #f)) (dbi:for-each-row (lambda (tid) ;; Actually this should not be needed as mystart cannot be simultaneously less than and test-id same as (if (not (equal? tid test-id)) | > | | | | | | 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 | (lock-queue:delete-lock-db dbdat) (lock-queue:any-younger? dbdat mystart test-id remtries: (- remtries 1))) (begin (debug:print-error 0 *default-log-port* " Failed to find younger locks for test with id " test-id ", error: " ((condition-property-accessor 'exn 'message) exn) ", giving up.") #f)) (let ((res #f)) (dbi:for-each-row (lambda (output) (lambda (tid) ;; Actually this should not be needed as mystart cannot be simultaneously less than and test-id same as (if (not (equal? tid test-id)) (set! res tid)))) (lock-queue:db-dat-get-db dbdat) "SELECT test_id FROM queue WHERE start_time > ?;" mystart) res))) (define (lock-queue:get-lock dbdat test-id #!key (count 10)(waiting-msg #f)) (tasks:wait-on-journal (lock-queue:db-dat-get-path dbdat) 1200 remove: #t waiting-msg: "lock-queue:get-lock, waiting on journal") (let* ((res #f) (db (lock-queue:db-dat-get-db dbdat)) (lckqry (dbi:prepare db "SELECT test_id,run_lock FROM runlocks WHERE run_lock='locked';")) (mklckqry (dbi:prepare db "INSERT INTO runlocks (test_id,run_lock) VALUES (?,'locked');"))) (let ((result (handle-exceptions exn (begin (debug:print 0 *default-log-port* "WARNING: failed to get queue lock. Removing lock db and returning fail") ;; Will try again in a few seconds") (debug:print 0 *default-log-port* " message: " ((condition-property-accessor 'exn 'message) exn)) (thread-sleep! 10) ;; (if (> count 0) ;; #f ;; (lock-queue:get-lock dbdat test-id count: (- count 1)) - give up on retries ;; (begin ;; never recovered, remote the lock file and return #f, no lock obtained (lock-queue:delete-lock-db dbdat) #f) (dbi:with-transaction db (lambda () (dbi:for-each-row (lambda (output) (lambda (tid lockstate) (set! res (list tid lockstate)))) lckqry) (if res (if (equal? (car res) test-id) #t ;; already have the lock #f) (begin (dbi:exec mklckqry test-id) |
︙ | ︙ |
Modified portlogger.scm from [581ebea116] to [40aae0c748].
︙ | ︙ | |||
8 9 10 11 12 13 14 15 16 17 18 19 20 21 | ;; implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR ;; PURPOSE. (require-extension (srfi 18) extras tcp s11n) (use sqlite3 srfi-1 posix srfi-69 hostinfo dot-locking z3) (import (prefix sqlite3 sqlite3:)) (import (prefix dbi dbi:)) (declare (unit portlogger)) (declare (uses db)) ;; lsof -i | > | 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | ;; implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR ;; PURPOSE. (require-extension (srfi 18) extras tcp s11n) (use sqlite3 srfi-1 posix srfi-69 hostinfo dot-locking z3) (import (prefix sqlite3 sqlite3:)) (include "/nfs/site/disks/icf_fdk_cw_gwa002/srehman/fossil/dbi/dbi.scm") (import (prefix dbi dbi:)) (declare (unit portlogger)) (declare (uses db)) ;; lsof -i |
︙ | ︙ | |||
62 63 64 65 66 67 68 | (debug:print 0 *default-log-port* " message: " ((condition-property-accessor 'exn 'message) exn)) (debug:print 0 *default-log-port* "exn=" (condition->list exn)) (if (file-exists? fname)(delete-file fname)) ;; brutally get rid of it (print-call-chain (current-error-port))) (let* (;; (lock (obtain-dot-lock fname 2 9 10)) (db (portlogger:open-db fname)) (res (apply proc db params))) | | | | | | | | | | | | | | 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 | (debug:print 0 *default-log-port* " message: " ((condition-property-accessor 'exn 'message) exn)) (debug:print 0 *default-log-port* "exn=" (condition->list exn)) (if (file-exists? fname)(delete-file fname)) ;; brutally get rid of it (print-call-chain (current-error-port))) (let* (;; (lock (obtain-dot-lock fname 2 9 10)) (db (portlogger:open-db fname)) (res (apply proc db params))) (dbi:close db) ;; (release-dot-lock fname) res)))) ;; (fold-row PROC INIT DATABASE SQL . PARAMETERS) (define (portlogger:take-port db portnum) (let* ((qry1 (dbi:prepare db "INSERT INTO ports (port,state) VALUES (?,?);")) (qry2 (dbi:prepare db "UPDATE ports SET state=?,update_time=strftime('%s','now') WHERE port=?;")) (qry3 (dbi:prepare db "SELECT state FROM ports WHERE port=?;")) (res (dbi:with-transaction db (lambda () ;; (fold-row (lambda (var curr) (or var curr)) #f db "SELECT var FROM foo WHERE id=100;") (let* ((curr #f) (res #f)) (set! curr (dbi:fold-row (lambda (var curr) (or curr var curr)) "not-tried" qry3 portnum)) ;; (print "curr=" curr) (set! res (case (string->symbol curr) ((released) (dbi:execute qry2 "taken" portnum) 'taken) ((not-tried) (dbi:execute qry1 portnum "taken") 'taken) ((taken) 'already-taken) ((failed) 'failed) (else 'error))) ;; (print "res=" res) res))))) (dbi:close qry1) (dbi:close qry2) (dbi:close qry3) res)) (define (portlogger:get-prev-used-port db) (handle-exceptions exn (begin (debug:print 0 *default-log-port* "EXCEPTION: portlogger database probably overloaded or unreadable. If you see this message again remove /tmp/.$USER-portlogger.db") (debug:print 0 *default-log-port* " message: " ((condition-property-accessor 'exn 'message) exn)) (debug:print 0 *default-log-port* "exn=" (condition->list exn)) (print-call-chain (current-error-port)) (debug:print 0 *default-log-port* "Continuing anyway.") #f) (dbi:fold-row (lambda (var curr) (or curr var curr)) #f db "SELECT (port) FROM ports WHERE state='released' LIMIT 1;"))) (define (portlogger:find-port db) |
︙ | ︙ | |||
137 138 139 140 141 142 143 | (debug:print 0 *default-log-port* "Continuing anyway.")) (portlogger:take-port db portnum)) portnum)) ;; set port to "released", "failed" etc. ;; (define (portlogger:set-port db portnum value) | | | | 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 | (debug:print 0 *default-log-port* "Continuing anyway.")) (portlogger:take-port db portnum)) portnum)) ;; set port to "released", "failed" etc. ;; (define (portlogger:set-port db portnum value) (dbi:execute db "UPDATE ports SET state=?,update_time=strftime('%s','now') WHERE port=?;" value portnum)) ;; set port to failed (attempted to take but got error) ;; (define (portlogger:set-failed db portnum) (dbi:execute db "UPDATE ports SET state='failed',fail_count=fail_count+1,update_time=strftime('%s','now') WHERE port=?;" portnum)) ;;====================================================================== ;; MAIN ;;====================================================================== (define (portlogger:main . args) (let* ((dbfname (conc "/tmp/." (current-user-name) "-portlogger.db")) |
︙ | ︙ | |||
172 173 174 175 176 177 178 | ((set) (let ((port (cadr args)) (state (caddr args))) (portlogger:set-port db (if (number? port) port (string->number port)) state) state)) ((failed)(portlogger:set-failed db (string->number (cadr args))) 'failed))))) | | | 173 174 175 176 177 178 179 180 181 182 183 | ((set) (let ((port (cadr args)) (state (caddr args))) (portlogger:set-port db (if (number? port) port (string->number port)) state) state)) ((failed)(portlogger:set-failed db (string->number (cadr args))) 'failed))))) (dbi:close db) result)) ;; (print (apply portlogger:main (cdr (argv)))) |
Modified sdb.scm from [0256a92e53] to [ede55c5109].
︙ | ︙ | |||
53 54 55 56 57 58 59 | (dbi:exec sdb "INSERT OR IGNORE INTO strs (str) VALUES (?);" str)) (define (sdb:string->id sdb str-cache str) (let ((id (hash-table-ref/default str-cache str #f))) (if (not id) (dbi:for-each-row (lambda (sid) | | | | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 | (dbi:exec sdb "INSERT OR IGNORE INTO strs (str) VALUES (?);" str)) (define (sdb:string->id sdb str-cache str) (let ((id (hash-table-ref/default str-cache str #f))) (if (not id) (dbi:for-each-row (lambda (sid) (set! id (vector-ref sid 0)) (hash-table-set! str-cache str id)) sdb "SELECT id FROM strs WHERE str=?;" str)) id)) (define (sdb:id->string sdb id-cache id) (let ((str (hash-table-ref/default id-cache id #f))) (if (not str) (dbi:for-each-row (lambda (istr) (set! str (vector-ref istr 0)) (hash-table-set! id-cache id str)) sdb "SELECT str FROM strs WHERE id=?;" id)) str)) ;; Numbers get passed though in both directions ;; |
︙ | ︙ |
Modified tests.scm from [63786038c0] to [9894ea1171].
︙ | ︙ | |||
11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ;;====================================================================== ;; Tests ;;====================================================================== (use sqlite3 srfi-1 posix regex regex-case srfi-69 dot-locking tcp directory-utils) (import (prefix sqlite3 sqlite3:)) (require-library stml) (declare (unit tests)) (declare (uses lock-queue)) (declare (uses db)) (declare (uses tdb)) (declare (uses common)) | > > > | 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | ;;====================================================================== ;; Tests ;;====================================================================== (use sqlite3 srfi-1 posix regex regex-case srfi-69 dot-locking tcp directory-utils) (import (prefix sqlite3 sqlite3:)) (include "/nfs/site/disks/icf_fdk_cw_gwa002/srehman/fossil/dbi/dbi.scm") (import (prefix dbi dbi:)) (require-library stml) (declare (unit tests)) (declare (uses lock-queue)) (declare (uses db)) (declare (uses tdb)) (declare (uses common)) |
︙ | ︙ | |||
1327 1328 1329 1330 1331 1332 1333 | (let* ((testdat (rmt:get-test-info-by-id run-id test-id))) (and testdat (equal? (test:get-state testdat) "KILLREQ")))) (define (test:tdb-get-rundat-count tdb) (if tdb (let ((res 0)) | | | | 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 | (let* ((testdat (rmt:get-test-info-by-id run-id test-id))) (and testdat (equal? (test:get-state testdat) "KILLREQ")))) (define (test:tdb-get-rundat-count tdb) (if tdb (let ((res 0)) (dbi:for-each-row (lambda (count) (set! res (vector-ref count 0))) tdb "SELECT count(id) FROM test_rundat;") res)) 0) (define (tests:update-central-meta-info run-id test-id cpuload diskfree minutes uname hostname) (rmt:general-call 'update-test-rundat run-id test-id (current-seconds) (or cpuload -1)(or diskfree -1) -1 (or minutes -1)) |
︙ | ︙ |