1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
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(1000) not null,
image text(1000) not null,
description text(1000) 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);
}
};
|