Skip to main content

che_orm/migration/
sqlite_executor.rs

1//! SQLite forward migration execution and history recording.
2use crate::{
3    Migration, MigrationGraph, MigrationId, MigrationOperation, ProjectState, SqliteSchemaEditor,
4    rusqlite::OptionalExtension,
5};
6use std::collections::{BTreeSet, HashMap};
7use std::time::Duration;
8use time::{OffsetDateTime, format_description::well_known::Rfc3339};
9
10pub fn apply_operation_migrations_on_connection(
11    connection: &mut crate::rusqlite::Connection,
12    graph: MigrationGraph,
13) -> Result<usize, Box<dyn std::error::Error + Send + Sync>> {
14    apply_operation_migrations_to_on_connection(connection, graph, None)
15}
16
17/// Applies pending migrations needed to reach `target`, or every pending
18/// migration when `target` is `None`. Target planning is forward-only.
19pub fn apply_operation_migrations_to_on_connection(
20    connection: &mut crate::rusqlite::Connection,
21    graph: MigrationGraph,
22    target: Option<MigrationId>,
23) -> Result<usize, Box<dyn std::error::Error + Send + Sync>> {
24    graph.validate_heads()?;
25    // Validate static target errors before acquiring a write lock or changing
26    // connection settings. The actual pending plan is recomputed under each
27    // lock because another process may commit between migrations.
28    graph.plan_forward(&BTreeSet::new(), target.as_ref())?;
29    let mut applied_count = 0;
30    loop {
31        let foreign_keys: bool =
32            connection.query_row("PRAGMA foreign_keys", [], |row| row.get(0))?;
33        connection.pragma_update(None, "foreign_keys", false)?;
34        if let Err(error) = begin_immediate(connection, Duration::from_secs(5)) {
35            connection.pragma_update(None, "foreign_keys", foreign_keys)?;
36            return Err(error);
37        }
38        let result = (|| {
39            // Read history only after acquiring the write lock. Recompute this
40            // per migration so concurrent manage processes cannot replay a
41            // stale pending set after the previous commit.
42            let applied = applied_operation_migrations(connection)?;
43            let pending = crate::migration::pending_operation_migrations(&graph, &applied)?;
44            let applied_ids = applied
45                .keys()
46                .map(|(app, name)| MigrationId {
47                    app: app.clone(),
48                    name: name.clone(),
49                })
50                .collect::<BTreeSet<_>>();
51            let planned_ids = graph
52                .plan_forward(&applied_ids, target.as_ref())?
53                .into_iter()
54                .map(|step| step.id)
55                .collect::<BTreeSet<_>>();
56            let Some(migration) = pending
57                .into_iter()
58                .find(|migration| planned_ids.contains(&migration.id))
59            else {
60                return Ok(None);
61            };
62            connection.execute_batch(OPERATION_MIGRATION_HISTORY_SQL)?;
63            let mut migration_state = graph
64                .replay_selected(ProjectState::default(), &applied_ids)
65                .map_err(|error| format!("cannot reconstruct migration state: {error}"))?;
66            apply_single_operation_migration(connection, migration, &mut migration_state)?;
67            Ok(Some(()))
68        })();
69        let result = result.and_then(|applied| {
70            connection.execute_batch("COMMIT")?;
71            Ok(applied)
72        });
73        if !connection.is_autocommit() {
74            connection.execute_batch("ROLLBACK")?;
75        }
76        connection.pragma_update(None, "foreign_keys", foreign_keys)?;
77        match result {
78            Ok(Some(())) => applied_count += 1,
79            Ok(None) => return Ok(applied_count),
80            Err(error) => return Err(error),
81        }
82    }
83}
84
85fn begin_immediate(
86    connection: &mut crate::rusqlite::Connection,
87    timeout: Duration,
88) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
89    connection.busy_timeout(timeout)?;
90    match connection.execute_batch("BEGIN IMMEDIATE") {
91        Ok(()) => Ok(()),
92        Err(crate::rusqlite::Error::SqliteFailure(error, _))
93            if matches!(
94                error.code,
95                crate::rusqlite::ErrorCode::DatabaseBusy
96                    | crate::rusqlite::ErrorCode::DatabaseLocked
97            ) =>
98        {
99            Err(format!(
100                "database is locked; could not acquire the migration write lock within {} ms",
101                timeout.as_millis()
102            )
103            .into())
104        }
105        Err(error) => Err(error.into()),
106    }
107}
108
109fn apply_single_operation_migration(
110    connection: &mut crate::rusqlite::Connection,
111    migration: &Migration,
112    migration_state: &mut ProjectState,
113) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
114    for operation in &migration.operations {
115        match operation {
116            MigrationOperation::RunSql { forward, .. } => {
117                use crate::rusqlite::hooks::{AuthAction, AuthContext, Authorization};
118                connection.authorizer(Some(|context: AuthContext<'_>| match context.action {
119                    AuthAction::Transaction { .. }
120                    | AuthAction::Savepoint { .. }
121                    | AuthAction::Attach { .. }
122                    | AuthAction::Detach { .. } => Authorization::Deny,
123                    _ => Authorization::Allow,
124                }))?;
125                let result = connection.execute_batch(forward);
126                let reset = connection.authorizer(None::<fn(AuthContext<'_>) -> Authorization>);
127                result?;
128                reset?;
129                operation.state_forwards(migration_state)?;
130            }
131            _ => {
132                if let Some(table) = SqliteSchemaEditor::rebuild_table_name(operation) {
133                    ensure_rebuild_can_preserve_rows(connection, operation)?;
134                    assert_rebuild_objects_match_history(connection, table, migration_state)?;
135                }
136                let plan = SqliteSchemaEditor::plan(operation, migration_state)?;
137                let normalizing = matches!(
138                    operation,
139                    MigrationOperation::NormalizeSqliteDateTimes { .. }
140                );
141                if normalizing {
142                    use crate::rusqlite::functions::FunctionFlags;
143                    connection.create_scalar_function(
144                        "che_datetime_utc",
145                        1,
146                        FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
147                        |context| {
148                            let value: Option<String> = context.get(0)?;
149                            value
150                                .map(|value| {
151                                    crate::datetime::normalize_sqlite_datetime(&value).map_err(
152                                        |error| {
153                                            crate::rusqlite::Error::UserFunctionError(Box::new(
154                                                error,
155                                            ))
156                                        },
157                                    )
158                                })
159                                .transpose()
160                        },
161                    )?;
162                }
163                let result = plan
164                    .statements
165                    .iter()
166                    .try_for_each(|statement| connection.execute_batch(statement));
167                if normalizing {
168                    connection.remove_function("che_datetime_utc", 1)?;
169                }
170                result?;
171                *migration_state = plan.resulting_state;
172            }
173        }
174    }
175    connection.execute(
176        "INSERT INTO che_migration_history (app, name, checksum, applied_on) VALUES (?1, ?2, ?3, ?4)",
177        crate::rusqlite::params![
178            migration.id.app,
179            migration.id.name,
180            migration.checksum,
181            OffsetDateTime::now_utc().format(&Rfc3339)?,
182        ],
183    )?;
184    assert_foreign_keys_valid(connection)?;
185    Ok(())
186}
187
188fn ensure_rebuild_can_preserve_rows(
189    connection: &crate::rusqlite::Connection,
190    operation: &MigrationOperation,
191) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
192    match operation {
193        MigrationOperation::AddColumn { table, column }
194            if !column.nullable
195                && !column.primary_key
196                && column.default.is_none()
197                && !column.auto_now_add
198                && !column.auto_now =>
199        {
200            let table = quote_identifier(table);
201            let has_rows: bool = connection.query_row(
202                &format!("SELECT EXISTS(SELECT 1 FROM {table} LIMIT 1)"),
203                [],
204                |row| row.get(0),
205            )?;
206            if has_rows {
207                return Err(format!(
208                    "cannot add required column {} without a value for existing rows; add a default or use an explicit nullable → backfill → non-null migration",
209                    column.name
210                )
211                .into());
212            }
213        }
214        MigrationOperation::AlterColumn {
215            table,
216            old_column,
217            new_column,
218        } if old_column.nullable && !new_column.nullable => {
219            let has_nulls: bool = connection.query_row(
220                &format!(
221                    "SELECT EXISTS(SELECT 1 FROM {} WHERE {} IS NULL LIMIT 1)",
222                    quote_identifier(table),
223                    quote_identifier(&new_column.name)
224                ),
225                [],
226                |row| row.get(0),
227            )?;
228            if has_nulls {
229                return Err(format!(
230                    "cannot make {}.{} non-null while existing rows contain NULL; backfill values before this migration",
231                    table, new_column.name
232                )
233                .into());
234            }
235        }
236        MigrationOperation::AddUniqueConstraint { table, columns } => {
237            ensure_unique_values(connection, table, columns)?;
238        }
239        MigrationOperation::AlterColumn {
240            table,
241            old_column,
242            new_column,
243        } if !old_column.unique && new_column.unique => {
244            ensure_unique_values(connection, table, std::slice::from_ref(&new_column.name))?;
245        }
246        _ => {}
247    }
248    Ok(())
249}
250
251fn ensure_unique_values(
252    connection: &crate::rusqlite::Connection,
253    table: &str,
254    columns: &[String],
255) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
256    let columns_sql = columns
257        .iter()
258        .map(|column| quote_identifier(column))
259        .collect::<Vec<_>>()
260        .join(", ");
261    let non_null = columns
262        .iter()
263        .map(|column| format!("{} IS NOT NULL", quote_identifier(column)))
264        .collect::<Vec<_>>()
265        .join(" AND ");
266    let duplicates: bool = connection.query_row(
267        &format!(
268            "SELECT EXISTS(SELECT 1 FROM {} WHERE {non_null} GROUP BY {columns_sql} HAVING count(*) > 1 LIMIT 1)",
269            quote_identifier(table)
270        ),
271        [],
272        |row| row.get(0),
273    )?;
274    if duplicates {
275        return Err(format!(
276            "cannot add unique constraint on {}({}); existing rows contain duplicates",
277            table,
278            columns.join(", ")
279        )
280        .into());
281    }
282    Ok(())
283}
284
285fn quote_identifier(identifier: &str) -> String {
286    format!("\"{}\"", identifier.replace('"', "\"\""))
287}
288
289fn assert_rebuild_objects_match_history(
290    connection: &crate::rusqlite::Connection,
291    table: &str,
292    migration_state: &ProjectState,
293) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
294    let table_sql: Option<String> = connection
295        .query_row(
296            "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?1",
297            [table],
298            |row| row.get(0),
299        )
300        .optional()?;
301    if table_sql
302        .as_deref()
303        .is_some_and(|sql| sql.to_ascii_uppercase().contains("AUTOINCREMENT"))
304    {
305        return Err(format!(
306            "cannot rebuild {table}: AUTOINCREMENT is not represented in historical state; use explicit RunSql that preserves sqlite_sequence"
307        )
308        .into());
309    }
310    // A rebuild drops every index owned by the old table. Refuse unknown
311    // definitions instead of silently losing them.
312    let expected = migration_state
313        .tables()
314        .iter()
315        .find(|item| item.name == table)
316        .ok_or_else(|| format!("missing historical table {table}"))?;
317    let expected_unique = expected
318        .columns
319        .iter()
320        .filter(|column| column.unique)
321        .map(|column| vec![column.name.clone()])
322        .chain(expected.unique_constraints.iter().cloned())
323        .collect::<Vec<_>>();
324    let mut unmatched_unique = expected_unique.clone();
325    let expected_primary_key = expected
326        .columns
327        .iter()
328        .filter(|column| column.primary_key)
329        .map(|column| column.name.clone())
330        .collect::<Vec<_>>();
331    let mut unmatched_indexes = expected.indexes.clone();
332    let mut indexes = connection
333        .prepare("SELECT name, \"unique\", origin, partial FROM pragma_index_list(?1)")?;
334    let indexes = indexes
335        .query_map([table], |row| {
336            Ok((
337                row.get::<_, String>(0)?,
338                row.get::<_, bool>(1)?,
339                row.get::<_, String>(2)?,
340                row.get::<_, bool>(3)?,
341            ))
342        })?
343        .collect::<Result<Vec<_>, _>>()?;
344    for (name, unique, origin, partial) in indexes {
345        let info = sqlite_index_columns(connection, &name)?;
346        match origin.as_str() {
347            "c" => {
348                if partial
349                    || info
350                        .0
351                        .iter()
352                        .any(|(_, desc, coll)| *desc || coll != "BINARY")
353                {
354                    return Err(format!("cannot rebuild {table}: divergent index {name}").into());
355                }
356                let unmatched = if unique {
357                    &mut unmatched_unique
358                } else {
359                    &mut unmatched_indexes
360                };
361                let Some(position) = unmatched.iter().position(|columns| *columns == info.1) else {
362                    return Err(format!(
363                        "cannot rebuild {table}: unmanaged index {name}; use explicit RunSql"
364                    )
365                    .into());
366                };
367                unmatched.remove(position);
368            }
369            "u" if unique && !partial => {
370                let Some(position) = unmatched_unique
371                    .iter()
372                    .position(|columns| *columns == info.1)
373                else {
374                    return Err(format!(
375                        "cannot rebuild {table}: unmanaged or divergent index {name}; use explicit RunSql"
376                    )
377                    .into());
378                };
379                unmatched_unique.remove(position);
380            }
381            "pk" if !partial && info.1 == expected_primary_key => {}
382            _ => {
383                return Err(format!(
384                    "cannot rebuild {table}: unmanaged or divergent index {name}; use explicit RunSql"
385                )
386                .into());
387            }
388        }
389    }
390    if !unmatched_indexes.is_empty() {
391        return Err(format!(
392            "cannot rebuild {table}: historical index(es) {:?} are missing from SQLite",
393            unmatched_indexes
394        )
395        .into());
396    }
397    if !unmatched_unique.is_empty() {
398        return Err(format!(
399            "cannot rebuild {table}: historical unique constraint(s) {:?} are missing from SQLite",
400            unmatched_unique
401        )
402        .into());
403    }
404    let dependents = sqlite_opaque_dependents(connection, table)?;
405    if !dependents.is_empty() {
406        return Err(format!(
407            "cannot rebuild table {table}; SQLite object(s) {} may depend on it. Drop and recreate the matching declared schema objects around the table operation",
408            dependents.join(", ")
409        )
410        .into());
411    }
412    Ok(())
413}
414
415fn sqlite_index_columns(
416    connection: &crate::rusqlite::Connection,
417    name: &str,
418) -> Result<(Vec<(String, bool, String)>, Vec<String>), crate::rusqlite::Error> {
419    let mut statement = connection.prepare(
420        "SELECT name, desc, coll FROM pragma_index_xinfo(?1) WHERE key = 1 ORDER BY seqno",
421    )?;
422    let details = statement
423        .query_map([name], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)))?
424        .collect::<Result<Vec<(String, bool, String)>, _>>()?;
425    let columns = details.iter().map(|(name, _, _)| name.clone()).collect();
426    Ok((details, columns))
427}
428
429fn sqlite_opaque_dependents(
430    connection: &crate::rusqlite::Connection,
431    table: &str,
432) -> Result<Vec<String>, crate::rusqlite::Error> {
433    let table = table.to_ascii_lowercase();
434    let mut statement = connection.prepare(
435        "SELECT type, name, tbl_name, sql FROM sqlite_master WHERE type IN ('trigger', 'view') AND sql IS NOT NULL ORDER BY type, name",
436    )?;
437    statement
438        .query_map([], |row| {
439            Ok((
440                row.get::<_, String>(0)?,
441                row.get::<_, String>(1)?,
442                row.get::<_, String>(2)?,
443                row.get::<_, String>(3)?,
444            ))
445        })?
446        .filter_map(|row| match row {
447            Ok((kind, name, owner, sql))
448                if owner.eq_ignore_ascii_case(&table)
449                    || sql.to_ascii_lowercase().contains(&table) =>
450            {
451                Some(Ok(format!("{kind} {name}")))
452            }
453            Ok(_) => None,
454            Err(error) => Some(Err(error)),
455        })
456        .collect()
457}
458
459const OPERATION_MIGRATION_HISTORY_SQL: &str = "CREATE TABLE IF NOT EXISTS che_migration_history (app TEXT NOT NULL, name TEXT NOT NULL, checksum TEXT NOT NULL, applied_on TEXT NOT NULL, PRIMARY KEY (app, name));";
460
461pub fn applied_operation_migrations(
462    connection: &crate::rusqlite::Connection,
463) -> Result<HashMap<(String, String), String>, Box<dyn std::error::Error + Send + Sync>> {
464    let has_history = connection
465        .query_row(
466            "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'che_migration_history'",
467            [],
468            |_| Ok(()),
469        )
470        .optional()?;
471    if has_history.is_none() {
472        return Ok(HashMap::new());
473    }
474    let mut statement =
475        connection.prepare("SELECT app, name, checksum FROM che_migration_history")?;
476    let rows = statement.query_map([], |row| {
477        Ok((
478            (row.get::<_, String>(0)?, row.get::<_, String>(1)?),
479            row.get(2)?,
480        ))
481    })?;
482    rows.collect::<Result<HashMap<_, _>, _>>()
483        .map_err(Into::into)
484}
485
486fn assert_foreign_keys_valid(
487    connection: &crate::rusqlite::Connection,
488) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
489    let mut statement = connection
490        .prepare("PRAGMA foreign_key_check;")
491        .map_err(|error| Box::new(error) as Box<dyn std::error::Error + Send + Sync>)?;
492    let mut rows = statement
493        .query([])
494        .map_err(|error| Box::new(error) as Box<dyn std::error::Error + Send + Sync>)?;
495    if rows
496        .next()
497        .map_err(|error| Box::new(error) as Box<dyn std::error::Error + Send + Sync>)?
498        .is_some()
499    {
500        return Err("migration left foreign key violations".into());
501    }
502    Ok(())
503}
504
505#[cfg(test)]
506mod tests {
507    use super::*;
508    use crate::{
509        ColumnState, ColumnType, ForeignKeyState, MigrationId, SchemaObjectKind, SchemaObjectState,
510        TableState,
511    };
512
513    fn column(name: &str) -> ColumnState {
514        ColumnState {
515            field_name: name.into(),
516            name: name.into(),
517            column_type: ColumnType::Text,
518            nullable: false,
519            primary_key: false,
520            unique: false,
521            default: None,
522            check: None,
523            choices: None,
524            references: None,
525            auto_now_add: false,
526            auto_now: false,
527        }
528    }
529
530    fn state_with_table(columns: Vec<ColumnState>) -> ProjectState {
531        let mut state = ProjectState::default();
532        MigrationOperation::CreateTable {
533            table: TableState {
534                name: "items".into(),
535                columns,
536                unique_constraints: vec![],
537                indexes: vec![],
538            },
539        }
540        .state_forwards(&mut state)
541        .unwrap();
542        state
543    }
544
545    #[test]
546    fn declared_trigger_can_be_dropped_rebuilt_and_restored_with_its_table() {
547        let trigger = SchemaObjectState {
548            app: "items".into(),
549            kind: SchemaObjectKind::Trigger,
550            name: "items_guard".into(),
551            sql: "CREATE TRIGGER items_guard BEFORE INSERT ON items BEGIN SELECT 1; END".into(),
552        };
553        let initial = Migration::new(
554            MigrationId {
555                app: "items".into(),
556                name: "0001_initial".into(),
557            },
558            vec![],
559            vec![
560                MigrationOperation::CreateTable {
561                    table: TableState {
562                        name: "items".into(),
563                        columns: vec![column("id")],
564                        unique_constraints: vec![],
565                        indexes: vec![],
566                    },
567                },
568                MigrationOperation::CreateSchemaObject {
569                    object: trigger.clone(),
570                },
571            ],
572        );
573        let mut title = column("title");
574        title.nullable = true;
575        let rebuild = Migration::new(
576            MigrationId {
577                app: "items".into(),
578                name: "0002_title".into(),
579            },
580            vec![initial.id.clone()],
581            vec![
582                MigrationOperation::DropSchemaObject {
583                    kind: SchemaObjectKind::Trigger,
584                    name: trigger.name.clone(),
585                },
586                MigrationOperation::AddColumn {
587                    table: "items".into(),
588                    column: title,
589                },
590                MigrationOperation::CreateSchemaObject { object: trigger },
591            ],
592        );
593        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
594        apply_operation_migrations_on_connection(
595            &mut connection,
596            MigrationGraph::new(vec![initial.clone()]).unwrap(),
597        )
598        .unwrap();
599        connection
600            .execute("INSERT INTO items(id) VALUES ('one')", [])
601            .unwrap();
602        apply_operation_migrations_on_connection(
603            &mut connection,
604            MigrationGraph::new(vec![initial, rebuild]).unwrap(),
605        )
606        .unwrap();
607        let row: (String, Option<String>) = connection
608            .query_row("SELECT id, title FROM items", [], |row| {
609                Ok((row.get(0)?, row.get(1)?))
610            })
611            .unwrap();
612        assert_eq!(row, ("one".into(), None));
613        let exists: bool = connection
614            .query_row(
615                "SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type = 'trigger' AND name = 'items_guard')",
616                [],
617                |row| row.get(0),
618            )
619            .unwrap();
620        assert!(exists);
621    }
622
623    #[test]
624    fn schema_object_operations_create_and_drop_sqlite_objects() {
625        let create = Migration::new(
626            MigrationId {
627                app: "items".into(),
628                name: "0001_create_view".into(),
629            },
630            vec![],
631            vec![MigrationOperation::CreateSchemaObject {
632                object: SchemaObjectState {
633                    app: "items".into(),
634                    kind: SchemaObjectKind::View,
635                    name: "item_view".into(),
636                    sql: "CREATE VIEW item_view AS SELECT 1 AS id".into(),
637                },
638            }],
639        );
640        let drop = Migration::new(
641            MigrationId {
642                app: "items".into(),
643                name: "0002_drop_view".into(),
644            },
645            vec![create.id.clone()],
646            vec![MigrationOperation::DropSchemaObject {
647                kind: SchemaObjectKind::View,
648                name: "item_view".into(),
649            }],
650        );
651        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
652        apply_operation_migrations_to_on_connection(
653            &mut connection,
654            MigrationGraph::new(vec![create.clone(), drop.clone()]).unwrap(),
655            Some(create.id.clone()),
656        )
657        .unwrap();
658        assert_eq!(
659            connection
660                .query_row("SELECT id FROM item_view", [], |row| row.get::<_, i64>(0))
661                .unwrap(),
662            1
663        );
664        apply_operation_migrations_on_connection(
665            &mut connection,
666            MigrationGraph::new(vec![create, drop]).unwrap(),
667        )
668        .unwrap();
669        let exists: Option<String> = connection
670            .query_row(
671                "SELECT name FROM sqlite_master WHERE type = 'view' AND name = 'item_view'",
672                [],
673                |row| row.get(0),
674            )
675            .optional()
676            .unwrap();
677        assert!(exists.is_none());
678    }
679
680    #[test]
681    fn rebuild_rejects_unmanaged_auto_unique_indexes() {
682        let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
683        connection
684            .execute_batch(
685                "CREATE TABLE items (id TEXT NOT NULL, obsolete TEXT NOT NULL, UNIQUE(obsolete))",
686            )
687            .unwrap();
688        let error = assert_rebuild_objects_match_history(
689            &connection,
690            "items",
691            &state_with_table(vec![column("id"), column("obsolete")]),
692        )
693        .unwrap_err();
694        assert!(error.to_string().contains("unmanaged or divergent index"));
695    }
696
697    #[test]
698    fn rebuild_accepts_historical_unique_constraints() {
699        let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
700        connection
701            .execute_batch("CREATE TABLE items (id TEXT NOT NULL, UNIQUE(id))")
702            .unwrap();
703        let mut id = column("id");
704        id.unique = true;
705        assert!(assert_rebuild_objects_match_history(
706            &connection,
707            "items",
708            &state_with_table(vec![id]),
709        )
710        .is_ok());
711    }
712
713    #[test]
714    fn consecutive_index_changes_preserve_rows_and_match_history() {
715        for add_after_removal in [false, true] {
716            let table = TableState {
717                name: "items".into(),
718                columns: vec![column("a"), column("b"), column("c")],
719                unique_constraints: vec![],
720                indexes: vec![vec!["a".into()], vec!["b".into()], vec!["c".into()]],
721            };
722            let initial = Migration::new(
723                MigrationId {
724                    app: "items".into(),
725                    name: "0001".into(),
726                },
727                vec![],
728                vec![MigrationOperation::CreateTable { table }],
729            );
730            let mut db = crate::rusqlite::Connection::open_in_memory().unwrap();
731            apply_operation_migrations_on_connection(
732                &mut db,
733                MigrationGraph::new(vec![initial.clone()]).unwrap(),
734            )
735            .unwrap();
736            db.execute("INSERT INTO items VALUES ('A','B','C')", [])
737                .unwrap();
738            // Historical SQL can use names different from the generated ones.
739            db.execute_batch("DROP INDEX items_idx_0; CREATE INDEX legacy_a ON items(a)")
740                .unwrap();
741            let last = if add_after_removal {
742                MigrationOperation::AddIndex {
743                    table: "items".into(),
744                    columns: vec!["a".into(), "b".into()],
745                }
746            } else {
747                MigrationOperation::RemoveIndex {
748                    table: "items".into(),
749                    columns: vec!["c".into()],
750                }
751            };
752            let next = Migration::new(
753                MigrationId {
754                    app: "items".into(),
755                    name: "0002".into(),
756                },
757                vec![initial.id.clone()],
758                vec![
759                    MigrationOperation::RemoveIndex {
760                        table: "items".into(),
761                        columns: vec!["a".into()],
762                    },
763                    last,
764                ],
765            );
766            let graph = MigrationGraph::new(vec![initial, next]).unwrap();
767            apply_operation_migrations_on_connection(&mut db, graph.clone()).unwrap();
768            assert_rebuild_objects_match_history(
769                &db,
770                "items",
771                &graph.replay(Default::default()).unwrap(),
772            )
773            .unwrap();
774            let row: (String, String, String) = db
775                .query_row("SELECT a,b,c FROM items", [], |row| {
776                    Ok((row.get(0)?, row.get(1)?, row.get(2)?))
777                })
778                .unwrap();
779            assert_eq!(row, ("A".into(), "B".into(), "C".into()));
780            assert_eq!(
781                apply_operation_migrations_on_connection(&mut db, graph).unwrap(),
782                0
783            );
784        }
785    }
786
787    #[test]
788    fn rebuild_accepts_historical_non_unique_index_names() {
789        let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
790        connection
791            .execute_batch(
792                "CREATE TABLE items (id TEXT NOT NULL); \
793                 CREATE INDEX legacy_items_id_idx ON items(id)",
794            )
795            .unwrap();
796        let mut state = ProjectState::default();
797        MigrationOperation::CreateTable {
798            table: TableState {
799                name: "items".into(),
800                columns: vec![column("id")],
801                unique_constraints: vec![],
802                indexes: vec![vec!["id".into()]],
803            },
804        }
805        .state_forwards(&mut state)
806        .unwrap();
807        assert!(assert_rebuild_objects_match_history(&connection, "items", &state).is_ok());
808    }
809
810    #[test]
811    fn rebuild_rejects_views_and_external_triggers_that_reference_the_table() {
812        let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
813        connection
814            .execute_batch(
815                "CREATE TABLE items (id TEXT NOT NULL); \
816                 CREATE TABLE audit (item_id TEXT); \
817                 CREATE VIEW item_view AS SELECT id FROM items; \
818                 CREATE TRIGGER audit_items AFTER INSERT ON audit BEGIN SELECT id FROM items; END;",
819            )
820            .unwrap();
821        let error = assert_rebuild_objects_match_history(
822            &connection,
823            "items",
824            &state_with_table(vec![column("id")]),
825        )
826        .unwrap_err();
827        let message = error.to_string();
828        assert!(message.contains("view item_view"));
829        assert!(message.contains("trigger audit_items"));
830    }
831
832    #[test]
833    fn rebuild_rejects_required_column_without_a_value_for_existing_rows() {
834        let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
835        connection
836            .execute_batch("CREATE TABLE items (id TEXT NOT NULL); INSERT INTO items VALUES ('1')")
837            .unwrap();
838        let mut required = column("title");
839        required.nullable = false;
840        let error = ensure_rebuild_can_preserve_rows(
841            &connection,
842            &MigrationOperation::AddColumn {
843                table: "items".into(),
844                column: required,
845            },
846        )
847        .unwrap_err();
848        assert!(error.to_string().contains("nullable → backfill → non-null"));
849    }
850
851    #[test]
852    fn rebuild_rejects_nulls_and_duplicates_before_new_constraints() {
853        let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
854        connection
855            .execute_batch("CREATE TABLE items (id TEXT, email TEXT); INSERT INTO items VALUES (NULL, 'same'), (NULL, 'same')")
856            .unwrap();
857        let mut old_id = column("id");
858        old_id.nullable = true;
859        let error = ensure_rebuild_can_preserve_rows(
860            &connection,
861            &MigrationOperation::AlterColumn {
862                table: "items".into(),
863                old_column: old_id.clone(),
864                new_column: column("id"),
865            },
866        )
867        .unwrap_err();
868        assert!(error.to_string().contains("contain NULL"));
869        let error = ensure_rebuild_can_preserve_rows(
870            &connection,
871            &MigrationOperation::AddUniqueConstraint {
872                table: "items".into(),
873                columns: vec!["email".into()],
874            },
875        )
876        .unwrap_err();
877        assert!(error.to_string().contains("contain duplicates"));
878    }
879
880    #[test]
881    fn backfill_allows_nullable_to_non_null_without_a_schema_default() {
882        let mut title = column("title");
883        title.nullable = true;
884        let initial = Migration::new(
885            MigrationId {
886                app: "items".into(),
887                name: "0001".into(),
888            },
889            vec![],
890            vec![MigrationOperation::CreateTable {
891                table: TableState {
892                    name: "items".into(),
893                    columns: vec![column("id"), title.clone()],
894                    unique_constraints: vec![],
895                    indexes: vec![],
896                },
897            }],
898        );
899        let mut required = title.clone();
900        required.nullable = false;
901        let follow_up = Migration::new(
902            MigrationId {
903                app: "items".into(),
904                name: "0002_title_required".into(),
905            },
906            vec![initial.id.clone()],
907            vec![
908                MigrationOperation::BackfillColumn {
909                    table: "items".into(),
910                    column: "title".into(),
911                    value: "'untitled'".into(),
912                },
913                MigrationOperation::AlterColumn {
914                    table: "items".into(),
915                    old_column: title,
916                    new_column: required,
917                },
918            ],
919        );
920        let graph = MigrationGraph::new(vec![initial, follow_up]).unwrap();
921        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
922        apply_operation_migrations_on_connection(
923            &mut connection,
924            MigrationGraph::new(vec![graph.ordered().next().unwrap().clone()]).unwrap(),
925        )
926        .unwrap();
927        connection
928            .execute("INSERT INTO items(id, title) VALUES ('1', NULL)", [])
929            .unwrap();
930        assert_eq!(
931            apply_operation_migrations_on_connection(&mut connection, graph).unwrap(),
932            1
933        );
934        assert_eq!(
935            connection
936                .query_row("SELECT title FROM items WHERE id='1'", [], |row| row
937                    .get::<_, String>(0))
938                .unwrap(),
939            "untitled"
940        );
941        assert!(
942            connection
943                .execute("INSERT INTO items(id, title) VALUES ('2', NULL)", [])
944                .is_err()
945        );
946        assert_eq!(
947            connection
948                .query_row(
949                    "SELECT dflt_value FROM pragma_table_info('items') WHERE name='title'",
950                    [],
951                    |row| row.get::<_, Option<String>>(0)
952                )
953                .unwrap(),
954            None
955        );
956    }
957
958    #[test]
959    fn rebuild_keeps_integer_primary_key_allocation_and_rejects_unmodeled_autoincrement() {
960        let mut id = column("id");
961        id.column_type = ColumnType::Integer;
962        id.primary_key = true;
963        let title = column("title");
964        let initial = Migration::new(
965            MigrationId {
966                app: "items".into(),
967                name: "0001".into(),
968            },
969            vec![],
970            vec![MigrationOperation::CreateTable {
971                table: TableState {
972                    name: "items".into(),
973                    columns: vec![id.clone(), title.clone()],
974                    unique_constraints: vec![],
975                    indexes: vec![],
976                },
977            }],
978        );
979        let mut new_title = title.clone();
980        new_title.default = Some("'untitled'".into());
981        let rebuild = Migration::new(
982            MigrationId {
983                app: "items".into(),
984                name: "0002_title_default".into(),
985            },
986            vec![initial.id.clone()],
987            vec![MigrationOperation::AlterColumn {
988                table: "items".into(),
989                old_column: title,
990                new_column: new_title,
991            }],
992        );
993        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
994        apply_operation_migrations_on_connection(
995            &mut connection,
996            MigrationGraph::new(vec![initial.clone()]).unwrap(),
997        )
998        .unwrap();
999        connection
1000            .execute("INSERT INTO items(id, title) VALUES (5, 'existing')", [])
1001            .unwrap();
1002        apply_operation_migrations_on_connection(
1003            &mut connection,
1004            MigrationGraph::new(vec![initial, rebuild]).unwrap(),
1005        )
1006        .unwrap();
1007        connection
1008            .execute("INSERT INTO items(title) VALUES ('next')", [])
1009            .unwrap();
1010        assert_eq!(
1011            connection
1012                .query_row("SELECT id FROM items WHERE title = 'next'", [], |row| row
1013                    .get::<_, i64>(
1014                    0
1015                ))
1016                .unwrap(),
1017            6
1018        );
1019
1020        connection
1021            .execute_batch(
1022                "CREATE TABLE sequence_items(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT)",
1023            )
1024            .unwrap();
1025        let error = assert_rebuild_objects_match_history(
1026            &connection,
1027            "sequence_items",
1028            &state_with_table(vec![id, column("title")]),
1029        )
1030        .unwrap_err();
1031        assert!(error.to_string().contains("AUTOINCREMENT"));
1032    }
1033
1034    #[test]
1035    fn rebuild_preserves_composite_constraints_indexes_and_foreign_keys() {
1036        let mut id = column("id");
1037        id.column_type = ColumnType::Integer;
1038        id.primary_key = true;
1039        let mut parent_id = id.clone();
1040        parent_id.name = "parent_id".into();
1041        parent_id.field_name = "parent_id".into();
1042        parent_id.primary_key = false;
1043        parent_id.references = Some(ForeignKeyState {
1044            target: "parents(id)".into(),
1045            on_delete: Some("cascade".into()),
1046        });
1047        let code = column("code");
1048        let description = column("description");
1049        let initial = Migration::new(
1050            MigrationId {
1051                app: "items".into(),
1052                name: "0001".into(),
1053            },
1054            vec![],
1055            vec![
1056                MigrationOperation::CreateTable {
1057                    table: TableState {
1058                        name: "parents".into(),
1059                        columns: vec![id.clone()],
1060                        unique_constraints: vec![],
1061                        indexes: vec![],
1062                    },
1063                },
1064                MigrationOperation::CreateTable {
1065                    table: TableState {
1066                        name: "items".into(),
1067                        columns: vec![id.clone(), parent_id, code.clone(), description.clone()],
1068                        unique_constraints: vec![vec!["parent_id".into(), "code".into()]],
1069                        indexes: vec![vec!["code".into(), "description".into()]],
1070                    },
1071                },
1072            ],
1073        );
1074        let mut new_description = description.clone();
1075        new_description.default = Some("'unknown'".into());
1076        let rebuild = Migration::new(
1077            MigrationId {
1078                app: "items".into(),
1079                name: "0002_description_default".into(),
1080            },
1081            vec![initial.id.clone()],
1082            vec![MigrationOperation::AlterColumn {
1083                table: "items".into(),
1084                old_column: description,
1085                new_column: new_description,
1086            }],
1087        );
1088        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1089        connection.execute_batch("PRAGMA foreign_keys=ON").unwrap();
1090        apply_operation_migrations_on_connection(
1091            &mut connection,
1092            MigrationGraph::new(vec![initial.clone()]).unwrap(),
1093        )
1094        .unwrap();
1095        connection
1096            .execute("INSERT INTO parents(id) VALUES (1)", [])
1097            .unwrap();
1098        connection
1099            .execute(
1100                "INSERT INTO items(id, parent_id, code, description) VALUES (1, 1, 'A', 'first')",
1101                [],
1102            )
1103            .unwrap();
1104        let graph = MigrationGraph::new(vec![initial, rebuild]).unwrap();
1105        apply_operation_migrations_on_connection(&mut connection, graph.clone()).unwrap();
1106        assert_rebuild_objects_match_history(
1107            &connection,
1108            "items",
1109            &graph.replay(Default::default()).unwrap(),
1110        )
1111        .unwrap();
1112        assert!(connection
1113            .execute(
1114                "INSERT INTO items(id, parent_id, code, description) VALUES (2, 1, 'A', 'duplicate')",
1115                [],
1116            )
1117            .is_err());
1118        assert!(connection
1119            .execute(
1120                "INSERT INTO items(id, parent_id, code, description) VALUES (3, 99, 'B', 'missing parent')",
1121                [],
1122            )
1123            .is_err());
1124        assert_eq!(
1125            connection
1126                .query_row("SELECT description FROM items WHERE id=1", [], |row| row
1127                    .get::<_, String>(
1128                    0
1129                ))
1130                .unwrap(),
1131            "first"
1132        );
1133    }
1134
1135    #[test]
1136    fn cyclic_foreign_key_tables_apply_with_their_declared_metadata() {
1137        let mut id = column("id");
1138        id.column_type = ColumnType::Integer;
1139        id.primary_key = true;
1140        let reference = |name: &str, target: &str| ColumnState {
1141            field_name: name.into(),
1142            name: name.into(),
1143            column_type: ColumnType::Integer,
1144            nullable: true,
1145            primary_key: false,
1146            unique: false,
1147            default: None,
1148            check: None,
1149            choices: None,
1150            references: Some(ForeignKeyState {
1151                target: target.into(),
1152                on_delete: Some("set null".into()),
1153            }),
1154            auto_now_add: false,
1155            auto_now: false,
1156        };
1157        let migration = Migration::new(
1158            MigrationId {
1159                app: "cycle".into(),
1160                name: "0001".into(),
1161            },
1162            vec![],
1163            vec![
1164                MigrationOperation::CreateTable {
1165                    table: TableState {
1166                        name: "left_items".into(),
1167                        columns: vec![id.clone(), reference("right_id", "right_items(id)")],
1168                        unique_constraints: vec![],
1169                        indexes: vec![],
1170                    },
1171                },
1172                MigrationOperation::CreateTable {
1173                    table: TableState {
1174                        name: "right_items".into(),
1175                        columns: vec![id, reference("left_id", "left_items(id)")],
1176                        unique_constraints: vec![],
1177                        indexes: vec![],
1178                    },
1179                },
1180            ],
1181        );
1182        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1183        connection.execute_batch("PRAGMA foreign_keys=ON").unwrap();
1184        apply_operation_migrations_on_connection(
1185            &mut connection,
1186            MigrationGraph::new(vec![migration]).unwrap(),
1187        )
1188        .unwrap();
1189        let left_target: String = connection
1190            .query_row(
1191                "SELECT \"table\" FROM pragma_foreign_key_list('left_items')",
1192                [],
1193                |row| row.get(0),
1194            )
1195            .unwrap();
1196        let right_target: String = connection
1197            .query_row(
1198                "SELECT \"table\" FROM pragma_foreign_key_list('right_items')",
1199                [],
1200                |row| row.get(0),
1201            )
1202            .unwrap();
1203        assert_eq!(
1204            (left_target, right_target),
1205            ("right_items".into(), "left_items".into())
1206        );
1207    }
1208
1209    #[test]
1210    fn busy_write_lock_has_a_bounded_diagnostic_error() {
1211        let path = std::env::temp_dir().join(format!(
1212            "che_orm_lock_{}.sqlite",
1213            time::OffsetDateTime::now_utc().unix_timestamp_nanos()
1214        ));
1215        let first = crate::rusqlite::Connection::open(&path).unwrap();
1216        first.execute_batch("BEGIN EXCLUSIVE").unwrap();
1217        let mut second = crate::rusqlite::Connection::open(&path).unwrap();
1218        let error = begin_immediate(&mut second, Duration::from_millis(10)).unwrap_err();
1219        assert!(
1220            error
1221                .to_string()
1222                .contains("migration write lock within 10 ms")
1223        );
1224        first.execute_batch("ROLLBACK").unwrap();
1225        std::fs::remove_file(path).unwrap();
1226    }
1227
1228    #[test]
1229    fn transaction_commands_cannot_commit_partial_migrations() {
1230        for sql in [
1231            "CREATE TABLE partial(id); COMMIT;",
1232            "CREATE TABLE partial(id); ROLLBACK;",
1233            "CREATE TABLE partial(id); SAVEPOINT sneaky;",
1234        ] {
1235            let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1236            connection
1237                .pragma_update(None, "foreign_keys", true)
1238                .unwrap();
1239            let graph = MigrationGraph::new(vec![Migration::new(
1240                MigrationId {
1241                    app: "test".into(),
1242                    name: "0001".into(),
1243                },
1244                vec![],
1245                vec![MigrationOperation::RunSql {
1246                    forward: sql.into(),
1247                    reverse: None,
1248                    state_operations: vec![],
1249                }],
1250            )])
1251            .unwrap();
1252            assert!(apply_operation_migrations_on_connection(&mut connection, graph).is_err());
1253            assert!(connection.is_autocommit());
1254            assert!(
1255                connection
1256                    .query_row("PRAGMA foreign_keys", [], |row| row.get::<_, bool>(0))
1257                    .unwrap()
1258            );
1259            assert_eq!(connection.query_row("SELECT count(*) FROM sqlite_master WHERE name IN ('partial', 'che_migration_history')", [], |row| row.get::<_, i64>(0)).unwrap(), 0);
1260        }
1261    }
1262
1263    #[test]
1264    fn trigger_begin_is_not_transaction_control_and_fk_setting_is_restored() {
1265        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1266        connection
1267            .pragma_update(None, "foreign_keys", false)
1268            .unwrap();
1269        let graph = MigrationGraph::new(vec![Migration::new(
1270            MigrationId { app: "test".into(), name: "0001".into() }, vec![],
1271            vec![MigrationOperation::RunSql { forward: "CREATE TABLE items(id); CREATE TRIGGER check_item BEFORE INSERT ON items BEGIN SELECT RAISE(ABORT, 'blocked'); END;".into(), reverse: None, state_operations: vec![] }],
1272        )]).unwrap();
1273        assert_eq!(
1274            apply_operation_migrations_on_connection(&mut connection, graph.clone()).unwrap(),
1275            1
1276        );
1277        assert_eq!(
1278            apply_operation_migrations_on_connection(&mut connection, graph).unwrap(),
1279            0
1280        );
1281        assert!(
1282            !connection
1283                .query_row("PRAGMA foreign_keys", [], |row| row.get::<_, bool>(0))
1284                .unwrap()
1285        );
1286        assert!(
1287            connection
1288                .execute("INSERT INTO items VALUES(1)", [])
1289                .is_err()
1290        );
1291    }
1292
1293    #[test]
1294    fn target_applies_only_its_forward_closure_and_rejects_rollback() {
1295        let initial = Migration::new(
1296            MigrationId {
1297                app: "tasks".into(),
1298                name: "0001".into(),
1299            },
1300            vec![],
1301            vec![MigrationOperation::RunSql {
1302                forward: "CREATE TABLE task_initial(id)".into(),
1303                reverse: None,
1304                state_operations: vec![],
1305            }],
1306        );
1307        let target = Migration::new(
1308            MigrationId {
1309                app: "tasks".into(),
1310                name: "0002".into(),
1311            },
1312            vec![initial.id.clone()],
1313            vec![MigrationOperation::RunSql {
1314                forward: "CREATE TABLE task_target(id)".into(),
1315                reverse: None,
1316                state_operations: vec![],
1317            }],
1318        );
1319        let independent = Migration::new(
1320            MigrationId {
1321                app: "audit".into(),
1322                name: "0001".into(),
1323            },
1324            vec![],
1325            vec![MigrationOperation::RunSql {
1326                forward: "CREATE TABLE audit_initial(id)".into(),
1327                reverse: None,
1328                state_operations: vec![],
1329            }],
1330        );
1331        let graph =
1332            MigrationGraph::new(vec![initial.clone(), target.clone(), independent]).unwrap();
1333        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1334        assert_eq!(
1335            apply_operation_migrations_to_on_connection(
1336                &mut connection,
1337                graph.clone(),
1338                Some(target.id.clone()),
1339            )
1340            .unwrap(),
1341            2
1342        );
1343        assert_eq!(
1344            connection
1345                .query_row(
1346                    "SELECT count(*) FROM sqlite_master WHERE name = 'audit_initial'",
1347                    [],
1348                    |row| row.get::<_, i64>(0)
1349                )
1350                .unwrap(),
1351            0
1352        );
1353        let count: i64 = connection
1354            .query_row("SELECT count(*) FROM che_migration_history", [], |row| {
1355                row.get(0)
1356            })
1357            .unwrap();
1358        assert_eq!(count, 2);
1359        assert!(
1360            apply_operation_migrations_to_on_connection(&mut connection, graph, Some(initial.id),)
1361                .unwrap_err()
1362                .to_string()
1363                .contains("rollback is not supported")
1364        );
1365        assert_eq!(
1366            connection
1367                .query_row("SELECT count(*) FROM che_migration_history", [], |row| row
1368                    .get::<_, i64>(
1369                    0
1370                ))
1371                .unwrap(),
1372            2
1373        );
1374    }
1375
1376    #[test]
1377    fn failed_migration_rolls_back_only_its_own_transaction() {
1378        let first = Migration::new(
1379            MigrationId {
1380                app: "tasks".into(),
1381                name: "0001".into(),
1382            },
1383            vec![],
1384            vec![MigrationOperation::RunSql {
1385                forward: "CREATE TABLE first_migration(id)".into(),
1386                reverse: None,
1387                state_operations: vec![],
1388            }],
1389        );
1390        let second = Migration::new(
1391            MigrationId {
1392                app: "tasks".into(),
1393                name: "0002".into(),
1394            },
1395            vec![first.id.clone()],
1396            vec![MigrationOperation::RunSql {
1397                forward: "CREATE TABLE failed_migration(id); INSERT INTO absent_table VALUES (1)"
1398                    .into(),
1399                reverse: None,
1400                state_operations: vec![],
1401            }],
1402        );
1403        let graph = MigrationGraph::new(vec![first.clone(), second]).unwrap();
1404        let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1405        assert!(apply_operation_migrations_on_connection(&mut connection, graph).is_err());
1406        assert_eq!(
1407            connection
1408                .query_row(
1409                    "SELECT count(*) FROM sqlite_master WHERE name = 'first_migration'",
1410                    [],
1411                    |row| row.get::<_, i64>(0),
1412                )
1413                .unwrap(),
1414            1
1415        );
1416        assert_eq!(
1417            connection
1418                .query_row(
1419                    "SELECT count(*) FROM sqlite_master WHERE name = 'failed_migration'",
1420                    [],
1421                    |row| row.get::<_, i64>(0),
1422                )
1423                .unwrap(),
1424            0
1425        );
1426        let history = applied_operation_migrations(&connection).unwrap();
1427        assert_eq!(history.len(), 1);
1428        assert_eq!(
1429            history.get(&(first.id.app, first.id.name)),
1430            Some(&first.checksum)
1431        );
1432    }
1433}