---
title: "Using a MySQL server backend"
graphics: yes
output:
  BiocStyle::html_document2
vignette: >
  %\VignetteIndexEntry{Using a MySQL server backend}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
  %\VignetteDepends{ensembldb,EnsDb.Hsapiens.v75,BiocStyle}
  %\VignettePackage{ensembldb}
  %\VignetteKeywords{annotation,database}
---

**Package**: `r BiocStyle::Biocpkg("ensembldb")`<br />
**Authors**: `r packageDescription("ensembldb")$Author`<br />
**Modified**: 20 September, 2016<br />
**Compiled**: `r date()`

# Introduction

`ensembldb` uses by default, similar to other annotation packages in Bioconductor,
a SQLite database backend, i.e. annotations are retrieved from file-based SQLite
databases that are provided *via* packages, such as the `EnsDb.Hsapiens.v75`
package. In addition, `ensembldb` allows to switch the backend from SQLite to
MySQL and thus to retrieve annotations from a MySQL server instead. Such a setup
might be useful for a lab running a well-configured MySQL server that would
require installation of EnsDb databases only on the database server and not on
the individual clients.

**Note** the code in this document is not executed during vignette generation as
this would require access to a MySQL server.

# Using `ensembldb` with a MySQL server

Installation of `EnsDb` databases in a MySQL server is straight forward - given
that the user has write access to the server:

```{r eval=FALSE}
library(ensembldb)
## Load the EnsDb package that should be installed on the MySQL server
library(EnsDb.Hsapiens.v75)

## Call the useMySQL method providing the required credentials to create
## databases and inserting data on the MySQL server
edb_mysql <- useMySQL(EnsDb.Hsapiens.v75, host = "localhost", user = "userwrite",
		      pass = "userpass")

## Use this EnsDb object
genes(edb_mysql)
```

To use an `EnsDb` in a MySQL server without the need to install the corresponding
R-package, the connection to the database can be passed to the `EnsDb` constructor
function. With the resulting `EnsDb` object annotations can be retrieved from the
MySQL database.

```{r eval=FALSE}
library(ensembldb)
library(RMySQL)

## Connect to the MySQL database to list the databases.
dbcon <- dbConnect(MySQL(), host = "localhost", user = "readonly",
		   pass = "readonly")

## List the available databases
listEnsDbs(dbcon)

## Connect to one of the databases and use that one.
dbcon <- dbConnect(MySQL(), host = "localhost", user = "readonly",
		   pass = "readonly", dbname = "ensdb_hsapiens_v75")
edb <- EnsDb(dbcon)
edb
```