New Case Study:See how Anthropic automated 95% of dependency reviews with Socket.Learn More →
Socket
Sign inDemoInstall
Socket
Sign inDemoInstall

@peerbit/indexer-sqlite3

Package Overview
Dependencies
Maintainers
0
Versions
123
Alerts
File Explorer

Advanced tools

License
Socket logo

Install Socket

Detect and block malicious and high-risk dependencies

Install

@peerbit/indexer-sqlite3 - npm Package Compare versions

Comparing version 1.1.4 to 1.2.0

dist/src/query-planner.d.ts
dist/src/query-planner.d.ts.map
dist/src/query-planner.js
dist/src/query-planner.js.map
src/query-planner.ts

5

dist/src/engine.d.ts
import { type AbstractType } from "@dao-xyz/borsh";
import type { Index, IndexEngineInitProperties, IndexedResult, Shape } from "@peerbit/indexer-interface";
import * as types from "@peerbit/indexer-interface";
import { QueryPlanner } from "./query-planner.js";
import { type Table } from "./schema.js";

@@ -16,2 +17,3 @@ import type { Database } from "./types.js";

primaryKeyString: string;
planner: QueryPlanner;
private scopeString?;

@@ -21,2 +23,3 @@ private _rootTables;

private _cursor;
private cursorPruner;
iteratorTimeout: number;

@@ -38,3 +41,3 @@ closed: boolean;

fetch: (amount: number) => Promise<IndexedResult[]>;
timeout: ReturnType<typeof setTimeout>;
expire: number;
}>;

@@ -41,0 +44,0 @@ init(properties: IndexEngineInitProperties<T, any>): this;

173

dist/src/engine.js
import { getSchema } from "@dao-xyz/borsh";
import * as types from "@peerbit/indexer-interface";
import { v4 as uuid } from "uuid";
import { MissingFieldError, buildJoin, convertCountRequestToQuery, convertDeleteRequestToQuery, convertFromSQLType, convertSearchRequestToQuery,
import { PlannableQuery, QueryPlanner } from "./query-planner.js";
import { MissingFieldError, buildJoin, convertCountRequestToQuery, convertDeleteRequestToQuery, convertFromSQLType,
/* convertFromSQLType, */
/* convertFromSQLType, */
convertSearchRequestToQuery,
/* getTableName, */

@@ -17,2 +21,3 @@ convertSumRequestToQuery, convertToSQLType, escapeColumnName, generateSelectQuery, getInlineTableFieldName, getSQLTable, getTablePrefixedField, insert, resolveInstanceFromValue, resolveTable, selectAllFieldsFromTable, selectChildren, } from "./schema.js";

primaryKeyString;
planner;
scopeString;

@@ -22,2 +27,3 @@ _rootTables;

_cursor; // TODO choose limit better
cursorPruner;
iteratorTimeout;

@@ -35,6 +41,9 @@ closed = true;

this.iteratorTimeout = options?.iteratorTimeout || 60e3;
this.planner = new QueryPlanner({
exec: this.properties.db.exec.bind(this.properties.db),
});
}
get tables() {
if (this.closed) {
throw new Error("Not started");
throw new types.NotStartedError();
}

@@ -45,3 +54,3 @@ return this._tables;

if (this.closed) {
throw new Error("Not started");
throw new types.NotStartedError();
}

@@ -52,3 +61,3 @@ return this._rootTables;

if (this.closed) {
throw new Error("Not started");
throw new types.NotStartedError();
}

@@ -101,5 +110,35 @@ return this._cursor;

const sqlCreateTable = `create table if not exists ${table.name} (${[...table.fields, ...table.constraints].map((s) => s.definition).join(", ")}) strict`;
const sqlCreateIndex = `create index if not exists ${table.name}_index on ${table.name} (${table.fields.map((field) => escapeColumnName(field.name)).join(", ")})`;
this.properties.db.exec(sqlCreateTable);
this.properties.db.exec(sqlCreateIndex);
/* const fieldsToIndex = table.fields.filter(
(field) =>
field.key !== ARRAY_INDEX_COLUMN && field.key !== table.primary,
);
if (fieldsToIndex.length > 0) {
let arr = fieldsToIndex.map((field) => escapeColumnName(field.name));
const createIndex = async (columns: string[]) => {
const key = createIndexKey(table.name, columns)
const command = `create index if not exists ${key} on ${table.name} (${columns.map((n) => escapeColumnName(n)).join(", ")})`;
await this.properties.db.exec(command);
table.indices.add(key);
const rev = columns.reverse()
const key2 = createIndexKey(table.name, rev)
const command2 = `create index if not exists ${key2} on ${table.name} (${rev.join(", ")})`;
await this.properties.db.exec(command2);
table.indices.add(key2);
}
await createIndex(fieldsToIndex.map(x => x.name));
await createIndex([table.primary as string, ...fieldsToIndex.map(x => x.name)]);
if (arr.length > 1) {
for (const field of fieldsToIndex) {
await createIndex([field.name]);
await createIndex([table.primary as string, field.name]);
}
}
} */
// put and return the id

@@ -115,2 +154,10 @@ let sqlPut = `insert into ${table.name} (${table.fields.map((field) => escapeColumnName(field.name)).join(", ")}) VALUES (${table.fields.map((_x) => "?").join(", ")}) RETURNING ${table.primary};`;

}
this.cursorPruner = setInterval(() => {
const now = Date.now();
for (const [k, v] of this._cursor) {
if (v.expire < now) {
this.clearupIterator(k);
}
}
}, this.iteratorTimeout);
this.closed = false;

@@ -129,2 +176,3 @@ }

this.closed = true;
clearInterval(this.cursorPruner);
await this.clearStatements();

@@ -135,5 +183,10 @@ this._tables.clear();

}
await this.planner.stop();
}
async drop() {
if (this.closed) {
throw new Error(`Already closed index ${this.id}, can not drop`);
}
this.closed = true;
clearInterval(this.cursorPruner);
await this.clearStatements();

@@ -149,2 +202,3 @@ // drop root table and cascade

}
await this.planner.stop();
}

