SHARE
TWEET

Untitled

a guest Apr 1st, 2019 178 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #' Attempt to interpolate a prepared query manually
  2. #'
  3. #' @param query Query string, with placeholders
  4. #' @param con Database connection. Required by [glue::glue_sql()] for
  5. #'   correct escaping.
  6. #' @param values Unnamed list of values.
  7. #' @return Interpolated query, as a character.
  8. #' @author Alexey Shiklomanov
  9. #' @export
  10. interpolate_query <- function(query, con, values) {
  11.   PEcAn.logger::logger.warn(paste0(
  12.     "`PostgreSQL` driver does not support prepared statements, so attempting ",
  13.     "to interpolate query manually. ",
  14.     "This may not be perfect or safe. ",
  15.     "You are strongly encouraged to use the `Postgres` database driver instead."
  16.   ))
  17.   nvalues <- length(values)
  18.  
  19.   # All values have a placeholder
  20.   need_placeholders <- paste0("$", seq_len(nvalues))
  21.   has_placeholders <- vapply(need_placeholders, grepl, logical(1),
  22.                              x = query, fixed = TRUE)
  23.  
  24.   # All placeholders have an associated value
  25.   all_placeholders <- gregexpr("\\$[[:digit:]]+", query)[[1]]
  26.  
  27.   # ...and are sequential, numbered $1 to $N.
  28.   need_placeholders2 <- paste0("$", seq_along(all_placeholders))
  29.   has_placeholders2 <- vapply(need_placeholders2, grepl, logical(1),
  30.                               x = query, fixed = TRUE)
  31.  
  32.   if (!all(has_placeholders) ||
  33.         !all(has_placeholders2) ||
  34.          length(all_placeholders) != nvalues) {
  35.     PEcAn.logger::logger.severe(paste0(
  36.       "Values require ", nvalues, " placeholders, ",
  37.       "but only ", sum(all_placeholders), " found in query."
  38.     ))
  39.   }
  40.  
  41.   query_fmt <- query
  42.   for (i in seq_along(need_placeholders)) {
  43.     iph <- all_placeholders[[i]]
  44.     ph <- paste0("\\", need_placeholders[[i]])
  45.     v <- values[[i]]
  46.     xval <- paste0("{X", i, "}")
  47.     if (length(v) > 1) {
  48.       PEcAn.logger::logger.warn(paste0(
  49.         "Detected a value of length > 1, so replacing preceding `=` with `IN` ",
  50.         "(if present). This may be unreliable."
  51.       ))
  52.       rxp <- paste0("( *= *)(?=", ph, ")")
  53.       query_fmt <- gsub(rxp, " IN ", query_fmt, perl = TRUE)
  54.       xval <- paste0("({X", i, "*})")
  55.     }
  56.     query_fmt <- gsub(ph, xval, query_fmt)
  57.   }
  58.  
  59.   values_names <- setNames(values, paste0("X", seq_along(need_placeholders)))
  60.  
  61.   string <- glue::glue_sql(query_fmt, .envir = values_names, .con = con)
  62.   PEcAn.logger::logger.debug(paste0(
  63.     "Query has been interpolated as follows: \n",
  64.     string
  65.   ), wrap = FALSE)
  66.   as.character(string)
  67. }
  68.  
  69. query <- "SELECT * FROM models WHERE id = $1 AND name = $2"
  70. values <- list(5, c("ED", "SIGNET"))
  71. con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), user = "bety", password = "bety", host = "localhost", port = 5432)
  72.  
  73. interpolate_query(query, con, values)
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top