commit c92d4c698b8990c78a58ae02642fc7e55b236648 Author: Karsten Loesing <karsten.loesing@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)