Visualization toolmaker. Founder @observablehq. Creator @d3. Former @nytgraphics. Pronounced BOSS-tock.
Published
Edited
Sep 10, 2021
2 forks
Importers
41 stars
cpi = SQLite.open("https://raw.githubusercontent.com/datadesk/cpi/d582252c644b90d48918a4cba951baf3c03c9629/cpi/cpi.db")
cpi.exec("SELECT * FROM sqlite_master WHERE type = 'table'")
cpi.exec("SELECT * FROM 'cu.series'")
cpi.exec(`SELECT *
FROM 'cu.series'
WHERE item_code = 'SA0'
AND area_code = 'A214'
ORDER BY begin_year ASC`)
cpi.exec(`SELECT *
FROM 'cu.series'
WHERE item_code = ?
AND area_code = ?
ORDER BY begin_year ASC`, ['SA0', 'A214'])
class SQLite {
constructor(buffer) {
Object.defineProperties(this, {
_db: {value: new SQL.Database(buffer && new Uint8Array(buffer))}
});
}
dispose() {
this._db.close();
}
async exec(sql, params) {
if (params !== undefined) {
let i = -1;
sql = sql.replace(/\?/g, () => {
const param = params[++i];
return Array.isArray(param)
? new Array(param.length).fill("?")
: "?";
});
params = params.flat(1);
}
const [result] = await this._db.exec(sql, params);
if (!result) return [];
const {columns, values} = result;
return values.map(row => {
const object = {};
for (let i = 0; i < row.length; ++i) object[columns[i]] = row[i];
return object;
});
}
static async open() {
return new SQLite(await fetch(...arguments).then(response => response.arrayBuffer()));
}
}
SQL = {
const [init, path] = await Promise.all([
require("sql.js@1"),
require.resolve("sql.js@1/dist/").then(path => path.replace(/\.js$/, ""))
]);
return init({locateFile: file => path + file});
}
class SQLiteDatabaseClient {
constructor(buffer) {
Object.defineProperties(this, {
_db: {value: Promise.resolve(buffer).then(buffer => new SQLite(buffer))}
});
}
async query(sql, params) {
return (await this._db).exec(sql, params);
}
async queryRow(sql, params) {
return (await this.query(sql, params))[0];
}
async explain(sql, params) {
const rows = (await this.query(`EXPLAIN QUERY PLAN ${sql}`, params));
const text = rows.map(row => row.detail).join("\n");
const pre = document.createElement("PRE");
pre.className = "observablehq--inspect";
pre.appendChild(document.createTextNode(text));
return pre;
}
async describe(object) {
if (object !== undefined) {
const [row] = await this.query(`SELECT * FROM '${object}' LIMIT 1`);
const value = Object.entries(row).map(([column_name, value]) => ({
column_name,
data_type: typeof value === "string" ? "character varying"
: typeof value === "number" ? "integer"
: undefined,
column_default: null,
is_nullable: "YES"
}));
const table = html`
<table>
<thead>
<tr>
<th>column_name</th>
<th>data_type</th>
<th>column_default</th>
<th>is_nullable</th>
</tr>
</thead>
<tbody>
${value.map(({column_name, data_type, column_default, is_nullable}) => html`
<tr>
<td>${column_name}</td>
<td>${data_type}</td>
<td>${column_default}</td>
<td>${is_nullable}</td>
</tr>
`)}
</tbody>
</table>
`;
table.value = value;
return table;
} else {
const rows = await this.query(`SELECT name FROM sqlite_master WHERE type = 'table'`);
const table = html`
<table>
<thead>
<tr>
<th>name</th>
</tr>
</thead>
<tbody>
${rows.map(({name}) => html`
<tr>
<td>${name}</td>
</tr>
`)}
</tbody>
</table>
`;
table.value = [{table_schema: "public", table_name: "names"}];
return table;
}
}
}
Purpose-built for displays of data
Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
