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 bf28d4c25b1b7a185247f1283018deced5683bf5..1f9d10b0967204d75435870e6b7ed55916d57e1d 100644 (file)
--- 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)
 
     # 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)
 
 
     # 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 }
     # 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 (file)
index 0000000..e433745
--- /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 e6165e9e7f0c3f9ee4573d59a673c5f36e8b2081..031b89aa5bb848b15eb03ff9ccef2cbed15325af 100644 (file)
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -3,6 +3,7 @@
 --
 
 SET statement_timeout = 0;
 --
 
 SET statement_timeout = 0;

+SET lock_timeout = 0;

 SET client_encoding = 'UTF8';
 SET standard_conforming_strings = on;
 SET check_function_bodies = false;
 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);
 
 
 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: 
 --
 --
 -- 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 ('20131212124700');
 

+INSERT INTO schema_migrations (version) VALUES ('20140115192822');
+

 INSERT INTO schema_migrations (version) VALUES ('21');
 
 INSERT INTO schema_migrations (version) VALUES ('22');
 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 baa7faecde3703d438548bb6c210ad812434d682..7c14333b30af91f0ad23b6f690d65d5852d11abb 100644 (file)
--- 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[: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})"
         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 3e817b6f990f1a8bdd5b6650d71435a6049bc494..49c3cd0edb699af6cb46cab3882cd1af5bc8d30a 100644 (file)
--- 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'}
 
   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'}
 
     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'}
 
     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'}
 
     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'}
   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'}
 
     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'}
 
     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'}
 
     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
   end
 
   def test_search_bad_params