DB Connections and Reports

The R library RPostgreSQL allows for easy communication with the Postresql database.

(This package does not come with the standard R installation and will need to be installed on your own R system; you can do this with the command install.packages("RPostgreSQL") if necessary.)

Using the attach() Command for Query Results

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, user="...", password="...", dbname="...", host="localhost")
res <- dbGetQuery(con, "select count(*) as cnt from ...")
attach(res)

The attach() command after the DB query makes the results available under their column names.

⇒ It is recommended to rename aggregate columns with the SQL option AS.

Real data is often incomplete or otherwise problematic. If necessary restrict the result to meaningful values, e.g. by using the following SQL options in the WHERE or HAVING clause:

• where val is not null
  to exclude records with NULLs
• where val in (x, y)
  to allow only the listed values

Generating Reports

Database contents change over time; therefore, automized analysis and reporting are a must. Linux makes it easy to run scripts automatically at scheduled times. Such scripts can call R for analysis and reporting.

⇒ Reporting can be done with R, but it tends to be somewhat tedious; for simple HTML reports it is probably better to call R from PHP and print the results from the PHP page.

The function cat() writes R objects to text files. We can use it to generate reports on test results in various formats.

In the following example we use a defined function mcat() to make the code easier to read:

fn <- "result.html"
mcat <- function(s) {
  cat(s, file=fn, append=T)
}

ms <- mean(cnt)
mt <- 3
p <- t.test(cnt, mu=mt)$p.value
sl <- 0.05

cat("<h2>Test Result</h2>\n", file=fn)
mcat("<p> Data: ")
mcat(cnt)
mcat("\n<p> Sample mean: ")
mcat(ms)
mcat("\n<p> T-Test for mu = ")
mcat(mt)
mcat("\n<p> p-value: ")
mcat(p)
mcat("\n<p> The sample mean is ")
if (p > sl) {
  mcat(" not ")
}
mcat(" significantly different from ")
mcat(mt)
mcat(" at the chosen significance level of ")
mcat(sl)
mcat(".\n")

The HTML file result.html is produced when we run this code. Note that the names of the test result values can be learned from the online manual in R, by calling help(t.test).

The Rich Text Format is widely used for interfacing different applications working with text documents. RTF is a markup language, similar to HTML. Here is a very small RTF document:

{\rtf {\fonttbl {\f0 Times New Roman;}}
\f0\fs30 Hello World! } 

Opening and closing braces must match. Line breaks are not relevant for the result (just like in HTML).

• {\rtf {\fonttbl {\f0 Times New Roman;}}
  This is the preamble setting up the font table. Only one font is defined here.
• \f0\fs30
  Switch to Font 0 at size 30
• }
  Close the beginning brace.

Some other RTF elements:

• \par
  new paragraph, starting on a new line.
• \par\par
  Effectively, an empty line.
• {\f2 Courier;}
  Define fixed-width Courier font inside \fonttbl
• \i switch to italics \i0 back to normal
• \b bold \b0 normal
• \trowd ... \row
  A table row
• \cellx3000
  cell width of about 2 inches
• \cell
  End of cell
• \pard\plain
  Back to normal (non-table) mode

Write some R code to generate the above t-test report in RTF!