SHARE
TWEET

Untitled

a guest Aug 18th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #' @include DBConnection.R
  2. NULL
  3.  
  4. #' Generate SQL: CREATE TABLE
  5. #'
  6. #' @param conn A subclass of \code{\linkS4class{DBIConnection}} describing
  7. #'   an active database connection.
  8. #' @param name A string giving the table name to create. Escaped with
  9. #'   \code{\link{dbQuoteIdentifier}}
  10. #' @param fields,types Character vectors of equal lengths giving the names
  11. #'   and types of the table's columns. Fields is escaped with
  12. #'   \code{\link{dbQuoteIdentifier}}. Types is not escaped.
  13. #' @param ... Other arguments passed on to methods. Not otherwise used.
  14. #' @aliases sqlCreateTable,DBIConnection-method
  15. #' @export
  16. #' @examples
  17. #' conn <- setClass("Mock", "DBIConnection")()
  18. #'
  19. #' sqlCreateTable(conn, "mtcars", names(mtcars), dbDataType(conn, mtcars))
  20. setGeneric("sqlCreateTable", function(conn, name, fields, types, ...) {
  21.   standardGeneric("sqlCreateTable")
  22. }, signature = "conn", valueClass = "SQL")
  23.  
  24. setMethod("sqlCreateTable", "DBIConnection",
  25.   function(conn, name, fields, types, ...) {
  26.     stopifnot(length(fields) == length(types))
  27.    
  28.     SQL(paste0(
  29.       "CREATE TABLE ", dbQuoteIdentifier(conn, name), " (",
  30.       paste0(dbQuoteIdentifier(conn, fields), " ", types, collapse = ", "),
  31.       ")"
  32.     ))
  33.   }
  34. )
  35.  
  36. #' Generate SQL: DELETE FROM
  37. #'
  38. #' @inheritParams sqlCreateTable
  39. #' @param name A string giving the table name to delete from. Escaped with
  40. #'   \code{\link{dbQuoteIdentifier}}
  41. #' @param where An optional string providing an SQL expression used to restrict
  42. #'   the set of rows to be deleted. Not escaped.
  43. #' @aliases sqlDeleteFrom,DBIConnection-method
  44. #' @export
  45. #' @examples
  46. #' conn <- setClass("Mock", "DBIConnection")()
  47. #'
  48. #' sqlDeleteFrom(conn, "mtcars")
  49. #' sqlDeleteFrom(conn, "mtcars", "cyl = 4")
  50. setGeneric("sqlDeleteFrom", function(conn, name, where = NULL, ...) {
  51.   standardGeneric("sqlDeleteFrom")
  52. }, signature = "conn", valueClass = "SQL")
  53.  
  54. setMethod("sqlDeleteFrom", "DBIConnection",
  55.   function(conn, name, where = NULL, ....) {
  56.     sql <- paste0("DELETE FROM ", dbQuoteIdentifier(conn, name))
  57.     if (!is.null(where)) {
  58.       sql <- paste0(sql, " WHERE ", where)
  59.     }
  60.     SQL(sql)
  61.   }
  62. )
  63.  
  64. #' Generate SQL: DROP TABLE
  65. #'
  66. #' @inheritParams sqlCreateTable
  67. #' @param name A string giving the table name to drop. Escaped with
  68. #'   \code{\link{dbQuoteIdentifier}}
  69. #' @aliases sqlDropTable,DBIConnection-method
  70. #' @export
  71. #' @examples
  72. #' conn <- setClass("Mock", "DBIConnection")()
  73. #'
  74. #' sqlDropTable(conn, "mtcars")
  75. setGeneric("sqlDropTable", function(conn, name, ...) {
  76.   standardGeneric("sqlDropTable")
  77. }, signature = "conn", valueClass = "SQL")
  78.  
  79. setMethod("sqlDropTable", "DBIConnection",
  80.   function(conn, name, ...) {
  81.     SQL(paste0("DROP TABLE ", dbQuoteIdentifier(conn, name)))
  82.   }
  83. )
  84.  
  85. #' Generate SQL: INSERT INTO
  86. #'
  87. #' @inheritParams sqlCreateTable
  88. #' @param name A string giving the table name to insert into. Escaped with
  89. #'   \code{\link{dbQuoteIdentifier}}
  90. #' @param values a data frame containing values to insert into the table.
  91. #'   Character columns will be escaped with \code{\link{dbQuoteString}}.
  92. #' @param fields The names of the fields to insert into. Defaults to
  93. #'   \code{names(values)}.
  94. #' @aliases sqlInsertInto,DBIConnection-method
  95. #' @return A \code{\link{SQL}} of length \code{nrow(values)}.
  96. #' @export
  97. #' @examples
  98. #' conn <- setClass("Mock", "DBIConnection")()
  99. #'
  100. #' sqlInsertInto(conn, "mtcars", mtcars)
  101. setGeneric("sqlInsertInto", function(conn, name, values, fields = names(values), ...) {
  102.   standardGeneric("sqlInsertInto")
  103. }, signature = "conn", valueClass = "SQL")
  104.  
  105. setMethod("sqlInsertInto", "DBIConnection",
  106.   function(conn, name, values, fields = names(values), ...) {
  107.     stopifnot(is.data.frame(values), length(values) == length(fields))
  108.    
  109.     sql_fields <- paste0(dbQuoteIdentifier(conn, fields), collapse = ", ")
  110.    
  111.     # Escape strings in values
  112.     is_string <- vapply(values, is.character, logical(1))
  113.     values[is_string] <- lapply(values[is_string], dbQuoteString, conn = conn)
  114.    
  115.     # Generate one sql expression per row of input
  116.     values_mat <- do.call("cbind", values)
  117.     values_row <- apply(values_mat, 1, paste, collapse = ", ")
  118.    
  119.     SQL(paste0("INSERT INTO ", dbQuoteIdentifier(conn, name),
  120.       " (", sql_fields, ") ",
  121.       "VALUES ", values_row))
  122.   }
  123. )
  124.  
  125. #' Generate SQL: UPDATE
  126. #'
  127. #' @inheritParams sqlCreateTable
  128. #' @param name A string giving the table name to update. Escaped with
  129. #'   \code{\link{dbQuoteIdentifier}}
  130. #' @param set a named list of character vectors or \code{\link{SQL}} objects.
  131. #'   Not escaped (because it's hard in general to do a good job of it).
  132. #'   Names are escaped with \code{\link{dbQuoteIdentifier}}.
  133. #' @param where An optional string providing an SQL expression used to restrict
  134. #'   the set of rows to be updated. Not escaped.
  135. #' @aliases sqlUpdate,DBIConnection-method
  136. #' @export
  137. #' @examples
  138. #' conn <- setClass("Mock", "DBIConnection")()
  139. #'
  140. #' sqlUpdate(conn, "mtcars", c(mpg = "mpg / 22"))
  141. setGeneric("sqlUpdate", function(conn, name, set, where = NULL, ...) {
  142.   standardGeneric("sqlUpdate")
  143. }, signature = "conn", valueClass = "SQL")
  144.  
  145. setMethod("sqlUpdate", "DBIConnection",
  146.   function(conn, name, set, where = NULL, ...) {
  147.     set_names <- dbQuoteIdentifier(conn, names(set))
  148.    
  149.     SQL(paste0("UPDATE ", dbQuoteIdentifier(conn, name), " SET ",
  150.       paste0(set_names, " = ", set, collapse = ", "),
  151.       if (!is.null(where)) paste0(" WHERE ", where)
  152.     ))
  153.   }
  154. )
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top