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})