howler_core/
migrations.rs

1use anyhow::{Context, Result};
2use rusqlite::{params, Connection};
3
4/// Database migration
5pub struct Migration {
6    pub version: i32,
7    pub name: &'static str,
8    pub sql: &'static str,
9}
10
11/// Get all migrations in order
12pub fn get_migrations() -> Vec<Migration> {
13    vec![
14        Migration {
15            version: 1,
16            name: "initial_schema",
17            sql: r#"
18                CREATE TABLE IF NOT EXISTS sightings (
19                    id INTEGER PRIMARY KEY AUTOINCREMENT,
20                    species TEXT NOT NULL,
21                    scientific_name TEXT,
22                    latitude REAL NOT NULL,
23                    longitude REAL NOT NULL,
24                    observed_on TEXT NOT NULL,
25                    source TEXT NOT NULL,
26                    source_id TEXT NOT NULL,
27                    details TEXT,
28                    UNIQUE(source, source_id)
29                );
30
31                CREATE TABLE IF NOT EXISTS species_status (
32                    id INTEGER PRIMARY KEY AUTOINCREMENT,
33                    scientific_name TEXT NOT NULL UNIQUE,
34                    common_name TEXT,
35                    red_list_category TEXT,
36                    population_trend TEXT,
37                    threats TEXT
38                );
39
40                CREATE INDEX IF NOT EXISTS idx_sightings_species ON sightings(species);
41                CREATE INDEX IF NOT EXISTS idx_sightings_source ON sightings(source);
42            "#,
43        },
44        Migration {
45            version: 2,
46            name: "add_advanced_features",
47            sql: r#"
48                CREATE TABLE IF NOT EXISTS packs (
49                    pack_id INTEGER PRIMARY KEY AUTOINCREMENT,
50                    territory_geometry TEXT,
51                    estimated_size INTEGER,
52                    center_latitude REAL,
53                    center_longitude REAL,
54                    area_km2 REAL
55                );
56
57                CREATE TABLE IF NOT EXISTS individuals (
58                    individual_id INTEGER PRIMARY KEY AUTOINCREMENT,
59                    individual_identifier TEXT UNIQUE,
60                    species TEXT,
61                    sex TEXT,
62                    age_class TEXT,
63                    pack_id INTEGER,
64                    FOREIGN KEY (pack_id) REFERENCES packs(pack_id)
65                );
66
67                CREATE TABLE IF NOT EXISTS movements (
68                    movement_id INTEGER PRIMARY KEY AUTOINCREMENT,
69                    from_sighting_id INTEGER,
70                    to_sighting_id INTEGER,
71                    distance_km REAL,
72                    bearing_degrees REAL,
73                    duration_seconds INTEGER,
74                    speed_kmh REAL,
75                    FOREIGN KEY (from_sighting_id) REFERENCES sightings(id),
76                    FOREIGN KEY (to_sighting_id) REFERENCES sightings(id)
77                );
78
79                CREATE INDEX IF NOT EXISTS idx_sightings_observed_on ON sightings(observed_on);
80                CREATE INDEX IF NOT EXISTS idx_sightings_location ON sightings(latitude, longitude);
81                CREATE INDEX IF NOT EXISTS idx_individuals_pack ON individuals(pack_id);
82            "#,
83        },
84        Migration {
85            version: 3,
86            name: "add_auth_and_annotations",
87            sql: r#"
88                CREATE TABLE IF NOT EXISTS users (
89                    id TEXT PRIMARY KEY,
90                    username TEXT NOT NULL UNIQUE,
91                    email TEXT NOT NULL UNIQUE,
92                    password_hash TEXT NOT NULL,
93                    role TEXT NOT NULL DEFAULT 'Viewer',
94                    created_at TEXT NOT NULL
95                );
96
97                CREATE TABLE IF NOT EXISTS sessions (
98                    user_id TEXT NOT NULL,
99                    token TEXT PRIMARY KEY,
100                    expires_at TEXT NOT NULL,
101                    FOREIGN KEY (user_id) REFERENCES users(id)
102                );
103
104                CREATE TABLE IF NOT EXISTS annotations (
105                    id TEXT PRIMARY KEY,
106                    sighting_id INTEGER NOT NULL,
107                    user_id TEXT NOT NULL,
108                    text TEXT NOT NULL,
109                    annotation_type TEXT NOT NULL DEFAULT 'Comment',
110                    created_at TEXT NOT NULL,
111                    updated_at TEXT NOT NULL,
112                    FOREIGN KEY (sighting_id) REFERENCES sightings(id),
113                    FOREIGN KEY (user_id) REFERENCES users(id)
114                );
115
116                CREATE TABLE IF NOT EXISTS sighting_ratings (
117                    id TEXT PRIMARY KEY,
118                    sighting_id INTEGER NOT NULL,
119                    user_id TEXT NOT NULL,
120                    confidence INTEGER NOT NULL CHECK(confidence >= 1 AND confidence <= 5),
121                    notes TEXT,
122                    created_at TEXT NOT NULL,
123                    FOREIGN KEY (sighting_id) REFERENCES sightings(id),
124                    FOREIGN KEY (user_id) REFERENCES users(id),
125                    UNIQUE(sighting_id, user_id)
126                );
127
128                CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
129                CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
130                CREATE INDEX IF NOT EXISTS idx_annotations_sighting ON annotations(sighting_id);
131                CREATE INDEX IF NOT EXISTS idx_annotations_user ON annotations(user_id);
132                CREATE INDEX IF NOT EXISTS idx_ratings_sighting ON sighting_ratings(sighting_id);
133            "#,
134        },
135    ]
136}
137
138/// Create migrations table if it doesn't exist
139fn ensure_migrations_table(conn: &Connection) -> Result<()> {
140    conn.execute(
141        "CREATE TABLE IF NOT EXISTS schema_migrations (
142            version INTEGER PRIMARY KEY,
143            applied_at TEXT NOT NULL
144        )",
145        [],
146    )
147    .context("Failed to create migrations table")?;
148    Ok(())
149}
150
151/// Get current migration version
152fn get_current_version(conn: &Connection) -> Result<i32> {
153    let mut stmt = conn.prepare("SELECT MAX(version) FROM schema_migrations")?;
154    let version: Option<i32> = stmt.query_row([], |row| row.get(0))?;
155    Ok(version.unwrap_or(0))
156}
157
158/// Apply a single migration
159fn apply_migration(conn: &Connection, migration: &Migration) -> Result<()> {
160    conn.execute_batch(migration.sql)
161        .context(format!("Failed to apply migration {}", migration.version))?;
162
163    conn.execute(
164        "INSERT INTO schema_migrations (version, applied_at) VALUES (?1, datetime('now'))",
165        params![migration.version],
166    )
167    .context("Failed to record migration")?;
168
169    Ok(())
170}
171
172/// Run all pending migrations
173pub fn run_migrations(conn: &Connection) -> Result<()> {
174    ensure_migrations_table(conn)?;
175    let current_version = get_current_version(conn)?;
176    let migrations = get_migrations();
177
178    for migration in migrations {
179        if migration.version > current_version {
180            println!(
181                "Applying migration v{}: {}",
182                migration.version, migration.name
183            );
184            apply_migration(conn, &migration)?;
185        }
186    }
187
188    Ok(())
189}
190
191/// Rollback to a specific version (destructive - use with caution)
192pub fn rollback_to_version(conn: &Connection, target_version: i32) -> Result<()> {
193    let current_version = get_current_version(conn)?;
194
195    if target_version >= current_version {
196        anyhow::bail!("Target version must be less than current version");
197    }
198
199    // For simplicity, we'll just delete the database and recreate to target version
200    // In production, you'd want proper rollback migrations
201    anyhow::bail!("Rollback not implemented - drop and recreate database instead");
202}
203
204#[cfg(test)]
205mod tests {
206    use super::*;
207    use tempfile::NamedTempFile;
208
209    #[test]
210    fn test_get_migrations() {
211        let migrations = get_migrations();
212        assert_eq!(migrations.len(), 3);
213        assert_eq!(migrations[0].version, 1);
214        assert_eq!(migrations[1].version, 2);
215        assert_eq!(migrations[2].version, 3);
216    }
217
218    #[test]
219    fn test_run_migrations() {
220        let temp_file = NamedTempFile::new().unwrap();
221        let conn = Connection::open(temp_file.path()).unwrap();
222
223        run_migrations(&conn).unwrap();
224
225        let version = get_current_version(&conn).unwrap();
226        assert_eq!(version, 3);
227    }
228
229    #[test]
230    fn test_ensure_migrations_table() {
231        let temp_file = NamedTempFile::new().unwrap();
232        let conn = Connection::open(temp_file.path()).unwrap();
233
234        ensure_migrations_table(&conn).unwrap();
235
236        // Check table exists
237        let mut stmt = conn
238            .prepare(
239                "SELECT name FROM sqlite_master WHERE type='table' AND name='schema_migrations'",
240            )
241            .unwrap();
242        let result: Option<String> = stmt.query_row([], |row| row.get(0)).unwrap();
243        assert_eq!(result, Some("schema_migrations".to_string()));
244    }
245}
howler_core/migrations.rs

howler_core/
migrations.rs
