From 5df27c8c3fc06f29b25dac7fcfa9d47f197ab30a Mon Sep 17 00:00:00 2001
From: randomdan <randomdan@localhost>
Date: Tue, 9 Nov 2010 18:46:40 +0000
Subject: Use package description from ebuild with highest version number, not
 file ID

---
 gentoobrowse/console/packageimport.xml | 18 ++++++++++--------
 1 file changed, 10 insertions(+), 8 deletions(-)

diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml
index 81502ce..ac831ab 100644
--- a/gentoobrowse/console/packageimport.xml
+++ b/gentoobrowse/console/packageimport.xml
@@ -12,16 +12,18 @@
 
 	<project2:sqlmerge name="packages" datasource="postgres" targettable="packages" useview="yes">
 		<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
+			SELECT categoryid, name, fc.content AS description FROM (
+				SELECT c.categoryid, f.fileid, f.d[2] AS name,
+					RANK() OVER(PARTITION BY c.categoryid, f.d[2] ORDER BY ebuildversion_constructor(f.d[3]) DESC) AS r FROM (
+				SELECT fileid,
+					REGEXP_MATCHES(filename, '^([^/]+)/(.+)-([0-9].*)$') as d
 				FROM files f
-				WHERE f.filetypeid = 1
-				GROUP BY cat, name) f LEFT OUTER JOIN filecache fc
-					ON fc.fileid = f.fileid AND fc.line = 8,
+				WHERE f.filetypeid = 1) f,
 					categories c
-				WHERE c.name = f.cat
+				WHERE c.name = f.d[1]) f, filecache fc
+				WHERE f.r = 1
+				AND fc.fileid = f.fileid
+				AND fc.line = 8
 		</sql>
 		<columns>
 			<column key="true">categoryid</column>
-- 
cgit v1.2.3