diff options
| -rw-r--r-- | gentoobrowse/console/changelogs.xml | 84 | ||||
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 13 | ||||
| -rw-r--r-- | 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 @@ <?xml version="1.0"?> <packageimport name="bugassociate" xmlns:xi="http://www.w3.org/2001/XInclude" xmlns:project2="http://project2.randomdan.homeip.net"> - <project2:sqlrows name="updatedlogs" datasource="postgres"> - <sql> - 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 - </sql> - </project2:sqlrows> - <project2:streamrows name="logcontent" encoding="utf-8" newline="" fieldSep="" quoteChar=""> - <project2:filestream> - <path source="parent" name="path" depth="1" /> - </project2:filestream> - <columns> - <text /> - </columns> - </project2:streamrows> - <project2:regexrows name="logentries"> - <sourceText source="parent" name="text" depth="1" /> - <regex>(\d+ *(?:jan|feb|mar|apr|may|jun|jul|aug|sep|oct|nov|dec)\w* \d+); *([^<]+)? *<([^ >]+)[>\s]\s*(?:[^:]*:)?\s*(.*?)(?:\R\*|\s*$|\R\R)</regex> - <columns> - <date /> - <person /> - <email /> - <comment /> - </columns> - </project2:regexrows> <project2:sqlmerge name="changelogs" datasource="postgres" targettable="package_changelogs"> <updatewhere> - 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) </updatewhere> - <project2:iterate name="seelogs" source="updatedlogs"> - <project2:iterate name="seelog" source="logcontent"> - <project2:iterate name="seeentries" source="logentries"> - <project2:if> - <project2:all> - <project2:validdatetest format="%d %b %Y"> - <apply-to source="parent" name="date" depth="1" warn="no" /> - </project2:validdatetest> - <project2:regextest regex=".+@.+"> - <apply-to source="parent" name="email" depth="1" /> - </project2:regextest> - </project2:all> - <project2:sqlmergeinsert> - <parameters> - <packageid source="parent" name="packageid" depth="3" /> - <date source="parent" name="date" depth="1" /> - <n source="parent" attribute="rownum" depth="1" /> - <person source="parent" name="person" depth="1" /> - <email source="parent" name="email" depth="1" /> - <comment source="parent" name="comment" depth="1" /> - </parameters> - </project2:sqlmergeinsert> - </project2:if> - </project2:iterate> - </project2:iterate> - </project2:iterate> + <sql> + 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 + </sql> <columns> <packageid key="true" /> <date key="true" /> <n key="true" /> + <repoid key="true" /> <person /> <email /> <comment /> 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 @@ <filter name="pkg"> WHERE packageid = <param name="pkgid" /> </filter> - ORDER BY date DESC, n + ORDER BY date DESC, n DESC </sql> </project2:sqlrows> </block> |