qail_core/migrate/
schema.rs

1//! QAIL Schema Format (Native AST)
2//!
3//! Replaces JSON with a human-readable, intent-aware schema format.
4//!
5//! ```qail
6//! table users {
7//!   id serial primary_key
8//!   name text not_null
9//!   email text nullable unique
10//! }
11//!
12//! unique index idx_users_email on users (email)
13//!
14//! rename users.username -> users.name
15//! ```
16
17use super::policy::{PolicyPermissiveness, PolicyTarget, RlsPolicy};
18use super::types::ColumnType;
19use std::collections::HashMap;
20
21/// A complete database schema.
22#[derive(Debug, Clone, Default)]
23pub struct Schema {
24    /// Declared tables.
25    pub tables: HashMap<String, Table>,
26    /// Declared indexes.
27    pub indexes: Vec<Index>,
28    /// Migration hints (renames, transforms, drops).
29    pub migrations: Vec<MigrationHint>,
30    /// PostgreSQL extensions (e.g. uuid-ossp, pgcrypto, PostGIS)
31    pub extensions: Vec<Extension>,
32    /// Schema-level comments on tables/columns
33    pub comments: Vec<Comment>,
34    /// Standalone sequences
35    pub sequences: Vec<Sequence>,
36    /// Standalone ENUM types
37    pub enums: Vec<EnumType>,
38    /// SQL views (CREATE VIEW / CREATE MATERIALIZED VIEW).
39    pub views: Vec<ViewDef>,
40    /// PL/pgSQL functions
41    pub functions: Vec<SchemaFunctionDef>,
42    /// Database triggers (CREATE TRIGGER).
43    pub triggers: Vec<SchemaTriggerDef>,
44    /// GRANT/REVOKE permissions
45    pub grants: Vec<Grant>,
46    /// RLS policies
47    pub policies: Vec<RlsPolicy>,
48    /// Infrastructure resources (buckets, queues, topics)
49    pub resources: Vec<ResourceDef>,
50}
51
52// ============================================================================
53// Infrastructure Resources
54// ============================================================================
55
56/// Kind of infrastructure resource declared in schema.qail.
57#[derive(Debug, Clone, PartialEq)]
58pub enum ResourceKind {
59    /// Object storage bucket.
60    Bucket,
61    /// Message queue.
62    Queue,
63    /// Pub/sub topic.
64    Topic,
65}
66
67impl std::fmt::Display for ResourceKind {
68    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
69        match self {
70            Self::Bucket => write!(f, "bucket"),
71            Self::Queue => write!(f, "queue"),
72            Self::Topic => write!(f, "topic"),
73        }
74    }
75}
76
77/// An infrastructure resource declaration.
78///
79/// ```qail
80/// bucket avatars {
81///     provider s3
82///     region "ap-southeast-1"
83/// }
84/// ```
85#[derive(Debug, Clone)]
86pub struct ResourceDef {
87    /// Resource name (e.g. `"avatars"`).
88    pub name: String,
89    /// Kind of resource.
90    pub kind: ResourceKind,
91    /// Cloud provider (e.g. `"s3"`, `"gcs"`).
92    pub provider: Option<String>,
93    /// Arbitrary key-value properties.
94    pub properties: HashMap<String, String>,
95}
96
97/// A table definition in the schema.
98#[derive(Debug, Clone)]
99pub struct Table {
100    /// Table name.
101    pub name: String,
102    /// Column definitions.
103    pub columns: Vec<Column>,
104    /// Table-level multi-column foreign keys
105    pub multi_column_fks: Vec<MultiColumnForeignKey>,
106    /// ENABLE ROW LEVEL SECURITY
107    pub enable_rls: bool,
108    /// FORCE ROW LEVEL SECURITY
109    pub force_rls: bool,
110}
111
112/// A column definition with compile-time type safety.
113#[derive(Debug, Clone)]
114pub struct Column {
115    /// Column name.
116    pub name: String,
117    /// Compile-time validated data type.
118    pub data_type: ColumnType,
119    /// Whether the column accepts NULL.
120    pub nullable: bool,
121    /// Whether this column is a primary key.
122    pub primary_key: bool,
123    /// Whether this column has a UNIQUE constraint.
124    pub unique: bool,
125    /// Default value expression.
126    pub default: Option<String>,
127    /// Foreign key reference.
128    pub foreign_key: Option<ForeignKey>,
129    /// CHECK constraint (Phase 1)
130    pub check: Option<CheckConstraint>,
131    /// GENERATED column (Phase 3)
132    pub generated: Option<Generated>,
133}
134
135/// Foreign key reference definition.
136#[derive(Debug, Clone)]
137pub struct ForeignKey {
138    /// Referenced table name.
139    pub table: String,
140    /// Referenced column name.
141    pub column: String,
142    /// Action taken when the referenced row is deleted.
143    pub on_delete: FkAction,
144    /// Action taken when the referenced row is updated.
145    pub on_update: FkAction,
146    /// DEFERRABLE clause (Phase 2)
147    pub deferrable: Deferrable,
148}
149
150/// Foreign key action on DELETE/UPDATE.
151#[derive(Debug, Clone, Default, PartialEq)]
152pub enum FkAction {
153    #[default]
154    /// No action on referenced row change.
155    NoAction,
156    /// Cascade the delete/update to referencing rows.
157    Cascade,
158    /// Set referencing column to NULL.
159    SetNull,
160    /// Set referencing column to its DEFAULT.
161    SetDefault,
162    /// Prevent the action (raises error).
163    Restrict,
164}
165
166/// An index definition.
167#[derive(Debug, Clone)]
168pub struct Index {
169    /// Index name.
170    pub name: String,
171    /// Table the index belongs to.
172    pub table: String,
173    /// Columns covered by the index.
174    pub columns: Vec<String>,
175    /// Whether the index enforces uniqueness.
176    pub unique: bool,
177    /// Index method (Phase 4): btree, hash, gin, gist, brin
178    pub method: IndexMethod,
179    /// Partial index WHERE clause
180    pub where_clause: Option<CheckExpr>,
181    /// INCLUDE columns (covering index)
182    pub include: Vec<String>,
183    /// CREATE CONCURRENTLY
184    pub concurrently: bool,
185    /// Expression columns (e.g. `(lower(email))`) — if set, these replace `columns`
186    pub expressions: Vec<String>,
187}
188
189/// Hints for the migration diff engine to improve migration quality.
190#[derive(Debug, Clone)]
191pub enum MigrationHint {
192    /// Rename a column (not delete + add)
193    Rename {
194        /// Original column name.
195        from: String,
196        /// New column name.
197        to: String,
198    },
199    /// Transform data with expression
200    Transform {
201        /// SQL expression for data transformation.
202        expression: String,
203        /// Target column name.
204        target: String,
205    },
206    /// Drop with confirmation
207    Drop {
208        /// Target name to drop.
209        target: String,
210        /// Whether the drop has been confirmed.
211        confirmed: bool,
212    },
213}
214
215// ============================================================================
216// Phase 1: CHECK Constraints (AST-native)
217// ============================================================================
218
219/// CHECK constraint expression (AST-native, no raw SQL)
220#[derive(Debug, Clone)]
221pub enum CheckExpr {
222    /// column > value
223    GreaterThan {
224        /// Column name.
225        column: String,
226        /// Comparison value.
227        value: i64,
228    },
229    /// column >= value
230    GreaterOrEqual {
231        /// Column name.
232        column: String,
233        /// Comparison value.
234        value: i64,
235    },
236    /// column < value
237    LessThan {
238        /// Column name.
239        column: String,
240        /// Comparison value.
241        value: i64,
242    },
243    /// column <= value
244    LessOrEqual {
245        /// Column name.
246        column: String,
247        /// Comparison value.
248        value: i64,
249    },
250    /// value BETWEEN low AND high
251    Between {
252        /// Column name.
253        column: String,
254        /// Lower bound.
255        low: i64,
256        /// Upper bound.
257        high: i64,
258    },
259    /// column IN (values)
260    In {
261        /// Column name.
262        column: String,
263        /// Allowed values.
264        values: Vec<String>,
265    },
266    /// column ~ pattern (regex)
267    Regex {
268        /// Column name.
269        column: String,
270        /// Regex pattern.
271        pattern: String,
272    },
273    /// LENGTH(column) <= max
274    MaxLength {
275        /// Column name.
276        column: String,
277        /// Maximum allowed length.
278        max: usize,
279    },
280    /// LENGTH(column) >= min
281    MinLength {
282        /// Column name.
283        column: String,
284        /// Minimum required length.
285        min: usize,
286    },
287    /// column IS NOT NULL
288    NotNull {
289        /// Column name.
290        column: String,
291    },
292    /// Logical AND of two expressions.
293    And(Box<CheckExpr>, Box<CheckExpr>),
294    /// Logical OR of two expressions.
295    Or(Box<CheckExpr>, Box<CheckExpr>),
296    /// Logical NOT of an expression.
297    Not(Box<CheckExpr>),
298    /// SQL boolean expression (preserved as-is).
299    Sql(String),
300}
301
302/// CHECK constraint with optional name
303#[derive(Debug, Clone)]
304pub struct CheckConstraint {
305    /// The constraint expression.
306    pub expr: CheckExpr,
307    /// Optional constraint name.
308    pub name: Option<String>,
309}
310
311// ============================================================================
312// Phase 2: DEFERRABLE Constraints
313// ============================================================================
314
315/// Constraint deferral mode
316#[derive(Debug, Clone, Default, PartialEq)]
317pub enum Deferrable {
318    #[default]
319    /// Not deferrable (default).
320    NotDeferrable,
321    /// DEFERRABLE (initially immediate).
322    Deferrable,
323    /// DEFERRABLE INITIALLY DEFERRED.
324    InitiallyDeferred,
325    /// DEFERRABLE INITIALLY IMMEDIATE.
326    InitiallyImmediate,
327}
328
329// ============================================================================
330// Phase 3: GENERATED Columns
331// ============================================================================
332
333/// GENERATED column type
334#[derive(Debug, Clone)]
335pub enum Generated {
336    /// GENERATED ALWAYS AS (expr) STORED
337    AlwaysStored(String),
338    /// GENERATED ALWAYS AS IDENTITY
339    AlwaysIdentity,
340    /// GENERATED BY DEFAULT AS IDENTITY
341    ByDefaultIdentity,
342}
343
344// ============================================================================
345// Phase 4: Advanced Index Types
346// ============================================================================
347
348/// Index method (USING clause)
349#[derive(Debug, Clone, Default, PartialEq)]
350pub enum IndexMethod {
351    #[default]
352    /// B-tree (default for most columns).
353    BTree,
354    /// Hash (equality-only lookups).
355    Hash,
356    /// GIN (full-text search, JSONB).
357    Gin,
358    /// GiST (geometric, range types).
359    Gist,
360    /// BRIN (large, naturally-ordered tables).
361    Brin,
362    /// SP-GiST (space-partitioned).
363    SpGist,
364}
365
366fn index_method_str(method: &IndexMethod) -> &'static str {
367    match method {
368        IndexMethod::BTree => "btree",
369        IndexMethod::Hash => "hash",
370        IndexMethod::Gin => "gin",
371        IndexMethod::Gist => "gist",
372        IndexMethod::Brin => "brin",
373        IndexMethod::SpGist => "spgist",
374    }
375}
376
377// ============================================================================
378// Phase 7: Extensions, Comments, Sequences
379// ============================================================================
380
381/// PostgreSQL extension (e.g. `CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`)
382#[derive(Debug, Clone, PartialEq)]
383pub struct Extension {
384    /// Extension name (e.g. `"uuid-ossp"`).
385    pub name: String,
386    /// Target schema.
387    pub schema: Option<String>,
388    /// Pinned version.
389    pub version: Option<String>,
390}
391
392impl Extension {
393    /// Create a new extension declaration.
394    pub fn new(name: impl Into<String>) -> Self {
395        Self {
396            name: name.into(),
397            schema: None,
398            version: None,
399        }
400    }
401
402    /// Set the target schema.
403    pub fn schema(mut self, schema: impl Into<String>) -> Self {
404        self.schema = Some(schema.into());
405        self
406    }
407
408    /// Pin to a specific version.
409    pub fn version(mut self, version: impl Into<String>) -> Self {
410        self.version = Some(version.into());
411        self
412    }
413}
414
415/// COMMENT ON TABLE/COLUMN
416#[derive(Debug, Clone, PartialEq)]
417pub struct Comment {
418    /// What the comment is attached to.
419    pub target: CommentTarget,
420    /// Comment text.
421    pub text: String,
422}
423
424/// Target of a COMMENT ON statement.
425#[derive(Debug, Clone, PartialEq)]
426pub enum CommentTarget {
427    /// COMMENT ON TABLE.
428    Table(String),
429    /// COMMENT ON COLUMN.
430    Column {
431        /// Table name.
432        table: String,
433        /// Column name.
434        column: String,
435    },
436    /// COMMENT ON arbitrary object target (e.g. FUNCTION/POLICY/TYPE/CONSTRAINT).
437    Raw(String),
438}
439
440impl Comment {
441    /// Create a comment on a table.
442    pub fn on_table(table: impl Into<String>, text: impl Into<String>) -> Self {
443        Self {
444            target: CommentTarget::Table(table.into()),
445            text: text.into(),
446        }
447    }
448
449    /// Create a comment on a column.
450    pub fn on_column(
451        table: impl Into<String>,
452        column: impl Into<String>,
453        text: impl Into<String>,
454    ) -> Self {
455        Self {
456            target: CommentTarget::Column {
457                table: table.into(),
458                column: column.into(),
459            },
460            text: text.into(),
461        }
462    }
463
464    /// Create a comment on an arbitrary object target.
465    pub fn on_raw(target: impl Into<String>, text: impl Into<String>) -> Self {
466        Self {
467            target: CommentTarget::Raw(target.into()),
468            text: text.into(),
469        }
470    }
471}
472
473/// Standalone sequence (CREATE SEQUENCE)
474#[derive(Debug, Clone, PartialEq)]
475pub struct Sequence {
476    /// Sequence name.
477    pub name: String,
478    /// Data type (e.g. `"bigint"`).
479    pub data_type: Option<String>,
480    /// START WITH value.
481    pub start: Option<i64>,
482    /// INCREMENT BY value.
483    pub increment: Option<i64>,
484    /// Minimum value for the sequence (MINVALUE clause).
485    pub min_value: Option<i64>,
486    /// Maximum value for the sequence (MAXVALUE clause).
487    pub max_value: Option<i64>,
488    /// CACHE size.
489    pub cache: Option<i64>,
490    /// Whether the sequence wraps around.
491    pub cycle: bool,
492    /// OWNED BY column reference.
493    pub owned_by: Option<String>,
494}
495
496impl Sequence {
497    /// Create a new sequence.
498    pub fn new(name: impl Into<String>) -> Self {
499        Self {
500            name: name.into(),
501            data_type: None,
502            start: None,
503            increment: None,
504            min_value: None,
505            max_value: None,
506            cache: None,
507            cycle: false,
508            owned_by: None,
509        }
510    }
511
512    /// Set the START WITH value.
513    pub fn start(mut self, v: i64) -> Self {
514        self.start = Some(v);
515        self
516    }
517
518    /// Set the INCREMENT BY value.
519    pub fn increment(mut self, v: i64) -> Self {
520        self.increment = Some(v);
521        self
522    }
523
524    /// Set the MINVALUE.
525    pub fn min_value(mut self, v: i64) -> Self {
526        self.min_value = Some(v);
527        self
528    }
529
530    /// Set the MAXVALUE.
531    pub fn max_value(mut self, v: i64) -> Self {
532        self.max_value = Some(v);
533        self
534    }
535
536    /// Set the CACHE size.
537    pub fn cache(mut self, v: i64) -> Self {
538        self.cache = Some(v);
539        self
540    }
541
542    /// Enable CYCLE (wrap around at limit).
543    pub fn cycle(mut self) -> Self {
544        self.cycle = true;
545        self
546    }
547
548    /// Set the OWNED BY column reference.
549    pub fn owned_by(mut self, col: impl Into<String>) -> Self {
550        self.owned_by = Some(col.into());
551        self
552    }
553}
554
555// ============================================================================
556// Phase 8: Standalone Enums, Multi-Column FK
557// ============================================================================
558
559/// Standalone ENUM type (CREATE TYPE ... AS ENUM)
560#[derive(Debug, Clone, PartialEq)]
561pub struct EnumType {
562    /// Type name.
563    pub name: String,
564    /// Allowed values.
565    pub values: Vec<String>,
566}
567
568impl EnumType {
569    /// Create a new enum type.
570    pub fn new(name: impl Into<String>, values: Vec<String>) -> Self {
571        Self {
572            name: name.into(),
573            values,
574        }
575    }
576
577    /// Add a new value (for ALTER TYPE ADD VALUE)
578    pub fn add_value(mut self, value: impl Into<String>) -> Self {
579        self.values.push(value.into());
580        self
581    }
582}
583
584/// Table-level multi-column foreign key
585#[derive(Debug, Clone, PartialEq)]
586pub struct MultiColumnForeignKey {
587    /// Source columns.
588    pub columns: Vec<String>,
589    /// Referenced table.
590    pub ref_table: String,
591    /// Referenced columns.
592    pub ref_columns: Vec<String>,
593    /// ON DELETE action.
594    pub on_delete: FkAction,
595    /// ON UPDATE action.
596    pub on_update: FkAction,
597    /// Deferral mode.
598    pub deferrable: Deferrable,
599    /// Optional constraint name.
600    pub name: Option<String>,
601}
602
603impl MultiColumnForeignKey {
604    /// Create a new multi-column foreign key.
605    pub fn new(
606        columns: Vec<String>,
607        ref_table: impl Into<String>,
608        ref_columns: Vec<String>,
609    ) -> Self {
610        Self {
611            columns,
612            ref_table: ref_table.into(),
613            ref_columns,
614            on_delete: FkAction::default(),
615            on_update: FkAction::default(),
616            deferrable: Deferrable::default(),
617            name: None,
618        }
619    }
620
621    /// Set the ON DELETE action.
622    pub fn on_delete(mut self, action: FkAction) -> Self {
623        self.on_delete = action;
624        self
625    }
626
627    /// Set the ON UPDATE action.
628    pub fn on_update(mut self, action: FkAction) -> Self {
629        self.on_update = action;
630        self
631    }
632
633    /// Set an explicit constraint name.
634    pub fn named(mut self, name: impl Into<String>) -> Self {
635        self.name = Some(name.into());
636        self
637    }
638}
639
640// ============================================================================
641// Phase 9: Views, Functions, Triggers, Grants
642// ============================================================================
643
644/// A SQL view definition.
645#[derive(Debug, Clone, PartialEq)]
646pub struct ViewDef {
647    /// View name.
648    pub name: String,
649    /// Underlying SQL query.
650    pub query: String,
651    /// Whether this is a MATERIALIZED VIEW.
652    pub materialized: bool,
653}
654
655impl ViewDef {
656    /// Create a standard (non-materialized) view.
657    pub fn new(name: impl Into<String>, query: impl Into<String>) -> Self {
658        Self {
659            name: name.into(),
660            query: query.into(),
661            materialized: false,
662        }
663    }
664
665    /// Mark as MATERIALIZED VIEW.
666    pub fn materialized(mut self) -> Self {
667        self.materialized = true;
668        self
669    }
670}
671
672/// A PL/pgSQL function definition for the schema model.
673#[derive(Debug, Clone, PartialEq)]
674pub struct SchemaFunctionDef {
675    /// Function name.
676    pub name: String,
677    /// Function arguments (e.g. `"p_id uuid"`).
678    pub args: Vec<String>,
679    /// Return type.
680    pub returns: String,
681    /// Function body.
682    pub body: String,
683    /// Language (default `"plpgsql"`).
684    pub language: String,
685    /// Volatility category (VOLATILE, STABLE, IMMUTABLE).
686    pub volatility: Option<String>,
687}
688
689impl SchemaFunctionDef {
690    /// Create a new function definition.
691    pub fn new(
692        name: impl Into<String>,
693        returns: impl Into<String>,
694        body: impl Into<String>,
695    ) -> Self {
696        Self {
697            name: name.into(),
698            args: Vec::new(),
699            returns: returns.into(),
700            body: body.into(),
701            language: "plpgsql".to_string(),
702            volatility: None,
703        }
704    }
705
706    /// Set the function language.
707    pub fn language(mut self, lang: impl Into<String>) -> Self {
708        self.language = lang.into();
709        self
710    }
711
712    /// Add a function argument.
713    pub fn arg(mut self, arg: impl Into<String>) -> Self {
714        self.args.push(arg.into());
715        self
716    }
717
718    /// Set the volatility category.
719    pub fn volatility(mut self, v: impl Into<String>) -> Self {
720        self.volatility = Some(v.into());
721        self
722    }
723}
724
725/// A trigger definition for the schema model.
726#[derive(Debug, Clone, PartialEq)]
727pub struct SchemaTriggerDef {
728    /// Trigger name.
729    pub name: String,
730    /// Target table.
731    pub table: String,
732    /// Timing (BEFORE, AFTER, INSTEAD OF).
733    pub timing: String,
734    /// Events that fire the trigger (INSERT, UPDATE, DELETE).
735    pub events: Vec<String>,
736    /// Optional column list for `UPDATE OF` triggers.
737    pub update_columns: Vec<String>,
738    /// Whether the trigger fires FOR EACH ROW (vs. FOR EACH STATEMENT).
739    pub for_each_row: bool,
740    /// Function to execute.
741    pub execute_function: String,
742    /// Optional WHEN condition.
743    pub condition: Option<String>,
744}
745
746impl SchemaTriggerDef {
747    /// Create a new trigger definition.
748    pub fn new(
749        name: impl Into<String>,
750        table: impl Into<String>,
751        execute_function: impl Into<String>,
752    ) -> Self {
753        Self {
754            name: name.into(),
755            table: table.into(),
756            timing: "BEFORE".to_string(),
757            events: vec!["INSERT".to_string()],
758            update_columns: Vec::new(),
759            for_each_row: true,
760            execute_function: execute_function.into(),
761            condition: None,
762        }
763    }
764
765    /// Set the trigger timing.
766    pub fn timing(mut self, t: impl Into<String>) -> Self {
767        self.timing = t.into();
768        self
769    }
770
771    /// Set the trigger events.
772    pub fn events(mut self, evts: Vec<String>) -> Self {
773        self.events = evts;
774        self
775    }
776
777    /// Fire FOR EACH STATEMENT instead of FOR EACH ROW.
778    pub fn for_each_statement(mut self) -> Self {
779        self.for_each_row = false;
780        self
781    }
782
783    /// Set an optional WHEN condition.
784    pub fn condition(mut self, cond: impl Into<String>) -> Self {
785        self.condition = Some(cond.into());
786        self
787    }
788}
789
790/// GRANT or REVOKE permission.
791#[derive(Debug, Clone, PartialEq)]
792pub struct Grant {
793    /// GRANT or REVOKE.
794    pub action: GrantAction,
795    /// Privileges being granted/revoked.
796    pub privileges: Vec<Privilege>,
797    /// Target object (table, schema, sequence).
798    pub on_object: String,
799    /// Role receiving (or losing) the privileges.
800    pub to_role: String,
801}
802
803/// Whether a permission statement is a GRANT or REVOKE.
804#[derive(Debug, Clone, PartialEq, Default)]
805pub enum GrantAction {
806    #[default]
807    /// Grant privileges.
808    Grant,
809    /// Revoke privileges.
810    Revoke,
811}
812
813/// SQL privilege type.
814#[derive(Debug, Clone, PartialEq)]
815pub enum Privilege {
816    /// ALL PRIVILEGES.
817    All,
818    /// SELECT.
819    Select,
820    /// INSERT.
821    Insert,
822    /// UPDATE.
823    Update,
824    /// DELETE.
825    Delete,
826    /// USAGE (on schemas, sequences).
827    Usage,
828    /// EXECUTE (on functions).
829    Execute,
830}
831
832impl std::fmt::Display for Privilege {
833    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
834        match self {
835            Privilege::All => write!(f, "ALL"),
836            Privilege::Select => write!(f, "SELECT"),
837            Privilege::Insert => write!(f, "INSERT"),
838            Privilege::Update => write!(f, "UPDATE"),
839            Privilege::Delete => write!(f, "DELETE"),
840            Privilege::Usage => write!(f, "USAGE"),
841            Privilege::Execute => write!(f, "EXECUTE"),
842        }
843    }
844}
845
846impl Grant {
847    /// Create a GRANT statement.
848    pub fn new(
849        privileges: Vec<Privilege>,
850        on_object: impl Into<String>,
851        to_role: impl Into<String>,
852    ) -> Self {
853        Self {
854            action: GrantAction::Grant,
855            privileges,
856            on_object: on_object.into(),
857            to_role: to_role.into(),
858        }
859    }
860
861    /// Create a REVOKE statement.
862    pub fn revoke(
863        privileges: Vec<Privilege>,
864        on_object: impl Into<String>,
865        from_role: impl Into<String>,
866    ) -> Self {
867        Self {
868            action: GrantAction::Revoke,
869            privileges,
870            on_object: on_object.into(),
871            to_role: from_role.into(),
872        }
873    }
874}
875
876impl Schema {
877    /// Create an empty schema.
878    pub fn new() -> Self {
879        Self::default()
880    }
881
882    /// Add a table definition.
883    pub fn add_table(&mut self, table: Table) {
884        self.tables.insert(table.name.clone(), table);
885    }
886
887    /// Add an index definition.
888    pub fn add_index(&mut self, index: Index) {
889        self.indexes.push(index);
890    }
891
892    /// Add a migration hint.
893    pub fn add_hint(&mut self, hint: MigrationHint) {
894        self.migrations.push(hint);
895    }
896
897    /// Add a PostgreSQL extension.
898    pub fn add_extension(&mut self, ext: Extension) {
899        self.extensions.push(ext);
900    }
901
902    /// Add a schema comment.
903    pub fn add_comment(&mut self, comment: Comment) {
904        self.comments.push(comment);
905    }
906
907    /// Add a standalone sequence.
908    pub fn add_sequence(&mut self, seq: Sequence) {
909        self.sequences.push(seq);
910    }
911
912    /// Add a standalone ENUM type.
913    pub fn add_enum(&mut self, enum_type: EnumType) {
914        self.enums.push(enum_type);
915    }
916
917    /// Add a view definition.
918    pub fn add_view(&mut self, view: ViewDef) {
919        self.views.push(view);
920    }
921
922    /// Add a function definition.
923    pub fn add_function(&mut self, func: SchemaFunctionDef) {
924        self.functions.push(func);
925    }
926
927    /// Add a trigger definition.
928    pub fn add_trigger(&mut self, trigger: SchemaTriggerDef) {
929        self.triggers.push(trigger);
930    }
931
932    /// Add a GRANT or REVOKE.
933    pub fn add_grant(&mut self, grant: Grant) {
934        self.grants.push(grant);
935    }
936
937    /// Add an infrastructure resource declaration.
938    pub fn add_resource(&mut self, resource: ResourceDef) {
939        self.resources.push(resource);
940    }
941
942    /// Add an RLS policy definition.
943    pub fn add_policy(&mut self, policy: RlsPolicy) {
944        self.policies.push(policy);
945    }
946
947    /// Validate all foreign key references in the schema.
948    pub fn validate(&self) -> Result<(), Vec<String>> {
949        let mut errors = Vec::new();
950
951        for table in self.tables.values() {
952            for col in &table.columns {
953                if let Some(ref fk) = col.foreign_key {
954                    if !self.tables.contains_key(&fk.table) {
955                        errors.push(format!(
956                            "FK error: {}.{} references non-existent table '{}'",
957                            table.name, col.name, fk.table
958                        ));
959                    } else {
960                        let ref_table = &self.tables[&fk.table];
961                        if !ref_table.columns.iter().any(|c| c.name == fk.column) {
962                            errors.push(format!(
963                                "FK error: {}.{} references non-existent column '{}.{}'",
964                                table.name, col.name, fk.table, fk.column
965                            ));
966                        }
967                    }
968                }
969            }
970        }
971
972        if errors.is_empty() {
973            Ok(())
974        } else {
975            Err(errors)
976        }
977    }
978}
979
980impl Table {
981    /// Create a new empty table.
982    pub fn new(name: impl Into<String>) -> Self {
983        Self {
984            name: name.into(),
985            columns: Vec::new(),
986            multi_column_fks: Vec::new(),
987            enable_rls: false,
988            force_rls: false,
989        }
990    }
991
992    /// Add a column (builder pattern).
993    pub fn column(mut self, col: Column) -> Self {
994        self.columns.push(col);
995        self
996    }
997
998    /// Add a table-level multi-column foreign key
999    pub fn foreign_key(mut self, fk: MultiColumnForeignKey) -> Self {
1000        self.multi_column_fks.push(fk);
1001        self
1002    }
1003}
1004
1005impl Column {
1006    fn primary_key_type_error(&self) -> String {
1007        format!(
1008            "Column '{}' of type {} cannot be a primary key. \
1009             Valid PK types: scalar/indexable types \
1010             (UUID, TEXT, VARCHAR, INT, BIGINT, SERIAL, BIGSERIAL, BOOLEAN, FLOAT, DECIMAL, \
1011             TIMESTAMP, TIMESTAMPTZ, DATE, TIME, ENUM, INET, CIDR, MACADDR)",
1012            self.name,
1013            self.data_type.name()
1014        )
1015    }
1016
1017    fn unique_type_error(&self) -> String {
1018        format!(
1019            "Column '{}' of type {} cannot have UNIQUE constraint. \
1020             JSONB and BYTEA types do not support standard indexing.",
1021            self.name,
1022            self.data_type.name()
1023        )
1024    }
1025
1026    /// Create a new column with compile-time type validation.
1027    pub fn new(name: impl Into<String>, data_type: ColumnType) -> Self {
1028        Self {
1029            name: name.into(),
1030            data_type,
1031            nullable: true,
1032            primary_key: false,
1033            unique: false,
1034            default: None,
1035            foreign_key: None,
1036            check: None,
1037            generated: None,
1038        }
1039    }
1040
1041    /// Mark as NOT NULL.
1042    pub fn not_null(mut self) -> Self {
1043        self.nullable = false;
1044        self
1045    }
1046
1047    /// Set as primary key with compile-time validation.
1048    /// Validates that the column type can be a primary key.
1049    ///
1050    /// This method is fail-soft: invalid type combinations are allowed to
1051    /// continue without panicking so production callers cannot crash on
1052    /// dynamic schema input. Use [`Column::try_primary_key`] for strict mode.
1053    pub fn primary_key(mut self) -> Self {
1054        if !self.data_type.can_be_primary_key() {
1055            #[cfg(debug_assertions)]
1056            eprintln!("QAIL: {}", self.primary_key_type_error());
1057        }
1058        self.primary_key = true;
1059        self.nullable = false;
1060        self
1061    }
1062
1063    /// Strict variant of [`Column::primary_key`].
1064    ///
1065    /// Returns an error instead of panicking when type policy disallows PK.
1066    pub fn try_primary_key(mut self) -> Result<Self, String> {
1067        if !self.data_type.can_be_primary_key() {
1068            return Err(self.primary_key_type_error());
1069        }
1070        self.primary_key = true;
1071        self.nullable = false;
1072        Ok(self)
1073    }
1074
1075    /// Set as unique with compile-time validation.
1076    /// Validates that the column type supports indexing.
1077    ///
1078    /// This method is fail-soft: invalid type combinations are allowed to
1079    /// continue without panicking so production callers cannot crash on
1080    /// dynamic schema input. Use [`Column::try_unique`] for strict mode.
1081    pub fn unique(mut self) -> Self {
1082        if !self.data_type.supports_indexing() {
1083            #[cfg(debug_assertions)]
1084            eprintln!("QAIL: {}", self.unique_type_error());
1085        }
1086        self.unique = true;
1087        self
1088    }
1089
1090    /// Strict variant of [`Column::unique`].
1091    ///
1092    /// Returns an error instead of panicking when type policy disallows UNIQUE.
1093    pub fn try_unique(mut self) -> Result<Self, String> {
1094        if !self.data_type.supports_indexing() {
1095            return Err(self.unique_type_error());
1096        }
1097        self.unique = true;
1098        Ok(self)
1099    }
1100
1101    /// Set a DEFAULT value expression.
1102    pub fn default(mut self, val: impl Into<String>) -> Self {
1103        self.default = Some(val.into());
1104        self
1105    }
1106
1107    /// Add a foreign key reference to another table.
1108    /// # Example
1109    /// ```ignore
1110    /// Column::new("user_id", ColumnType::Uuid)
1111    ///     .references("users", "id")
1112    ///     .on_delete(FkAction::Cascade)
1113    /// ```
1114    pub fn references(mut self, table: &str, column: &str) -> Self {
1115        self.foreign_key = Some(ForeignKey {
1116            table: table.to_string(),
1117            column: column.to_string(),
1118            on_delete: FkAction::default(),
1119            on_update: FkAction::default(),
1120            deferrable: Deferrable::default(),
1121        });
1122        self
1123    }
1124
1125    /// Set the ON DELETE action for the foreign key.
1126    pub fn on_delete(mut self, action: FkAction) -> Self {
1127        if let Some(ref mut fk) = self.foreign_key {
1128            fk.on_delete = action;
1129        }
1130        self
1131    }
1132
1133    /// Set the ON UPDATE action for the foreign key.
1134    pub fn on_update(mut self, action: FkAction) -> Self {
1135        if let Some(ref mut fk) = self.foreign_key {
1136            fk.on_update = action;
1137        }
1138        self
1139    }
1140
1141    // ==================== Phase 1: CHECK ====================
1142
1143    /// Add a CHECK constraint (AST-native)
1144    pub fn check(mut self, expr: CheckExpr) -> Self {
1145        self.check = Some(CheckConstraint { expr, name: None });
1146        self
1147    }
1148
1149    /// Add a named CHECK constraint
1150    pub fn check_named(mut self, name: impl Into<String>, expr: CheckExpr) -> Self {
1151        self.check = Some(CheckConstraint {
1152            expr,
1153            name: Some(name.into()),
1154        });
1155        self
1156    }
1157
1158    // ==================== Phase 2: DEFERRABLE ====================
1159
1160    /// Make foreign key DEFERRABLE
1161    pub fn deferrable(mut self) -> Self {
1162        if let Some(ref mut fk) = self.foreign_key {
1163            fk.deferrable = Deferrable::Deferrable;
1164        }
1165        self
1166    }
1167
1168    /// Make foreign key DEFERRABLE INITIALLY DEFERRED
1169    pub fn initially_deferred(mut self) -> Self {
1170        if let Some(ref mut fk) = self.foreign_key {
1171            fk.deferrable = Deferrable::InitiallyDeferred;
1172        }
1173        self
1174    }
1175
1176    /// Make foreign key DEFERRABLE INITIALLY IMMEDIATE
1177    pub fn initially_immediate(mut self) -> Self {
1178        if let Some(ref mut fk) = self.foreign_key {
1179            fk.deferrable = Deferrable::InitiallyImmediate;
1180        }
1181        self
1182    }
1183
1184    // ==================== Phase 3: GENERATED ====================
1185
1186    /// GENERATED ALWAYS AS (expr) STORED
1187    pub fn generated_stored(mut self, expr: impl Into<String>) -> Self {
1188        self.generated = Some(Generated::AlwaysStored(expr.into()));
1189        self
1190    }
1191
1192    /// GENERATED ALWAYS AS IDENTITY
1193    pub fn generated_identity(mut self) -> Self {
1194        self.generated = Some(Generated::AlwaysIdentity);
1195        self
1196    }
1197
1198    /// GENERATED BY DEFAULT AS IDENTITY
1199    pub fn generated_by_default(mut self) -> Self {
1200        self.generated = Some(Generated::ByDefaultIdentity);
1201        self
1202    }
1203}
1204
1205impl Index {
1206    /// Create a new index on the given columns.
1207    pub fn new(name: impl Into<String>, table: impl Into<String>, columns: Vec<String>) -> Self {
1208        Self {
1209            name: name.into(),
1210            table: table.into(),
1211            columns,
1212            unique: false,
1213            method: IndexMethod::default(),
1214            where_clause: None,
1215            include: Vec::new(),
1216            concurrently: false,
1217            expressions: Vec::new(),
1218        }
1219    }
1220
1221    /// Create an expression index (e.g. `CREATE INDEX ON t ((lower(email)))`)
1222    pub fn expression(
1223        name: impl Into<String>,
1224        table: impl Into<String>,
1225        expressions: Vec<String>,
1226    ) -> Self {
1227        Self {
1228            name: name.into(),
1229            table: table.into(),
1230            columns: Vec::new(),
1231            unique: false,
1232            method: IndexMethod::default(),
1233            where_clause: None,
1234            include: Vec::new(),
1235            concurrently: false,
1236            expressions,
1237        }
1238    }
1239
1240    /// Mark this index as UNIQUE.
1241    pub fn unique(mut self) -> Self {
1242        self.unique = true;
1243        self
1244    }
1245
1246    // ==================== Phase 4: Advanced Index Options ====================
1247
1248    /// Set index method (USING clause)
1249    pub fn using(mut self, method: IndexMethod) -> Self {
1250        self.method = method;
1251        self
1252    }
1253
1254    /// Create a partial index with WHERE clause
1255    pub fn partial(mut self, expr: CheckExpr) -> Self {
1256        self.where_clause = Some(expr);
1257        self
1258    }
1259
1260    /// Add INCLUDE columns (covering index)
1261    pub fn include(mut self, cols: Vec<String>) -> Self {
1262        self.include = cols;
1263        self
1264    }
1265
1266    /// Create index CONCURRENTLY
1267    pub fn concurrently(mut self) -> Self {
1268        self.concurrently = true;
1269        self
1270    }
1271}
1272
1273/// Format a Schema to .qail format string.
1274/// Convert FkAction to its QAIL string representation
1275fn fk_action_str(action: &FkAction) -> &'static str {
1276    match action {
1277        FkAction::NoAction => "no_action",
1278        FkAction::Cascade => "cascade",
1279        FkAction::SetNull => "set_null",
1280        FkAction::SetDefault => "set_default",
1281        FkAction::Restrict => "restrict",
1282    }
1283}
1284
1285/// Serialize CheckExpr to QAIL check syntax
1286fn check_expr_str(expr: &CheckExpr) -> String {
1287    match expr {
1288        CheckExpr::GreaterThan { column, value } => format!("{} > {}", column, value),
1289        CheckExpr::GreaterOrEqual { column, value } => format!("{} >= {}", column, value),
1290        CheckExpr::LessThan { column, value } => format!("{} < {}", column, value),
1291        CheckExpr::LessOrEqual { column, value } => format!("{} <= {}", column, value),
1292        CheckExpr::Between { column, low, high } => format!("{} between {} {}", column, low, high),
1293        CheckExpr::In { column, values } => format!("{} in [{}]", column, values.join(", ")),
1294        CheckExpr::Regex { column, pattern } => format!("{} ~ '{}'", column, pattern),
1295        CheckExpr::MaxLength { column, max } => format!("length({}) <= {}", column, max),
1296        CheckExpr::MinLength { column, min } => format!("length({}) >= {}", column, min),
1297        CheckExpr::NotNull { column } => format!("{} not_null", column),
1298        CheckExpr::And(l, r) => format!("{} and {}", check_expr_str(l), check_expr_str(r)),
1299        CheckExpr::Or(l, r) => format!("{} or {}", check_expr_str(l), check_expr_str(r)),
1300        CheckExpr::Not(e) => format!("not {}", check_expr_str(e)),
1301        CheckExpr::Sql(sql) => sql.clone(),
1302    }
1303}
1304
1305/// Serialize a `Schema` back to a QAIL-format string.
1306pub fn to_qail_string(schema: &Schema) -> String {
1307    let mut output = String::new();
1308    output.push_str("# QAIL Schema\n\n");
1309
1310    // Extensions first (must be created before any DDL)
1311    for ext in &schema.extensions {
1312        let mut line = format!("extension \"{}\"", ext.name);
1313        if let Some(ref s) = ext.schema {
1314            line.push_str(&format!(" schema {}", s));
1315        }
1316        if let Some(ref v) = ext.version {
1317            line.push_str(&format!(" version \"{}\"", v));
1318        }
1319        output.push_str(&line);
1320        output.push('\n');
1321    }
1322    if !schema.extensions.is_empty() {
1323        output.push('\n');
1324    }
1325
1326    // Enums (CREATE TYPE ... AS ENUM, must precede tables)
1327    for enum_type in &schema.enums {
1328        let values = enum_type
1329            .values
1330            .iter()
1331            .map(|v| v.as_str())
1332            .collect::<Vec<_>>()
1333            .join(", ");
1334        output.push_str(&format!("enum {} {{ {} }}\n", enum_type.name, values));
1335    }
1336    if !schema.enums.is_empty() {
1337        output.push('\n');
1338    }
1339
1340    // Sequences (before tables, since columns may reference them)
1341    for seq in &schema.sequences {
1342        if seq.start.is_some()
1343            || seq.increment.is_some()
1344            || seq.min_value.is_some()
1345            || seq.max_value.is_some()
1346            || seq.cache.is_some()
1347            || seq.cycle
1348            || seq.owned_by.is_some()
1349        {
1350            let mut opts = Vec::new();
1351            if let Some(v) = seq.start {
1352                opts.push(format!("start {}", v));
1353            }
1354            if let Some(v) = seq.increment {
1355                opts.push(format!("increment {}", v));
1356            }
1357            if let Some(v) = seq.min_value {
1358                opts.push(format!("minvalue {}", v));
1359            }
1360            if let Some(v) = seq.max_value {
1361                opts.push(format!("maxvalue {}", v));
1362            }
1363            if let Some(v) = seq.cache {
1364                opts.push(format!("cache {}", v));
1365            }
1366            if seq.cycle {
1367                opts.push("cycle".to_string());
1368            }
1369            if let Some(ref o) = seq.owned_by {
1370                opts.push(format!("owned_by {}", o));
1371            }
1372            output.push_str(&format!("sequence {} {{ {} }}\n", seq.name, opts.join(" ")));
1373        } else {
1374            output.push_str(&format!("sequence {}\n", seq.name));
1375        }
1376    }
1377    if !schema.sequences.is_empty() {
1378        output.push('\n');
1379    }
1380
1381    let mut table_names: Vec<&String> = schema.tables.keys().collect();
1382    table_names.sort();
1383    for table_name in table_names {
1384        let table = &schema.tables[table_name];
1385        output.push_str(&format!("table {} {{\n", table.name));
1386        for col in &table.columns {
1387            let mut constraints: Vec<String> = Vec::new();
1388            if col.primary_key {
1389                constraints.push("primary_key".to_string());
1390            }
1391            if !col.nullable && !col.primary_key {
1392                constraints.push("not_null".to_string());
1393            }
1394            if col.unique {
1395                constraints.push("unique".to_string());
1396            }
1397            if let Some(def) = &col.default {
1398                constraints.push(format!("default {}", def));
1399            }
1400            if let Some(ref fk) = col.foreign_key {
1401                let mut fk_str = format!("references {}({})", fk.table, fk.column);
1402                if fk.on_delete != FkAction::NoAction {
1403                    fk_str.push_str(&format!(" on_delete {}", fk_action_str(&fk.on_delete)));
1404                }
1405                if fk.on_update != FkAction::NoAction {
1406                    fk_str.push_str(&format!(" on_update {}", fk_action_str(&fk.on_update)));
1407                }
1408                match &fk.deferrable {
1409                    Deferrable::Deferrable => fk_str.push_str(" deferrable"),
1410                    Deferrable::InitiallyDeferred => fk_str.push_str(" initially_deferred"),
1411                    Deferrable::InitiallyImmediate => fk_str.push_str(" initially_immediate"),
1412                    Deferrable::NotDeferrable => {} // default, omit
1413                }
1414                constraints.push(fk_str);
1415            }
1416            if let Some(ref check) = col.check {
1417                constraints.push(format!("check({})", check_expr_str(&check.expr)));
1418                if let Some(name) = &check.name {
1419                    constraints.push(format!("check_name {}", name));
1420                }
1421            }
1422
1423            let constraint_str = if constraints.is_empty() {
1424                String::new()
1425            } else {
1426                format!(" {}", constraints.join(" "))
1427            };
1428
1429            output.push_str(&format!(
1430                "  {} {}{}\n",
1431                col.name,
1432                col.data_type.to_pg_type(),
1433                constraint_str
1434            ));
1435        }
1436        // Multi-column foreign keys
1437        for fk in &table.multi_column_fks {
1438            output.push_str(&format!(
1439                "  foreign_key ({}) references {}({})\n",
1440                fk.columns.join(", "),
1441                fk.ref_table,
1442                fk.ref_columns.join(", ")
1443            ));
1444        }
1445        // RLS directives
1446        if table.enable_rls {
1447            output.push_str("  enable_rls\n");
1448        }
1449        if table.force_rls {
1450            output.push_str("  force_rls\n");
1451        }
1452        output.push_str("}\n\n");
1453    }
1454
1455    for idx in &schema.indexes {
1456        let unique = if idx.unique { "unique " } else { "" };
1457        let cols = if !idx.expressions.is_empty() {
1458            idx.expressions.join(", ")
1459        } else {
1460            idx.columns.join(", ")
1461        };
1462        let mut line = format!("{}index {} on {}", unique, idx.name, idx.table);
1463        if idx.method != IndexMethod::BTree {
1464            line.push_str(" using ");
1465            line.push_str(index_method_str(&idx.method));
1466        }
1467        line.push_str(" (");
1468        line.push_str(&cols);
1469        line.push(')');
1470        if let Some(where_clause) = &idx.where_clause {
1471            line.push_str(" where ");
1472            line.push_str(&check_expr_str(where_clause));
1473        }
1474        output.push_str(&line);
1475        output.push('\n');
1476    }
1477
1478    for hint in &schema.migrations {
1479        match hint {
1480            MigrationHint::Rename { from, to } => {
1481                output.push_str(&format!("rename {} -> {}\n", from, to));
1482            }
1483            MigrationHint::Transform { expression, target } => {
1484                output.push_str(&format!("transform {} -> {}\n", expression, target));
1485            }
1486            MigrationHint::Drop { target, confirmed } => {
1487                let confirm = if *confirmed { " confirm" } else { "" };
1488                output.push_str(&format!("drop {}{}\n", target, confirm));
1489            }
1490        }
1491    }
1492
1493    // Views
1494    for view in &schema.views {
1495        let prefix = if view.materialized {
1496            "materialized view"
1497        } else {
1498            "view"
1499        };
1500        output.push_str(&format!(
1501            "{} {} $$\n{}\n$$\n\n",
1502            prefix, view.name, view.query
1503        ));
1504    }
1505
1506    // Functions
1507    for func in &schema.functions {
1508        let args = func.args.join(", ");
1509        let volatility = func
1510            .volatility
1511            .as_deref()
1512            .filter(|v| !v.trim().is_empty())
1513            .map(|v| format!(" {}", v))
1514            .unwrap_or_default();
1515        output.push_str(&format!(
1516            "function {}({}) returns {} language {}{} $$\n{}\n$$\n\n",
1517            func.name, args, func.returns, func.language, volatility, func.body
1518        ));
1519    }
1520
1521    // Triggers
1522    for trigger in &schema.triggers {
1523        let mut events = Vec::new();
1524        for evt in &trigger.events {
1525            if evt.eq_ignore_ascii_case("UPDATE") && !trigger.update_columns.is_empty() {
1526                events.push(format!("UPDATE OF {}", trigger.update_columns.join(", ")));
1527            } else {
1528                events.push(evt.clone());
1529            }
1530        }
1531        output.push_str(&format!(
1532            "trigger {} on {} {} {} execute {}\n",
1533            trigger.name,
1534            trigger.table,
1535            trigger.timing.to_lowercase(),
1536            events.join(" or ").to_lowercase(),
1537            trigger.execute_function
1538        ));
1539    }
1540    if !schema.triggers.is_empty() {
1541        output.push('\n');
1542    }
1543
1544    // Policies
1545    for policy in &schema.policies {
1546        let cmd = match policy.target {
1547            PolicyTarget::All => "all",
1548            PolicyTarget::Select => "select",
1549            PolicyTarget::Insert => "insert",
1550            PolicyTarget::Update => "update",
1551            PolicyTarget::Delete => "delete",
1552        };
1553        let perm = match policy.permissiveness {
1554            PolicyPermissiveness::Permissive => "",
1555            PolicyPermissiveness::Restrictive => " restrictive",
1556        };
1557        let role_str = match &policy.role {
1558            Some(r) => format!(" to {}", r),
1559            None => String::new(),
1560        };
1561        output.push_str(&format!(
1562            "policy {} on {} for {}{}{}",
1563            policy.name, policy.table, cmd, role_str, perm
1564        ));
1565        if let Some(ref using) = policy.using {
1566            output.push_str(&format!("\n  using $$ {} $$", using));
1567        }
1568        if let Some(ref wc) = policy.with_check {
1569            output.push_str(&format!("\n  with_check $$ {} $$", wc));
1570        }
1571        output.push_str("\n\n");
1572    }
1573
1574    // Grants
1575    for grant in &schema.grants {
1576        let privs: Vec<String> = grant
1577            .privileges
1578            .iter()
1579            .map(|p| p.to_string().to_lowercase())
1580            .collect();
1581        match grant.action {
1582            GrantAction::Grant => {
1583                output.push_str(&format!(
1584                    "grant {} on {} to {}\n",
1585                    privs.join(", "),
1586                    grant.on_object,
1587                    grant.to_role
1588                ));
1589            }
1590            GrantAction::Revoke => {
1591                output.push_str(&format!(
1592                    "revoke {} on {} from {}\n",
1593                    privs.join(", "),
1594                    grant.on_object,
1595                    grant.to_role
1596                ));
1597            }
1598        }
1599    }
1600    if !schema.grants.is_empty() {
1601        output.push('\n');
1602    }
1603
1604    // Comments last (tables must exist first)
1605    for comment in &schema.comments {
1606        match &comment.target {
1607            CommentTarget::Table(t) => {
1608                output.push_str(&format!("comment on {} \"{}\"\n", t, comment.text));
1609            }
1610            CommentTarget::Column { table, column } => {
1611                output.push_str(&format!(
1612                    "comment on {}.{} \"{}\"\n",
1613                    table, column, comment.text
1614                ));
1615            }
1616            CommentTarget::Raw(target) => {
1617                output.push_str(&format!("comment on {} \"{}\"\n", target, comment.text));
1618            }
1619        }
1620    }
1621
1622    output
1623}
1624
1625/// Convert a Schema to a list of Qail commands (CREATE TABLE, CREATE INDEX).
1626/// Used by shadow migration to apply the base schema before applying diffs.
1627pub fn schema_to_commands(schema: &Schema) -> Vec<crate::ast::Qail> {
1628    use crate::ast::{Action, ColumnGeneration, Constraint, Expr, IndexDef, Qail};
1629
1630    let mut cmds = Vec::new();
1631
1632    // Topologically sort tables by FK dependencies:
1633    // referenced targets must be created before dependent tables.
1634    let mut indegree: std::collections::HashMap<String, usize> = std::collections::HashMap::new();
1635    let mut reverse_adj: std::collections::HashMap<String, Vec<String>> =
1636        std::collections::HashMap::new();
1637
1638    for name in schema.tables.keys() {
1639        indegree.insert(name.clone(), 0);
1640    }
1641
1642    for table in schema.tables.values() {
1643        let mut deps = std::collections::HashSet::new();
1644        for col in &table.columns {
1645            if let Some(fk) = &col.foreign_key
1646                && fk.table != table.name
1647                && schema.tables.contains_key(&fk.table)
1648            {
1649                deps.insert(fk.table.clone());
1650            }
1651        }
1652
1653        indegree.insert(table.name.clone(), deps.len());
1654        for dep in deps {
1655            reverse_adj.entry(dep).or_default().push(table.name.clone());
1656        }
1657    }
1658
1659    let mut ready = std::collections::BTreeSet::new();
1660    for (name, deg) in &indegree {
1661        if *deg == 0 {
1662            ready.insert(name.clone());
1663        }
1664    }
1665
1666    let mut ordered_names: Vec<String> = Vec::with_capacity(schema.tables.len());
1667    while let Some(next) = ready.pop_first() {
1668        ordered_names.push(next.clone());
1669        if let Some(dependents) = reverse_adj.get(&next) {
1670            for dep_name in dependents {
1671                if let Some(d) = indegree.get_mut(dep_name)
1672                    && *d > 0
1673                {
1674                    *d -= 1;
1675                    if *d == 0 {
1676                        ready.insert(dep_name.clone());
1677                    }
1678                }
1679            }
1680        }
1681    }
1682
1683    // If there is an FK cycle, append remaining names in lexical order
1684    // so output is deterministic (runtime may still reject unresolved cycle).
1685    if ordered_names.len() < schema.tables.len() {
1686        let mut leftovers: Vec<String> = schema
1687            .tables
1688            .keys()
1689            .filter(|name| !ordered_names.contains(*name))
1690            .cloned()
1691            .collect();
1692        leftovers.sort();
1693        ordered_names.extend(leftovers);
1694    }
1695
1696    for table_name in ordered_names {
1697        let table = &schema.tables[&table_name];
1698        // Build columns using Expr::Def exactly like diff.rs does
1699        let columns: Vec<Expr> = table
1700            .columns
1701            .iter()
1702            .map(|col| {
1703                let mut constraints = Vec::new();
1704
1705                if col.primary_key {
1706                    constraints.push(Constraint::PrimaryKey);
1707                }
1708                if col.nullable {
1709                    constraints.push(Constraint::Nullable);
1710                }
1711                if col.unique {
1712                    constraints.push(Constraint::Unique);
1713                }
1714                if let Some(def) = &col.default {
1715                    constraints.push(Constraint::Default(def.clone()));
1716                }
1717                if let Some(ref fk) = col.foreign_key {
1718                    constraints.push(Constraint::References(foreign_key_to_sql(fk)));
1719                }
1720                if let Some(check) = &col.check {
1721                    let check_sql = check_expr_to_sql(&check.expr);
1722                    if let Some(name) = &check.name {
1723                        constraints.push(Constraint::Check(vec![format!(
1724                            "CONSTRAINT {} CHECK ({})",
1725                            name, check_sql
1726                        )]));
1727                    } else {
1728                        constraints.push(Constraint::Check(vec![check_sql]));
1729                    }
1730                }
1731                if let Some(generated) = &col.generated {
1732                    let gen_constraint = match generated {
1733                        Generated::AlwaysStored(expr) => {
1734                            Constraint::Generated(ColumnGeneration::Stored(expr.clone()))
1735                        }
1736                        Generated::AlwaysIdentity => {
1737                            Constraint::Generated(ColumnGeneration::Stored("identity".to_string()))
1738                        }
1739                        Generated::ByDefaultIdentity => Constraint::Generated(
1740                            ColumnGeneration::Stored("identity_by_default".to_string()),
1741                        ),
1742                    };
1743                    constraints.push(gen_constraint);
1744                }
1745
1746                Expr::Def {
1747                    name: col.name.clone(),
1748                    data_type: col.data_type.to_pg_type(),
1749                    constraints,
1750                }
1751            })
1752            .collect();
1753
1754        cmds.push(Qail {
1755            action: Action::Make,
1756            table: table.name.clone(),
1757            columns,
1758            ..Default::default()
1759        });
1760    }
1761
1762    // Add indexes using IndexDef like diff.rs
1763    for idx in &schema.indexes {
1764        cmds.push(Qail {
1765            action: Action::Index,
1766            table: String::new(),
1767            index_def: Some(IndexDef {
1768                name: idx.name.clone(),
1769                table: idx.table.clone(),
1770                columns: if !idx.expressions.is_empty() {
1771                    idx.expressions.clone()
1772                } else {
1773                    idx.columns.clone()
1774                },
1775                unique: idx.unique,
1776                index_type: Some(index_method_str(&idx.method).to_string()),
1777                where_clause: idx.where_clause.as_ref().map(check_expr_to_sql),
1778            }),
1779            ..Default::default()
1780        });
1781    }
1782
1783    cmds
1784}
1785
1786fn fk_action_to_sql(action: &FkAction) -> &'static str {
1787    match action {
1788        FkAction::NoAction => "NO ACTION",
1789        FkAction::Cascade => "CASCADE",
1790        FkAction::SetNull => "SET NULL",
1791        FkAction::SetDefault => "SET DEFAULT",
1792        FkAction::Restrict => "RESTRICT",
1793    }
1794}
1795
1796fn deferrable_to_sql(deferrable: &Deferrable) -> Option<&'static str> {
1797    match deferrable {
1798        Deferrable::NotDeferrable => None,
1799        Deferrable::Deferrable => Some("DEFERRABLE"),
1800        Deferrable::InitiallyDeferred => Some("DEFERRABLE INITIALLY DEFERRED"),
1801        Deferrable::InitiallyImmediate => Some("DEFERRABLE INITIALLY IMMEDIATE"),
1802    }
1803}
1804
1805fn foreign_key_to_sql(fk: &ForeignKey) -> String {
1806    let mut target = format!("{}({})", fk.table, fk.column);
1807    if fk.on_delete != FkAction::NoAction {
1808        target.push_str(" ON DELETE ");
1809        target.push_str(fk_action_to_sql(&fk.on_delete));
1810    }
1811    if fk.on_update != FkAction::NoAction {
1812        target.push_str(" ON UPDATE ");
1813        target.push_str(fk_action_to_sql(&fk.on_update));
1814    }
1815    if let Some(def) = deferrable_to_sql(&fk.deferrable) {
1816        target.push(' ');
1817        target.push_str(def);
1818    }
1819    target
1820}
1821
1822fn check_expr_to_sql(expr: &CheckExpr) -> String {
1823    match expr {
1824        CheckExpr::GreaterThan { column, value } => format!("{column} > {value}"),
1825        CheckExpr::GreaterOrEqual { column, value } => format!("{column} >= {value}"),
1826        CheckExpr::LessThan { column, value } => format!("{column} < {value}"),
1827        CheckExpr::LessOrEqual { column, value } => format!("{column} <= {value}"),
1828        CheckExpr::Between { column, low, high } => format!("{column} BETWEEN {low} AND {high}"),
1829        CheckExpr::In { column, values } => {
1830            if values.len() == 1 && looks_like_raw_check_expr(&values[0]) {
1831                return values[0].clone();
1832            }
1833            let quoted = values
1834                .iter()
1835                .map(|v| format!("'{}'", v.replace('\'', "''")))
1836                .collect::<Vec<_>>()
1837                .join(", ");
1838            format!("{column} IN ({quoted})")
1839        }
1840        CheckExpr::Regex { column, pattern } => {
1841            format!("{column} ~ '{}'", pattern.replace('\'', "''"))
1842        }
1843        CheckExpr::MaxLength { column, max } => format!("char_length({column}) <= {max}"),
1844        CheckExpr::MinLength { column, min } => format!("char_length({column}) >= {min}"),
1845        CheckExpr::NotNull { column } => format!("{column} IS NOT NULL"),
1846        CheckExpr::And(left, right) => {
1847            format!(
1848                "({}) AND ({})",
1849                check_expr_to_sql(left),
1850                check_expr_to_sql(right)
1851            )
1852        }
1853        CheckExpr::Or(left, right) => {
1854            format!(
1855                "({}) OR ({})",
1856                check_expr_to_sql(left),
1857                check_expr_to_sql(right)
1858            )
1859        }
1860        CheckExpr::Not(inner) => format!("NOT ({})", check_expr_to_sql(inner)),
1861        CheckExpr::Sql(sql) => sql.clone(),
1862    }
1863}
1864
1865fn looks_like_raw_check_expr(s: &str) -> bool {
1866    s.chars()
1867        .any(|c| c.is_whitespace() || matches!(c, '<' | '>' | '=' | '!' | '(' | ')' | ':'))
1868}
1869
1870#[cfg(test)]
1871mod tests {
1872    use super::*;
1873
1874    #[test]
1875    fn test_schema_builder() {
1876        let mut schema = Schema::new();
1877
1878        let users = Table::new("users")
1879            .column(Column::new("id", ColumnType::Serial).primary_key())
1880            .column(Column::new("name", ColumnType::Text).not_null())
1881            .column(Column::new("email", ColumnType::Text).unique());
1882
1883        schema.add_table(users);
1884        schema.add_index(Index::new("idx_users_email", "users", vec!["email".into()]).unique());
1885
1886        let output = to_qail_string(&schema);
1887        assert!(output.contains("table users"));
1888        assert!(output.contains("id SERIAL primary_key"));
1889        assert!(output.contains("unique index idx_users_email"));
1890    }
1891
1892    #[test]
1893    fn test_migration_hints() {
1894        let mut schema = Schema::new();
1895        schema.add_hint(MigrationHint::Rename {
1896            from: "users.username".into(),
1897            to: "users.name".into(),
1898        });
1899
1900        let output = to_qail_string(&schema);
1901        assert!(output.contains("rename users.username -> users.name"));
1902    }
1903
1904    #[test]
1905    fn test_to_qail_string_includes_function_volatility() {
1906        let mut schema = Schema::new();
1907        let func = SchemaFunctionDef::new(
1908            "is_super_admin",
1909            "boolean",
1910            "BEGIN RETURN true; END;".to_string(),
1911        )
1912        .language("plpgsql")
1913        .volatility("stable");
1914        schema.add_function(func);
1915
1916        let output = to_qail_string(&schema);
1917        assert!(
1918            output.contains("function is_super_admin() returns boolean language plpgsql stable $$")
1919        );
1920    }
1921
1922    #[test]
1923    fn test_invalid_primary_key_type_strict() {
1924        let err = Column::new("data", ColumnType::Jsonb)
1925            .try_primary_key()
1926            .expect_err("JSONB should be rejected by strict PK policy");
1927        assert!(err.contains("cannot be a primary key"));
1928    }
1929
1930    #[test]
1931    fn test_invalid_primary_key_type_fail_soft() {
1932        let col = Column::new("data", ColumnType::Jsonb).primary_key();
1933        assert!(col.primary_key);
1934        assert!(!col.nullable);
1935    }
1936
1937    #[test]
1938    fn test_invalid_unique_type_strict() {
1939        let err = Column::new("data", ColumnType::Jsonb)
1940            .try_unique()
1941            .expect_err("JSONB should be rejected by strict UNIQUE policy");
1942        assert!(err.contains("cannot have UNIQUE"));
1943    }
1944
1945    #[test]
1946    fn test_invalid_unique_type_fail_soft() {
1947        let col = Column::new("data", ColumnType::Jsonb).unique();
1948        assert!(col.unique);
1949    }
1950
1951    #[test]
1952    fn test_foreign_key_valid() {
1953        let mut schema = Schema::new();
1954
1955        schema.add_table(
1956            Table::new("users").column(Column::new("id", ColumnType::Uuid).primary_key()),
1957        );
1958
1959        schema.add_table(
1960            Table::new("posts")
1961                .column(Column::new("id", ColumnType::Uuid).primary_key())
1962                .column(
1963                    Column::new("user_id", ColumnType::Uuid)
1964                        .references("users", "id")
1965                        .on_delete(FkAction::Cascade),
1966                ),
1967        );
1968
1969        // Should pass validation
1970        assert!(schema.validate().is_ok());
1971    }
1972
1973    #[test]
1974    fn test_foreign_key_invalid_table() {
1975        let mut schema = Schema::new();
1976
1977        schema.add_table(
1978            Table::new("posts")
1979                .column(Column::new("id", ColumnType::Uuid).primary_key())
1980                .column(Column::new("user_id", ColumnType::Uuid).references("nonexistent", "id")),
1981        );
1982
1983        // Should fail validation
1984        let result = schema.validate();
1985        assert!(result.is_err());
1986        assert!(result.unwrap_err()[0].contains("non-existent table"));
1987    }
1988
1989    #[test]
1990    fn test_foreign_key_invalid_column() {
1991        let mut schema = Schema::new();
1992
1993        schema.add_table(
1994            Table::new("users").column(Column::new("id", ColumnType::Uuid).primary_key()),
1995        );
1996
1997        schema.add_table(
1998            Table::new("posts")
1999                .column(Column::new("id", ColumnType::Uuid).primary_key())
2000                .column(
2001                    Column::new("user_id", ColumnType::Uuid).references("users", "wrong_column"),
2002                ),
2003        );
2004
2005        // Should fail validation
2006        let result = schema.validate();
2007        assert!(result.is_err());
2008        assert!(result.unwrap_err()[0].contains("non-existent column"));
2009    }
2010
2011    #[test]
2012    fn test_schema_to_commands_preserves_fk_actions_and_checks() {
2013        let mut schema = Schema::new();
2014        schema.add_table(
2015            Table::new("orgs").column(Column::new("id", ColumnType::Uuid).primary_key()),
2016        );
2017        schema.add_table(
2018            Table::new("users")
2019                .column(Column::new("id", ColumnType::Uuid).primary_key())
2020                .column(
2021                    Column::new("org_id", ColumnType::Uuid)
2022                        .references("orgs", "id")
2023                        .on_delete(FkAction::Cascade)
2024                        .on_update(FkAction::Restrict),
2025                )
2026                .column(
2027                    Column::new("age", ColumnType::Int).check(CheckExpr::GreaterOrEqual {
2028                        column: "age".to_string(),
2029                        value: 18,
2030                    }),
2031                ),
2032        );
2033
2034        let cmds = schema_to_commands(&schema);
2035        let users_cmd = cmds
2036            .iter()
2037            .find(|c| c.action == crate::ast::Action::Make && c.table == "users")
2038            .expect("users create command should exist");
2039        let org_id_constraints = users_cmd
2040            .columns
2041            .iter()
2042            .find_map(|e| match e {
2043                crate::ast::Expr::Def {
2044                    name, constraints, ..
2045                } if name == "org_id" => Some(constraints),
2046                _ => None,
2047            })
2048            .expect("org_id should exist");
2049        let age_constraints = users_cmd
2050            .columns
2051            .iter()
2052            .find_map(|e| match e {
2053                crate::ast::Expr::Def {
2054                    name, constraints, ..
2055                } if name == "age" => Some(constraints),
2056                _ => None,
2057            })
2058            .expect("age should exist");
2059
2060        assert!(
2061            org_id_constraints.iter().any(|c| matches!(
2062                c,
2063                crate::ast::Constraint::References(target)
2064                if target.contains("orgs(id)")
2065                    && target.contains("ON DELETE CASCADE")
2066                    && target.contains("ON UPDATE RESTRICT")
2067            )),
2068            "foreign key action clauses should be preserved"
2069        );
2070        assert!(
2071            age_constraints
2072                .iter()
2073                .any(|c| matches!(c, crate::ast::Constraint::Check(vals) if vals.len() == 1)),
2074            "check expressions should be preserved"
2075        );
2076    }
2077}
qail_core/migrate/schema.rs

qail_core/migrate/
schema.rs
