tests/testthat/test_03_createDB.R

In b-becker/eatGADS: Normalizing, Subsetting and Reshaping Hierarchical Data

context("Create Data Base")
library(eatGADS)

# load test data (df1, df2, pkList, fkList)
load(file = "c:/Benjamin_Becker/02_Repositories/packages/eatGADS/tests/testthat/helper_dbdata.rda")

bigList <- mergeLabels(df1 = df1, df2 = df2)
allList <- addKeys(bigList = bigList, pkList = pkList, fkList = fkList)

createDB(allList = allList, filePath = ":memory:")

### test shell + sqlite3
test_that("Data base can be created via Shell ", {
  expect_message(init_DB(filePath = ":memory:"),
  "filePath points to work memory")
})

### data table query creation
test_that("Create partial Query for variable definitions", {
  createdQuery <- write_varDef(df1$dat)
  expected <- "v1 REAL , ID2 REAL ,"
  expect_equal(createdQuery, expected)
})
test_that("Create Query for primary key", {
  createdQuery <- write_primeKey(pkList$df2)
  expected <- "PRIMARY KEY ( ID2, v2 )"
  expect_equal(createdQuery, expected)
})
test_that("Create Query for foreign key", {
  createdQuery <- write_foreignKey(fkList$df2)
  expected <- "FOREIGN KEY ( ID2 ) REFERENCES df1 ( ID2 )"
  expect_equal(createdQuery, expected)
})

#
test_that("Create Query for a single data frame", {
  createdQuery <- writeQ_create(df1$dat, pkList$df1, fkList$df1, "df1")
  expected <- "CREATE TABLE df1 ( v1 REAL , ID2 REAL , PRIMARY KEY ( v1 )  );"
  expect_equal(createdQuery, expected)
})

### merge order query creation
test_that("Create Query for a single data frame", {
  createdQuery <- writeQ_mergeOrder(names(bigList$dfList))
  expected <- c("CREATE TABLE metaInformation ( mergeOrder TEXT );",
                "INSERT INTO metainformation (mergeOrder) VALUES ( ' df1 df2 ' );")
  expect_equal(createdQuery, expected)
})

### label frame query creation
test_that("Create Query for a single data frame", {
  createdQuery <- writeQ_create(df = bigList$labelList, df_name = "Labels",
                                primeKey = c("varName", "value", "data_table"), foreignKey = NULL)
  expected <- "CREATE TABLE Labels ( varName TEXT , varLabel TEXT , value REAL , label TEXT , missings TEXT , data_table TEXT , PRIMARY KEY ( varName, value, data_table )  );"
  expect_equal(createdQuery, expected)
})

### test safe create query? dbWriteTable?
# tbd


### final check
test_that("Data base creation as a whole runs ", {
  runs <- createDB(allList = allList, filePath = ":memory:")
  expect_identical(runs, NULL)
})