From d56594d3289002566f4653d607f0837befd65109 Mon Sep 17 00:00:00 2001 From: polwex Date: Thu, 15 May 2025 10:13:00 +0700 Subject: wtf man --- src/lib/db/index.ts | 36 +++++++---- src/lib/db/schema.sql | 172 ++++++++++++++++++++++++++++++++++++++++++++++++++ 2 files changed, 197 insertions(+), 11 deletions(-) create mode 100644 src/lib/db/schema.sql (limited to 'src/lib/db') 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; +-- -- cgit v1.2.3