Subject: CAcert Code Development list.
List archive
- From: Wytze van der Raay <wytze AT cacert.org>
- To: Michael Tänzer <michael.taenzer AT cacert.org>
- Cc: "critical-admin AT cacert.org" <critical-admin AT cacert.org>, cacert-devel AT lists.cacert.org, Ulrich Schröter CAcert <ulrich AT cacert.org>, Dirk Astrath <dirk.astrath AT cacert.org>, Marcus Mängel <inopiae AT cacert.org>
- Subject: Re: Patch request: Bug #1024
- Date: Fri, 10 Aug 2012 13:17:18 +0200
- Organization: CAcert
Hi Michael,
Op 9-8-2012 18:51, Michael Tänzer schreef:
> 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
The fix has been installed on the production server on August 10, 2012.
See also the attached log message. Also, the translation server upload
and downloads have been performed as requested.
Regards,
-- wytze
--- Begin Message ---Fix for https://bugs.cacert.org/view.php?id=1024
- From: Wytze van der Raay <wytze AT cacert.org>
- To: cacert-systemlog AT lists.cacert.org
- Subject: Fwd: [cvs.cacert.org checkin notification]
- Date: Fri, 10 Aug 2012 13:14:17 +0200
- Organization: CAcert
Assurer flag is not set correctly on updatesort.php run
In conjunction with the attached CVS changes a new tarball has been
made available incorporating all updates. The new tarball is available
through http://www.cacert.org/src-lic.php
-- end
--- Begin Message ---
- From: "root" <root AT cvs.cacert.org>
- To: critical-admin AT cacert.org
- Subject: cvs.cacert.org checkin notification
- Date: Fri, 10 Aug 2012 13:08:15 +0200 (CEST)
uid=0(root) gid=0(root) groups=0(root)
updatesort.php 1.1 1.2
Fri Aug 10 13:08:14 CEST 2012
Update of /var/lib/cvs/cacert/scripts/cron
In directory hlin:/home/cacert/www/scripts/cron
Modified Files:
updatesort.php
Log Message:
Fix for https://bugs.cacert.org/view.php?id=1024
Assurer flag is not set correctly on updatesort.php run
===================================================================
RCS file: /var/lib/cvs/cacert/scripts/cron/updatesort.php,v
retrieving revision 1.1
retrieving revision 1.2
diff -u -r1.1 -r1.2
--- updatesort.php 2012/03/29 14:24:05 1.1
+++ updatesort.php 2012/08/10 11:08:12 1.2
@@ -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`
--- End Message ------ Begin Message ---
- From: "root" <root AT cvs.cacert.org>
- To: critical-admin AT cacert.org
- Subject: cvs.cacert.org checkin notification
- Date: Fri, 10 Aug 2012 13:08:23 +0200 (CEST)
uid=0(root) gid=0(root) groups=0(root)
account.php 1.2 1.3
Fri Aug 10 13:08:23 CEST 2012
Update of /var/lib/cvs/cacert/includes/lib
In directory hlin:/home/cacert/www/includes/lib
Modified Files:
account.php
Log Message:
Fix for https://bugs.cacert.org/view.php?id=1024
Assurer flag is not set correctly on updatesort.php run
===================================================================
RCS file: /var/lib/cvs/cacert/includes/lib/account.php,v
retrieving revision 1.2
retrieving revision 1.3
diff -u -r1.2 -r1.3
--- account.php 2012/04/29 18:28:38 1.2
+++ account.php 2012/08/10 11:08:22 1.3
@@ -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
--- End Message ---Attachment: smime.p7s
Description: S/MIME-cryptografische ondertekening
--- End Message ---
Attachment:
smime.p7s
Description: S/MIME-cryptografische ondertekening
- 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.