summaryrefslogtreecommitdiff
path: root/src/lib
diff options
context:
space:
mode:
Diffstat (limited to 'src/lib')
-rw-r--r--src/lib/db/index.ts82
-rw-r--r--src/lib/db/schema.sql7
-rw-r--r--src/lib/server/cookie.ts6
-rw-r--r--src/lib/types/cards.ts6
4 files changed, 66 insertions, 35 deletions
diff --git a/src/lib/db/index.ts b/src/lib/db/index.ts
index b43edc3..fcfab57 100644
--- a/src/lib/db/index.ts
+++ b/src/lib/db/index.ts
@@ -44,7 +44,7 @@ class DatabaseHandler {
name: string;
expiry: number;
};
- console.log("cokifetch", { coki, res });
+ // console.log("cokifetch", { coki, res });
return res;
}
setCookie(coki: string, user: number, expiry: number) {
@@ -329,18 +329,24 @@ 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,
- ): Result<DeckResponse> {
+ fetchLesson({
+ userId,
+ lessonId,
+ count,
+ page,
+ random,
+ }: {
+ userId: number;
+ lessonId: number;
+ count?: number;
+ page?: number;
+ random?: boolean;
+ }): Result<DeckResponse> {
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, ll.lang as llang, cards.text, cards.note, cards.id as cid,
@@ -351,7 +357,9 @@ class DatabaseHandler {
up.next_review_date,
up.last_reviewed,
up.is_mastered,
+ e.id as eid,
e.*,
+ (CASE WHEN bm.word_id IS NULL THEN 0 ELSE 1 END) as is_bookmarked,
(SELECT
json_group_array(json_object(
'pos', pos,
@@ -371,31 +379,28 @@ class DatabaseHandler {
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 ?;
+ LEFT JOIN user_progress up ON up.card_id = cards.id AND up.user_id = ?1
+ LEFT JOIN bookmarks bm ON bm.word_id = e.id AND bm.user_id = ?1
+ WHERE l.id = ?2 AND (up.next_review_date IS NULL OR up.next_review_date < ?3)
+ ${
+ random
+ ? // ? "AND e.id IN (SELECT id FROM expressions ORDER BY RANDOM() LIMIT ?4 OFFSET ?5)"
+ // "AND e.rowid > (ABS(RANDOM()) % (SELECT max(rowid) FROM expressions)) LIMIT ?4 OFFSET ?5"
+ "ORDER BY RANDOM() LIMIT ?4 OFFSET ?5"
+ : "ORDER BY cards.id, e.id LIMIT ?4 OFFSET ?5"
+ };
`;
- // 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 ?;
- // `;
+ // SELECT * FROM expressions e
+ // WHERE e.rowid > (
+ // ABS(RANDOM()) % (SELECT max(rowid) FROM expressions)
+ // )
+ // LIMIT 10;
const query = this.db.query(queryString);
const res = query.all(userId, lessonId, tomorrow.getTime(), size, offset);
- console.log(res.length);
+ // console.log("cards", res.length);
if (res.length === 0) return { error: "Lesson not found" };
const row: any = res[0];
- // console.log({ row });
+ console.log({ row });
const lesson = {
id: lessonId,
name: row.name,
@@ -415,6 +420,7 @@ class DatabaseHandler {
return { ...s, senses, related, forms };
});
const expression = {
+ isBookmarked: row.is_bookmarked > 0,
ipa: JSON.parse(row.ipa),
prosody: JSON.parse(row.prosody),
syllables: row.syllables,
@@ -673,6 +679,26 @@ class DatabaseHandler {
const res = query.run({ wordId, category });
return res.lastInsertRowid;
}
+ addBookmark(userId: number, wordId: number | bigint, notes?: string) {
+ const queryString = `
+ INSERT OR IGNORE
+ INTO bookmarks(user_id, word_id, created, notes)
+ VALUES(?, ?, ?, ?)
+ `;
+ const query = this.db.query(queryString);
+ const res = query.run(userId, wordId, Date.now(), notes || null);
+ return res.lastInsertRowid;
+ }
+ delBookmark(userId: number, wordId: number | bigint) {
+ const queryString = `
+ DELETE
+ FROM bookmarks
+ WHERE word_id = ? AND user_id = ?
+ `;
+ const query = this.db.query(queryString);
+ const res = query.run(wordId, userId);
+ return res;
+ }
addThaiSyl(params: {
spelling: string;
tone: number;
diff --git a/src/lib/db/schema.sql b/src/lib/db/schema.sql
index 4506619..129400a 100644
--- a/src/lib/db/schema.sql
+++ b/src/lib/db/schema.sql
@@ -58,10 +58,13 @@ CREATE INDEX IF NOT EXISTS idx_senses_parent ON senses(parent_id);
CREATE TABLE IF NOT EXISTS bookmarks(
- word_id INTEGER PRIMARY KEY,
+ word_id INTEGER NOT NULL,
+ user_id INTEGER NOT NULL,
notes TEXT,
created INTEGER NOT NULL,
- FOREIGN KEY (word_id) REFERENCES expressions(id)
+ PRIMARY KEY (word_id, user_id),
+ FOREIGN KEY (word_id) REFERENCES expressions(id),
+ FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE INDEX IF NOT EXISTS idx_bookmarks ON bookmarks(word_id);
diff --git a/src/lib/server/cookie.ts b/src/lib/server/cookie.ts
index bbabd63..9a7e632 100644
--- a/src/lib/server/cookie.ts
+++ b/src/lib/server/cookie.ts
@@ -18,7 +18,7 @@ const cookieMiddleware: Middleware = () => {
// }
if (coki) {
const userRow = db.fetchCookie(coki);
- console.log({ userRow });
+ // console.log({ userRow });
if (userRow) ctx.data.user = { id: userRow.id, name: userRow.name };
// else {
// if (ctx.req.url.pathname === "/login") return await next();
@@ -30,8 +30,8 @@ const cookieMiddleware: Middleware = () => {
}
await next();
const hctx: any = getHonoContext();
- console.log("hono", hctx.lol);
- console.log("ctx coki", ctx.data.cookie);
+ // console.log("hono", hctx.lol);
+ // console.log("ctx coki", ctx.data.cookie);
ctx.res.headers ||= {};
if (ctx.data.cookie)
ctx.res.headers["set-cookie"] = ctx.data.cookie as string;
diff --git a/src/lib/types/cards.ts b/src/lib/types/cards.ts
index 0592a34..cef02d2 100644
--- a/src/lib/types/cards.ts
+++ b/src/lib/types/cards.ts
@@ -154,18 +154,20 @@ export interface ReviewResult {
export type CardResponse = {
id: number;
text: string;
- note: string;
+ note: string | null;
progress: SRSProgress;
expression: {
+ id: number;
ipa: Array<{ ipa: string; tags: string[] }>;
spelling: string;
type: ExpressionType;
- syllables: number;
+ syllables: number | null;
confidence: number;
lang: string;
frequency: number;
prosody: any;
senses: Sense[];
+ isBookmarked: boolean;
};
};
export type Sense = {
generated by cgit v1.2.3 (git 2.25.1) at 2025-09-09 12:01:19 +0000