From 8261e4f924199e86f8ef851dae025458843c23ac Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sun, 12 Jun 2016 17:33:19 +0100 Subject: Remove the need to associate bugs and packages with functional index --- gentoobrowse-api/db/schema.sql | 37 +++++++--------------- gentoobrowse-api/service/maintenanceBugs.cpp | 1 - gentoobrowse-api/service/maintenanceCommon.cpp | 13 -------- .../service/maintenancePackageTree.cpp | 1 - gentoobrowse-api/service/maintenanceimpl.h | 1 - .../service/sql/portage/getPackageBugs.sql | 7 ++-- gentoobrowse-api/unittests/data.sql | 1 - .../unittests/fixtures/package_bugs.dat | 2 -- gentoobrowse-api/unittests/testPortage.cpp | 6 ++-- 9 files changed, 18 insertions(+), 51 deletions(-) delete mode 100644 gentoobrowse-api/unittests/fixtures/package_bugs.dat 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 ) -- cgit v1.2.3