From 1c64cce1859ae39a3360f21970bb86a8dedf1fda Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Fri, 7 Aug 2015 02:39:05 +0100
Subject: Replace categories and ebuilddefs

---
 gentoobrowse/console/packageimport.xml | 39 +++++++++++++++++-----------------
 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;
-- 
cgit v1.2.3