Add support for anonymous comments

This allows for removing users without also removing the corresponding
comments. Instead, all comments from deleted users will be displayed as
"Anonymous comment".

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

4 files changed, 33 insertions, 8 deletions

diff --git a/UPGRADING b/UPGRADING
index 9a0f44d4..9736ef04 100644
--- a/UPGRADING
+++ b/UPGRADING
@@ -1,6 +1,23 @@
 Upgrading
 =========
 
+From 2.3.1 to 3.0.0
+-------------------
+
+1. Drop the user ID foreign key from the "PackageComments" table:
+
+`ALTER TABLE PackageComments DROP FOREIGN KEY PackageComments_ibfk_1;` should
+work in most cases. Otherwise, check the output of `SHOW CREATE TABLE
+PackageComments;` and use the foreign key name shown there.
+
+2. Add support for anonymous comments:
+
+----
+ALTER TABLE PackageComments
+	MODIFY UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+	ADD FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE SET NULL;
+----
+
 From 2.2.0 to 2.3.0
 -------------------
 
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql
index 25e828ea..c01701c8 100644
--- a/support/schema/aur-schema.sql
+++ b/support/schema/aur-schema.sql
@@ -161,14 +161,14 @@ CREATE UNIQUE INDEX VoteUsersIDPackageID ON PackageVotes (UsersID, PackageID);
 CREATE TABLE PackageComments (
 	ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 	PackageID INTEGER UNSIGNED NOT NULL,
-	UsersID INTEGER UNSIGNED NOT NULL,
+	UsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 	Comments TEXT NOT NULL DEFAULT '',
 	CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 	DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 	PRIMARY KEY (ID),
 	INDEX (UsersID),
 	INDEX (PackageID),
-	FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE CASCADE,
+	FOREIGN KEY (UsersID) REFERENCES Users(ID) ON SET NULL,
 	FOREIGN KEY (DelUsersID) REFERENCES Users(ID) ON DELETE CASCADE,
 	FOREIGN KEY (PackageID) REFERENCES Packages(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php
index c1a64f76..80165c97 100644
--- a/web/lib/pkgfuncs.inc.php
+++ b/web/lib/pkgfuncs.inc.php
@@ -208,11 +208,11 @@ function package_comments($pkgid) {
 	if ($pkgid > 0) {
 		$dbh = DB::connect();
 		$q = "SELECT PackageComments.ID, UserName, UsersID, Comments, CommentTS ";
-		$q.= "FROM PackageComments, Users ";
-		$q.= "WHERE PackageComments.UsersID = Users.ID";
-		$q.= " AND PackageID = " . $pkgid;
-		$q.= " AND DelUsersID IS NULL"; # only display non-deleted comments
-		$q.= " ORDER BY CommentTS DESC";
+		$q.= "FROM PackageComments LEFT JOIN Users ";
+		$q.= "ON PackageComments.UsersID = Users.ID ";
+		$q.= "WHERE PackageID = " . $pkgid . " ";
+		$q.= "AND DelUsersID IS NULL "; # only display non-deleted comments
+		$q.= "ORDER BY CommentTS DESC";
 
 		if (!isset($_GET['comments'])) {
 			$q.= " LIMIT 10";
diff --git a/web/template/pkg_comments.php b/web/template/pkg_comments.php
index 2ed6420d..88e739e5 100644
--- a/web/template/pkg_comments.php
+++ b/web/template/pkg_comments.php
@@ -10,7 +10,7 @@ $pkgname = $row['Name'];
 	</h3>
 
 	<?php while (list($indx, $row) = each($comments)): ?>
-		<?php if ($SID):
+		<?php if ($row['UserName'] && $SID):
 			$row['UserName'] = "<a href=\"" . get_user_uri($row['UserName']) . "\">{$row['UserName']}</a>";
 		endif; ?>
 		<h4>
@@ -22,10 +22,18 @@ $pkgname = $row['Name'];
 						<input type="hidden" name="token" value="<?= htmlspecialchars($_COOKIE['AURSID']) ?>" />
 						<input type="image" src="/images/x.png" alt="<?= __('Delete comment') ?>" name="submit" value="1" />
 					</fieldset>
+					<?php if ($row['UserName']): ?>
 					<?= __('Comment by %s', $row['UserName']) ?>
+					<?php else: ?>
+					<?= __('Anonymous comment') ?>
+					<?php endif; ?>
 				</form>
 			<?php else: ?>
+			<?php if ($row['UserName']): ?>
 			<?= __('Comment by %s', $row['UserName']) ?>
+			<?php else: ?>
+			<?= __('Anonymous comment') ?>
+			<?php endif; ?>
 			<?php endif; ?>
 		</h4>
 		<p class="timestamp"><?= gmdate('Y-m-d H:i', $row['CommentTS']) ?></p>