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 within your R session via the command

install.packages("RPostgreSQL")

if necessary.

The following statements connect to a Postgres database on the local host and run a simple query:

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

After the attach statement the DB query results are available under their column names from the SQL query. In the above example you now have two R variables age and weight.

Automated Database Reports

Linux makes it easy to run scripts automatically at scheduled times. Such scripts can call R for analysis and reporting.

The function cat() writes R objects to text files. We can use it to generate reports on statistical 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)
}

Having defined that function now allows us to prepare a simple report:

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 in the working directory 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).

RTF

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). The preamble sets up the font table. Only one font is defined here. We can then switch to Font 0 at size 30

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

Generating reports with R can be somewhat tedious; for simple HTML reports it is probably better to call R from PHP and print the results from the PHP page.

In [ ]: