diff options
| -rw-r--r-- | .gitignore | 1 | ||||
| -rw-r--r-- | package.json | 1 | ||||
| -rw-r--r-- | pnpm-lock.yaml | 10 | ||||
| -rw-r--r-- | server/db/groups.ts | 27 | ||||
| -rw-r--r-- | server/db/index.ts | 8 | ||||
| -rw-r--r-- | server/db/schema.ts | 42 | ||||
| -rw-r--r-- | server/index.ts | 1 |
7 files changed, 90 insertions, 0 deletions
@@ -38,3 +38,4 @@ yarn-error.log* TODO dist .env +k-heardle.db diff --git a/package.json b/package.json index 043b809..0c02cf9 100644 --- a/package.json +++ b/package.json @@ -65,6 +65,7 @@ "devDependencies": { "@babel/core": "^7.29.7", "@babel/plugin-proposal-private-property-in-object": "^7.21.11", + "@types/better-sqlite3": "^9.6.0", "@types/cors": "^2.8.19", "@types/express": "^5.0.6", "@types/lodash": "^4.17.25", diff --git a/pnpm-lock.yaml b/pnpm-lock.yaml index 66654b2..790da68 100644 --- a/pnpm-lock.yaml +++ b/pnpm-lock.yaml @@ -93,6 +93,9 @@ importers: '@babel/plugin-proposal-private-property-in-object': specifier: ^7.21.11 version: 7.21.11(@babel/core@7.29.7(supports-color@10.2.2))(supports-color@10.2.2) + '@types/better-sqlite3': + specifier: ^9.6.0 + version: 9.6.0 '@types/cors': specifier: ^2.8.19 version: 2.8.19 @@ -609,6 +612,9 @@ packages: '@types/aria-query@5.0.4': resolution: {integrity: sha512-rfT93uj5s0PRL7EzccGMs3brplhcrghnDoV26NqKhCAS1hVo+WdNsPvE/yb6ilfr5hi2MEk6d5EWJTKdxg8jVw==} + '@types/better-sqlite3@9.6.0': + resolution: {integrity: sha512-ZEEwBSgMu7GYJOynoagg5X9JbxfL6dTJDsgViJIqh67jV44kyOr9RXfmFjLK5rzC4MWssP06t9hu/JwGDnUbCg==} + '@types/body-parser@1.19.6': resolution: {integrity: sha512-HLFeCYgz89uk22N5Qg3dvGvsv46B8GLvKKo1zKG4NybA8U2DiEO3w9lqGg29t/tfLRJpJ6iQxnVw4OnB7MoM9g==} @@ -2546,6 +2552,10 @@ snapshots: '@types/aria-query@5.0.4': {} + '@types/better-sqlite3@9.6.0': + dependencies: + '@types/node': 25.9.5 + '@types/body-parser@1.19.6': dependencies: '@types/connect': 3.4.38 diff --git a/server/db/groups.ts b/server/db/groups.ts new file mode 100644 index 0000000..1060501 --- /dev/null +++ b/server/db/groups.ts @@ -0,0 +1,27 @@ +import { db } from "./index"; +import crypto from "node:crypto"; + +export function createGroup(name: string) { + const id = crypto.randomUUID(); + const joinCode = crypto + .randomBytes(3) + .toString("hex") + .toUpperCase(); + + db.prepare(` + INSERT INTO groups + (id, name, join_code, created_at) + VALUES (?, ?, ?, ?) + `).run( + id, + name, + joinCode, + Date.now() + ); + + return { + id, + name, + joinCode + }; +} diff --git a/server/db/index.ts b/server/db/index.ts new file mode 100644 index 0000000..e8d0392 --- /dev/null +++ b/server/db/index.ts @@ -0,0 +1,8 @@ +import Database from "better-sqlite3"; +import path from "node:path"; + +const dbPath = path.join(process.cwd(), "k-heardle.db"); + +export const db = new Database(dbPath); + +db.pragma("foreign_keys = ON"); diff --git a/server/db/schema.ts b/server/db/schema.ts new file mode 100644 index 0000000..8fc2532 --- /dev/null +++ b/server/db/schema.ts @@ -0,0 +1,42 @@ +import { db } from "./index"; + +db.exec(` +CREATE TABLE IF NOT EXISTS groups ( + id TEXT PRIMARY KEY, + name TEXT NOT NULL, + join_code TEXT UNIQUE NOT NULL, + current_streak INTEGER NOT NULL DEFAULT 0, + last_completed TEXT, + created_at INTEGER NOT NULL +); + +CREATE TABLE IF NOT EXISTS group_members ( + group_id TEXT NOT NULL, + username TEXT NOT NULL, + joined_at INTEGER NOT NULL, + + PRIMARY KEY (group_id, username), + + FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE +); + +CREATE TABLE IF NOT EXISTS daily_solves ( + group_id TEXT NOT NULL, + username TEXT NOT NULL, + date TEXT NOT NULL, + solved INTEGER NOT NULL, + attempts INTEGER NOT NULL, + PRIMARY KEY (group_id, username, date), + FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE +); + +CREATE TABLE IF NOT EXISTS mv_solves ( + group_id TEXT NOT NULL, + username TEXT NOT NULL, + date TEXT NOT NULL, + solved INTEGER NOT NULL, + attempts INTEGER NOT NULL, + PRIMARY KEY (group_id, username, date), + FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE +); +`); diff --git a/server/index.ts b/server/index.ts index 5799881..b076de0 100644 --- a/server/index.ts +++ b/server/index.ts @@ -3,6 +3,7 @@ import cors from "cors"; import dotenv from "dotenv"; import { dailyRouter } from "./daily"; import { selectRouter } from "./select"; +import "./db/schema" dotenv.config(); |