soar_db/
migration.rs

1use std::error::Error;
2
3use diesel::{sql_query, RunQueryDsl, SqliteConnection};
4use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};
5
6pub const CORE_MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations/core");
7pub const METADATA_MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations/metadata");
8
9#[derive(Clone, Copy, Debug)]
10pub enum DbType {
11    Core,
12    Metadata,
13}
14
15fn get_migrations(db_type: &DbType) -> EmbeddedMigrations {
16    match db_type {
17        DbType::Core => CORE_MIGRATIONS,
18        DbType::Metadata => METADATA_MIGRATIONS,
19    }
20}
21
22pub fn apply_migrations(
23    conn: &mut SqliteConnection,
24    db_type: &DbType,
25) -> Result<(), Box<dyn Error + Send + Sync + 'static>> {
26    loop {
27        match conn.run_pending_migrations(get_migrations(db_type)) {
28            Ok(_) => break,
29            Err(e) if e.to_string().contains("already exists") => {
30                mark_first_pending(conn, db_type)?;
31            }
32            Err(e) => return Err(e),
33        }
34    }
35
36    Ok(())
37}
38
39fn mark_first_pending(
40    conn: &mut SqliteConnection,
41    db_type: &DbType,
42) -> Result<(), Box<dyn Error + Send + Sync + 'static>> {
43    let pending = conn.pending_migrations(get_migrations(db_type))?;
44    if let Some(first) = pending.first() {
45        sql_query("INSERT INTO __diesel_schema_migrations (version) VALUES (?1)")
46            .bind::<diesel::sql_types::Text, _>(first.name().version())
47            .execute(conn)?;
48    }
49
50    Ok(())
51}
52
53/// Migrate text JSON columns to JSONB binary format.
54///
55/// This is needed when migrating from rusqlite (which stores JSON as text)
56/// to diesel (which uses SQLite's native JSONB format).
57///
58/// Handles both:
59/// - Text type columns (typeof = 'text')
60/// - Blob columns containing text JSON (starts with '[' or '{')
61///
62/// # Performance Note
63///
64/// This runs on every database open but is essentially a no-op after the first
65/// successful migration. The WHERE clause only matches rows with text-based JSON,
66/// so once all rows are converted to JSONB binary format, no rows will be updated.
67///
68/// TODO: Remove this migration in a future version (v0.10 or v1.0) once users
69/// have had sufficient time to migrate their databases.
70pub fn migrate_json_to_jsonb(
71    conn: &mut SqliteConnection,
72    db_type: DbType,
73) -> Result<usize, Box<dyn Error + Send + Sync + 'static>> {
74    // Check for text type OR blob containing text JSON (starts with '[' or '{')
75    // Use hex comparison for blobs: 5B = '[', 7B = '{'
76    let json_condition = |col: &str| {
77        format!(
78            "{col} IS NOT NULL AND (typeof({col}) = 'text' OR (typeof({col}) = 'blob' AND hex(substr({col}, 1, 1)) IN ('5B', '7B')))"
79        )
80    };
81
82    let queries: Vec<String> = match db_type {
83        DbType::Core => {
84            vec![
85                format!(
86                    "UPDATE packages SET provides = jsonb(provides) WHERE {}",
87                    json_condition("provides")
88                ),
89                format!(
90                    "UPDATE packages SET install_patterns = jsonb(install_patterns) WHERE {}",
91                    json_condition("install_patterns")
92                ),
93            ]
94        }
95        DbType::Metadata => {
96            vec![
97                format!(
98                    "UPDATE packages SET licenses = jsonb(licenses) WHERE {}",
99                    json_condition("licenses")
100                ),
101                format!(
102                    "UPDATE packages SET homepages = jsonb(homepages) WHERE {}",
103                    json_condition("homepages")
104                ),
105                format!(
106                    "UPDATE packages SET notes = jsonb(notes) WHERE {}",
107                    json_condition("notes")
108                ),
109                format!(
110                    "UPDATE packages SET source_urls = jsonb(source_urls) WHERE {}",
111                    json_condition("source_urls")
112                ),
113                format!(
114                    "UPDATE packages SET tags = jsonb(tags) WHERE {}",
115                    json_condition("tags")
116                ),
117                format!(
118                    "UPDATE packages SET categories = jsonb(categories) WHERE {}",
119                    json_condition("categories")
120                ),
121                format!(
122                    "UPDATE packages SET provides = jsonb(provides) WHERE {}",
123                    json_condition("provides")
124                ),
125                format!(
126                    "UPDATE packages SET snapshots = jsonb(snapshots) WHERE {}",
127                    json_condition("snapshots")
128                ),
129                format!(
130                    "UPDATE packages SET replaces = jsonb(replaces) WHERE {}",
131                    json_condition("replaces")
132                ),
133            ]
134        }
135    };
136
137    let mut total = 0;
138    for query in queries {
139        total += sql_query(&query).execute(conn)?;
140    }
141
142    Ok(total)
143}
soar_db/migration.rs

soar_db/
migration.rs
