[{"_1":2,"_170":-5,"_171":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_37":38,"_39":40,"_41":42,"_43":44,"_45":46,"_47":48,"_49":50,"_51":52,"_53":54,"_55":56,"_57":58,"_59":60,"_61":62,"_63":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","48807a73dfb4c250ae0af97d07e94d04aadb2cac","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_149":150,"_167":168,"_169":-5},"handle","yawnxyz","name","dobby","path","main.tsx","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":108,"_109":110,"_111":-5,"_112":-5,"_12":113,"_114":-5,"_115":108,"_116":-5,"_117":-5,"_118":32,"_14":119,"_120":121,"_122":121,"_123":124,"_125":124,"_126":124,"_127":121,"_128":121,"_23":129,"_132":133,"_135":136,"_148":20},"id","ab88cb7c-2792-11f0-9d52-569c3dd06744","createdAt",["D",1746217134504],"deletedAt","legacyEnvVars",true,"privacy","public","description","imageUrl","a8270e74-6479-475a-8025-9b69544ee0e7","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","8319defa-6e1a-46d4-ba66-2114ffd3360b","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_130":131},"profileImageUrl","https://images.clerk.dev/oauth_github/img_2NnaHhpxNuH1xWRIRjQNoo16TVc.jpeg","org",{"_94":95,"_134":131,"_12":113},"avatar","branch",{"_137":138,"_96":44,"_102":139,"_140":103,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":-5,"_146":97,"_147":95},"version",92,"ab890d26-2792-11f0-9d52-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":151,"_96":99,"_152":153,"_154":-5,"_155":139,"_156":138,"_157":108,"_104":158,"_137":159,"_160":161,"_162":163,"_164":20,"_165":-5,"_98":166},"adaf5f9c-2792-11f0-9d52-569c3dd06744","fileId","ab8b62b0-2792-11f0-9d52-569c3dd06744","parentId","branchId","branchVersion","latest",["D",1727849864664],91,"fileType","script","content","import { sqlite } from \"https://esm.town/v/std/sqlite\";\nimport { sql, eq, ne, gt, gte, lt, lte, like, between, and, or, inArray, isNull, isNotNull } from \"npm:drizzle-orm\";\nimport { drizzle } from \"npm:drizzle-orm/libsql\";\nimport { sqliteTable, text, blob, integer, real, index, unique, uniqueIndex, customType } from \"npm:drizzle-orm/sqlite-core\";\n\ntype ColumnType = 'TEXT' | 'INTEGER' | 'REAL' | 'BLOB' | 'JSON' | 'JSONARRAY' | 'VECTOR' | 'SELF_REFERENCE' | 'BOOLEAN' | 'TIMESTAMP' | 'TIMESTAMP_MS' | 'NUMBER';\n\nconst embedding = customType<{\n data: ArrayBuffer;\n configRequired: true;\n config: {\n size: number;\n };\n}>({\n dataType(config) {\n return `F32_BLOB(${config.size})`;\n },\n fromDriver(value) {\n const isBuffer = Buffer.isBuffer(value);\n if (!isBuffer) throw new Error('Expected a Buffer');\n const arrayBuffer = value.buffer as ArrayBuffer;\n return arrayBuffer;\n },\n toDriver(value) {\n return value;\n },\n});\n\ninterface ColumnDefinition {\n name: string;\n type: ColumnType;\n primaryKey?: boolean;\n notNull?: boolean;\n size?: number;\n autoIncrement?: boolean;\n unique?: boolean; // Added unique property\n}\n\ninterface IndexDefinition {\n name: string;\n columns: string[];\n unique?: boolean;\n}\n\ninterface FTSIndexDefinition {\n name: string;\n columns: string[];\n}\n\nexport class Dobby {\n private db: any;\n private table: any;\n private dbName: string;\n private columns: ColumnDefinition[];\n private indexes: IndexDefinition[];\n private ftsIndexes: FTSIndexDefinition[];\n\n constructor(dbName: string, columns: ColumnDefinition[], indexes: IndexDefinition[] = [], ftsIndexes: FTSIndexDefinition[] = []) {\n this.db = drizzle(sqlite as any);\n this.dbName = dbName;\n this.columns = columns;\n this.indexes = indexes;\n this.ftsIndexes = ftsIndexes;\n this.table = this.createTableSchema();\n }\n\n private createTableSchema() {\n const schema: Record = {};\n this.columns.forEach(col => {\n switch (col.type) {\n case 'TEXT':\n schema[col.name] = text(col.name);\n break;\n case 'INTEGER':\n case 'NUMBER':\n schema[col.name] = integer(col.name, { mode: 'number' });\n break;\n case 'BOOLEAN':\n schema[col.name] = integer(col.name, { mode: 'boolean' });\n break;\n case 'TIMESTAMP':\n schema[col.name] = integer(col.name, { mode: 'timestamp' });\n break;\n case 'TIMESTAMP_MS':\n schema[col.name] = integer(col.name, { mode: 'timestamp_ms' });\n break;\n case 'REAL':\n schema[col.name] = real(col.name);\n break;\n case 'JSON':\n schema[col.name] = text(col.name, { mode: 'json' });\n break;\n case 'JSONARRAY':\n schema[col.name] = text(col.name, { mode: 'json' }).$type[]>();\n break;\n case 'BLOB':\n schema[col.name] = blob(col.name);\n break;\n case 'VECTOR':\n if (!col.size) {\n throw new Error(`Size must be specified for VECTOR column ${col.name}`);\n }\n schema[col.name] = embedding('embedding', { size: col.size });\n break;\n }\n if (col.primaryKey) {\n if ((col.type === 'INTEGER' || col.type === 'NUMBER') && col.autoIncrement) {\n schema[col.name] = schema[col.name].primaryKey({ autoIncrement: true });\n } else {\n schema[col.name] = schema[col.name].primaryKey();\n }\n }\n if (col.notNull) schema[col.name] = schema[col.name].notNull();\n if (col.unique) schema[col.name] = schema[col.name].unique(); // Apply unique constraint\n });\n\n return sqliteTable(this.dbName, schema, (table) => {\n const indexObjects: Record = {};\n this.indexes.forEach(idx => {\n const indexColumns = idx.columns.map(colName => table[colName]);\n if (idx.unique) {\n indexObjects[idx.name] = uniqueIndex(idx.name).on(...indexColumns);\n } else {\n indexObjects[idx.name] = index(idx.name).on(...indexColumns);\n }\n });\n return indexObjects;\n });\n }\n\n async createDatabase(): Promise {\n try {\n const columnDefs = this.columns.map(col =>\n `${col.name} ${col.type}${col.primaryKey ? ' PRIMARY KEY' : ''}${col.notNull ? ' NOT NULL' : ''}`\n ).join(', ');\n\n // console.log('columnDefs:', columnDefs, this.dbName)\n\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${this.dbName} (\n ${columnDefs}\n )\n `);\n\n // Create indexes\n for (const idx of this.indexes) {\n const indexColumns = idx.columns.join(', ');\n const uniqueStr = idx.unique ? 'UNIQUE' : '';\n await sqlite.execute(`\n CREATE ${uniqueStr} INDEX IF NOT EXISTS ${idx.name}\n ON ${this.dbName} (${indexColumns})\n `);\n }\n\n // Create FTS5 virtual tables\n for (const ftsIndex of this.ftsIndexes) {\n const ftsColumns = ftsIndex.columns.join(', ');\n await sqlite.execute(`\n CREATE VIRTUAL TABLE IF NOT EXISTS ${ftsIndex.name}\n USING fts5(${ftsColumns}, content='${this.dbName}', content_rowid='id')\n `);\n\n // Create triggers to keep FTS index updated\n await sqlite.execute(`\n CREATE TRIGGER IF NOT EXISTS ${ftsIndex.name}_ai AFTER INSERT ON ${this.dbName} BEGIN\n INSERT INTO ${ftsIndex.name}(rowid, ${ftsColumns}) VALUES (new.id, ${ftsIndex.columns.map(col => `new.${col}`).join(', ')});\n END;\n `);\n\n await sqlite.execute(`\n CREATE TRIGGER IF NOT EXISTS ${ftsIndex.name}_ad AFTER DELETE ON ${this.dbName} BEGIN\n INSERT INTO ${ftsIndex.name}(${ftsIndex.name}, rowid, ${ftsColumns}) VALUES('delete', old.id, ${ftsIndex.columns.map(col => `old.${col}`).join(', ')});\n END;\n `);\n\n await sqlite.execute(`\n CREATE TRIGGER IF NOT EXISTS ${ftsIndex.name}_au AFTER UPDATE ON ${this.dbName} BEGIN\n INSERT INTO ${ftsIndex.name}(${ftsIndex.name}, rowid, ${ftsColumns}) VALUES('delete', old.id, ${ftsIndex.columns.map(col => `old.${col}`).join(', ')});\n INSERT INTO ${ftsIndex.name}(rowid, ${ftsColumns}) VALUES (new.id, ${ftsIndex.columns.map(col => `new.${col}`).join(', ')});\n END;\n `);\n }\n } catch (error) {\n console.error(`Error creating database '${this.dbName}':`, error);\n throw error;\n }\n }\n\n async insert(data: Record): Promise {\n const cleanedData = Object.fromEntries(Object.entries(data).filter(([_, v]) => v !== null && v !== undefined));\n return await this.db.insert(this.table).values(cleanedData).run();\n }\n\n async get(conditions: Record): Promise {\n const whereClause = sql`${sql.join(\n Object.entries(conditions).map(\n ([key, value]) => sql`${sql.identifier(key)} = ${value}`\n ),\n sql` AND `\n )}`;\n // console.log('whereClause:', whereClause)\n return await this.db.select().from(this.table).where(whereClause).get();\n }\n\n async update(conditions: Record, data: Record): Promise {\n const whereClause = sql`${sql.join(\n Object.entries(conditions).map(\n ([key, value]) => sql`${this.table[key]} = ${value}`\n ),\n sql` AND `\n )}`;\n return await this.db.update(this.table).set(data).where(whereClause).run();\n }\n\n async delete(conditions: Record): Promise {\n const whereClause = sql`${sql.join(\n Object.entries(conditions).map(\n ([key, value]) => sql`${this.table[key]} = ${value}`\n ),\n sql` AND `\n )}`;\n return await this.db.delete(this.table).where(whereClause).run();\n }\n\n async list(limit: number = 100): Promise {\n return await this.db.select().from(this.table).limit(limit).all();\n }\n\n async deleteDatabase(): Promise {\n try {\n // Drop the main table\n await sqlite.execute(`DROP TABLE IF EXISTS ${this.dbName}`);\n\n // Drop FTS virtual tables\n for (const ftsIndex of this.ftsIndexes) {\n await sqlite.execute(`DROP TABLE IF EXISTS ${ftsIndex.name}`);\n }\n\n console.log(`Database '${this.dbName}' and its FTS indexes have been deleted.`);\n } catch (error) {\n console.error(`Error deleting database '${this.dbName}':`, error);\n throw error;\n }\n }\n\n async upsert(data: Record, uniqueKeys: string[] = [\"id\"]): Promise {\n const uniqueConditions = Object.fromEntries(\n uniqueKeys.map(key => [key, data[key]])\n );\n\n const existingRecord = await this.get(uniqueConditions);\n\n if (existingRecord) {\n // Update existing record\n return await this.update(uniqueConditions, data);\n } else {\n // Insert new record\n return await this.insert(data);\n }\n }\n\n async filter(conditions: Record, options: { limit?: number, offset?: number } = {}): Promise {\n const whereConditions: string[] = [];\n const args: InValue[] = [];\n\n for (const [key, value] of Object.entries(conditions)) {\n if (typeof value === 'object' && value !== null) {\n const [operator, operand] = Object.entries(value)[0];\n switch (operator) {\n case 'eq': whereConditions.push(`${key} = ?`); args.push(operand); break;\n case 'ne': whereConditions.push(`${key} != ?`); args.push(operand); break;\n case 'gt': whereConditions.push(`${key} > ?`); args.push(operand); break;\n case 'gte': whereConditions.push(`${key} >= ?`); args.push(operand); break;\n case 'lt': whereConditions.push(`${key} < ?`); args.push(operand); break;\n case 'lte': whereConditions.push(`${key} <= ?`); args.push(operand); break;\n case 'like': whereConditions.push(`${key} LIKE ?`); args.push(operand); break;\n case 'between': whereConditions.push(`${key} BETWEEN ? AND ?`); args.push(operand[0], operand[1]); break;\n case 'in': whereConditions.push(`${key} IN (${operand.map(() => '?').join(',')})`); args.push(...operand); break;\n case 'isNull': whereConditions.push(`${key} IS NULL`); break;\n case 'isNotNull': whereConditions.push(`${key} IS NOT NULL`); break;\n }\n } else {\n whereConditions.push(`${key} = ?`);\n args.push(value);\n }\n }\n\n let sql = `SELECT * FROM ${this.dbName}`;\n if (whereConditions.length > 0) {\n sql += ` WHERE ${whereConditions.join(' AND ')}`;\n }\n if (options.limit) {\n sql += ` LIMIT ?`;\n args.push(options.limit);\n }\n if (options.offset) {\n sql += ` OFFSET ?`;\n args.push(options.offset);\n }\n\n // console.log('Generated SQL:', sql);\n // console.log('Arguments:', args);\n\n const result = await sqlite.execute({\n sql,\n args\n });\n\n return result.rows.map(row => {\n const obj: Record = {};\n result.columns.forEach((col, index) => {\n obj[col] = row[index];\n });\n return obj;\n });\n }\n\n async search(column: string, searchTerm: string, options: { limit?: number, offset?: number } = {}): Promise {\n if (!this.columns.some(col => col.name === column)) {\n throw new Error(`Column '${column}' does not exist in the table.`);\n }\n\n let sql = `SELECT * FROM ${this.dbName} WHERE ${column} LIKE ?`;\n const args: InValue[] = [`%${searchTerm}%`];\n\n if (options.limit) {\n sql += ` LIMIT ?`;\n args.push(options.limit);\n }\n\n if (options.offset) {\n sql += ` OFFSET ?`;\n args.push(options.offset);\n }\n\n const result = await sqlite.execute({\n sql,\n args\n });\n\n return result.rows.map(row => {\n const obj: Record = {};\n result.columns.forEach((col, index) => {\n obj[col] = row[index];\n });\n return obj;\n });\n }\n\n async fullTextSearch(ftsIndex: string, query: string, options: { limit?: number, offset?: number } = {}): Promise {\n if (!this.ftsIndexes.some(idx => idx.name === ftsIndex)) {\n throw new Error(`FTS index '${ftsIndex}' does not exist.`);\n }\n\n let sqlQuery = `\n SELECT ${this.dbName}.*\n FROM ${ftsIndex}\n JOIN ${this.dbName} ON ${this.dbName}.id = ${ftsIndex}.rowid\n WHERE ${ftsIndex} MATCH ?\n `;\n\n const args: InValue[] = [query];\n\n if (options.limit) {\n sqlQuery += ' LIMIT ?';\n args.push(options.limit);\n }\n\n if (options.offset) {\n sqlQuery += ' OFFSET ?';\n args.push(options.offset);\n }\n\n const result = await sqlite.execute({\n sql: sqlQuery,\n args: args\n });\n\n return result.rows;\n }\n\n async vectorSearch(embeddingColumn: string, queryVector: number[], options: { limit?: number, offset?: number } = {}): Promise {\n if (!this.columns.some(col => col.name === embeddingColumn && col.type === 'VECTOR')) {\n throw new Error(`Column '${embeddingColumn}' does not exist or is not a VECTOR type.`);\n }\n\n const queryVectorString = JSON.stringify(queryVector);\n let sql = `SELECT *, vector_distance_cos(${embeddingColumn}, ?) as distance FROM ${this.dbName} ORDER BY distance`;\n const args: InValue[] = [queryVectorString];\n\n if (options.limit) {\n sql += ` LIMIT ?`;\n args.push(options.limit);\n }\n\n if (options.offset) {\n sql += ` OFFSET ?`;\n args.push(options.offset);\n }\n\n const result = await sqlite.execute({\n sql,\n args\n });\n\n return result.rows.map(row => {\n const obj: Record = {};\n result.columns.forEach((col, index) => {\n obj[col] = row[index];\n });\n return obj;\n });\n }\n}\n\n// New standalone function to drop any database\nexport async function dropDatabase(dbName: string): Promise {\n try {\n await sqlite.execute(`DROP TABLE IF EXISTS ${dbName}`);\n console.log(`Database '${dbName}' has been deleted.`);\n } catch (error) {\n console.error(`Error deleting database '${dbName}':`, error);\n throw error;\n }\n}\n","deleted","label",[99],"permalink","https://www.val.town/x/yawnxyz/dobby/code/main.tsx","extension","actionData","errors"]