Module: DynamicMigrations::Postgres::Server::Database::TriggersAndFunctionsLoader

Included in:
DynamicMigrations::Postgres::Server::Database
Defined in:
lib/dynamic_migrations/postgres/server/database/triggers_and_functions_loader.rb

Defined Under Namespace

Classes: EventTriggerProcedureSchemaMismatchError

Instance Method Summary collapse

Instance Method Details

#fetch_triggers_and_functions ⇒ Object

fetch all columns from the database and build and return a useful hash representing the triggers_and_functions of your database

this query is very fast, so does not need cached (a materialized view)



15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/dynamic_migrations/postgres/server/database/triggers_and_functions_loader.rb', line 15

def fetch_triggers_and_functions
  rows = connection.exec(<<~SQL)
    SELECT
      n.nspname AS trigger_schema,
      t.tgname AS trigger_name,
      em.text AS event_manipulation,
      n.nspname AS event_object_schema,
      c.relname AS event_object_table,
      rank() OVER (
        PARTITION BY (n.nspname),
        (c.relname),
        em.num,
        (t.tgtype & 1),
        (t.tgtype & 66)
        ORDER BY
          t.tgname
      ) AS action_order,
      CASE WHEN pg_has_role(c.relowner, 'USAGE') THEN (
        regexp_match(
          pg_get_triggerdef(t.oid),
          '.{35,} WHEN ((.+)) EXECUTE FUNCTION'
        )
      ) [1] ELSE NULL END AS action_condition,
      SUBSTRING(
        pg_get_triggerdef(t.oid)
        FROM
        '\\(([^(]+)\\)$'
      ) AS parameters,
      p_n.nspname AS function_schema,
      p.proname AS function_name,
      p.prosrc AS function_definition,
      CASE t.tgtype & 1 WHEN 1 THEN 'row' ELSE 'statement' END AS action_orientation,
      CASE t.tgtype & 66 WHEN 2 THEN 'before' WHEN 64 THEN 'instead_of' ELSE 'after' END AS action_timing,
      t.tgoldtable AS action_reference_old_table,
      t.tgnewtable AS action_reference_new_table,
      obj_description(t.oid, 'pg_trigger') as description,
      obj_description(p.oid, 'pg_proc') as function_description
    FROM
      -- trigger tables
      pg_namespace n,
      pg_class c,
      pg_trigger t,
      -- procedure tables
      pg_proc p,
      pg_namespace p_n,
      (
        VALUES
          (4, 'insert'),
          (8, 'delete'),
          (16, 'update')
      ) em(num, text)
    WHERE
      n.oid = c.relnamespace
      AND c.oid = t.tgrelid
      AND p.oid = t.tgfoid
      AND p_n.oid = p.pronamespace
      AND (t.tgtype & em.num) <> 0
      AND NOT t.tgisinternal
      AND NOT pg_is_other_temp_schema(n.oid)
      AND (
        pg_has_role(c.relowner, 'USAGE')
        OR has_table_privilege(
          c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER'
        )
        OR has_any_column_privilege(
          c.oid, 'INSERT, UPDATE, REFERENCES'
        )
      );
  SQL

  schemas = {}
  rows.each do |row|
    trigger_name = row["trigger_name"].to_sym
    event_object_schema = row["event_object_schema"].to_sym
    event_object_table = row["event_object_table"].to_sym

    schema = schemas[event_object_schema] ||= {}
    table = schema[event_object_table] ||= {}

    # By convention (and to simplify things) we place the trigger and the triggers table in the same schema
    # The function can be in a different schema (and often is, expecially for shared functions)
    unless row["trigger_schema"] == row["event_object_schema"]
      raise EventTriggerProcedureSchemaMismatchError, "Expected trigger and event_object to be in the same schema for trigger '#{trigger_name}'"
    end

    # turn the parameters into an array of strings
    # the format looks a little like this: "'hi', 'there', 'hmmm'"
    parameters = row["parameters"]&.delete("'")&.split(",")&.map(&:strip) || []

    table[trigger_name] = {
      trigger_schema: row["trigger_schema"].to_sym,
      event_manipulation: row["event_manipulation"].to_sym,
      action_order: row["action_order"].to_i,
      action_condition: row["action_condition"],
      function_schema: row["function_schema"].to_sym,
      function_name: row["function_name"].to_sym,
      function_definition: row["function_definition"],
      parameters: parameters,
      action_orientation: row["action_orientation"].to_sym,
      action_timing: row["action_timing"].to_sym,
      # `action_reference_old_table` and `action_reference_new_table` can be null
      action_reference_old_table: row["action_reference_old_table"]&.to_sym,
      action_reference_new_table: row["action_reference_new_table"]&.to_sym,
      description: row["description"],
      function_description: row["function_description"]
    }
  end
  schemas
end