From b7941073acec76e5b4f89648aca1413c15eb067f Mon Sep 17 00:00:00 2001
From: Lukas Fleischer <archlinux@cryptocrack.de>
Date: Fri, 4 Apr 2014 00:01:44 +0200
Subject: Add provisional support for package bases

This adds a PackageBases table to the database schema and moves the
following fields from the Packages table to PackageBases:

* CategoryID
* NumVotes
* OutOfDateTS
* SubmittedTS
* ModifiedTS
* SubmitterUID
* MaintainerUID

It also fixes all database accesses to comply with the new layout.

Having a separate PackageBases table is the first step to split package
support. By now, we create one PackageBases entry per package (where the
package base has the same name as the corresponding package). When
adding full support for split packages later, the package base name will
be derived from the pkgbase variable and a single package base will be
shared amongst all packages built from one source package.

Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
---
 web/lib/stats.inc.php | 21 ++++++++++++---------
 1 file changed, 12 insertions(+), 9 deletions(-)

(limited to 'web/lib/stats.inc.php')

diff --git a/web/lib/stats.inc.php b/web/lib/stats.inc.php
index e0e0b02e..da3542a1 100644
--- a/web/lib/stats.inc.php
+++ b/web/lib/stats.inc.php
@@ -11,7 +11,10 @@ function updates_table() {
 	$dbh = DB::connect();
 	$key = 'recent_updates';
 	if(!($newest_packages = get_cache_value($key))) {
-		$q = 'SELECT * FROM Packages ORDER BY ModifiedTS DESC LIMIT 10';
+		$q = 'SELECT Packages.Name, Version, ModifiedTS, SubmittedTS ';
+		$q.= 'FROM Packages INNER JOIN PackageBases ON ';
+		$q.= 'Packages.PackageBaseID = PackageBases.ID ';
+		$q.= 'ORDER BY ModifiedTS DESC LIMIT 10';
 		$result = $dbh->query($q);
 
 		$newest_packages = new ArrayObject();
@@ -31,12 +34,12 @@ function updates_table() {
  * @return void
  */
 function user_table($userid) {
-	$base_q = "SELECT count(*) FROM Packages WHERE Packages.MaintainerUID = " . $userid;
+	$base_q = "SELECT COUNT(*) FROM PackageBases WHERE MaintainerUID = " . $userid;
 
 	$maintainer_unsupported_count = db_cache_value($base_q,
 		'user_unsupported_count:' . $userid);
 
-	$q = "SELECT count(*) FROM Packages WHERE Packages.OutOfDateTS IS NOT NULL AND Packages.MaintainerUID = " . $userid;
+	$q = "SELECT COUNT(*) FROM PackageBases WHERE OutOfDateTS IS NOT NULL AND MaintainerUID = " . $userid;
 
 	$flagged_outdated = db_cache_value($q, 'user_flagged_outdated:' . $userid);
 
@@ -50,10 +53,10 @@ function user_table($userid) {
  */
 function general_stats_table() {
 	# AUR statistics
-	$q = "SELECT count(*) FROM Packages";
+	$q = "SELECT COUNT(*) FROM PackageBases";
 	$unsupported_count = db_cache_value($q, 'unsupported_count');
 
-	$q = "SELECT count(*) FROM Packages WHERE MaintainerUID IS NULL";
+	$q = "SELECT COUNT(*) FROM PackageBases WHERE MaintainerUID IS NULL";
 	$orphan_count = db_cache_value($q, 'orphan_count');
 
 	$q = "SELECT count(*) FROM Users";
@@ -65,16 +68,16 @@ function general_stats_table() {
 	$targstamp = intval(strtotime("-7 days"));
 	$yearstamp = intval(strtotime("-1 year"));
 
-	$q = "SELECT count(*) FROM Packages WHERE Packages.ModifiedTS >= $targstamp AND Packages.ModifiedTS = Packages.SubmittedTS";
+	$q = "SELECT COUNT(*) FROM PackageBases WHERE ModifiedTS >= $targstamp AND ModifiedTS = SubmittedTS";
 	$add_count = db_cache_value($q, 'add_count');
 
-	$q = "SELECT count(*) FROM Packages WHERE Packages.ModifiedTS >= $targstamp AND Packages.ModifiedTS != Packages.SubmittedTS";
+	$q = "SELECT COUNT(*) FROM PackageBases WHERE ModifiedTS >= $targstamp AND ModifiedTS != SubmittedTS";
 	$update_count = db_cache_value($q, 'update_count');
 
-	$q = "SELECT count(*) FROM Packages WHERE Packages.ModifiedTS >= $yearstamp AND Packages.ModifiedTS != Packages.SubmittedTS";
+	$q = "SELECT COUNT(*) FROM PackageBases WHERE ModifiedTS >= $yearstamp AND ModifiedTS != SubmittedTS";
 	$update_year_count = db_cache_value($q, 'update_year_count');
 
-	$q = "SELECT count(*) FROM Packages WHERE Packages.ModifiedTS = Packages.SubmittedTS";
+	$q = "SELECT COUNT(*) FROM PackageBases WHERE ModifiedTS = SubmittedTS";
 	$never_update_count = db_cache_value($q, 'never_update_count');
 
 	include('stats/general_stats_table.php');
-- 
cgit v1.2.3-24-g4f1b