diff options
| author | Dan Goodliffe <dan@randomdan.homeip.net> | 2015-12-21 05:02:49 +0000 |
|---|---|---|
| committer | Dan Goodliffe <dan@randomdan.homeip.net> | 2015-12-21 05:02:49 +0000 |
| commit | c30dd0e5d096f0e70acda338b5cf8505d54bae74 (patch) | |
| tree | d614d19c6cb3a8a3e6d5d6d819e70bb5514beca7 | |
| parent | Switch lib64 references for native ones (diff) | |
| download | gentoobrowse-c30dd0e5d096f0e70acda338b5cf8505d54bae74.tar.bz2 gentoobrowse-c30dd0e5d096f0e70acda338b5cf8505d54bae74.tar.xz gentoobrowse-c30dd0e5d096f0e70acda338b5cf8505d54bae74.zip | |
Add FTS index and supporting functions for news
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 17 |
1 files changed, 17 insertions, 0 deletions
diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql index b4adf34..119d5bc 100644 --- a/gentoobrowse/datasources/schema.sql +++ b/gentoobrowse/datasources/schema.sql @@ -239,6 +239,12 @@ BEGIN END; $$; ALTER FUNCTION gentoobrowse.pathpartsmatchesspecs(pathparts text[], spec filterspec[]) OWNER TO gentoo; +-- Name: sum(tsvector); Type: AGGREGATE; Schema: gentoobrowse; Owner: postgres +CREATE AGGREGATE sum(tsvector) ( + SFUNC = tsvector_concat, + STYPE = tsvector +); +ALTER AGGREGATE gentoobrowse.sum(tsvector) OWNER TO postgres; -- Name: ~; Type: OPERATOR; Schema: gentoobrowse; Owner: gentoo CREATE OPERATOR ~ ( PROCEDURE = ebuildversionrange, @@ -391,6 +397,15 @@ CREATE TABLE news ( urls text[] ); ALTER TABLE news OWNER TO gentoo; +-- Name: newsfts(news); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +CREATE FUNCTION newsfts(n news) RETURNS tsvector + LANGUAGE plpgsql IMMUTABLE + AS $$ +BEGIN + RETURN SUM(t) FROM (SELECT TO_TSVECTOR('english', UNNEST(n.body)) t) a; +END +$$; +ALTER FUNCTION gentoobrowse.newsfts(n news) OWNER TO gentoo; -- Name: package_bugs; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE TABLE package_bugs ( packageid integer NOT NULL, @@ -671,6 +686,8 @@ CREATE INDEX idx_ebuildrdeps_package ON ebuild_rdeps USING btree (packageid); CREATE INDEX idx_ebuilds_firstseen ON ebuilds USING btree (firstseen); -- Name: idx_ebuilduses_use; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_ebuilduses_use ON ebuild_uses USING btree (use); +-- Name: idx_news_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +CREATE INDEX idx_news_fts ON news USING gin (newsfts(news.*)); -- Name: idx_news_posted; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_news_posted ON news USING btree (posted); -- Name: idx_package_name; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: |