From e075b6e48cd7c23d8940419fe7be0878512116fe Mon Sep 17 00:00:00 2001
From: Dan Goodliffe <dan@randomdan.homeip.net>
Date: Sun, 7 Aug 2016 19:28:08 +0100
Subject: Use tables in GentooBrowse API DB

---
 gentoobrowse/views/package.xml | 18 +++++++++++-------
 1 file changed, 11 insertions(+), 7 deletions(-)

diff --git a/gentoobrowse/views/package.xml b/gentoobrowse/views/package.xml
index f4f6048..e581430 100644
--- a/gentoobrowse/views/package.xml
+++ b/gentoobrowse/views/package.xml
@@ -79,10 +79,11 @@
 	<project2:sqlrows name="packageBugs" datasource="postgres-web">
 		<sql>
 			SELECT b.bugid, b.summary
-			FROM bugs b, package_bugs pb
-			WHERE b.bugid = pb.bugid
+			FROM bugs b, categories c, packages p
+			WHERE p.categoryid = c.categoryid
+			AND gentoobrowse.bugpackages(b) @> array[c.name || '/' || p.name]
 			<filter name="pkg">
-				AND pb.packageid = <param name="pkgid" />
+				AND p.packageid = <param name="pkgid" />
 			</filter>
 			ORDER BY b.bugid
 		</sql>
@@ -158,13 +159,16 @@
 	</project2:sqlrows>
 	<project2:sqlrows name="changeLog" datasource="postgres-web">
 		<sql>
-			SELECT date, person, email, comment, r.name AS reponame
-			FROM package_changelogs c, repos r
-			WHERE c.repoid = r.repoid
+			SELECT committime AS date, authorname person, authoremail email, cl.summary AS comment, r.name AS reponame
+			FROM changelog cl, categories c, packages p, repos r
+			WHERE p.categoryid = c.categoryid
+			AND cl.repoid = r.repoid
+			AND gentoobrowse.changelogpackages(cl) @> array[c.name || '/' || p.name]
 			<filter name="pkg">
 				AND packageid = <param name="pkgid" />
 			</filter>
-			ORDER BY date DESC, n DESC, c.repoid
+			AND cl.authoremail != 'repo-qa-checks@gentoo.org'
+			ORDER BY cl.committime DESC, cl.repoid
 		</sql>
 	</project2:sqlrows>
 </block>
-- 
cgit v1.2.3