Remove the need to associate bugs and packages with functional index

9 files changed, 18 insertions, 51 deletions

diff --git a/gentoobrowse-api/db/schema.sql b/gentoobrowse-api/db/schema.sql
index 783bbcf..532ebbb 100644
--- a/gentoobrowse-api/db/schema.sql
+++ b/gentoobrowse-api/db/schema.sql
@@ -63,6 +63,15 @@ BEGIN
 END
 $$;
 ALTER FUNCTION gentoobrowse.changelogpackages(cl changelog) OWNER TO gentoo;
+-- Name: bugpackages(bugs); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo
+CREATE FUNCTION bugpackages(b bugs) RETURNS text[]
+    LANGUAGE plpgsql IMMUTABLE
+    AS $$
+BEGIN
+	RETURN ARRAY_AGG(p[1]) FROM REGEXP_MATCHES(b.summary, '([[:alnum:]-]+/[[:alnum:]_-]+?)(?:-(?:[0-9][.0-9]*[[:alpha:]]?\*?(?:(?:_(?:alpha|beta|pre|rc|p))?[[:digit:]]*)*(?:-r[[:digit:]]+)?))?', 'g') p;
+END
+$$;
+ALTER FUNCTION gentoobrowse.bugpackages(b bugs) OWNER TO gentoo;
 -- Name: changelogfts(changelog); Type: FUNCTION; Schema: gentoobrowse; Owner: gentoo
 CREATE FUNCTION changelogfts(cl changelog) RETURNS tsvector
     LANGUAGE plpgsql IMMUTABLE
@@ -462,15 +471,6 @@ BEGIN
 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,
-    bugid integer NOT NULL,
-    firstseen timestamp without time zone DEFAULT now() NOT NULL
-);
-ALTER TABLE package_bugs OWNER TO gentoo;
--- Name: TABLE package_bugs; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo
-COMMENT ON TABLE package_bugs IS 'Association of bugs to products by bug description';
 -- Name: package_urls; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
 CREATE TABLE package_urls (
     packageid integer NOT NULL,
@@ -668,10 +668,6 @@ ALTER TABLE ONLY files
 -- Name: pk_news; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
 ALTER TABLE ONLY news
     ADD CONSTRAINT pk_news PRIMARY KEY (newsid);
--- Name: pk_packagebugs; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
-ALTER TABLE ONLY package_bugs
-    ADD CONSTRAINT pk_packagebugs PRIMARY KEY (packageid, bugid);
-ALTER TABLE package_bugs CLUSTER ON pk_packagebugs;
 -- Name: pk_packagemasksets; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
 ALTER TABLE ONLY masksets
     ADD CONSTRAINT pk_packagemasksets PRIMARY KEY (setno);
@@ -740,6 +736,8 @@ CREATE INDEX idx_changelog_committime ON changelog USING btree (committime);
 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_bugs_packages; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
+CREATE INDEX idx_bugs_packages ON bugs USING gin (bugpackages(bugs.*));
 -- 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: 
@@ -760,8 +758,6 @@ CREATE INDEX idx_news_fts ON news USING gin (newsfts(news.*));
 CREATE INDEX idx_news_posted ON news USING btree (posted);
 -- Name: idx_package_name; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
 CREATE INDEX idx_package_name ON packages USING btree (name);
--- Name: idx_packagebugs_bug; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
-CREATE INDEX idx_packagebugs_bug ON package_bugs USING btree (bugid);
 -- Name: idx_packages_fts; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
 CREATE INDEX idx_packages_fts ON packages USING gin (packagefts(packages.*));
 -- Name: idx_packages_herd; Type: INDEX; Schema: gentoobrowse; Owner: gentoo; Tablespace: 
@@ -808,12 +804,6 @@ ALTER TABLE ONLY ebuild_uses
 -- Name: fk_files_type; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo
 ALTER TABLE ONLY files
     ADD CONSTRAINT fk_files_type FOREIGN KEY (filetypeid) REFERENCES filetypes(filetypeid) ON UPDATE CASCADE;
--- Name: fk_packagebugs_bug; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo
-ALTER TABLE ONLY package_bugs
-    ADD CONSTRAINT fk_packagebugs_bug FOREIGN KEY (bugid) REFERENCES bugs(bugid) ON UPDATE CASCADE ON DELETE CASCADE;
--- Name: fk_packagebugs_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo
-ALTER TABLE ONLY package_bugs
-    ADD CONSTRAINT fk_packagebugs_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE;
 -- Name: fk_packageurls_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo
 ALTER TABLE ONLY package_urls
     ADD CONSTRAINT fk_packageurls_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE;
@@ -914,11 +904,6 @@ REVOKE ALL ON TABLE masksets FROM PUBLIC;
 REVOKE ALL ON TABLE masksets FROM gentoo;
 GRANT ALL ON TABLE masksets TO gentoo;
 GRANT SELECT ON TABLE masksets TO apache;
--- Name: package_bugs; Type: ACL; Schema: gentoobrowse; Owner: gentoo
-REVOKE ALL ON TABLE package_bugs FROM PUBLIC;
-REVOKE ALL ON TABLE package_bugs FROM gentoo;
-GRANT ALL ON TABLE package_bugs TO gentoo;
-GRANT SELECT ON TABLE package_bugs TO apache;
 -- Name: package_urls; Type: ACL; Schema: gentoobrowse; Owner: gentoo
 REVOKE ALL ON TABLE package_urls FROM PUBLIC;
 REVOKE ALL ON TABLE package_urls FROM gentoo;
diff --git a/gentoobrowse-api/service/maintenanceBugs.cpp b/gentoobrowse-api/service/maintenanceBugs.cpp
index fbd91ec..c850929 100644
--- a/gentoobrowse-api/service/maintenanceBugs.cpp
+++ b/gentoobrowse-api/service/maintenanceBugs.cpp
@@ -91,7 +91,6 @@ namespace Gentoo {
 				blp.parse_stream(cs);
 			}
 			dbc->patchTable(&tp);
-			associateBugs(dbc.get());
 			Utils::Database::drop(dbc.get(), tp.src);
 		}
 	}
