[R-sig-DB] append=TRUE, overwrite=FALSE ignored in dbWriteTable

[Tomoaki NISHIYAMA]

Hi Gabor,

Is there any reason not to support append in RpgSQL and JDBC?
I expect that omitting CREATE TABLE in case there is the preexisting table and
append=TRUE would be sufficient and that will not hurt the performance too much.  
An untested patch to show the concept is below.  
(I don't have the Java setup right now).

$ diff -urp RpgSQL.orig RpgSQL
diff -urp RpgSQL.orig/R/class.R RpgSQL/R/class.R
--- RpgSQL.orig/R/class.R	2011-05-12 10:55:39.000000000 +0900
+++ RpgSQL/R/class.R	2011-10-28 09:56:48.156864736 +0900
@@ -113,7 +113,7 @@ setMethod("dbConnect", "pgSQLDriver", de
   new("pgSQLConnection", jc=jc, identifier.quote=drv using identifier.quote)},
           valueClass="pgSQLConnection")
 
-setMethod("dbWriteTable", "pgSQLConnection", def=function(conn, name, value, overwrite=TRUE, ...) {
+setMethod("dbWriteTable", "pgSQLConnection", def=function(conn, name, value, overwrite=FALSE, append=FALSE, ...) {
   ac <- .jcall(conn using jc, "Z", "getAutoCommit")
   if (is.vector(value) && !is.list(value)) value <- data.frame(x=value)
   if (length(value)<1) stop("value must have at least one column")
@@ -124,21 +124,26 @@ setMethod("dbWriteTable", "pgSQLConnecti
     if (!is.data.frame(value)) value <- as.data.frame(value)
   }
   fts <- sapply(value, dbDataType, dbObj=conn)
-  if (dbExistsTable(conn, name)) {
-    if (overwrite) dbRemoveTable(conn, name)
-    else stop("Table `",name,"' already exists")
-  }
-  fdef <- paste(.sql.qescape(names(value), FALSE, conn using identifier.quote),fts,collapse=',')
-  # cat("conn using identifier.quote:", conn using identifier.quote, "\n")
-  # qname <- .sql.qescape(name, FALSE, conn using identifier.quote)
-  qname <- .sql.qescape(name, TRUE, conn using identifier.quote)
-  ct <- paste("CREATE TABLE ",qname," (",fdef,")",sep= '')
-  # cat("ct:", ct, "\n")
   if (ac) {
     .jcall(conn using jc, "V", "setAutoCommit", FALSE)
     on.exit(.jcall(conn using jc, "V", "setAutoCommit", ac))
   }
-  dbSendUpdate(conn, ct)
+  tablepreexist <-dbExistsTable(conn, name)
+  if (tablepreexist) {
+    if (overwrite) dbRemoveTable(conn, name)
+    else {
+      if(!append) stop("Table `",name,"' already exists")
+    }
+  }
+  if(!tablepreexist || overwrite){
+    fdef <- paste(.sql.qescape(names(value), FALSE, conn using identifier.quote),fts,collapse=',')
+    # cat("conn using identifier.quote:", conn using identifier.quote, "\n")
+    # qname <- .sql.qescape(name, FALSE, conn using identifier.quote)
+    qname <- .sql.qescape(name, TRUE, conn using identifier.quote)
+    ct <- paste("CREATE TABLE ",qname," (",fdef,")",sep= '')
+    # cat("ct:", ct, "\n")
+    dbSendUpdate(conn, ct)
+  }
   if (length(value[[1]])) {
     inss <- paste("INSERT INTO ",qname," VALUES(", paste(rep("?",length(value)),collapse=','),")",sep='')
     for (j in 1:length(value[[1]]))

-- 
Tomoaki NISHIYAMA

Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi, 
Kanazawa, 920-0934, Japan


On 2011/10/28, at 1:52, Gabor Grothendieck wrote:

> On Thu, Oct 27, 2011 at 7:11 AM, Gabor Grothendieck
> <ggrothendieck using gmail.com> wrote:
>> On Thu, Oct 27, 2011 at 6:05 AM, Tomoaki NISHIYAMA
>> <tomoakin using kenroku.kanazawa-u.ac.jp> wrote:
>>> Hi,
>>> 
>>> Ok, back to the mailing list.
>>> 
>>> If I understand correctly, you are using RpgSQL (note this is one of the most important information)
>>> and it appears that RpgSQL does not match to the specification of DBI.
>>> http://cran.r-project.org/web/packages/DBI/index.html
>>> 
>>> The definition in RpgSQL is something like
>>> setMethod("dbWriteTable", "pgSQLConnection", def=function(conn, name, value, overwrite=TRUE, ...) {
>>> and does NOT have append argument.
>>> In addition, the default value of overwrite differs to the DBI spec.
>>> In DBI the spec is
>>>  dbWriteTable(conn, name, value, row.names = T, ...,
>>>              overwrite = F, append = F)
>>> 
>>> This discrepancy is perhaps inherited from RJDBC.
>>> There was a similar report early this year.
>>> https://stat.ethz.ch/pipermail/r-sig-db/2011q1/000996.html
>>> 
>>> So, I Cc:ed this mail to the maintainers of RpgSQL and RJDBC.
>>> 
>>> You can modify the respective source code
>>> RpgSQL/R/class.R
>>> as you like, or use RPostgreSQL.
>>> Using RPostgreSQL on windows is not very easy, though.
>> 
>> 
>> Both RpgSQL and RJDBC do handle overwrite.  Neither support append but
>> insert can be used instead.
>> 
>> library(RpgSQL)
>> library(gsubfn) # fn - quasi-perl style string interpolation
>> 
>> ## assumes appropriate RpgSQL startup options have been set.
>> ## See example in ?pgSQL
>> con <- dbConnect(pgSQL())
>> 
>> ## write BOD to database. BOD comes with R
>> if (dbExistsTable(con, "bod")) dbSendUpdate(con, "drop table bod")
>> dbWriteTable(con, "bod", BOD)
>> 
>> ## insert some new rows.
>> ## Here we don't need to use quotes around the values but
>> ## if the values were not numeric we would need to.
>> NEW <- 2*BOD
>> for(i in 1:nrow(NEW)) fn$dbSendUpdate(con,
>>        "insert into bod values(`NEW[i,1]`, `NEW[i,2]`)")
>> 
>> dbGetQuery(con, "select * from bod")
>> 
>> dbDisconnect(con)
>> 
> 
> Here is a second alternative.  Instead of inserting the new records
> one by one this one writes the new records all at once to a temporary
> table, tmp, and then inserts them en masse into the existing bod table
> and finally drops the temporary table at the end.
> 
> library(RpgSQL)
> 
> con <- dbConnect(pgSQL())
> 
> ## write BOD into bod table
> if (dbExistsTable(con, "bod")) dbSendUpdate(con, "drop table bod")
> dbWriteTable(con, "bod", BOD)
> 
> ## insert NEW into tmp table, insert that into bod and drop tmp
> NEW <- 2*BOD
> if (dbExistsTable(con, "tmp")) dbSendUpdate(con, "drop table tmp")
> dbWriteTable(con, "tmp", NEW)
> dbSendUpdate(con, "insert into bod select * from tmp")
> dbSendUpdate(con, "drop table tmp")
> 
> ## show result
> dbGetQuery(con, "select * from bod")
> 
> dbDisconnect(con)
> 
> 
> -- 
> Statistics & Software Consulting
> GKX Group, GKX Associates Inc.
> tel: 1-877-GKX-GROUP
> email: ggrothendieck at gmail.com
>