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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
use sqlx::sqlite::SqlitePool;
use crate::model::category::{Category, Create, Update};
pub async fn list(pool: &SqlitePool) -> Vec<Category> {
let res = sqlx::query_as::<_, Category>(
r#"
SELECT
id,
name,
color
FROM
categories
WHERE
deleted_at IS NULL
ORDER BY
name
"#,
)
.fetch_all(pool)
.await;
match res {
Ok(categories) => categories,
Err(err) => {
log::error!("Error listing categories: {:?}", err);
vec![]
}
}
}
pub async fn get(pool: &SqlitePool, id: i64) -> Option<Category> {
let query = r#"
SELECT
id,
name,
color
FROM
categories
WHERE
id = ?
AND deleted_at IS NULL
"#;
let res = sqlx::query_as::<_, Category>(query)
.bind(id)
.fetch_one(pool)
.await;
match res {
Ok(p) => Some(p),
Err(err) => {
log::error!("Error looking for category {}: {:?}", id, err);
None
}
}
}
pub async fn create(pool: &SqlitePool, c: &Create) -> Option<i64> {
let res = sqlx::query(
r#"
INSERT INTO
categories(name, color)
VALUES
(?, ?)
"#,
)
.bind(c.name.clone())
.bind(c.color.clone())
.execute(pool)
.await;
match res {
Ok(x) => Some(x.last_insert_rowid()),
Err(err) => {
log::error!("Error creating category: {:?}", err);
None
}
}
}
pub async fn update(pool: &SqlitePool, id: i64, c: &Update) -> bool {
let res = sqlx::query(
r#"
UPDATE
categories
SET
name = ?,
color = ?,
updated_at = datetime()
WHERE
id = ?
"#,
)
.bind(c.name.clone())
.bind(c.color.clone())
.bind(id)
.execute(pool)
.await;
match res {
Ok(_) => true,
Err(err) => {
log::error!("Error updating category {}: {:?}", id, err);
false
}
}
}
pub async fn delete(pool: &SqlitePool, id: i64) -> bool {
let res = sqlx::query(
r#"
UPDATE
categories
SET
deleted_at = datetime()
WHERE
id = ?
"#,
)
.bind(id)
.execute(pool)
.await;
match res {
Ok(_) => true,
Err(err) => {
log::error!("Error deleting category {}: {:?}", id, err);
false
}
}
}
|
