1use crate::ModelSnapshot;
2use sql_orm_core::OrmError;
3use std::fs;
4use std::path::{Path, PathBuf};
5use std::time::{SystemTime, UNIX_EPOCH};
6
7const MIGRATIONS_DIR: &str = "migrations";
8const ORM_VERSION: &str = env!("CARGO_PKG_VERSION");
9
10#[derive(Debug, Clone, PartialEq, Eq)]
11pub struct MigrationScaffold {
12 pub id: String,
13 pub name: String,
14 pub directory: PathBuf,
15}
16
17impl MigrationScaffold {
18 pub fn up_sql_path(&self) -> PathBuf {
19 self.directory.join("up.sql")
20 }
21
22 pub fn down_sql_path(&self) -> PathBuf {
23 self.directory.join("down.sql")
24 }
25
26 pub fn snapshot_path(&self) -> PathBuf {
27 self.directory.join("model_snapshot.json")
28 }
29}
30
31#[derive(Debug, Clone, PartialEq, Eq)]
32pub struct MigrationEntry {
33 pub id: String,
34 pub name: String,
35 pub directory: PathBuf,
36 pub up_sql_path: PathBuf,
37 pub down_sql_path: PathBuf,
38 pub snapshot_path: PathBuf,
39}
40
41pub fn create_migration_scaffold(root: &Path, name: &str) -> Result<MigrationScaffold, OrmError> {
42 create_migration_scaffold_with_snapshot(root, name, &ModelSnapshot::default())
43}
44
45pub fn create_migration_scaffold_with_snapshot(
46 root: &Path,
47 name: &str,
48 snapshot: &ModelSnapshot,
49) -> Result<MigrationScaffold, OrmError> {
50 if name.trim().is_empty() {
51 return Err(OrmError::new("migration name cannot be empty"));
52 }
53
54 let slug = slugify(name);
55 let timestamp = migration_timestamp()?;
56 let id = format!("{timestamp}_{slug}");
57 let migrations_dir = root.join(MIGRATIONS_DIR);
58 let directory = migrations_dir.join(&id);
59
60 fs::create_dir_all(&directory)
61 .map_err(|_| OrmError::new("failed to create migration directory"))?;
62 fs::write(directory.join("up.sql"), initial_up_sql_template(&id))
63 .map_err(|_| OrmError::new("failed to write migration up.sql"))?;
64 fs::write(directory.join("down.sql"), initial_down_sql_template(&id))
65 .map_err(|_| OrmError::new("failed to write migration down.sql"))?;
66 write_model_snapshot(&directory.join("model_snapshot.json"), snapshot)?;
67
68 Ok(MigrationScaffold {
69 id,
70 name: name.to_string(),
71 directory,
72 })
73}
74
75fn initial_up_sql_template(id: &str) -> String {
76 format!("-- Migration: {id}\n-- SQL Server DDL for this migration.\n")
77}
78
79fn initial_down_sql_template(id: &str) -> String {
80 format!(
81 "-- Migration: {id}\n-- Manual rollback SQL for this editable migration.\n-- The current MVP does not execute down.sql automatically.\n"
82 )
83}
84
85pub fn write_model_snapshot(path: &Path, snapshot: &ModelSnapshot) -> Result<(), OrmError> {
86 fs::write(path, snapshot.to_json_pretty()?)
87 .map_err(|_| OrmError::new("failed to write migration model snapshot"))
88}
89
90pub fn write_migration_up_sql(path: &Path, sql_statements: &[String]) -> Result<(), OrmError> {
91 let sql = if sql_statements.is_empty() {
92 String::from("-- No schema changes detected.\n")
93 } else {
94 let mut sql = sql_statements.join(";\n\n");
95 sql.push_str(";\n");
96 sql
97 };
98
99 fs::write(path, sql).map_err(|_| OrmError::new("failed to write migration up.sql"))
100}
101
102pub fn write_migration_down_sql(path: &Path, sql_statements: &[String]) -> Result<(), OrmError> {
103 let sql = if sql_statements.is_empty() {
104 String::from("-- No reversible schema changes detected.\n")
105 } else {
106 let mut sql = sql_statements.join(";\n\n");
107 sql.push_str(";\n");
108 sql
109 };
110
111 fs::write(path, sql).map_err(|_| OrmError::new("failed to write migration down.sql"))
112}
113
114pub fn read_model_snapshot(path: &Path) -> Result<ModelSnapshot, OrmError> {
115 let json = fs::read_to_string(path)
116 .map_err(|_| OrmError::new("failed to read migration model snapshot"))?;
117 ModelSnapshot::from_json(&json)
118}
119
120pub fn list_migrations(root: &Path) -> Result<Vec<MigrationEntry>, OrmError> {
121 let migrations_dir = root.join(MIGRATIONS_DIR);
122 if !migrations_dir.exists() {
123 return Ok(Vec::new());
124 }
125
126 let mut entries = fs::read_dir(&migrations_dir)
127 .map_err(|_| OrmError::new("failed to read migrations directory"))?
128 .filter_map(Result::ok)
129 .filter(|entry| entry.file_type().map(|kind| kind.is_dir()).unwrap_or(false))
130 .filter_map(|entry| parse_migration_entry(entry.path()))
131 .collect::<Vec<_>>();
132
133 entries.sort_by(|left, right| left.id.cmp(&right.id));
134 Ok(entries)
135}
136
137pub fn latest_migration(root: &Path) -> Result<Option<MigrationEntry>, OrmError> {
138 Ok(list_migrations(root)?.into_iter().last())
139}
140
141pub fn read_latest_model_snapshot(
142 root: &Path,
143) -> Result<Option<(MigrationEntry, ModelSnapshot)>, OrmError> {
144 let Some(migration) = latest_migration(root)? else {
145 return Ok(None);
146 };
147
148 let snapshot = read_model_snapshot(&migration.snapshot_path)?;
149 Ok(Some((migration, snapshot)))
150}
151
152pub fn build_database_update_script(
153 root: &Path,
154 history_table_sql: &str,
155) -> Result<String, OrmError> {
156 let migrations = list_migrations(root)?;
157 let mut script = vec![
158 "-- sql-orm database update".to_string(),
159 "SET ANSI_NULLS ON;".to_string(),
160 "SET ANSI_PADDING ON;".to_string(),
161 "SET ANSI_WARNINGS ON;".to_string(),
162 "SET ARITHABORT ON;".to_string(),
163 "SET CONCAT_NULL_YIELDS_NULL ON;".to_string(),
164 "SET QUOTED_IDENTIFIER ON;".to_string(),
165 "SET NUMERIC_ROUNDABORT OFF;".to_string(),
166 history_table_sql.to_string(),
167 ];
168
169 for migration in migrations {
170 let up_sql = fs::read_to_string(&migration.up_sql_path)
171 .map_err(|_| OrmError::new("failed to read migration up.sql"))?;
172 let checksum = checksum_hex(up_sql.as_bytes());
173 let statements = split_sql_statements(&up_sql);
174 let body = if statements.is_empty() {
175 String::new()
176 } else {
177 statements
178 .iter()
179 .map(|statement| format!(" EXEC(N'{}');", escape_sql_literal(statement)))
180 .collect::<Vec<_>>()
181 .join("\n")
182 + "\n"
183 };
184 script.push(render_idempotent_migration_block(
185 &migration.id,
186 &migration.name,
187 &checksum,
188 &body,
189 ));
190 }
191
192 Ok(script.join("\n\n"))
193}
194
195pub fn build_database_downgrade_script(
196 root: &Path,
197 history_table_sql: &str,
198 target: &str,
199) -> Result<String, OrmError> {
200 let target = target.trim();
201 if target.is_empty() {
202 return Err(OrmError::new(
203 "database downgrade requires an explicit target",
204 ));
205 }
206
207 let migrations = list_migrations(root)?;
208 if target != "0" && !migrations.iter().any(|migration| migration.id == target) {
209 return Err(OrmError::new(format!(
210 "database downgrade target `{target}` is not a known local migration"
211 )));
212 }
213
214 let rollback_migrations = migrations
215 .iter()
216 .filter(|migration| target == "0" || migration.id.as_str() > target)
217 .rev()
218 .map(|migration| {
219 let up_sql = fs::read_to_string(&migration.up_sql_path).map_err(|_| {
220 OrmError::new(format!(
221 "database downgrade migration `{}` is missing local up.sql for checksum validation",
222 migration.id
223 ))
224 })?;
225 let down_sql = fs::read_to_string(&migration.down_sql_path).map_err(|_| {
226 OrmError::new(format!(
227 "database downgrade migration `{}` is missing local down.sql",
228 migration.id
229 ))
230 })?;
231 if is_unresolved_down_sql_template(&down_sql) {
232 return Err(OrmError::new(format!(
233 "database downgrade migration `{}` has no reversible payload in down.sql; edit down.sql with executable rollback SQL",
234 migration.id
235 )));
236 }
237 let down_statements = split_sql_statements(&down_sql);
238 if down_statements.is_empty() {
239 return Err(OrmError::new(format!(
240 "database downgrade migration `{}` has no executable down.sql statements",
241 migration.id
242 )));
243 }
244
245 Ok(DowngradeMigrationBlock {
246 id: migration.id.clone(),
247 checksum: checksum_hex(up_sql.as_bytes()),
248 down_statements,
249 })
250 })
251 .collect::<Result<Vec<_>, OrmError>>()?;
252
253 let mut script = vec![
254 "-- sql-orm database downgrade".to_string(),
255 "SET ANSI_NULLS ON;".to_string(),
256 "SET ANSI_PADDING ON;".to_string(),
257 "SET ANSI_WARNINGS ON;".to_string(),
258 "SET ARITHABORT ON;".to_string(),
259 "SET CONCAT_NULL_YIELDS_NULL ON;".to_string(),
260 "SET QUOTED_IDENTIFIER ON;".to_string(),
261 "SET NUMERIC_ROUNDABORT OFF;".to_string(),
262 history_table_sql.to_string(),
263 render_downgrade_history_guard(&migrations, target),
264 ];
265
266 for migration in rollback_migrations {
267 script.push(render_idempotent_downgrade_block(&migration));
268 }
269
270 Ok(script.join("\n\n"))
271}
272
273fn render_idempotent_migration_block(id: &str, name: &str, checksum: &str, body: &str) -> String {
274 format!(
275 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'{id}' AND [checksum] <> N'{checksum}')\nBEGIN\n THROW 50001, N'sql-orm migration checksum mismatch for {id}', 1;\nEND\n\nIF NOT EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'{id}')\nBEGIN\n BEGIN TRY\n BEGIN TRANSACTION;\n{body} INSERT INTO [dbo].[__sql_orm_migrations] ([id], [name], [checksum], [orm_version]) VALUES (N'{id}', N'{name}', N'{checksum}', N'{version}');\n COMMIT TRANSACTION;\n END TRY\n BEGIN CATCH\n IF XACT_STATE() <> 0\n ROLLBACK TRANSACTION;\n THROW;\n END CATCH\nEND",
276 id = id,
277 name = name,
278 checksum = checksum,
279 version = ORM_VERSION,
280 body = body,
281 )
282}
283
284#[derive(Debug, Clone, PartialEq, Eq)]
285struct DowngradeMigrationBlock {
286 id: String,
287 checksum: String,
288 down_statements: Vec<String>,
289}
290
291fn render_downgrade_history_guard(migrations: &[MigrationEntry], target: &str) -> String {
292 let local_history_guard = if migrations.is_empty() {
293 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations])\nBEGIN\n THROW 50002, N'sql-orm migration history contains entries missing from local migrations', 1;\nEND".to_string()
294 } else {
295 let known_ids = migrations
296 .iter()
297 .map(|migration| format!("N'{}'", escape_sql_literal(&migration.id)))
298 .collect::<Vec<_>>()
299 .join(", ");
300 format!(
301 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] NOT IN ({known_ids}))\nBEGIN\n THROW 50002, N'sql-orm migration history contains entries missing from local migrations', 1;\nEND",
302 known_ids = known_ids,
303 )
304 };
305 let target_literal = escape_sql_literal(target);
306 let target_guard = if target == "0" {
307 String::new()
308 } else {
309 format!(
310 "\n\nIF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] > N'{target}')\n AND NOT EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'{target}')\nBEGIN\n THROW 50003, N'sql-orm downgrade target {target} is not applied in migration history', 1;\nEND",
311 target = target_literal,
312 )
313 };
314
315 format!(
316 "{local_history_guard}{target_guard}",
317 local_history_guard = local_history_guard,
318 target_guard = target_guard,
319 )
320}
321
322fn render_idempotent_downgrade_block(migration: &DowngradeMigrationBlock) -> String {
323 let id = escape_sql_literal(&migration.id);
324 let checksum = escape_sql_literal(&migration.checksum);
325 let body = migration
326 .down_statements
327 .iter()
328 .map(|statement| format!(" EXEC(N'{}');", escape_sql_literal(statement)))
329 .collect::<Vec<_>>()
330 .join("\n");
331
332 format!(
333 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'{id}' AND [checksum] <> N'{checksum}')\nBEGIN\n THROW 50001, N'sql-orm migration checksum mismatch for {id}', 1;\nEND\n\nIF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'{id}')\nBEGIN\n BEGIN TRY\n BEGIN TRANSACTION;\n{body}\n DELETE FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'{id}';\n COMMIT TRANSACTION;\n END TRY\n BEGIN CATCH\n IF XACT_STATE() <> 0\n ROLLBACK TRANSACTION;\n THROW;\n END CATCH\nEND",
334 id = id,
335 checksum = checksum,
336 body = body,
337 )
338}
339
340fn parse_migration_entry(path: PathBuf) -> Option<MigrationEntry> {
341 let file_name = path.file_name()?.to_str()?;
342 let (timestamp, slug) = file_name.split_once('_')?;
343 if timestamp.is_empty() || slug.is_empty() {
344 return None;
345 }
346
347 Some(MigrationEntry {
348 id: file_name.to_string(),
349 name: slug.replace('_', " "),
350 up_sql_path: path.join("up.sql"),
351 down_sql_path: path.join("down.sql"),
352 snapshot_path: path.join("model_snapshot.json"),
353 directory: path,
354 })
355}
356
357fn migration_timestamp() -> Result<String, OrmError> {
358 let duration = SystemTime::now()
359 .duration_since(UNIX_EPOCH)
360 .map_err(|_| OrmError::new("system clock is before UNIX_EPOCH"))?;
361 Ok(duration.as_nanos().to_string())
362}
363
364fn slugify(name: &str) -> String {
365 let mut slug = String::new();
366 let mut previous_was_separator = false;
367
368 for ch in name.chars() {
369 if ch.is_ascii_alphanumeric() {
370 slug.push(ch.to_ascii_lowercase());
371 previous_was_separator = false;
372 } else if !previous_was_separator {
373 slug.push('_');
374 previous_was_separator = true;
375 }
376 }
377
378 slug.trim_matches('_').to_string()
379}
380
381fn checksum_hex(bytes: &[u8]) -> String {
382 let mut hash = 0xcbf29ce484222325u64;
383 for byte in bytes {
384 hash ^= u64::from(*byte);
385 hash = hash.wrapping_mul(0x100000001b3);
386 }
387
388 format!("{hash:016x}")
389}
390
391fn escape_sql_literal(sql: &str) -> String {
392 sql.replace('\'', "''")
393}
394
395fn is_unresolved_down_sql_template(sql: &str) -> bool {
396 let mut saw_executable_statement = false;
397 let mut saw_unresolved_marker = false;
398
399 for line in sql.lines() {
400 let trimmed = line.trim();
401 if trimmed.is_empty() {
402 continue;
403 }
404
405 if trimmed.starts_with("--") {
406 let lower = trimmed.to_ascii_lowercase();
407 if lower.contains("manual rollback sql")
408 || lower.contains("does not execute down.sql automatically")
409 || lower.contains("no reversible schema changes detected")
410 {
411 saw_unresolved_marker = true;
412 }
413 continue;
414 }
415
416 saw_executable_statement = true;
417 }
418
419 saw_unresolved_marker && !saw_executable_statement
420}
421
422fn split_sql_statements(sql: &str) -> Vec<String> {
423 sql.split(';')
424 .map(str::trim)
425 .filter(|statement| !statement.is_empty())
426 .filter(|statement| {
427 statement.lines().any(|line| {
428 let trimmed = line.trim();
429 !trimmed.is_empty() && !trimmed.starts_with("--")
430 })
431 })
432 .map(|statement| format!("{statement};"))
433 .collect()
434}
435
436#[cfg(test)]
437mod tests {
438 use super::{
439 build_database_downgrade_script, build_database_update_script, checksum_hex,
440 create_migration_scaffold, create_migration_scaffold_with_snapshot, latest_migration,
441 list_migrations, read_latest_model_snapshot, read_model_snapshot, write_migration_down_sql,
442 write_migration_up_sql, write_model_snapshot,
443 };
444 use crate::{ModelSnapshot, SchemaSnapshot};
445 use std::fs;
446 use std::path::{Path, PathBuf};
447 use std::time::{SystemTime, UNIX_EPOCH};
448
449 fn temp_project_root() -> PathBuf {
450 let unique = SystemTime::now()
451 .duration_since(UNIX_EPOCH)
452 .unwrap()
453 .as_nanos();
454 let path = std::env::temp_dir().join(format!("sql_orm_migrate_{unique}"));
455 fs::create_dir_all(&path).unwrap();
456 path
457 }
458
459 fn write_local_migration(root: &Path, id: &str, up_sql: &str, down_sql: &str) {
460 let migration_dir = root.join("migrations").join(id);
461 fs::create_dir_all(&migration_dir).unwrap();
462 fs::write(migration_dir.join("up.sql"), up_sql).unwrap();
463 fs::write(migration_dir.join("down.sql"), down_sql).unwrap();
464 fs::write(
465 migration_dir.join("model_snapshot.json"),
466 "{ \"schemas\": [] }",
467 )
468 .unwrap();
469 }
470
471 #[test]
472 fn creates_scaffolded_migration_files() {
473 let root = temp_project_root();
474
475 let scaffold = create_migration_scaffold(&root, "Create Customers").unwrap();
476
477 assert!(scaffold.id.contains("create_customers"));
478 assert!(scaffold.up_sql_path().exists());
479 assert!(scaffold.down_sql_path().exists());
480 assert!(scaffold.snapshot_path().exists());
481 assert!(!scaffold.directory.join("migration.rs").exists());
482
483 assert_eq!(
484 fs::read_to_string(scaffold.up_sql_path()).unwrap(),
485 format!(
486 "-- Migration: {}\n-- SQL Server DDL for this migration.\n",
487 scaffold.id
488 )
489 );
490 assert_eq!(
491 fs::read_to_string(scaffold.down_sql_path()).unwrap(),
492 format!(
493 "-- Migration: {}\n-- Manual rollback SQL for this editable migration.\n-- The current MVP does not execute down.sql automatically.\n",
494 scaffold.id
495 )
496 );
497
498 let snapshot = read_model_snapshot(&scaffold.snapshot_path()).unwrap();
499 assert_eq!(snapshot, ModelSnapshot::default());
500 }
501
502 #[test]
503 fn writes_and_reads_model_snapshot_artifact() {
504 let root = temp_project_root();
505 let snapshot_path = root.join("model_snapshot.json");
506 let snapshot = ModelSnapshot::new(vec![SchemaSnapshot::new("sales", Vec::new())]);
507
508 write_model_snapshot(&snapshot_path, &snapshot).unwrap();
509
510 assert_eq!(read_model_snapshot(&snapshot_path).unwrap(), snapshot);
511 }
512
513 #[test]
514 fn writes_generated_down_sql_artifact() {
515 let root = temp_project_root();
516 let down_sql_path = root.join("down.sql");
517
518 write_migration_down_sql(
519 &down_sql_path,
520 &[
521 "DROP TABLE [sales].[customers]".to_string(),
522 "DROP SCHEMA [sales]".to_string(),
523 ],
524 )
525 .unwrap();
526
527 assert_eq!(
528 fs::read_to_string(down_sql_path).unwrap(),
529 "DROP TABLE [sales].[customers];\n\nDROP SCHEMA [sales];\n"
530 );
531 }
532
533 #[test]
534 fn creates_scaffold_with_provided_model_snapshot() {
535 let root = temp_project_root();
536 let snapshot = ModelSnapshot::new(vec![SchemaSnapshot::new("sales", Vec::new())]);
537
538 let scaffold =
539 create_migration_scaffold_with_snapshot(&root, "Create Sales", &snapshot).unwrap();
540
541 assert_eq!(
542 read_model_snapshot(&scaffold.snapshot_path()).unwrap(),
543 snapshot
544 );
545 }
546
547 #[test]
548 fn lists_migrations_in_sorted_order() {
549 let root = temp_project_root();
550 let migrations_dir = root.join("migrations");
551 fs::create_dir_all(migrations_dir.join("200_create_orders")).unwrap();
552 fs::create_dir_all(migrations_dir.join("100_create_customers")).unwrap();
553
554 let migrations = list_migrations(&root).unwrap();
555
556 assert_eq!(migrations.len(), 2);
557 assert_eq!(migrations[0].id, "100_create_customers");
558 assert_eq!(migrations[1].id, "200_create_orders");
559 }
560
561 #[test]
562 fn returns_latest_migration_in_lexical_order() {
563 let root = temp_project_root();
564 let migrations_dir = root.join("migrations");
565 fs::create_dir_all(migrations_dir.join("100_create_customers")).unwrap();
566 fs::create_dir_all(migrations_dir.join("200_create_orders")).unwrap();
567
568 let latest = latest_migration(&root).unwrap().unwrap();
569
570 assert_eq!(latest.id, "200_create_orders");
571 }
572
573 #[test]
574 fn reads_latest_model_snapshot_from_last_local_migration() {
575 let root = temp_project_root();
576 let older_dir = root.join("migrations/100_create_customers");
577 let newer_dir = root.join("migrations/200_create_orders");
578 fs::create_dir_all(&older_dir).unwrap();
579 fs::create_dir_all(&newer_dir).unwrap();
580 fs::write(older_dir.join("up.sql"), "-- noop").unwrap();
581 fs::write(older_dir.join("down.sql"), "-- noop").unwrap();
582 fs::write(
583 older_dir.join("model_snapshot.json"),
584 "{\n \"schemas\": []\n}\n",
585 )
586 .unwrap();
587 fs::write(newer_dir.join("up.sql"), "-- noop").unwrap();
588 fs::write(newer_dir.join("down.sql"), "-- noop").unwrap();
589 fs::write(
590 newer_dir.join("model_snapshot.json"),
591 "{\n \"schemas\": [\n {\n \"name\": \"sales\",\n \"tables\": []\n }\n ]\n}\n",
592 )
593 .unwrap();
594
595 let (migration, snapshot) = read_latest_model_snapshot(&root).unwrap().unwrap();
596
597 assert_eq!(migration.id, "200_create_orders");
598 assert!(snapshot.schema("sales").is_some());
599 }
600
601 #[test]
602 fn builds_database_update_script_with_history_inserts() {
603 let root = temp_project_root();
604 let scaffold = create_migration_scaffold(&root, "Create Customers").unwrap();
605 fs::write(
606 scaffold.directory.join("up.sql"),
607 "CREATE SCHEMA [sales];\nCREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
608 )
609 .unwrap();
610
611 let script =
612 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
613 .unwrap();
614
615 assert!(script.contains("CREATE TABLE [dbo].[__sql_orm_migrations]"));
616 assert!(script.contains("SET ANSI_NULLS ON;"));
617 assert!(script.contains("SET QUOTED_IDENTIFIER ON;"));
618 assert!(script.contains("SET NUMERIC_ROUNDABORT OFF;"));
619 assert!(script.contains("IF NOT EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations]"));
620 assert!(script.contains("IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations]"));
621 assert!(script.contains("THROW 50001, N'sql-orm migration checksum mismatch"));
622 assert!(script.contains("BEGIN TRY"));
623 assert!(script.contains("BEGIN TRANSACTION;"));
624 assert!(script.contains("EXEC(N'CREATE SCHEMA [sales];');"));
625 assert!(
626 script.contains("EXEC(N'CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);');")
627 );
628 assert!(script.contains("INSERT INTO [dbo].[__sql_orm_migrations]"));
629 assert!(script.contains("COMMIT TRANSACTION;"));
630 assert!(script.contains("ROLLBACK TRANSACTION;"));
631 }
632
633 #[test]
634 fn builds_database_update_script_without_empty_exec_blocks() {
635 let root = temp_project_root();
636 let scaffold = create_migration_scaffold(&root, "Noop").unwrap();
637 fs::write(
638 scaffold.directory.join("up.sql"),
639 "-- comment only migration\n\n-- still intentionally empty\n",
640 )
641 .unwrap();
642
643 let script =
644 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
645 .unwrap();
646
647 assert!(!script.contains("EXEC(N'');"));
648 assert!(script.contains("INSERT INTO [dbo].[__sql_orm_migrations]"));
649 }
650
651 #[test]
652 fn builds_database_downgrade_script_in_reverse_order() {
653 let root = temp_project_root();
654 write_local_migration(
655 &root,
656 "100_create_customers",
657 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
658 "DROP TABLE [sales].[customers];",
659 );
660 write_local_migration(
661 &root,
662 "200_create_orders",
663 "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);",
664 "DROP TABLE [sales].[orders];",
665 );
666 write_local_migration(
667 &root,
668 "300_create_lines",
669 "CREATE TABLE [sales].[order_lines] ([id] bigint NOT NULL);",
670 "DROP TABLE [sales].[order_lines];",
671 );
672
673 let script = build_database_downgrade_script(
674 &root,
675 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
676 "100_create_customers",
677 )
678 .unwrap();
679
680 let lines_pos = script.find("DROP TABLE [sales].[order_lines]").unwrap();
681 let orders_pos = script.find("DROP TABLE [sales].[orders]").unwrap();
682 assert!(lines_pos < orders_pos);
683 assert!(!script.contains("DROP TABLE [sales].[customers]"));
684 assert!(script.contains("CREATE TABLE [dbo].[__sql_orm_migrations]"));
685 assert!(
686 script.contains(
687 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] NOT IN"
688 )
689 );
690 assert!(script.contains("sql-orm downgrade target 100_create_customers is not applied"));
691 assert!(
692 script.contains(
693 "THROW 50001, N'sql-orm migration checksum mismatch for 300_create_lines'"
694 )
695 );
696 assert!(script.contains("BEGIN TRANSACTION;"));
697 assert!(script.contains(
698 "DELETE FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'300_create_lines';"
699 ));
700 assert!(script.contains("ROLLBACK TRANSACTION;"));
701 }
702
703 #[test]
704 fn builds_database_downgrade_script_to_empty_database_sentinel() {
705 let root = temp_project_root();
706 write_local_migration(
707 &root,
708 "100_create_customers",
709 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
710 "DROP TABLE [sales].[customers];",
711 );
712
713 let script = build_database_downgrade_script(
714 &root,
715 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
716 "0",
717 )
718 .unwrap();
719
720 assert!(script.contains("DROP TABLE [sales].[customers]"));
721 assert!(!script.contains("downgrade target 0 is not applied"));
722 }
723
724 #[test]
725 fn database_downgrade_script_with_no_local_migrations_rejects_any_history_rows() {
726 let root = temp_project_root();
727
728 let script = build_database_downgrade_script(
729 &root,
730 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
731 "0",
732 )
733 .unwrap();
734
735 assert!(script.contains("IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations])"));
736 assert!(script.contains("history contains entries missing from local migrations"));
737 assert!(!script.contains("NOT IN (NULL)"));
738 }
739
740 #[test]
741 fn database_downgrade_requires_explicit_target_and_renders_checksum_guards() {
742 let root = temp_project_root();
743 let orders_up = "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);";
744 write_local_migration(
745 &root,
746 "100_create_customers",
747 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
748 "DROP TABLE [sales].[customers];",
749 );
750 write_local_migration(
751 &root,
752 "200_create_orders",
753 orders_up,
754 "DROP TABLE [sales].[orders];",
755 );
756
757 let error = build_database_downgrade_script(
758 &root,
759 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
760 " ",
761 )
762 .unwrap_err();
763 assert!(
764 error
765 .to_string()
766 .contains("database downgrade requires an explicit target")
767 );
768
769 let script = build_database_downgrade_script(
770 &root,
771 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
772 "100_create_customers",
773 )
774 .unwrap();
775 let orders_checksum = checksum_hex(orders_up.as_bytes());
776 let checksum_guard =
777 format!("WHERE [id] = N'200_create_orders' AND [checksum] <> N'{orders_checksum}'");
778
779 assert!(script.contains("N'100_create_customers', N'200_create_orders'"));
780 assert!(script.contains(&checksum_guard));
781 assert!(
782 script
783 .find("sql-orm migration checksum mismatch for 200_create_orders")
784 .unwrap()
785 < script.find("DROP TABLE [sales].[orders]").unwrap()
786 );
787 assert_eq!(script.matches("DROP TABLE [sales].[orders]").count(), 1);
788 assert!(!script.contains("DROP TABLE [sales].[customers]"));
789 }
790
791 #[test]
792 fn database_downgrade_rejects_unknown_target_and_empty_down_sql() {
793 let root = temp_project_root();
794 write_local_migration(
795 &root,
796 "100_create_customers",
797 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
798 "DROP TABLE [sales].[customers];",
799 );
800
801 let error = build_database_downgrade_script(
802 &root,
803 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
804 "999_missing",
805 )
806 .unwrap_err();
807 assert!(
808 error
809 .to_string()
810 .contains("target `999_missing` is not a known local migration")
811 );
812
813 write_local_migration(
814 &root,
815 "200_create_orders",
816 "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);",
817 "-- manual rollback pending\n",
818 );
819
820 let error = build_database_downgrade_script(
821 &root,
822 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
823 "100_create_customers",
824 )
825 .unwrap_err();
826 assert!(
827 error
828 .to_string()
829 .contains("migration `200_create_orders` has no executable down.sql statements")
830 );
831 }
832
833 #[test]
834 fn database_downgrade_reports_missing_artifacts_and_unresolved_templates() {
835 let root = temp_project_root();
836 write_local_migration(
837 &root,
838 "100_create_customers",
839 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
840 "DROP TABLE [sales].[customers];",
841 );
842 write_local_migration(
843 &root,
844 "200_create_orders",
845 "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);",
846 "DROP TABLE [sales].[orders];",
847 );
848 fs::remove_file(root.join("migrations/200_create_orders/down.sql")).unwrap();
849
850 let error = build_database_downgrade_script(
851 &root,
852 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
853 "100_create_customers",
854 )
855 .unwrap_err();
856 assert!(
857 error
858 .to_string()
859 .contains("migration `200_create_orders` is missing local down.sql")
860 );
861
862 fs::write(
863 root.join("migrations/200_create_orders/down.sql"),
864 "-- Migration: 200_create_orders\n-- Manual rollback SQL for this editable migration.\n-- The current MVP does not execute down.sql automatically.\n",
865 )
866 .unwrap();
867
868 let error = build_database_downgrade_script(
869 &root,
870 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
871 "100_create_customers",
872 )
873 .unwrap_err();
874 assert!(
875 error
876 .to_string()
877 .contains("migration `200_create_orders` has no reversible payload in down.sql")
878 );
879
880 fs::remove_file(root.join("migrations/200_create_orders/up.sql")).unwrap();
881 fs::write(
882 root.join("migrations/200_create_orders/down.sql"),
883 "DROP TABLE [sales].[orders];",
884 )
885 .unwrap();
886
887 let error = build_database_downgrade_script(
888 &root,
889 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
890 "100_create_customers",
891 )
892 .unwrap_err();
893 assert!(
894 error
895 .to_string()
896 .contains("migration `200_create_orders` is missing local up.sql")
897 );
898 }
899
900 #[test]
901 fn writes_up_sql_from_compiled_statements() {
902 let root = temp_project_root();
903 let up_sql_path = root.join("up.sql");
904
905 write_migration_up_sql(
906 &up_sql_path,
907 &[
908 "CREATE SCHEMA [sales]".to_string(),
909 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL)".to_string(),
910 ],
911 )
912 .unwrap();
913
914 let sql = fs::read_to_string(up_sql_path).unwrap();
915
916 assert_eq!(
917 sql,
918 "CREATE SCHEMA [sales];\n\nCREATE TABLE [sales].[customers] ([id] bigint NOT NULL);\n"
919 );
920 }
921
922 #[test]
923 fn writes_noop_up_sql_when_no_statements_exist() {
924 let root = temp_project_root();
925 let up_sql_path = root.join("up.sql");
926
927 write_migration_up_sql(&up_sql_path, &[]).unwrap();
928
929 assert_eq!(
930 fs::read_to_string(up_sql_path).unwrap(),
931 "-- No schema changes detected.\n"
932 );
933 }
934}