Migrate package import to project2 sqlmerge

Show bug summary on package page

6 files changed, 215 insertions, 12 deletions

diff --git a/gentoobrowse/browse.xslt b/gentoobrowse/browse.xslt
index db3f4f6..c356828 100644
--- a/gentoobrowse/browse.xslt
+++ b/gentoobrowse/browse.xslt
@@ -105,7 +105,10 @@
 	<xsl:apply-templates select="packageUrls" />
 	<xsl:apply-templates select="versions" />
 	<xsl:apply-templates select="deps" />
+	<xsl:apply-templates select="packageBugs" />
   </xsl:template>
+
+  <!-- Package URLs -->
   <xsl:template match="/gentoo/summary/package/packageUrls">
   	<div class="urls">
 	  <xsl:apply-templates match="url" />
@@ -119,14 +122,37 @@
 		<xsl:value-of select="url" />
 	</a>
   </xsl:template>
+
+  <!-- Package bugs -->
+  <xsl:template match="/gentoo/summary/package/packageBugs">
+  	<h3>Open bugs</h3>
+  	<ul>
+	  <xsl:apply-templates match="bug" />
+	</ul>
+  </xsl:template>
+  <xsl:template match="/gentoo/summary/package/packageBugs/bug">
+  	<li>
+		<a target="_blank">
+			<xsl:attribute name="href">http://bugs.gentoo.org/<xsl:value-of select="bugid" /></xsl:attribute>
+			<xsl:value-of select="summary" />
+		</a>
+		(<xsl:value-of select="bugid" />)
+	</li>
+  </xsl:template>
+
+  <!-- Version use flags -->
   <xsl:template match="/gentoo/summary/package/versionUses/use">
   	<xsl:value-of select="use" />
 	<xsl:text> </xsl:text>
   </xsl:template>
+
+  <!-- Version archs -->
   <xsl:template match="/gentoo/summary/package/versionArchs/arch">
   	<xsl:value-of select="arch" />
 	<xsl:text> </xsl:text>
   </xsl:template>
+
+  <!-- Package dependencies -->
   <xsl:template match="/gentoo/summary/package/deps">
   	<h3>Dependencies</h3>
 	<ul>
diff --git a/gentoobrowse/console/bugassociate.xml b/gentoobrowse/console/bugassociate.xml
new file mode 100644
index 0000000..b026205
--- /dev/null
+++ b/gentoobrowse/console/bugassociate.xml
@@ -0,0 +1,18 @@
+<?xml version="1.0"?>
+<bugassoc name="bugassociate" xmlns:xi="http://www.w3.org/2001/XInclude"
+		xmlns:project2="http://project2.randomdan.homeip.net">
+	<xi:include href="../datasources/postgres.xml" />
+	<project2:sqlmerge name="bugbind" datasource="postgres" targettable="package_bugs">
+		<sql>
+			SELECT b.bugid, p.packageid
+			FROM bugs b, packages p, categories c
+			WHERE p.categoryid = c.categoryid
+			AND TO_TSVECTOR('english', b.summary) @@ PLAINTO_TSQUERY('english', c.name || '/' || p.name)
+		</sql>
+		<columns>
+			<column key="true">bugid</column>
+			<column key="true">packageid</column>
+		</columns>
+	</project2:sqlmerge>
+</bugassoc>
+
diff --git a/gentoobrowse/console/bugimport.xml b/gentoobrowse/console/bugimport.xml
index 90b73ee..4e32e56 100644
--- a/gentoobrowse/console/bugimport.xml
+++ b/gentoobrowse/console/bugimport.xml
@@ -18,16 +18,5 @@
 			<column>summary</column>
 		</columns>
 	</project2:sqlmerge>
-	<project2:sqlmerge name="bugbind" datasource="postgres" targettable="package_bugs">
-		<sql>
-			SELECT b.bugid, p.packageid
-			FROM bugs b, packages p, categories c
-			WHERE p.categoryid = c.categoryid
-			AND TO_TSVECTOR('english', b.summary) @@ PLAINTO_TSQUERY('english', c.name || '/' || p.name)
-		</sql>
-		<columns>
-			<column key="true">bugid</column>
-			<column key="true">packageid</column>
-		</columns>
-	</project2:sqlmerge>
+	<xi:include href="bugassociate.xml" />
 </bugimport>
