Public
Edited
Mar 27, 2023
Importers
SQLJS = require("sql.js")
downloadURL = (data, fileName) => {
const a = document.createElement("a");
try {
a.href = data;
a.download = fileName;
document.body.appendChild(a);
a.style.display = "none";
a.click();
} finally {
a.remove();
}
}
download = (data, fileName, mimeType) => {
const blob = new Blob([data], {
type: mimeType
});
const url = window.URL.createObjectURL(blob);
downloadURL(url, fileName);
setTimeout(() => window.URL.revokeObjectURL(url), 100000);
}
sqlite_extensions = [".sqlite", ".sqlite3", ".db", ".db3", ".s3db", ".sl3"]
create_db = async (filenameOrOptions, ...stmts) => {
let fileName =
typeof filenameOrOptions === "string"
? filenameOrOptions
: filenameOrOptions.fileName;
if (!sqlite_extensions.find((e) => fileName.endsWith(e))) {
fileName = `${fileName}.db`;
}
let data =
typeof filenameOrOptions === "string" ? undefined : filenameOrOptions.data;
let SQL = await SQLJS({
locateFile: (file) => `https://sql.js.org/dist/${file}`
});
let db = new SQL.Database(data);
try {
let result = stmts.flatMap((s) =>
typeof s === "string" ? (db.run(s), [{ sql: s }]) : s(db)
);
download(db.export(), fileName, "application/octet-stream");
return result;
} finally {
db.close();
}
}
colname = (key) => key.replaceAll(/[^a-zA-Z0-9]+/g, "_")
from_table = (table, name, columns = {}, table_options = {}) =>
(db) => {
let q = (s) => s.replace(/"/g, '""');
if (table.length > 0) {
// First we build a map of all columns, with name, type, and get fields.
let cols = { ...columns };
// A column is be ignored by specifying `null` or `false`
let canonicalize = (v) =>
v === false || v === null ? { ignore: true } : v;
Object.keys(cols).forEach((k) => (cols[k] = canonicalize(cols[k])));
// Fill in keys not pre-specified with any keys found in the data.
// Ensure each has .name and .get fields.
// and compute the column ordering
let order = new Set();
let fill_in = (k, r) => {
// If we're looking at rows, add them to the order now. Otherwise, add latef.
r && order.add(k);
let col = cols[k];
col = cols[k] = cols[k] ?? { ...(table_options.template ?? {}) };
col.name = q(col.name || colname(k));
col.type = affinity(r?.[k], col.type);
// A nullValue becomes a transform to apply to the data
col.transform =
col.transform ??
(col.nullValue
? typeof col.nullValue === "function"
? col.nullValue
: (v) => (v === null || v === undefined ? undefined : v)
: (v) => (col.nullValue !== v && v !== null ? v : undefined));
// Construct the function to get the field from the record.
col.get =
col.get ??
(col.value
? typeof col.value === "function"
? col.value
: (r) => col.value
: col.default
? typeof col.default === "function"
? (r) => col.transform(r[k]) ?? col.default(r)
: (r) => col.transform(r[k]) ?? col.default
: (r) => col.transform(r[k]));
};
Object.keys(cols).forEach((k) => fill_in(k));
table.forEach((r) => Object.keys(r).forEach((k) => fill_in(k, r)));
// Place any keys only specified but not found at the end.
Object.keys(columns).forEach((k) => order.add(k));
// Now we build our complete list of keys to consider.
let keys = [...order].filter((k) => !cols[k].ignore);
// Let's build and run our CREATE TABLE statement.
let primary = (s) =>
typeof s.primary === "string"
? `PRIMARY KEY ${s} `
: s.primary
? `PRIMARY KEY`
: "";
let unique = (s) => (s.unique ? "UNIQUE " : "");
let constraints = (s) =>
`${primary(s)}${unique(s)}${s.constraints ?? ""}`;
let colspec = (s) => `"${s.name}" ${s.type ?? ""} ${constraints(s)}`;
let col = (key) => colspec(cols[key]);
let colspecs = keys.map(col);
let constraint = (kwd, c) =>
`${kwd} (${c.columns.join(", ")})${c.on ? ` ON CONFLICT ${c.on}` : ""}`;
let foreign = (s) =>
`FOREIGN KEY (${s.columns.join(", ")}) REFERENCES "${s.references}" ${
s.action
}`;
let tblconstraints = [
...(table_options.primary
? [constraint("PRIMARY KEY", table_options.primary)]
: []),
...(table_options.unique
? [constraint("UNIQUE", table_options.unique)]
: []),
...(table_options.check ? [`CHECK (${table_options.check})`] : []),
...(table_options.foreign_key
? [foreign(table_options.foreign_key)]
: [])
];
let errors = [];
let run = (stmt, data, row) => {
try {
console.log(stmt);
db.run(stmt, data);
return true;
} catch (e) {
errors.push({
table,
row,
error: e.toString()
});
}
};
let spec = [...colspecs, ...tblconstraints].join(", ");
let create = `CREATE TABLE IF NOT EXISTS "${name}" (${spec})`;
if (!run(create)) {
return [{ table: name, row: -1, rows: table.length, create}, ...errors];
}
// Now build the INSERT statement
let values = keys.map((key, i) => `\$${i}`).join(",");
let colnames = keys.map((k) => cols[k].name).join(",");
let value = (k, row) => cols[k].get(row) ?? null;
let insert = `INSERT into "${name}" (${colnames}) VALUES(${values})`;
console.log(insert);
// And run it on each row
table.forEach((row) =>
db.run(
insert,
keys.map((k) => value(k, row))
)
);
// Return a bit of diagnostic info
return [{ table: name, rows: table.length, create, insert }, ...errors];
}
}
affinity = (obj, oldType) => {
let TypedArray = Object.getPrototypeOf(Uint8Array);
if (obj === undefined) {
return oldType;
}
switch (oldType) {
case "TEXT":
if (
obj instanceof Blob ||
obj instanceof TypedArray ||
obj instanceof ArrayBuffer
) {
return "BLOB";
}
return "TEXT";
case "BLOB":
return "BLOB";
case "NUMERIC":
switch (typeof obj) {
case "string":
return "TEXT";
case "number":
return "NUMERIC";
case "object":
if (obj instanceof Date) {
return "NUMERIC";
} else if (
obj instanceof Blob ||
obj instanceof TypedArray ||
obj instanceof ArrayBuffer ||
obj instanceof DataView
) {
return "BLOB";
}
return "TEXT";
default:
return "TEXT";
}
case "INTEGER":
if (obj === undefined) {
return "INTEGER";
} else if (Number.isInteger(obj)) {
return "iNTEGER";
}
if (
obj instanceof Blob ||
obj instanceof TypedArray ||
obj instanceof ArrayBuffer ||
obj instanceof DataView
) {
return "BLOB";
} else if (obj instanceof Date) {
return "INTEGER";
}
return "TEXT";
case undefined:
switch (typeof obj) {
case "string":
return "TEXT";
case "number":
if (Number.isInteger(obj)) {
return "INTEGER";
}
return "NUMERIC";
case "undefined":
return undefined;
default:
if (
obj instanceof Blob ||
obj instanceof TypedArray ||
obj instanceof ArrayBuffer ||
obj instanceof DataView
) {
return "BLOB";
} else if (obj instanceof Date) {
return "INTEGER";
}
return "TEXT";
}
}
}
from_xslx = (xslx, sheets) => (db) => {
let sheetFn = (sheetName, sheetInfo) => {
let sheet = xslx.sheet(sheetName, {
headers: sheetInfo.headers ?? true,
range: sheetInfo.range
});
return from_table(
sheet,
sheetInfo.tableName ?? colname(sheetName),
sheetInfo.columns ?? {},
sheetInfo.table_options ?? {}
)(db);
};
return Object.entries(sheets).flatMap(([sheetName, sheetInfo]) =>
sheetInfo instanceof Array
? sheetInfo.flatMap((si) => sheetFn(sheetName, si))
: sheetFn(sheetName, sheetInfo)
);
}
XSLX = import("xlsx")
import { zip, zipreader } from "@fil/jszip"
jszip = require("https://unpkg.com/jszip@latest/dist/jszip.js")
jszip.loadAsync(zz_ab)
zz = fetch(
`${ACCESS}/https://www.eia.gov/electricity/data/eia860/xls/eia8602021.zip`
)
zz_ab = zz.arrayBuffer()
zipreader
buf_to_string = (buf) => new TextDecoder("utf8").decode(buf)
load_xlsx = (buf) => XSLX.read(buf)
LOADERS = ({
xlsx: load_xlsx,
csv: (buf) => d3.csv(buf_to_string(buf)),
tsv: (buf) => d3.sv(buf_to_string(buf))
})
load_zip = async ({ url, CORS }) => {
const fURL = CORS ? `${ACCESS}/${url}` : url;
const entries = await fetch(fURL)
.then(zipreader)
.then((z) => [...z.entries()]);
const convert = ([path, p]) => {
const ext = /[.]([^.]+)$/.exec(path)[1];
const loader = LOADERS[ext];
return loader ? [[path, new Future(async () => loader(await p)), ext]] : [];
};
return Object.fromEntries(entries.flatMap(convert));
}
ZIP = load_zip({
url: "https://www.eia.gov/electricity/data/eia860/xls/eia8602021.zip",
CORS: true
})
ZIP["1___Utility_Y2021.xlsx"]
Object.fromEntries(Object.entries({ a: 5 }))
class FutureState {
// The computation to be performed, or null if it is already started or no longer eligible.
computation;
onStart;
onTimeout;
onCancel;
startTime;
// Enables cancellation and timeout.
reject;
exception;
state;
}
class Future {
#s; // shared state.
#promise;
#onStartPromise;
#onTimeoutPromise;
#onCancelPromise;
static get [Symbol.species]() {
return Future;
}
get state() {
return this.#s.state;
}
constructor(computation) {
if (computation instanceof Future) {
const o = computation;
this.#s = o.#s;
this.#promise = o.#promise;
this.#onStartPromise = o.#onStartPromise;
this.#onTimeoutPromise = o.#onTimeoutPromise;
this.#onCancelPromise = o.#onCancelPromise;
}
this.#onStartPromise = new Promise(
(resolve, reject) => (this.#s.onStart = resolve)
);
this.#onTimeoutPromise = new Promise(
(resolve, reject) => (this.#s.onTimeout = resolve)
);
this.#promise = new Promise((resolve, reject) => {
this.#s.computation = () => {
this.#s.computation = null;
this.#s.state = "STARTED";
this.#s.startTime = Date.now();
this.#s.onStart(this.#s.startTime);
try {
resolve(computation(this));
} catch (e) {
// Not if this is already resolved.
if (this.#s.state === "STARTED") {
this.#s.exception = e;
}
reject(e);
}
};
}).then(
(v) => this.#resolved("FULFILLED", undefined, v),
(e) =>
e instanceof Timeout
? this.#resolved("TIMEOUT", this.#s.onTimeout, e)
: e instanceof Cancelled
? this.#resolved("CANCELLED", this.#s.onCancel, e)
: this.#resolved("REJECTED", undefined, e)
);
}
// Arrive at a final state.
#resolved(state, handler, v) {
this.#s.state = state;
this.#s.computation = this.#s.onCancel = null;
this.#s.onTimeout = this.#s.onStart = null;
this.#s.exception = v;
handler?.(v);
return v;
}
then(onFulfilled, onRejected) {
this.#s.computation?.();
const next = new Future(this);
next.#promise = next.#promise.then(onFulfilled ?? ((a) => a), onRejected);
}
catch(onRejected) {
const next = new Future(this);
next.#promise = next.#promise.catch(onRejected);
return next;
}
finally(onFinally) {
const next = new Future(this);
next.#promise = next.#promise.finally(onFinally);
return next;
}
when(onFulfilled, onRejected) {
const next = new Future(this);
next.#promise = next.#promise.then(onFulfilled, onRejected);
return next;
}
onStart(handler) {
const next = new Future(this);
next.#onStartPromise = next.#onStartPromise.then(handler);
return next;
}
start() {
this.#s.computation?.();
return this;
}
cancel(msg = "Cancelled") {
this.#resolved(
"Cancelled",
this.#s.onCancel,
new Cancelled(msg, this, this.#s.startTime)
);
return this;
}
onCancel(handler) {
const next = new Future(this);
next.#onCancelPromise = next.#onCancelPromise.catch(handler);
return next;
}
onTimeout(handler) {
const next = new Future(this);
next.#onTimeoutPromise = this.#onTimeoutPromise.catch(handler);
return next;
}
isCancelled() {
return this.#s.state !== "PENDING" || this.#s.state !== "STARTED";
}
check(continuation) {
switch (this.#s.state) {
case "PENDING":
throw new Error(
"Check is to be used as part of a running Future computation."
);
case "STARTED":
return continuation(this);
case "TIMEOUT":
case "CANCELLED":
throw this.#s.exception;
default:
throw new Error("Computation has already completed.");
}
}
static delay(delay) {
return (computation) => {
const p = new Promise((resolve, reject) => setTimeout(resolve, delay));
return new Future(() => p.then(computation));
};
}
static timeoutFromNow(timeout, msg = "Timeout") {
return (computation) => {
// Start the timer now.
const start = Date.now();
const future = new Future(() => {
const c = Promise.resolve(computation()).then(
(v) => ((future.#s.onTimeout = null), v)
);
const p = new Promise((resolve, reject) =>
setTimeout(() => reject(new Timeout(msg, future, start)), timeout)
).catch((e) => () => (future.#s.onTimeout?.(e), Throw(e)));
return Promise.race([p, c]);
});
return future;
};
}
static timeout(timeout, msg = "Timeout") {
const msg_dflt = msg;
return (computation, msg) => {
const tmsg = msg ?? msg_dflt;
const future = new Future(() => {
// Start the timer when the Future executes.
const start = Date.now();
const p = new Promise((resolve, reject) =>
setTimeout(() => reject(new Timeout(tmsg, future, start)), timeout)
).catch((e) => () => (future.#s.onTimeout?.(e), Throw(e)));
const c = Promise.resolve(computation()).then(
(v) => ((future.#s.onTimeout = null), v)
);
return Promise.race([p, c]);
});
return future;
};
}
}
class FutureException extends Error {
future;
start;
end;
constructor(msg, future, start, end) {
super(msg);
this.future = future;
this.start = start;
this.end = end;
}
}
class Timeout extends FutureException {
constructor(msg = "Timeout", future, start = undefined, end = Date.now()) {
super(msg, future, start, end);
}
}
class Cancelled extends FutureException {
constructor(msg = "Timeout", future, start = undefined, end = Date.now()) {
super(msg, future, start, end);
}
}
Throw = (e = new Error()) => {
throw e;
}
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.