R/dbCreateTable.R

In leoluyi/DBIutils:

# library(RSQLServer)
# library(RJDBC)
library(magrittr)

setGeneric("dbCreateTable", valueClass = "logical",
           signature = c("conn", "name", "value"),
           function(conn, name, value, ...) {
             standardGeneric("dbCreateTable")
           }
)

setMethod(
  "dbCreateTable",
  signature("SQLServerConnection"),
  def = function(conn, name, value, key_var = NULL, char_len = 150,
                 overwrite = FALSE, verbose = TRUE) {
    # https://github.com/imanuelcostigan/RSQLServer/blob/master/R/dbi-methods.R#L332
    name <- toupper(as.character(name))
    key_var <- as.character(key_var)

    # if (!length(key_var)) {
    #   stop('Please provide primary key `key_var = "var"`.\n',
    #        paste0(names(value), collapse = ", "),
    #        call. = FALSE)
    # }
    if (! all(key_var %in% names(value))) {
      stop('Cannot find "', key_var, '" in names of value')
    }

    dbWithTransaction(conn, {
      found <- dbExistsTable(conn, name)
      temp <- grepl("^#", name)

      if ((found || temp) && overwrite) {
        dbRemoveTable(conn, name)
      }
      if (found && !overwrite) {
        stop(sprintf("There is already an object named '%s' in the database.", name),
             " Use 'overwrite = TRUE' to force create",
             call. = FALSE)
      }
      if (!found || temp || overwrite) {
        creaet_table_sql <- sqlCreateTable(conn, name, value, row.names = FALSE) %>%
          as.character() %>%
          gsub("(N?VARCHAR)\\(\\d+\\)", sprintf("\\1(%s)", char_len), .,
               perl = TRUE)
        if (length(key_var)) {
          create_pk_str <- sprintf(", CONSTRAINT PK_%s PRIMARY KEY (%s))",
                                   name,
                                   paste0(key_var, collapse = ", "))
          creaet_table_sql <- creaet_table_sql %>%
            sub("[)]\\n?$", create_pk_str, .)
        }
        if (verbose) {cat(SQL(creaet_table_sql))}
      }
      dbExecute(conn, creaet_table_sql)
    })
    TRUE
  }
)


setMethod(
  "dbCreateTable",
  signature("JDBCConnection"),
  def = function(conn, name, value, key_var = NULL, char_len = 150,
                 overwrite = FALSE, verbose = TRUE, ...) {
    # https://github.com/s-u/RJDBC/blob/master/R/class.R#L245
    ac <- .jcall(conn@jc, "Z", "getAutoCommit")
    if (ac) {
      .jcall(conn@jc, "V", "setAutoCommit", FALSE)
      on.exit(.jcall(conn@jc, "V", "setAutoCommit", ac))
    }

    name <- toupper(as.character(name))
    key_var <- as.character(key_var)

    # if (!length(key_var)) {
    #   stop('Please provide primary key `key_var = "var"`.\n',
    #        paste0(names(value), collapse = ", "),
    #        call. = FALSE)
    # }
    if (! (key_var %in% names(value))) {
      stop('Cannot find "', key_var, '" in names of value')
    }

    found <- dbExistsTable(conn, name)
    temp <- grepl("^#", name)


    if (found && !overwrite) {
      stop(sprintf(" There is already an object named '%s' in the database.", name),
           " Use 'overwrite = TRUE' to force create",
           call. = FALSE)
    }
    if ((found || temp) && overwrite) {
      dbRemoveTable(conn, name)
    }
    if (!found || temp || overwrite) {
      creaet_table_sql <- sqlCreateTable(conn, name, value, row.names = FALSE) %>%
        as.character() %>%
        gsub("(N?VARCHAR)\\(\\d+\\)", sprintf("\\1(%s)", char_len), .,
             perl = TRUE)
      if (length(key_var)) {
        create_pk_str <- sprintf(", CONSTRAINT PK_%s PRIMARY KEY (%s))",
                                 name,
                                 paste0(key_var, collapse = ", "))
        creaet_table_sql <- creaet_table_sql %>%
          sub("[)]\\n?$", create_pk_str, .)
      }
      if (verbose) {cat(SQL(creaet_table_sql))}
    }

    dbExecute(conn, creaet_table_sql)
    if (ac) dbCommit(conn)
    TRUE
  }
)