inst/_tests/testthat/test_collections.R

In mbannert/timeseriesdb: A Time Series Database for Official Statistics with R and PostgreSQL

context("collections - insert")

if(is_test_db_reachable()) {
  con_admin <- connect_to_test_db()
  con_writer <- connect_to_test_db("dev_writer")
  con_reader <- connect_to_test_db("dev_reader_public")
}


# db_collection_add_ts -------------------------------------------------------
test_with_fresh_db(con_admin, "db_collection_add_ts returns OK", {
  result <- db_collection_add_ts(con_writer,
                              "tests first",
                              "ts4",
                              schema = "tsdb_test")

  expect_is(result, "list")
  expect_named(result, c("status"))
  expect_equal(result$status, "ok")
})

test_with_fresh_db(con_admin, "db_collection_add_ts warns if keys not in catalog", {
  expect_warning(db_collection_add_ts(con_writer,
                                   "tests first",
                                   "tsx",
                                   schema = "tsdb_test"), "specific collection")
})

test_with_fresh_db(con_admin, "db_collection_add_ts returns warning keys not in catalog", {
  result <- suppressWarnings(db_collection_add_ts(con_writer,
                                               "tests first",
                                               "tsx",
                                               schema = "tsdb_test"))

  expect_is(result, "list")
  expect_named(result, c("status", "message", "invalid_keys"))
  expect_equal(result$status, "warning")
  expect_equal(result$invalid_keys, "tsx")
})

test_with_fresh_db(con_admin, "db_collection_add_ts add keys to existing", {
  db_collection_add_ts(con_writer, "tests first", "vts1", user = "test",
                    schema = "tsdb_test")

  result <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collect_catalog
                             JOIN tsdb_test.collections
                             ON tsdb_test.collect_catalog.id = tsdb_test.collections.id
                             AND name = 'tests first'")

  expect_equal(nrow(result), 4)
  expect_setequal(result$ts_key, c("ts1", "ts2", "ts3", "vts1"))
})

test_with_fresh_db(con_admin, "db_collection_add_ts add new collection & keys", {
  db_collection_add_ts(con_writer,
                    "tests new",
                    c("vts1", "vts2"),
                    user = "test",
                    description = "a collection generated by a test",
                    schema = "tsdb_test")

  collections_result <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collections
                                         WHERE name = 'tests new'
                                         AND owner = 'test'")

  expect_equal(collections_result[, -c(1, 6)], data.frame(
    name = "tests new",
    owner = "test",
    description = "a collection generated by a test",
    created_by = "dev_writer",
    stringsAsFactors = FALSE
  ))

  collect_catalog_result <- dbGetQuery(con_admin, "SELECT tsdb_test.collections.id, ts_key FROM tsdb_test.collect_catalog
                                       JOIN tsdb_test.collections
                                       ON tsdb_test.collect_catalog.id = tsdb_test.collections.id
                                       AND name = 'tests new'
                                       ORDER BY tsdb_test.collect_catalog.ts_key")

  expect_equal(collect_catalog_result, data.frame(
    id = collections_result$id,
    ts_key = c("vts1", "vts2"),
    stringsAsFactors = FALSE
  ))
})

test_with_fresh_db(con_admin, "reader may create sets and add keys", {
  db_collection_add_ts(con_reader,
                    "tests new",
                    c("vts1", "vts2"),
                    user = "test",
                    description = "a collection generated by a test",
                    schema = "tsdb_test")

  collections_result <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collections
                                   WHERE name = 'tests new'
                                   AND owner = 'test'")

  expect_equal(collections_result[, -c(1, 6)], data.frame(
    name = "tests new",
    owner = "test",
    description = "a collection generated by a test",
    created_by = "dev_reader_public",
    stringsAsFactors = FALSE
  ))

  collect_catalog_result <- dbGetQuery(con_admin, "SELECT tsdb_test.collections.id, ts_key FROM tsdb_test.collect_catalog
                                       JOIN tsdb_test.collections
                                       ON tsdb_test.collect_catalog.id = tsdb_test.collections.id
                                       AND name = 'tests new'
                                       ORDER BY tsdb_test.collect_catalog.ts_key")

  expect_equal(collect_catalog_result, data.frame(
    id = collections_result$id,
    ts_key = c("vts1", "vts2"),
    stringsAsFactors = FALSE
  ))
})

# db_collect_remove -------------------------------------------------------

context("collections - remove")

test_with_fresh_db(con_admin, "db_collection_remove_ts removing some keys returns ok", {
  result <- db_collection_remove_ts(con_writer,
                                 collection_name = "tests first",
                                 ts_keys = "ts1",
                                 user = "test",
                                 schema = "tsdb_test")
  expect_is(result, "list")
  expect_named(result, c("status"))
  expect_equal(result$status, "ok")
})

test_with_fresh_db(con_admin, "db_collection_remove_ts warns if combo not found", {


  expect_error(db_collection_remove_ts(con_writer,
                                    collection_name = "not a collection",
                                    ts_keys = c("does", "it", "matter?"),
                                    user = "alexandra_maine",
                                    schema = "tsdb_test"), "not exist")
})

test_with_fresh_db(con_admin, "db_collection_remove_ts also removing collection", {
  result <- db_collection_remove_ts(con_writer,
                                 collection_name = "tests second",
                                 ts_keys = c("ts4", "ts5"),
                                 user = "test",
                                 schema = "tsdb_test")

  expect_is(result, "list")
  expect_named(result, c("status", "message", "removed_collection"))
  expect_equal(result$status, "notice")
})

test_with_fresh_db(con_admin, "db_collection_remove_ts state", {
  db_collection_remove_ts(con_writer,
                       collection_name = "tests first",
                       ts_keys = c("ts1"),
                       user = "test",
                       schema = "tsdb_test")

  result <- dbGetQuery(con_admin, "SELECT ts_key FROM tsdb_test.collect_catalog
                             JOIN tsdb_test.collections
                             ON tsdb_test.collect_catalog.id = tsdb_test.collections.id
                             AND name = 'tests first'")

  expect_setequal(result$ts_key, c("ts2", "ts3"))

  result_collections <- dbGetQuery(con_admin, "SELECT 1 FROM tsdb_test.collections
                                         WHERE name = 'tests first'")

  expect_equal(nrow(result_collections), 1)
})

test_with_fresh_db(con_admin, "db_collection_remove_ts with remove collection state", {
  db_collection_remove_ts(con_writer,
                       collection_name = "tests first",
                       ts_keys = c("ts1", "ts2", "ts3"),
                       user = "test",
                       schema = "tsdb_test")

  result <- dbGetQuery(con_admin, "SELECT ts_key FROM tsdb_test.collect_catalog
                       JOIN tsdb_test.collections
                       ON tsdb_test.collect_catalog.id = tsdb_test.collections.id
                       AND name = 'tests first'")

  expect_equal(nrow(result), 0)

  result_collections <- dbGetQuery(con_admin, "SELECT 1 FROM tsdb_test.collections
                                   WHERE name = 'tests first'")

  expect_equal(nrow(result_collections), 0)
})

test_with_fresh_db(con_admin, "reader may remove from collections", {
  db_collection_remove_ts(con_reader,
                       collection_name = "tests first",
                       ts_keys = c("ts1"),
                       user = "test",
                       schema = "tsdb_test")

  result <- dbGetQuery(con_admin, "SELECT ts_key FROM tsdb_test.collect_catalog
                             JOIN tsdb_test.collections
                             ON tsdb_test.collect_catalog.id = tsdb_test.collections.id
                             AND name = 'tests first'")

  expect_setequal(result$ts_key, c("ts2", "ts3"))

  result_collections <- dbGetQuery(con_admin, "SELECT 1 FROM tsdb_test.collections
                                         WHERE name = 'tests first'")

  expect_equal(nrow(result_collections), 1)
})

# db_collection_delete ----------------------------------------------------
context("collections - delete")

test_with_fresh_db(con_admin, "db_collection_delete returns ok", {
  # In case anyone in the future should care: I wrote this with my eyes closed. o.Ô
  result <- db_collection_delete(con_writer,
                                 collection_name = "tests first",
                                 user = "test",
                                 schema = "tsdb_test")

  expect_is(result, "list")
  expect_named(result, c("status", "id"))
  expect_equal(result$status, "ok")
})

test_with_fresh_db(con_admin, "db_collection_delete warns if collection+user not found", {
  expect_warning(db_collection_delete(con_writer,
                                      collection_name = "rubber duckie",
                                      user = "The other Severin",
                                      schema = "tsdb_test"), "not be found")
})

test_with_fresh_db(con_admin, "db_collection_delete warning contents", {
  result <- suppressWarnings(db_collection_delete(con_writer,
                                                  collection_name = "rubber duckie",
                                                  user = "The other Severin",
                                                  schema = "tsdb_test"))

  expect_is(result, "list")
  expect_named(result, c("status", "message"))
  expect_equal(result$status, "warning")
})

test_with_fresh_db(con_admin, "db_collection_delete db state", {
  out <- db_collection_delete(con_writer,
                              collection_name = "tests first",
                              user = "test",
                              schema = "tsdb_test")

  result_collections <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collections
                                         WHERE name = 'tests first'")
  expect_equal(nrow(result_collections), 0)

  result_collect_catalog <- dbGetQuery(con_admin, sprintf("SELECT * FROM tsdb_test.collect_catalog WHERE id = '%s'", out$id))

  expect_equal(nrow(result_collect_catalog), 0)
})

test_with_fresh_db(con_admin, "db_collection_delete with missing user+collection does nothing", {
  suppressWarnings(db_collection_delete(con_writer,
                                        collection_name = "xxx",
                                        user = "whoever",
                                        schema = "tsdb_test"))

  result_collections <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collections")
  expect_equal(nrow(result_collections), 5)

  result_collect_catalog <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collect_catalog")
  expect_equal(nrow(result_collect_catalog), 12)
})

test_with_fresh_db(con_admin, "reader may delete collections", {
  out <- db_collection_delete(con_reader,
                              collection_name = "tests first",
                              user = "test",
                              schema = "tsdb_test")

  result_collections <- dbGetQuery(con_admin, "SELECT * FROM tsdb_test.collections
                                   WHERE name = 'tests first'")
  expect_equal(nrow(result_collections), 0)

  result_collect_catalog <- dbGetQuery(con_admin, sprintf("SELECT * FROM tsdb_test.collect_catalog WHERE id = '%s'", out$id))

  expect_equal(nrow(result_collect_catalog), 0)
})


# db_collection_list -----------------------------------------------------

test_with_fresh_db(con_admin, "db_collection_list", {
  out <- db_collection_list(con_reader, "test", schema = "tsdb_test")

  expect_equal(out,
               data.table(
                 name = c("mdtest", "readtest", "tests first", "tests second"),
                 description = c("collection with the vts", "time series for testing readers", "the first collection ever", NA_character_)
               ))
})


# read latest update ------------------------------------------------------

test_with_fresh_db(con_admin, "getting the latest update", {
  out <- db_collection_get_last_update(con_reader, "some random one", "johnny_public", schema = "tsdb_test")

  expect_equal(out$name, "some random one")
  # It's hot and I don't feel like wrangling DST issues
  expect_equal(
    out$updated,
    max(dbGetQuery(con_admin, "SELECT created_at FROM tsdb_test.timeseries_main WHERE ts_key = 'vts1'")$created_at)
  )
})


# read keys in a collection -----------------------------------------------

test_with_fresh_db(con_admin, "getting keys in a collection", {
  out <- db_collection_get_keys(con_reader, "some random one", "johnny_public", schema = "tsdb_test")

  expect_equal(out, c("ts1", "ts4", "vts1"))
})

test_with_fresh_db(con_admin, "getting keys in a nonexistent collection", {
  out <- db_collection_get_keys(con_reader, "notacollection", "C'thulhu", schema = "tsdb_test")

  expect_length(out, 0)
})