[{"_1":2,"_3":-5,"_4":-5},"loaderData",{"_5":6,"_7":8,"_9":10},"actionData","errors","root",{"_220":221,"_222":223},"routes/_app.x.$handle.$name",{"_96":95,"_31":42,"_104":86,"_26":105,"_13":14,"_106":-5,"_11":107,"_108":109,"_110":111,"_112":113,"_114":115,"_20":-5,"_116":-5,"_117":-5,"_118":24,"_119":120,"_121":46,"_122":-5,"_123":-5,"_124":125},"routes/_app.x.$handle.$name.branch.$branch.version.$version.diff",{"_11":12,"_13":14,"_15":16,"_17":-5,"_18":-5},"project",{"_40":41,"_31":42,"_35":43,"_44":-5,"_45":46,"_47":48,"_49":48,"_50":-5,"_51":-5,"_18":52,"_53":-5,"_54":46,"_55":-5,"_56":-5,"_57":58,"_59":60,"_61":62,"_63":64,"_65":66,"_67":68,"_69":70,"_71":70,"_72":73,"_74":73,"_75":73,"_76":70,"_77":70,"_78":79,"_80":81,"_82":83,"_84":24},"version",424,"changes",[19],"aiChatInfo","userId",{"_20":21,"_22":-7,"_23":24,"_25":24,"_26":27,"_28":27},"file",{"_29":30,"_31":27,"_32":20,"_33":34,"_35":36,"_37":14,"_38":39,"_23":24},"previous","deleted",false,"deletedHere","path","AGENTS.md","latestPath","fileId","173e173c-1752-11f1-b042-42dde27851f2","name","fileType","content","You are an advanced assistant specialized in generating Val Town code.\n\n## Core Guidelines\n\n- Ask clarifying questions when requirements are ambiguous\n- Provide complete, functional solutions rather than skeleton implementations\n- Test your logic against edge cases before presenting the final solution\n- Ensure all code follows Val Town's specific platform requirements\n- If a section of code that you're working on is getting too complex, consider refactoring it into subcomponents\n\n## Code Standards\n\n- Generate code in TypeScript or TSX\n- Add appropriate TypeScript types and interfaces for all data structures\n- Prefer official SDKs or libraries than writing API calls directly\n- Ask the user to supply API or library documentation if you are at all unsure about it\n- **Never bake in secrets into the code** - always use environment variables\n- Include comments explaining complex logic (avoid commenting obvious operations)\n- Follow modern ES6+ conventions and functional programming practices if possible\n\n## Types of triggers\n\n### 1. HTTP Trigger\n\n- Create web APIs and endpoints\n- Handle HTTP requests and responses\n- Example structure:\n\n```ts\nexport default async function (req: Request) {\n return new Response(\"Hello World\");\n}\n```\n\nFiles that are HTTP triggers have http in their name like `foobar.http.tsx`\n\n### 2. Cron Triggers\n\n- Run on a schedule\n- Use cron expressions for timing\n- Example structure:\n\n```ts\nexport default async function () {\n // Scheduled task code\n}\n```\n\nFiles that are Cron triggers have cron in their name like `foobar.cron.tsx`\n\n### 3. Email Triggers\n\n- Process incoming emails\n- Handle email-based workflows\n- Example structure:\n\n```ts\nexport default async function (email: Email) {\n // Process email\n}\n```\n\nFiles that are Email triggers have email in their name like `foobar.email.tsx`\n\n\n## Val Town Standard Libraries\n\nVal Town provides several hosted services and utility functions.\n\n### Blob Storage\n\n```ts\nimport { blob } from \"https://esm.town/v/std/blob\";\nawait blob.setJSON(\"myKey\", { hello: \"world\" });\nlet blobDemo = await blob.getJSON(\"myKey\");\nlet appKeys = await blob.list(\"app_\");\nawait blob.delete(\"myKey\");\n```\n\n### SQLite\n\n```ts\nimport { sqlite } from \"https://esm.town/v/stevekrouse/sqlite\";\nconst TABLE_NAME = 'todo_app_users_2';\n// Create table - do this before usage and change table name when modifying schema\nawait sqlite.execute(`CREATE TABLE IF NOT EXISTS ${TABLE_NAME} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n name TEXT NOT NULL\n)`);\n// Query data\nconst result = await sqlite.execute(`SELECT * FROM ${TABLE_NAME} WHERE id = ?`, [1]);\n```\n\nNote: When changing a SQLite table's schema, change the table's name (e.g., add _2 or _3) to create a fresh table.\n\n### OpenAI\n\n```ts\nimport { OpenAI } from \"https://esm.town/v/std/openai\";\nconst openai = new OpenAI();\nconst completion = await openai.chat.completions.create({\n messages: [\n { role: \"user\", content: \"Say hello in a creative way\" },\n ],\n model: \"gpt-4o-mini\",\n max_tokens: 30,\n});\n```\n\n### Email\n\n```ts\nimport { email } from \"https://esm.town/v/std/email\";\n// By default emails the owner of the val\nawait email({ \n subject: \"Hi\", \n text: \"Hi\", \n html: \"

Hi

\"\n});\n```\n\n## Val Town Utility Functions\n\nVal Town provides several utility functions to help with common project tasks.\n\n### Importing Utilities\n\nAlways import utilities with version pins to avoid breaking changes:\n\n```ts\nimport { parseProject, readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n```\n\n### Available Utilities\n\n\n#### **serveFile** - Serve project files with proper content types\n\nFor example, in Hono:\n\n```ts\n// serve all files in frontend/ and shared/\napp.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\napp.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n```\n\n#### **readFile** - Read files from within the project:\n\n```ts\n// Read a file from the project\nconst fileContent = await readFile(\"/frontend/index.html\", import.meta.url);\n```\n\n#### **listFiles** - List all files in the project\n\n```ts\nconst files = await listFiles(import.meta.url);\n```\n\n#### **parseProject** - Extract information about the current project from import.meta.url\n\nThis is useful for including info for linking back to a val, ie in \"view source\" urls:\n\n```ts\nconst projectVal = parseProject(import.meta.url);\nconsole.log(projectVal.username); // Owner of the project\nconsole.log(projectVal.name); // Project name\nconsole.log(projectVal.version); // Version number\nconsole.log(projectVal.branch); // Branch name\nconsole.log(projectVal.links.self.project); // URL to the project page\n```\n\nHowever, it's *extremely importing* to note that `parseProject` and other Standard Library utilities ONLY RUN ON THE SERVER.\nIf you need access to this data on the client, run it in the server and pass it to the client by splicing it into the HTML page\nor by making an API request for it.\n\n## Val Town Platform Specifics\n\n- **Redirects:** Use `return new Response(null, { status: 302, headers: { Location: \"/place/to/redirect\" }})` instead of `Response.redirect` which is broken\n- **Images:** Avoid external images or base64 images. Use emojis, unicode symbols, or icon fonts/libraries instead\n- **AI Image:** To inline generate an AI image use: `

`\n- **Storage:** DO NOT use the Deno KV module for storage\n- **Browser APIs:** DO NOT use the `alert()`, `prompt()`, or `confirm()` methods\n- **Weather Data:** Use open-meteo for weather data (doesn't require API keys) unless otherwise specified\n- **View Source:** Add a view source link by importing & using `import.meta.url.replace(\"ems.sh\", \"val.town)\"` (or passing this data to the client) and include `target=\"_top\"` attribute\n- **Error Debugging:** Add `` to HTML to capture client-side errors\n- **Error Handling:** Only use try...catch when there's a clear local resolution; Avoid catches that merely log or return 500s. Let errors bubble up with full context\n- **Environment Variables:** Use `Deno.env.get('keyname')` when you need to, but generally prefer APIs that don't require keys\n- **Imports:** Use `https://esm.sh` for npm and Deno dependencies to ensure compatibility on server and browser\n- **Storage Strategy:** Only use backend storage if explicitly required; prefer simple static client-side sites\n- **React Configuration:** When using React libraries, pin versions with `?deps=react@18.2.0,react-dom@18.2.0` and start the file with `/** @jsxImportSource https://esm.sh/react@18.2.0 */`\n- Ensure all React dependencies and sub-dependencies are pinned to the same version\n- **Styling:** Default to using TailwindCSS via `` unless otherwise specified\n\n## Project Structure and Design Patterns\n\n### Recommended Directory Structure\n```\n├── backend/\n│ ├── database/\n│ │ ├── migrations.ts # Schema definitions\n│ │ ├── queries.ts # DB query functions\n│ │ └── README.md\n│ └── routes/ # Route modules\n│ ├── [route].ts\n│ └── static.ts # Static file serving\n│ ├── index.ts # Main entry point\n│ └── README.md\n├── frontend/\n│ ├── components/\n│ │ ├── App.tsx\n│ │ └── [Component].tsx\n│ ├── favicon.svg\n│ ├── index.html # Main HTML template\n│ ├── index.tsx # Frontend JS entry point\n│ ├── README.md\n│ └── style.css\n├── README.md\n└── shared/\n ├── README.md\n └── utils.ts # Shared types and functions\n```\n\n### Backend (Hono) Best Practices\n\n- Hono is the recommended API framework\n- Main entry point should be `backend/index.ts`\n- **Static asset serving:** Use the utility functions to read and serve project files:\n ```ts\n import { readFile, serveFile } from \"https://esm.town/v/std/utils@85-main/index.ts\";\n \n // serve all files in frontend/ and shared/\n app.get(\"/frontend/*\", c => serveFile(c.req.path, import.meta.url));\n app.get(\"/shared/*\", c => serveFile(c.req.path, import.meta.url));\n \n // For index.html, often you'll want to bootstrap with initial data\n app.get(\"/\", async c => {\n let html = await readFile(\"/frontend/index.html\", import.meta.url);\n \n // Inject data to avoid extra round-trips\n const initialData = await fetchInitialData();\n const dataScript = ``;\n \n html = html.replace(\"\", `${dataScript}`);\n return c.html(html);\n });\n ```\n- Create RESTful API routes for CRUD operations\n- Always include this snippet at the top-level Hono app to re-throwing errors to see full stack traces:\n ```ts\n // Unwrap Hono errors to see original error details\n app.onError((err, c) => {\n throw err;\n });\n ```\n\n### Database Patterns\n- Run migrations on startup or comment out for performance\n- Change table names when modifying schemas rather than altering\n- Export clear query functions with proper TypeScript typing\n\n## Common Gotchas and Solutions\n\n1. **Environment Limitations:** \n - Val Town runs on Deno in a serverless context, not Node.js\n - Code in `shared/` must work in both frontend and backend environments\n - Cannot use `Deno` keyword in shared code\n - Use `https://esm.sh` for imports that work in both environments\n\n2. **SQLite Peculiarities:**\n - Limited support for ALTER TABLE operations\n - Create new tables with updated schemas and copy data when needed\n - Always run table creation before querying\n\n3. **React Configuration:**\n - All React dependencies must be pinned to 18.2.0\n - Always include `@jsxImportSource https://esm.sh/react@18.2.0` at the top of React files\n - Rendering issues often come from mismatched React versions\n\n4. **File Handling:**\n - Val Town only supports text files, not binary\n - Use the provided utilities to read files across branches and forks\n - For files in the project, use `readFile` helpers\n\n5. **API Design:**\n - `fetch` handler is the entry point for HTTP vals\n - Run the Hono app with `export default app.fetch // This is the entry point for HTTP vals`\n\n","createdAt",["D",1772577676981],"branchVersion","branchId","29488f20-27b6-11f0-aecd-569c3dd06744","id","29484b28-27b6-11f0-aecd-569c3dd06744","SQLiteTable",["D",1746232377860],"deletedAt","legacyEnvVars",true,"privacy","public","httpPrivacy","description","imageUrl","f129b0f0-09bd-41ac-a51c-c16933d81d65","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount",1,"orgId","5cdb635f-04df-409e-bb23-93b87a60f6ca","tursoName","29484b2827b611f0aecd569c3dd06744","tursoHostname","29484b2827b611f0aecd569c3dd06744-val-town.aws-us-east-2.turso.io","tursoMeasuredAt",["D",1789519256886],"pinned",0,"userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm","user",{"_96":95,"_103":99},"org",{"_96":95,"_97":-5,"_98":99,"_18":52,"_100":101,"_102":24},"branch",{"_13":85,"_31":86,"_40":39,"_87":41,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":42,"_94":95},"isOwned",509,"main","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","pql","handle","displayName","avatar","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJrYUVVM1VyRHRhckNnNGxidGEwUUdaSFRDYSJ9","tier","pro","canRestrictVals","profileImageUrl","branchName","","adoptOrgId",{"_40":41,"_31":42,"_35":215,"_44":-5,"_45":46,"_47":48,"_49":48,"_50":-5,"_51":-5,"_18":52,"_53":-5,"_54":46,"_55":-5,"_56":-5,"_57":58,"_59":60,"_61":62,"_63":64,"_65":216,"_67":68,"_69":70,"_71":70,"_72":73,"_74":73,"_75":73,"_76":70,"_77":70,"_78":217,"_80":218,"_82":219,"_84":24},"tree",[190,191,192,193,194,195],"files",[190,191,192,193,194,195],"counts",{"_180":68,"_181":58,"_182":68,"_183":184,"_185":186},"contents",[126,127,128,129,130,131],"upstreamProject","upstreamBranch","emptyEnvVars","flags",[],"hasHttpFiles","grants","orgs","customDomains",{},{"_40":177,"_31":27,"_29":30,"_135":-5,"_38":39,"_37":14,"_137":46,"_35":178,"_13":68,"_32":20,"_33":34,"_23":24,"_142":-5,"_26":179},{"_40":169,"_31":170,"_29":171,"_135":-5,"_38":39,"_37":172,"_137":24,"_35":173,"_13":174,"_32":20,"_33":175,"_23":24,"_142":-5,"_26":176},{"_40":160,"_31":161,"_29":162,"_135":-5,"_38":39,"_37":163,"_137":46,"_35":164,"_13":165,"_32":166,"_33":167,"_23":24,"_142":-5,"_26":168},{"_40":152,"_31":153,"_29":154,"_135":-5,"_38":39,"_37":155,"_137":24,"_35":156,"_13":157,"_32":140,"_33":158,"_23":24,"_142":-5,"_26":159},{"_40":144,"_31":145,"_29":146,"_135":-5,"_38":39,"_37":147,"_137":24,"_35":148,"_13":149,"_32":140,"_33":150,"_23":24,"_142":-5,"_26":151},{"_40":132,"_31":133,"_29":134,"_135":-5,"_38":39,"_37":136,"_137":24,"_35":138,"_13":139,"_32":140,"_33":141,"_23":24,"_142":-5,"_26":143},"23d52a3a-1671-11f1-a92c-42dde27851f2","utils","14f88bd6-68bd-11f0-9b48-0224a6c84d84","parentId",423,"latest",["D",1772481061338],21,"script","import { OpenAI } from \"https://esm.town/v/std/openai\";\n\n// ---- SHARED TYPES ---- //\n\n// Select conditions: key-value equalities (AND-joined) or raw SQL string\nexport type RowSelectCondition = object | string;\n\nexport type SelectOutputOptionsBase = {\n json?: boolean;\n columns?: string[];\n limit?: number;\n};\n\nexport type SelectOutputOptions = SelectOutputOptionsBase & (\n | { ascendBy?: string; descendBy?: never }\n | { descendBy?: string; ascendBy?: never }\n);\n\n\nexport type StreamOptions = {\n tableName?: string;\n batchSize?: number;\n stopAt?: number;\n};\n\n// ---- FUNCTIONS ---- //\n\nexport async function AISuggestCmd(plainText: string, tableName?: string): Promise {\n if (!plainText) return \"No prompt provided\";\n\n const tableSpecificSnippet = tableName ? `for a table named ${tableName}` : \"\";\n\n const decodedPrompt =\n `Give me just the sqlite command ${tableSpecificSnippet}, with no formatting or comments: ${plainText}`;\n\n // Initialize OpenAI\n const openai = new OpenAI();\n\n try {\n // Generate response using OpenAI\n const completion = await openai.chat.completions.create({\n messages: [\n { role: \"user\", content: decodedPrompt },\n ],\n max_tokens: 3000,\n });\n\n const [{ message: { content } }] = completion.choices;\n // Return the response as JSON\n console.log(\"AI suggestion: \", content);\n return content;\n } catch (error) {\n return \"Failed to generate response\";\n }\n}\n\nexport async function SQLiteTableInfo() {\n const { sqlite, name: tableName } = this;\n try {\n // First, verify the table exists\n const tableExists = await sqlite.execute(\n `SELECT name FROM sqlite_master WHERE type='table' AND name = ?`,\n [tableName],\n );\n\n if (tableExists.rows.length === 0) {\n throw new Error(`Table ${tableName} does not exist`);\n }\n\n // Get table-specific information\n const tableInfo = await sqlite.execute(`PRAGMA table_info(${tableName})`);\n\n // Count rows in the table\n const { rows: [{ row_count: rowCount }] } = await sqlite.execute(`SELECT COUNT(*) as row_count FROM ${tableName}`);\n\n // Get table size details using SQLite-specific queries\n const { rows: [{ page_size: pageSize }] } = await sqlite.execute(`PRAGMA page_size`);\n\n // Get page count for this specific table\n const { rows: [{ page_count: pageCount }] } = await sqlite.execute(\n `\n SELECT count(*) as page_count \n FROM sqlite_master \n WHERE type='table' AND name=? \n AND rootpage > 0\n `,\n [tableName],\n );\n\n // Estimate table size based on page count and page size\n const estimatedTableBytes = pageCount * pageSize;\n\n return {\n // tableName,\n columns: tableInfo.rows.map(({ name, type }) => ({ name, type })),\n rowCount,\n pageSize,\n pageCount,\n estimatedTableBytes,\n humanReadableSize: formatBytes(estimatedTableBytes),\n // indexes: indexInfoResult.rows.map(index => index.name),\n // indexCount: indexInfoResult.rows.length,\n };\n } catch (error) {\n return {\n error: `Error getting size for table ${tableName}: ${error.message}`,\n tableName,\n };\n }\n}\n\nfunction formatBytes(bytes: number, decimals = 2): string {\n if (bytes === 0) return \"0 Bytes\";\n\n const k = 1024;\n const dm = decimals < 0 ? 0 : decimals;\n const sizes = [\"Bytes\", \"KB\", \"MB\", \"GB\", \"TB\", \"PB\", \"EB\", \"ZB\", \"YB\"];\n\n const i = Math.floor(Math.log(bytes) / Math.log(k));\n\n return parseFloat((bytes / Math.pow(k, i)).toFixed(dm)) + \" \" + sizes[i];\n}\n\n\n// Generates a bulk SQLite INSERT … ON CONFLICT upsert statement that MERGES records by conflictKey:\n// inserts new rows, updates existing rows only for fields explicitly provided, and preserves\n// existing values when incoming fields are missing (undefined). Requires a UNIQUE constraint\n// on conflictKey. Does not perform validation, type coercion, parameter binding, or per-row logic.\n\nexport const assembleBulkUpsertCommand = (\n tableName: string,\n records: object[],\n conflictKey: string,\n) => {\n if (!records || records.length === 0) {\n throw new Error(\"No records provided\");\n }\n\n if (!conflictKey) {\n throw new Error(\"Conflict key must be specified\");\n }\n\n const columns = Array.from(\n new Set(records.flatMap(record => Object.keys(record))),\n );\n\n if (!columns.includes(conflictKey)) {\n throw new Error(`Conflict key '${conflictKey}' not found in records`);\n }\n\n const values = records.map(record => {\n const rowValues = columns.map(col => {\n const value = record[col];\n return value === undefined\n ? \"NULL\"\n : `'${String(value).replace(/'/g, \"''\")}'`;\n });\n return `(${rowValues.join(\", \")})`;\n }).join(\",\\n \");\n\n const nonConflictColumns = columns.filter(col => col !== conflictKey);\n\n return `\n INSERT INTO ${tableName} (${columns.join(\", \")})\n VALUES\n ${values}\n ON CONFLICT(${conflictKey}) DO UPDATE SET\n ${nonConflictColumns\n .map(col => `${col} = COALESCE(excluded.${col}, ${col})`)\n .join(\",\\n \")};\n `.trim();\n};\n\n\nexport const assembleBulkInsertCommand = (tableName: string, records: object[]): string => {\n if (!records || records.length === 0) {\n throw new Error(\"No records provided\");\n }\n\n // Collect all column names from records\n const columns = Array.from(\n new Set(records.flatMap(record => Object.keys(record)))\n );\n\n // Escape and serialize values\n const values = records.map(record => {\n const rowValues = columns.map(col => {\n const value = record[col];\n return value === undefined ? \"NULL\" : `'${String(value).replace(/'/g, \"''\")}'`;\n });\n return `(${rowValues.join(\", \")})`;\n }).join(\",\\n \");\n\n return `\n INSERT INTO ${tableName} (${columns.join(\", \")})\n VALUES\n ${values};\n `.trim();\n};\n\nexport async function streamTableRows(\n processRowFn: (rows: Record[]) => void | Promise,\n options: StreamOptions = {},\n): Promise {\n const { sqlite } = this;\n const {\n tableName,\n batchSize = 1000,\n stopAt = Infinity,\n } = options;\n\n const KEY = \"SQLiteUtils\";\n const effectiveTableName = tableName || `${KEY}_rows`;\n\n try {\n if (!/^[a-zA-Z0-9_]+$/.test(effectiveTableName)) {\n throw new Error(\"Invalid table name. Only alphanumeric characters and underscores allowed.\");\n }\n\n let offset = 0;\n let batchCount = 0;\n\n while (batchCount < stopAt) {\n const query = `\n SELECT * FROM ${effectiveTableName}\n LIMIT ${batchSize}\n OFFSET ${offset}\n `;\n\n const result = await sqlite.execute(query);\n\n if (result.rows.length === 0) {\n break;\n }\n\n await processRowFn(result.rows);\n\n offset += batchSize;\n batchCount += 1;\n }\n\n return true;\n } catch (error) {\n console.error(\"Error streaming rows:\", error);\n throw error;\n }\n}\n\n// ---- HTTP ACCESS ---- //\n\nconst ALLOWED_OPERATIONS = [\n \"getRow\",\n \"getRows\",\n \"countRows\",\n \"getInfo\",\n] as const;\ntype AllowedOperation = typeof ALLOWED_OPERATIONS[number];\n\nexport async function http(req: Request): Promise {\n const url = new URL(req.url);\n console.log(url);\n const pathSegments = url.pathname.split(\"/\")\n .filter((segment) => segment.length > 0);\n\n const operation = pathSegments.at(-1) as AllowedOperation;\n\n if (!ALLOWED_OPERATIONS.includes(operation as AllowedOperation)) {\n return Response.json(\n {\n error: `Unknown operation: ${operation}. Supported operations: ${\n ALLOWED_OPERATIONS.join(\", \")\n }`,\n },\n { status: 400 },\n );\n }\n\n try {\n // Parse query params into select conditions.\n // Reserved params (prefixed with _) are treated as output options, not conditions.\n const conditions: Record = {};\n const outputOptions: SelectOutputOptions = { json: true };\n\n for (const [key, value] of url.searchParams.entries()) {\n if (key.startsWith(\"_\")) {\n const optionKey = key.slice(1) as keyof SelectOutputOptions;\n // Attempt to coerce numeric strings to numbers, otherwise treat as comma-split array or plain string\n if (value !== \"\" && !isNaN(Number(value))) {\n (outputOptions as any)[optionKey] = Number(value);\n } else if (value.includes(\",\")) {\n (outputOptions as any)[optionKey] = value.split(\",\").map((c) => c.trim());\n } else {\n (outputOptions as any)[optionKey] = value;\n }\n } else {\n // Attempt to coerce numeric strings to numbers\n const coerced = value !== \"\" && !isNaN(Number(value))\n ? Number(value)\n : value;\n conditions[key] = coerced;\n }\n }\n\n const hasConditions = Object.keys(conditions).length > 0;\n const args: any[] = [hasConditions ? conditions : undefined];\n if (operation !== \"countRows\") args.push(outputOptions);\n const result = await (this[operation] as Function)(...args);\n\n return Response.json(result ?? null);\n } catch (error) {\n console.error(`Error in ${operation} operation:`, error);\n return Response.json(\n { error: error.message || \"Internal server error\" },\n { status: 500 },\n );\n }\n}\n","label",[133],"59d00bba-1670-11f1-b7d0-42dde27851f2","tests.tsx","7701205e-0f93-11f1-a2bc-42dde27851f2",422,["D",1772480722388],12,"import { SQLiteTable } from \"./main.tsx\";\n\n// Test table using object-form schema\nconst _testTable = new SQLiteTable(\n \"_testTable\",\n {\n id: { type: \"integer\", primaryKey: true },\n name: { type: \"text\" },\n score: { type: \"number\" },\n },\n { scope: \"val\" },\n);\n\n// Test table using string-form schema\nconst _testTableStr = new SQLiteTable(\n \"_testTableStr\",\n {\n id: \"integer primary key\",\n label: \"text not null\",\n value: \"real\",\n },\n { scope: \"val\" },\n);\n\ntype TestResult = { name: string; passed: boolean; error?: string };\n\nfunction assert(condition: boolean, message: string) {\n if (!condition) throw new Error(`Assertion failed: ${message}`);\n}\n\nfunction assertEqual(actual: any, expected: any, message: string) {\n const a = JSON.stringify(actual);\n const e = JSON.stringify(expected);\n if (a !== e) {\n throw new Error(`${message}\\n expected: ${e}\\n actual: ${a}`);\n }\n}\n\nasync function runTest(\n name: string,\n fn: () => Promise,\n): Promise {\n try {\n await fn();\n return { name, passed: true };\n } catch (err) {\n return { name, passed: false, error: String(err) };\n }\n}\n\n// ── Setup / Teardown ────────────────────────────────────────────────────────\n\nasync function setup() {\n // Drop any leftover tables from a previous failed run, then re-create them\n if (await _testTable.exists()) await _testTable.drop();\n await _testTable.create();\n\n if (await _testTableStr.exists()) await _testTableStr.drop();\n await _testTableStr.create();\n}\n\nasync function teardown() {\n if (await _testTable.exists()) await _testTable.drop();\n if (await _testTableStr.exists()) await _testTableStr.drop();\n}\n\n// ── Individual tests ────────────────────────────────────────────────────────\n\nconst tests = [\n // ── Object-form schema ─────────────────────────────────────────────────────\n\n async function tableExists() {\n const result = await _testTable.exists();\n assert(!!result, \"table should exist after create()\");\n },\n\n async function insertAndGetRow() {\n await _testTable.insertRow({ id: 1, name: \"Alice\", score: 42 });\n const row = await _testTable.getRow({ id: 1 }, { json: true });\n assertEqual(\n row,\n { id: 1, name: \"Alice\", score: 42 },\n \"inserted row mismatch\",\n );\n },\n\n async function getRows() {\n await _testTable.insertRow({ id: 2, name: \"Bob\", score: 99 });\n await _testTable.insertRow({ id: 3, name: \"Charlie\", score: 7 });\n const rows = await _testTable.getRows(undefined, { json: true });\n assert(rows.length === 3, `expected 3 rows, got ${rows.length}`);\n },\n\n async function countRows() {\n const count = await _testTable.countRows();\n assertEqual(count, 3, \"countRows should return 3\");\n },\n\n async function countRowsWithCondition() {\n const count = await _testTable.countRows({ score: { greaterThan: 10 } });\n assertEqual(count, 2, \"rows with score > 10 should be 2\");\n },\n\n async function updateRow() {\n await _testTable.updateRow({ id: 1 }, { score: 100 });\n const row = await _testTable.getRow({ id: 1 }, { json: true });\n assertEqual(row?.score, 100, \"score should be updated to 100\");\n },\n\n async function batchInsert() {\n await _testTable.batchInsert([\n { id: 4, name: \"Dave\", score: 55 },\n { id: 5, name: \"Eve\", score: 60 },\n ]);\n const count = await _testTable.countRows();\n assertEqual(count, 5, \"batchInsert: total rows should be 5\");\n },\n\n async function batchUpsert() {\n // Upsert existing id=1 (update score) and new id=6\n await _testTable.batchUpsert(\n [\n { id: 1, name: \"Alice\", score: 200 },\n { id: 6, name: \"Frank\", score: 11 },\n ],\n \"id\",\n );\n const alice = await _testTable.getRow({ id: 1 }, { json: true });\n assertEqual(alice?.score, 200, \"upserted alice score should be 200\");\n const count = await _testTable.countRows();\n assertEqual(count, 6, \"batchUpsert: total rows should be 6\");\n },\n\n async function getInfo() {\n const info = await _testTable.getInfo();\n assert(\n typeof info === \"object\" && info !== null,\n \"getInfo should return an object\",\n );\n assert(Array.isArray(info.columns), \"getInfo.columns should be an array\");\n const names = info.columns.map((c: any) => c.name);\n assert(names.includes(\"id\"), \"getInfo should include 'id' column\");\n assert(names.includes(\"name\"), \"getInfo should include 'name' column\");\n assert(names.includes(\"score\"), \"getInfo should include 'score' column\");\n assert(\n typeof info.rowCount === \"number\",\n \"getInfo.rowCount should be a number\",\n );\n },\n\n async function addIndexAndGetIndices() {\n await _testTable.addIndex({\n name: \"_testTable_score_idx\",\n column: \"score\",\n });\n const indices = await _testTable.getIndices();\n assert(Array.isArray(indices), \"getIndices should return an array\");\n const found = indices.some((i: any) => i.name === \"_testTable_score_idx\");\n assert(found, \"created index should appear in getIndices()\");\n },\n\n async function selectColumns() {\n const rows = await _testTable.getRows(undefined, {\n json: true,\n columns: [\"id\", \"name\"],\n });\n assert(rows.length > 0, \"should return rows\");\n assert(!(\"score\" in rows[0]), \"score column should be excluded\");\n assert(\"name\" in rows[0], \"name column should be present\");\n },\n\n // ── deleteRows ─────────────────────────────────────────────────────────────\n\n async function insertRowDuplicateKeyThrows() {\n await _testTable.insertRow({ id: 99, name: \"Unique\", score: 1 });\n let threw = false;\n try {\n await _testTable.insertRow({ id: 99, name: \"Duplicate\", score: 2 });\n } catch (_err) {\n threw = true;\n }\n assert(threw, \"inserting a duplicate primary key should throw\");\n },\n\n async function deleteRowsWithCondition() {\n const before = await _testTable.countRows();\n await _testTable.deleteRows({ id: 6 });\n const after = await _testTable.countRows();\n assertEqual(after, before - 1, \"deleteRows should remove exactly 1 row\");\n const gone = await _testTable.getRow({ id: 6 }, { json: true });\n assertEqual(gone, undefined, \"deleted row should not be retrievable\");\n },\n\n async function deleteAllRows() {\n await _testTable.deleteRows();\n const count = await _testTable.countRows();\n assertEqual(\n count,\n 0,\n \"deleteRows() with no condition should clear the table\",\n );\n },\n\n // ── Raw (non-JSON) output ──────────────────────────────────────────────────\n\n async function getRawRow() {\n await _testTable.insertRow({ id: 1, name: \"Alice\", score: 42 });\n const row = await _testTable.getRow({ id: 1 });\n assert(row !== undefined, \"raw getRow should return a result\");\n // raw rows are objects with column keys (newer sqlite lib)\n assert(\"id\" in row, \"raw row should have 'id' key\");\n },\n\n async function getRawRows() {\n const result = await _testTable.getRows();\n // raw output is the ResultSet object, not a plain array\n assert(result !== undefined, \"raw getRows should return a result\");\n assert(\n \"rows\" in result,\n \"raw getRows result should have a 'rows' property\",\n );\n assert(\n \"columns\" in result,\n \"raw getRows result should have a 'columns' property\",\n );\n },\n\n // ── getRow miss ────────────────────────────────────────────────────────────\n\n async function getRowMiss() {\n const row = await _testTable.getRow({ id: 9999 }, { json: true });\n assertEqual(row, undefined, \"getRow with no match should return undefined\");\n },\n\n // ── Condition operators ────────────────────────────────────────────────────\n\n async function conditionOperators() {\n // seed a few more rows for operator tests\n await _testTable.insertRow({ id: 2, name: \"Bob\", score: 99 });\n await _testTable.insertRow({ id: 3, name: \"Charlie\", score: 7 });\n await _testTable.insertRow({ id: 4, name: \"Dave\", score: 55 });\n await _testTable.insertRow({ id: 5, name: \"Eve\", score: 60 });\n\n // atLeast (>=)\n const gte = await _testTable.countRows({ score: { atLeast: 55 } });\n assertEqual(gte, 3, \"atLeast 55: expected 3 rows\");\n\n // lessThan\n const lt = await _testTable.countRows({ score: { lessThan: 55 } });\n assertEqual(lt, 2, \"lessThan 55: expected 2 rows\");\n\n // atMost (<=)\n const lte = await _testTable.countRows({ score: { atMost: 55 } });\n assertEqual(lte, 3, \"atMost 55: expected 3 rows\");\n\n // notEqual\n const ne = await _testTable.countRows({ score: { notEqual: 42 } });\n assertEqual(ne, 4, \"notEqual 42: expected 4 rows\");\n\n // like\n const like = await _testTable.getRows({ name: { like: \"A%\" } }, {\n json: true,\n });\n assertEqual(like.length, 1, \"LIKE 'A%': expected 1 row\");\n assertEqual(like[0].name, \"Alice\", \"LIKE 'A%': expected Alice\");\n\n // in\n const inList = await _testTable.getRows({ id: { in: [1, 3] } }, {\n json: true,\n });\n assertEqual(inList.length, 2, \"IN [1,3]: expected 2 rows\");\n\n // in with empty array (always-false)\n const inEmpty = await _testTable.countRows({ id: { in: [] } });\n assertEqual(inEmpty, 0, \"IN []: expected 0 rows\");\n },\n\n async function conditionNullEquality() {\n // insert a row with a null score\n await _testTable.insertRow({ id: 10, name: \"Null Score\" });\n const nullRows = await _testTable.getRows({ score: null }, { json: true });\n assertEqual(nullRows.length, 1, \"IS NULL: expected 1 row with null score\");\n assertEqual(nullRows[0].id, 10, \"IS NULL: expected row id=10\");\n },\n\n async function conditionStringFreeform() {\n const rows = await _testTable.getRows(\"score > 50\", { json: true });\n assert(rows.length > 0, \"freeform string condition should return results\");\n assert(\n rows.every((r: any) => r.score > 50),\n \"all rows should satisfy score > 50\",\n );\n },\n\n // ── getRow edge cases ──────────────────────────────────────────────────────\n\n async function getRowNoCondition() {\n // no condition — should return the first row without throwing\n const row = await _testTable.getRow(undefined, { json: true });\n assert(row !== undefined, \"getRow with no condition should return a row\");\n assert(\"id\" in row, \"returned row should have an 'id' field\");\n },\n\n async function getRowColumns() {\n const row = await _testTable.getRow({ id: 1 }, {\n json: true,\n columns: [\"id\", \"name\"],\n });\n assert(row !== undefined, \"getRow with columns should return a row\");\n assert(\"id\" in row, \"row should have 'id'\");\n assert(\"name\" in row, \"row should have 'name'\");\n assert(\n !(\"score\" in row),\n \"row should not have 'score' when excluded via columns\",\n );\n },\n\n // ── getRows edge cases ─────────────────────────────────────────────────────\n\n async function getRowsEmptyTable() {\n // temporarily clear and verify empty json output\n await _testTable.deleteRows();\n const rows = await _testTable.getRows(undefined, { json: true });\n assertEqual(rows, [], \"getRows on empty table should return []\");\n const raw = await _testTable.getRows();\n assert(\n raw.rows.length === 0,\n \"raw getRows on empty table should have 0 rows\",\n );\n // re-seed for subsequent tests\n await _testTable.insertRow({ id: 1, name: \"Alice\", score: 42 });\n await _testTable.insertRow({ id: 2, name: \"Bob\", score: 99 });\n await _testTable.insertRow({ id: 3, name: \"Charlie\", score: 7 });\n await _testTable.insertRow({ id: 4, name: \"Dave\", score: 55 });\n await _testTable.insertRow({ id: 5, name: \"Eve\", score: 60 });\n },\n\n async function getRowsLimitOnly() {\n const rows = await _testTable.getRows(undefined, { json: true, limit: 2 });\n assertEqual(rows.length, 2, \"limit alone should cap results to 2 rows\");\n },\n\n async function getRowsColumnsWithOrdering() {\n const rows = await _testTable.getRows(undefined, {\n json: true,\n ascendBy: \"score\",\n columns: [\"id\", \"score\"],\n });\n assert(rows.length > 1, \"should return multiple rows\");\n assert(!(\"name\" in rows[0]), \"name should be excluded via columns\");\n assert(\"score\" in rows[0], \"score should be present\");\n for (let i = 1; i < rows.length; i++) {\n assert(\n rows[i].score >= rows[i - 1].score,\n \"rows should be in ascending order by score\",\n );\n }\n },\n\n async function getRowsFreeformConditionWithOrdering() {\n const rows = await _testTable.getRows(\"score > 10\", {\n json: true,\n descendBy: \"score\",\n });\n assert(\n rows.every((r: any) => r.score > 10),\n \"all rows should satisfy score > 10\",\n );\n for (let i = 1; i < rows.length; i++) {\n assert(\n rows[i].score <= rows[i - 1].score,\n \"rows should be in descending order by score\",\n );\n }\n },\n\n async function getRowsAscendBy() {\n const rows = await _testTable.getRows(undefined, {\n json: true,\n ascendBy: \"score\",\n });\n assert(\n Array.isArray(rows) && rows.length > 1,\n \"ascendBy should return multiple rows\",\n );\n for (let i = 1; i < rows.length; i++) {\n assert(\n rows[i].score >= rows[i - 1].score,\n `row ${i} score (${rows[i].score}) should be >= row ${i - 1} score (${\n rows[i - 1].score\n })`,\n );\n }\n },\n\n async function getRowsDescendBy() {\n const rows = await _testTable.getRows(undefined, {\n json: true,\n descendBy: \"score\",\n });\n assert(\n Array.isArray(rows) && rows.length > 1,\n \"descendBy should return multiple rows\",\n );\n for (let i = 1; i < rows.length; i++) {\n assert(\n rows[i].score <= rows[i - 1].score,\n `row ${i} score (${rows[i].score}) should be <= row ${i - 1} score (${\n rows[i - 1].score\n })`,\n );\n }\n },\n\n async function getRowsAscendByWithLimit() {\n const rows = await _testTable.getRows(undefined, {\n json: true,\n ascendBy: \"score\",\n limit: 2,\n });\n assertEqual(rows.length, 2, \"ascendBy with limit 2 should return 2 rows\");\n assert(rows[0].score <= rows[1].score, \"rows should be in ascending order\");\n },\n\n async function getRowsDescendByWithCondition() {\n const rows = await _testTable.getRows(\n { score: { greaterThan: 10 } },\n { json: true, descendBy: \"score\" },\n );\n assert(\n rows.every((r: any) => r.score > 10),\n \"all rows should satisfy score > 10\",\n );\n for (let i = 1; i < rows.length; i++) {\n assert(\n rows[i].score <= rows[i - 1].score,\n \"rows should be in descending order by score\",\n );\n }\n },\n\n // ── addColumn / removeColumn ───────────────────────────────────────────────\n\n async function addColumn() {\n await _testTable.addColumn(\"tag\", { type: \"text\" });\n const info = await _testTable.getInfo();\n const colNames = info.columns.map((c: any) => c.name);\n assert(\n colNames.includes(\"tag\"),\n \"new 'tag' column should appear in getInfo\",\n );\n },\n\n async function removeColumn() {\n await _testTable.removeColumn(\"tag\");\n const info = await _testTable.getInfo();\n const colNames = info.columns.map((c: any) => c.name);\n assert(\n !colNames.includes(\"tag\"),\n \"'tag' column should be gone after removeColumn\",\n );\n },\n\n // ── streamRowBatches ───────────────────────────────────────────────────────\n\n async function streamRowBatches() {\n const batches: any[][] = [];\n await _testTable.streamRowBatches((rows: any[]) => {\n batches.push(rows);\n });\n const totalStreamed = batches.reduce((sum, b) => sum + b.length, 0);\n const count = await _testTable.countRows();\n assertEqual(\n totalStreamed,\n count,\n \"streamed row count should match total rows\",\n );\n },\n\n async function streamRowBatchesStopAt() {\n // With batchSize=1000 and only a handful of rows, stopAt=1 should yield exactly 1 batch\n await _testTable.streamRowBatches(\n (_rows: any[]) => {},\n { stopAt: 1 },\n );\n // No error thrown = pass; just verify it runs without issue\n },\n\n // ── String-form schema ─────────────────────────────────────────────────────\n\n async function stringSchemaTableExists() {\n const result = await _testTableStr.exists();\n assert(!!result, \"string-schema table should exist after create()\");\n },\n\n async function stringSchemaCorrectColumns() {\n const info = await _testTableStr.getInfo();\n const colNames = info.columns.map((c: any) => c.name);\n assert(\n colNames.includes(\"id\"),\n \"string-schema table should have 'id' column\",\n );\n assert(\n colNames.includes(\"label\"),\n \"string-schema table should have 'label' column\",\n );\n assert(\n colNames.includes(\"value\"),\n \"string-schema table should have 'value' column\",\n );\n },\n\n async function stringSchemaInsertAndGetRow() {\n await _testTableStr.insertRow({ id: 1, label: \"alpha\", value: 1.5 });\n const row = await _testTableStr.getRow({ id: 1 }, { json: true });\n assertEqual(\n row,\n { id: 1, label: \"alpha\", value: 1.5 },\n \"string-schema row mismatch\",\n );\n },\n\n async function stringSchemaNotNullEnforced() {\n // 'label' is defined as \"text not null\" — omitting it should throw\n let threw = false;\n try {\n await _testTableStr.insertRow({ id: 2, value: 3.14 });\n } catch (_err) {\n threw = true;\n }\n assert(threw, \"inserting a row without a NOT NULL column should throw\");\n },\n\n async function stringSchemaPrimaryKeyEnforced() {\n // inserting a duplicate primary key should throw\n let threw = false;\n try {\n await _testTableStr.insertRow({ id: 1, label: \"duplicate\", value: 0 });\n } catch (_err) {\n threw = true;\n }\n assert(threw, \"string-schema: duplicate primary key should throw\");\n },\n];\n\n// ── Runner ──────────────────────────────────────────────────────────────────\n\nasync function runAllTests(): Promise {\n await setup();\n const results: TestResult[] = [];\n\n for (const test of tests) {\n results.push(await runTest(test.name, test));\n }\n\n await teardown();\n\n const passed = results.filter((r) => r.passed).length;\n const failed = results.filter((r) => !r.passed);\n\n console.log(`\\n=== Test Results: ${passed}/${results.length} passed ===\\n`);\n for (const r of results) {\n const icon = r.passed ? \"✅\" : \"❌\";\n console.log(`${icon} ${r.name}`);\n }\n\n if (failed.length > 0) {\n console.log(`\\n=== ${failed.length} test(s) failed ===\\n`);\n for (const r of failed) {\n console.log(`❌ ${r.name}\\n ${r.error}\\n`);\n }\n throw new Error(`${failed.length} test(s) failed`);\n }\n}\n\nawait runAllTests();",[145],"259330f4-166e-11f1-b835-42dde27851f2","main.tsx","294acb64-27b6-11f0-aecd-569c3dd06744",415,["D",1772479775766],307,"import valSQLite, {\n sqlite as userSQLite,\n} from \"https://esm.town/v/std/sqlite/main.ts?v=14\";\n\nimport {\n AISuggestCmd,\n assembleBulkInsertCommand,\n assembleBulkUpsertCommand,\n http,\n RowSelectCondition,\n SelectOutputOptions,\n SQLiteTableInfo,\n StreamOptions,\n streamTableRows,\n} from \"./utils\";\n\ntype ColumnDefinition = string | {\n type?: \"integer\" | \"number\" | \"string\" | \"text\";\n primaryKey?: boolean;\n unique?: boolean;\n};\n\ntype TableScheme = { [key: string]: ColumnDefinition };\n\nexport class SQLiteTable {\n constructor(\n tableName: string,\n schema: TableScheme,\n options?: { scope?: \"user\" | \"val\" },\n ) {\n this.name = tableName;\n this.schema = schema;\n this.scope = options?.scope || \"user\";\n this.sqlite = this.scope === \"val\" ? valSQLite : userSQLite;\n return this;\n }\n\n name: string = \"newTable\";\n schema: TableScheme;\n scope: \"user\" | \"val\";\n sqlite: typeof userSQLite;\n\n async create() {\n const { name, schema } = this;\n await this.sqlite.execute(`create table if not exists ${name}(\n ${parseColumnDefinition(schema)}\n )`);\n }\n\n async drop() {\n const { name } = this;\n await this.sqlite.execute(`drop table ${name}`);\n }\n\n async exists() {\n const { name } = this;\n const table = await this.sqlite.execute(\n `SELECT * FROM sqlite_master WHERE type='table' AND name='${name}';`,\n );\n const { rows: [result] } = table;\n return result;\n }\n\n async getInfo() {\n return await SQLiteTableInfo.call(this);\n }\n\n async insertRow(rowData: object) {\n const { name } = this;\n const keys = Object.keys(rowData);\n const sql = `insert into ${name}(${keys.join(\", \")}) values (${\n keys.map((k) => `:${k}`).join(\", \")\n })`;\n return await this.sqlite.execute({\n sql,\n args: rowData,\n });\n }\n\n async updateRow(conditions: RowSelectCondition, rowUpdate: object) {\n const { name } = this;\n const rowUpdateString = stringifyEqualities(rowUpdate)\n .join(\", \\n\");\n const fullString = `UPDATE ${name} SET ${rowUpdateString}${\n formatConditions(conditions)\n }`;\n await this.sqlite.execute(fullString);\n }\n\n async batchInsert(records: object[]) {\n const { name } = this;\n const fullString = assembleBulkInsertCommand(name, records);\n return await this.sqlite.execute(fullString);\n }\n\n async batchUpsert(records: object[], conflictKey: string) {\n const { name } = this;\n const fullString = assembleBulkUpsertCommand(name, records, conflictKey);\n return await this.sqlite.execute(fullString);\n }\n\n // get first matching row\n async getRow(\n conditions?: RowSelectCondition,\n outputOptions?: Pick,\n ) {\n return await this._selectQuery({\n conditions,\n outputOptions,\n single: true,\n });\n }\n\n // get all rows meeting a certain condition. option to return as an array of json objects\n async getRows(\n conditions?: RowSelectCondition,\n outputOptions?: SelectOutputOptions,\n ) {\n return await this._selectQuery({\n conditions,\n outputOptions,\n single: false,\n });\n }\n\n // count rows meeting a condition\n async countRows(conditions?: RowSelectCondition) {\n const { name } = this;\n const { rows: [firstRow] } = await this.sqlite.execute(\n `select count(*) from ${name}${formatConditions(conditions)}`,\n );\n // rows may be objects (newer sqlite) or arrays (older sqlite)\n return Array.isArray(firstRow) ? firstRow[0] : Object.values(firstRow)[0];\n }\n\n // delete all rows meeting a condition\n async deleteRows(conditions?: RowSelectCondition) {\n const { name } = this;\n return await this.sqlite.execute(\n `delete from ${name}${formatConditions(conditions)}`,\n );\n }\n\n async streamRowBatches(onBatch: Function, options?: Pick) {\n const { name } = this;\n return await streamTableRows.call(this, onBatch, {\n tableName: name,\n stopAt: options?.stopAt || Infinity,\n batchSize: 1000,\n });\n }\n\n /**\n * Add a new column to the table.\n * @param columnName name of the new column\n * @param columnScheme type and constraints for the new column\n */\n async addColumn(columnName: string, columnScheme: ColumnDefinition) {\n const { name } = this;\n // parseColumnDefinition will return e.g. \"colName TEXT UNIQUE\"\n const colDef = parseColumnDefinition({ [columnName]: columnScheme });\n await this.sqlite.execute(`ALTER TABLE ${name} ADD COLUMN ${colDef}`);\n }\n\n /**\n * Remove a column from the table.\n * Requires SQLite 3.35.0 or later for native DROP COLUMN support.\n * @param columnName name of the column to drop\n */\n async removeColumn(columnName: string) {\n const { name } = this;\n // SQLite 3.35+ supports: ALTER TABLE ... DROP COLUMN ...\n await this.sqlite.execute(`ALTER TABLE ${name} DROP COLUMN ${columnName}`);\n }\n\n /**\n * Get all indices for the table.\n */\n async getIndices() {\n const result = await this.sqlite.execute(\n `SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = '${this.name}'`,\n );\n return formatJSONOutput(result);\n }\n\n /**\n * Add an index to the table.\n * @param name name of the index\n * @param column column to index\n */\n async addIndex({ name, column }: { name: string; column: string }) {\n await this.sqlite.execute(\n `CREATE INDEX ${name} ON ${this.name} (${column})`,\n );\n }\n\n async AISuggestCmd(plainText: string): Promise {\n const { name } = this;\n return await AISuggestCmd(plainText, name);\n }\n\n async http(req: Request): Promise {\n const bound = http.bind(this);\n return await bound(req);\n }\n\n private async _selectQuery({\n conditions,\n outputOptions = {},\n single = false,\n }: {\n conditions?: RowSelectCondition;\n outputOptions?: SelectOutputOptions;\n single?: boolean;\n }) {\n const { name } = this;\n\n const columnL = outputOptions.columns?.length\n ? outputOptions.columns.join(\", \")\n : \"*\";\n\n let query = `select ${columnL} from ${name}${formatConditions(conditions)}`;\n\n if (single) {\n query += ` LIMIT 1`;\n } else {\n if (outputOptions.ascendBy) {\n query += ` ORDER BY ${outputOptions.ascendBy} ASC`;\n } else if (outputOptions.descendBy) {\n query += ` ORDER BY ${outputOptions.descendBy} DESC`;\n }\n\n if (outputOptions.limit != null) {\n query += ` LIMIT ${outputOptions.limit}`;\n }\n }\n\n const rawOutput = await this.sqlite.execute(query);\n\n // Non-JSON output\n if (!outputOptions.json) {\n return single ? (rawOutput.rows?.[0] ?? undefined) : rawOutput;\n }\n\n // JSON output\n const jsonRows = formatJSONOutput(rawOutput);\n return single ? (jsonRows[0] ?? undefined) : jsonRows;\n }\n}\n\nfunction formatJSONOutput(rawOutput) {\n if (!rawOutput) return [];\n const { rows, columns } = rawOutput;\n if (!rows?.length) return [];\n const json = rows.map((row) => {\n // rows may already be objects (newer sqlite lib versions)\n if (!Array.isArray(row)) return row;\n return Object.fromEntries(\n row.map((rowVal, rowValIndex) => [columns[rowValIndex], rowVal]),\n );\n });\n\n return json;\n}\n\nfunction parseColumnDefinition(scheme: TableScheme): string {\n return Object.entries(scheme)\n .map(([columnName, columnScheme]) => {\n // If user passed a raw string, trust it as-is\n if (typeof columnScheme === \"string\") {\n return `${columnName} ${columnScheme}`;\n }\n\n const parts: string[] = [];\n const type = columnScheme.type ?? \"text\";\n parts.push(type.toUpperCase());\n\n if (columnScheme.primaryKey) parts.push(\"PRIMARY KEY\");\n if (columnScheme.unique) parts.push(\"UNIQUE\");\n\n return `${columnName} ${parts.join(\" \")}`;\n })\n .join(\",\\n\");\n}\n\nfunction stringifyEqualities(obj: Object): string[] {\n return Object.entries(obj).map(([key, value]) => {\n // Primitive: default to equality\n if (\n typeof value === \"string\" ||\n typeof value === \"number\" ||\n typeof value === \"boolean\" ||\n value === null\n ) {\n const keyValue = [key, formatValue(value)];\n return keyValue.join(value === null ? \" IS \" : \" = \");\n }\n\n // Operator object\n if (typeof value === \"object\" && value !== null) {\n const clauses = Object.entries(value).map(([op, val]) => {\n const formatted = formatValue(val);\n switch (op) {\n case \"greaterThan\":\n return `${key} > ${formatted}`;\n case \"atLeast\":\n return `${key} >= ${formatted}`;\n case \"lessThan\":\n return `${key} < ${formatted}`;\n case \"atMost\":\n return `${key} <= ${formatted}`;\n case \"notEqual\":\n return `${key} != ${formatted}`;\n case \"like\":\n return `${key} LIKE ${formatted}`;\n case \"in\": {\n if (!Array.isArray(val)) {\n throw new Error(`'in' expects an array for key \"${key}\"`);\n }\n if (val.length === 0) return \"0\"; // IN () is invalid; treat as always-false\n const list = val.map(formatValue).join(\", \");\n return `${key} IN (${list})`;\n }\n\n default:\n throw new Error(`Unsupported operator: ${op}`);\n }\n });\n return clauses.join(\" AND \");\n }\n\n throw new Error(`Invalid condition for key \"${key}\": ${value}`);\n });\n}\n\nfunction formatValue(value: any): string {\n if (typeof value === \"string\") return `'${sanitizeString(value)}'`;\n if (value === null) return \"NULL\";\n return String(value);\n}\n\nfunction formatConditions(conditions?: RowSelectCondition): string {\n if (!conditions) return \"\";\n if (typeof conditions === \"string\") return ` WHERE ${conditions}`;\n\n const conditionsString = stringifyEqualities(conditions)\n .join(\" AND \");\n return ` WHERE ${conditionsString}`;\n}\n\n// Escape single quotes by doubling them\n\nfunction sanitizeString(string: string): string {\n return string.replace(/'/g, \"''\");\n}\n",[153],"aec51b48-0f94-11f1-8531-42dde27851f2","httpDemo.ts","281b626e-0d0a-11f1-a3a9-42dde27851f2",359,["D",1771726668666],7,"http","import { SQLiteTable } from \"./main.tsx\";\n\nconst table = new SQLiteTable(\"selftable\", { scope: \"val\" });\n\n// Learn more: https://docs.val.town/vals/http/\nexport default async function (req: Request): Promise {\n return await table.http(req);\n}",[161],"1ea98626-166e-11f1-a145-42dde27851f2","README.md","294ad15e-27b6-11f0-aecd-569c3dd06744",413,["D",1772479764165],28,"# SQLiteTable\n\nA thin abstraction over SQLite, to work with tables via JSON. All methods async.\n\n## Usage\n\n```javascript\nconst table = new SQLiteTable(\"myTable\", {\n id: \"unique primary key\",\n title: \"text\",\n body: \"text\",\n created: \"integer\",\n});\n\nawait table.create();\n```\n\n---\n\n# API\n\n## Basics\n\n#### `new SQLiteTable(name: string, schema: TableScheme, options?)`\n\nStart an instance of SQLiteTable. `schema` is a required object with column\nnames as keys and definitions as values — it is stored on the instance and used\nby `.create()`. See [TableScheme](#tablescheme) below for how to define columns.\n\n`options.scope` controls which SQLite database the table lives in:\n\n| Value | Database | Use when |\n| ------------------ | --------------------------- | --------------------------------------------------------------- |\n| `\"user\"` (default) | Shared across all your vals | Data should persist and be accessible from multiple vals |\n| `\"val\"` | Scoped to this val only | Data is private to this val, or you want isolation between vals |\n\n```javascript\n// User-scoped (default) — table is shared across your account\nconst table = new SQLiteTable(\"posts\", {\n id: \"unique primary key\",\n title: \"text\",\n});\n\n// Val-scoped — table is isolated to this val\nconst table = new SQLiteTable(\"posts\", { id: \"unique primary key\" }, {\n scope: \"val\",\n});\n```\n\n#### `.create()`\n\nCreate this table in your VT SQLite storage using the schema passed to the\nconstructor. Run this only once per table.\n\n#### `.drop()`\n\nDrop this table from your VT SQLite storage.\n\n#### `.exists()`\n\nReturns whether this table exists in storage.\n\n---\n\n## Write operations\n\n### `.insertRow(rowData)`\n\nAdd a new row to your table. `rowData` is a JSON with column names as keys, and\nrow values as values.\n\n```javascript\ntable.insertRow({\n id: 1234,\n title: \"Post title\",\n body: \"Post body\",\n created: 12345678,\n});\n```\n\n### `.deleteRows(conditions?)`\n\nDeletes all rows in the table that match the condition.\n\n### `.updateRow(conditions?, rowData)`\n\nModifies all rows in the table that match the condition. `rowData` is a JSON of\nkey-value pairs to change; it does not affect any columns not mentioned.\n\n```javascript\ntable.updateRow(\n { id: 1234 },\n {\n title:\n \"New title! The existing body text is not mentioned here so it is unaffected\",\n },\n);\n```\n\n---\n\n## Read operations\n\n### `.getInfo()`\n\nReturns metadata about the table:\n\n- `columns`: array of `{ name, type }` for each column\n- `rowCount`: total number of rows\n- `pageSize`, `pageCount`: SQLite page-level storage info\n- `estimatedTableBytes`, `humanReadableSize`: estimated table size on disk\n\n```javascript\nconst info = await table.getInfo();\n// {\n// columns: [{ name: \"id\", type: \"INTEGER\" }, { name: \"title\", type: \"TEXT\" }],\n// rowCount: 42,\n// humanReadableSize: \"4 KB\",\n// ...\n// }\n```\n\n### `.countRows(conditions?)`\n\nReturns a count of all rows in the table that match the condition.\n\nconditions: Either a JSON object of column conditions, or a raw SQL WHERE clause\nas a string. See [Conditions](#conditions) below.\n\n### `.getRow(conditions?, options?)`\n\nReturns the first row matching the given conditions.\n\n`options` is an optional [`SelectOutputOptionsBase`](#selectoutputoptionsbase) object.\n\n```javascript\ntable.getRow({ id: 1234 }, { json: true, columns: [\"id\", \"title\"] });\n```\n\n### `.getRows(conditions?, options?)`\n\nReturns all rows matching the given conditions.\n\n`options` is an optional [`SelectOutputOptions`](#selectoutputoptions) object, which extends `SelectOutputOptionsBase` with ordering.\n\n```javascript\n// Basic fetch\ntable.getRows({ status: \"active\" }, { json: true });\n\n// Sorted descending, limited\ntable.getRows({ status: \"active\" }, { json: true, descendBy: \"created\", limit: 10 });\n\n// Ascending, specific columns only\ntable.getRows(undefined, { json: true, ascendBy: \"score\", columns: [\"id\", \"score\"] });\n```\n\n---\n\n## Output options\n\n### `SelectOutputOptionsBase`\n\nShared options accepted by both `.getRow()` and `.getRows()`:\n\n| Field | Type | Default | Description |\n| --------- | ---------- | ------- | -------------------------------------------------------- |\n| `json` | `boolean` | `false` | Return plain JS objects instead of raw DB output |\n| `columns` | `string[]` | — | Limit which columns are returned (defaults to all) |\n\n### `SelectOutputOptions`\n\nExtends `SelectOutputOptionsBase` with ordering options, accepted by `.getRows()`. `ascendBy` and `descendBy` are mutually exclusive.\n\n| Field | Type | Default | Description |\n| ----------- | -------- | ------- | ---------------------------------------------- |\n| `ascendBy` | `string` | — | Column name to sort by, ascending (`ASC`) |\n| `descendBy` | `string` | — | Column name to sort by, descending (`DESC`) |\n| `limit` | `number` | — | Maximum number of rows to return |\n\n### `.streamRowBatches(onBatch: Function)`\n\nIterate over all rows in batches of 1000, passing each batch to onBatch.\n\n```javascript\nawait table.streamRowBatches((batch) => {\n for (const row of batch) {\n console.log(row);\n }\n});\n```\n\n---\n\n## Indices\n\n### `.getIndices()`\n\nReturns all indices on the table as an array of `{ name, sql }` objects.\n\n```javascript\nconst indices = await table.getIndices();\n// [{ name: \"idx_title\", sql: \"CREATE INDEX idx_title ON posts (title)\" }]\n```\n\n### `.addIndex({ name, column })`\n\nCreates an index on a single column. Note: there is no `IF NOT EXISTS` guard —\ncalling this twice with the same `name` will throw.\n\n```javascript\nawait table.addIndex({ name: \"idx_title\", column: \"title\" });\n```\n\n---\n\n## Web access\n\n### `.http(req: Request): Promise`\n\nExposes read operations (`getRow`, `getRows`, `countRows`, `getInfo`) as an HTTP\nhandler, so your table can be queried directly over the network. Useful for\nbuilding lightweight read APIs on top of your SQLite data without writing custom\nrouting logic.\n\nPass it as the handler in an HTTP val, bound to the table instance:\n\n```typescript\nimport { SQLiteTable } from \"https://esm.town/v/peterqliu/SQLiteTable/main.tsx\";\n\nconst table = new SQLiteTable(\"posts\", {\n id: \"unique primary key\",\n title: \"text\",\n});\n\nexport default (req: Request) => table.http(req);\n```\n\nThen query it via URL. The last path segment selects the operation, and query\nparams become row conditions:\n\n```\nGET /getRows?title=Hello\nGET /getRow?id=1234\nGET /countRows?created=12345678\n```\n\nUse `_columns` to limit which columns are returned:\n\n```\nGET /getRows?_columns=id,title\n```\n\n**Note:** Operators in object form are not available when using `.http()`. HTTP\nquery params only support simple equality conditions.\n\nUtilities\n\n### `.AISuggestCmd(plainText: string): Promise`\n\nUse AI to generate a suggested SQLite command for a given text description of\nyour intent.\n\n```javascript\nconst cmd = await table.AISuggestCmd(\n \"show me all active users sorted by join date\",\n);\n```\n\n---\n\n## TableScheme\n\nA `TableScheme` is a plain object where each key is a column name and each value\nis a column definition. Definitions can be provided in two forms:\n\n### String form\n\nPass a raw SQLite column definition string. The value is used verbatim, so you\nhave full control over type and constraints.\n\n```javascript\n{\n id: \"integer primary key\",\n title: \"text not null\",\n score: \"real default 0\",\n}\n```\n\n### Object form\n\nPass an object with the following optional fields:\n\n| Field | Type | Description |\n| ------------ | --------------------------------------------------- | ---------------------------------- |\n| `type` | `\"text\"` \\| `\"integer\"` \\| `\"number\"` \\| `\"string\"` | Column type (defaults to `\"text\"`) |\n| `primaryKey` | `boolean` | Adds `PRIMARY KEY` constraint |\n| `unique` | `boolean` | Adds `UNIQUE` constraint |\n\n```javascript\n{\n id: { type: \"integer\", primaryKey: true },\n email: { type: \"text\", unique: true },\n score: { type: \"number\" },\n bio: {}, // defaults to TEXT, no constraints\n}\n```\n\nBoth forms can be mixed freely in the same schema:\n\n```javascript\nconst table = new SQLiteTable(\"users\", {\n id: { type: \"integer\", primaryKey: true },\n email: { type: \"text\", unique: true },\n created: \"integer not null\",\n});\n```\n\n---\n\n## Conditions\n\nSome methods use a `conditions` argument to filter which rows are matched.\nConditions can be provided in two forms:\n\n### Object form (recommended)\n\nPass a plain JS object where each key is a column name. Multiple keys are joined\nwith `AND`.\n\n**Equality** (primitive value): matches rows where the column equals the value.\nA `null` value uses `IS NULL`.\n\n```javascript\ntable.getRows({ status: \"active\", score: 42 });\n// WHERE status = 'active' AND score = 42\n\ntable.getRows({ deleted_at: null });\n// WHERE deleted_at IS NULL\n```\n\n**Operator object**: for non-equality comparisons, set the value to an object\nwith one or more of the following operators. Multiple operators on the same key\nare also joined with `AND`.\n\n| Operator | SQL equivalent | Example |\n| ------------- | -------------- | ------------------------------------ |\n| `greaterThan` | `>` | `{ score: { greaterThan: 10 } }` |\n| `atLeast` | `>=` | `{ score: { atLeast: 10 } }` |\n| `lessThan` | `<` | `{ score: { lessThan: 100 } }` |\n| `atMost` | `<=` | `{ score: { atMost: 100 } }` |\n| `notEqual` | `!=` | `{ status: { notEqual: \"banned\" } }` |\n| `like` | `LIKE` | `{ title: { like: \"%hello%\" } }` |\n| `in` | `IN (...)` | `{ id: { in: [1, 2, 3] } }` |\n\n```javascript\n// Rows where score is between 10 and 100 (inclusive), and status is not \"banned\"\ntable.getRows({\n score: { atLeast: 10, atMost: 100 },\n status: { notEqual: \"banned\" },\n});\n\n// Rows where title contains \"hello\" (case-insensitive depends on collation)\ntable.getRows({ title: { like: \"%hello%\" } });\n\n// Rows where id is one of a set of values\ntable.getRows({ id: { in: [1, 2, 3] } });\n```\n\n### Raw SQL form\n\nPass a string to use a raw SQL `WHERE` clause directly. This gives you full\nSQLite expressiveness, but you are responsible for safe formatting.\n\n```javascript\ntable.getRows(\"created > 1700000000 OR pinned = 1\");\n```",[170],"173e166a-1752-11f1-b042-42dde27851f2",["D",1772577676981],[27],"pulls","branches","forks","envVars",{"_187":68,"_189":68},"connections",{"_187":68,"_188":68},"count","unfilledCount","emptyCount",{"_40":177,"_31":27,"_29":30,"_135":-5,"_38":39,"_37":14,"_137":46,"_35":212,"_13":68,"_32":20,"_33":34,"_23":24,"_142":-5,"_197":213,"_26":214},{"_40":169,"_31":170,"_29":171,"_135":-5,"_38":39,"_37":172,"_137":24,"_35":209,"_13":174,"_32":20,"_33":175,"_23":24,"_142":-5,"_197":210,"_26":211},{"_40":160,"_31":161,"_29":162,"_135":-5,"_38":39,"_37":163,"_137":46,"_35":206,"_13":165,"_32":166,"_33":167,"_23":24,"_142":-5,"_197":207,"_26":208},{"_40":152,"_31":153,"_29":154,"_135":-5,"_38":39,"_37":155,"_137":24,"_35":203,"_13":157,"_32":140,"_33":158,"_23":24,"_142":-5,"_197":204,"_26":205},{"_40":144,"_31":145,"_29":146,"_135":-5,"_38":39,"_37":147,"_137":24,"_35":200,"_13":149,"_32":140,"_33":150,"_23":24,"_142":-5,"_197":201,"_26":202},{"_40":132,"_31":133,"_29":134,"_135":-5,"_38":39,"_37":136,"_137":24,"_35":196,"_13":139,"_32":140,"_33":141,"_23":24,"_142":-5,"_197":198,"_26":199},["D",1772481061338],"children",[],[133],["D",1772480722388],[],[145],["D",1772479775766],[],[153],["D",1771726668666],[],[161],["D",1772479764165],[],[170],["D",1772577676981],[],[27],["D",1746232377860],["D",1789519256886],{"_96":95,"_103":99},{"_96":95,"_97":-5,"_98":99,"_18":52,"_100":101,"_102":24},{"_13":85,"_31":86,"_40":39,"_87":41,"_88":-5,"_89":-5,"_90":-5,"_91":-5,"_92":-5,"_93":42,"_94":95},"ctx",{"_78":-5,"_123":253,"_254":255,"_256":257,"_258":24,"_259":24,"_260":24,"_261":46,"_262":-5,"_263":-7,"_264":-7,"_265":68,"_266":24,"_267":24,"_268":-5,"_269":24,"_270":24,"_271":24,"_272":-5},"ENV",{"_224":225,"_226":227,"_228":86,"_229":230,"_231":232,"_233":234,"_235":236,"_237":238,"_239":240,"_241":242,"_243":244,"_245":246,"_247":248,"_249":250,"_251":252},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","RENDER_GIT_COMMIT","e87eb04e707cf64e7e532c8e8ec395f209e17f15","RENDER_GIT_BRANCH","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","CLOUDFLARE_TURNSTILE_SITE_KEY","0x4AAAAAACtaIZJH7s9rBMZ6","GOOGLE_CLIENT_ID","875989696899-jtsqkhlbdv5jrlsk8631p7ipjg3c6o62.apps.googleusercontent.com","STRIPE_PUBLISHABLE_KEY","pk_live_51N2zvWEpZTTLipIkVEwXrUG9AT0yeOOEYxszarZT1jpsZV58Ly6wlg3SltZpHN26CUalOBmUSY7adQRQiSkCZz2500qSXNkf8Y","API_URL","https://api.val.town",[],"preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","preferencesCloseBrackets","preferencesChatPanelState","preferencesDprint","preferencesDenofmt","notificationCount","flagDenols","isBusinessOrgMember","businessOrgHandle","isBusinessOrgAdmin","isHigherTownieTier","navCollapsed","currentOrgId"]

Diff · v424