CAcert System Admins discussion list

[Alejandro Mery Pellegrini <amery AT cacert.org>]

Hi, The translation of the magic numbers follow:
238 -> United States
40 -> Canada
147 -> Mexico

May we do it for the whole world? *please*

Alejandro

Evaldo Gardenali wrote:
> Hi
> 
> For the Board:
> Following Greg's idea, I request the Board to empower our Sysadmins to
> produce user and assurer density reports, at least once soon, but
> hopefully quarterly so we can follow our growth. A future idea would be
> to draw density-coloured maps with the help of available mapping websites.
> 
> Motion: To approve preparation of geographic user and assurer density
> reports, suited to the evaluation by the assurance officer of areas that
> need special seeding, halt TTP program to strengthen the web of trust,
> or encourage or disencourage super-assurer status. This report should
> also be published, following our openness guidelines, for users to plan
> where to get assured or where to go help seed the web of trust.
> 
> System Administrators:
> 
> Please advise on the possibility of such reports (one summarized reply
> back to board please). Greg's SQL sample should be treated as an
> example, and proper sanitization or re-engineering should occur if
> appropriate. Developers should get contacted for a more permanent and
> secure solution (for example routinely extraction of report data without
> the need for raw sql access)
> 
> Regards,
> 
> Evaldo Gardenali
> 
> -------- Original Message --------
> Subject:     Data Request
> Date:     Sun, 19 Apr 2009 15:23:54 -0700
> From:     Greg Stark 
> <gstark AT cacert.org>
> To:     Evaldo Gardenali 
> <evaldo AT gardenali.biz>
> 
> 
> 
> Evaldo,
> I am not sure if I should motion the board or proceed to Arbitration.
> I would like to request the following request be processed
> The this query produces results showing CAcert membership density and type
> in an area for North America.
> 
> SELECT `u`.`ccid`, `c`.`name` AS `Country`, `u`.`regid`, `r`.`name` AS
> `Region`, `u`.`locid`, `l`.`name` AS `Location`, SUM(CASE WHEN
> `d`.`tpoints` < 50 THEN 1 ELSE 0 END) AS `Tier1`, SUM(CASE WHEN
> `d`.`tpoints` Between 50 And 99 THEN 1 ELSE 0 END) AS `Tier2`, SUM(CASE
> WHEN `d`.`tpoints` > 99 THEN 1 ELSE 0 END) AS `Assurer` FROM `users` u
> ,`countries` c, `regions` r, `locations` l, (SELECT `notary`.`to` AS `id`,
> sum(`notary`.`points`) AS `tpoints` FROM `notary` GROUP BY `notary`.`to`)
> AS d WHERE (`u`.`ccid`=238 or `u`.`ccid`=40 or `u`.`ccid`=147) and
>       `u`.`id` = `d`.`id` and
>       `u`.`ccid` = `c`.`id` and
>       `u`.`regid` = `r`.`id` and
>       `u`.`locid` = `l`.`id`
> GROUP BY `u`.`ccid`, `c`.`name`, `u`.`regid`, `r`.`name`, `u`.`locid`,
> `l`.`name` ORDER BY `u`.`ccid`;
> 
>

Attachment: smime.p7s
Description: S/MIME Cryptographic Signature