From: Tom Hughes Date: Wed, 15 Jan 2014 19:58:58 +0000 (+0000) Subject: Reenable the note search API X-Git-Tag: live~4517 X-Git-Url: https://git.openstreetmap.org/rails.git/commitdiff_plain/45618726efd410f721d3406b341533168ced6f11?ds=inline Reenable the note search API Add a postgress freetext index on the note comments, and enable note searching using freetext matching. --- diff --git a/app/controllers/notes_controller.rb b/app/controllers/notes_controller.rb index bf28d4c25..1f9d10b09 100644 --- a/app/controllers/notes_controller.rb +++ b/app/controllers/notes_controller.rb @@ -255,16 +255,11 @@ class NotesController < ApplicationController # Get any conditions that need to be applied @notes = closed_condition(Note.all) - @notes = @notes.joins(:comments).where("note_comments.body ~ ?", params[:q]) + @notes = @notes.joins(:comments).where("to_tsvector('english', note_comments.body) @@ plainto_tsquery('english', ?)", params[:q]) # Find the notes we want to return @notes = @notes.order("updated_at DESC").limit(result_limit).preload(:comments) - # Disable notes search until we can make it scalable - response.headers['Error'] = "Searching of notes is currently unavailable" - render :text => "", :status => :service_unavailable - return false - # Render the result respond_to do |format| format.rss { render :action => :index } diff --git a/db/migrate/20140115192822_add_text_index_to_note_comments.rb b/db/migrate/20140115192822_add_text_index_to_note_comments.rb new file mode 100644 index 000000000..e43374595 --- /dev/null +++ b/db/migrate/20140115192822_add_text_index_to_note_comments.rb @@ -0,0 +1,11 @@ +require 'migrate' + +class AddTextIndexToNoteComments < ActiveRecord::Migration + def up + add_index :note_comments, [], :columns => "to_tsvector('english', body)", :method => "GIN", :name => "index_note_comments_on_body" + end + + def down + remove_index :note_comments, :name => "index_note_comments_on_body" + end +end diff --git a/db/structure.sql b/db/structure.sql index e6165e9e7..031b89aa5 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -3,6 +3,7 @@ -- SET statement_timeout = 0; +SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; @@ -1797,6 +1798,13 @@ CREATE INDEX gpx_files_visible_visibility_idx ON gpx_files USING btree (visible, CREATE UNIQUE INDEX index_client_applications_on_key ON client_applications USING btree (key); +-- +-- Name: index_note_comments_on_body; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_note_comments_on_body ON note_comments USING gin (to_tsvector('english'::regconfig, body)); + + -- -- Name: index_note_comments_on_created_at; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2466,6 +2474,8 @@ INSERT INTO schema_migrations (version) VALUES ('20130328184137'); INSERT INTO schema_migrations (version) VALUES ('20131212124700'); +INSERT INTO schema_migrations (version) VALUES ('20140115192822'); + INSERT INTO schema_migrations (version) VALUES ('21'); INSERT INTO schema_migrations (version) VALUES ('22'); diff --git a/lib/migrate.rb b/lib/migrate.rb index baa7faecd..7c14333b3 100644 --- a/lib/migrate.rb +++ b/lib/migrate.rb @@ -126,6 +126,9 @@ module ActiveRecord if Hash === options and options[:lowercase] quoted_column_names = quoted_column_names.map { |e| "LOWER(#{e})" } end + if Hash === options and options[:columns] + quoted_column_names = quoted_column_names + Array[options[:columns]] + end quoted_column_names = quoted_column_names.join(", ") execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} USING #{index_method} (#{quoted_column_names})" diff --git a/test/functional/notes_controller_test.rb b/test/functional/notes_controller_test.rb index 3e817b6f9..49c3cd0ed 100644 --- a/test/functional/notes_controller_test.rb +++ b/test/functional/notes_controller_test.rb @@ -638,76 +638,68 @@ class NotesControllerTest < ActionController::TestCase def test_search_success get :search, {:q => 'note 1', :format => 'xml'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/xml", @response.content_type - # assert_select "osm", :count => 1 do - # assert_select "note", :count => 1 - # end + assert_response :success + assert_equal "application/xml", @response.content_type + assert_select "osm", :count => 1 do + assert_select "note", :count => 1 + end get :search, {:q => 'note 1', :format => 'json'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/json", @response.content_type - # js = ActiveSupport::JSON.decode(@response.body) - # assert_not_nil js - # assert_equal "FeatureCollection", js["type"] - # assert_equal 1, js["features"].count + assert_response :success + assert_equal "application/json", @response.content_type + js = ActiveSupport::JSON.decode(@response.body) + assert_not_nil js + assert_equal "FeatureCollection", js["type"] + assert_equal 1, js["features"].count get :search, {:q => 'note 1', :format => 'rss'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/rss+xml", @response.content_type - # assert_select "rss", :count => 1 do - # assert_select "channel", :count => 1 do - # assert_select "item", :count => 1 - # end - # end + assert_response :success + assert_equal "application/rss+xml", @response.content_type + assert_select "rss", :count => 1 do + assert_select "channel", :count => 1 do + assert_select "item", :count => 1 + end + end get :search, {:q => 'note 1', :format => 'gpx'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/gpx+xml", @response.content_type - # assert_select "gpx", :count => 1 do - # assert_select "wpt", :count => 1 - # end + assert_response :success + assert_equal "application/gpx+xml", @response.content_type + assert_select "gpx", :count => 1 do + assert_select "wpt", :count => 1 + end end def test_search_no_match get :search, {:q => 'no match', :format => 'xml'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/xml", @response.content_type - # assert_select "osm", :count => 1 do - # assert_select "note", :count => 0 - # end + assert_response :success + assert_equal "application/xml", @response.content_type + assert_select "osm", :count => 1 do + assert_select "note", :count => 0 + end get :search, {:q => 'no match', :format => 'json'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/json", @response.content_type - # js = ActiveSupport::JSON.decode(@response.body) - # assert_not_nil js - # assert_equal "FeatureCollection", js["type"] - # assert_equal 0, js["features"].count + assert_response :success + assert_equal "application/json", @response.content_type + js = ActiveSupport::JSON.decode(@response.body) + assert_not_nil js + assert_equal "FeatureCollection", js["type"] + assert_equal 0, js["features"].count get :search, {:q => 'no match', :format => 'rss'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/rss+xml", @response.content_type - # assert_select "rss", :count => 1 do - # assert_select "channel", :count => 1 do - # assert_select "item", :count => 0 - # end - # end + assert_response :success + assert_equal "application/rss+xml", @response.content_type + assert_select "rss", :count => 1 do + assert_select "channel", :count => 1 do + assert_select "item", :count => 0 + end + end get :search, {:q => 'no match', :format => 'gpx'} - assert_response :service_unavailable - # assert_response :success - # assert_equal "application/gpx+xml", @response.content_type - # assert_select "gpx", :count => 1 do - # assert_select "wpt", :count => 0 - # end + assert_response :success + assert_equal "application/gpx+xml", @response.content_type + assert_select "gpx", :count => 1 do + assert_select "wpt", :count => 0 + end end def test_search_bad_params