From c30dd0e5d096f0e70acda338b5cf8505d54bae74 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Mon, 21 Dec 2015 05:02:49 +0000 Subject: Add FTS index and supporting functions for news --- gentoobrowse/datasources/schema.sql | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) 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: -- cgit v1.2.3