From eed30a287b07d16c92e1b1712ef2b570f350854e Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Fri, 7 Aug 2015 23:24:46 +0100
Subject: Do category metadata inline

---
 gentoobrowse/console/categorymetadata.xml | 36 -------------------------------
 gentoobrowse/console/packageimport.xml    | 12 +++++++++--
 gentoobrowse/datasources/schema.sql       |  3 +--
 3 files changed, 11 insertions(+), 40 deletions(-)
 delete mode 100644 gentoobrowse/console/categorymetadata.xml

diff --git a/gentoobrowse/console/categorymetadata.xml b/gentoobrowse/console/categorymetadata.xml
deleted file mode 100644
index 780b2ba..0000000
--- a/gentoobrowse/console/categorymetadata.xml
+++ /dev/null
@@ -1,36 +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="catblanksummaries" datasource="postgres">
-		<sql>
-			SELECT c.categoryid, 'file://' || ft.searchroot || f.filename AS path, f.moddate
-			FROM categories c, files f, filetypes ft
-			WHERE (c.metadatadate IS NULL OR c.metadatadate != f.moddate)
-			AND f.filename = c.name || '/metadata.xml'
-			AND f.filetypeid = ft.filetypeid
-			AND ft.filetypeid = 4
-		</sql>
-	</project2:sqlrows>
-	<project2:xpathrows name="cat" html="false" warnings="true">
-		<url source="parent" name="path" depth="1" />
-		<filterviews>
-			<catmetadata root="/catmetadata">
-				<fields>
-					<longdesc xpath="longdescription[@lang='en']" />
-				</fields>
-			</catmetadata>
-		</filterviews>
-	</project2:xpathrows>
-	<project2:iterate name="eachcat" source="catblanksummaries">
-		<project2:iterate name="catdetail" source="cat" filter="catmetadata">
-			<project2:sqltask name="update" datasource="postgres">
-				<sql>
-					UPDATE categories SET
-						summary = <param source="parent" name="longdesc" />,
-						metadatadate = <param source="parent" name="moddate" depth="2" />
-					WHERE categoryid = <param source="parent" name="categoryid" depth="2" />
-				</sql>
-			</project2:sqltask>
-		</project2:iterate>
-	</project2:iterate>
-</homepages>
diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml
index 7a98ef9..04375ca 100644
--- a/gentoobrowse/console/packageimport.xml
+++ b/gentoobrowse/console/packageimport.xml
@@ -2,9 +2,18 @@
 <packageimport name="bugassociate" xmlns:xi="http://www.w3.org/2001/XInclude"
 		xmlns:project2="http://project2.randomdan.homeip.net">
 	<project2:sqlmerge name="categories" datasource="postgres" targettable="categories" useview="yes">
-		<sql>SELECT DISTINCT pathparts[3] AS name FROM files WHERE filetypeid = 1</sql>
+		<sql>
+			SELECT c.name, m.summary
+			FROM (SELECT DISTINCT pathparts[3] AS name FROM files WHERE filetypeid = 1) c
+				LEFT OUTER JOIN (
+					SELECT f.pathparts[1] AS name, RANK() OVER(PARTITION BY f.pathparts[1] ORDER BY repoid DESC) r,
+						TRIM(REGEXP_REPLACE(CAST((XPATH('/catmetadata/longdescription[@lang="en"]/text()', doc))[1] AS TEXT), '\s+', ' ', 'g')) summary
+					FROM files f, XMLPARSE(DOCUMENT pg_read_file(filename)) doc
+					WHERE filetypeid = 10) m ON m.name = c.name
+		</sql>
 		<columns>
 			<name key="true" />
+			<summary />
 		</columns>
 	</project2:sqlmerge>
 
@@ -190,7 +199,6 @@
 	<xi:include href="use.global.xml" />
 	<xi:include href="use.local.xml" />
 	<xi:include href="use.grouped.xml" />
-	<xi:include href="categorymetadata.xml" />
 	<xi:include href="packagemasks.xml" />
 	<xi:include href="bugassociate.xml" />
 </packageimport>
diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql
index ffe4111..83ee5a3 100644
--- a/gentoobrowse/datasources/schema.sql
+++ b/gentoobrowse/datasources/schema.sql
@@ -257,8 +257,7 @@ ALTER OPERATOR gentoobrowse.~ (text[], filterspec[]) OWNER TO postgres;
 CREATE TABLE categories (
     categoryid integer NOT NULL,
     name text,
-    summary text,
-    metadatadate timestamp without time zone
+    summary text
 );
 ALTER TABLE categories OWNER TO gentoo;
 -- Name: TABLE categories; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo
-- 
cgit v1.2.3