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::to_snake_case;
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!(writer, "create schema if not exists {}{}", schema_name, separator);
121            }
122            sql_println!(writer, "");
123        });
124    }
125
126    fn create_enum_types(&self) {
127        let separator = self.context.settings().statement_separator().to_string();
128        let database_model = self.context.settings().database_model();
129
130        for schema in database_model.schemas() {
131            for enum_type in schema.enum_types() {
132                let enum_type_name = to_snake_case(enum_type.name());
133                let values = enum_type
134                    .values()
135                    .iter()
136                    .map(|v| format!("'{}'", escape_sql_literal(v.code())))
137                    .collect::<Vec<_>>()
138                    .join(",");
139
140                self.context.with_writer(|writer| {
141                    sql_println!(writer, "drop type if exists {} cascade{}", enum_type_name, separator);
142                    sql_println!(writer, "create type {} as enum ({}){}", enum_type_name, values, separator);
143                    sql_println!(writer, "");
144                });
145            }
146        }
147    }
148}
149
150impl SqlGenerator for PostgresGenerator {
151    fn context(&self) -> &GeneratorContext {
152        &self.context
153    }
154
155    fn output_header(&self) {
156        self.create_schemas();
157        if self.context.settings().target_postgres_version() < 18 {
158            self.create_uuid_generator_function();
159        }
160        if self.context.settings().emit_postgres_extensions() {
161            self.create_extensions();
162        }
163        self.create_enum_types();
164    }
165
166    fn output_tables(&self) {
167        self.sql_generator.output_tables();
168    }
169
170    fn output_relations(&self) {
171        self.sql_generator.output_relations();
172    }
173
174    fn output_indexes(&self) {
175        self.sql_generator.output_indexes();
176    }
177
178    fn output_triggers(&self) {
179        self.sql_generator.output_triggers();
180    }
181
182    fn output_functions(&self) {
183        self.sql_generator.output_functions();
184    }
185
186    fn output_views(&self) {
187        self.sql_generator.output_views();
188    }
189
190    fn output_procedures(&self) {
191        self.sql_generator.output_procedures();
192    }
193
194    fn output_other_sql_top(&self) {
195        self.sql_generator.output_other_sql_top();
196    }
197
198    fn output_other_sql_bottom(&self) {
199        self.sql_generator.output_other_sql_bottom();
200    }
201}
202
203#[cfg(test)]
204mod tests {
205    use super::*;
206    use crate::common::generate_options::GenerateOptions;
207    use crate::common::print_writer::PrintWriter;
208    use crate::common::sql_generator_settings::SqlGeneratorSettings;
209    use crate::common::sql_writer::SqlWriter;
210    use crate::common::test_support::SharedBuffer;
211    use schema_model::builder::SchemaBuilder;
212    use schema_model::model::database_model::DatabaseModel;
213    use schema_model::model::enum_type::{EnumType, EnumValue};
214    use schema_model::model::types::{BooleanMode, DatabaseType, ForeignKeyMode};
215    use std::cell::RefCell;
216    use std::rc::Rc;
217
218    fn make_context_with_version(model: DatabaseModel, target_postgres_version: u32) -> (GeneratorContext, SharedBuffer) {
219        make_context(model, target_postgres_version, true)
220    }
221
222    fn make_context(model: DatabaseModel, target_postgres_version: u32, emit_postgres_extensions: bool) -> (GeneratorContext, SharedBuffer) {
223        let buffer = SharedBuffer::new();
224        let mut options = GenerateOptions::new(
225            Rc::new(model),
226            Rc::new(RefCell::new(PrintWriter::new_auto_flush(Box::new(buffer.clone())))),
227        );
228        options.target_postgres_version = target_postgres_version;
229        options.emit_postgres_extensions = emit_postgres_extensions;
230        let settings = SqlGeneratorSettings::new(DatabaseType::Postgresql, &options);
231        let writer = SqlWriter::new(options.writer.clone());
232        (GeneratorContext::new(settings, writer), buffer)
233    }
234
235    #[test]
236    fn output_header_includes_uuid_function_before_postgres_18() {
237        let schema = SchemaBuilder::new(None::<&str>).build();
238        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
239        let (ctx, buffer) = make_context_with_version(model, 17);
240
241        let generator = PostgresGenerator::new(ctx);
242        generator.output_header();
243
244        let output = buffer.contents();
245        assert!(output.contains("create or replace function generate_uuid()"));
246    }
247
248    #[test]
249    fn output_header_omits_uuid_function_on_postgres_18_and_later() {
250        let schema = SchemaBuilder::new(None::<&str>).build();
251        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
252        let (ctx, buffer) = make_context_with_version(model, 18);
253
254        let generator = PostgresGenerator::new(ctx);
255        generator.output_header();
256
257        let output = buffer.contents();
258        assert!(!output.contains("create or replace function generate_uuid()"));
259    }
260
261    #[test]
262    fn output_header_renders_enum_types_when_present() {
263        let enum_type = EnumType::new(
264            "status_type",
265            vec![
266                EnumValue::new("ACTIVE", Some("A".to_string())),
267                EnumValue::new("INACTIVE", Some("I".to_string())),
268            ],
269        );
270        let schema = SchemaBuilder::new(None::<&str>).add_enum_type(enum_type).build();
271        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
272        let (ctx, buffer) = make_context_with_version(model, 18);
273
274        let generator = PostgresGenerator::new(ctx);
275        generator.output_header();
276
277        let output = buffer.contents();
278        assert!(output.contains("create type status_type as enum ('A','I')"));
279    }
280
281    #[test]
282    fn output_header_omits_enum_ddl_when_no_enums_defined() {
283        let schema = SchemaBuilder::new(None::<&str>).build();
284        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
285        let (ctx, buffer) = make_context_with_version(model, 18);
286
287        let generator = PostgresGenerator::new(ctx);
288        generator.output_header();
289
290        let output = buffer.contents();
291        assert!(!output.contains("create type"));
292    }
293
294    #[test]
295    fn output_header_creates_non_default_schemas() {
296        let default_schema = SchemaBuilder::new(None::<&str>).build();
297        let app_schema = SchemaBuilder::new(Some("app")).build();
298        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![default_schema, app_schema]);
299        let (ctx, buffer) = make_context_with_version(model, 18);
300
301        let generator = PostgresGenerator::new(ctx);
302        generator.output_header();
303
304        let output = buffer.contents();
305        assert!(output.contains("create schema if not exists app;"));
306    }
307
308    #[test]
309    fn output_header_never_creates_the_public_schema() {
310        // `public` always exists in Postgres; emitting `create schema if not exists public`
311        // is harmless but noisy, so it's skipped.
312        let schema = SchemaBuilder::new(Some("public")).build();
313        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
314        let (ctx, buffer) = make_context_with_version(model, 18);
315
316        let generator = PostgresGenerator::new(ctx);
317        generator.output_header();
318
319        let output = buffer.contents();
320        assert!(!output.contains("create schema"));
321    }
322
323    #[test]
324    fn output_header_omits_schema_creation_when_only_default_schema_present() {
325        let schema = SchemaBuilder::new(None::<&str>).build();
326        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
327        let (ctx, buffer) = make_context_with_version(model, 18);
328
329        let generator = PostgresGenerator::new(ctx);
330        generator.output_header();
331
332        let output = buffer.contents();
333        assert!(!output.contains("create schema"));
334    }
335
336    #[test]
337    fn output_header_includes_extensions_block_by_default() {
338        let schema = SchemaBuilder::new(None::<&str>).build();
339        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
340        let (ctx, buffer) = make_context_with_version(model, 18);
341
342        let generator = PostgresGenerator::new(ctx);
343        generator.output_header();
344
345        let output = buffer.contents();
346        assert!(output.contains("create extension if not exists \"citext\""));
347        assert!(output.contains("create extension if not exists \"btree_gist\""));
348    }
349
350    #[test]
351    fn output_header_includes_pgcrypto_before_postgres_18() {
352        let schema = SchemaBuilder::new(None::<&str>).build();
353        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
354        let (ctx, buffer) = make_context_with_version(model, 17);
355
356        let generator = PostgresGenerator::new(ctx);
357        generator.output_header();
358
359        let output = buffer.contents();
360        assert!(output.contains("create extension if not exists \"pgcrypto\""));
361    }
362
363    #[test]
364    fn output_header_omits_pgcrypto_on_postgres_18_and_later() {
365        let schema = SchemaBuilder::new(None::<&str>).build();
366        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
367        let (ctx, buffer) = make_context_with_version(model, 18);
368
369        let generator = PostgresGenerator::new(ctx);
370        generator.output_header();
371
372        let output = buffer.contents();
373        assert!(!output.contains("pgcrypto"));
374    }
375
376    #[test]
377    fn output_header_omits_extensions_block_when_disabled() {
378        let schema = SchemaBuilder::new(None::<&str>).build();
379        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
380        let (ctx, buffer) = make_context(model, 18, false);
381
382        let generator = PostgresGenerator::new(ctx);
383        generator.output_header();
384
385        let output = buffer.contents();
386        assert!(!output.contains("create extension"));
387    }
388
389    #[test]
390    fn output_header_checks_current_user_by_default() {
391        let schema = SchemaBuilder::new(None::<&str>).build();
392        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
393        let (ctx, buffer) = make_context_with_version(model, 18);
394
395        let generator = PostgresGenerator::new(ctx);
396        generator.output_header();
397
398        let output = buffer.contents();
399        assert!(output.contains("where usename = CURRENT_USER"));
400    }
401
402    #[test]
403    fn output_header_checks_configured_extension_user() {
404        let schema = SchemaBuilder::new(None::<&str>).build();
405        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
406        let buffer = SharedBuffer::new();
407        let mut options = GenerateOptions::new(
408            Rc::new(model),
409            Rc::new(RefCell::new(PrintWriter::new_auto_flush(Box::new(buffer.clone())))),
410        );
411        options.target_postgres_version = 18;
412        options.extension_check_user = Some("schema_admin".to_string());
413        let settings = SqlGeneratorSettings::new(DatabaseType::Postgresql, &options);
414        let writer = SqlWriter::new(options.writer.clone());
415        let ctx = GeneratorContext::new(settings, writer);
416
417        let generator = PostgresGenerator::new(ctx);
418        generator.output_header();
419
420        let output = buffer.contents();
421        assert!(output.contains("where usename = 'schema_admin'"));
422        assert!(!output.contains("CURRENT_USER)"));
423    }
424
425    #[test]
426    fn output_header_escapes_single_quote_in_configured_extension_user() {
427        // Regression test: an unescaped embedded quote would break the generated SQL
428        // string literal (or worse, let the value's content escape it).
429        let schema = SchemaBuilder::new(None::<&str>).build();
430        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
431        let buffer = SharedBuffer::new();
432        let mut options = GenerateOptions::new(
433            Rc::new(model),
434            Rc::new(RefCell::new(PrintWriter::new_auto_flush(Box::new(buffer.clone())))),
435        );
436        options.target_postgres_version = 18;
437        options.extension_check_user = Some("o'brien".to_string());
438        let settings = SqlGeneratorSettings::new(DatabaseType::Postgresql, &options);
439        let writer = SqlWriter::new(options.writer.clone());
440        let ctx = GeneratorContext::new(settings, writer);
441
442        let generator = PostgresGenerator::new(ctx);
443        generator.output_header();
444
445        let output = buffer.contents();
446        assert!(output.contains("where usename = 'o''brien'"));
447    }
448
449    #[test]
450    fn output_header_escapes_single_quote_in_enum_value_code() {
451        // Regression test: an unescaped embedded quote in an enum value's code would
452        // break the generated `create type ... as enum (...)` statement.
453        let enum_type = EnumType::new(
454            "status_type",
455            vec![EnumValue::new("PRIME", Some("O'Brien".to_string()))],
456        );
457        let schema = SchemaBuilder::new(None::<&str>).add_enum_type(enum_type).build();
458        let model = DatabaseModel::new(BooleanMode::Native, ForeignKeyMode::Relations, vec![schema]);
459        let (ctx, buffer) = make_context_with_version(model, 18);
460
461        let generator = PostgresGenerator::new(ctx);
462        generator.output_header();
463
464        let output = buffer.contents();
465        assert!(output.contains("create type status_type as enum ('O''Brien')"));
466    }
467}