New subject: [aur-dev] [PATCH 2/3] Add global comment notification setting

7 Feb 2016

As a preparatory step to adding support for package notifications on
events other than comments, rename the database table accordingly.

Signed-off-by: Lukas Fleischer 
---
 git-interface/git-serve.py   |  2 +-
 git-interface/git-update.py  |  4 ++--
 schema/aur-schema.sql        |  6 +++---
 scripts/notify.py            |  8 ++++----
 upgrading/4.2.0.txt          |  6 ++++++
 web/lib/acctfuncs.inc.php    |  2 +-
 web/lib/pkgbasefuncs.inc.php | 14 +++++++-------
 web/lib/pkgfuncs.inc.php     |  6 +++---
 8 files changed, 27 insertions(+), 21 deletions(-)

diff --git a/git-interface/git-serve.py b/git-interface/git-serve.py
index 8581590..ff389d2 100755
--- a/git-interface/git-serve.py
+++ b/git-interface/git-serve.py
@@ -81,7 +81,7 @@ def create_pkgbase(pkgbase, user):
                 "UNIX_TIMESTAMP(), %s, %s)", [pkgbase, userid, userid])
     pkgbase_id = cur.lastrowid
 
-    cur.execute("INSERT INTO CommentNotify (PackageBaseID, UserID) " +
+    cur.execute("INSERT INTO PackageNotifications (PackageBaseID, UserID) " +
                 "VALUES (%s, %s)", [pkgbase_id, userid])
 
     db.commit()
diff --git a/git-interface/git-update.py b/git-interface/git-update.py
index a4a8d8d..3b587b3 100755
--- a/git-interface/git-update.py
+++ b/git-interface/git-update.py
@@ -160,11 +160,11 @@ def save_metadata(metadata, db, cur, user):
 
     # Add user to notification list on adoption.
     if was_orphan:
-        cur.execute("SELECT COUNT(*) FROM CommentNotify WHERE " +
+        cur.execute("SELECT COUNT(*) FROM PackageNotifications WHERE " +
                     "PackageBaseID = %s AND UserID = %s",
                     [pkgbase_id, user_id])
         if cur.fetchone()[0] == 0:
-            cur.execute("INSERT INTO CommentNotify (PackageBaseID, UserID) " +
+            cur.execute("INSERT INTO PackageNotifications (PackageBaseID, UserID) " +
                         "VALUES (%s, %s)", [pkgbase_id, user_id])
 
     db.commit()
diff --git a/schema/aur-schema.sql b/schema/aur-schema.sql
index 9645e13..543f025 100644
--- a/schema/aur-schema.sql
+++ b/schema/aur-schema.sql
@@ -284,15 +284,15 @@ CREATE TABLE PackageComaintainers (
 	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
 
--- Comment addition notifications
+-- Package base notifications
 --
-CREATE TABLE CommentNotify (
+CREATE TABLE PackageNotifications (
 	PackageBaseID INTEGER UNSIGNED NOT NULL,
 	UserID INTEGER UNSIGNED NOT NULL,
 	FOREIGN KEY (PackageBaseID) REFERENCES PackageBases(ID) ON DELETE CASCADE,
 	FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
-CREATE UNIQUE INDEX NotifyUserIDPkgID ON CommentNotify (UserID, PackageBaseID);
+CREATE UNIQUE INDEX NotifyUserIDPkgID ON PackageNotifications (UserID, PackageBaseID);
 
 -- Package name blacklist
 --
diff --git a/scripts/notify.py b/scripts/notify.py
index 7b10efb..08cdda8 100755
--- a/scripts/notify.py
+++ b/scripts/notify.py
@@ -88,10 +88,10 @@ def get_maintainer_email(cur, pkgbase_id):
 
 def get_recipients(cur, pkgbase_id, uid):
     cur.execute('SELECT DISTINCT Users.Email FROM Users ' +
-                'INNER JOIN CommentNotify ' +
-                'ON CommentNotify.UserID = Users.ID WHERE ' +
-                'CommentNotify.UserID != %s AND ' +
-                'CommentNotify.PackageBaseID = %s', [uid, pkgbase_id])
+                'INNER JOIN PackageNotifications ' +
+                'ON PackageNotifications.UserID = Users.ID WHERE ' +
+                'PackageNotifications.UserID != %s AND ' +
+                'PackageNotifications.PackageBaseID = %s', [uid, pkgbase_id])
     return [row[0] for row in cur.fetchall()]
 
 
diff --git a/upgrading/4.2.0.txt b/upgrading/4.2.0.txt
index c43c6e7..7d404b3 100644
--- a/upgrading/4.2.0.txt
+++ b/upgrading/4.2.0.txt
@@ -41,3 +41,9 @@ ALTER TABLE PackageRequests ADD COLUMN ClosureComment TEXT NOT NULL DEFAULT '';
 ----
 ALTER TABLE PackageBases MODIFY COLUMN FlaggerComment TEXT NOT NULL DEFAULT '';
 ----
+
+7. Rename the CommentNotify table to PackageNotifications:
+
+----
+ALTER TABLE CommentNotify RENAME TO PackageNotifications;
+----
diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php
index 6fb2b40..0c6388a 100644
--- a/web/lib/acctfuncs.inc.php
+++ b/web/lib/acctfuncs.inc.php
@@ -897,7 +897,7 @@ function user_delete($id) {
 	$fields_delete = array(
 		array("Sessions", "UsersID"),
 		array("PackageVotes", "UsersID"),
-		array("CommentNotify", "UsersID")
+		array("PackageNotifications", "UsersID")
 	);
 
 	$fields_set_null = array(
diff --git a/web/lib/pkgbasefuncs.inc.php b/web/lib/pkgbasefuncs.inc.php
index 20f5bb4..c0e672a 100644
--- a/web/lib/pkgbasefuncs.inc.php
+++ b/web/lib/pkgbasefuncs.inc.php
@@ -542,15 +542,15 @@ function pkgbase_delete ($base_ids, $merge_base_id, $via, $grant=false) {
 		$dbh->exec($q);
 
 		/* Merge notifications */
-		$q = "SELECT DISTINCT UserID FROM CommentNotify cn ";
+		$q = "SELECT DISTINCT UserID FROM PackageNotifications cn ";
 		$q.= "WHERE PackageBaseID IN (" . implode(",", $base_ids) . ") ";
-		$q.= "AND NOT EXISTS (SELECT * FROM CommentNotify cn2 ";
+		$q.= "AND NOT EXISTS (SELECT * FROM PackageNotifications cn2 ";
 		$q.= "WHERE cn2.PackageBaseID = " . intval($merge_base_id) . " ";
 		$q.= "AND cn2.UserID = cn.UserID)";
 		$result = $dbh->query($q);
 
 		while ($notify_uid = $result->fetch(PDO::FETCH_COLUMN, 0)) {
-			$q = "INSERT INTO CommentNotify (UserID, PackageBaseID) ";
+			$q = "INSERT INTO PackageNotifications (UserID, PackageBaseID) ";
 			$q.= "VALUES (" . intval($notify_uid) . ", " . intval($merge_base_id) . ")";
 			$dbh->exec($q);
 		}
@@ -840,7 +840,7 @@ function pkgbase_user_voted($uid, $base_id) {
 function pkgbase_user_notify($uid, $base_id) {
 	$dbh = DB::connect();
 
-	$q = "SELECT * FROM CommentNotify WHERE UserID = " . $dbh->quote($uid);
+	$q = "SELECT * FROM PackageNotifications WHERE UserID = " . $dbh->quote($uid);
 	$q.= " AND PackageBaseID = " . $dbh->quote($base_id);
 	$result = $dbh->query($q);
 
@@ -898,20 +898,20 @@ function pkgbase_notify ($base_ids, $action=true) {
 
 
 		if ($action) {
-			$q = "SELECT COUNT(*) FROM CommentNotify WHERE ";
+			$q = "SELECT COUNT(*) FROM PackageNotifications WHERE ";
 			$q .= "UserID = $uid AND PackageBaseID = $bid";
 
 			/* Notification already added. Don't add again. */
 			$result = $dbh->query($q);
 			if ($result->fetchColumn() == 0) {
-				$q = "INSERT INTO CommentNotify (PackageBaseID, UserID) VALUES ($bid, $uid)";
+				$q = "INSERT INTO PackageNotifications (PackageBaseID, UserID) VALUES ($bid, $uid)";
 				$dbh->exec($q);
 			}
 
 			$output .= $basename;
 		}
 		else {
-			$q = "DELETE FROM CommentNotify WHERE PackageBaseID = $bid ";
+			$q = "DELETE FROM PackageNotifications WHERE PackageBaseID = $bid ";
 			$q .= "AND UserID = $uid";
 			$dbh->exec($q);
 
diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php
index c2bbe38..0e152dd 100644
--- a/web/lib/pkgfuncs.inc.php
+++ b/web/lib/pkgfuncs.inc.php
@@ -716,7 +716,7 @@ function pkg_search_page($SID="") {
 	/* Build the package search query. */
 	$q_select = "SELECT ";
 	if ($SID) {
-		$q_select .= "CommentNotify.UserID AS Notify,
+		$q_select .= "PackageNotifications.UserID AS Notify,
 			   PackageVotes.UsersID AS Voted, ";
 	}
 	$q_select .= "Users.Username AS Maintainer,
@@ -731,8 +731,8 @@ function pkg_search_page($SID="") {
 		/* This is not needed for the total row count query. */
 		$q_from_extra = "LEFT JOIN PackageVotes
 		ON (PackageBases.ID = PackageVotes.PackageBaseID AND PackageVotes.UsersID = $myuid)
-		LEFT JOIN CommentNotify
-		ON (PackageBases.ID = CommentNotify.PackageBaseID AND CommentNotify.UserID = $myuid) ";
+		LEFT JOIN PackageNotifications
+		ON (PackageBases.ID = PackageNotifications.PackageBaseID AND PackageNotifications.UserID = $myuid) ";
 	} else {
 		$q_from_extra = "";
 	}
-- 
2.7.0

    

[aur-dev] [PATCH 1/3] Rename the CommentNotify table to PackageNotifications

Lukas Fleischer

Lukas Fleischer

Lukas Fleischer

tags

participants (1)