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