commit c92d4c698b8990c78a58ae02642fc7e55b236648
Author: Karsten Loesing <karsten.loesing(a)gmx.net>
Date: Tue Jul 31 14:00:11 2012 +0200
Cut off last 1--2 days from graphs/CSVs consistently.
---
rserve/csv.R | 55 +++++++++++++++++++++++++++++--------------------------
rserve/graphs.R | 44 +++++++++++++++++++++-----------------------
rserve/tables.R | 6 ++----
3 files changed, 52 insertions(+), 53 deletions(-)
diff --git a/rserve/csv.R b/rserve/csv.R
index 6d76bf7..8eb69fe 100644
--- a/rserve/csv.R
+++ b/rserve/csv.R
@@ -1,10 +1,12 @@
export_networksize <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
- q <- "SELECT date, avg_running AS relays FROM network_size"
+ q <- paste("SELECT date, avg_running AS relays FROM network_size",
+ "WHERE date < current_date - 1")
rs <- dbSendQuery(con, q)
relays <- fetch(rs, n = -1)
- q <- "SELECT date, avg_running AS bridges FROM bridge_network_size"
+ q <- paste("SELECT date, avg_running AS bridges",
+ "FROM bridge_network_size WHERE date < current_date - 1")
rs <- dbSendQuery(con, q)
bridges <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -19,7 +21,8 @@ export_cloudbridges <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, avg_running_ec2 AS cloudbridges",
- "FROM bridge_network_size ORDER BY date")
+ "FROM bridge_network_size WHERE date < current_date - 1",
+ "ORDER BY date")
rs <- dbSendQuery(con, q)
cloudbridges <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -31,7 +34,7 @@ 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")
+ "WHERE date < current_date - 1 ORDER BY date, country")
rs <- dbSendQuery(con, q)
relays <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -42,7 +45,8 @@ export_relaycountries <- function(path) {
export_versions <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
- q <- "SELECT date, version, relays FROM relay_versions"
+ q <- paste("SELECT date, version, relays FROM relay_versions",
+ "WHERE date < current_date - 1")
rs <- dbSendQuery(con, q)
versions <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -57,7 +61,7 @@ export_platforms <- function(path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, avg_linux AS linux, avg_darwin AS darwin,",
"avg_bsd AS bsd, avg_windows AS windows, avg_other AS other",
- "FROM relay_platforms ORDER BY date")
+ "FROM relay_platforms WHERE date < current_date - 1 ORDER BY date")
rs <- dbSendQuery(con, q)
platforms <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -68,11 +72,12 @@ export_platforms <- function(path) {
export_bandwidth <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
- q <- "SELECT date, bwadvertised FROM total_bandwidth"
+ q <- paste("SELECT date, bwadvertised FROM total_bandwidth",
+ "WHERE date < current_date - 1")
rs <- dbSendQuery(con, q)
bw_desc <- fetch(rs, n = -1)
q <- paste("SELECT date, read, written FROM total_bwhist",
- "WHERE date < (SELECT MAX(date) FROM total_bwhist) - 1")
+ "WHERE date < current_date - 1")
rs <- dbSendQuery(con, q)
bw_hist <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -90,8 +95,7 @@ export_bwhist_flags <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, isexit, isguard, read, written",
- "FROM bwhist_flags",
- "WHERE date < (SELECT MAX(date) FROM bwhist_flags) - 1",
+ "FROM bwhist_flags WHERE date < current_date - 1",
"ORDER BY date, isexit, isguard")
rs <- dbSendQuery(con, q)
bw <- fetch(rs, n = -1)
@@ -105,7 +109,7 @@ export_dirbytes <- function(path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, dr, dw, brp, bwp, brd, bwd FROM user_stats",
"WHERE country = 'zy' AND bwp / bwd <= 3",
- "AND date < (SELECT MAX(date) FROM user_stats) - 1 ORDER BY date")
+ "AND date < current_date - 1 ORDER BY date")
rs <- dbSendQuery(con, q)
dir <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -122,7 +126,7 @@ export_relayflags <- function(path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, avg_running AS running, avg_exit AS exit,",
"avg_guard AS guard, avg_fast AS fast, avg_stable AS stable",
- "FROM network_size ORDER BY date")
+ "FROM network_size WHERE date < current_date - 1 ORDER BY date")
rs <- dbSendQuery(con, q)
relayflags <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -135,7 +139,8 @@ export_relayflags_hour <- function(path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT validafter, avg_running AS running,",
"avg_exit AS exit, avg_guard AS guard, avg_fast AS fast,",
- "avg_stable AS stable FROM network_size_hour ORDER BY validafter")
+ "avg_stable AS stable FROM network_size_hour",
+ "WHERE DATE(validafter) < current_date - 1 ORDER BY validafter")
rs <- dbSendQuery(con, q)
relayflags <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -147,8 +152,7 @@ export_direct_users <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, country, r, bwp, brn, bwn, brp, bwr, brr",
- "FROM user_stats",
- "WHERE date < (SELECT MAX(date) FROM user_stats) - 1",
+ "FROM user_stats WHERE date < current_date - 1",
"ORDER BY date, country")
rs <- dbSendQuery(con, q)
u <- fetch(rs, n = -1)
@@ -166,8 +170,7 @@ export_bridge_users <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, country, users AS bridgeusers",
- "FROM bridge_stats",
- "WHERE date < (SELECT MAX(date) FROM bridge_stats)",
+ "FROM bridge_stats WHERE date < current_date - 1",
"ORDER BY date, country")
rs <- dbSendQuery(con, q)
bridgeusers <- fetch(rs, n = -1)
@@ -181,7 +184,8 @@ export_bridge_users <- function(path) {
export_gettor <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
- q <- "SELECT date, bundle, downloads FROM gettor_stats"
+ q <- paste("SELECT date, bundle, downloads FROM gettor_stats",
+ "WHERE date < current_date - 1")
rs <- dbSendQuery(con, q)
downloads <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -216,7 +220,7 @@ export_torperf <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT source, date, q1, md, q3 FROM torperf_stats",
- "ORDER BY source, date")
+ "WHERE date < current_date - 1 ORDER BY source, date")
rs <- dbSendQuery(con, q)
torperf <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -228,7 +232,8 @@ export_torperf_failures <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT source, date, timeouts, failures, requests",
- "FROM torperf_stats ORDER BY source, date")
+ "FROM torperf_stats WHERE date < current_date - 1",
+ "ORDER BY source, date")
rs <- dbSendQuery(con, q)
torperf <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -240,8 +245,7 @@ help_export_monthly_users <- function(path, aggr_fun) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, country, r, bwp, brn, bwn, brp, bwr, brr",
- "FROM user_stats",
- "WHERE date < (SELECT MAX(date) FROM user_stats) - 1",
+ "FROM user_stats WHERE date < current_date - 1",
"ORDER BY date, country")
rs <- dbSendQuery(con, q)
u <- fetch(rs, n = -1)
@@ -249,8 +253,7 @@ help_export_monthly_users <- function(path, aggr_fun) {
users = u$r * (u$bwp * u$brn / u$bwn - u$brp) /
(u$bwr * u$brn / u$bwn - u$brr) / 10)
q <- paste("SELECT date, country, FLOOR(users) AS users",
- "FROM bridge_stats",
- "WHERE date < (SELECT MAX(date) FROM bridge_stats)",
+ "FROM bridge_stats WHERE date < current_date - 1",
"ORDER BY date, country")
rs <- dbSendQuery(con, q)
bridge <- fetch(rs, n = -1)
@@ -280,6 +283,7 @@ export_connbidirect <- function(path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT DATE(statsend) AS date, source, belownum AS below,",
"readnum AS read, writenum AS write, bothnum AS \"both\"",
+ "WHERE DATE(statsend) < current_date - 1",
"FROM connbidirect ORDER BY 1, 2")
rs <- dbSendQuery(con, q)
c <- fetch(rs, n = -1)
@@ -293,8 +297,7 @@ export_dirreq_stats <- function(path) {
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, r, bwp, brp, bwn, brn, bwr, brr ",
- "FROM user_stats ",
- "WHERE date < (SELECT MAX(date) FROM user_stats) - 1 ",
+ "FROM user_stats WHERE date < current_date - 1",
"AND country = 'zy' ORDER BY date", sep = "")
rs <- dbSendQuery(con, q)
u <- fetch(rs, n = -1)
diff --git a/rserve/graphs.R b/rserve/graphs.R
index 09563a3..fac88a7 100644
--- a/rserve/graphs.R
+++ b/rserve/graphs.R
@@ -282,12 +282,12 @@ plot_networksize <- function(start, end, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
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 = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
relays <- fetch(rs, n = -1)
q <- paste("SELECT date, avg_running AS bridges ",
"FROM bridge_network_size WHERE date >= '", start,
- "' AND date <= '", end, "'", sep = "")
+ "' AND date <= '", end, "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
bridges <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -329,7 +329,7 @@ plot_cloudbridges <- function(start, end, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, avg_running_ec2 ",
"FROM bridge_network_size WHERE date >= '", start,
- "' AND date <= '", end, "'", sep = "")
+ "' AND date <= '", end, "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
bridges <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -363,11 +363,11 @@ plot_relaycountries <- function(start, end, country, path, dpi) {
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 = "")
+ "' AND date < current_date - 1", 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 date < current_date - 1 ",
"AND country = '", country, "'", sep = "")
}
rs <- dbSendQuery(con, q)
@@ -407,7 +407,7 @@ plot_versions <- function(start, end, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, version, relays FROM relay_versions ",
"WHERE date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM relay_versions)", sep = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
versions <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -443,8 +443,7 @@ plot_platforms <- function(start, end, path, dpi) {
con <- dbConnect(drv, user=dbuser, password=dbpassword, dbname=db)
q <- paste("SELECT date, avg_linux, avg_darwin, avg_bsd, avg_windows, ",
"avg_other FROM relay_platforms WHERE date >= '", start,
- "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM relay_platforms)", sep = "")
+ "' AND date <= '", end, "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
platforms <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -476,12 +475,12 @@ plot_bandwidth <- function(start, end, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, bwadvertised FROM total_bandwidth ",
"WHERE date >= '", start, "' AND date <= '", end, "' ",
- "AND date < (SELECT MAX(date) FROM total_bandwidth) - 1 ", sep = "")
+ "AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
bw_desc <- fetch(rs, n = -1)
q <- paste("SELECT date, read, written FROM total_bwhist ",
"WHERE date >= '", start, "' AND date <= '", end, "' ",
- "AND date < (SELECT MAX(date) FROM total_bwhist) - 1 ", sep = "")
+ "AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
bw_hist <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -514,7 +513,7 @@ plot_bwhist_flags <- function(start, end, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, isexit, isguard, read, written ",
"FROM bwhist_flags WHERE date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM bwhist_flags) - 1 ", sep = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
bw <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -560,9 +559,8 @@ plot_dirbytes <- function(start, end, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, dr, dw, brp, bwp, brd, bwd FROM user_stats ",
"WHERE country = 'zy' AND bwp / bwd <= 3 AND date >= '", start,
- "' AND date <= '", end, "' ",
- "AND date < (SELECT MAX(date) FROM user_stats) - 1 ORDER BY date",
- sep = "")
+ "' AND date <= '", end, "' AND date < current_date - 1 ",
+ "ORDER BY date", sep = "")
rs <- dbSendQuery(con, q)
dir <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -599,7 +597,7 @@ plot_relayflags <- function(start, end, flags, granularity, path, dpi) {
columns <- paste("avg_", tolower(flags), sep = "", collapse = ", ")
q <- paste("SELECT date, ", columns, " FROM network_size ",
"WHERE date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM network_size)", sep = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
networksize <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -639,7 +637,8 @@ plot_relayflags <- function(start, end, flags, granularity, path, dpi) {
columns <- paste("avg_", tolower(flags), sep = "", collapse = ", ")
q <- paste("SELECT validafter, ", columns, " FROM network_size_hour ",
"WHERE DATE(validafter) >= '", start,
- "' AND DATE(validafter) <= '", end, "'", sep = "")
+ "' AND DATE(validafter) <= '", end, "' ",
+ "AND DATE(validafter) < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
networksize <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -672,7 +671,7 @@ plot_direct_users <- function(start, end, country, events, path, nocutoff,
q <- paste("SELECT date, r, bwp, brn, bwn, brp, bwr, brr, country ",
"FROM user_stats WHERE date >= '", start, "' AND date <= '", end,
"' ", ifelse(nocutoff == "off",
- " AND date < (SELECT MAX(date) FROM user_stats) - 1 ", ""),
+ " AND date < current_date - 1 ", ""),
" AND (country = 'zy'", ifelse(country == "all", "",
paste(" OR country = '", country, "'", sep = "")), ")", sep = "")
rs <- dbSendQuery(con, q)
@@ -748,7 +747,7 @@ plot_bridge_users <- function(start, end, country, path, dpi) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, users FROM bridge_stats ",
"WHERE date >= '", start, "' AND date <= '", end, "' ",
- "AND date < (SELECT MAX(date) FROM bridge_stats) - 1",
+ "AND date < current_date - 1",
" AND country = '", ifelse(country == "all", "zy", country), "'",
sep = "")
rs <- dbSendQuery(con, q)
@@ -790,8 +789,7 @@ plot_gettor <- function(start, end, language, path, dpi) {
q <- paste("SELECT date, SUM(downloads) AS downloads ",
"FROM gettor_stats WHERE bundle ", condition, " AND date >= '",
start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM gettor_stats) GROUP BY date",
- sep = "")
+ "' AND date < current_date - 1 GROUP BY date", sep = "")
rs <- dbSendQuery(con, q)
downloads <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -828,7 +826,7 @@ plot_torperf <- function(start, end, source, filesize, path, dpi) {
q <- paste("SELECT date, q1, md, q3 FROM torperf_stats ",
"WHERE source = '", paste(source, filesize, sep = "-"),
"' AND date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM torperf_stats)", sep = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
torperf <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -879,7 +877,7 @@ plot_torperf_failures <- function(start, end, source, filesize, path,
"FROM torperf_stats WHERE source = '",
paste(source, filesize, sep = "-"),
"' AND date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM torperf_stats)", sep = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
torperf <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -932,7 +930,7 @@ plot_connbidirect <- function(start, end, path, dpi) {
q <- paste("SELECT DATE(statsend) AS date, readnum, writenum, bothnum ",
"FROM connbidirect WHERE DATE(statsend) >= '", start,
"' AND DATE(statsend) <= '", end, "' AND DATE(statsend) < ",
- "(SELECT MAX(DATE(statsend)) FROM connbidirect)", sep = "")
+ "current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
c <- fetch(rs, n = -1)
dbDisconnect(con)
diff --git a/rserve/tables.R b/rserve/tables.R
index e2e270d..cb74379 100644
--- a/rserve/tables.R
+++ b/rserve/tables.R
@@ -7,8 +7,7 @@ write_direct_users <- function(start, end, path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, country, r, bwp, brn, bwn, brp, bwr, brr ",
"FROM user_stats WHERE date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM user_stats) - 1 ",
- "ORDER BY date, country", sep = "")
+ "' AND date < current_date - 1 ORDER BY date, country", sep = "")
rs <- dbSendQuery(con, q)
u <- fetch(rs, n = -1)
dbDisconnect(con)
@@ -35,8 +34,7 @@ write_censorship_events <- function(start, end, path) {
con <- dbConnect(drv, user = dbuser, password = dbpassword, dbname = db)
q <- paste("SELECT date, country, r, bwp, brn, bwn, brp, bwr, brr ",
"FROM user_stats WHERE date >= '", start, "' AND date <= '", end,
- "' AND date < (SELECT MAX(date) FROM user_stats) - 1",
- sep = "")
+ "' AND date < current_date - 1", sep = "")
rs <- dbSendQuery(con, q)
u <- fetch(rs, n = -1)
dbDisconnect(con)
