CAcert System Admins discussion list

[Teus Hagen <teus AT theunis.org>]

Why not add to statistics overview of nr assurers per region?

In some way it is already available via search assurer in a circle
around a location (it gives more detail as eg email addresses for those
assurers who allow the publication of their location/email address).

Still the problem is that CAcert has no clue in which country a user
resides, and assurers do not provide any detail of their location either....

So my conclusion is:
any statistics on location does not give exact info.
If it is only about assurers who permitted it by providing their
location this info is available already and does not require any Board
decision. This type of info is usable as it gives info about assurers
who care for doing assurances (which does not say others do not care).

teus



On 04/23/2009 06:59 PM, 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`;
>