Created
October 23, 2009 20:05
-
-
Save BrianTheCoder/217158 to your computer and use it in GitHub Desktop.
an example of full text search in postgres using datamapper
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
require 'logger' | |
DataMapper.setup(:default, 'postgres://localhost/text_search_demo') | |
DataObjects::Postgres.logger = Logger.new(STDOUT) | |
require 'searchable' | |
class Article | |
include DataMapper::Resource | |
include Searchable | |
self.search_indexes = [ :title, :body ] | |
property :id, Serial | |
property :title, String, :nullable => false, :length => 200 | |
property :body, Text | |
end |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
source "http://gems.github.com" | |
source "http://gemcutter.org" | |
bundle_path "gems" | |
gem "dm-core" | |
gem "dm-migrations" | |
gem "dm-sweatshop" | |
gem "data_objects" | |
gem "do_postgres" | |
gem "faker" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
load 'gems/environment.rb' | |
Bundler.require_env | |
require 'article' | |
Article.fix{{ | |
:title => Faker::Lorem.sentence, | |
:body => Faker::Lorem.paragraphs(5) | |
}} | |
def seed(n = 10) | |
Article.auto_migrate! | |
n.of{ Article.gen } | |
end |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
migration( 1, :add_text_indexing_to_stories ) do | |
up do | |
ADD_TEXT_INDEXING_TO_MEMORY = <<-EOF | |
ALTER TABLE memories ADD COLUMN title_search_index tsvector; | |
ALTER TABLE memories ADD COLUMN body_search_index tsvector; | |
UPDATE memories SET title_search_index = to_tsvector('english', coalesce(title,'')); | |
UPDATE memories SET body_search_index = to_tsvector('english', coalesce(body,'')); | |
CREATE TRIGGER title_index_update BEFORE INSERT OR UPDATE ON memories FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(title_search_index, 'pg_catalog.english', title); | |
CREATE TRIGGER body_index_update BEFORE INSERT OR UPDATE ON memories FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(body_search_index, 'pg_catalog.english', body); | |
EOF | |
repository.adapter.execute(ADD_TEXT_INDEXING_TO_MEMORY) | |
end | |
down do | |
REMOVE_TEXT_INDEXING_TO_MEMORY = <<-EOF | |
ALTER TABLE memories DROP COLUMN title_search_index; | |
ALTER TABLE memories DROP COLUMN body_search_index; | |
DROP TRIGGER title_index_update; | |
DROP TRIGGER body_index_update; | |
EOF | |
repository.adapter.execute(REMOVE_TEXT_INDEXING_TO_MEMORY) | |
end | |
end |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
module Searchable | |
def self.included(model) | |
model.send(:extend, ClassMethods) | |
model.class_eval do | |
class_inheritable_accessor(:search_indexes) | |
self.search_indexes = [] | |
end | |
end | |
module ClassMethods | |
def search(query, options = {}) | |
conds = search_indexes.map{|idx| "#{idx}_search_index @@ plainto_tsquery(?)" } | |
conds_array = [conds.join(" OR ")] | |
search_indexes.size.times{ conds_array << escape_string(query) } | |
all(options.merge(:conditions => conds_array)) | |
end | |
private | |
def escape_string(str) | |
str.gsub(/([\0\n\r\032\'\"\\])/) do | |
case $1 | |
when "\0" then "\\0" | |
when "\n" then "\\n" | |
when "\r" then "\\r" | |
when "\032" then "\\Z" | |
when "'" then "''" | |
else "\\"+$1 | |
end | |
end | |
end | |
end | |
end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment