Rails、PostgreSQL 和历史触发器 [英] Rails, PostgreSQL, and History Triggers

问题描述

所以...

我正在添加由触发器填充的历史表，以便通过诸如...之类的方式在我的项目中进行审计

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.

切换很容易:

1. 更新您的 config/application.rb 以包含 config.active_record.schema_format = :sql.
2. 执行 rake db:structure:dump 以获得初始 db/structure.sql.
3. 从目录树和版本控制中删除 db/schema.rb.
4. 将 db/structure.sql 添加到版本控制.
5. 调整您的佣金习惯:
   • 使用 db:structure:dump 而不是 db:schema:dump
   • 使用 db:structure:load 而不是 db:schema:load

1. Update your config/application.rb to contain config.active_record.schema_format = :sql.
2. Do a rake db:structure:dump to get an initial db/structure.sql.
3. Delete db/schema.rb from your directory tree and revision control.
4. Add db/structure.sql to revision control.
5. Adjust your rake habits:
   • Use db:structure:dump instead of db:schema:dump
   • Use db:structure:load instead of db:schema:load

其他一切都应该像往常一样工作(当然，假设您是理智的并且使用 PostgreSQL 进行开发、测试和生产).

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屋！