drizzle_types/postgres/ddl/
sql.rs

1//! SQL generation for `PostgreSQL` DDL types
2//!
3//! This module provides SQL generation methods for DDL types, enabling
4//! unified SQL output from both compile-time and runtime schema definitions.
5
6use crate::alloc_prelude::*;
7use core::fmt::Write;
8
9use super::{
10    CheckConstraint, Column, Enum, ForeignKey, Generated, GeneratedType, Identity, IdentityType,
11    Index, IndexColumn, IndexColumnDef, Policy, PrimaryKey, Sequence, Table, UniqueConstraint,
12    View,
13};
14
15fn quote_ident(ident: &str) -> String {
16    format!("\"{}\"", ident.replace('"', "\"\""))
17}
18
19fn quote_literal(value: &str) -> String {
20    format!("'{}'", value.replace('\'', "''"))
21}
22
23fn schema_prefix(schema: &str) -> String {
24    if schema == "public" {
25        String::new()
26    } else {
27        format!("{}.", quote_ident(schema))
28    }
29}
30
31fn qualified_name(schema: &str, name: &str) -> String {
32    format!("{}{}", schema_prefix(schema), quote_ident(name))
33}
34
35fn index_column_sql(column: &IndexColumn) -> String {
36    let mut sql = if column.is_expression {
37        format!("({})", column.value)
38    } else {
39        quote_ident(&column.value)
40    };
41
42    if let Some(op) = column.opclass.as_ref() {
43        let _ = write!(sql, " {op}");
44    }
45
46    if !column.asc {
47        sql.push_str(" DESC");
48    }
49
50    if column.nulls_first {
51        sql.push_str(" NULLS FIRST");
52    }
53
54    sql
55}
56
57// =============================================================================
58// Table SQL Generation
59// =============================================================================
60
61/// A complete table definition with all related entities for SQL generation
62#[derive(Clone, Debug)]
63pub struct TableSql<'a> {
64    pub table: &'a Table,
65    pub columns: &'a [Column],
66    pub primary_key: Option<&'a PrimaryKey>,
67    pub foreign_keys: &'a [ForeignKey],
68    pub unique_constraints: &'a [UniqueConstraint],
69    pub check_constraints: &'a [CheckConstraint],
70    pub indexes: &'a [Index],
71    pub policies: &'a [Policy],
72}
73
74impl<'a> TableSql<'a> {
75    /// Create a new `TableSql` for SQL generation
76    #[must_use]
77    pub const fn new(table: &'a Table) -> Self {
78        Self {
79            table,
80            columns: &[],
81            primary_key: None,
82            foreign_keys: &[],
83            unique_constraints: &[],
84            check_constraints: &[],
85            indexes: &[],
86            policies: &[],
87        }
88    }
89
90    /// Set columns
91    #[must_use]
92    pub const fn columns(mut self, columns: &'a [Column]) -> Self {
93        self.columns = columns;
94        self
95    }
96
97    /// Set primary key
98    #[must_use]
99    pub const fn primary_key(mut self, pk: Option<&'a PrimaryKey>) -> Self {
100        self.primary_key = pk;
101        self
102    }
103
104    /// Set foreign keys
105    #[must_use]
106    pub const fn foreign_keys(mut self, fks: &'a [ForeignKey]) -> Self {
107        self.foreign_keys = fks;
108        self
109    }
110
111    /// Set unique constraints
112    #[must_use]
113    pub const fn unique_constraints(mut self, uniques: &'a [UniqueConstraint]) -> Self {
114        self.unique_constraints = uniques;
115        self
116    }
117
118    /// Set check constraints
119    #[must_use]
120    pub const fn check_constraints(mut self, checks: &'a [CheckConstraint]) -> Self {
121        self.check_constraints = checks;
122        self
123    }
124
125    /// Set indexes
126    #[must_use]
127    pub const fn indexes(mut self, indexes: &'a [Index]) -> Self {
128        self.indexes = indexes;
129        self
130    }
131
132    /// Set policies
133    #[must_use]
134    pub const fn policies(mut self, policies: &'a [Policy]) -> Self {
135        self.policies = policies;
136        self
137    }
138
139    /// Generate CREATE TABLE SQL
140    #[must_use]
141    pub fn create_table_sql(&self) -> String {
142        let table_kind = if self.table.is_temporary.unwrap_or(false) {
143            "TEMPORARY "
144        } else if self.table.is_unlogged.unwrap_or(false) {
145            "UNLOGGED "
146        } else {
147            ""
148        };
149        let mut sql = format!(
150            "CREATE {}TABLE {} (\n",
151            table_kind,
152            qualified_name(self.table.schema(), self.table.name())
153        );
154
155        let mut lines = Vec::new();
156
157        // Column definitions
158        for column in self.columns {
159            lines.push(format!("\t{}", column.to_column_sql()));
160        }
161
162        // Primary key
163        if let Some(pk) = &self.primary_key {
164            let cols = pk
165                .columns
166                .iter()
167                .map(|c| quote_ident(c))
168                .collect::<Vec<_>>()
169                .join(", ");
170            if pk.name_explicit {
171                lines.push(format!(
172                    "\tCONSTRAINT {} PRIMARY KEY({})",
173                    quote_ident(pk.name()),
174                    cols
175                ));
176            } else {
177                lines.push(format!("\tPRIMARY KEY({cols})"));
178            }
179        }
180
181        // Foreign keys
182        for fk in self.foreign_keys {
183            lines.push(format!("\t{}", fk.to_constraint_sql()));
184        }
185
186        // Unique constraints
187        for unique in self.unique_constraints {
188            lines.push(format!("\t{}", unique.to_constraint_sql()));
189        }
190
191        // Check constraints
192        for check in self.check_constraints {
193            lines.push(format!(
194                "\tCONSTRAINT {} CHECK ({})",
195                quote_ident(check.name()),
196                check.value
197            ));
198        }
199
200        sql.push_str(&lines.join(",\n"));
201        sql.push('\n');
202        sql.push(')');
203
204        if let Some(inherits) = self.table.inherits.as_ref() {
205            let _ = write!(sql, " INHERITS ({})", quote_ident(inherits));
206        }
207
208        if let Some(tablespace) = self.table.tablespace.as_ref() {
209            let _ = write!(sql, " TABLESPACE {}", quote_ident(tablespace));
210        }
211
212        sql.push(';');
213
214        sql
215    }
216
217    /// Generate DROP TABLE SQL
218    #[must_use]
219    pub fn drop_table_sql(&self) -> String {
220        format!(
221            "DROP TABLE {};",
222            qualified_name(self.table.schema(), self.table.name())
223        )
224    }
225
226    /// Generate all related indexes
227    #[must_use]
228    pub fn create_indexes_sql(&self) -> Vec<String> {
229        self.indexes
230            .iter()
231            .map(super::index::Index::create_index_sql)
232            .collect()
233    }
234
235    /// Generate COMMENT ON statements for the table and its columns.
236    #[must_use]
237    pub fn create_comments_sql(&self) -> Vec<String> {
238        let mut comments = Vec::new();
239        let table_name = qualified_name(self.table.schema(), self.table.name());
240
241        if let Some(comment) = self.table.comment.as_ref() {
242            comments.push(format!(
243                "COMMENT ON TABLE {} IS {};",
244                table_name,
245                quote_literal(comment)
246            ));
247        }
248
249        for column in self.columns {
250            if let Some(comment) = column.comment.as_ref() {
251                comments.push(format!(
252                    "COMMENT ON COLUMN {}.{} IS {};",
253                    table_name,
254                    quote_ident(column.name()),
255                    quote_literal(comment)
256                ));
257            }
258        }
259
260        comments
261    }
262
263    /// Generate RLS enable statement if needed
264    #[must_use]
265    pub fn enable_rls_sql(&self) -> Option<String> {
266        if self.table.is_rls_enabled.unwrap_or(false) {
267            Some(format!(
268                "ALTER TABLE {} ENABLE ROW LEVEL SECURITY;",
269                qualified_name(self.table.schema(), self.table.name())
270            ))
271        } else {
272            None
273        }
274    }
275
276    /// Generate all policies
277    #[must_use]
278    pub fn create_policies_sql(&self) -> Vec<String> {
279        self.policies
280            .iter()
281            .map(super::policy::Policy::create_policy_sql)
282            .collect()
283    }
284}
285
286// =============================================================================
287// Column SQL Generation
288// =============================================================================
289
290impl Column {
291    /// Generate the column definition SQL (without leading/trailing punctuation)
292    #[must_use]
293    pub fn to_column_sql(&self) -> String {
294        let mut sql = format!("{} {}", quote_ident(self.name()), self.sql_type());
295
296        if let Some(dimensions) = self.dimensions
297            && dimensions > 0
298        {
299            for _ in 0..dimensions {
300                sql.push_str("[]");
301            }
302        }
303
304        // COLLATE follows the type in the PostgreSQL grammar. Collation
305        // names are double-quoted identifiers (`COLLATE "en_US"`,
306        // `COLLATE "C"`).
307        if let Some(collate) = self.collate.as_ref() {
308            let _ = write!(sql, " COLLATE {}", quote_ident(collate));
309        }
310
311        // Handle identity columns
312        if let Some(identity) = &self.identity {
313            sql.push_str(&identity.to_sql());
314        }
315
316        // Handle generated columns
317        if let Some(generated) = &self.generated {
318            sql.push_str(&generated.to_sql());
319        }
320
321        // Default value (skip if identity or generated - PostgreSQL doesn't allow both)
322        if self.identity.is_none()
323            && self.generated.is_none()
324            && let Some(default) = self.default.as_ref()
325        {
326            let _ = write!(sql, " DEFAULT {default}");
327        }
328
329        // NOT NULL
330        if self.not_null {
331            sql.push_str(" NOT NULL");
332        }
333
334        sql
335    }
336
337    /// Generate ADD COLUMN SQL
338    #[must_use]
339    pub fn add_column_sql(&self) -> String {
340        format!(
341            "ALTER TABLE {} ADD COLUMN {};",
342            qualified_name(self.schema(), self.table()),
343            self.to_column_sql()
344        )
345    }
346
347    /// Generate DROP COLUMN SQL
348    #[must_use]
349    pub fn drop_column_sql(&self) -> String {
350        format!(
351            "ALTER TABLE {} DROP COLUMN {};",
352            qualified_name(self.schema(), self.table()),
353            quote_ident(self.name())
354        )
355    }
356}
357
358// =============================================================================
359// Identity Column SQL
360// =============================================================================
361
362impl Identity {
363    /// Generate the GENERATED AS IDENTITY clause
364    #[must_use]
365    pub fn to_sql(&self) -> String {
366        let identity_type = match self.type_ {
367            IdentityType::Always => "ALWAYS",
368            IdentityType::ByDefault => "BY DEFAULT",
369        };
370
371        let mut sql = format!(" GENERATED {identity_type} AS IDENTITY");
372
373        // Add sequence options if any are specified
374        let mut options = Vec::new();
375
376        if let Some(increment) = self.increment.as_ref() {
377            options.push(format!("INCREMENT BY {increment}"));
378        }
379        if let Some(min) = self.min_value.as_ref() {
380            options.push(format!("MINVALUE {min}"));
381        }
382        if let Some(max) = self.max_value.as_ref() {
383            options.push(format!("MAXVALUE {max}"));
384        }
385        if let Some(start) = self.start_with.as_ref() {
386            options.push(format!("START WITH {start}"));
387        }
388        if let Some(cache) = self.cache {
389            options.push(format!("CACHE {cache}"));
390        }
391        if self.cycle.unwrap_or(false) {
392            options.push("CYCLE".to_string());
393        }
394
395        if !options.is_empty() {
396            let _ = write!(sql, " ({})", options.join(" "));
397        }
398
399        sql
400    }
401}
402
403// =============================================================================
404// Generated Column SQL
405// =============================================================================
406
407impl Generated {
408    /// Generate the GENERATED clause SQL
409    #[must_use]
410    pub fn to_sql(&self) -> String {
411        let gen_type = match self.gen_type {
412            GeneratedType::Stored => "STORED",
413            GeneratedType::Virtual => "VIRTUAL",
414        };
415        format!(" GENERATED ALWAYS AS ({}) {}", self.expression, gen_type)
416    }
417}
418
419// =============================================================================
420// Foreign Key SQL Generation
421// =============================================================================
422
423impl ForeignKey {
424    /// Generate the CONSTRAINT ... FOREIGN KEY clause SQL
425    #[must_use]
426    pub fn to_constraint_sql(&self) -> String {
427        let from_cols = self
428            .columns
429            .iter()
430            .map(|c| quote_ident(c))
431            .collect::<Vec<_>>()
432            .join(", ");
433
434        let to_cols = self
435            .columns_to
436            .iter()
437            .map(|c| quote_ident(c))
438            .collect::<Vec<_>>()
439            .join(", ");
440
441        let mut sql = format!(
442            "CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {}({})",
443            quote_ident(self.name()),
444            from_cols,
445            qualified_name(self.schema_to(), self.table_to()),
446            to_cols
447        );
448
449        if let Some(on_delete) = self.on_delete.as_ref()
450            && !on_delete.eq_ignore_ascii_case("NO ACTION")
451        {
452            let _ = write!(sql, " ON DELETE {}", on_delete.to_uppercase());
453        }
454
455        if let Some(on_update) = self.on_update.as_ref()
456            && !on_update.eq_ignore_ascii_case("NO ACTION")
457        {
458            let _ = write!(sql, " ON UPDATE {}", on_update.to_uppercase());
459        }
460
461        if self.deferrable || self.initially_deferred {
462            sql.push_str(" DEFERRABLE");
463            if self.initially_deferred {
464                sql.push_str(" INITIALLY DEFERRED");
465            }
466        }
467
468        sql
469    }
470
471    /// Generate ADD FOREIGN KEY SQL
472    #[must_use]
473    pub fn add_fk_sql(&self) -> String {
474        format!(
475            "ALTER TABLE {} ADD {};",
476            qualified_name(self.schema(), self.table()),
477            self.to_constraint_sql()
478        )
479    }
480
481    /// Generate DROP FOREIGN KEY SQL
482    #[must_use]
483    pub fn drop_fk_sql(&self) -> String {
484        format!(
485            "ALTER TABLE {} DROP CONSTRAINT {};",
486            qualified_name(self.schema(), self.table()),
487            quote_ident(self.name())
488        )
489    }
490}
491
492// =============================================================================
493// Index SQL Generation
494// =============================================================================
495
496impl Index {
497    /// Generate CREATE INDEX SQL
498    #[must_use]
499    pub fn create_index_sql(&self) -> String {
500        let unique = if self.is_unique { "UNIQUE " } else { "" };
501
502        let concurrently = if self.concurrently {
503            "CONCURRENTLY "
504        } else {
505            ""
506        };
507
508        let columns = self
509            .columns
510            .iter()
511            .map(index_column_sql)
512            .collect::<Vec<_>>()
513            .join(", ");
514
515        let using = self
516            .method
517            .as_ref()
518            .map(|m| format!(" USING {m}"))
519            .unwrap_or_default();
520
521        let mut sql = format!(
522            "CREATE {}INDEX {}{} ON {}{}({})",
523            unique,
524            concurrently,
525            quote_ident(self.name()),
526            qualified_name(self.schema(), self.table()),
527            using,
528            columns
529        );
530
531        if let Some(with) = self.with.as_ref() {
532            let _ = write!(sql, " WITH ({with})");
533        }
534
535        if let Some(where_clause) = self.where_clause.as_ref() {
536            let _ = write!(sql, " WHERE {where_clause}");
537        }
538
539        sql.push(';');
540        sql
541    }
542
543    /// Generate DROP INDEX SQL
544    #[must_use]
545    pub fn drop_index_sql(&self) -> String {
546        format!("DROP INDEX {};", qualified_name(self.schema(), self.name()))
547    }
548}
549
550impl IndexColumnDef {
551    /// Generate the column reference for an index
552    #[must_use]
553    pub fn to_sql(&self) -> String {
554        let mut sql = if self.is_expression {
555            format!("({})", self.value)
556        } else {
557            quote_ident(self.value)
558        };
559
560        if let Some(op) = self.opclass.as_ref() {
561            let _ = write!(sql, " {op}");
562        }
563
564        if !self.asc {
565            sql.push_str(" DESC");
566        }
567
568        if self.nulls_first {
569            sql.push_str(" NULLS FIRST");
570        }
571
572        sql
573    }
574}
575
576// =============================================================================
577// Enum SQL Generation
578// =============================================================================
579
580impl Enum {
581    /// Generate CREATE TYPE ... AS ENUM SQL
582    #[must_use]
583    pub fn create_enum_sql(&self) -> String {
584        let values = self
585            .values
586            .iter()
587            .map(|v| quote_literal(v))
588            .collect::<Vec<_>>()
589            .join(", ");
590        format!(
591            "CREATE TYPE {} AS ENUM ({});",
592            qualified_name(self.schema(), self.name()),
593            values
594        )
595    }
596
597    /// Generate DROP TYPE SQL
598    #[must_use]
599    pub fn drop_enum_sql(&self) -> String {
600        format!("DROP TYPE {};", qualified_name(self.schema(), self.name()))
601    }
602
603    /// Generate ALTER TYPE ... ADD VALUE SQL
604    #[must_use]
605    pub fn add_value_sql(&self, value: &str, before: Option<&str>) -> String {
606        before.map_or_else(
607            || {
608                format!(
609                    "ALTER TYPE {} ADD VALUE {};",
610                    qualified_name(self.schema(), self.name()),
611                    quote_literal(value)
612                )
613            },
614            |before_value| {
615                format!(
616                    "ALTER TYPE {} ADD VALUE {} BEFORE {};",
617                    qualified_name(self.schema(), self.name()),
618                    quote_literal(value),
619                    quote_literal(before_value)
620                )
621            },
622        )
623    }
624}
625
626// =============================================================================
627// Sequence SQL Generation
628// =============================================================================
629
630impl Sequence {
631    /// Generate CREATE SEQUENCE SQL
632    #[must_use]
633    pub fn create_sequence_sql(&self) -> String {
634        let mut sql = format!(
635            "CREATE SEQUENCE {}",
636            qualified_name(self.schema(), self.name())
637        );
638
639        if let Some(inc) = self.increment_by.as_ref() {
640            let _ = write!(sql, " INCREMENT BY {inc}");
641        }
642        if let Some(min) = self.min_value.as_ref() {
643            let _ = write!(sql, " MINVALUE {min}");
644        }
645        if let Some(max) = self.max_value.as_ref() {
646            let _ = write!(sql, " MAXVALUE {max}");
647        }
648        if let Some(start) = self.start_with.as_ref() {
649            let _ = write!(sql, " START WITH {start}");
650        }
651        if let Some(cache) = self.cache_size {
652            let _ = write!(sql, " CACHE {cache}");
653        }
654        if self.cycle.unwrap_or(false) {
655            sql.push_str(" CYCLE");
656        }
657
658        sql.push(';');
659        sql
660    }
661
662    /// Generate DROP SEQUENCE SQL
663    #[must_use]
664    pub fn drop_sequence_sql(&self) -> String {
665        format!(
666            "DROP SEQUENCE {};",
667            qualified_name(self.schema(), self.name())
668        )
669    }
670}
671
672// =============================================================================
673// View SQL Generation
674// =============================================================================
675
676/// Append WITH-options clause for `CREATE VIEW`, returning the uppercased
677/// CHECK OPTION clause (to be emitted after `AS <definition>`), if any.
678fn append_view_with_options(
679    sql: &mut String,
680    with_opts: &super::view::ViewWithOption,
681) -> Option<String> {
682    let mut options = String::new();
683    let mut has_option = false;
684
685    let mut push_option = |name: &str, value: &dyn core::fmt::Display| {
686        if has_option {
687            options.push_str(", ");
688        } else {
689            has_option = true;
690        }
691        let _ = write!(options, "{name} = {value}");
692    };
693
694    if let Some(value) = with_opts.security_barrier {
695        push_option("security_barrier", &value);
696    }
697    if let Some(value) = with_opts.security_invoker {
698        push_option("security_invoker", &value);
699    }
700    if let Some(value) = with_opts.fillfactor {
701        push_option("fillfactor", &value);
702    }
703    if let Some(value) = with_opts.toast_tuple_target {
704        push_option("toast_tuple_target", &value);
705    }
706    if let Some(value) = with_opts.parallel_workers {
707        push_option("parallel_workers", &value);
708    }
709    if let Some(value) = with_opts.autovacuum_enabled {
710        push_option("autovacuum_enabled", &value);
711    }
712    if let Some(value) = with_opts.vacuum_index_cleanup.as_ref() {
713        push_option("vacuum_index_cleanup", value);
714    }
715    if let Some(value) = with_opts.vacuum_truncate {
716        push_option("vacuum_truncate", &value);
717    }
718    if let Some(value) = with_opts.autovacuum_vacuum_threshold {
719        push_option("autovacuum_vacuum_threshold", &value);
720    }
721    if let Some(value) = with_opts.autovacuum_vacuum_scale_factor {
722        push_option("autovacuum_vacuum_scale_factor", &value);
723    }
724    if let Some(value) = with_opts.autovacuum_vacuum_cost_delay {
725        push_option("autovacuum_vacuum_cost_delay", &value);
726    }
727    if let Some(value) = with_opts.autovacuum_vacuum_cost_limit {
728        push_option("autovacuum_vacuum_cost_limit", &value);
729    }
730    if let Some(value) = with_opts.autovacuum_freeze_min_age {
731        push_option("autovacuum_freeze_min_age", &value);
732    }
733    if let Some(value) = with_opts.autovacuum_freeze_max_age {
734        push_option("autovacuum_freeze_max_age", &value);
735    }
736    if let Some(value) = with_opts.autovacuum_freeze_table_age {
737        push_option("autovacuum_freeze_table_age", &value);
738    }
739    if let Some(value) = with_opts.autovacuum_multixact_freeze_min_age {
740        push_option("autovacuum_multixact_freeze_min_age", &value);
741    }
742    if let Some(value) = with_opts.autovacuum_multixact_freeze_max_age {
743        push_option("autovacuum_multixact_freeze_max_age", &value);
744    }
745    if let Some(value) = with_opts.autovacuum_multixact_freeze_table_age {
746        push_option("autovacuum_multixact_freeze_table_age", &value);
747    }
748    if let Some(value) = with_opts.log_autovacuum_min_duration {
749        push_option("log_autovacuum_min_duration", &value);
750    }
751    if let Some(value) = with_opts.user_catalog_table {
752        push_option("user_catalog_table", &value);
753    }
754
755    if has_option {
756        let _ = write!(sql, " WITH ({options})");
757    }
758
759    with_opts
760        .check_option
761        .as_deref()
762        .map(str::to_ascii_uppercase)
763}
764
765impl View {
766    /// Generate CREATE VIEW SQL
767    #[must_use]
768    pub fn create_view_sql(&self) -> String {
769        let materialized = if self.materialized {
770            "MATERIALIZED "
771        } else {
772            ""
773        };
774
775        let Some(def) = self.definition.as_ref() else {
776            return format!(
777                "-- {}View {} has no definition",
778                materialized,
779                qualified_name(self.schema(), self.name())
780            );
781        };
782
783        let mut sql = String::with_capacity(def.len() + 64);
784        let _ = write!(
785            sql,
786            "CREATE {}VIEW {}",
787            materialized,
788            qualified_name(self.schema(), self.name()),
789        );
790
791        if let Some(using) = self.using.as_ref() {
792            let _ = write!(sql, " USING {using}");
793        }
794
795        let check_option_clause = self
796            .with
797            .as_ref()
798            .and_then(|with_opts| append_view_with_options(&mut sql, with_opts));
799
800        if let Some(tablespace) = self.tablespace.as_ref() {
801            let _ = write!(sql, " TABLESPACE {}", quote_ident(tablespace));
802        }
803
804        sql.push_str(" AS ");
805        sql.push_str(def);
806
807        if let Some(check_option) = check_option_clause {
808            let _ = write!(sql, " WITH {check_option} CHECK OPTION");
809        }
810
811        if self.materialized && matches!(self.with_no_data, Some(true)) {
812            sql.push_str(" WITH NO DATA");
813        }
814
815        sql.push(';');
816        sql
817    }
818
819    /// Generate DROP VIEW SQL
820    #[must_use]
821    pub fn drop_view_sql(&self) -> String {
822        let materialized = if self.materialized {
823            "MATERIALIZED "
824        } else {
825            ""
826        };
827        format!(
828            "DROP {}VIEW {};",
829            materialized,
830            qualified_name(self.schema(), self.name())
831        )
832    }
833}
834
835// =============================================================================
836// Policy SQL Generation
837// =============================================================================
838
839impl Policy {
840    /// Generate CREATE POLICY SQL
841    #[must_use]
842    pub fn create_policy_sql(&self) -> String {
843        let mut sql = format!(
844            "CREATE POLICY {} ON {}",
845            quote_ident(self.name()),
846            qualified_name(self.schema(), self.table())
847        );
848
849        let as_clause = self.as_clause.as_deref().unwrap_or("PERMISSIVE");
850        let _ = write!(sql, " AS {}", as_clause.to_uppercase());
851
852        if let Some(r#for) = self.for_clause.as_ref() {
853            let _ = write!(sql, " FOR {}", r#for.to_uppercase());
854        }
855
856        if let Some(to) = self.to.as_ref()
857            && !to.is_empty()
858        {
859            let to_roles = to
860                .iter()
861                .map(|r| {
862                    if r.eq_ignore_ascii_case("public") {
863                        "PUBLIC".to_string()
864                    } else {
865                        quote_ident(r)
866                    }
867                })
868                .collect::<Vec<_>>()
869                .join(", ");
870            let _ = write!(sql, " TO {to_roles}");
871        }
872
873        if let Some(using) = self.using.as_ref() {
874            let _ = write!(sql, " USING ({using})");
875        }
876
877        if let Some(with_check) = self.with_check.as_ref() {
878            let _ = write!(sql, " WITH CHECK ({with_check})");
879        }
880
881        sql.push(';');
882        sql
883    }
884
885    /// Generate DROP POLICY SQL
886    #[must_use]
887    pub fn drop_policy_sql(&self) -> String {
888        format!(
889            "DROP POLICY {} ON {};",
890            quote_ident(self.name()),
891            qualified_name(self.schema(), self.table())
892        )
893    }
894}
895
896// =============================================================================
897// Table-level utilities
898// =============================================================================
899
900impl Table {
901    /// Generate DROP TABLE SQL
902    #[must_use]
903    pub fn drop_table_sql(&self) -> String {
904        format!("DROP TABLE {};", qualified_name(self.schema(), self.name()))
905    }
906
907    /// Generate RENAME TABLE SQL
908    #[must_use]
909    pub fn rename_table_sql(&self, new_name: &str) -> String {
910        format!(
911            "ALTER TABLE {} RENAME TO {};",
912            qualified_name(self.schema(), self.name()),
913            quote_ident(new_name)
914        )
915    }
916}
917
918// =============================================================================
919// Primary Key SQL Generation
920// =============================================================================
921
922impl PrimaryKey {
923    /// Generate the PRIMARY KEY constraint clause
924    #[must_use]
925    pub fn to_constraint_sql(&self) -> String {
926        let cols = self
927            .columns
928            .iter()
929            .map(|c| quote_ident(c))
930            .collect::<Vec<_>>()
931            .join(", ");
932
933        format!(
934            "CONSTRAINT {} PRIMARY KEY({})",
935            quote_ident(self.name()),
936            cols
937        )
938    }
939
940    /// Generate ADD PRIMARY KEY SQL
941    #[must_use]
942    pub fn add_pk_sql(&self) -> String {
943        format!(
944            "ALTER TABLE {} ADD {};",
945            qualified_name(self.schema(), self.table()),
946            self.to_constraint_sql()
947        )
948    }
949
950    /// Generate DROP PRIMARY KEY SQL
951    #[must_use]
952    pub fn drop_pk_sql(&self) -> String {
953        format!(
954            "ALTER TABLE {} DROP CONSTRAINT {};",
955            qualified_name(self.schema(), self.table()),
956            quote_ident(self.name())
957        )
958    }
959}
960
961// =============================================================================
962// Unique Constraint SQL Generation
963// =============================================================================
964
965impl UniqueConstraint {
966    fn constraint_sql(&self, space_before_columns: bool) -> String {
967        let cols = self
968            .columns
969            .iter()
970            .map(|c| quote_ident(c))
971            .collect::<Vec<_>>()
972            .join(", ");
973
974        let separator = if space_before_columns { " " } else { "" };
975        let mut sql = format!(
976            "CONSTRAINT {} UNIQUE{}({})",
977            quote_ident(self.name()),
978            separator,
979            cols
980        );
981        if self.deferrable || self.initially_deferred {
982            sql.push_str(" DEFERRABLE");
983            if self.initially_deferred {
984                sql.push_str(" INITIALLY DEFERRED");
985            }
986        }
987        sql
988    }
989
990    /// Generate the UNIQUE constraint clause
991    #[must_use]
992    pub fn to_constraint_sql(&self) -> String {
993        self.constraint_sql(false)
994    }
995
996    /// Generate ADD UNIQUE SQL
997    #[must_use]
998    pub fn add_unique_sql(&self) -> String {
999        format!(
1000            "ALTER TABLE {} ADD {};",
1001            qualified_name(self.schema(), self.table()),
1002            self.constraint_sql(true)
1003        )
1004    }
1005
1006    /// Generate DROP UNIQUE SQL
1007    #[must_use]
1008    pub fn drop_unique_sql(&self) -> String {
1009        format!(
1010            "ALTER TABLE {} DROP CONSTRAINT {};",
1011            qualified_name(self.schema(), self.table()),
1012            quote_ident(self.name())
1013        )
1014    }
1015}
1016
1017// =============================================================================
1018// Check Constraint SQL Generation
1019// =============================================================================
1020
1021impl CheckConstraint {
1022    /// Generate the CHECK constraint clause
1023    #[must_use]
1024    pub fn to_constraint_sql(&self) -> String {
1025        format!(
1026            "CONSTRAINT {} CHECK ({})",
1027            quote_ident(self.name()),
1028            self.value
1029        )
1030    }
1031
1032    /// Generate ADD CHECK SQL
1033    #[must_use]
1034    pub fn add_check_sql(&self) -> String {
1035        format!(
1036            "ALTER TABLE {} ADD {};",
1037            qualified_name(self.schema(), self.table()),
1038            self.to_constraint_sql()
1039        )
1040    }
1041
1042    /// Generate DROP CHECK SQL
1043    #[must_use]
1044    pub fn drop_check_sql(&self) -> String {
1045        format!(
1046            "ALTER TABLE {} DROP CONSTRAINT {};",
1047            qualified_name(self.schema(), self.table()),
1048            quote_ident(self.name())
1049        )
1050    }
1051}
1052
1053#[cfg(test)]
1054mod tests {
1055    use super::*;
1056    use crate::postgres::ddl::{ColumnDef, PrimaryKeyDef, TableDef};
1057    use std::borrow::Cow;
1058
1059    #[test]
1060    fn test_simple_create_table() {
1061        let table = TableDef::new("public", "users").into_table();
1062        let columns = [
1063            ColumnDef::new("public", "users", "id", "SERIAL")
1064                .not_null()
1065                .into_column(),
1066            ColumnDef::new("public", "users", "name", "TEXT")
1067                .not_null()
1068                .into_column(),
1069            ColumnDef::new("public", "users", "email", "TEXT").into_column(),
1070        ];
1071        const PK_COLS: &[Cow<'static, str>] = &[Cow::Borrowed("id")];
1072        let pk = PrimaryKeyDef::new("public", "users", "users_pkey")
1073            .columns(PK_COLS)
1074            .into_primary_key();
1075
1076        let sql = TableSql::new(&table)
1077            .columns(&columns)
1078            .primary_key(Some(&pk))
1079            .create_table_sql();
1080
1081        assert!(sql.contains("CREATE TABLE \"users\""));
1082        assert!(sql.contains("\"id\" SERIAL NOT NULL"));
1083        assert!(sql.contains("\"name\" TEXT NOT NULL"));
1084        assert!(sql.contains("\"email\" TEXT"));
1085    }
1086
1087    #[test]
1088    fn test_table_with_schema() {
1089        let table = TableDef::new("myschema", "users").into_table();
1090        let sql = TableSql::new(&table).create_table_sql();
1091        assert!(sql.contains("\"myschema\".\"users\""));
1092    }
1093
1094    #[test]
1095    fn test_unique_concurrently_index_word_order() {
1096        let mut index = Index::new(
1097            "public",
1098            "users",
1099            "users_email_idx",
1100            vec![IndexColumn::new("email")],
1101        )
1102        .unique();
1103        index.concurrently = true;
1104
1105        assert_eq!(
1106            index.create_index_sql(),
1107            "CREATE UNIQUE INDEX CONCURRENTLY \"users_email_idx\" ON \"users\"(\"email\");"
1108        );
1109    }
1110
1111    #[test]
1112    fn test_policy_uses_explicit_as_and_public_role() {
1113        let mut policy = Policy::new("public", "users", "users_policy");
1114        policy.to = Some(vec![Cow::Borrowed("public")]);
1115
1116        assert_eq!(
1117            policy.create_policy_sql(),
1118            "CREATE POLICY \"users_policy\" ON \"users\" AS PERMISSIVE TO PUBLIC;"
1119        );
1120    }
1121}
drizzle_types/postgres/ddl/sql.rs

drizzle_types/postgres/ddl/
sql.rs
