# Installation of WebAPI and Atlas 

We describe how we installed WebAPI and Atlas

## System Configuration:

### OS
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
DISTRIB_DESCRIPTION="Ubuntu 18.04.5 LTS"

### Docker
Docker version 19.03.14

## Docker-compose
docker-compose version 1.25.4

### Java
openjdk 11.0.9.1 2020-11-04
OpenJDK Runtime Environment (build 11.0.9.1+1-Ubuntu-0ubuntu1.18.04)
OpenJDK 64-Bit Server VM (build 11.0.9.1+1-Ubuntu-0ubuntu1.18.04, mixed mode, sharing)

### Maven
Apache Maven 3.6.0
Maven home: /usr/share/maven
Java version: 1.8.0_275, vendor: Private Build, runtime: /usr/lib/jvm/java-8-openjdk-amd64/jre
Default locale: fr_FR, platform encoding: UTF-8
OS name: "linux", version: "5.4.0-58-generic", arch: "amd64", family: "unix"

### pgAdmin
Version
4.24
Copyright
Copyright (C) 2013 - 2020, The pgAdmin Development Team
Python Version
3.8.3 (default, May 20 2020, 20:46:20) [GCC 9.2.0]
Flask Version
1.0.2
Application Mode
Server

### postGres
postgres (PostgreSQL) 12.1 (Debian 12.1-1.pgdg100+1)

## WebAPI
We launched 3 services: PostgreSQL, pgadmin and tomcat:
```bash
docker-compose up -d
```

We followed the instructions to install WebAPI: https://github.com/OHDSI/WebAPI/wiki/WebAPI-Installation-Guide

### WebAPI Database
We used PostgreSQL for WebAPI database storage. pgAdmin was used to configure PostgreSQL.  
We created the different roles in the pgAdmin interface. 

#### pgAdmin interface
After login, create a new Server: 

* Name: ohdsi
* Host: webapi_postgres (container name, see docker-compose.yml)
* Port: 5432
* Username: postgres (by default, not an environment variable)
* Password: ${POSTGRES_PASSWORD}


### Build WebAPI

We cloned the github repository, moved to the latest release (v2.8.0)
```bash
git clone https://github.com/OHDSI/WebAPI.git
git checkout refs/tags/v2.8.0
```

We added 2 little fixes to WebAPI:
* https://github.com/OHDSI/WebAPI/issues/1730 (remove a ";" in a delete statement)
* https://github.com/OHDSI/WebAPI/issues/1731 added a Oracle driver to dependency
Our modifications can be found here: https://github.com/scossin/WebAPI

Then copy the WebAPIConfig to WebAPI folder:
```bash
cp ./WebAPIConfig ./WebAPI
```