diff --git a/gentoobrowse/console/packagefileimport.xml b/gentoobrowse/console/packagefileimport.xml
new file mode 100644
index 0000000..270ef8b
--- /dev/null
+++ b/gentoobrowse/console/packagefileimport.xml
@@ -0,0 +1,37 @@
+<?xml version="1.0"?>
+<packageimport name="bugassociate" xmlns:xi="http://www.w3.org/2001/XInclude"
+		xmlns:project2="http://project2.randomdan.homeip.net">
+	<xi:include href="../datasources/postgres.xml" />
+
+	<project2:sqlmerge name="files" datasource="postgres" targettable="files">
+		<project2:prociterate name="findCacheFiles" newline="&#10;" encoding="ascii" fieldSep="|" quoteChar=""
+				path="find /usr/portage/metadata/cache/ -type f -printf &quot;%p|%t\n&quot; | sed &apos;s#/usr/portage/metadata/cache/##&apos;" >
+			<columns>
+				<column>filename</column>
+				<column>moddate</column>
+			</columns>
+		</project2:prociterate>
+		<columns>
+			<column key="true">filename</column>
+			<column>moddate</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="loadCacheContent" datasource="postgres" targettable="filecache">
+		<project2:prociterate name="readFileContent" newline="&#10;" encoding="ascii" fieldSep="|" quoteChar=""
+				path="grep -n . /usr/portage/metadata/cache/app-misc/w* | sed -e &apos;s#/usr/portage/metadata/cache/##&apos; -e &apos;s#:#|#&apos; -e &apos;s#:#|#&apos;" >
+			<columns>
+				<column>fileid</column>
+				<column>line</column>
+				<column>content</column>
+			</columns>
+		</project2:prociterate>
+		<columns>
+			<column key="true" maptable="files" mapcolumn="filename">fileid</column>
+			<column key="true">line</column>
+			<column>content</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<xi:include href="packageimport.xml" />
+</packageimport>
diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml
new file mode 100644
index 0000000..6f8d39d
--- /dev/null
+++ b/gentoobrowse/console/packageimport.xml
@@ -0,0 +1,121 @@
+<?xml version="1.0"?>
+<packageimport name="bugassociate" xmlns:xi="http://www.w3.org/2001/XInclude"
+		xmlns:project2="http://project2.randomdan.homeip.net">
+	<xi:include href="../datasources/postgres.xml" />
+
+	<project2:sqlmerge name="categories" datasource="postgres" targettable="categories">
+		<sql>SELECT DISTINCT (REGEXP_MATCHES(filename, '^[^/]+'))[1] AS name FROM files</sql>
+		<columns>
+			<column key="true">name</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="packages" datasource="postgres" targettable="packages">
+		<sql>
+			SELECT c.categoryid, f.name, fc.content AS description FROM (
+				SELECT MAX(fileid) AS fileid,
+					(REGEXP_MATCHES(filename, '^[^/]+'))[1] AS cat,
+					(REGEXP_MATCHES(filename, '/(.+)-[0-9]'))[1] AS name
+				FROM files f
+				GROUP BY cat, name) f LEFT OUTER JOIN filecache fc
+					ON fc.fileid = f.fileid AND fc.line = 8,
+					categories c
+				WHERE c.name = f.cat
+		</sql>
+		<columns>
+			<column key="true">categoryid</column>
+			<column key="true">name</column>
+			<column>description</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="ebuilds" datasource="postgres" targettable="ebuilds">
+		<sql>
+			SELECT packageid, version, fileid AS ebuildid, ebuildversion_constructor(version) AS versioninst
+			FROM ( SELECT f.fileid,
+				(REGEXP_MATCHES(filename, '^[^/]+'))[1] AS cat,
+				(REGEXP_MATCHES(filename, '/(.+)-[0-9]'))[1] AS name,
+				(REGEXP_MATCHES(filename, '/.+-([0-9].*)$'))[1] AS version
+				FROM files f) f, categories c, packages p
+			WHERE f.cat = c.name
+			AND c.categoryid = p.categoryid
+			AND p.name = f.name
+		</sql>
+		<columns>
+			<column key="true">ebuildid</column>
+			<column>packageid</column>
+			<column>version</column>
+			<column>versioninst</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="archs" datasource="postgres" targettable="ebuild_archs">
+		<sql>
+			SELECT DISTINCT f.fileid AS ebuildid, REGEXP_SPLIT_TO_TABLE(f.content, ' ') AS arch
+			FROM filecache f
+			WHERE f.line = 9
+		</sql>
+		<columns>
+			<column key="true">ebuildid</column>
+			<column key="true">arch</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="uses" datasource="postgres" targettable="ebuild_uses">
+		<sql>
+			SELECT DISTINCT f.fileid AS ebuildid, REGEXP_SPLIT_TO_TABLE(f.content, ' ') AS use
+			FROM filecache f
+			WHERE f.line = 11
+		</sql>
+		<columns>
+			<column key="true">ebuildid</column>
+			<column key="true">use</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="urls" datasource="postgres" targettable="package_urls">
+		<sql>
+			SELECT DISTINCT e.packageid, REGEXP_SPLIT_TO_TABLE(f.content, ' ') AS url
+			FROM filecache f, ebuilds e
+			WHERE f.line = 6
+			AND f.fileid = e.ebuildid
+		</sql>
+		<columns>
+			<column key="true">packageid</column>
+			<column key="true">url</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<project2:sqlmerge name="deps" datasource="postgres" targettable="ebuild_deps">
+		<sql>
+			SELECT fileid AS ebuildid, packageid, COALESCE(slot, '') AS slot, COALESCE(op, '') AS op,
+				COALESCE(versionspec, '') AS versionspec, COALESCE(flags, '') AS flags
+			FROM (
+				SELECT DISTINCT fileid, field[2] AS cat, field[3] AS pkg, field[7] AS slot,
+					field[1] AS op, field[5] AS versionspec, field[9] AS flags
+				FROM (
+					SELECT fileid, REGEXP_MATCHES(dep,
+						'([^a-z0-9]*)([a-z0-9-]+)/([a-z0-9-]+?)(-([0-9][^:]*))?(:([0-9.]+))?(\\[([^]]+)\\])?') as field
+					FROM (
+						SELECT f.fileid, REGEXP_SPLIT_TO_TABLE(f.content, ' ') AS dep
+						FROM filecache f
+						WHERE line = 1
+					) src
+				) AS src2
+			) AS src3, categories c, packages p
+			WHERE p.categoryid = c.categoryid
+			AND p.name = pkg
+			AND c.name = cat
+		</sql>
+		<columns>
+			<column key="true">ebuildid</column>
+			<column key="true">packageid</column>
+			<column key="true">slot</column>
+			<column key="true">op</column>
+			<column key="true">versionspec</column>
+			<column key="true">flags</column>
+		</columns>
+	</project2:sqlmerge>
+
+	<xi:include href="bugassociate.xml" />
+</packageimport>
diff --git a/gentoobrowse/views/package.xml b/gentoobrowse/views/package.xml
index 6ec12ec..ba5041b 100644
--- a/gentoobrowse/views/package.xml
+++ b/gentoobrowse/views/package.xml
@@ -72,6 +72,18 @@
 				<param bind="0" value="^packageid" />
 			</parameters>
 		</project2:sqlview>
+		<project2:sqlview name="packageBugs" recordname="bug" datasource="postgres">
+			<sql>
+				SELECT b.bugid, b.summary
+				FROM bugs b, package_bugs pb
+				WHERE b.bugid = pb.bugid
+				AND pb.packageid = ?
+				ORDER BY b.bugid
+			</sql>
+			<parameters>
+				<param bind="0" value="^packageid" />
+			</parameters>
+		</project2:sqlview>
 		<project2:sqlview name="deps" recordname="package" datasource="postgres">
 			<sql>
 				SELECT distinct cd.name AS catname, pd.name AS pkgname, pd.description