1context("dbWriteTable")
2
3# In memory --------------------------------------------------------------------
4
5test_that("can't override existing table with default options", {
6  con <- dbConnect(SQLite())
7  on.exit(dbDisconnect(con))
8
9  x <- data.frame(col1 = 1:10, col2 = letters[1:10])
10  dbWriteTable(con, "t1", x)
11  expect_error(dbWriteTable(con, "t1", x), "exists in database")
12})
13
14test_that("throws error if constrainted violated", {
15  con <- dbConnect(SQLite())
16  on.exit(dbDisconnect(con))
17
18  x <- data.frame(col1 = 1:10, col2 = letters[1:10])
19
20  dbWriteTable(con, "t1", x)
21  dbGetQuery(con, "CREATE UNIQUE INDEX t1_c1_c2_idx ON t1(col1, col2)")
22  expect_error(dbWriteTable(con, "t1", x, append = TRUE),
23    "UNIQUE constraint failed")
24})
25
26test_that("can't add table when result set open", {
27  # This needs to fail because cloning a temporary file or in memory
28  # database creates new database
29  con <- dbConnect(SQLite(), tempfile())
30  on.exit(dbDisconnect(con))
31
32  x <- data.frame(col1 = 1:10, col2 = letters[1:10])
33  dbWriteTable(con, "t1", x)
34
35  res <- dbSendQuery(con, "SELECT * FROM t1")
36  expect_warning(dbWriteTable(con, "t2", x), "pending rows")
37  expect_error(dbClearResult(res), "Expired")
38})
39
40test_that("rownames preserved", {
41  con <- dbConnect(SQLite())
42  on.exit(dbDisconnect(con))
43
44  df <- data.frame(x = 1:10)
45  row.names(df) <- paste(letters[1:10], 1:10, sep="")
46
47  dbWriteTable(con, "t1", df)
48  t1 <- dbReadTable(con, "t1")
49  expect_equal(rownames(t1), rownames(df))
50})
51
52test_that("commas in fields are preserved", {
53  con <- dbConnect(SQLite())
54  on.exit(dbDisconnect(con))
55
56  df <- data.frame(
57    x = c("ABC, Inc.","DEF Holdings"),
58    stringsAsFactors = FALSE
59  )
60  dbWriteTable(con, "t1", df, row.names = FALSE)
61  expect_equal(dbReadTable(con, "t1"), df)
62})
63
64test_that("NAs preserved in factors", {
65  con <- dbConnect(SQLite())
66  on.exit(dbDisconnect(con))
67
68  df <- data.frame(x = 1:10, y = factor(LETTERS[1:10]))
69  df$y[4] <- NA
70
71  dbWriteTable(con, "bad_table", df)
72  bad_table <- dbReadTable(con, "bad_table")
73  expect_equal(bad_table$x, df$x)
74  expect_equal(bad_table$y, as.character(df$y))
75})
76
77test_that("logical converted to int", {
78  con <- dbConnect(SQLite())
79  on.exit(dbDisconnect(con))
80
81  local <- data.frame(x = 1:3, y = c(NA, TRUE, FALSE))
82  dbWriteTable(con, "t1", local)
83  remote <- dbReadTable(con, "t1")
84
85  expect_equal(remote$y, as.integer(local$y))
86})
87
88test_that("can roundtrip special field names", {
89  con <- dbConnect(SQLite())
90  on.exit(dbDisconnect(con))
91
92  local <- data.frame(x = 1:3, select = 1:3, `  ` = 1:3, check.names = FALSE)
93  dbWriteTable(con, "torture", local)
94  remote <- dbReadTable(con, "torture", check.names = FALSE)
95
96  expect_equal(local, remote)
97})
98
99# From file -------------------------------------------------------------------
100
101test_that("comments are preserved", {
102  con <- dbConnect(SQLite())
103  on.exit(dbDisconnect(con))
104
105  tmp_file <- tempfile()
106  cat('A,B,C\n11,2#2,33\n', file = tmp_file)
107  on.exit(file.remove(tmp_file), add = TRUE)
108
109  dbWriteTable(con, "t1", tmp_file, header = TRUE, sep = ",")
110  remote <- dbReadTable(con, "t1")
111  expect_equal(remote$B, "2#2")
112})
113
114test_that("colclasses overridden by argument", {
115  con <- dbConnect(SQLite())
116  on.exit(dbDisconnect(con))
117
118  tmp_file <- tempfile()
119  cat('A,B,C\n1,2,3\n4,5,6\na,7,8\n', file = tmp_file)
120  on.exit(file.remove(tmp_file), add = TRUE)
121
122  dbWriteTable(con, "t1", tmp_file, header = TRUE, sep = ",",
123    colClasses = c("character", "integer", "double"))
124
125  remote <- dbReadTable(con, "t1")
126  expect_equal(sapply(remote, class),
127    c(A="character", B="integer", C="numeric"))
128})
129
130test_that("options work", {
131  con <- dbConnect(SQLite())
132  on.exit(dbDisconnect(con))
133
134  expected <- data.frame(
135    a = c(1:3, NA),
136    b = c("x", "y", "z", "E"),
137    stringsAsFactors = FALSE
138  )
139
140  dbWriteTable(con, "dat", "dat-n.txt", sep="|", eol="\n", overwrite = TRUE)
141  expect_equal(dbReadTable(con, "dat"), expected)
142
143  dbWriteTable(con, "dat", "dat-rn.txt", sep="|", eol="\r\n", overwrite = TRUE)
144  expect_equal(dbReadTable(con, "dat"), expected)
145})