From df7ffaf4cb722890ca3159c3839c61552f7195d3 Mon Sep 17 00:00:00 2001 From: polwex Date: Thu, 15 May 2025 04:37:12 +0700 Subject: all working now... --- src/lib/db/index.ts | 701 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 701 insertions(+) create mode 100644 src/lib/db/index.ts (limited to 'src/lib/db/index.ts') 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) { + 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) { + 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 = { + CC: "conjunction", + DT: "determiner", + IN: "preposition", + MD: "auxiliar", + PRP: "nominative", // TODO oi + PRP$: "gemitive", + WDT: "determiner", + WP: "interrogative", + WP$: "interrogative", +}; +export const domains: Record = { + "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; -- cgit v1.2.3