diff options
| -rw-r--r-- | gentoobrowse/console/packageimport.xml | 30 | ||||
| -rw-r--r-- | gentoobrowse/console/packagemetadata.xml | 49 | ||||
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 4 |
3 files changed, 28 insertions, 55 deletions
diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml index c0061b3..7a98ef9 100644 --- a/gentoobrowse/console/packageimport.xml +++ b/gentoobrowse/console/packageimport.xml @@ -31,18 +31,41 @@ </sql> </project2:sqltask> - <project2:sqlmerge name="packages" datasource="postgres" targettable="packages" useview="yes"> + <project2:sqlmerge name="packages" datasource="postgres" targettable="packages" useview="no"> <sql> - SELECT categoryid, pkgname AS name, description + SELECT d.categoryid, d.pkgname AS name, description, maintainer, maintainername, herd, summary, image FROM ( - SELECT categoryid, pkgname, description, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY versioninst desc) r + SELECT categoryid, pkgname, description, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY version DESC) r FROM ebuilddefs) d + LEFT OUTER JOIN ( + SELECT categoryid, pkgname, maintainer, maintainername, herd, r, + TRIM(REGEXP_REPLACE(REGEXP_REPLACE(COALESCE(longdescen, longdesc), '<<data:image/[^>]+>>', ''), '\s+', ' ', 'g')) AS summary, + (SELECT REGEXP_MATCHES(COALESCE(longdescen, longdesc), '<<(data:image/[^>]+)>>')) AS image + FROM ( + SELECT categoryid, pkgname, RANK() OVER(PARTITION BY categoryid, pkgname ORDER BY repoid DESC) r, + CAST((XPATH('/pkgmetadata/maintainer/email/text()', doc))[1] AS TEXT) maintainer, + CAST((XPATH('/pkgmetadata/maintainer/name/text()', doc))[1] AS TEXT) maintainername, + CAST((XPATH('/pkgmetadata/longdescription[@lang="en"]/text()', doc))[1] AS TEXT) longdescen, + CAST((XPATH('/pkgmetadata/longdescription[not(@lang)]/text()', doc))[1] AS TEXT) longdesc, + CAST((XPATH('/pkgmetadata/herd/text()', doc))[1] AS TEXT) herd + FROM ( + SELECT f.repoid, categoryid, f.pathparts[2] pkgname, XMLPARSE(DOCUMENT PG_READ_FILE(f.filename)) doc + FROM files f, categories c + WHERE filetypeid = 4 + AND f.pathparts[1] = c.name) x) x + ) m ON m.categoryid = d.categoryid AND m.pkgname = d.pkgname WHERE d.r = 1 + AND m.r = 1 </sql> <columns> <categoryid key="true" /> <name key="true" /> <description /> + <maintainer /> + <maintainername /> + <herd /> + <summary /> + <image /> </columns> </project2:sqlmerge> @@ -168,7 +191,6 @@ <xi:include href="use.local.xml" /> <xi:include href="use.grouped.xml" /> <xi:include href="categorymetadata.xml" /> - <xi:include href="packagemetadata.xml" /> <xi:include href="packagemasks.xml" /> <xi:include href="bugassociate.xml" /> </packageimport> diff --git a/gentoobrowse/console/packagemetadata.xml b/gentoobrowse/console/packagemetadata.xml deleted file mode 100644 index b66abfe..0000000 --- a/gentoobrowse/console/packagemetadata.xml +++ /dev/null @@ -1,49 +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="pkgblanksummaries" datasource="postgres"> - <sql> - SELECT p.packageid, 'file://' || ft.searchroot || f.filename AS path, f.moddate - FROM packages p, categories c, files f, filetypes ft - WHERE (f.cachedat IS NULL OR f.cachedat != f.moddate) - AND p.categoryid = c.categoryid - AND f.filename = c.name || '/' || p.name || '/metadata.xml' - AND f.filetypeid = ft.filetypeid - AND ft.filetypeid = 4 - </sql> - </project2:sqlrows> - <project2:xpathrows name="pkg" html="false" warnings="true"> - <url source="parent" name="path" depth="1" /> - <filterviews> - <pkgmetadata root="/pkgmetadata"> - <fields> - <longdesc xpath="longdescription" /> - <maintainer xpath="maintainer/email" /> - <herd xpath="herd" /> - </fields> - </pkgmetadata> - </filterviews> - </project2:xpathrows> - <project2:iterate name="eachpkg" source="pkgblanksummaries"> - <project2:iterate name="pkgdetail" source="pkg" filter="pkgmetadata"> - <project2:sqltask name="update" datasource="postgres"> - <sql> - UPDATE packages SET - summary = regexp_replace(<param source="parent" name="longdesc" />, '<<data:image/[^>]+>>', ''), - image = (SELECT regexp_matches(<param source="parent" name="longdesc" />, '<<(data:image/[^>]+)>>'))[1], - maintainer = <param source="parent" name="maintainer" />, - herd = <param source="parent" name="herd" />, - metadatadate = <param source="parent" name="moddate" depth="2" /> - WHERE packageid = <param source="parent" name="packageid" depth="2" /> - </sql> - </project2:sqltask> - </project2:iterate> - </project2:iterate> - <project2:sqltask name="update" datasource="postgres"> - <sql> - UPDATE files SET - cachedat = moddate - WHERE filetypeid = 4 - </sql> - </project2:sqltask> -</homepages> diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql index be36a20..ffe4111 100644 --- a/gentoobrowse/datasources/schema.sql +++ b/gentoobrowse/datasources/schema.sql @@ -201,8 +201,8 @@ CREATE TABLE packages ( summary text, maintainer text, herd text, - metadatadate timestamp without time zone, - image text + image text, + maintainername text ); ALTER TABLE packages OWNER TO gentoo; -- Name: TABLE packages; Type: COMMENT; Schema: gentoobrowse; Owner: gentoo |