swanandp/acts_as_list — pull request #135 by fabn (2014-10-23 16:52:04+00:00)
Title: Discussion about unique indexes

Hi @swanandp this PR is not meant to be merged but only to start a discussion on uniqueness constraints.

In current codebase unique indexes cannot be added to position columns otherwise lot of tests will fail. This is because there are some queries which swap values between rows and other issues with update order.

For instance a simple `UPDATE table SET position = position + 1` will lead to duplicate values unless updates are executed in order (with a non standard SQL ORDER BY in UPDATE queries).

I think is worth to mention this in the readme because for list behavior I'd expect to be able to do one of the following

```ruby
add_index :table, :position, unique: true # for unscoped lists
add_index :table, [:parent_id, :position], unique: true # for scoped lists
```

In any case it will be possible to make unique index working by using [this approach](http://stackoverflow.com/a/24132242/518204) in MySQL and using [DEFERRABLE constraints](http://hashrocket.com/blog/posts/deferring-database-constraints) in Postgres. I don't know if this is possible in SQLite.

Also a lot of queries can be rewritten using a standard `CASE` statement ([example](https://github.com/fabn/acts_as_list/compare/unique-issue?expand=1#diff-708784f0c89e5cdbdd54e18d2b2243d1R147)) to halve the number of executed queries and to avoid unique issue in Postgres.

If you're interested in this I can try to identify problematic method and they can be rewritten by wrapping them in a new method, something like

```ruby
# Obviously this need to be customized according to adapter, this version is for MySQL
def without_unique_checks(&block)
connection.execute('SET UNIQUE_CHECKS = 0;')
block.call
connection.execute('SET UNIQUE_CHECKS = 1;')
end

# For instance
def move_to_top
without_unique_checks do
increment_positions_on_higher_items
assume_top_position
end
end
```

What do you think?