问题描述
我使用 SQLite3 进行开发，使用 PostgreSQL 进行部署.但是，我面临以下问题:
I am using SQLite3 for development and PostgreSQL for deployment. However, I am facing the following problem:
我使用 SQLite3 的简单搜索:
My simple search using SQLite3:
def self.search(search)
if search
find(:all, :conditions => ["style LIKE ? OR construction LIKE ?", "%#{search}%", "%#{search}%"])
else
find(:all)
end
end
但是PostgreSQL不行，需要把ILIKE的LIKE换成ILIKE解决:p>
However, it doesn't work for PostgreSQL, and I need to replace the LIKE for ILIKE to solve the problem:
def self.search(search)
if search
find(:all, :conditions => ["style ILIKE ? OR construction ILIKE ?", "%#{search}%", "%#{search}%"])
else
find(:all)
end
end
是否有一种Ruby 方式"可以在任何数据库中进行这些搜索?
Is there a "Ruby way" to do these searches across any database?
编辑 - 根据您的回答，我认为我不会为此找到通用的 Ruby 解决方案.
EDIT - based on your answers I don't believe I will find a generic Ruby solution for that.
我遵循 Ruby on Rails 教程:通过示例学习 Rails - Michael Hartl，其中最后的 Gemfile 显示了两个数据库...嗯，令人失望...
I have followed the Ruby on Rails Tutorial: Learn Rails by Example - by Michael Hartl, where the final Gemfile shows both databases... well, disappointing...
推荐答案
问题的根源在这里:
我使用 SQLite3 进行开发，使用 PostgreSQL 进行部署.
I am using SQLite3 for development and PostgreSQL for deployment.
这是个坏主意™.您将不断遇到不兼容问题 - 或更糟糕的是:在损坏完成之前不会意识到一些问题.
使用相同的 RDBMS (PostgreSQL) 进行开发和生产，省去无谓的麻烦.
That's a bad idea™. You will keep running into incompatibilities - or worse: not realize some until damage is done.
Use the same RDBMS (PostgreSQL) for development and production and save yourself the pointless trouble.
当您遇到不幸的设置时，有一个简单的解决方法:
While you are stuck with your unfortunate setup, there is a simple fix:
lower(style) LIKE lower(?)
在两个平台上都可以工作.
Works on both platforms alike.
如果您提供小写搜索模式，您可以删除右侧的
lower().
在标准 SQLite 中 lower(X) 仅折叠 ASCII 字母.更多信息，我引用了SQLite 手册中的核心函数一章:
In standard SQLite lower(X) only folds ASCII letters. For more, I quote the chapter Core Functions in the SQLite manual:
lower(X) 函数返回字符串 X 的副本，其中包含所有ASCII 字符转换为小写.默认内置lower()函数仅适用于 ASCII 字符.做大小写转换非 ASCII 字符，加载 ICU 扩展.
The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension.
强调我的.
PostgreSQL 下(X) 可以开箱即用地使用 UTF-8.
PostgreSQL lower(X) works with UTF-8 out of the box.
作为一个受欢迎的副作用，您可以使用 加速该查询.html" rel="noreferrer">表达式上的索引 lower(style)，这将比使用ILIKE和
As a welcome side effect, you can speed up that query in PostgreSQL with an index on the expression lower(style), which will be faster than using ILIKE and a basic index on style.
此外，从 PostgreSQL 9.1 开始，您可以将 GIN 或 GIST 索引与 pg_trgm 扩展 以加快 any LIKE 和 ILIKE 查询 - trigrams不区分大小写.此相关答案中的详细说明和链接:
Also, since PostgreSQL 9.1 you can use a GIN or GIST index with the pg_trgm extension to speed up any LIKE and ILIKE query - trigrams are case-insensitive. Detailed instructions and links in this related answer:
- 自动完成字段的类似 UTF-8 字符串
这篇关于SQLite3“LIKE"的通用 Ruby 解决方案还是 PostgreSQL“ILIKE"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！