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