aboutsummaryrefslogtreecommitdiffstats
path: root/src/database
diff options
context:
space:
mode:
Diffstat (limited to 'src/database')
-rw-r--r--src/database/mod.rs344
1 files changed, 344 insertions, 0 deletions
diff --git a/src/database/mod.rs b/src/database/mod.rs
new file mode 100644
index 0000000..53f4bf4
--- /dev/null
+++ b/src/database/mod.rs
@@ -0,0 +1,344 @@
1use chrono::{DateTime, Utc};
2use sqlx::FromRow;
3use sqlx::SqlitePool;
4
5use crate::error::{Error, Result};
6
7pub type DbPool = SqlitePool;
8
9pub async fn init_pool(path: &str) -> Result<DbPool> {
10 if let Some(parent) = std::path::Path::new(path).parent() {
11 tokio::fs::create_dir_all(parent).await.map_err(Error::Io)?;
12 }
13 let pool = SqlitePool::connect(&format!("sqlite:{path}?mode=rwc")).await?;
14 run_migrations(&pool).await?;
15 Ok(pool)
16}
17
18async fn run_migrations(pool: &DbPool) -> Result<()> {
19 sqlx::query(
20 "CREATE TABLE IF NOT EXISTS settings (
21 key TEXT PRIMARY KEY,
22 value TEXT NOT NULL
23 )",
24 )
25 .execute(pool)
26 .await?;
27
28 sqlx::query(
29 "CREATE TABLE IF NOT EXISTS voice_activity (
30 user_id TEXT PRIMARY KEY,
31 join_time REAL NOT NULL DEFAULT 0,
32 total_active REAL NOT NULL DEFAULT 0,
33 total_afk REAL NOT NULL DEFAULT 0,
34 is_afk INTEGER NOT NULL DEFAULT 0,
35 afk_start REAL NOT NULL DEFAULT 0,
36 active_start REAL NOT NULL DEFAULT 0,
37 channel_name TEXT NOT NULL DEFAULT '',
38 last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
39 )",
40 )
41 .execute(pool)
42 .await?;
43
44 sqlx::query(
45 "CREATE TABLE IF NOT EXISTS cron_jobs (
46 id INTEGER PRIMARY KEY AUTOINCREMENT,
47 cron_expression TEXT NOT NULL,
48 type TEXT NOT NULL CHECK(type IN ('audio', 'wiadomość')),
49 channel_id INTEGER NOT NULL,
50 active INTEGER NOT NULL DEFAULT 1,
51 audio_filename TEXT,
52 message TEXT,
53 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
54 )",
55 )
56 .execute(pool)
57 .await?;
58
59 sqlx::query(
60 "CREATE TABLE IF NOT EXISTS triggers (
61 id INTEGER PRIMARY KEY AUTOINCREMENT,
62 name TEXT UNIQUE NOT NULL,
63 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
64 last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
65 )",
66 )
67 .execute(pool)
68 .await?;
69
70 sqlx::query(
71 "CREATE TABLE IF NOT EXISTS trigger_keywords (
72 id INTEGER PRIMARY KEY AUTOINCREMENT,
73 trigger_id INTEGER NOT NULL REFERENCES triggers(id) ON DELETE CASCADE,
74 keyword TEXT NOT NULL,
75 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
76 UNIQUE(trigger_id, keyword)
77 )",
78 )
79 .execute(pool)
80 .await?;
81
82 sqlx::query(
83 "CREATE TABLE IF NOT EXISTS trigger_responses (
84 id INTEGER PRIMARY KEY AUTOINCREMENT,
85 trigger_id INTEGER NOT NULL REFERENCES triggers(id) ON DELETE CASCADE,
86 response_text TEXT NOT NULL,
87 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
88 )",
89 )
90 .execute(pool)
91 .await?;
92
93 Ok(())
94}
95
96// -- Voice Activity --
97#[derive(Debug, Clone, FromRow)]
98pub struct VoiceActivity {
99 pub user_id: String,
100 pub join_time: f64,
101 pub total_active: f64,
102 pub total_afk: f64,
103 pub is_afk: i32,
104 pub afk_start: f64,
105 pub active_start: f64,
106 pub channel_name: String,
107 #[allow(dead_code)]
108 pub last_updated: Option<DateTime<Utc>>,
109}
110
111#[allow(dead_code)]
112pub async fn get_voice_activity(pool: &DbPool, user_id: &str) -> Result<Option<VoiceActivity>> {
113 Ok(
114 sqlx::query_as("SELECT * FROM voice_activity WHERE user_id = ?")
115 .bind(user_id)
116 .fetch_optional(pool)
117 .await?,
118 )
119}
120
121pub async fn upsert_voice_activity(pool: &DbPool, va: &VoiceActivity) -> Result<()> {
122 sqlx::query(
123 "INSERT INTO voice_activity (user_id, join_time, total_active, total_afk, is_afk, afk_start, active_start, channel_name)
124 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
125 ON CONFLICT(user_id) DO UPDATE SET
126 join_time = excluded.join_time,
127 total_active = excluded.total_active,
128 total_afk = excluded.total_afk,
129 is_afk = excluded.is_afk,
130 afk_start = excluded.afk_start,
131 active_start = excluded.active_start,
132 channel_name = excluded.channel_name,
133 last_updated = CURRENT_TIMESTAMP",
134 )
135 .bind(&va.user_id)
136 .bind(va.join_time)
137 .bind(va.total_active)
138 .bind(va.total_afk)
139 .bind(va.is_afk)
140 .bind(va.afk_start)
141 .bind(va.active_start)
142 .bind(&va.channel_name)
143 .execute(pool)
144 .await?;
145 Ok(())
146}
147
148// -- Cron Jobs --
149#[derive(Debug, Clone, FromRow)]
150pub struct CronJob {
151 pub id: i64,
152 pub cron_expression: String,
153 pub r#type: String,
154 pub channel_id: i64,
155 pub active: i32,
156 pub audio_filename: Option<String>,
157 pub message: Option<String>,
158 #[allow(dead_code)]
159 pub created_at: Option<DateTime<Utc>>,
160}
161
162#[allow(dead_code)]
163pub async fn list_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
164 Ok(sqlx::query_as("SELECT * FROM cron_jobs ORDER BY id")
165 .fetch_all(pool)
166 .await?)
167}
168
169#[allow(dead_code)]
170pub async fn list_active_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
171 Ok(sqlx::query_as("SELECT * FROM cron_jobs WHERE active = 1")
172 .fetch_all(pool)
173 .await?)
174}
175
176pub async fn add_cron_job(
177 pool: &DbPool,
178 cron_expression: &str,
179 r#type: &str,
180 channel_id: i64,
181 audio_filename: Option<&str>,
182 message: Option<&str>,
183) -> Result<i64> {
184 let result = sqlx::query(
185 "INSERT INTO cron_jobs (cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?)",
186 )
187 .bind(cron_expression)
188 .bind(r#type)
189 .bind(channel_id)
190 .bind(audio_filename)
191 .bind(message)
192 .execute(pool)
193 .await?;
194 Ok(result.last_insert_rowid())
195}
196
197pub async fn delete_cron_job(pool: &DbPool, id: i64) -> Result<bool> {
198 let result = sqlx::query("DELETE FROM cron_jobs WHERE id = ?")
199 .bind(id)
200 .execute(pool)
201 .await?;
202 Ok(result.rows_affected() > 0)
203}
204
205pub async fn set_cron_job_active(pool: &DbPool, id: i64, active: bool) -> Result<bool> {
206 let result = sqlx::query("UPDATE cron_jobs SET active = ? WHERE id = ?")
207 .bind(if active { 1 } else { 0 })
208 .bind(id)
209 .execute(pool)
210 .await?;
211 Ok(result.rows_affected() > 0)
212}
213
214// -- Triggers --
215#[derive(Debug, Clone, FromRow)]
216pub struct Trigger {
217 pub id: i64,
218 pub name: String,
219 pub created_at: Option<DateTime<Utc>>,
220 pub last_updated: Option<DateTime<Utc>>,
221}
222
223#[allow(dead_code)]
224#[derive(Debug, Clone, FromRow)]
225pub struct TriggerKeyword {
226 pub id: i64,
227 pub trigger_id: i64,
228 pub keyword: String,
229}
230
231#[allow(dead_code)]
232#[derive(Debug, Clone, FromRow)]
233pub struct TriggerResponse {
234 pub id: i64,
235 pub trigger_id: i64,
236 pub response_text: String,
237}
238
239pub async fn create_trigger(pool: &DbPool, name: &str) -> Result<i64> {
240 let result = sqlx::query("INSERT INTO triggers (name) VALUES (?)")
241 .bind(name)
242 .execute(pool)
243 .await?;
244 Ok(result.last_insert_rowid())
245}
246
247pub async fn delete_trigger(pool: &DbPool, id: i64) -> Result<bool> {
248 let result = sqlx::query("DELETE FROM triggers WHERE id = ?")
249 .bind(id)
250 .execute(pool)
251 .await?;
252 Ok(result.rows_affected() > 0)
253}
254
255pub async fn get_trigger_by_name(pool: &DbPool, name: &str) -> Result<Option<Trigger>> {
256 Ok(sqlx::query_as("SELECT * FROM triggers WHERE name = ?")
257 .bind(name)
258 .fetch_optional(pool)
259 .await?)
260}
261
262pub async fn list_triggers(pool: &DbPool) -> Result<Vec<Trigger>> {
263 Ok(sqlx::query_as("SELECT * FROM triggers ORDER BY name")
264 .fetch_all(pool)
265 .await?)
266}
267
268pub async fn add_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result<()> {
269 sqlx::query("INSERT OR IGNORE INTO trigger_keywords (trigger_id, keyword) VALUES (?, ?)")
270 .bind(trigger_id)
271 .bind(keyword)
272 .execute(pool)
273 .await?;
274 update_trigger_timestamp(pool, trigger_id).await
275}
276
277pub async fn remove_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result<bool> {
278 let result = sqlx::query("DELETE FROM trigger_keywords WHERE trigger_id = ? AND keyword = ?")
279 .bind(trigger_id)
280 .bind(keyword)
281 .execute(pool)
282 .await?;
283 update_trigger_timestamp(pool, trigger_id).await?;
284 Ok(result.rows_affected() > 0)
285}
286
287pub async fn add_response(pool: &DbPool, trigger_id: i64, response: &str) -> Result<()> {
288 sqlx::query("INSERT INTO trigger_responses (trigger_id, response_text) VALUES (?, ?)")
289 .bind(trigger_id)
290 .bind(response)
291 .execute(pool)
292 .await?;
293 update_trigger_timestamp(pool, trigger_id).await
294}
295
296pub async fn remove_response(pool: &DbPool, trigger_id: i64, response: &str) -> Result<bool> {
297 let result =
298 sqlx::query("DELETE FROM trigger_responses WHERE trigger_id = ? AND response_text = ?")
299 .bind(trigger_id)
300 .bind(response)
301 .execute(pool)
302 .await?;
303 update_trigger_timestamp(pool, trigger_id).await?;
304 Ok(result.rows_affected() > 0)
305}
306
307pub async fn get_keywords(pool: &DbPool, trigger_id: i64) -> Result<Vec<String>> {
308 let rows: Vec<(String,)> =
309 sqlx::query_as("SELECT keyword FROM trigger_keywords WHERE trigger_id = ?")
310 .bind(trigger_id)
311 .fetch_all(pool)
312 .await?;
313 Ok(rows.into_iter().map(|r| r.0).collect())
314}
315
316pub async fn get_responses(pool: &DbPool, trigger_id: i64) -> Result<Vec<String>> {
317 let rows: Vec<(String,)> =
318 sqlx::query_as("SELECT response_text FROM trigger_responses WHERE trigger_id = ?")
319 .bind(trigger_id)
320 .fetch_all(pool)
321 .await?;
322 Ok(rows.into_iter().map(|r| r.0).collect())
323}
324
325pub async fn load_all_triggers_with_keywords_and_responses(
326 pool: &DbPool,
327) -> Result<Vec<(Trigger, Vec<String>, Vec<String>)>> {
328 let triggers = list_triggers(pool).await?;
329 let mut result = Vec::new();
330 for t in triggers {
331 let keywords = get_keywords(pool, t.id).await?;
332 let responses = get_responses(pool, t.id).await?;
333 result.push((t, keywords, responses));
334 }
335 Ok(result)
336}
337
338async fn update_trigger_timestamp(pool: &DbPool, id: i64) -> Result<()> {
339 sqlx::query("UPDATE triggers SET last_updated = CURRENT_TIMESTAMP WHERE id = ?")
340 .bind(id)
341 .execute(pool)
342 .await?;
343 Ok(())
344}
generated by cgit v1.2.3 (git 2.46.0) at 2026-11-08 12:12:11 +0000