Support multiple licenses per package

Split out package licenses into two separate tables in order to support
multiple licenses per package. The code on the package details page is
adjusted accordingly.

UPGRADING contains instructions on how to convert existing licenses in
the database to the new layout.

Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de>

5 files changed, 157 insertions, 16 deletions

diff --git a/UPGRADING b/UPGRADING
index 814fea1..357cd6c 100644
--- a/UPGRADING
+++ b/UPGRADING
@@ -217,6 +217,39 @@ CREATE TABLE PackageGroups (
 ) ENGINE = InnoDB;
 ----
 
+18. Create tables to store package licenses:
+
+----
+CREATE TABLE Licenses (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(64) NOT NULL,
+	PRIMARY KEY (ID),
+	UNIQUE (Name)
+) ENGINE = InnoDB;
+CREATE TABLE PackageLicenses (
+	PackageID INTEGER UNSIGNED NOT NULL,
+	LicenseID INTEGER UNSIGNED NOT NULL,
+	PRIMARY KEY (PackageID, LicenseID),
+	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	FOREIGN KEY (LicenseID) REFERENCES Licenses(ID) ON DELETE CASCADE
+) ENGINE = InnoDB;
+----
+
+19. Convert existing licenses to the new storage format:
+
+----
+INSERT INTO Licenses (Name) SELECT DISTINCT License FROM Packages;
+INSERT INTO PackageLicenses (PackageID, LicenseID)
+	SELECT Packages.ID, Licenses.ID FROM Packages
+	INNER JOIN Licenses ON Licenses.Name = Packages.License;
+----
+
+20. Delete the license column from the Packages table:
+
+----
+ALTER TABLE Packages DROP COLUMN License;
+----
+
 From 2.2.0 to 2.3.0
 -------------------
 
diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index ae42fd3..8533548 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -126,13 +126,33 @@ CREATE TABLE Packages (
 	Version VARCHAR(32) NOT NULL DEFAULT '',
 	Description VARCHAR(255) NOT NULL DEFAULT "An Arch Package",
 	URL VARCHAR(255) NOT NULL DEFAULT "https://www.archlinux.org",
-	License VARCHAR(40) NOT NULL DEFAULT '',
 	PRIMARY KEY (ID),
 	UNIQUE (Name),
 	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
 
 
+-- Information about licenses
+--
+CREATE TABLE Licenses (
+	ID INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
+	Name VARCHAR(64) NOT NULL,
+	PRIMARY KEY (ID),
+	UNIQUE (Name)
+) ENGINE = InnoDB;
+
+
+-- Information about package-license-relations
+--
+CREATE TABLE PackageLicenses (
+	PackageID INTEGER UNSIGNED NOT NULL,
+	LicenseID INTEGER UNSIGNED NOT NULL,
+	PRIMARY KEY (PackageID, LicenseID),
+	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE,
+	FOREIGN KEY (LicenseID) REFERENCES Licenses(ID) ON DELETE CASCADE
+) ENGINE = InnoDB;
+
+
 -- Information about groups
 --
 CREATE TABLE Groups (
diff --git a/web/html/pkgsubmit.php b/web/html/pkgsubmit.php
index 107441f..1075620 100644
--- a/web/html/pkgsubmit.php
+++ b/web/html/pkgsubmit.php
@@ -151,6 +151,7 @@ if ($uid):
 					}
 				}
 				$section_info = array(
+					'license' => array(),
 					'groups' => array(),
 					'depends' => array(),
 					'makedepends' => array(),
@@ -167,9 +168,9 @@ if ($uid):
 			case 'pkgver':
 			case 'pkgrel':
 			case 'url':
-			case 'license':
 				$section_info[$key] = $value;
 				break;
+			case 'license':
 			case 'groups':
 			case 'source':
 			case 'depends':
@@ -198,7 +199,7 @@ if ($uid):
 			if (!isset($pkgbase_info['pkgbase'])) {
 				$pkgbase_info['pkgbase'] = $pkgbase_info['pkgname'];
 			}
-			foreach (array('groups', 'source', 'depends', 'makedepends', 'checkdepends', 'optdepends', 'conflicts', 'provides', 'replaces') as $array_opt) {
+			foreach (array('license', 'groups', 'source', 'depends', 'makedepends', 'checkdepends', 'optdepends', 'conflicts', 'provides', 'replaces') as $array_opt) {
 				if (empty($pkgbase_info[$array_opt])) {
 					$pkgbase_info[$array_opt] = array();
 				} else {
@@ -263,9 +264,11 @@ if ($uid):
 				$error = __("Error - Package description cannot be greater than %d characters", 255);
 				break;
 			}
-			if (strlen($pi['license']) > 40) {
-				$error = __("Error - Package license cannot be greater than %d characters", 40);
-				break;
+			foreach ($pi['license'] as $lic) {
+				if (strlen($lic > 64)) {
+					$error = __("Error - Package license cannot be greater than %d characters", 64);
+					break;
+				}
 			}
 			if (strlen($pkginfo[$key]['full-version']) > 32) {
 				$error = __("Error - Package version cannot be greater than %d characters", 32);
@@ -357,7 +360,12 @@ if ($uid):
 			}
 
 			foreach ($pkginfo as $pi) {
-				$pkgid = pkg_create($base_id, $pi['pkgname'], $pi['license'], $pi['full-version'], $pi['pkgdesc'], $pi['url']);
+				$pkgid = pkg_create($base_id, $pi['pkgname'], $pi['full-version'], $pi['pkgdesc'], $pi['url']);
+
+				foreach ($pi['license'] as $lic) {
+					$licid = pkg_create_license($lic);
+					pkg_add_lic($pkgid, $licid);
+				}
 
 				foreach ($pi['groups'] as $grp) {
 					$grpid = pkg_create_group($grp);
diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php
index 6811767..f800e34 100644
--- a/web/lib/pkgfuncs.inc.php
+++ b/web/lib/pkgfuncs.inc.php
@@ -105,6 +105,32 @@ function pkg_from_name($name="") {
 }
 
 /**
+ * Get licenses for a specific package
+ *
+ * @param int $pkgid The package to get licenses for
+ *
+ * @return array All licenses for the package
+ */
+function pkg_licenses($pkgid) {
+	$lics = array();
+	$pkgid = intval($pkgid);
+	if ($pkgid > 0) {
+		$dbh = DB::connect();
+		$q = "SELECT l.Name FROM Licenses l ";
+		$q.= "INNER JOIN PackageLicenses pl ON pl.LicenseID = l.ID ";
+		$q.= "WHERE pl.PackageID = ". $pkgid;
+		$result = $dbh->query($q);
+		if (!$result) {
+			return array();
+		}
+		while ($row = $result->fetch(PDO::FETCH_COLUMN, 0)) {
+			$lics[] = $row;
+		}
+	}
+	return $lics;
+}
+
+/**
  * Get package groups for a specific package
  *
  * @param int $pkgid The package to get groups for
@@ -756,20 +782,18 @@ function pkg_details_by_name($pkgname) {
  *
  * @param int $base_id ID of the package base
  * @param string $pkgname Name of the new package
- * @param string $license License of the new package
  * @param string $pkgver Version of the new package
  * @param string $pkgdesc Description of the new package
  * @param string $pkgurl Upstream URL for the new package
  *
  * @return int ID of the new package
  */
-function pkg_create($base_id, $pkgname, $license, $pkgver, $pkgdesc, $pkgurl) {
+function pkg_create($base_id, $pkgname, $pkgver, $pkgdesc, $pkgurl) {
 	$dbh = DB::connect();
-	$q = sprintf("INSERT INTO Packages (PackageBaseID, Name, License, " .
-		"Version, Description, URL) VALUES (%d, %s, %s, %s, %s, %s)",
-		$base_id, $dbh->quote($pkgname), $dbh->quote($license),
-		$dbh->quote($pkgver), $dbh->quote($pkgdesc),
-		$dbh->quote($pkgurl));
+	$q = sprintf("INSERT INTO Packages (PackageBaseID, Name, Version, " .
+		"Description, URL) VALUES (%d, %s, %s, %s, %s)",
+		$base_id, $dbh->quote($pkgname), $dbh->quote($pkgver),
+		$dbh->quote($pkgdesc), $dbh->quote($pkgurl));
 	$dbh->exec($q);
 	return $dbh->lastInsertId();
 }
@@ -874,3 +898,46 @@ function pkg_add_grp($pkgid, $grpid) {
 	);
 	$dbh->exec($q);
 }
+
+/**
+ * Creates a new license and returns its ID
+ *
+ * If the license already exists, the ID of the already existing license is
+ * returned.
+ *
+ * @param string $name The name of the license to create
+ *
+ * @return int The ID of the license
+ */
+function pkg_create_license($name) {
+	$dbh = DB::connect();
+	$q = sprintf("SELECT ID FROM Licenses WHERE Name = %s", $dbh->quote($name));
+	$result = $dbh->query($q);
+	if ($result) {
+		$licid = $result->fetch(PDO::FETCH_COLUMN, 0);
+		if ($licid > 0) {
+			return $licid;
+		}
+	}
+
+	$q = sprintf("INSERT INTO Licenses (Name) VALUES (%s)", $dbh->quote($name));
+	$dbh->exec($q);
+	return $dbh->lastInsertId();
+}
+
+/**
+ * Add a license to a package
+ *
+ * @param int $pkgid The package ID of the package
+ * @param int $grpid The ID of the license to add
+ *
+ * @return void
+ */
+function pkg_add_lic($pkgid, $licid) {
+	$dbh = DB::connect();
+	$q = sprintf("INSERT INTO PackageLicenses (PackageID, LicenseID) VALUES (%d, %d)",
+		$pkgid,
+		$licid
+	);
+	$dbh->exec($q);
+}
diff --git a/web/template/pkg_details.php b/web/template/pkg_details.php
index ff3f710..7836596 100644
--- a/web/template/pkg_details.php
+++ b/web/template/pkg_details.php
@@ -22,6 +22,7 @@ $out_of_date_time = ($row["OutOfDateTS"] == 0) ? $msg : gmdate("Y-m-d", intval($
 
 $urlpath = URL_DIR . substr($row['BaseName'], 0, 2) . "/" . $row['BaseName'];
 
+$lics = pkg_licenses($row["ID"]);
 $grps = pkg_groups($row["ID"]);
 
 $deps = pkg_dependencies($row["ID"]);
@@ -170,10 +171,22 @@ if ($SID && ($uid == $row["MaintainerUID"] ||
 <?php endif; ?>
 			</td>
 		</tr>
+		<?php if (count($lics) > 0): ?>
 		<tr>
-			<th><?= __('License') . ': ' ?></th>
-			<td><?= htmlspecialchars($license) ?></td>
+			<th><?= __('Licenses') . ': ' ?></th>
+			<td class="wrap">
+				<?php foreach($lics as $lic): ?>
+				<span class="related">
+					<?php if ($lic !== end($lics)): ?>
+					<?= htmlspecialchars($lic) ?>,
+					<?php else: ?>
+					<?= htmlspecialchars($lic) ?>
+					<?php endif; ?>
+				</span>
+				<?php endforeach; ?>
+			</td>
 		</tr>
+		<?php endif; ?>
 		<?php if (count($grps) > 0): ?>
 		<tr>
 			<th><?= __('Groups') . ': ' ?></th>