aboutsummaryrefslogtreecommitdiff
path: root/ext/wasm
diff options
context:
space:
mode:
authorstephan <stephan@noemail.net>2022-08-29 12:39:34 +0000
committerstephan <stephan@noemail.net>2022-08-29 12:39:34 +0000
commit2cae138fbae5404fc8fc5319ba52abdb0d723c0c (patch)
treeb206bf98e518d0400819c81693539a16734c1486 /ext/wasm
parentf2e624ea918d6509165a07205d08acd7ffe77927 (diff)
downloadsqlite-2cae138fbae5404fc8fc5319ba52abdb0d723c0c.tar.gz
sqlite-2cae138fbae5404fc8fc5319ba52abdb0d723c0c.zip
Add batch-runner.js for running batch SQL scripts with timing info.
FossilOrigin-Name: 11f3ed61150c5940da6c157e5063e70c3aa0628dfd0023c47bb65b00af74ab1f
Diffstat (limited to 'ext/wasm')
-rw-r--r--ext/wasm/GNUmakefile18
-rw-r--r--ext/wasm/batch-runner.html59
-rw-r--r--ext/wasm/batch-runner.js237
-rwxr-xr-xext/wasm/split-speedtest1-script.sh11
4 files changed, 323 insertions, 2 deletions
diff --git a/ext/wasm/GNUmakefile b/ext/wasm/GNUmakefile
index 40a69b200..25d1e9808 100644
--- a/ext/wasm/GNUmakefile
+++ b/ext/wasm/GNUmakefile
@@ -194,15 +194,17 @@ ifneq (0,$(ENABLE_WASMFS))
emcc.jsflags += -pthread -sWASMFS -sPTHREAD_POOL_SIZE=2
emcc.cflags += '-DSQLITE_DEFAULT_UNIX_VFS="unix-none"'
emcc.environment := $(emcc.environment),worker
+ emcc.jsflags += -sINITIAL_MEMORY=128450560
else
emcc.jsflags += -sALLOW_MEMORY_GROWTH
# emcc: warning: USE_PTHREADS + ALLOW_MEMORY_GROWTH may run non-wasm code
# slowly, see https://github.com/WebAssembly/design/issues/1271
# [-Wpthreads-mem-growth]
+ emcc.jsflags += -sINITIAL_MEMORY=13107200
+ #emcc.jsflags += -sINITIAL_MEMORY=64225280
+ # ^^^^ 64MB is not enough for WASMFS/OPFS test runs using batch-runner.js
endif
emcc.jsflags += $(emcc.environment)
-#emcc.jsflags += -sINITIAL_MEMORY=13107200
-emcc.jsflags += -sINITIAL_MEMORY=64225280
#emcc.jsflags += -sTOTAL_STACK=4194304
emcc.jsflags += -sEXPORT_NAME=sqlite3InitModule
emcc.jsflags += -sGLOBAL_BASE=4096 # HYPOTHETICALLY keep func table indexes from overlapping w/ heap addr.
@@ -285,6 +287,18 @@ all: $(sqlite3.js)
# End main Emscripten-based module build
########################################################################
+########################################################################
+# Bits for use with batch-runner.js...
+speedtest1 := ../../speedtest1
+$(speedtest1):
+ $(MAKE) -C ../.. speedtest1
+speedtest1.sql: $(speedtest1)
+ $(speedtest1) --script $@
+batch-sql.in := $(sort $(wildcard *.sql))
+batch-runner.list: $(batch-sql.in) $(MAKEFILE) speedtest1.sql
+ bash split-speedtest1-script.sh speedtest1.sql
+ ls -1 *.sql | sort > $@
+batch: batch-runner.list
########################################################################
# fiddle_remote is the remote destination for the fiddle app. It
diff --git a/ext/wasm/batch-runner.html b/ext/wasm/batch-runner.html
new file mode 100644
index 000000000..f5031f8fe
--- /dev/null
+++ b/ext/wasm/batch-runner.html
@@ -0,0 +1,59 @@
+<!doctype html>
+<html lang="en-us">
+ <head>
+ <meta charset="utf-8">
+ <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
+ <link rel="shortcut icon" href="data:image/x-icon;," type="image/x-icon">
+ <link rel="stylesheet" href="common/emscripten.css"/>
+ <link rel="stylesheet" href="common/testing.css"/>
+ <title>sqlite3-api batch SQL runner</title>
+ </head>
+ <body>
+ <header id='titlebar'><span>sqlite3-api batch SQL runner</span></header>
+ <!-- emscripten bits -->
+ <figure id="module-spinner">
+ <div class="spinner"></div>
+ <div class='center'><strong>Initializing app...</strong></div>
+ <div class='center'>
+ On a slow internet connection this may take a moment. If this
+ message displays for "a long time", intialization may have
+ failed and the JavaScript console may contain clues as to why.
+ </div>
+ </figure>
+ <div class="emscripten" id="module-status">Downloading...</div>
+ <div class="emscripten">
+ <progress value="0" max="100" id="module-progress" hidden='1'></progress>
+ </div><!-- /emscripten bits -->
+ <p class='warning'>ACHTUNG: this file requires a generated input list
+ file. Run "make batch" from this directory to generate it.
+ </p>
+ <p class='warning'>WARNING: if the WASMFS/OPFS layer crashes, this page may
+ become completely unresponsive and need to be closed and
+ reloaded to recover.
+ </p>
+ <hr>
+ <div>
+ <select id='sql-select'>
+ <option disabled selected>Populated via script code</option>
+ </select>
+ <button id='sql-run'>Run selected SQL</button>
+ <button id='output-clear'>Clear output</button>
+ </div>
+ <hr>
+ <div id='test-output'></div>
+
+ <script src="sqlite3.js"></script>
+ <script src="common/SqliteTestUtil.js"></script>
+ <script src="batch-runner.js"></script>
+ <style>
+ .warning { color: firebrick; }
+ #test-output {
+ border: 1px inset;
+ padding: 0.25em;
+ max-height: 20em;
+ overflow: auto;
+ white-space: break-spaces;
+ }
+ </style>
+ </body>
+</html>
diff --git a/ext/wasm/batch-runner.js b/ext/wasm/batch-runner.js
new file mode 100644
index 000000000..b412dc2ce
--- /dev/null
+++ b/ext/wasm/batch-runner.js
@@ -0,0 +1,237 @@
+/*
+ 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 running 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 T = self.SqliteTestUtil;
+ const toss = function(...args){throw new Error(args.join(' '))};
+ const debug = console.debug.bind(console);
+
+ const App = {
+ e: {
+ output: document.querySelector('#test-output'),
+ selSql: document.querySelector('#sql-select'),
+ btnRun: document.querySelector('#sql-run'),
+ btnClear: document.querySelector('#output-clear')
+ },
+ 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);
+ },
+ logHtml: function(...args){
+ console.log(...args);
+ if(1) this.logHtml2('', ...args);
+ },
+ logErr: function(...args){
+ console.error(...args);
+ if(1) this.logHtml2('error', ...args);
+ },
+
+ openDb: function(fn){
+ if(this.pDb){
+ toss("Already have an opened db.");
+ }
+ const capi = this.sqlite3.capi, wasm = capi.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(fn, ppDb, oFlags, null);
+ pDb = wasm.getPtrValue(ppDb)
+ }finally{
+ wasm.scopedAllocPop(stack);
+ }
+ this.logHtml("Opened db:",capi.sqlite3_db_filename(pDb, 'main'));
+ return this.pDb = pDb;
+ },
+
+ closeDb: function(){
+ if(this.pDb){
+ this.sqlite3.capi.sqlite3_close_v2(this.pDb);
+ this.pDb = undefined;
+ }
+ },
+
+ 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();
+ }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){
+ opt = document.createElement('option');
+ opt.value = opt.innerText = fn;
+ sel.appendChild(opt);
+ });
+ this.logHtml("Loaded",infile);
+ },
+
+ /** Fetch ./fn and return its contents as a Uint8Array. */
+ fetchFile: async function(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);
+ return sql;
+ },
+
+ checkRc: function(rc){
+ if(rc){
+ toss("Prepare failed:",this.sqlite3.capi.sqlite3_errmsg(this.pDb));
+ }
+ },
+
+ blockControls: function(block){
+ [
+ this.e.selSql, this.e.btnRun, this.e.btnClear
+ ].forEach((e)=>e.disabled = block);
+ },
+
+ /** Fetch ./fn and eval it as an SQL blob. */
+ evalFile: async function(fn){
+ const sql = await this.fetchFile(fn);
+ this.logHtml("Running",fn,'...');
+ const capi = this.sqlite3.capi, wasm = capi.wasm;
+ let pStmt = 0, pSqlBegin;
+ const stack = wasm.scopedAllocPush();
+ const metrics = Object.create(null);
+ metrics.prepTotal = metrics.stepTotal = 0;
+ metrics.stmtCount = 0;
+ this.blockControls(true);
+ // Use setTimeout() so that the above log messages run before the loop starts.
+ setTimeout((function(){
+ metrics.timeStart = performance.now();
+ try {
+ let t;
+ let sqlByteLen = sql.byteLength;
+ const [ppStmt, pzTail] = wasm.scopedAllocPtr(2);
+ pSqlBegin = wasm.alloc( sqlByteLen + 1/*SQL + NUL*/);
+ let pSql = pSqlBegin;
+ const pSqlEnd = pSqlBegin + sqlByteLen;
+ wasm.heap8().set(sql, pSql);
+ wasm.setMemValue(pSql + sqlByteLen, 0);
+ while(wasm.getMemValue(pSql,'i8')){
+ pStmt = 0;
+ wasm.setPtrValue(ppStmt, 0);
+ wasm.setPtrValue(pzTail, 0);
+ t = performance.now();
+ let rc = capi.sqlite3_prepare_v3(
+ this.pDb, pSql, sqlByteLen, 0, ppStmt, pzTail
+ );
+ metrics.prepTotal += performance.now() - t;
+ this.checkRc(rc);
+ ++metrics.stmtCount;
+ pStmt = wasm.getPtrValue(ppStmt);
+ pSql = wasm.getPtrValue(pzTail);
+ sqlByteLen = pSqlEnd - pSql;
+ if(!pStmt) continue/*empty statement*/;
+ t = performance.now();
+ rc = capi.sqlite3_step(pStmt);
+ metrics.stepTotal += performance.now() - t;
+ switch(rc){
+ case capi.SQLITE_ROW:
+ case capi.SQLITE_DONE: break;
+ default: this.checkRc(rc); toss("Not reached.");
+ }
+ }
+ }catch(e){
+ this.logErr(e.message);
+ throw e;
+ }finally{
+ wasm.dealloc(pSqlBegin);
+ wasm.scopedAllocPop(stack);
+ this.blockControls(false);
+ }
+ metrics.timeEnd = performance.now();
+ metrics.timeTotal = (metrics.timeEnd - metrics.timeStart);
+ this.logHtml("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.timeTotal,"ms");
+ this.logHtml("Overhead (time - prep - step):",
+ (metrics.timeTotal - metrics.prepTotal - metrics.stepTotal)+"ms");
+ }.bind(this)), 10);
+ },
+
+ run: function(sqlite3){
+ this.sqlite3 = sqlite3;
+ const capi = sqlite3.capi, wasm = capi.wasm;
+ this.logHtml("Loaded module:",capi.sqlite3_libversion(), capi.sqlite3_sourceid());
+ this.logHtml("WASM heap size =",wasm.heap8().length);
+ this.logHtml("WARNING: if the WASMFS/OPFS layer crashes, this page may",
+ "become unresponsive and need to be closed and ",
+ "reloaded to recover.");
+ const pDir = capi.sqlite3_web_persistent_dir();
+ const dbFile = pDir ? pDir+"/speedtest.db" : ":memory:";
+ if(pDir){
+ // We initially need a clean db file, so...
+ capi.sqlite3_wasm_vfs_unlink(dbFile);
+ }
+ this.openDb(dbFile);
+ this.loadSqlList();
+ const who = this;
+ 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);
+ }
+ }/*App*/;
+
+ self.sqlite3TestModule.initSqlite3().then(function(theEmccModule){
+ self._MODULE = theEmccModule /* this is only to facilitate testing from the console */;
+ App.run(theEmccModule.sqlite3);
+ });
+})();
diff --git a/ext/wasm/split-speedtest1-script.sh b/ext/wasm/split-speedtest1-script.sh
new file mode 100755
index 000000000..c7dae3eb6
--- /dev/null
+++ b/ext/wasm/split-speedtest1-script.sh
@@ -0,0 +1,11 @@
+#!/bin/bash
+# Expects $1 to be a (speedtest1 --script) output file. Output is a
+# series of SQL files extracted from that file.
+infile=${1:?arg = speedtest1 --script output file}
+testnums=$(grep -e '^-- begin test' "$infile" | cut -d' ' -f4)
+#echo testnums=$testnums
+for n in $testnums; do
+ ofile=$(printf "speedtest1-%03d.sql" $n)
+ sed -n -e "/^-- begin test $n\$/,/^-- end test $n\$/p" $infile > $ofile
+ echo -e "$n\t$ofile"
+done
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-16 02:31:08 +0000