WASM+SQLite for Web Products

  • brew install cmake
  • brew install python@3.9
  • brew install md5sha1sum
  • brew install pyenv
  • brew update node
  • sudo apachectl start
% cat hello.c#include <stdio.h>int main(int argc, char **argv)
{
printf(“Hello, world\n”);
}
% emcc hello.c
% node a.out
Hello, world
  • The runtime tests (this verifies all the runtime features plus arithmetic, string processing and so forth)
  • The “demo” program (this demonstrates a variety of result set types, DDL, and some interesting algorithms coded in CQL)
# normal CQL command to generate the c file% cql --in demo.sql --cg demo.h demo.c --generate_copy# this is usually just “cc” but otherwise the same
# using the standard amalgam and the standard cqlrt.c unchanged
% emcc -I .../sqlite-3280000 .../sqlite-3280000/sqlite3-all.c demo.c cqlrt.c demo_client.c -o demo% node demoCQL data access demo: creating and reading from a table
result_set: row 0) 1 0 1 0 12 a name
result_set: row 1) 2 0 1 0 14 some name
result_set: row 2) 3 0 1 0 15 yet another name
result_set: row 3) 4 0 1 0 19 some name
result_set: row 4) 5 0 1 0 21 what name
result_set_copy: row 0) 2 0 1 0 14 some name
result_set_copy: row 1) 3 0 1 0 15 yet another name
result_set_copy: row 2) 4 0 1 0 19 some name
result_set_updated: row 0) 1 0 1 0 12 a name
result_set_updated: row 1) 2 0 1 0 14 some name
result_set_updated: row 2) 3 0 1 0 15 yet another name
result_set_updated: row 3) 4 0 1 0 19 some name
result_set_updated: row 4) 5 0 1 0 21 what name
....#
..#*..
..+####+.
.......+####.... +
..##+*##########+.++++
.+.##################+.
.............+###################+.+
..++..#.....*#####################+.
...+#######++#######################.
....+*################################.
#############################################...
....+*################################.
...+#######++#######################.
..++..#.....*#####################+.
.............+###################+.+
.+.##################+.
..##+*##########+.++++
.......+####.... +
..+####+.
..#*..
....#
+.
# preprocessing required because of macros
% cc -E -I test -x c -w run_test.sql >run_test_pre.sql
# normal CQL compilation
% cql — in run_test_pre.sql — cg run_test.h run_test.c — generate_copy — global_proc cql_startup
# same command line as “cc” note -DCQL_RUN_TEST enables mocks
% emcc -DCQL_RUN_TEST -I . -I .../sqlite-3280000 .../sqlite-3280000/sqlite3-all.c run_test.c cqlrt.c run_test_client.c test/cqltest.c result_set_extension.c -o test
% node testRunning C client test
Running cql_row_same client test
Running blob rowset test
Running ref comparison test
Running sparse blob rowset test
Running C client test with huge number of rows
Running cql finalize on error test
Running C one row result set test
Running column fetchers test
Running column encoded fetchers test
Running column encoded cursor fetchers test
Running column encoded out union fetchers test
Running column encoded multi out union fetchers test
Running column encoded turn off fetchers test
Running column some encoded field fetchers test
Running error case rowset test
Running autodrop rowset test (pass 1)
Running autodrop rowset test (pass 2)
Running autodrop rowset test (pass 3)
Running cql_bytebuf_open test
Running cql_bytebuf_alloc_within_bytebuf_exp_growth_cap test
Running cql_bytebuf_alloc_over_bytebuf_exp_growth_cap test
125 tests executed. 125 passed, 0 failed. 0 expectations failed of 2122.
program exited (with status: 0), but EXIT_RUNTIME is not set, so halting execution but not exiting the runtime or preventing further async execution (build with EXIT_RUNTIME=1, if you want a true shutdown)
  • We can build a stock sqlite
  • We can use all cqlrt features
  • We can use cql code gen (all forms of output are in this test)
