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 at 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 at 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 at identifier.quote),fts,collapse=',')
- # cat("conn at identifier.quote:", conn at identifier.quote, "\n")
- # qname <- .sql.qescape(name, FALSE, conn at identifier.quote)
- qname <- .sql.qescape(name, TRUE, conn at identifier.quote)
- ct <- paste("CREATE TABLE ",qname," (",fdef,")",sep= '')
- # cat("ct:", ct, "\n")
if (ac) {
.jcall(conn at jc, "V", "setAutoCommit", FALSE)
on.exit(.jcall(conn at 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 at identifier.quote),fts,collapse=',')
+ # cat("conn at identifier.quote:", conn at identifier.quote, "\n")
+ # qname <- .sql.qescape(name, FALSE, conn at identifier.quote)
+ qname <- .sql.qescape(name, TRUE, conn at 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 at gmail.com> wrote:
>> On Thu, Oct 27, 2011 at 6:05 AM, Tomoaki NISHIYAMA
>> <tomoakin at 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
>