%\VignetteDependsAdvancedR2011Data{}
%\VignetteIndexEntry{SQLite Exercises}
%\VignettePackage{AdvancedR2011}

\documentclass{article}

\usepackage{BioconductorVignette}
\SweaveOpts{keep.source=TRUE,width=9,height=8,eps=FALSE,include=FALSE}

\title{SQL in R, Self-Study Exercises}
\author{Marc Carlson \\ Fred Hutchinson Cancer Research Center}
\date{17-18 February, 2011}


\begin{document}
\maketitle
<<setup, echo=FALSE>>=
options(width = 40)
@ 


\section{Introduction} 
This set of Exercises is intended to give you practice at using and
building databases with SQL from R.  You will get to practice building
a small database, populate it with data, and then write functions that
can retrieve data from it.  You will even write a function to connect
your data to other databases so that you can take full advanatage of
Bioconductors modular system of databases.  The code that you write in
these exercises is meant to be used later in subsequent exercises, so
try to make sure that you understand this material since you will
build on it later.


\section{Exercises} 

\begin{Ext} % Make a database from the 2 included files (2 tables)
  For the 1st exercise you need to make a database that contains 2
tables.  Create a database called metadata.sqlite and populate two tables
in that DB.  There are two .csv files included in the extdata
directory of the AdvancedR2011 course that can be located with a call
to system.file.  Read these files into R using read.csv and then
populate the database with two tables called subjects and snps
respectively.  While you do so, there will be some data in the snp
information that you don't need.  Be sure to exclude this or it will
make your life more difficult later.  You will need the following
fields of data from each file:


  From subjects.csv: id, subject\_id, case\_control

  
  From snps.csv: id, seqnames, start, gene\_id and snp\_id

  
\end{Ext}

\begin{Question}
  Using the information above, write an R and SQL script to create the
two-table database described above and then run it.  When you are
done, you should have an empty database with two tables.  You should then
check the contents of your database usinge dbListTables() and
dbListFields().
\end{Question}
  
\begin{Solution}
<<createDB, results=hide,echo=FALSE>>=
  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)")
@ 
  

\end{Solution}




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



\begin{Ext} %  Populate tables
  Next we need to put the data from the data.frames in R into our new tables
\end{Ext}

\begin{Question}
    Using the information above from the descriptions above, populate
your two tables selectively with the contents from the two .csv files
provided.  Remember that we created tables to hold only some of the
data from these files.  So we need to only grab data from the files that matches the fields we want to use. 
    
  From subjects.csv: ID, subjectID, disease

  
  From snps.csv: ID, seqnames, start, gene\_id and snpIds


\end{Question}
  
\begin{Solution}
<<popTables, results=hide,echo=FALSE>>=
  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)
@



\end{Solution}




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



\begin{Ext} % create the indices
  For the purposes of efficient data retrieval later on, you may also
want to create indices on any fields that contain a legitimate
identifier.  This is probably a good idea because real identifiers can
potentially be used in another context to join to other data, and
having such information indexed can vastly speed up these joining
operations.  You have to be aware though that there is still no such
thing as a free lunch.  Adding these indices speeds up joins at the
cost of a database taking up additional storage space.  For this
example, we are not very concerned about the space requirements for
this small database, so lets plan to put indices on to anything that
looks like it could be used as a legitimate identifier later on.

Please also be aware that any primary keys declared when you initially
defined a table will already be indexed by SQLite, so if you specified
that in your initial create table statement, you won't need to do it
again.  It is possible to double (or, triple etc.) the space required
by an index by defining it multiple times unecessarily.
\end{Ext}

\begin{Question}
    Using the information above, create indices for the tables you
have populated for any field that is called an "id".
\end{Question}
  
\begin{Solution}
<<createIndices, results=hide,echo=FALSE>>=
  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)")
@ 
  
\end{Solution}

So now that we are done with the task of making the database, we can close
the connection to it:

