User Tools

Site Tools


documentation:software:webapi:webapi_installation_guide

IMPORTANT NOTE

All WebAPI documentation has moved to GitHub. Please disregard the content below as it is legacy and kept for posterity.

WebAPI Installation Guide (LEGACY)

Overview


This page describes the database setup, maven build configuration and deployment of OHDSI/WebAPI to a Apache Tomcat environment. This application is Java-based, packaged as a WAR, and should be able to be deployed into any Java servlet container.

Database Preparation


MS SQL Server


Please refer to the SQL Server Setup Guide article for installation and setup details.

PostgreSQL 9.3


Please refer to the PostgreSQL Setup Guide article for installation and setup details.

Oracle 11g XE


Please refer to the Oracle Setup Guide article for installation and setup details.

Installing WebAPI


Installing Maven


Download Maven binary zip from https://maven.apache.org/download.cgi Unzip to Program Files folder Add Maven bin folder to system path Make sure JAVA_HOME is set (Note: You will not be able to build multi-architecture R projects when JAVA_HOME is set)

Cloning the WebAPI project


Clone the WebApi project to a local folder, e.g.:

git clone https://github.com/OHDSI/WebAPI.git

WebAPI Configuration


Each database platform will have their own JDBC driver class name and connection string URL. The following sections describes how PostgreSQL, MSSql and Oracle could be configured to connect to the DB server.

Microsoft SQL Server

Create settings.xml File

