CAcert System Admins discussion list

[Evaldo Gardenali <evaldo AT gardenali.biz>]

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`;