## ------------------------------------------------------------------------ dbFile <- system.file("extdata", "TxDb.Hsapiens.UCSC.hg19.knownGene.sqlite", package="TxDb.Hsapiens.UCSC.hg19.knownGene") dbFile ## ------------------------------------------------------------------------ library(RSQLite) txcon <- dbConnect(SQLite(), dbname=dbFile) ## ------------------------------------------------------------------------ dbListTables(txcon) ## ------------------------------------------------------------------------ dbListFields(txcon, name="metadata") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT * FROM metadata") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT name FROM metadata") ## ------------------------------------------------------------------------ dbListFields(txcon, name="chrominfo") dbGetQuery(txcon, "SELECT chrom,length FROM chrominfo LIMIT 4") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT chrom,length FROM chrominfo WHERE length > 100000000") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT chrom,length FROM chrominfo WHERE chrom='chrX'") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT * FROM gene LIMIT 4") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT * FROM transcript LIMIT 4") ## ------------------------------------------------------------------------ sql <- "SELECT gene_id, tx_name FROM gene,transcript WHERE gene._tx_id=transcript._tx_id LIMIT 4" dbGetQuery(txcon, sql) ## ------------------------------------------------------------------------ sql <- "SELECT gene_id, tx_name, tx_strand FROM gene AS g, transcript AS t WHERE g._tx_id=t._tx_id LIMIT 4" dbGetQuery(txcon, sql) ## ------------------------------------------------------------------------ sql <- "SELECT * FROM gene AS g, (SELECT * FROM transcript WHERE tx_strand='+') AS t WHERE g._tx_id=t._tx_id LIMIT 4" dbGetQuery(txcon, sql) ## ------------------------------------------------------------------------ sql <- "SELECT * FROM (SELECT * FROM gene, splicing USING(_tx_id)), transcript USING (_tx_id) LIMIT 4" dbGetQuery(txcon, sql) ## ------------------------------------------------------------------------ sql <- "SELECT * FROM gene AS g, transcript AS t, splicing AS s WHERE g._tx_id=s._tx_id AND s._tx_id=t._tx_id LIMIT 4" dbGetQuery(txcon, sql) ## ------------------------------------------------------------------------ gfl <- system.file("extdata","gene_names.txt", package="UnderstandingRBioc") gene_names <- read.table(gfl, header=TRUE) head(gene_names) cfl <- system.file("extdata","chroms.txt", package="UnderstandingRBioc") chroms <- read.table(cfl, header=TRUE) head(chroms) pfl <- system.file("extdata","pmids.txt", package="UnderstandingRBioc") pmids <- read.table(pfl, header=TRUE) head(pmids) ## ------------------------------------------------------------------------ library(RSQLite) dbFile <- sprintf("%s.sqlite", tempfile()) dbFile con <- dbConnect(SQLite(), dbname=dbFile) ## ------------------------------------------------------------------------ sql <- "CREATE TABLE gene_info ( gene_id TEXT, gene_symbol TEXT, gene_name TEXT )" dbGetQuery(con, sql) ## ------------------------------------------------------------------------ dbListTables(con) ## ------------------------------------------------------------------------ dbListFields(con, name="gene_info") ## ------------------------------------------------------------------------ head(gene_names) ## ------------------------------------------------------------------------ sql <- "INSERT INTO gene_info VALUES ($gene_id, $symbol, $name)" dbBeginTransaction(con) dbGetPreparedQuery(con, sql, bind.data = gene_names) dbCommit(con) ## ------------------------------------------------------------------------ dbGetQuery(con, "CREATE Table metadata (name TEXT, value TEXT)") dbGetQuery(con, "INSERT INTO metadata VALUES ('Db type','myHamsterDb')") dbGetQuery(con, "INSERT INTO metadata VALUES ('Supporting package', 'AnnotationDbi')") ## ------------------------------------------------------------------------ dbGetQuery(con, "SELECT * FROM metadata") ## ------------------------------------------------------------------------ library(AnnotationDbi) setRefClass("myHamsterDb", contains="AnnotationDb") myHamster.db <- loadDb(dbFile) ## ------------------------------------------------------------------------ .cols <- function(x) { con <- AnnotationDbi:::dbConn(x) list <- dbListTables(con) unwanted <- c("metadata") list <- list[!list %in% unwanted] fields <- as.vector(sapply(list, dbListFields, con=con)) toupper(fields) } ## ------------------------------------------------------------------------ setMethod("columns", "myHamsterDb", function(x){.cols(x)}) ## ------------------------------------------------------------------------ columns(myHamster.db) ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT tx_name,tx_chrom FROM transcript LIMIT 4") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT * FROM splicing, exon WHERE splicing._exon_id=exon._exon_id LIMIT 4") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT * FROM transcript AS t, (SELECT * FROM splicing,exon WHERE splicing._exon_id=exon._exon_id) AS e WHERE e._tx_id=t._tx_id LIMIT 4") ## ------------------------------------------------------------------------ sql <- "CREATE TABLE chrom ( gene_id TEXT, chromosome TEXT )" dbGetQuery(con, sql) ## ------------------------------------------------------------------------ sql <- "CREATE TABLE pmid ( gene_id TEXT, pmid TEXT )" dbGetQuery(con, sql) ## ------------------------------------------------------------------------ sql <- "INSERT INTO chrom VALUES ($gene_id, $chromosome)" dbBeginTransaction(con) dbGetPreparedQuery(con, sql, bind.data = chroms) dbCommit(con) ## ------------------------------------------------------------------------ sql <- "INSERT INTO pmid VALUES ($gene_id, $pmid)" dbBeginTransaction(con) dbGetPreparedQuery(con, sql, bind.data = pmids) dbCommit(con) ## ------------------------------------------------------------------------ dbGetQuery(con, "SELECT * FROM chrom LIMIT 4") dbGetQuery(con, "SELECT * FROM pmid LIMIT 4") ## ----, eval=FALSE-------------------------------------------------------- ## dbGetQuery(con, "CREATE Table metadata (name TEXT, value TEXT)") ## dbGetQuery(con, "INSERT INTO metadata VALUES ('Db type','myHamsterDb')") ## dbGetQuery(con, "INSERT INTO metadata VALUES ('Supporting package', ## 'AnnotationDbi')") ## ------------------------------------------------------------------------ dbGetQuery(txcon, "SELECT * FROM metadata") ## ------------------------------------------------------------------------ setMethod("keytypes", "myHamsterDb", function(x){.cols(x)}) ## ------------------------------------------------------------------------ keytypes(myHamster.db)