I recently incorporated db-triggers with a Rails app to maintain some counts that were otherwise fairly expensive to retrieve. Rails wasn’t super-pumped about the idea (what with the “keep all the logic in the app” approach and all), but sometimes… you know… you know better than your framework.
Some things I was aiming for:
- Set them up with normal migrations.
- Test them with the normal test suite/normal fixtures.
- Make recovery/reset simple for when the table (inevitably) is somehow out of sync.
The “frequent counts” table
I’ll have multiple counts but not TOO many — enough that I don’t want to have a column per count but not enough that I mind using “LIKE” to lookup patterns, so my table has: id, code, current_count.
Code will be a unique key (important later) and be formatted like “style_ABC_size_456″.
So, when a new item is added it’ll be associated with a style and some sizes – each combination will either need to be setup (with a current_count = 1) or an existing combo will be found and +=1.
The FrequentCount class has the fairly straightforward finders that you’d expect + methods to reset each of the counts that it contains. The reset methods follow the pattern “reset_frequent_count_COUNT_NAME” -> they clear the existing counts that they maintain before repopulating them.
I also threw in a reset_all method that looks for anything on the class following the “reset_frequent_count_COUNT_NAME” pattern and runs them.
The trigger-SQL
The SQL for creating the triggers will be needed by the migration as well as the test suite. In fact, the test suite will need to run them somewhat often due to the way the standard tests ”prepare” the database.
I ended up throwing it in lib/trigger_sql.rb. Methods there are named with the pattern “sql_for_TABLE_OPERATION_TRIGGER_NAME” ex: sql_for_items_insert_style_and_size
create trigger items_insert_style_and_size after insert on items
for each row
begin
insert into frequent_counts(code, current_count)
values (concat(‘style_’, new.style, ‘_size_’, new.size), 1)
on duplicate key update current_count = current_count + 1;
end;
TriggerSql.connection.execute(TriggerSql.sql_for_items_insert_items_by_style_and_size)
# find methods that follow our pattern of “methods providing trigger sql” and execute the contents of eachTriggerSql.methods.select{ |m| m =~ /sql_for_.+/ }.each do |method_name|ActiveRecord::Base.connection.execute(TriggerSql.send(method_name))end
