A SQL database and an interactive grapher in a single HTML file

6 minute read Published: 2021-05-04

Table of Contents

Glassbench is a Rust micro-benchmark harness. It uses a SQLite database to store bench measurements.

I wanted to give it the option to open an interactive data viewer and grapher, with ability to search the history.

An usual web based solution for that is to run a simple web server.

But Glassbench is executed by cargo and immediately quits and I didn't want it to leave behind anything to clean, especially not daemons. Besides, when you open a single HTML local file, it can't query additional resources, due to browser's security protections.

So I decided to make it a single temporary html page opened in the user's browser.

More precisely, Glassbench's html data viewer:

If you want to see such a page without installing Glassbench, here's an example.

Here's the main code:

pub fn open_in_browser(&self) -> Result<(), GlassBenchError> {
	let (mut w, path) = make_temp_file()?;
	self.write_html(&mut w)?;
	open::that(path)?;
	Ok(())
}

pub fn write_html<W: io::Write>(&self, mut w: W) -> Result<(), GlassBenchError> {
	writeln!(w, "<!DOCTYPE HTML>")?;
	writeln!(w, "<html>")?;
	writeln!(w, "<head>")?;
	writeln!(w, "<meta charset=UTF-8>")?;
	writeln!(w, "<style type=text/css>{}</style>", VIEWER_CSS)?;
	writeln!(w, "<style type=text/css>{}</style>", VIS_CSS)?;
	writeln!(w, "<script>{}</script>", VIS_JS)?;
	writeln!(w, "<script>{}</script>", SQL_JS)?;
	writeln!(w, "<script>{}</script>", DOLL_JS)?;
	writeln!(w, "<script charset=UTF-8>{}</script>", VIEWER_JS)?;
	write_db(&mut w)?;
	writeln!(w, "</head>")?;
	writeln!(w, "<body>")?;
	writeln!(w, "<script>")?;
	writeln!(w, "const gb_conf = {}", serde_json::to_string(&self.conf)?)?;
	writeln!(
	    w,
	    r#"const sql_conf = {{ locateFile: filename=>"data:application/wasm;base64,{}" }};"#,
	    base64::encode(SQL_WASM),
	)?;
	writeln!(w, "main(sql_conf);")?;
	writeln!(w, "</script>")?;
	writeln!(w, "</body>")?;
	writeln!(w, "</html>")?;
	Ok(())
}

Embedd stylesheets and Javascript scripts

There's nothing really magic about embedding text in an HTML file.

But worth nothing is that to keep the code clean and to avoid a huge string literal, I store those resources:

Rust makes this very simple:

pub const DOLL_JS: &str = include_str!("../rsc/dom-doll.js");
pub const VIS_JS: &str = include_str!("../rsc/vis-timeline-graph2d.min.js");
pub const VIS_CSS: &str = include_str!("../rsc/vis-timeline-graph2d.min.css");
pub const SQL_JS: &str = include_str!("../rsc/sql-wasm.js");
pub const VIEWER_JS: &str = include_str!("../rsc/viewer.js");
pub const VIEWER_CSS: &str = include_str!("../rsc/viewer.css");

Embed the SQL engine WASM part

The SQL Engine I use is sql-js. It's a very cool project as it's able to query a database in the browser.

Regarding embedding, the specificity is that it's in WebAssembly. The boot code asynchronously loads a WASM file, with an URL. There's no other option.

You can't query an external file from an HTML file you open with file:// (without messing with the browser's setting).

So the solution I chose was to encode the WASM file in base64 and give it to sql-js boot loader as a Data URL.

Once you know how to do it, it's incredibly simple:

writeln!(
    w,
    r#"const sql_conf = {{ locateFile: filename=>"data:application/wasm;base64,{}" }};"#,
    base64::encode(SQL_WASM),
)?;

This writes a JavaScript statement creating the configuration object for Sql-js by including the WASM file encoded as base64.

The {{ and }} are { and } escaped for the Rust format string.

Embed the database file

The SQL.Database constructor of Sql-js can be given a JavaScript Uint8Array.

So here's what is done there:

1. In Rust, read the sqlite file into bytes:

let mut file = File::open(Db::path()?)?;
let mut bytes = Vec::new();
file.read_to_end(&mut bytes)?;

2. Encode those bytes in base64 and write as a Javascript string literal in the HTML file:

writeln!(w,
    r#"<script>const db64="{}"</script>"#,
    base64::encode(&bytes),
)?;

3. In JavaScript, decode the base64 into a byte array:

function base64ToUint8Array(base64) {
	var binary_string = window.atob(base64)
	var len = binary_string.length
	var bytes = new Uint8Array(len)
	for (var i = 0; i < len; i++) {
		bytes[i] = binary_string.charCodeAt(i)
	}
	return bytes
}

let db_bytes = base64ToUint8Array(db64)

4. Initialize the Sql-js Database object:

// here we're using the sql_conf which contains the wasm as Data-Url
const SQL = await initSqlJs(sql_conf)
const db = new SQL.Database(db_bytes)

Embed the initial user query and conf

The request done on command line to open the grapher can contain a specific bench or task to graph:

cargo bench -- my_task -- --graph 2

Those parameters must be written by the Rust code in the HTML file so that the JavaScript code can do the selections in the widgets and display the curb the user required.

As the strings can contain special characters, quotes, etc. it's better to do a proper JSON encoding and not just dump them raw.

The solution here is to have a Rust struct mimicking the JavaScript object then use serde to do the encoding:

/// configuration sent to the html page in JSON
#[derive(Serialize)]
struct Conf<'b> {
    bench_name: &'b str,
    task_name: Option<&'b str>,
    gb_version: String,
}

The #[derive(Serialize)] attribute tells serde how to do the serialization here:

writeln!(w, "const gb_conf = {}", serde_json::to_string(&self.conf)?)?;

Generate a temporary file

Glassbench doesn't want to be tied to one platform. So, rather than direcly writing a randomly called file in /tmp, I use a cross-platform crate: tempfile.

Its usage is quite straighforward:

tempfile::Builder::new()
    .prefix("glassbench-")
    .suffix(".html")
    .rand_bytes(12)
    .tempfile()?
    .keep()
    .map_err(|_| io::Error::new(
        io::ErrorKind::Other,
        "temp file can't be kept",
    ))

The only specific thing here is that I want the temp file to survive Glassbench execution so that the browser is sure to have time to open it, hence the .keep().

Open it in the browser

Various systems have different ways to open a file. For example your common linux desktop usually rely xdg-open.

Several Rust crates provide an OS independant facade to this role. Glassbench uses open.

Here's writing the file and opening it:

let (mut w, path) = make_temp_file()?;
self.write_html(&mut w)?;
open::that(path)?;

Conclusion

I'm convinced this solution to generate stand-alone HTML files embedding everything from the database to the query engine and grapher can be used in other places, like on rough static hosting, to more easily give files, or for any short living application wanting to open a viewer with complex SQL queries.

I hope this page, while explaining all the steps, makes it look as simple as it really is to use.

While this example is in Rust, it's probably easy to port it in any other language.