1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
import { sqliteBlob } from "https://esm.town/v/postpostscript/sqliteBlob";
import { Statement } from "https://esm.town/v/postpostscript/sqliteBuilder";
import {
defaultPatterns,
sqliteUniverse,
sqliteUniverseWithOptions,
} from "https://esm.town/v/postpostscript/sqliteUniverseBeta";
import { sqlite as sqlitePrivate } from "https://esm.town/v/std/sqlite";
await (async () => {
const start = performance.now();
const result = await Statement`
SELECT t1.*, t2.*
FROM "@postpostscript/sqlitePublic:::authIdExampleComments_comment" t1
JOIN "https://pps-sqlitepublic.web.val.run:::example" t2
LIMIT 1
`.execute({
sqlite: sqliteUniverse,
});
console.log(result);
console.log("took", performance.now() - start, "ms");
})();
await (async () => {
const start = performance.now();
// to query against your private data, use sqliteUniverseWithOptions with the @std/sqlite interface option passed
// use sqliteBlob to query your blob metadata!
const sqlite = sqliteUniverseWithOptions({
interfaces: {
exact: {
"@std/sqlite": sqlitePrivate,
"@std/blob": () => sqliteBlob(),
},
patterns: defaultPatterns,
fallback() {
return sqlitePrivate;
},
},
});
const result = await Statement`
SELECT t1.*, t4.*
FROM "@std/sqlite:::authIdExampleComments_comment" t1
JOIN "@std/blob:::blobs" t4
LIMIT 1
`.execute({ sqlite });
// Statement`
// DELETE FROM "@std/sqlite/use_tracking"
// `,
console.log(result);
console.log("took", performance.now() - start, "ms");
})();
sqliteBlob: make sqlite queries against your blobs!*
- does not include blob values
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
import { Statement, type StatementInstance } from "https://esm.town/v/postpostscript/sqliteBuilder";
import { createSqlite } from "https://esm.town/v/postpostscript/sqliteWasm";
import { blob as blobAPI } from "https://esm.town/v/std/blob";
export async function sqliteBlob(options: SqliteBlobOptions = {}) {
const schema = sqliteBlobSchema(options);
const sqlite = createSqlite();
sqlite.batch(await schema);
return sqlite;
}
export async function sqliteBlobSchema(
{ prefix = undefined, table = Statement`blobs`, blob = blobAPI }: SqliteBlobOptions = {},
) {
const blobs = await blob.list(prefix);
return [
Statement`
CREATE TABLE ${table} (
key TEXT PRIMARY KEY,
size INTEGER NOT NULL,
lastModified DATETIME NOT NULL
)
`,
...blobs.map(({ key, size, lastModified }) => {
return Statement`
INSERT INTO ${table} VALUES (
${key},
${size},
${new Date(lastModified).getTime() / 1000}
)
`;
}),
];
}
export type SqliteBlobOptions = {
prefix?: string;
table?: StatementInstance;
blob?: {
list: (...args: any[]) => any;
};
};
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
import { sqliteFromBlob } from "https://esm.town/v/postpostscript/sqliteBackup";
import { sqliteBlob } from "https://esm.town/v/postpostscript/sqliteBlob";
import { defaultPatterns, sqliteUniverseWithOptions } from "https://esm.town/v/postpostscript/sqliteUniverseBeta";
import { sqlite as sqlitePrivate } from "https://esm.town/v/std/sqlite";
export const sqliteReadonly = sqliteUniverseWithOptions({
interfaces: {
exact: {
"@std/sqlite": sqlitePrivate,
"@std/blob": () => sqliteBlob(),
},
patterns: [
...defaultPatterns,
[
/^blob:\/\//,
({ endpoint, tables }) => {
return sqliteFromBlob({
name: endpoint.replace("blob://", ""),
});
},
],
],
fallback({ endpoint, tables }) {
return sqlitePrivate;
},
},
});