diff options
| -rw-r--r-- | gentoobrowse/console/packageimport.xml | 102 | ||||
| -rw-r--r-- | gentoobrowse/views/search.xml | 2 | ||||
| -rw-r--r-- | gentoobrowse/views/use.xml | 4 |
3 files changed, 51 insertions, 57 deletions
diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml index c136294..4844dd0 100644 --- a/gentoobrowse/console/packageimport.xml +++ b/gentoobrowse/console/packageimport.xml @@ -2,26 +2,44 @@ <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 (REGEXP_MATCHES(filename, '^[^/]+'))[1] AS name FROM files WHERE filetypeid = 1</sql> + <sql>SELECT DISTINCT SPLIT_PART(filename, '/', 1) AS name FROM files WHERE filetypeid = 1</sql> <columns> <name key="true" /> </columns> </project2:sqlmerge> + <project2:sqltask name="tmpdefs" datasource="postgres"> + <sql> + CREATE TEMPORARY TABLE ebuilddefs AS + SELECT d.*, c.categoryid, c.name AS catname, d[2] AS pkgname, d[3] AS version, ebuildversion_constructor(d[3]) AS versioninst + FROM ( + SELECT *, REGEXP_MATCHES(filename, '^([^/]+)/(.+)-([0-9].*)$') AS d + FROM CROSSTAB( + 'SELECT f.filename AS filename, f.fileid, f.firstseen, f.moddate, SPLIT_PART(content, ''='', 1) AS category, + (REGEXP_MATCHES(content, E''([^=]+)=(.*)''))[2] AS value + FROM filecache fc, files f + WHERE fc.fileid = f.fileid + AND f.filetypeid = 1 + ORDER BY 1,2', + 'SELECT DISTINCT SPLIT_PART(content, ''='', 1) AS attr + FROM filecache fc, files f + WHERE fc.fileid = f.fileid + AND f.filetypeid = 1 + ORDER BY attr') AS ct(filename TEXT, fileid INT, firstseen TIMESTAMP WITHOUT TIME ZONE, moddate TIMESTAMP WITHOUT TIME ZONE, + defined_phases TEXT, depend TEXT, description TEXT, eapi INT, + homepage TEXT, iuse TEXT, keywords TEXT, license TEXT, pdepend TEXT, properties TEXT, rdepend TEXT, required_use TEXT, + restrict TEXT, slot TEXT, src_uri TEXT, _eclasses_ TEXT, _md5_ TEXT)) d, categories c + WHERE SPLIT_PART(d.filename, '/', 1) = c.name + </sql> + </project2:sqltask> + <project2:sqlmerge name="packages" datasource="postgres" targettable="packages" useview="yes"> <sql> - 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) f, - categories c - WHERE c.name = f.d[1]) f, filecache fc - WHERE f.r = 1 - AND fc.fileid = f.fileid - AND fc.line = 8 + SELECT categoryid, pkgname AS name, description + FROM ( + SELECT categoryid, pkgname, description, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY versioninst desc) r + FROM ebuilddefs) d + WHERE d.r = 1 </sql> <columns> <categoryid key="true" /> @@ -32,19 +50,10 @@ <project2:sqlmerge name="ebuilds" datasource="postgres" targettable="ebuilds" useview="yes"> <sql> - SELECT packageid, version, f.fileid AS ebuildid, ebuildversion_constructor(version) AS versioninst, fcs.content AS slot, - fcl.content AS license, f.firstseen, f.moddate - FROM ( SELECT f.fileid, f.firstseen, f.moddate, - (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 - WHERE filetypeid = 1) f LEFT OUTER JOIN filecache fcs - ON fcs.fileid = f.fileid AND fcs.line = 3 LEFT OUTER JOIN filecache fcl - ON fcl.fileid = f.fileid AND fcl.line = 7, categories c, packages p - WHERE f.cat = c.name - AND c.categoryid = p.categoryid - AND p.name = f.name + SELECT p.packageid, e.version, e.fileid AS ebuildid, e.versioninst, e.slot, e.license, e.firstseen, e.moddate + FROM ebuilddefs e, packages p + WHERE e.pkgname = p.name + AND e.categoryid = p.categoryid </sql> <columns> <ebuildid key="true" /> @@ -60,11 +69,8 @@ <project2:sqlmerge name="archs" datasource="postgres" targettable="ebuild_archs" useview="yes"> <sql> - SELECT DISTINCT f.fileid AS ebuildid, REGEXP_SPLIT_TO_TABLE(fc.content, ' ') AS arch - FROM filecache fc, files f - WHERE fc.line = 9 - AND fc.fileid = f.fileid - AND filetypeid = 1 + SELECT DISTINCT e.fileid AS ebuildid, REGEXP_SPLIT_TO_TABLE(e.keywords, ' ') AS arch + FROM ebuilddefs e </sql> <columns> <ebuildid key="true" /> @@ -74,11 +80,8 @@ <project2:sqlmerge name="uses" datasource="postgres" targettable="ebuild_uses" useview="yes"> <sql> - SELECT DISTINCT fc.fileid AS ebuildid, REGEXP_SPLIT_TO_TABLE(fc.content, ' ') AS use - FROM filecache fc, files f - WHERE fc.line = 11 - AND fc.fileid = f.fileid - AND filetypeid = 1 + SELECT DISTINCT e.fileid AS ebuildid, REGEXP_SPLIT_TO_TABLE(e.iuse, ' ') AS use + FROM ebuilddefs e </sql> <columns> <ebuildid key="true" /> @@ -88,14 +91,11 @@ <project2:sqlmerge name="urls" datasource="postgres" targettable="package_urls" useview="yes"> <sql> - SELECT packageid, url FROM ( - SELECT DISTINCT e.packageid, REGEXP_SPLIT_TO_TABLE(fc.content, ' ') AS url - FROM filecache fc, ebuilds e, files f - WHERE fc.line = 6 - AND fc.fileid = e.ebuildid - AND fc.fileid = f.fileid - AND filetypeid = 1) AS u - WHERE url LIKE '%://%' + SELECT DISTINCT p.packageid, e.homepage AS url + FROM ebuilddefs e, packages p + WHERE e.pkgname = p.name + AND e.categoryid = p.categoryid + AND homepage LIKE '%://%' </sql> <columns> <packageid key="true" /> @@ -114,11 +114,8 @@ SELECT fileid, REGEXP_MATCHES(dep, E'([^a-zA-Z0-9]*)([a-zA-Z0-9-]+)/([a-zA-Z0-9_+-]+?)(-([0-9][^:]*))?(:([0-9.]+))?(\\[([^]]+)\\])?') as field FROM ( - SELECT fc.fileid, REGEXP_SPLIT_TO_TABLE(fc.content, ' ') AS dep - FROM filecache fc, files f - WHERE line = 1 - AND fc.fileid = f.fileid - AND filetypeid = 1 + SELECT e.fileid, REGEXP_SPLIT_TO_TABLE(e.depend, ' ') AS dep + FROM ebuilddefs e ) src ) AS src2 ) AS src3, categories c, packages p @@ -147,11 +144,8 @@ SELECT fileid, REGEXP_MATCHES(dep, E'([^a-zA-Z0-9]*)([a-zA-Z0-9-]+)/([a-zA-Z0-9_+-]+?)(-([0-9][^:]*))?(:([0-9.]+))?(\\[([^]]+)\\])?') as field FROM ( - SELECT fc.fileid, REGEXP_SPLIT_TO_TABLE(fc.content, ' ') AS dep - FROM filecache fc, files f - WHERE line = 2 - AND fc.fileid = f.fileid - AND filetypeid = 1 + SELECT e.fileid, REGEXP_SPLIT_TO_TABLE(e.rdepend, ' ') AS dep + FROM ebuilddefs e ) src ) AS src2 ) AS src3, categories c, packages p diff --git a/gentoobrowse/views/search.xml b/gentoobrowse/views/search.xml index db08d33..347642a 100644 --- a/gentoobrowse/views/search.xml +++ b/gentoobrowse/views/search.xml @@ -38,7 +38,7 @@ AND e.packageid = p.packageid AND p.categoryid = c.categoryid <filter name="byUse"> - AND eu.use = <param name="use" /> + AND LTRIM(eu.use, '+') = <param name="use" /> </filter> ORDER BY c.name, p.name </sql> diff --git a/gentoobrowse/views/use.xml b/gentoobrowse/views/use.xml index f0938bd..957cdbc 100644 --- a/gentoobrowse/views/use.xml +++ b/gentoobrowse/views/use.xml @@ -23,7 +23,7 @@ SELECT use, description FROM use_global <filter name="use"> - WHERE use = <param name="use" /> + WHERE LTRIM(use, '+') = <param name="use" /> </filter> </sql> </project2:sqlrows> @@ -32,7 +32,7 @@ SELECT packageid, use, description FROM use_local <filter name="use"> - WHERE use = <param name="use" /> + WHERE LTRIM(use, '+') = <param name="use" /> ORDER BY packageid </filter> <filter name="package"> |