Rails/ActiveRecord 中不区分大小写的唯一索引? [英] Case-insensitive unique index in Rails/ActiveRecord?

问题描述

我需要在 rails 的列上创建一个不区分大小写的索引.我是通过 SQL 做到的:

I need to create a case-insensitive index on a column in rails. I did this via SQL:

execute(
   "CREATE UNIQUE INDEX index_users_on_lower_email_index 
    ON users (lower(email))"
 )

这很好用，但在我的 schema.rb 文件中我有:

This works great, but in my schema.rb file I have:

add_index "users", [nil], 
  :name => "index_users_on_lower_email_index", 
  :unique => true

注意nil".因此，当我尝试克隆数据库以运行测试时，出现明显错误.我在这里做错了吗?我应该在导轨内使用其他一些约定吗?

Notice the "nil". So when I try to clone the database to run a test, I get an obvious error. Am I doing something wrong here? Is there some other convention that I should be using inside rails?

感谢您的帮助.

推荐答案

由于 MySQL 索引已经不区分大小写，我猜您正在处理 PostgreSQL，它默认创建区分大小写的索引.我在这里回答基于 Rails 3.2.3 和 PostgreSQL 8.4.

Since MySQL indexes are already case-insensitive, I'm guessing you're dealing with PostgreSQL, which creates case-sensitive indexes by default. I'm answering here based on Rails 3.2.3 and PostgreSQL 8.4.

似乎功能索引是另一个例子ActiveRecord 无法生成.外键和 UUID 列是另外两个想到的.所以别无选择(除了猴子修补 ActiveRecord)，只能使用 execute 语句.

It seems functional indexes are one more example of things that ActiveRecord can't generate. Foreign keys and UUID columns are two more that come to mind. So there is no choice (other than monkey-patching ActiveRecord) but to use execute statements.

这意味着要准确转储您的数据库，您需要放弃与数据库无关的 schema.rb，转而使用特定于数据库的 structure.sql.请参阅有关迁移的 Rails 指南，部分 6.2 模式转储类型.设置如下:

This means for an accurate dump of your database, you'll need to abandon the DB-agnostic schema.rb in favor of DB-specific structure.sql. See the Rails Guide on Migrations, section 6.2 Types of Schema Dumps. This is set as follows:

config/application.rb

config.active_record.schema_format = :sql

db/structure.sql 应该在您运行迁移时自动更新.您可以使用以下命令手动生成它:

db/structure.sql should be updated automatically when you run a migration. You can generate it manually with this command:

rake db:structure:dump

该文件是纯 Postgres SQL.尽管在使用 rake -T 列出 rake 任务时没有记录，但似乎可以使用此命令从 structure.sql 转储中加载数据库:

The file is pure Postgres SQL. Although not documented when you use rake -T to list rake tasks, it seems that you can use this command to load the database from the structure.sql dump:

rake db:structure:load

这里没有什么神奇之处:源代码(此处显示来自 Rails 3.2.16)只是在 structure.sql 上调用 psql.

There's nothing magic here: the source code (shown here from Rails 3.2.16) just calls psql on structure.sql.

最后，这是我的迁移，以删除旧的区分大小写的电子邮件约束并添加区分大小写的功能索引:

Finally, here is my migration to drop an old, case-sensitive email constraint and add the case-sensitive functional index:

class FixEmailUniqueIndexOnUsers < ActiveRecord::Migration
  def up
    remove_index :users, :email
    execute "CREATE UNIQUE INDEX index_users_on_lowercase_email 
             ON users USING btree (lower(email));"
  end

  def down
    execute "DROP INDEX index_users_on_lowercase_email;"
    add_index :users, :email, :unique => true
  end
end

这篇关于Rails/ActiveRecord 中不区分大小写的唯一索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！