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

drizzle_types/postgres/ddl/
sql.rs
