[{"_1":2,"_179":-5,"_180":-5},"loaderData",{"_3":4,"_93":94},"root",{"_5":6,"_36":37,"_66":67},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":-5,"_32":33,"_34":20,"_35":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","preferencesChatPanelState","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_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":65},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","9d6c1783bea8575660da505e921aa115b9856783","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",{"_68":69},"__internal_clerk_state",{"_70":71,"_72":47,"_73":74,"_75":74,"_76":20,"_77":74,"_78":74,"_79":74,"_80":74,"_81":82,"_91":20,"_92":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",{"_83":20,"_19":20,"_84":74,"_85":86,"_87":74,"_88":47,"_89":20,"_90":74},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_95":96,"_97":98,"_99":100,"_101":102,"_157":158,"_176":177,"_178":-5},"handle","pql","name","SQLiteTable","path","utils","project",{"_103":104,"_97":98,"_105":106,"_107":-5,"_108":109,"_110":111,"_112":-5,"_113":-5,"_12":114,"_115":-5,"_116":109,"_117":-5,"_118":-5,"_119":33,"_14":120,"_121":122,"_123":124,"_125":-5,"_126":127,"_128":127,"_129":130,"_131":130,"_132":130,"_133":127,"_134":127,"_23":135,"_138":139,"_143":144,"_156":20},"id","29484b28-27b6-11f0-aecd-569c3dd06744","createdAt",["D",1746232377860],"deletedAt","legacyEnvVars",true,"privacy","public","description","imageUrl","f129b0f0-09bd-41ac-a51c-c16933d81d65","pinnedFileId","pinPreview","pinMigrated","screenshotVersion","likeCount","5cdb635f-04df-409e-bb23-93b87a60f6ca","tursoName","29484b2827b611f0aecd569c3dd06744","tursoHostname","29484b2827b611f0aecd569c3dd06744-val-town.aws-us-east-2.turso.io","pinned","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_95":96,"_136":137},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoicHJveHkiLCJzcmMiOiJodHRwczovL2ltYWdlcy5jbGVyay5kZXYvdXBsb2FkZWQvaW1nXzJrYUVVM1VyRHRhckNnNGxidGEwUUdaSFRDYSJ9","org",{"_95":96,"_140":137,"_12":114,"_141":142},"avatar","tier","pro","branch",{"_145":146,"_97":45,"_103":147,"_148":104,"_149":-5,"_150":-5,"_151":-5,"_152":-5,"_153":-5,"_154":98,"_155":96},"version",495,"29488f20-27b6-11f0-aecd-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_103":159,"_97":100,"_160":161,"_162":-5,"_163":147,"_164":165,"_166":109,"_105":167,"_145":168,"_169":170,"_171":172,"_173":20,"_174":-5,"_99":175},"529450e6-1bf6-11f1-80f3-42dde27851f2","fileId","14f88bd6-68bd-11f0-9b48-0224a6c84d84","parentId","branchId","branchVersion",493,"latest",["D",1773088018621],29,"fileType","script","content","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 | 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// Shared helper: collects all column names across records and serializes values to SQL literals.\n// Vector columns are inlined as vector('[...]') function calls rather than quoted strings.\nfunction buildColumnsAndValues(\n records: Record[],\n vectorColumns: Set = new Set(),\n): { columns: string[]; values: string } {\n if (!records || records.length === 0) {\n throw new Error(\"No records provided\");\n }\n const columns = Array.from(new Set(records.flatMap(record => Object.keys(record))));\n const values = records.map(record => {\n const rowValues = columns.map(col => {\n const value = record[col];\n if (value === undefined) return \"NULL\";\n if (vectorColumns.has(col)) return `vector('${JSON.stringify(value)}')`;\n return `'${String(value).replace(/'/g, \"''\")}'`;\n });\n return `(${rowValues.join(\", \")})`;\n }).join(\",\\n \");\n return { columns, values };\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: Record[],\n conflictKey: string,\n vectorColumns?: Set,\n) => {\n if (!conflictKey) {\n throw new Error(\"Conflict key must be specified\");\n }\n\n const { columns, values } = buildColumnsAndValues(records, vectorColumns);\n\n if (!columns.includes(conflictKey)) {\n throw new Error(`Conflict key '${conflictKey}' not found in records`);\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 = (\n tableName: string,\n records: Record[],\n vectorColumns?: Set,\n): string => {\n const { columns, values } = buildColumnsAndValues(records, vectorColumns);\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","deleted","label",[100],"permalink","https://www.val.town/x/pql/SQLiteTable/code/utils","extension","actionData","errors"]