import { sqlite } from "https://esm.town/v/std/sqlite"; import { sql, eq, ne, gt, gte, lt, lte, like, between, and, or, inArray, isNull, isNotNull } from "npm:drizzle-orm"; import { drizzle } from "npm:drizzle-orm/libsql"; import { sqliteTable, text, blob, integer, real, index, uniqueIndex, customType } from "npm:drizzle-orm/sqlite-core"; type ColumnType = 'TEXT' | 'INTEGER' | 'REAL' | 'BLOB' | 'JSON' | 'JSONARRAY' | 'VECTOR' | 'SELF_REFERENCE' | 'BOOLEAN' | 'TIMESTAMP' | 'TIMESTAMP_MS' | 'NUMBER'; const embedding = customType<{ data: ArrayBuffer; configRequired: true; config: { size: number; }; }>({ dataType(config) { return `F32_BLOB(${config.size})`; }, fromDriver(value) { const isBuffer = Buffer.isBuffer(value); if (!isBuffer) throw new Error('Expected a Buffer'); const arrayBuffer = value.buffer as ArrayBuffer; return arrayBuffer; }, toDriver(value) { return value; }, }); interface ColumnDefinition { name: string; type: ColumnType; primaryKey?: boolean; notNull?: boolean; size?: number; autoIncrement?: boolean; } interface IndexDefinition { name: string; columns: string[]; unique?: boolean; } interface FTSIndexDefinition { name: string; columns: string[]; } export class Dobby { private db: any; private table: any; private dbName: string; private columns: ColumnDefinition[]; private indexes: IndexDefinition[]; private ftsIndexes: FTSIndexDefinition[]; constructor(dbName: string, columns: ColumnDefinition[], indexes: IndexDefinition[] = [], ftsIndexes: FTSIndexDefinition[] = []) { this.db = drizzle(sqlite as any); this.dbName = dbName; this.columns = columns; this.indexes = indexes; this.ftsIndexes = ftsIndexes; this.table = this.createTableSchema(); } private createTableSchema() { const schema: Record = {}; this.columns.forEach(col => { switch (col.type) { case 'TEXT': schema[col.name] = text(col.name); break; case 'INTEGER': case 'NUMBER': schema[col.name] = integer(col.name, { mode: 'number' }); break; case 'BOOLEAN': schema[col.name] = integer(col.name, { mode: 'boolean' }); break; case 'TIMESTAMP': schema[col.name] = integer(col.name, { mode: 'timestamp' }); break; case 'TIMESTAMP_MS': schema[col.name] = integer(col.name, { mode: 'timestamp_ms' }); break; case 'REAL': schema[col.name] = real(col.name); break; case 'JSON': schema[col.name] = text(col.name, { mode: 'json' }); break; case 'JSONARRAY': schema[col.name] = text(col.name, { mode: 'json' }).$type[]>(); break; case 'BLOB': schema[col.name] = blob(col.name); break; case 'VECTOR': if (!col.size) { throw new Error(`Size must be specified for VECTOR column ${col.name}`); } schema[col.name] = embedding('embedding', { size: col.size }); break; } if (col.primaryKey) { if ((col.type === 'INTEGER' || col.type === 'NUMBER') && col.autoIncrement) { schema[col.name] = schema[col.name].primaryKey({ autoIncrement: true }); } else { schema[col.name] = schema[col.name].primaryKey(); } } if (col.notNull) schema[col.name] = schema[col.name].notNull(); }); return sqliteTable(this.dbName, schema, (table) => { const indexObjects: Record = {}; this.indexes.forEach(idx => { const indexColumns = idx.columns.map(colName => table[colName]); if (idx.unique) { indexObjects[idx.name] = uniqueIndex(idx.name).on(...indexColumns); } else { indexObjects[idx.name] = index(idx.name).on(...indexColumns); } }); return indexObjects; }); } async createDatabase(): Promise { try { const columnDefs = this.columns.map(col => `${col.name} ${col.type}${col.primaryKey ? ' PRIMARY KEY' : ''}${col.notNull ? ' NOT NULL' : ''}` ).join(', '); // console.log('columnDefs:', columnDefs, this.dbName) await sqlite.execute(` CREATE TABLE IF NOT EXISTS ${this.dbName} ( ${columnDefs} ) `); // Create indexes for (const idx of this.indexes) { const indexColumns = idx.columns.join(', '); const uniqueStr = idx.unique ? 'UNIQUE' : ''; await sqlite.execute(` CREATE ${uniqueStr} INDEX IF NOT EXISTS ${idx.name} ON ${this.dbName} (${indexColumns}) `); } // Create FTS5 virtual tables for (const ftsIndex of this.ftsIndexes) { const ftsColumns = ftsIndex.columns.join(', '); await sqlite.execute(` CREATE VIRTUAL TABLE IF NOT EXISTS ${ftsIndex.name} USING fts5(${ftsColumns}, content='${this.dbName}', content_rowid='id') `); // Create triggers to keep FTS index updated await sqlite.execute(` CREATE TRIGGER IF NOT EXISTS ${ftsIndex.name}_ai AFTER INSERT ON ${this.dbName} BEGIN INSERT INTO ${ftsIndex.name}(rowid, ${ftsColumns}) VALUES (new.id, ${ftsIndex.columns.map(col => `new.${col}`).join(', ')}); END; `); await sqlite.execute(` CREATE TRIGGER IF NOT EXISTS ${ftsIndex.name}_ad AFTER DELETE ON ${this.dbName} BEGIN INSERT INTO ${ftsIndex.name}(${ftsIndex.name}, rowid, ${ftsColumns}) VALUES('delete', old.id, ${ftsIndex.columns.map(col => `old.${col}`).join(', ')}); END; `); await sqlite.execute(` CREATE TRIGGER IF NOT EXISTS ${ftsIndex.name}_au AFTER UPDATE ON ${this.dbName} BEGIN INSERT INTO ${ftsIndex.name}(${ftsIndex.name}, rowid, ${ftsColumns}) VALUES('delete', old.id, ${ftsIndex.columns.map(col => `old.${col}`).join(', ')}); INSERT INTO ${ftsIndex.name}(rowid, ${ftsColumns}) VALUES (new.id, ${ftsIndex.columns.map(col => `new.${col}`).join(', ')}); END; `); } } catch (error) { console.error(`Error creating database '${this.dbName}':`, error); throw error; } } async insert(data: Record): Promise { const cleanedData = Object.fromEntries(Object.entries(data).filter(([_, v]) => v !== null && v !== undefined)); await this.db.insert(this.table).values(cleanedData).run(); } async get(conditions: Record): Promise { const whereClause = sql`${sql.join( Object.entries(conditions).map( ([key, value]) => sql`${sql.identifier(key)} = ${value}` ), sql` AND ` )}`; // console.log('whereClause:', whereClause) return await this.db.select().from(this.table).where(whereClause).get(); } async update(conditions: Record, data: Record): Promise { const whereClause = sql`${sql.join( Object.entries(conditions).map( ([key, value]) => sql`${this.table[key]} = ${value}` ), sql` AND ` )}`; await this.db.update(this.table).set(data).where(whereClause).run(); } async delete(conditions: Record): Promise { const whereClause = sql`${sql.join( Object.entries(conditions).map( ([key, value]) => sql`${this.table[key]} = ${value}` ), sql` AND ` )}`; await this.db.delete(this.table).where(whereClause).run(); } async list(limit: number = 100): Promise { return await this.db.select().from(this.table).limit(limit).all(); } async deleteDatabase(): Promise { try { // Drop the main table await sqlite.execute(`DROP TABLE IF EXISTS ${this.dbName}`); // Drop FTS virtual tables for (const ftsIndex of this.ftsIndexes) { await sqlite.execute(`DROP TABLE IF EXISTS ${ftsIndex.name}`); } console.log(`Database '${this.dbName}' and its FTS indexes have been deleted.`); } catch (error) { console.error(`Error deleting database '${this.dbName}':`, error); throw error; } } async upsert(data: Record, uniqueKeys: string[] = ["id"]): Promise { const uniqueConditions = Object.fromEntries( uniqueKeys.map(key => [key, data[key]]) ); const existingRecord = await this.get(uniqueConditions); if (existingRecord) { // Update existing record await this.update(uniqueConditions, data); } else { // Insert new record await this.insert(data); } } async filter(conditions: Record, options: { limit?: number, offset?: number } = {}): Promise { const whereConditions: string[] = []; const args: InValue[] = []; for (const [key, value] of Object.entries(conditions)) { if (typeof value === 'object' && value !== null) { const [operator, operand] = Object.entries(value)[0]; switch (operator) { case 'eq': whereConditions.push(`${key} = ?`); args.push(operand); break; case 'ne': whereConditions.push(`${key} != ?`); args.push(operand); break; case 'gt': whereConditions.push(`${key} > ?`); args.push(operand); break; case 'gte': whereConditions.push(`${key} >= ?`); args.push(operand); break; case 'lt': whereConditions.push(`${key} < ?`); args.push(operand); break; case 'lte': whereConditions.push(`${key} <= ?`); args.push(operand); break; case 'like': whereConditions.push(`${key} LIKE ?`); args.push(operand); break; case 'between': whereConditions.push(`${key} BETWEEN ? AND ?`); args.push(operand[0], operand[1]); break; case 'in': whereConditions.push(`${key} IN (${operand.map(() => '?').join(',')})`); args.push(...operand); break; case 'isNull': whereConditions.push(`${key} IS NULL`); break; case 'isNotNull': whereConditions.push(`${key} IS NOT NULL`); break; } } else { whereConditions.push(`${key} = ?`); args.push(value); } } let sql = `SELECT * FROM ${this.dbName}`; if (whereConditions.length > 0) { sql += ` WHERE ${whereConditions.join(' AND ')}`; } if (options.limit) { sql += ` LIMIT ?`; args.push(options.limit); } if (options.offset) { sql += ` OFFSET ?`; args.push(options.offset); } // console.log('Generated SQL:', sql); // console.log('Arguments:', args); const result = await sqlite.execute({ sql, args }); return result.rows.map(row => { const obj: Record = {}; result.columns.forEach((col, index) => { obj[col] = row[index]; }); return obj; }); } async search(column: string, searchTerm: string, options: { limit?: number, offset?: number } = {}): Promise { if (!this.columns.some(col => col.name === column)) { throw new Error(`Column '${column}' does not exist in the table.`); } let sql = `SELECT * FROM ${this.dbName} WHERE ${column} LIKE ?`; const args: InValue[] = [`%${searchTerm}%`]; if (options.limit) { sql += ` LIMIT ?`; args.push(options.limit); } if (options.offset) { sql += ` OFFSET ?`; args.push(options.offset); } const result = await sqlite.execute({ sql, args }); return result.rows.map(row => { const obj: Record = {}; result.columns.forEach((col, index) => { obj[col] = row[index]; }); return obj; }); } async fullTextSearch(ftsIndex: string, query: string, options: { limit?: number, offset?: number } = {}): Promise { if (!this.ftsIndexes.some(idx => idx.name === ftsIndex)) { throw new Error(`FTS index '${ftsIndex}' does not exist.`); } let sqlQuery = ` SELECT ${this.dbName}.* FROM ${ftsIndex} JOIN ${this.dbName} ON ${this.dbName}.id = ${ftsIndex}.rowid WHERE ${ftsIndex} MATCH ? `; const args: InValue[] = [query]; if (options.limit) { sqlQuery += ' LIMIT ?'; args.push(options.limit); } if (options.offset) { sqlQuery += ' OFFSET ?'; args.push(options.offset); } const result = await sqlite.execute({ sql: sqlQuery, args: args }); return result.rows; } async vectorSearch(embeddingColumn: string, queryVector: number[], options: { limit?: number, offset?: number } = {}): Promise { if (!this.columns.some(col => col.name === embeddingColumn && col.type === 'VECTOR')) { throw new Error(`Column '${embeddingColumn}' does not exist or is not a VECTOR type.`); } const queryVectorString = JSON.stringify(queryVector); let sql = `SELECT *, vector_distance_cos(${embeddingColumn}, ?) as distance FROM ${this.dbName} ORDER BY distance`; const args: InValue[] = [queryVectorString]; if (options.limit) { sql += ` LIMIT ?`; args.push(options.limit); } if (options.offset) { sql += ` OFFSET ?`; args.push(options.offset); } const result = await sqlite.execute({ sql, args }); return result.rows.map(row => { const obj: Record = {}; result.columns.forEach((col, index) => { obj[col] = row[index]; }); return obj; }); } } // New standalone function to drop any database export async function dropDatabase(dbName: string): Promise { try { await sqlite.execute(`DROP TABLE IF EXISTS ${dbName}`); console.log(`Database '${dbName}' has been deleted.`); } catch (error) { console.error(`Error deleting database '${dbName}':`, error); throw error; } }

dobby