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

[Gabor Grothendieck]

On Thu, Oct 27, 2011 at 9:07 PM, Tomoaki NISHIYAMA
<tomoakin using kenroku.kanazawa-u.ac.jp> wrote:
> 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]]))
>

The main reasons have been that (1) RpgSQL was developed for sqldf and
that feature would rarely be needed by sqldf users, (2) RpgSQL is JDBC
based and so is based on RJDBC and it seemed reasonable to follow
RJDBC in terms of feature set particularly if it means that RpgSQL can
leverage code in RJDBC once its implemented there (note that that is
particularly important because RpgSQL is not my only JDBC-basd driver
-- there is also RH2 and any code that can put in RJDBC potentially
removes it from two other packages making everything simpler), (3)
lastly its relatively easy for the user to accomplish the same thing
in other ways.  My preference would be for the RJDBC author to
implement it first and then to leverage it if possible.

-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com