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