summaryrefslogtreecommitdiff
path: root/src/lib/db
diff options
context:
space:
mode:
Diffstat (limited to 'src/lib/db')
-rw-r--r--src/lib/db/index.ts701
1 files changed, 701 insertions, 0 deletions
diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
new file mode 100644
index 0000000..9897af8
--- /dev/null
+++ b/src/lib/db/index.ts
@@ -0,0 +1,701 @@
+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";
+
+const PAGE_SIZE = 100;
+
+export function getState(coki: string | null): State {
+ let user = null;
+ if (coki) {
+ const row: any = db.fetchCookie(coki);
+ console.log("user row", row);
+ user = row;
+ }
+ return { user };
+}
+class DatabaseHandler {
+ db: Database;
+ constructor() {
+ const dbPath = "/home/y/code/bun/ssr/waku/bulkdata/prosody.db";
+ const db = new Database(dbPath, { create: true });
+ db.exec("PRAGMA journal_mode = WAL"); // Enable Write-Ahead Logging for better performance
+ db.exec("PRAGMA foreign_keys = ON");
+ this.db = db;
+ }
+ async init() {
+ const file = Bun.file("./schema.sql");
+ const sql = await file.text();
+ this.db.exec(sql);
+ }
+ // cokis
+ fetchCookie(coki: string) {
+ const query = this.db.query(
+ `
+ SELECT * FROM cookies
+ WHERE cookie = ?
+ `,
+ );
+ const res = query.get(coki);
+ return res;
+ }
+ setCookie(coki: string, user: number, expiry: number) {
+ const query = this.db.query(`
+ INSERT OR REPLACE INTO cookies(user, cookie, expiry)
+ VALUES(?, ?, ?)
+ `);
+ const res = query.run(user, coki, expiry);
+ return res.lastInsertRowid;
+ }
+ // read
+ //
+ fetchLanguage(lang: string, page?: number) {
+ const query = this.db.query(
+ `
+ SELECT * FROM lessons
+ JOIN languages l ON l.code = lessons.lang
+ WHERE lessons.lang= ?
+ `,
+ );
+ const res = query.all(lang);
+ console.log(res, "results");
+ return res;
+ }
+ fetchSyllables(lang: string, page?: number) {
+ const query = this.db.query(
+ `
+ SELECT * FROM expressions e
+ WHERE e.syllables = 1 AND e.lang = ?
+ ORDER BY frequency DESC
+ LIMIT ${PAGE_SIZE} ${page ? "OFFSET " + getDBOffset(page, PAGE_SIZE) : ""}
+ `,
+ );
+ const results = query.all(lang);
+ console.log({ lang, page }, "results");
+ const rpage = (page || 0) + 1;
+ return { page: rpage, results };
+ }
+ fetchInit(userId: number) {
+ const query1 = this.db.query(`
+ SELECT *
+ FROM languages
+ `);
+ const languages = query1.all();
+ const query2 = this.db.query(`
+ SELECT *
+ FROM categories
+ `);
+ const categories = query2.all().map((c: any) => c.name);
+ const query3 = this.db.query(`
+ SELECT
+ l.id,
+ l.name,
+ l.position,
+ l.description,
+ l.lang,
+ COUNT(cl.card_id) as card_count,
+ SUM(CASE WHEN up.is_mastered = 1 THEN 1 ELSE 0 END) as completed_cards
+ FROM lessons l
+ JOIN cards_lessons cl ON cl.lesson_id = l.id
+ JOIN user_progress up ON cl.card_id = up.card_id AND up.user_id = ?
+ GROUP BY l.id, l.name, l.position, l.description, l.lang
+ ORDER BY l.position
+ `);
+ const decks = query3.all(userId);
+ return { languages, categories, decks };
+ }
+ fetchCats() {
+ const query1 = this.db.query(`
+ SELECT *
+ FROM languages
+ `);
+ const languages = query1.all();
+ const query2 = this.db.query(`
+ SELECT *
+ FROM categories
+ `);
+ const categories = query2.all().map((c: any) => c.name);
+ return { languages, categories };
+ }
+ fetchResource(spelling: string) {
+ const query = this.db.query(`
+ SELECT
+ spelling,
+ ipa,
+ frequency,
+ type,
+ subtype,
+ GROUP_CONCAT(c.name, ',') AS category,
+ FROM expressions
+ JOIN word_categories wc ON wc.word_id = words.id
+ JOIN categories c ON c.id = wc.category_id
+ WHERE spelling = $spelling
+ GROUP BY words.id
+ `);
+ return query.get({ spelling });
+ }
+ fetchFrequent(count: number, page: number) {
+ const offset = (page - 1) * count;
+ const query = this.db.query(`
+ SELECT
+ spelling,
+ ipa,
+ frequency,
+ GROUP_CONCAT(c.name, ',') AS category
+ FROM expressions e
+ JOIN word_categories wc ON wc.word_id = e.id
+ JOIN categories c ON c.id = wc.category_id
+ ORDER BY e.frequency DESC
+ LIMIT $count
+ OFFSET $offset
+ `);
+ return query.get({ count, offset });
+ }
+
+ fetchExpressionRaw(params: Record<string, string>) {
+ const paramString = Object.keys(params)
+ .map((k) => `${k} = ?`)
+ .join(" AND ");
+ const queryString = `
+ SELECT *
+ FROM expressions
+ WHERE ${paramString}
+ ORDER BY frequency DESC
+ `;
+ const query = this.db.query(queryString);
+ return query.all(...Object.values(params));
+ }
+ fetchExpressionBySpelling(spelling: string, lang: string) {
+ const queryString = `
+ SELECT *
+ FROM expressions e
+ WHERE e.spelling = ? AND e.lang = ?
+ ORDER BY e.frequency DESC
+ `;
+ const query = this.db.query(queryString);
+ return query.get(spelling, lang);
+ }
+ fetchWordBySpelling(spelling: string, lang: string) {
+ const queryString = `
+ SELECT *,
+ (SELECT
+ json_group_array(json_object(
+ 'pos', pos,
+ 'senses', s.senses,
+ 'forms', forms,
+ 'etymology', etymology,
+ 'related', related)
+ )
+ FROM senses s WHERE s.parent_id = e.id
+ ) as senses_array
+ FROM expressions e
+ WHERE e.spelling = ? AND e.lang = ?
+ ORDER BY e.frequency DESC
+ `;
+ const query = this.db.query(queryString);
+ return query.get(spelling, lang);
+ }
+ fetchExpressionsByCard(cid: number) {
+ const queryString = `
+ SELECT
+ e.spelling, e.id as eid, e.ipa
+ FROM cards_expressions ce
+ JOIN expressions e ON ce.expression_id = e.id
+ WHERE ce.card_id = $cid AND e.spelling IS NOT NULL
+ ORDER BY e.frequency DESC
+ `;
+ const query = this.db.query(queryString);
+ return query.all({ cid });
+ }
+
+ searchExpression(params: ExpressionSearchParams) {
+ let queryParam: string[] = [];
+ if (params.lang) queryParam.push(`e.lang = '${params.lang}'`);
+ if (params.spelling) queryParam.push(`e.spelling = '${params.spelling}'`);
+ if (params.pos) queryParam.push(`e.pos = '${params.pos}'`);
+ if (params.syllables)
+ queryParam.push(
+ `e.syllables ${params.syllables.sign} ${params.syllables.num}`,
+ );
+ if (params.type) queryParam.push(`e.type = ${params.type}`);
+ if (params.frequency)
+ queryParam.push(
+ `e.frequency ${params.frequency.above ? ">" : "<"} ${params.frequency}`,
+ );
+ const queryString = `
+ SELECT * FROM expressions e
+ WHERE ${queryParam.join(" AND ")}
+ `;
+ console.log({ queryString });
+ const query = this.db.query(queryString);
+ return query.all(...Object.values(params));
+ }
+ fetchLessons(count?: number, page?: number) {
+ const p = page ? page : 1;
+ const size = count ? count : PAGE_SIZE;
+ const offset = getDBOffset(p, size);
+ // const queryString = `
+ // SELECT
+ // l.id, l.text as ltext, cards.text as ctext, cards.note as cnote, cards.id as cid
+ // FROM cards_lessons cl
+ // JOIN cards ON cards.id = cl.card_id
+ // JOIN lessons l ON l.id = cl.lesson_id
+ // LIMIT $count
+ // OFFSET $offset
+ // `;
+ const queryString = `
+ SELECT
+ l.id AS lesson_id,
+ l.text AS lesson_text,
+ c.id AS card_id,
+ c.text AS card_text,
+ c.note AS card_note,
+ e.id AS expression_id,
+ e.spelling AS expression_spelling,
+ e.ipa AS expression_ipa,
+ e.type AS expression_type,
+ e.subtype AS expression_subtype,
+ GROUP_CONCAT(cat.name, ', ') AS categories
+ FROM
+ lessons l
+ JOIN
+ cards_lessons cl ON l.id = cl.lesson_id
+ JOIN
+ cards c ON c.id = cl.card_id
+ JOIN
+ cards_expressions ce ON c.id = ce.card_id
+ JOIN
+ expressions e ON e.id = ce.expression_id
+ LEFT JOIN
+ word_categories wc ON wc.word_id = e.id
+ LEFT JOIN
+ categories cat ON cat.id = wc.category_id
+ GROUP BY
+ l.id, c.id, e.id
+ ORDER BY
+ l.id ASC, c.id ASC, e.id ASC
+ LIMIT ? OFFSET ?;
+ `;
+ const query = this.db.query(queryString);
+ return query.all(size, offset);
+ }
+
+ // SELECT l.id, l.text, cards.text, cards.note FROM cards_lessons cl LEFT JOIN lessons l ON l.id = cl.lesson_id LEFT JOIN cards ON cards.id = cl.card_id ORDER BY l.id ASC LIMIT 20 OFFSET 0;
+ fetchLesson(userId: number, lessonId: number, count?: number, page?: number) {
+ const p = page ? page : 1;
+ const size = count ? count : PAGE_SIZE;
+ const offset = getDBOffset(p, size);
+ const tomorrow = new Date();
+ tomorrow.setDate(tomorrow.getDate() + 1);
+ console.log(tomorrow.getTime());
+ const queryString = `
+ SELECT
+ l.name, l.description, l.lang as llang, cards.text, cards.note, cards.id as cid,
+ up.id as upid,
+ up.repetition_count,
+ up.ease_factor,
+ up.interval,
+ up.next_review_date,
+ up.last_reviewed,
+ up.is_mastered,
+ e.*,
+ (SELECT
+ json_group_array(json_object(
+ 'pos', pos,
+ 'senses', s.senses,
+ 'forms', forms,
+ 'etymology', etymology,
+ 'related', related)
+ )
+ FROM senses s WHERE s.parent_id = e.id
+ ) as senses_array,
+ (SELECT COUNT(DISTINCT cl_inner.card_id)
+ FROM cards_lessons cl_inner
+ WHERE cl_inner.lesson_id = l.id) AS total_card_count
+ FROM cards_lessons cl
+ JOIN lessons l ON l.id = cl.lesson_id
+ JOIN cards ON cards.id = cl.card_id
+ JOIN cards_expressions ce ON cards.id = ce.card_id
+ JOIN expressions e ON e.id = ce.expression_id
+ LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?
+ WHERE l.id = ? AND (up.next_review_date IS NULL OR up.next_review_date < ?)
+ ORDER BY cards.id, e.id
+ LIMIT ? OFFSET ?;
+ `;
+ // const queryString = `
+ // SELECT
+ // l.id, l.name, l.description, l.lang, cards.text, cards.note, cards.id as cid,
+ // spelling, ipa, frequency, e.id as eid,
+ // GROUP_CONCAT(wc.category, ',') AS category
+ // FROM cards_lessons cl
+ // JOIN lessons l ON l.id = cl.lesson_id
+ // JOIN cards ON cards.id = cl.card_id
+ // JOIN cards_expressions ce ON cards.id = ce.card_id
+ // JOIN expressions e ON e.id = ce.expression_id
+ // JOIN word_categories wc ON wc.word_id = e.id
+ // WHERE l.id = ?
+ // LIMIT ? OFFSET ?;
+ // `;
+ const query = this.db.query(queryString);
+ const res = query.all(userId, lessonId, tomorrow.getTime(), size, offset);
+ console.log(res.length);
+ if (res.length === 0) return null;
+ const row: any = res[0];
+ // console.log({ row });
+ const lesson = {
+ id: lessonId,
+ name: row.name,
+ description: row.description,
+ language: row.llang,
+ cardCount: row.total_card_count,
+ };
+ const cards = res.map((row: any) => {
+ // TODO parse here...?
+ const sense_array = JSON.parse(row.senses_array);
+ const senses = sense_array.map((s: any) => {
+ const senses = JSON.parse(s.senses);
+ const related = JSON.parse(s.related);
+ const forms = JSON.parse(s.forms);
+ return { ...s, senses, related, forms };
+ });
+ const expression = {
+ ipa: JSON.parse(row.ipa),
+ prosody: JSON.parse(row.prosody),
+ syllables: row.syllables,
+ frequency: row.frequency,
+ type: row.type,
+ lang: row.lang,
+ spelling: row.spelling,
+ id: row.id,
+ confidence: row.confidence,
+ senses,
+ };
+ const progress = !row.upid
+ ? DEFAULT_SRS
+ : {
+ repetitionCount: row.repetition_count,
+ easeFactor: row.ease_factor,
+ interval: row.interval,
+ nextReviewDate: row.next_review_date,
+ lastReviewed: row.last_reviewed,
+ isMastered: row.is_mastered,
+ };
+ const card = {
+ text: row.text,
+ note: row.note,
+ id: row.cid,
+ expression,
+ progress,
+ };
+ return card;
+ });
+ return { lesson, cards };
+ }
+ fetchCard(cid: number, userid: number) {
+ const query = this.db.query(`
+ SELECT
+ l.id, l.text, cards.text, cards.note, u.repetition_count, u.ease_factor, u.interval, u.next_review_date, u.last_reviewed, u.is_mastered
+ FROM cards_lessons cl
+ JOIN lessons l ON l.id = cl.lesson_id
+ JOIN cards ON cards.id = lc.card_id
+ JOIN attempts a ON a.card_id = cards.id AND a.user_id = $userid
+ JOIN user_progress u ON u.card_id = cards.id AND u.user_id = $userid
+ WHERE cards.id = $cid
+ `);
+ return query.all({ cid, userid });
+ }
+ //
+ // write
+ //
+ addLanguage(code: string, name: string) {
+ const query = this.db
+ .query(`INSERT OR IGNORE INTO languages(code, name) VALUES(?, ?)`)
+ .run(code, name);
+ }
+ addWord(params: AddWord) {
+ const columns = Object.keys(params);
+
+ const queryString = `INSERT INTO
+ expressions(${columns.join(", ")})
+ VALUES (${columns.map((c) => "?").join(",")})
+ `;
+ const query = this.db.query(queryString).run(...Object.values(params));
+ return query;
+ }
+ addSense(params: AddSense) {
+ const columns = Object.keys(params);
+
+ const queryString = `INSERT INTO senses(${columns.join(", ")}) VALUES (${columns.map((c) => "?").join(",")})`;
+ const query = this.db.query(queryString).run(...Object.values(params));
+ return query.lastInsertRowid;
+ }
+ upsertWord(params: AddWord) {
+ const columns = Object.keys(params);
+ const queryString = `
+ INSERT INTO expressions(${columns.join(", ")})
+ VALUES (${columns.map((c) => "?").join(",")})
+ ON CONFLICT(spelling, lang) DO UPDATE SET
+ ${columns.map((c) => `${c}=excluded.${c}`).join(",\n")}
+ WHERE excluded.spelling = expressions.spelling
+ `;
+ console.log("upserting", queryString);
+ const query = this.db.query(queryString).run(...Object.values(params));
+ return query.lastInsertRowid;
+ }
+ updateWord(id: number, params: Record<string, any>) {
+ const columns = Object.keys(params);
+ const queryString = `
+ UPDATE expressions SET
+ ${columns.map((c) => `${c}= ?`).join(",\n")}
+ WHERE expressions.id = ?
+ `;
+ console.log("upserting", queryString);
+ const query = this.db.query(queryString).run(...Object.values(params), id);
+ return query.lastInsertRowid;
+ }
+
+ addFrequency(spelling: string, frequency: number) {
+ const queryString = `
+ UPDATE expressions
+ SET frequency = ?
+ WHERE expressions.spelling = ?
+ `;
+ const query = this.db.query(queryString);
+ const res = query.run(frequency, spelling);
+ }
+ // addIPA(spelling: string, ipa: string) {
+ // const queryString = `
+ // UPDATE expressions
+ // SET ipa= $ipa
+ // WHERE expressions.spelling = $spelling
+ // `;
+ // const query = this.db.query(queryString);
+ // const res = query.run({ spelling, ipa });
+ // }
+ addLesson(body: {
+ name: string;
+ description: string | null;
+ lang: string | null;
+ }) {
+ const query = this.db.query(`
+ INSERT
+ INTO lessons(name, description, lang)
+ VALUES(?, ?, ?)
+ `);
+ const res = query.run(body.name, body.description, body.lang);
+ return res.lastInsertRowid;
+ }
+ addCard(params: {
+ text: string;
+ eid: number;
+ mnote?: string;
+ lesson_id?: number | bigint;
+ }) {
+ const { text, mnote, eid, lesson_id } = params;
+ const note = mnote ? mnote : null;
+ const query = this.db.query(`
+ INSERT
+ INTO cards(text, note)
+ VALUES(?, ?)
+ `);
+ const res = query.run(text, note);
+ const cid = res.lastInsertRowid;
+ const query2 = this.db.query(`
+ INSERT OR IGNORE INTO cards_expressions(card_id, expression_id)
+ VALUES(?, ?)
+ `);
+ query2.run(cid, eid);
+ if (lesson_id) {
+ const query = this.db.query(`
+ INSERT INTO cards_lessons(card_id, lesson_id)
+ VALUES(?, ?)
+ `);
+ query.run(cid, lesson_id);
+ }
+ }
+ addCardO(lesson_id: number | bigint | null, text: string, mnote?: string) {
+ // wtf is this fucntion when did I write this
+ const note = mnote ? mnote : null;
+ const query = this.db.query(`
+ INSERT
+ INTO cards(text, note)
+ VALUES($text, $note)
+ `);
+ const params = { text, note, spel: text };
+ const res = query.run(params);
+ const cid = res.lastInsertRowid;
+ const wquery = this.db.query(`
+ INSERT OR IGNORE
+ INTO cards_expressions(card_id, expression_id)
+ VALUES($cid, (
+ SELECT id FROM expressions e
+ WHERE e.spelling LIKE $spelling
+ ))
+ `);
+ const wtr = this.db.transaction((pairs) => {
+ // console.log("adding to ce", { pairs, cid, text });
+ for (const pair of pairs) wquery.run(pair);
+ });
+ const words = text
+ .replace(/[^\w\s]/g, "")
+ .replace(/\s+/g, " ")
+ .trim()
+ .split(" ");
+ const combinations = wordFactorial(words);
+ const richWords = Array.from(combinations).map((spelling) => {
+ return { spelling, cid };
+ });
+ wtr(richWords);
+ if (lesson_id) {
+ const query = this.db.query(`
+ INSERT INTO cards_lessons(card_id, lesson_id)
+ VALUES($cid, $lesson_id)
+ `);
+ query.run({ lesson_id, cid });
+ }
+ }
+ processCard(userId: number, cardId: number, timestamp: number, ok: boolean) {
+ const query = this.db
+ .query(
+ `
+ INSERT into attempts(user_id,timestamp, card_id, good) VALUES (?, ?, ?, ?);
+ `,
+ )
+ .run(userId, timestamp, cardId, ok ? 1 : 0);
+ }
+
+ addUser(name: string, creds: string) {
+ try {
+ const query = this.db.query(`
+ INSERT OR ABORT
+ INTO users(name, creds)
+ VALUES($name, $creds)
+ `);
+ const q = query.run({ $name: name, $creds: creds });
+ return { ok: q.lastInsertRowid };
+ } catch (e) {
+ return { error: `${e}` };
+ }
+ }
+ loginUser(name: string, creds: string) {
+ const query = this.db.query(`
+ SELECT id FROM users
+ WHERE name = ? AND creds = ?
+ `);
+ const row = query.get(name, creds) as { id: number } | null;
+ if (!row) return { error: "not found" };
+ else return { ok: row.id };
+ }
+ addCat(category: string) {
+ const queryString = `
+ INSERT OR IGNORE
+ INTO categories(name)
+ VALUES(?)
+ `;
+ const query = this.db.query(queryString);
+ const res = query.run(category);
+ return res.lastInsertRowid;
+ }
+ addWCat(wordId: number | bigint, category: string) {
+ const queryString = `
+ INSERT
+ INTO word_categories(word_id, category_id)
+ VALUES($wordId, (
+ SELECT id FROM categories
+ WHERE name = $category
+ ))
+ `;
+ const query = this.db.query(queryString);
+ const res = query.run({ wordId, category });
+ return res.lastInsertRowid;
+ }
+ addThaiSyl(params: {
+ spelling: string;
+ tone: number;
+ is_long: number;
+ ipa: string;
+ frequency?: number;
+ }) {
+ const columns = Object.keys(params);
+
+ const queryString = `
+ INSERT OR IGNORE
+ INTO thai_syllables(${columns.join(", ")})
+ VALUES(${columns.map((c) => "?").join(", ")})
+ `;
+ const query = this.db.query(queryString).run(...Object.values(params));
+ return query.lastInsertRowid;
+ }
+}
+
+export const poss: Record<string, string> = {
+ CC: "conjunction",
+ DT: "determiner",
+ IN: "preposition",
+ MD: "auxiliar",
+ PRP: "nominative", // TODO oi
+ PRP$: "gemitive",
+ WDT: "determiner",
+ WP: "interrogative",
+ WP$: "interrogative",
+};
+export const domains: Record<string, string> = {
+ "adj.all": "adjective",
+ "adj.pert": "adjective",
+ "adj.ppl": "adjective",
+ "adv.all": "adverb",
+ "noun.Tops": "",
+ "noun.act": "abstract",
+ "noun.animal": "animate",
+ "noun.artifact": "inanimate",
+ "noun.attribute": "abstract",
+ "noun.body": "inanimate",
+ "noun.cognition": "abstract",
+ "noun.communication": "abstract",
+ "noun.event": "abstract",
+ "noun.feeling": "abstract",
+ "noun.food": "inanimate",
+ "noun.group": "noun",
+ "noun.location": "spatial",
+ "noun.motive": "abstract",
+ "noun.object": "inanimate",
+ "noun.person": "animate",
+ "noun.phenomenon": "abstract",
+ "noun.plant": "noun",
+ "noun.possession": "noun",
+ "noun.process": "noun",
+ "noun.quantity": "uncountable",
+ "noun.relation": "noun",
+ "noun.shape": "noun",
+ "noun.state": "noun",
+ "noun.substance": "uncountable",
+ "noun.time": "temporal",
+ "verb.body": "verb",
+ "verb.change": "verb",
+ "verb.cognition": "verb",
+ "verb.communication": "verb",
+ "verb.competition": "verb",
+ "verb.consumption": "verb",
+ "verb.contact": "verb",
+ "verb.creation": "verb",
+ "verb.emotion": "mental",
+ "verb.motion": "verb",
+ "verb.perception": "mental",
+ "verb.possession": "verb",
+ "verb.social": "verb",
+ "verb.stative": "verb",
+ "verb.weather": "verb",
+};
+
+type ExpressionSearchParams = {
+ lang?: string;
+ spelling?: string;
+ pos?: string;
+ syllables?: { num: number; sign: string };
+ frequency?: { num: number; above: boolean };
+ type?: ExpressionType;
+};
+type ExpressionType = "syllable" | "word" | "expression";
+
+const db = new DatabaseHandler();
+export default db;
generated by cgit v1.2.3 (git 2.25.1) at 2025-09-09 14:09:47 +0000