1context("dbSendQuery")
2
3test_that("attempting to change schema with pending rows generates warning", {
4  con <- dbConnect(SQLite())
5  on.exit(dbDisconnect(con))
6
7  df <- data.frame(a = letters, b = LETTERS, c = 1:26, stringsAsFactors = FALSE)
8  dbWriteTable(con, "t1", df)
9
10  rs <- dbSendQuery(con, "SELECT * FROM t1")
11  row1 <- fetch(rs, n = 1)
12  expect_equal(row1, df[1, ])
13
14  expect_warning(dbSendQuery(con, "CREATE TABLE t2 (x text, y integer)"),
15    "pending rows")
16})
17
18
19test_that("simple position binding works", {
20  con <- dbConnect(SQLite(), ":memory:")
21  dbWriteTable(con, "t1", data.frame(x = 1, y = 2))
22
23  dbGetPreparedQuery(con, "INSERT INTO t1 VALUES (?, ?)",
24    bind.data = data.frame(x = 2, y = 1))
25
26  expect_equal(dbReadTable(con, "t1")$x, c(1, 2))
27})
28
29test_that("simple named binding works", {
30  con <- dbConnect(SQLite(), ":memory:")
31  dbWriteTable(con, "t1", data.frame(x = 1, y = 2))
32
33  dbGetPreparedQuery(con, "INSERT INTO t1 VALUES (:x, :y)",
34    bind.data = data.frame(y = 1, x = 2))
35
36  expect_equal(dbReadTable(con, "t1")$x, c(1, 2))
37})
38
39test_that("named binding errors if missing name", {
40  con <- dbConnect(SQLite(), ":memory:")
41  dbWriteTable(con, "t1", data.frame(x = 1, y = 2))
42
43  expect_error(
44    dbGetPreparedQuery(con, "INSERT INTO t1 VALUES (:x, :y)",
45      bind.data = data.frame(y = 1)),
46    "incomplete data binding"
47  )
48})
49
50bind_select_setup <- function() {
51  con <- dbConnect(SQLite())
52  df <- data.frame(id = letters[1:5],
53    x = 1:5,
54    y = c(1L, 1L, 2L, 2L, 3L),
55    stringsAsFactors = FALSE)
56
57  dbWriteTable(con, "t1", df, row.names = FALSE)
58  con
59}
60
61test_that("one row per bound select", {
62  con <- bind_select_setup()
63
64  got <- dbGetPreparedQuery(con, "select * from t1 where id = ?",
65    data.frame(id = c("e", "a", "c")))
66
67  expect_equal(got$id, c("e", "a", "c"))
68})
69
70test_that("failed matches are silently dropped", {
71  con <- bind_select_setup()
72  sql <- "SELECT * FROM t1 WHERE id = ?"
73
74  df1 <- dbGetPreparedQuery(con, sql, data.frame(id = "X"))
75  expect_equal(nrow(df1), 0)
76  expect_equal(names(df1), c("id", "x", "y"))
77
78  df2 <- dbGetPreparedQuery(con, sql, data.frame(id = c("X", "Y")))
79  expect_equal(nrow(df2), 0)
80  expect_equal(names(df2), c("id", "x", "y"))
81
82  df3 <- dbGetPreparedQuery(con, sql, data.frame(id = c("X", "a", "Y")))
83  expect_equal(nrow(df3), 1)
84  expect_equal(names(df3), c("id", "x", "y"))
85})
86
87test_that("NA matches NULL", {
88  con <- bind_select_setup()
89  dbGetQuery(con, "INSERT INTO t1 VALUES ('x', NULL, NULL)")
90
91  got <- dbGetPreparedQuery(con, "SELECT id FROM t1 WHERE y IS :y",
92    data.frame(y = NA_integer_))
93
94  expect_equal(got$id, "x")
95})
96