simple fulltext search with postgres + sequel

gistfile1.rb

  if params[:query].present?
    # not doing :* prefix matching right now.
    # Preprocess the string into what TSvector wants. spaces -> ?
    # Then only letters, numbers, underscores and qmarks are left.
    query = params[:query].gsub(/\s+/, '?').gsub(/[^\w\?]/, '')
    @results = @results.select { [ts_headline('english', :text, to_tsquery('english', query), 'MaxFragments=2').as(headline), id, title, happened_at]}
    @results = @results.filter("ts_text @@ to_tsquery('english', ?::text)", query)
  end


Sequel.migration do
  up do
    create_table :transcripts do
      primary_key :id
      String :text
      String :title
      DateTime :happened_at
    end

    run "ALTER TABLE transcripts ADD COLUMN ts_text tsvector;"
    run "CREATE INDEX ts_text_idx ON transcripts USING gin(ts_text);"
    run "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
         ON transcripts FOR EACH ROW EXECUTE PROCEDURE
         tsvector_update_trigger(ts_text, 'pg_catalog.english', text, title, location);"
  end