###################################################
### chunk number 1: setup
###################################################
#line 18 "SQLiteInRExercises.Rnw"
options(width = 40)


###################################################
### chunk number 2: createDB
###################################################
#line 67 "SQLiteInRExercises.Rnw"
  library("RSQLite")
  drv <- SQLite()
  con <- dbConnect(drv, dbname="metadata.sqlite")
  dbGetQuery(con,
             "CREATE TABLE subjects
                (id INTEGER PRIMARY KEY,
                 subject_id TEXT UNIQUE NOT NULL,
                 case_control TEXT NOT NULL)")
  dbGetQuery(con,
             "CREATE TABLE snps
                (id INTEGER PRIMARY KEY,
                 seqnames TEXT,
                 start INTEGER,
                 gene_id TEXT,
                 snp_id TEXT UNIQUE NOT NULL)")


###################################################
### chunk number 3: popTables
###################################################
#line 114 "SQLiteInRExercises.Rnw"
  subjects <- read.csv(system.file("extdata", "subjects.csv",
                       package="AdvancedR2011Data"), stringsAsFactors=FALSE)
  subSQL <- "INSERT INTO subjects VALUES ($id,$subject_id,$case_control)"
  dbBeginTransaction(con)
  dbGetPreparedQuery(con, subSQL, bind.data = subjects)
  dbCommit(con)    
                       
  snps <- read.csv(system.file("extdata", "snps.csv",
                   package="AdvancedR2011Data"), stringsAsFactors=FALSE)
  snpSQL <- "INSERT INTO snps VALUES ($id,$seqnames,$start,$gene_id,$snp_id)"
  dbBeginTransaction(con)
  dbGetPreparedQuery(con, snpSQL, bind.data = snps)
  dbCommit(con)


###################################################
### chunk number 4: createIndices
###################################################
#line 167 "SQLiteInRExercises.Rnw"
  dbGetQuery(con, "CREATE index sub_subject_idx ON subjects (subject_id)")
  
  dbGetQuery(con, "CREATE index snp_gene_idx ON snps (gene_id)")
  dbGetQuery(con, "CREATE index snp_snp_idx ON snps (snp_id)")


###################################################
### chunk number 5: dbDisconnect
###################################################
#line 179 "SQLiteInRExercises.Rnw"
dbDisconnect(con)


###################################################
### chunk number 6: createFunctions
###################################################
#line 204 "SQLiteInRExercises.Rnw"
dbPath <- system.file("extdata","metadata.sqlite",package="AdvancedR2011Data")
  getSubjects <- function(dbPath){
    drv <- SQLite()
    con <- dbConnect(drv, dbname=dbPath)
    sql <- "SELECT * FROM subjects ORDER BY id"
    res <- dbGetQuery(con, sql)  
    dbDisconnect(con)
    res
  }

  su <- getSubjects(dbPath)

  getSnps <- function(dbPath){
    drv <- SQLite()
    con <- dbConnect(drv, dbname=dbPath)
    sql <- "SELECT * FROM snps ORDER BY id"
    res <- dbGetQuery(con, sql)
    dbDisconnect(con)
    res
  }
  
  sn <- getSnps(dbPath)



###################################################
### chunk number 7: getKEGGSnps
###################################################
#line 267 "SQLiteInRExercises.Rnw"
  getKEGGSnps <- function(dbPath){
    drv <- SQLite()
    con <- dbConnect(drv, dbname=dbPath)
    require("org.Hs.eg.db")
    orgPath <- system.file("extdata", "org.Hs.eg.sqlite",
                       package="org.Hs.eg.db")
    ## Attach the foreign db from the dbPath
    dbGetQuery(con, sprintf("ATTACH '%s' AS db", orgPath))
    ## Then use that in the query                                     
    sql <- paste("SELECT * FROM db.kegg AS k, db.genes as g, snps as s
                   WHERE k._id=g._id AND g.gene_id=s.gene_id")
    res <- dbGetQuery(con, sql)
    dbDisconnect(con)
    res
  }

  keggs <- getKEGGSnps(dbPath)



###################################################
### chunk number 8: cleanup
###################################################
#line 342 "SQLiteInRExercises.Rnw"
 file.remove("metadata.sqlite")


###################################################
### chunk number 9: SessionInfo
###################################################
#line 348 "SQLiteInRExercises.Rnw"
sessionInfo()