diff --git a/gentoobrowse-api/service/maintenanceCommon.cpp b/gentoobrowse-api/service/maintenanceCommon.cpp
index 95ebba8..49101ee 100644
--- a/gentoobrowse-api/service/maintenanceCommon.cpp
+++ b/gentoobrowse-api/service/maintenanceCommon.cpp
@@ -12,19 +12,6 @@ namespace Gentoo {
 		{
 			return c.adapter->getCommunicator()->getProperties();
 		}
-
-		void
-		Maintenance::associateBugs(DB::Connection * dbc)
-		{
-			DB::TablePatch tp;
-			tp.src = Utils::Database::createTempWith(dbc,
-					sql::maintenance::bugAssociate.getSql(), { "bugId", "packageId" });
-			tp.dest = "gentoobrowse.package_bugs";
-			tp.pk = { "bugId", "packageId" };
-			tp.cols = { "bugId", "packageId" };
-			dbc->patchTable(&tp);
-			Utils::Database::drop(dbc, tp.src);
-		}
 	}
 }
 
diff --git a/gentoobrowse-api/service/maintenancePackageTree.cpp b/gentoobrowse-api/service/maintenancePackageTree.cpp
index 06ba502..8d7ab72 100644
--- a/gentoobrowse-api/service/maintenancePackageTree.cpp
+++ b/gentoobrowse-api/service/maintenancePackageTree.cpp
@@ -206,7 +206,6 @@ namespace Gentoo {
 			createTempFileList(dbc.get(), tmp);
 			processChanges(dbc.get(), tmp);
 			dbc->execute("SET search_path = public, pg_catalog");
-			associateBugs(dbc.get());
 		}
 	}
 }
