1/* 2 2022-08-29 3 4 The author disclaims copyright to this source code. In place of a 5 legal notice, here is a blessing: 6 7 * May you do good and not evil. 8 * May you find forgiveness for yourself and forgive others. 9 * May you share freely, never taking more than you give. 10 11 *********************************************************************** 12 13 A basic batch SQL runner for sqlite3-api.js. This file must be run in 14 main JS thread and sqlite3.js must have been loaded before it. 15*/ 16'use strict'; 17(function(){ 18 const toss = function(...args){throw new Error(args.join(' '))}; 19 const warn = console.warn.bind(console); 20 let sqlite3; 21 const urlParams = new URL(self.location.href).searchParams; 22 const cacheSize = (()=>{ 23 if(urlParams.has('cachesize')) return +urlParams.get('cachesize'); 24 return 200; 25 })(); 26 27 /** Throws if the given sqlite3 result code is not 0. */ 28 const checkSqliteRc = (dbh,rc)=>{ 29 if(rc) toss("Prepare failed:",sqlite3.capi.sqlite3_errmsg(dbh)); 30 }; 31 32 const sqlToDrop = [ 33 "SELECT type,name FROM sqlite_schema ", 34 "WHERE name NOT LIKE 'sqlite\\_%' escape '\\' ", 35 "AND name NOT LIKE '\\_%' escape '\\'" 36 ].join(''); 37 38 const clearDbWebSQL = function(db){ 39 db.handle.transaction(function(tx){ 40 const onErr = (e)=>console.error(e); 41 const callback = function(tx, result){ 42 const rows = result.rows; 43 let i, n; 44 i = n = rows.length; 45 while(i--){ 46 const row = rows.item(i); 47 const name = JSON.stringify(row.name); 48 const type = row.type; 49 switch(type){ 50 case 'index': case 'table': 51 case 'trigger': case 'view': { 52 const sql2 = 'DROP '+type+' '+name; 53 tx.executeSql(sql2, [], ()=>{}, onErr); 54 break; 55 } 56 default: 57 warn("Unhandled db entry type:",type,'name =',name); 58 break; 59 } 60 } 61 }; 62 tx.executeSql(sqlToDrop, [], callback, onErr); 63 db.handle.changeVersion(db.handle.version, "", ()=>{}, onErr, ()=>{}); 64 }); 65 }; 66 67 const clearDbSqlite = function(db){ 68 // This would be SO much easier with the oo1 API, but we specifically want to 69 // inject metrics we can't get via that API, and we cannot reliably (OPFS) 70 // open the same DB twice to clear it using that API, so... 71 const rc = sqlite3.wasm.exports.sqlite3_wasm_db_reset(db.handle); 72 App.logHtml("reset db rc =",rc,db.id, db.filename); 73 }; 74 75 76 const E = (s)=>document.querySelector(s); 77 const App = { 78 e: { 79 output: E('#test-output'), 80 selSql: E('#sql-select'), 81 btnRun: E('#sql-run'), 82 btnRunNext: E('#sql-run-next'), 83 btnRunRemaining: E('#sql-run-remaining'), 84 btnExportMetrics: E('#export-metrics'), 85 btnClear: E('#output-clear'), 86 btnReset: E('#db-reset'), 87 cbReverseLog: E('#cb-reverse-log-order'), 88 selImpl: E('#select-impl'), 89 fsToolbar: E('#toolbar') 90 }, 91 db: Object.create(null), 92 dbs: Object.create(null), 93 cache:{}, 94 log: console.log.bind(console), 95 warn: console.warn.bind(console), 96 cls: function(){this.e.output.innerHTML = ''}, 97 logHtml2: function(cssClass,...args){ 98 const ln = document.createElement('div'); 99 if(cssClass) ln.classList.add(cssClass); 100 ln.append(document.createTextNode(args.join(' '))); 101 this.e.output.append(ln); 102 //this.e.output.lastElementChild.scrollIntoViewIfNeeded(); 103 }, 104 logHtml: function(...args){ 105 console.log(...args); 106 if(1) this.logHtml2('', ...args); 107 }, 108 logErr: function(...args){ 109 console.error(...args); 110 if(1) this.logHtml2('error', ...args); 111 }, 112 113 execSql: async function(name,sql){ 114 const db = this.getSelectedDb(); 115 const banner = "========================================"; 116 this.logHtml(banner, 117 "Running",name,'('+sql.length,'bytes) using',db.id); 118 const capi = this.sqlite3.capi, wasm = this.sqlite3.wasm; 119 let pStmt = 0, pSqlBegin; 120 const stack = wasm.scopedAllocPush(); 121 const metrics = db.metrics = Object.create(null); 122 metrics.prepTotal = metrics.stepTotal = 0; 123 metrics.stmtCount = 0; 124 metrics.malloc = 0; 125 metrics.strcpy = 0; 126 this.blockControls(true); 127 if(this.gotErr){ 128 this.logErr("Cannot run SQL: error cleanup is pending."); 129 return; 130 } 131 // Run this async so that the UI can be updated for the above header... 132 const endRun = ()=>{ 133 metrics.evalSqlEnd = performance.now(); 134 metrics.evalTimeTotal = (metrics.evalSqlEnd - metrics.evalSqlStart); 135 this.logHtml(db.id,"metrics:",JSON.stringify(metrics, undefined, ' ')); 136 this.logHtml("prepare() count:",metrics.stmtCount); 137 this.logHtml("Time in prepare_v2():",metrics.prepTotal,"ms", 138 "("+(metrics.prepTotal / metrics.stmtCount),"ms per prepare())"); 139 this.logHtml("Time in step():",metrics.stepTotal,"ms", 140 "("+(metrics.stepTotal / metrics.stmtCount),"ms per step())"); 141 this.logHtml("Total runtime:",metrics.evalTimeTotal,"ms"); 142 this.logHtml("Overhead (time - prep - step):", 143 (metrics.evalTimeTotal - metrics.prepTotal - metrics.stepTotal)+"ms"); 144 this.logHtml(banner,"End of",name); 145 }; 146 147 let runner; 148 if('websql'===db.id){ 149 const who = this; 150 runner = function(resolve, reject){ 151 /* WebSQL cannot execute multiple statements, nor can it execute SQL without 152 an explicit transaction. Thus we have to do some fragile surgery on the 153 input SQL. Since we're only expecting carefully curated inputs, the hope is 154 that this will suffice. PS: it also can't run most SQL functions, e.g. even 155 instr() results in "not authorized". */ 156 if('string'!==typeof sql){ // assume TypedArray 157 sql = new TextDecoder().decode(sql); 158 } 159 sql = sql.replace(/-- [^\n]+\n/g,''); // comment lines interfere with our split() 160 const sqls = sql.split(/;+\n/); 161 const rxBegin = /^BEGIN/i, rxCommit = /^COMMIT/i; 162 try { 163 const nextSql = ()=>{ 164 let x = sqls.shift(); 165 while(sqls.length && !x) x = sqls.shift(); 166 return x && x.trim(); 167 }; 168 const who = this; 169 const transaction = function(tx){ 170 try { 171 let s; 172 /* Try to approximate the spirit of the input scripts 173 by running batches bound by BEGIN/COMMIT statements. */ 174 for(s = nextSql(); !!s; s = nextSql()){ 175 if(rxBegin.test(s)) continue; 176 else if(rxCommit.test(s)) break; 177 //console.log("websql sql again",sqls.length, s); 178 ++metrics.stmtCount; 179 const t = performance.now(); 180 tx.executeSql(s,[], ()=>{}, (t,e)=>{ 181 console.error("WebSQL error",e,"SQL =",s); 182 who.logErr(e.message); 183 //throw e; 184 return false; 185 }); 186 metrics.stepTotal += performance.now() - t; 187 } 188 }catch(e){ 189 who.logErr("transaction():",e.message); 190 throw e; 191 } 192 }; 193 const n = sqls.length; 194 const nextBatch = function(){ 195 if(sqls.length){ 196 console.log("websql sqls.length",sqls.length,'of',n); 197 db.handle.transaction(transaction, (e)=>{ 198 who.logErr("Ignoring and contiuing:",e.message) 199 //reject(e); 200 return false; 201 }, nextBatch); 202 }else{ 203 resolve(who); 204 } 205 }; 206 metrics.evalSqlStart = performance.now(); 207 nextBatch(); 208 }catch(e){ 209 //this.gotErr = e; 210 console.error("websql error:",e); 211 who.logErr(e.message); 212 //reject(e); 213 } 214 }.bind(this); 215 }else{/*sqlite3 db...*/ 216 runner = function(resolve, reject){ 217 metrics.evalSqlStart = performance.now(); 218 try { 219 let t; 220 let sqlByteLen = sql.byteLength; 221 const [ppStmt, pzTail] = wasm.scopedAllocPtr(2); 222 t = performance.now(); 223 pSqlBegin = wasm.scopedAlloc( sqlByteLen + 1/*SQL + NUL*/) || toss("alloc(",sqlByteLen,") failed"); 224 metrics.malloc = performance.now() - t; 225 metrics.byteLength = sqlByteLen; 226 let pSql = pSqlBegin; 227 const pSqlEnd = pSqlBegin + sqlByteLen; 228 t = performance.now(); 229 wasm.heap8().set(sql, pSql); 230 wasm.setMemValue(pSql + sqlByteLen, 0); 231 metrics.strcpy = performance.now() - t; 232 let breaker = 0; 233 while(pSql && wasm.getMemValue(pSql,'i8')){ 234 wasm.setPtrValue(ppStmt, 0); 235 wasm.setPtrValue(pzTail, 0); 236 t = performance.now(); 237 let rc = capi.sqlite3_prepare_v3( 238 db.handle, pSql, sqlByteLen, 0, ppStmt, pzTail 239 ); 240 metrics.prepTotal += performance.now() - t; 241 checkSqliteRc(db.handle, rc); 242 pStmt = wasm.getPtrValue(ppStmt); 243 pSql = wasm.getPtrValue(pzTail); 244 sqlByteLen = pSqlEnd - pSql; 245 if(!pStmt) continue/*empty statement*/; 246 ++metrics.stmtCount; 247 t = performance.now(); 248 rc = capi.sqlite3_step(pStmt); 249 capi.sqlite3_finalize(pStmt); 250 pStmt = 0; 251 metrics.stepTotal += performance.now() - t; 252 switch(rc){ 253 case capi.SQLITE_ROW: 254 case capi.SQLITE_DONE: break; 255 default: checkSqliteRc(db.handle, rc); toss("Not reached."); 256 } 257 } 258 resolve(this); 259 }catch(e){ 260 if(pStmt) capi.sqlite3_finalize(pStmt); 261 //this.gotErr = e; 262 reject(e); 263 }finally{ 264 capi.sqlite3_exec(db.handle,"rollback;",0,0,0); 265 wasm.scopedAllocPop(stack); 266 } 267 }.bind(this); 268 } 269 let p; 270 if(1){ 271 p = new Promise(function(res,rej){ 272 setTimeout(()=>runner(res, rej), 50)/*give UI a chance to output the "running" banner*/; 273 }); 274 }else{ 275 p = new Promise(runner); 276 } 277 return p.catch( 278 (e)=>this.logErr("Error via execSql("+name+",...):",e.message) 279 ).finally(()=>{ 280 endRun(); 281 this.blockControls(false); 282 }); 283 }, 284 285 clearDb: function(){ 286 const db = this.getSelectedDb(); 287 if('websql'===db.id){ 288 this.logErr("TODO: clear websql db."); 289 return; 290 } 291 if(!db.handle) return; 292 const capi = this.sqlite3, wasm = this.sqlite3.wasm; 293 //const scope = wasm.scopedAllocPush( 294 this.logErr("TODO: clear db"); 295 }, 296 297 /** 298 Loads batch-runner.list and populates the selection list from 299 it. Returns a promise which resolves to nothing in particular 300 when it completes. Only intended to be run once at the start 301 of the app. 302 */ 303 loadSqlList: async function(){ 304 const sel = this.e.selSql; 305 sel.innerHTML = ''; 306 this.blockControls(true); 307 const infile = 'batch-runner.list'; 308 this.logHtml("Loading list of SQL files:", infile); 309 let txt; 310 try{ 311 const r = await fetch(infile); 312 if(404 === r.status){ 313 toss("Missing file '"+infile+"'."); 314 } 315 if(!r.ok) toss("Loading",infile,"failed:",r.statusText); 316 txt = await r.text(); 317 const warning = E('#warn-list'); 318 if(warning) warning.remove(); 319 }catch(e){ 320 this.logErr(e.message); 321 throw e; 322 }finally{ 323 this.blockControls(false); 324 } 325 const list = txt.split(/\n+/); 326 let opt; 327 if(0){ 328 opt = document.createElement('option'); 329 opt.innerText = "Select file to evaluate..."; 330 opt.value = ''; 331 opt.disabled = true; 332 opt.selected = true; 333 sel.appendChild(opt); 334 } 335 list.forEach(function(fn){ 336 if(!fn) return; 337 opt = document.createElement('option'); 338 opt.value = fn; 339 opt.innerText = fn.split('/').pop(); 340 sel.appendChild(opt); 341 }); 342 this.logHtml("Loaded",infile); 343 }, 344 345 /** Fetch ./fn and return its contents as a Uint8Array. */ 346 fetchFile: async function(fn, cacheIt=false){ 347 if(cacheIt && this.cache[fn]) return this.cache[fn]; 348 this.logHtml("Fetching",fn,"..."); 349 let sql; 350 try { 351 const r = await fetch(fn); 352 if(!r.ok) toss("Fetch failed:",r.statusText); 353 sql = new Uint8Array(await r.arrayBuffer()); 354 }catch(e){ 355 this.logErr(e.message); 356 throw e; 357 } 358 this.logHtml("Fetched",sql.length,"bytes from",fn); 359 if(cacheIt) this.cache[fn] = sql; 360 return sql; 361 }/*fetchFile()*/, 362 363 /** Disable or enable certain UI controls. */ 364 blockControls: function(disable){ 365 //document.querySelectorAll('.disable-during-eval').forEach((e)=>e.disabled = disable); 366 this.e.fsToolbar.disabled = disable; 367 }, 368 369 /** 370 Converts this.metrics() to a form which is suitable for easy conversion to 371 CSV. It returns an array of arrays. The first sub-array is the column names. 372 The 2nd and subsequent are the values, one per test file (only the most recent 373 metrics are kept for any given file). 374 */ 375 metricsToArrays: function(){ 376 const rc = []; 377 Object.keys(this.dbs).sort().forEach((k)=>{ 378 const d = this.dbs[k]; 379 const m = d.metrics; 380 delete m.evalSqlStart; 381 delete m.evalSqlEnd; 382 const mk = Object.keys(m).sort(); 383 if(!rc.length){ 384 rc.push(['db', ...mk]); 385 } 386 const row = [k.split('/').pop()/*remove dir prefix from filename*/]; 387 rc.push(row); 388 row.push(...mk.map((kk)=>m[kk])); 389 }); 390 return rc; 391 }, 392 393 metricsToBlob: function(colSeparator='\t'){ 394 const ar = [], ma = this.metricsToArrays(); 395 if(!ma.length){ 396 this.logErr("Metrics are empty. Run something."); 397 return; 398 } 399 ma.forEach(function(row){ 400 ar.push(row.join(colSeparator),'\n'); 401 }); 402 return new Blob(ar); 403 }, 404 405 downloadMetrics: function(){ 406 const b = this.metricsToBlob(); 407 if(!b) return; 408 const url = URL.createObjectURL(b); 409 const a = document.createElement('a'); 410 a.href = url; 411 a.download = 'batch-runner-js-'+((new Date().getTime()/1000) | 0)+'.csv'; 412 this.logHtml("Triggering download of",a.download); 413 document.body.appendChild(a); 414 a.click(); 415 setTimeout(()=>{ 416 document.body.removeChild(a); 417 URL.revokeObjectURL(url); 418 }, 500); 419 }, 420 421 /** 422 Fetch file fn and eval it as an SQL blob. This is an async 423 operation and returns a Promise which resolves to this 424 object on success. 425 */ 426 evalFile: async function(fn){ 427 const sql = await this.fetchFile(fn); 428 return this.execSql(fn,sql); 429 }/*evalFile()*/, 430 431 /** 432 Clears all DB tables in all _opened_ databases. Because of 433 disparities between backends, we cannot simply "unlink" the 434 databases to clean them up. 435 */ 436 clearStorage: function(onlySelectedDb=false){ 437 const list = onlySelectedDb 438 ? [('boolean'===typeof onlySelectedDb) 439 ? this.dbs[this.e.selImpl.value] 440 : onlySelectedDb] 441 : Object.values(this.dbs); 442 for(let db of list){ 443 if(db && db.handle){ 444 this.logHtml("Clearing db",db.id); 445 db.clear(); 446 } 447 } 448 }, 449 450 /** 451 Fetches the handle of the db associated with 452 this.e.selImpl.value, opening it if needed. 453 */ 454 getSelectedDb: function(){ 455 if(!this.dbs.memdb){ 456 for(let opt of this.e.selImpl.options){ 457 const d = this.dbs[opt.value] = Object.create(null); 458 d.id = opt.value; 459 switch(d.id){ 460 case 'virtualfs': 461 d.filename = 'file:/virtualfs.sqlite3?vfs=unix-none'; 462 break; 463 case 'memdb': 464 d.filename = ':memory:'; 465 break; 466 case 'wasmfs-opfs': 467 d.filename = 'file:'+( 468 this.sqlite3.capi.sqlite3_wasmfs_opfs_dir() 469 )+'/wasmfs-opfs.sqlite3b'; 470 break; 471 case 'websql': 472 d.filename = 'websql.db'; 473 break; 474 default: 475 this.logErr("Unhandled db selection option (see details in the console).",opt); 476 toss("Unhandled db init option"); 477 } 478 } 479 }/*first-time init*/ 480 const dbId = this.e.selImpl.value; 481 const d = this.dbs[dbId]; 482 if(d.handle) return d; 483 if('websql' === dbId){ 484 d.handle = self.openDatabase('batch-runner', '0.1', 'foo', 1024 * 1024 * 50); 485 d.clear = ()=>clearDbWebSQL(d); 486 d.handle.transaction(function(tx){ 487 tx.executeSql("PRAGMA cache_size="+cacheSize); 488 App.logHtml(dbId,"cache_size =",cacheSize); 489 }); 490 }else{ 491 const capi = this.sqlite3.capi, wasm = this.sqlite3.wasm; 492 const stack = wasm.scopedAllocPush(); 493 let pDb = 0; 494 try{ 495 const oFlags = capi.SQLITE_OPEN_CREATE | capi.SQLITE_OPEN_READWRITE; 496 const ppDb = wasm.scopedAllocPtr(); 497 const rc = capi.sqlite3_open_v2(d.filename, ppDb, oFlags, null); 498 pDb = wasm.getPtrValue(ppDb) 499 if(rc) toss("sqlite3_open_v2() failed with code",rc); 500 capi.sqlite3_exec(pDb, "PRAGMA cache_size="+cacheSize, 0, 0, 0); 501 this.logHtml(dbId,"cache_size =",cacheSize); 502 }catch(e){ 503 if(pDb) capi.sqlite3_close_v2(pDb); 504 }finally{ 505 wasm.scopedAllocPop(stack); 506 } 507 d.handle = pDb; 508 d.clear = ()=>clearDbSqlite(d); 509 } 510 d.clear(); 511 this.logHtml("Opened db:",dbId,d.filename); 512 console.log("db =",d); 513 return d; 514 }, 515 516 run: function(sqlite3){ 517 delete this.run; 518 this.sqlite3 = sqlite3; 519 const capi = sqlite3.capi, wasm = sqlite3.wasm; 520 this.logHtml("Loaded module:",capi.sqlite3_libversion(), capi.sqlite3_sourceid()); 521 this.logHtml("WASM heap size =",wasm.heap8().length); 522 this.loadSqlList(); 523 if(capi.sqlite3_wasmfs_opfs_dir()){ 524 E('#warn-opfs').classList.remove('hidden'); 525 }else{ 526 E('#warn-opfs').remove(); 527 E('option[value=wasmfs-opfs]').disabled = true; 528 } 529 if('function' === typeof self.openDatabase){ 530 E('#warn-websql').classList.remove('hidden'); 531 }else{ 532 E('option[value=websql]').disabled = true; 533 E('#warn-websql').remove(); 534 } 535 const who = this; 536 if(this.e.cbReverseLog.checked){ 537 this.e.output.classList.add('reverse'); 538 } 539 this.e.cbReverseLog.addEventListener('change', function(){ 540 who.e.output.classList[this.checked ? 'add' : 'remove']('reverse'); 541 }, false); 542 this.e.btnClear.addEventListener('click', ()=>this.cls(), false); 543 this.e.btnRun.addEventListener('click', function(){ 544 if(!who.e.selSql.value) return; 545 who.evalFile(who.e.selSql.value); 546 }, false); 547 this.e.btnRunNext.addEventListener('click', function(){ 548 ++who.e.selSql.selectedIndex; 549 if(!who.e.selSql.value) return; 550 who.evalFile(who.e.selSql.value); 551 }, false); 552 this.e.btnReset.addEventListener('click', function(){ 553 who.clearStorage(true); 554 }, false); 555 this.e.btnExportMetrics.addEventListener('click', function(){ 556 who.logHtml2('warning',"Triggering download of metrics CSV. Check your downloads folder."); 557 who.downloadMetrics(); 558 //const m = who.metricsToArrays(); 559 //console.log("Metrics:",who.metrics, m); 560 }); 561 this.e.selImpl.addEventListener('change', function(){ 562 who.getSelectedDb(); 563 }); 564 this.e.btnRunRemaining.addEventListener('click', async function(){ 565 let v = who.e.selSql.value; 566 const timeStart = performance.now(); 567 while(v){ 568 await who.evalFile(v); 569 if(who.gotError){ 570 who.logErr("Error handling script",v,":",who.gotError.message); 571 break; 572 } 573 ++who.e.selSql.selectedIndex; 574 v = who.e.selSql.value; 575 } 576 const timeTotal = performance.now() - timeStart; 577 who.logHtml("Run-remaining time:",timeTotal,"ms ("+(timeTotal/1000/60)+" minute(s))"); 578 who.clearStorage(); 579 }, false); 580 }/*run()*/ 581 }/*App*/; 582 583 self.sqlite3TestModule.initSqlite3().then(function(sqlite3_){ 584 sqlite3 = sqlite3_; 585 self.App = App /* only to facilitate dev console access */; 586 App.run(sqlite3); 587 }); 588})(); 589