dbWriteTable options

[Gjermund06]

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

[Gjermund06]

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 = ", "), ")"
  )

[ktmud]

Related #68

[krlmlr]

Thanks. Have you installed RMySQL from CRAN or from GitHub?

[mowryand]

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, ...)
  }
)