[aur-dev] [PATCH 0/3] Automated TU vote participation calculation

newer
[aur-dev] [PATCH 1/3] Add a quorum...

Lukas Fleischer

4 Aug 2013 4 Aug '13

4:27 p.m.

The first idea is to allow for setting one's inactivity status using the AUR instead of having to consult several different sources (mailing lists, the Wiki, archweb, ...) This allows for doing an automated calculation of vote participation. Note that this currently doesn't really match the TU bylaws since the bylaws define active TUs as the number of active TUs at the *end* of the vote, whereas this patch counts TUs when the vote is started. We should come up with a proper solution before this is merged (some additional patches; amend the bylaws?) Comments welcome. Lukas Fleischer (3): Allow for setting an account's inactivity status Store the number of active TUs when starting a vote Show participation in vote details UPGRADING | 16 ++++++++++++++++ support/schema/aur-schema.sql | 2 ++ web/html/account.php | 5 +++-- web/lib/acctfuncs.inc.php | 39 ++++++++++++++++++++++++++++++-------- web/template/account_details.php | 6 ++++++ web/template/account_edit_form.php | 9 +++++++++ web/template/tu_details.php | 6 ++++++ 7 files changed, 73 insertions(+), 10 deletions(-) -- 1.8.4.rc1.383.g13e9f3f

Show replies by date

Lukas Fleischer

4 Aug 4 Aug

4:27 p.m.

New subject: [aur-dev] [PATCH 1/3] Allow for setting an account's inactivity status

