Subject: CAcert Code Development list.
List archive
- From: Michael Tänzer <michael.taenzer AT cacert.org>
- To: "critical-admin AT cacert.org" <critical-admin AT cacert.org>
- Cc: cacert-devel AT lists.cacert.org, Ulrich Schröter CAce rt <ulrich AT cacert.org>, Dirk Astrath <dirk.astrath AT cacert.org>, Marcus Mängel <inopiae AT cacert.org>
- Subject: Patch request: Bug #1024
- Date: Thu, 09 Aug 2012 18:51:13 +0200
- Openpgp: id=9940BEF1
Hi folks,
We have a fix for https://bugs.cacert.org/view.php?id=1024
"Assurer flag is not set correctly on updatesort.php run"
The fix was reviewed by Dirk Astrath (dastrath) and me (NEOatNHNG) and
tested by Ulrich Schröter (Uli60), Marcus Mängel (INOPIAE) and JensK.
Diff is attached. Please also run the locale makefile so that our
translators see the new strings (if present) on
https://translations.cacert.org and new translations get imported into
the system.
Changed files:
includes/lib/account.php
scripts/cron/updatesort.php
--
Have a nice day,
Michael Tänzer
diff --git a/includes/lib/account.php b/includes/lib/account.php index c7697ce..e311668 100644 --- a/includes/lib/account.php +++ b/includes/lib/account.php @@ -17,37 +17,82 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA */ -function fix_assurer_flag($userID) +/** + * Function to recalculate the cached Assurer status + * + * @param int $userID + * if the user ID is not given the flag will be recalculated for all users + * + * @return bool + * false if there was an error on fixing the flag. This does NOT return the + * new value of the flag + */ +function fix_assurer_flag($userID = NULL) { - // If requirements for assurers are modified see also scripts/cron/updatesort.php - - // Update Assurer-Flag on users table if 100 points. - // Should the number of points be SUM(points) or SUM(awarded)? - $query = mysql_query('UPDATE `users` AS `u` SET `assurer` = 1 WHERE '. - '`u`.`id` = \''.(int)intval($userID).'\' AND '. - 'EXISTS(SELECT 1 FROM `cats_passed` AS `cp`, `cats_variant` AS `cv` '. - 'WHERE `cp`.`variant_id` = `cv`.`id` AND `cv`.`type_id` = 1 AND '. - '`cp`.`user_id` = `u`.`id`) AND '. - '(SELECT SUM(`points`) FROM `notary` AS `n` WHERE `n`.`to` = `u`.`id` '. - 'AND (`n`.`expire` > now() OR `n`.`expire` IS NULL)) >= 100'); - // Challenge has been passed and non-expired points >= 100 - + // Update Assurer-Flag on users table if 100 points and CATS passed. + // + // We may have some performance issues here if no userID is given + // there are ~150k assurances and ~220k users currently + // but the exists-clause on cats_passed should be a good filter + $sql = ' + UPDATE `users` AS `u` SET `assurer` = 1 + WHERE '.( + ($userID === NULL) ? + '`u`.`assurer` = 0' : + '`u`.`id` = \''.intval($userID).'\'' + ).' + AND EXISTS( + SELECT 1 FROM `cats_passed` AS `cp`, `cats_variant` AS `cv` + WHERE `cp`.`variant_id` = `cv`.`id` + AND `cv`.`type_id` = 1 + AND `cp`.`user_id` = `u`.`id` + ) + AND ( + SELECT SUM(`points`) FROM `notary` AS `n` + WHERE `n`.`to` = `u`.`id` + AND (`n`.`expire` > now() + OR `n`.`expire` IS NULL) + ) >= 100'; + + $query = mysql_query($sql); if (!$query) { return false; } - + // Challenge has been passed and non-expired points >= 100 + // Reset flag if requirements are not met - $query = mysql_query('UPDATE `users` AS `u` SET `assurer` = 0 WHERE '. - '`u`.`id` = \''.(int)intval($userID).'\' AND '. - '(NOT EXISTS(SELECT 1 FROM `cats_passed` AS `cp`, `cats_variant` AS '. - '`cv` WHERE `cp`.`variant_id` = `cv`.`id` AND `cv`.`type_id` = 1 '. - 'AND `cp`.`user_id` = `u`.`id`) OR '. - '(SELECT SUM(`points`) FROM `notary` AS `n` WHERE `n`.`to` = `u`.`id` '. - 'AND (`n`.`expire` > now() OR `n`.`expire` IS NULL)) < 100)'); - + // + // Also a bit performance critical but assurer flag is only set on + // ~5k accounts + $sql = ' + UPDATE `users` AS `u` SET `assurer` = 0 + WHERE '.( + ($userID === NULL) ? + '`u`.`assurer` <> 0' : + '`u`.`id` = \''.intval($userID).'\'' + ).' + AND ( + NOT EXISTS( + SELECT 1 FROM `cats_passed` AS `cp`, + `cats_variant` AS `cv` + WHERE `cp`.`variant_id` = `cv`.`id` + AND `cv`.`type_id` = 1 + AND `cp`.`user_id` = `u`.`id` + ) + OR ( + SELECT SUM(`points`) FROM `notary` AS `n` + WHERE `n`.`to` = `u`.`id` + AND ( + `n`.`expire` > now() + OR `n`.`expire` IS NULL + ) + ) < 100 + )'; + + $query = mysql_query($sql); if (!$query) { return false; } - + return true; } \ No newline at end of file diff --git a/scripts/cron/updatesort.php b/scripts/cron/updatesort.php index 498eda2..051b179 100755 --- a/scripts/cron/updatesort.php +++ b/scripts/cron/updatesort.php @@ -17,55 +17,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA */ require_once(dirname(__FILE__).'/../../includes/mysql.php'); + require_once(dirname(__FILE__).'/../../includes/lib/account.php'); - - /* Set assurer flag for accounts who miss it - - See also includes/lib/account.php, function fix_assurer_flag($userID) - - We may have some performance problems here, there are 150k assurances and 220k users - in the production database. The exists-clause on cats_passed should be a good filter... */ - - /* Synchronisation of assurer flag currently deactivated, see https://bugs.cacert.org/view.php?id=1003 - and https://bugs.cacert.org/view.php?id=1024 */ -/* - $query = "select `n`.`to` as `uid` from `notary` as `n`, `users` as `u` ". - " where `n`.`to`=`u`.`id` and `u`.`assurer`<>'1' ". - " and (`n`.`expire` > now() OR `n`.`expire` IS NULL) ". - " and exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` ". - " where `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id` = 1 and `cp`.`user_id`=`n`.`to`)". - " group by `n`.`to` having sum(`n`.`points`)>=100"; - - $res = mysql_query($query); - while($row = mysql_fetch_assoc($res)) - { - $query = "update users set `assurer`='1' where `id`='${row['uid']}'"; - //echo $query."\n"; - mysql_query($query); + // Recalculate assurer flag for all accounts + if (!fix_assurer_flag()) { + fwrite(STDERR, "ERROR on fixing the assurer flag. Continuing anyway"); } -*/ - /* Remove assurer flag from accounts not eligible. - - Also a bit performance critical, but assurer flag is only set at 5k accounts - */ - /* Synchronisation of assurer flag currently deactivated, see https://bugs.cacert.org/view.php?id=1003 - and https://bugs.cacert.org/view.php?id=1024 */ -/* - $query = "select `u`.id as `uid` from `users` as `u` " . - " where `u`.`assurer` = '1' ". - " and (not exists(select 1 from `cats_passed` as `cp`, `cats_variant` as `cv` ". - " where `cp`.`variant_id`=`cv`.`id` and `cv`.`type_id` = 1 and `cp`.`user_id`=`u`.`id`) ". - " or (select sum(`n`.`points`) from `notary` as `n` where `n`.`to`=`u`.`id` and (`n`.`expire` > now() OR `n`.`expire` IS NULL)) < 100) "; - $res = mysql_query($query); - while($row = mysql_fetch_assoc($res)) - { - $query = "update users set `assurer`='0' where `id`='${row['uid']}'"; - //echo $query."\n"; - mysql_query($query); - } -*/ mysql_query("update `locations` set `acount`=0"); $query = "SELECT `users`.`locid` AS `locid`, count(*) AS `total` FROM `users`
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
- Patch request: Bug #1024, Michael Tänzer, 08/09/2012
- Re: Patch request: Bug #1024, Wytze van der Raay, 08/10/2012
Archive powered by MHonArc 2.6.16.