Remove most uses of files table from website, data now in ebuilds table

8 files changed, 31 insertions, 37 deletions

diff --git a/gentoobrowse/atomebuilds.xslt b/gentoobrowse/atomebuilds.xslt
index acd0ce3..bd18e12 100644
--- a/gentoobrowse/atomebuilds.xslt
+++ b/gentoobrowse/atomebuilds.xslt
@@ -21,10 +21,10 @@
   </xsl:template>
   <xsl:template match="/gentoo/newebuilds/ebuild">
     <item>
-      <title><xsl:value-of select="filename" /></title>
+      <title><xsl:value-of select="catname" />/<xsl:value-of select="pkgname" />-<xsl:value-of select="version" /></title>
       <description><xsl:value-of select="description" /></description>
       <link>http://<xsl:value-of select="/gentoo/project2:servername" />/package/<xsl:value-of select="catname" />/<xsl:value-of select="pkgname" /></link>
-      <guid isPermaLink="false">http://<xsl:value-of select="/gentoo/project2:servername" />/package/<xsl:value-of select="filename" /></guid>
+      <guid isPermaLink="false">http://<xsl:value-of select="/gentoo/project2:servername" />/package/<xsl:value-of select="catname" />/<xsl:value-of select="pkgname" />-<xsl:value-of select="version" /></guid>
       <pubDate><xsl:value-of select="translate(moddate,' ','T')" />Z</pubDate>
       <category>ebuild</category>
     </item>
diff --git a/gentoobrowse/console/emailebuilds.xml b/gentoobrowse/console/emailebuilds.xml
index 14f57f6..7df144f 100644
--- a/gentoobrowse/console/emailebuilds.xml
+++ b/gentoobrowse/console/emailebuilds.xml
@@ -6,11 +6,10 @@
 		<sql>
 			select ut.userid, ut.useremail from
 				(select e.ebuildid, u.userid, u.useremail
-				from files f, ebuilds e, user_packages up, users u
+				from ebuilds e, user_packages up, users u
 				where e.packageid = up.packageid
-				and f.fileid = e.ebuildid
 				and u.userid = up.userid
-				and f.firstseen > up.trackedsince) as ut left outer join user_ebuild_emails ebe
+				and e.firstseen > up.trackedsince) as ut left outer join user_ebuild_emails ebe
 					on ebe.userid = ut.userid and ebe.ebuildid = ut.ebuildid
 			where ebe.userid is null
 			group by ut.userid, ut.useremail
@@ -23,12 +22,11 @@
 				select ut.ebuildid, ut.userid, now()
 				from (
 					select e.ebuildid, up.userid
-					from files f, ebuilds e, user_packages up, users u
+					from ebuilds e, user_packages up, users u
 					where e.packageid = up.packageid
-					and f.fileid = e.ebuildid
 					and u.userid = up.userid
 					and u.userid = ?
-					and f.firstseen > up.trackedsince) as ut left outer join user_ebuild_emails ebe
+					and e.firstseen > up.trackedsince) as ut left outer join user_ebuild_emails ebe
 						on ebe.userid = ut.userid and ebe.ebuildid = ut.ebuildid
 				where ebe.userid is null
 			</sql>
diff --git a/gentoobrowse/console/packageimport.xml b/gentoobrowse/console/packageimport.xml
index ac831ab..95743a4 100644
--- a/gentoobrowse/console/packageimport.xml
+++ b/gentoobrowse/console/packageimport.xml
@@ -34,8 +34,9 @@
 
 	<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
-			FROM ( SELECT f.fileid,
+			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
@@ -54,6 +55,8 @@
 			<column>versioninst</column>
 			<column>slot</column>
 			<column>license</column>
+			<column>firstseen</column>
+			<column>moddate</column>
 		</columns>
 	</project2:sqlmerge>
 
diff --git a/gentoobrowse/emails/newpackageversions.xml b/gentoobrowse/emails/newpackageversions.xml
index 83bebbc..f65a2de 100644
--- a/gentoobrowse/emails/newpackageversions.xml
+++ b/gentoobrowse/emails/newpackageversions.xml
@@ -7,14 +7,13 @@
 			select ut.cname, ut.pname, ut.version
 			from (
 				select e.ebuildid, up.userid, u.useremail, p.name as pname, c.name as cname, e.version
-				from files f, ebuilds e, user_packages up, users u, packages p, categories c
+				from ebuilds e, user_packages up, users u, packages p, categories c
 				where e.packageid = up.packageid
-				and f.fileid = e.ebuildid
 				and u.userid = up.userid
 				and u.userid = ?
 				and e.packageid = p.packageid
 				and c.categoryid = p.categoryid
-				and f.firstseen > up.trackedsince) as ut left outer join user_ebuild_emails ebe
+				and e.firstseen > up.trackedsince) as ut left outer join user_ebuild_emails ebe
 					on ebe.userid = ut.userid and ebe.ebuildid = ut.ebuildid
 			where ebe.userid is null
 			order by ut.cname, ut.pname, ut.version
