Rails,PostgreSQL和历史触发器 [英] Rails, PostgreSQL, and History Triggers
问题描述
SO ...
我正在通过类似...的方式添加由历史记录表填充的触发器,以便在我的项目中进行审核。
I am adding history tables populated by triggers for auditing in my project via something like...
execute <<-SQL
CREATE OR REPLACE FUNCTION process_history_table() RETURNS TRIGGER AS $history_table$
BEGIN
IF (TG_OP = 'DELETE') THEN
INSERT INTO history_table VALUES (DEFAULT, 'D', now(), OLD.*);
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO history_table VALUES (DEFAULT, 'U', now(), NEW.*);
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO history_table VALUES (DEFAULT, 'I', now(), NEW.*);
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$history_table$ LANGUAGE plpgsql;
CREATE TRIGGER history_table
AFTER INSERT OR UPDATE OR DELETE ON table
FOR EACH ROW EXECUTE PROCEDURE process_history_table();
SQL
...这将适用于生产环境和其他环境。问题是当某人运行 bundle exec rake db:drop db:create db:schema:load db:migrate RAILS_ENV = test
或类似的东西时(最重要的是 db:schema:load
部分),因为触发器没有保存在 db / schema.rb
文件中,因此将绕过触发器的创建。
...and this will work for production and other environments. The problem is when someone runs bundle exec rake db:drop db:create db:schema:load db:migrate RAILS_ENV=test
or something similar (most important is the db:schema:load
portion), this will bypass trigger creation as triggers are not saved in the db/schema.rb
file.
也许正确的解决方案是说,在使用Rails时,开发人员永远不要运行 db:schema:load
和请始终运行 db:migrate
,以确保可以连续重新运行所有迁移。但是,我们已经很长时间没有那样操作了,我认为这样做会很痛苦,因为我们可能需要更新几十个或更多个迁移。关于如何将触发器逐步添加到应用程序中以及如何继续以与今天相同的方式构建/重新创建开发人员/测试环境的任何想法将非常有帮助。
Perhaps the correct solution is to say that when using rails, developers should never run db:schema:load
and always run db:migrate
instead to ensure all migrations can continuously be re-run. However, we have not been operating that way for a long time and I believe it would be quite painful to do so as we may need to update several dozen or more migrations. Any thoughts on how I could incorporate triggers into my application incrementally and have the developer / test environments continue to be built / re-created the same way as today would be very helpful.
谢谢!
推荐答案
如果您需要或想要ActiveRecord无法理解的特定于数据库的功能,则应切换到 db / structure.sql
用于跟踪架构。 db / structure.sql
几乎是使用数据库的本机工具生成的模式的原始转储,因此它将包含触发器,CHECK约束,函数结果的索引以及所有其他内容。
If you need or want database-specific features that ActiveRecord doesn't understand then you should switch to db/structure.sql
for keeping track of your schema. db/structure.sql
is pretty much a raw dump of your schema made using the database's native tools so it will contain triggers, CHECK constraints, indexes on function results, and everything else.
切换很容易:
- 更新您的
config / application.rb
包含config.active_record.schema_format =:sql
。 - 执行
rake db:structure:dump
以获得初始的db / structure.sql
。 - 从目录树和版本控制中删除
db / schema.rb
。 - 添加
db / structure .sql
进行版本控制。 - 调整耙度:
- 使用
db:structure:dump
而不是db:schema:dump
- 使用
db:structure:load
而不是db:schema:load
- 使用
- Update your
config/application.rb
to containconfig.active_record.schema_format = :sql
. - Do a
rake db:structure:dump
to get an initialdb/structure.sql
. - Delete
db/schema.rb
from your directory tree and revision control. - Add
db/structure.sql
to revision control. - Adjust your rake habits:
- Use
db:structure:dump
instead ofdb:schema:dump
- Use
db:structure:load
instead ofdb:schema:load
- Use
其他所有内容都应照常进行(当然,假设您是
Everything else should work as usual (assuming, of course, that you're sane and using PostgreSQL for development, testing, and production).
进行此更改后,触发器将在 db / structure.sql中进行跟踪。
并重新创建数据库不会丢失它们。
With this change made, your triggers will be tracked in db/structure.sql
and recreating the database won't lose them.
这篇关于Rails,PostgreSQL和历史触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!