1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
| #!/usr/bin/Rscript
#install.packages('RMySQL')
#install.packages('ggplot2')
library(RMySQL)
library(ggplot2)
library(grid)
# PNG layout of plots helper.
vplayout <- function(x, y) viewport(layout.pos.row=x, layout.pos.col=y)
# Connect to DB.
dbUsername <- 'some_user'
dbPassword <- 'some_password'
dbName <- 'app_stats_snapshot'
dbHost <- 'localhost'
dbConnection <- dbConnect(MySQL(), user=dbUsername, password=dbPassword,
dbname=dbName, host=dbHost)
# Exemplary queries.
recordsPerDayQuery <-
'select date(created_at) as Day, count(*) as Count from records where created_at >= "2012-1-1" and date(created_at) < date(now()) group by Day'
otherRecordsPerDayQuery <-
'select date(created_at) as Day, count(*) as Count from other_records where created_at >= "2012-1-1" and date(created_at) < date(now()) group by Day'
# ...
# Query data and plot charts.
d1 <- dbGetQuery(dbConnection, recordsPerDayQuery)
xlabText <- '2012'; ylabText <- 'Count'
Records <- d1$Count
heading1 <- 'Records per Day'
plot1 <- qplot(as.Date(d1$Day), d1$Count, geom='line', color=Records,
main=heading1, xlab=xlabText, ylab=ylabText)
d2 <- dbGetQuery(dbConnection, otherRecordsPerDayQuery)
Other <- d2$Count
heading2 <- 'Other Records per Day'
plot2 <- qplot(as.Date(d2$Day), d2$Count, geom='line', color=Other,
main=heading2, xlab='2012', ylab='Count')
# ...
# Write data tables to mail text file.
mailTextFilename <- '/path/to/stats-mail.txt'
cat(paste('Hi,\n\n#', heading1, '\n'), file=mailTextFilename)
write.table(d1, file=mailTextFilename, append=TRUE,
row.names=FALSE, quote=FALSE, sep=' | ')
cat(paste('\n#', heading2, '\n'), file=mailTextFilename, append=TRUE)
write.table(d2, file=mailTextFilename, append=TRUE,
row.names=FALSE, quote=FALSE, sep=' | ')
# ...
# Make PNG with plotted charts.
png('/path/to/stats.png', width=1024, height=768)
grid.newpage()
pushViewport(viewport(layout=grid.layout(2, 1)))
print(plot1, vp=vplayout(1, 1))
print(plot2, vp=vplayout(2, 1))
# ...
dev.off()
|