tests/testthat/test_05_pullDB.R

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

context("Pull and merge data from DB")
library(eatGADS)

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


### variable input check
test_that("Variable selection checked correctly", {
  expect_error(check_vSelect(c("ID2", "ID3"), c("v1", "ID2")),
               "ID3 are not in the data base")
  expect_silent(check_vSelect(c("ID2", "v1", "v2"), c("v1", "ID2", "v2")))
})

test_that("Variable selection prepared correctly", {
  expect_identical(prep_vSelect(c("v1", "ID2"), filePath = "helper_database.db"), list(df1 = c("v1", "ID2"), df2 = ("ID2")))
})

test_that("Variable selection prepared correctly for output all variables", {
  expect_identical(prep_vSelect(vSelect = NULL, filePath = "helper_database.db"), list(df1 = c("v1", "ID2"), df2 = c("ID2", "v2")))
})

### Query creation
test_that("Left Joins formulated correctly based on foreign keys and merge Order", {
  expect_identical(write_LJoins(c("df1", "df2"), fkList = fkList),
                   "LEFT JOIN df2 using ( ID2  )")
})

test_that("Variable selection pasted correctly", {
  expect_identical(write_SEL(list(df1 = c("v1", "ID2"), df2 = ("ID2"))),
                   "df1.v1,  df1.ID2,  df2.ID2")
})


test_that("Complete Query pasted correctly", {
  expect_identical(writeQ_pull(list(df1 = c("v1", "ID2"), df2 = c("ID2", "v2")), mergeOrder = c("df1", "df2"),fkList = fkList),
                   "SELECT DISTINCT df1.v1,  df1.ID2,  df2.ID2,  df2.v2 FROM df1 LEFT JOIN df2 using ( ID2  ) ;")
})


### Merged result
# create expected data result
m1 <- merge(expected_bigList$dfList$df1, expected_bigList$dfList$df2, all = TRUE)
# temporary workaround because variable is very uglily duplicated
m1$ID2..3 <- m1$ID2
expected <- m1[, c(2, 1, 4, 3)]


test_that("Merged results are correct for complete pulls", {
  expect_equal(getDF("helper_database.db"), expected)
})