Provide CSV file with the number of running relays per country over time
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.