cron: scope jobs to the guild that created them

1 files changed, 35 insertions, 15 deletions

diff --git a/src/database/mod.rs b/src/database/mod.rs
index b68d927..9679811 100644
--- a/src/database/mod.rs
+++ b/src/database/mod.rs
@@ -55,17 +55,22 @@ async fn run_migrations(pool: &DbPool) -> Result<()> {
             audio_filename TEXT,
             message TEXT,
             created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-            last_run TIMESTAMP
+            last_run TIMESTAMP,
+            guild_id INTEGER NOT NULL DEFAULT 0
         )",
     )
     .execute(pool)
     .await?;
 
-    // Add last_run column for pre-existing databases that lack it.
+    // Add columns for pre-existing databases that lack them.
     sqlx::query("ALTER TABLE cron_jobs ADD COLUMN last_run TIMESTAMP")
         .execute(pool)
         .await
         .ok();
+    sqlx::query("ALTER TABLE cron_jobs ADD COLUMN guild_id INTEGER NOT NULL DEFAULT 0")
+        .execute(pool)
+        .await
+        .ok();
 
     sqlx::query(
         "CREATE TABLE IF NOT EXISTS triggers (
@@ -305,6 +310,8 @@ pub async fn get_user_rank(pool: &DbPool, user_id: &str, metric: &str) -> Result
 #[derive(Debug, Clone, FromRow)]
 pub struct CronJob {
     pub id: i64,
+    #[allow(dead_code)]
+    pub guild_id: i64,
     pub cron_expression: String,
     pub r#type: String,
     pub channel_id: i64,
@@ -317,10 +324,13 @@ pub struct CronJob {
 }
 
 #[allow(dead_code)]
-pub async fn list_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
-    Ok(sqlx::query_as("SELECT * FROM cron_jobs ORDER BY id")
-        .fetch_all(pool)
-        .await?)
+pub async fn list_cron_jobs(pool: &DbPool, guild_id: i64) -> Result<Vec<CronJob>> {
+    Ok(
+        sqlx::query_as("SELECT * FROM cron_jobs WHERE guild_id = ? ORDER BY id")
+            .bind(guild_id)
+            .fetch_all(pool)
+            .await?,
+    )
 }
 
 #[allow(dead_code)]
@@ -332,6 +342,7 @@ pub async fn list_active_cron_jobs(pool: &DbPool) -> Result<Vec<CronJob>> {
 
 pub async fn add_cron_job(
     pool: &DbPool,
+    guild_id: i64,
     cron_expression: &str,
     r#type: &str,
     channel_id: i64,
@@ -353,9 +364,10 @@ pub async fn add_cron_job(
     }).unwrap_or(existing.len() as i64 + 1);
 
     let result = sqlx::query(
-        "INSERT INTO cron_jobs (id, cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?, ?)",
+        "INSERT INTO cron_jobs (id, guild_id, cron_expression, type, channel_id, audio_filename, message) VALUES (?, ?, ?, ?, ?, ?, ?)",
     )
     .bind(next_id)
+    .bind(guild_id)
     .bind(cron_expression)
     .bind(r#type)
     .bind(channel_id)
@@ -366,9 +378,10 @@ pub async fn add_cron_job(
     Ok(result.last_insert_rowid())
 }
 
-pub async fn delete_cron_job(pool: &DbPool, id: i64) -> Result<bool> {
-    let result = sqlx::query("DELETE FROM cron_jobs WHERE id = ?")
+pub async fn delete_cron_job(pool: &DbPool, id: i64, guild_id: i64) -> Result<bool> {
+    let result = sqlx::query("DELETE FROM cron_jobs WHERE id = ? AND guild_id = ?")
         .bind(id)
+        .bind(guild_id)
         .execute(pool)
         .await?;
     Ok(result.rows_affected() > 0)
@@ -383,12 +396,19 @@ pub async fn update_cron_last_run(pool: &DbPool, id: i64, last_run: DateTime<Utc
     Ok(())
 }
 
-pub async fn set_cron_job_active(pool: &DbPool, id: i64, active: bool) -> Result<bool> {
-    let result = sqlx::query("UPDATE cron_jobs SET active = ? WHERE id = ?")
-        .bind(if active { 1 } else { 0 })
-        .bind(id)
-        .execute(pool)
-        .await?;
+pub async fn set_cron_job_active(
+    pool: &DbPool,
+    id: i64,
+    guild_id: i64,
+    active: bool,
+) -> Result<bool> {
+    let result =
+        sqlx::query("UPDATE cron_jobs SET active = ? WHERE id = ? AND guild_id = ?")
+            .bind(if active { 1 } else { 0 })
+            .bind(id)
+            .bind(guild_id)
+            .execute(pool)
+            .await?;
     Ok(result.rows_affected() > 0)
 }