From 861c03b85160972431ca9b262345d15edecf9acb Mon Sep 17 00:00:00 2001 From: Fuwn Date: Tue, 24 Oct 2023 17:12:54 -0700 Subject: feat: badge wall --- src/lib/userBadgesDatabase.ts | 57 +++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 57 insertions(+) create mode 100644 src/lib/userBadgesDatabase.ts (limited to 'src/lib') diff --git a/src/lib/userBadgesDatabase.ts b/src/lib/userBadgesDatabase.ts new file mode 100644 index 00000000..c129770e --- /dev/null +++ b/src/lib/userBadgesDatabase.ts @@ -0,0 +1,57 @@ +import { dev } from '$app/environment'; +import Database from 'better-sqlite3'; + +export interface Badge { + post: string; + image: string; + description?: string; + id?: number; +} + +const database = new Database('./due_moe.sqlite3', { + verbose: dev ? console.log : undefined +}); + +database.exec(`create table if not exists user_badges ( + id integer primary key, + user_id integer not null, + post text not null, + image text not null, + description text default null, + time timestamp default current_timestamp +)`); + +export const getUserBadges = (userId: number): Badge[] => { + return database + .prepare('select * from user_badges where user_id = ?') + .all(String(userId)) as Badge[]; +}; + +export const addUserBadge = (userId: number, badge: Badge) => { + const { post, image, description } = badge; + const statement = database.prepare(` + insert into user_badges (user_id, post, image, description) values (?, ?, ?, ?) + `); + + statement.run(userId, post, image, description); +}; + +export const addUserBadges = (userId: number, badges: Badge[]) => { + const statement = database.prepare(` + insert into user_badges (user_id, post, image, description) values (?, ?, ?, ?) + `); + + for (const badge of badges) { + const { post, image, description } = badge; + + statement.run(userId, post, image, description); + } +}; + +export const removeUserBadge = (userId: number, id: number) => { + if (!isNaN(id)) { + const statement = database.prepare('delete from user_badges where user_id = ? and id = ?'); + + statement.run(userId, id); + } +}; -- cgit v1.2.3 From ab00423409358eace0aa66017bfb252cdf680fd5 Mon Sep 17 00:00:00 2001 From: Fuwn Date: Tue, 24 Oct 2023 17:23:09 -0700 Subject: feat(badges): add safe limits --- src/lib/userBadgesDatabase.ts | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'src/lib') diff --git a/src/lib/userBadgesDatabase.ts b/src/lib/userBadgesDatabase.ts index c129770e..ae1b1176 100644 --- a/src/lib/userBadgesDatabase.ts +++ b/src/lib/userBadgesDatabase.ts @@ -15,9 +15,9 @@ const database = new Database('./due_moe.sqlite3', { database.exec(`create table if not exists user_badges ( id integer primary key, user_id integer not null, - post text not null, - image text not null, - description text default null, + post text(1000) not null, + image text(1000) not null, + description text(1000) default null, time timestamp default current_timestamp )`); -- cgit v1.2.3 From dc9755d709b5bc88fd81f6e11641c1e78659283d Mon Sep 17 00:00:00 2001 From: Fuwn Date: Tue, 24 Oct 2023 17:28:39 -0700 Subject: fix(database): change path for docker --- src/lib/userBadgesDatabase.ts | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'src/lib') diff --git a/src/lib/userBadgesDatabase.ts b/src/lib/userBadgesDatabase.ts index ae1b1176..78655cd3 100644 --- a/src/lib/userBadgesDatabase.ts +++ b/src/lib/userBadgesDatabase.ts @@ -8,7 +8,7 @@ export interface Badge { id?: number; } -const database = new Database('./due_moe.sqlite3', { +const database = new Database('./data/due_moe.sqlite3', { verbose: dev ? console.log : undefined }); -- cgit v1.2.3 From f99fd41ee23d7669812ff1d88d23a9b7f22db901 Mon Sep 17 00:00:00 2001 From: Fuwn Date: Tue, 24 Oct 2023 18:18:37 -0700 Subject: feat(user): prettier user route --- src/lib/AniList/user.ts | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'src/lib') diff --git a/src/lib/AniList/user.ts b/src/lib/AniList/user.ts index dd9995fd..fdc98a58 100644 --- a/src/lib/AniList/user.ts +++ b/src/lib/AniList/user.ts @@ -15,6 +15,9 @@ export interface User { volumesRead: number; }; }; + avatar: { + large: string; + }; } export const user = async (username: string): Promise => { @@ -28,7 +31,7 @@ export const user = async (username: string): Promise => { }, body: JSON.stringify({ query: `{ User(name: "${username}") { - name id statistics { + name id avatar { large } statistics { anime { count meanScore minutesWatched episodesWatched } -- cgit v1.2.3 From e61da1367da23c309bd006416d598d861dfcfb2c Mon Sep 17 00:00:00 2001 From: Fuwn Date: Thu, 26 Oct 2023 14:48:00 -0700 Subject: refactor(badges): move to bun:sqlite --- src/lib/userBadgesDatabase.ts | 61 ++++++++++++++++++++----------------------- 1 file changed, 29 insertions(+), 32 deletions(-) (limited to 'src/lib') diff --git a/src/lib/userBadgesDatabase.ts b/src/lib/userBadgesDatabase.ts index 78655cd3..8cf40d5b 100644 --- a/src/lib/userBadgesDatabase.ts +++ b/src/lib/userBadgesDatabase.ts @@ -1,57 +1,54 @@ -import { dev } from '$app/environment'; -import Database from 'better-sqlite3'; +import { Database } from 'bun:sqlite'; export interface Badge { - post: string; - image: string; + post?: string; + image?: string; description?: string; id?: number; } const database = new Database('./data/due_moe.sqlite3', { - verbose: dev ? console.log : undefined + create: true }); -database.exec(`create table if not exists user_badges ( - id integer primary key, - user_id integer not null, - post text(1000) not null, - image text(1000) not null, - description text(1000) default null, - time timestamp default current_timestamp -)`); +database + .query( + `CREATE TABLE IF NOT EXISTS user_badges ( + id INTEGER PRIMARY KEY, + user_id INTEGER NOT NULL, + post TEXT(1000) NOT NULL, + image TEXT(1000) NOT NULL, + description TEXT(1000) DEFAULT NULL, + time TIMESTAMP DEFAULT CURRENT_TIMESTAMP +)` + ) + .run(); export const getUserBadges = (userId: number): Badge[] => { return database - .prepare('select * from user_badges where user_id = ?') - .all(String(userId)) as Badge[]; + .query('SELECT * FROM user_badges WHERE user_id = $userId') + .all({ $userId: String(userId) }) as Badge[]; }; export const addUserBadge = (userId: number, badge: Badge) => { const { post, image, description } = badge; - const statement = database.prepare(` - insert into user_badges (user_id, post, image, description) values (?, ?, ?, ?) - `); - statement.run(userId, post, image, description); -}; - -export const addUserBadges = (userId: number, badges: Badge[]) => { - const statement = database.prepare(` - insert into user_badges (user_id, post, image, description) values (?, ?, ?, ?) - `); + if (post === undefined || image === undefined) { + return; + } - for (const badge of badges) { - const { post, image, description } = badge; + const statement = database.query( + `INSERT INTO user_badges (user_id, post, image, description) VALUES (?1, ?2, ?3, ?4);` + ); - statement.run(userId, post, image, description); - } + statement.run([userId, post, image, description]); }; export const removeUserBadge = (userId: number, id: number) => { if (!isNaN(id)) { - const statement = database.prepare('delete from user_badges where user_id = ? and id = ?'); - - statement.run(userId, id); + database.query('DELETE FROM user_badges WHERE user_id = $userId AND id = $id').run({ + $userId: userId, + $id: id + }); } }; -- cgit v1.2.3