---
title: "Installation Guide"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{installation_guide}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

```{r setup}
library(timeseriesdb)
```

## Starting Point

This guides assumes you are familiar with your operating system and the 
R environment. In another words we assume you have basic R, basic PostgreSQL as well 
as basic unix shell know-how in case you work on Linux / OSX. 

Also, {timeseriesdb}'s works well inside a docker container which is probably
the most convenient setup for testing purposes if you're familiar with docker. 
However, docker know-how is not a prerequisite for running and installing 
{timeseriesdb} by any means. 

## Installation of the R Package

Installation of the {timeseriesdb} R package is as straight forward as for any other R package

from CRAN:

```{r, eval=FALSE}
install.packages("timeseriesdb")
```

or for the latest developer version from github (assuming you have the {remotes} package installed):

```{r, eval=FALSE}
remotes::install_github("mbannert/timeseriesdb")
```

{timeseriesdb} ships we installation routines to help you set up the database relations. Still, the database itself  needs to be there first and you need 
proper access rights before you can install the {timeseriesdb} database backend.


## PostgreSQL 

{timseriesdb} uses the PostgreSQL RDMBS as a database backend. 
You can either use {timeseriesdb} with a remote database running on, e.g., 
[Google Cloud](https://cloud.google.com/sql/docs/postgres#:~:text=Cloud%20SQL%20for%20PostgreSQL%20is,databases%20on%20Google%20Cloud%20Platform.), [AWS](https://aws.amazon.com/rds/postgresql/), [Heruko](https://www.heroku.com/postgres) and many more, or you can simply install PostgreSQL locally. In both cases the [PostgreSQL docker images](https://hub.docker.com/_/postgres)
are a hassle-free solution, in particular if you just want to try out timeseriesdb.
Alternatively, just install [PostgreSQL on your local machine](https://www.postgresql.org/download/). To verify that your installation worked make sure the database is running and create 
a basic connection. 


### Dedicated Schema and Extensions

Create a SCHEMA that is dedicated to your {timeseriesdb} instance. 
The PostgreSQL hierarchy looks like this database cluster > database > schema > relation (table). 

Creating a schema is simply a matter of 

```sql

CREATE SCHEMA IF NOT EXISTS timeseries;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS btree_gist;

```

as long as you have the rights to do so. Also, don't forget to install the *uid* and *btree* extension which are needed for the unique identifiers and the enforcement of constraints {timeseriesdb} makes use of. 

### Admin and Roles

To create a basic set of roles run the following SQL code. The below code will 
create a *reader* and *writer* role as well as an admin role and three levels of
access: *public*, *main* and *restricted*.

```sql
CREATE ROLE timeseries_reader NOLOGIN;
CREATE ROLE timeseries_writer NOLOGIN;
GRANT timeseries_reader TO timeseries_writer;

CREATE ROLE timeseries_access_public;
CREATE ROLE timeseries_access_main;
CREATE ROLE timeseries_access_restricted;
GRANT timeseries_access_public TO timeseries_access_main;
GRANT timeseries_access_main to timeseries_access_restricted;

CREATE ROLE timeseries_admin NOLOGIN;
GRANT timeseries_writer TO timeseries_admin;
GRANT timeseries_access_restricted TO timeseries_admin;

```

In order to setup all the relations you will need using R, create an 
admin user in advance who gets assigned the admin role created above: 

```sql
-- public/private distinction does not really make sense for admins
CREATE ROLE tsdb_admin WITH LOGIN PASSWORD 'tsdb_admin'; 
GRANT timeseries_admin TO tsdb_admin;
GRANT CREATE, USAGE ON SCHEMA timeseries TO tsdb_admin;

```

## Finalize Setup: Create Tables using R

Given proper access rights and a proper connection, installation of the database
backend from R is just a matter of: 

```{r, eval=FALSE}
library(timeseriesdb)
con <- db_connection_create("yourdbname",
                            user = "tsdb_admin",
                            host = "localhost")
install_timeseriesdb(con)
```

YAY! You're all set up. Store a time series to your fresh new database.

```{r, eval=FALSE}
tsl <- list(AirPassengers = AirPassengers)
db_ts_store(con, tsl)
```


## Docker Development Setup

If you're familiar with docker, the easiest way to play around with 
{timeseriesdb} is to run the bash script that ship with {timeseriesdb}'s
source code. (Note: depending on your OS and configuration, e.g., on some
Ubuntu systems you’ll need use sudo to run docker.)

```
./start_docker_dev.sh
```

If you don’t know where R package installation directory is, simply find out by running

```{r, eval=FALSE}
system.file(package = "timeseriesdb")
```

or download the {timeseriesdb} package source from CRAN or GitHub.
This will fire up a docker container based on a postgresql:latest docker image
and set up {timeseriesdb} so you could connect to the database running inside the
container like this: 

```{r, eval=FALSE}
con <- db_connection_create(
  dbname = "postgres",
  user = "dev_admin",
  host = "localhost",
  passwd = "dev_admin",
  port = 1111
)
```