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