commit 7b77fb788bf5b3f1e98fd245c823465edeaea932
parent dc9a66639823efc07df5577cf79afc6b9520fd31
Author: Florian Dold <dold@taler.net>
Date: Fri, 28 Aug 2026 01:14:28 +0200
idb-bridge: batch SQLite store and index operations
Diffstat:
2 files changed, 467 insertions(+), 65 deletions(-)
diff --git a/packages/idb-bridge/src/SqliteBackend.test.ts b/packages/idb-bridge/src/SqliteBackend.test.ts
@@ -39,10 +39,23 @@ test("sqlite3 backend", async (t) => {
const tx = await backend.enterVersionChange(dbConn, 1);
backend.createObjectStore(tx, "books", "isbn", true);
backend.createIndex(tx, "byName", "books", "name", false, false);
+ backend.createIndex(tx, "byEdition", "books", "edition", true, false);
await backend.storeRecord(tx, {
objectStoreName: "books",
storeLevel: StoreLevel.AllowOverwrite,
- value: { name: "foo" },
+ value: { name: "foo", edition: "first" },
+ key: undefined,
+ });
+ await backend.storeRecord(tx, {
+ objectStoreName: "books",
+ storeLevel: StoreLevel.AllowOverwrite,
+ value: { name: "foo", edition: "second" },
+ key: undefined,
+ });
+ await backend.storeRecord(tx, {
+ objectStoreName: "books",
+ storeLevel: StoreLevel.AllowOverwrite,
+ value: { name: "quux", edition: "third" },
key: undefined,
});
const res = await backend.getObjectStoreRecords(tx, {
@@ -69,6 +82,81 @@ test("sqlite3 backend", async (t) => {
assert.deepStrictEqual(indexRes.values![0].isbn, 1);
assert.deepStrictEqual(indexRes.values![0].name, "foo");
+ const bulkIndexRes = await backend.getIndexRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ indexName: "byName",
+ resultLevel: ResultLevel.Full,
+ range: BridgeIDBKeyRange.only("foo"),
+ });
+ assert.deepStrictEqual(bulkIndexRes.primaryKeys, [1, 2]);
+ assert.deepStrictEqual(
+ bulkIndexRes.values!.map((x: any) => x.edition),
+ ["first", "second"],
+ );
+
+ const bulkObjectRes = await backend.getObjectStoreRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ resultLevel: ResultLevel.Full,
+ range: BridgeIDBKeyRange.bound(1, 3, true, false),
+ });
+ assert.deepStrictEqual(bulkObjectRes.primaryKeys, [2, 3]);
+ assert.deepStrictEqual(
+ bulkObjectRes.values!.map((x: any) => x.edition),
+ ["second", "third"],
+ );
+
+ const allObjectCount = await backend.getObjectStoreRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ resultLevel: ResultLevel.OnlyCount,
+ range: undefined,
+ });
+ assert.deepStrictEqual(allObjectCount.count, 3);
+
+ const boundedObjectCount = await backend.getObjectStoreRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ resultLevel: ResultLevel.OnlyCount,
+ range: BridgeIDBKeyRange.bound(1, 3, true, false),
+ });
+ assert.deepStrictEqual(boundedObjectCount.count, 2);
+
+ const duplicateIndexCount = await backend.getIndexRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ indexName: "byName",
+ resultLevel: ResultLevel.OnlyCount,
+ range: BridgeIDBKeyRange.only("foo"),
+ });
+ assert.deepStrictEqual(duplicateIndexCount.count, 2);
+
+ const boundedIndexCount = await backend.getIndexRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ indexName: "byName",
+ resultLevel: ResultLevel.OnlyCount,
+ range: BridgeIDBKeyRange.bound("foo", "quux", true, false),
+ });
+ assert.deepStrictEqual(boundedIndexCount.count, 1);
+
+ const uniqueIndexCount = await backend.getIndexRecords(tx, {
+ direction: "next",
+ limit: 0,
+ objectStoreName: "books",
+ indexName: "byEdition",
+ resultLevel: ResultLevel.OnlyCount,
+ range: BridgeIDBKeyRange.bound("first", "third"),
+ });
+ assert.deepStrictEqual(uniqueIndexCount.count, 3);
+
await backend.commit(tx);
const tx2 = await backend.beginTransaction(dbConn, ["books"], "readwrite");
diff --git a/packages/idb-bridge/src/SqliteBackend.ts b/packages/idb-bridge/src/SqliteBackend.ts
@@ -443,6 +443,102 @@ export class SqliteBackend implements Backend {
const indexId = await this._provideIndex(connInfo, scopeInfo, indexInfo);
const indexUnique = indexInfo.unique;
+ // IDBIndex.count() does not need keys or values. Walking the index one
+ // entry at a time is especially expensive for the SQLite backend: each
+ // step issues another SELECT. Let SQLite count the bounded range in one
+ // query. Cursor requests can also ask the backend for OnlyCount with a
+ // limit or continuation position, so keep those on the general path.
+ if (
+ req.resultLevel === ResultLevel.OnlyCount &&
+ req.limit === 0 &&
+ req.advanceIndexKey == null &&
+ req.advancePrimaryKey == null &&
+ req.lastIndexPosition == null &&
+ req.lastObjectStorePosition == null
+ ) {
+ const count = await this._countKeyRange({
+ table: indexUnique ? "unique_index_data" : "index_data",
+ ownerColumn: "index_id",
+ owner: indexId,
+ keyColumn: "index_key",
+ range: req.range,
+ });
+ if (this.trackStats) {
+ const k = `${req.objectStoreName}.${req.indexName}`;
+ this.accessStats.readsPerIndex[k] =
+ (this.accessStats.readsPerIndex[k] ?? 0) + 1;
+ this.accessStats.readItemsPerIndex[k] =
+ (this.accessStats.readItemsPerIndex[k] ?? 0) + count;
+ }
+ return {
+ count,
+ indexKeys: [],
+ primaryKeys: undefined,
+ values: undefined,
+ };
+ }
+
+ // IDBIndex.get(), getAll() and getAllKeys() all use a forward request
+ // without a continuation position. The generic cursor implementation
+ // below advances with one SELECT per index entry and, for full records,
+ // another SELECT per object value. Fetch this common shape as one
+ // ordered query while leaving cursor/advance semantics on the established
+ // path.
+ if (
+ req.direction === "next" &&
+ req.resultLevel !== ResultLevel.OnlyCount &&
+ req.advanceIndexKey == null &&
+ req.advancePrimaryKey == null &&
+ req.lastIndexPosition == null &&
+ req.lastObjectStorePosition == null
+ ) {
+ const table = indexUnique ? "unique_index_data" : "index_data";
+ const includeValues = req.resultLevel === ResultLevel.Full;
+ let sql = includeValues
+ ? `SELECT i.index_key, i.object_key, o.value FROM ${table} i` +
+ " LEFT JOIN object_data o" +
+ " ON o.object_store_id = $object_store_id" +
+ " AND o.key = i.object_key"
+ : `SELECT i.index_key, i.object_key FROM ${table} i`;
+ sql += " WHERE i.index_id = $index_id";
+ const params: Record<string, any> = {
+ index_id: indexId,
+ object_store_id: objectStoreId,
+ limit: req.limit <= 0 ? -1 : req.limit,
+ };
+ sql = this._appendKeyRange(sql, params, "i.index_key", req.range);
+ sql += " ORDER BY i.index_key, i.object_key LIMIT $limit";
+ const rows = await (await this._prep(sql)).getAll(params);
+ const indexKeys: IDBValidKey[] = [];
+ const primaryKeys: IDBValidKey[] = [];
+ const values: unknown[] = [];
+ for (const row of rows) {
+ assertDbInvariant(row.index_key instanceof Uint8Array);
+ assertDbInvariant(row.object_key instanceof Uint8Array);
+ indexKeys.push(deserializeKey(row.index_key));
+ primaryKeys.push(deserializeKey(row.object_key));
+ if (includeValues) {
+ if (typeof row.value !== "string") {
+ throw Error("invariant failed: value not found");
+ }
+ values.push(structuredRevive(JSON.parse(row.value)));
+ }
+ }
+ if (this.trackStats) {
+ const k = `${req.objectStoreName}.${req.indexName}`;
+ this.accessStats.readsPerIndex[k] =
+ (this.accessStats.readsPerIndex[k] ?? 0) + 1;
+ this.accessStats.readItemsPerIndex[k] =
+ (this.accessStats.readItemsPerIndex[k] ?? 0) + rows.length;
+ }
+ return {
+ count: rows.length,
+ indexKeys,
+ primaryKeys,
+ values: includeValues ? values : undefined,
+ };
+ }
+
let numResults = 0;
const encPrimaryKeys: Uint8Array[] = [];
const encIndexKeys: Uint8Array[] = [];
@@ -842,6 +938,80 @@ export class SqliteBackend implements Backend {
const objectStoreId = await this._provideObjectStore(connInfo, scopeInfo);
+ // Same fast path as index count above. IDBObjectStore.count() otherwise
+ // turns a record count into one SQL query per key in the store.
+ if (
+ req.resultLevel === ResultLevel.OnlyCount &&
+ req.limit === 0 &&
+ req.advancePrimaryKey == null &&
+ req.lastObjectStorePosition == null
+ ) {
+ const count = await this._countKeyRange({
+ table: "object_data",
+ ownerColumn: "object_store_id",
+ owner: objectStoreId,
+ keyColumn: "key",
+ range: req.range,
+ });
+ if (this.trackStats) {
+ const k = `${req.objectStoreName}`;
+ this.accessStats.readsPerStore[k] =
+ (this.accessStats.readsPerStore[k] ?? 0) + 1;
+ this.accessStats.readItemsPerStore[k] =
+ (this.accessStats.readItemsPerStore[k] ?? 0) + count;
+ }
+ return {
+ count,
+ indexKeys: undefined,
+ primaryKeys: undefined,
+ values: undefined,
+ };
+ }
+
+ // Fast path for IDBObjectStore.get(), getAll() and getAllKeys().
+ if (
+ req.direction === "next" &&
+ req.resultLevel !== ResultLevel.OnlyCount &&
+ req.advancePrimaryKey == null &&
+ req.lastObjectStorePosition == null
+ ) {
+ const includeValues = req.resultLevel === ResultLevel.Full;
+ let sql = includeValues
+ ? "SELECT key, value FROM object_data"
+ : "SELECT key FROM object_data";
+ sql += " WHERE object_store_id = $object_store_id";
+ const params: Record<string, any> = {
+ object_store_id: objectStoreId,
+ limit: req.limit <= 0 ? -1 : req.limit,
+ };
+ sql = this._appendKeyRange(sql, params, "key", req.range);
+ sql += " ORDER BY key LIMIT $limit";
+ const rows = await (await this._prep(sql)).getAll(params);
+ const primaryKeys: IDBValidKey[] = [];
+ const values: unknown[] = [];
+ for (const row of rows) {
+ assertDbInvariant(row.key instanceof Uint8Array);
+ primaryKeys.push(deserializeKey(row.key));
+ if (includeValues) {
+ assertDbInvariant(typeof row.value === "string");
+ values.push(structuredRevive(JSON.parse(row.value)));
+ }
+ }
+ if (this.trackStats) {
+ const k = `${req.objectStoreName}`;
+ this.accessStats.readsPerStore[k] =
+ (this.accessStats.readsPerStore[k] ?? 0) + 1;
+ this.accessStats.readItemsPerStore[k] =
+ (this.accessStats.readItemsPerStore[k] ?? 0) + rows.length;
+ }
+ return {
+ count: rows.length,
+ indexKeys: undefined,
+ primaryKeys,
+ values: includeValues ? values : undefined,
+ };
+ }
+
let currentKey = await this._startObjectKey(objectStoreId, forward);
if (req.advancePrimaryKey != null) {
@@ -949,6 +1119,41 @@ export class SqliteBackend implements Backend {
};
}
+ /** Count an IndexedDB key range directly in one SQLite aggregate. */
+ private async _countKeyRange(req: {
+ table: "index_data" | "unique_index_data" | "object_data";
+ ownerColumn: "index_id" | "object_store_id";
+ owner: SqliteRowid;
+ keyColumn: "index_key" | "key";
+ range: IDBKeyRange | undefined | null;
+ }): Promise<number> {
+ let sql =
+ `SELECT COUNT(*) AS count FROM ${req.table}` +
+ ` WHERE ${req.ownerColumn} = $owner`;
+ const params: Record<string, any> = { owner: req.owner };
+ sql = this._appendKeyRange(sql, params, req.keyColumn, req.range);
+ const row = await (await this._prep(sql)).getFirst(params);
+ return Number(expectDbNumber(row, "count"));
+ }
+
+ /** Add serialized IndexedDB range bounds to a SQLite query. */
+ private _appendKeyRange(
+ sql: string,
+ params: Record<string, any>,
+ keyColumn: "index_key" | "i.index_key" | "key",
+ range: IDBKeyRange | undefined | null,
+ ): string {
+ if (range?.lower != null) {
+ sql += ` AND ${keyColumn} ${range.lowerOpen ? ">" : ">="} $lower`;
+ params.lower = serializeKey(range.lower);
+ }
+ if (range?.upper != null) {
+ sql += ` AND ${keyColumn} ${range.upperOpen ? "<" : "<="} $upper`;
+ params.upper = serializeKey(range.upper);
+ }
+ return sql;
+ }
+
async _startObjectKey(
objectStoreId: number | bigint,
forward: boolean,
@@ -2031,26 +2236,25 @@ export class SqliteBackend implements Backend {
);
}
const objectStoreId = await this._provideObjectStore(connInfo, scopeInfo);
- const metaRes = await (
- await this._prep(sqlGetObjectStoreMetaById)
- ).getFirst({
- id: objectStoreId satisfies SqliteRowid,
- });
- if (metaRes === undefined) {
- throw Error(
- `object store ${JSON.stringify(
- storeReq.objectStoreName,
- )} does not exist`,
- );
+ const keyPath = scopeInfo.keyPath;
+ let keyGenerator = 0;
+ if (scopeInfo.autoIncrement) {
+ // The connection metadata records that a generator exists, but its
+ // changing value remains in SQLite and must be read for each insert.
+ const metaRes = await (
+ await this._prep(sqlGetObjectStoreMetaById)
+ ).getFirst({
+ id: objectStoreId satisfies SqliteRowid,
+ });
+ if (metaRes === undefined) {
+ throw Error(
+ `object store ${JSON.stringify(
+ storeReq.objectStoreName,
+ )} does not exist`,
+ );
+ }
+ keyGenerator = Number(expectDbNumber(metaRes, "auto_increment"));
}
- assertDbInvariant(!!metaRes && typeof metaRes === "object");
- assertDbInvariant("key_path" in metaRes);
- assertDbInvariant("auto_increment" in metaRes);
- const dbKeyPath = metaRes.key_path;
- assertDbInvariant(dbKeyPath === null || typeof dbKeyPath === "string");
- const keyPath = deserializeKeyPath(dbKeyPath);
- const autoIncrement = metaRes.auto_increment;
- assertDbInvariant(typeof autoIncrement === "number");
let key;
let value;
@@ -2075,12 +2279,12 @@ export class SqliteBackend implements Backend {
const storeKeyResult = makeStoreKeyValue({
value: storeReq.value,
key: storeReq.key,
- currentKeyGenerator: autoIncrement,
- autoIncrement: autoIncrement != 0,
+ currentKeyGenerator: keyGenerator,
+ autoIncrement: scopeInfo.autoIncrement,
keyPath: keyPath,
});
- if (autoIncrement != 0) {
+ if (scopeInfo.autoIncrement) {
updatedKeyGenerator = storeKeyResult.updatedKeyGenerator;
}
@@ -2089,14 +2293,13 @@ export class SqliteBackend implements Backend {
}
const serializedObjectKey = serializeKey(key);
-
- const existingObj = await this._getObjectValue(
+ const existingObject = await this._getObjectValue(
objectStoreId,
serializedObjectKey,
);
if (storeReq.storeLevel === StoreLevel.NoOverwrite) {
- if (existingObj) {
+ if (existingObject !== undefined) {
throw new ConstraintError(
`Cannot add a record to object store ${JSON.stringify(
storeReq.objectStoreName,
@@ -2105,6 +2308,38 @@ export class SqliteBackend implements Backend {
}
}
+ // Resolve all index IDs and keys before changing the object. For the
+ // common put/update path, old index entries can be deleted
+ // unconditionally; checking whether the object exists would add a SQL
+ // round trip and does not change the result.
+ const indexRows: Array<{
+ indexId: SqliteRowid;
+ indexInfo: MyIndexMeta;
+ keys: Uint8Array[];
+ }> = [];
+ for (const indexInfo of scopeInfo.indexMap.values()) {
+ const indexId = await this._provideIndex(connInfo, scopeInfo, indexInfo);
+ let keys: Uint8Array[] = [];
+ try {
+ keys = getIndexKeys(value, indexInfo.keyPath, indexInfo.multiEntry).map(
+ serializeKey,
+ );
+ } catch (e) {
+ if (!(e instanceof DataError)) {
+ throw e;
+ }
+ }
+ indexRows.push({ indexId, indexInfo, keys });
+ }
+
+ if (existingObject !== undefined) {
+ await this._deleteObjectFromIndexes(
+ indexRows,
+ serializedObjectKey,
+ structuredRevive(JSON.parse(existingObject)),
+ );
+ }
+
await (
await this._prep(sqlInsertObjectData)
).run({
@@ -2113,7 +2348,7 @@ export class SqliteBackend implements Backend {
value: JSON.stringify(structuredEncapsulate(value)),
});
- if (autoIncrement != 0) {
+ if (scopeInfo.autoIncrement) {
await (
await this._prep(sqlUpdateAutoIncrement)
).run({
@@ -2122,32 +2357,11 @@ export class SqliteBackend implements Backend {
});
}
- for (const [k, indexInfo] of scopeInfo.indexMap.entries()) {
- const indexId = await this._provideIndex(connInfo, scopeInfo, indexInfo);
- if (existingObj) {
- await this.deleteFromIndex(
- indexId,
- indexInfo.unique,
- serializedObjectKey,
- );
- }
-
- try {
- await this.insertIntoIndex(
- storeReq.objectStoreName,
- indexInfo,
- serializedObjectKey,
- value,
- );
- } catch (e) {
- // FIXME: handle this in insertIntoIndex!
- if (e instanceof DataError) {
- // We don't propagate this error here.
- continue;
- }
- throw e;
- }
- }
+ await this._insertIndexRows(
+ storeReq.objectStoreName,
+ indexRows,
+ serializedObjectKey,
+ );
if (this.trackStats) {
this.accessStats.writesPerStore[storeReq.objectStoreName] =
@@ -2159,21 +2373,121 @@ export class SqliteBackend implements Backend {
};
}
- private async deleteFromIndex(
- indexId: SqliteRowid,
- indexUnique: boolean,
+ /** Delete one object's old entries through the indexes' primary keys. */
+ private async _deleteObjectFromIndexes(
+ rows: Array<{ indexId: SqliteRowid; indexInfo: MyIndexMeta }>,
objectKey: Uint8Array,
+ oldValue: unknown,
): Promise<void> {
- let stmt: Sqlite3Statement;
- if (indexUnique) {
- stmt = await this._prep(sqlUniqueIndexDataDeleteKey);
- } else {
- stmt = await this._prep(sqlIndexDataDeleteKey);
+ for (const unique of [false, true]) {
+ const entries: Array<{ indexId: SqliteRowid; key: Uint8Array }> = [];
+ for (const row of rows) {
+ if (row.indexInfo.unique !== unique) {
+ continue;
+ }
+ try {
+ for (const key of getIndexKeys(
+ oldValue,
+ row.indexInfo.keyPath,
+ row.indexInfo.multiEntry,
+ )) {
+ entries.push({ indexId: row.indexId, key: serializeKey(key) });
+ }
+ } catch (e) {
+ if (!(e instanceof DataError)) {
+ throw e;
+ }
+ }
+ }
+ const table = unique ? "unique_index_data" : "index_data";
+ for (let offset = 0; offset < entries.length; offset += 100) {
+ const batch = entries.slice(offset, offset + 100);
+ const params: Record<string, any> = { object_key: objectKey };
+ const predicates = batch.map((entry, i) => {
+ params[`index_id_${i}`] = entry.indexId;
+ params[`index_key_${i}`] = entry.key;
+ return `(index_id = $index_id_${i} AND index_key = $index_key_${i})`;
+ });
+ await (
+ await this._prep(
+ `DELETE FROM ${table} WHERE object_key = $object_key AND (` +
+ predicates.join(" OR ") +
+ ")",
+ )
+ ).run(params);
+ }
+ }
+ }
+
+ /** Insert one object's entries into all indexes in bounded SQL batches. */
+ private async _insertIndexRows(
+ objectStoreName: string,
+ rows: Array<{
+ indexId: SqliteRowid;
+ indexInfo: MyIndexMeta;
+ keys: Uint8Array[];
+ }>,
+ objectKey: Uint8Array,
+ ): Promise<void> {
+ const insert = async (
+ table: "index_data" | "unique_index_data",
+ entries: Array<{ indexId: SqliteRowid; key: Uint8Array }>,
+ conflictingIndex?: MyIndexMeta,
+ ): Promise<void> => {
+ for (let offset = 0; offset < entries.length; offset += 200) {
+ const batch = entries.slice(offset, offset + 200);
+ const params: Record<string, any> = { object_key: objectKey };
+ const tuples = batch.map((entry, i) => {
+ params[`index_id_${i}`] = entry.indexId;
+ params[`index_key_${i}`] = entry.key;
+ return `($index_id_${i}, $object_key, $index_key_${i})`;
+ });
+ try {
+ await (
+ await this._prep(
+ `INSERT INTO ${table} (index_id, object_key, index_key) VALUES ` +
+ tuples.join(", "),
+ )
+ ).run(params);
+ } catch (e: any) {
+ if (e.code === SqliteError.constraintPrimarykey) {
+ const detail = conflictingIndex
+ ? `Unique index ${JSON.stringify(
+ conflictingIndex.currentName,
+ )} on object store ${JSON.stringify(
+ objectStoreName,
+ )} (key path ${JSON.stringify(conflictingIndex.keyPath)})`
+ : `An index on object store ${JSON.stringify(objectStoreName)}`;
+ throw new ConstraintError(
+ `${detail} already contains one of these keys.`,
+ );
+ }
+ throw e;
+ }
+ }
+ };
+
+ // Non-unique indexes cannot conflict with one another, so all their rows
+ // can share one insert. Keep unique indexes in separate statements so a
+ // constraint error still names the index and key path that rejected it.
+ await insert(
+ "index_data",
+ rows.flatMap((row) =>
+ row.indexInfo.unique
+ ? []
+ : row.keys.map((key) => ({ indexId: row.indexId, key })),
+ ),
+ );
+ for (const row of rows) {
+ if (!row.indexInfo.unique) {
+ continue;
+ }
+ await insert(
+ "unique_index_data",
+ row.keys.map((key) => ({ indexId: row.indexId, key })),
+ row.indexInfo,
+ );
}
- await stmt.run({
- index_id: indexId,
- object_key: objectKey,
- });
}
private async insertIntoIndex(