@@ -160,16 +214,24 @@ async resolveDependencies(parentId, table) {

const { join: joinMap, selects } = selectAllFieldsFromTable(table, options?.shape);
const sql = `${generateSelectQuery(table, selects)} ${buildJoin(joinMap, true)} where ${this.primaryKeyString} = ? limit 1`;
const stmt = await this.properties.db.prepare(sql, sql);
const rows = await stmt.get([
table.primaryField?.from?.type
? convertToSQLType(id.key, table.primaryField.from.type)
: id.key,
]);
if (!rows) {
continue;
const sql = `${generateSelectQuery(table, selects)} ${buildJoin(joinMap).join} where ${this.primaryKeyString} = ? limit 1`;
try {
const stmt = await this.properties.db.prepare(sql, sql);
const rows = await stmt.get([
table.primaryField?.from?.type
? convertToSQLType(id.key, table.primaryField.from.type)
: id.key,
]);
if (rows?.[getTablePrefixedField(table, table.primary)] == null) {
continue;
}
return {
value: (await resolveInstanceFromValue(rows, this.tables, table, this.resolveDependencies.bind(this), true, options?.shape)),
id,
};
}
return {
value: (await resolveInstanceFromValue(rows, this.tables, table, this.resolveDependencies.bind(this), true, options?.shape)),
id,
};
catch (error) {
if (this.closed) {
throw new types.NotStartedError();
}
throw error;
}
}

@@ -181,6 +243,6 @@ return undefined;

return insert(async (values, table) => {
const preId = values[table.primaryIndex];
let preId = values[table.primaryIndex];
if (preId != null) {
const statement = this.properties.db.statements.get(replaceStatementKey(table));
await statement.run(values.map((x) => (typeof x === "boolean" ? (x ? 1 : 0) : x)));
await statement.run(values);
await statement.reset?.();

@@ -191,3 +253,3 @@ return preId;

const statement = this.properties.db.statements.get(putStatementKey(table));
const out = await statement.get(values.map((x) => (typeof x === "boolean" ? (x ? 1 : 0) : x)));
const out = await statement.get(values);
await statement.reset?.();

@@ -218,2 +280,7 @@ // TODO types

let sqlFetch = undefined;
const normalizedQuery = new PlannableQuery({
query: types.toQuery(request?.query),
sort: request?.sort,
});
let planningScope;
/* let totalCount: undefined | number = undefined; */

@@ -223,23 +290,33 @@ const fetch = async (amount) => {

if (!once) {
let { sql, bindable: toBind } = convertSearchRequestToQuery(request, this.tables, this._rootTables, {
planningScope = this.planner.scope(normalizedQuery);
let { sql, bindable: toBind } = convertSearchRequestToQuery(normalizedQuery, this.tables, this._rootTables, {
planner: planningScope,
shape: options?.shape,
stable: typeof amount === "number", // if we are to fetch all, we dont need stable sorting
fetchAll: amount === "all", // if we are to fetch all, we dont need stable sorting
});
sqlFetch = sql;
bindable = toBind;
await planningScope.beforePrepare();
stmt = await this.properties.db.prepare(sqlFetch, sqlFetch);
// stmt.reset?.(); // TODO dont invoke reset if not needed
/* countStmt.reset?.(); */
// Bump timeout timer
clearTimeout(iterator.timeout);
iterator.timeout = setTimeout(() => this.clearupIterator(requestId), this.iteratorTimeout);
iterator.expire = Date.now() + this.iteratorTimeout;
}
once = true;
const allResults = await stmt.all([
/* console.log("----------------------")
console.log(sqlFetch); */
const allResults = await planningScope.perform(async () => {
const allResults = await stmt.all([
...bindable,
...(amount !== "all" ? [amount, offset] : []),
]);
return allResults;
});
/* const allResults: Record<string, any>[] = await stmt.all([
...bindable,
amount === "all" ? Number.MAX_SAFE_INTEGER : amount,
offset,
...(amount !== "all" ? [amount,
offset] : [])
]);
*/
let results = await Promise.all(allResults.map(async (row) => {
let selectedTable = this._rootTables.find((table /* row["table_name"] === table.name, */) => row[getTablePrefixedField(table, this.primaryKeyString)] !=
let selectedTable = this._rootTables.find((table) => row[getTablePrefixedField(table, this.primaryKeyString)] !=
null);

@@ -253,11 +330,5 @@ const value = await resolveInstanceFromValue(row, this.tables, selectedTable, this.resolveDependencies.bind(this), true, options?.shape);

offset += results.length;
/* if (results.length > 0) {
totalCount =
totalCount ??
(await this.count(
request,
));
iterator.kept = totalCount - results.length - offsetStart;
} else {
iterator.kept = 0;
/* const uniqueIds = new Set(results.map((x) => x.id.primitive));
if (uniqueIds.size !== results.length) {
throw new Error("Duplicate ids in result set");
} */

@@ -267,3 +338,2 @@ if (amount === "all" || results.length < amount) {

await this.clearupIterator(requestId);
clearTimeout(iterator.timeout);
}

@@ -275,3 +345,3 @@ return results;

/* countStatement: countStmt, */
timeout: setTimeout(() => this.clearupIterator(requestId), this.iteratorTimeout),
expire: Date.now() + this.iteratorTimeout,
};

@@ -285,5 +355,5 @@ this.cursor.set(requestId, iterator);

while (true) {
const res = await fetch(100);
const res = await fetch("all");
results.push(...res);
if (res.length === 0) {
if (hasMore === false) {
break;

@@ -320,3 +390,2 @@ }

}
clearTimeout(cache.timeout);
/* cache.countStatement.finalize?.(); */

@@ -503,5 +572,13 @@ // await cache.fetchStatement.finalize?.();

}
for (const index of this.indices) {
await index.index.drop();
if (!this.properties.parent) {
for (const index of this.indices) {
await index.index.stop();
}
await this.properties.db.drop();
}
else {
for (const index of this.indices) {
await index.index.drop();
}
}
this.scopes.clear();

@@ -508,0 +585,0 @@ }

38

dist/src/schema.d.ts
import { type AbstractType, type Constructor, type Field, type FieldType } from "@dao-xyz/borsh";
import * as types from "@peerbit/indexer-interface";
import { type PlanningSession } from "./query-planner.js";
export type SQLLiteValue = string | number | null | bigint | Uint8Array | Int8Array | ArrayBuffer;
export type BindableValue = string | bigint | number | Uint8Array | Int8Array | ArrayBuffer | null;
export declare const u64ToI64: (u64: bigint | number) => bigint;
export declare const i64ToU64: (i64: number | bigint) => bigint;
export declare const convertToSQLType: (value: boolean | bigint | string | number | Uint8Array, type?: FieldType) => BindableValue;
export declare const escapeColumnName: (name: string) => string;
export declare const escapeColumnName: (name: string, char?: string) => string;
export declare class MissingFieldError extends Error {

@@ -19,2 +22,3 @@ constructor(message: string);

from: Field | undefined;
unwrappedType: FieldType | undefined;
path: string[];

@@ -42,5 +46,6 @@ describesExistenceOfAnother?: string;

isSimpleValue: boolean;
indices: Set<string>;
}
export declare const getSQLTable: (ctor: AbstractType<any>, path: string[], primary: string | false, inline: boolean, addJoinField: ((fields: SQLField[], constraints: SQLConstraint[]) => void) | undefined, fromOptionalField?: boolean) => Table[];
export declare const getTableName: (path: string[] | undefined, clazz: string | Constructor<any>) => string;
export declare const getTableName: (path: string[] | undefined, clazz: string | Constructor<any>) => any;
export declare const CHILD_TABLE_ID = "__id";

@@ -69,5 +74,7 @@ export declare const ARRAY_INDEX_COLUMN = "__index";

}[];
joins: Map<string, JoinTable>;
joins: Map<string, JoinOrRootTable>;
groupBy: string | undefined;
}[];
export declare const selectAllFieldsFromTable: (table: Table, shape: types.Shape | undefined) => {
groupBy: string | undefined;
selects: {

@@ -93,5 +100,9 @@ from: string;

};
export declare const convertSearchRequestToQuery: (request: types.IterateOptions | undefined, tables: Map<string, Table>, rootTables: Table[], options?: {
export declare const convertSearchRequestToQuery: (request: {
query: types.Query[];
sort?: types.Sort[] | types.Sort;
} | undefined, tables: Map<string, Table>, rootTables: Table[], options?: {
shape?: types.Shape | undefined;
stable?: boolean;
fetchAll?: boolean;
planner?: PlanningSession;
}) => {

@@ -101,12 +112,25 @@ sql: string;

};
export declare const buildJoin: (joinBuilder: Map<string, JoinTable>, resolveAllColumns: boolean) => string;
export declare const convertQueryToSQLQuery: (query: types.Query, tables: Map<string, Table>, table: Table, joinBuilder: Map<string, JoinTable>, path?: string[], tableAlias?: string | undefined) => {
export declare const buildJoin: (joinBuilder: Map<string, JoinOrRootTable>, options?: {
planner?: PlanningSession;
}) => {
join: string;
};
export declare const convertQueryToSQLQuery: (query: types.Query, tables: Map<string, Table>, table: Table, joinBuilder: Map<string, JoinOrRootTable>, path: string[], tableAlias: string | undefined, skipKeys: number) => {
where: string;
bindable: any[];
};
type JoinOrRootTable = JoinTable | RootTable;
type JoinTable = {
table: Table;
as: string;
type: "left" | "cross";
columns: string[];
};
type RootTable = {
type: "root";
table: Table;
as: string;
columns: string[];
};
export {};
//# sourceMappingURL=schema.d.ts.map

493

dist/src/schema.js

@@ -11,4 +11,5 @@ var __decorate = (this && this.__decorate) || function (decorators, target, key, desc) {

import { FixedArrayKind, OptionKind, VecKind, WrappedType, deserialize, field as fieldDecalaration, getDependencies, getSchema, serialize, variant, } from "@dao-xyz/borsh";
import { toHexString } from "@peerbit/crypto";
import { fromHexString, toHexString } from "@peerbit/crypto";
import * as types from "@peerbit/indexer-interface";
import { flattenQuery } from "./query-planner.js";
const SQLConversionMap = {

@@ -31,2 +32,8 @@ u8: "INTEGER",

const WRAPPED_SIMPLE_VALUE_VARIANT = "wrapped";
let JSON_GROUP_ARRAY = "json_group_array";
let JSON_OBJECT = "distinct json_object";
export const u64ToI64 = (u64) => {
return (typeof u64 === "number" ? BigInt(u64) : u64) - 9223372036854775808n;
};
export const i64ToU64 = (i64) => (typeof i64 === "number" ? BigInt(i64) : i64) + 9223372036854775808n;
export const convertToSQLType = (value, type) => {

@@ -38,2 +45,6 @@ // add bigint when https://github.com/TryGhost/node-sqlite3/pull/1501 fixed

}
if (type === "u64") {
// shift to fit in i64
return u64ToI64(value);
}
}

@@ -43,3 +54,3 @@ return value;

const nullAsUndefined = (value) => (value === null ? undefined : value);
export const escapeColumnName = (name) => `"${name}"`;
export const escapeColumnName = (name, char = '"') => `${char}${name}${char}`;
export class MissingFieldError extends Error {

@@ -68,5 +79,9 @@ constructor(message) {

if (type === "u64") {
return typeof value === "number" || typeof value === "string"
? BigInt(value)
: nullAsUndefined(value);
if (typeof value === "number" || typeof value === "bigint") {
return i64ToU64(value); // TODO is not always value type bigint?
}
if (value == null) {
return nullAsUndefined(value);
}
throw new Error(`Unexpected value type for value ${value} expected number or bigint for u64 field`);
}

@@ -123,2 +138,3 @@ return nullAsUndefined(value);

inline,
indices: new Set(),
};

@@ -155,2 +171,9 @@ ret.push(table);

export const getTableName = (path = [], clazz) => {
let pathKey = path.length > 0 ? path.join("__") + "__" : "";
if (typeof clazz !== "string") {
const tableName = clazz["__table_" + pathKey];
if (tableName) {
return tableName;
}
}
let name = typeof clazz === "string" ? clazz : getNameOfClass(clazz);

@@ -160,4 +183,6 @@ // prefix the generated table name so that the name is a valid SQL identifier (table name)

// leading _ to allow path to have numbers
const ret = (path.length > 0 ? path.join("__") + "__" : "") +
name.replace(/[^a-zA-Z0-9_]/g, "_");
const ret = pathKey + name.replace(/[^a-zA-Z0-9_]/g, "_");
if (typeof clazz !== "string") {
clazz["__table_" + pathKey] = ret;
}
return ret;

@@ -185,8 +210,8 @@ };

// we resolve primary field here since it might be unknown until this point
const primaryField = primary != null
const parentPrimaryField = primary != null
? sqlFields.find((field) => field.name === primary)
: undefined;
const parentPrimaryFieldName = primaryField?.key || CHILD_TABLE_ID;
const parentPrimaryFieldType = primaryField
? primaryField.type
const parentPrimaryFieldName = parentPrimaryField?.key || CHILD_TABLE_ID;
const parentPrimaryFieldType = parentPrimaryField
? parentPrimaryField.type
: "INTEGER";

@@ -200,2 +225,3 @@ fields.unshift({

from: undefined,
unwrappedType: undefined,
path: [CHILD_TABLE_ID],

@@ -209,4 +235,5 @@ },

type: parentPrimaryFieldType,
from: parentPrimaryField?.from,
unwrappedType: parentPrimaryField?.unwrappedType,
isPrimary: false,
from: undefined,
path: [PARENT_TABLE_ID],

@@ -270,2 +297,3 @@ });

from: undefined,
unwrappedType: undefined,
path: [ARRAY_INDEX_COLUMN],

@@ -293,2 +321,3 @@ },

from: field,
unwrappedType: unwrapNestedType(field.type),
path: [...path.slice(1), key],

@@ -368,2 +397,3 @@ });

from: undefined,
unwrappedType: undefined,
path: [...path.slice(1), key],

@@ -455,3 +485,3 @@ describesExistenceOfAnother: path[path.length - 1],

if (ctor) {
clazzName = getNameOfClass(ctor);
clazzName = ctor;
break;

@@ -545,3 +575,3 @@ }

}
bindableValues[bindableValues.length - 1] = false; // assign the value "false" to the exist field column
bindableValues[bindableValues.length - 1] = 0; // assign the value "false" to the exist field column
continue;

@@ -553,3 +583,3 @@ }

if (field.type instanceof OptionKind) {
bindableValues.push(true); // assign the value "true" to the exist field column
bindableValues.push(1); // assign the value "true" to the exist field column
}

@@ -638,3 +668,3 @@ return undefined;

export const generateSelectQuery = (table, selects) => {
return `SELECT ${selects.map((x) => `${x.from} as ${x.as}`).join(", ")} FROM ${table.name}`;
return `select ${selects.map((x) => `${x.from} as ${x.as}`).join(", ")} FROM ${table.name}`;
};

@@ -644,7 +674,6 @@ export const selectAllFieldsFromTables = (tables, shape) => {

for (const table of tables) {
const { selects, join: joinFromSelect } = selectAllFieldsFromTable(table, shape);
selectsPerTable.push({ selects, joins: joinFromSelect });
const { selects, join: joinFromSelect, groupBy, } = selectAllFieldsFromTable(table, shape);
selectsPerTable.push({ selects, joins: joinFromSelect, groupBy });
}
// pad with empty selects to make sure all selects have the same length
/* const maxSelects = Math.max(...selectsPerTable.map(x => x.selects.length)); */
let newSelects = [];

@@ -664,6 +693,2 @@ for (const [i, selects] of selectsPerTable.entries()) {

newSelects.push(newSelect);
/* let pad = 0;
while (select.selects.length < maxSelects) {
select.selects.push({ from: "NULL", as: `'pad#${++pad}'` });
} */
}

@@ -680,5 +705,55 @@ // also return table name

const fieldResolvers = [];
let groupByParentId = false;
for (const tableAndShape of stack) {
if (!tableAndShape.table.inline) {
if (tableAndShape.table.referencedInArray) {
let selectBuilder = `${JSON_GROUP_ARRAY}(${JSON_OBJECT}(`;
groupByParentId = true; // we need to group by the parent id as else we will not be returned with more than 1 result
let first = false;
const as = createReconstructReferenceName(tableAndShape.table);
for (const field of tableAndShape.table.fields) {
if ((field.isPrimary ||
!tableAndShape.shape ||
matchFieldInShape(tableAndShape.shape, [], field) ||
// also always include the index field
field.name === ARRAY_INDEX_COLUMN) &&
field.name !== PARENT_TABLE_ID) {
let resolveField = `${as}.${escapeColumnName(field.name)}`;
// if field is bigint we need to convert it to string, so that later in a JSON.parse scenario it is not converted to a number, but remains a string until we can convert it back to a bigint manually
if (field.unwrappedType === "u64") {
resolveField = `CAST(${resolveField} AS TEXT)`;
}
// if field is blob we need to convert it to hex string
if (field.type === "BLOB") {
resolveField = `HEX(${resolveField})`;
}
if (first) {
selectBuilder += `, `;
}
first = true;
selectBuilder += `${escapeColumnName(field.name, "'")}, ${resolveField}`;
}
}
selectBuilder += `)) `; // FILTER (WHERE ${tableAndShape.table.name}.${tableAndShape.table.primary} IS NOT NULL)
fieldResolvers.push({
from: selectBuilder,
as,
});
join.set(createReconstructReferenceName(tableAndShape.table), {
as,
table: tableAndShape.table,
type: "left",
columns: [],
});
}
else if (!tableAndShape.table.inline) {
// we end up here when we have simple joins we want to make that are not arrays, and not inlined
if (tableAndShape.table.parent != null) {
join.set(createReconstructReferenceName(tableAndShape.table), {
as: tableAndShape.table.name,
table: tableAndShape.table,
type: "left",
columns: [],
});
}
for (const field of tableAndShape.table.fields) {
if (field.isPrimary ||

@@ -695,5 +770,2 @@ !tableAndShape.shape ||

for (const child of tableAndShape.table.children) {
if (child.referencedInArray) {
continue;
}
let childShape = undefined;

@@ -716,5 +788,2 @@ if (tableAndShape.shape) {

stack.push({ table: child, shape: childShape });
if (!child.inline) {
join.set(child.name, { as: child.name, table: child });
}
}

@@ -726,2 +795,6 @@ }

return {
groupBy: groupByParentId
? `${table.name}.${escapeColumnName(table.primary)}` ||
undefined
: undefined,
selects: fieldResolvers, // `SELECT ${fieldResolvers.join(", ")} FROM ${table.name}`,

@@ -761,10 +834,44 @@ join,

if (isArray) {
let once = false;
/* let once = false; */
let resolvedArr = [];
for (const subtable of subTables) {
// TODO types
let rootTable = getNonInlinedTable(table);
const arr = await resolveChildren(fromTablePrefixedValues[getTablePrefixedField(rootTable, rootTable.primary, !tablePrefixed)], subtable);
if (arr) {
once = true;
// check if the array already in the provided row
let arr = undefined;
const tableName = createReconstructReferenceName(subtable);
if (fromTablePrefixedValues[tableName]) {
arr = JSON.parse(fromTablePrefixedValues[tableName]);
arr = arr.filter((x) => x[subtable.primary] != null);
// we need to go over all fields that are to be bigints and convert
// them back to bigints
// for blob fields we need to convert them back to Uint8Array
for (const field of subtable.fields) {
if (field.name === PARENT_TABLE_ID) {
continue;
}
if (field.unwrappedType === "u64") {
for (const item of arr) {
item[field.name] = BigInt(item[field.name]);
}
}
else if (field.type === "BLOB") {
for (const item of arr) {
item[field.name] = fromHexString(item[field.name]);
}
}
}
}
else {
if (subtable.children) {
// TODO we only end up where when we resolve nested arrays,
// which shoulld instead be resolved in a nested select (with json_group_array and json_object)
let rootTable = getNonInlinedTable(table);
const parentId = fromTablePrefixedValues[getTablePrefixedField(rootTable, rootTable.primary, !tablePrefixed)];
arr = await resolveChildren(parentId, subtable);
}
else {
arr = [];
}
}
if (arr && arr.length > 0) {
/* once = true; */
for (const element of arr) {

@@ -779,8 +886,3 @@ const resolved = await resolveInstanceFromValue(element, tables, subtable, // TODO fix

}
if (!once) {
obj[field.key] = undefined;
}
else {
obj[field.key] = resolvedArr;
}
obj[field.key] = resolvedArr; // we can not do option(vec('T')) since we dont store the option type for Arrays (TODO)
}

@@ -874,3 +976,3 @@ else {

export const convertDeleteRequestToQuery = (request, tables, table) => {
const { query, bindable } = convertRequestToQuery("delete", request, tables, table);
const { query, bindable } = convertRequestToQuery("delete", { query: types.toQuery(request.query) }, tables, table);
return {

@@ -882,3 +984,3 @@ sql: `DELETE FROM ${table.name} WHERE ${table.primary} IN (SELECT ${table.primary} from ${table.name} ${query}) returning ${table.primary}`,

export const convertSumRequestToQuery = (request, tables, table) => {
const { query, bindable } = convertRequestToQuery("sum", request, tables, table);
const { query, bindable } = convertRequestToQuery("sum", { query: types.toQuery(request.query), key: request.key }, tables, table);
const inlineName = getInlineTableFieldName(request.key);

@@ -896,3 +998,3 @@ const field = table.fields.find((x) => x.name === inlineName);

export const convertCountRequestToQuery = (request, tables, table) => {
const { query, bindable } = convertRequestToQuery("count", request, tables, table);
const { query, bindable } = convertRequestToQuery("count", { query: request?.query ? types.toQuery(request.query) : undefined }, tables, table);
return {

@@ -903,2 +1005,35 @@ sql: `SELECT count(*) as count FROM ${table.name} ${query}`,

};
const buildOrderBy = (sort, tables, table, joinBuilder, resolverBuilder, path = [], options) => {
let orderByBuilder = undefined;
if ((!sort || (Array.isArray(sort) && sort.length === 0)) &&
!options?.fetchAll) {
sort =
table.primary && path.length === 0
? [{ key: [table.primary], direction: types.SortDirection.ASC }]
: undefined;
}
if (sort) {
let sortArr = Array.isArray(sort) ? sort : [sort];
if (sortArr.length > 0) {
orderByBuilder = "";
let once = false;
for (const sort of sortArr) {
const { foreignTables, queryKey } = resolveTableToQuery(table, tables, joinBuilder, [...path, ...sort.key], undefined, true);
for (const foreignTable of foreignTables) {
if (once) {
orderByBuilder += ", ";
}
once = true;
foreignTable.columns.push(queryKey); // add the sort key to the list of columns that will be used for this query
orderByBuilder += `"${foreignTable.as}#${queryKey}" ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`;
resolverBuilder.push({
from: `${table.name}.${escapeColumnName(queryKey)}`,
as: `'${foreignTable.as}#${queryKey}'`,
});
}
}
}
}
return { orderByBuilder };
};
export const convertSearchRequestToQuery = (request, tables, rootTables, options) => {

@@ -912,17 +1047,15 @@ let unionBuilder = "";

for (const [i, table] of rootTables.entries()) {
const { selects, joins: joinFromSelect } = selectsPerTable[i];
const selectQuery = generateSelectQuery(table, selects);
const { selects, joins, groupBy } = selectsPerTable[i];
try {
const { orderBy, query, bindable } = convertRequestToQuery("iterate", request, tables, table, joinFromSelect, [], {
stable: options?.stable,
});
unionBuilder += `${unionBuilder.length > 0 ? " UNION ALL " : ""} ${selectQuery} ${query}`;
orderByClause =
orderBy?.length > 0
? orderByClause.length > 0
? orderByClause + ", " + orderBy
: orderBy
: orderByClause;
matchedOnce = true;
bindableBuilder.push(...bindable);
const { orderByBuilder } = buildOrderBy(request?.sort, tables, table, joins, selects, [], options);
if (!orderByClause && orderByBuilder) {
// assume all order by clauses will be the same
orderByClause =
orderByBuilder.length > 0
? orderByClause.length > 0
? orderByClause + ", " + orderByBuilder
: orderByBuilder
: orderByClause;
}
//orderByAddedOnce = true;
}

@@ -936,2 +1069,20 @@ catch (error) {

}
const selectQuery = generateSelectQuery(table, selects);
for (const flattenRequest of flattenQuery(request)) {
try {
const { query, bindable } = convertRequestToQuery("iterate", flattenRequest, tables, table, new Map(joins), // copy the map, else we might might do unececessary joins
[], options);
unionBuilder += `${unionBuilder.length > 0 ? " UNION " : ""} ${selectQuery} ${query} ${groupBy ? "GROUP BY " + groupBy : ""}`;
matchedOnce = true;
bindableBuilder.push(...bindable);
}
catch (error) {
if (error instanceof MissingFieldError) {
lastError = error;
orderByClause = "";
continue;
}
throw error;
}
}
}

@@ -942,18 +1093,32 @@ if (!matchedOnce) {

return {
sql: `${unionBuilder} ${orderByClause ? "ORDER BY " + orderByClause : ""} limit ? offset ?`,
sql: `${unionBuilder} ${orderByClause ? "ORDER BY " + orderByClause : ""} ${options?.fetchAll ? "" : "limit ? offset ?"}`,
bindable: bindableBuilder,
};
};
function isIterateRequest(request, type) {
return type === "iterate";
}
const getOrSetRootTable = (joinBuilder, table) => {
const refName = createQueryTableReferenceName(table);
let ref = joinBuilder.get(refName);
if (ref) {
return ref;
}
const join = {
// add the root as a join even though it is not, just so we can collect the columns it will be queried
table: table,
type: "root",
as: table.name,
columns: [],
};
joinBuilder.set(refName, join);
return join;
};
const convertRequestToQuery = (type, request, tables, table, extraJoin, path = [], options) => {
let whereBuilder = "";
let bindableBuilder = [];
let orderByBuilder = undefined;
/* let orderByBuilder: string | undefined = undefined; */
/* let tablesToSelect: string[] = [table.name]; */
let joinBuilder = extraJoin || new Map();
getOrSetRootTable(joinBuilder, table);
const coercedQuery = types.toQuery(request?.query);
if (coercedQuery.length === 1) {
const { where, bindable } = convertQueryToSQLQuery(coercedQuery[0], tables, table, joinBuilder, path);
const { where, bindable } = convertQueryToSQLQuery(coercedQuery[0], tables, table, joinBuilder, path, undefined, 0);
whereBuilder += where;

@@ -963,9 +1128,12 @@ bindableBuilder.push(...bindable);

else if (coercedQuery.length > 1) {
const { where, bindable } = convertQueryToSQLQuery(new types.And(coercedQuery), tables, table, joinBuilder, path);
const { where, bindable } = convertQueryToSQLQuery(new types.And(coercedQuery), tables, table, joinBuilder, path, undefined, 0);
whereBuilder += where;
bindableBuilder.push(...bindable);
}
if (isIterateRequest(request, type)) {
/* if (isIterateRequest(request, type)) {
let sort = request?.sort;
if (!sort && options?.stable) {
if (
(!sort || (Array.isArray(sort) && sort.length === 0)) &&
!options?.fetchAll
) {
sort =

@@ -976,2 +1144,3 @@ table.primary && path.length === 0

}
if (sort) {

@@ -983,4 +1152,12 @@ let sortArr = Array.isArray(sort) ? sort : [sort];

for (const sort of sortArr) {
const { foreignTables, queryKey } = resolveTableToQuery(table, tables, joinBuilder, [...path, ...sort.key], undefined, true);
for (const table of foreignTables) {
const { foreignTables, queryKey } = resolveTableToQuery(
table,
tables,
joinBuilder,
[...path, ...sort.key],
undefined,
true,
);
for (const foreignTable of foreignTables) {
if (once) {

@@ -990,14 +1167,11 @@ orderByBuilder += ", ";

once = true;
orderByBuilder += `${table.as}.${queryKey} ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`;
foreignTable.columns.push(queryKey); // add the sort key to the list of columns that will be used for this query
orderByBuilder += `${foreignTable.as}.${queryKey} ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`;
}
}
/* orderByBuilder += request.sort
.map(
(sort) =>
`${table.name}.${sort.key} ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`
)
.join(", "); */
}
}
}
} */
const where = whereBuilder.length > 0 ? "where " + whereBuilder : undefined;

@@ -1007,16 +1181,44 @@ if (extraJoin && extraJoin.size > 0) {

}
let join = buildJoin(joinBuilder, type === "iterate" ? true : false);
let { join } = buildJoin(joinBuilder, options);
const query = `${join ? join : ""} ${where ? where : ""}`;
return {
query,
orderBy: orderByBuilder,
/* orderBy: orderByBuilder, */
bindable: bindableBuilder,
};
};
export const buildJoin = (joinBuilder, resolveAllColumns) => {
let joinTypeDefault = resolveAllColumns
? /* "FULL OUTER JOIN" */ "LEFT OUTER JOIN"
: "JOIN";
export const buildJoin = (joinBuilder, options) => {
/* let joinTypeDefault = resolveAllColumns
? "CROSS JOIN"
: "JOIN"; */
let join = "";
for (const [_key, table] of joinBuilder) {
if (table.type !== "root") {
continue;
}
const out = _buildJoin(table, options);
join += out.join;
}
for (const [_key, table] of joinBuilder) {
if (table.type === "root") {
continue;
}
const out = _buildJoin(table, options);
join += out.join;
}
return { join };
};
const _buildJoin = (table, options) => {
let join = "";
let indexedBy = undefined;
if (table.type !== "root") {
table.columns.push(PARENT_TABLE_ID); // we unshift because we join on the parent id before where clause
}
if (table.columns.length > 0) {
const usedColumns = removeDuplicatesOrdered(table.columns);
indexedBy = options?.planner
? ` INDEXED BY ${options.planner.resolveIndex(table.table.name, usedColumns)} `
: "";
}
if (table.type !== "root") {
let nonInlinedParent = table.table.parent && getNonInlinedTable(table.table.parent);

@@ -1026,8 +1228,9 @@ if (!nonInlinedParent) {

}
let joinType = table.table.referencedInArray
? /* "FULL OUTER JOIN" */ "LEFT OUTER JOIN"
: joinTypeDefault;
join += `${joinType} ${table.table.name} AS ${table.as} ON ${nonInlinedParent.name}.${nonInlinedParent.primary} = ${table.as}.${PARENT_TABLE_ID} `;
let joinType = table.type === "cross" ? "LEFT JOIN" : "LEFT JOIN";
join += ` ${joinType} ${table.table.name} AS ${table.as} ${indexedBy} ON ${nonInlinedParent.name}.${nonInlinedParent.primary} = ${table.as}.${PARENT_TABLE_ID} `;
}
return join;
else if (indexedBy) {
join += indexedBy;
}
return { join };
};

@@ -1039,9 +1242,9 @@ const insertMapIntoMap = (map, insert) => {

};
export const convertQueryToSQLQuery = (query, tables, table, joinBuilder, path = [], tableAlias = undefined) => {
export const convertQueryToSQLQuery = (query, tables, table, joinBuilder, path, tableAlias, skipKeys) => {
let whereBuilder = "";
let bindableBuilder = [];
/* let tablesToSelect: string[] = []; */
const handleAnd = (queries, path, tableAlias) => {
const handleAnd = (queries, path, tableAlias, keysOffset) => {
for (const query of queries) {
const { where, bindable } = convertQueryToSQLQuery(query, tables, table, joinBuilder, path, tableAlias);
const { where, bindable } = convertQueryToSQLQuery(query, tables, table, joinBuilder, path, tableAlias, keysOffset);
whereBuilder =

@@ -1053,3 +1256,3 @@ whereBuilder.length > 0 ? `(${whereBuilder}) AND (${where})` : where;

if (query instanceof types.StateFieldQuery) {
const { where, bindable } = convertStateFieldQuery(query, tables, table, joinBuilder, path, tableAlias);
const { where, bindable } = convertStateFieldQuery(query, tables, table, joinBuilder, path, tableAlias, skipKeys);
whereBuilder += where;

@@ -1060,14 +1263,15 @@ bindableBuilder.push(...bindable);

let joinPrefix = "__" + String(tables.size);
path = [...path, query.path];
handleAnd(query.query, path, joinPrefix);
path = [...path, ...query.path];
let newSkipKeys = skipKeys + query.path.length;
handleAnd(query.query, path, joinPrefix, newSkipKeys);
}
else if (query instanceof types.LogicalQuery) {
if (query instanceof types.And) {
handleAnd(query.and, path, tableAlias);
handleAnd(query.and, path, tableAlias, skipKeys);
}
else if (query instanceof types.Or) {
for (const subquery of query.or) {
const { where, bindable } = convertQueryToSQLQuery(subquery, tables, table, joinBuilder, path, tableAlias);
const { where, bindable } = convertQueryToSQLQuery(subquery, tables, table, joinBuilder, path, tableAlias, skipKeys);
whereBuilder =
whereBuilder.length > 0 ? `(${whereBuilder}) OR (${where})` : where;
whereBuilder.length > 0 ? `(${whereBuilder}) OR(${where})` : where;
bindableBuilder.push(...bindable);

@@ -1077,4 +1281,4 @@ }

else if (query instanceof types.Not) {
const { where, bindable } = convertQueryToSQLQuery(query.not, tables, table, joinBuilder, path, tableAlias);
whereBuilder = `NOT (${where})`;
const { where, bindable } = convertQueryToSQLQuery(query.not, tables, table, joinBuilder, path, tableAlias, skipKeys);
whereBuilder = `NOT(${where})`;
bindableBuilder.push(...bindable);

@@ -1097,8 +1301,12 @@ }

};
const createTableReferenceName = (table, alias, fieldType, joinSize) => {
if (!alias &&
(fieldType instanceof VecKind ||
(fieldType instanceof OptionKind &&
fieldType.elementType instanceof VecKind))) {
let aliasSuffix = "_" + String(joinSize);
/* const createQueryTableReferenceName = (
table: Table,
alias: string | undefined,
) => {
if (
!alias
) {
let aliasSuffix =
"_query"; // "_" + String(joinSize); TODO this property will make every join unique, which is not wanted unless (ever?) since we can do OR in SQL which means we can do one join and perform AND/OR logic without joining multiple times to apply multiple conditions
alias = aliasSuffix;

@@ -1108,3 +1316,9 @@ }

return tableNameAs;
}; */
const createQueryTableReferenceName = (table) => {
return table.parent == null ? table.name : "_query_" + table.name;
};
const createReconstructReferenceName = (table) => {
return table.name; /* table.parent == null ? table.name : "_rec_" + table.name; */
};
const resolveTableToQuery = (table, tables, join, path, alias, searchSelf) => {

@@ -1124,7 +1338,14 @@ // we are matching in two ways.

queryKey: field.name,
foreignTables: [{ table, as: table.name }],
foreignTables: [getOrSetRootTable(join, table)],
};
}
}
let currentTables = [{ table, as: alias || table.name }];
let currentTables = [
{
table,
as: alias || table.name,
type: "cross",
columns: [],
},
];
let prevTables = undefined;

@@ -1140,9 +1361,16 @@ // outer:

// second arg is needed because of polymorphic fields we might end up here intentially to check what tables to query
throw new MissingFieldError(`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))}`);
throw new MissingFieldError(`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))} `);
}
for (const child of currentTable.children) {
const tableNameAs = createTableReferenceName(child, alias, field.type, join.size);
const tableNameAs = createQueryTableReferenceName(child);
let isMatching = child.parentPath[child.parentPath.length - 1] === key;
if (isMatching) {
const tableWithAlias = { table: child, as: tableNameAs };
const tableWithAlias = {
columns: [],
table: child,
as: tableNameAs,
type: currentTable.children.length > 1
? "left"
: "cross",
};
if (child.isSimpleValue) {

@@ -1194,3 +1422,3 @@ if (!child.inline) {

};
const convertStateFieldQuery = (query, tables, table, join, path, tableAlias = undefined) => {
const convertStateFieldQuery = (query, tables, table, join, path, tableAlias, skipKeys) => {
// if field id represented as foreign table, do join and compare

@@ -1201,3 +1429,7 @@ const inlinedName = getInlineTableFieldName(query.key);

if (isForeign) {
const { queryKey, foreignTables } = resolveTableToQuery(table, tables, join, [...path, ...query.key], tableAlias, false);
const tablePath = [...path];
for (let i = skipKeys; i < query.key.length; i++) {
tablePath.push(query.key[i]);
}
const { queryKey, foreignTables } = resolveTableToQuery(table, tables, join, tablePath, tableAlias, false);
query = cloneQuery(query);

@@ -1211,3 +1443,3 @@ query.key = [queryKey];

}
const { where, bindable } = convertQueryToSQLQuery(query, tables, ftable.table, join, path, ftable.as);
const { where, bindable } = convertQueryToSQLQuery(query, tables, ftable.table, join, path, ftable.as, skipKeys);
whereBuilder.push(where);

@@ -1221,2 +1453,7 @@ bindableBuilder.push(bindable);

}
const columnAggregator = join.get(createQueryTableReferenceName(table));
if (!columnAggregator) {
throw new Error("Unexpected");
}
columnAggregator.columns.push(inlinedName);
let bindable = [];

@@ -1228,7 +1465,7 @@ const keyWithTable = (tableAlias || table.name) + "." + escapeColumnName(inlinedName);

if (query.method === types.StringMatchMethod.contains) {
statement = `${keyWithTable} LIKE ?`;
statement = `${keyWithTable} LIKE ? `;
bindable.push(`%${query.value}%`);
}
else if (query.method === types.StringMatchMethod.prefix) {
statement = `${keyWithTable} LIKE ?`;
statement = `${keyWithTable} LIKE ? `;
bindable.push(`${query.value}%`);

@@ -1254,3 +1491,3 @@ }

// TODO perf
where = `hex(${keyWithTable}) LIKE ?`;
where = `hex(${keyWithTable}) LIKE ? `;
bindable.push(`%${toHexString(new Uint8Array([Number(query.value.value)]))}%`);

@@ -1263,19 +1500,19 @@ }

else if (query.compare === types.Compare.Greater) {
where = `${keyWithTable} > ?`;
where = `${keyWithTable} > ? `;
}
else if (query.compare === types.Compare.Less) {
where = `${keyWithTable} < ?`;
where = `${keyWithTable} <?`;
}
else if (query.compare === types.Compare.GreaterOrEqual) {
where = `${keyWithTable} >= ?`;
where = `${keyWithTable} >= ? `;
}
else if (query.compare === types.Compare.LessOrEqual) {
where = `${keyWithTable} <= ?`;
where = `${keyWithTable} <= ? `;
}
else {
throw new Error(`Unsupported compare type: ${query.compare}`);
throw new Error(`Unsupported compare type: ${query.compare} `);
}
if (unwrapNestedType(tableField.from.type) === "u64") {
// shift left because that is how we insert the value
bindable.push(query.value.value);
bindable.push(u64ToI64(query.value.value));
}

@@ -1299,2 +1536,12 @@ else {

};
const removeDuplicatesOrdered = (arr) => {
let seen = new Set();
return arr.filter((item) => {
if (seen.has(item)) {
return false;
}
seen.add(item);
return true;
});
};
//# sourceMappingURL=schema.js.map

5

dist/src/sqlite3-messages.worker.d.ts

@@ -19,2 +19,5 @@ interface Message {

}
interface Drop extends Message {
type: "drop";
}
interface Open extends Message {

@@ -80,3 +83,3 @@ type: "open";

}
export type DatabaseMessages = CreateDatabase | Exec | Prepare | Close | Open | Run | Status;
export type DatabaseMessages = CreateDatabase | Exec | Prepare | Close | Drop | Open | Run | Status;
export type StatementMessages = Bind | Step | Get | Reset | All | Finalize | RunStatement;

@@ -83,0 +86,0 @@ export type ResponseMessages = ErrorResponse | Response;

7

dist/src/sqlite3.browser.js

@@ -131,2 +131,9 @@ import pDefer from "p-defer";

}
async drop() {
return this.send({
type: "drop",
id: uuid(),
databaseId: this.databaseId,
});
}
async status() {

@@ -133,0 +140,0 @@ return this.send({

38

dist/src/sqlite3.js

@@ -6,2 +6,3 @@ import DB from "better-sqlite3";

let statements = new Map();
let dbFileName;
let close = () => {

@@ -17,21 +18,29 @@ for (const stmt of statements.values()) {

};
let drop = () => {
if (db && !db?.memory) {
fs.rmSync(dbFileName);
db = undefined;
}
return close();
};
let open = () => {
if (db) {
if (db?.open) {
return db;
}
let dbFileName;
if (directory) {
// if directory is provided, check if directory exist, if not create it
if (!fs.existsSync(directory)) {
fs.mkdirSync(directory, { recursive: true });
if (!db) {
if (directory) {
// if directory is provided, check if directory exist, if not create it
if (!fs.existsSync(directory)) {
fs.mkdirSync(directory, { recursive: true });
}
dbFileName = `${directory}/db.sqlite`;
}
dbFileName = `${directory}/db.sqlite`;
else {
dbFileName = ":memory:";
}
db = new DB(dbFileName, {
fileMustExist: false,
readonly: false /* , verbose: (message) => console.log(message) */,
});
}
else {
dbFileName = ":memory:";
}
db = new DB(dbFileName, {
fileMustExist: false,
readonly: false /* , verbose: (message) => console.log(message) */,
});
// TODO this test makes things faster, but for benchmarking it might yield wierd results where some runs are faster than others

@@ -66,2 +75,3 @@ db.pragma("journal_mode = WAL");

close,
drop,
open,

@@ -68,0 +78,0 @@ status: () => (db ? "open" : "closed"),

1

dist/src/sqlite3.wasm.d.ts

@@ -24,2 +24,3 @@ import { type Database as SQLDatabase, type PreparedStatement as SQLStatement } from "@sqlite.org/sqlite-wasm";

open: () => Promise<SQLDatabase | undefined>;
drop: () => Promise<any>;
prepare: (sql: string, id?: string) => Promise<Statement>;

@@ -26,0 +27,0 @@ get(sql: string): SQLDatabase;

10

dist/src/sqlite3.wasm.js

@@ -88,2 +88,9 @@ import { BinaryReader, BinaryWriter } from "@dao-xyz/borsh";

};
let dbFileName;
let drop = async () => {
if (poolUtil && dbFileName != null) {
poolUtil.unlink(dbFileName);
}
return close();
};
let open = async () => {

@@ -97,3 +104,3 @@ if (sqliteDb) {

directory = directory.replace(/^\./, "");
let dbFileName = `${directory}/db.sqlite`;
dbFileName = `${directory}/db.sqlite`;
poolUtil =

@@ -119,2 +126,3 @@ poolUtil ||

open,
drop,
prepare: async (sql, id) => {

@@ -121,0 +129,0 @@ if (id == null) {

7

dist/src/sqlite3.worker.js

@@ -20,2 +20,5 @@ import * as messages from "./sqlite3-messages.worker.js";

}
if (message.type === "drop") {
return; // ignore close message if database is not found
}
if (message.type === "status") {

@@ -45,2 +48,6 @@ return "closed";

}
else if (message.type === "drop") {
await db.drop();
this.databases.delete(message.databaseId);
}
else if (message.type === "open") {

@@ -47,0 +54,0 @@ await db.open();

1

dist/src/types.d.ts

@@ -9,2 +9,3 @@ import type { BindableValue, SQLLiteValue } from "./schema.js";

close: (err?: (err: any) => any) => Promise<any> | any;
drop: () => Promise<any> | any;
open(): Promise<any> | any;

@@ -11,0 +12,0 @@ statements: {

8

package.json
{
"name": "@peerbit/indexer-sqlite3",
"version": "1.1.4",
"version": "1.2.0",
"description": "SQLite index for document store",

@@ -72,10 +72,10 @@ "type": "module",

"dependencies": {
"better-sqlite3": "^11.5.0",
"better-sqlite3": "^11.6.0",
"@peerbit/indexer-interface": "^2.0.1",
"@sqlite.org/sqlite-wasm": "^3.47.0-build1"
"@sqlite.org/sqlite-wasm": "^3.47.2-build1"
},
"devDependencies": {
"@types/better-sqlite3": "^7.6.11",
"@types/better-sqlite3": "^7.6.12",
"@peerbit/indexer-tests": "^1.1.3"
}
}

211

src/engine.ts

@@ -10,2 +10,3 @@ import { type AbstractType, type Constructor, getSchema } from "@dao-xyz/borsh";

import { v4 as uuid } from "uuid";
import { PlannableQuery, QueryPlanner } from "./query-planner.js";
import {

@@ -18,2 +19,5 @@ MissingFieldError,

convertFromSQLType,
/* convertFromSQLType, */
/* convertFromSQLType, */
convertSearchRequestToQuery,

@@ -50,2 +54,3 @@ /* getTableName, */

primaryKeyString!: string;
planner: QueryPlanner;
private scopeString?: string;

@@ -59,5 +64,6 @@ private _rootTables!: Table[];

/* countStatement: Statement; */
timeout: ReturnType<typeof setTimeout>;
expire: number;
}
>; // TODO choose limit better
private cursorPruner: ReturnType<typeof setInterval> | undefined;

@@ -85,2 +91,5 @@ iteratorTimeout: number;

this.iteratorTimeout = options?.iteratorTimeout || 60e3;
this.planner = new QueryPlanner({
exec: this.properties.db.exec.bind(this.properties.db),
});
}

@@ -90,3 +99,3 @@

if (this.closed) {
throw new Error("Not started");
throw new types.NotStartedError();
}

@@ -98,3 +107,3 @@ return this._tables;

if (this.closed) {
throw new Error("Not started");
throw new types.NotStartedError();
}

@@ -106,3 +115,3 @@ return this._rootTables;

if (this.closed) {
throw new Error("Not started");
throw new types.NotStartedError();
}

@@ -180,7 +189,37 @@ return this._cursor;

const sqlCreateTable = `create table if not exists ${table.name} (${[...table.fields, ...table.constraints].map((s) => s.definition).join(", ")}) strict`;
const sqlCreateIndex = `create index if not exists ${table.name}_index on ${table.name} (${table.fields.map((field) => escapeColumnName(field.name)).join(", ")})`;
this.properties.db.exec(sqlCreateTable);
this.properties.db.exec(sqlCreateIndex);
/* const fieldsToIndex = table.fields.filter(
(field) =>
field.key !== ARRAY_INDEX_COLUMN && field.key !== table.primary,
);
if (fieldsToIndex.length > 0) {
let arr = fieldsToIndex.map((field) => escapeColumnName(field.name));
const createIndex = async (columns: string[]) => {
const key = createIndexKey(table.name, columns)
const command = `create index if not exists ${key} on ${table.name} (${columns.map((n) => escapeColumnName(n)).join(", ")})`;
await this.properties.db.exec(command);
table.indices.add(key);
const rev = columns.reverse()
const key2 = createIndexKey(table.name, rev)
const command2 = `create index if not exists ${key2} on ${table.name} (${rev.join(", ")})`;
await this.properties.db.exec(command2);
table.indices.add(key2);
}
await createIndex(fieldsToIndex.map(x => x.name));
await createIndex([table.primary as string, ...fieldsToIndex.map(x => x.name)]);
if (arr.length > 1) {
for (const field of fieldsToIndex) {
await createIndex([field.name]);
await createIndex([table.primary as string, field.name]);
}
}
} */
// put and return the id

@@ -194,2 +233,3 @@ let sqlPut = `insert into ${table.name} (${table.fields.map((field) => escapeColumnName(field.name)).join(", ")}) VALUES (${table.fields.map((_x) => "?").join(", ")}) RETURNING ${table.primary};`;

await this.properties.db.prepare(sqlReplace, replaceStatementKey(table));
if (table.parent) {

@@ -203,2 +243,11 @@ await this.properties.db.prepare(

this.cursorPruner = setInterval(() => {
const now = Date.now();
for (const [k, v] of this._cursor) {
if (v.expire < now) {
this.clearupIterator(k);
}
}
}, this.iteratorTimeout);
this.closed = false;

@@ -219,2 +268,3 @@ }

this.closed = true;
clearInterval(this.cursorPruner!);

@@ -228,6 +278,13 @@ await this.clearStatements();

}
await this.planner.stop();
}
async drop(): Promise<void> {
if (this.closed) {
throw new Error(`Already closed index ${this.id}, can not drop`);
}
this.closed = true;
clearInterval(this.cursorPruner!);

@@ -238,2 +295,3 @@ await this.clearStatements();

// drop table faster by dropping constraints first
for (const table of this._rootTables) {

@@ -248,2 +306,3 @@ await this.properties.db.exec(`drop table if exists ${table.name}`);

}
await this.planner.stop();
}

@@ -271,23 +330,32 @@

);
const sql = `${generateSelectQuery(table, selects)} ${buildJoin(joinMap, true)} where ${this.primaryKeyString} = ? limit 1`;
const stmt = await this.properties.db.prepare(sql, sql);
const rows = await stmt.get([
table.primaryField?.from?.type
? convertToSQLType(id.key, table.primaryField.from.type)
: id.key,
]);
if (!rows) {
continue;
const sql = `${generateSelectQuery(table, selects)} ${buildJoin(joinMap).join} where ${this.primaryKeyString} = ? limit 1`;
try {
const stmt = await this.properties.db.prepare(sql, sql);
const rows = await stmt.get([
table.primaryField?.from?.type
? convertToSQLType(id.key, table.primaryField.from.type)
: id.key,
]);
if (
rows?.[getTablePrefixedField(table, table.primary as string)] == null
) {
continue;
}
return {
value: (await resolveInstanceFromValue(
rows,
this.tables,
table,
this.resolveDependencies.bind(this),
true,
options?.shape,
)) as unknown as T,
id,
};
} catch (error) {
if (this.closed) {
throw new types.NotStartedError();
}
throw error;
}
return {
value: (await resolveInstanceFromValue(
rows,
this.tables,
table,
this.resolveDependencies.bind(this),
true,
options?.shape,
)) as unknown as T,
id,
};
}

@@ -301,3 +369,3 @@ return undefined;

async (values, table) => {
const preId = values[table.primaryIndex];
let preId = values[table.primaryIndex];

@@ -308,5 +376,3 @@ if (preId != null) {

)!;
await statement.run(
values.map((x) => (typeof x === "boolean" ? (x ? 1 : 0) : x)),
);
await statement.run(values);
await statement.reset?.();

@@ -318,5 +384,3 @@ return preId;

)!;
const out = await statement.get(
values.map((x) => (typeof x === "boolean" ? (x ? 1 : 0) : x)),
);
const out = await statement.get(values);
await statement.reset?.();

@@ -367,2 +431,8 @@

const normalizedQuery = new PlannableQuery({
query: types.toQuery(request?.query),
sort: request?.sort,
});
let planningScope: ReturnType<QueryPlanner["scope"]>;
/* let totalCount: undefined | number = undefined; */

@@ -372,24 +442,24 @@ const fetch = async (amount: number | "all") => {

if (!once) {
planningScope = this.planner.scope(normalizedQuery);
let { sql, bindable: toBind } = convertSearchRequestToQuery(
request,
normalizedQuery,
this.tables,
this._rootTables,
{
planner: planningScope,
shape: options?.shape,
stable: typeof amount === "number", // if we are to fetch all, we dont need stable sorting
fetchAll: amount === "all", // if we are to fetch all, we dont need stable sorting
},
);
sqlFetch = sql;
bindable = toBind;
await planningScope.beforePrepare();
stmt = await this.properties.db.prepare(sqlFetch, sqlFetch);
// stmt.reset?.(); // TODO dont invoke reset if not needed
/* countStmt.reset?.(); */
// Bump timeout timer
clearTimeout(iterator.timeout);
iterator.timeout = setTimeout(
() => this.clearupIterator(requestId),
this.iteratorTimeout,
);
iterator.expire = Date.now() + this.iteratorTimeout;
}

@@ -399,8 +469,18 @@

const allResults: Record<string, any>[] = await stmt.all([
/* console.log("----------------------")
console.log(sqlFetch); */
const allResults = await planningScope.perform(async () => {
const allResults: Record<string, any>[] = await stmt.all([
...bindable,
...(amount !== "all" ? [amount, offset] : []),
]);
return allResults;
});
/* const allResults: Record<string, any>[] = await stmt.all([
...bindable,
amount === "all" ? Number.MAX_SAFE_INTEGER : amount,
offset,
...(amount !== "all" ? [amount,
offset] : [])
]);
*/
let results: IndexedResult<types.ReturnTypeFromShape<T, S>>[] =

@@ -410,3 +490,3 @@ await Promise.all(

let selectedTable = this._rootTables.find(
(table /* row["table_name"] === table.name, */) =>
(table) =>
row[getTablePrefixedField(table, this.primaryKeyString)] !=

@@ -441,11 +521,5 @@ null,

/* if (results.length > 0) {
totalCount =
totalCount ??
(await this.count(
request,
));
iterator.kept = totalCount - results.length - offsetStart;
} else {
iterator.kept = 0;
/* const uniqueIds = new Set(results.map((x) => x.id.primitive));
if (uniqueIds.size !== results.length) {
throw new Error("Duplicate ids in result set");
} */

@@ -456,3 +530,2 @@

await this.clearupIterator(requestId);
clearTimeout(iterator.timeout);
}

@@ -465,6 +538,3 @@ return results;

/* countStatement: countStmt, */
timeout: setTimeout(
() => this.clearupIterator(requestId),
this.iteratorTimeout,
),
expire: Date.now() + this.iteratorTimeout,
};

@@ -479,5 +549,5 @@

while (true) {
const res = await fetch(100);
const res = await fetch("all");
results.push(...res);
if (res.length === 0) {
if (hasMore === false) {
break;

@@ -516,3 +586,2 @@ }

}
clearTimeout(cache.timeout);
/* cache.countStatement.finalize?.(); */

@@ -748,8 +817,14 @@ // await cache.fetchStatement.finalize?.();

for (const index of this.indices) {
await index.index.drop();
if (!this.properties.parent) {
for (const index of this.indices) {
await index.index.stop();
}
await this.properties.db.drop();
} else {
for (const index of this.indices) {
await index.index.drop();
}
}
this.scopes.clear();
}
}

683

src/schema.ts

@@ -17,4 +17,5 @@ import {

} from "@dao-xyz/borsh";
import { toHexString } from "@peerbit/crypto";
import { fromHexString, toHexString } from "@peerbit/crypto";
import * as types from "@peerbit/indexer-interface";
import { type PlanningSession, flattenQuery } from "./query-planner.js";

@@ -58,2 +59,11 @@ const SQLConversionMap: any = {

let JSON_GROUP_ARRAY = "json_group_array";
let JSON_OBJECT = "distinct json_object";
export const u64ToI64 = (u64: bigint | number) => {
return (typeof u64 === "number" ? BigInt(u64) : u64) - 9223372036854775808n;
};
export const i64ToU64 = (i64: number | bigint) =>
(typeof i64 === "number" ? BigInt(i64) : i64) + 9223372036854775808n;
export const convertToSQLType = (

@@ -69,2 +79,7 @@ value: boolean | bigint | string | number | Uint8Array,

}
if (type === "u64") {
// shift to fit in i64
return u64ToI64(value as number | bigint);
}
}

@@ -75,3 +90,4 @@ return value as BindableValue;

const nullAsUndefined = (value: any) => (value === null ? undefined : value);
export const escapeColumnName = (name: string) => `"${name}"`;
export const escapeColumnName = (name: string, char = '"') =>
`${char}${name}${char}`;

@@ -107,5 +123,11 @@ export class MissingFieldError extends Error {

if (type === "u64") {
return typeof value === "number" || typeof value === "string"
? BigInt(value)
: nullAsUndefined(value);
if (typeof value === "number" || typeof value === "bigint") {
return i64ToU64(value as number | bigint); // TODO is not always value type bigint?
}
if (value == null) {
return nullAsUndefined(value);
}
throw new Error(
`Unexpected value type for value ${value} expected number or bigint for u64 field`,
);
}

@@ -143,2 +165,3 @@ return nullAsUndefined(value);

from: Field | undefined;
unwrappedType: FieldType | undefined;
path: string[];

@@ -164,2 +187,3 @@ describesExistenceOfAnother?: string;

isSimpleValue: boolean;
indices: Set<string>;
}

@@ -213,2 +237,3 @@

inline,
indices: new Set<string>(),
};

@@ -253,2 +278,10 @@ ret.push(table);

) => {
let pathKey = path.length > 0 ? path.join("__") + "__" : "";
if (typeof clazz !== "string") {
const tableName = (clazz as any)["__table_" + pathKey];
if (tableName) {
return tableName;
}
}
let name: string = typeof clazz === "string" ? clazz : getNameOfClass(clazz);

@@ -261,5 +294,7 @@

const ret =
(path.length > 0 ? path.join("__") + "__" : "") +
name.replace(/[^a-zA-Z0-9_]/g, "_");
const ret = pathKey + name.replace(/[^a-zA-Z0-9_]/g, "_");
if (typeof clazz !== "string") {
(clazz as any)["__table_" + pathKey] = ret;
}
return ret;

@@ -314,9 +349,10 @@ };

// we resolve primary field here since it might be unknown until this point
const primaryField =
const parentPrimaryField =
primary != null
? sqlFields.find((field) => field.name === primary)
: undefined;
const parentPrimaryFieldName = primaryField?.key || CHILD_TABLE_ID;
const parentPrimaryFieldType = primaryField
? primaryField.type
const parentPrimaryFieldName =
parentPrimaryField?.key || CHILD_TABLE_ID;
const parentPrimaryFieldType = parentPrimaryField
? parentPrimaryField.type
: "INTEGER";

@@ -332,2 +368,3 @@

from: undefined,
unwrappedType: undefined,
path: [CHILD_TABLE_ID],

@@ -342,4 +379,5 @@ },

type: parentPrimaryFieldType,
from: parentPrimaryField?.from,
unwrappedType: parentPrimaryField?.unwrappedType,
isPrimary: false,
from: undefined,
path: [PARENT_TABLE_ID],

@@ -411,2 +449,3 @@ },

from: undefined,
unwrappedType: undefined,
path: [ARRAY_INDEX_COLUMN],

@@ -442,2 +481,3 @@ },

from: field,
unwrappedType: unwrapNestedType(field.type),
path: [...path.slice(1), key],

@@ -530,2 +570,3 @@ });

from: undefined,
unwrappedType: undefined,
path: [...path.slice(1), key],

@@ -632,3 +673,3 @@ describesExistenceOfAnother: path[path.length - 1],

): Table => {
let clazzName: string | undefined = undefined;
let clazzName: string | Constructor<any> | undefined = undefined;
if (!isNestedType(field.type)) {

@@ -652,3 +693,3 @@ clazzName = WRAPPED_SIMPLE_VALUE_VARIANT;

if (ctor) {
clazzName = getNameOfClass(ctor);
clazzName = ctor;
break;

@@ -785,3 +826,3 @@ }

}
bindableValues[bindableValues.length - 1] = false; // assign the value "false" to the exist field column
bindableValues[bindableValues.length - 1] = 0; // assign the value "false" to the exist field column
continue;

@@ -795,3 +836,3 @@ }

if (field.type instanceof OptionKind) {
bindableValues.push(true); // assign the value "true" to the exist field column
bindableValues.push(1); // assign the value "true" to the exist field column
}

@@ -912,3 +953,3 @@ return undefined;

) => {
return `SELECT ${selects.map((x) => `${x.from} as ${x.as}`).join(", ")} FROM ${table.name}`;
return `select ${selects.map((x) => `${x.from} as ${x.as}`).join(", ")} FROM ${table.name}`;
};

@@ -925,16 +966,17 @@

}[];
joins: Map<string, JoinTable>;
joins: Map<string, JoinOrRootTable>;
groupBy: string | undefined;
}[] = [];
for (const table of tables) {
const { selects, join: joinFromSelect } = selectAllFieldsFromTable(
table,
shape,
);
selectsPerTable.push({ selects, joins: joinFromSelect });
const {
selects,
join: joinFromSelect,
groupBy,
} = selectAllFieldsFromTable(table, shape);
selectsPerTable.push({ selects, joins: joinFromSelect, groupBy });
}
// pad with empty selects to make sure all selects have the same length
/* const maxSelects = Math.max(...selectsPerTable.map(x => x.selects.length)); */
let newSelects: {

@@ -944,2 +986,3 @@ from: string;

}[][] = [];
for (const [i, selects] of selectsPerTable.entries()) {

@@ -957,7 +1000,2 @@ const newSelect = [];

newSelects.push(newSelect);
/* let pad = 0;
while (select.selects.length < maxSelects) {
select.selects.push({ from: "NULL", as: `'pad#${++pad}'` });
} */
}

@@ -979,6 +1017,65 @@ // also return table name

const fieldResolvers: { from: string; as: string }[] = [];
let groupByParentId = false;
for (const tableAndShape of stack) {
if (!tableAndShape.table.inline) {
if (tableAndShape.table.referencedInArray) {
let selectBuilder = `${JSON_GROUP_ARRAY}(${JSON_OBJECT}(`;
groupByParentId = true; // we need to group by the parent id as else we will not be returned with more than 1 result
let first = false;
const as = createReconstructReferenceName(tableAndShape.table);
for (const field of tableAndShape.table.fields) {
if (
(field.isPrimary ||
!tableAndShape.shape ||
matchFieldInShape(tableAndShape.shape, [], field) ||
// also always include the index field
field.name === ARRAY_INDEX_COLUMN) &&
field.name !== PARENT_TABLE_ID
) {
let resolveField = `${as}.${escapeColumnName(field.name)}`;
// if field is bigint we need to convert it to string, so that later in a JSON.parse scenario it is not converted to a number, but remains a string until we can convert it back to a bigint manually
if (field.unwrappedType === "u64") {
resolveField = `CAST(${resolveField} AS TEXT)`;
}
// if field is blob we need to convert it to hex string
if (field.type === "BLOB") {
resolveField = `HEX(${resolveField})`;
}
if (first) {
selectBuilder += `, `;
}
first = true;
selectBuilder += `${escapeColumnName(field.name, "'")}, ${resolveField}`;
}
}
selectBuilder += `)) `; // FILTER (WHERE ${tableAndShape.table.name}.${tableAndShape.table.primary} IS NOT NULL)
fieldResolvers.push({
from: selectBuilder,
as,
});
join.set(createReconstructReferenceName(tableAndShape.table), {
as,
table: tableAndShape.table,
type: "left" as const,
columns: [],
});
} else if (!tableAndShape.table.inline) {
// we end up here when we have simple joins we want to make that are not arrays, and not inlined
if (tableAndShape.table.parent != null) {
join.set(createReconstructReferenceName(tableAndShape.table), {
as: tableAndShape.table.name,
table: tableAndShape.table,
type: "left" as const,
columns: [],
});
}
for (const field of tableAndShape.table.fields) {
if (
field.isPrimary ||

@@ -997,6 +1094,2 @@ !tableAndShape.shape ||

for (const child of tableAndShape.table.children) {
if (child.referencedInArray) {
continue;
}
let childShape: types.Shape | undefined = undefined;

@@ -1020,7 +1113,3 @@ if (tableAndShape.shape) {

}
stack.push({ table: child, shape: childShape });
if (!child.inline) {
join.set(child.name, { as: child.name, table: child });
}
}

@@ -1034,2 +1123,6 @@ }

return {
groupBy: groupByParentId
? `${table.name}.${escapeColumnName(table.primary as string)}` ||
undefined
: undefined,
selects: fieldResolvers, // `SELECT ${fieldResolvers.join(", ")} FROM ${table.name}`,

@@ -1093,20 +1186,51 @@ join,

if (isArray) {
let once = false;
/* let once = false; */
let resolvedArr = [];
for (const subtable of subTables) {
// TODO types
let rootTable = getNonInlinedTable(table);
const arr = await resolveChildren(
fromTablePrefixedValues[
getTablePrefixedField(
rootTable,
rootTable.primary as string,
!tablePrefixed,
)
],
subtable,
);
if (arr) {
once = true;
// check if the array already in the provided row
let arr: any[] | undefined = undefined;
const tableName = createReconstructReferenceName(subtable);
if (fromTablePrefixedValues[tableName]) {
arr = JSON.parse(fromTablePrefixedValues[tableName]) as Array<any>;
arr = arr.filter((x) => x[subtable.primary as string] != null);
// we need to go over all fields that are to be bigints and convert
// them back to bigints
// for blob fields we need to convert them back to Uint8Array
for (const field of subtable.fields) {
if (field.name === PARENT_TABLE_ID) {
continue;
}
if (field.unwrappedType === "u64") {
for (const item of arr!) {
item[field.name] = BigInt(item[field.name]);
}
} else if (field.type === "BLOB") {
for (const item of arr!) {
item[field.name] = fromHexString(item[field.name]);
}
}
}
} else {
if (subtable.children) {
// TODO we only end up where when we resolve nested arrays,
// which shoulld instead be resolved in a nested select (with json_group_array and json_object)
let rootTable = getNonInlinedTable(table);
const parentId =
fromTablePrefixedValues[
getTablePrefixedField(
rootTable,
rootTable.primary as string,
!tablePrefixed,
)
];
arr = await resolveChildren(parentId, subtable);
} else {
arr = [];
}
}
if (arr && arr.length > 0) {
/* once = true; */
for (const element of arr) {

@@ -1129,7 +1253,3 @@ const resolved: SimpleNested | any = await resolveInstanceFromValue(

if (!once) {
obj[field.key] = undefined;
} else {
obj[field.key] = resolvedArr;
}
obj[field.key] = resolvedArr; // we can not do option(vec('T')) since we dont store the option type for Arrays (TODO)
} else {

@@ -1267,3 +1387,3 @@ // resolve nested object from row directly

"delete",
request,
{ query: types.toQuery(request.query) },
tables,

@@ -1285,3 +1405,3 @@ table,

"sum",
request,
{ query: types.toQuery(request.query), key: request.key },
tables,

@@ -1311,3 +1431,3 @@ table,

"count",
request,
{ query: request?.query ? types.toQuery(request.query) : undefined },
tables,

@@ -1322,4 +1442,66 @@ table,

const buildOrderBy = (
sort: types.Sort[] | types.Sort | undefined,
tables: Map<string, Table>,
table: Table,
joinBuilder: Map<string, JoinOrRootTable>,
resolverBuilder: { from: string; as: string }[],
path: string[] = [],
options?: {
fetchAll?: boolean;
planner?: PlanningSession;
},
) => {
let orderByBuilder: string | undefined = undefined;
if (
(!sort || (Array.isArray(sort) && sort.length === 0)) &&
!options?.fetchAll
) {
sort =
table.primary && path.length === 0
? [{ key: [table.primary], direction: types.SortDirection.ASC }]
: undefined;
}
if (sort) {
let sortArr = Array.isArray(sort) ? sort : [sort];
if (sortArr.length > 0) {
orderByBuilder = "";
let once = false;
for (const sort of sortArr) {
const { foreignTables, queryKey } = resolveTableToQuery(
table,
tables,
joinBuilder,
[...path, ...sort.key],
undefined,
true,
);
for (const foreignTable of foreignTables) {
if (once) {
orderByBuilder += ", ";
}
once = true;
foreignTable.columns.push(queryKey); // add the sort key to the list of columns that will be used for this query
orderByBuilder += `"${foreignTable.as}#${queryKey}" ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`;
resolverBuilder.push({
from: `${table.name}.${escapeColumnName(queryKey)}`,
as: `'${foreignTable.as}#${queryKey}'`,
});
}
}
}
}
return { orderByBuilder };
};
export const convertSearchRequestToQuery = (
request: types.IterateOptions | undefined,
request:
| { query: types.Query[]; sort?: types.Sort[] | types.Sort }
| undefined,
tables: Map<string, Table>,

@@ -1329,3 +1511,4 @@ rootTables: Table[],

shape?: types.Shape | undefined;
stable?: boolean;
fetchAll?: boolean;
planner?: PlanningSession;
},

@@ -1341,26 +1524,28 @@ ): { sql: string; bindable: any[] } => {

let bindableBuilder: any[] = [];
for (const [i, table] of rootTables.entries()) {
const { selects, joins: joinFromSelect } = selectsPerTable[i];
const selectQuery = generateSelectQuery(table, selects);
const { selects, joins, groupBy } = selectsPerTable[i];
try {
const { orderBy, query, bindable } = convertRequestToQuery(
"iterate",
request,
const { orderByBuilder } = buildOrderBy(
request?.sort,
tables,
table,
joinFromSelect,
joins,
selects,
[],
{
stable: options?.stable,
},
options,
);
unionBuilder += `${unionBuilder.length > 0 ? " UNION ALL " : ""} ${selectQuery} ${query}`;
orderByClause =
orderBy?.length > 0
? orderByClause.length > 0
? orderByClause + ", " + orderBy
: orderBy
: orderByClause;
matchedOnce = true;
bindableBuilder.push(...bindable);
if (!orderByClause && orderByBuilder) {
// assume all order by clauses will be the same
orderByClause =
orderByBuilder.length > 0
? orderByClause.length > 0
? orderByClause + ", " + orderByBuilder
: orderByBuilder
: orderByClause;
}
//orderByAddedOnce = true;
} catch (error) {

@@ -1373,2 +1558,29 @@ if (error instanceof MissingFieldError) {

}
const selectQuery = generateSelectQuery(table, selects);
for (const flattenRequest of flattenQuery(request)) {
try {
const { query, bindable } = convertRequestToQuery(
"iterate",
flattenRequest,
tables,
table,
new Map(joins), // copy the map, else we might might do unececessary joins
[],
options,
);
unionBuilder += `${unionBuilder.length > 0 ? " UNION " : ""} ${selectQuery} ${query} ${groupBy ? "GROUP BY " + groupBy : ""}`;
matchedOnce = true;
bindableBuilder.push(...bindable);
} catch (error) {
if (error instanceof MissingFieldError) {
lastError = error;
orderByClause = "";
continue;
}
throw error;
}
}
}

@@ -1381,3 +1593,3 @@

return {
sql: `${unionBuilder} ${orderByClause ? "ORDER BY " + orderByClause : ""} limit ? offset ?`,
sql: `${unionBuilder} ${orderByClause ? "ORDER BY " + orderByClause : ""} ${options?.fetchAll ? "" : "limit ? offset ?"}`,
bindable: bindableBuilder,

@@ -1387,11 +1599,34 @@ };

type SearchQueryParts = { query: string; orderBy: string; bindable: any[] };
type CountQueryParts = { query: string; join: string; bindable: any[] };
type SearchQueryParts = {
query: string;
/* orderBy: string; */
bindable: any[];
selects: string[];
};
type CountQueryParts = {
query: string;
join: string;
bindable: any[];
selects: string[];
};
function isIterateRequest(
request: any,
type: string,
): request is types.IterateOptions | undefined {
return type === "iterate";
}
const getOrSetRootTable = (
joinBuilder: Map<string, JoinOrRootTable>,
table: Table,
) => {
const refName = createQueryTableReferenceName(table);
let ref = joinBuilder.get(refName);
if (ref) {
return ref;
}
const join = {
// add the root as a join even though it is not, just so we can collect the columns it will be queried
table: table,
type: "root" as const,
as: table.name,
columns: [],
};
joinBuilder.set(refName, join);
return join;
};

@@ -1405,15 +1640,26 @@ const convertRequestToQuery = <

| (T extends "iterate"
? types.IterateOptions
? {
query?: types.Query[];
sort?: types.Sort[] | types.Sort;
}
: T extends "count"
? types.CountOptions
? {
query?: types.Query[];
}
: T extends "delete"
? types.DeleteOptions
: types.SumOptions)
? {
query?: types.Query[];
}
: {
query?: types.Query[];
key: string | string[];
})
| undefined,
tables: Map<string, Table>,
table: Table,
extraJoin?: Map<string, JoinTable>,
extraJoin?: Map<string, JoinOrRootTable>,
path: string[] = [],
options?: {
stable?: boolean;
fetchAll?: boolean;
planner?: PlanningSession;
},

@@ -1423,5 +1669,8 @@ ): R => {

let bindableBuilder: any[] = [];
let orderByBuilder: string | undefined = undefined;
/* let orderByBuilder: string | undefined = undefined; */
/* let tablesToSelect: string[] = [table.name]; */
let joinBuilder: Map<string, JoinTable> = extraJoin || new Map();
let joinBuilder: Map<string, JoinOrRootTable> = extraJoin || new Map();
getOrSetRootTable(joinBuilder, table);
const coercedQuery = types.toQuery(request?.query);

@@ -1435,2 +1684,4 @@ if (coercedQuery.length === 1) {

path,
undefined,
0,
);

@@ -1446,2 +1697,4 @@ whereBuilder += where;

path,
undefined,
0,
);

@@ -1452,5 +1705,8 @@ whereBuilder += where;

if (isIterateRequest(request, type)) {
/* if (isIterateRequest(request, type)) {
let sort = request?.sort;
if (!sort && options?.stable) {
if (
(!sort || (Array.isArray(sort) && sort.length === 0)) &&
!options?.fetchAll
) {
sort =

@@ -1476,3 +1732,4 @@ table.primary && path.length === 0

);
for (const table of foreignTables) {
for (const foreignTable of foreignTables) {
if (once) {

@@ -1482,15 +1739,11 @@ orderByBuilder += ", ";

once = true;
orderByBuilder += `${table.as}.${queryKey} ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`;
foreignTable.columns.push(queryKey); // add the sort key to the list of columns that will be used for this query
orderByBuilder += `${foreignTable.as}.${queryKey} ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`;
}
}
/* orderByBuilder += request.sort
.map(
(sort) =>
`${table.name}.${sort.key} ${sort.direction === types.SortDirection.ASC ? "ASC" : "DESC"}`
)
.join(", "); */
}
}
}
} */
const where = whereBuilder.length > 0 ? "where " + whereBuilder : undefined;

@@ -1501,3 +1754,3 @@

}
let join = buildJoin(joinBuilder, type === "iterate" ? true : false);
let { join } = buildJoin(joinBuilder, options);

@@ -1508,3 +1761,3 @@ const query = `${join ? join : ""} ${where ? where : ""}`;

query,
orderBy: orderByBuilder,
/* orderBy: orderByBuilder, */
bindable: bindableBuilder,

@@ -1515,10 +1768,49 @@ } as R;

export const buildJoin = (
joinBuilder: Map<string, JoinTable>,
resolveAllColumns: boolean,
joinBuilder: Map<string, JoinOrRootTable>,
options?: {
planner?: PlanningSession;
},
): { join: string } => {
/* let joinTypeDefault = resolveAllColumns
? "CROSS JOIN"
: "JOIN"; */
let join = "";
for (const [_key, table] of joinBuilder) {
if (table.type !== "root") {
continue;
}
const out = _buildJoin(table, options);
join += out.join;
}
for (const [_key, table] of joinBuilder) {
if (table.type === "root") {
continue;
}
const out = _buildJoin(table, options);
join += out.join;
}
return { join };
};
const _buildJoin = (
table: JoinOrRootTable,
options?: {
planner?: PlanningSession;
},
) => {
let joinTypeDefault = resolveAllColumns
? /* "FULL OUTER JOIN" */ "LEFT OUTER JOIN"
: "JOIN";
let join = "";
for (const [_key, table] of joinBuilder) {
let indexedBy: string | undefined = undefined;
if (table.type !== "root") {
table!.columns.push(PARENT_TABLE_ID); // we unshift because we join on the parent id before where clause
}
if (table!.columns.length > 0) {
const usedColumns = removeDuplicatesOrdered(table!.columns);
indexedBy = options?.planner
? ` INDEXED BY ${options.planner.resolveIndex(table.table.name, usedColumns)} `
: "";
}
if (table.type !== "root") {
let nonInlinedParent =

@@ -1529,9 +1821,9 @@ table.table.parent && getNonInlinedTable(table.table.parent);

}
let joinType = table.type === "cross" ? "LEFT JOIN" : "LEFT JOIN";
join += ` ${joinType} ${table.table.name} AS ${table.as} ${indexedBy} ON ${nonInlinedParent.name}.${nonInlinedParent.primary} = ${table.as}.${PARENT_TABLE_ID} `;
} else if (indexedBy) {
join += indexedBy;
}
let joinType = table.table.referencedInArray
? /* "FULL OUTER JOIN" */ "LEFT OUTER JOIN"
: joinTypeDefault;
join += `${joinType} ${table.table.name} AS ${table.as} ON ${nonInlinedParent.name}.${nonInlinedParent.primary} = ${table.as}.${PARENT_TABLE_ID} `;
}
return join;
return { join };
};

@@ -1549,5 +1841,6 @@

table: Table,
joinBuilder: Map<string, JoinTable>,
path: string[] = [],
tableAlias: string | undefined = undefined,
joinBuilder: Map<string, JoinOrRootTable>,
path: string[],
tableAlias: string | undefined,
skipKeys: number,
): { where: string; bindable: any[] } => {

@@ -1561,3 +1854,4 @@ let whereBuilder = "";

path: string[],
tableAlias?: string,
tableAlias: string | undefined,
keysOffset: number,
) => {

@@ -1572,2 +1866,3 @@ for (const query of queries) {

tableAlias,
keysOffset,
);

@@ -1588,2 +1883,3 @@ whereBuilder =

tableAlias,
skipKeys,
);

@@ -1594,7 +1890,8 @@ whereBuilder += where;

let joinPrefix = "__" + String(tables.size);
path = [...path, query.path];
handleAnd(query.query, path, joinPrefix);
path = [...path, ...query.path];
let newSkipKeys = skipKeys + query.path.length;
handleAnd(query.query, path, joinPrefix, newSkipKeys);
} else if (query instanceof types.LogicalQuery) {
if (query instanceof types.And) {
handleAnd(query.and, path, tableAlias);
handleAnd(query.and, path, tableAlias, skipKeys);
} else if (query instanceof types.Or) {

@@ -1609,5 +1906,6 @@ for (const subquery of query.or) {

tableAlias,
skipKeys,
);
whereBuilder =
whereBuilder.length > 0 ? `(${whereBuilder}) OR (${where})` : where;
whereBuilder.length > 0 ? `(${whereBuilder}) OR(${where})` : where;
bindableBuilder.push(...bindable);

@@ -1623,4 +1921,5 @@ }

tableAlias,
skipKeys,
);
whereBuilder = `NOT (${where})`;
whereBuilder = `NOT(${where})`;
bindableBuilder.push(...bindable);

@@ -1644,20 +1943,28 @@ } else {

type JoinOrRootTable = JoinTable | RootTable;
type JoinTable = {
table: Table;
as: string;
type: "left" | "cross";
columns: string[];
};
const createTableReferenceName = (
type RootTable = {
type: "root";
table: Table;
as: string;
columns: string[];
};
/* const createQueryTableReferenceName = (
table: Table,
alias: string | undefined,
fieldType: FieldType,
joinSize: number,
) => {
if (
!alias &&
(fieldType instanceof VecKind ||
(fieldType instanceof OptionKind &&
fieldType.elementType instanceof VecKind))
!alias
) {
let aliasSuffix = "_" + String(joinSize);
let aliasSuffix =
"_query"; // "_" + String(joinSize); TODO this property will make every join unique, which is not wanted unless (ever?) since we can do OR in SQL which means we can do one join and perform AND/OR logic without joining multiple times to apply multiple conditions
alias = aliasSuffix;

@@ -1667,12 +1974,20 @@ }

return tableNameAs;
}; */
const createQueryTableReferenceName = (table: Table) => {
return table.parent == null ? table.name : "_query_" + table.name;
};
const createReconstructReferenceName = (table: Table) => {
return table.name; /* table.parent == null ? table.name : "_rec_" + table.name; */
};
const resolveTableToQuery = (
table: Table,
tables: Map<string, Table>,
join: Map<string, JoinTable>,
join: Map<string, JoinOrRootTable>,
path: string[],
alias: string | undefined,
searchSelf: boolean,
) => {
): { queryKey: string; foreignTables: JoinOrRootTable[] } => {
// we are matching in two ways.

@@ -1694,3 +2009,3 @@

queryKey: field.name,
foreignTables: [{ table, as: table.name }],
foreignTables: [getOrSetRootTable(join, table)],
};

@@ -1700,3 +2015,10 @@ }

let currentTables: JoinTable[] = [{ table, as: alias || table.name }];
let currentTables: JoinTable[] = [
{
table,
as: alias || table.name,
type: "cross" as const,
columns: [],
},
];
let prevTables: JoinTable[] | undefined = undefined;

@@ -1714,16 +2036,25 @@

throw new MissingFieldError(
`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))}`,
`Property with key "${key}" is not found in the schema ${JSON.stringify(schema.fields.map((x) => x.key))} `,
);
}
for (const child of currentTable.children) {
const tableNameAs = createTableReferenceName(
const tableNameAs = createQueryTableReferenceName(
child,
alias,
/* alias */ /* ,
field.type,
join.size,
join.size, */
);
let isMatching =
child.parentPath![child.parentPath!.length - 1] === key;
if (isMatching) {
const tableWithAlias = { table: child, as: tableNameAs };
const tableWithAlias = {
columns: [],
table: child,
as: tableNameAs,
type:
currentTable.children.length > 1
? ("left" as const)
: ("cross" as const),
};
if (child.isSimpleValue) {

@@ -1792,5 +2123,6 @@ if (!child.inline) {

table: Table,
join: Map<string, JoinTable>,
join: Map<string, JoinOrRootTable>,
path: string[],
tableAlias: string | undefined = undefined,
tableAlias: string | undefined,
skipKeys: number,
): { where: string; bindable: any[] } => {

@@ -1804,2 +2136,6 @@ // if field id represented as foreign table, do join and compare

if (isForeign) {
const tablePath: string[] = [...path];
for (let i = skipKeys; i < query.key.length; i++) {
tablePath.push(query.key[i]);
}
const { queryKey, foreignTables } = resolveTableToQuery(

@@ -1809,3 +2145,3 @@ table,

join,
[...path, ...query.key],
tablePath,
tableAlias,

@@ -1823,2 +2159,3 @@ false,

}
const { where, bindable } = convertQueryToSQLQuery(

@@ -1831,2 +2168,3 @@ query,

ftable.as,
skipKeys,
);

@@ -1842,2 +2180,8 @@ whereBuilder.push(where);

const columnAggregator = join.get(createQueryTableReferenceName(table))!;
if (!columnAggregator) {
throw new Error("Unexpected");
}
columnAggregator.columns.push(inlinedName);
let bindable: any[] = [];

@@ -1851,6 +2195,6 @@ const keyWithTable =

if (query.method === types.StringMatchMethod.contains) {
statement = `${keyWithTable} LIKE ?`;
statement = `${keyWithTable} LIKE ? `;
bindable.push(`%${query.value}%`);
} else if (query.method === types.StringMatchMethod.prefix) {
statement = `${keyWithTable} LIKE ?`;
statement = `${keyWithTable} LIKE ? `;
bindable.push(`${query.value}%`);

@@ -1874,3 +2218,3 @@ } else if (query.method === types.StringMatchMethod.exact) {

// TODO perf
where = `hex(${keyWithTable}) LIKE ?`;
where = `hex(${keyWithTable}) LIKE ? `;
bindable.push(

@@ -1883,11 +2227,11 @@ `%${toHexString(new Uint8Array([Number(query.value.value)]))}%`,

} else if (query.compare === types.Compare.Greater) {
where = `${keyWithTable} > ?`;
where = `${keyWithTable} > ? `;
} else if (query.compare === types.Compare.Less) {
where = `${keyWithTable} < ?`;
where = `${keyWithTable} <?`;
} else if (query.compare === types.Compare.GreaterOrEqual) {
where = `${keyWithTable} >= ?`;
where = `${keyWithTable} >= ? `;
} else if (query.compare === types.Compare.LessOrEqual) {
where = `${keyWithTable} <= ?`;
where = `${keyWithTable} <= ? `;
} else {
throw new Error(`Unsupported compare type: ${query.compare}`);
throw new Error(`Unsupported compare type: ${query.compare} `);
}

@@ -1897,3 +2241,3 @@

// shift left because that is how we insert the value
bindable.push(query.value.value);
bindable.push(u64ToI64(query.value.value));
} else {

@@ -1913,1 +2257,12 @@ bindable.push(query.value.value);

};
const removeDuplicatesOrdered = (arr: string[]) => {
let seen = new Set();
return arr.filter((item) => {
if (seen.has(item)) {
return false;
}
seen.add(item);
return true;
});
};

5

src/sqlite3-messages.worker.ts

@@ -27,2 +27,6 @@ import { fromBase64, toBase64 } from "@peerbit/crypto";

interface Drop extends Message {
type: "drop";
}
interface Open extends Message {

@@ -111,2 +115,3 @@ type: "open";

| Close
| Drop
| Open

@@ -113,0 +118,0 @@ | Run

8

src/sqlite3.browser.ts

@@ -169,2 +169,10 @@ import pDefer from "p-defer";

async drop() {
return this.send({
type: "drop",
id: uuid(),
databaseId: this.databaseId,
});
}
async status() {

@@ -171,0 +179,0 @@ return this.send<"open" | "closed">({

37

src/sqlite3.ts

@@ -11,2 +11,3 @@ import DB from "better-sqlite3";

let statements: Map<string, IStatement> = new Map();
let dbFileName: string;

@@ -24,22 +25,31 @@ let close = () => {

};
let drop = () => {
if (db && !db?.memory) {
fs.rmSync(dbFileName);
db = undefined;
}
return close();
};
let open = () => {
if (db) {
if (db?.open) {
return db;
}
let dbFileName: string;
if (directory) {
// if directory is provided, check if directory exist, if not create it
if (!fs.existsSync(directory)) {
fs.mkdirSync(directory, { recursive: true });
if (!db) {
if (directory) {
// if directory is provided, check if directory exist, if not create it
if (!fs.existsSync(directory)) {
fs.mkdirSync(directory, { recursive: true });
}
dbFileName = `${directory}/db.sqlite`;
} else {
dbFileName = ":memory:";
}
dbFileName = `${directory}/db.sqlite`;
} else {
dbFileName = ":memory:";
db = new DB(dbFileName, {
fileMustExist: false,
readonly: false /* , verbose: (message) => console.log(message) */,
});
}
db = new DB(dbFileName, {
fileMustExist: false,
readonly: false /* , verbose: (message) => console.log(message) */,
});
// TODO this test makes things faster, but for benchmarking it might yield wierd results where some runs are faster than others

@@ -74,2 +84,3 @@ db.pragma("journal_mode = WAL");

close,
drop,
open,

@@ -76,0 +87,0 @@ status: () => (db ? "open" : "closed"),

12

src/sqlite3.wasm.ts

@@ -113,2 +113,11 @@ import { BinaryReader, BinaryWriter } from "@dao-xyz/borsh";

};
let dbFileName: string;
let drop = async () => {
if (poolUtil && dbFileName != null) {
poolUtil.unlink(dbFileName);
}
return close();
};
let open = async () => {

@@ -123,3 +132,3 @@ if (sqliteDb) {

let dbFileName = `${directory}/db.sqlite`;
dbFileName = `${directory}/db.sqlite`;

@@ -148,2 +157,3 @@ poolUtil =

open,
drop,
prepare: async (sql: string, id?: string) => {

@@ -150,0 +160,0 @@ if (id == null) {

7

src/sqlite3.worker.ts

@@ -24,3 +24,5 @@ import * as messages from "./sqlite3-messages.worker.js";

}
if (message.type === "drop") {
return; // ignore close message if database is not found
}
if (message.type === "status") {

@@ -49,2 +51,5 @@ return "closed";

this.databases.delete(message.databaseId);
} else if (message.type === "drop") {
await db.drop();
this.databases.delete(message.databaseId);
} else if (message.type === "open") {

@@ -51,0 +56,0 @@ await db.open();

1

src/types.ts

@@ -11,2 +11,3 @@ import type { BindableValue, SQLLiteValue } from "./schema.js";

close: (err?: (err: any) => any) => Promise<any> | any;
drop: () => Promise<any> | any;
open(): Promise<any> | any;

@@ -13,0 +14,0 @@ statements: {

dist/peerbit/sqlite3-bundler-friendly.mjs

Sorry, the diff of this file is not supported yet

dist/peerbit/sqlite3-node.mjs

Sorry, the diff of this file is not supported yet

dist/peerbit/sqlite3.js

Sorry, the diff of this file is too big to display

dist/peerbit/sqlite3.min.js

Sorry, the diff of this file is too big to display

dist/peerbit/sqlite3.mjs

Sorry, the diff of this file is not supported yet

dist/peerbit/sqlite3.wasm

Sorry, the diff of this file is not supported yet

dist/peerbit/sqlite3.worker.min.js

Sorry, the diff of this file is too big to display

dist/src/engine.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/engine.js.map

Sorry, the diff of this file is not supported yet

dist/src/schema.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/schema.js.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3-messages.worker.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3-messages.worker.js.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.browser.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.browser.js.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.js.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.wasm.d.ts.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.wasm.js.map

Sorry, the diff of this file is not supported yet

dist/src/sqlite3.worker.js.map

Sorry, the diff of this file is not supported yet

dist/src/types.d.ts.map

Sorry, the diff of this file is not supported yet

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

About

Packages

npm

Go

Maven

PyPI

Rubygems

Stay in touch

Get open source security insights delivered straight into your inbox.

  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc