More control/customizability over SQL DDL statements in copy_dm_to()

[nbenn]

Column types

In build_copy_queries(), there is get_sql_col_types() which essentially calls DBI::dbDataType(). As such, the user can take control over what SQL is generated by providing classes with corresponding implementations of DBI::dbDataType(). For example:

setOldClass("auto_increment")

auto_increment <- function(seed = 1L, increment = 1L) {
  structure(integer(), seed = seed, increment = increment, class = "auto_increment")
}

setMethod(
  "dbDataType",
  signature("Microsoft SQL Server", "auto_increment"),
  function(dbObj, obj, ...) {
    paste0(
      dbDataType(dbObj, unclass(obj)),
      " IDENTITY(", attr(obj, "seed"), ",", attr(obj, "increment"), ")"
    )
  }
)

(To get this to work fully, an implementation of DBI::dbDataType() for signature("Microsoft SQL Server", "data.frame") is required as well, as dispatch currently on tables and not columns, but this is a technical detail.)

Now we can define a table and get the desired DDL as

table <- tibble::tibble(
  a = auto_increment(),
  b = integer()
)

dm:::build_copy_queries(con, dm(table))
# <SQL> CREATE TEMPORARY TABLE table (
#   "a" INT IDENTITY( 1 , 1 ),
#   "b" INT
# )

Constraints/Indexes

Something along the lines as shown above could also be done for constraints (and indexes). Currently, in terms of constraints afaik, only foreign key constraints can explicitly be defined (with unique constraints being set implicitly). It would be nice if other types of constraints could be defined as well, such as NOT NULL, or in the case of SQL Server, CHECK constraints, alongside explicit UNIQUE constraints. The same holds for indexes. By introducing a generic function, {dm} would not need to know all possible kinds of constraints, indexes, etc. but would only handle the most common ones and if a user wants to do something more exotic, they can always supply their own class with corresponding behavior.

Tables

Finally (and arguably least importantly), we could also dispatch on the entire table to generate the CREATE TABLE statement. This would allow a user to again maybe introduce their own class(es) for things like views etc.

Wrap-Up

Keeping current behavior, while adding this type of flexibility, from a distance at least, does not seem to be a disruptive undertaking. We could introduce our own generic to_sql() (or whatever), which is dispatched on con and the column/constraint/table object(s) and maybe have currently supported constraints/indexes, as well as tables and columns use this new infrastructure.

Further down the line

Having classes such as the above auto_increment, we could also start thinking about how we can replicate certain db behavior on the R side. Additionally, having such information available on the R side might prove helpful in scenarios such as multi-table insertion with foreign key constraints. Also, this might make possible some (at least I think so) interesting functionality, such as defining a view using {dplyr} code, which then can be used both on the db and R sides to compute the derived table. But most importantly: such added functionality could also live outside of {dm} if we feel that it does not represent core functionality. We could test-drive some more experimental ideas in a separate package and maybe later migrate to {dm} -- or not.