From c5c2d3d5514554008217683484075c18cfc45244 Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Fri, 7 Aug 2015 22:53:33 +0100
Subject: Do package metadata inline

---
 gentoobrowse/console/packageimport.xml   | 30 ++++++++++++++++---
 gentoobrowse/console/packagemetadata.xml | 49 --------------------------------
 gentoobrowse/datasources/schema.sql      |  4 +--
 3 files changed, 28 insertions(+), 55 deletions(-)
 delete mode 100644 gentoobrowse/console/packagemetadata.xml

diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml
index c0061b3..7a98ef9 100644
--- a/gentoobrowse/console/packageimport.xml
+++ b/gentoobrowse/console/packageimport.xml
@@ -31,18 +31,41 @@
 		</sql>
 	</project2:sqltask>
 
-	<project2:sqlmerge name="packages" datasource="postgres" targettable="packages" useview="yes">
+	<project2:sqlmerge name="packages" datasource="postgres" targettable="packages" useview="no">
 		<sql>
-			SELECT categoryid, pkgname AS name, description
+			SELECT d.categoryid, d.pkgname AS name, description, maintainer, maintainername, herd, summary, image
 			FROM (
-					SELECT categoryid, pkgname, description, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY versioninst desc) r
+					SELECT categoryid, pkgname, description, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY version DESC) r
 					FROM ebuilddefs) d
+						LEFT OUTER JOIN (
+							SELECT categoryid, pkgname, maintainer, maintainername, herd, r,
+								TRIM(REGEXP_REPLACE(REGEXP_REPLACE(COALESCE(longdescen, longdesc), '&lt;&lt;data:image/[^>]+&gt;&gt;', ''), '\s+', ' ', 'g')) AS summary,
+								(SELECT REGEXP_MATCHES(COALESCE(longdescen, longdesc), '&lt;&lt;(data:image/[^&gt;]+)&gt;&gt;')) AS image
+							FROM (
+								SELECT categoryid, pkgname, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY repoid DESC) r,
+									CAST((XPATH('/pkgmetadata/maintainer/email/text()', doc))[1] AS TEXT) maintainer,
+									CAST((XPATH('/pkgmetadata/maintainer/name/text()', doc))[1] AS TEXT) maintainername,
+									CAST((XPATH('/pkgmetadata/longdescription[@lang="en"]/text()', doc))[1] AS TEXT) longdescen,
+									CAST((XPATH('/pkgmetadata/longdescription[not(@lang)]/text()', doc))[1] AS TEXT) longdesc,
+									CAST((XPATH('/pkgmetadata/herd/text()', doc))[1] AS TEXT) herd
+								FROM (
+									SELECT f.repoid, categoryid, f.pathparts[2] pkgname, XMLPARSE(DOCUMENT PG_READ_FILE(f.filename)) doc
+									FROM files f, categories c
+									WHERE filetypeid = 4
+									AND f.pathparts[1] = c.name) x) x
+							) m ON m.categoryid = d.categoryid AND m.pkgname = d.pkgname
 			WHERE d.r = 1
+			AND m.r = 1
 		</sql>
 		<columns>
 			<categoryid key="true" />
 			<name key="true" />
 			<description />
+			<maintainer />
+			<maintainername />
+			<herd />
+			<summary />
+			<image />
 		</columns>
 	</project2:sqlmerge>
 
@@ -168,7 +191,6 @@
 	<xi:include href="use.local.xml" />
 	<xi:include href="use.grouped.xml" />
 	<xi:include href="categorymetadata.xml" />
-	<xi:include href="packagemetadata.xml" />
 	<xi:include href="packagemasks.xml" />
 	<xi:include href="bugassociate.xml" />
 </packageimport>
diff --git a/gentoobrowse/console/packagemetadata.xml b/gentoobrowse/console/packagemetadata.xml
deleted file mode 100644
index b66abfe..0000000
--- a/gentoobrowse/console/packagemetadata.xml
+++ /dev/null
@@ -1,49 +0,0 @@
-<?xml version="1.0"?>
-<homepages name="bugimport" xmlns:xi="http://www.w3.org/2001/XInclude"
-		xmlns:project2="http://project2.randomdan.homeip.net">
-	<project2:sqlrows name="pkgblanksummaries" datasource="postgres">
-		<sql>
-			SELECT p.packageid, 'file://' || ft.searchroot || f.filename AS path, f.moddate
-			FROM packages p, categories c, files f, filetypes ft
-			WHERE (f.cachedat IS NULL OR f.cachedat != f.moddate)
-			AND p.categoryid = c.categoryid
-			AND f.filename = c.name || '/' || p.name || '/metadata.xml'
-			AND f.filetypeid = ft.filetypeid
-			AND ft.filetypeid = 4
-		</sql>
-	</project2:sqlrows>
-	<project2:xpathrows name="pkg" html="false" warnings="true">
-		<url source="parent" name="path" depth="1" />
-		<filterviews>
-			<pkgmetadata root="/pkgmetadata">
-				<fields>
-					<longdesc xpath="longdescription" />
-					<maintainer xpath="maintainer/email" />
-					<herd xpath="herd" />
-				</fields>
-			</pkgmetadata>
-		</filterviews>
-	</project2:xpathrows>
-	<project2:iterate name="eachpkg" source="pkgblanksummaries">
-		<project2:iterate name="pkgdetail" source="pkg" filter="pkgmetadata">
-			<project2:sqltask name="update" datasource="postgres">
-				<sql>
-					UPDATE packages SET
-						summary = regexp_replace(<param source="parent" name="longdesc" />, '&lt;&lt;data:image/[^&gt;]+&gt;&gt;', ''),
-						image = (SELECT regexp_matches(<param source="parent" name="longdesc" />, '&lt;&lt;(data:image/[^&gt;]+)&gt;&gt;'))[1],
-						maintainer = <param source="parent" name="maintainer" />,
-						herd = <param source="parent" name="herd" />,
-						metadatadate = <param source="parent" name="moddate" depth="2" />
-					WHERE packageid = <param source="parent" name="packageid" depth="2" />
-				</sql>
-			</project2:sqltask>
-		</project2:iterate>
-	</project2:iterate>
-	<project2:sqltask name="update" datasource="postgres">
-		<sql>
-			UPDATE files SET
-				cachedat = moddate
-			WHERE filetypeid = 4
-		</sql>
-	</project2:sqltask>
-</homepages>
diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql
index be36a20..ffe4111 100644
--- a/gentoobrowse/datasources/schema.sql
+++ b/gentoobrowse/datasources/schema.sql
@@ -201,8 +201,8 @@ CREATE TABLE packages (
     summary text,
     maintainer text,
     herd text,
-    metadatadate timestamp without time zone,
-    image text
+    image text,
+    maintainername text
 );
 ALTER TABLE packages OWNER TO gentoo;
 -- Name: TABLE packages; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo
-- 
cgit v1.2.3