## ----setup, include = FALSE--------------------------------------------------- knitr::opts_chunk$set( collapse = TRUE, comment = "#>" ) ## ----Load, message=FALSE, eval = TRUE----------------------------------------- library(SQLDataFrame) library(DBI) ## ----constructor-------------------------------------------------------------- dbfile <- system.file("extdata/test.db", package = "SQLDataFrame") conn <- DBI::dbConnect(dbDriver("SQLite"), dbname = dbfile) dbListTables(conn) obj <- SQLDataFrame( conn = conn, dbtable = "state", dbkey = "state") obj ## ----------------------------------------------------------------------------- slotNames(obj) dbtable(obj) dbkey(obj) dbcon(obj) ## ----------------------------------------------------------------------------- tblData(obj) ## ----------------------------------------------------------------------------- dbnrows(obj) ## equivalent to nrow(obj) dbconcatKey(obj) ## ----------------------------------------------------------------------------- obj@indexes obj_sub <- obj[sample(5, 3, replace = TRUE), 2:3] obj_sub obj_sub@indexes identical(tblData(obj), tblData(obj_sub)) identical(dbnrows(obj), dbnrows(obj_sub)) nrow(obj) nrow(obj_sub) ## ----------------------------------------------------------------------------- obj_filter <- obj %>% filter(division == "South Atlantic" & size == "medium") obj_filter@indexes identical(tblData(obj), tblData(obj_filter)) obj_select <- obj %>% select(division, size) obj_select@indexes identical(tblData(obj), tblData(obj_select)) ## ----------------------------------------------------------------------------- rnms <- ROWNAMES(obj) obj[sample(rnms, 3), ] ## ----------------------------------------------------------------------------- obj1 <- SQLDataFrame(conn = conn, dbtable = "state", dbkey = c("region", "population")) ROWNAMES(obj1[1:10,]) obj1[c("South:3615.0", "West:365.0"), ] ## ----------------------------------------------------------------------------- sessionInfo() ## ----------------------------------------------------------------------------- library(SQLDataFrame) library(DBI) dbfile <- system.file("extdata/test.db", package = "SQLDataFrame") conn <- DBI::dbConnect(DBI::dbDriver("SQLite"), dbname = dbfile) obj <- SQLDataFrame(conn = conn, dbtable = "state", dbkey = "state") dim(obj) is(obj, "DataFrame") library(GenomicRanges) gr <- GRanges("chr1", IRanges(1:50, 60)) mcols(gr) <- obj ## assay <- array(1:10, dim=c(50, 2)) ## cdata <- obj[1:2, ] ## SE(assays = assya, rowRanges = gr, colData = cdata) ## see inst/extdata/sqldf_udpate.Rmd for FIXME tag!!!