[R] RPgSQL W2K

[John Fox]

Dear Christian,

At 08:41 AM 2/11/2003 +0100, Christian Schulz wrote:

>i found tis message in the archive  and have
>got the same problems ?
>John, perhaps you have found now a way
>install RPgSQL  for windows2000, or anybody other  ?
>
>many thanks for advance
>christian
>
>
>[SNIP]
>I wonder whether anyone has succeeded in building either the RPgSQL or the
>Rdbi.PGSQL and Rdbi packages for Windows. If so, would you be willing to
>share either the binary package(s) or instructions about how to set up the
>configure scripts? My preliminary efforts to get things going under Windows
>2000 haven't met with much success.

I haven't been able to install RPgSQL for Windows, but I found (following 
Brian Ripley's suggestion) that RODBC works well, both with Postgresql and 
MySQL.

I ended up implementing something similar to the proxy data frames in 
RPgSQL, which I've attached to this message. This is another example of 
some code that seems too small to bundle in a package, so it's not 
documented, but the general idea is to create an odbcDataset object via the 
function of that name, which can then be indexed, summarized, etc. Row 
indices can be SQL queries. For example, assuming that the table usarrests 
is in the MySQL database test:

         channel <- odbcConnect("test", case="mysql")
         USarrests <- odbcDataset(channel, "usarrests")
         USarrests["rape > 20 AND assault < 200 ORDER BY rape", c("rape", 
"assault")]
         lm(murder ~ ., data=USarrests[])

I hope that this is of some use to you.
  John

----------------------- snip -------------------------

.DBMS <- matrix(c("MySQL",      "Column_name", "Type_name",
                   "PostgreSQL", "COLUMN_NAME", "TYPE_NAME"),
             byrow=TRUE, ncol=3)
colnames(.DBMS) <- c("dbname", "column.selector", "type.selector")

odbcDataset <- function(channel, table, rownames){
     if (length(channel) != 1 || !is.integer(channel) || channel < 0) 
stop("channel must be a non-negative integer")
     if (length(table) != 1 || !is.character(table)) stop("table must be a 
data-base table name")
     odbcTableExists(channel, table)
     info <- odbcGetInfo(channel)
     db <- sapply(.DBMS[,"dbname"], function(x) 1 == length(grep(x, info)))
     if (!any(db)) stop(paste("database not located in the .DBMS 
table\ndatabase info:", info))
     if (missing(rownames)){
         col.info <- sqlColumns(channel, table)
         rownames <- 1 == length(grep("char", col.info[1, .DBMS[db, 
"type.selector"]]))
         }
     result <- list(channel=channel, table=table, column.selector=.DBMS[db, 
"column.selector"], rownames=rownames)
     class(result) <- c("odbcDataset", "data.frame")
     result
     }

print.odbcDataset <- function(x, ..., verbose=FALSE){
     if (verbose) print(x[,])
     else {
         x <- unclass(x)  # necessary because of $.odbcDataset
         cat(  "channel:           ", x$channel)
         cat("\ntable:             ", x$table)
         cat("\nrow names:         ", x$rownames)
         class(x) <- c("odbcDataset", "data.frame")
         cat("\nnumber of rows:    ", nrow(x))
         cat("\nnumber of columns: ", ncol(x), "\n")
         invisible(x)
         }
     }

summary.odbcDataset <- function(object, ...) summary(object[,])

"[.odbcDataset" <- function(x, i, j, ...){
     same.sign <- function(x) {
         any(x > 0) == all(x >= 0)
         }
     x <- unclass(x)
     names <- sqlColumns(x$channel, x$table)[[x$column.selector]]
     selection <- if (missing(j)) "*"
                     else if (is.numeric(j)) {
                         j <- j[j != 0]
                         if (length(j) == 0) return(NULL)
                         if (!same.sign(j)) stop("cannot mix positive and 
negative subscripts")
                         if (j[1] > 0) names[j + x$rownames]
                             else names[-1 * c(rep(1, x$rownames), (abs(j) 
+ x$rownames))]
                         }
                         else j
     selection <- if (selection == "*" || !x$rownames) selection
                     else c(names[1], selection)
     if (selection != "*" && (any(is.na(selection)) || 
any(!is.element(selection, names)))) stop("bad column index")
     result <- if (missing(i)) sqlQuery(x$channel, paste("select", 
paste(selection, collapse=","), "from", x$table))
                 else {
                     if (!is.character(i) && length(i) != 1) stop("row 
'subscript' must be an SQL row selector")
                     sqlQuery(x$channel, paste("select", paste(selection, 
collapse=","), "from", x$table, "where", i))
                     }
     if (x$rownames) {
         rownames(result) <- as.character(result[,1])
         result <- result[, -1]
         }
     if (length(dim(result)) == 2 && dim(result)[2] == 1) 
result[,,drop=TRUE] else result
     }

"$.odbcDataset" <- function(x, i){
     x <- unclass(x)
     sqlQuery(x$channel, paste("select", i, "from", x$table))[[1]]
     }

"[[.odbcDataset" <- function(x, i){
     x <- unclass(x)
     if (is.numeric(i)) i <- sqlColumns(x$channel, 
x$table)[[x$column.selector]][i + x$rownames]
     sqlQuery(x$channel, paste("select", i, "from", x$table))[[1]]
     }

as.data.frame.odbcDataset <- function(x, rownames, optional) x[,]

as.matrix.odbcDataset <- function(x) as.matrix(x[,])

as.list.odbcDataset <- function(x, ...) as.list(x[,])

row.names.odbcDataset <- function(x) {
     x <- unclass(x)
     if (x$rownames) {
         name <- sqlColumns(x$channel, x$table)[[x$column.selector]][1]
         as.character(sqlQuery(x$channel, paste("select", name, "from", 
x$table))[[1]])
         }
     else NULL
     }

names.odbcDataset <- function(x){
     x <- unclass(x)
     names <- sqlColumns(x$channel, x$table)[[x$column.selector]]
     if (x$rownames) names[-1] else names
     }

dimnames.odbcDataset <- function(x) list(row.names(x), names(x))

dim.odbcDataset <- function (x) c(length(x[[1]]), length(names(x)))