"dbi.table works on data.table help examples"

dbi.table works on data.table help examples

Track[, .(SUM = sum(Milliseconds, na.rm = TRUE),
MEAN = mean(Milliseconds, na.rm = TRUE))],
test_that("aggregation works with by", {
MEAN = mean(Milliseconds, na.rm = TRUE)),
test_that("j syntax consistent with data.table", {
Track[, c("MediaTypeId", "UnitPrice", "Name", "Milliseconds")],
expect_no_error(tmp <- c("MediaTypeId", "UnitPrice", "Name", "Milliseconds"))
# dbi.table returns dbi.table; data.table returns numeric
expect_true(Album[ArtistId == local(ArtistId), .N][]$N == 1L)
test_that("i a dbi.table, data.table, or data.frame works", {
expect_no_error(id2 <- unique(Album[ArtistId == 2, .(ArtistId)]))
Album[data.table(ArtistId = 2), on = "ArtistId"],
Album[data.frame(ArtistId = 2), on = "ArtistId"],
test_that("empty temporary tables work", {
nrow(as.dbi.table(conn, datasets::iris[0, ], type = "query")[]) == 0L
nrow(as.dbi.table(conn, datasets::iris[0, ], type = "temporary")[]) == 0L

"i a dbi.table, data.table, or data.frame works"

merge(Album, Genre, by.x = "AlbumId", by.y = "GenreId"),
merge(Album, Genre, by.x = "AlbumId", by.y = "GenreId",
merge(Genre, Album, by.x = "GenreId", by.y = "AlbumId",
all = TRUE)[is.na(Name) | is.na(Title)],
test_that("sometimes merge doesn't work", {
expect_error(merge(Album, Genre, by.x = "AlbumId"))
expect_error(merge(Album, Genre, by.x = character(0), by.y = character(0)))
by.x = character(0), by.y = character(0)))
merge(Album, Album, by = c("AlbumId", "ArtistId")),
test_that("self merge works with no.dups = FALSE", {
merge(Album, Album, by.x = "AlbumId", by.y = "ArtistId", no.dups = FALSE),
merge(Album, Album, by.x = "AlbumId", by.y = "ArtistId",
test_that("extract merge foreign key join", {
Track[Customer, on = c(TrackId = "CustomerId", AlbumId = "SupportRepId")],
test_that("extract merge w/ foreign key joins using the binary operator ==", {
test_that("extract merge w/ syntax as X[Y, on=.(a, b)]", {
Album[Artist, on = .(AlbumId == ArtistId, ArtistId == ArtistId)],
test_that("extract merge w/ (non-equi) joins using binary operators >=, <=", {
test_that("extract merge w/ (non-equi) joins using binary operators >, <", {
Track[Customer, on = c("TrackId > CustomerId", "AlbumId < SupportRepId")],
test_that("extract self-merge withs w/ half-named character", {
Album[Album, on = c("AlbumId", ArtistId = "ArtistId")],
test_that("extract anti-join works w/ character", {
test_that("extract anti-join works w/ character non-equi join", {
Artist[!Album, on = "ArtistId > ArtistId"],
test_that("extract anti-join works w/ call non-equi join", {
Artist[!Album, on = .(ArtistId > ArtistId)],
test_that("extract anti-join works w/ char-call non-equi join", {
Artist[!Album, on = c("ArtistId > ArtistId")],
test_that("merge preserves where and order by", {
Artist <- Artist[ArtistId > 5 & ArtistId < 10]