diff --git a/gentoobrowse/home.xslt b/gentoobrowse/home.xslt
index 23ad66e..ea25942 100644
--- a/gentoobrowse/home.xslt
+++ b/gentoobrowse/home.xslt
@@ -41,7 +41,7 @@
 			<xsl:attribute name="href">
 				/package/<xsl:value-of select="catname" />/<xsl:value-of select="pkgname" />
 			</xsl:attribute>
-			<xsl:value-of select="filename" />
+			<xsl:value-of select="catname" />/<xsl:value-of select="pkgname" />-<xsl:value-of select="version" />
 		</a>
 	</li>
   </xsl:template>
diff --git a/gentoobrowse/views/newebuilds.xml b/gentoobrowse/views/newebuilds.xml
index 92d5fbd..9a4cbf3 100644
--- a/gentoobrowse/views/newebuilds.xml
+++ b/gentoobrowse/views/newebuilds.xml
@@ -3,19 +3,17 @@
 	<xi:include href="../datasources/postgres-web.xml" />
 	<project2:sqlview name="newebuilds" recordname="ebuild" datasource="postgres-web">
 		<sql>
-			SELECT filename, description, moddate, p.name AS pkgname, c.name AS catname, f.firstseen sync,
+			SELECT description, moddate, p.name AS pkgname, c.name AS catname, e.version, e.firstseen sync,
 				up.trackedsince IS NOT NULL AS tracked
-			FROM ebuilds e, files f, categories c, packages p LEFT OUTER JOIN user_packages up
+			FROM ebuilds e, categories c, packages p LEFT OUTER JOIN user_packages up
 				ON up.userid = ?
 				AND up.packageid = p.packageid
-			WHERE e.ebuildid = f.fileid
-			AND e.packageid = p.packageid
+			WHERE e.packageid = p.packageid
 			AND p.categoryid = c.categoryid
-			AND f.firstseen IN (
-					SELECT DISTINCT f.firstseen
-					FROM files f
-					WHERE f.filetypeid = 1
-					ORDER BY f.firstseen
+			AND e.firstseen IN (
+					SELECT DISTINCT e.firstseen
+					FROM ebuilds e
+					ORDER BY e.firstseen
 					DESC LIMIT 3)
 			ORDER BY tracked DESC, c.name, p.name, e.versioninst
 		</sql>
@@ -25,10 +23,9 @@
 	</project2:sqlview>
 	<project2:sqlview name="syncs" recordname="sync" datasource="postgres-web">
 		<sql>
-			SELECT DISTINCT f.firstseen AS synctime
-			FROM files f
-			WHERE filetypeid = 1
-			ORDER BY f.firstseen
+			SELECT DISTINCT e.firstseen AS synctime
+			FROM ebuilds e
+			ORDER BY e.firstseen
 			DESC LIMIT 3
 		</sql>
 	</project2:sqlview>
diff --git a/gentoobrowse/views/newebuildsatom.xml b/gentoobrowse/views/newebuildsatom.xml
index e758330..76cc414 100644
--- a/gentoobrowse/views/newebuildsatom.xml
+++ b/gentoobrowse/views/newebuildsatom.xml
@@ -3,17 +3,15 @@
 	<xi:include href="../datasources/postgres-web.xml" />
 	<project2:sqlview name="newebuilds" recordname="ebuild" datasource="postgres-web">
 		<sql>
-			SELECT filename, description, moddate, p.name AS pkgname, c.name AS catname, f.firstseen sync
-			FROM ebuilds e, files f, categories c, packages p, user_packages up, users u
-			WHERE e.ebuildid = f.fileid
-			AND e.packageid = p.packageid
+			SELECT version, description, moddate, p.name AS pkgname, c.name AS catname, e.firstseen sync
+			FROM ebuilds e, categories c, packages p, user_packages up, users u
+			WHERE e.packageid = p.packageid
 			AND p.categoryid = c.categoryid
 			AND up.userid = u.userid
 			AND u.username = ?
 			AND up.packageid = p.packageid
-			AND f.firstseen >= up.trackedsince
-			AND f.filetypeid = 1
-			ORDER BY f.firstseen DESC, c.name, p.name, e.versioninst
+			AND e.firstseen >= up.trackedsince
+			ORDER BY e.firstseen DESC, c.name, p.name, e.versioninst
 			LIMIT 50
 		</sql>
 		<parameters>
diff --git a/gentoobrowse/views/package.xml b/gentoobrowse/views/package.xml
index 5b0daab..d526df4 100644
--- a/gentoobrowse/views/package.xml
+++ b/gentoobrowse/views/package.xml
@@ -28,10 +28,9 @@
 		</project2:sqlview>
 		<project2:sqlview name="versions" recordname="version" datasource="postgres-web">
 			<sql>
-				SELECT e.ebuildid, e.version, f.moddate, e.slot, e.license
-				FROM ebuilds e, files f
+				SELECT e.ebuildid, e.version, e.moddate, e.slot, e.license
+				FROM ebuilds e
 				WHERE e.packageid = ?
-				AND e.ebuildid = f.fileid
 				ORDER BY versioninst desc
 			</sql>
 			<parameters>