## ------------------------------------------------------------------------
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)