Module: Sequel::Postgres::DatabaseMethods

Defined in:
lib/sequel_postgresql_triggers.rb

Overview

Add the pgt_* methods so that any Sequel database connecting to PostgreSQL can use them. All of these methods require the plpgsql procedural language be added to the PostgreSQL database before they can be used. On PostgreSQL 9.0 and later versions, it is installed by default. For older versions, you can install it with:

DB.create_language(:plpgsql)

All of the public methods take the following options in their opts hash:

  • :function_name: The name of the function to use. This is important to specify if you want an easy way to drop the function.

  • :trigger_name: The name of the trigger to use. This is important to specify if you want an easy way to drop the trigger.

Instance Method Summary collapse

Instance Method Details

#pgt_counter_cache(main_table, main_table_id_column, counter_column, counted_table, counted_table_id_column, opts = {}) ⇒ Object

Turns a column in the main table into a counter cache. A counter cache is a column in the main table with the number of rows in the counted table for the matching id. Arguments:

  • main_table : name of table holding counter cache column

  • main_table_id_column : column in main table matching counted_table_id_column in counted_table

  • counter_column : column in main table containing the counter cache

  • counted_table : name of table being counted

  • counted_table_id_column : column in counted_table matching main_table_id_column in main_table

  • opts : option hash, see module documentation



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
 
# File 'lib/sequel_postgresql_triggers.rb', line 27

