[aur-dev] [PATCH] 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> --- UPGRADING | 17 +++++++++++++++++ support/schema/aur-schema.sql | 4 ++-- web/lib/pkgfuncs.inc.php | 10 +++++----- web/template/pkg_comments.php | 10 +++++++++- 4 files changed, 33 insertions(+), 8 deletions(-) diff --git a/UPGRADING b/UPGRADING index 9a0f44d..9736ef0 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 25e828e..c01701c 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 c1a64f7..80165c9 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 2ed6420..88e739e 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> -- 1.8.5.3
Lukas Fleischer wrote:
This allows for removing users without also removing the corresponding comments. Instead, all comments from deleted users will be displayed as "Anonymous comment".
Can you also insert the original poster's name into the comment? E.g. "/originally posted by Foo" In that case I would call it a "ghost comment" instead of an "anonymous comment".
Maybe you should notify user he can no more delete his messages when he's deleting his account. Le Mardi 4 février 2014 18h54, Lukas Fleischer <archlinux@cryptocrack.de> a écrit : 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> --- UPGRADING | 17 +++++++++++++++++ support/schema/aur-schema.sql | 4 ++-- web/lib/pkgfuncs.inc.php | 10 +++++----- web/template/pkg_comments.php | 10 +++++++++- 4 files changed, 33 insertions(+), 8 deletions(-) diff --git a/UPGRADING b/UPGRADING index 9a0f44d..9736ef0 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 25e828e..c01701c 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 c1a64f7..80165c9 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 2ed6420..88e739e 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> -- 1.8.5.3
On Tue, Feb 4, 2014 at 12:54 PM, Lukas Fleischer <archlinux@cryptocrack.de> wrote:
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> ---
I'm less than favorable towards this change. There is no built-in method of deleting AUR accounts, so we know that all account deletions will be done by someone consciously removing accounts in the database. So as of right now these "Anonymous comments" will only be created by the explicit action of the AUR maintainer. In this case the explicit action that was being proposed is the deletion of the accounts that haven't been used for >500 days. So the comments that would be effected by this change must be at a minimum 500 days old. Do we really believe that the majority of these old comments are still useful? What is really gained by deleting these accounts if we are making an extra effort with this patch to retain the comments associated with them?
UPGRADING | 17 +++++++++++++++++ support/schema/aur-schema.sql | 4 ++-- web/lib/pkgfuncs.inc.php | 10 +++++----- web/template/pkg_comments.php | 10 +++++++++- 4 files changed, 33 insertions(+), 8 deletions(-)
diff --git a/UPGRADING b/UPGRADING index 9a0f44d..9736ef0 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 25e828e..c01701c 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,
Should be: FOREIGN KEY (UsersID) REFERENCES Users(ID) ON DELETE 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 c1a64f7..80165c9 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 2ed6420..88e739e 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> -- 1.8.5.3
participants (4)
-
canyonknight
-
Lukas Fleischer
-
nico cornu
-
Xyne