From 516ab43b9bab3da361fab4c4cea0731645dbe159 Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Sat, 8 Aug 2015 01:58:52 +0100 Subject: Replace change logs --- gentoobrowse/console/changelogs.xml | 84 +++++++++++-------------------------- gentoobrowse/datasources/schema.sql | 13 ++++-- gentoobrowse/views/package.xml | 2 +- 3 files changed, 34 insertions(+), 65 deletions(-) diff --git a/gentoobrowse/console/changelogs.xml b/gentoobrowse/console/changelogs.xml index 6622a1c..0e191bf 100644 --- a/gentoobrowse/console/changelogs.xml +++ b/gentoobrowse/console/changelogs.xml @@ -1,77 +1,41 @@ - - - SELECT f.filename, ft.searchroot || f.filename AS path, p.packageid - FROM files f, filetypes ft, packages p, categories c - WHERE f.filetypeid = ft.filetypeid - AND (f.cachedat IS NULL OR f.cachedat != f.moddate) - AND ft.filetypeid = 2 - AND p.name = SPLIT_PART(filename, '/', 2) - AND c.name = SPLIT_PART(filename, '/', 1) - AND c.categoryid = p.categoryid - - - - - - - - - - - - - (\d+ *(?:jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)\w* \d+); *([^<]+)? *<([^ >]+)[>\s]\s*(?:[^:]*:)?\s*(.*?)(?:\R\*|\s*$|\R\R) - - - - - - - - a.packageid IN ( - SELECT p.packageid + (a.packageid, a.repoid) IN ( + SELECT p.packageid, f.repoid FROM files f, packages p, categories c WHERE f.filetypeid = 2 AND (f.cachedat IS NULL OR f.cachedat != f.moddate) - AND p.name = SPLIT_PART(filename, '/', 2) - AND c.name = SPLIT_PART(filename, '/', 1) + AND p.name = f.pathparts[2] + AND c.name = f.pathparts[1] AND c.categoryid = p.categoryid) - - - - - - - - - - - - - - - - - - - - - - - - - - + + SELECT repoid, p.packageid, RANK() OVER(PARTITION BY repoid, packageid, TO_DATE(l[1], 'DD Mon YYYY') ORDER BY n DESC) AS n, + TO_DATE(l[1], 'DD Mon YYYY') AS date, + TRIM(l[2]) AS person, + TRIM(l[3]) AS email, + TRIM(l[4]) AS comment + FROM ( + SELECT repoid, cat, pkg, n, + REGEXP_MATCHES(r, '(\d+ *(?:jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec) \d+); (.+)? <(.+@[^ >]+)[>\s] (?:[^:]*:)? (.*)', 'i') l + FROM ( + SELECT repoid, pathparts[1] cat, pathparts[2] pkg, REGEXP_REPLACE(r, '\s+', ' ', 'g') r, ROW_NUMBER() OVER() n + FROM files f, REGEXP_SPLIT_TO_TABLE(PG_READ_FILE(filename), '\n\s*\n') r + WHERE filetypeid = 2 + AND (f.cachedat IS NULL OR f.cachedat != f.moddate)) f + ) f, packages p, categories c + WHERE p.name = f.pkg + AND c.name = f.cat + AND p.categoryid = c.categoryid + + diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql index 83ee5a3..d737ef7 100644 --- a/gentoobrowse/datasources/schema.sql +++ b/gentoobrowse/datasources/schema.sql @@ -311,13 +311,13 @@ COMMENT ON TABLE ebuild_uses IS 'Use flags affecting ebuilds'; CREATE TABLE ebuilds ( ebuildid integer NOT NULL, packageid integer NOT NULL, - repoid integer, version text NOT NULL, versioninst ebuildversion NOT NULL, slot text NOT NULL, license text, firstseen timestamp without time zone NOT NULL, - moddate timestamp without time zone NOT NULL + moddate timestamp without time zone NOT NULL, + repoid integer ); ALTER TABLE ebuilds OWNER TO gentoo; -- Name: TABLE ebuilds; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo @@ -404,7 +404,8 @@ CREATE TABLE package_changelogs ( n smallint NOT NULL, person text, email text, - comment text + comment text, + repoid integer NOT NULL ); ALTER TABLE package_changelogs OWNER TO gentoo; -- Name: package_urls; Type: TABLE; Schema: gentoobrowse; Owner: gentoo; Tablespace: @@ -583,7 +584,7 @@ ALTER TABLE ONLY files ADD CONSTRAINT pk_files PRIMARY KEY (fileid); -- Name: pk_package_changelogs; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: ALTER TABLE ONLY package_changelogs - ADD CONSTRAINT pk_package_changelogs PRIMARY KEY (packageid, date, n); + ADD CONSTRAINT pk_package_changelogs PRIMARY KEY (packageid, date, n, repoid); ALTER TABLE package_changelogs CLUSTER ON pk_package_changelogs; -- Name: pk_packagebugs; Type: CONSTRAINT; Schema: gentoobrowse; Owner: gentoo; Tablespace: ALTER TABLE ONLY package_bugs @@ -684,6 +685,7 @@ CREATE INDEX idx_userpackages_package ON user_packages USING btree (packageid); -- Name: fk_ebuild_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuilds ADD CONSTRAINT fk_ebuild_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; +-- Name: fk_ebuild_repo; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuilds ADD CONSTRAINT fk_ebuild_repo FOREIGN KEY (repoid) REFERENCES repos(repoid) ON UPDATE CASCADE ON DELETE CASCADE; -- Name: fk_ebuildarch_file; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo @@ -719,6 +721,9 @@ ALTER TABLE ONLY package_bugs -- Name: fk_packagechangelogs_package; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY package_changelogs ADD CONSTRAINT fk_packagechangelogs_package FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; +-- Name: fk_packagechangelogs_repo; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo +ALTER TABLE ONLY package_changelogs + ADD CONSTRAINT fk_packagechangelogs_repo FOREIGN KEY (repoid) REFERENCES repos(repoid) 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; diff --git a/gentoobrowse/views/package.xml b/gentoobrowse/views/package.xml index bb8be37..d063177 100644 --- a/gentoobrowse/views/package.xml +++ b/gentoobrowse/views/package.xml @@ -162,7 +162,7 @@ WHERE packageid = - ORDER BY date DESC, n + ORDER BY date DESC, n DESC -- cgit v1.2.3