commit c40db6ca3f95eabb843b1c65cebe7324430b7271
Author: Karsten Loesing <karsten.loes...@gmx.net>
Date: Thu Apr 21 12:09:00 2011 +0200
Add statistics on relays by country.
---
db/tordir.sql | 37 ++++++++++
etc/web.xml | 4 +
rserve/csv.R | 12 +++
rserve/graphs.R | 59 +++++++++++++++
src/org/torproject/ernie/web/CsvServlet.java | 1 +
.../ernie/web/GraphParameterChecker.java | 2 +
web/WEB-INF/network.jsp | 77 ++++++++++++++++++++
7 files changed, 192 insertions(+), 0 deletions(-)
diff --git a/db/tordir.sql b/db/tordir.sql
index ab0482e..8efba09 100644
--- a/db/tordir.sql
+++ b/db/tordir.sql
@@ -146,6 +146,14 @@ CREATE TABLE network_size_hour (
CONSTRAINT network_size_hour_pkey PRIMARY KEY(validafter)
);
+-- TABLE relay_countries
+CREATE TABLE relay_countries (
+ date DATE NOT NULL,
+ country CHARACTER(2) NOT NULL,
+ relays INTEGER NOT NULL,
+ CONSTRAINT relay_countries_pkey PRIMARY KEY(date, country)
+);
+
-- TABLE relay_platforms
CREATE TABLE relay_platforms (
date DATE NOT NULL,
@@ -429,6 +437,34 @@ CREATE OR REPLACE FUNCTION refresh_network_size_hour()
RETURNS INTEGER AS $$
END;
$$ LANGUAGE plpgsql;
+-- FUNCTION refresh_relay_countries()
+CREATE OR REPLACE FUNCTION refresh_relay_countries() RETURNS INTEGER AS $$
+ BEGIN
+
+ DELETE FROM relay_countries
+ WHERE date IN (SELECT date FROM updates);
+
+ INSERT INTO relay_countries
+ (date, country, relays)
+ SELECT date, country, relays / count AS relays
+ FROM (
+ SELECT DATE(validafter),
+ COALESCE(lower((geoip_lookup(address)).country), 'zz')
+ AS country,
+ COUNT(*) AS relays
+ FROM statusentry
+ WHERE isrunning = TRUE
+ AND DATE(validafter) >= (SELECT MIN(date) FROM updates)
+ AND DATE(validafter) <= (SELECT MAX(date) FROM updates)
+ AND DATE(validafter) IN (SELECT date FROM updates)
+ GROUP BY 1, 2
+ ) b
+ NATURAL JOIN relay_statuses_per_day;
+
+ RETURN 1;
+ END;
+$$ LANGUAGE plpgsql;
+
-- FUNCTION refresh_relay_platforms()
CREATE OR REPLACE FUNCTION refresh_relay_platforms() RETURNS INTEGER AS $$
BEGIN
@@ -782,6 +818,7 @@ CREATE OR REPLACE FUNCTION refresh_all() RETURNS INTEGER AS
$$
PERFORM refresh_relay_statuses_per_day();
PERFORM refresh_network_size();
PERFORM refresh_network_size_hour();
+ PERFORM refresh_relay_countries();
PERFORM refresh_relay_platforms();
PERFORM refresh_relay_versions();
PERFORM refresh_total_bandwidth();
diff --git a/etc/web.xml b/etc/web.xml
index 145472b..a9b36bc 100644
--- a/etc/web.xml
+++ b/etc/web.xml
@@ -173,6 +173,10 @@
</servlet-mapping>
<servlet-mapping>
<servlet-name>GraphImage</servlet-name>
+ <url-pattern>/relaycountries.png</url-pattern>
+ </servlet-mapping>
+ <servlet-mapping>
+ <servlet-name>GraphImage</servlet-name>
<url-pattern>/relayflags.png</url-pattern>
</servlet-mapping>
<servlet-mapping>
diff --git a/rserve/csv.R b/rserve/csv.R
index e68971c..37a0856 100644
--- a/rserve/csv.R
+++ b/rserve/csv.R
@@ -15,6 +15,18 @@ export_networksize <- function(path) {
write.csv(networksize, path, quote = FALSE, row.names = FALSE)
}
+export_relaycountries <- function(path) {
+ drv <- dbDriver("PostgreSQL")
+ con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
+ q <- paste("SELECT date, country, relays FROM relay_countries",
+ "ORDER BY date, country")
+ rs <- dbSendQuery(con, q)
+ relays <- fetch(rs, n = -1)
+ dbDisconnect(con)
+ dbUnloadDriver(drv)
+ write.csv(relays, path, quote = FALSE, row.names = FALSE)
+}
+
export_versions <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
diff --git a/rserve/graphs.R b/rserve/graphs.R
index a37f9bd..3e56cc8 100644
--- a/rserve/graphs.R
+++ b/rserve/graphs.R
@@ -43,6 +43,65 @@ plot_networksize <- function(start, end, path, dpi) {
ggsave(filename = path, width = 8, height = 5, dpi = as.numeric(dpi))
}
+plot_relaycountries <- function(start, end, country, path, dpi) {
+ drv <- dbDriver("PostgreSQL")
+ con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
+ if (country == "all") {
+ q <- paste("SELECT date, avg_running AS relays FROM network_size ",
+ "WHERE date >= '", start, "' AND date <= '", end,
+ "' AND date < (SELECT MAX(date) FROM network_size)", sep = "")
+ } else {
+ q <- paste("SELECT date, relays FROM relay_countries ",
+ "WHERE date >= '", start, "' AND date <= '", end,
+ "' AND date < (SELECT MAX(date) FROM relay_countries) ",
+ "AND country = '", country, "'", sep = "")
+ }
+ rs <- dbSendQuery(con, q)
+ u <- fetch(rs, n = -1)
+ if (length(u$date) == 0)
+ u <- data.frame(date = as.Date(start), relays = 0)
+ dbDisconnect(con)
+ dbUnloadDriver(drv)
+ dates <- seq(from = as.Date(start, "%Y-%m-%d"),
+ to = as.Date(end, "%Y-%m-%d"), by="1 day")
+ missing <- setdiff(dates, u$date)
+ if (length(missing) > 0)
+ u <- rbind(u,
+ data.frame(date = as.Date(missing, origin = "1970-01-01"),
+ relays = NA))
+ peoples <- data.frame(country = c("ae", "au", "bh", "br", "ca", "cn",
+ "cu", "de", "dj", "dz", "eg", "et", "fr", "gb", "il", "ir", "it",
+ "iq", "jo", "jp", "kp", "kr", "kw", "lb", "ly", "ma", "mm", "om",
+ "pl", "ps", "qa", "ru", "sa", "sd", "se", "sy", "tn", "tm", "us",
+ "uz", "vn", "ye"),
+ people = c("U.A.E.", "Australian", "Bahraini", "Brazilian",
+ "Canadian", "Chinese", "Cuban", "German", "Djiboutian", "Algerian",
+ "Egyptian", "Ethiopian", "French", "U.K.", "Israeli", "Iranian",
+ "Italian", "Iraqi", "Jordanian", "Japanese", "North Korean",
+ "South Korean", "Kuwaiti", "Lebanese", "Libyan", "Moroccan",
+ "Burmese", "Omani", "Polish", "Palestinian", "Qatari", "Russian",
+ "Saudi", "Sudanese", "Swedish", "Syrian", "Tunisian", "Turkmen",
+ "U.S.", "Uzbek", "Vietnamese", "Yemeni"),
+ stringsAsFactors = FALSE)
+ title <- ifelse(country == "all",
+ "Number of relays in all countries\n",
+ paste("Number of", peoples[peoples$country == country, "people"],
+ "relays\n"))
+ formatter <- function(x, ...) { format(x, scientific = FALSE, ...) }
+ ggplot(u, aes(x = as.Date(date, "%Y-%m-%d"), y = relays)) +
+ geom_line(size = 1) +
+ scale_x_date(name = paste("\nThe Tor Project - ",
+ "https://metrics.torproject.org/";, sep = ""), format =
+ c("%d-%b", "%d-%b", "%b-%Y", "%b-%Y", "%Y", "%Y")[
+ cut(as.numeric(max(as.Date(u$date, "%Y-%m-%d")) -
+ min(as.Date(u$date, "%Y-%m-%d"))),
+ c(0, 10, 56, 365, 730, 5000, Inf), labels=FALSE)]) +
+ scale_y_continuous(name = "", limits = c(0, max(u$relays,
+ na.rm = TRUE)), formatter = formatter) +
+ opts(title = title)
+ ggsave(filename = path, width = 8, height = 5, dpi = as.numeric(dpi))
+}
+
plot_versions <- function(start, end, path, dpi) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
diff --git a/src/org/torproject/ernie/web/CsvServlet.java
b/src/org/torproject/ernie/web/CsvServlet.java
index c68a2d0..0997f31 100644
--- a/src/org/torproject/ernie/web/CsvServlet.java
+++ b/src/org/torproject/ernie/web/CsvServlet.java
@@ -41,6 +41,7 @@ public class CsvServlet extends HttpServlet {
this.availableCsvFiles.add("networksize");
this.availableCsvFiles.add("platforms");
this.availableCsvFiles.add("relays-monthly-snapshots");
+ this.availableCsvFiles.add("relaycountries");
this.availableCsvFiles.add("relayflags");
this.availableCsvFiles.add("relayflags-hour");
this.availableCsvFiles.add("torperf");
diff --git a/src/org/torproject/ernie/web/GraphParameterChecker.java
b/src/org/torproject/ernie/web/GraphParameterChecker.java
index caeee06..2822cc4 100644
--- a/src/org/torproject/ernie/web/GraphParameterChecker.java
+++ b/src/org/torproject/ernie/web/GraphParameterChecker.java
@@ -40,6 +40,8 @@ public class GraphParameterChecker {
this.availableGraphs = new HashMap<String, String>();
this.availableGraphs.put("networksize", "start,end,filename,dpi");
+ this.availableGraphs.put("relaycountries",
+ "start,end,country,filename,dpi");
this.availableGraphs.put("relayflags", "start,end,flag,granularity,"
+ "filename,dpi");
this.availableGraphs.put("versions", "start,end,filename,dpi");
diff --git a/web/WEB-INF/network.jsp b/web/WEB-INF/network.jsp
index 2f457d9..84d5f1d 100644
--- a/web/WEB-INF/network.jsp
+++ b/web/WEB-INF/network.jsp
@@ -44,6 +44,83 @@ bridges in the network.</p>
<p><a href="csv/networksize.csv">CSV</a> file containing all data.</p>
<br>
+<h3>Relays by country</h3>
+<br>
+<p>The following graph shows the average daily number of relays by
+country.</p>
+<a name="relaycountries"></a>
+<img src="relaycountries.png${relaycountries_url}"
+ width="576" height="360" alt="Relay countries graph">
+<form action="network.html#relaycountries">
+ <div class="formrow">
+ <input type="hidden" name="graph" value="relaycountries">
+ <p>
+ <label>Start date (yyyy-mm-dd):</label>
+ <input type="text" name="start" size="10"
+ value="${relaycountries_start[0]}">
+ <label>End date (yyyy-mm-dd):</label>
+ <input type="text" name="end" size="10"
+ value="${relaycountries_end[0]}">
+ </p><p>
+ Source: <select name="country">
+ <option value="all"<c:if test="${relaycountries_country[0] eq 'all'}">
selected</c:if>>All relays</option>
+ <option value="dz"<c:if test="${relaycountries_country[0] eq 'dz'}">
selected</c:if>>Algeria</option>
+ <option value="au"<c:if test="${relaycountries_country[0] eq 'au'}">
selected</c:if>>Australia</option>
+ <option value="bh"<c:if test="${relaycountries_country[0] eq 'bh'}">
selected</c:if>>Bahrain</option>
+ <option value="br"<c:if test="${relaycountries_country[0] eq 'br'}">
selected</c:if>>Brazil</option>
+ <option value="mm"<c:if test="${relaycountries_country[0] eq 'mm'}">
selected</c:if>>Burma</option>
+ <option value="ca"<c:if test="${relaycountries_country[0] eq 'ca'}">
selected</c:if>>Canada</option>
+ <option value="cn"<c:if test="${relaycountries_country[0] eq 'cn'}">
selected</c:if>>China</option>
+ <option value="cu"<c:if test="${relaycountries_country[0] eq 'cu'}">
selected</c:if>>Cuba</option>
+ <option value="dj"<c:if test="${relaycountries_country[0] eq 'dj'}">
selected</c:if>>Djibouti</option>
+ <option value="eg"<c:if test="${relaycountries_country[0] eq 'eg'}">
selected</c:if>>Egypt</option>
+ <option value="et"<c:if test="${relaycountries_country[0] eq 'et'}">
selected</c:if>>Ethiopia</option>
+ <option value="fr"<c:if test="${relaycountries_country[0] eq 'fr'}">
selected</c:if>>France</option>
+ <option value="de"<c:if test="${relaycountries_country[0] eq 'de'}">
selected</c:if>>Germany</option>
+ <option value="ir"<c:if test="${relaycountries_country[0] eq 'ir'}">
selected</c:if>>Iran</option>
+ <option value="iq"<c:if test="${relaycountries_country[0] eq 'iq'}">
selected</c:if>>Iraq</option>
+ <option value="il"<c:if test="${relaycountries_country[0] eq 'il'}">
selected</c:if>>Israel</option>
+ <option value="it"<c:if test="${relaycountries_country[0] eq 'it'}">
selected</c:if>>Italy</option>
+ <option value="jp"<c:if test="${relaycountries_country[0] eq 'jp'}">
selected</c:if>>Japan</option>
+ <option value="jo"<c:if test="${relaycountries_country[0] eq 'jo'}">
selected</c:if>>Jordan</option>
+ <option value="kw"<c:if test="${relaycountries_country[0] eq 'kw'}">
selected</c:if>>Kuwait</option>
+ <option value="lb"<c:if test="${relaycountries_country[0] eq 'lb'}">
selected</c:if>>Lebanon</option>
+ <option value="ly"<c:if test="${relaycountries_country[0] eq 'ly'}">
selected</c:if>>Libya</option>
+ <option value="ma"<c:if test="${relaycountries_country[0] eq 'ma'}">
selected</c:if>>Morocco</option>
+ <option value="kp"<c:if test="${relaycountries_country[0] eq 'kp'}">
selected</c:if>>North Korea</option>
+ <option value="om"<c:if test="${relaycountries_country[0] eq 'om'}">
selected</c:if>>Oman</option>
+ <option value="ps"<c:if test="${relaycountries_country[0] eq 'ps'}">
selected</c:if>>Palestinian territories</option>
+ <option value="pl"<c:if test="${relaycountries_country[0] eq 'pl'}">
selected</c:if>>Poland</option>
+ <option value="qa"<c:if test="${relaycountries_country[0] eq 'qa'}">
selected</c:if>>Qatar</option>
+ <option value="ru"<c:if test="${relaycountries_country[0] eq 'ru'}">
selected</c:if>>Russia</option>
+ <option value="sa"<c:if test="${relaycountries_country[0] eq 'sa'}">
selected</c:if>>Saudi Arabia</option>
+ <option value="kr"<c:if test="${relaycountries_country[0] eq 'kr'}">
selected</c:if>>South Korea</option>
+ <option value="sd"<c:if test="${relaycountries_country[0] eq 'sd'}">
selected</c:if>>Sudan</option>
+ <option value="se"<c:if test="${relaycountries_country[0] eq 'se'}">
selected</c:if>>Sweden</option>
+ <option value="sy"<c:if test="${relaycountries_country[0] eq 'sy'}">
selected</c:if>>Syria</option>
+ <option value="tn"<c:if test="${relaycountries_country[0] eq 'tn'}">
selected</c:if>>Tunisia</option>
+ <option value="tm"<c:if test="${relaycountries_country[0] eq 'tm'}">
selected</c:if>>Turkmenistan</option>
+ <option value="ae"<c:if test="${relaycountries_country[0] eq 'ae'}">
selected</c:if>>U.A.E.</option>
+ <option value="gb"<c:if test="${relaycountries_country[0] eq 'gb'}">
selected</c:if>>U.K.</option>
+ <option value="us"<c:if test="${relaycountries_country[0] eq 'us'}">
selected</c:if>>U.S.A.</option>
+ <option value="uz"<c:if test="${relaycountries_country[0] eq 'uz'}">
selected</c:if>>Uzbekistan</option>
+ <option value="vn"<c:if test="${relaycountries_country[0] eq 'vn'}">
selected</c:if>>Vietnam</option>
+ <option value="ye"<c:if test="${relaycountries_country[0] eq 'ye'}">
selected</c:if>>Yemen</option>
+ </select>
+ </p><p>
+ Resolution: <select name="dpi">
+ <option value="72"<c:if test="${relaycountries_dpi[0] eq '72'}">
selected</c:if>>Screen - 576x360</option>
+ <option value="150"<c:if test="${relaycountries_dpi[0] eq '150'}">
selected</c:if>>Print low - 1200x750</option>
+ <option value="300"<c:if test="${relaycountries_dpi[0] eq '300'}">
selected</c:if>>Print high - 2400x1500</option>
+ </select>
+ </p><p>
+ <input class="submit" type="submit" value="Update graph">
+ </p>
+ </div>
+</form>
+<p><a href="csv/relaycountries.csv">CSV</a> file containing all data.</p>
+<br>
+
<h3>Relays with Exit, Fast, Guard, and Stable flags</h3>
<br>
<p>The directory authorities assign certain flags to relays that clients
_______________________________________________
tor-commits mailing list
tor-commits@lists.torproject.org
https://lists.torproject.org/cgi-bin/mailman/listinfo/tor-commits
