dbi.table (version 1.0.1)

reference.test: Test dbi.table vs. Reference Implementation

Description

Evaluate an expression including at least one dbi.table and compare the result with the Reference Implementation. This function is primarily for testing and is potentially very slow for large tables.

Usage

reference.test(
  expr,
  envir = parent.frame(),
  ignore.row.order = TRUE,
  verbose = TRUE
)

Value

a logical value.

Arguments

expr

an expression involving at least one dbi.table and whose result can be coerced into a data.table.

envir

an environment. Where to evaluate expr.

ignore.row.order

a logical value. This argument is passed to all.equal.

verbose

a logical value. When TRUE, the output from all.equal is displayed in a message when all.equal returns anything other than TRUE.

Reference Implementation

Suppose that id1 identifies a table in a SQL database and that [i, j, by] describes a subset/select/summarize operation using data.table syntax. The Reference Implementation for this operation is:

setDT(dbReadTable(conn, id1))[i, j, by]

More generally, for an expression involving multiple SQL database objects and using data.table syntax, the Reference Implementation would be to download each of these objects in their entirety, convert them to data.tables, then evaluate the expression.

The goal of the dbi.table is to generate an SQL query that produces the same results set as the Reference Implementation up to row ordering.

Examples

  duck <- dbi.catalog(chinook.duckdb)
  Album <- duck$main$Album
  Artist <- duck$main$Artist

  reference.test(merge(Album, Artist, by = "ArtistId"))