1use crate::ModelSnapshot;
2use sql_orm_core::{OrmError, quote_sql_string_literal};
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::migration("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::migration("failed to create migration directory"))?;
62 fs::write(directory.join("up.sql"), initial_up_sql_template(&id))
63 .map_err(|_| OrmError::migration("failed to write migration up.sql"))?;
64 fs::write(directory.join("down.sql"), initial_down_sql_template(&id))
65 .map_err(|_| OrmError::migration("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::migration("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::migration("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::migration("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::migration("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::migration("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::migration("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({});", quote_sql_string_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::migration(
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::migration(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::migration(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::migration(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::migration(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::migration(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 let id_literal = quote_sql_string_literal(id);
275 let name_literal = quote_sql_string_literal(name);
276 let checksum_literal = quote_sql_string_literal(checksum);
277 let version_literal = quote_sql_string_literal(ORM_VERSION);
278 let checksum_mismatch_message =
279 quote_sql_string_literal(&format!("sql-orm migration checksum mismatch for {id}"));
280
281 format!(
282 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = {id_literal} AND [checksum] <> {checksum_literal})\nBEGIN\n THROW 50001, {checksum_mismatch_message}, 1;\nEND\n\nIF NOT EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = {id_literal})\nBEGIN\n BEGIN TRY\n BEGIN TRANSACTION;\n{body} INSERT INTO [dbo].[__sql_orm_migrations] ([id], [name], [checksum], [orm_version]) VALUES ({id_literal}, {name_literal}, {checksum_literal}, {version_literal});\n COMMIT TRANSACTION;\n END TRY\n BEGIN CATCH\n IF XACT_STATE() <> 0\n ROLLBACK TRANSACTION;\n THROW;\n END CATCH\nEND",
283 body = body,
284 )
285}
286
287#[derive(Debug, Clone, PartialEq, Eq)]
288struct DowngradeMigrationBlock {
289 id: String,
290 checksum: String,
291 down_statements: Vec<String>,
292}
293
294fn render_downgrade_history_guard(migrations: &[MigrationEntry], target: &str) -> String {
295 let local_history_guard = if migrations.is_empty() {
296 "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()
297 } else {
298 let known_ids = migrations
299 .iter()
300 .map(|migration| quote_sql_string_literal(&migration.id))
301 .collect::<Vec<_>>()
302 .join(", ");
303 format!(
304 "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",
305 known_ids = known_ids,
306 )
307 };
308 let target_literal = quote_sql_string_literal(target);
309 let target_guard = if target == "0" {
310 String::new()
311 } else {
312 let target_error = quote_sql_string_literal(&format!(
313 "sql-orm downgrade target {target} is not applied in migration history"
314 ));
315 format!(
316 "\n\nIF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] > {target})\n AND NOT EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = {target})\nBEGIN\n THROW 50003, {target_error}, 1;\nEND",
317 target = target_literal,
318 )
319 };
320
321 format!(
322 "{local_history_guard}{target_guard}",
323 local_history_guard = local_history_guard,
324 target_guard = target_guard,
325 )
326}
327
328fn render_idempotent_downgrade_block(migration: &DowngradeMigrationBlock) -> String {
329 let id = quote_sql_string_literal(&migration.id);
330 let checksum = quote_sql_string_literal(&migration.checksum);
331 let checksum_mismatch_message = quote_sql_string_literal(&format!(
332 "sql-orm migration checksum mismatch for {}",
333 migration.id
334 ));
335 let body = migration
336 .down_statements
337 .iter()
338 .map(|statement| format!(" EXEC({});", quote_sql_string_literal(statement)))
339 .collect::<Vec<_>>()
340 .join("\n");
341
342 format!(
343 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = {id} AND [checksum] <> {checksum})\nBEGIN\n THROW 50001, {checksum_mismatch_message}, 1;\nEND\n\nIF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] = {id})\nBEGIN\n BEGIN TRY\n BEGIN TRANSACTION;\n{body}\n DELETE FROM [dbo].[__sql_orm_migrations] WHERE [id] = {id};\n COMMIT TRANSACTION;\n END TRY\n BEGIN CATCH\n IF XACT_STATE() <> 0\n ROLLBACK TRANSACTION;\n THROW;\n END CATCH\nEND",
344 id = id,
345 checksum = checksum,
346 checksum_mismatch_message = checksum_mismatch_message,
347 body = body,
348 )
349}
350
351fn parse_migration_entry(path: PathBuf) -> Option<MigrationEntry> {
352 let file_name = path.file_name()?.to_str()?;
353 let (timestamp, slug) = file_name.split_once('_')?;
354 if timestamp.is_empty() || slug.is_empty() {
355 return None;
356 }
357
358 Some(MigrationEntry {
359 id: file_name.to_string(),
360 name: slug.replace('_', " "),
361 up_sql_path: path.join("up.sql"),
362 down_sql_path: path.join("down.sql"),
363 snapshot_path: path.join("model_snapshot.json"),
364 directory: path,
365 })
366}
367
368fn migration_timestamp() -> Result<String, OrmError> {
369 let duration = SystemTime::now()
370 .duration_since(UNIX_EPOCH)
371 .map_err(|_| OrmError::migration("system clock is before UNIX_EPOCH"))?;
372 Ok(duration.as_nanos().to_string())
373}
374
375fn slugify(name: &str) -> String {
376 let mut slug = String::new();
377 let mut previous_was_separator = false;
378
379 for ch in name.chars() {
380 if ch.is_ascii_alphanumeric() {
381 slug.push(ch.to_ascii_lowercase());
382 previous_was_separator = false;
383 } else if !previous_was_separator {
384 slug.push('_');
385 previous_was_separator = true;
386 }
387 }
388
389 slug.trim_matches('_').to_string()
390}
391
392fn checksum_hex(bytes: &[u8]) -> String {
393 let mut hash = 0xcbf29ce484222325u64;
394 for byte in bytes {
395 hash ^= u64::from(*byte);
396 hash = hash.wrapping_mul(0x100000001b3);
397 }
398
399 format!("{hash:016x}")
400}
401
402fn is_unresolved_down_sql_template(sql: &str) -> bool {
403 let mut saw_executable_statement = false;
404 let mut saw_unresolved_marker = false;
405
406 for line in sql.lines() {
407 let trimmed = line.trim();
408 if trimmed.is_empty() {
409 continue;
410 }
411
412 if trimmed.starts_with("--") {
413 let lower = trimmed.to_ascii_lowercase();
414 if lower.contains("manual rollback sql")
415 || lower.contains("does not execute down.sql automatically")
416 || lower.contains("no reversible schema changes detected")
417 {
418 saw_unresolved_marker = true;
419 }
420 continue;
421 }
422
423 saw_executable_statement = true;
424 }
425
426 saw_unresolved_marker && !saw_executable_statement
427}
428
429fn split_sql_statements(sql: &str) -> Vec<String> {
430 let mut statements = Vec::new();
431 let mut current = String::new();
432 let mut line = String::new();
433 let mut chars = sql.chars().peekable();
434 let mut state = SqlScriptState::Default;
435
436 while let Some(ch) = chars.next() {
437 match state {
438 SqlScriptState::Default => {
439 if ch != '\r' && ch != '\n' {
440 line.push(ch);
441 }
442
443 match ch {
444 '\'' => {
445 current.push(ch);
446 state = SqlScriptState::StringLiteral;
447 }
448 '[' => {
449 current.push(ch);
450 state = SqlScriptState::BracketIdentifier;
451 }
452 '"' => {
453 current.push(ch);
454 state = SqlScriptState::DoubleQuotedIdentifier;
455 }
456 '-' if chars.peek() == Some(&'-') => {
457 current.push(ch);
458 current.push(chars.next().expect("peeked dash"));
459 state = SqlScriptState::LineComment;
460 }
461 '/' if chars.peek() == Some(&'*') => {
462 current.push(ch);
463 current.push(chars.next().expect("peeked star"));
464 state = SqlScriptState::BlockComment;
465 }
466 ';' => {
467 current.push(ch);
468 push_sql_statement(&mut statements, &mut current);
469 }
470 '\n' => {
471 if is_go_batch_separator(&line) {
472 remove_current_line(&mut current);
473 push_sql_statement(&mut statements, &mut current);
474 } else {
475 current.push(ch);
476 }
477 line.clear();
478 }
479 '\r' => {
480 current.push(ch);
481 }
482 _ => {
483 current.push(ch);
484 }
485 }
486 }
487 SqlScriptState::StringLiteral => {
488 current.push(ch);
489 if ch == '\'' {
490 if chars.peek() == Some(&'\'') {
491 current.push(chars.next().expect("peeked quote"));
492 } else {
493 state = SqlScriptState::Default;
494 }
495 }
496 if ch == '\n' {
497 line.clear();
498 }
499 }
500 SqlScriptState::BracketIdentifier => {
501 current.push(ch);
502 if ch == ']' {
503 if chars.peek() == Some(&']') {
504 current.push(chars.next().expect("peeked bracket"));
505 } else {
506 state = SqlScriptState::Default;
507 }
508 }
509 if ch == '\n' {
510 line.clear();
511 }
512 }
513 SqlScriptState::DoubleQuotedIdentifier => {
514 current.push(ch);
515 if ch == '"' {
516 if chars.peek() == Some(&'"') {
517 current.push(chars.next().expect("peeked double quote"));
518 } else {
519 state = SqlScriptState::Default;
520 }
521 }
522 if ch == '\n' {
523 line.clear();
524 }
525 }
526 SqlScriptState::LineComment => {
527 current.push(ch);
528 if ch == '\n' {
529 state = SqlScriptState::Default;
530 line.clear();
531 }
532 }
533 SqlScriptState::BlockComment => {
534 current.push(ch);
535 if ch == '*' && chars.peek() == Some(&'/') {
536 current.push(chars.next().expect("peeked slash"));
537 state = SqlScriptState::Default;
538 }
539 if ch == '\n' {
540 line.clear();
541 }
542 }
543 }
544 }
545
546 if is_go_batch_separator(&line) {
547 remove_current_line(&mut current);
548 }
549 push_sql_statement(&mut statements, &mut current);
550
551 statements
552}
553
554#[derive(Debug, Clone, Copy, PartialEq, Eq)]
555enum SqlScriptState {
556 Default,
557 StringLiteral,
558 BracketIdentifier,
559 DoubleQuotedIdentifier,
560 LineComment,
561 BlockComment,
562}
563
564fn push_sql_statement(statements: &mut Vec<String>, current: &mut String) {
565 let statement = current.trim();
566 if !statement.is_empty() && has_executable_sql(statement) {
567 statements.push(statement.to_string());
568 }
569 current.clear();
570}
571
572fn remove_current_line(current: &mut String) {
573 match current.rfind('\n') {
574 Some(index) => current.truncate(index + 1),
575 None => current.clear(),
576 }
577}
578
579fn is_go_batch_separator(line: &str) -> bool {
580 line.trim().eq_ignore_ascii_case("GO")
581}
582
583fn has_executable_sql(statement: &str) -> bool {
584 let mut chars = statement.chars().peekable();
585 while let Some(ch) = chars.next() {
586 match ch {
587 '-' if chars.peek() == Some(&'-') => {
588 chars.next();
589 for comment_ch in chars.by_ref() {
590 if comment_ch == '\n' {
591 break;
592 }
593 }
594 }
595 '/' if chars.peek() == Some(&'*') => {
596 chars.next();
597 let mut previous = '\0';
598 for comment_ch in chars.by_ref() {
599 if previous == '*' && comment_ch == '/' {
600 break;
601 }
602 previous = comment_ch;
603 }
604 }
605 _ if ch.is_whitespace() || ch == ';' => {}
606 _ => return true,
607 }
608 }
609
610 false
611}
612
613#[cfg(test)]
614mod tests {
615 use super::{
616 build_database_downgrade_script, build_database_update_script, checksum_hex,
617 create_migration_scaffold, create_migration_scaffold_with_snapshot, latest_migration,
618 list_migrations, read_latest_model_snapshot, read_model_snapshot, split_sql_statements,
619 write_migration_down_sql, write_migration_up_sql, write_model_snapshot,
620 };
621 use crate::{ModelSnapshot, SchemaSnapshot};
622 use sql_orm_core::OrmErrorKind;
623 use std::fs;
624 use std::path::{Path, PathBuf};
625 use std::time::{SystemTime, UNIX_EPOCH};
626
627 fn temp_project_root() -> PathBuf {
628 let unique = SystemTime::now()
629 .duration_since(UNIX_EPOCH)
630 .unwrap()
631 .as_nanos();
632 let path = std::env::temp_dir().join(format!("sql_orm_migrate_{unique}"));
633 fs::create_dir_all(&path).unwrap();
634 path
635 }
636
637 fn write_local_migration(root: &Path, id: &str, up_sql: &str, down_sql: &str) {
638 let migration_dir = root.join("migrations").join(id);
639 fs::create_dir_all(&migration_dir).unwrap();
640 fs::write(migration_dir.join("up.sql"), up_sql).unwrap();
641 fs::write(migration_dir.join("down.sql"), down_sql).unwrap();
642 fs::write(
643 migration_dir.join("model_snapshot.json"),
644 "{ \"schemas\": [] }",
645 )
646 .unwrap();
647 }
648
649 #[test]
650 fn creates_scaffolded_migration_files() {
651 let root = temp_project_root();
652
653 let scaffold = create_migration_scaffold(&root, "Create Customers").unwrap();
654
655 assert!(scaffold.id.contains("create_customers"));
656 assert!(scaffold.up_sql_path().exists());
657 assert!(scaffold.down_sql_path().exists());
658 assert!(scaffold.snapshot_path().exists());
659 assert!(!scaffold.directory.join("migration.rs").exists());
660
661 assert_eq!(
662 fs::read_to_string(scaffold.up_sql_path()).unwrap(),
663 format!(
664 "-- Migration: {}\n-- SQL Server DDL for this migration.\n",
665 scaffold.id
666 )
667 );
668 assert_eq!(
669 fs::read_to_string(scaffold.down_sql_path()).unwrap(),
670 format!(
671 "-- Migration: {}\n-- Manual rollback SQL for this editable migration.\n-- The current MVP does not execute down.sql automatically.\n",
672 scaffold.id
673 )
674 );
675
676 let snapshot = read_model_snapshot(&scaffold.snapshot_path()).unwrap();
677 assert_eq!(snapshot, ModelSnapshot::default());
678 }
679
680 #[test]
681 fn writes_and_reads_model_snapshot_artifact() {
682 let root = temp_project_root();
683 let snapshot_path = root.join("model_snapshot.json");
684 let snapshot = ModelSnapshot::new(vec![SchemaSnapshot::new("sales", Vec::new())]);
685
686 write_model_snapshot(&snapshot_path, &snapshot).unwrap();
687
688 assert_eq!(read_model_snapshot(&snapshot_path).unwrap(), snapshot);
689 }
690
691 #[test]
692 fn writes_generated_down_sql_artifact() {
693 let root = temp_project_root();
694 let down_sql_path = root.join("down.sql");
695
696 write_migration_down_sql(
697 &down_sql_path,
698 &[
699 "DROP TABLE [sales].[customers]".to_string(),
700 "DROP SCHEMA [sales]".to_string(),
701 ],
702 )
703 .unwrap();
704
705 assert_eq!(
706 fs::read_to_string(down_sql_path).unwrap(),
707 "DROP TABLE [sales].[customers];\n\nDROP SCHEMA [sales];\n"
708 );
709 }
710
711 #[test]
712 fn creates_scaffold_with_provided_model_snapshot() {
713 let root = temp_project_root();
714 let snapshot = ModelSnapshot::new(vec![SchemaSnapshot::new("sales", Vec::new())]);
715
716 let scaffold =
717 create_migration_scaffold_with_snapshot(&root, "Create Sales", &snapshot).unwrap();
718
719 assert_eq!(
720 read_model_snapshot(&scaffold.snapshot_path()).unwrap(),
721 snapshot
722 );
723 }
724
725 #[test]
726 fn lists_migrations_in_sorted_order() {
727 let root = temp_project_root();
728 let migrations_dir = root.join("migrations");
729 fs::create_dir_all(migrations_dir.join("200_create_orders")).unwrap();
730 fs::create_dir_all(migrations_dir.join("100_create_customers")).unwrap();
731
732 let migrations = list_migrations(&root).unwrap();
733
734 assert_eq!(migrations.len(), 2);
735 assert_eq!(migrations[0].id, "100_create_customers");
736 assert_eq!(migrations[1].id, "200_create_orders");
737 }
738
739 #[test]
740 fn returns_latest_migration_in_lexical_order() {
741 let root = temp_project_root();
742 let migrations_dir = root.join("migrations");
743 fs::create_dir_all(migrations_dir.join("100_create_customers")).unwrap();
744 fs::create_dir_all(migrations_dir.join("200_create_orders")).unwrap();
745
746 let latest = latest_migration(&root).unwrap().unwrap();
747
748 assert_eq!(latest.id, "200_create_orders");
749 }
750
751 #[test]
752 fn reads_latest_model_snapshot_from_last_local_migration() {
753 let root = temp_project_root();
754 let older_dir = root.join("migrations/100_create_customers");
755 let newer_dir = root.join("migrations/200_create_orders");
756 fs::create_dir_all(&older_dir).unwrap();
757 fs::create_dir_all(&newer_dir).unwrap();
758 fs::write(older_dir.join("up.sql"), "-- noop").unwrap();
759 fs::write(older_dir.join("down.sql"), "-- noop").unwrap();
760 fs::write(
761 older_dir.join("model_snapshot.json"),
762 "{\n \"schemas\": []\n}\n",
763 )
764 .unwrap();
765 fs::write(newer_dir.join("up.sql"), "-- noop").unwrap();
766 fs::write(newer_dir.join("down.sql"), "-- noop").unwrap();
767 fs::write(
768 newer_dir.join("model_snapshot.json"),
769 "{\n \"schemas\": [\n {\n \"name\": \"sales\",\n \"tables\": []\n }\n ]\n}\n",
770 )
771 .unwrap();
772
773 let (migration, snapshot) = read_latest_model_snapshot(&root).unwrap().unwrap();
774
775 assert_eq!(migration.id, "200_create_orders");
776 assert!(snapshot.schema("sales").is_some());
777 }
778
779 #[test]
780 fn builds_database_update_script_with_history_inserts() {
781 let root = temp_project_root();
782 let scaffold = create_migration_scaffold(&root, "Create Customers").unwrap();
783 fs::write(
784 scaffold.directory.join("up.sql"),
785 "CREATE SCHEMA [sales];\nCREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
786 )
787 .unwrap();
788
789 let script =
790 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
791 .unwrap();
792
793 assert!(script.contains("CREATE TABLE [dbo].[__sql_orm_migrations]"));
794 assert!(script.contains("SET ANSI_NULLS ON;"));
795 assert!(script.contains("SET QUOTED_IDENTIFIER ON;"));
796 assert!(script.contains("SET NUMERIC_ROUNDABORT OFF;"));
797 assert!(script.contains("IF NOT EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations]"));
798 assert!(script.contains("IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations]"));
799 assert!(script.contains("THROW 50001, N'sql-orm migration checksum mismatch"));
800 assert!(script.contains("BEGIN TRY"));
801 assert!(script.contains("BEGIN TRANSACTION;"));
802 assert!(script.contains("EXEC(N'CREATE SCHEMA [sales];');"));
803 assert!(
804 script.contains("EXEC(N'CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);');")
805 );
806 assert!(script.contains("INSERT INTO [dbo].[__sql_orm_migrations]"));
807 assert!(script.contains("COMMIT TRANSACTION;"));
808 assert!(script.contains("ROLLBACK TRANSACTION;"));
809 }
810
811 #[test]
812 fn builds_database_update_script_without_empty_exec_blocks() {
813 let root = temp_project_root();
814 let scaffold = create_migration_scaffold(&root, "Noop").unwrap();
815 fs::write(
816 scaffold.directory.join("up.sql"),
817 "-- comment only migration\n\n-- still intentionally empty\n",
818 )
819 .unwrap();
820
821 let script =
822 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
823 .unwrap();
824
825 assert!(!script.contains("EXEC(N'');"));
826 assert!(script.contains("INSERT INTO [dbo].[__sql_orm_migrations]"));
827 }
828
829 #[test]
830 fn database_update_script_escapes_single_quotes_inside_exec_blocks() {
831 let root = temp_project_root();
832 let scaffold = create_migration_scaffold(&root, "Quoted Literal").unwrap();
833 fs::write(
834 scaffold.directory.join("up.sql"),
835 "INSERT INTO [dbo].[messages] ([body]) VALUES (N'O''Brien');",
836 )
837 .unwrap();
838
839 let script =
840 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
841 .unwrap();
842
843 assert!(
844 script.contains(
845 "EXEC(N'INSERT INTO [dbo].[messages] ([body]) VALUES (N''O''''Brien'');');"
846 )
847 );
848 }
849
850 #[test]
851 fn split_sql_statements_respects_literals_comments_and_go_batches() {
852 let statements = split_sql_statements(
853 "CREATE TABLE [dbo].[semi;colon] ([body] nvarchar(200));\n\
854 INSERT INTO [dbo].[semi;colon] ([body]) VALUES (N'one;two -- not comment');\n\
855 -- GO is ignored inside a line comment\n\
856 /* semicolon ; and GO are ignored inside block comments */\n\
857 GO\n\
858 SELECT N'GO; still literal';\n",
859 );
860
861 assert_eq!(
862 statements,
863 vec![
864 "CREATE TABLE [dbo].[semi;colon] ([body] nvarchar(200));",
865 "INSERT INTO [dbo].[semi;colon] ([body]) VALUES (N'one;two -- not comment');",
866 "SELECT N'GO; still literal';",
867 ]
868 );
869 }
870
871 #[test]
872 fn split_sql_statements_discards_comment_only_batches() {
873 let statements = split_sql_statements(
874 "-- comment only\n\
875 GO\n\
876 /* block comment ; only */\n\
877 GO\n\
878 CREATE SCHEMA [sales]\n\
879 GO\n",
880 );
881
882 assert_eq!(statements, vec!["CREATE SCHEMA [sales]"]);
883 }
884
885 #[test]
886 fn database_update_script_splits_go_batches_without_splitting_literals() {
887 let root = temp_project_root();
888 let scaffold = create_migration_scaffold(&root, "Go Batch").unwrap();
889 fs::write(
890 scaffold.directory.join("up.sql"),
891 "CREATE SCHEMA [sales]\nGO\nINSERT INTO [dbo].[messages] ([body]) VALUES (N'a;b');",
892 )
893 .unwrap();
894
895 let script =
896 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
897 .unwrap();
898
899 assert!(script.contains("EXEC(N'CREATE SCHEMA [sales]');"));
900 assert!(
901 script.contains("EXEC(N'INSERT INTO [dbo].[messages] ([body]) VALUES (N''a;b'');');")
902 );
903 assert!(!script.contains("EXEC(N'GO');"));
904 }
905
906 #[test]
907 fn database_update_script_preserves_comments_and_semicolons_inside_strings() {
908 let root = temp_project_root();
909 let scaffold = create_migration_scaffold(&root, "Commented Literal").unwrap();
910 fs::write(
911 scaffold.directory.join("up.sql"),
912 "-- comment with ; and GO text\n\
913 INSERT INTO [dbo].[messages] ([body]) VALUES (N'alpha; beta GO');\n\
914 /* block comment with ; before next statement */\n\
915 UPDATE [dbo].[messages] SET [body] = N'O''Brien; still one literal';",
916 )
917 .unwrap();
918
919 let script =
920 build_database_update_script(&root, "CREATE TABLE [dbo].[__sql_orm_migrations] (...);")
921 .unwrap();
922
923 assert!(script.contains(
924 "EXEC(N'-- comment with ; and GO text\nINSERT INTO [dbo].[messages] ([body]) VALUES (N''alpha; beta GO'');');"
925 ));
926 assert!(script.contains(
927 "EXEC(N'/* block comment with ; before next statement */\nUPDATE [dbo].[messages] SET [body] = N''O''''Brien; still one literal'';');"
928 ));
929 assert_eq!(script.matches("EXEC(N'").count(), 2);
930 }
931
932 #[test]
933 fn database_downgrade_script_preserves_comments_and_semicolons_inside_strings() {
934 let root = temp_project_root();
935 write_local_migration(
936 &root,
937 "100_create_messages",
938 "CREATE TABLE [dbo].[messages] ([body] nvarchar(200) NOT NULL);",
939 "-- comment with ; and GO text\n\
940 UPDATE [dbo].[messages] SET [body] = N'alpha; beta GO';\n\
941 /* block comment with ; before drop */\n\
942 DROP TABLE [dbo].[messages];",
943 );
944
945 let script = build_database_downgrade_script(
946 &root,
947 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
948 "0",
949 )
950 .unwrap();
951
952 assert!(script.contains(
953 "EXEC(N'-- comment with ; and GO text\nUPDATE [dbo].[messages] SET [body] = N''alpha; beta GO'';');"
954 ));
955 assert!(script.contains(
956 "EXEC(N'/* block comment with ; before drop */\nDROP TABLE [dbo].[messages];');"
957 ));
958 assert_eq!(script.matches(" EXEC(N'").count(), 2);
959 }
960
961 #[test]
962 fn builds_database_downgrade_script_in_reverse_order() {
963 let root = temp_project_root();
964 write_local_migration(
965 &root,
966 "100_create_customers",
967 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
968 "DROP TABLE [sales].[customers];",
969 );
970 write_local_migration(
971 &root,
972 "200_create_orders",
973 "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);",
974 "DROP TABLE [sales].[orders];",
975 );
976 write_local_migration(
977 &root,
978 "300_create_lines",
979 "CREATE TABLE [sales].[order_lines] ([id] bigint NOT NULL);",
980 "DROP TABLE [sales].[order_lines];",
981 );
982
983 let script = build_database_downgrade_script(
984 &root,
985 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
986 "100_create_customers",
987 )
988 .unwrap();
989
990 let lines_pos = script.find("DROP TABLE [sales].[order_lines]").unwrap();
991 let orders_pos = script.find("DROP TABLE [sales].[orders]").unwrap();
992 assert!(lines_pos < orders_pos);
993 assert!(!script.contains("DROP TABLE [sales].[customers]"));
994 assert!(script.contains("CREATE TABLE [dbo].[__sql_orm_migrations]"));
995 assert!(
996 script.contains(
997 "IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations] WHERE [id] NOT IN"
998 )
999 );
1000 assert!(script.contains("sql-orm downgrade target 100_create_customers is not applied"));
1001 assert!(
1002 script.contains(
1003 "THROW 50001, N'sql-orm migration checksum mismatch for 300_create_lines'"
1004 )
1005 );
1006 assert!(script.contains("BEGIN TRANSACTION;"));
1007 assert!(script.contains(
1008 "DELETE FROM [dbo].[__sql_orm_migrations] WHERE [id] = N'300_create_lines';"
1009 ));
1010 assert!(script.contains("ROLLBACK TRANSACTION;"));
1011 }
1012
1013 #[test]
1014 fn builds_database_downgrade_script_to_empty_database_sentinel() {
1015 let root = temp_project_root();
1016 write_local_migration(
1017 &root,
1018 "100_create_customers",
1019 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
1020 "DROP TABLE [sales].[customers];",
1021 );
1022
1023 let script = build_database_downgrade_script(
1024 &root,
1025 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1026 "0",
1027 )
1028 .unwrap();
1029
1030 assert!(script.contains("DROP TABLE [sales].[customers]"));
1031 assert!(!script.contains("downgrade target 0 is not applied"));
1032 }
1033
1034 #[test]
1035 fn database_downgrade_script_with_no_local_migrations_rejects_any_history_rows() {
1036 let root = temp_project_root();
1037
1038 let script = build_database_downgrade_script(
1039 &root,
1040 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1041 "0",
1042 )
1043 .unwrap();
1044
1045 assert!(script.contains("IF EXISTS (SELECT 1 FROM [dbo].[__sql_orm_migrations])"));
1046 assert!(script.contains("history contains entries missing from local migrations"));
1047 assert!(!script.contains("NOT IN (NULL)"));
1048 }
1049
1050 #[test]
1051 fn database_downgrade_requires_explicit_target_and_renders_checksum_guards() {
1052 let root = temp_project_root();
1053 let orders_up = "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);";
1054 write_local_migration(
1055 &root,
1056 "100_create_customers",
1057 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
1058 "DROP TABLE [sales].[customers];",
1059 );
1060 write_local_migration(
1061 &root,
1062 "200_create_orders",
1063 orders_up,
1064 "DROP TABLE [sales].[orders];",
1065 );
1066
1067 let error = build_database_downgrade_script(
1068 &root,
1069 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1070 " ",
1071 )
1072 .unwrap_err();
1073 assert_eq!(error.kind(), OrmErrorKind::Migration);
1074 assert!(
1075 error
1076 .to_string()
1077 .contains("database downgrade requires an explicit target")
1078 );
1079
1080 let script = build_database_downgrade_script(
1081 &root,
1082 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1083 "100_create_customers",
1084 )
1085 .unwrap();
1086 let orders_checksum = checksum_hex(orders_up.as_bytes());
1087 let checksum_guard =
1088 format!("WHERE [id] = N'200_create_orders' AND [checksum] <> N'{orders_checksum}'");
1089
1090 assert!(script.contains("N'100_create_customers', N'200_create_orders'"));
1091 assert!(script.contains(&checksum_guard));
1092 assert!(
1093 script
1094 .find("sql-orm migration checksum mismatch for 200_create_orders")
1095 .unwrap()
1096 < script.find("DROP TABLE [sales].[orders]").unwrap()
1097 );
1098 assert_eq!(script.matches("DROP TABLE [sales].[orders]").count(), 1);
1099 assert!(!script.contains("DROP TABLE [sales].[customers]"));
1100 }
1101
1102 #[test]
1103 fn database_downgrade_rejects_unknown_target_and_empty_down_sql() {
1104 let root = temp_project_root();
1105 write_local_migration(
1106 &root,
1107 "100_create_customers",
1108 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
1109 "DROP TABLE [sales].[customers];",
1110 );
1111
1112 let error = build_database_downgrade_script(
1113 &root,
1114 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1115 "999_missing",
1116 )
1117 .unwrap_err();
1118 assert!(
1119 error
1120 .to_string()
1121 .contains("target `999_missing` is not a known local migration")
1122 );
1123
1124 write_local_migration(
1125 &root,
1126 "200_create_orders",
1127 "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);",
1128 "-- manual rollback pending\n",
1129 );
1130
1131 let error = build_database_downgrade_script(
1132 &root,
1133 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1134 "100_create_customers",
1135 )
1136 .unwrap_err();
1137 assert!(
1138 error
1139 .to_string()
1140 .contains("migration `200_create_orders` has no executable down.sql statements")
1141 );
1142 }
1143
1144 #[test]
1145 fn database_downgrade_reports_missing_artifacts_and_unresolved_templates() {
1146 let root = temp_project_root();
1147 write_local_migration(
1148 &root,
1149 "100_create_customers",
1150 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL);",
1151 "DROP TABLE [sales].[customers];",
1152 );
1153 write_local_migration(
1154 &root,
1155 "200_create_orders",
1156 "CREATE TABLE [sales].[orders] ([id] bigint NOT NULL);",
1157 "DROP TABLE [sales].[orders];",
1158 );
1159 fs::remove_file(root.join("migrations/200_create_orders/down.sql")).unwrap();
1160
1161 let error = build_database_downgrade_script(
1162 &root,
1163 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1164 "100_create_customers",
1165 )
1166 .unwrap_err();
1167 assert!(
1168 error
1169 .to_string()
1170 .contains("migration `200_create_orders` is missing local down.sql")
1171 );
1172
1173 fs::write(
1174 root.join("migrations/200_create_orders/down.sql"),
1175 "-- Migration: 200_create_orders\n-- Manual rollback SQL for this editable migration.\n-- The current MVP does not execute down.sql automatically.\n",
1176 )
1177 .unwrap();
1178
1179 let error = build_database_downgrade_script(
1180 &root,
1181 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1182 "100_create_customers",
1183 )
1184 .unwrap_err();
1185 assert!(
1186 error
1187 .to_string()
1188 .contains("migration `200_create_orders` has no reversible payload in down.sql")
1189 );
1190
1191 fs::remove_file(root.join("migrations/200_create_orders/up.sql")).unwrap();
1192 fs::write(
1193 root.join("migrations/200_create_orders/down.sql"),
1194 "DROP TABLE [sales].[orders];",
1195 )
1196 .unwrap();
1197
1198 let error = build_database_downgrade_script(
1199 &root,
1200 "CREATE TABLE [dbo].[__sql_orm_migrations] (...);",
1201 "100_create_customers",
1202 )
1203 .unwrap_err();
1204 assert!(
1205 error
1206 .to_string()
1207 .contains("migration `200_create_orders` is missing local up.sql")
1208 );
1209 }
1210
1211 #[test]
1212 fn writes_up_sql_from_compiled_statements() {
1213 let root = temp_project_root();
1214 let up_sql_path = root.join("up.sql");
1215
1216 write_migration_up_sql(
1217 &up_sql_path,
1218 &[
1219 "CREATE SCHEMA [sales]".to_string(),
1220 "CREATE TABLE [sales].[customers] ([id] bigint NOT NULL)".to_string(),
1221 ],
1222 )
1223 .unwrap();
1224
1225 let sql = fs::read_to_string(up_sql_path).unwrap();
1226
1227 assert_eq!(
1228 sql,
1229 "CREATE SCHEMA [sales];\n\nCREATE TABLE [sales].[customers] ([id] bigint NOT NULL);\n"
1230 );
1231 }
1232
1233 #[test]
1234 fn writes_noop_up_sql_when_no_statements_exist() {
1235 let root = temp_project_root();
1236 let up_sql_path = root.join("up.sql");
1237
1238 write_migration_up_sql(&up_sql_path, &[]).unwrap();
1239
1240 assert_eq!(
1241 fs::read_to_string(up_sql_path).unwrap(),
1242 "-- No schema changes detected.\n"
1243 );
1244 }
1245}