Skip to content
1        10        20        30        40        50        60        70        80        901use crate::db::Database;23struct Migration {4    name: &'static str,5    sql: &'static str,6}78const MIGRATIONS: &[Migration] = &[9    Migration {10        name: "create_users_table",11        sql: r"CREATE TABLE IF NOT EXISTS users (12            id TEXT PRIMARY KEY,13            username TEXT UNIQUE NOT NULL,14            email TEXT,15            password_hash TEXT NOT NULL,16            created_at TEXT NOT NULL17        );",18    },19    Migration {20        name: "create_tokens_table",21        sql: r"CREATE TABLE IF NOT EXISTS tokens (22            token TEXT PRIMARY KEY,23            user_id TEXT NOT NULL,24            created_at TEXT NOT NULL,25            FOREIGN KEY (user_id) REFERENCES users(id)26        );",27    },28    Migration {29        name: "create_tokens_created_at_index",30        sql: r"CREATE INDEX IF NOT EXISTS idx_tokens_created_at ON tokens(created_at);",31    },32    Migration {33        name: "create_sessions_table",34        sql: r"CREATE TABLE IF NOT EXISTS sessions (35            session_key TEXT PRIMARY KEY,36            state TEXT NOT NULL,37            expires_at INTEGER NOT NULL38        );",39    },40    Migration {41        name: "create_sessions_expires_at_index",42        sql: r"CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);",43    },44    Migration {45        name: "drop_legacy_tokens_table",46        sql: r"DROP TABLE IF EXISTS tokens;",47    },48    Migration {49        name: "create_invites_table",50        sql: r"CREATE TABLE IF NOT EXISTS invites (51            token TEXT PRIMARY KEY,52            created_at TEXT NOT NULL53        );",54    },55    Migration {56        name: "create_tickets_table",57        sql: r"CREATE TABLE IF NOT EXISTS tickets (58            id TEXT PRIMARY KEY,59            title TEXT NOT NULL,60            description TEXT NOT NULL DEFAULT '',61            creator_id TEXT NOT NULL,62            created_at TEXT NOT NULL,63            FOREIGN KEY (creator_id) REFERENCES users(id)64        );",65    },66    Migration {67        name: "create_tags_table",68        sql: r"CREATE TABLE IF NOT EXISTS tags (id TEXT PRIMARY KEY, name TEXT UNIQUE NOT NULL);",69    },70    Migration {71        name: "create_ticket_tags_table",72        sql: r"CREATE TABLE IF NOT EXISTS ticket_tags (73            ticket_id TEXT NOT NULL, tag_id TEXT NOT NULL, PRIMARY KEY (ticket_id, tag_id),74            FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,75            FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE76        );",77    },78    Migration {79        name: "create_ticket_tags_index",80        sql: r"CREATE INDEX IF NOT EXISTS idx_ticket_tags_tag ON ticket_tags(tag_id);",81    },82    Migration {83        name: "add_quest_completion_fields",84        sql: r"ALTER TABLE tickets ADD COLUMN completed_at TEXT;",85    },86    Migration {87        name: "add_ticket_outcome_field",88        sql: r"ALTER TABLE tickets ADD COLUMN closed_as TEXT;",89    },90    Migration {91        name: "backfill_ticket_done_outcomes",92        sql: r"UPDATE tickets SET closed_as = 'done' WHERE completed_at IS NOT NULL;",93    },94];9596impl Database {97    pub async fn create_tables(&self) -> Result<(), String> {98        let conn = self.conn().await?;99100        conn.query("PRAGMA journal_mode = WAL;", ())101            .await102            .map_err(|e| format!("Failed to set WAL mode: {e}"))?;103104        conn.execute(105            r"CREATE TABLE IF NOT EXISTS _migrations (106                name TEXT PRIMARY KEY,107                applied_at TEXT NOT NULL DEFAULT (datetime('now'))108            );",109            (),110        )111        .await112        .map_err(|e| format!("Failed to create migrations table: {e}"))?;113114        let mut rows = conn115            .query("SELECT name FROM _migrations", ())116            .await117            .map_err(|e| format!("Failed to list applied migrations: {e}"))?;118119        let mut applied = Vec::new();120        while let Some(row) = rows.next().await.map_err(|e| e.to_string())? {121            applied.push(row.get::<String>(0).map_err(|e| e.to_string())?);122        }123124        for migration in MIGRATIONS {125            if applied.contains(&migration.name.to_string()) {126                continue;127            }128129            conn.execute("BEGIN IMMEDIATE", ())130                .await131                .map_err(|e| e.to_string())?;132133            let result: Result<bool, String> = async {134                let mut rows = conn135                    .query(136                        "SELECT 1 FROM _migrations WHERE name = ?1",137                        turso::params![migration.name],138                    )139                    .await140                    .map_err(|e| format!("Failed to check migration {}: {e}", migration.name))?;141142                if rows.next().await.map_err(|e| e.to_string())?.is_some() {143                    return Ok(false);144                }145146                conn.execute(migration.sql, ())147                    .await148                    .map_err(|e| format!("Migration {} failed: {e}", migration.name))?;149150                conn.execute(151                    "INSERT INTO _migrations (name) VALUES (?1)",152                    turso::params![migration.name],153                )154                .await155                .map_err(|e| format!("Failed to record migration {}: {e}", migration.name))?;156157                Ok(true)158            }159            .await;160161            match result {162                Ok(true) => {163                    conn.execute("COMMIT", ()).await.map_err(|e| {164                        format!("Failed to commit migration {}: {e}", migration.name)165                    })?;166                }167                Ok(false) => {168                    conn.execute("ROLLBACK", ()).await.map_err(|e| {169                        format!("Failed to rollback migration {}: {e}", migration.name)170                    })?;171                }172                Err(e) => {173                    conn.execute("ROLLBACK", ())174                        .await175                        .map_err(|re| format!("{e} (rollback also failed: {re})"))?;176                    return Err(e);177                }178            }179        }180181        Ok(())182    }183}184185#[cfg(test)]186mod tests {187    use super::*;188189    #[tokio::test]190    async fn test_create_tables_runs_all_migrations() {191        let db_path = format!("/tmp/test_pear_migrations_{}.db", uuid::Uuid::new_v4());192        let db = Database::new(&db_path);193194        assert!(db.create_tables().await.is_ok());195196        let mut rows = db197            .conn()198            .await199            .unwrap()200            .query(201                "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name",202                (),203            )204            .await205            .unwrap();206207        let mut tables = Vec::new();208        while let Some(row) = rows.next().await.unwrap() {209            tables.push(row.get::<String>(0).unwrap());210        }211212        for table in [213            "users",214            "sessions",215            "invites",216            "tickets",217            "tags",218            "ticket_tags",219            "_migrations",220        ] {221            assert!(tables.contains(&table.to_string()), "missing {table}");222        }223        assert!(!tables.contains(&"projects".to_string()));224        assert!(!tables.contains(&"ticket_projects".to_string()));225226        let mut columns = db227            .conn()228            .await229            .unwrap()230            .query("PRAGMA table_info(tickets)", ())231            .await232            .unwrap();233        let mut ticket_columns = Vec::new();234        while let Some(row) = columns.next().await.unwrap() {235            ticket_columns.push(row.get::<String>(1).unwrap());236        }237        assert!(ticket_columns.contains(&"closed_as".to_string()));238239        let _ = std::fs::remove_file(&db_path);240    }241242    #[tokio::test]243    async fn test_concurrent_create_tables_runs_migrations_once() {244        let db_path = format!(245            "/tmp/test_pear_concurrent_migrations_{}.db",246            uuid::Uuid::new_v4()247        );248        let db1 = Database::new(&db_path);249        let db2 = Database::new(&db_path);250251        let (result1, result2) = tokio::join!(db1.create_tables(), db2.create_tables());252        assert!(result1.is_ok(), "first create_tables failed: {result1:?}");253        assert!(result2.is_ok(), "second create_tables failed: {result2:?}");254255        let mut rows = db1256            .conn()257            .await258            .unwrap()259            .query(260                "SELECT name FROM _migrations WHERE name = 'add_ticket_outcome_field'",261                (),262            )263            .await264            .unwrap();265        assert!(rows.next().await.unwrap().is_some());266267        let _ = std::fs::remove_file(&db_path);268    }269}