Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
an example of full text search in postgres using datamapper
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
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"
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
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
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