[aur-dev] [PATCH 1/2] Store active TUs for each vote

[Lukas Fleischer]

This allows for easily keeping track of TUs that become active during a
voting period later.

Signed-off-by: Lukas Fleischer <archlinux at cryptocrack.de>
---
 UPGRADING                     | 18 +++++++++++++-----
 support/schema/aur-schema.sql | 10 +++++++++-
 web/lib/acctfuncs.inc.php     | 23 ++++++++++++++---------
 3 files changed, 36 insertions(+), 15 deletions(-)

diff --git a/UPGRADING b/UPGRADING
index a300a91..740078c 100644
--- a/UPGRADING
+++ b/UPGRADING
@@ -10,13 +10,21 @@ From 2.2.0 to 2.3.0
 ALTER TABLE Users ADD COLUMN InactivityTS BIGINT NOT NULL DEFAULT 0;
 ----
 
-2. Add fields to store the number of active TUs and the quorum to the
-   "TU_VoteInfo" table:
+2. Add a field to store the quorum to the "TU_VoteInfo" table:
 
 ----
-ALTER TABLE TU_VoteInfo
-	ADD COLUMN ActiveTUs tinyint(3) unsigned NOT NULL default '0',
-	ADD COLUMN Quorum decimal(2, 2) unsigned NOT NULL;
+ALTER TABLE TU_VoteInfo ADD COLUMN Quorum decimal(2, 2) unsigned NOT NULL;
+----
+
+3. Create the "TU_VoteActive" table:
+
+----
+CREATE TABLE IF NOT EXISTS TU_VoteActive (
+  VoteID int(10) unsigned NOT NULL,
+  UserID int(10) unsigned NOT NULL,
+  FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
+  FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
+) ENGINE = InnoDB;
 ----
 
 From 2.1.0 to 2.2.0
diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql
index 59da3aa..6efe91c 100644
--- a/support/schema/aur-schema.sql
+++ b/support/schema/aur-schema.sql
@@ -203,11 +203,19 @@ CREATE TABLE IF NOT EXISTS TU_VoteInfo (
   Yes tinyint(3) unsigned NOT NULL default '0',
   No tinyint(3) unsigned NOT NULL default '0',
   Abstain tinyint(3) unsigned NOT NULL default '0',
-  ActiveTUs tinyint(3) unsigned NOT NULL default '0',
   PRIMARY KEY  (ID),
   FOREIGN KEY (SubmitterID) REFERENCES Users(ID) ON DELETE CASCADE
 ) ENGINE = InnoDB;
 
+-- TUs being active during a specific voting period
+--
+CREATE TABLE IF NOT EXISTS TU_VoteActive (
+  VoteID int(10) unsigned NOT NULL,
+  UserID int(10) unsigned NOT NULL,
+  FOREIGN KEY (VoteID) REFERENCES TU_VoteInfo(ID) ON DELETE CASCADE,
+  FOREIGN KEY (UserID) REFERENCES Users(ID) ON DELETE CASCADE
+) ENGINE = InnoDB;
+
 -- Individual vote records
 --
 CREATE TABLE IF NOT EXISTS TU_Votes (
diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php
index 9df1856..3f7b595 100644
--- a/web/lib/acctfuncs.inc.php
+++ b/web/lib/acctfuncs.inc.php
@@ -618,19 +618,18 @@ function open_user_proposals($user) {
 function add_tu_proposal($agenda, $user, $votelength, $quorum, $submitteruid) {
 	$dbh = DB::connect();
 
-	$q = "SELECT COUNT(*) FROM Users WHERE AccountTypeID = 2 AND ";
-	$q.= "InactivityTS = 0";
-	$result = $dbh->query($q);
-	$row = $result->fetch(PDO::FETCH_NUM);
-	$active_tus = $row[0];
-
 	$q = "INSERT INTO TU_VoteInfo (Agenda, User, Submitted, End, Quorum, ";
-	$q.= "SubmitterID, ActiveTUs) VALUES ";
+	$q.= "SubmitterID) VALUES ";
 	$q.= "(" . $dbh->quote($agenda) . ", " . $dbh->quote($user) . ", ";
 	$q.= "UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + " . $dbh->quote($votelength);
-	$q.= ", " . $dbh->quote($quorum) . ", " . $submitteruid . ", ";
-	$q.= $active_tus . ")";
+	$q.= ", " . $dbh->quote($quorum) . ", " . $submitteruid . ")";
 	$result = $dbh->exec($q);
+	$vote_id = last_insert_id();
+
+	$q = "INSERT INTO TU_VoteActive (VoteID, UserID) SELECT ";
+	$q.= $vote_id . " AS VoteID, ID AS UserID FROM Users WHERE ";
+	$q.= "AccountTypeID = 2 AND InactivityTS = 0";
+	$dbh->exec($q);
 }
 
 /**
@@ -1026,6 +1025,12 @@ function vote_details($voteid) {
 	$result = $dbh->query($q);
 	$row = $result->fetch(PDO::FETCH_ASSOC);
 
+	$q = "SELECT COUNT(*) FROM TU_VoteActive WHERE VoteID = ";
+	$q.= intval($voteid);
+	$result = $dbh->query($q);
+	$row_ = $result->fetch(PDO::FETCH_NUM);
+	$row['ActiveTUs'] = $row_[0];
+
 	return $row;
 }
 
-- 
1.8.4.rc1.383.g13e9f3f