sqlite/ext/wasm/batch-runner.js

/*
  2022-08-29

  The author disclaims copyright to this source code.  In place of a
  legal notice, here is a blessing:

  *   May you do good and not evil.
  *   May you find forgiveness for yourself and forgive others.
  *   May you share freely, never taking more than you give.

  ***********************************************************************

  A basic batch SQL runner for sqlite3-api.js. This file must be run in
  main JS thread and sqlite3.js must have been loaded before it.
*/
'use strict';
(function(){
  const toss = function(...args){throw new Error(args.join(' '))};
  const warn = console.warn.bind(console);
  let sqlite3;
  const urlParams = new URL(self.location.href).searchParams;
  const cacheSize = (()=>{
    if(urlParams.has('cachesize')) return +urlParams.get('cachesize');
    return 200;
  })();

  /** Throws if the given sqlite3 result code is not 0. */
  const checkSqliteRc = (dbh,rc)=>{
    if(rc) toss("Prepare failed:",sqlite3.capi.sqlite3_errmsg(dbh));
  };

  const sqlToDrop = [
    "SELECT type,name FROM sqlite_schema ",
    "WHERE name NOT LIKE 'sqlite\\_%' escape '\\' ",
    "AND name NOT LIKE '\\_%' escape '\\'"
  ].join('');
  
  const clearDbWebSQL = function(db){
    db.handle.transaction(function(tx){
      const onErr = (e)=>console.error(e);
      const callback = function(tx, result){
        const rows = result.rows;
        let i, n;
        i = n = rows.length;
        while(i--){
          const row = rows.item(i);
          const name = JSON.stringify(row.name);
          const type = row.type;
          switch(type){
              case 'index': case 'table':
              case 'trigger': case 'view': {
                const sql2 = 'DROP '+type+' '+name;
                tx.executeSql(sql2, [], ()=>{}, onErr);
                break;
              }
              default:
                warn("Unhandled db entry type:",type,'name =',name);
                break;
          }
        }
      };
      tx.executeSql(sqlToDrop, [], callback, onErr);
      db.handle.changeVersion(db.handle.version, "", ()=>{}, onErr, ()=>{});
    });
  };

  const clearDbSqlite = function(db){
    // This would be SO much easier with the oo1 API, but we specifically want to
    // inject metrics we can't get via that API, and we cannot reliably (OPFS)
    // open the same DB twice to clear it using that API, so...
    const rc = sqlite3.wasm.exports.sqlite3_wasm_db_reset(db.handle);
    App.logHtml("reset db rc =",rc,db.id, db.filename);
  };

  const E = (s)=>document.querySelector(s);
  const App = {
    e: {
      output: E('#test-output'),
      selSql: E('#sql-select'),
      btnRun: E('#sql-run'),
      btnRunNext: E('#sql-run-next'),
      btnRunRemaining: E('#sql-run-remaining'),
      btnExportMetrics: E('#export-metrics'),
      btnClear: E('#output-clear'),
      btnReset: E('#db-reset'),
      cbReverseLog: E('#cb-reverse-log-order'),
      selImpl: E('#select-impl'),
      fsToolbar: E('#toolbar')
    },
    db: Object.create(null),
    dbs: Object.create(null),
    cache:{},
    metrics: {
      fileCount: 0,
      runTimeMs: 0,
      prepareTimeMs: 0,
      stepTimeMs: 0,
      stmtCount: 0,
      strcpyMs: 0,
      sqlBytes: 0
    },
    log: console.log.bind(console),
    warn: console.warn.bind(console),
    cls: function(){this.e.output.innerHTML = ''},
    logHtml2: function(cssClass,...args){
      const ln = document.createElement('div');
      if(cssClass) ln.classList.add(cssClass);
      ln.append(document.createTextNode(args.join(' ')));
      this.e.output.append(ln);
      //this.e.output.lastElementChild.scrollIntoViewIfNeeded();
    },
    logHtml: function(...args){
      console.log(...args);
      if(1) this.logHtml2('', ...args);
    },
    logErr: function(...args){
      console.error(...args);
      if(1) this.logHtml2('error', ...args);
    },

    execSql: async function(name,sql){
      const db = this.getSelectedDb();
      const banner = "========================================";
      this.logHtml(banner,
                   "Running",name,'('+sql.length,'bytes) using',db.id);
      const capi = this.sqlite3.capi, wasm = this.sqlite3.wasm;
      let pStmt = 0, pSqlBegin;
      const metrics = db.metrics = Object.create(null);
      metrics.prepTotal = metrics.stepTotal = 0;
      metrics.stmtCount = 0;
      metrics.malloc = 0;
      metrics.strcpy = 0;
      this.blockControls(true);
      if(this.gotErr){
        this.logErr("Cannot run SQL: error cleanup is pending.");
        return;
      }
      // Run this async so that the UI can be updated for the above header...
      const endRun = ()=>{
        metrics.evalSqlEnd = performance.now();
        metrics.evalTimeTotal = (metrics.evalSqlEnd - metrics.evalSqlStart);
        this.logHtml(db.id,"metrics:",JSON.stringify(metrics, undefined, ' '));
        this.logHtml("prepare() count:",metrics.stmtCount);
        this.logHtml("Time in prepare_v2():",metrics.prepTotal,"ms",
                     "("+(metrics.prepTotal / metrics.stmtCount),"ms per prepare())");
        this.logHtml("Time in step():",metrics.stepTotal,"ms",
                     "("+(metrics.stepTotal / metrics.stmtCount),"ms per step())");
        this.logHtml("Total runtime:",metrics.evalTimeTotal,"ms");
        this.logHtml("Overhead (time - prep - step):",
                     (metrics.evalTimeTotal - metrics.prepTotal - metrics.stepTotal)+"ms");
        this.logHtml(banner,"End of",name);
        this.metrics.prepareTimeMs += metrics.prepTotal;
        this.metrics.stepTimeMs += metrics.stepTotal;
        this.metrics.stmtCount += metrics.stmtCount;
        this.metrics.strcpyMs += metrics.strcpy;
        this.metrics.sqlBytes += sql.length;
      };

      let runner;
      if('websql'===db.id){
        const who = this;
        runner = function(resolve, reject){
          /* WebSQL cannot execute multiple statements, nor can it execute SQL without
             an explicit transaction. Thus we have to do some fragile surgery on the
             input SQL. Since we're only expecting carefully curated inputs, the hope is
             that this will suffice. PS: it also can't run most SQL functions, e.g. even
             instr() results in "not authorized". */
          if('string'!==typeof sql){ // assume TypedArray
            sql = new TextDecoder().decode(sql);
          }
          sql = sql.replace(/-- [^\n]+\n/g,''); // comment lines interfere with our split()
          const sqls = sql.split(/;+\n/);
          const rxBegin = /^BEGIN/i, rxCommit = /^COMMIT/i;
          try {
            const nextSql = ()=>{
              let x = sqls.shift();
              while(sqls.length && !x) x = sqls.shift();
              return x && x.trim();
            };
            const who = this;
            const transaction = function(tx){
              try {
                let s;
                /* Try to approximate the spirit of the input scripts
                   by running batches bound by BEGIN/COMMIT statements. */
                for(s = nextSql(); !!s; s = nextSql()){
                  if(rxBegin.test(s)) continue;
                  else if(rxCommit.test(s)) break;
                  //console.log("websql sql again",sqls.length, s);
                  ++metrics.stmtCount;
                  const t = performance.now();
                  tx.executeSql(s,[], ()=>{}, (t,e)=>{
                    console.error("WebSQL error",e,"SQL =",s);
                    who.logErr(e.message);
                    //throw e;
                    return false;
                  });
                  metrics.stepTotal += performance.now() - t;
                }
              }catch(e){
                who.logErr("transaction():",e.message);
                throw e;
              }
            };
            const n = sqls.length;
            const nextBatch = function(){
              if(sqls.length){
                console.log("websql sqls.length",sqls.length,'of',n);
                db.handle.transaction(transaction, (e)=>{
                  who.logErr("Ignoring and contiuing:",e.message)
                  //reject(e);
                  return false;
                }, nextBatch);
              }else{
                resolve(who);
              }
            };
            metrics.evalSqlStart = performance.now();
            nextBatch();
          }catch(e){
            //this.gotErr = e;
            console.error("websql error:",e);
            who.logErr(e.message);
            //reject(e);
          }
        }.bind(this);
      }else{/*sqlite3 db...*/
        runner = function(resolve, reject){
          ++this.metrics.fileCount;
          metrics.evalSqlStart = performance.now();
          const stack = wasm.scopedAllocPush();
          try {
            let t;
            let sqlByteLen = sql.byteLength;
            const [ppStmt, pzTail] = wasm.scopedAllocPtr(2);
            t = performance.now();
            pSqlBegin = wasm.scopedAlloc( sqlByteLen + 1/*SQL + NUL*/) || toss("alloc(",sqlByteLen,") failed");
            metrics.malloc = performance.now() - t;
            metrics.byteLength = sqlByteLen;
            let pSql = pSqlBegin;
            const pSqlEnd = pSqlBegin + sqlByteLen;
            t = performance.now();
            wasm.heap8().set(sql, pSql);
            wasm.poke(pSql + sqlByteLen, 0);
            metrics.strcpy = performance.now() - t;
            let breaker = 0;
            while(pSql && wasm.peek(pSql,'i8')){
              wasm.pokePtr(ppStmt, 0);
              wasm.pokePtr(pzTail, 0);
              t = performance.now();
              let rc = capi.sqlite3_prepare_v3(
                db.handle, pSql, sqlByteLen, 0, ppStmt, pzTail
              );
              metrics.prepTotal += performance.now() - t;
              checkSqliteRc(db.handle, rc);
              pStmt = wasm.peekPtr(ppStmt);
              pSql = wasm.peekPtr(pzTail);
              sqlByteLen = pSqlEnd - pSql;
              if(!pStmt) continue/*empty statement*/;
              ++metrics.stmtCount;
              t = performance.now();
              rc = capi.sqlite3_step(pStmt);
              capi.sqlite3_finalize(pStmt);
              pStmt = 0;
              metrics.stepTotal += performance.now() - t;
              switch(rc){
                  case capi.SQLITE_ROW:
                  case capi.SQLITE_DONE: break;
                  default: checkSqliteRc(db.handle, rc); toss("Not reached.");
              }
            }
            resolve(this);
          }catch(e){
            if(pStmt) capi.sqlite3_finalize(pStmt);
            //this.gotErr = e;
            reject(e);
          }finally{
            capi.sqlite3_exec(db.handle,"rollback;",0,0,0);
            wasm.scopedAllocPop(stack);
          }
        }.bind(this);
      }
      let p;
      if(1){
        p = new Promise(function(res,rej){
          setTimeout(()=>runner(res, rej), 0)/*give UI a chance to output the "running" banner*/;
        });
      }else{
        p = new Promise(runner);
      }
      return p.catch(
        (e)=>this.logErr("Error via execSql("+name+",...):",e.message)
      ).finally(()=>{
        endRun();
        this.blockControls(false);
      });
    },
    
    clearDb: function(){
      const db = this.getSelectedDb();
      if('websql'===db.id){
        this.logErr("TODO: clear websql db.");
        return;
      }
      if(!db.handle) return;
      const capi = this.sqlite3, wasm = this.sqlite3.wasm;
      //const scope = wasm.scopedAllocPush(
      this.logErr("TODO: clear db");
    },
    
    /**
       Loads batch-runner.list and populates the selection list from
       it. Returns a promise which resolves to nothing in particular
       when it completes. Only intended to be run once at the start
       of the app.
     */
    loadSqlList: async function(){
      const sel = this.e.selSql;
      sel.innerHTML = '';
      this.blockControls(true);
      const infile = 'batch-runner.list';
      this.logHtml("Loading list of SQL files:", infile);
      let txt;
      try{
        const r = await fetch(infile);
        if(404 === r.status){
          toss("Missing file '"+infile+"'.");
        }
        if(!r.ok) toss("Loading",infile,"failed:",r.statusText);
        txt = await r.text();
        const warning = E('#warn-list');
        if(warning) warning.remove();
      }catch(e){
        this.logErr(e.message);
        throw e;
      }finally{
        this.blockControls(false);
      }
      const list = txt.split(/\n+/);
      let opt;
      if(0){
        opt = document.createElement('option');
        opt.innerText = "Select file to evaluate...";
        opt.value = '';
        opt.disabled = true;
        opt.selected = true;
        sel.appendChild(opt);
      }
      list.forEach(function(fn){
        if(!fn) return;
        opt = document.createElement('option');
        opt.value = fn;
        opt.innerText = fn.split('/').pop();
        sel.appendChild(opt);
      });
      this.logHtml("Loaded",infile);
    },

    /** Fetch ./fn and return its contents as a Uint8Array. */
    fetchFile: async function(fn, cacheIt=false){
      if(cacheIt && this.cache[fn]) return this.cache[fn];
      this.logHtml("Fetching",fn,"...");
      let sql;
      try {
        const r = await fetch(fn);
        if(!r.ok) toss("Fetch failed:",r.statusText);
        sql = new Uint8Array(await r.arrayBuffer());
      }catch(e){
        this.logErr(e.message);
        throw e;
      }
      this.logHtml("Fetched",sql.length,"bytes from",fn);
      if(cacheIt) this.cache[fn] = sql;
      return sql;
    }/*fetchFile()*/,

    /** Disable or enable certain UI controls. */
    blockControls: function(disable){
      //document.querySelectorAll('.disable-during-eval').forEach((e)=>e.disabled = disable);
      this.e.fsToolbar.disabled = disable;
    },

    /**
       Converts this.metrics() to a form which is suitable for easy conversion to
       CSV. It returns an array of arrays. The first sub-array is the column names.
       The 2nd and subsequent are the values, one per test file (only the most recent
       metrics are kept for any given file).
    */
    metricsToArrays: function(){
      const rc = [];
      Object.keys(this.dbs).sort().forEach((k)=>{
        const d = this.dbs[k];
        const m = d.metrics;
        delete m.evalSqlStart;
        delete m.evalSqlEnd;
        const mk = Object.keys(m).sort();
        if(!rc.length){
          rc.push(['db', ...mk]);
        }
        const row = [k.split('/').pop()/*remove dir prefix from filename*/];
        rc.push(row);
        row.push(...mk.map((kk)=>m[kk]));
      });
      return rc;
    },

    metricsToBlob: function(colSeparator='\t'){
      const ar = [], ma = this.metricsToArrays();
      if(!ma.length){
        this.logErr("Metrics are empty. Run something.");
        return;
      }
      ma.forEach(function(row){
        ar.push(row.join(colSeparator),'\n');
      });
      return new Blob(ar);
    },

    downloadMetrics: function(){
      const b = this.metricsToBlob();
      if(!b) return;
      const url = URL.createObjectURL(b);
      const a = document.createElement('a');
      a.href = url;
      a.download = 'batch-runner-js-'+((new Date().getTime()/1000) | 0)+'.csv';
      this.logHtml("Triggering download of",a.download);
      document.body.appendChild(a);
      a.click();
      setTimeout(()=>{
        document.body.removeChild(a);
        URL.revokeObjectURL(url);
      }, 500);
    },

    /**
       Fetch file fn and eval it as an SQL blob. This is an async
       operation and returns a Promise which resolves to this
       object on success.
    */
    evalFile: async function(fn){
      const sql = await this.fetchFile(fn);
      return this.execSql(fn,sql);
    }/*evalFile()*/,

    /**
       Clears all DB tables in all _opened_ databases. Because of
       disparities between backends, we cannot simply "unlink" the
       databases to clean them up.
    */
    clearStorage: function(onlySelectedDb=false){
      const list = onlySelectedDb
            ? [('boolean'===typeof onlySelectedDb)
                ? this.dbs[this.e.selImpl.value]
                : onlySelectedDb]
            : Object.values(this.dbs);
      for(let db of list){
        if(db && db.handle){
          this.logHtml("Clearing db",db.id);
          db.clear();
        }
      }
    },

    /**
       Fetches the handle of the db associated with
       this.e.selImpl.value, opening it if needed.
    */
    getSelectedDb: function(){
      if(!this.dbs.memdb){
        for(let opt of this.e.selImpl.options){
          const d = this.dbs[opt.value] = Object.create(null);
          d.id = opt.value;
          switch(d.id){
              case 'virtualfs':
                d.filename = 'file:/virtualfs.sqlite3?vfs=unix-none';
                break;
              case 'memdb':
                d.filename = ':memory:';
                break;
              case 'wasmfs-opfs':
                d.filename = 'file:'+(
                  this.sqlite3.capi.sqlite3_wasmfs_opfs_dir()
                )+'/wasmfs-opfs.sqlite3b';
                break;
              case 'websql':
                d.filename = 'websql.db';
                break;
              default:
                this.logErr("Unhandled db selection option (see details in the console).",opt);
                toss("Unhandled db init option");
          }
        }
      }/*first-time init*/
      const dbId = this.e.selImpl.value;
      const d = this.dbs[dbId];
      if(d.handle) return d;
      if('websql' === dbId){
        d.handle = self.openDatabase('batch-runner', '0.1', 'foo', 1024 * 1024 * 50);
        d.clear = ()=>clearDbWebSQL(d);
        d.handle.transaction(function(tx){
          tx.executeSql("PRAGMA cache_size="+cacheSize);
          App.logHtml(dbId,"cache_size =",cacheSize);
        });
      }else{
        const capi = this.sqlite3.capi, wasm = this.sqlite3.wasm;
        const stack = wasm.scopedAllocPush();
        let pDb = 0;
        try{
          const oFlags = capi.SQLITE_OPEN_CREATE | capi.SQLITE_OPEN_READWRITE;
          const ppDb = wasm.scopedAllocPtr();
          const rc = capi.sqlite3_open_v2(d.filename, ppDb, oFlags, null);
          pDb = wasm.peekPtr(ppDb)
          if(rc) toss("sqlite3_open_v2() failed with code",rc);
          capi.sqlite3_exec(pDb, "PRAGMA cache_size="+cacheSize, 0, 0, 0);
          this.logHtml(dbId,"cache_size =",cacheSize);
        }catch(e){
          if(pDb) capi.sqlite3_close_v2(pDb);
        }finally{
          wasm.scopedAllocPop(stack);
        }
        d.handle = pDb;
        d.clear = ()=>clearDbSqlite(d);
      }
      d.clear();
      this.logHtml("Opened db:",dbId,d.filename);
      console.log("db =",d);
      return d;
    },

    run: function(sqlite3){
      delete this.run;
      this.sqlite3 = sqlite3;
      const capi = sqlite3.capi, wasm = sqlite3.wasm;
      this.logHtml("Loaded module:",capi.sqlite3_libversion(), capi.sqlite3_sourceid());
      this.logHtml("WASM heap size =",wasm.heap8().length);
      this.loadSqlList();
      if(capi.sqlite3_wasmfs_opfs_dir()){
        E('#warn-opfs').classList.remove('hidden');
      }else{
        E('#warn-opfs').remove();
        E('option[value=wasmfs-opfs]').disabled = true;
      }
      if('function' === typeof self.openDatabase){
        E('#warn-websql').classList.remove('hidden');
      }else{
        E('option[value=websql]').disabled = true;
        E('#warn-websql').remove();
      }
      const who = this;
      if(this.e.cbReverseLog.checked){
        this.e.output.classList.add('reverse');
      }
      this.e.cbReverseLog.addEventListener('change', function(){
        who.e.output.classList[this.checked ? 'add' : 'remove']('reverse');
      }, false);
      this.e.btnClear.addEventListener('click', ()=>this.cls(), false);
      this.e.btnRun.addEventListener('click', function(){
        if(!who.e.selSql.value) return;
        who.evalFile(who.e.selSql.value);
      }, false);
      this.e.btnRunNext.addEventListener('click', function(){
        ++who.e.selSql.selectedIndex;
        if(!who.e.selSql.value) return;
        who.evalFile(who.e.selSql.value);
      }, false);
      this.e.btnReset.addEventListener('click', function(){
        who.clearStorage(true);
      }, false);
      this.e.btnExportMetrics.addEventListener('click', function(){
        who.logHtml2('warning',"Triggering download of metrics CSV. Check your downloads folder.");
        who.downloadMetrics();
        //const m = who.metricsToArrays();
        //console.log("Metrics:",who.metrics, m);
      });
      this.e.selImpl.addEventListener('change', function(){
        who.getSelectedDb();
      });
      this.e.btnRunRemaining.addEventListener('click', async function(){
        let v = who.e.selSql.value;
        const timeStart = performance.now();
        while(v){
          await who.evalFile(v);
          if(who.gotError){
            who.logErr("Error handling script",v,":",who.gotError.message);
            break;
          }
          ++who.e.selSql.selectedIndex;
          v = who.e.selSql.value;
        }
        const timeTotal = performance.now() - timeStart;
        who.logHtml("Run-remaining time:",timeTotal,"ms ("+(timeTotal/1000/60)+" minute(s))");
        who.clearStorage();
        App.metrics.runTimeMs = timeTotal;
        who.logHtml("Total metrics:",JSON.stringify(App.metrics,undefined,'  '));
      }, false);
    }/*run()*/
  }/*App*/;

  self.sqlite3TestModule.initSqlite3().then(function(sqlite3_){
    sqlite3 = sqlite3_;
    self.App = App /* only to facilitate dev console access */;
    App.run(sqlite3);
  });
})();