Project

db_text_search

0.03
Low commit activity in last 3 years
No release in over a year
db_text_searchthredded/db_text_searchHomepageDocumentationSource CodeBug TrackerWiki
Different relational databases treat text search very differently. DbTextSearch 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, and basic full-text search for a list of terms, and FTS index creation.
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
 Popularity
Downloads
118,833
Stars
64
Forks
7
Watchers
7
 Releases
Current version
1.0.0
Total releases
9
First release
2016-03-29
Latest release
2022-02-18
 Issues
Open Issues
0
Closed Issues
4
Total Issues
4
Issue Closure Rate
100%
 Pull Requests
Open Pull Requests
1
Closed Pull Requests
0
Merged Pull Requests
9
Pull Request Acceptance Rate
90%
 Development
Primary Language
Ruby
Licenses
MIT
Average date of last 50 commits
2018-06-28
Reverse Dependencies
2

 Dependencies

Development

mysql2
>= 0.3.20
pg
>= 0.18.4
rspec
~> 3.4
simplecov
>= 0
sqlite3
>= 1.3.11
rake
~> 13.0

Runtime

activerecord
>= 4.1.15
 Project Readme

DbTextSearch Build Status Code Climate Test Coverage

Different relational databases treat text search very differently. DbTextSearch provides a unified interface on top of ActiveRecord for SQLite, MySQL, and PostgreSQL to do:

  • Case-insensitive string-in-set querying, prefix querying, and case-insensitive index creation.
  • Basic full-text search for a list of terms, and FTS index creation.

Installation

Add this line to your application's Gemfile:

gem 'db_text_search', '~> 1.0'

Usage

Case-insensitive string matching

Add an index in a migration to an existing CI (case-insensitive) or CS (case-sensitive) column:

DbTextSearch::CaseInsensitive.add_index connection, :users, :username
# Options: name, unique

Or, create a new CI column:

DbTextSearch::CaseInsensitive.add_ci_text_column connection, :users, :username

Perform a search for records with column that case-insensitively equals to one of the strings in a given set:

# Find all confirmed users that have either the username Alice or Bob (case-insensitively):
DbTextSearch::CaseInsensitive.new(User.confirmed, :username).in(%w(Alice Bob))
 #=> ActiveRecord::Relation

Perform a case-insensitive prefix search:

DbTextSearch::CaseInsensitive.new(User.confirmed, :username).prefix('Jo')

See also: API documentation.

Full text search

Add an index:

DbTextSearch::FullText.add_index connection, :posts, :content
# Options: name

Perform a full-text search:

DbTextSearch::FullText.new(Post.published, :content).search('peace')
DbTextSearch::FullText.new(Post.published, :content).search(%w(love kaori))

Under the hood

Case-insensitive string matching

Case-insensitive equality methods
Column type SQLite MySQL PostgreSQL
Detected types Search / index Detected types Search / index Detected types Search / index
CI always treated as CS COLLATE NOCASE default default CITEXT default
CS non-ci collations LOWER
no index 		default LOWER
Case-insensitive prefix matching (using LIKE)
Column type SQLite MySQL PostgreSQL
CI default, cannot always use an index,
even for prefix queries 		default cannot use an index
CS cannot use an index LOWER(column text_pattern_ops)

Full-text search

MySQL

A FULLTEXT index, and a MATCH AGAINST query. MySQL v5.6.4+ is required.

PostgreSQL

A gist(to_tsvector(...)) index, and a @@ plainto_tsquery query. Methods also accept an optional pg_ts_config argument (default: "'english'") that is ignored for other databases.

SQLite

No index, a LIKE %term% query for each term joined with AND.

Development

Make sure you have a working installation of SQLite, MySQL, and PostgreSQL. After checking out the repo, run bin/setup to install dependencies. Then, run rake test_all to run the tests with all databases and gemfiles.

See the Rakefile for other available test tasks.

You can also run bin/console for an interactive prompt that will allow you to experiment.

Contributing

Bug reports and pull requests are welcome on GitHub at https://github.com/thredded/db_text_search. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.

License

The gem is available as open source under the terms of the MIT License.