Store current date and time when deleting comments

Instead of modifying EditedTS when a comment is deleted, use a separate
field DelTS. Use this field to determine whether a comment has been
deleted, instead of checking DelUsersID which might be unset when the
corresponding user is deleted.

Fixes FS#47362.

Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>

4 files changed, 35 insertions, 21 deletions

diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index f99833a..0c1b08a 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -260,6 +260,7 @@ CREATE TABLE PackageComments (
 	CommentTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 	EditedTS BIGINT UNSIGNED NULL DEFAULT NULL,
 	EditedUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
+	DelTS BIGINT UNSIGNED NULL DEFAULT NULL,
 	DelUsersID INTEGER UNSIGNED NULL DEFAULT NULL,
 	PinnedTS BIGINT UNSIGNED NOT NULL DEFAULT 0,
 	PRIMARY KEY (ID),
diff --git a/upgrading/4.2.0.txt b/upgrading/4.2.0.txt
index 7482204..851d384 100644
--- a/upgrading/4.2.0.txt
+++ b/upgrading/4.2.0.txt
@@ -16,14 +16,21 @@ CREATE UNIQUE INDEX ProviderNameProvides ON OfficialProviders (Name, Provides);
 ALTER TABLE Users MODIFY Email VARCHAR(254) NOT NULL;
 ----
 
-3. Add new column in PackageComments for pinning system.
+3. Add new columns to the PackageComments table:
 
 ----
-ALTER TABLE PackageComments ADD COLUMN PinnedTS BIGINT UNSIGNED NOT NULL DEFAULT 0;
+ALTER TABLE PackageComments
+	ADD COLUMN DelTS BIGINT UNSIGNED NULL DEFAULT NULL,
+	ADD COLUMN PinnedTS BIGINT UNSIGNED NOT NULL DEFAULT 0;
 ----
 
+4. Update the deletion time stamp of all deleted comments:
 
-3. Add new column to store the closure comment of package requests:
+----
+UPDATE PackageComments SET DelTS = EditedTS WHERE DelUsersID IS NOT NULL;
+----
+
+5. Add new column to store the closure comment of package requests:
 
 ----
 ALTER TABLE PackageRequests ADD COLUMN ClosureComment TEXT NOT NULL DEFAULT '';
diff --git a/web/lib/pkgbasefuncs.inc.php b/web/lib/pkgbasefuncs.inc.php
index 7b744d5..1abe426 100644
--- a/web/lib/pkgbasefuncs.inc.php
+++ b/web/lib/pkgbasefuncs.inc.php
@@ -21,7 +21,7 @@ function pkgbase_comments_count($base_id, $include_deleted, $only_pinned=false)
 	$q = "SELECT COUNT(*) FROM PackageComments ";
 	$q.= "WHERE PackageBaseID = " . $base_id . " ";
 	if (!$include_deleted) {
-		$q.= "AND DelUsersID IS NULL";
+		$q.= "AND DelTS IS NULL";
 	}
 	if ($only_pinned) {
 		$q.= "AND NOT PinnedTS = 0";
@@ -53,7 +53,7 @@ function pkgbase_comments($base_id, $limit, $include_deleted, $only_pinned=false
 
 	$dbh = DB::connect();
 	$q = "SELECT PackageComments.ID, A.UserName AS UserName, UsersID, Comments, ";
-	$q.= "PackageBaseID, CommentTS, EditedTS, B.UserName AS EditUserName, ";
+	$q.= "PackageBaseID, CommentTS, DelTS, EditedTS, B.UserName AS EditUserName, ";
 	$q.= "DelUsersID, C.UserName AS DelUserName, ";
 	$q.= "PinnedTS FROM PackageComments ";
 	$q.= "LEFT JOIN Users A ON PackageComments.UsersID = A.ID ";
@@ -62,7 +62,7 @@ function pkgbase_comments($base_id, $limit, $include_deleted, $only_pinned=false
 	$q.= "WHERE PackageBaseID = " . $base_id . " ";
 
 	if (!$include_deleted) {
-		$q.= "AND DelUsersID IS NULL ";
+		$q.= "AND DelTS IS NULL ";
 	}
 	if ($only_pinned) {
 		$q.= "AND NOT PinnedTS = 0 ";
@@ -918,7 +918,7 @@ function pkgbase_delete_comment() {
 	if (can_delete_comment($comment_id)) {
 		$q = "UPDATE PackageComments ";
 		$q.= "SET DelUsersID = ".$uid.", ";
-		$q.= "EditedTS = UNIX_TIMESTAMP() ";
+		$q.= "DelTS = UNIX_TIMESTAMP() ";
 		$q.= "WHERE ID = ".intval($comment_id);
 		$dbh->exec($q);
 		return array(true, __("Comment has been deleted."));
diff --git a/web/template/pkg_comments.php b/web/template/pkg_comments.php
index 4f3ee3d..5a15fab 100644
--- a/web/template/pkg_comments.php
+++ b/web/template/pkg_comments.php
@@ -25,25 +25,30 @@ if (!isset($count)) {
 			$heading = __('Anonymous comment on %s', $date_fmtd);
 		}
 
-		if ($uid && $row['EditedTS']) {
+		$is_deleted = $row['DelTS'];
+		$is_edited = $row['EditedTS'];
+		$is_pinned = $row['PinnedTS'];
+
+		if ($uid && $is_deleted) {
+			$date_fmtd = gmdate('Y-m-d H:i', $row['DelTS']);
+			$user_fmtd = html_format_username($row['DelUserName']);
+			$heading .= ' <span class="edited">(';
+			$heading .= __('deleted on %s by %s', $date_fmtd, $user_fmtd);
+			$heading .= ')</span>';
+		} elseif ($uid && $is_edited) {
 			$date_fmtd = gmdate('Y-m-d H:i', $row['EditedTS']);
+			$user_fmtd = html_format_username($row['EditUserName']);
 			$heading .= ' <span class="edited">(';
-			if ($row['DelUsersID']) {
-				$user_fmtd = html_format_username($row['DelUserName']);
-				$heading .= __('deleted on %s by %s', $date_fmtd, $user_fmtd);
-			} else {
-				$user_fmtd = html_format_username($row['EditUserName']);
-				$heading .= __('last edited on %s by %s', $date_fmtd, $user_fmtd);
-			}
+			$heading .= __('edited on %s by %s', $date_fmtd, $user_fmtd);
 			$heading .= ')</span>';
 		}
 
 		$row['DelUserName'] = html_format_username($row['DelUserName']);
 		$row['EditUserName'] = html_format_username($row['EditUserName']);
 		?>
-		<h4 id="comment-<?= $row['ID'] ?>"<?php if ($row['DelUsersID']): ?> class="comment-deleted"<?php endif; ?>>
+		<h4 id="comment-<?= $row['ID'] ?>"<?php if ($is_deleted): ?> class="comment-deleted"<?php endif; ?>>
 			<?= $heading ?>
-			<?php if (!$row['DelUsersID'] && can_delete_comment_array($row)): ?>
+			<?php if (!$is_deleted && can_delete_comment_array($row)): ?>
 				<form class="delete-comment-form" method="post" action="<?= htmlspecialchars(get_pkgbase_uri($pkgbase_name), ENT_QUOTES); ?>">
 					<fieldset style="display:inline;">
 						<input type="hidden" name="action" value="do_DeleteComment" />
@@ -53,11 +58,12 @@ if (!isset($count)) {
 					</fieldset>
 				</form>
 			<?php endif; ?>
-			<?php if (!$row['DelUsersID'] && can_edit_comment_array($row)): ?>
+
+			<?php if (!$is_deleted && can_edit_comment_array($row)): ?>
 			<a href="<?= htmlspecialchars(get_pkgbase_uri($pkgbase_name) . 'edit-comment/?comment_id=' . $row['ID'], ENT_QUOTES) ?>" class="edit-comment" title="<?= __('Edit comment') ?>"><img src="/images/pencil.min.svg" alt="<?= __('Edit comment') ?>" width="11" height="11"></a>
 			<?php endif; ?>
 
-			<?php if (!$row['DelUsersID'] && !$row['PinnedTS'] && can_pin_comment_array($row) && !(pkgbase_comments_count($base_id, false, true) >= 5)): ?>
+			<?php if (!$is_deleted && !$is_pinned && can_pin_comment_array($row) && !(pkgbase_comments_count($base_id, false, true) >= 5)): ?>
 				<form class="pin-comment-form" method="post" action="<?= htmlspecialchars(get_pkgbase_uri($pkgbase_name), ENT_QUOTES); ?>">
 					<fieldset style="display:inline;">
 						<input type="hidden" name="action" value="do_PinComment" />
@@ -69,7 +75,7 @@ if (!isset($count)) {
 				</form>
 			<?php endif; ?>
 
-			<?php if (!$row['DelUsersID'] && $row['PinnedTS'] && can_pin_comment_array($row)): ?>
+			<?php if (!$is_deleted && $is_pinned && can_pin_comment_array($row)): ?>
 				<form class="pin-comment-form" method="post" action="<?= htmlspecialchars(get_pkgbase_uri($pkgbase_name), ENT_QUOTES); ?>">
 					<fieldset style="display:inline;">
 						<input type="hidden" name="action" value="do_UnpinComment" />
@@ -80,7 +86,7 @@ if (!isset($count)) {
 				</form>
 			<?php endif; ?>
 		</h4>
-		<div class="article-content<?php if ($row['DelUsersID']): ?> comment-deleted<?php endif; ?>">
+		<div class="article-content<?php if ($is_deleted): ?> comment-deleted<?php endif; ?>">
 			<p>
 				<?= parse_comment($row['Comments']) ?>
 			</p>