1skip_if_not_installed("DBI")
2skip_if_not_installed("RSQLite")
3
4describe("glue_sql", {
5  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
6  on.exit(DBI::dbDisconnect(con))
7
8  it("errors if no connection given", {
9    var <- "foo"
10    expect_error(glue_sql("{var}"), "missing")
11  })
12  it("returns the string if no substations needed", {
13    expect_identical(glue_sql("foo", .con = con), DBI::SQL("foo"))
14  })
15  it("quotes string values", {
16    var <- "foo"
17    expect_identical(glue_sql("{var}", .con = con), DBI::SQL("'foo'"))
18  })
19  it("quotes identifiers", {
20    var <- "foo"
21    expect_identical(glue_sql("{`var`}", .con = con), DBI::SQL("`foo`"))
22  })
23  it("quotes Id identifiers", {
24    var <- DBI::Id(schema = "foo", table = "bar", column = "baz")
25    expect_identical(glue_sql("{`var`}", .con = con), DBI::SQL("`foo`.`bar`.`baz`"))
26  })
27  it("quotes lists of Id identifiers", {
28    var <- c(
29      DBI::Id(schema = "foo", table = "bar", column = "baz"),
30      DBI::Id(schema = "foo", table = "bar", column = "baz2")
31    )
32    expect_identical(glue_sql("{`var`*}", .con = con), DBI::SQL("`foo`.`bar`.`baz`, `foo`.`bar`.`baz2`"))
33  })
34  it("Does not quote numbers", {
35    var <- 1
36    expect_identical(glue_sql("{var}", .con = con), DBI::SQL("1"))
37  })
38  it("Does not quote DBI::SQL()", {
39    var <- DBI::SQL("foo")
40    expect_identical(glue_sql("{var}", .con = con), DBI::SQL("foo"))
41  })
42  it("collapses values if succeeded by a *", {
43    expect_identical(glue_sql("{var*}", .con = con, var = 1), DBI::SQL(1))
44    expect_identical(glue_sql("{var*}", .con = con, var = 1:5), DBI::SQL("1, 2, 3, 4, 5"))
45
46    expect_identical(glue_sql("{var*}", .con = con, var = "a"), DBI::SQL("'a'"))
47    expect_identical(glue_sql("{var*}", .con = con, var = letters[1:5]), DBI::SQL("'a', 'b', 'c', 'd', 'e'"))
48  })
49  it('collapses values should return NULL for length zero vector', {
50    expect_identical(glue_sql("{var*}", .con = con, var = character()), DBI::SQL("NULL"))
51    expect_identical(glue_sql("{var*}", .con = con, var = DBI::SQL(character())), DBI::SQL("NULL"))
52  })
53  it("should return an SQL NULL by default for missing values", {
54    var <- list(NA, NA_character_, NA_real_, NA_integer_)
55    expect_identical(glue_sql("x = {var}", .con = con), rep(DBI::SQL("x = NULL"), 4))
56  })
57
58  it("should return NA for missing values and .na = NULL", {
59    var <- list(NA, NA_character_, NA_real_, NA_integer_)
60    expect_identical(glue_sql("x = {var}", .con = con, .na = NULL), rep(DBI::SQL(NA), 4))
61  })
62
63  it("should preserve the type of the even with missing values (#130)", {
64      expect_identical(glue_sql("x = {c(1L, NA)}", .con = con), DBI::SQL(c(paste0("x = ", c(1, "NULL")))))
65      expect_identical(glue_sql("x = {c(1, NA)}", .con = con), DBI::SQL(c(paste0("x = ", c(1, "NULL")))))
66      expect_identical(glue_sql("x = {c('1', NA)}", .con = con), DBI::SQL(c(paste0("x = ", c("'1'", "NULL")))))
67      expect_identical(glue_sql("x = {c(TRUE, NA)}", .con = con), DBI::SQL(c(paste0("x = ", c("TRUE", "NULL")))))
68  })
69
70  it("should return NA for missing values quote strings", {
71    var <- c("C", NA)
72    expect_identical(glue_sql("x = {var}", .con = con), DBI::SQL(c("x = 'C'", "x = NULL")))
73  })
74
75  it("should return a quoted date for Dates", {
76    var <- as.Date("2019-01-01")
77    expect_identical(glue_sql("x = {var}", .con = con), DBI::SQL("x = '2019-01-01'"))
78  })
79
80  it("should quote values from lists properly", {
81    var <- list(1, 2, "three")
82    expect_identical(glue_sql("x = {var}", .con = con), DBI::SQL(c("x = 1", "x = 2", "x = 'three'")))
83  })
84
85  it("should handle NA when collapsing (#185)", {
86    expect_identical(glue_sql("x IN ({c(NA, 'A')*})", .con = con), DBI::SQL(paste0("x IN (NULL, 'A')")))
87    expect_identical(glue_sql("x IN ({c(NA, 1)*})", .con = con), DBI::SQL(paste0("x IN (NULL, 1)")))
88    expect_identical(glue_sql("x IN ({c(NA, 1L)*})", .con = con), DBI::SQL(paste0("x IN (NULL, 1)")))
89    expect_identical(glue_sql("x IN ({c(NA, TRUE)*})", .con = con), DBI::SQL(paste0("x IN (NULL, TRUE)")))
90  })
91
92  it("should handle DBI::SQL() elements correctly when collapsing (#191)", {
93    expect_identical(glue_sql("x IN ({DBI::SQL(c('a','b'))*})", .con = con), DBI::SQL(paste0("x IN (a, b)")))
94  })
95
96  it("should allow whitespace after the *", {
97    x <- 1:3
98    expect_identical(
99      glue_sql(.con = con, "{x* }"),
100      DBI::SQL(paste0("1, 2, 3"))
101    )
102  })
103})
104
105describe("glue_data_sql", {
106  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
107  on.exit(DBI::dbDisconnect(con))
108
109  it("collapses values if succeeded by a *", {
110    var <- "foo"
111    expect_identical(glue_data_sql(mtcars, "{head(gear)*}", .con = con), DBI::SQL("4, 4, 4, 3, 3, 3"))
112  })
113})
114
115describe("glue_sql_collapse", {
116  it("returns an SQL object", {
117    expect_identical(
118      glue_sql_collapse(character()),
119      DBI::SQL(character())
120    )
121
122    expect_identical(
123      glue_sql_collapse(c("foo", "bar", "baz")),
124      DBI::SQL("foobarbaz")
125    )
126  })
127})
128