rails 上的 sqlite3:create_table 使用 collat​​e nocase [英] sqlite3 on rails: create_table using collate nocase

问题描述

在 ruby​​ v2.3.0p0 中使用 rails 4.2.0

我想创建不区分大小写的索引，因为我的大多数搜索都不区分大小写，而且我不想每次都进行全表搜索.

所以在我的 create_table 迁移中，我尝试添加如下行:

add_index :events, :name, :COLLATE =>:NOCASE

当我迁移时，我得到:

== 20150515163641 CreateEvents:迁移 ===============================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================-- create_table(:events)->0.0018s-- add_index(:events, :submitter, {:COLLATE=>:NOCASE})耙子中止！标准错误:发生错误，这次和所有以后的迁移都取消了:未知密钥::COLLATE.有效的键是: :unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type

如何使用 SQLITE3 和 rails 在我的迁移文件中创建不区分大小写的索引?

更新------------------------

感谢 Anthony 的帮助，我发现 rails 尚不支持 Sqlite3 代码中的整理/整理.

因此，我尝试根据 Rails 生成的语法在迁移中手动编写它，但添加了 collat​​e 选项:

execute("CREATE INDEX 'index_events_on_name' ON 'events' ('name' COLLATE NOCASE);")

根据 Sqlite3，这正确创建了索引，但是 Schema.rb 现在是错误的，当然.

更重要的是，当我搜索时，它似乎仍然没有进行不区分大小写的比较:

Event.where("name == ?",name)Event.find_by_name(name)

如何说服 Rails 使用我创建的索引?

解决方案

看起来不像索引上的 collat​​ion 已经合并了:

https://github.com/rails/rails/pull/18499/files

这个 PR 顺便增加了对列的整理支持.>

语法如下:

create_table :foo do |t|t.string :string_nocase, 排序规则: 'NOCASE't.text :text_rtrim，整理:'RTRIM'结尾add_column :foo, :title, :string, collat​​ion: 'RTRIM'change_column :foo, :title, :string, collat​​ion: 'NOCASE'

Using rails 4.2.0 with ruby v2.3.0p0

I want to create indexes that are case insensitive since most of my searches are case insensitive and I don't want to have to do a full table search every time.

So in my create_table migrations, I try adding lines such as:

add_index :events, :name, :COLLATE => :NOCASE

And when I migrate I get:

== 20150515163641 CreateEvents: migrating =====================================
-- create_table(:events)
   -> 0.0018s
-- add_index(:events, :submitter, {:COLLATE=>:NOCASE})
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Unknown key: :COLLATE. Valid keys are: :unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type

How can I create a case-insensitive index in my migration files using SQLITE3 and rails?

UPDATE ------------------------

Thanks to Anthony's help, I see that rails doesn't support collate/collation in the Sqlite3 code yet.

So I tried writing it manually in my migration based on the syntax that Rails would generate, but with the collate option added:

execute("CREATE INDEX 'index_events_on_name' ON 'events' ('name' COLLATE NOCASE);")

This creates the index properly, according to Sqlite3, but the Schema.rb is now wrong, of course.

And more importantly, it still doesn't seem to be doing case-insensitive comparisons when I search:

Event.where("name == ?",name)
Event.find_by_name(name)

How do I convince rails to use the index I created?

解决方案

Doesn't look like collation on an index has been merged yet:

https://github.com/rails/rails/pull/18499/files

This PR, adds collation support for columns by the way.

The syntax looks like:

create_table :foo do |t|
  t.string :string_nocase, collation: 'NOCASE'
  t.text :text_rtrim, collation: 'RTRIM'
end

add_column :foo, :title, :string, collation: 'RTRIM'

change_column :foo, :title, :string, collation: 'NOCASE'

这篇关于rails 上的 sqlite3:create_table 使用 collat​​e nocase的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！