From cd8ed8bc5793985df2c1692072a932ceee517b95 Mon Sep 17 00:00:00 2001 From: salmonik Date: Wed, 8 Jul 2026 02:13:35 +0200 Subject: OPEN THE RUST GATES Signed-off-by: salmonik --- src/database/mod.rs | 344 ++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 344 insertions(+) create mode 100644 src/database/mod.rs (limited to 'src/database/mod.rs') 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 @@ +use chrono::{DateTime, Utc}; +use sqlx::FromRow; +use sqlx::SqlitePool; + +use crate::error::{Error, Result}; + +pub type DbPool = SqlitePool; + +pub async fn init_pool(path: &str) -> Result { + if let Some(parent) = std::path::Path::new(path).parent() { + tokio::fs::create_dir_all(parent).await.map_err(Error::Io)?; + } + let pool = SqlitePool::connect(&format!("sqlite:{path}?mode=rwc")).await?; + run_migrations(&pool).await?; + Ok(pool) +} + +async fn run_migrations(pool: &DbPool) -> Result<()> { + sqlx::query( + "CREATE TABLE IF NOT EXISTS settings ( + key TEXT PRIMARY KEY, + value TEXT NOT NULL + )", + ) + .execute(pool) + .await?; + + sqlx::query( + "CREATE TABLE IF NOT EXISTS voice_activity ( + user_id TEXT PRIMARY KEY, + join_time REAL NOT NULL DEFAULT 0, + total_active REAL NOT NULL DEFAULT 0, + total_afk REAL NOT NULL DEFAULT 0, + is_afk INTEGER NOT NULL DEFAULT 0, + afk_start REAL NOT NULL DEFAULT 0, + active_start REAL NOT NULL DEFAULT 0, + channel_name TEXT NOT NULL DEFAULT '', + last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )", + ) + .execute(pool) + .await?; + + sqlx::query( + "CREATE TABLE IF NOT EXISTS cron_jobs ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + cron_expression TEXT NOT NULL, + type TEXT NOT NULL CHECK(type IN ('audio', 'wiadomość')), + channel_id INTEGER NOT NULL, + active INTEGER NOT NULL DEFAULT 1, + audio_filename TEXT, + message TEXT, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )", + ) + .execute(pool) + .await?; + + sqlx::query( + "CREATE TABLE IF NOT EXISTS triggers ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + name TEXT UNIQUE NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )", + ) + .execute(pool) + .await?; + + sqlx::query( + "CREATE TABLE IF NOT EXISTS trigger_keywords ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + trigger_id INTEGER NOT NULL REFERENCES triggers(id) ON DELETE CASCADE, + keyword TEXT NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + UNIQUE(trigger_id, keyword) + )", + ) + .execute(pool) + .await?; + + sqlx::query( + "CREATE TABLE IF NOT EXISTS trigger_responses ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + trigger_id INTEGER NOT NULL REFERENCES triggers(id) ON DELETE CASCADE, + response_text TEXT NOT NULL, + created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP + )", + ) + .execute(pool) + .await?; + + Ok(()) +} + +// -- Voice Activity -- +#[derive(Debug, Clone, FromRow)] +pub struct VoiceActivity { + pub user_id: String, + pub join_time: f64, + pub total_active: f64, + pub total_afk: f64, + pub is_afk: i32, + pub afk_start: f64, + pub active_start: f64, + pub channel_name: String, + #[allow(dead_code)] + pub last_updated: Option>, +} + +#[allow(dead_code)] +pub async fn get_voice_activity(pool: &DbPool, user_id: &str) -> Result> { + Ok( + sqlx::query_as("SELECT * FROM voice_activity WHERE user_id = ?") + .bind(user_id) + .fetch_optional(pool) + .await?, + ) +} + +pub async fn upsert_voice_activity(pool: &DbPool, va: &VoiceActivity) -> Result<()> { + sqlx::query( + "INSERT INTO voice_activity (user_id, join_time, total_active, total_afk, is_afk, afk_start, active_start, channel_name) + VALUES (?, ?, ?, ?, ?, ?, ?, ?) + ON CONFLICT(user_id) DO UPDATE SET + join_time = excluded.join_time, + total_active = excluded.total_active, + total_afk = excluded.total_afk, + is_afk = excluded.is_afk, + afk_start = excluded.afk_start, + active_start = excluded.active_start, + channel_name = excluded.channel_name, + last_updated = CURRENT_TIMESTAMP", + ) + .bind(&va.user_id) + .bind(va.join_time) + .bind(va.total_active) + .bind(va.total_afk) + .bind(va.is_afk) + .bind(va.afk_start) + .bind(va.active_start) + .bind(&va.channel_name) + .execute(pool) + .await?; + Ok(()) +} + +// -- Cron Jobs -- +#[derive(Debug, Clone, FromRow)] +pub struct CronJob { + pub id: i64, + pub cron_expression: String, + pub r#type: String, + pub channel_id: i64, + pub active: i32, + pub audio_filename: Option, + pub message: Option, + #[allow(dead_code)] + pub created_at: Option>, +} + +#[allow(dead_code)] +pub async fn list_cron_jobs(pool: &DbPool) -> Result> { + Ok(sqlx::query_as("SELECT * FROM cron_jobs ORDER BY id") + .fetch_all(pool) + .await?) +} + +#[allow(dead_code)] +pub async fn list_active_cron_jobs(pool: &DbPool) -> Result> { + Ok(sqlx::query_as("SELECT * FROM cron_jobs WHERE active = 1") + .fetch_all(pool) + .await?) +} + +pub async fn add_cron_job( + pool: &DbPool, + cron_expression: &str, + r#type: &str, + channel_id: i64, + audio_filename: Option<&str>, + message: Option<&str>, +) -> Result { + let result = sqlx::query( + "INSERT INTO cron_jobs (cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?)", + ) + .bind(cron_expression) + .bind(r#type) + .bind(channel_id) + .bind(audio_filename) + .bind(message) + .execute(pool) + .await?; + Ok(result.last_insert_rowid()) +} + +pub async fn delete_cron_job(pool: &DbPool, id: i64) -> Result { + let result = sqlx::query("DELETE FROM cron_jobs WHERE id = ?") + .bind(id) + .execute(pool) + .await?; + Ok(result.rows_affected() > 0) +} + +pub async fn set_cron_job_active(pool: &DbPool, id: i64, active: bool) -> Result { + let result = sqlx::query("UPDATE cron_jobs SET active = ? WHERE id = ?") + .bind(if active { 1 } else { 0 }) + .bind(id) + .execute(pool) + .await?; + Ok(result.rows_affected() > 0) +} + +// -- Triggers -- +#[derive(Debug, Clone, FromRow)] +pub struct Trigger { + pub id: i64, + pub name: String, + pub created_at: Option>, + pub last_updated: Option>, +} + +#[allow(dead_code)] +#[derive(Debug, Clone, FromRow)] +pub struct TriggerKeyword { + pub id: i64, + pub trigger_id: i64, + pub keyword: String, +} + +#[allow(dead_code)] +#[derive(Debug, Clone, FromRow)] +pub struct TriggerResponse { + pub id: i64, + pub trigger_id: i64, + pub response_text: String, +} + +pub async fn create_trigger(pool: &DbPool, name: &str) -> Result { + let result = sqlx::query("INSERT INTO triggers (name) VALUES (?)") + .bind(name) + .execute(pool) + .await?; + Ok(result.last_insert_rowid()) +} + +pub async fn delete_trigger(pool: &DbPool, id: i64) -> Result { + let result = sqlx::query("DELETE FROM triggers WHERE id = ?") + .bind(id) + .execute(pool) + .await?; + Ok(result.rows_affected() > 0) +} + +pub async fn get_trigger_by_name(pool: &DbPool, name: &str) -> Result> { + Ok(sqlx::query_as("SELECT * FROM triggers WHERE name = ?") + .bind(name) + .fetch_optional(pool) + .await?) +} + +pub async fn list_triggers(pool: &DbPool) -> Result> { + Ok(sqlx::query_as("SELECT * FROM triggers ORDER BY name") + .fetch_all(pool) + .await?) +} + +pub async fn add_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result<()> { + sqlx::query("INSERT OR IGNORE INTO trigger_keywords (trigger_id, keyword) VALUES (?, ?)") + .bind(trigger_id) + .bind(keyword) + .execute(pool) + .await?; + update_trigger_timestamp(pool, trigger_id).await +} + +pub async fn remove_keyword(pool: &DbPool, trigger_id: i64, keyword: &str) -> Result { + let result = sqlx::query("DELETE FROM trigger_keywords WHERE trigger_id = ? AND keyword = ?") + .bind(trigger_id) + .bind(keyword) + .execute(pool) + .await?; + update_trigger_timestamp(pool, trigger_id).await?; + Ok(result.rows_affected() > 0) +} + +pub async fn add_response(pool: &DbPool, trigger_id: i64, response: &str) -> Result<()> { + sqlx::query("INSERT INTO trigger_responses (trigger_id, response_text) VALUES (?, ?)") + .bind(trigger_id) + .bind(response) + .execute(pool) + .await?; + update_trigger_timestamp(pool, trigger_id).await +} + +pub async fn remove_response(pool: &DbPool, trigger_id: i64, response: &str) -> Result { + let result = + sqlx::query("DELETE FROM trigger_responses WHERE trigger_id = ? AND response_text = ?") + .bind(trigger_id) + .bind(response) + .execute(pool) + .await?; + update_trigger_timestamp(pool, trigger_id).await?; + Ok(result.rows_affected() > 0) +} + +pub async fn get_keywords(pool: &DbPool, trigger_id: i64) -> Result> { + let rows: Vec<(String,)> = + sqlx::query_as("SELECT keyword FROM trigger_keywords WHERE trigger_id = ?") + .bind(trigger_id) + .fetch_all(pool) + .await?; + Ok(rows.into_iter().map(|r| r.0).collect()) +} + +pub async fn get_responses(pool: &DbPool, trigger_id: i64) -> Result> { + let rows: Vec<(String,)> = + sqlx::query_as("SELECT response_text FROM trigger_responses WHERE trigger_id = ?") + .bind(trigger_id) + .fetch_all(pool) + .await?; + Ok(rows.into_iter().map(|r| r.0).collect()) +} + +pub async fn load_all_triggers_with_keywords_and_responses( + pool: &DbPool, +) -> Result, Vec)>> { + let triggers = list_triggers(pool).await?; + let mut result = Vec::new(); + for t in triggers { + let keywords = get_keywords(pool, t.id).await?; + let responses = get_responses(pool, t.id).await?; + result.push((t, keywords, responses)); + } + Ok(result) +} + +async fn update_trigger_timestamp(pool: &DbPool, id: i64) -> Result<()> { + sqlx::query("UPDATE triggers SET last_updated = CURRENT_TIMESTAMP WHERE id = ?") + .bind(id) + .execute(pool) + .await?; + Ok(()) +} -- cgit v1.2.3