implemented textsearch

diff --git a/app/controllers/thumbs_controller.rb b/app/controllers/thumbs_controller.rb
index 8f2ac7a..cc9e0a9 100644 (file)
--- a/app/controllers/thumbs_controller.rb
+++ b/app/controllers/thumbs_controller.rb
@@ -4,7 +4,8 @@ class ThumbsController < ApplicationController
   # GET /thumbs
   # GET /thumbs.xml
   def index
-    @thumbs = Thumb.paginate(:page => params[:page])
+    @search = Thumb::Search.new(params[:search])
+    @thumbs = Thumb.paginate(@search.find_options.merge(:page => params[:page]))
 
     respond_to do |format|
       format.html # index.html.erb

diff --git a/app/models/thumb.rb b/app/models/thumb.rb
index 6fcaff1..ae94ff0 100644 (file)
--- a/app/models/thumb.rb
+++ b/app/models/thumb.rb
@@ -1,3 +1,30 @@
 class Thumb < ActiveRecord::Base
   set_primary_key :video_id
+
+  class Search
+    attr_accessor :text
+
+    def initialize(attributes)
+      attributes.each do |name, value|
+        send("#{name}=", value)
+      end if attributes
+    end
+
+    def conditions
+      @conditions ||= begin
+        sql = "TRUE"
+        ret = [ sql ]
+        terms = []
+        unless text.blank?
+          sql.concat " and (vector @@ to_tsquery('japanese', web_query(?)))"
+          ret.push text
+        end
+        ret
+      end
+    end
+
+    def find_options
+      { :conditions => conditions }
+    end
+  end
 end

diff --git a/app/views/thumbs/index.html.erb b/app/views/thumbs/index.html.erb
index e0cad09..a01a93b 100644 (file)
--- a/app/views/thumbs/index.html.erb
+++ b/app/views/thumbs/index.html.erb
@@ -1,3 +1,10 @@
+<% form_for(:search, @search, :url => thumbs_path, :html => { :method => :get }) do |f| %>
+  <p>
+    <%= _'Thumb|Title' %> or <%= _'Thumb|Description' %><br />
+    <%= f.text_field :text %>
+    <%= f.submit _("Search") %>
+  </p>
+<% end %>
 <p>
   Total <%=h @thumbs.total_entries %>
 </p>

diff --git a/config/environment.rb b/config/environment.rb
index 554590c..92bfe55 100644 (file)
--- a/config/environment.rb
+++ b/config/environment.rb
@@ -30,6 +30,8 @@ Rails::Initializer.run do |config|
   # you must remove the Active Record framework.
   config.frameworks -= [ :active_resource, :action_mailer ]
 
+  config.active_record.schema_format = :sql
+
   # Activate observers that should always be running
   # config.active_record.observers = :cacher, :garbage_collector, :forum_observer
   config.active_record.colorize_logging = false

