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

[canyonknight]

On Mon, Aug 5, 2013 at 5:43 PM, Lukas Fleischer
<archlinux at 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 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;

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
>