ext/wasm/batch-runner.js

   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.poke(pSql + sqlByteLen, 0);
 231             metrics.strcpy = performance.now() - t;
 232             let breaker = 0;
 233             while(pSql && wasm.peek(pSql,'i8')){
 234               wasm.pokePtr(ppStmt, 0);
 235               wasm.pokePtr(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.peekPtr(ppStmt);
 243               pSql = wasm.peekPtr(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.peekPtr(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 })();