r-dbi
dbWriteTable options
First thank you for maintaining this important workhorse!
The options overwrite & append do not make sense to me. If we start looking at the append option in write.table and cat, append=T do exactly that and append=F will overwrite the file. In that context you dont need both. The consept append may not make sense in a database (expect for a table without a primary key) since a database do not write to a table in the same way as a OS write to a file. The consept of primary key and unique key are very important in a database and will guarantee you that you don't exisitng 'value' to the table. this conspet is missing in a write-to-file context.
MySQL has (atleast) two methods for handeling duplicate values, namly INSERT IGNORE and REPLACE. I would propose to exchange the append option with an option that dictate which method to use.
Regrds
Codewise (assumnig the new parameter is called ignore.dupl):
sql <- paste0(
ifelse(ignore.dupl, 'INSERT IGNORE', 'REPLACE'),
" INTO ", name, " (", paste0(fields, collapse = ", "), ")\n",
"VALUES (", paste0(params, collapse = ", "), ")"
)
Here is a simple modification to the existing 'dbWriteTable' method that adds ability to REPLACE (note the replace = FALSE argument):
setMethod(
f = "dbWriteTable",
signature = c(conn = "MySQLConnection", name = "character", value = "data.frame"),
definition = function (conn, name, value, ...) {
.local <- function (conn, name, value, field.types = NULL,
row.names = TRUE, overwrite = FALSE, append = FALSE, replace = FALSE,
..., allow.keywords = FALSE)
{
if (overwrite && append)
stop("overwrite and append cannot both be TRUE",
call. = FALSE)
found <- dbExistsTable(conn, name)
if (found && !overwrite && !append) {
stop("Table ", name, " exists in database, and both overwrite and",
" append are FALSE", call. = FALSE)
}
if (found && overwrite) {
dbRemoveTable(conn, name)
}
value <- RMySQL:::explict_rownames(value, row.names)
if (!found || overwrite) {
sql <- mysqlBuildTableDefinition(conn, name, value,
field.types = field.types, row.names = FALSE)
dbGetQuery(conn, sql)
}
if (nrow(value) == 0)
return(TRUE)
fn <- normalizePath(tempfile("rsdbi"), winslash = "/",
mustWork = FALSE)
RMySQL:::safe.write(value, file = fn)
on.exit(unlink(fn), add = TRUE)
sql <- paste0("LOAD DATA LOCAL INFILE ", dbQuoteString(conn,
fn), " ", ifelse(replace == T, "REPLACE", ""), " INTO TABLE ", dbQuoteIdentifier(conn, name),
" FIELDS TERMINATED BY '\t' ", " LINES TERMINATED BY '\n' ",
" (", paste(dbQuoteIdentifier(conn, names(value)),
collapse = ", "), ");")
dbGetQuery(conn, sql)
TRUE
}
.local(conn, name, value, ...)
}
)
