ext/wasm/batch-runner-sahpool.js

   1 /*
   2   2023-11-30
   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 the SAHPool VFS. This file must be run in
  14   a worker thread. This is not a full-featured app, just a way to get some
  15   measurements for batch execution of SQL for the OPFS SAH Pool VFS.
  16 */
  17 'use strict';
  18
  19 const wMsg = function(msgType,...args){
  20   postMessage({
  21     type: msgType,
  22     data: args
  23   });
  24 };
  25 const toss = function(...args){throw new Error(args.join(' '))};
  26 const warn = (...args)=>{ wMsg('warn',...args); };
  27 const error = (...args)=>{ wMsg('error',...args); };
  28 const log = (...args)=>{ wMsg('stdout',...args); }
  29 let sqlite3;
  30 const urlParams = new URL(globalThis.location.href).searchParams;
  31 const cacheSize = (()=>{
  32   if(urlParams.has('cachesize')) return +urlParams.get('cachesize');
  33   return 200;
  34 })();
  35
  36
  37 /** Throws if the given sqlite3 result code is not 0. */
  38 const checkSqliteRc = (dbh,rc)=>{
  39   if(rc) toss("Prepare failed:",sqlite3.capi.sqlite3_errmsg(dbh));
  40 };
  41
  42 const sqlToDrop = [
  43   "SELECT type,name FROM sqlite_schema ",
  44   "WHERE name NOT LIKE 'sqlite\\_%' escape '\\' ",
  45   "AND name NOT LIKE '\\_%' escape '\\'"
  46 ].join('');
  47
  48 const clearDbSqlite = function(db){
  49   // This would be SO much easier with the oo1 API, but we specifically want to
  50   // inject metrics we can't get via that API, and we cannot reliably (OPFS)
  51   // open the same DB twice to clear it using that API, so...
  52   const rc = sqlite3.wasm.exports.sqlite3_wasm_db_reset(db.handle);
  53   log("reset db rc =",rc,db.id, db.filename);
  54 };
  55
  56 const App = {
  57   db: undefined,
  58   cache:Object.create(null),
  59   log: log,
  60   warn: warn,
  61   error: error,
  62   metrics: {
  63     fileCount: 0,
  64     runTimeMs: 0,
  65     prepareTimeMs: 0,
  66     stepTimeMs: 0,
  67     stmtCount: 0,
  68     strcpyMs: 0,
  69     sqlBytes: 0
  70   },
  71   fileList: undefined,
  72   execSql: async function(name,sql){
  73     const db = this.db;
  74     const banner = "========================================";
  75     this.log(banner,
  76              "Running",name,'('+sql.length,'bytes)');
  77     const capi = this.sqlite3.capi, wasm = this.sqlite3.wasm;
  78     let pStmt = 0, pSqlBegin;
  79     const metrics = db.metrics = Object.create(null);
  80     metrics.prepTotal = metrics.stepTotal = 0;
  81     metrics.stmtCount = 0;
  82     metrics.malloc = 0;
  83     metrics.strcpy = 0;
  84     if(this.gotErr){
  85       this.error("Cannot run SQL: error cleanup is pending.");
  86       return;
  87     }
  88     // Run this async so that the UI can be updated for the above header...
  89     const endRun = ()=>{
  90       metrics.evalSqlEnd = performance.now();
  91       metrics.evalTimeTotal = (metrics.evalSqlEnd - metrics.evalSqlStart);
  92       this.log("metrics:",JSON.stringify(metrics, undefined, ' '));
  93       this.log("prepare() count:",metrics.stmtCount);
  94       this.log("Time in prepare_v2():",metrics.prepTotal,"ms",
  95                "("+(metrics.prepTotal / metrics.stmtCount),"ms per prepare())");
  96       this.log("Time in step():",metrics.stepTotal,"ms",
  97                "("+(metrics.stepTotal / metrics.stmtCount),"ms per step())");
  98       this.log("Total runtime:",metrics.evalTimeTotal,"ms");
  99       this.log("Overhead (time - prep - step):",
 100                (metrics.evalTimeTotal - metrics.prepTotal - metrics.stepTotal)+"ms");
 101       this.log(banner,"End of",name);
 102       this.metrics.prepareTimeMs += metrics.prepTotal;
 103       this.metrics.stepTimeMs += metrics.stepTotal;
 104       this.metrics.stmtCount += metrics.stmtCount;
 105       this.metrics.strcpyMs += metrics.strcpy;
 106       this.metrics.sqlBytes += sql.length;
 107     };
 108
 109     const runner = function(resolve, reject){
 110       ++this.metrics.fileCount;
 111       metrics.evalSqlStart = performance.now();
 112       const stack = wasm.scopedAllocPush();
 113       try {
 114         let t, rc;
 115         let sqlByteLen = sql.byteLength;
 116         const [ppStmt, pzTail] = wasm.scopedAllocPtr(2);
 117         t = performance.now();
 118         pSqlBegin = wasm.scopedAlloc( sqlByteLen + 1/*SQL + NUL*/) || toss("alloc(",sqlByteLen,") failed");
 119         metrics.malloc = performance.now() - t;
 120         metrics.byteLength = sqlByteLen;
 121         let pSql = pSqlBegin;
 122         const pSqlEnd = pSqlBegin + sqlByteLen;
 123         t = performance.now();
 124         wasm.heap8().set(sql, pSql);
 125         wasm.poke(pSql + sqlByteLen, 0);
 126         //log("SQL:",wasm.cstrToJs(pSql));
 127         metrics.strcpy = performance.now() - t;
 128         let breaker = 0;
 129         while(pSql && wasm.peek8(pSql)){
 130           wasm.pokePtr(ppStmt, 0);
 131           wasm.pokePtr(pzTail, 0);
 132           t = performance.now();
 133           rc = capi.sqlite3_prepare_v2(
 134             db.handle, pSql, sqlByteLen, ppStmt, pzTail
 135           );
 136           metrics.prepTotal += performance.now() - t;
 137           checkSqliteRc(db.handle, rc);
 138           pStmt = wasm.peekPtr(ppStmt);
 139           pSql = wasm.peekPtr(pzTail);
 140           sqlByteLen = pSqlEnd - pSql;
 141           if(!pStmt) continue/*empty statement*/;
 142           ++metrics.stmtCount;
 143           t = performance.now();
 144           rc = capi.sqlite3_step(pStmt);
 145           capi.sqlite3_finalize(pStmt);
 146           pStmt = 0;
 147           metrics.stepTotal += performance.now() - t;
 148           switch(rc){
 149             case capi.SQLITE_ROW:
 150             case capi.SQLITE_DONE: break;
 151             default: checkSqliteRc(db.handle, rc); toss("Not reached.");
 152           }
 153         }
 154         resolve(this);
 155       }catch(e){
 156         if(pStmt) capi.sqlite3_finalize(pStmt);
 157         this.gotErr = e;
 158         reject(e);
 159       }finally{
 160         capi.sqlite3_exec(db.handle,"rollback;",0,0,0);
 161         wasm.scopedAllocPop(stack);
 162       }
 163     }.bind(this);
 164     const p = new Promise(runner);
 165     return p.catch(
 166       (e)=>this.error("Error via execSql("+name+",...):",e.message)
 167     ).finally(()=>{
 168       endRun();
 169     });
 170   },
 171
 172   /**
 173      Loads batch-runner.list and populates the selection list from
 174      it. Returns a promise which resolves to nothing in particular
 175      when it completes. Only intended to be run once at the start
 176      of the app.
 177   */
 178   loadSqlList: async function(){
 179     const infile = 'batch-runner.list';
 180     this.log("Loading list of SQL files:", infile);
 181     let txt;
 182     try{
 183       const r = await fetch(infile);
 184       if(404 === r.status){
 185         toss("Missing file '"+infile+"'.");
 186       }
 187       if(!r.ok) toss("Loading",infile,"failed:",r.statusText);
 188       txt = await r.text();
 189     }catch(e){
 190       this.error(e.message);
 191       throw e;
 192     }
 193     App.fileList = txt.split(/\n+/).filter(x=>!!x);
 194     this.log("Loaded",infile);
 195   },
 196
 197   /** Fetch ./fn and return its contents as a Uint8Array. */
 198   fetchFile: async function(fn, cacheIt=false){
 199     if(cacheIt && this.cache[fn]) return this.cache[fn];
 200     this.log("Fetching",fn,"...");
 201     let sql;
 202     try {
 203       const r = await fetch(fn);
 204       if(!r.ok) toss("Fetch failed:",r.statusText);
 205       sql = new Uint8Array(await r.arrayBuffer());
 206     }catch(e){
 207       this.error(e.message);
 208       throw e;
 209     }
 210     this.log("Fetched",sql.length,"bytes from",fn);
 211     if(cacheIt) this.cache[fn] = sql;
 212     return sql;
 213   }/*fetchFile()*/,
 214
 215   /**
 216      Converts this.metrics() to a form which is suitable for easy conversion to
 217      CSV. It returns an array of arrays. The first sub-array is the column names.
 218      The 2nd and subsequent are the values, one per test file (only the most recent
 219      metrics are kept for any given file).
 220   */
 221   metricsToArrays: function(){
 222     const rc = [];
 223     Object.keys(this.dbs).sort().forEach((k)=>{
 224       const d = this.dbs[k];
 225       const m = d.metrics;
 226       delete m.evalSqlStart;
 227       delete m.evalSqlEnd;
 228       const mk = Object.keys(m).sort();
 229       if(!rc.length){
 230         rc.push(['db', ...mk]);
 231       }
 232       const row = [k.split('/').pop()/*remove dir prefix from filename*/];
 233       rc.push(row);
 234       row.push(...mk.map((kk)=>m[kk]));
 235     });
 236     return rc;
 237   },
 238
 239   metricsToBlob: function(colSeparator='\t'){
 240     const ar = [], ma = this.metricsToArrays();
 241     if(!ma.length){
 242       this.error("Metrics are empty. Run something.");
 243       return;
 244     }
 245     ma.forEach(function(row){
 246       ar.push(row.join(colSeparator),'\n');
 247     });
 248     return new Blob(ar);
 249   },
 250
 251   /**
 252      Fetch file fn and eval it as an SQL blob. This is an async
 253      operation and returns a Promise which resolves to this
 254      object on success.
 255   */
 256   evalFile: async function(fn){
 257     const sql = await this.fetchFile(fn);
 258     return this.execSql(fn,sql);
 259   }/*evalFile()*/,
 260
 261   /**
 262      Fetches the handle of the db associated with
 263      this.e.selImpl.value, opening it if needed.
 264   */
 265   initDb: function(){
 266     const capi = this.sqlite3.capi, wasm = this.sqlite3.wasm;
 267     const stack = wasm.scopedAllocPush();
 268     let pDb = 0;
 269     const d = Object.create(null);
 270     d.filename = "/batch.db";
 271     try{
 272       const oFlags = capi.SQLITE_OPEN_CREATE | capi.SQLITE_OPEN_READWRITE;
 273       const ppDb = wasm.scopedAllocPtr();
 274       const rc = capi.sqlite3_open_v2(d.filename, ppDb, oFlags, this.PoolUtil.vfsName);
 275       pDb = wasm.peekPtr(ppDb)
 276       if(rc) toss("sqlite3_open_v2() failed with code",rc);
 277       capi.sqlite3_exec(pDb, "PRAGMA cache_size="+cacheSize, 0, 0, 0);
 278       this.log("cache_size =",cacheSize);
 279     }catch(e){
 280       if(pDb) capi.sqlite3_close_v2(pDb);
 281       throw e;
 282     }finally{
 283       wasm.scopedAllocPop(stack);
 284     }
 285     d.handle = pDb;
 286     this.log("Opened db:",d.filename,'@',d.handle);
 287     return d;
 288   },
 289
 290   closeDb: function(){
 291     if(this.db.handle){
 292       this.sqlite3.capi.sqlite3_close_v2(this.db.handle);
 293       this.db.handle = undefined;
 294     }
 295   },
 296
 297   run: async function(sqlite3){
 298     delete this.run;
 299     this.sqlite3 = sqlite3;
 300     const capi = sqlite3.capi, wasm = sqlite3.wasm;
 301     this.log("Loaded module:",capi.sqlite3_libversion(), capi.sqlite3_sourceid());
 302     this.log("WASM heap size =",wasm.heap8().length);
 303     let timeStart;
 304     sqlite3.installOpfsSAHPoolVfs({
 305       clearOnInit: true, initialCapacity: 4,
 306       name: 'batch-sahpool',
 307       verbosity: 2
 308     }).then(PoolUtil=>{
 309       App.PoolUtil = PoolUtil;
 310       App.db = App.initDb();
 311     })
 312       .then(async ()=>this.loadSqlList())
 313       .then(async ()=>{
 314         timeStart = performance.now();
 315         for(let i = 0; i < App.fileList.length; ++i){
 316           const fn = App.fileList[i];
 317           await App.evalFile(fn);
 318           if(App.gotErr) throw App.gotErr;
 319         }
 320       })
 321       .then(()=>{
 322         App.metrics.runTimeMs = performance.now() - timeStart;
 323         App.log("total metrics:",JSON.stringify(App.metrics, undefined, ' '));
 324         App.log("Reload the page to run this again.");
 325         App.closeDb();
 326         App.PoolUtil.removeVfs();
 327       })
 328       .catch(e=>this.error("ERROR:",e));
 329   }/*run()*/
 330 }/*App*/;
 331
 332 let sqlite3Js = 'sqlite3.js';
 333 if(urlParams.has('sqlite3.dir')){
 334   sqlite3Js = urlParams.get('sqlite3.dir') + '/' + sqlite3Js;
 335 }
 336 importScripts(sqlite3Js);
 337 globalThis.sqlite3InitModule().then(async function(sqlite3_){
 338   log("Done initializing. Running batch runner...");
 339   sqlite3 = sqlite3_;
 340   App.run(sqlite3_);
 341 });