dplyr (version 0.1.1)

sql: SQL escaping.

Description

These functions are critical when writing functions that translate R functions to sql functions. Typically a conversion function should escape all it's inputs and return an sql object.

Usage

sql(x)
ident(x)
is.sql(x)
is.ident(x)
escape(x, parens = NA, collapse = " ", con = NULL)

Arguments

...

Character vectors that will be combined into a single SQL expression. ident flags its input as a identifier, to ensure that it gets the correct quoting.

x

An object to escape. Existing sql vectors will be left as is, character vectors are escaped with single quotes, numeric vectors have trailing .0 added if they're whole numbers, identifiers are escaped with double quotes.

parens,collapse

Controls behaviour when multiple values are supplied. parens should be a logical flag, or if NA, will wrap in parens if length > 1.

Default behaviour: lists are always wrapped in parens and separated by commas, identifiers are separated by commas and never wrapped, atomic vectors are separated by spaces and wrapped in parens if needed.

Examples

# Doubles vs. integers
escape(1:5)
escape(c(1, 5.4))

# String vs known sql vs. sql identifier
escape("X")
escape(sql("X"))
escape(ident("X"))

# Escaping is idempotent
escape("X")
escape(escape("X"))
escape(escape(escape("X")))

# You can use these functions to make your own R wrappers for SQL functions.
# The following is a more sophisticated version of round that have more
# informative variable names and if present, checks that the second argument
# is a number.
sql_round <- function(x, dp = NULL) {
  x <- escape(x)
  if (is.null(dp)) return(sql(paste0("ROUND(", x, ")")))

  stopifnot(is.numeric(dp), length(dp) == 1)
  sql(paste0("ROUND(", x, ", ", dp, ")"))
}
sql_round(sql("X"), 5)

rounder <- sql_variant(sql_translator(round = sql_round, .parent = base_agg))
translate_sql(round(X), variant = rounder)
translate_sql(round(X, 5), variant = rounder)