Skip to Content.
Sympa Menu

cacert-devel - Patch request: Bug #1024

Subject: CAcert Code Development list.

List archive

Patch request: Bug #1024


Chronological Thread 
  • 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




Archive powered by MHonArc 2.6.16.

Top of Page