<<dbDisconnect>>=
dbDisconnect(con)
@



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



\begin{Ext} % Write some simple getters to access the data
  Most R users are not familiar with SQL and the intricacies of
interacting with a relational database.  So it is usually a good idea
to provide useful accessor method to get certain data into R in a
desired format.
\end{Ext}

\begin{Question}
  Place your new database into the extdata directory of your package
and install it.  Then write a pair of simple accessor functions to
retrieve the contents of each table called getSnps() and
getSubjects().
\end{Question}
  
\begin{Solution}
<<createFunctions,results=hide,echo=FALSE>>=
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)

@
  
\end{Solution}




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%




\begin{Ext} % Connect the SNP data other data about the SNPs - write a function to do this for KEGG PATH Ids
  Sometimes it is useful to connect to another Database to get more
information.  For this exercise you need to join to another database
to find out what kegg pathway IDs can be associated with the snps that
you have in the "snps" table.

In this case, the database you want to use is the org.Hs.eg.sqlite
databased from the human organism package org.Hs.eg.db.  The
org.Hs.eg.db package uses the "genes" table as it's central table,
while the KEGG information you need from it is located in the "kegg"
table.  To retrieve this data, you will have to BOTH join to the genes
table and also to the kegg table.  This is because in order to get the
kegg pathway IDs associated with the entrez gene IDs, the genes table
has to be joined to the kegg table by using the internal "\_id" that
these two tables share.  So ultimately this task will involve joing
three tables: "snps" (local), "genes", and "kegg".
\end{Ext}

\begin{Question}
  Using the entrez gene IDs in the snps table as a foreign key, write
a function to pull information from the org.Hs.eg.sqlite database
found in the org.Hs.eg.dbs package.  You can use system.file() to
access this other database from the extdata directory in it's
respective package.  
\end{Question}
  
\begin{Solution}
<<getKEGGSnps,results=hide,echo=FALSE>>=
  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)

@
  
\end{Solution}



%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



% \begin{Ext} 
%   The above function creates a dependency on another database, so it may
% be desirable to check that the other database we are depending on is
% current.  The dates for the recent version of the org.Hs.eg.db
% database can be discovered by checking the contents of the metadata
% table inside of the org.Hs.eg.sqlite database.  For R 2.12, the
% version of these KEGG pathway IDs has a "KEGGSOURCEDATE" of
% "2010-Sep7", which means that this data was pulled down from KEGG at
% that time.  Choosing annotations from earlier or later than that time
% might make results less reproducible and could confound the analysis.
% Ideally, annotations should be updated periodically, but you always
% want to know about it when it happens.  IOW, you never really want
% annotation updates to be a surprise.
% \end{Ext}

% \begin{Question}
% Write a function that checks this metadata table and reports whether
% or not the results are equivalent to the version needed for use with R
% 2.12.
% \end{Question}
  
% \begin{Solution}
% <<checkMetadata,results=hide,echo=FALSE>>=
%   checkKEGGMeta <- function(){
%     drv <- SQLite()
%     require("org.Hs.eg.db")
%     con <- dbConnect(drv, dbname=system.file("extdata", "org.Hs.eg.sqlite", 
%                                              package = "org.Hs.eg.db"))
%     sql <- "SELECT value FROM metadata WHERE name='KEGGSOURCEDATE';"
%     res <- dbGetQuery(con, sql)
%     dbDisconnect(con)
%     if (res=="2010-Sep7"){return(TRUE)}else{return(FALSE)}   
%   }
%   checkKEGGMeta()
% @
  
% \end{Solution}




%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%



%% just to clean up after ourselves 
<<cleanup,results=hide,echo=FALSE>>=
 file.remove("metadata.sqlite")
@ 

The version number of R and packages loaded for generating the vignette were:

<<SessionInfo, echo=FALSE>>=
sessionInfo()
@



\end{document}
