Different relational databases treat text search very differently. The new DbTextSearch gem provides a unified interface on top of ActiveRecord for SQLite, MySQL, and PostgreSQL to do:
- Case-insensitive string-in-set querying, and CI index creation.
- Basic full-text search for a list of terms, and FTS index creation.
DbTextSearch does all the heavy lifting under the hood, hiding the complexity of handling each database differently away, which is great for gem authors, when migrating an application from one database to another, or writing code that must support multiple databases.
Here is how db_text_search handles different types of columns in different databases for case-insensitive comparisons:
| Column type | SQLite | MySQL | PostgreSQL | |||
|---|---|---|---|---|---|---|
| Detected types | Search / index | Detected types | Search / index | Detected types | Search / index | |
| Case-insensitive | always treated as case-sensitive | COLLATE NOCASE |
default | default | CITEXT | default |
| Case-sensitive | non-ci collations | LOWERno index |
default | LOWER |
||
Full-text search is even more gnarly. Great that you don't have to worry about that if you use this gem! Check it out and learn more on github.com/thredded/db_text_search.