Remove Dummy Package concept

Instead, we just store dependencies directly in the PackageDepends
table. Since we don't use this info anywhere besides the package details
page, there is little value in precalculating what is in the AUR vs.
what is not.

An upgrade path is provided via several SQL statements in the UPGRADING
document. There should be no user-visible change from this, but the DB
schema gets a bit more sane and we no longer have loads of junk packages
in our tables that are never shown to the end user. This should also
help the MySQL query planner in several cases as we no longer have to be
careful to exclude dummy packages on every query.

Signed-off-by: Dan McGee <dan@archlinux.org>
Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>

10 files changed, 41 insertions, 84 deletions

diff --git a/UPGRADING b/UPGRADING
index 4743b8e..e6590f0 100644
--- a/UPGRADING
+++ b/UPGRADING
@@ -8,6 +8,18 @@ From 1.8.1 to X.X.X
 
 UPDATE Packages SET ModifiedTS = SubmittedTS WHERE ModifiedTS = 0;
 
+2. Move to new method of storing package depends.
+
+----
+ALTER TABLE PackageDepends ADD COLUMN DepName VARCHAR(64) NOT NULL DEFAULT '' AFTER PackageID;
+UPDATE PackageDepends SET DepName = (SELECT Name FROM Packages WHERE ID = DepPkgID);
+ALTER TABLE PackageDepends MODIFY DepName VARCHAR(64) NOT NULL;
+ALTER TABLE PackageDepends DROP FOREIGN KEY `PackageDepends_ibfk_2`;
+ALTER TABLE PackageDepends DROP COLUMN DepPkgID;
+DELETE FROM Packages WHERE DummyPkg = 1;
+ALTER TABLE Packages DROP COLUMN DummyPkg;
+----
+
 From 1.8.0 to 1.8.1
 -------------------
 
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql
index 8226b18..d8c8560 100644
--- a/support/schema/aur-schema.sql
+++ b/support/schema/aur-schema.sql
@@ -98,7 +98,6 @@ CREATE TABLE Packages (
 	CategoryID TINYINT UNSIGNED NOT NULL DEFAULT 1,
 	Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package",
 	URL VARCHAR(255) NOT NULL DEFAULT "http://www.archlinux.org",
-	DummyPkg TINYINT UNSIGNED NOT NULL DEFAULT 0,         -- 1=>dummy
 	License VARCHAR(40) NOT NULL DEFAULT '',
 	NumVotes INTEGER UNSIGNED NOT NULL DEFAULT 0,
 	OutOfDateTS BIGINT UNSIGNED NULL DEFAULT NULL,
@@ -109,7 +108,6 @@ CREATE TABLE Packages (
 	PRIMARY KEY (ID),
 	UNIQUE (Name),
 	INDEX (CategoryID),
-	INDEX (DummyPkg),
 	INDEX (NumVotes),
 	INDEX (SubmitterUID),
 	INDEX (MaintainerUID),
@@ -124,11 +122,10 @@ CREATE TABLE Packages (
 --
 CREATE TABLE PackageDepends (
 	PackageID INTEGER UNSIGNED NOT NULL,
-	DepPkgID INTEGER UNSIGNED NOT NULL,
+	DepName VARCHAR(64) NOT NULL,
 	DepCondition VARCHAR(20),
 	INDEX (PackageID),
 	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
-	FOREIGN KEY (DepPkgID) REFERENCES Packages(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
 
 
diff --git a/support/scripts/newpackage-notify b/support/scripts/newpackage-notify
index 66cb45c..9afee07 100755
--- a/support/scripts/newpackage-notify
+++ b/support/scripts/newpackage-notify
@@ -40,7 +40,7 @@ q = dbconnection.cursor()
 
 q.execute("SELECT Packages.Name, Packages.Version, Packages.ID, "
 	"Packages.Description, Users.Username FROM Packages, Users "
-	"WHERE SubmittedTS >= %d AND DummyPkg = 0 AND "
+	"WHERE SubmittedTS >= %d AND "
 	"Packages.SubmitterUID = Users.ID" % starttime)
 
 packages = q.fetchall()
diff --git a/web/html/pkgsubmit.php b/web/html/pkgsubmit.php
index 3eb60c5..1540c8a 100644
--- a/web/html/pkgsubmit.php
+++ b/web/html/pkgsubmit.php
@@ -311,16 +311,6 @@ if ($_COOKIE["AURSID"]):
 				$q = "DELETE FROM PackageSources WHERE PackageID = " . $packageID;
 				db_query($q, $dbh);
 
-				# If the package was a dummy, undummy it
-				if ($pdata['DummyPkg']) {
-					$q = sprintf( "UPDATE Packages SET DummyPkg = 0, SubmitterUID = %d, MaintainerUID = %d, SubmittedTS = UNIX_TIMESTAMP() WHERE ID = %d",
-						$uid,
-						$uid,
-						$packageID);
-
-					db_query($q, $dbh);
-				}
-
 				# If a new category was chosen, change it to that
 				if ($_POST['category'] > 1) {
 					$q = sprintf( "UPDATE Packages SET CategoryID = %d WHERE ID = %d",
@@ -366,7 +356,6 @@ if ($_COOKIE["AURSID"]):
 			# Update package depends
 			$depends = explode(" ", $new_pkgbuild['depends']);
 			foreach ($depends as $dep) {
-				$q = "INSERT INTO PackageDepends (PackageID, DepPkgID, DepCondition) VALUES (";
 				$deppkgname = preg_replace("/(<|<=|=|>=|>).*/", "", $dep);
 				$depcondition = str_replace($deppkgname, "", $dep);
 
@@ -374,8 +363,10 @@ if ($_COOKIE["AURSID"]):
 					break;
 				}
 
-				$deppkgid = create_dummy($deppkgname, $_COOKIE['AURSID']);
-				$q .= $packageID . ", " . $deppkgid . ", '" . mysql_real_escape_string($depcondition) . "')";
+				$q = sprintf("INSERT INTO PackageDepends (PackageID, DepName, DepCondition) VALUES (%d, '%s', '%s')",
+					$packageID,
+					mysql_real_escape_string($deppkgname),
+					mysql_real_escape_string($depcondition));
 
 				db_query($q, $dbh);
 			}
diff --git a/web/html/rss.php b/web/html/rss.php
index c9b87ec..cb0bf40 100644
--- a/web/html/rss.php
+++ b/web/html/rss.php
@@ -31,7 +31,6 @@ $rss->image = $image;
 #Get the latest packages and add items for them
 $dbh = db_connect();
 $q = "SELECT * FROM Packages ";
-$q.= "WHERE DummyPkg != 1 ";
 $q.= "ORDER BY SubmittedTS DESC ";
 $q.= "LIMIT 0 , 20";
 $result = db_query($q, $dbh);
diff --git a/web/lib/aur.inc b/web/lib/aur.inc
index 430666c..0e494a2 100644
--- a/web/lib/aur.inc
+++ b/web/lib/aur.inc
@@ -331,12 +331,11 @@ function html_footer($ver="") {
 function can_submit_pkg($name="", $sid="") {
 	if (!$name || !$sid) {return 0;}
 	$dbh = db_connect();
-	$q = "SELECT MaintainerUID, DummyPkg ";
+	$q = "SELECT MaintainerUID ";
 	$q.= "FROM Packages WHERE Name = '".mysql_real_escape_string($name)."'";
 	$result = db_query($q, $dbh);
 	if (mysql_num_rows($result) == 0) {return 1;}
 	$row = mysql_fetch_row($result);
-	if ($row[1] == "1") { return 1; }
 	$my_uid = uid_from_sid($sid);
 
 	if ($row[0] === NULL || $row[0] == $my_uid) {
diff --git a/web/lib/aurjson.class.php b/web/lib/aurjson.class.php
index 0c69281..3570909 100644
--- a/web/lib/aurjson.class.php
+++ b/web/lib/aurjson.class.php
@@ -110,7 +110,7 @@ class AurJSON {
         $keyword_string = addcslashes($keyword_string, '%_');
 
         $query = "SELECT " . implode(',', $this->fields) .
-            " FROM Packages WHERE DummyPkg=0 AND " .
+            " FROM Packages WHERE " .
             "  ( Name LIKE '%{$keyword_string}%' OR " .
             "    Description LIKE '%{$keyword_string}%' )";
         $result = db_query($query, $this->dbh);
@@ -136,7 +136,7 @@ class AurJSON {
      **/
     private function info($pqdata) {
         $base_query = "SELECT " . implode(',', $this->fields) .
-            " FROM Packages WHERE DummyPkg=0 AND ";
+            " FROM Packages WHERE ";
 
         if ( is_numeric($pqdata) ) {
             // just using sprintf to coerce the pqd to an int
diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc
index 699a3a9..d9e2c13 100644
--- a/web/lib/pkgfuncs.inc
+++ b/web/lib/pkgfuncs.inc
@@ -95,7 +95,6 @@ function package_exists($name="") {
 	$dbh = db_connect();
 	$q = "SELECT ID FROM Packages ";
 	$q.= "WHERE Name = '".mysql_real_escape_string($name)."' ";
-	$q.= "AND DummyPkg = 0";
 	$result = db_query($q, $dbh);
 	if (!$result) {return NULL;}
 	$row = mysql_fetch_row($result);
@@ -109,10 +108,10 @@ function package_dependencies($pkgid=0) {
 	$pkgid = intval($pkgid);
 	if ($pkgid > 0) {
 		$dbh = db_connect();
-		$q = "SELECT DepPkgID, Name, DummyPkg, DepCondition FROM PackageDepends, Packages ";
-		$q.= "WHERE PackageDepends.DepPkgID = Packages.ID ";
-		$q.= "AND PackageDepends.PackageID = ". $pkgid;
-		$q.= " ORDER BY Name";
+		$q = "SELECT pd.DepName, pd.DepCondition, p.ID FROM PackageDepends pd ";
+		$q.= "LEFT JOIN Packages p ON pd.DepName = p.Name ";
+		$q.= "WHERE pd.PackageID = ". $pkgid . " ";
+		$q.= "ORDER BY pd.DepName";
 		$result = db_query($q, $dbh);
 		if (!$result) {return array();}
 		while ($row = mysql_fetch_row($result)) {
@@ -122,15 +121,14 @@ function package_dependencies($pkgid=0) {
 	return $deps;
 }
 
-function package_required($pkgid=0) {
+function package_required($name="") {
 	$deps = array();
-	$pkgid = intval($pkgid);
-	if ($pkgid > 0) {
+	if ($name != "") {
 		$dbh = db_connect();
-		$q = "SELECT PackageID, Name, DummyPkg from PackageDepends, Packages ";
-		$q.= "WHERE PackageDepends.PackageID = Packages.ID ";
-		$q.= "AND PackageDepends.DepPkgID = ". $pkgid;
-		$q.= " ORDER BY Name";
+		$q = "SELECT p.Name, PackageID FROM PackageDepends pd ";
+		$q.= "JOIN Packages p ON pd.PackageID = p.ID ";
+		$q.= "WHERE DepName = '".mysql_real_escape_string($name)."' ";
+		$q.= "ORDER BY p.Name";
 		$result = db_query($q, $dbh);
 		if (!$result) {return array();}
 		while ($row = mysql_fetch_row($result)) {
@@ -140,38 +138,6 @@ function package_required($pkgid=0) {
 	return $deps;
 }
 
-# create a dummy package and return it's Packages.ID  if it already exists,
-# return the existing ID
-#
-function create_dummy($pname="", $sid="") {
-	if ($pname && $sid) {
-		$uid = uid_from_sid($sid);
-		if (!$uid) {return NULL;}
-		$dbh = db_connect();
-		$q = "SELECT ID FROM Packages WHERE Name = '";
-		$q.= mysql_real_escape_string($pname)."'";
-		$result = db_query($q, $dbh);
-		if (!mysql_num_rows($result)) {
-			# Insert the dummy
-			#
-			$q = "INSERT INTO Packages (Name, Description, URL, SubmittedTS, ";
-			$q.= "SubmitterUID, DummyPkg) VALUES ('";
-			$q.= mysql_real_escape_string($pname)."', 'A dummy package', '/#', ";
-			$q.= "UNIX_TIMESTAMP(), ".$uid.", 1)";
-			$result = db_query($q, $dbh);
-			if (!$result) {
-				return NULL;
-			}
-			return mysql_insert_id($dbh);
-		} else {
-			$data = mysql_fetch_row($result);
-			return $data[0];
-		}
-	}
-	return NULL;
-
-}
-
 # Return the number of comments for a specified package
 function package_comments_count($pkgid = 0) {
 	$pkgid = intval($pkgid);
@@ -458,7 +424,7 @@ function pkg_search_page($SID="") {
 		$q_from_extra = "";
 	}
 
-	$q_where = "WHERE Packages.DummyPkg = 0 ";
+	$q_where = "WHERE 1 = 1 ";
 	// TODO: possibly do string matching on category
 	//       to make request variable values more sensible
 	if (isset($_GET["C"]) && intval($_GET["C"])) {
diff --git a/web/lib/stats.inc b/web/lib/stats.inc
index f924fb5..a345c40 100644
--- a/web/lib/stats.inc
+++ b/web/lib/stats.inc
@@ -36,7 +36,7 @@ function updates_table($dbh)
 	global $apc_prefix, $apc_ttl;
 	$key = $apc_prefix . 'recent_updates';
 	if(!(EXTENSION_LOADED_APC && ($newest_packages = apc_fetch($key)))) {
-		$q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY ModifiedTS DESC LIMIT 0 , 10';
+		$q = 'SELECT * FROM Packages ORDER BY ModifiedTS DESC LIMIT 0 , 10';
 		$result = db_query($q, $dbh);
 
 		$newest_packages = new ArrayObject();
@@ -74,7 +74,7 @@ function general_stats_table($dbh)
 {
 	global $apc_prefix;
 	# AUR statistics
-	$q = "SELECT count(*) FROM Packages WHERE DummyPkg = 0";
+	$q = "SELECT count(*) FROM Packages";
 	$unsupported_count = db_cache_value($q, $dbh, $apc_prefix . 'unsupported_count');
 
 	$q = "SELECT count(*) from Users";
diff --git a/web/template/pkg_details.php b/web/template/pkg_details.php
index 1a1e611..ef2ba73 100644
--- a/web/template/pkg_details.php
+++ b/web/template/pkg_details.php
@@ -101,24 +101,22 @@ $out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : gmdate("r", intval($row[
 	</p>
 <?php
 
-	# $deps[0] = array('id','name', 'dummy');
 	$deps = package_dependencies($row["ID"]);
-	$requiredby = package_required($row["ID"]);
+	$requiredby = package_required($row["Name"]);
 
 	if (count($deps) > 0 || count($requiredby) > 0) {
 		echo '<p>';
 	}
 
 	if (count($deps) > 0) {
-
 		echo "<span class='boxSoftTitle'><span class='f3'>". __("Dependencies")."</span></span>";
 
 		while (list($k, $darr) = each($deps)) {
-			if ($darr[2] == 0) {
-				# $darr[3] is the DepCondition
-				echo " <a href='packages.php?ID=".$darr[0]."'>".$darr[1].$darr[3]."</a>";
+			# darr: (DepName, DepCondition, PackageID), where ID is NULL if it didn't exist
+			if (!is_null($darr[2])) {
+				echo " <a href='packages.php?ID=".$darr[2]."'>".$darr[0].$darr[1]."</a>";
 			} else {
-				echo " <a href='http://www.archlinux.org/packages/?q=".$darr[1]."'>".$darr[1].$darr[3]."</a>";
+				echo " <a href='http://www.archlinux.org/packages/?q=".$darr[0]."'>".$darr[0].$darr[1]."</a>";
 			}
 		}
 
@@ -128,17 +126,12 @@ $out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : gmdate("r", intval($row[
 	}
 
 	if (count($requiredby) > 0) {
-
 		echo "<span class='boxSoftTitle'><span class='f3'>". __("Required by")."</span></span>";
 
 		while (list($k, $darr) = each($requiredby)) {
-			if ($darr[2] == 0) {
-				echo " <a href='packages.php?ID=".$darr[0]."'>".$darr[1]."</a>";
-			} else {
-				print "<a href='http://www.archlinux.org/packages/?q=".$darr[1]."'>".$darr[1]."</a>";
-			}
+			# darr: (PackageName, PackageID)
+			echo " <a href='packages.php?ID=".$darr[1]."'>".$darr[0]."</a>";
 		}
-
 	}
 
 	if (count($deps) > 0 || count($requiredby) > 0) {