Skip to main content

schema_sql_generator/postgresql/
postgres_generator.rs

1use crate::common::generator_context::GeneratorContext;
2use crate::common::sql_generator::{DefaultSqlGenerator, SqlGenerator};
3use crate::common::sql_string::escape_sql_literal;
4use crate::postgresql::postgres_function_generator::PostgresFunctionGenerator;
5use crate::postgresql::postgres_index_generator::PostgresIndexGenerator;
6use crate::postgresql::postgres_other_sql_generator::PostgresOtherSqlGenerator;
7use crate::postgresql::postgres_procedure_generator::PostgresProcedureGenerator;
8use crate::postgresql::postgres_relation_generator::PostgresRelationGenerator;
9use crate::postgresql::postgres_table_generator::PostgresTableGenerator;
10use crate::postgresql::postgres_trigger_generator::PostgresTriggerGenerator;
11use crate::postgresql::postgres_util::qualified_enum_type_name;
12use crate::postgresql::postgres_view_generator::PostgresViewGenerator;
13use crate::sql_println;
14
15pub struct PostgresGenerator {
16    context: GeneratorContext,
17    sql_generator: DefaultSqlGenerator,
18}
19
20impl PostgresGenerator {
21    pub fn new(context: GeneratorContext) -> Self {
22        let sql_generator = DefaultSqlGenerator::new(
23            context.clone(),
24            Box::new(PostgresTableGenerator::new(context.clone())),
25            Box::new(PostgresRelationGenerator::new(context.clone())),
26            Box::new(PostgresIndexGenerator::new(context.clone())),
27            Box::new(PostgresFunctionGenerator::new(context.clone())),
28            Box::new(PostgresViewGenerator::new(context.clone())),
29            Box::new(PostgresProcedureGenerator::new(context.clone())),
30            Box::new(PostgresTriggerGenerator::new(context.clone())),
31            Box::new(PostgresOtherSqlGenerator::new(context.clone())),
32        );
33
34        Self {
35            context: context.clone(),
36            sql_generator,
37        }
38    }
39
40    fn create_uuid_generator_function(&self) {
41        let separator = self.context.settings().statement_separator().to_string();
42
43        self.context.with_writer(|writer| {
44            sql_println!(writer, "create or replace function generate_uuid() returns uuid language plpgsql volatile parallel unsafe as $$");
45            sql_println!(writer, "declare");
46            sql_println!(writer, "   -- The current UNIX timestamp in milliseconds");
47            sql_println!(writer, "   unix_time_ms CONSTANT bigint NOT NULL DEFAULT (extract(epoch FROM clock_timestamp()) * 1000)::bigint;");
48            sql_println!(writer, "");
49            sql_println!(writer, "   -- The buffer used to create the UUID: the low 6 bytes (48 bits) of the timestamp, followed by 10 random bytes");
50            sql_println!(writer, "   buffer bytea not null default substring(int8send(unix_time_ms) from 3) || gen_random_bytes(10);");
51            sql_println!(writer, "begin");
52            sql_println!(writer, "   -- Set the version nibble of byte 6 to 0111 (UUID v7), keeping the last 4 bits unchanged");
53            sql_println!(writer, "   buffer = set_byte(buffer, 6, (get_byte(buffer, 6) & 15) | 112);");
54            sql_println!(writer, "");
55            sql_println!(writer, "   -- Set the top 2 bits of byte 8 to 10 (the UUID variant specified in RFC 4122), keeping the last 6 bits unchanged");
56            sql_println!(writer,
57                "   buffer = set_byte(buffer, 8, (get_byte(buffer, 8) & 63) | 128);",
58            );
59            sql_println!(writer, "");
60            sql_println!(writer, "   return encode(buffer, 'hex')::uuid;");
61            sql_println!(writer, "end");
62            sql_println!(writer, "$${}", separator);
63            sql_println!(writer, "");
64        });
65    }
66
67    fn create_extensions(&self) {
68        let separator = self.context.settings().statement_separator().to_string();
69        let check_user = match self.context.settings().extension_check_user() {
70            Some(user) => format!("'{}'", escape_sql_literal(user)),
71            None => "CURRENT_USER".to_string(),
72        };
73        // generate_uuid() (created below in create_uuid_generator_function, when the target
74        // version is older than 18's built-in uuidv7()) calls gen_random_bytes(), which lives
75        // in pgcrypto - not citext/btree_gist. Without this, the function creates fine (its
76        // body isn't resolved until first call) and fails on the first insert into a Uuid
77        // column with "function gen_random_bytes(integer) does not exist" (H8).
78        let needs_pgcrypto = self.context.settings().target_postgres_version() < 18;
79
80        self.context.with_writer(|writer| {
81            sql_println!(writer, "do $$");
82            sql_println!(writer, "begin");
83            sql_println!(writer, "   if (select usesuper from pg_user where usename = {}) then", check_user);
84            if needs_pgcrypto {
85                sql_println!(writer, "      create extension if not exists \"pgcrypto\";");
86            }
87            sql_println!(writer, "      create extension if not exists \"citext\";");
88            sql_println!(writer, "      create extension if not exists \"btree_gist\";");
89            sql_println!(writer, "   else");
90            sql_println!(writer, "      raise notice 'Could not create extensions, user % does not have permission.', current_user;");
91            sql_println!(writer, "   end if;");
92            sql_println!(writer, "end;");
93            sql_println!(writer, "$${}", separator);
94            sql_println!(writer, "");
95        });
96    }
97
98    /// Emits `create schema if not exists ...` for every non-default schema the model
99    /// declares, so `create table {schema}.{table}` further down doesn't fail against a
100    /// schema that was never created (C4). `public` is skipped - Postgres always has it.
101    fn create_schemas(&self) {
102        let separator = self.context.settings().statement_separator().to_string();
103        let database_model = self.context.settings().database_model();
104
105        let mut schema_names: Vec<&str> = database_model
106            .schemas()
107            .iter()
108            .filter_map(|schema| schema.schema_name())
109            .filter(|name| !name.eq_ignore_ascii_case("public"))
110            .collect();
111        schema_names.sort_unstable();
112        schema_names.dedup_by(|a, b| a.eq_ignore_ascii_case(b));
113
114        if schema_names.is_empty() {
115            return;
116        }
117
118        self.context.with_writer(|writer| {
119            for schema_name in schema_names {
120                sql_println!(
121                    writer,
122                    "create schema if not exists {}{}",
123                    schema_name,
124                    separator
125                );
126            }
127            sql_println!(writer, "");
128        });
129    }
130
131    fn create_enum_types(&self) {
132        let separator = self.context.settings().statement_separator().to_string();
133        let database_model = self.context.settings().database_model();
134
135        for schema in database_model.schemas() {
136            for enum_type in schema.enum_types() {
137                let enum_type_name =
138                    qualified_enum_type_name(schema.schema_name(), enum_type.name());
139                let values = enum_type
140                    .values()
141                    .iter()
142                    .map(|v| format!("'{}'", escape_sql_literal(v.code())))
143                    .collect::<Vec<_>>()
144                    .join(",");
145
146                self.context.with_writer(|writer| {
147                    sql_println!(
148                        writer,
149                        "drop type if exists {} cascade{}",
150                        enum_type_name,
151                        separator
152                    );
153                    sql_println!(
154                        writer,
155                        "create type {} as enum ({}){}",
156                        enum_type_name,
157                        values,
158                        separator
159                    );
160                    sql_println!(writer, "");
161                });
162            }
163        }
164    }
165}
166
167impl SqlGenerator for PostgresGenerator {
168    fn context(&self) -> &GeneratorContext {
169        &self.context
170    }
171
172    fn output_header(&self) {
173        self.create_schemas();
174        if self.context.settings().target_postgres_version() < 18 {
175            self.create_uuid_generator_function();
176        }
177        if self.context.settings().emit_postgres_extensions() {
178            self.create_extensions();
179        }
180        self.create_enum_types();
181    }
182
183    fn output_tables(&self) {
184        self.sql_generator.output_tables();
185    }
186
187    fn output_relations(&self) {
188        self.sql_generator.output_relations();
189    }
190
191    fn output_indexes(&self) {
192        self.sql_generator.output_indexes();
193    }
194
195    fn output_triggers(&self) {
196        self.sql_generator.output_triggers();
197    }
198
199    fn output_functions(&self) {
200        self.sql_generator.output_functions();
201    }
202
203    fn output_views(&self) {
204        self.sql_generator.output_views();
205    }
206
207    fn output_procedures(&self) {
208        self.sql_generator.output_procedures();
209    }
210
211    fn output_other_sql_top(&self) {
212        self.sql_generator.output_other_sql_top();
213    }
214
215    fn output_other_sql_bottom(&self) {
216        self.sql_generator.output_other_sql_bottom();
217    }
218}
219
220#[cfg(test)]
221mod tests {
222    use super::*;
223    use crate::common::generate_options::GenerateOptions;
224    use crate::common::print_writer::PrintWriter;
225    use crate::common::sql_generator_settings::SqlGeneratorSettings;
226    use crate::common::sql_writer::SqlWriter;
227    use crate::common::test_support::SharedBuffer;
228    use schema_model::builder::SchemaBuilder;
229    use schema_model::model::database_model::DatabaseModel;
230    use schema_model::model::enum_type::{EnumType, EnumValue};
231    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode};
232    use std::cell::RefCell;
233    use std::rc::Rc;
234
235    fn make_context_with_version(
236        model: DatabaseModel,
237        target_postgres_version: u32,
238    ) -> (GeneratorContext, SharedBuffer) {
239        make_context(model, target_postgres_version, true)
240    }
241
242    fn make_context(
243        model: DatabaseModel,
244        target_postgres_version: u32,
245        emit_postgres_extensions: bool,
246    ) -> (GeneratorContext, SharedBuffer) {
247        let buffer = SharedBuffer::new();
248        let mut options = GenerateOptions::new(
249            Rc::new(model),
250            Rc::new(RefCell::new(PrintWriter::new_auto_flush(Box::new(
251                buffer.clone(),
252            )))),
253        );
254        options.target_postgres_version = target_postgres_version;
255        options.emit_postgres_extensions = emit_postgres_extensions;
256        let settings = SqlGeneratorSettings::new(DatabaseType::Postgresql, &options);
257        let writer = SqlWriter::new(options.writer.clone());
258        (GeneratorContext::new(settings, writer), buffer)
259    }
260
261    #[test]
262    fn output_header_includes_uuid_function_before_postgres_18() {
263        let schema = SchemaBuilder::new(None::<&str>).build();
264        let model =
265            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
266        let (ctx, buffer) = make_context_with_version(model, 17);
267
268        let generator = PostgresGenerator::new(ctx);
269        generator.output_header();
270
271        let output = buffer.contents();
272        assert!(output.contains("create or replace function generate_uuid()"));
273    }
274
275    #[test]
276    fn output_header_omits_uuid_function_on_postgres_18_and_later() {
277        let schema = SchemaBuilder::new(None::<&str>).build();
278        let model =
279            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
280        let (ctx, buffer) = make_context_with_version(model, 18);
281
282        let generator = PostgresGenerator::new(ctx);
283        generator.output_header();
284
285        let output = buffer.contents();
286        assert!(!output.contains("create or replace function generate_uuid()"));
287    }
288
289    #[test]
290    fn output_header_renders_enum_types_when_present() {
291        let enum_type = EnumType::new(
292            "status_type",
293            vec![
294                EnumValue::new("ACTIVE", Some("A".to_string())),
295                EnumValue::new("INACTIVE", Some("I".to_string())),
296            ],
297        );
298        let schema = SchemaBuilder::new(None::<&str>)
299            .add_enum_type(enum_type)
300            .build();
301        let model =
302            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
303        let (ctx, buffer) = make_context_with_version(model, 18);
304
305        let generator = PostgresGenerator::new(ctx);
306        generator.output_header();
307
308        let output = buffer.contents();
309        assert!(output.contains("create type status_type as enum ('A','I')"));
310    }
311
312    #[test]
313    fn output_header_omits_enum_ddl_when_no_enums_defined() {
314        let schema = SchemaBuilder::new(None::<&str>).build();
315        let model =
316            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
317        let (ctx, buffer) = make_context_with_version(model, 18);
318
319        let generator = PostgresGenerator::new(ctx);
320        generator.output_header();
321
322        let output = buffer.contents();
323        assert!(!output.contains("create type"));
324    }
325
326    #[test]
327    fn output_header_creates_non_default_schemas() {
328        let default_schema = SchemaBuilder::new(None::<&str>).build();
329        let app_schema = SchemaBuilder::new(Some("app")).build();
330        let model = DatabaseModel::new(
331            BooleanMode::Native,
332            ForeignKeyMode::Relations,
333            vec![default_schema, app_schema],
334        );
335        let (ctx, buffer) = make_context_with_version(model, 18);
336
337        let generator = PostgresGenerator::new(ctx);
338        generator.output_header();
339
340        let output = buffer.contents();
341        assert!(output.contains("create schema if not exists app;"));
342    }
343
344    #[test]
345    fn output_header_never_creates_the_public_schema() {
346        // `public` always exists in Postgres; emitting `create schema if not exists public`
347        // is harmless but noisy, so it's skipped.
348        let schema = SchemaBuilder::new(Some("public")).build();
349        let model =
350            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
351        let (ctx, buffer) = make_context_with_version(model, 18);
352
353        let generator = PostgresGenerator::new(ctx);
354        generator.output_header();
355
356        let output = buffer.contents();
357        assert!(!output.contains("create schema"));
358    }
359
360    #[test]
361    fn output_header_omits_schema_creation_when_only_default_schema_present() {
362        let schema = SchemaBuilder::new(None::<&str>).build();
363        let model =
364            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
365        let (ctx, buffer) = make_context_with_version(model, 18);
366
367        let generator = PostgresGenerator::new(ctx);
368        generator.output_header();
369
370        let output = buffer.contents();
371        assert!(!output.contains("create schema"));
372    }
373
374    #[test]
375    fn output_header_includes_extensions_block_by_default() {
376        let schema = SchemaBuilder::new(None::<&str>).build();
377        let model =
378            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
379        let (ctx, buffer) = make_context_with_version(model, 18);
380
381        let generator = PostgresGenerator::new(ctx);
382        generator.output_header();
383
384        let output = buffer.contents();
385        assert!(output.contains("create extension if not exists \"citext\""));
386        assert!(output.contains("create extension if not exists \"btree_gist\""));
387    }
388
389    #[test]
390    fn output_header_includes_pgcrypto_before_postgres_18() {
391        let schema = SchemaBuilder::new(None::<&str>).build();
392        let model =
393            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
394        let (ctx, buffer) = make_context_with_version(model, 17);
395
396        let generator = PostgresGenerator::new(ctx);
397        generator.output_header();
398
399        let output = buffer.contents();
400        assert!(output.contains("create extension if not exists \"pgcrypto\""));
401    }
402
403    #[test]
404    fn output_header_omits_pgcrypto_on_postgres_18_and_later() {
405        let schema = SchemaBuilder::new(None::<&str>).build();
406        let model =
407            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
408        let (ctx, buffer) = make_context_with_version(model, 18);
409
410        let generator = PostgresGenerator::new(ctx);
411        generator.output_header();
412
413        let output = buffer.contents();
414        assert!(!output.contains("pgcrypto"));
415    }
416
417    #[test]
418    fn output_header_omits_extensions_block_when_disabled() {
419        let schema = SchemaBuilder::new(None::<&str>).build();
420        let model =
421            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
422        let (ctx, buffer) = make_context(model, 18, false);
423
424        let generator = PostgresGenerator::new(ctx);
425        generator.output_header();
426
427        let output = buffer.contents();
428        assert!(!output.contains("create extension"));
429    }
430
431    #[test]
432    fn output_header_checks_current_user_by_default() {
433        let schema = SchemaBuilder::new(None::<&str>).build();
434        let model =
435            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
436        let (ctx, buffer) = make_context_with_version(model, 18);
437
438        let generator = PostgresGenerator::new(ctx);
439        generator.output_header();
440
441        let output = buffer.contents();
442        assert!(output.contains("where usename = CURRENT_USER"));
443    }
444
445    #[test]
446    fn output_header_checks_configured_extension_user() {
447        let schema = SchemaBuilder::new(None::<&str>).build();
448        let model =
449            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
450        let buffer = SharedBuffer::new();
451        let mut options = GenerateOptions::new(
452            Rc::new(model),
453            Rc::new(RefCell::new(PrintWriter::new_auto_flush(Box::new(
454                buffer.clone(),
455            )))),
456        );
457        options.target_postgres_version = 18;
458        options.extension_check_user = Some("schema_admin".to_string());
459        let settings = SqlGeneratorSettings::new(DatabaseType::Postgresql, &options);
460        let writer = SqlWriter::new(options.writer.clone());
461        let ctx = GeneratorContext::new(settings, writer);
462
463        let generator = PostgresGenerator::new(ctx);
464        generator.output_header();
465
466        let output = buffer.contents();
467        assert!(output.contains("where usename = 'schema_admin'"));
468        assert!(!output.contains("CURRENT_USER)"));
469    }
470
471    #[test]
472    fn output_header_escapes_single_quote_in_configured_extension_user() {
473        // Regression test: an unescaped embedded quote would break the generated SQL
474        // string literal (or worse, let the value's content escape it).
475        let schema = SchemaBuilder::new(None::<&str>).build();
476        let model =
477            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
478        let buffer = SharedBuffer::new();
479        let mut options = GenerateOptions::new(
480            Rc::new(model),
481            Rc::new(RefCell::new(PrintWriter::new_auto_flush(Box::new(
482                buffer.clone(),
483            )))),
484        );
485        options.target_postgres_version = 18;
486        options.extension_check_user = Some("o'brien".to_string());
487        let settings = SqlGeneratorSettings::new(DatabaseType::Postgresql, &options);
488        let writer = SqlWriter::new(options.writer.clone());
489        let ctx = GeneratorContext::new(settings, writer);
490
491        let generator = PostgresGenerator::new(ctx);
492        generator.output_header();
493
494        let output = buffer.contents();
495        assert!(output.contains("where usename = 'o''brien'"));
496    }
497
498    #[test]
499    fn output_header_escapes_single_quote_in_enum_value_code() {
500        // Regression test: an unescaped embedded quote in an enum value's code would
501        // break the generated `create type ... as enum (...)` statement.
502        let enum_type = EnumType::new(
503            "status_type",
504            vec![EnumValue::new("PRIME", Some("O'Brien".to_string()))],
505        );
506        let schema = SchemaBuilder::new(None::<&str>)
507            .add_enum_type(enum_type)
508            .build();
509        let model =
510            DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
511        let (ctx, buffer) = make_context_with_version(model, 18);
512
513        let generator = PostgresGenerator::new(ctx);
514        generator.output_header();
515
516        let output = buffer.contents();
517        assert!(output.contains("create type status_type as enum ('O''Brien')"));
518    }
519
520    #[test]
521    fn output_header_qualifies_enum_types_with_their_schema() {
522        // M4: two schemas declaring a same-named enum must not collide (an unqualified
523        // `drop type ... cascade` in the second would remove the first).
524        let default_schema = SchemaBuilder::new(None::<&str>)
525            .add_enum_type(EnumType::new(
526                "StatusType",
527                vec![EnumValue::new("A", None::<String>)],
528            ))
529            .build();
530        let app = SchemaBuilder::new(Some("app"))
531            .add_enum_type(EnumType::new(
532                "StatusType",
533                vec![EnumValue::new("B", None::<String>)],
534            ))
535            .build();
536        let model = DatabaseModel::new(
537            BooleanMode::Native,
538            ForeignKeyMode::Relations,
539            vec![default_schema, app],
540        );
541        let (ctx, buffer) = make_context_with_version(model, 18);
542
543        PostgresGenerator::new(ctx).output_header();
544
545        let output = buffer.contents();
546        assert!(output.contains("drop type if exists status_type cascade"));
547        assert!(output.contains("create type status_type as enum ('A')"));
548        assert!(output.contains("drop type if exists app.status_type cascade"));
549        assert!(output.contains("create type app.status_type as enum ('B')"));
550    }
551}