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

soar_db/
migration.rs
