DBI method for creating tables

dbCreateTable.R

# 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 (! (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("NVARCHAR(%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) {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 (character).")
    }
    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("NVARCHAR(%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) {SQL(creaet_table_sql)}
    }
    
    dbExecute(conn, creaet_table_sql)
    if (ac) dbCommit(conn) 
    TRUE
  }
)

👍

Fabulous!!!