2b:null 2c:["$","title",null,{"children":"Hello, sql.js-httpvfs | Max Bo | Observable"}] 31:[{"id":"d0458b3c8960fc3490b9812bf0f63d021bf9d9be25393c122fe69d0eb3c5662feb2a59f2c4650b5d752764ce4eadcbd21ec81089eee0e022bbbefb20be8680f1","href":"https://static.observableusercontent.com/files/d0458b3c8960fc3490b9812bf0f63d021bf9d9be25393c122fe69d0eb3c5662feb2a59f2c4650b5d752764ce4eadcbd21ec81089eee0e022bbbefb20be8680f1","name":"sqlite.worker@1.js","type":"application/javascript","lastModified":1651451352896.75,"size":87674},{"id":"5a5cfd04f6921c008b80ca55799e4642776ac0dfc6911e0b6d95a327344cec20a74ee94037d01eecaf0f41f6e5c4e5d06b97ca5b4494080d8767d803745db2c7","href":"https://static.observableusercontent.com/files/5a5cfd04f6921c008b80ca55799e4642776ac0dfc6911e0b6d95a327344cec20a74ee94037d01eecaf0f41f6e5c4e5d06b97ca5b4494080d8767d803745db2c7","name":"sql-wasm@1.wasm","type":"application/wasm","lastModified":1651451352896.75,"size":1404195}] 2f:{"id":"82c5f60496b36926","slug":"sqljs-httpvfs","title":"Hello, sql.js-httpvfs","thumbnail":"43c507cf0bfc65e67c9a9b1bbcdfe3b2005b50a426ead9522e56195ca906de43","access_level":"public","create_time":"$D2022-05-02T00:29:12.896Z","update_time":"$D2023-01-17T00:10:35.084Z","view_time":null,"first_public_time":"$D2022-05-03T10:17:43.545Z","fork":{"id":"1d26ac16f605cf7e","slug":"phiresky-sqlite-query","title":"Network efficient SQLite querying from static file hosting.","owner":{"id":"7db5ed2b0697d645","login":"tomlarkworthy","name":"Tom Larkworthy"}},"stars":8,"starred":false,"owner":{"type":"team","id":"cc48459e4293f942","login":"mjbo","name":"Max Bo","avatar_url":"https://avatars-next.observableusercontent.com/05ae9baba40510a4a22fa7d179f29dd4ffd85fcff859c54a31d42e166ae61dfa","tier":"starter_2024"},"version":910,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"5bb83a6e9493ea1d","login":"mjbo","name":"Max Bo","avatar_url":"https://avatars-next.observableusercontent.com/05ae9baba40510a4a22fa7d179f29dd4ffd85fcff859c54a31d42e166ae61dfa","tier":null}],"members":[{"type":"individual","id":"5bb83a6e9493ea1d","login":"mjbo","name":"Max Bo","avatar_url":"https://avatars-next.observableusercontent.com/05ae9baba40510a4a22fa7d179f29dd4ffd85fcff859c54a31d42e166ae61dfa","tier":null,"role":"editor"}],"comments":[{"id":"3baa03995680cfed","reply_to_id":null,"content":"Ideally we'd be able to interpolate a list and have it expand out into a `(?, ?, ?)`, but Observable's `SQLiteDatabaseClient.sql` tag function [doesn't support](https://github.com/observablehq/stdlib/blob/8c0110cd0d10ba46fd4bfb3ea723b7b9c6971a11/src/sqlite.js#L42) this. Instead we have to use `instr` substring check rather than using the `in` set membership operator.","create_time":"$D2022-05-03T10:21:31.248Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"5bb83a6e9493ea1d","login":"mjbo","name":"Max Bo","avatar_url":"https://avatars-next.observableusercontent.com/05ae9baba40510a4a22fa7d179f29dd4ffd85fcff859c54a31d42e166ae61dfa","tier":null}},{"id":"aa0a8d3d20b7b8b9","reply_to_id":null,"content":"It seems to be working for me right now in Mobile Safari on iOS 16.1.1.","create_time":"$D2023-01-02T09:50:29.529Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"dc026342127b807b","login":"simonw","name":"Simon Willison","avatar_url":"https://avatars-next.observableusercontent.com/78a40181b372a172b86abe69479cab717df036b9cdcbaab5a197e45a76ffb4f3","tier":null}},{"id":"ac9002cd54f552e5","reply_to_id":"aa0a8d3d20b7b8b9","content":"Hmm curious `RuntimeError: A network error occurred.` is thrown on Safari iOS 15.6.1 and Chrime iOS. Might be fixed in 16.","create_time":"$D2023-01-02T22:15:47.495Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"5bb83a6e9493ea1d","login":"mjbo","name":"Max Bo","avatar_url":"https://avatars-next.observableusercontent.com/05ae9baba40510a4a22fa7d179f29dd4ffd85fcff859c54a31d42e166ae61dfa","tier":null}},{"id":"6644c9eedf9f5ddc","reply_to_id":"aa0a8d3d20b7b8b9","content":"Confirmed working on iOS 16! Notice updated.","create_time":"$D2023-01-03T00:23:51.845Z","update_time":null,"resolved":true,"user":{"type":"individual","id":"5bb83a6e9493ea1d","login":"mjbo","name":"Max Bo","avatar_url":"https://avatars-next.observableusercontent.com/05ae9baba40510a4a22fa7d179f29dd4ffd85fcff859c54a31d42e166ae61dfa","tier":null}}],"custom_thumbnail":true,"trashed":false,"subscribed":null,"forks":4,"collection":{"id":"697dca7b1f39efd7","slug":"hello-x","title":"Hello, X","owner":{"id":"cc48459e4293f942","login":"mjbo","name":"Max Bo"}},"type":"notebook","body":{"title":"Hello, sql.js-httpvfs","cells":[{"id":0,"value":"# Hello, [sql.js-httpvfs](https://github.com/phiresky/sql.js-httpvfs)","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":162,"value":"This notebook queries a statically hosted SQLite file, using [sql.js-httpvfs](https://github.com/phiresky/sql.js-httpvfs). To use:\n\n```js\nimport {SQLiteHTTPVFSDatabaseClient} from \"@mjbo/sqljs-httpvfs\"\n```\n\nThen call `SQLiteHTTPVFSDatabaseClient.open`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":189,"value":"db = SQLiteHTTPVFSDatabaseClient.open({\n configs: [\n {\n from: \"jsonconfig\",\n configUrl:\n \"https://phiresky.github.io/world-development-indicators-sqlite/split-db/config.json\"\n }\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":849,"value":"*This will only function in WebKit versions >= 16*.","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":310,"value":"[sql.js-httpvfs](https://github.com/phiresky/sql.js-httpvfs) is a fork of [sql.js](https://github.com/sql-js/sql.js/) by [phiresky](https://phiresky.github.io/blog/) that provides a read-only [HTTP-Range-request](https://developer.mozilla.org/en-US/docs/Web/HTTP/Range_requests) based virtual file system for SQLite. \n\nIt allows hosting an SQLite database on a static file hoster and querying that database from the browser without fully downloading it, reducing bandwith usage and *time-to-first-plot*.\n\nWe can now reproduce the queries and charts demonstrated in the sql.js-httpvfs intro blog, Hosting SQLite databases on Github Pages.\n\nWe'll be using the World Development Indicators dataset - a dataset with 6 tables and over 8 million rows (670 MiByte total).","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":742,"value":"db.describe()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":544,"value":"select * from wdi_country","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"countries","database":"var:db"},{"id":371,"value":"select * from indicator_search","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"indicators","database":"var:db"},{"id":636,"value":"select indicator_code, short_name, year, value\nfrom wdi_data, wdi_country\nwhere\n wdi_data.country_code = wdi_country.country_code\n and wdi_data.indicator_code = ${selectedIndicator.indicator_code}\n -- gnarly hack to get around the fact we can't interpolate lists for use with IN\n and instr(${selectedCountries.map((c) => c.country_code).join(\",\")}, wdi_data.country_code) > 0","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"results","database":"var:db"},{"id":381,"value":"viewof selectedCountries = Inputs.select(countries, {\n label: \"Countries (multiselect):\",\n multiple: true,\n format: (d) => d.short_name,\n // no easy way to interpolate a list of values into a SQLite query. Disaster\n value: [\"United States\", \"Germany\", \"India\", \"China\", \"Korea\"].map((c) =>\n countries.find((co) => co.short_name === c)\n )\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":379,"value":"viewof selectedIndicator = Inputs.select(indicators, {\n label: \"Indicator:\",\n format: (i) => i.indicator_name,\n value: indicators.find((i) => i.indicator_code === \"IT.NET.USER.ZS\")\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":661,"value":"{\n const data = results.map((result) => ({\n ...result,\n date: new Date(result.year, 0)\n }));\n\n return addTooltips(\n Plot.plot({\n color: {\n legend: true\n },\n marks: [\n Plot.line(data, {\n x: \"date\",\n y: \"value\",\n stroke: \"short_name\"\n }),\n Plot.dot(data, {\n x: \"date\",\n y: \"value\",\n stroke: \"short_name\",\n title: (d) =>\n // ideally we'd use truncate the displayed values to the same length as the legend\n // if anyone knows how to do this please leave a Suggestion\n `${d.date.getFullYear()}\\n${d.short_name}: ${d.value.toFixed(2)}`\n })\n ]\n })\n );\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":532,"value":"

Extra information about this indicator

Indicator Code: ${selectedIndicator.indicator_code}
Long definition: ${selectedIndicator.long_definition}
Statistical concept and methodology: ${selectedIndicator.statistical_concept_and_methodology}
Development relevance: ${selectedIndicator.development_relevance}

","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":779,"value":"## Hosting your own database\n\nsql.js-httpvfs can be configured in two modes\n\n1. `jsonconfig`, where sql.js-httpvfs reads a from a hosted database that has been [split into chunks](https://github.com/phiresky/sql.js-httpvfs#usage), configured by a `config.json` hosted along side it\n2. `inline`, where sql.js-httpvfs is configured browser-side\n\nLet's demonstrate the `inline` configuration too.\n\nFirst, we've statically hosted the [@observablehq/sqlite](/@observablehq/sqlite) `chinkook.db` using [GitHub Pages](https://docs.github.com/en/pages/getting-started-with-github-pages/about-github-pages). \n\nThen we call `SQLiteHTTPVFSDatabaseClient.open`","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":796,"value":"dbInline = SQLiteHTTPVFSDatabaseClient.open({\n configs: [\n {\n from: \"inline\",\n config: {\n serverMode: \"full\", // file is just a plain old full sqlite database\n url: \"https://maxbo.me/chinook.db\", // url to the database (relative or full)\n requestChunkSize: 4096 // the page size of the sqlite database (by default 4096)\n }\n }\n ]\n})","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":808,"value":"dbInline.describe()","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":810,"value":"select * from tracks limit 30","pinned":true,"hidden":false,"mode":"sql","author":"$undefined","summary":"$undefined","output":"","database":"var:dbInline"},{"id":175,"value":"---\n\n## Implementation","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined"},{"id":21,"value":"sqlite = require(\"sql.js-httpvfs\")","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":824,"value":"Sadly there's no good way to package workers and wasm directly so we need to recover these two URLs.","pinned":false,"hidden":false,"mode":"html","author":"$undefined","summary":"$undefined"},{"id":40,"value":"wasmUrl = \"https://cdn.jsdelivr.net/npm/sql.js-httpvfs/dist/sql-wasm.wasm\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":25,"value":"workerUrl = URL.createObjectURL(\n new Blob(\n [\n await fetch(\n \"https://cdn.jsdelivr.net/npm/sql.js-httpvfs/dist/sqlite.worker.js\"\n ).then((d) => d.text())\n ],\n {\n type: \"application/javascript\"\n }\n )\n)","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":159,"value":"class SQLiteHTTPVFSDatabaseClient {\n static async open({ configs, maxBytesToRead }) {\n const worker = await sqlite.createDbWorker(\n // you can also pass multiple config objects which can then be used\n // as separate database schemas with `ATTACH virtualFilename as schemaname`,\n // where `virtualFilename` is also set in the config object.\n configs,\n workerUrl,\n wasmUrl,\n maxBytesToRead // optional, defaults to Infinity\n );\n\n return new SQLiteDatabaseClient(worker.db);\n }\n}","pinned":true,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"},{"id":738,"value":"import { addTooltips } from \"@mkfreeman/plot-tooltip\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined"}],"files":"$W31","stdlib":"1","titling":"auto"}} 30:"$2f:body"