diff --git a/db/development_structure.sql b/db/development_structure.sql
new file mode 100644 (file)
index 0000000..2447249
--- /dev/null
+++ b/db/development_structure.sql
@@ -0,0 +1,435 @@
+--
+-- PostgreSQL database dump
+--
+
+SET statement_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = off;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET escape_string_warning = off;
+
+SET search_path = public, pg_catalog;
+
+--
+-- Name: furigana(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION furigana(text) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/textsearch_ja', 'furigana';
+
+
+--
+-- Name: hiragana(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION hiragana(text) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/textsearch_ja', 'hiragana';
+
+
+--
+-- Name: ja_analyze(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ja_analyze(text, OUT word text, OUT type text, OUT subtype1 text, OUT subtype2 text, OUT subtype3 text, OUT conjtype text, OUT conjugation text, OUT basic text, OUT ruby text, OUT pronounce text) RETURNS SETOF record
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/textsearch_ja', 'ja_analyze';
+
+
+--
+-- Name: ja_normalize(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ja_normalize(text) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/textsearch_ja', 'ja_normalize';
+
+
+--
+-- Name: ja_wakachi(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ja_wakachi(text) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/textsearch_ja', 'ja_wakachi';
+
+
+--
+-- Name: katakana(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION katakana(text) RETURNS text
+    LANGUAGE c IMMUTABLE STRICT
+    AS '$libdir/textsearch_ja', 'katakana';
+
+
+--
+-- Name: ts_ja_end(internal); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ts_ja_end(internal) RETURNS void
+    LANGUAGE c STRICT
+    AS '$libdir/textsearch_ja', 'ts_ja_end';
+
+
+--
+-- Name: ts_ja_gettoken(internal, internal, internal); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ts_ja_gettoken(internal, internal, internal) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/textsearch_ja', 'ts_ja_gettoken';
+
+
+--
+-- Name: ts_ja_lexize(internal, internal, internal, internal); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ts_ja_lexize(internal, internal, internal, internal) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/textsearch_ja', 'ts_ja_lexize';
+
+
+--
+-- Name: ts_ja_start(internal, integer); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION ts_ja_start(internal, integer) RETURNS internal
+    LANGUAGE c STRICT
+    AS '$libdir/textsearch_ja', 'ts_ja_start';
+
+
+--
+-- Name: web_query(text); Type: FUNCTION; Schema: public; Owner: -
+--
+
+CREATE FUNCTION web_query(text) RETURNS text
+    LANGUAGE sql IMMUTABLE STRICT
+    AS $_$
+  SELECT regexp_replace(regexp_replace(regexp_replace($1,
+    E'(^|\\s+)-', E'\\1!', 'g'),
+    E'\\s+OR\\s+', '|', 'g'),
+    E'\\s+', '&', 'g');
+$_$;
+
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: arcs; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE arcs (
+    id integer NOT NULL,
+    code character varying(10) NOT NULL,
+    extname character varying(10) NOT NULL,
+    location_id integer,
+    summary character varying(255),
+    origname character varying(255),
+    created_at timestamp without time zone,
+    updated_at timestamp without time zone
+);
+
+
+--
+-- Name: arcs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE arcs_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: arcs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE arcs_id_seq OWNED BY arcs.id;
+
+
+--
+-- Name: locations; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE locations (
+    id integer NOT NULL,
+    code character varying(10) NOT NULL,
+    site character varying(255) NOT NULL,
+    summary character varying(255) NOT NULL,
+    created_at timestamp without time zone,
+    updated_at timestamp without time zone
+);
+
+
+--
+-- Name: locations_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE locations_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: locations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE locations_id_seq OWNED BY locations.id;
+
+
+--
+-- Name: pmds; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE pmds (
+    id integer NOT NULL,
+    arc_id integer,
+    path character varying(255) NOT NULL,
+    created_at timestamp without time zone,
+    updated_at timestamp without time zone
+);
+
+
+--
+-- Name: pmds_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE pmds_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: pmds_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE pmds_id_seq OWNED BY pmds.id;
+
+
+--
+-- Name: schema_migrations; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE schema_migrations (
+    version character varying(255) NOT NULL
+);
+
+
+--
+-- Name: thumbs; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE thumbs (
+    video_id character varying(15) NOT NULL,
+    title character varying(255),
+    description text,
+    created_at timestamp without time zone,
+    updated_at timestamp without time zone,
+    vector tsvector
+);
+
+
+--
+-- Name: vmds; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE vmds (
+    id integer NOT NULL,
+    arc_id integer,
+    path character varying(255) NOT NULL,
+    created_at timestamp without time zone,
+    updated_at timestamp without time zone
+);
+
+
+--
+-- Name: vmds_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE vmds_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: vmds_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE vmds_id_seq OWNED BY vmds.id;
+
+
+--
+-- Name: xes; Type: TABLE; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE TABLE xes (
+    id integer NOT NULL,
+    arc_id integer,
+    path character varying(255) NOT NULL,
+    created_at timestamp without time zone,
+    updated_at timestamp without time zone
+);
+
+
+--
+-- Name: xes_id_seq; Type: SEQUENCE; Schema: public; Owner: -
+--
+
+CREATE SEQUENCE xes_id_seq
+    START WITH 1
+    INCREMENT BY 1
+    NO MAXVALUE
+    NO MINVALUE
+    CACHE 1;
+
+
+--
+-- Name: xes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
+--
+
+ALTER SEQUENCE xes_id_seq OWNED BY xes.id;
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE arcs ALTER COLUMN id SET DEFAULT nextval('arcs_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE locations ALTER COLUMN id SET DEFAULT nextval('locations_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE pmds ALTER COLUMN id SET DEFAULT nextval('pmds_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE vmds ALTER COLUMN id SET DEFAULT nextval('vmds_id_seq'::regclass);
+
+
+--
+-- Name: id; Type: DEFAULT; Schema: public; Owner: -
+--
+
+ALTER TABLE xes ALTER COLUMN id SET DEFAULT nextval('xes_id_seq'::regclass);
+
+
+--
+-- Name: arcs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY arcs
+    ADD CONSTRAINT arcs_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: locations_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY locations
+    ADD CONSTRAINT locations_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: pmds_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY pmds
+    ADD CONSTRAINT pmds_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: vmds_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY vmds
+    ADD CONSTRAINT vmds_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: xes_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
+--
+
+ALTER TABLE ONLY xes
+    ADD CONSTRAINT xes_pkey PRIMARY KEY (id);
+
+
+--
+-- Name: index_thumbs_on_vector; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE INDEX index_thumbs_on_vector ON thumbs USING gin (vector);
+
+
+--
+-- Name: index_thumbs_on_video_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE UNIQUE INDEX index_thumbs_on_video_id ON thumbs USING btree (video_id);
+
+
+--
+-- Name: unique_schema_migrations; Type: INDEX; Schema: public; Owner: -; Tablespace: 
+--
+
+CREATE UNIQUE INDEX unique_schema_migrations ON schema_migrations USING btree (version);
+
+
+--
+-- Name: tsvectorupdate; Type: TRIGGER; Schema: public; Owner: -
+--
+
+CREATE TRIGGER tsvectorupdate
+    BEFORE INSERT OR UPDATE ON thumbs
+    FOR EACH ROW
+    EXECUTE PROCEDURE tsvector_update_trigger('vector', 'pg_catalog.japanese', 'description');
+
+
+--
+-- PostgreSQL database dump complete
+--
+
+INSERT INTO schema_migrations (version) VALUES ('20100608074721');
+
+INSERT INTO schema_migrations (version) VALUES ('20100608080758');
+
+INSERT INTO schema_migrations (version) VALUES ('20100608081439');
+
+INSERT INTO schema_migrations (version) VALUES ('20100608143904');
+
+INSERT INTO schema_migrations (version) VALUES ('20100608144944');
+
+INSERT INTO schema_migrations (version) VALUES ('20100612234341');
+
+INSERT INTO schema_migrations (version) VALUES ('20100613202235');
+
+INSERT INTO schema_migrations (version) VALUES ('20100613202356');
+
+INSERT INTO schema_migrations (version) VALUES ('20100613205243');
\ No newline at end of file

diff --git a/db/migrate/20100613202356_create_trigger_tsvectorupdate.rb b/db/migrate/20100613202356_create_trigger_tsvectorupdate.rb
index e9858f1..26c7ce5 100644 (file)
--- a/db/migrate/20100613202356_create_trigger_tsvectorupdate.rb
+++ b/db/migrate/20100613202356_create_trigger_tsvectorupdate.rb
@@ -3,7 +3,7 @@ class CreateTriggerTsvectorupdate < ActiveRecord::Migration
     execute <<-SQL
       CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
           ON thumbs FOR EACH ROW EXECUTE PROCEDURE
-          tsvector_update_trigger(vector, 'pg_catalog.japanese', description)
+          tsvector_update_trigger(vector, 'pg_catalog.japanese', title, description)
     SQL
   end