Specify user name, password, and location of the OHDSI database (this was created as ‘ohdsi_app_user’ from the SQL Server Setup Guide. Note that the user should have read, write, and create privileges on the OHDSI database. Here is an example XML that is based on the above configuration:

<settings>
<profiles>
  <profile>
    <id>webapi-mssql</id>
    <properties>
      <datasource.driverClassName>com.microsoft.sqlserver.jdbc.SQLServerDriver</datasource.driverClassName>
      <datasource.url>jdbc:sqlserver://localhost;databaseName=OHDSI</datasource.url>
      <datasource.username>ohdsi_app_user</datasource.username>
      <datasource.password>app1</datasource.password>
      <datasource.dialect>sql server</datasource.dialect>
      <datasource.ohdsi.schema>dbo</datasource.ohdsi.schema>
      <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
      <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
      <flyway.datasource.username>ohdsi_app_user</flyway.datasource.username>
      <flyway.datasource.password>app1</flyway.datasource.password>
      <flyway.locations>classpath:db/migration/sqlserver</flyway.locations>
      <security.enabled>false</security.enabled>
      <security.token.expiration>43200</security.token.expiration>
      <security.origin>*</security.origin>
      <security.ssl.enabled>false</security.ssl.enabled>
      <security.oauth.callback.ui>http://localhost/atlas/#/welcome</security.oauth.callback.ui>
      <security.oauth.callback.api>http://localhost:8080/WebAPI/user/oauth/callback</security.oauth.callback.api>
      <security.oauth.google.apiKey></security.oauth.google.apiKey>
      <security.oauth.google.apiSecret></security.oauth.google.apiSecret>
      <security.oauth.facebook.apiKey></security.oauth.facebook.apiKey>
      <security.oauth.facebook.apiSecret></security.oauth.facebook.apiSecret>
    </properties> 
  </profile>  
</profiles>
</settings>

Note: this file above is saved as /WebAPIConfig/settings.xml and will be referred to in the “Building the .war file section”.

Download the appropriate JDBC driver

For SQL Server, the driver is available from the Maven repository, so no additional steps are required.

PostgreSQL

Create settings.xml File

Specify user name, password, and location of the OHDSI schema (this was created as ‘webapi’ from the PostgreSQL Setup Guide. Note that the user should have read, write, and create privileges on the OHDSI schema. Here is an example XML that is based on the above configuration:

<settings>
<profiles>
  <profile>
    <id>webapi-postgresql</id>
    <properties>
      <datasource.driverClassName>org.postgresql.Driver</datasource.driverClassName>
      <datasource.url>jdbc:postgresql://localhost:5432/OHDSI</datasource.url>
      <datasource.username>ohdsi_app_user</datasource.username>
      <datasource.password>app1</datasource.password>
      <datasource.dialect>postgresql</datasource.dialect>
      <datasource.ohdsi.schema>webapi</datasource.ohdsi.schema>
      <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
      <flyway.datasource.url>${datasource.url}</flyway.datasource.url>
      <flyway.datasource.username>ohdsi_admin_user</flyway.datasource.username>
      <flyway.datasource.password>!PASSWORD!</flyway.datasource.password>
      <flyway.locations>classpath:db/migration/postgresql</flyway.locations>
      <security.enabled>false</security.enabled>
      <security.token.expiration>43200</security.token.expiration>
      <security.origin>*</security.origin>
      <security.ssl.enabled>false</security.ssl.enabled>
      <security.oauth.callback.ui>http://localhost/atlas/#/welcome</security.oauth.callback.ui>
      <security.oauth.callback.api>http://localhost:8080/WebAPI/user/oauth/callback</security.oauth.callback.api>
      <security.oauth.google.apiKey></security.oauth.google.apiKey>
      <security.oauth.google.apiSecret></security.oauth.google.apiSecret>
      <security.oauth.facebook.apiKey></security.oauth.facebook.apiKey>
      <security.oauth.facebook.apiSecret></security.oauth.facebook.apiSecret>
    </properties> 
  </profile>  
</profiles>
</settings>

Note: this file above is saved as /WebAPIConfig/settings.xml and will be referred to in the “Building the .war file section”.

Download the appropriate JDBC driver

For PostgreSQL, the driver is available from the Maven repository, so no additional steps are required.

Oracle

Create settings.xml File Specify user name, password, and location of the OHDSI schema (this was created as ‘ohdsi’ in the Oracle Setup Guide). Note that the user should have read, write, and create privileges on the OHDSI schema because this user is the owner of the 'ohdsi' schema. Here is an example XML that is based on the above configuration:

<settings>
  <profile>
    <id>webapi-oracle</id>
    <properties>             
      <datasource.driverClassName>oracle.jdbc.OracleDriver</datasource.driverClassName>
     <datasource.url>jdbc:oracle:thin:@//127.0.0.1:1521/xe</datasource.url>
      <datasource.username>ohdsi</datasource.username>
      <datasource.password>{password}</datasource.password>
      <datasource.dialect>oracle</datasource.dialect>
      <datasource.ohdsi.schema>OHDSI</datasource.ohdsi.schema>
      <flyway.datasource.driverClassName>${datasource.driverClassName}</flyway.datasource.driverClassName>
      <flyway.datasource.url>jdbc:oracle:thin:@//127.0.0.1:1521/xe</flyway.datasource.url>
      <flyway.datasource.username>ohdsi</flyway.datasource.username>
      <flyway.datasource.password>{password}</flyway.datasource.password>
      <flyway.locations>classpath:db/migration/oracle</flyway.locations>
      <security.enabled>false</security.enabled>
      <security.token.expiration>43200</security.token.expiration>
      <security.origin>*</security.origin>
      <security.ssl.enabled>false</security.ssl.enabled>
      <security.oauth.callback.ui>http://localhost/atlas/#/welcome</security.oauth.callback.ui>
      <security.oauth.callback.api>http://localhost:8080/WebAPI/user/oauth/callback</security.oauth.callback.api>
      <security.oauth.google.apiKey></security.oauth.google.apiKey>
      <security.oauth.google.apiSecret></security.oauth.google.apiSecret>
      <security.oauth.facebook.apiKey></security.oauth.facebook.apiKey>
      <security.oauth.facebook.apiSecret></security.oauth.facebook.apiSecret>
    </properties>
  </profile>   
</profiles>
</settings>

Note: this file above is saved as /WebAPIConfig/settings.xml and will be referred to in the “Building the .war file” section.

Download the Oracle JDBC driver You will need to download the Oracle JDBC driver, and install it into your local maven repository. Once you download the jar, you will need to execute the following maven command to install it into the local repository. The simplest way is to navigate via the CLI to the directory the JAR was downloaded to, and execute the following command:

mvn install:install-file -Dfile=ojdbc.jar -DgroupId=ojdbc -DartifactId=ojdbc -Dversion=6.0.0 -Dpackaging=jar

(The above is all a single line command.)

Building the .war file

Open command prompt, go to the WebAPI folder, type

set JAVA_HOME=C:/Program Files/Java/jdk1.8.0_112

Make sure you point to a JDK, not a JRE. It is probably a good idea to stick to the same Java version that is running Tomcat

mvn clean package -DskipTests -s WebAPIConfig/settings.xml -P {profile id}

This will create the file WebAPI.war in the target subfolder. Note: {profile id} is set to the value of the profile ID from the example configuration. The valid values for this are 'webapi-postgresql', 'webapi-mssql' or 'webapi-oracle'. Use the proper profile id for your database environment.

Deploy the war file


In Tomcat, you will need to increase the maximum file size allowed for WAR files. Go to webapps/manager/WEB-INF/web.xml and then increase the max-file-size and max-request-size to at least the size of the WAR file.

<multipart-config>
  <max-file-size></max-file-size>
  <max-request-size></max-request-size>
 <file-size-threshold>0</file-size-threshold>
</multipart-config>

In Tomcat (e.g. using the manager app), deploy the war file.

This should automatically create a large number of tables in the webapi schema.

Verify Application


Reading the Tomcat logs, the following output should appear indicating that the tables have been created (in this example, we see an Oracle output):

2016-02-24 11:01:08.148 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.dbsupport.DbSupportFactory -  - Database: jdbc:oracle:thin:@//127.0.0.1:1521/xe (Oracle 11.2)
2016-02-24 11:01:08.358 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.metadatatable.MetaDataTableImpl -  - Creating Metadata table: "OHDSI"."schema_version"
2016-02-24 11:01:08.468 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Current version of schema "OHDSI": << Empty Schema >>
2016-02-24 11:01:08.468 WARN org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - outOfOrder mode is active. Migration of schema "OHDSI" may not be reproducible.
2016-02-24 11:01:08.468 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.1
2016-02-24 11:01:08.608 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.2
2016-02-24 11:01:08.628 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.3
2016-02-24 11:01:08.668 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.3.1
2016-02-24 11:01:08.778 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.3.2
2016-02-24 11:01:08.798 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.4
2016-02-24 11:01:08.987 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.4.1
2016-02-24 11:01:09.007 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.4.2
2016-02-24 11:01:09.027 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.4.3
2016-02-24 11:01:09.068 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.5
2016-02-24 11:01:09.148 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.5.1
2016-02-24 11:01:09.168 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.6.1
2016-02-24 11:01:09.348 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.6.2
2016-02-24 11:01:09.750 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.6.3
2016-02-24 11:01:09.780 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.7.0
2016-02-24 11:01:09.840 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.7.1
2016-02-24 11:01:09.920 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.7.2
2016-02-24 11:01:10.020 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.0.8
2016-02-24 11:01:10.060 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.1.0
2016-02-24 11:01:10.090 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.1.1
2016-02-24 11:01:10.130 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Migrating schema "OHDSI" to version 1.0.1.1.1
2016-02-24 11:01:14.482 INFO org.ohdsi.webapi.WebApi.main() org.flywaydb.core.internal.command.DbMigrate -  - Successfully applied 21 migrations to schema "OHDSI" (execution time 00:06.124s).

Other platforms should see similar output, except for the JDBC url in the connection. Use your DB platform's administrator tool to verify tables now exist in the schema.

Configure source and source_daimon tables


The webapi.source and webapi.source_daimon tables were created when you started the tomcat service with the WebAPI war deployed. However, these tables must be populated with a source and daimons for CDM, Vocabulary and Results must be added to the source in order to use the OHDSI tools. For this example it is assumed that the CDM and Vocabulary exist as a separate schema in the same database instance. The CDM/Vocabulary tables are in the schema ‘cdm’ and the ohdsi tables are installed in the 'ohdsi' schema.

WebAPI SOURCE and SOURE_DAIMON Inserts


INSERT INTO ohdsi.source (source_id, source_name, source_key, source_connection, source_dialect) VALUES (1, 'My Cdm', 'MY_CDM', ' jdbc:oracle:thin:ohdsi/{password}@127.0.0.1:1521/xe', 'oracle');
INSERT INTO ohdsi.source (source_id, source_name, source_key, source_connection, source_dialect) VALUES (2, 'Default vocabulary', 'vocab', 'jdbc:oracle:thin:ohdsi/{password}@127.0.0.1:1521/xe', 'oracle');
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (1,1,0, 'cdm', 0);
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (2,1,1, 'cdm', 0);
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (3,1,2, 'ohdsi', 0);
INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (4,2,1, 'cdm', 1);

The above inserts create 2 Sources: One used to point to the instance containing the CDM tables, the second pointing to the server containing a Vocabulary. The vocabulary source is going to use the same source that holds the CDM tables, but you could configure the vocabulary source to point to another custom vocabulary if required.. Then, daimon’s are configured: a CDM, Vocabulary and Results are configured for the ‘My CDM’ source, and a vocabulary daimon is configured for the ‘Default Vocabulary’ datasource. Note: WebAPI will need to be restarted in order to see the the new sources (Issue# TBD).

Note: IF using postgresql, the jdbc connection string is of the form

jdbc:postgresql://<SERVER>:5432/<DATABASE_NAME>?user=<USER_NAME>&password=<USER_PASSWORD>`

Verify Configuration


Once WebAPI is started, and the source/source_daimon inserts are complete, you should be able to open a browser to the following URL:

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

This should result in the following output:

[
{"sourceId":1,"sourceName":"My Cdm","sourceDialect":"postgresql","sourceKey":"MY_CDM", "daimons":
  [ 
    {"sourceDaimonId":1,"daimonType":"CDM","tableQualifier":"public","priority":"0"},
    {"sourceDaimonId":2,"daimonType":"Vocabulary","tableQualifier":"public","priority":"0"},
    {"sourceDaimonId":3,"daimonType":"Results","tableQualifier":"OHDSI.webapi","priority":"0"}
  ]
},
{"sourceId":2,"sourceName":"Default vocabulary","sourceDialect":"postgresql","sourceKey":"vocab","daimons":
  [
    {"sourceDaimonId":4,"daimonType":"Vocabulary","tableQualifier":"public","priority":"1"}
  ]
}]

WebAPI is now configured and ready to serve OHDSI tools!

Troubleshooting


Errors during WebAPI startup


If errors are encountered in the logs of WebAPI, the most likely reason is JDBC url was not set properly (note: database names are case sensitive!), or the admin user was not granted privileges to create the necessary tables in the webapi schema.

Errors when calling sources URL


The most likely error you will receive when accessing http://localhost:8080/WebAPI/source/sources is the ohdsi_app_user does not have permission on relation ‘source’. This means the default privileges were not assigned when logged into the database as ohdsi_admin_user. These table permissions will have to be granted manually. If no error is appearing in the logs at all, please confirm the Tomcat servlet engine is listening on port 8080. If it is on a different port, you will need to adjust the URLs above to the correct port.

documentation/software/webapi/webapi_installation_guide.txt · Last modified: 2019/04/19 16:23 by anthonysena