[tor-bugs] #2512 [Metrics]: Provide CSV file with the number of running relays per country over time

[Tor Bug Tracker & Wiki]

#2512: Provide CSV file with the number of running relays per country over time
-------------------------+--------------------------------------------------
 Reporter:  karsten      |       Owner:  karsten
     Type:  enhancement  |      Status:  new    
 Priority:  major        |   Milestone:         
Component:  Metrics      |     Version:         
 Keywords:               |      Points:         
   Parent:               |  
-------------------------+--------------------------------------------------
 We have been asked for a CSV file with the number of running relays per
 country over time.  This CSV file would be downloaded once an hour and
 would contain numbers with a resolution of 1 hour.  I assume it's okay to
 limit the CSV file to the last 7 or 14 days, because only the recent
 development will be visualized.  Also, I assume it's okay if the CSV file
 download takes up to 1 minute, which might be necessary to generate it on
 demand.

 The CSV file would most likely have the following format (example data):

 {{{
 datetime,country,relays
 2011-02-08 11:00:00,de,385
 2011-02-08 11:00:00,fr,332
 2011-02-08 11:00:00,it,98
 2011-02-08 11:00:00,us,434
 ...
 2011-02-08 12:00:00,de,392
 2011-02-08 12:00:00,fr,341
 ...
 }}}

 The CSV file would be generated on demand.  The database is updated every
 hour between :15 and :25, so it should be safe to download the CSV file at
 :30 every hour.

 In order to implement this, we'll have to

  - extend the metrics-db database schema to store a GeoIP database,
  - add a function `get_country(address CHARACTER VARYING(15))` to resolve
 an IP address in dotted notation to a two-letter country code,
  - add a view that calculates aggregates for the CSV file, and
  - add a new CSV file type to metrics-web.

 Future work would be to

  - turn the view into a ''materialized'' view and add a graph to the
 metrics website, and
  - update the GeoipDB regularly.

-- 
Ticket URL: <https://trac.torproject.org/projects/tor/ticket/2512>
Tor Bug Tracker & Wiki <https://trac.torproject.org/>
The Tor Project: anonymity online