module PgSnippets::V1

Constants

CHANGE_ID
CREATED_AT
SEQUENCE
UPDATED_AT

Public Class Methods

add_change_column_index(table_name, change_column_name: CHANGE_ID) click to toggle source
# File lib/pg_snippets/v1.rb, line 35
      def add_change_column_index(table_name, change_column_name: CHANGE_ID)
"create index on #{table_name}(#{change_column_name});"
      end
add_metadata_fields(table_name, change_column_name: CHANGE_ID, updated_at_column_name: UPDATED_AT, created_at_column_name: CREATED_AT) click to toggle source
# File lib/pg_snippets/v1.rb, line 28
      def add_metadata_fields(table_name, change_column_name: CHANGE_ID, updated_at_column_name: UPDATED_AT, created_at_column_name: CREATED_AT)
"alter table #{table_name}
add column #{change_column_name} bigint
,add column #{updated_at_column_name} timestamp
,add column #{created_at_column_name} timestamp;"
      end
add_suppress_redundant_updates_trigger(table_name) click to toggle source
# File lib/pg_snippets/v1.rb, line 39
      def add_suppress_redundant_updates_trigger(table_name)
"create trigger z_suppress_redundant_updates_on_#{table_name}
before update on #{table_name}
for each row execute procedure suppress_redundant_updates_trigger();"
      end
add_update_metadata_trigger(table_name) click to toggle source
# File lib/pg_snippets/v1.rb, line 45
      def add_update_metadata_trigger(table_name)
"create trigger zz_update_metadata_on_#{table_name}
before insert or update on #{table_name}
for each row execute procedure update_metadata();"
      end
create_sequence(name=SEQUENCE) click to toggle source
# File lib/pg_snippets/v1.rb, line 9
def create_sequence(name=SEQUENCE)
  "create sequence #{name};"
end
create_update_metadata_function(change_column_name: CHANGE_ID, sequence_name: SEQUENCE, updated_at_column_name: UPDATED_AT, created_at_column_name: CREATED_AT) click to toggle source
# File lib/pg_snippets/v1.rb, line 13
      def create_update_metadata_function(change_column_name: CHANGE_ID, sequence_name: SEQUENCE, updated_at_column_name: UPDATED_AT, created_at_column_name: CREATED_AT)
"create function update_metadata() returns trigger as $$
begin
  NEW.#{change_column_name} := nextval('#{sequence_name}');
  NEW.#{updated_at_column_name} := now();
  NEW.#{created_at_column_name} := now();
  return NEW;
end;
$$  language plpgsql;"
      end
setup_db() click to toggle source
# File lib/pg_snippets/v1.rb, line 24
def setup_db
  create_sequence + create_update_metadata_function
end
setup_table(table_name) click to toggle source
# File lib/pg_snippets/v1.rb, line 51
def setup_table(table_name)
  add_metadata_fields(table_name) + add_change_column_index(table_name) + add_suppress_redundant_updates_trigger(table_name) + add_update_metadata_trigger(table_name)
end