Nothing
skip_if_not_installed("DBI")
skip_if_not_installed("RSQLite")
describe("glue_sql", {
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
on.exit(DBI::dbDisconnect(con))
it("errors if no connection given", {
var <- "foo"
expect_error(glue_sql("{var}"), "missing")
})
it("returns the string if no substations needed", {
expect_identical(glue_sql("foo", .con = con), DBI::SQL("foo"))
})
it("quotes string values", {
var <- "foo"
expect_identical(glue_sql("{var}", .con = con), DBI::SQL("'foo'"))
})
it("quotes identifiers", {
var <- "foo"
expect_identical(glue_sql("{`var`}", .con = con), DBI::SQL("`foo`"))
})
it("quotes Id identifiers", {
var <- DBI::Id(schema = "foo", table = "bar", column = "baz")
expect_identical(glue_sql("{`var`}", .con = con), DBI::SQL("`foo`.`bar`.`baz`"))
})
it("quotes lists of Id identifiers", {
var <- c(
DBI::Id(schema = "foo", table = "bar", column = "baz"),
DBI::Id(schema = "foo", table = "bar", column = "baz2")
)
expect_identical(
glue_sql("{`var`*}", .con = con),
DBI::SQL("`foo`.`bar`.`baz`, `foo`.`bar`.`baz2`")
)
})
it("Does not quote numbers", {
var <- 1
expect_identical(glue_sql("{var}", .con = con), DBI::SQL("1"))
})
it("Does not quote DBI::SQL()", {
var <- DBI::SQL("foo")
expect_identical(glue_sql("{var}", .con = con), DBI::SQL("foo"))
})
it("collapses values if succeeded by a *", {
expect_identical(glue_sql("{var*}", .con = con, var = 1), DBI::SQL(1))
expect_identical(glue_sql("{var*}", .con = con, var = 1:5), DBI::SQL("1, 2, 3, 4, 5"))
expect_identical(glue_sql("{var*}", .con = con, var = "a"), DBI::SQL("'a'"))
expect_identical(glue_sql("{var*}", .con = con, var = letters[1:5]), DBI::SQL("'a', 'b', 'c', 'd', 'e'"))
})
it('collapses empty values to empty string', {
expect_identical(glue_sql("{var*}", .con = con, var = character()), DBI::SQL(""))
expect_identical(glue_sql("{var*}", .con = con, var = DBI::SQL(character())), DBI::SQL(""))
})
it("mimics glue() when not collapsing", {
expect_equal(glue_sql("{var}", var = NULL), DBI::SQL(glue("{var}", var = NULL)))
})
it("should return an SQL NULL by default for missing values", {
var <- list(NA, NA_character_, NA_real_, NA_integer_)
expect_identical(glue_sql("x = {var}", .con = con), rep(DBI::SQL("x = NULL"), 4))
})
it("should preserve the type of the even with missing values (#130)", {
expect_identical(glue_sql("x = {c(1L, NA)}", .con = con), DBI::SQL(c(paste0("x = ", c(1, "NULL")))))
expect_identical(glue_sql("x = {c(1, NA)}", .con = con), DBI::SQL(c(paste0("x = ", c(1, "NULL")))))
expect_identical(glue_sql("x = {c('1', NA)}", .con = con), DBI::SQL(c(paste0("x = ", c("'1'", "NULL")))))
expect_identical(glue_sql("x = {c(TRUE, NA)}", .con = con), DBI::SQL(c(paste0("x = ", c("1", "NULL")))))
})
it("should return NA for missing values quote strings", {
var <- c("C", NA)
expect_identical(glue_sql("x = {var}", .con = con), DBI::SQL(c("x = 'C'", "x = NULL")))
})
it("should return a quoted date for Dates", {
var <- as.Date("2019-01-01")
expect_identical(glue_sql("x = {var}", .con = con), DBI::SQL("x = '2019-01-01'"))
})
it("should quote values from lists properly", {
var <- list(1, 2, "three")
expect_identical(
glue_sql("x = {var}", .con = con),
DBI::SQL(c("x = 1", "x = 2", "x = 'three'"))
)
})
it("should handle NA when collapsing (#185)", {
expect_identical(glue_sql("x IN ({c(NA, 'A')*})", .con = con), DBI::SQL(paste0("x IN (NULL, 'A')")))
expect_identical(glue_sql("x IN ({c(NA, 1)*})", .con = con), DBI::SQL(paste0("x IN (NULL, 1)")))
expect_identical(glue_sql("x IN ({c(NA, 1L)*})", .con = con), DBI::SQL(paste0("x IN (NULL, 1)")))
expect_identical(glue_sql("x IN ({c(NA, TRUE)*})", .con = con), DBI::SQL(paste0("x IN (NULL, 1)")))
})
it("should handle DBI::SQL() elements correctly when collapsing (#191)", {
expect_identical(glue_sql("x IN ({DBI::SQL(c('a','b'))*})", .con = con), DBI::SQL(paste0("x IN (a, b)")))
})
it("should allow whitespace after the *", {
x <- 1:3
expect_identical(
glue_sql(.con = con, "{x* }"),
DBI::SQL(paste0("1, 2, 3"))
)
})
})
describe("glue_data_sql", {
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
on.exit(DBI::dbDisconnect(con))
it("collapses values if succeeded by a *", {
var <- "foo"
expect_identical(glue_data_sql(mtcars, "{head(gear)*}", .con = con), DBI::SQL("4, 4, 4, 3, 3, 3"))
})
})
describe("glue_sql_collapse", {
it("returns an SQL object", {
expect_identical(
glue_sql_collapse(character()),
DBI::SQL("")
)
expect_identical(
glue_sql_collapse(c("foo", "bar", "baz")),
DBI::SQL("foobarbaz")
)
})
})
test_that("get nice errors if rlang installed", {
expect_snapshot(error = TRUE, {
glue_sql("{x + }")
glue_sql("{NOTFOUND}")
})
})
Any scripts or data that you put into this service are public.
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.