不区分大小写的轨道/ ActiveRecord的唯一指标？

问题描述

我需要在轨列创建一个不区分大小写的索引。我这样做是通过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

请注意无。所以，当我尝试克隆数据库运行一个测试，我得到一个明显的错误。难道我做错了什么吗？有没有我应该使用内滑轨一些其他的约定？

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），但使用执行语句。

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.

这意味着你的数据库的准确转储，你需要放弃DB无关schema.rb支持DB-特定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:

配置/ 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。尽管当您使用未记录耙-T 列出耙任务，似乎你可以使用这个命令加载从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

没有什么神奇的所在位置：source code 只是调用psql的上structure.sql。

There's nothing magic here: the source code 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

---

更新2014年2月4日

---

Update February 4, 2014

修复损坏的链接，锁定到Rails 3.2.16。

Fix broken links, locking to Rails 3.2.16.

这篇关于不区分大小写的轨道/ ActiveRecord的唯一指标？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！