Although I don’t use ActiveRecord as an ORM, I do use its migrations framework for evolving my database schema through standalone_migrations.
But I'm finding the current DSL limited with regards to deferrable
constraints support.
Let me demonstrate with a common use-case, like trees
implementation, with SQL written for PostgreSQL:
create table tree(parent_id integer not null, position integer not
null, unique(parent_id, position)); insert into tree(parent_id, position) select generate_series(1, 3), 1; insert into tree(parent_id, position) select generate_series(1, 3), 2;
update tree set position = position + 1;
ERROR: duplicate key value violates unique constraint
“tree_parent_id_position_key”
In PostgreSQL the constraint check is performed during the UPDATE,
so its success will depend on what order PG will try to update the “position” field.
Since we want PG to defer the constraint check to the end of the
UPDATE statement (it is also possible to specify constraints to be check on transaction commit, but I’ll keep this short) we must create this “tree” table in PG like this:
create table tree(parent_id integer not null, position integer not
null, unique(parent_id, position) deferrable);
But there is no DSL support in migrations to tell the database to
use a deferrable constraint when supported by the database.
Also, I believe that deferrable should be the default and one should
specify “deferrable: false” if this is not desired in case add_index would support a “deferrable” option. In such case, it should generate a statement like:
alter table :table_name add constraint :constraint_name unique
(parent_id, position) deferrable
Another option would be to introduce some "add_constraint" DSL to
Migrations. Anyway, there should be some way of stating this deferrable information in schema.rb.
Each approach would be fine to me as long as I don't need to do it
myself using PG-specific SQL with “execute” method.
Thanks for considering,
Rodrigo.