summaryrefslogtreecommitdiff
path: root/src/lib
diff options
context:
space:
mode:
Diffstat (limited to 'src/lib')
-rw-r--r--src/lib/db/index.ts36
-rw-r--r--src/lib/db/schema.sql172
-rw-r--r--src/lib/server/cookie.ts47
-rw-r--r--src/lib/server/cookiebridge.ts33
-rw-r--r--src/lib/server/header.ts12
-rw-r--r--src/lib/server/setcookie.ts25
-rw-r--r--src/lib/utils.ts2
7 files changed, 315 insertions, 12 deletions
diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index 9897af8..3d46fd9 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -1,7 +1,7 @@
import Database from "bun:sqlite";
-import { getDBOffset, wordFactorial } from "@/lib/utils";
-import type { AddSense, AddWord, State } from "@/lib/types";
-import { DEFAULT_SRS } from "@/lib/services/srs";
+import { getDBOffset, wordFactorial } from "../utils";
+import type { AddSense, AddWord, State } from "../types";
+import { DEFAULT_SRS } from "../services/srs";
const PAGE_SIZE = 100;
@@ -32,11 +32,17 @@ class DatabaseHandler {
fetchCookie(coki: string) {
const query = this.db.query(
`
- SELECT * FROM cookies
- WHERE cookie = ?
+ SELECT u.id, u.name, c.expiry FROM cookies as c
+ JOIN users as u ON u.id = c.user
+ WHERE c.cookie = ?
`,
);
- const res = query.get(coki);
+ const res = query.get(coki) as {
+ id: number;
+ name: string;
+ expiry: number;
+ };
+ console.log("cokifetch", { coki, res });
return res;
}
setCookie(coki: string, user: number, expiry: number) {
@@ -577,14 +583,22 @@ class DatabaseHandler {
return { error: `${e}` };
}
}
- loginUser(name: string, creds: string) {
+ async loginUser(name: string, creds: string) {
const query = this.db.query(`
- SELECT id FROM users
- WHERE name = ? AND creds = ?
+ SELECT * FROM users
+ WHERE name = ?
`);
- const row = query.get(name, creds) as { id: number } | null;
+ const row = query.get(name) as {
+ id: number;
+ name: string;
+ creds: string;
+ } | null;
if (!row) return { error: "not found" };
- else return { ok: row.id };
+ else {
+ const ok = await Bun.password.verify(creds, row.creds);
+ if (!ok) return { error: "Wrong password" };
+ else return { ok: row.id };
+ }
}
addCat(category: string) {
const queryString = `
diff --git a/src/lib/db/schema.sql b/src/lib/db/schema.sql
new file mode 100644
index 0000000..1b678c5
--- /dev/null
+++ b/src/lib/db/schema.sql
@@ -0,0 +1,172 @@
+-- Enable foreign key support
+PRAGMA foreign_keys = ON;
+PRAGMA journal_mode = WAL;
+PRAGMA cache_size = -2000;
+PRAGMA mmap_size = 30000000000;
+
+
+CREATE TABLE IF NOT EXISTS cookies(
+ user INTEGER NOT NULL,
+ cookie TEXT NOT NULL,
+ expiry INTEGER NOT NULL,
+ PRIMARY KEY(user, cookie),
+ FOREIGN KEY (user) REFERENCES users(id) ON DELETE CASCADE
+);
+CREATE TABLE IF NOT EXISTS languages (
+ code TEXT PRIMARY KEY,
+ name TEXT NOT NULL,
+ native_name TEXT
+);
+-- type is "word" or other
+--
+
+-- an orthographic (and likely phonetic too entity)
+-- lang is 2char code ISO 6393-1 I gues
+CREATE TABLE IF NOT EXISTS expressions(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ spelling TEXT NOT NULL,
+ lang TEXT NOT NULL,
+ frequency INTEGER,
+ type TEXT NOT NULL,
+ syllables INTEGER,
+ ipa JSONB,
+ prosody JSONB,
+ confidence INTEGER NOT NULL DEFAULT 0,
+ FOREIGN KEY (lang) REFERENCES languages(code),
+ CONSTRAINT spell_unique UNIQUE (spelling, lang)
+);
+CREATE INDEX IF NOT EXISTS idx_words_spelling ON expressions(spelling);
+CREATE INDEX IF NOT EXISTS idx_words_type ON expressions(type);
+CREATE INDEX IF NOT EXISTS idx_words_lang ON expressions(lang);
+-- a semantic entity
+CREATE TABLE IF NOT EXISTS senses(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ parent_id INTEGER NOT NULL,
+ spelling TEXT NOT NULL,
+ pos TEXT,
+ etymology TEXT,
+ ipa JSONB,
+ prosody JSONB,
+ senses JSONB,
+ forms JSONB,
+ related JSONB,
+ confidence INTEGER NOT NULL DEFAULT 0,
+ FOREIGN KEY (parent_id) REFERENCES expressions(id)
+);
+CREATE INDEX IF NOT EXISTS idx_words_pos ON senses(pos);
+CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
+
+
+
+-- Categories table (for noun and verb categories)
+CREATE TABLE IF NOT EXISTS categories (
+ name TEXT PRIMARY KEY
+);
+
+-- Word Categories junction table
+CREATE TABLE IF NOT EXISTS word_categories (
+ word_id INTEGER NOT NULL,
+ category INTEGER NOT NULL,
+ PRIMARY KEY (word_id, category),
+ FOREIGN KEY (word_id) REFERENCES expressions(id),
+ FOREIGN KEY (category) REFERENCES categories(name)
+);
+CREATE INDEX IF NOT EXISTS idx_word_categories_category_id ON word_categories(category);
+
+
+-- Progress
+CREATE TABLE IF NOT EXISTS users(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name TEXT NOT NULL,
+ creds TEXT NOT NULL,
+ CONSTRAINT name_unique UNIQUE (name)
+);
+CREATE TABLE IF NOT EXISTS user_progress (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ user_id INTEGER NOT NULL,
+ card_id INTEGER NOT NULL,
+ repetition_count INTEGER DEFAULT 0,
+ ease_factor REAL DEFAULT 2.5,
+ interval INTEGER DEFAULT 1,
+ next_review_date INTEGER,
+ last_reviewed INTEGER,
+ is_mastered BOOLEAN DEFAULT FALSE,
+ FOREIGN KEY (user_id) REFERENCES users(id),
+ FOREIGN KEY (card_id) REFERENCES cards(id),
+ CONSTRAINT progress_unique UNIQUE (user_id, card_id)
+);
+-- CREATE TABLE IF NOT EXISTS user_progress (
+-- id INTEGER PRIMARY KEY AUTOINCREMENT,
+-- user_id INTEGER NOT NULL,
+-- card_id INTEGER NOT NULL,
+-- repetition_count INTEGER DEFAULT 0,
+-- ease_factor REAL DEFAULT 2.5,
+-- interval INTEGER DEFAULT 1,
+-- next_review_date DATETIME,
+-- last_reviewed DATETIME,
+-- is_mastered BOOLEAN DEFAULT FALSE,
+-- CONSTRAINT progress_unique UNIQUE (user_id, card_id)
+-- FOREIGN KEY (user_id) REFERENCES users(id),
+-- FOREIGN KEY (card_id) REFERENCES cards(id)
+-- );
+-- Lessons
+CREATE TABLE IF NOT EXISTS lessons(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ name TEXT NOT NULL,
+ position INTEGER NOT NULL DEFAULT 0,
+ description TEXT,
+ lang TEXT,
+ FOREIGN KEY (lang) REFERENCES languages(code)
+);
+CREATE TABLE IF NOT EXISTS cards(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ text TEXT NOT NULL,
+ note TEXT
+);
+CREATE TABLE IF NOT EXISTS cards_expressions(
+ expression_id INTEGER NOT NULL,
+ card_id INTEGER NOT NULL,
+ PRIMARY KEY (card_id, expression_id),
+ FOREIGN KEY (card_id) REFERENCES cards(id),
+ FOREIGN KEY (expression_id) REFERENCES expressions(id)
+);
+CREATE TABLE IF NOT EXISTS cards_lessons(
+ lesson_id INTEGER,
+ card_id INTEGER NOT NULL,
+ PRIMARY KEY (card_id, lesson_id),
+ FOREIGN KEY (card_id) REFERENCES cards(id),
+ FOREIGN KEY (lesson_id) REFERENCES lessons(id)
+);
+
+CREATE TABLE IF NOT EXISTS attempts(
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ user_id INTEGER NOT NULL,
+ timestamp INTEGER NOT NULL,
+ card_id INTEGER NOT NULL,
+ good INTEGER NOT NULL, -- 0 or 1
+ FOREIGN KEY (user_id) REFERENCES users(id)
+ FOREIGN KEY (card_id) REFERENCES cards(id)
+);
+
+-- Index to query attempts on a specific card
+CREATE INDEX IF NOT EXISTS idx_attempts_card ON attempts(card_id);
+
+-- Index to query attempts for a specific user
+CREATE INDEX IF NOT EXISTS idx_attempts_user ON attempts(user_id);
+
+-- (Optional) Index to query attempts by user and resource (useful if you often query by both)
+CREATE INDEX IF NOT EXISTS idx_attempts_user_resource ON attempts(user_id, card_id);
+CREATE INDEX IF NOT EXISTS idx_cards_resources
+ON cards_expressions(expression_id, card_id);
+
+-- CREATE TRIGGER IF NOT EXISTS populate_cards_resources
+-- AFTER INSERT ON cards
+-- FOR EACH ROW
+-- BEGIN
+-- -- Insert matching words into cards_resources
+-- INSERT INTO cards_expressions(card_id, expression_id)
+-- SELECT NEW.id, w.id
+-- FROM expressions w
+-- WHERE NEW.text LIKE '%' || w.spelling || '%';
+-- END;
+--
diff --git a/src/lib/server/cookie.ts b/src/lib/server/cookie.ts
new file mode 100644
index 0000000..fadac9d
--- /dev/null
+++ b/src/lib/server/cookie.ts
@@ -0,0 +1,47 @@
+import {
+ getCookie,
+ getSignedCookie,
+ setCookie,
+ setSignedCookie,
+ deleteCookie,
+} from "hono/cookie";
+import cookie from "cookie";
+// console.log("db module path:", "@/lib/db");
+// console.log(
+// "globalThis.__WAKU_MIDDLEWARE_CONTEXT_STORAGE__:",
+// globalThis.__WAKU_MIDDLEWARE_CONTEXT_STORAGE__,
+// );
+import db from "../db";
+
+import type { Middleware } from "waku/config";
+
+// XXX we would probably like to extend config.
+
+const cookieMiddleware: Middleware = () => {
+ console.log("cookieMiddleware executed");
+ return async (ctx, next) => {
+ if (ctx.req.url.pathname === "/login") return await next();
+ const cookies = cookie.parse(ctx.req.headers.cookie || "");
+ console.log({ cookies });
+ const coki = cookies.sorlang;
+ if (!coki) {
+ ctx.res.status = 301;
+ ctx.res.headers = {
+ Location: "/login",
+ };
+ }
+ if (coki) {
+ const userRow = db.fetchCookie(coki);
+ if (userRow) ctx.data.user = { id: userRow.id, name: userRow.name };
+ else {
+ ctx.res.status = 301;
+ ctx.res.headers = {
+ Location: "/login",
+ };
+ }
+ }
+ await next();
+ };
+};
+
+export default cookieMiddleware;
diff --git a/src/lib/server/cookiebridge.ts b/src/lib/server/cookiebridge.ts
new file mode 100644
index 0000000..ca4bd44
--- /dev/null
+++ b/src/lib/server/cookiebridge.ts
@@ -0,0 +1,33 @@
+import { getContextData } from "waku/middleware/context";
+import {
+ RequestCookies,
+ ResponseCookies,
+ type ResponseCookie,
+} from "@edge-runtime/cookies";
+import { mergeSetCookies } from "./setcookie";
+
+const cookies = () => {
+ const ctx = getContextData() as {
+ headers: Record<string, string | string[]>;
+ cookies?: ResponseCookie[];
+ };
+ const headerObj = ctx.headers || {};
+ headerObj["set-cookie"] = mergeSetCookies(
+ headerObj["set-cookie"] || [],
+ (ctx.cookies || []) as ResponseCookie[],
+ );
+ const headers = new Headers(headerObj as Record<string, string>);
+ const reqCookies = new RequestCookies(headers);
+ const resCookies = new ResponseCookies(headers);
+
+ const getCookie: ResponseCookies["get"] = (...args) =>
+ resCookies.get(...args) || reqCookies.get(...args);
+ const setCookie: ResponseCookies["set"] = (...args) => {
+ const updated = resCookies.set(...args);
+ ctx.cookies = updated.getAll();
+ return updated;
+ };
+ return { getCookie, setCookie };
+};
+
+export { cookies };
diff --git a/src/lib/server/header.ts b/src/lib/server/header.ts
new file mode 100644
index 0000000..33f8792
--- /dev/null
+++ b/src/lib/server/header.ts
@@ -0,0 +1,12 @@
+// https://github.com/t6adev/waku-auth-middleware-demo/blob/a476ecb3d5caf0c7731a34314450400d4dcc2dac/src/middleware/validateRouting.ts
+
+import type { Middleware } from "waku/config";
+
+const headersMiddleware: Middleware = () => {
+ return async (ctx, next) => {
+ ctx.data.headers = ctx.req.headers;
+ await next();
+ };
+};
+
+export default headersMiddleware;
diff --git a/src/lib/server/setcookie.ts b/src/lib/server/setcookie.ts
new file mode 100644
index 0000000..f64b380
--- /dev/null
+++ b/src/lib/server/setcookie.ts
@@ -0,0 +1,25 @@
+import type { Middleware } from "waku/config";
+import { type ResponseCookie, stringifyCookie } from "@edge-runtime/cookies";
+
+export const mergeSetCookies = (
+ resSetCookies: string | string[],
+ cookiesInContext: ResponseCookie[],
+) => {
+ if (typeof resSetCookies === "string") {
+ resSetCookies = [resSetCookies];
+ }
+ return [...resSetCookies, ...cookiesInContext.map(stringifyCookie)];
+};
+
+const setCookieMiddleware: Middleware = () => {
+ return async (ctx, next) => {
+ await next();
+ ctx.res.headers ||= {};
+ ctx.res.headers["set-cookie"] = mergeSetCookies(
+ ctx.res.headers["set-cookie"] || [],
+ (ctx.data.cookies || []) as ResponseCookie[],
+ );
+ };
+};
+
+export default setCookieMiddleware;
diff --git a/src/lib/utils.ts b/src/lib/utils.ts
index 113c874..d3fdf9c 100644
--- a/src/lib/utils.ts
+++ b/src/lib/utils.ts
@@ -1,6 +1,6 @@
import { type ClassValue, clsx } from "clsx";
import { twMerge } from "tailwind-merge";
-import type { Result } from "@/lib/types";
+import type { Result } from "./types";
export function cn(...inputs: ClassValue[]) {
return twMerge(clsx(inputs));
generated by cgit v1.2.3 (git 2.25.1) at 2025-09-09 12:01:17 +0000