prax_migrate/
procedure.rs

1//! Procedure migrations - Version control for stored procedures, functions, and triggers.
2//!
3//! This module provides functionality to manage database procedures through migrations:
4//! - Track procedure definitions in the schema
5//! - Detect changes between schema and database
6//! - Generate CREATE/ALTER/DROP statements
7//! - Support for PostgreSQL, MySQL, SQLite (UDFs), and MSSQL
8//!
9//! # Example
10//!
11//! ```rust,ignore
12//! use prax_migrate::procedure::{ProcedureDiff, ProcedureMigration};
13//!
14//! // Define a procedure in schema
15//! let proc = ProcedureDefinition::new("calculate_tax")
16//!     .language(ProcedureLanguage::PlPgSql)
17//!     .parameters(vec![
18//!         Parameter::new("amount", "DECIMAL"),
19//!         Parameter::new("rate", "DECIMAL"),
20//!     ])
21//!     .returns("DECIMAL")
22//!     .body("RETURN amount * rate;");
23//!
24//! // Generate migration
25//! let migration = ProcedureMigration::create(&proc);
26//! println!("{}", migration.up_sql());
27//! ```
28
29use std::collections::HashMap;
30
31use serde::{Deserialize, Serialize};
32
33// ============================================================================
34// Procedure Definition
35// ============================================================================
36
37/// A stored procedure or function definition.
38#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
39pub struct ProcedureDefinition {
40    /// Procedure name.
41    pub name: String,
42    /// Schema/namespace.
43    pub schema: Option<String>,
44    /// Whether this is a function (returns value) or procedure.
45    pub is_function: bool,
46    /// Parameters.
47    pub parameters: Vec<ProcedureParameter>,
48    /// Return type (for functions).
49    pub return_type: Option<String>,
50    /// Returns a set/table (SETOF, TABLE).
51    pub returns_set: bool,
52    /// Table columns for table-returning functions.
53    pub return_columns: Vec<ReturnColumn>,
54    /// Procedure language.
55    pub language: ProcedureLanguage,
56    /// Procedure body.
57    pub body: String,
58    /// Volatility (VOLATILE, STABLE, IMMUTABLE).
59    pub volatility: Volatility,
60    /// Security definer (runs as owner vs caller).
61    pub security_definer: bool,
62    /// Cost estimate.
63    pub cost: Option<i32>,
64    /// Rows estimate (for set-returning functions).
65    pub rows: Option<i32>,
66    /// Parallel safety.
67    pub parallel: ParallelSafety,
68    /// Whether to replace if exists.
69    pub or_replace: bool,
70    /// Comment/description.
71    pub comment: Option<String>,
72    /// Checksum of the body for change detection.
73    pub checksum: Option<String>,
74    /// Version number for manual versioning.
75    pub version: Option<i32>,
76}
77
78impl Default for ProcedureDefinition {
79    fn default() -> Self {
80        Self {
81            name: String::new(),
82            schema: None,
83            is_function: true,
84            parameters: Vec::new(),
85            return_type: None,
86            returns_set: false,
87            return_columns: Vec::new(),
88            language: ProcedureLanguage::Sql,
89            body: String::new(),
90            volatility: Volatility::Volatile,
91            security_definer: false,
92            cost: None,
93            rows: None,
94            parallel: ParallelSafety::Unsafe,
95            or_replace: true,
96            comment: None,
97            checksum: None,
98            version: None,
99        }
100    }
101}
102
103impl ProcedureDefinition {
104    /// Create a new procedure definition.
105    pub fn new(name: impl Into<String>) -> Self {
106        Self {
107            name: name.into(),
108            ..Default::default()
109        }
110    }
111
112    /// Create a new function definition.
113    pub fn function(name: impl Into<String>) -> Self {
114        Self {
115            name: name.into(),
116            is_function: true,
117            ..Default::default()
118        }
119    }
120
121    /// Create a new stored procedure definition.
122    pub fn procedure(name: impl Into<String>) -> Self {
123        Self {
124            name: name.into(),
125            is_function: false,
126            ..Default::default()
127        }
128    }
129
130    /// Set the schema.
131    pub fn schema(mut self, schema: impl Into<String>) -> Self {
132        self.schema = Some(schema.into());
133        self
134    }
135
136    /// Add a parameter.
137    pub fn param(mut self, name: impl Into<String>, data_type: impl Into<String>) -> Self {
138        self.parameters.push(ProcedureParameter {
139            name: name.into(),
140            data_type: data_type.into(),
141            mode: ParameterMode::In,
142            default: None,
143        });
144        self
145    }
146
147    /// Add an OUT parameter.
148    pub fn out_param(mut self, name: impl Into<String>, data_type: impl Into<String>) -> Self {
149        self.parameters.push(ProcedureParameter {
150            name: name.into(),
151            data_type: data_type.into(),
152            mode: ParameterMode::Out,
153            default: None,
154        });
155        self
156    }
157
158    /// Add an INOUT parameter.
159    pub fn inout_param(mut self, name: impl Into<String>, data_type: impl Into<String>) -> Self {
160        self.parameters.push(ProcedureParameter {
161            name: name.into(),
162            data_type: data_type.into(),
163            mode: ParameterMode::InOut,
164            default: None,
165        });
166        self
167    }
168
169    /// Set return type.
170    pub fn returns(mut self, return_type: impl Into<String>) -> Self {
171        self.return_type = Some(return_type.into());
172        self
173    }
174
175    /// Set returns SETOF type.
176    pub fn returns_setof(mut self, return_type: impl Into<String>) -> Self {
177        self.return_type = Some(return_type.into());
178        self.returns_set = true;
179        self
180    }
181
182    /// Set returns TABLE.
183    pub fn returns_table(mut self, columns: Vec<ReturnColumn>) -> Self {
184        self.returns_set = true;
185        self.return_columns = columns;
186        self
187    }
188
189    /// Set the language.
190    pub fn language(mut self, language: ProcedureLanguage) -> Self {
191        self.language = language;
192        self
193    }
194
195    /// Set the body.
196    pub fn body(mut self, body: impl Into<String>) -> Self {
197        self.body = body.into();
198        self.update_checksum();
199        self
200    }
201
202    /// Set volatility.
203    pub fn volatility(mut self, volatility: Volatility) -> Self {
204        self.volatility = volatility;
205        self
206    }
207
208    /// Mark as IMMUTABLE.
209    pub fn immutable(mut self) -> Self {
210        self.volatility = Volatility::Immutable;
211        self
212    }
213
214    /// Mark as STABLE.
215    pub fn stable(mut self) -> Self {
216        self.volatility = Volatility::Stable;
217        self
218    }
219
220    /// Mark as security definer.
221    pub fn security_definer(mut self) -> Self {
222        self.security_definer = true;
223        self
224    }
225
226    /// Set cost.
227    pub fn cost(mut self, cost: i32) -> Self {
228        self.cost = Some(cost);
229        self
230    }
231
232    /// Set parallel safety.
233    pub fn parallel(mut self, parallel: ParallelSafety) -> Self {
234        self.parallel = parallel;
235        self
236    }
237
238    /// Set comment.
239    pub fn comment(mut self, comment: impl Into<String>) -> Self {
240        self.comment = Some(comment.into());
241        self
242    }
243
244    /// Update the checksum based on the body.
245    fn update_checksum(&mut self) {
246        use std::collections::hash_map::DefaultHasher;
247        use std::hash::{Hash, Hasher};
248
249        let mut hasher = DefaultHasher::new();
250        self.body.hash(&mut hasher);
251        self.checksum = Some(format!("{:016x}", hasher.finish()));
252    }
253
254    /// Get the fully qualified name.
255    pub fn qualified_name(&self) -> String {
256        match &self.schema {
257            Some(schema) => format!("{}.{}", schema, self.name),
258            None => self.name.clone(),
259        }
260    }
261
262    /// Check if the procedure has changed compared to another.
263    pub fn has_changed(&self, other: &ProcedureDefinition) -> bool {
264        // Compare checksums if available
265        if let (Some(a), Some(b)) = (&self.checksum, &other.checksum) {
266            if a != b {
267                return true;
268            }
269        }
270
271        // Compare key properties
272        self.body != other.body
273            || self.parameters != other.parameters
274            || self.return_type != other.return_type
275            || self.returns_set != other.returns_set
276            || self.language != other.language
277            || self.volatility != other.volatility
278            || self.security_definer != other.security_definer
279    }
280}
281
282/// Procedure parameter.
283#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
284pub struct ProcedureParameter {
285    /// Parameter name.
286    pub name: String,
287    /// Data type.
288    pub data_type: String,
289    /// Parameter mode.
290    pub mode: ParameterMode,
291    /// Default value.
292    pub default: Option<String>,
293}
294
295/// Parameter mode.
296#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
297pub enum ParameterMode {
298    #[default]
299    In,
300    Out,
301    InOut,
302    Variadic,
303}
304
305/// Return column for table-returning functions.
306#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
307pub struct ReturnColumn {
308    /// Column name.
309    pub name: String,
310    /// Data type.
311    pub data_type: String,
312}
313
314impl ReturnColumn {
315    /// Create a new return column.
316    pub fn new(name: impl Into<String>, data_type: impl Into<String>) -> Self {
317        Self {
318            name: name.into(),
319            data_type: data_type.into(),
320        }
321    }
322}
323
324/// Procedure language.
325#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
326pub enum ProcedureLanguage {
327    #[default]
328    Sql,
329    PlPgSql,
330    PlPython,
331    PlPerl,
332    PlTcl,
333    PlV8,
334    C,
335}
336
337impl ProcedureLanguage {
338    /// Get SQL language name.
339    pub fn to_sql(&self) -> &'static str {
340        match self {
341            Self::Sql => "SQL",
342            Self::PlPgSql => "plpgsql",
343            Self::PlPython => "plpython3u",
344            Self::PlPerl => "plperl",
345            Self::PlTcl => "pltcl",
346            Self::PlV8 => "plv8",
347            Self::C => "C",
348        }
349    }
350}
351
352/// Function volatility.
353#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
354pub enum Volatility {
355    #[default]
356    Volatile,
357    Stable,
358    Immutable,
359}
360
361impl Volatility {
362    /// Get SQL volatility string.
363    pub fn to_sql(&self) -> &'static str {
364        match self {
365            Self::Volatile => "VOLATILE",
366            Self::Stable => "STABLE",
367            Self::Immutable => "IMMUTABLE",
368        }
369    }
370}
371
372/// Parallel safety.
373#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
374pub enum ParallelSafety {
375    #[default]
376    Unsafe,
377    Restricted,
378    Safe,
379}
380
381impl ParallelSafety {
382    /// Get SQL parallel string.
383    pub fn to_sql(&self) -> &'static str {
384        match self {
385            Self::Unsafe => "PARALLEL UNSAFE",
386            Self::Restricted => "PARALLEL RESTRICTED",
387            Self::Safe => "PARALLEL SAFE",
388        }
389    }
390}
391
392// ============================================================================
393// Trigger Definition
394// ============================================================================
395
396/// A database trigger definition.
397#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
398pub struct TriggerDefinition {
399    /// Trigger name.
400    pub name: String,
401    /// Schema/namespace.
402    pub schema: Option<String>,
403    /// Table the trigger is on.
404    pub table: String,
405    /// Trigger timing.
406    pub timing: TriggerTiming,
407    /// Events that fire the trigger.
408    pub events: Vec<TriggerEvent>,
409    /// Row or statement level.
410    pub level: TriggerLevel,
411    /// WHEN condition.
412    pub condition: Option<String>,
413    /// Function to execute.
414    pub function: String,
415    /// Function arguments.
416    pub function_args: Vec<String>,
417    /// Whether to replace if exists.
418    pub or_replace: bool,
419    /// Comment/description.
420    pub comment: Option<String>,
421    /// Checksum.
422    pub checksum: Option<String>,
423}
424
425impl Default for TriggerDefinition {
426    fn default() -> Self {
427        Self {
428            name: String::new(),
429            schema: None,
430            table: String::new(),
431            timing: TriggerTiming::Before,
432            events: vec![TriggerEvent::Insert],
433            level: TriggerLevel::Row,
434            condition: None,
435            function: String::new(),
436            function_args: Vec::new(),
437            or_replace: true,
438            comment: None,
439            checksum: None,
440        }
441    }
442}
443
444impl TriggerDefinition {
445    /// Create a new trigger definition.
446    pub fn new(name: impl Into<String>, table: impl Into<String>) -> Self {
447        Self {
448            name: name.into(),
449            table: table.into(),
450            ..Default::default()
451        }
452    }
453
454    /// Set timing to BEFORE.
455    pub fn before(mut self) -> Self {
456        self.timing = TriggerTiming::Before;
457        self
458    }
459
460    /// Set timing to AFTER.
461    pub fn after(mut self) -> Self {
462        self.timing = TriggerTiming::After;
463        self
464    }
465
466    /// Set timing to INSTEAD OF.
467    pub fn instead_of(mut self) -> Self {
468        self.timing = TriggerTiming::InsteadOf;
469        self
470    }
471
472    /// Set events.
473    pub fn on(mut self, events: Vec<TriggerEvent>) -> Self {
474        self.events = events;
475        self
476    }
477
478    /// Set as row-level trigger.
479    pub fn for_each_row(mut self) -> Self {
480        self.level = TriggerLevel::Row;
481        self
482    }
483
484    /// Set as statement-level trigger.
485    pub fn for_each_statement(mut self) -> Self {
486        self.level = TriggerLevel::Statement;
487        self
488    }
489
490    /// Set WHEN condition.
491    pub fn when(mut self, condition: impl Into<String>) -> Self {
492        self.condition = Some(condition.into());
493        self
494    }
495
496    /// Set the function to execute.
497    pub fn execute(mut self, function: impl Into<String>) -> Self {
498        self.function = function.into();
499        self
500    }
501
502    /// Get the fully qualified name.
503    pub fn qualified_name(&self) -> String {
504        match &self.schema {
505            Some(schema) => format!("{}.{}", schema, self.name),
506            None => self.name.clone(),
507        }
508    }
509}
510
511/// Trigger timing.
512#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
513pub enum TriggerTiming {
514    #[default]
515    Before,
516    After,
517    InsteadOf,
518}
519
520impl TriggerTiming {
521    /// Get SQL timing string.
522    pub fn to_sql(&self) -> &'static str {
523        match self {
524            Self::Before => "BEFORE",
525            Self::After => "AFTER",
526            Self::InsteadOf => "INSTEAD OF",
527        }
528    }
529}
530
531/// Trigger event.
532#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
533pub enum TriggerEvent {
534    Insert,
535    Update,
536    Delete,
537    Truncate,
538}
539
540impl TriggerEvent {
541    /// Get SQL event string.
542    pub fn to_sql(&self) -> &'static str {
543        match self {
544            Self::Insert => "INSERT",
545            Self::Update => "UPDATE",
546            Self::Delete => "DELETE",
547            Self::Truncate => "TRUNCATE",
548        }
549    }
550}
551
552/// Trigger level.
553#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
554pub enum TriggerLevel {
555    #[default]
556    Row,
557    Statement,
558}
559
560impl TriggerLevel {
561    /// Get SQL level string.
562    pub fn to_sql(&self) -> &'static str {
563        match self {
564            Self::Row => "FOR EACH ROW",
565            Self::Statement => "FOR EACH STATEMENT",
566        }
567    }
568}
569
570// ============================================================================
571// Procedure Diff
572// ============================================================================
573
574/// Differences in procedures between two states.
575#[derive(Debug, Clone, Default)]
576pub struct ProcedureDiff {
577    /// Procedures to create.
578    pub create: Vec<ProcedureDefinition>,
579    /// Procedures to drop.
580    pub drop: Vec<String>,
581    /// Procedures to alter (replace).
582    pub alter: Vec<ProcedureAlterDiff>,
583    /// Triggers to create.
584    pub create_triggers: Vec<TriggerDefinition>,
585    /// Triggers to drop.
586    pub drop_triggers: Vec<String>,
587    /// Triggers to alter.
588    pub alter_triggers: Vec<TriggerAlterDiff>,
589}
590
591/// A procedure alter diff.
592#[derive(Debug, Clone)]
593pub struct ProcedureAlterDiff {
594    /// Old procedure definition.
595    pub old: ProcedureDefinition,
596    /// New procedure definition.
597    pub new: ProcedureDefinition,
598    /// What changed.
599    pub changes: Vec<ProcedureChange>,
600}
601
602/// What changed in a procedure.
603#[derive(Debug, Clone, PartialEq, Eq)]
604pub enum ProcedureChange {
605    Body,
606    Parameters,
607    ReturnType,
608    Language,
609    Volatility,
610    SecurityDefiner,
611    Cost,
612    Parallel,
613}
614
615/// A trigger alter diff.
616#[derive(Debug, Clone)]
617pub struct TriggerAlterDiff {
618    /// Old trigger definition.
619    pub old: TriggerDefinition,
620    /// New trigger definition.
621    pub new: TriggerDefinition,
622}
623
624impl ProcedureDiff {
625    /// Check if there are any differences.
626    pub fn is_empty(&self) -> bool {
627        self.create.is_empty()
628            && self.drop.is_empty()
629            && self.alter.is_empty()
630            && self.create_triggers.is_empty()
631            && self.drop_triggers.is_empty()
632            && self.alter_triggers.is_empty()
633    }
634
635    /// Get a summary of the diff.
636    pub fn summary(&self) -> String {
637        let mut parts = Vec::new();
638
639        if !self.create.is_empty() {
640            parts.push(format!("Create {} procedures", self.create.len()));
641        }
642        if !self.drop.is_empty() {
643            parts.push(format!("Drop {} procedures", self.drop.len()));
644        }
645        if !self.alter.is_empty() {
646            parts.push(format!("Alter {} procedures", self.alter.len()));
647        }
648        if !self.create_triggers.is_empty() {
649            parts.push(format!("Create {} triggers", self.create_triggers.len()));
650        }
651        if !self.drop_triggers.is_empty() {
652            parts.push(format!("Drop {} triggers", self.drop_triggers.len()));
653        }
654        if !self.alter_triggers.is_empty() {
655            parts.push(format!("Alter {} triggers", self.alter_triggers.len()));
656        }
657
658        if parts.is_empty() {
659            "No changes".to_string()
660        } else {
661            parts.join(", ")
662        }
663    }
664}
665
666/// Differ for procedures.
667pub struct ProcedureDiffer;
668
669impl ProcedureDiffer {
670    /// Compute the diff between two sets of procedures.
671    pub fn diff(
672        from: &[ProcedureDefinition],
673        to: &[ProcedureDefinition],
674    ) -> ProcedureDiff {
675        let mut diff = ProcedureDiff::default();
676
677        let from_map: HashMap<_, _> = from.iter().map(|p| (p.qualified_name(), p)).collect();
678        let to_map: HashMap<_, _> = to.iter().map(|p| (p.qualified_name(), p)).collect();
679
680        // Find procedures to create
681        for (name, proc) in &to_map {
682            if !from_map.contains_key(name) {
683                diff.create.push((*proc).clone());
684            }
685        }
686
687        // Find procedures to drop
688        for (name, _) in &from_map {
689            if !to_map.contains_key(name) {
690                diff.drop.push(name.clone());
691            }
692        }
693
694        // Find procedures to alter
695        for (name, new_proc) in &to_map {
696            if let Some(old_proc) = from_map.get(name) {
697                if old_proc.has_changed(new_proc) {
698                    let changes = detect_procedure_changes(old_proc, new_proc);
699                    diff.alter.push(ProcedureAlterDiff {
700                        old: (*old_proc).clone(),
701                        new: (*new_proc).clone(),
702                        changes,
703                    });
704                }
705            }
706        }
707
708        diff
709    }
710
711    /// Compute trigger diff.
712    pub fn diff_triggers(
713        from: &[TriggerDefinition],
714        to: &[TriggerDefinition],
715    ) -> ProcedureDiff {
716        let mut diff = ProcedureDiff::default();
717
718        let from_map: HashMap<_, _> = from.iter().map(|t| (t.qualified_name(), t)).collect();
719        let to_map: HashMap<_, _> = to.iter().map(|t| (t.qualified_name(), t)).collect();
720
721        // Find triggers to create
722        for (name, trigger) in &to_map {
723            if !from_map.contains_key(name) {
724                diff.create_triggers.push((*trigger).clone());
725            }
726        }
727
728        // Find triggers to drop
729        for (name, _) in &from_map {
730            if !to_map.contains_key(name) {
731                diff.drop_triggers.push(name.clone());
732            }
733        }
734
735        // Find triggers to alter
736        for (name, new_trigger) in &to_map {
737            if let Some(old_trigger) = from_map.get(name) {
738                if old_trigger != new_trigger {
739                    diff.alter_triggers.push(TriggerAlterDiff {
740                        old: (*old_trigger).clone(),
741                        new: (*new_trigger).clone(),
742                    });
743                }
744            }
745        }
746
747        diff
748    }
749}
750
751fn detect_procedure_changes(
752    old: &ProcedureDefinition,
753    new: &ProcedureDefinition,
754) -> Vec<ProcedureChange> {
755    let mut changes = Vec::new();
756
757    if old.body != new.body {
758        changes.push(ProcedureChange::Body);
759    }
760    if old.parameters != new.parameters {
761        changes.push(ProcedureChange::Parameters);
762    }
763    if old.return_type != new.return_type || old.returns_set != new.returns_set {
764        changes.push(ProcedureChange::ReturnType);
765    }
766    if old.language != new.language {
767        changes.push(ProcedureChange::Language);
768    }
769    if old.volatility != new.volatility {
770        changes.push(ProcedureChange::Volatility);
771    }
772    if old.security_definer != new.security_definer {
773        changes.push(ProcedureChange::SecurityDefiner);
774    }
775    if old.cost != new.cost {
776        changes.push(ProcedureChange::Cost);
777    }
778    if old.parallel != new.parallel {
779        changes.push(ProcedureChange::Parallel);
780    }
781
782    changes
783}
784
785// ============================================================================
786// SQL Generation
787// ============================================================================
788
789/// Generate SQL for procedure migrations.
790pub struct ProcedureSqlGenerator {
791    /// Database type.
792    pub db_type: DatabaseType,
793}
794
795/// Database type for SQL generation.
796#[derive(Debug, Clone, Copy, PartialEq, Eq)]
797pub enum DatabaseType {
798    PostgreSQL,
799    MySQL,
800    SQLite,
801    MSSQL,
802}
803
804impl ProcedureSqlGenerator {
805    /// Create a new generator.
806    pub fn new(db_type: DatabaseType) -> Self {
807        Self { db_type }
808    }
809
810    /// Generate CREATE FUNCTION/PROCEDURE SQL.
811    pub fn create_procedure(&self, proc: &ProcedureDefinition) -> String {
812        match self.db_type {
813            DatabaseType::PostgreSQL => self.create_postgres_procedure(proc),
814            DatabaseType::MySQL => self.create_mysql_procedure(proc),
815            DatabaseType::SQLite => self.create_sqlite_udf(proc),
816            DatabaseType::MSSQL => self.create_mssql_procedure(proc),
817        }
818    }
819
820    /// Generate DROP FUNCTION/PROCEDURE SQL.
821    pub fn drop_procedure(&self, proc: &ProcedureDefinition) -> String {
822        let obj_type = if proc.is_function { "FUNCTION" } else { "PROCEDURE" };
823        let name = proc.qualified_name();
824
825        match self.db_type {
826            DatabaseType::PostgreSQL => {
827                // PostgreSQL requires parameter types for drop
828                let params = proc
829                    .parameters
830                    .iter()
831                    .map(|p| p.data_type.as_str())
832                    .collect::<Vec<_>>()
833                    .join(", ");
834                format!("DROP {} IF EXISTS {}({});", obj_type, name, params)
835            }
836            DatabaseType::MySQL => {
837                format!("DROP {} IF EXISTS {};", obj_type, name)
838            }
839            DatabaseType::SQLite => {
840                // SQLite doesn't have stored procedures
841                format!("-- SQLite: Remove UDF registration for {}", name)
842            }
843            DatabaseType::MSSQL => {
844                format!(
845                    "IF OBJECT_ID('{}', '{}') IS NOT NULL DROP {} {};",
846                    name,
847                    if proc.is_function { "FN" } else { "P" },
848                    obj_type,
849                    name
850                )
851            }
852        }
853    }
854
855    /// Generate ALTER FUNCTION/PROCEDURE SQL (usually CREATE OR REPLACE).
856    pub fn alter_procedure(&self, diff: &ProcedureAlterDiff) -> String {
857        // For most databases, alter means drop + create or CREATE OR REPLACE
858        match self.db_type {
859            DatabaseType::PostgreSQL => {
860                // PostgreSQL supports CREATE OR REPLACE
861                self.create_postgres_procedure(&diff.new)
862            }
863            DatabaseType::MySQL => {
864                // MySQL requires DROP + CREATE
865                format!(
866                    "{}\n{}",
867                    self.drop_procedure(&diff.old),
868                    self.create_mysql_procedure(&diff.new)
869                )
870            }
871            DatabaseType::SQLite => {
872                self.create_sqlite_udf(&diff.new)
873            }
874            DatabaseType::MSSQL => {
875                // MSSQL uses ALTER
876                self.alter_mssql_procedure(&diff.new)
877            }
878        }
879    }
880
881    /// Generate CREATE TRIGGER SQL.
882    pub fn create_trigger(&self, trigger: &TriggerDefinition) -> String {
883        match self.db_type {
884            DatabaseType::PostgreSQL => self.create_postgres_trigger(trigger),
885            DatabaseType::MySQL => self.create_mysql_trigger(trigger),
886            DatabaseType::SQLite => self.create_sqlite_trigger(trigger),
887            DatabaseType::MSSQL => self.create_mssql_trigger(trigger),
888        }
889    }
890
891    /// Generate DROP TRIGGER SQL.
892    pub fn drop_trigger(&self, trigger: &TriggerDefinition) -> String {
893        match self.db_type {
894            DatabaseType::PostgreSQL => {
895                format!(
896                    "DROP TRIGGER IF EXISTS {} ON {};",
897                    trigger.name, trigger.table
898                )
899            }
900            DatabaseType::MySQL => {
901                format!("DROP TRIGGER IF EXISTS {};", trigger.name)
902            }
903            DatabaseType::SQLite => {
904                format!("DROP TRIGGER IF EXISTS {};", trigger.name)
905            }
906            DatabaseType::MSSQL => {
907                format!("DROP TRIGGER IF EXISTS {};", trigger.qualified_name())
908            }
909        }
910    }
911
912    // PostgreSQL implementations
913    fn create_postgres_procedure(&self, proc: &ProcedureDefinition) -> String {
914        let mut sql = String::new();
915
916        let obj_type = if proc.is_function { "FUNCTION" } else { "PROCEDURE" };
917        let or_replace = if proc.or_replace { "OR REPLACE " } else { "" };
918
919        sql.push_str(&format!("CREATE {}{}  {} (", or_replace, obj_type, proc.qualified_name()));
920
921        // Parameters
922        let params: Vec<String> = proc
923            .parameters
924            .iter()
925            .map(|p| {
926                let mode = match p.mode {
927                    ParameterMode::In => "",
928                    ParameterMode::Out => "OUT ",
929                    ParameterMode::InOut => "INOUT ",
930                    ParameterMode::Variadic => "VARIADIC ",
931                };
932                let default = p
933                    .default
934                    .as_ref()
935                    .map(|d| format!(" DEFAULT {}", d))
936                    .unwrap_or_default();
937                format!("{}{} {}{}", mode, p.name, p.data_type, default)
938            })
939            .collect();
940        sql.push_str(&params.join(", "));
941        sql.push_str(")\n");
942
943        // Return type
944        if let Some(ref ret) = proc.return_type {
945            if proc.returns_set {
946                sql.push_str(&format!("RETURNS SETOF {}\n", ret));
947            } else {
948                sql.push_str(&format!("RETURNS {}\n", ret));
949            }
950        } else if !proc.return_columns.is_empty() {
951            let cols: Vec<String> = proc
952                .return_columns
953                .iter()
954                .map(|c| format!("{} {}", c.name, c.data_type))
955                .collect();
956            sql.push_str(&format!("RETURNS TABLE ({})\n", cols.join(", ")));
957        } else if proc.is_function {
958            sql.push_str("RETURNS void\n");
959        }
960
961        // Language
962        sql.push_str(&format!("LANGUAGE {}\n", proc.language.to_sql()));
963
964        // Volatility
965        sql.push_str(&format!("{}\n", proc.volatility.to_sql()));
966
967        // Security
968        if proc.security_definer {
969            sql.push_str("SECURITY DEFINER\n");
970        }
971
972        // Cost
973        if let Some(cost) = proc.cost {
974            sql.push_str(&format!("COST {}\n", cost));
975        }
976
977        // Parallel
978        if proc.parallel != ParallelSafety::Unsafe {
979            sql.push_str(&format!("{}\n", proc.parallel.to_sql()));
980        }
981
982        // Body
983        sql.push_str(&format!("AS $$\n{}\n$$;", proc.body));
984
985        // Comment
986        if let Some(ref comment) = proc.comment {
987            sql.push_str(&format!(
988                "\n\nCOMMENT ON {} {} IS '{}';",
989                obj_type,
990                proc.qualified_name(),
991                comment.replace('\'', "''")
992            ));
993        }
994
995        sql
996    }
997
998    fn create_postgres_trigger(&self, trigger: &TriggerDefinition) -> String {
999        let mut sql = String::new();
1000
1001        let or_replace = if trigger.or_replace {
1002            "OR REPLACE "
1003        } else {
1004            ""
1005        };
1006
1007        sql.push_str(&format!(
1008            "CREATE {}TRIGGER {}\n",
1009            or_replace, trigger.name
1010        ));
1011
1012        // Timing
1013        sql.push_str(&format!("{} ", trigger.timing.to_sql()));
1014
1015        // Events
1016        let events: Vec<&str> = trigger.events.iter().map(|e| e.to_sql()).collect();
1017        sql.push_str(&events.join(" OR "));
1018
1019        // Table
1020        sql.push_str(&format!("\nON {}\n", trigger.table));
1021
1022        // Level
1023        sql.push_str(&format!("{}\n", trigger.level.to_sql()));
1024
1025        // Condition
1026        if let Some(ref cond) = trigger.condition {
1027            sql.push_str(&format!("WHEN ({})\n", cond));
1028        }
1029
1030        // Function
1031        let args = if trigger.function_args.is_empty() {
1032            String::new()
1033        } else {
1034            trigger.function_args.join(", ")
1035        };
1036        sql.push_str(&format!("EXECUTE FUNCTION {}({});", trigger.function, args));
1037
1038        sql
1039    }
1040
1041    // MySQL implementations
1042    fn create_mysql_procedure(&self, proc: &ProcedureDefinition) -> String {
1043        let mut sql = String::new();
1044
1045        let obj_type = if proc.is_function { "FUNCTION" } else { "PROCEDURE" };
1046
1047        // MySQL doesn't support CREATE OR REPLACE for procedures
1048        sql.push_str(&format!("CREATE {} {} (", obj_type, proc.qualified_name()));
1049
1050        // Parameters
1051        let params: Vec<String> = proc
1052            .parameters
1053            .iter()
1054            .map(|p| {
1055                let mode = match p.mode {
1056                    ParameterMode::In => "IN ",
1057                    ParameterMode::Out => "OUT ",
1058                    ParameterMode::InOut => "INOUT ",
1059                    ParameterMode::Variadic => "",
1060                };
1061                format!("{}{} {}", mode, p.name, p.data_type)
1062            })
1063            .collect();
1064        sql.push_str(&params.join(", "));
1065        sql.push_str(")\n");
1066
1067        // Return type (functions only)
1068        if proc.is_function {
1069            if let Some(ref ret) = proc.return_type {
1070                sql.push_str(&format!("RETURNS {}\n", ret));
1071            }
1072        }
1073
1074        // Characteristics
1075        if proc.volatility == Volatility::Immutable {
1076            sql.push_str("DETERMINISTIC\n");
1077        } else {
1078            sql.push_str("NOT DETERMINISTIC\n");
1079        }
1080
1081        if proc.security_definer {
1082            sql.push_str("SQL SECURITY DEFINER\n");
1083        }
1084
1085        // Body
1086        sql.push_str(&format!("BEGIN\n{}\nEND;", proc.body));
1087
1088        sql
1089    }
1090
1091    fn create_mysql_trigger(&self, trigger: &TriggerDefinition) -> String {
1092        let mut sql = String::new();
1093
1094        sql.push_str(&format!("CREATE TRIGGER {}\n", trigger.name));
1095
1096        // Timing
1097        sql.push_str(&format!("{} ", trigger.timing.to_sql()));
1098
1099        // Event (MySQL only supports one)
1100        if let Some(event) = trigger.events.first() {
1101            sql.push_str(&format!("{}\n", event.to_sql()));
1102        }
1103
1104        // Table
1105        sql.push_str(&format!("ON {}\n", trigger.table));
1106
1107        // Level
1108        sql.push_str(&format!("{}\n", trigger.level.to_sql()));
1109
1110        // Body (MySQL uses the function body directly)
1111        sql.push_str(&format!("BEGIN\n    CALL {}();\nEND;", trigger.function));
1112
1113        sql
1114    }
1115
1116    // SQLite implementations
1117    fn create_sqlite_udf(&self, proc: &ProcedureDefinition) -> String {
1118        // SQLite UDFs are registered in code, not SQL
1119        format!(
1120            "-- SQLite UDF: {} must be registered via rusqlite::create_scalar_function\n\
1121             -- Parameters: {}\n\
1122             -- Body:\n-- {}",
1123            proc.name,
1124            proc.parameters
1125                .iter()
1126                .map(|p| format!("{}: {}", p.name, p.data_type))
1127                .collect::<Vec<_>>()
1128                .join(", "),
1129            proc.body.replace('\n', "\n-- ")
1130        )
1131    }
1132
1133    fn create_sqlite_trigger(&self, trigger: &TriggerDefinition) -> String {
1134        let mut sql = String::new();
1135
1136        sql.push_str(&format!("CREATE TRIGGER IF NOT EXISTS {}\n", trigger.name));
1137
1138        // Timing
1139        sql.push_str(&format!("{} ", trigger.timing.to_sql()));
1140
1141        // Events
1142        let events: Vec<&str> = trigger.events.iter().map(|e| e.to_sql()).collect();
1143        sql.push_str(&events.join(" OR "));
1144
1145        // Table
1146        sql.push_str(&format!("\nON {}\n", trigger.table));
1147
1148        // Level (SQLite only supports row-level)
1149        sql.push_str("FOR EACH ROW\n");
1150
1151        // Condition
1152        if let Some(ref cond) = trigger.condition {
1153            sql.push_str(&format!("WHEN {}\n", cond));
1154        }
1155
1156        // Body (inline for SQLite)
1157        sql.push_str(&format!("BEGIN\n    SELECT {}();\nEND;", trigger.function));
1158
1159        sql
1160    }
1161
1162    // MSSQL implementations
1163    fn create_mssql_procedure(&self, proc: &ProcedureDefinition) -> String {
1164        let mut sql = String::new();
1165
1166        let obj_type = if proc.is_function { "FUNCTION" } else { "PROCEDURE" };
1167
1168        sql.push_str(&format!("CREATE {} {} (", obj_type, proc.qualified_name()));
1169
1170        // Parameters
1171        let params: Vec<String> = proc
1172            .parameters
1173            .iter()
1174            .map(|p| {
1175                let output = if p.mode == ParameterMode::Out || p.mode == ParameterMode::InOut {
1176                    " OUTPUT"
1177                } else {
1178                    ""
1179                };
1180                format!("@{} {}{}", p.name, p.data_type, output)
1181            })
1182            .collect();
1183        sql.push_str(&params.join(", "));
1184        sql.push_str(")\n");
1185
1186        // Return type (functions only)
1187        if proc.is_function {
1188            if let Some(ref ret) = proc.return_type {
1189                sql.push_str(&format!("RETURNS {}\n", ret));
1190            }
1191        }
1192
1193        sql.push_str("AS\nBEGIN\n");
1194        sql.push_str(&proc.body);
1195        sql.push_str("\nEND;");
1196
1197        sql
1198    }
1199
1200    fn alter_mssql_procedure(&self, proc: &ProcedureDefinition) -> String {
1201        // Change CREATE to ALTER
1202        self.create_mssql_procedure(proc).replacen("CREATE", "ALTER", 1)
1203    }
1204
1205    fn create_mssql_trigger(&self, trigger: &TriggerDefinition) -> String {
1206        let mut sql = String::new();
1207
1208        sql.push_str(&format!(
1209            "CREATE TRIGGER {}\nON {}\n",
1210            trigger.qualified_name(),
1211            trigger.table
1212        ));
1213
1214        // Timing
1215        sql.push_str(&format!("{} ", trigger.timing.to_sql()));
1216
1217        // Events
1218        let events: Vec<&str> = trigger.events.iter().map(|e| e.to_sql()).collect();
1219        sql.push_str(&events.join(", "));
1220
1221        sql.push_str("\nAS\nBEGIN\n");
1222        sql.push_str(&format!("    EXEC {};\n", trigger.function));
1223        sql.push_str("END;");
1224
1225        sql
1226    }
1227
1228    /// Generate full migration SQL for a procedure diff.
1229    pub fn generate_migration(&self, diff: &ProcedureDiff) -> MigrationSql {
1230        let mut up = Vec::new();
1231        let mut down = Vec::new();
1232
1233        // Create procedures
1234        for proc in &diff.create {
1235            up.push(self.create_procedure(proc));
1236            down.push(self.drop_procedure(proc));
1237        }
1238
1239        // Drop procedures
1240        for name in &diff.drop {
1241            // For down, we'd need the original definition
1242            up.push(format!("DROP FUNCTION IF EXISTS {};", name));
1243            // down would need to recreate, but we don't have the original
1244            down.push(format!("-- Recreate {} (original definition needed)", name));
1245        }
1246
1247        // Alter procedures
1248        for alter in &diff.alter {
1249            up.push(self.alter_procedure(alter));
1250            // Down would restore the old version
1251            down.push(self.create_procedure(&alter.old));
1252        }
1253
1254        // Create triggers
1255        for trigger in &diff.create_triggers {
1256            up.push(self.create_trigger(trigger));
1257            down.push(self.drop_trigger(trigger));
1258        }
1259
1260        // Drop triggers
1261        for name in &diff.drop_triggers {
1262            up.push(format!("DROP TRIGGER IF EXISTS {};", name));
1263            down.push(format!("-- Recreate trigger {} (original definition needed)", name));
1264        }
1265
1266        // Alter triggers
1267        for alter in &diff.alter_triggers {
1268            up.push(self.drop_trigger(&alter.old));
1269            up.push(self.create_trigger(&alter.new));
1270            down.push(self.drop_trigger(&alter.new));
1271            down.push(self.create_trigger(&alter.old));
1272        }
1273
1274        MigrationSql {
1275            up: up.join("\n\n"),
1276            down: down.join("\n\n"),
1277        }
1278    }
1279}
1280
1281/// Generated migration SQL.
1282#[derive(Debug, Clone)]
1283pub struct MigrationSql {
1284    /// Up migration SQL.
1285    pub up: String,
1286    /// Down migration SQL (rollback).
1287    pub down: String,
1288}
1289
1290// ============================================================================
1291// Procedure Store
1292// ============================================================================
1293
1294/// Storage for procedure definitions with version tracking.
1295#[derive(Debug, Clone, Default, Serialize, Deserialize)]
1296pub struct ProcedureStore {
1297    /// Stored procedures and functions.
1298    pub procedures: HashMap<String, ProcedureDefinition>,
1299    /// Triggers.
1300    pub triggers: HashMap<String, TriggerDefinition>,
1301    /// Scheduled events.
1302    pub events: HashMap<String, ScheduledEvent>,
1303    /// History of changes.
1304    pub history: Vec<ProcedureHistoryEntry>,
1305}
1306
1307/// A history entry for procedure changes.
1308#[derive(Debug, Clone, Serialize, Deserialize)]
1309pub struct ProcedureHistoryEntry {
1310    /// Timestamp.
1311    pub timestamp: String,
1312    /// Migration ID.
1313    pub migration_id: String,
1314    /// Change type.
1315    pub change_type: ChangeType,
1316    /// Object name.
1317    pub name: String,
1318    /// Old checksum.
1319    pub old_checksum: Option<String>,
1320    /// New checksum.
1321    pub new_checksum: Option<String>,
1322}
1323
1324/// Type of change.
1325#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
1326pub enum ChangeType {
1327    Create,
1328    Alter,
1329    Drop,
1330}
1331
1332impl ProcedureStore {
1333    /// Create a new store.
1334    pub fn new() -> Self {
1335        Self::default()
1336    }
1337
1338    /// Add a procedure.
1339    pub fn add_procedure(&mut self, proc: ProcedureDefinition) {
1340        self.procedures.insert(proc.qualified_name(), proc);
1341    }
1342
1343    /// Add a trigger.
1344    pub fn add_trigger(&mut self, trigger: TriggerDefinition) {
1345        self.triggers.insert(trigger.qualified_name(), trigger);
1346    }
1347
1348    /// Get all procedures as a list.
1349    pub fn procedures_list(&self) -> Vec<&ProcedureDefinition> {
1350        self.procedures.values().collect()
1351    }
1352
1353    /// Get all triggers as a list.
1354    pub fn triggers_list(&self) -> Vec<&TriggerDefinition> {
1355        self.triggers.values().collect()
1356    }
1357
1358    /// Save to file as TOML.
1359    pub fn save(&self, path: &std::path::Path) -> std::io::Result<()> {
1360        let content = toml::to_string_pretty(self)
1361            .map_err(|e| std::io::Error::new(std::io::ErrorKind::Other, e))?;
1362        std::fs::write(path, content)
1363    }
1364
1365    /// Load from file as TOML.
1366    pub fn load(path: &std::path::Path) -> std::io::Result<Self> {
1367        let content = std::fs::read_to_string(path)?;
1368        toml::from_str(&content)
1369            .map_err(|e| std::io::Error::new(std::io::ErrorKind::Other, e))
1370    }
1371
1372    /// Add an event.
1373    pub fn add_event(&mut self, event: ScheduledEvent) {
1374        self.events.insert(event.name.clone(), event);
1375    }
1376
1377    /// Get all events as a list.
1378    pub fn events_list(&self) -> Vec<&ScheduledEvent> {
1379        self.events.values().collect()
1380    }
1381}
1382
1383// ============================================================================
1384// Event Scheduler (MySQL) / SQL Agent Jobs (MSSQL)
1385// ============================================================================
1386
1387/// A scheduled event definition (MySQL EVENT / MSSQL SQL Agent Job).
1388#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1389pub struct ScheduledEvent {
1390    /// Event name.
1391    pub name: String,
1392    /// Schema/database.
1393    pub schema: Option<String>,
1394    /// Schedule definition.
1395    pub schedule: EventSchedule,
1396    /// SQL body to execute.
1397    pub body: String,
1398    /// Whether the event is enabled.
1399    pub enabled: bool,
1400    /// Event preservation after expiration.
1401    pub on_completion: OnCompletion,
1402    /// Comment/description.
1403    pub comment: Option<String>,
1404    /// Start time (optional).
1405    pub starts: Option<String>,
1406    /// End time (optional).
1407    pub ends: Option<String>,
1408    /// Definer (MySQL) or owner (MSSQL).
1409    pub definer: Option<String>,
1410}
1411
1412impl Default for ScheduledEvent {
1413    fn default() -> Self {
1414        Self {
1415            name: String::new(),
1416            schema: None,
1417            schedule: EventSchedule::Once,
1418            body: String::new(),
1419            enabled: true,
1420            on_completion: OnCompletion::Drop,
1421            comment: None,
1422            starts: None,
1423            ends: None,
1424            definer: None,
1425        }
1426    }
1427}
1428
1429impl ScheduledEvent {
1430    /// Create a new scheduled event.
1431    pub fn new(name: impl Into<String>) -> Self {
1432        Self {
1433            name: name.into(),
1434            ..Default::default()
1435        }
1436    }
1437
1438    /// Set the schema.
1439    pub fn schema(mut self, schema: impl Into<String>) -> Self {
1440        self.schema = Some(schema.into());
1441        self
1442    }
1443
1444    /// Set to run once at a specific time.
1445    pub fn at(mut self, datetime: impl Into<String>) -> Self {
1446        self.schedule = EventSchedule::At(datetime.into());
1447        self
1448    }
1449
1450    /// Set to run every interval.
1451    pub fn every(mut self, interval: EventInterval) -> Self {
1452        self.schedule = EventSchedule::Every(interval);
1453        self
1454    }
1455
1456    /// Set the SQL body.
1457    pub fn body(mut self, body: impl Into<String>) -> Self {
1458        self.body = body.into();
1459        self
1460    }
1461
1462    /// Disable the event.
1463    pub fn disabled(mut self) -> Self {
1464        self.enabled = false;
1465        self
1466    }
1467
1468    /// Preserve the event after completion.
1469    pub fn preserve(mut self) -> Self {
1470        self.on_completion = OnCompletion::Preserve;
1471        self
1472    }
1473
1474    /// Set start time.
1475    pub fn starts(mut self, datetime: impl Into<String>) -> Self {
1476        self.starts = Some(datetime.into());
1477        self
1478    }
1479
1480    /// Set end time.
1481    pub fn ends(mut self, datetime: impl Into<String>) -> Self {
1482        self.ends = Some(datetime.into());
1483        self
1484    }
1485
1486    /// Set comment.
1487    pub fn comment(mut self, comment: impl Into<String>) -> Self {
1488        self.comment = Some(comment.into());
1489        self
1490    }
1491
1492    /// Get the fully qualified name.
1493    pub fn qualified_name(&self) -> String {
1494        match &self.schema {
1495            Some(schema) => format!("{}.{}", schema, self.name),
1496            None => self.name.clone(),
1497        }
1498    }
1499}
1500
1501/// Event schedule type.
1502#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1503pub enum EventSchedule {
1504    /// Run once (immediately or when triggered).
1505    Once,
1506    /// Run at a specific time.
1507    At(String),
1508    /// Run every interval.
1509    Every(EventInterval),
1510    /// Cron expression (MSSQL).
1511    Cron(String),
1512}
1513
1514/// Event interval for recurring events.
1515#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1516pub struct EventInterval {
1517    /// Quantity.
1518    pub quantity: u32,
1519    /// Unit.
1520    pub unit: IntervalUnit,
1521}
1522
1523impl EventInterval {
1524    /// Create a new interval.
1525    pub fn new(quantity: u32, unit: IntervalUnit) -> Self {
1526        Self { quantity, unit }
1527    }
1528
1529    /// Every N seconds.
1530    pub fn seconds(n: u32) -> Self {
1531        Self::new(n, IntervalUnit::Second)
1532    }
1533
1534    /// Every N minutes.
1535    pub fn minutes(n: u32) -> Self {
1536        Self::new(n, IntervalUnit::Minute)
1537    }
1538
1539    /// Every N hours.
1540    pub fn hours(n: u32) -> Self {
1541        Self::new(n, IntervalUnit::Hour)
1542    }
1543
1544    /// Every N days.
1545    pub fn days(n: u32) -> Self {
1546        Self::new(n, IntervalUnit::Day)
1547    }
1548
1549    /// Every N weeks.
1550    pub fn weeks(n: u32) -> Self {
1551        Self::new(n, IntervalUnit::Week)
1552    }
1553
1554    /// Every N months.
1555    pub fn months(n: u32) -> Self {
1556        Self::new(n, IntervalUnit::Month)
1557    }
1558
1559    /// Convert to MySQL interval string.
1560    pub fn to_mysql(&self) -> String {
1561        format!("{} {}", self.quantity, self.unit.to_mysql())
1562    }
1563}
1564
1565/// Interval unit.
1566#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
1567pub enum IntervalUnit {
1568    Second,
1569    Minute,
1570    Hour,
1571    Day,
1572    Week,
1573    Month,
1574    Quarter,
1575    Year,
1576}
1577
1578impl IntervalUnit {
1579    /// Convert to MySQL interval unit.
1580    pub fn to_mysql(&self) -> &'static str {
1581        match self {
1582            Self::Second => "SECOND",
1583            Self::Minute => "MINUTE",
1584            Self::Hour => "HOUR",
1585            Self::Day => "DAY",
1586            Self::Week => "WEEK",
1587            Self::Month => "MONTH",
1588            Self::Quarter => "QUARTER",
1589            Self::Year => "YEAR",
1590        }
1591    }
1592}
1593
1594/// What to do when event completes.
1595#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
1596pub enum OnCompletion {
1597    /// Drop the event.
1598    #[default]
1599    Drop,
1600    /// Preserve the event.
1601    Preserve,
1602}
1603
1604// ============================================================================
1605// MSSQL SQL Agent Job
1606// ============================================================================
1607
1608/// SQL Server Agent Job definition.
1609#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1610pub struct SqlAgentJob {
1611    /// Job name.
1612    pub name: String,
1613    /// Job description.
1614    pub description: Option<String>,
1615    /// Job category.
1616    pub category: Option<String>,
1617    /// Job owner.
1618    pub owner: Option<String>,
1619    /// Whether the job is enabled.
1620    pub enabled: bool,
1621    /// Job steps.
1622    pub steps: Vec<JobStep>,
1623    /// Job schedules.
1624    pub schedules: Vec<JobSchedule>,
1625    /// Notification settings.
1626    pub notify_level: NotifyLevel,
1627}
1628
1629impl Default for SqlAgentJob {
1630    fn default() -> Self {
1631        Self {
1632            name: String::new(),
1633            description: None,
1634            category: None,
1635            owner: None,
1636            enabled: true,
1637            steps: Vec::new(),
1638            schedules: Vec::new(),
1639            notify_level: NotifyLevel::OnFailure,
1640        }
1641    }
1642}
1643
1644impl SqlAgentJob {
1645    /// Create a new SQL Agent job.
1646    pub fn new(name: impl Into<String>) -> Self {
1647        Self {
1648            name: name.into(),
1649            ..Default::default()
1650        }
1651    }
1652
1653    /// Set description.
1654    pub fn description(mut self, desc: impl Into<String>) -> Self {
1655        self.description = Some(desc.into());
1656        self
1657    }
1658
1659    /// Add a step.
1660    pub fn step(mut self, step: JobStep) -> Self {
1661        self.steps.push(step);
1662        self
1663    }
1664
1665    /// Add a T-SQL step.
1666    pub fn tsql_step(mut self, name: impl Into<String>, sql: impl Into<String>) -> Self {
1667        self.steps.push(JobStep {
1668            name: name.into(),
1669            step_type: StepType::TSql,
1670            command: sql.into(),
1671            database: None,
1672            on_success: StepAction::GoToNextStep,
1673            on_failure: StepAction::QuitWithFailure,
1674            retry_attempts: 0,
1675            retry_interval: 0,
1676        });
1677        self
1678    }
1679
1680    /// Add a schedule.
1681    pub fn schedule(mut self, schedule: JobSchedule) -> Self {
1682        self.schedules.push(schedule);
1683        self
1684    }
1685
1686    /// Disable the job.
1687    pub fn disabled(mut self) -> Self {
1688        self.enabled = false;
1689        self
1690    }
1691}
1692
1693/// A step in a SQL Agent job.
1694#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1695pub struct JobStep {
1696    /// Step name.
1697    pub name: String,
1698    /// Step type.
1699    pub step_type: StepType,
1700    /// Command to execute.
1701    pub command: String,
1702    /// Database context.
1703    pub database: Option<String>,
1704    /// Action on success.
1705    pub on_success: StepAction,
1706    /// Action on failure.
1707    pub on_failure: StepAction,
1708    /// Retry attempts.
1709    pub retry_attempts: u32,
1710    /// Retry interval in minutes.
1711    pub retry_interval: u32,
1712}
1713
1714/// Job step type.
1715#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
1716pub enum StepType {
1717    #[default]
1718    TSql,
1719    CmdExec,
1720    PowerShell,
1721    Ssis,
1722    Ssas,
1723    Ssrs,
1724}
1725
1726impl StepType {
1727    /// Get the subsystem name for sp_add_jobstep.
1728    pub fn subsystem(&self) -> &'static str {
1729        match self {
1730            Self::TSql => "TSQL",
1731            Self::CmdExec => "CmdExec",
1732            Self::PowerShell => "PowerShell",
1733            Self::Ssis => "SSIS",
1734            Self::Ssas => "AnalysisCommand",
1735            Self::Ssrs => "Reporting Services Command",
1736        }
1737    }
1738}
1739
1740/// Action after step completion.
1741#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
1742pub enum StepAction {
1743    #[default]
1744    GoToNextStep,
1745    GoToStep(u32),
1746    QuitWithSuccess,
1747    QuitWithFailure,
1748}
1749
1750impl StepAction {
1751    /// Get the action ID for sp_add_jobstep.
1752    pub fn action_id(&self) -> u32 {
1753        match self {
1754            Self::GoToNextStep => 3,
1755            Self::GoToStep(_) => 4,
1756            Self::QuitWithSuccess => 1,
1757            Self::QuitWithFailure => 2,
1758        }
1759    }
1760}
1761
1762/// Job schedule.
1763#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1764pub struct JobSchedule {
1765    /// Schedule name.
1766    pub name: String,
1767    /// Frequency type.
1768    pub frequency: ScheduleFrequency,
1769    /// Time of day (for daily/weekly/monthly).
1770    pub active_start_time: Option<String>,
1771    /// Start date.
1772    pub start_date: Option<String>,
1773    /// End date.
1774    pub end_date: Option<String>,
1775    /// Whether enabled.
1776    pub enabled: bool,
1777}
1778
1779impl Default for JobSchedule {
1780    fn default() -> Self {
1781        Self {
1782            name: String::new(),
1783            frequency: ScheduleFrequency::Daily { every_n_days: 1 },
1784            active_start_time: None,
1785            start_date: None,
1786            end_date: None,
1787            enabled: true,
1788        }
1789    }
1790}
1791
1792impl JobSchedule {
1793    /// Create a new schedule.
1794    pub fn new(name: impl Into<String>) -> Self {
1795        Self {
1796            name: name.into(),
1797            ..Default::default()
1798        }
1799    }
1800
1801    /// Run once.
1802    pub fn once(mut self) -> Self {
1803        self.frequency = ScheduleFrequency::Once;
1804        self
1805    }
1806
1807    /// Run daily.
1808    pub fn daily(mut self, every_n_days: u32) -> Self {
1809        self.frequency = ScheduleFrequency::Daily { every_n_days };
1810        self
1811    }
1812
1813    /// Run weekly.
1814    pub fn weekly(mut self, days: Vec<Weekday>) -> Self {
1815        self.frequency = ScheduleFrequency::Weekly {
1816            every_n_weeks: 1,
1817            days,
1818        };
1819        self
1820    }
1821
1822    /// Run monthly.
1823    pub fn monthly(mut self, day_of_month: u32) -> Self {
1824        self.frequency = ScheduleFrequency::Monthly {
1825            every_n_months: 1,
1826            day_of_month,
1827        };
1828        self
1829    }
1830
1831    /// Set time of day.
1832    pub fn at(mut self, time: impl Into<String>) -> Self {
1833        self.active_start_time = Some(time.into());
1834        self
1835    }
1836}
1837
1838/// Schedule frequency.
1839#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1840pub enum ScheduleFrequency {
1841    Once,
1842    Daily { every_n_days: u32 },
1843    Weekly { every_n_weeks: u32, days: Vec<Weekday> },
1844    Monthly { every_n_months: u32, day_of_month: u32 },
1845    OnIdle,
1846    OnAgentStart,
1847}
1848
1849impl ScheduleFrequency {
1850    /// Get frequency type ID for sp_add_schedule.
1851    pub fn freq_type(&self) -> u32 {
1852        match self {
1853            Self::Once => 1,
1854            Self::Daily { .. } => 4,
1855            Self::Weekly { .. } => 8,
1856            Self::Monthly { .. } => 16,
1857            Self::OnIdle => 128,
1858            Self::OnAgentStart => 64,
1859        }
1860    }
1861
1862    /// Get frequency interval.
1863    pub fn freq_interval(&self) -> u32 {
1864        match self {
1865            Self::Once => 0,
1866            Self::Daily { every_n_days } => *every_n_days,
1867            Self::Weekly { days, .. } => {
1868                days.iter().map(|d| d.bitmask()).fold(0, |acc, m| acc | m)
1869            }
1870            Self::Monthly { day_of_month, .. } => *day_of_month,
1871            Self::OnIdle | Self::OnAgentStart => 0,
1872        }
1873    }
1874}
1875
1876/// Day of week.
1877#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
1878pub enum Weekday {
1879    Sunday,
1880    Monday,
1881    Tuesday,
1882    Wednesday,
1883    Thursday,
1884    Friday,
1885    Saturday,
1886}
1887
1888impl Weekday {
1889    /// Get bitmask for SQL Agent.
1890    pub fn bitmask(&self) -> u32 {
1891        match self {
1892            Self::Sunday => 1,
1893            Self::Monday => 2,
1894            Self::Tuesday => 4,
1895            Self::Wednesday => 8,
1896            Self::Thursday => 16,
1897            Self::Friday => 32,
1898            Self::Saturday => 64,
1899        }
1900    }
1901}
1902
1903/// Notification level for job completion.
1904#[derive(Debug, Clone, Copy, PartialEq, Eq, Default, Serialize, Deserialize)]
1905pub enum NotifyLevel {
1906    Never,
1907    OnSuccess,
1908    #[default]
1909    OnFailure,
1910    Always,
1911}
1912
1913// ============================================================================
1914// MongoDB Atlas Triggers
1915// ============================================================================
1916
1917/// MongoDB Atlas Trigger definition.
1918#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
1919pub struct AtlasTrigger {
1920    /// Trigger name.
1921    pub name: String,
1922    /// Trigger type.
1923    pub trigger_type: AtlasTriggerType,
1924    /// Whether enabled.
1925    pub enabled: bool,
1926    /// Function to execute.
1927    pub function_name: String,
1928    /// Match expression for database triggers.
1929    pub match_expression: Option<String>,
1930    /// Project expression.
1931    pub project: Option<String>,
1932    /// Full document option.
1933    pub full_document: bool,
1934    /// Full document before change.
1935    pub full_document_before_change: bool,
1936}
1937
1938impl Default for AtlasTrigger {
1939    fn default() -> Self {
1940        Self {
1941            name: String::new(),
1942            trigger_type: AtlasTriggerType::Database {
1943                database: String::new(),
1944                collection: String::new(),
1945                operation_types: Vec::new(),
1946            },
1947            enabled: true,
1948            function_name: String::new(),
1949            match_expression: None,
1950            project: None,
1951            full_document: false,
1952            full_document_before_change: false,
1953        }
1954    }
1955}
1956
1957impl AtlasTrigger {
1958    /// Create a new Atlas trigger.
1959    pub fn new(name: impl Into<String>) -> Self {
1960        Self {
1961            name: name.into(),
1962            ..Default::default()
1963        }
1964    }
1965
1966    /// Configure as database trigger.
1967    pub fn database(
1968        mut self,
1969        database: impl Into<String>,
1970        collection: impl Into<String>,
1971        operations: Vec<AtlasOperation>,
1972    ) -> Self {
1973        self.trigger_type = AtlasTriggerType::Database {
1974            database: database.into(),
1975            collection: collection.into(),
1976            operation_types: operations,
1977        };
1978        self
1979    }
1980
1981    /// Configure as scheduled trigger.
1982    pub fn scheduled(mut self, cron: impl Into<String>) -> Self {
1983        self.trigger_type = AtlasTriggerType::Scheduled {
1984            schedule: cron.into(),
1985        };
1986        self
1987    }
1988
1989    /// Configure as authentication trigger.
1990    pub fn authentication(mut self, operation: AuthOperation) -> Self {
1991        self.trigger_type = AtlasTriggerType::Authentication { operation };
1992        self
1993    }
1994
1995    /// Set the function to execute.
1996    pub fn function(mut self, name: impl Into<String>) -> Self {
1997        self.function_name = name.into();
1998        self
1999    }
2000
2001    /// Enable full document.
2002    pub fn full_document(mut self) -> Self {
2003        self.full_document = true;
2004        self
2005    }
2006
2007    /// Disable the trigger.
2008    pub fn disabled(mut self) -> Self {
2009        self.enabled = false;
2010        self
2011    }
2012}
2013
2014/// Atlas trigger type.
2015#[derive(Debug, Clone, PartialEq, Eq, Serialize, Deserialize)]
2016pub enum AtlasTriggerType {
2017    /// Database change trigger.
2018    Database {
2019        database: String,
2020        collection: String,
2021        operation_types: Vec<AtlasOperation>,
2022    },
2023    /// Scheduled trigger (cron).
2024    Scheduled { schedule: String },
2025    /// Authentication trigger.
2026    Authentication { operation: AuthOperation },
2027}
2028
2029/// Atlas database operation types.
2030#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
2031pub enum AtlasOperation {
2032    Insert,
2033    Update,
2034    Replace,
2035    Delete,
2036}
2037
2038impl AtlasOperation {
2039    /// Get the operation type string.
2040    pub fn as_str(&self) -> &'static str {
2041        match self {
2042            Self::Insert => "INSERT",
2043            Self::Update => "UPDATE",
2044            Self::Replace => "REPLACE",
2045            Self::Delete => "DELETE",
2046        }
2047    }
2048}
2049
2050/// Authentication operation types.
2051#[derive(Debug, Clone, Copy, PartialEq, Eq, Serialize, Deserialize)]
2052pub enum AuthOperation {
2053    Create,
2054    Login,
2055    Delete,
2056}
2057
2058// ============================================================================
2059// Event SQL Generation
2060// ============================================================================
2061
2062impl ProcedureSqlGenerator {
2063    /// Generate CREATE EVENT SQL (MySQL).
2064    pub fn create_event(&self, event: &ScheduledEvent) -> String {
2065        match self.db_type {
2066            DatabaseType::MySQL => self.create_mysql_event(event),
2067            DatabaseType::MSSQL => {
2068                // MSSQL uses SQL Agent - return a comment
2069                format!(
2070                    "-- Use SqlAgentJob for MSSQL scheduled tasks\n\
2071                     -- Event: {}\n\
2072                     -- Schedule: {:?}",
2073                    event.name, event.schedule
2074                )
2075            }
2076            _ => format!("-- Events not supported for {:?}", self.db_type),
2077        }
2078    }
2079
2080    /// Generate DROP EVENT SQL.
2081    pub fn drop_event(&self, event: &ScheduledEvent) -> String {
2082        match self.db_type {
2083            DatabaseType::MySQL => {
2084                format!("DROP EVENT IF EXISTS {};", event.qualified_name())
2085            }
2086            _ => format!("-- DROP EVENT not supported for {:?}", self.db_type),
2087        }
2088    }
2089
2090    fn create_mysql_event(&self, event: &ScheduledEvent) -> String {
2091        let mut sql = String::new();
2092
2093        sql.push_str(&format!("CREATE EVENT IF NOT EXISTS {}\n", event.qualified_name()));
2094
2095        // Schedule
2096        match &event.schedule {
2097            EventSchedule::Once => sql.push_str("ON SCHEDULE AT CURRENT_TIMESTAMP\n"),
2098            EventSchedule::At(datetime) => sql.push_str(&format!("ON SCHEDULE AT '{}'\n", datetime)),
2099            EventSchedule::Every(interval) => {
2100                sql.push_str(&format!("ON SCHEDULE EVERY {}\n", interval.to_mysql()));
2101                if let Some(ref starts) = event.starts {
2102                    sql.push_str(&format!("STARTS '{}'\n", starts));
2103                }
2104                if let Some(ref ends) = event.ends {
2105                    sql.push_str(&format!("ENDS '{}'\n", ends));
2106                }
2107            }
2108            EventSchedule::Cron(_) => {
2109                // MySQL doesn't support cron directly, use EVERY
2110                sql.push_str("ON SCHEDULE EVERY 1 DAY\n");
2111            }
2112        }
2113
2114        // Completion
2115        match event.on_completion {
2116            OnCompletion::Drop => sql.push_str("ON COMPLETION NOT PRESERVE\n"),
2117            OnCompletion::Preserve => sql.push_str("ON COMPLETION PRESERVE\n"),
2118        }
2119
2120        // Enabled
2121        if event.enabled {
2122            sql.push_str("ENABLE\n");
2123        } else {
2124            sql.push_str("DISABLE\n");
2125        }
2126
2127        // Comment
2128        if let Some(ref comment) = event.comment {
2129            sql.push_str(&format!("COMMENT '{}'\n", comment.replace('\'', "''")));
2130        }
2131
2132        // Body
2133        sql.push_str(&format!("DO\n{};", event.body));
2134
2135        sql
2136    }
2137
2138    /// Generate SQL Agent job creation script.
2139    pub fn create_sql_agent_job(&self, job: &SqlAgentJob) -> String {
2140        let mut sql = String::new();
2141
2142        // Add job
2143        sql.push_str("-- Create SQL Agent Job\n");
2144        sql.push_str("EXEC msdb.dbo.sp_add_job\n");
2145        sql.push_str(&format!("    @job_name = N'{}',\n", job.name));
2146
2147        if let Some(ref desc) = job.description {
2148            sql.push_str(&format!("    @description = N'{}',\n", desc));
2149        }
2150
2151        sql.push_str(&format!(
2152            "    @enabled = {};\n\n",
2153            if job.enabled { 1 } else { 0 }
2154        ));
2155
2156        // Add steps
2157        for (i, step) in job.steps.iter().enumerate() {
2158            sql.push_str(&format!("-- Step {}: {}\n", i + 1, step.name));
2159            sql.push_str("EXEC msdb.dbo.sp_add_jobstep\n");
2160            sql.push_str(&format!("    @job_name = N'{}',\n", job.name));
2161            sql.push_str(&format!("    @step_name = N'{}',\n", step.name));
2162            sql.push_str(&format!("    @subsystem = N'{}',\n", step.step_type.subsystem()));
2163            sql.push_str(&format!(
2164                "    @command = N'{}',\n",
2165                step.command.replace('\'', "''")
2166            ));
2167
2168            if let Some(ref db) = step.database {
2169                sql.push_str(&format!("    @database_name = N'{}',\n", db));
2170            }
2171
2172            sql.push_str(&format!(
2173                "    @on_success_action = {},\n",
2174                step.on_success.action_id()
2175            ));
2176            sql.push_str(&format!(
2177                "    @on_fail_action = {},\n",
2178                step.on_failure.action_id()
2179            ));
2180            sql.push_str(&format!("    @retry_attempts = {},\n", step.retry_attempts));
2181            sql.push_str(&format!("    @retry_interval = {};\n\n", step.retry_interval));
2182        }
2183
2184        // Add schedules
2185        for schedule in &job.schedules {
2186            sql.push_str(&format!("-- Schedule: {}\n", schedule.name));
2187            sql.push_str("EXEC msdb.dbo.sp_add_schedule\n");
2188            sql.push_str(&format!("    @schedule_name = N'{}',\n", schedule.name));
2189            sql.push_str(&format!(
2190                "    @enabled = {},\n",
2191                if schedule.enabled { 1 } else { 0 }
2192            ));
2193            sql.push_str(&format!(
2194                "    @freq_type = {},\n",
2195                schedule.frequency.freq_type()
2196            ));
2197            sql.push_str(&format!(
2198                "    @freq_interval = {};\n",
2199                schedule.frequency.freq_interval()
2200            ));
2201
2202            // Attach schedule to job
2203            sql.push_str("\nEXEC msdb.dbo.sp_attach_schedule\n");
2204            sql.push_str(&format!("    @job_name = N'{}',\n", job.name));
2205            sql.push_str(&format!("    @schedule_name = N'{}';\n\n", schedule.name));
2206        }
2207
2208        // Add job server
2209        sql.push_str("EXEC msdb.dbo.sp_add_jobserver\n");
2210        sql.push_str(&format!("    @job_name = N'{}',\n", job.name));
2211        sql.push_str("    @server_name = N'(LOCAL)';\n");
2212
2213        sql
2214    }
2215
2216    /// Generate DROP SQL Agent job script.
2217    pub fn drop_sql_agent_job(&self, job_name: &str) -> String {
2218        format!(
2219            "IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'{}')\n\
2220             BEGIN\n\
2221                 EXEC msdb.dbo.sp_delete_job @job_name = N'{}';\n\
2222             END;",
2223            job_name, job_name
2224        )
2225    }
2226}
2227
2228// ============================================================================
2229// Event Diff
2230// ============================================================================
2231
2232/// Differences in scheduled events.
2233#[derive(Debug, Clone, Default)]
2234pub struct EventDiff {
2235    /// Events to create.
2236    pub create: Vec<ScheduledEvent>,
2237    /// Events to drop.
2238    pub drop: Vec<String>,
2239    /// Events to alter.
2240    pub alter: Vec<EventAlterDiff>,
2241    /// SQL Agent jobs to create.
2242    pub create_jobs: Vec<SqlAgentJob>,
2243    /// SQL Agent jobs to drop.
2244    pub drop_jobs: Vec<String>,
2245}
2246
2247/// Event alter diff.
2248#[derive(Debug, Clone)]
2249pub struct EventAlterDiff {
2250    /// Old event.
2251    pub old: ScheduledEvent,
2252    /// New event.
2253    pub new: ScheduledEvent,
2254}
2255
2256impl EventDiff {
2257    /// Check if empty.
2258    pub fn is_empty(&self) -> bool {
2259        self.create.is_empty()
2260            && self.drop.is_empty()
2261            && self.alter.is_empty()
2262            && self.create_jobs.is_empty()
2263            && self.drop_jobs.is_empty()
2264    }
2265}
2266
2267#[cfg(test)]
2268mod tests {
2269    use super::*;
2270
2271    #[test]
2272    fn test_procedure_definition() {
2273        let proc = ProcedureDefinition::function("calculate_tax")
2274            .schema("public")
2275            .param("amount", "DECIMAL(10,2)")
2276            .param("rate", "DECIMAL(5,4)")
2277            .returns("DECIMAL(10,2)")
2278            .language(ProcedureLanguage::PlPgSql)
2279            .immutable()
2280            .body("RETURN amount * rate;");
2281
2282        assert_eq!(proc.name, "calculate_tax");
2283        assert_eq!(proc.qualified_name(), "public.calculate_tax");
2284        assert_eq!(proc.parameters.len(), 2);
2285        assert!(proc.is_function);
2286    }
2287
2288    #[test]
2289    fn test_trigger_definition() {
2290        let trigger = TriggerDefinition::new("audit_users", "users")
2291            .after()
2292            .on(vec![TriggerEvent::Insert, TriggerEvent::Update])
2293            .for_each_row()
2294            .execute("audit_trigger_fn");
2295
2296        assert_eq!(trigger.name, "audit_users");
2297        assert_eq!(trigger.table, "users");
2298        assert_eq!(trigger.timing, TriggerTiming::After);
2299        assert_eq!(trigger.events.len(), 2);
2300    }
2301
2302    #[test]
2303    fn test_procedure_diff() {
2304        let old = vec![
2305            ProcedureDefinition::function("fn1").body("v1"),
2306            ProcedureDefinition::function("fn2").body("v2"),
2307        ];
2308        let new = vec![
2309            ProcedureDefinition::function("fn1").body("v1_updated"),
2310            ProcedureDefinition::function("fn3").body("v3"),
2311        ];
2312
2313        let diff = ProcedureDiffer::diff(&old, &new);
2314
2315        assert_eq!(diff.create.len(), 1); // fn3
2316        assert_eq!(diff.drop.len(), 1); // fn2
2317        assert_eq!(diff.alter.len(), 1); // fn1
2318    }
2319
2320    #[test]
2321    fn test_postgres_sql_generation() {
2322        let generator = ProcedureSqlGenerator::new(DatabaseType::PostgreSQL);
2323
2324        let proc = ProcedureDefinition::function("greet")
2325            .param("name", "TEXT")
2326            .returns("TEXT")
2327            .language(ProcedureLanguage::Sql)
2328            .immutable()
2329            .body("SELECT 'Hello, ' || name || '!';");
2330
2331        let sql = generator.create_procedure(&proc);
2332        assert!(sql.contains("CREATE OR REPLACE"));
2333        assert!(sql.contains("FUNCTION"));
2334        assert!(sql.contains("RETURNS TEXT"));
2335        assert!(sql.contains("IMMUTABLE"));
2336    }
2337
2338    #[test]
2339    fn test_trigger_sql_generation() {
2340        let generator = ProcedureSqlGenerator::new(DatabaseType::PostgreSQL);
2341
2342        let trigger = TriggerDefinition::new("update_timestamp", "users")
2343            .before()
2344            .on(vec![TriggerEvent::Update])
2345            .for_each_row()
2346            .execute("set_updated_at");
2347
2348        let sql = generator.create_trigger(&trigger);
2349        assert!(sql.contains("CREATE OR REPLACE TRIGGER"));
2350        assert!(sql.contains("BEFORE UPDATE"));
2351        assert!(sql.contains("FOR EACH ROW"));
2352    }
2353
2354    #[test]
2355    fn test_procedure_store() {
2356        let mut store = ProcedureStore::new();
2357
2358        store.add_procedure(ProcedureDefinition::function("fn1").body("test"));
2359        store.add_trigger(TriggerDefinition::new("tr1", "table1"));
2360
2361        assert_eq!(store.procedures.len(), 1);
2362        assert_eq!(store.triggers.len(), 1);
2363    }
2364
2365    #[test]
2366    fn test_scheduled_event() {
2367        let event = ScheduledEvent::new("cleanup_old_data")
2368            .every(EventInterval::days(1))
2369            .body("DELETE FROM logs WHERE created_at < NOW() - INTERVAL 30 DAY")
2370            .preserve()
2371            .comment("Daily cleanup of old log entries");
2372
2373        assert_eq!(event.name, "cleanup_old_data");
2374        assert!(matches!(event.schedule, EventSchedule::Every(_)));
2375        assert_eq!(event.on_completion, OnCompletion::Preserve);
2376    }
2377
2378    #[test]
2379    fn test_mysql_event_sql() {
2380        let generator = ProcedureSqlGenerator::new(DatabaseType::MySQL);
2381
2382        let event = ScheduledEvent::new("hourly_stats")
2383            .every(EventInterval::hours(1))
2384            .body("CALL update_statistics()");
2385
2386        let sql = generator.create_event(&event);
2387        assert!(sql.contains("CREATE EVENT IF NOT EXISTS"));
2388        assert!(sql.contains("ON SCHEDULE EVERY 1 HOUR"));
2389    }
2390
2391    #[test]
2392    fn test_sql_agent_job() {
2393        let job = SqlAgentJob::new("nightly_backup")
2394            .description("Nightly database backup")
2395            .tsql_step("Backup", "BACKUP DATABASE mydb TO DISK = 'C:\\backups\\mydb.bak'")
2396            .schedule(
2397                JobSchedule::new("daily_2am")
2398                    .daily(1)
2399                    .at("02:00:00")
2400            );
2401
2402        assert_eq!(job.name, "nightly_backup");
2403        assert_eq!(job.steps.len(), 1);
2404        assert_eq!(job.schedules.len(), 1);
2405    }
2406
2407    #[test]
2408    fn test_sql_agent_job_sql() {
2409        let generator = ProcedureSqlGenerator::new(DatabaseType::MSSQL);
2410
2411        let job = SqlAgentJob::new("test_job")
2412            .tsql_step("Step1", "SELECT 1");
2413
2414        let sql = generator.create_sql_agent_job(&job);
2415        assert!(sql.contains("sp_add_job"));
2416        assert!(sql.contains("sp_add_jobstep"));
2417    }
2418
2419    #[test]
2420    fn test_atlas_trigger() {
2421        let trigger = AtlasTrigger::new("on_user_create")
2422            .database("mydb", "users", vec![AtlasOperation::Insert])
2423            .function("handleUserCreate")
2424            .full_document();
2425
2426        assert_eq!(trigger.name, "on_user_create");
2427        assert!(trigger.full_document);
2428        assert!(matches!(trigger.trigger_type, AtlasTriggerType::Database { .. }));
2429    }
2430}
2431
prax_migrate/procedure.rs

prax_migrate/
procedure.rs
