Sequel PostgreSQL Triggers

Sequel PostgreSQL Triggers is a small enhancement to Sequel allowing a user to easily handle the following types of columns:

  • Timestamp Columns (Created At/Updated At)

  • Counter/Sum Caches

  • Immutable Columns

It handles these internally to the database via triggers, so even if other applications access the database (without using Sequel), things will still work (unless the database superuser disables triggers).

To use any of these methods before PostgreSQL 9.0, you have to add the plpgsql procedural language to PostgreSQL, which you can do with:

DB.create_language(:plpgsql)

Triggers

Created At Columns - pgt_created_at

pgt_created_at takes the table and column given and makes it so that upon insertion, the column is set to the CURRENT_TIMESTAMP, and that upon update, the column’s value is always set to the previous value. This is sort of like an immutable column, but it doesn’t bring up an error if you try to change it, it just ignores it.

Updated At Columns - pgt_updated_at

Similar to pgt_created_at, takes a table and column and makes it so that upon insertion, the column is set to CURRENT_TIMESTAMP. It differs that upon update, the column is also set to CURRENT_TIMESTAMP.

Counter Cache - pgt_counter_cache

This takes quite a few arguments (see the RDoc) and sets up a counter cache so that when the counted table is inserted to or deleted from, records in the main table are updated with the count of the corresponding records in the counted table. The counter cache column must have a default of 0 for this to work correctly.

Sum Cache - pgt_sum_cache

Similar to pgt_counter_cache, except instead of storing a count of records in the main table, it stores the sum on one of the columns in summed table. The sum cache column must have a default of 0 for this to work correctly.

Sum Through Many Cache - pgt_sum_through_many_cache

Similar to pgt_sum_cache, except instead of a one-to-many relationship, it supports a many-to-many relationship with a single join table. The sum cache column must have a default of 0 for this to work correctly.

Immutable Columns - pgt_immutable

This takes a table name and one or more column names, and adds an update trigger that raises an exception if you try to modify the value of any of the columns.

Touch Propagation - pgt_touch

This takes several arguments (again, see the RDoc) and sets up a trigger that watches one table for changes, and touches timestamps of related rows in a separate table.

License

This library is released under the MIT License. See the MIT-LICENSE file for details.

Author

Jeremy Evans <[email protected]>