[aur-dev] [PATCH 1/2] Store current date and time when deleting comments
Lukas Fleischer
lfleischer at archlinux.org
Sun Dec 13 20:21:19 UTC 2015
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 at archlinux.org>
---
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 .= ' <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>
--
2.6.4
More information about the aur-dev
mailing list