[{"_1":2,"_169":-5,"_170":-5},"loaderData",{"_3":4,"_91":92},"root",{"_5":6,"_34":35,"_64":65},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":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","ENV",{"_36":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},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","5f86047c78fbcbbdc94436f811a86add7eaf8c7a","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",{"_66":67},"__internal_clerk_state",{"_68":69,"_70":45,"_71":72,"_73":72,"_74":20,"_75":72,"_76":72,"_77":72,"_78":72,"_79":80,"_89":20,"_90":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",{"_81":20,"_19":20,"_82":72,"_83":84,"_85":72,"_86":45,"_87":20,"_88":72},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_93":94,"_95":96,"_97":98,"_99":100,"_148":149,"_166":167,"_168":-5},"handle","yawnxyz","name","luciaValtownSqlite","path","main.tsx","project",{"_101":102,"_95":96,"_103":104,"_105":-5,"_106":107,"_108":109,"_110":-5,"_111":-5,"_12":112,"_113":-5,"_114":107,"_115":-5,"_116":117,"_14":118,"_119":120,"_121":120,"_122":123,"_124":123,"_125":123,"_126":120,"_127":120,"_23":128,"_131":132,"_134":135,"_147":20},"id","054053ce-2793-11f0-9d52-569c3dd06744","createdAt",["D",1746217285024],"deletedAt","legacyEnvVars",true,"privacy","public","description","imageUrl","a8270e74-6479-475a-8025-9b69544ee0e7","pinnedFileId","pinPreview","screenshotVersion","likeCount",1,"8319defa-6e1a-46d4-ba66-2114ffd3360b","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_93":94,"_129":130},"profileImageUrl","https://images.clerk.dev/oauth_github/img_2NnaHhpxNuH1xWRIRjQNoo16TVc.jpeg","org",{"_93":94,"_133":130},"avatar","branch",{"_136":137,"_95":43,"_101":138,"_139":102,"_140":-5,"_141":-5,"_142":-5,"_143":-5,"_144":-5,"_145":96,"_146":94},"version",5,"054091d6-2793-11f0-9d52-569c3dd06744","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_101":150,"_95":98,"_151":152,"_153":-5,"_154":138,"_155":137,"_156":107,"_103":157,"_136":158,"_159":160,"_161":162,"_163":20,"_164":-5,"_97":165},"055c264e-2793-11f0-9d52-569c3dd06744","fileId","054576ba-2793-11f0-9d52-569c3dd06744","parentId","branchId","branchVersion","latest",["D",1725398681916],4,"fileType","script","content","\nimport { sqlite } from \"https://esm.town/v/std/sqlite\";\nimport { raw, sql } from \"https://esm.town/v/pomdtr/sql\";\nimport { zip } from \"npm:lodash-es\";\nimport { generateId, Scrypt } from \"npm:lucia\";\nimport type {\n Adapter,\n DatabaseSession,\n DatabaseUser,\n RegisteredDatabaseSessionAttributes,\n RegisteredDatabaseUserAttributes,\n} from \"npm:lucia\";\n\nexport interface TableNames {\n userTable: string;\n sessionTable: string;\n}\n\nexport interface Controller {\n execute(sql: string, args: any[]): Promise;\n get(sql: string, args: any[]): Promise;\n getAll(sql: string, args: any[]): Promise;\n}\n\ninterface SessionSchema extends RegisteredDatabaseSessionAttributes {\n id: string;\n user_id: string;\n expires_at: number;\n}\n\ninterface UserSchema extends RegisteredDatabaseUserAttributes {\n id: string;\n}\n\nexport class SQLiteAdapter implements Adapter {\n private controller: Controller;\n private escapedUserTableName: string;\n private escapedSessionTableName: string;\n\n constructor(controller: Controller, tableNames: TableNames) {\n this.controller = controller;\n this.escapedSessionTableName = escapeName(tableNames.sessionTable);\n this.escapedUserTableName = escapeName(tableNames.userTable);\n }\n\n public async deleteSession(sessionId: string): Promise {\n await this.controller.execute(`DELETE FROM ${this.escapedSessionTableName} WHERE id = ?`, [\n sessionId,\n ]);\n }\n\n public async deleteUserSessions(userId: string): Promise {\n await this.controller.execute(\n `DELETE FROM ${this.escapedSessionTableName} WHERE user_id = ?`,\n [userId],\n );\n }\n\n public async getSessionAndUser(\n sessionId: string,\n ): Promise<[session: DatabaseSession | null, user: DatabaseUser | null]> {\n const [databaseSession, databaseUser] = await Promise.all([\n this.getSession(sessionId),\n this.getUserFromSessionId(sessionId),\n ]);\n return [databaseSession, databaseUser];\n }\n\n public async getUserSessions(userId: string): Promise {\n const result = await this.controller.getAll(\n `SELECT * FROM ${this.escapedSessionTableName} WHERE user_id = ?`,\n [userId],\n );\n return result.map((val) => transformIntoDatabaseSession(val));\n }\n\n public async setSession(databaseSession: DatabaseSession): Promise {\n const value: SessionSchema = {\n id: databaseSession.id,\n user_id: databaseSession.userId,\n expires_at: Math.floor(databaseSession.expiresAt.getTime() / 1000),\n ...databaseSession.attributes,\n };\n const entries = Object.entries(value).filter(([_, v]) => v !== undefined);\n const columns = entries.map(([k]) => escapeName(k));\n const placeholders = Array(columns.length).fill(\"?\");\n const values = entries.map(([_, v]) => v);\n await this.controller.execute(\n `INSERT INTO ${this.escapedSessionTableName} (${\n columns.join(\", \")\n }) VALUES (${placeholders.join(\", \")})`,\n values,\n );\n }\n\n public async updateSessionExpiration(sessionId: string, expiresAt: Date): Promise {\n await this.controller.execute(\n `UPDATE ${this.escapedSessionTableName} SET expires_at = ? WHERE id = ?`,\n [Math.floor(expiresAt.getTime() / 1000), sessionId],\n );\n }\n\n public async deleteExpiredSessions(): Promise {\n await this.controller.execute(\n `DELETE FROM ${this.escapedSessionTableName} WHERE expires_at <= ?`,\n [Math.floor(Date.now() / 1000)],\n );\n }\n\n private async getSession(sessionId: string): Promise {\n const result = await this.controller.get(\n `SELECT * FROM ${this.escapedSessionTableName} WHERE id = ?`,\n [sessionId],\n );\n if (!result) return null;\n return transformIntoDatabaseSession(result);\n }\n\n private async getUserFromSessionId(sessionId: string): Promise {\n const result = await this.controller.get(\n `SELECT ${this.escapedUserTableName}.* FROM ${this.escapedSessionTableName} INNER JOIN ${this.escapedUserTableName} ON ${this.escapedUserTableName}.id = ${this.escapedSessionTableName}.user_id WHERE ${this.escapedSessionTableName}.id = ?`,\n [sessionId],\n );\n if (!result) return null;\n return transformIntoDatabaseUser(result);\n }\n}\n\nexport class ValTownAdapter extends SQLiteAdapter {\n private userTable: string;\n private sessionTable: string;\n\n constructor(tableNames: TableNames) {\n super(new ValtownController(), tableNames);\n this.userTable = tableNames.userTable;\n this.sessionTable = tableNames.sessionTable;\n }\n\n async createTables(): Promise {\n await sqlite.batch([\n `CREATE TABLE IF NOT EXISTS ${this.userTable} (\n id TEXT NOT NULL PRIMARY KEY,\n username TEXT NOT NULL UNIQUE,\n hashed_password TEXT,\n github_id INTEGER UNIQUE,\n google_id TEXT UNIQUE\n );`,\n `CREATE TABLE IF NOT EXISTS ${this.sessionTable} (\n id TEXT NOT NULL PRIMARY KEY,\n user_id TEXT NOT NULL,\n expires_at INTEGER NOT NULL,\n FOREIGN KEY (user_id) REFERENCES ${this.userTable}(id)\n );`,\n ]);\n }\n\n async createUser({\n username,\n password,\n github_id,\n google_id\n }: {\n username: string;\n password?: string;\n github_id?: number;\n google_id?: string;\n }): Promise {\n const userId = generateId(15);\n let hashedPassword = null;\n if (password) {\n hashedPassword = await new Scrypt().hash(password);\n }\n\n await sqlite.execute({\n sql: `INSERT INTO ${this.userTable} (id, username, hashed_password, github_id, google_id) VALUES (?, ?, ?, ?, ?)`,\n args: [userId, username, hashedPassword, github_id, google_id],\n });\n\n return userId;\n }\n\n async getUser({\n username,\n github_id,\n google_id\n }: {\n username?: string;\n github_id?: number;\n google_id?: string;\n }): Promise<{ id: string; username: string; hashed_password: string | null; github_id: number | null; google_id: string | null } | null> {\n let query, args;\n if (username) {\n query = `SELECT * FROM ${this.userTable} WHERE username = ?`;\n args = [username];\n } else if (github_id) {\n query = `SELECT * FROM ${this.userTable} WHERE github_id = ?`;\n args = [github_id];\n } else if (google_id) {\n query = `SELECT * FROM ${this.userTable} WHERE google_id = ?`;\n args = [google_id];\n } else {\n throw new Error(\"Either username, github_id, or google_id must be provided\");\n }\n\n const result = await sqlite.execute({ sql: query, args });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return Object.fromEntries(zip(result.columns, result.rows[0])) as any;\n }\n\n async verifyPassword(hashed_password: string, password: string): Promise {\n return new Scrypt().verify(hashed_password, password);\n }\n}\n\nclass ValtownController implements Controller {\n constructor() {}\n\n public async execute(sql: string, args: any[]): Promise {\n await sqlite.execute({ sql, args });\n }\n\n public async get(sql: string, args: any[]): Promise {\n const result = await sqlite.execute({ sql, args });\n if (result.rows.length === 0) return null;\n return Object.fromEntries(zip(result.columns, result.rows[0])) as T;\n }\n\n public async getAll(sql: string, args: any[]): Promise {\n const result = await sqlite.execute({ sql, args });\n return result.rows.map(row => Object.fromEntries(zip(result.columns, row))) as T[];\n }\n}\n\nfunction transformIntoDatabaseSession(raw: SessionSchema): DatabaseSession {\n const { id, user_id: userId, expires_at: expiresAtUnix, ...attributes } = raw;\n return {\n userId,\n id,\n expiresAt: new Date(expiresAtUnix * 1000),\n attributes,\n };\n}\n\nfunction transformIntoDatabaseUser(raw: UserSchema): DatabaseUser {\n const { id, ...attributes } = raw;\n return {\n id,\n attributes,\n };\n}\n\nfunction escapeName(val: string): string {\n return \"`\" + val + \"`\";\n}\n\n// Helper functions\nexport async function createTables(userTable: string, sessionTable: string) {\n return sqlite.batch([\n `CREATE TABLE IF NOT EXISTS ${userTable} (\n id TEXT NOT NULL PRIMARY KEY,\n username TEXT NOT NULL UNIQUE,\n hashed_password TEXT,\n github_id INTEGER UNIQUE,\n google_id TEXT UNIQUE\n );`,\n `CREATE TABLE IF NOT EXISTS ${sessionTable} (\n id TEXT NOT NULL PRIMARY KEY,\n user_id TEXT NOT NULL,\n expires_at INTEGER NOT NULL,\n FOREIGN KEY (user_id) REFERENCES ${userTable}(id)\n );`,\n ]);\n}\n\nexport async function createUser({ userTable, sessionTable, username, password, github_id, google_id }: {\n userTable: string;\n sessionTable: string;\n username: string;\n password?: string;\n github_id?: number;\n google_id?: string;\n}) {\n const userId = generateId(15);\n let hashedPassword = null;\n if (password) {\n hashedPassword = await new Scrypt().hash(password);\n }\n\n await sqlite.execute({\n sql: `INSERT INTO ${userTable} (id, username, hashed_password, github_id, google_id) VALUES (?, ?, ?, ?, ?)`,\n args: [userId, username, hashedPassword, github_id, google_id],\n });\n\n return userId;\n}\n\nexport async function getUser(\n { userTable, username, github_id, google_id }: {\n userTable: string;\n username?: string;\n github_id?: number;\n google_id?: string;\n },\n): Promise<{ id: string; username: string; hashed_password: string | null; github_id: number | null; google_id: string | null } | null> {\n let query, args;\n if (username) {\n query = `SELECT * FROM ${userTable} WHERE username = ?`;\n args = [username];\n } else if (github_id) {\n query = `SELECT * FROM ${userTable} WHERE github_id = ?`;\n args = [github_id];\n } else if (google_id) {\n query = `SELECT * FROM ${userTable} WHERE google_id = ?`;\n args = [google_id];\n } else {\n throw new Error(\"Either username, github_id, or google_id must be provided\");\n }\n\n const result = await sqlite.execute({ sql: query, args });\n\n if (result.rows.length === 0) {\n return null;\n }\n\n return Object.fromEntries(zip(result.columns, result.rows[0])) as any;\n}\n\nexport async function verifyPassword(hashed_password: string, password: string): Promise {\n return new Scrypt().verify(hashed_password, password);\n}\n","deleted","label",[98],"permalink","https://www.val.town/x/yawnxyz/luciaValtownSqlite/code/main.tsx","extension","actionData","errors"]