diff options
| -rw-r--r-- | gentoobrowse/console/packageimport.xml | 18 |
1 files 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> |