[aur-dev] [PATCH 1/2] Store active TUs for each vote
This allows for easily keeping track of TUs that become active during a voting period later. Signed-off-by: Lukas Fleischer <archlinux@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
Instead of computing vote participation at the beginning of a vote only, update the number of active TUs whenever a TU becomes active during the voting period. A TU is considered active (with regard to a specific vote) if there exists a point in time during the voting period when he is active. Signed-off-by: Lukas Fleischer <archlinux@cryptocrack.de> --- web/lib/acctfuncs.inc.php | 17 +++++++++++++++++ 1 file changed, 17 insertions(+) diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 3f7b595..3a4ff51 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -287,6 +287,23 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", print __("The account, %s%s%s, has been successfully modified.", "<strong>", htmlspecialchars($U,ENT_QUOTES), "</strong>"); } + + if ($T == 2 && $inactivity_ts == 0) { + /* + * A (new) TU became active -- add him to the + * active TU list for all running votes. + */ + $q = "INSERT INTO TU_VoteActive (UserID, "; + $q.= "VoteID) SELECT " . intval($UID) . " "; + $q.= "AS UserID, ID AS VoteID FROM "; + $q.= "TU_VoteInfo WHERE End >= "; + $q.= "UNIX_TIMESTAMP() AND NOT EXISTS ("; + $q.= "SELECT * FROM TU_VoteActive WHERE "; + $q.= "TU_VoteActive.UserID = " . intval($UID); + $q.= " AND TU_VoteActive.VoteID = "; + $q.= "TU_VoteInfo.ID)"; + $dbh->exec($q); + } } } return; -- 1.8.4.rc1.383.g13e9f3f
On Mon, Aug 5, 2013 at 5:43 PM, Lukas Fleischer <archlinux@cryptocrack.de> wrote:
This allows for easily keeping track of TUs that become active during a voting period later.
Signed-off-by: Lukas Fleischer <archlinux@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;
I see this patch is based against "[PATCH 2/3] Store the number of active TUs when starting a vote". Are you going to drop that patch since this patch looks to supersede it?
+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];
To avoid dealing with the array like above, could we do this?: $row['ActiveTUs'] = $result->fetchColumn();
+ return $row; }
-- 1.8.4.rc1.383.g13e9f3f
On Mon, Aug 05, 2013 at 07:55:19PM -0400, canyonknight wrote:
On Mon, Aug 5, 2013 at 5:43 PM, Lukas Fleischer <archlinux@cryptocrack.de> wrote:
This allows for easily keeping track of TUs that become active during a voting period later.
Signed-off-by: Lukas Fleischer <archlinux@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;
I see this patch is based against "[PATCH 2/3] Store the number of active TUs when starting a vote". Are you going to drop that patch since this patch looks to supersede it?
Hmmm, yes. Rebasing sounds like a good idea here...
[...]
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 [...] @@ -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];
To avoid dealing with the array like above, could we do this?:
$row['ActiveTUs'] = $result->fetchColumn();
Sounds like a good idea. I wonder why we use fetch() followed by accessing the fetched row's first column everywhere else?
+ return $row; }
-- 1.8.4.rc1.383.g13e9f3f
participants (2)
-
canyonknight
-
Lukas Fleischer