From d0ed50175150bd21bb8d14b32b93052504073ab6 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 12 Jun 2016 15:13:11 +0100 Subject: Index changelog by package --- gentoobrowse-api/db/schema.sql | 13 +++++++++++++ 1 file changed, 13 insertions(+) diff --git a/gentoobrowse-api/db/schema.sql b/gentoobrowse-api/db/schema.sql index dfe6f42..4a90665 100644 --- a/gentoobrowse-api/db/schema.sql +++ b/gentoobrowse-api/db/schema.sql @@ -52,6 +52,17 @@ CREATE TABLE bugs ( ALTER TABLE bugs OWNER TO gentoo; -- Name: TABLE bugs; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo COMMENT ON TABLE bugs IS 'Import of Gentoo Bugs bug list from snapshots'; +-- Name: changelogpackages(changelog); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo +CREATE FUNCTION changelogpackages(cl changelog) RETURNS text[] + LANGUAGE plpgsql IMMUTABLE + AS $$ +BEGIN + RETURN (SELECT ARRAY_AGG(split_part(file, '/', 1) || '/' || split_part(file, '/', 2)) + FROM UNNEST(cl.files) file + WHERE split_part(file, '/', 3) LIKE '%.ebuild'); +END +$$; +ALTER FUNCTION gentoobrowse.changelogpackages(cl changelog) OWNER TO gentoo; -- Name: changelogfts(changelog); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo CREATE FUNCTION changelogfts(cl changelog) RETURNS tsvector LANGUAGE plpgsql IMMUTABLE @@ -742,6 +753,8 @@ ALTER TABLE use_local CLUSTER ON use_local_pkey; CREATE INDEX idx_changelog_committime ON changelog USING btree (committime); -- Name: idx_changelog_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_changelog_fts ON changelog USING gin (changelogfts(changelog.*)); +-- Name: idx_changelog_packages; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: +CREATE INDEX idx_changelog_packages ON changelog USING gin (changelogpackages(changelog.*)); -- Name: idx_bug_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: CREATE INDEX idx_bug_fts ON bugs USING gin (bugfts(bugs.*)); -- Name: idx_bugs_firstseen; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: -- cgit v1.2.3