From e839a5f61f0faa21ca8b4bd5767f7575d5e576ee Mon Sep 17 00:00:00 2001 From: polwex Date: Wed, 21 May 2025 14:00:28 +0700 Subject: the card flip animation is legit --- src/lib/db/index.ts | 92 ++++++++++++++++++++++++++++++++++------------------- 1 file changed, 60 insertions(+), 32 deletions(-) (limited to 'src/lib/db/index.ts') diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts index a710a1e..b43edc3 100644 --- a/src/lib/db/index.ts +++ b/src/lib/db/index.ts @@ -1,8 +1,9 @@ import Database from "bun:sqlite"; import { getDBOffset, wordFactorial } from "../utils"; -import type { AddSense, AddWord, State } from "../types"; +import type { AddSense, AddWord, Result, State } from "../types"; import { DEFAULT_SRS } from "../services/srs"; import { DBWord, WordData } from "@/zoom/logic/types"; +import { DeckResponse } from "../types/cards"; const PAGE_SIZE = 100; @@ -57,16 +58,35 @@ class DatabaseHandler { // 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 query = this.db.query(` + SELECT + l.id, + l.name, + l.position, + l.description, + COUNT(cl.card_id) AS count + FROM + lessons l + INNER JOIN + lang_lessons ll ON l.id = ll.lesson_id + LEFT JOIN + cards_lessons cl ON l.id = cl.lesson_id + WHERE + ll.lang = ? + GROUP BY + l.id, l.name, l.position, l.description + ORDER BY + l.position, l.name; + `); const res = query.all(lang); console.log(res, "results"); - return res; + return res as Array<{ + id: number; + count: number; + name: string; + description: string; + position: number; + }>; } fetchSyllables(lang: string, page?: number) { const query = this.db.query( @@ -135,7 +155,7 @@ class DatabaseHandler { 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 + JOIN categories c ON c.name = wc.category WHERE spelling = $spelling GROUP BY words.id `); @@ -151,7 +171,7 @@ class DatabaseHandler { 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 + JOIN categories c ON c.name= wc.category ORDER BY e.frequency DESC LIMIT $count OFFSET $offset @@ -297,7 +317,7 @@ class DatabaseHandler { LEFT JOIN word_categories wc ON wc.word_id = e.id LEFT JOIN - categories cat ON cat.id = wc.category_id + categories cat ON cat.name = wc.category GROUP BY l.id, c.id, e.id ORDER BY @@ -309,7 +329,12 @@ class DatabaseHandler { } // 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) { + fetchLesson( + userId: number, + lessonId: number, + count?: number, + page?: number, + ): Result { const p = page ? page : 1; const size = count ? count : PAGE_SIZE; const offset = getDBOffset(p, size); @@ -318,7 +343,7 @@ class DatabaseHandler { console.log(tomorrow.getTime()); const queryString = ` SELECT - l.name, l.description, l.lang as llang, cards.text, cards.note, cards.id as cid, + l.name, l.description, ll.lang as llang, cards.text, cards.note, cards.id as cid, up.id as upid, up.repetition_count, up.ease_factor, @@ -342,6 +367,7 @@ class DatabaseHandler { 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 lang_lessons ll ON l.id = ll.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 @@ -367,7 +393,7 @@ class DatabaseHandler { 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; + if (res.length === 0) return { error: "Lesson not found" }; const row: any = res[0]; // console.log({ row }); const lesson = { @@ -377,8 +403,10 @@ class DatabaseHandler { language: row.llang, cardCount: row.total_card_count, }; + // TODO IPA, prosody, senses... should we unify the format on the wikisource standard? const cards = res.map((row: any) => { // TODO parse here...? + // console.log({ row }); const sense_array = JSON.parse(row.senses_array); const senses = sense_array.map((s: any) => { const senses = JSON.parse(s.senses); @@ -417,7 +445,7 @@ class DatabaseHandler { }; return card; }); - return { lesson, cards }; + return { ok: { lesson, cards } }; } fetchCard(cid: number, userid: number) { const query = this.db.query(` @@ -448,7 +476,7 @@ class DatabaseHandler { VALUES (${columns.map((c) => "?").join(",")}) `; const query = this.db.query(queryString).run(...Object.values(params)); - return query; + return query.lastInsertRowid; } addSense(params: AddSense) { const columns = Object.keys(params); @@ -521,25 +549,28 @@ class DatabaseHandler { }) { const { text, mnote, eid, lesson_id } = params; const note = mnote ? mnote : null; - const query = this.db.query(` + const tx = this.db.transaction(() => { + 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(` + 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(` + 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); - } + query.run(cid, lesson_id); + } + }); + return tx(); } addCardO(lesson_id: number | bigint | null, text: string, mnote?: string) { // wtf is this fucntion when did I write this @@ -634,12 +665,9 @@ class DatabaseHandler { } 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 - )) + INSERT OR IGNORE + INTO word_categories(word_id, category) + VALUES($wordId, $category) `; const query = this.db.query(queryString); const res = query.run({ wordId, category }); -- cgit v1.2.3