From 51407d4a296563ccb3f488589a531babba7a8c22 Mon Sep 17 00:00:00 2001 From: Lukas Fleischer Date: Sun, 13 Dec 2015 20:57:06 +0100 Subject: 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 --- schema/aur-schema.sql | 1 + upgrading/4.2.0.txt | 13 ++++++++++--- web/lib/pkgbasefuncs.inc.php | 8 ++++---- web/template/pkg_comments.php | 34 ++++++++++++++++++++-------------- 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 .= ' ('; + $heading .= __('deleted on %s by %s', $date_fmtd, $user_fmtd); + $heading .= ')'; + } elseif ($uid && $is_edited) { $date_fmtd = gmdate('Y-m-d H:i', $row['EditedTS']); + $user_fmtd = html_format_username($row['EditUserName']); $heading .= ' ('; - 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 .= ')'; } $row['DelUserName'] = html_format_username($row['DelUserName']); $row['EditUserName'] = html_format_username($row['EditUserName']); ?> -

class="comment-deleted"> +

class="comment-deleted"> - +
@@ -53,11 +58,12 @@ if (!isset($count)) {
- + + <?= __('Edit comment') ?> - = 5)): ?> + = 5)): ?>
@@ -69,7 +75,7 @@ if (!isset($count)) { - +
@@ -80,7 +86,7 @@ if (!isset($count)) {

-
+

-- cgit v1.2.3-70-g09d2