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.



219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 219

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

.extension_names(sql) ⇒ Object

Every extension name a dump installs, in the order the CREATE EXTENSION statements appear. Used to report which of them #strip_extensions is about to drop; run it on the raw dump, before any wrapping pass.



176
177
178
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 176

def extension_names(sql)
  sql.scan(CREATE_EXTENSION_RE).map { |(name)| name.delete('"') }
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_extensions(sql, names) ⇒ Object

Drop every trace of the extensions named in names — the ones a managed PostgreSQL platform installs to run the source instance itself (see PostgresqlAdapter::PLATFORM_MANAGED_EXTENSIONS). Removed rather than merely wrapped like any other extension, because they cannot be created anywhere outside that platform, so keeping them only leaves a restore-time WARNING and objects the target will never have.

Three things are emitted per extension and all three must go, or a leftover references a name that is no longer installed: pg_dump's -- Name: <ext>; Type: EXTENSION header block, the CREATE EXTENSION, and the COMMENT ON EXTENSION (whose own header reads -- Name: EXTENSION <ext>; Type: COMMENT). Run this on the raw dump, before the wrapping passes: they rewrite the bare statements this matches into DO blocks.



193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
 
# File 'lib/exwiw/ddl_postprocessor.rb', line 193

def strip_extensions(sql, names)
  return sql if names.empty?

  # Whole names only: each match is anchored by the whitespace/quote before it
  # and a \b after, so `google_vacuum_mgmt` never matches an extension merely
  # containing it (`not_google_vacuum_mgmt`, `google_vacuum_mgmt_v2`).
  names_re = Regexp.union(names.map { |n| Regexp.escape(n) })
  name = /(?:"#{names_re}"|#{names_re}\b)/

  # Each removal takes the blank lines that trailed the object too, so the
  # surrounding statements keep pg_dump's spacing instead of gaining a gap.
  trailing_blank_lines = /(?:[ \t]*\r?\n(?:[ \t]*\r?\n)*)?/

  sql = sql.gsub(
    /^--\r?\n-- Name: (?:EXTENSION\s+)?#{name};[ \t]*Type:[ \t]*(?:EXTENSION|COMMENT);[^\n]*\n--(?:\r?\n)+/i,
    "",
  )
  sql = sql.gsub(/^[ \t]*CREATE\s+EXTENSION\b(?:\s+IF\s+NOT\s+EXISTS)?\s+#{name}[^;]*;#{trailing_blank_lines}/i, "")
  sql.gsub(
    /^[ \t]*COMMENT\s+ON\s+EXTENSION\s+#{name}\s+IS\s+(?:'(?:[^']|'')*'|NULL)\s*;#{trailing_blank_lines}/i,
    "",
  )
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