diff options
| -rw-r--r-- | gentoobrowse/console/packageimport.xml | 39 | ||||
| -rw-r--r-- | gentoobrowse/datasources/schema.sql | 3 |
2 files changed, 22 insertions, 20 deletions
diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml index f586b31..c0061b3 100644 --- a/gentoobrowse/console/packageimport.xml +++ b/gentoobrowse/console/packageimport.xml @@ -2,7 +2,7 @@ <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 SPLIT_PART(filename, '/', 1) AS name FROM files WHERE filetypeid = 1</sql> + <sql>SELECT DISTINCT pathparts[3] AS name FROM files WHERE filetypeid = 1</sql> <columns> <name key="true" /> </columns> @@ -11,25 +11,23 @@ <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 + SELECT p.*, d[1] pkgname, d[2] AS version, ebuildversion_constructor(d[2]) 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 + SELECT c.name catname, c.categoryid, f.repoid, f.firstseen, f.moddate, REGEXP_MATCHES(pathparts[4], '^(.+)-([0-9].*)$') AS d, p.* + FROM CROSSTAB($$ + SELECT fileid, a, SUBSTRING(md FROM '=(.*)') v + FROM ( + SELECT f.fileid, f.filename, a + FROM files f, unnest(array['DEFINED_PHASES', 'DEPEND', 'DESCRIPTION', 'EAPI', 'HOMEPAGE', 'IUSE', 'KEYWORDS', + 'LICENSE', 'PDEPEND', 'PROPERTIES', 'RDEPEND', 'REQUIRED_USE', 'RESTRICT', 'SLOT', 'SRC_URI']) a + WHERE filetypeid = 1 + ) f LEFT OUTER JOIN REGEXP_SPLIT_TO_TABLE(PG_READ_FILE(f.filename), '\n') md + ON SPLIT_PART(md, '=' ,1) = a + ORDER BY FILEID, a $$) AS p(fileid INT, defined_phases TEXT, depend TEXT, description TEXT, eapi text, homepage TEXT, + iuse TEXT, keywords TEXT, license TEXT, pdepend TEXT, properties TEXT, rdepend TEXT, required_use TEXT, + restrict TEXT, slot TEXT, src_uri TEXT), files f, categories c + WHERE f.fileid = p.fileid + AND f.pathparts[3] = c.name) p </sql> </project2:sqltask> @@ -50,7 +48,7 @@ <project2:sqlmerge name="ebuilds" datasource="postgres" targettable="ebuilds" useview="yes"> <sql> - SELECT p.packageid, e.version, e.fileid AS ebuildid, e.versioninst, e.slot, e.license, e.firstseen, e.moddate + SELECT p.packageid, e.version, e.fileid AS ebuildid, e.versioninst, e.slot, e.license, e.firstseen, e.moddate, e.repoid FROM ebuilddefs e, packages p WHERE e.pkgname = p.name AND e.categoryid = p.categoryid @@ -64,6 +62,7 @@ <license /> <firstseen /> <moddate /> + <repoid /> </columns> </project2:sqlmerge> diff --git a/gentoobrowse/datasources/schema.sql b/gentoobrowse/datasources/schema.sql index a93a2c2..be36a20 100644 --- a/gentoobrowse/datasources/schema.sql +++ b/gentoobrowse/datasources/schema.sql @@ -312,6 +312,7 @@ COMMENT ON TABLE ebuild_uses IS 'Use flags affecting ebuilds'; CREATE TABLE ebuilds ( ebuildid integer NOT NULL, packageid integer NOT NULL, + repoid integer, version text NOT NULL, versioninst ebuildversion NOT NULL, slot text NOT NULL, @@ -684,6 +685,8 @@ CREATE INDEX idx_userpackages_package ON user_packages USING btree (packageid); -- Name: fk_ebuild_pkg; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuilds ADD CONSTRAINT fk_ebuild_pkg FOREIGN KEY (packageid) REFERENCES packages(packageid) ON UPDATE CASCADE ON DELETE CASCADE; +ALTER TABLE ONLY ebuilds + ADD CONSTRAINT fk_ebuild_repo FOREIGN KEY (repoid) REFERENCES repos(repoid) ON UPDATE CASCADE ON DELETE CASCADE; -- Name: fk_ebuildarch_file; Type: FK CONSTRAINT; Schema: gentoobrowse; Owner: gentoo ALTER TABLE ONLY ebuild_archs ADD CONSTRAINT fk_ebuildarch_file FOREIGN KEY (ebuildid) REFERENCES ebuilds(ebuildid) ON UPDATE CASCADE ON DELETE CASCADE; |