Public
Edited
Dec 16, 2023
6 forks
4 stars
sqliteWasm = import("https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0")
sqlite = sqliteWasm.default(
(file) => "https://esm.sh/@vlcn.io/crsqlite-wasm@0.16.0/dist/crsqlite.wasm"
)
db = sqlite.open(":memory:")
created = db.execMany([
`CREATE TABLE IF NOT EXISTS todo_list ("name" primary key not null, "creation_time");`,
`CREATE TABLE IF NOT EXISTS todo ("id" primary key not null, "list", "text", "complete");`
// primary keys act as the identifier by which to merge.
// if two things have different primary keys, they'll both exist.
// if two things have the same primary key, their contents are merged.
])
upgraded = created == OK && // "created == OK" is to tell observable not to run this cell until the one above has run
db.execMany([
`SELECT crsql_as_crr('todo_list');`,
`SELECT crsql_as_crr('todo');`
])
inserted = {
upgraded == OK;
let list = [
"milk",
"potatos",
"avocado",
"butter",
"cheese",
"broccoli",
"spinach"
];
// `insert or ignore` given this is a notebook and ppl will re-run cells.
await db.exec(`INSERT OR IGNORE INTO todo_list VALUES ('groceries', ?)`, [
Date.now()
]);
await Promise.all(
list.map((item) =>
db.exec(`INSERT INTO todo VALUES (?, 'groceries', ?, 0)`, [
nanoid.nanoid(),
item
])
)
);
list = ["test", "document", "explain", "onboard", "hire"];
await db.exec(`INSERT OR IGNORE INTO todo_list VALUES ('work', ?)`, [
Date.now()
]);
await Promise.all(
list.map((item) =>
db.exec(`INSERT INTO todo VALUES (?, 'work', ?, 0)`, [
nanoid.nanoid(),
item
])
)
);
return OK;
}
{
inserted == OK;
const groceries = await db.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'groceries'`
);
return Inputs.table(groceries);
}
{
inserted == OK;
const work = await db.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'work'`
);
return Inputs.table(work);
}
changesets = {
inserted == OK;
return await db.execA("SELECT * FROM crsql_changes where db_version > -1");
}
db2 = {
changesets;
const db2 = await sqlite.open(":memory:");
await db2.execMany([
`CREATE TABLE IF NOT EXISTS todo_list ("name" primary key not null, "creation_time");`,
`CREATE TABLE IF NOT EXISTS todo ("id" primary key not null, "list", "text", "complete");`,
`SELECT crsql_as_crr('todo_list');`,
`SELECT crsql_as_crr('todo');`
]);
return db2;
}
merged = {
await db2.tx(async (tx) => {
for (const cs of changesets) {
await tx.exec(
`INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
cs
);
}
});
return OK;
}
{
merged == OK;
const groceries = await db2.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'groceries'`
);
return Inputs.table(groceries);
}
{
merged == OK;
const work = await db2.execO(
`SELECT "list", "text" FROM "todo" WHERE "list" = 'work'`
);
return Inputs.table(work);
}
db1_version = {
merged == OK;
return (await db.execA(`SELECT crsql_db_version()`))[0][0];
}
db2_version = {
db1_version;
return (await db2.execA(`SELECT crsql_db_version()`))[0][0];
}
concurrent_modification = {
// dependency tracking
db1_version;
db2_version;
// Both dbs create a `home` list
// given `name` is the pk, the lists will merge rather than duplicate.
await db.exec(`INSERT OR IGNORE INTO todo_list VALUES (?, ?)`, [
"home",
Date.now()
]);
await db2.exec(`INSERT OR IGNORE INTO todo_list VALUES (?, ?)`, [
"home",
Date.now()
]);
// both dbs add some stuff to that list
await db.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"paint",
0
]);
await db2.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"mow",
0
]);
await db.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"water",
0
]);
// given each item is a nanoid for primary key, `weed` will show up twice
await db2.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"weed",
0
]);
await db.exec(`INSERT INTO todo VALUES (?, ?, ?, ?)`, [
nanoid.nanoid(),
"home",
"weed",
0
]);
// and complete things on other lists
await db.exec(`UPDATE todo SET complete = 1 WHERE list = 'groceries'`);
return OK
}
db1_changesets = {
concurrent_modification == OK;
return await db.execA(`SELECT * FROM crsql_changes WHERE db_version > ?`, [
db1_version
]);
}
db2_changesets = {
concurrent_modification == OK;
return await db2.execA(`SELECT * FROM crsql_changes WHERE db_version > ?`, [
db2_version
]);
}
Inputs.table(db1_changesets)
Inputs.table(db2_changesets)
both_merged = {
await db2.tx(async (tx) => {
for (const cs of db1_changesets) {
await tx.exec(
`INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
cs
);
}
});
await db.tx(async (tx) => {
for (const cs of db2_changesets) {
await tx.exec(
`INSERT INTO crsql_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
cs
);
}
});
return OK;
}
{
both_merged == OK;
return Inputs.table(await db2.execO(`SELECT * FROM todo ORDER BY text ASC`));
}
{
both_merged == OK;
return Inputs.table(await db.execO(`SELECT * FROM todo ORDER BY text ASC`));
}
nanoid = import("https://esm.sh/nanoid@4.0.0")
OK = 0
siteid = (await db.execA(`SELECT crsql_site_id()`))[0][0]
db2_siteid = (await db2.execA(`SELECT crsql_site_id()`))[0][0]
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.
