escaping-proposal.r

DBIObject <- setClass("DBIObject", contains = "VIRTUAL")
DBIConnection <- setClass("DBIConnection", contains = c("DBIObject", "VIRTUAL"))
MockConnection <- setClass("MockConnection", contains = "DBIConnection")

SQL <- setClass("SQL", contains = "character")
setMethod("show", "SQL", function(object) {
  cat(paste0("<SQL> ", object@.Data, collapse = "\n"))
})
sql <- function(x) SQL(x)

setGeneric("dbQuoteIdentifier", function(conn, x, ...) {
  standardGeneric("dbQuoteIdentifier")
})
setMethod("dbQuoteIdentifier", c("DBIConnection", "character"), 
  function(conn, x, ...) {
    x <- gsub('"', '""', x, fixed = TRUE)
    sql(paste('"', x, '"', sep = ""))
  }
)
setMethod("dbQuoteIdentifier", c("DBIConnection", "SQL"), 
  function(conn, x, ...) {
    x
  }
)

setGeneric("dbQuoteString", function(conn, x, ...) {
  standardGeneric("dbQuoteString")
})
setMethod("dbQuoteString", c("DBIConnection", "character"), 
  function(conn, x, ...) {
    x <- gsub("'", "''", x, fixed = TRUE)
    sql(paste("'", x, "'", sep = ""))
  }
)
setMethod("dbQuoteString", c("DBIConnection", "SQL"), 
  function(conn, x, ...) {
    x
  }
)


x <- c("select", "a", "'")
dbQuoteIdentifier(MockConnection(), x)
# <SQL> "select"
# <SQL> "a"
# <SQL> "'"
dbQuoteIdentifier(MockConnection(), sql(x))
# <SQL> select
# <SQL> a
# <SQL> '

dbQuoteString(MockConnection(), x)
# <SQL> 'select'
# <SQL> 'a'
# <SQL> ''''
dbQuoteString(MockConnection(), sql(x))
# <SQL> select
# <SQL> a
# <SQL> '


# But note that subclasses need to implement both a character and SQL
# method, otherwise dispatch is ambiguous

setMethod("dbQuoteString", c("MockConnection", "character"), 
  function(conn, x, ...) {
    # Does this cause problems?
    paste0("--", x, "--")
  }
)
dbQuoteString(MockConnection(), x)
dbQuoteString(MockConnection(), sql(x))


tableName <- function(conn, x) {
  if (!is(x, "SQL")) {
    x <- dbQuoteIdentifier(conn, x)
  }
  SQL(paste0(x, collapse = "."))
}

mc <- MockConnection()
tableName(mc, "mytable")
tableName(mc, SQL("mytable"))
tableName(mc, c("myschema", "mytable"))
tableName(mc, SQL(c("myschema", "mytable")))
tableName(mc, "myschema.mytable")
tableName(mc, SQL("mytable.mytable"))