sea_query_spanner/
schema.rs

1//! Spanner DDL Schema Builder
2//!
3//! Provides a fluent API for building Spanner DDL statements.
4//! This is separate from SeaQuery's TableBuilder because Spanner DDL
5//! has significant differences from standard SQL DDL.
6
7use {crate::types::spanner_type_name, sea_query::ColumnType};
8
9/// Quote an identifier if it contains special characters (like hyphens)
10/// that are not allowed in unquoted Spanner identifiers.
11///
12/// Spanner identifiers must:
13/// - Start with a letter (a-z, A-Z) or underscore (_)
14/// - Contain only letters, digits, and underscores
15///
16/// If an identifier contains other characters (like hyphens), it must be quoted with backticks.
17pub fn quote_identifier(name: &str) -> String {
18    // Check if quoting is needed
19    let needs_quoting = name.chars().enumerate().any(|(i, c)| {
20        if i == 0 {
21            // First character must be letter or underscore
22            !(c.is_ascii_alphabetic() || c == '_')
23        } else {
24            // Subsequent characters must be letter, digit, or underscore
25            !(c.is_ascii_alphanumeric() || c == '_')
26        }
27    });
28
29    if needs_quoting {
30        format!("`{}`", name)
31    } else {
32        name.to_string()
33    }
34}
35
36/// Builder for CREATE TABLE statements in Spanner DDL format
37#[derive(Debug, Clone, Default)]
38pub struct SpannerTableBuilder {
39    table_name: String,
40    columns: Vec<SpannerColumn>,
41    primary_keys: Vec<String>,
42    interleave_in_parent: Option<String>,
43    on_delete_cascade: bool,
44    row_deletion_policy: Option<String>,
45}
46
47/// Represents a column definition for Spanner
48#[derive(Debug, Clone)]
49pub struct SpannerColumn {
50    name: String,
51    column_type: String,
52    not_null: bool,
53    default_expr: Option<String>,
54    generated_expr: Option<String>,
55    stored: bool,
56}
57
58impl SpannerTableBuilder {
59    /// Create a new table builder
60    pub fn new() -> Self {
61        Self::default()
62    }
63
64    /// Set the table name
65    pub fn table<S: Into<String>>(mut self, name: S) -> Self {
66        self.table_name = name.into();
67        self
68    }
69
70    /// Add a column with a SeaQuery ColumnType
71    pub fn col<S: Into<String>>(mut self, name: S, col_type: &ColumnType, not_null: bool) -> Self {
72        self.columns.push(SpannerColumn {
73            name: name.into(),
74            column_type: spanner_type_name(col_type),
75            not_null,
76            default_expr: None,
77            generated_expr: None,
78            stored: false,
79        });
80        self
81    }
82
83    /// Add a column with a raw Spanner type string
84    pub fn col_raw<S: Into<String>, T: Into<String>>(
85        mut self,
86        name: S,
87        spanner_type: T,
88        not_null: bool,
89    ) -> Self {
90        self.columns.push(SpannerColumn {
91            name: name.into(),
92            column_type: spanner_type.into(),
93            not_null,
94            default_expr: None,
95            generated_expr: None,
96            stored: false,
97        });
98        self
99    }
100
101    /// Add a STRING column
102    pub fn string<S: Into<String>>(self, name: S, max_len: Option<u32>, not_null: bool) -> Self {
103        let type_str = match max_len {
104            Some(len) => format!("STRING({})", len),
105            None => "STRING(MAX)".to_string(),
106        };
107        self.col_raw(name, type_str, not_null)
108    }
109
110    /// Add an INT64 column
111    pub fn int64<S: Into<String>>(self, name: S, not_null: bool) -> Self {
112        self.col_raw(name, "INT64", not_null)
113    }
114
115    /// Add a FLOAT64 column
116    pub fn float64<S: Into<String>>(self, name: S, not_null: bool) -> Self {
117        self.col_raw(name, "FLOAT64", not_null)
118    }
119
120    /// Add a BOOL column
121    pub fn bool<S: Into<String>>(self, name: S, not_null: bool) -> Self {
122        self.col_raw(name, "BOOL", not_null)
123    }
124
125    /// Add a BYTES column
126    pub fn bytes<S: Into<String>>(self, name: S, max_len: Option<u32>, not_null: bool) -> Self {
127        let type_str = match max_len {
128            Some(len) => format!("BYTES({})", len),
129            None => "BYTES(MAX)".to_string(),
130        };
131        self.col_raw(name, type_str, not_null)
132    }
133
134    /// Add a DATE column
135    pub fn date<S: Into<String>>(self, name: S, not_null: bool) -> Self {
136        self.col_raw(name, "DATE", not_null)
137    }
138
139    /// Add a TIMESTAMP column
140    pub fn timestamp<S: Into<String>>(self, name: S, not_null: bool) -> Self {
141        self.col_raw(name, "TIMESTAMP", not_null)
142    }
143
144    /// Add a JSON column
145    pub fn json<S: Into<String>>(self, name: S, not_null: bool) -> Self {
146        self.col_raw(name, "JSON", not_null)
147    }
148
149    /// Add a NUMERIC column
150    pub fn numeric<S: Into<String>>(self, name: S, not_null: bool) -> Self {
151        self.col_raw(name, "NUMERIC", not_null)
152    }
153
154    /// Add a UUID column
155    pub fn uuid<S: Into<String>>(self, name: S, not_null: bool) -> Self {
156        self.col_raw(name, "UUID", not_null)
157    }
158
159    /// Add a column with DEFAULT expression
160    pub fn col_with_default<S: Into<String>, T: Into<String>, D: Into<String>>(
161        mut self,
162        name: S,
163        spanner_type: T,
164        not_null: bool,
165        default_expr: D,
166    ) -> Self {
167        self.columns.push(SpannerColumn {
168            name: name.into(),
169            column_type: spanner_type.into(),
170            not_null,
171            default_expr: Some(default_expr.into()),
172            generated_expr: None,
173            stored: false,
174        });
175        self
176    }
177
178    /// Add a generated column
179    pub fn col_generated<S: Into<String>, T: Into<String>, E: Into<String>>(
180        mut self,
181        name: S,
182        spanner_type: T,
183        expr: E,
184        stored: bool,
185    ) -> Self {
186        self.columns.push(SpannerColumn {
187            name: name.into(),
188            column_type: spanner_type.into(),
189            not_null: false,
190            default_expr: None,
191            generated_expr: Some(expr.into()),
192            stored,
193        });
194        self
195    }
196
197    /// Set primary key columns
198    pub fn primary_key<I, S>(mut self, columns: I) -> Self
199    where
200        I: IntoIterator<Item = S>,
201        S: Into<String>,
202    {
203        self.primary_keys = columns.into_iter().map(Into::into).collect();
204        self
205    }
206
207    /// Set INTERLEAVE IN PARENT clause
208    pub fn interleave_in_parent<S: Into<String>>(mut self, parent_table: S) -> Self {
209        self.interleave_in_parent = Some(parent_table.into());
210        self
211    }
212
213    /// Set ON DELETE CASCADE for interleaved table
214    pub fn on_delete_cascade(mut self) -> Self {
215        self.on_delete_cascade = true;
216        self
217    }
218
219    /// Set row deletion policy (TTL)
220    pub fn row_deletion_policy<S: Into<String>>(mut self, column: S, days: u32) -> Self {
221        self.row_deletion_policy = Some(format!(
222            "OLDER_THAN({}, INTERVAL {} DAY)",
223            column.into(),
224            days
225        ));
226        self
227    }
228
229    /// Build the CREATE TABLE DDL statement
230    pub fn build(self) -> String {
231        let mut ddl = format!("CREATE TABLE {} (\n", quote_identifier(&self.table_name));
232
233        for (i, col) in self.columns.iter().enumerate() {
234            if i > 0 {
235                ddl.push_str(",\n");
236            }
237            ddl.push_str("  ");
238            ddl.push_str(&quote_identifier(&col.name));
239            ddl.push(' ');
240            ddl.push_str(&col.column_type);
241
242            if col.not_null {
243                ddl.push_str(" NOT NULL");
244            }
245
246            if let Some(default) = &col.default_expr {
247                ddl.push_str(" DEFAULT (");
248                ddl.push_str(default);
249                ddl.push(')');
250            }
251
252            if let Some(gen) = &col.generated_expr {
253                ddl.push_str(" AS (");
254                ddl.push_str(gen);
255                ddl.push(')');
256                if col.stored {
257                    ddl.push_str(" STORED");
258                }
259            }
260        }
261
262        ddl.push_str("\n) PRIMARY KEY (");
263        let quoted_pks: Vec<String> = self
264            .primary_keys
265            .iter()
266            .map(|pk| quote_identifier(pk))
267            .collect();
268        ddl.push_str(&quoted_pks.join(", "));
269        ddl.push(')');
270
271        if let Some(parent) = &self.interleave_in_parent {
272            ddl.push_str(",\n  INTERLEAVE IN PARENT ");
273            ddl.push_str(&quote_identifier(parent));
274            if self.on_delete_cascade {
275                ddl.push_str(" ON DELETE CASCADE");
276            }
277        }
278
279        if let Some(policy) = &self.row_deletion_policy {
280            ddl.push_str(",\n  ROW DELETION POLICY (");
281            ddl.push_str(policy);
282            ddl.push(')');
283        }
284
285        ddl
286    }
287}
288
289/// Builder for CREATE INDEX statements in Spanner DDL format
290#[derive(Debug, Clone, Default)]
291pub struct SpannerIndexBuilder {
292    index_name: String,
293    table_name: String,
294    columns: Vec<(String, Option<bool>)>, // (column_name, is_desc)
295    unique: bool,
296    null_filtered: bool,
297    storing: Vec<String>,
298    interleave_in: Option<String>,
299}
300
301impl SpannerIndexBuilder {
302    pub fn new() -> Self {
303        Self::default()
304    }
305
306    /// Set index name
307    pub fn name<S: Into<String>>(mut self, name: S) -> Self {
308        self.index_name = name.into();
309        self
310    }
311
312    /// Set table name
313    pub fn table<S: Into<String>>(mut self, name: S) -> Self {
314        self.table_name = name.into();
315        self
316    }
317
318    /// Add a column to the index
319    pub fn col<S: Into<String>>(mut self, name: S) -> Self {
320        self.columns.push((name.into(), None));
321        self
322    }
323
324    /// Add a column with ASC order
325    pub fn col_asc<S: Into<String>>(mut self, name: S) -> Self {
326        self.columns.push((name.into(), Some(false)));
327        self
328    }
329
330    /// Add a column with DESC order
331    pub fn col_desc<S: Into<String>>(mut self, name: S) -> Self {
332        self.columns.push((name.into(), Some(true)));
333        self
334    }
335
336    /// Make this a unique index
337    pub fn unique(mut self) -> Self {
338        self.unique = true;
339        self
340    }
341
342    /// Make this a null-filtered index
343    pub fn null_filtered(mut self) -> Self {
344        self.null_filtered = true;
345        self
346    }
347
348    /// Add STORING columns
349    pub fn storing<I, S>(mut self, columns: I) -> Self
350    where
351        I: IntoIterator<Item = S>,
352        S: Into<String>,
353    {
354        self.storing = columns.into_iter().map(Into::into).collect();
355        self
356    }
357
358    /// Set INTERLEAVE IN clause
359    pub fn interleave_in<S: Into<String>>(mut self, table: S) -> Self {
360        self.interleave_in = Some(table.into());
361        self
362    }
363
364    /// Build the CREATE INDEX DDL statement
365    pub fn build(self) -> String {
366        let mut ddl = String::new();
367        ddl.push_str("CREATE ");
368
369        if self.unique {
370            ddl.push_str("UNIQUE ");
371        }
372        if self.null_filtered {
373            ddl.push_str("NULL_FILTERED ");
374        }
375
376        ddl.push_str("INDEX ");
377        ddl.push_str(&quote_identifier(&self.index_name));
378        ddl.push_str(" ON ");
379        ddl.push_str(&quote_identifier(&self.table_name));
380        ddl.push_str(" (");
381
382        for (i, (col, order)) in self.columns.iter().enumerate() {
383            if i > 0 {
384                ddl.push_str(", ");
385            }
386            ddl.push_str(&quote_identifier(col));
387            if let Some(is_desc) = order {
388                ddl.push_str(if *is_desc { " DESC" } else { " ASC" });
389            }
390        }
391
392        ddl.push(')');
393
394        if !self.storing.is_empty() {
395            ddl.push_str(" STORING (");
396            let quoted_storing: Vec<String> =
397                self.storing.iter().map(|s| quote_identifier(s)).collect();
398            ddl.push_str(&quoted_storing.join(", "));
399            ddl.push(')');
400        }
401
402        if let Some(table) = &self.interleave_in {
403            ddl.push_str(", INTERLEAVE IN ");
404            ddl.push_str(&quote_identifier(table));
405        }
406
407        ddl
408    }
409}
410
411/// Builder for ALTER TABLE statements in Spanner DDL format
412#[derive(Debug, Clone)]
413pub enum SpannerAlterTable {
414    AddColumn {
415        table: String,
416        column: SpannerColumn,
417    },
418    DropColumn {
419        table: String,
420        column: String,
421    },
422    AlterColumn {
423        table: String,
424        column: String,
425        new_type: Option<String>,
426        set_not_null: Option<bool>,
427        set_default: Option<String>,
428        drop_default: bool,
429    },
430    AddForeignKey {
431        table: String,
432        constraint_name: String,
433        columns: Vec<String>,
434        ref_table: String,
435        ref_columns: Vec<String>,
436        on_delete: Option<String>,
437    },
438    DropConstraint {
439        table: String,
440        constraint_name: String,
441    },
442}
443
444impl SpannerAlterTable {
445    pub fn add_column<T: Into<String>, N: Into<String>, S: Into<String>>(
446        table: T,
447        name: N,
448        spanner_type: S,
449        not_null: bool,
450    ) -> Self {
451        Self::AddColumn {
452            table: table.into(),
453            column: SpannerColumn {
454                name: name.into(),
455                column_type: spanner_type.into(),
456                not_null,
457                default_expr: None,
458                generated_expr: None,
459                stored: false,
460            },
461        }
462    }
463
464    pub fn drop_column<T: Into<String>, N: Into<String>>(table: T, column: N) -> Self {
465        Self::DropColumn {
466            table: table.into(),
467            column: column.into(),
468        }
469    }
470
471    pub fn build(self) -> String {
472        match self {
473            Self::AddColumn { table, column } => {
474                let mut ddl = format!(
475                    "ALTER TABLE {} ADD COLUMN {} {}",
476                    quote_identifier(&table),
477                    quote_identifier(&column.name),
478                    column.column_type
479                );
480                if column.not_null {
481                    ddl.push_str(" NOT NULL");
482                }
483                if let Some(default) = column.default_expr {
484                    ddl.push_str(" DEFAULT (");
485                    ddl.push_str(&default);
486                    ddl.push(')');
487                }
488                ddl
489            }
490            Self::DropColumn { table, column } => {
491                format!(
492                    "ALTER TABLE {} DROP COLUMN {}",
493                    quote_identifier(&table),
494                    quote_identifier(&column)
495                )
496            }
497            Self::AlterColumn {
498                table,
499                column,
500                new_type,
501                set_not_null,
502                set_default,
503                drop_default,
504            } => {
505                let mut ddl = format!(
506                    "ALTER TABLE {} ALTER COLUMN {}",
507                    quote_identifier(&table),
508                    quote_identifier(&column)
509                );
510                if let Some(t) = new_type {
511                    ddl.push(' ');
512                    ddl.push_str(&t);
513                }
514                if let Some(nn) = set_not_null {
515                    if nn {
516                        ddl.push_str(" NOT NULL");
517                    }
518                }
519                if let Some(def) = set_default {
520                    ddl.push_str(" DEFAULT (");
521                    ddl.push_str(&def);
522                    ddl.push(')');
523                }
524                if drop_default {
525                    ddl.push_str(" DROP DEFAULT");
526                }
527                ddl
528            }
529            Self::AddForeignKey {
530                table,
531                constraint_name,
532                columns,
533                ref_table,
534                ref_columns,
535                on_delete,
536            } => {
537                let quoted_columns: Vec<String> =
538                    columns.iter().map(|c| quote_identifier(c)).collect();
539                let quoted_ref_columns: Vec<String> =
540                    ref_columns.iter().map(|c| quote_identifier(c)).collect();
541                let mut ddl = format!(
542                    "ALTER TABLE {} ADD CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {} ({})",
543                    quote_identifier(&table),
544                    quote_identifier(&constraint_name),
545                    quoted_columns.join(", "),
546                    quote_identifier(&ref_table),
547                    quoted_ref_columns.join(", ")
548                );
549                if let Some(action) = on_delete {
550                    ddl.push_str(" ON DELETE ");
551                    ddl.push_str(&action);
552                }
553                ddl
554            }
555            Self::DropConstraint {
556                table,
557                constraint_name,
558            } => {
559                format!(
560                    "ALTER TABLE {} DROP CONSTRAINT {}",
561                    quote_identifier(&table),
562                    quote_identifier(&constraint_name)
563                )
564            }
565        }
566    }
567}
568
569#[cfg(test)]
570mod tests {
571    use super::*;
572
573    #[test]
574    fn test_create_table_basic() {
575        let ddl = SpannerTableBuilder::new()
576            .table("users")
577            .string("id", Some(36), true)
578            .string("name", None, true)
579            .string("email", None, false)
580            .timestamp("created_at", true)
581            .primary_key(["id"])
582            .build();
583
584        assert_eq!(
585            ddl,
586            "CREATE TABLE users (\n  id STRING(36) NOT NULL,\n  name STRING(MAX) NOT NULL,\n  email STRING(MAX),\n  created_at TIMESTAMP NOT NULL\n) PRIMARY KEY (id)"
587        );
588    }
589
590    #[test]
591    fn test_create_table_interleaved() {
592        let ddl = SpannerTableBuilder::new()
593            .table("posts")
594            .string("user_id", Some(36), true)
595            .string("post_id", Some(36), true)
596            .string("content", None, true)
597            .primary_key(["user_id", "post_id"])
598            .interleave_in_parent("users")
599            .on_delete_cascade()
600            .build();
601
602        assert!(ddl.contains("INTERLEAVE IN PARENT users ON DELETE CASCADE"));
603    }
604
605    #[test]
606    fn test_create_index() {
607        let ddl = SpannerIndexBuilder::new()
608            .name("idx_users_email")
609            .table("users")
610            .col("email")
611            .unique()
612            .build();
613
614        assert_eq!(ddl, "CREATE UNIQUE INDEX idx_users_email ON users (email)");
615    }
616
617    #[test]
618    fn test_create_index_with_storing() {
619        let ddl = SpannerIndexBuilder::new()
620            .name("idx_users_name")
621            .table("users")
622            .col("name")
623            .storing(["email", "created_at"])
624            .build();
625
626        assert_eq!(
627            ddl,
628            "CREATE INDEX idx_users_name ON users (name) STORING (email, created_at)"
629        );
630    }
631
632    #[test]
633    fn test_alter_table_add_column() {
634        let ddl = SpannerAlterTable::add_column("users", "age", "INT64", false).build();
635        assert_eq!(ddl, "ALTER TABLE users ADD COLUMN age INT64");
636    }
637
638    #[test]
639    fn test_alter_table_drop_column() {
640        let ddl = SpannerAlterTable::drop_column("users", "age").build();
641        assert_eq!(ddl, "ALTER TABLE users DROP COLUMN age");
642    }
643
644    #[test]
645    fn test_quote_identifier_simple() {
646        assert_eq!(quote_identifier("users"), "users");
647        assert_eq!(quote_identifier("user_id"), "user_id");
648        assert_eq!(quote_identifier("_private"), "_private");
649        assert_eq!(quote_identifier("Table123"), "Table123");
650    }
651
652    #[test]
653    fn test_quote_identifier_with_hyphen() {
654        assert_eq!(quote_identifier("fk-user-id"), "`fk-user-id`");
655        assert_eq!(
656            quote_identifier("fk-schedule_events-schedule_id"),
657            "`fk-schedule_events-schedule_id`"
658        );
659        assert_eq!(quote_identifier("my-table"), "`my-table`");
660    }
661
662    #[test]
663    fn test_quote_identifier_with_special_chars() {
664        assert_eq!(quote_identifier("table.name"), "`table.name`");
665        assert_eq!(quote_identifier("col:type"), "`col:type`");
666        assert_eq!(quote_identifier("123start"), "`123start`");
667    }
668
669    #[test]
670    fn test_foreign_key_with_hyphen_name() {
671        let ddl = SpannerAlterTable::AddForeignKey {
672            table: "products".to_string(),
673            constraint_name: "fk-product-category".to_string(),
674            columns: vec!["category".to_string()],
675            ref_table: "categories".to_string(),
676            ref_columns: vec!["name".to_string()],
677            on_delete: None,
678        }
679        .build();
680
681        assert_eq!(
682            ddl,
683            "ALTER TABLE products ADD CONSTRAINT `fk-product-category` FOREIGN KEY (category) REFERENCES categories (name)"
684        );
685    }
686
687    #[test]
688    fn test_drop_constraint_with_hyphen_name() {
689        let ddl = SpannerAlterTable::DropConstraint {
690            table: "products".to_string(),
691            constraint_name: "fk-product-category".to_string(),
692        }
693        .build();
694
695        assert_eq!(
696            ddl,
697            "ALTER TABLE products DROP CONSTRAINT `fk-product-category`"
698        );
699    }
700
701    #[test]
702    fn test_index_with_hyphen_name() {
703        let ddl = SpannerIndexBuilder::new()
704            .name("idx-users-email")
705            .table("users")
706            .col("email")
707            .build();
708
709        assert_eq!(ddl, "CREATE INDEX `idx-users-email` ON users (email)");
710    }
711}
sea_query_spanner/schema.rs

sea_query_spanner/
schema.rs