diff --git a/gentoobrowse-api/service/maintenanceimpl.h b/gentoobrowse-api/service/maintenanceimpl.h
index 6206836..3a00ba5 100644
--- a/gentoobrowse-api/service/maintenanceimpl.h
+++ b/gentoobrowse-api/service/maintenanceimpl.h
@@ -36,7 +36,6 @@ namespace Gentoo {
 				void refreshPackageTree(const Ice::Current &) override;
 				void refreshBugs(const Ice::Current &) override;
 				void refreshChangeLogs(const Ice::Current &) override;
-				void associateBugs(DB::Connection *);
 
 			private:
 				static void createTempFileList(DB::Connection *, const boost::filesystem::path &);
diff --git a/gentoobrowse-api/service/sql/portage/getPackageBugs.sql b/gentoobrowse-api/service/sql/portage/getPackageBugs.sql
index 7361fa6..eac650e 100644
--- a/gentoobrowse-api/service/sql/portage/getPackageBugs.sql
+++ b/gentoobrowse-api/service/sql/portage/getPackageBugs.sql
@@ -1,5 +1,6 @@
 SELECT b.bugid, b.severity, b.status, b.summary, b.firstseen
-FROM gentoobrowse.bugs b, gentoobrowse.package_bugs pb
-WHERE b.bugid = pb.bugid
-AND pb.packageid = ?
+FROM gentoobrowse.bugs b, gentoobrowse.categories c, gentoobrowse.packages p
+WHERE p.packageid = ?
+AND p.categoryid = c.categoryid
+AND gentoobrowse.bugpackages(b) @> array[c.name || '/' || p.name]
 ORDER BY b.bugid
diff --git a/gentoobrowse-api/unittests/data.sql b/gentoobrowse-api/unittests/data.sql
index 44d1ce5..e7746a9 100644
--- a/gentoobrowse-api/unittests/data.sql
+++ b/gentoobrowse-api/unittests/data.sql
@@ -21,7 +21,6 @@ COPY gentoobrowse.filetypes (filetypeid, description, spec) FROM '$SCRIPTDIR/fix
 COPY gentoobrowse.files (filename, fileid, moddate, firstseen, cachedat, filetypeid, repoid, filesize, pathparts, encoding) FROM '$SCRIPTDIR/fixtures/files.dat';
 COPY gentoobrowse.license (name, legalbumph) FROM '$SCRIPTDIR/fixtures/license.dat';
 COPY gentoobrowse.news (newsid, title, posted, authorname, authoremail, atomspec, body, urls) FROM '$SCRIPTDIR/fixtures/news.dat';
-COPY gentoobrowse.package_bugs (packageid, bugid, firstseen) FROM '$SCRIPTDIR/fixtures/package_bugs.dat';
 COPY gentoobrowse.package_urls (packageid, url) FROM '$SCRIPTDIR/fixtures/package_urls.dat';
 COPY gentoobrowse.use_global (use, description) FROM '$SCRIPTDIR/fixtures/use_global.dat';
 COPY gentoobrowse.use_groups (usegroupid, name) FROM '$SCRIPTDIR/fixtures/use_groups.dat';
diff --git a/gentoobrowse-api/unittests/fixtures/package_bugs.dat b/gentoobrowse-api/unittests/fixtures/package_bugs.dat
deleted file mode 100644
index 3ef448f..0000000
--- a/gentoobrowse-api/unittests/fixtures/package_bugs.dat
+++ /dev/null
@@ -1,2 +0,0 @@
-55923	567108	2010-06-19 14:36:57.532747
-55925	567166	2010-06-19 14:36:57.532747
diff --git a/gentoobrowse-api/unittests/testPortage.cpp b/gentoobrowse-api/unittests/testPortage.cpp
index 13625ef..90ad707 100644
--- a/gentoobrowse-api/unittests/testPortage.cpp
+++ b/gentoobrowse-api/unittests/testPortage.cpp
@@ -178,10 +178,10 @@ BOOST_AUTO_TEST_CASE( getBug )
 
 BOOST_AUTO_TEST_CASE( getPackageBugs )
 {
-	auto bs = p->getPackageBugs(55925);
+	auto bs = p->getPackageBugs(54654);
 	BOOST_REQUIRE_EQUAL(1, bs.size());
-	BOOST_REQUIRE_EQUAL(567166, bs.front()->bugid);
-	BOOST_REQUIRE_EQUAL("2015-11-30T19:01:45.824590", bs.front()->firstseen);
+	BOOST_REQUIRE_EQUAL(567248, bs.front()->bugid);
+	BOOST_REQUIRE_EQUAL("2015-12-01T20:03:52.402138", bs.front()->firstseen);
 }
 
 BOOST_AUTO_TEST_CASE( getBugsSearch )