tests/testthat/test-zzz-package.R

In poissonconsulting/createsqlite: Tools to generate SQLite database creation scripts

context("createsqlite")

test_that("package", {

  ### --- check file create from conn
  file <- ":memory:"
  if(file.exists(file)) unlink(file)

  dir <- tempdir()
  file <- file.path(dir, "test.sqlite")
  unlink(file)
  conn <- DBI::dbConnect(RSQLite::SQLite(), file)
  expect_is(conn, "SQLiteConnection")

  DBI::dbGetQuery(conn,
                  "CREATE TABLE Table1 (
                  Var1 TEXT NOT NULL,
                  Var2 REAL,
                  geometry TEXT NOT NULL,
                  CHECK(
                  Var1 IN ('Yes', 'No')
                  ),
                  PRIMARY KEY (Var1))")

  DBI::dbGetQuery(conn,
                  "CREATE TABLE Table2 (
                  Var1 TEXT NOT NULL,
                  Var2 INTEGER NOT NULL,
                  geometry TEXT,
                  CHECK(
                  Var1 IN ('Yes', 'No')
                  ),
                  FOREIGN KEY (Var1) REFERENCES Table1 (Var1),
                  PRIMARY KEY (Var2))")

  df1 <- data.frame(Var1 = c("Yes", "No"),
                    Var2 = c(1, 1.1),
                    geometry = c("a", "b"))

  dbWriteTable(conn, name = "Table1", value = df1, row.names = FALSE,
               append = TRUE)

  sch1 <- schema_conn(conn, table_name = "Table1")
  sch2 <- schemas_conn(conn)

  expect_is(sch1, "character")
  expect_length(sch1, 1L)

  expect_is(sch2, "character")
  expect_length(sch2, 1L)

  sch3 <- schemas_conn(conn, collapse = function(x)x)
  expect_is(sch3, "list")

  sch2 <- schemas_conn(conn, wrapper = function(x){paste("blah", x, "blah")})
  expect_length(regmatches(sch2, gregexpr("blah", sch2))[[1]], 4L)

  head <- poisson_header(conn)
  foot <- poisson_footer()
  schemas <- schemas_conn(conn)

  scr <- schema_conn_file(conn)
  scr2 <- schema_conn_file(conn, path = file.path(dir, "create-db.R"))

  expect_length(list.files(dir, pattern = ".R"), 1L)
  expect_true(grepl(schemas, scr, fixed = TRUE))
  expect_true(grepl(head, scr, fixed = TRUE))
  expect_true(grepl(foot, scr, fixed = TRUE))

  ### --- check file create from data.frames

})