def pgt_counter_cache(main_table, main_table_id_column, counter_column, counted_table, counted_table_id_column, opts={})
  trigger_name = opts[:trigger_name] || "pgt_cc_#{main_table}__#{main_table_id_column}__#{counter_column}__#{counted_table_id_column}"
  function_name = opts[:function_name] || "pgt_cc_#{main_table}__#{main_table_id_column}__#{counter_column}__#{counted_table}__#{counted_table_id_column}"

  table = quote_schema_table(main_table)
  id_column = quote_identifier(counted_table_id_column)
  main_column = quote_identifier(main_table_id_column)
  count_column = quote_identifier(counter_column)

  pgt_trigger(counted_table, trigger_name, function_name, [:insert, :update, :delete], <<-SQL)
  BEGIN
    IF (TG_OP = 'UPDATE' AND (NEW.#{id_column} = OLD.#{id_column} OR (OLD.#{id_column} IS NULL AND NEW.#{id_column} IS NULL))) THEN
      RETURN NEW;
    ELSE
      IF ((TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.#{id_column} IS NOT NULL) THEN
        UPDATE #{table} SET #{count_column} = #{count_column} + 1 WHERE #{main_column} = NEW.#{id_column};
      END IF;
      IF ((TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND OLD.#{id_column} IS NOT NULL) THEN
        UPDATE #{table} SET #{count_column} = #{count_column} - 1 WHERE #{main_column} = OLD.#{id_column};
      END IF;
    END IF;

    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    END IF;
    RETURN NEW;
  END;
  SQL
end

#pgt_created_at(table, column, opts = {}) ⇒ Object

Turns a column in the table into a created at timestamp column, which always contains the timestamp the record was inserted into the database. Arguments:

  • table : name of table

  • column : column in table that should be a created at timestamp column

  • opts : option hash, see module documentation



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'lib/sequel_postgresql_triggers.rb', line 63

def pgt_created_at(table, column, opts={})
  trigger_name = opts[:trigger_name] || "pgt_ca_#{column}"
  function_name = opts[:function_name] || "pgt_ca_#{table}__#{column}"
  col = quote_identifier(column)
  pgt_trigger(table, trigger_name, function_name, [:insert, :update], <<-SQL)
  BEGIN
    IF (TG_OP = 'UPDATE') THEN
      NEW.#{col} := OLD.#{col};
    ELSIF (TG_OP = 'INSERT') THEN
      NEW.#{col} := CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
  END;
  SQL
end

#pgt_immutable(table, *columns) ⇒ Object

Makes all given columns in the given table immutable, so an exception is raised if there is an attempt to modify the value when updating the record. Arguments:

  • table : name of table

  • columns : All columns in the table that should be immutable. Can end with a hash of options, see module documentation.



84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/sequel_postgresql_triggers.rb', line 84

def pgt_immutable(table, *columns)
  opts = columns.last.is_a?(Hash) ? columns.pop : {}
  trigger_name = opts[:trigger_name] || "pgt_im_#{columns.join('__')}"
  function_name = opts[:function_name] || "pgt_im_#{columns.join('__')}"
  ifs = columns.map do |c|
    old = "OLD.#{quote_identifier(c)}"
    new = "NEW.#{quote_identifier(c)}"
    <<-END
      IF #{new} IS DISTINCT FROM #{old} THEN
          RAISE EXCEPTION 'Attempted #{c} update: Old: %, New: %', #{old}, #{new};
      END IF;
    END
  end.join("\n")
  pgt_trigger(table, trigger_name, function_name, :update, "BEGIN #{ifs} RETURN NEW; END;")
end

#pgt_sum_cache(main_table, main_table_id_column, sum_column, summed_table, summed_table_id_column, summed_column, opts = {}) ⇒ Object

Turns a column in the main table into a sum cache. A sum cache is a column in the main table with the sum of a column in the summed table for the matching id. Arguments:

  • main_table : name of table holding counter cache column

  • main_table_id_column : column in main table matching counted_table_id_column in counted_table

  • sum_column : column in main table containing the sum cache

  • summed_table : name of table being summed

  • summed_table_id_column : column in summed_table matching main_table_id_column in main_table

  • summed_column : column in summed_table being summed

  • opts : option hash, see module documentation



110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
 
# File 'lib/sequel_postgresql_triggers.rb', line 110

def pgt_sum_cache(main_table, main_table_id_column, sum_column, summed_table, summed_table_id_column, summed_column, opts={})
  trigger_name = opts[:trigger_name] || "pgt_sc_#{main_table}__#{main_table_id_column}__#{sum_column}__#{summed_table_id_column}"
  function_name = opts[:function_name] || "pgt_sc_#{main_table}__#{main_table_id_column}__#{sum_column}__#{summed_table}__#{summed_table_id_column}__#{summed_column}"

  table = quote_schema_table(main_table)
  id_column = quote_identifier(summed_table_id_column)
  summed_column = quote_identifier(summed_column)
  main_column = quote_identifier(main_table_id_column)
  sum_column = quote_identifier(sum_column)

  pgt_trigger(summed_table, trigger_name, function_name, [:insert, :delete, :update], <<-SQL)
  BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.#{id_column} = OLD.#{id_column}) THEN
      UPDATE #{table} SET #{sum_column} = #{sum_column} + NEW.#{summed_column} - OLD.#{summed_column} WHERE #{main_column} = NEW.#{id_column};
    ELSE
      IF ((TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.#{id_column} IS NOT NULL) THEN
        UPDATE #{table} SET #{sum_column} = #{sum_column} + NEW.#{summed_column} WHERE #{main_column} = NEW.#{id_column};
      END IF;
      IF ((TG_OP = 'DELETE' OR TG_OP = 'UPDATE') AND OLD.#{id_column} IS NOT NULL) THEN
        UPDATE #{table} SET #{sum_column} = #{sum_column} - OLD.#{summed_column} WHERE #{main_column} = OLD.#{id_column};
      END IF;
    END IF;
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    END IF;
    RETURN NEW;
  END;
  SQL
end

#pgt_sum_through_many_cache(opts = {}) ⇒ Object

Turns a column in the main table into a sum cache through a join table. A sum cache is a column in the main table with the sum of a column in the summed table for the matching id. The join table must have NOT NULL constraints on the foreign keys to the main table and summed table and a composite unique constraint on both foreign keys.

Arguments:

  • opts : option hash, see module documentation, and below.

  • :main_table: name of table holding sum cache column

  • :main_table_id_column: primary key column in main table referenced by main_table_fk_column (default: :id)

  • :sum_column: column in main table containing the sum cache, must be NOT NULL and default to 0

  • :summed_table: name of table being summed

  • :summed_table_id_column: primary key column in summed_table referenced by summed_table_fk_column (default: ;id)

  • :summed_column: column in summed_table being summed, must be NOT NULL

  • :join_table: name of table which joins main_table with summed_table

  • :main_table_fk_column: column in join_table referencing main_table_id_column, must be NOT NULL

  • :summed_table_fk_column: column in join_table referencing summed_table_id_column, must be NOT NULL



157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
 
# File 'lib/sequel_postgresql_triggers.rb', line 157

def pgt_sum_through_many_cache(opts={})
  main_table = opts.fetch(:main_table)
  main_table_id_column = opts.fetch(:main_table_id_column, :id)
  sum_column = opts.fetch(:sum_column)
  summed_table = opts.fetch(:summed_table)
  summed_table_id_column = opts.fetch(:summed_table_id_column, :id)
  summed_column = opts.fetch(:summed_column)
  join_table = opts.fetch(:join_table)
  main_table_fk_column = opts.fetch(:main_table_fk_column)
  summed_table_fk_column = opts.fetch(:summed_table_fk_column)

  trigger_name = opts[:trigger_name] || "pgt_stmc_#{main_table}__#{main_table_id_column}__#{sum_column}__#{summed_table_id_column}__#{summed_column}__#{main_table_fk_column}__#{summed_table_fk_column}"
  function_name = opts[:function_name] || "pgt_stmc_#{main_table}__#{main_table_id_column}__#{sum_column}__#{summed_table}__#{summed_table_id_column}__#{summed_column}__#{join_table}__#{main_table_fk_column}__#{summed_table_fk_column}"
  join_trigger_name = opts[:join_trigger_name] || "pgt_stmc_join_#{main_table}__#{main_table_id_column}__#{sum_column}__#{summed_table_id_column}__#{summed_column}__#{main_table_fk_column}__#{summed_table_fk_column}"
  join_function_name = opts[:join_function_name] || "pgt_stmc_join_#{main_table}__#{main_table_id_column}__#{sum_column}__#{summed_table}__#{summed_table_id_column}__#{summed_column}__#{join_table}__#{main_table_fk_column}__#{summed_table_fk_column}"

  orig_summed_table = summed_table
  orig_join_table = join_table

  main_table = quote_schema_table(main_table)
  main_table_id_column = quote_schema_table(main_table_id_column)
  sum_column = quote_schema_table(sum_column)
  summed_table = quote_schema_table(summed_table)
  summed_table_id_column = quote_schema_table(summed_table_id_column)
  summed_column = quote_schema_table(summed_column)
  join_table = quote_schema_table(join_table)
  main_table_fk_column = quote_schema_table(main_table_fk_column)
  summed_table_fk_column = quote_schema_table(summed_table_fk_column)

  pgt_trigger(orig_summed_table, trigger_name, function_name, [:insert, :delete, :update], <<-SQL)
  BEGIN
    IF (TG_OP = 'UPDATE' AND NEW.#{summed_table_id_column} = OLD.#{summed_table_id_column}) THEN
      UPDATE #{main_table} SET #{sum_column} = #{sum_column} + NEW.#{summed_column} - OLD.#{summed_column} WHERE #{main_table_id_column} IN (SELECT #{main_table_fk_column} FROM #{join_table} WHERE #{summed_table_fk_column} = NEW.#{summed_table_id_column});
    ELSE
      IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        UPDATE #{main_table} SET #{sum_column} = #{sum_column} + NEW.#{summed_column} WHERE #{main_table_id_column} IN (SELECT #{main_table_fk_column} FROM #{join_table} WHERE #{summed_table_fk_column} = NEW.#{summed_table_id_column});
      END IF;
      IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
        UPDATE #{main_table} SET #{sum_column} = #{sum_column} - OLD.#{summed_column} WHERE #{main_table_id_column} IN (SELECT #{main_table_fk_column} FROM #{join_table} WHERE #{summed_table_fk_column} = OLD.#{summed_table_id_column});
      END IF;
    END IF;
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    END IF;
    RETURN NEW;
  END;
  SQL

  pgt_trigger(orig_join_table, join_trigger_name, join_function_name, [:insert, :delete, :update], <<-SQL)
  BEGIN
    IF (NOT (TG_OP = 'UPDATE' AND NEW.#{main_table_fk_column} = OLD.#{main_table_fk_column} AND NEW.#{summed_table_fk_column} = OLD.#{summed_table_fk_column})) THEN
      IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
        UPDATE #{main_table} SET #{sum_column} = #{sum_column} + (SELECT #{summed_column} FROM #{summed_table} WHERE #{summed_table_id_column} = NEW.#{summed_table_fk_column}) WHERE #{main_table_id_column} = NEW.#{main_table_fk_column};
      END IF;
      IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
        UPDATE #{main_table} SET #{sum_column} = #{sum_column} - (SELECT #{summed_column} FROM #{summed_table} WHERE #{summed_table_id_column} = OLD.#{summed_table_fk_column}) WHERE #{main_table_id_column} = OLD.#{main_table_fk_column};
      END IF;
    END IF;
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    END IF;
    RETURN NEW;
  END;
  SQL
end

#pgt_touch(main_table, touch_table, column, expr, opts = {}) ⇒ Object

When rows in a table are updated, touches a timestamp of related rows in another table. Arguments:

  • main_table : name of table that is being watched for changes

  • touch_table : name of table that needs to be touched

  • column : name of timestamp column to be touched

  • expr : hash or array that represents the columns that define the relationship

  • opts : option hash, see module documentation



231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
 
# File 'lib/sequel_postgresql_triggers.rb', line 231

def pgt_touch(main_table, touch_table, column, expr, opts={})
  trigger_name = opts[:trigger_name] || "pgt_t_#{main_table}__#{touch_table}"
  function_name = opts[:function_name] || "pgt_t_#{main_table}__#{touch_table}"
  cond = lambda{|source| expr.map{|k,v| "#{quote_identifier(k)} = #{source}.#{quote_identifier(v)}"}.join(" AND ")}
  same_id = expr.map{|k,v| "NEW.#{quote_identifier(v)} = OLD.#{quote_identifier(v)}"}.join(" AND ")

  table = quote_schema_table(touch_table)
  col = quote_identifier(column)
  update = lambda{|source| " UPDATE #{table} SET #{col} = CURRENT_TIMESTAMP WHERE #{cond[source]} AND ((#{col} <> CURRENT_TIMESTAMP) OR (#{col} IS NULL));"}

  sql = <<-SQL
    BEGIN
      IF (TG_OP = 'UPDATE' AND (#{same_id})) THEN
        #{update['NEW']}
      ELSE
        IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
          #{update['NEW']}
        END IF;
        IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN
          #{update['OLD']}
        END IF;
      END IF;

      IF (TG_OP = 'DELETE') THEN
        RETURN OLD;
      END IF;
      RETURN NEW;
    END;
  SQL
  pgt_trigger(main_table, trigger_name, function_name, [:insert, :delete, :update], sql, :after=>true)
end

#pgt_updated_at(table, column, opts = {}) ⇒ Object

Turns a column in the table into a updated at timestamp column, which always contains the timestamp the record was inserted or last updated. Arguments:

  • table : name of table

  • column : column in table that should be a updated at timestamp column

  • opts : option hash, see module documentation



269
270
271
272
273
274
275
276
277
278
 
# File 'lib/sequel_postgresql_triggers.rb', line 269

def pgt_updated_at(table, column, opts={})
  trigger_name = opts[:trigger_name] || "pgt_ua_#{column}"
  function_name = opts[:function_name] || "pgt_ua_#{table}__#{column}"
  pgt_trigger(table, trigger_name, function_name, [:insert, :update], <<-SQL)
  BEGIN
    NEW.#{quote_identifier(column)} := CURRENT_TIMESTAMP;
    RETURN NEW;
  END;
  SQL
end