Replace change logs

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+); *([^&lt;]+)? *&lt;([^ &gt;]+)[&gt;\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+); (.+)? &lt;(.+@[^ &gt;]+)[&gt;\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>