From 4a4e3f20845e45725340e48ad171898fbb06b6a5 Mon Sep 17 00:00:00 2001 From: randomdan Date: Sat, 13 Nov 2010 18:20:48 +0000 Subject: Remove most uses of files table from website, data now in ebuilds table --- gentoobrowse/atomebuilds.xslt | 4 ++-- gentoobrowse/console/emailebuilds.xml | 10 ++++------ gentoobrowse/console/packageimport.xml | 7 +++++-- gentoobrowse/emails/newpackageversions.xml | 5 ++--- gentoobrowse/home.xslt | 2 +- gentoobrowse/views/newebuilds.xml | 23 ++++++++++------------- gentoobrowse/views/newebuildsatom.xml | 12 +++++------- gentoobrowse/views/package.xml | 5 ++--- 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:value-of select="filename" /> + <xsl:value-of select="catname" />/<xsl:value-of select="pkgname" />-<xsl:value-of select="version" /> http:///package// - http:///package/ + http:///package//- Z ebuild 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 @@ 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 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 @@ - 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 @@ versioninst slot license + firstseen + moddate 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 @@ /package// - + /- 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 @@ - 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 @@ -25,10 +23,9 @@ - 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 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 @@ - 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 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 @@ - 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 -- cgit v1.2.3