/usr/lib/R/site-library/ensembldb/doc/MySQL-backend.Rmd is in r-bioc-ensembldb 2.2.2-1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 | ---
title: "Using a MySQL server backend"
author: "Johannes Rainer"
package: ensembldb
output:
BiocStyle::html_document:
toc_float: true
vignette: >
%\VignetteIndexEntry{Using a MySQL server backend}
%\VignetteEngine{knitr::rmarkdown}
%\VignetteEncoding{UTF-8}
%\VignetteDepends{ensembldb,EnsDb.Hsapiens.v75,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.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
```
|