Then enters the folder and compile the project with maven (don't forget to change the ohdsi_admin_user and ohdsi_app_user passwords):
```bash
mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P webapi-postgresql
```

Then we moved it to the tomcat: 
```bash
docker cp target/WebAPI.war webapitomcat:/usr/local/tomcat/webapps
docker logs webapitomcat -f # check no error connecting 
```

Check installation by going to: http://localhost:8080/WebAPI/info


### CMD Configuration

#### WebAPI
The next step (https://github.com/OHDSI/WebAPI/wiki/CDM-Configuration) is to configure WebAPI to connect to:
* CDM: containing patient level data
* Vocab: OMOP vocabulary
* Results: cohort results
* Temp: temp table

In our configuration, CDM and Vocab are in the same schema (OMOP), Results are in the OMOP_COHORT schema and TEMP in the OMOP_TEMP schema. The OMOP_COHORT user can read the OMOP schema but it can't write in the TEMP schema. So only 2 schemas are used by WebAPI:  
* OMOP_COHORT to write Results and Temp tables
* OMOP (patient level data)

We go back to pgAdmin to enter data in the webapi.source and webapi.source_daimon tables.  
The following commands were executed:

```sql
TRUNCATE TABLE webapi.source CASCADE
TRUNCATE TABLE webapi.source_daimon CASCADE

-- SOURCE OMOP_COHORT
INSERT INTO webapi.source (source_id, source_name, source_key, source_connection, source_dialect, username, password) 
SELECT nextval('webapi.source_sequence'), 'OMOP_COHORT', 'OMOP_COHORT', 'jdbc:oracle:thin:@{IP}:{PORT}:{DATABASE}', 'oracle','OMOP_COHORT','{PASSWORD}';

-- CDM (OMOP schema)
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
SELECT nextval('webapi.source_sequence'), source_id, 0, 'OMOP', 0
FROM webapi.source
WHERE source_key = 'OMOP_COHORT'
;

-- VOCAB (OMOP schema)
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
SELECT nextval('webapi.source_sequence'), source_id, 1, 'OMOP', 1
FROM webapi.source
WHERE source_key = 'OMOP_COHORT'
;

-- RESULTS (OMOP_COHORT schema)
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
SELECT nextval('webapi.source_sequence'), source_id, 2, 'OMOP_COHORT', 1
FROM webapi.source
WHERE source_key = 'OMOP_COHORT'
;

-- TEMP (OMOP_COHORT schema)
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) 
SELECT nextval('webapi.source_sequence'), source_id, 5, 'OMOP_COHORT', 0
FROM webapi.source
WHERE source_key = 'OMOP_COHORT'
;

-- check:
SELECT * FROM webapi.source;
SELECT * FROM webapi.source_daimon;
```

Check sources: http://localhost:8080/WebAPI/source/sources

Make a backup of the database: 
* in the pgAdmin interface, right click on ohdsi "Backup Globals", save the file
* retrieve the backup file in the pgAdmin container
* move the file to the postgres container to restore a backup
* execute the following command: 
```bash
psql -U postgres -d ohdsi < OHDSI_BACKUP
```

#### Oracle

(This script is handy to delete all tables in a schema:)
```sql
SELECT 'DROP TABLE "' || TABLE_NAME || '" CASCADE CONSTRAINTS;' FROM user_tables;
```

By going to: http://localhost:8080/WebAPI/ddl/results?dialect=oracle&schema=OMOP_COHORT&vocabSchema=OMOP&tempSchema=OMOP_TEMP&initConceptHierarchy=true  
We get the Oracle scripts to execute to create new tables in our OMOP_COHORT and OMOP_TEMP schema

We tried to execute all the commands in Oracle SQL developer but we got an error because of the BEGIN-END syntax. We needed to replace all 
```sql
END;
```

by 

```sql
END; -- newline and /
/
```
Then, we needed to split the commands concerning the OMOP_COHORT schema and the OMOP_TEMP schema. 
Excecuting the script was very long (multiple hours).

## Atlas
We cloned the github repository, moved to the latest release (v2.8.0)
```bash
git clone https://github.com/OHDSI/Atlas.git
git checkout refs/tags/v2.8.0
```

### Local configuration
Only nginx service is exposed with basic authentification required. WebAPI is access through Nginx with reverse proxy (default.conf):   
```
    location /WebAPI {
        proxy_set_header Accept-Encoding "";
        proxy_pass http://webapitomcat:8080;
    } 
```
Our config-local.js file looks like this:

```js
define([], function () {
	var configLocal = {};

	configLocal.api = {
		name: 'EDS CHU Bordeaux',
		url: 'http://{HOSTNAME}:{NGINX_PORT}/WebAPI/' 
		// in production: 'https://eds.chu-bordeaux.fr/WebAPI/'
		// in development: 'http://iam:5000/WebAPI'
	};
	return configLocal;
});
```
where  
* {HOSTNAME} is the name of the server where the docker-compose services are deployed 
* {NGINX_PORT} is the NGINX port set in the .env file

move this configuration file to the Atlas folder:
```bash
npm run build
tar -zcf atlas.tar.gz *
sudo mv atlas.tar.gz ../atlas/ # move it to the nginx volume 
cd ../atlas/
sudo tar -xf atlas.tar.gz  # deploy  
```