% emcc -O2 ...
-rw-r--r-- 1 rmariani staff 93711 May 4 11:04 x.html
-rw-r--r-- 1 rmariani staff 87587 May 4 11:04 x.js
-rwxr-xr-x 1 rmariani staff 1076577 May 4 11:04 x.wasm
% emcc -O3 -Oz ...
-rw-r--r-- 1 rmariani staff 93711 May 4 11:05 x.html
-rw-r--r-- 1 rmariani staff 80277 May 4 11:05 x.js
-rwxr-xr-x 1 rmariani staff 694965 May 4 11:05 x.wasm
% emcc -O3 -Os ...
-rw-r--r-- 1 rmariani staff 93711 May 4 11:06 x.html
-rw-r--r-- 1 rmariani staff 80387 May 4 11:06 x.js
-rwxr-xr-x 1 rmariani staff 702200 May 4 11:06 x.wasm
% emcc -O3
-rw-r--r-- 1 rmariani staff 93711 May 4 11:07 x.html
-rw-r--r-- 1 rmariani staff 80387 May 4 11:07 x.js
-rwxr-xr-x 1 rmariani staff 1141683 May 4 11:07 x.wasm
# back to -Oz for a gzip test...% emcc -O3 -Oz ...
-rw-r--r-- 1 rmariani staff 93711 May 4 12:21 x.html
-rw-r--r-- 1 rmariani staff 80277 May 4 12:21 x.js
-rwxr-xr-x 1 rmariani staff 694965 May 4 12:21 x.wasm
% gzip x.*
% ls -l x.*
-rw-r--r-- 1 rmariani staff 36959 May 4 12:21 x.html.gz
-rw-r--r-- 1 rmariani staff 20826 May 4 12:21 x.js.gz
-rwxr-xr-x 1 rmariani staff 292024 May 4 12:21 x.wasm.gz
  1. You can instruct Emscripten that there are WebAssembly modules to link to by specifying them in the dynamicLibraries array of Emscripten’s generated JavaScript file. It will automatically download and link modules that are specified in this array.
  2. Your C or C++ code can manually link to a module by using the dlopen function.
  3. In your JavaScript, you can manually take the exports of one module and pass them in as imports to another using the WebAssembly JavaScript API
function myAppStartup(callback) {
FS.mkdir('/data');
FS.mount(IDBFS, {}, '/data');
FS.syncfs(true, function (err) {
// handle callback
});
}
function myAppShutdown(callback) {
FS.syncfs(function (err) {
// handle callback
});
}
  • The file system is actually reading from MEMFS with periodic sync, so that means ALL the data has to be in memory
  • That means you have to copy ALL the data at startup into memory
  • That also means you have to copy ALL the data to the db every time you sync
META VFS Published Results
  • Create database
  • Create table
  • Insert 1300 rows
  • Sum over one of the columns
  • Close the database
  • Open the database again
  • Sum over one of the columns again
  • Close the database again
  • “unix” is the default in-memory filesystem that SQL.js already uses.
  • “mem” is a sample synchronous in-memory VFS
  • “async mem” is a sample asynchronous in-memory VFS
  • Comparison to normal non-wasm native mode control case
  • WAL journaling
  • An IndexedDB back end on the VFS
PRAGMA locking_mode=EXCLUSIVE;
PRAGMA journal_mode=WAL;
drop table if exists dummy;create table dummy(id integer);with
nums(n) as (select 1
union all
select n+1 from nums
limit 1000000)
insert into dummy
select * from nums;
select count(*) from dummy;-- for sure can't be optimized
select count(*) from dummy where id % 31 = 0;
  • The journal mode can be easily changed
  • We do all the tests in exclusive mode because that’s the only mode were WAL works and also we’re the only user anyway so it doesn’t matter
  • The dummy table is very simple, we can easily control how many rows we insert, most tests were done with 1,000,000 rows
  • In the base test we select the count of rows afterwards, there are two queries, the second is to make it impossible to avoid reading the rows because of an index trick, as it happens this wasn’t needed but the normal “select” test simply removes the drop/insert and does the two selects
  • The read 200/6200 test replaces the above with the following:
PRAGMA locking_mode=EXCLUSIVE;PRAGMA journal_mode=WAL;-- make sure we can read only some of the rows
-- without paying the full cost
select id from dummy where id % 31 = 0 limit 200;
-- 2 selectsselect count(*) from dummy where id % 31 = 0;
select count(*) from dummy where id % 31 = 0;
-- 4 selectsselect count(*) from dummy where id % 31 = 0;
select count(*) from dummy where id % 31 = 0;
select count(*) from dummy where id % 31 = 0;
select count(*) from dummy where id % 31 = 0;

--

--

--

I’m a software engineer at Facebook; I specialize in software performance engineering and programming tools generally. I survived Microsoft from 1988 to 2017.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Can Web 3.0 Awaken the Internet’s True Potential? (Part Two)

Selenium Grid with Debug Docker containers

Crud Operation in Laravel for beginners with scratch

Platform Cosmos The Netflix

Progress Bars and Elevator Close Buttons Are The Scams of The Century

Easy start python3 and Django 2 on Mac and Ubuntu

Learn the Basics of Programming with Go in 2022

Tools for New Developers — Part 1

A laptop computer in front of a window, through which we can see that it is a cold and rainy day outside.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rico Mariani

Rico Mariani

I’m a software engineer at Facebook; I specialize in software performance engineering and programming tools generally. I survived Microsoft from 1988 to 2017.

More from Medium

Why Modern Web Development is a Mess

Keys to efficient i18n

Declarative Optional.Js