[aur-dev] [PATCH 1/6] Store conflicts, provides and replaces in the DB

26 Apr 2014

Package conflicts, provides and replaces are now stored in the new
PackageRelations table. The gendummydata script generates test entries
for these relations.

Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>
---
 UPGRADING                | 28 ++++++++++++++++++++++++++++
 schema/aur-schema.sql    | 26 ++++++++++++++++++++++++++
 schema/gendummydata.py   | 27 ++++++++++++++++-----------
 web/html/pkgsubmit.php   | 18 ++++++++++++++++--
 web/lib/pkgfuncs.inc.php | 36 ++++++++++++++++++++++++++++++++++++
 5 files changed, 122 insertions(+), 13 deletions(-)

diff --git a/UPGRADING b/UPGRADING
index 0a801cc..f523140 100644
--- a/UPGRADING
+++ b/UPGRADING
@@ -171,6 +171,34 @@ ALTER TABLE PackageDepends
 ALTER TABLE PackageDepends MODIFY DepName VARCHAR(255) NOT NULL;
 ----
 
+15. Create a new table to store package relation types:
+
+----
+CREATE TABLE RelationTypes (
+	ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(32) NOT NULL DEFAULT '',
+	PRIMARY KEY (ID)
+) ENGINE = InnoDB;
+INSERT INTO RelationTypes VALUES (1, 'conflicts');
+INSERT INTO RelationTypes VALUES (2, 'provides');
+INSERT INTO RelationTypes VALUES (3, 'replaces');
+----
+
+16. Create a new table to store package relations:
+
+----
+CREATE TABLE PackageRelations (
+	PackageID INTEGER UNSIGNED NOT NULL,
+	RelTypeID TINYINT UNSIGNED NOT NULL,
+	RelName VARCHAR(255) NOT NULL,
+	RelCondition VARCHAR(20),
+	INDEX (PackageID),
+	INDEX (RelName),
+	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
+) ENGINE = InnoDB;
+----
+
 From 2.2.0 to 2.3.0
 -------------------
 
diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index af03b69..c98ba77 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -160,6 +160,32 @@ CREATE TABLE PackageDepends (
 ) ENGINE = InnoDB;
 
 
+-- Define the package relation types
+--
+CREATE TABLE RelationTypes (
+	ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(32) NOT NULL DEFAULT '',
+	PRIMARY KEY (ID)
+) ENGINE = InnoDB;
+INSERT INTO RelationTypes VALUES (1, 'conflicts');
+INSERT INTO RelationTypes VALUES (2, 'provides');
+INSERT INTO RelationTypes VALUES (3, 'replaces');
+
+
+-- Track which conflicts, provides and replaces a package has
+--
+CREATE TABLE PackageRelations (
+	PackageID INTEGER UNSIGNED NOT NULL,
+	RelTypeID TINYINT UNSIGNED NOT NULL,
+	RelName VARCHAR(255) NOT NULL,
+	RelCondition VARCHAR(20),
+	INDEX (PackageID),
+	INDEX (RelName),
+	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	FOREIGN KEY (RelTypeID) REFERENCES RelationTypes(ID) ON DELETE NO ACTION
+) ENGINE = InnoDB;
+
+
 -- Track which sources a package has
 --
 CREATE TABLE PackageSources (
diff --git a/schema/gendummydata.py b/schema/gendummydata.py
index 18852a2..bb622d1 100755
--- a/schema/gendummydata.py
+++ b/schema/gendummydata.py
@@ -29,6 +29,7 @@ MAX_DEVS  = .1         # what percentage of MAX_USERS are Developers
 MAX_TUS   = .2         # what percentage of MAX_USERS are Trusted Users
 MAX_PKGS  = 900       # how many packages to load
 PKG_DEPS  = (1, 15)    # min/max depends a package has
+PKG_RELS  = (1, 5)     # min/max relations a package has
 PKG_SRC   = (1, 3)     # min/max sources a package has
 PKG_CMNTS = (1, 5)     # min/max number of comments a package has
 CATEGORIES_COUNT = 17  # the number of categories from aur-schema
@@ -253,18 +254,22 @@ for p in list(track_votes.keys()):
 log.debug("Creating statements for package depends/sources.")
 for p in list(seen_pkgs.keys()):
 	num_deps = random.randrange(PKG_DEPS[0], PKG_DEPS[1])
-	this_deps = {}
-	i = 0
-	while i != num_deps:
+	for i in range(0, num_deps):
 		dep = random.choice([k for k in seen_pkgs])
-		if dep not in this_deps:
-			deptype = random.randrange(1, 5)
-			if deptype == 4:
-				dep += ": for " + random.choice([k for k in seen_pkgs])
-			s = "INSERT INTO PackageDepends VALUES (%d, %d, '%s', NULL);\n"
-			s = s % (seen_pkgs[p], deptype, dep)
-			out.write(s)
-			i += 1
+		deptype = random.randrange(1, 5)
+		if deptype == 4:
+			dep += ": for " + random.choice([k for k in seen_pkgs])
+		s = "INSERT INTO PackageDepends VALUES (%d, %d, '%s', NULL);\n"
+		s = s % (seen_pkgs[p], deptype, dep)
+		out.write(s)
+
+	num_rels = random.randrange(PKG_RELS[0], PKG_RELS[1])
+	for i in range(0, num_deps):
+		rel = random.choice([k for k in seen_pkgs])
+		reltype = random.randrange(1, 4)
+		s = "INSERT INTO PackageRelations VALUES (%d, %d, '%s', NULL);\n"
+		s = s % (seen_pkgs[p], reltype, rel)
+		out.write(s)
 
 	num_sources = random.randrange(PKG_SRC[0], PKG_SRC[1])
 	for i in range(num_sources):
diff --git a/web/html/pkgsubmit.php b/web/html/pkgsubmit.php
index 11206e1..3df38d8 100644
--- a/web/html/pkgsubmit.php
+++ b/web/html/pkgsubmit.php
@@ -155,7 +155,10 @@ if ($uid):
 					'makedepends' => array(),
 					'checkdepends' => array(),
 					'optdepends' => array(),
-					'source' => array()
+					'source' => array(),
+					'conflicts' => array(),
+					'provides' => array(),
+					'replaces' => array()
 				);
 				/* Fall-through case. */
 			case 'epoch':
