Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #' Attempt to interpolate a prepared query manually
- #'
- #' @param query Query string, with placeholders
- #' @param con Database connection. Required by [glue::glue_sql()] for
- #' correct escaping.
- #' @param values Unnamed list of values.
- #' @return Interpolated query, as a character.
- #' @author Alexey Shiklomanov
- #' @export
- interpolate_query <- function(query, con, values) {
- PEcAn.logger::logger.warn(paste0(
- "`PostgreSQL` driver does not support prepared statements, so attempting ",
- "to interpolate query manually. ",
- "This may not be perfect or safe. ",
- "You are strongly encouraged to use the `Postgres` database driver instead."
- ))
- nvalues <- length(values)
- # All values have a placeholder
- need_placeholders <- paste0("$", seq_len(nvalues))
- has_placeholders <- vapply(need_placeholders, grepl, logical(1),
- x = query, fixed = TRUE)
- # All placeholders have an associated value
- all_placeholders <- gregexpr("\\$[[:digit:]]+", query)[[1]]
- # ...and are sequential, numbered $1 to $N.
- need_placeholders2 <- paste0("$", seq_along(all_placeholders))
- has_placeholders2 <- vapply(need_placeholders2, grepl, logical(1),
- x = query, fixed = TRUE)
- if (!all(has_placeholders) ||
- !all(has_placeholders2) ||
- length(all_placeholders) != nvalues) {
- PEcAn.logger::logger.severe(paste0(
- "Values require ", nvalues, " placeholders, ",
- "but only ", sum(all_placeholders), " found in query."
- ))
- }
- query_fmt <- query
- for (i in seq_along(need_placeholders)) {
- iph <- all_placeholders[[i]]
- ph <- paste0("\\", need_placeholders[[i]])
- v <- values[[i]]
- xval <- paste0("{X", i, "}")
- if (length(v) > 1) {
- PEcAn.logger::logger.warn(paste0(
- "Detected a value of length > 1, so replacing preceding `=` with `IN` ",
- "(if present). This may be unreliable."
- ))
- rxp <- paste0("( *= *)(?=", ph, ")")
- query_fmt <- gsub(rxp, " IN ", query_fmt, perl = TRUE)
- xval <- paste0("({X", i, "*})")
- }
- query_fmt <- gsub(ph, xval, query_fmt)
- }
- values_names <- setNames(values, paste0("X", seq_along(need_placeholders)))
- string <- glue::glue_sql(query_fmt, .envir = values_names, .con = con)
- PEcAn.logger::logger.debug(paste0(
- "Query has been interpolated as follows: \n",
- string
- ), wrap = FALSE)
- as.character(string)
- }
- query <- "SELECT * FROM models WHERE id = $1 AND name = $2"
- values <- list(5, c("ED", "SIGNET"))
- con <- DBI::dbConnect(RPostgreSQL::PostgreSQL(), user = "bety", password = "bety", host = "localhost", port = 5432)
- interpolate_query(query, con, values)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement