Module: Exwiw::DdlPostprocessor

Defined in:
lib/exwiw/ddl_postprocessor.rb

Overview

Rewrites raw CREATE statements emitted by mysqldump / pg_dump / sqlite_master.sql into idempotent forms so the generated insert-000-schema.sql file can be re-applied without error.

Constant Summary collapse

ADD_CONSTRAINT_RE =

ALTER TABLE ... ADD CONSTRAINT ...; is not idempotent on its own. PostgreSQL's PL/pgSQL has no IF-NOT-EXISTS clause for ADD CONSTRAINT, so wrap each statement in a DO block that swallows duplicate_object. Matches only statements whose ALTER TABLE clause leads directly into ADD CONSTRAINT (no intervening ALTER COLUMN / DROP / etc) so that unrelated ALTER TABLE statements in the same dump are not absorbed.

/^[ \t]*ALTER\s+TABLE\s+(?:ONLY\s+)?[^\s;,]+\s+(?:\n[ \t]*)?ADD\s+CONSTRAINT\b[^;]*;/m.freeze
CREATE_TYPE_ENUM_RE =

A bare CREATE TYPE ... AS ENUM (...) (as a full-database pg_dump emits, unlike a --table dump, which omits enum types) is not idempotent: a second restore raises duplicate_object. Wrap each in a DO block that swallows that error, matching the form of #create_type_enum_statements. Enum labels never contain a semicolon or an unescaped ), so the match ends at the first ); after AS ENUM (.

/^[ \t]*CREATE\s+TYPE\b.+?\bAS\s+ENUM\s*\(.+?\)\s*;/mi.freeze
CREATE_EXTENSION_RE =

A bare CREATE EXTENSION ...; (as a full-database pg_dump emits, unlike a --table dump, which omits extensions) has no graceful skip: a restore target that cannot create the extension aborts the whole restore. Wrap each in a DO block that catches only the two "cannot provide it here" cases — feature_not_supported (0A000, binaries absent) and invalid_schema_name (3F000, required schema absent) — and re-raises them as a WARNING so the skip surfaces in the restore logs. insufficient_ privilege (42501) is deliberately NOT caught: a restore role lacking CREATE privilege is a misconfiguration to fix, not to skip silently.

/^[ \t]*CREATE\s+EXTENSION\b(?:\s+IF\s+NOT\s+EXISTS)?\s+(?<name>"[^"]+"|[^\s;]+)[^;]*;/i.freeze

Class Method Summary collapse

Class Method Details

.add_if_not_exists_to_create_index(sql) ⇒ Object

CREATE [UNIQUE] INDEX [name] → CREATE [UNIQUE] INDEX IF NOT EXISTS [name]. Use only for databases that support it (PostgreSQL, SQLite). MySQL does NOT support CREATE INDEX IF NOT EXISTS — do not call from the MySQL adapter.



21
22
23
24
25
26
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 21

def add_if_not_exists_to_create_index(sql)
  sql.gsub(/\bCREATE(\s+UNIQUE)?\s+INDEX\b(?!\s+IF\s+NOT\s+EXISTS)/i) do
    unique = Regexp.last_match(1) || ""
    "CREATE#{unique} INDEX IF NOT EXISTS"
  end
end

.add_if_not_exists_to_create_schema(sql) ⇒ Object

CREATE SCHEMA [name] → CREATE SCHEMA IF NOT EXISTS [name].



29
30
31
32
33
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 29

def add_if_not_exists_to_create_schema(sql)
  sql.gsub(/\bCREATE\s+SCHEMA\b(?!\s+IF\s+NOT\s+EXISTS)/i) do |m|
    "#{m} IF NOT EXISTS"
  end
end

.add_if_not_exists_to_create_sequence(sql) ⇒ Object

CREATE SEQUENCE [name] → CREATE SEQUENCE IF NOT EXISTS [name].



36
37
38
39
40
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 36

def add_if_not_exists_to_create_sequence(sql)
  sql.gsub(/\bCREATE\s+SEQUENCE\b(?!\s+IF\s+NOT\s+EXISTS)/i) do |m|
    "#{m} IF NOT EXISTS"
  end
end

.add_if_not_exists_to_create_table(sql) ⇒ Object

CREATE TABLE [name] → CREATE TABLE IF NOT EXISTS [name]. TEMP / TEMPORARY variants and already-IF-NOT-EXISTS lines are skipped.



12
13
14
15
16
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 12

def add_if_not_exists_to_create_table(sql)
  sql.gsub(/\bCREATE\s+TABLE\b(?!\s+IF\s+NOT\s+EXISTS)/i) do |m|
    "#{m} IF NOT EXISTS"
  end
end

.create_type_enum_statements(enum_types) ⇒ Object

Generate idempotent CREATE TYPE ... AS ENUM statements. enum_types is an Array of Hashes with keys :schema, :name, :labels.



111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 111

def create_type_enum_statements(enum_types)
  return "" if enum_types.empty?

  stmts = enum_types.map do |t|
    qualified_name = "\"#{t[:schema]}\".\"#{t[:name]}\""
    labels_sql = t[:labels].map { |l| "'#{l.gsub("'", "''")}'" }.join(', ')
    <<~SQL.chomp
      DO $exwiw$ BEGIN
        CREATE TYPE #{qualified_name} AS ENUM (#{labels_sql});
      EXCEPTION WHEN duplicate_object THEN NULL;
      END $exwiw$;
    SQL
  end

  stmts.join("\n\n") + "\n\n"
end

.strip_triggers(sql) ⇒ Object

pg_dump --table includes triggers but not the referenced function definitions, causing UndefinedFunction errors on the target DB.



63
64
65
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 63

def strip_triggers(sql)
  sql.gsub(/^[ \t]*CREATE\s+(?:OR\s+REPLACE\s+)?(?:CONSTRAINT\s+)?TRIGGER\b[^;]*;\r?\n?/i, "")
end

.wrap_add_constraint_in_do_block(sql) ⇒ Object 



50
51
52
53
54
55
56
57
58
59
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 50

def wrap_add_constraint_in_do_block(sql)
  sql.gsub(ADD_CONSTRAINT_RE) do |stmt|
    <<~SQL.chomp
      DO $exwiw$ BEGIN
        #{stmt.strip}
      EXCEPTION WHEN duplicate_object THEN NULL;
      END $exwiw$;
    SQL
  end
end

.wrap_create_extension_in_do_block(sql) ⇒ Object 



97
98
99
100
101
102
103
104
105
106
107
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 97

def wrap_create_extension_in_do_block(sql)
  sql.gsub(CREATE_EXTENSION_RE) do
    stmt = Regexp.last_match(0).strip
    extname = Regexp.last_match(:name).delete('"')
    warning = "exwiw: skipped CREATE EXTENSION #{extname} (SQLSTATE %): %"
    warning_literal = "'#{warning.gsub("'", "''")}'"
    "DO $$ BEGIN #{stmt} " \
      "EXCEPTION WHEN feature_not_supported OR invalid_schema_name THEN " \
      "RAISE WARNING #{warning_literal}, SQLSTATE, SQLERRM; END $$;"
  end
end

.wrap_create_type_enum_in_do_block(sql) ⇒ Object 



75
76
77
78
79
80
81
82
83
84
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 75

def wrap_create_type_enum_in_do_block(sql)
  sql.gsub(CREATE_TYPE_ENUM_RE) do |stmt|
    <<~SQL.chomp
      DO $exwiw$ BEGIN
        #{stmt.strip}
      EXCEPTION WHEN duplicate_object THEN NULL;
      END $exwiw$;
    SQL
  end
end