This adds a field to the users table and corresponding fields to the account edit and display forms that allow for setting an (in-)activity status. This might turn out to be useful if a user is on vacation and can not respond to update/orphan/deletion requests. It will also be used for automated participation calculation later. Signed-off-by: Lukas Fleischer --- UPGRADING | 9 +++++++++ support/schema/aur-schema.sql | 1 + web/html/account.php | 5 +++-- web/lib/acctfuncs.inc.php | 28 ++++++++++++++++++++++------ web/template/account_details.php | 6 ++++++ web/template/account_edit_form.php | 9 +++++++++ 6 files changed, 50 insertions(+), 8 deletions(-) diff --git a/UPGRADING b/UPGRADING index a04471f..d8527e3 100644 --- a/UPGRADING +++ b/UPGRADING @@ -1,6 +1,15 @@ Upgrading ========= +From 2.2.0 to 2.3.0 +------------------- + +1. Add an inactivity time stamp to the "Users" table: + +---- +ALTER TABLE Users ADD COLUMN InactivityTS BIGINT NOT NULL DEFAULT 0; +---- + From 2.1.0 to 2.2.0 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index 0d04f12..68db93f 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -35,6 +35,7 @@ CREATE TABLE Users ( LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (ID), UNIQUE (Username), UNIQUE (Email), diff --git a/web/html/account.php b/web/html/account.php index 4af3043..79b5eeb 100644 --- a/web/html/account.php +++ b/web/html/account.php @@ -52,7 +52,8 @@ if (isset($_COOKIE["AURSID"])) { display_account_form($atype, "UpdateAccount", $row["Username"], $row["AccountTypeID"], $row["Suspended"], $row["Email"], "", "", $row["RealName"], $row["LangPreference"], - $row["IRCNick"], $row["PGPKey"], $row["ID"]); + $row["IRCNick"], $row["PGPKey"], + $row["InactivityTS"] ? 1 : 0, $row["ID"]); } else { print __("You do not have permission to edit this account."); } @@ -81,7 +82,7 @@ if (isset($_COOKIE["AURSID"])) { in_request("U"), in_request("T"), in_request("S"), in_request("E"), in_request("P"), in_request("C"), in_request("R"), in_request("L"), in_request("I"), - in_request("K"), in_request("ID")); + in_request("K"), in_request("J"), in_request("ID")); } } else { if ($atype == "Trusted User" || $atype == "Developer") { diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 1deeac5..7602ec2 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -54,12 +54,13 @@ function html_format_pgp_fingerprint($fingerprint) { * @param string $L The language preference of the displayed user * @param string $I The IRC nickname of the displayed user * @param string $K The PGP key fingerprint of the displayed user + * @param string $J The inactivity status of the displayed user * @param string $UID The user ID of the displayed user * * @return void */ function display_account_form($UTYPE,$A,$U="",$T="",$S="", - $E="",$P="",$C="",$R="",$L="",$I="",$K="",$UID=0) { + $E="",$P="",$C="",$R="",$L="",$I="",$K="",$J="", $UID=0) { global $SUPPORTED_LANGS; include("account_edit_form.php"); @@ -83,12 +84,13 @@ function display_account_form($UTYPE,$A,$U="",$T="",$S="", * @param string $L The language preference of the user * @param string $I The IRC nickname of the user * @param string $K The PGP fingerprint of the user + * @param string $J The inactivity status of the user * @param string $UID The user ID of the modified account * * @return string|void Return void if successful, otherwise return error */ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", - $P="",$C="",$R="",$L="",$I="",$K="",$UID=0) { + $P="",$C="",$R="",$L="",$I="",$K="",$J="",$UID=0) { # error check and process request for a new/modified account global $SUPPORTED_LANGS, $AUR_LOCATION; @@ -185,7 +187,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", if ($error) { print "<ul class='errorlist'><li>".$error."</li></ul>\n"; display_account_form($UTYPE, $A, $U, $T, $S, $E, "", "", - $R, $L, $I, $K, $UID); + $R, $L, $I, $K, $J, $UID); } else { if ($TYPE == "new") { # no errors, go ahead and create the unprivileged user @@ -206,9 +208,10 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", $I = $dbh->quote($I); $K = $dbh->quote(str_replace(" ", "", $K)); $q = "INSERT INTO Users (AccountTypeID, Suspended, "; - $q.= "Username, Email, Passwd, Salt, RealName, "; - $q.= "LangPreference, IRCNick, PGPKey) VALUES (1, 0, "; - $q.= "$U, $E, $P, $salt, $R, $L, $I, $K)"; + $q.= "InactivityTS, Username, Email, Passwd, Salt, "; + $q.= "RealName, LangPreference, IRCNick, PGPKey) "; + $q.= "VALUES (1, 0, 0, $U, $E, $P, $salt, $R, $L, "; + $q.= "$I, $K)"; $result = $dbh->exec($q); if (!$result) { print __("Error trying to create account, %s%s%s.", @@ -240,6 +243,18 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", } else { # no errors, go ahead and modify the user account + $q = "SELECT InactivityTS FROM Users WHERE "; + $q.= "ID = " . intval($UID); + $result = $dbh->query($q); + $row = $result->fetch(PDO::FETCH_NUM); + if ($row[0] && $J) { + $inactivity_ts = $row[0]; + } elseif ($J) { + $inactivity_ts = time(); + } else { + $inactivity_ts = 0; + } + $q = "UPDATE Users SET "; $q.= "Username = " . $dbh->quote($U); if ($T) { @@ -262,6 +277,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", $q.= ", LangPreference = " . $dbh->quote($L); $q.= ", IRCNick = " . $dbh->quote($I); $q.= ", PGPKey = " . $dbh->quote(str_replace(" ", "", $K)); + $q.= ", InactivityTS = " . $inactivity_ts; $q.= " WHERE ID = ".intval($UID); $result = $dbh->exec($q); if (!$result) { diff --git a/web/template/account_details.php b/web/template/account_details.php index fdebfb3..02ed32b 100644 --- a/web/template/account_details.php +++ b/web/template/account_details.php @@ -38,6 +38,12 @@ <td><?= html_format_pgp_fingerprint($row["PGPKey"]) ?></td> </tr> <tr> + <th><?= __("Status") . ":" ?></th> + <td> + <?= $row["InactivityTS"] ? __("Inactive since") . ' ' . date("Y-m-d H:i", $row["InactivityTS"]) : __("Active"); ?> + </td> + </tr> + <tr> <th><?= __("Last Voted") . ":" ?></th> <td> <?= $row["LastVoted"] ? date("Y-m-d", $row["LastVoted"]) : __("Never"); ?> diff --git a/web/template/account_edit_form.php b/web/template/account_edit_form.php index a0de2a3..fbab7eb 100644 --- a/web/template/account_edit_form.php +++ b/web/template/account_edit_form.php @@ -56,6 +56,15 @@ <?php endif; ?> <p> + <label for="id_inactive"><?= __("Inactive") ?>:</label> + <?php if ($J): ?> + <input type="checkbox" name="J" id="id_inactive" checked="checked" /> + <?php else: ?> + <input type="checkbox" name="J" id="id_inactive" /> + <?php endif; ?> + </p> + + <p> <label for="id_email"><?= __("Email Address") ?>:</label> <input type="text" size="30" maxlength="64" name="E" id="id_email" value="<?= htmlspecialchars($E,ENT_QUOTES) ?>" /> (<?= __("required") ?>) </p> -- 1.8.4.rc1.383.g13e9f3f

Florian Pritz

10:44 p.m.

New subject: [aur-dev] [PATCH 1/3] Allow for setting an account's inactivity status

On 04.08.2013 15:27, Lukas Fleischer wrote:

...

This adds a field to the users table and corresponding fields to the account edit and display forms that allow for setting an (in-)activity status.

This might turn out to be useful if a user is on vacation and can not respond to update/orphan/deletion requests. It will also be used for automated participation calculation later.

Signed-off-by: Lukas Fleischer --- UPGRADING | 9 +++++++++ support/schema/aur-schema.sql | 1 + web/html/account.php | 5 +++-- web/lib/acctfuncs.inc.php | 28 ++++++++++++++++++++++------ web/template/account_details.php | 6 ++++++ web/template/account_edit_form.php | 9 +++++++++ 6 files changed, 50 insertions(+), 8 deletions(-)

diff --git a/UPGRADING b/UPGRADING index a04471f..d8527e3 100644 --- a/UPGRADING +++ b/UPGRADING @@ -1,6 +1,15 @@ Upgrading =========

+From 2.2.0 to 2.3.0 +------------------- + +1. Add an inactivity time stamp to the "Users" table: + +---- +ALTER TABLE Users ADD COLUMN InactivityTS BIGINT NOT NULL DEFAULT 0; +---- + From 2.1.0 to 2.2.0 -------------------

diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index 0d04f12..68db93f 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -35,6 +35,7 @@ CREATE TABLE Users ( LastVoted BIGINT UNSIGNED NOT NULL DEFAULT 0, LastLogin BIGINT UNSIGNED NOT NULL DEFAULT 0, LastLoginIPAddress INTEGER UNSIGNED NOT NULL DEFAULT 0, + InactivityTS BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (ID), UNIQUE (Username), UNIQUE (Email), diff --git a/web/html/account.php b/web/html/account.php index 4af3043..79b5eeb 100644 --- a/web/html/account.php +++ b/web/html/account.php @@ -52,7 +52,8 @@ if (isset($_COOKIE["AURSID"])) { display_account_form($atype, "UpdateAccount", $row["Username"], $row["AccountTypeID"], $row["Suspended"], $row["Email"], "", "", $row["RealName"], $row["LangPreference"], - $row["IRCNick"], $row["PGPKey"], $row["ID"]); + $row["IRCNick"], $row["PGPKey"], + $row["InactivityTS"] ? 1 : 0, $row["ID"]); } else { print __("You do not have permission to edit this account."); } @@ -81,7 +82,7 @@ if (isset($_COOKIE["AURSID"])) { in_request("U"), in_request("T"), in_request("S"), in_request("E"), in_request("P"), in_request("C"), in_request("R"), in_request("L"), in_request("I"), - in_request("K"), in_request("ID")); + in_request("K"), in_request("J"), in_request("ID")); } } else { if ($atype == "Trusted User" || $atype == "Developer") { diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 1deeac5..7602ec2 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -54,12 +54,13 @@ function html_format_pgp_fingerprint($fingerprint) { * @param string $L The language preference of the displayed user * @param string $I The IRC nickname of the displayed user * @param string $K The PGP key fingerprint of the displayed user + * @param string $J The inactivity status of the displayed user * @param string $UID The user ID of the displayed user * * @return void */ function display_account_form($UTYPE,$A,$U="",$T="",$S="", - $E="",$P="",$C="",$R="",$L="",$I="",$K="",$UID=0) { + $E="",$P="",$C="",$R="",$L="",$I="",$K="",$J="", $UID=0) {

That looks like you should use an array instead of dedicated variables/arguments. Default array + array_merge($defaults, $args). I know that's a rather large change and falls into refactoring, just saying in case you didn't think of it yet.

...

global $SUPPORTED_LANGS;

include("account_edit_form.php"); @@ -83,12 +84,13 @@ function display_account_form($UTYPE,$A,$U="",$T="",$S="", * @param string $L The language preference of the user * @param string $I The IRC nickname of the user * @param string $K The PGP fingerprint of the user + * @param string $J The inactivity status of the user * @param string $UID The user ID of the modified account * * @return string|void Return void if successful, otherwise return error */ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", - $P="",$C="",$R="",$L="",$I="",$K="",$UID=0) { + $P="",$C="",$R="",$L="",$I="",$K="",$J="",$UID=0) {

# error check and process request for a new/modified account global $SUPPORTED_LANGS, $AUR_LOCATION; @@ -185,7 +187,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", if ($error) { print "<ul class='errorlist'><li>".$error."</li></ul>\n"; display_account_form($UTYPE, $A, $U, $T, $S, $E, "", "", - $R, $L, $I, $K, $UID); + $R, $L, $I, $K, $J, $UID); } else { if ($TYPE == "new") { # no errors, go ahead and create the unprivileged user @@ -206,9 +208,10 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", $I = $dbh->quote($I); $K = $dbh->quote(str_replace(" ", "", $K)); $q = "INSERT INTO Users (AccountTypeID, Suspended, "; - $q.= "Username, Email, Passwd, Salt, RealName, "; - $q.= "LangPreference, IRCNick, PGPKey) VALUES (1, 0, "; - $q.= "$U, $E, $P, $salt, $R, $L, $I, $K)"; + $q.= "InactivityTS, Username, Email, Passwd, Salt, "; + $q.= "RealName, LangPreference, IRCNick, PGPKey) "; + $q.= "VALUES (1, 0, 0, $U, $E, $P, $salt, $R, $L, "; + $q.= "$I, $K)"; $result = $dbh->exec($q); if (!$result) { print __("Error trying to create account, %s%s%s.", @@ -240,6 +243,18 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", } else { # no errors, go ahead and modify the user account

+ $q = "SELECT InactivityTS FROM Users WHERE "; + $q.= "ID = " . intval($UID); + $result = $dbh->query($q); + $row = $result->fetch(PDO::FETCH_NUM); + if ($row[0] && $J) { + $inactivity_ts = $row[0]; + } elseif ($J) { + $inactivity_ts = time(); + } else { + $inactivity_ts = 0; + } + $q = "UPDATE Users SET "; $q.= "Username = " . $dbh->quote($U); if ($T) { @@ -262,6 +277,7 @@ function process_account_form($UTYPE,$TYPE,$A,$U="",$T="",$S="",$E="", $q.= ", LangPreference = " . $dbh->quote($L); $q.= ", IRCNick = " . $dbh->quote($I); $q.= ", PGPKey = " . $dbh->quote(str_replace(" ", "", $K)); + $q.= ", InactivityTS = " . $inactivity_ts; $q.= " WHERE ID = ".intval($UID); $result = $dbh->exec($q); if (!$result) { diff --git a/web/template/account_details.php b/web/template/account_details.php index fdebfb3..02ed32b 100644 --- a/web/template/account_details.php +++ b/web/template/account_details.php @@ -38,6 +38,12 @@ <td><?= html_format_pgp_fingerprint($row["PGPKey"]) ?></td> </tr> <tr> + <th><?= __("Status") . ":" ?></th> + <td> + <?= $row["InactivityTS"] ? __("Inactive since") . ' ' . date("Y-m-d H:i", $row["InactivityTS"]) : __("Active"); ?> + </td> + </tr> + <tr> <th><?= __("Last Voted") . ":" ?></th> <td> <?= $row["LastVoted"] ? date("Y-m-d", $row["LastVoted"]) : __("Never"); ?> diff --git a/web/template/account_edit_form.php b/web/template/account_edit_form.php index a0de2a3..fbab7eb 100644 --- a/web/template/account_edit_form.php +++ b/web/template/account_edit_form.php @@ -56,6 +56,15 @@ <?php endif; ?>

<p> + <label for="id_inactive"><?= __("Inactive") ?>:</label> + <?php if ($J): ?> + <input type="checkbox" name="J" id="id_inactive" checked="checked" /> + <?php else: ?> + <input type="checkbox" name="J" id="id_inactive" /> + <?php endif; ?>

<?php print($J ? 'checked="checked"' : ""); ?> (not 100% sure if the syntax is correct, but you get the idea). A single <input> tag is better because you can't break stuff partially if you ever change the field name.

...

+ </p> + + <p> <label for="id_email"><?= __("Email Address") ?>:</label> <input type="text" size="30" maxlength="64" name="E" id="id_email" value="<?= htmlspecialchars($E,ENT_QUOTES) ?>" /> (<?= __("required") ?>) </p>

Lukas Fleischer

4:27 p.m.

New subject: [aur-dev] [PATCH 2/3] Store the number of active TUs when starting a vote

This will be used for automated calculation of vote participation later. Signed-off-by: Lukas Fleischer --- UPGRADING | 7 +++++++ support/schema/aur-schema.sql | 1 + web/lib/acctfuncs.inc.php | 11 +++++++++-- 3 files changed, 17 insertions(+), 2 deletions(-) diff --git a/UPGRADING b/UPGRADING index d8527e3..307ed8d 100644 --- a/UPGRADING +++ b/UPGRADING @@ -10,6 +10,13 @@ From 2.2.0 to 2.3.0 ALTER TABLE Users ADD COLUMN InactivityTS BIGINT NOT NULL DEFAULT 0; ---- +2. Add a field to store the number of active TUs to the "TU_VoteInfo" table: + +---- +ALTER TABLE TU_VoteInfo + ADD COLUMN ActiveTUs tinyint(3) unsigned NOT NULL default '0'; +---- + From 2.1.0 to 2.2.0 ------------------- diff --git a/support/schema/aur-schema.sql b/support/schema/aur-schema.sql index 68db93f..51f9601 100644 --- a/support/schema/aur-schema.sql +++ b/support/schema/aur-schema.sql @@ -202,6 +202,7 @@ 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; diff --git a/web/lib/acctfuncs.inc.php b/web/lib/acctfuncs.inc.php index 7602ec2..6fe70af 100644 --- a/web/lib/acctfuncs.inc.php +++ b/web/lib/acctfuncs.inc.php @@ -618,10 +618,17 @@ function open_user_proposals($user) { function add_tu_proposal($agenda, $user, $votelength, $submitteruid) { $dbh = DB::connect(); - $q = "INSERT INTO TU_VoteInfo (Agenda, User, Submitted, End, SubmitterID) VALUES "; + $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, "; + $q.= "SubmitterID, ActiveTUs) VALUES "; $q.= "(" . $dbh->quote($agenda) . ", " . $dbh->quote($user) . ", "; $q.= "UNIX_TIMESTAMP(), UNIX_TIMESTAMP() + " . $dbh->quote($votelength); - $q.= ", " . $submitteruid . ")"; + $q.= ", " . $submitteruid . ", " . $active_tus . ")"; $result = $dbh->exec($q); } -- 1.8.4.rc1.383.g13e9f3f

Lukas Fleischer

4:27 p.m.

New subject: [aur-dev] [PATCH 3/3] Show participation in vote details

This is calculated by dividing the sum of all votes by the number of active TUs (where the number of active TUs is measured when the vote starts). Signed-off-by: Lukas Fleischer --- web/template/tu_details.php | 6 ++++++ 1 file changed, 6 insertions(+) diff --git a/web/template/tu_details.php b/web/template/tu_details.php index be816ce..4f291f0 100644 --- a/web/template/tu_details.php +++ b/web/template/tu_details.php @@ -34,6 +34,7 @@ <th><?= __("Abstain") ?></th> <th><?= __("Total") ?></th> <th><?= __('Voted') ?></th> + <th><?= __('Participation') ?></th> </tr> <tr> <td><?= $row['Yes'] ?></td> @@ -47,6 +48,11 @@ <span style="color: green; font-weight: bold"><?= __("Yes") ?></span> <?php endif; ?> </td> + <?php if ($row['ActiveTUs'] > 0): ?> + <td><?= number_format(($row['Yes'] + $row['No'] + $row['Abstain']) / $row['ActiveTUs'] * 100, 2) ?>%</td> + <?php else: ?> + <td><?= __("unknown") ?></td> + <?php endif; ?> </tr> </table> </div> -- 1.8.4.rc1.383.g13e9f3f

3939

Age (days ago)

3939

Last active (days ago)

List overview

Download

4 comments

2 participants

participants (2)

Florian Pritz
Lukas Fleischer