Skip to content
1        10        20        30        40        50        60        70        80        90        1001use std::path::Path;2use std::sync::Arc;3use std::time::Duration;4use tokio::sync::OnceCell;5use turso::Builder;67use super::{ColumnInfo, TablePreview};89#[derive(Clone)]10pub struct SqliteDb {11    db: Arc<OnceCell<turso::Database>>,12    db_path: String,13}1415impl SqliteDb {16    #[must_use]17    pub fn new(db_path: &str) -> Self {18        Self {19            db: Arc::new(OnceCell::new()),20            db_path: db_path.to_owned(),21        }22    }2324    #[must_use]25    pub fn db_path(&self) -> &str {26        &self.db_path27    }2829    pub async fn conn(&self) -> Result<turso::Connection, String> {30        let db = self31            .db32            .get_or_try_init(|| async {33                let db_path = Path::new(&self.db_path);34                if let Some(parent) = db_path35                    .parent()36                    .filter(|p| !p.as_os_str().is_empty() && !p.exists())37                {38                    let _ = std::fs::create_dir_all(parent);39                }4041                let db = Builder::new_local(&self.db_path)42                    .build()43                    .await44                    .map_err(|e| {45                        format!("Failed to create local SQLite db at {}: {e}", self.db_path)46                    })?;4748                Ok::<turso::Database, String>(db)49            })50            .await?;5152        let conn = db53            .connect()54            .map_err(|e| format!("Failed to connect to SQLite: {e}"))?;55        conn.busy_timeout(Duration::from_secs(30))56            .map_err(|e| format!("Failed to set busy timeout: {e}"))?;57        Ok(conn)58    }5960    pub async fn init(&self) -> Result<(), String> {61        let conn = self.conn().await?;62        conn.query("PRAGMA journal_mode = WAL;", ())63            .await64            .map_err(|e| format!("Failed to set WAL mode: {e}"))?;6566        conn.execute(67            "CREATE TABLE IF NOT EXISTS tournaments (68                id INTEGER PRIMARY KEY AUTOINCREMENT,69                name TEXT NOT NULL,70                status TEXT NOT NULL DEFAULT 'draft',71                format TEXT NOT NULL DEFAULT 'elimination',72                turn_time_seconds INTEGER NOT NULL DEFAULT 20,73                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP74            );",75            (),76        )77        .await78        .map_err(|e| format!("Failed to create tournaments table: {e}"))?;7980        conn.execute(81            "CREATE TABLE IF NOT EXISTS participants (82                id INTEGER PRIMARY KEY AUTOINCREMENT,83                tournament_id INTEGER NOT NULL,84                player_name TEXT NOT NULL,85                pokemon_id INTEGER NOT NULL,86                pin TEXT NOT NULL,87                is_bot INTEGER NOT NULL DEFAULT 0,88                status TEXT NOT NULL DEFAULT 'registered',89                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP90            );",91            (),92        )93        .await94        .map_err(|e| format!("Failed to create participants table: {e}"))?;9596        conn.execute(97            "CREATE TABLE IF NOT EXISTS battles (98                id INTEGER PRIMARY KEY AUTOINCREMENT,99                tournament_id INTEGER NOT NULL,100                round INTEGER NOT NULL DEFAULT 1,101                status TEXT NOT NULL DEFAULT 'pending',102                player1_name TEXT NOT NULL,103                player2_name TEXT NOT NULL,104                winner_name TEXT,105                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP106            );",107            (),108        )109        .await110        .map_err(|e| format!("Failed to create battles table: {e}"))?;111112        conn.execute(113            "CREATE TABLE IF NOT EXISTS battle_moves (114                id INTEGER PRIMARY KEY AUTOINCREMENT,115                battle_id INTEGER NOT NULL,116                turn_number INTEGER NOT NULL,117                actor TEXT NOT NULL,118                move_name TEXT NOT NULL,119                damage INTEGER NOT NULL DEFAULT 0,120                message TEXT NOT NULL,121                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP122            );",123            (),124        )125        .await126        .map_err(|e| format!("Failed to create battle_moves table: {e}"))?;127128        log::info!("SQLite schema initialized successfully");129        Ok(())130    }131132    pub async fn list_tables(&self) -> Result<Vec<String>, String> {133        let conn = self.conn().await?;134        let mut rows = conn135            .query(136                "SELECT name FROM sqlite_master137                 WHERE type='table'138                   AND name NOT LIKE 'sqlite_%'139                   AND name NOT LIKE '_litestream_%'140                 ORDER BY name;",141                (),142            )143            .await144            .map_err(|e| format!("Failed to list sqlite tables: {e}"))?;145146        let mut tables = Vec::new();147        while let Some(row) = rows148            .next()149            .await150            .map_err(|e| format!("Failed to read table row: {e}"))?151        {152            let name: String = row153                .get(0)154                .map_err(|e| format!("Failed to get table name: {e}"))?;155            tables.push(name);156        }157        Ok(tables)158    }159160    pub async fn describe_table(&self, table: &str) -> Result<Vec<ColumnInfo>, String> {161        if !table.chars().all(|c| c.is_alphanumeric() || c == '_') {162            return Err("Invalid table name".to_string());163        }164165        let conn = self.conn().await?;166        let pragma_sql = format!("PRAGMA table_info({table});");167        let mut rows = conn168            .query(&pragma_sql, ())169            .await170            .map_err(|e| format!("Failed to get schema for {table}: {e}"))?;171172        let mut columns = Vec::new();173        while let Some(row) = rows174            .next()175            .await176            .map_err(|e| format!("Failed to read column row: {e}"))?177        {178            // pragma table_info columns: cid(0), name(1), type(2), notnull(3), dflt_value(4), pk(5)179            let name: String = row.get(1).unwrap_or_default();180            let data_type: String = row.get(2).unwrap_or_default();181            let notnull: i64 = row.get(3).unwrap_or(0);182            let pk: i64 = row.get(5).unwrap_or(0);183184            columns.push(ColumnInfo {185                name,186                data_type,187                is_nullable: notnull == 0,188                is_pk: pk > 0,189            });190        }191        Ok(columns)192    }193194    pub async fn table_row_count(&self, table: &str) -> Result<i64, String> {195        if !table.chars().all(|c| c.is_alphanumeric() || c == '_') {196            return Err("Invalid table name".to_string());197        }198199        let conn = self.conn().await?;200        let count_sql = format!("SELECT count(*) FROM {table};");201        let mut rows = conn202            .query(&count_sql, ())203            .await204            .map_err(|e| format!("Failed to get count for {table}: {e}"))?;205206        if let Some(row) = rows207            .next()208            .await209            .map_err(|e| format!("Failed to read count row: {e}"))?210        {211            let count: i64 = row.get(0).unwrap_or(0);212            Ok(count)213        } else {214            Ok(0)215        }216    }217218    pub async fn preview_table(&self, table: &str, limit: usize) -> Result<TablePreview, String> {219        if !table.chars().all(|c| c.is_alphanumeric() || c == '_') {220            return Err("Invalid table name".to_string());221        }222223        let total_rows = self.table_row_count(table).await?;224        let columns_info = self.describe_table(table).await?;225        let col_names: Vec<String> = columns_info.into_iter().map(|c| c.name).collect();226227        let conn = self.conn().await?;228        let preview_sql = format!("SELECT * FROM {table} LIMIT {limit};");229        let mut rows = conn230            .query(&preview_sql, ())231            .await232            .map_err(|e| format!("Failed to query preview for {table}: {e}"))?;233234        let mut data_rows = Vec::new();235        while let Some(row) = rows236            .next()237            .await238            .map_err(|e| format!("Failed to read preview row: {e}"))?239        {240            let mut row_vals = Vec::new();241            for (idx, _col) in col_names.iter().enumerate() {242                // Try reading as string, int, or float243                let val_str = if let Ok(s) = row.get::<String>(idx) {244                    s245                } else if let Ok(i) = row.get::<i64>(idx) {246                    i.to_string()247                } else if let Ok(f) = row.get::<f64>(idx) {248                    format!("{f:.2}")249                } else {250                    "NULL".to_string()251                };252                row_vals.push(val_str);253            }254            data_rows.push(row_vals);255        }256257        Ok(TablePreview {258            table_name: table.to_string(),259            total_rows,260            columns: col_names,261            rows: data_rows,262        })263    }264}265266#[cfg(test)]267mod tests {268    use super::*;269270    #[tokio::test]271    async fn test_sqlite_db_init_and_inspection() {272        let temp_path = format!(273            "/tmp/test_sqlite_{}.db",274            std::time::SystemTime::now()275                .duration_since(std::time::UNIX_EPOCH)276                .unwrap()277                .as_nanos()278        );279280        let db = SqliteDb::new(&temp_path);281        db.init().await.expect("init sqlite");282283        let tables = db.list_tables().await.expect("list tables");284        assert!(tables.contains(&"tournaments".to_string()));285        assert!(tables.contains(&"battles".to_string()));286        assert!(tables.contains(&"participants".to_string()));287        assert!(tables.contains(&"battle_moves".to_string()));288289        let cols = db.describe_table("tournaments").await.expect("describe");290        assert!(cols.iter().any(|c| c.name == "turn_time_seconds"));291292        let count = db.table_row_count("tournaments").await.expect("count");293        assert_eq!(count, 0);294295        let preview = db.preview_table("tournaments", 10).await.expect("preview");296        assert_eq!(preview.total_rows, 0);297        assert!(!preview.columns.is_empty());298299        let _ = std::fs::remove_file(&temp_path);300    }301}