@@ -171,6 +174,9 @@ if ($uid):
 			case 'makedepends':
 			case 'checkdepends':
 			case 'optdepends':
+			case 'conflicts':
+			case 'provides':
+			case 'replaces':
 				$section_info[$key][] = $value;
 				break;
 			}
@@ -190,7 +196,7 @@ if ($uid):
 			if (!isset($pkgbase_info['pkgbase'])) {
 				$pkgbase_info['pkgbase'] = $pkgbase_info['pkgname'];
 			}
-			foreach (array('source', 'depends', 'makedepends', 'checkdepends', 'optdepends') as $array_opt) {
+			foreach (array('source', 'depends', 'makedepends', 'checkdepends', 'optdepends', 'conflicts', 'provides', 'replaces') as $array_opt) {
 				if (empty($pkgbase_info[$array_opt])) {
 					$pkgbase_info[$array_opt] = array();
 				} else {
@@ -359,6 +365,14 @@ if ($uid):
 					}
 				}
 
+				foreach (array('conflicts', 'provides', 'replaces') as $reltype) {
+					foreach ($pi[$reltype] as $rel) {
+						$relpkgname = preg_replace("/(<|=|>).*/", "", $rel);
+						$relcondition = str_replace($relpkgname, "", $rel);
+						pkg_add_rel($pkgid, $reltype, $relpkgname, $relcondition);
+					}
+				}
+
 				foreach ($pi['source'] as $src) {
 					pkg_add_src($pkgid, $src);
 				}
diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php
index 5c30a95..2eb0be4 100644
--- a/web/lib/pkgfuncs.inc.php
+++ b/web/lib/pkgfuncs.inc.php
@@ -148,6 +148,21 @@ function pkg_dependency_type_id_from_name($name) {
 }
 
 /**
+ * Get the ID of a relation type given its name
+ *
+ * @param string $name The name of the relation type
+ *
+ * @return int The ID of the relation type
+ */
+function pkg_relation_type_id_from_name($name) {
+	$dbh = DB::connect();
+	$q = "SELECT ID FROM RelationTypes WHERE Name = ";
+	$q.= $dbh->quote($name);
+	$result = $dbh->query($q);
+	return $result->fetch(PDO::FETCH_COLUMN, 0);
+}
+
+/**
  * Get the HTML code to display a package dependency link
  *
  * @param string $name The name of the dependency
@@ -727,6 +742,27 @@ function pkg_add_dep($pkgid, $type, $depname, $depcondition) {
 }
 
 /**
+ * Add a relation for a specific package to the database
+ *
+ * @param int $pkgid The package ID to add the relation for
+ * @param string $type The type of relation to add
+ * @param string $relname The name of the relation to add
+ * @param string $relcondition The version requirement of the relation
+ *
+ * @return void
+ */
+function pkg_add_rel($pkgid, $type, $relname, $relcondition) {
+	$dbh = DB::connect();
+	$q = sprintf("INSERT INTO PackageRelations (PackageID, RelTypeID, RelName, RelCondition) VALUES (%d, %d, %s, %s)",
+		$pkgid,
+		pkg_relation_type_id_from_name($type),
+		$dbh->quote($relname),
+		$dbh->quote($relcondition)
+	);
+	$dbh->exec($q);
+}
+
+/**
  * Add a source for a specific package to the database
  *
  * @param int $pkgid The package ID to add the source for
-- 
1.9.2

    