1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
|
---
title: "Using a MySQL server backend"
author: "Johannes Rainer"
package: ensembldb
output:
BiocStyle::html_document:
toc_float: true
vignette: >
%\VignetteIndexEntry{Using a MariaDB/MySQL server backend}
%\VignetteEngine{knitr::rmarkdown}
%\VignetteEncoding{UTF-8}
%\VignetteDepends{ensembldb,EnsDb.Hsapiens.v86,BiocStyle}
---
# 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.v86`
package. In addition, `ensembldb` allows to switch the backend from SQLite to
MariaDB/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.v86)
## Call the useMySQL method providing the required credentials to create
## databases and inserting data on the MySQL server
edb_mysql <- useMySQL(EnsDb.Hsapiens.v86, 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(RMariaDB)
## Connect to the MySQL database to list the databases.
dbcon <- dbConnect(MariaDB(), host = "localhost", user = "readonly",
pass = "readonly")
## List the available databases
listEnsDbs(dbcon)
## Connect to one of the databases and use that one.
dbcon <- dbConnect(MariaDB(), host = "localhost", user = "readonly",
pass = "readonly", dbname = "ensdb_hsapiens_v75")
edb <- EnsDb(dbcon)
edb
```
# Session information
```{r sessionInfo }
sessionInfo()
```
|
