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
DEFINER_ACCOUNT_PART =

A user@host pair as mysqldump writes it. Each side is independently a backtick-quoted identifier (doubled backticks escape), a single- or double-quoted string, or a bare word (root@localhost). A definer may also be CURRENT_USER / CURRENT_USER(), which has no @host.

/(?:`(?:[^`]|``)*`|'(?:[^']|'')*'|"(?:[^"]|"")*"|[A-Za-z0-9_$.%\-]+)/.freeze
DEFINER_CLAUSE = 
/DEFINER[ \t]*=[ \t]*
       (?:CURRENT_USER(?:[ \t]*\([ \t]*\))?
|#{DEFINER_ACCOUNT_PART}(?:@#{DEFINER_ACCOUNT_PART})?)/xi.freeze
DEFINER_COMMENT_RE =

mysqldump wraps every view/trigger/routine/event DEFINER in a versioned comment, e.g. /*!50013 DEFINER=u@h SQL SECURITY DEFINER */ or the trigger form /*!50017 DEFINER=u@h*/. From MySQL 8.2, creating a stored object owned by another account needs SET_ANY_DEFINER, which managed MySQL (RDS / Cloud SQL) does not grant, and a nonexistent definer leaves an orphan object that later breaks CREATE USER / DROP USER. Omitting the clause defaults the definer to CURRENT_USER (the restoring account), sidestepping both.

Anchoring on the versioned comment, rather than gsubbing DEFINER= anywhere, keeps this from touching a literal "DEFINER=" inside a trigger body string or a column COMMENT. SQL SECURITY DEFINER|INVOKER is preserved — it governs runtime privileges, not who the definer is.

%r{/\*!(?<ver>\d{5})[ \t]+#{DEFINER_CLAUSE}[ \t]*
(?<rest>(?:(?!\*/).)*?)[ \t]*\*/(?<trail>[ \t]*)}xi.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 "cannot provide it here" cases and re-raises them as a WARNING so the skip surfaces in the restore logs:

- feature_not_supported (0A000, binaries absent)
- invalid_schema_name (3F000, required schema absent)
- internal_error (XX000): an extension that must be preloaded via
shared_preload_libraries raises a bare `elog(ERROR, "<name> is not in
shared_preload_libraries")` (default SQLSTATE XX000) when it is not
preloaded on the target — e.g. pglogical restored into a plain RDS. Like
the other two, this is the target being unable to provide the extension,
not a broken dump, so the restore should skip it rather than abort.

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
COMMENT_ON_EXTENSION_RE =

pg_dump emits COMMENT ON EXTENSION <name> IS '...'; right after the matching CREATE EXTENSION. When the CREATE was skipped (its DO block caught feature_not_supported because the target cannot provide the extension — e.g. AlloyDB's google_vacuum_mgmt restored into vanilla PostgreSQL), this bare COMMENT then aborts the whole restore with undefined_object (42704, "extension ... does not exist"). Wrap each COMMENT in a DO block that swallows undefined_object, so it applies when the extension exists and is a no-op when it was skipped. The IS clause is matched as a whole single-quoted string (doubled quotes escaped) or NULL, so an embedded ; does not end the match early.

/^[ \t]*COMMENT\s+ON\s+EXTENSION\s+(?:"[^"]+"|[^\s]+)\s+IS\s+(?:'(?:[^']|'')*'|NULL)\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.



175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 175

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_definer_clauses(sql) ⇒ Object 



96
97
98
99
100
101
102
103
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 96

def strip_definer_clauses(sql)
  sql.gsub(DEFINER_COMMENT_RE) do
    m = Regexp.last_match
    # Drop the comment whole if DEFINER was its only content (trigger
    # form); otherwise keep the surviving content (e.g. SQL SECURITY).
    m[:rest].empty? ? "" : "/*!#{m[:ver]} #{m[:rest]} */#{m[:trail]}"
  end
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_comment_on_extension_in_do_block(sql) ⇒ Object 



166
167
168
169
170
171
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 166

def wrap_comment_on_extension_in_do_block(sql)
  sql.gsub(COMMENT_ON_EXTENSION_RE) do
    stmt = Regexp.last_match(0).strip
    "DO $exwiw$ BEGIN #{stmt} EXCEPTION WHEN undefined_object THEN NULL; END $exwiw$;"
  end
end

.wrap_create_extension_in_do_block(sql) ⇒ Object 



141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 141

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 OR internal_error THEN " \
      "RAISE WARNING #{warning_literal}, SQLSTATE, SQLERRM; END $$;"
  end
end

.wrap_create_type_enum_in_do_block(sql) ⇒ Object 



113
114
115
116
117
118
119
120
121
122
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 113

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