Msfte is a plugin for Ruby on Rails that uses MSFTESQL (Microsoft Full-Text Engine for SQL Server) to enable simple full-text search in your models using a full-text index that you configure in your database.
In your Rails root, type
ruby script/plugin install git://github.com/dvrensk/msfte
If you are behind a firewall, visit github.com/dvrensk/msfte and download the plugin as a zip file and unpack it in vendor/plugins.
In each model that you want to search with Msfte, add
acts_as_msfte
You will need to use your favourite MSSQL management tool to create the actual full-text indexes.
If you want to use pagination you will need to installa and load the will_paginate gem.
All results are sorted by relevance unless you provide an explicit :order.
Post.find_with_msfte 'linux' Post.find_with_msfte 'linux', :order => "created_at DESC" # Limit to title Post.find_with_msfte "linux", :search_attributes => [:title] # Look for more than one word Post.find_with_msfte :matches_any => ["nice", "secure"] Post.find_with_msfte :matches_all => ["nice", "secure"] # Look for phrases Post.find_with_msfte :matches_any => ["phrase one", "phrase two"] Post.find_with_msfte :matches_all => ["phrase one", "phrase two"] # Partial match, useful for auto-completion Post.find_with_msfte 'window*' Post.find_with_msfte :starts_with_any => "hack secu" # equivalent to Post.find_with_msfte :starts_with_any => ["hack", "secu"]
Msfte is compatible with will_paginate and uses WillPaginate::Collection for its paginated results. To use pagination, say
Post.find_with_msfte "lindy hop", :per_page => 10, :page => 1
You have to install and load the will_paginate gem if you want to use pagination.
# Search posts and comments Post.find_with_msfte "operating", :include_in_search => :comments # Search only comments Post.find_with_msfte "operating", :include_in_search => :comments, :ignore_self => true
The plugin is incapable of creating the actual full-text indexes. The corollary is that Msfte#find_with_msfte cannot be used in tests, since the db:test:prepare task is not FTI-aware. In the project where the plugin was developed we worked around the problem by creating some extra rake tasks:
namespace "test" do task :all => "test:fti:all" task :fti => "test:fti:all" namespace "fti" do desc "Run all tests, making sure FTI is active" task :all => ['db:test:prepare', 'db:test:setup_fti', :test] desc "Run functional tests, making sure FTI is active" task :functionals => ['db:test:prepare', 'db:test:setup_fti', "test:functionals"] desc "Run unit tests, making sure FTI is active" task :units => ['db:test:prepare', 'db:test:setup_fti', "test:units"] desc "Run integration tests, making sure FTI is active" task :integration => ['db:test:prepare', 'db:test:setup_fti', "test:integration"] end end namespace "db" do namespace "test" do task :setup_fti do sh "Setup_testDatabase_fti.bat" end end end
(Setup_testDatabase_fti runs a script that connects to the DB and runs the SQL needed to create the indexes.) Now, running rake test:fti will prepare the test DB, add the FTIs and run all the tests. Nice and dandy if it weren’t for the fact that MSFTESQL updates the indexes asynchronously, so with all the fixtures being loaded and deleted, there is no knowing if an index search will return anything or not. And even if it works on your machine, it may not work on the CI machine.
There are bugs in at least some MSSQL-for-Rails adapters that surface as malformed queries when you combine pagination and association searching. E.g. the following query would not work with the adapter that we used:
Post.find_with_msfte "linux", :include_in_search => :comments, :page => 2, :per_page => 10
The problem lies in the method that tries to make up for the fact that MSSQL does not have LIMIT in its SQL syntax. A workaround is to specify an explicit sort order (i.e. not sorting by relevance):
Post.find_with_msfte "linux", :include_in_search => :comments, :page => 2, :per_page => 10, :order => "posts.created_at DESC"
See limitations above.
Remove the limitations! It shouldn’t be so hard to create the FTIs on demand based on options passed to acts_as_msfte, but it was a non-requirement in the original project.
Solving the testing problem might be harder. There should be a way to ask the DB server if the FTIs are up to date. These queries could be a start, but they are not enough:
select * from sys.dm_fts_index_population
where table_id = OBJECT_ID('posts', 'U')
select * from sys.fulltext_indexes
where object_id = OBJECT_ID('posts', 'U')
Check README_contrib.rdoc for more information.
This plugin was developed as a replacement for (acts_as_)ferret when it dawned on us that there would be 40 different services importing data into the database, and that there was no way we could make all those services trigger an update of Ferret’s index.
I found the following articles very helpful when I wanted to develop this plugin TDD style:
David Vrensk <[email protected]>, commissioned by a client that wishes to remain anonymous.