Oracle/webapi schema not created

jaybww · August 13, 2018, 4:45pm

When I get to the point in the install directions where it says:

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):

I see the Migrating messages:
Migrating schema “OHDSI” to version 1.0.0.3.2
Migrating schema “OHDSI” to version 1.0.0.4
and so on…

The catalina log itself doesn’t show any errors, only the following:

13-Aug-2018 16:24:30.475 INFO [http-nio-8080-exec-41] org.apache.catalina.startup.HostConfig.deployWAR Deploying web application archive [D:\etc\tomcat9\webapps\WebAPI.war]
13-Aug-2018 16:24:30.479 WARNING [http-nio-8080-exec-41] org.apache.catalina.startup.SetContextPropertiesRule.begin [SetContextPropertiesRule]{Context} Setting property ‘antiJARLocking’ to ‘true’ did not find a matching property.
13-Aug-2018 16:24:44.563 INFO [http-nio-8080-exec-41] org.apache.jasper.servlet.TldScanner.scanJars At least one JAR was scanned for TLDs yet contained no TLDs. Enable debug logging for this logger for a complete list of JARs that were scanned but no TLDs were found in them. Skipping unneeded JARs during scanning can improve startup time and JSP compilation time.
13-Aug-2018 16:25:28.826 INFO [http-nio-8080-exec-41] org.apache.catalina.startup.HostConfig.deployWAR Deployment of web application archive [D:\etc\tomcat9\webapps\WebAPI.war] has finished in [58,351] ms

The webapi schema was not created. There were no error messages and there is nothing in the log to indicate why this schema is not created. It seems like there is a missing set of instructions for creating this schema. The next step in the instructions: Configure source and source_daimon tables
cannot be carried out without this schema

Chris_Knoll · August 13, 2018, 9:37pm

The WebAPI database is separate from the databases referenced in your source and source_daimon tables. WebAPI does not create tables in your CDM databases (specified in the source table).

The records you insert into source is the JDBC url to your database server with your CDM (and username/password to connect)
The records you insert into source_daimon is to indicate the name of the schema that contains the CDM tables and the schema that contains the results tables (stored in the ‘table_qualifier’ column). You should already have the CDM tables set up, but to get the DDL for the results schema, you can start your WebAPI instance and open a browser to the url: {host of WebAPI:port}/WebAPI/ddl/results?dialect=oracle and that will give you a template to create the results schema for your CDM…

jaybww · August 13, 2018, 11:01pm

I am stil missing something. How do I execute the following line if there is no webapi schema?

INSERT INTO webapi.source_daimon (source_daimon_id, source_id, daimon_type, table_qualifier, priority) VALUES (1,1,0, ‘cdm’, 0);

Chris_Knoll · August 13, 2018, 11:25pm

When you start the WebAPI application, did you see anything in the log indicating :

Migrating schema "OHDSI" to version 1.0.0.3.2

Or any other errors? When WebAPI starts, it connects to the database and access those tables, so, I am missing how WebAPI launched without errors but the database wasn’t accessable…

jaybww · August 13, 2018, 11:47pm

Yes I saw that message.
No errors were listed.

There is a source_daimon table in the ohdsi schema (not webapi)

Deploying the war file creates/accesses numerous tables in the ohdsi schema but there was nothing about the webapi schema

Chris_Knoll · August 14, 2018, 2:15pm

This is a source of a bit of confusion:

There are 3 main schemas that WebAPI references:

The ‘webapi’ schema: This is the schema that WebAPI manages. You can name it anything you want, as it is specified in your settings.xml file. In your case, it looks like you named it ‘ohdsi’. This table contains the catalog of tables that WebAPI needs to support it’s function: job tables, other repository tables, etc. This schema is not associated to any specific CDM instance. It maintains a map, however, to multiple CDMs via the source and source_daimon tables.

The ‘cdm’ schema: This is the schema that contains the Common Data Model tables such as DRUG_EXPOSURE, CONCEPT, etc. Sometimes people refer to the subset of tables in the CDM for vocabulary as the ‘vocabulary schema’, but a ‘cdm’ schema contains all vocabulary tables plus the patient-level tables.

The ‘results’ schema: This is a the schema that WebAPI uses to write analyitical results for a CDM. the ‘cdm’ schema and the ‘results’ schema is always co-located in the same server. Ie: it is very common to see operations that performs INSERT INTO results_schema.result_table SELECT ... from cdm_schema.cdm_table. The results schema is not automatically configured via WebAPI. Instead, for each CDM you have, you need to create a results schema for it, and then populate it with the tables defined in the DDL script viewable by going to your WebAPI istance: /WebAPI/ddl/results.

The instructions you are reading have no way of knowing what you named your ‘webapi’ schema. So, the default instructions say ‘insert into webapi.source_daimon’. But you should change the ‘webapi’ schema name in the example to the schema name you made for yourself in your database.