modules/registry/registry-core/src/main/resources/expcatalog-derby.sql

/*
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 */

CREATE TABLE GATEWAY
(
        GATEWAY_ID VARCHAR(255),
        GATEWAY_NAME VARCHAR(255),
	      DOMAIN VARCHAR(255),
	      EMAIL_ADDRESS VARCHAR(255),
        GATEWAY_ACRONYM varchar(255),
        GATEWAY_ADMIN_EMAIL varchar(255),
        GATEWAY_ADMIN_FIRST_NAME varchar(255),
        GATEWAY_APPROVAL_STATUS varchar(255),
        GATEWAY_PUBLIC_ABSTRACT varchar(255),
        GATEWAY_URL varchar(255),
        GATEWAY_ADMIN_LAST_NAME varchar(255),
        IDENTITY_SERVER_PASSWORD_TOKEN varchar(255),
        IDENTITY_SERVER_USERNAME varchar(255),
        GATEWAY_REVIEW_PROPOSAL_DESCRIPTION varchar(255),
        DECLINED_REASON varchar(255),
        OAUTH_CLIENT_SECRET varchar(255),
        OAUTH_CLIENT_ID varchar(255),
        REQUEST_CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        REQUESTER_USERNAME VARCHAR(255),
        PRIMARY KEY (GATEWAY_ID)
);

CREATE TABLE NOTIFICATION
(
        NOTIFICATION_ID VARCHAR(255) NOT NULL,
        GATEWAY_ID VARCHAR(255),
        TITLE VARCHAR(255),
        PRIORITY VARCHAR(255),
        NOTIFICATION_MESSAGE VARCHAR(4096) NOT NULL,
        PUBLISHED_DATE TIMESTAMP,
        EXPIRATION_DATE TIMESTAMP,
        CREATION_DATE TIMESTAMP,
        PRIMARY KEY (NOTIFICATION_ID)
);

CREATE TABLE USERS
(
        AIRAVATA_INTERNAL_USER_ID VARCHAR(255) NOT NULL,
        USER_NAME VARCHAR(255),
        PASSWORD VARCHAR(255),
        GATEWAY_ID VARCHAR(255) NOT NULL,
        PRIMARY KEY (GATEWAY_ID, USER_NAME),
        FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
        UNIQUE (AIRAVATA_INTERNAL_USER_ID)
);

CREATE TABLE GATEWAY_WORKER
(
        GATEWAY_ID VARCHAR(255) NOT NULL,
        USER_NAME VARCHAR(255),
        PRIMARY KEY (GATEWAY_ID, USER_NAME),
        FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE
);

CREATE TABLE PROJECT
(
        GATEWAY_ID VARCHAR(255) NOT NULL,
        USER_NAME VARCHAR(255),
        PROJECT_NAME VARCHAR(255),
        PROJECT_ID VARCHAR(255),
        DESCRIPTION VARCHAR(255),
        CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (PROJECT_ID),
        FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE
);

CREATE TABLE PROJECT_USER
(
        PROJECT_ID VARCHAR(255) NOT NULL,
        USER_NAME VARCHAR(255) NOT NULL,
        PRIMARY KEY (PROJECT_ID,USER_NAME),
        FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE
);

CREATE TABLE EXPERIMENT (
        EXPERIMENT_ID varchar(255),
        PROJECT_ID varchar(255) NOT NULL,
        GATEWAY_ID varchar(255),
        EXPERIMENT_TYPE varchar(255),
        USER_NAME varchar(255),
        EXPERIMENT_NAME varchar(255),
        CREATION_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
        DESCRIPTION varchar(255),
        EXECUTION_ID varchar(255),
        GATEWAY_EXECUTION_ID varchar(255),
        GATEWAY_INSTANCE_ID varchar(255),
        ENABLE_EMAIL_NOTIFICATION SMALLINT,
        EMAIL_ADDRESSES CLOB,
        PRIMARY KEY (EXPERIMENT_ID),
        FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE
);


CREATE TABLE EXPERIMENT_INPUT
(
        EXPERIMENT_ID varchar(255) NOT NULL,
        INPUT_NAME varchar(255),
        INPUT_VALUE CLOB,
        DATA_TYPE varchar(255),
        APPLICATION_ARGUMENT varchar(255),
        STANDARD_INPUT SMALLINT,
        USER_FRIENDLY_DESCRIPTION CLOB,
        METADATA varchar(4096),
        INPUT_ORDER INT,
        IS_REQUIRED SMALLINT,
        REQUIRED_TO_ADDED_TO_CMD SMALLINT,
        DATA_STAGED SMALLINT,
        STORAGE_RESOURCE_ID varchar(255),
        IS_READ_ONLY SMALLINT,
        OVERRIDE_FILENAME VARCHAR(255),
        PRIMARY KEY(EXPERIMENT_ID,INPUT_NAME),
        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);

CREATE TABLE EXPERIMENT_OUTPUT
(
        EXPERIMENT_ID varchar(255) NOT NULL,
        OUTPUT_NAME varchar(255),
        OUTPUT_VALUE CLOB,
        DATA_TYPE varchar(255),
        APPLICATION_ARGUMENT varchar(255),
        IS_REQUIRED SMALLINT,
        REQUIRED_TO_ADDED_TO_CMD SMALLINT,
        DATA_MOVEMENT SMALLINT,
        LOCATION varchar(255),
        SEARCH_QUERY varchar(255),
        OUTPUT_STREAMING SMALLINT,
        STORAGE_RESOURCE_ID varchar(255),
        METADATA varchar(4096),
        PRIMARY KEY(EXPERIMENT_ID,OUTPUT_NAME),
        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);


CREATE TABLE EXPERIMENT_STATUS (
        STATUS_ID varchar(255),
        EXPERIMENT_ID varchar(255) NOT NULL,
        STATE varchar(255),
        TIME_OF_STATE_CHANGE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        REASON CLOB,
        PRIMARY KEY (STATUS_ID, EXPERIMENT_ID),
        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);


CREATE TABLE EXPERIMENT_ERROR (
        ERROR_ID varchar(255),
        EXPERIMENT_ID varchar(255) NOT NULL,
        CREATION_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
        ACTUAL_ERROR_MESSAGE CLOB,
        USER_FRIENDLY_MESSAGE CLOB,
        TRANSIENT_OR_PERSISTENT SMALLINT,
        ROOT_CAUSE_ERROR_ID_LIST CLOB,
        PRIMARY KEY (ERROR_ID, EXPERIMENT_ID),
        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);

CREATE TABLE USER_CONFIGURATION_DATA (
        EXPERIMENT_ID varchar(255) NOT NULL,
        AIRAVATA_AUTO_SCHEDULE SMALLINT,
        OVERRIDE_MANUAL_SCHEDULED_PARAMS SMALLINT,
        SHARE_EXPERIMENT_PUBLICALLY SMALLINT,
        THROTTLE_RESOURCES SMALLINT,
        USER_DN varchar(255),
        GENERATE_CERT SMALLINT,
        RESOURCE_HOST_ID varchar(255),
        TOTAL_CPU_COUNT INT,
        NODE_COUNT INT,
        NUMBER_OF_THREADS INT,
        QUEUE_NAME varchar(255),
        WALL_TIME_LIMIT INT,
        TOTAL_PHYSICAL_MEMORY INT,
        STATIC_WORKING_DIR varchar(255),
        OVERRIDE_LOGIN_USER_NAME varchar(255),
        OVERRIDE_SCRATCH_LOCATION varchar(255),
        OVERRIDE_ALLOCATION_PROJECT_NUMBER varchar(255),
        STORAGE_RESOURCE_ID varchar(255),
        EXPERIMENT_DATA_DIR VARCHAR (512),
        GROUP_RESOURCE_PROFILE_ID VARCHAR(255) DEFAULT NULL,
        IS_USE_USER_CR_PREF SMALLINT,
        PRIMARY KEY (EXPERIMENT_ID),
        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);

CREATE VIEW LATEST_EXPERIMENT_STATUS AS
  select ES1.EXPERIMENT_ID AS EXPERIMENT_ID, ES1.STATE AS STATE, ES1.TIME_OF_STATE_CHANGE AS TIME_OF_STATE_CHANGE
  from EXPERIMENT_STATUS ES1 LEFT JOIN EXPERIMENT_STATUS ES2 ON (ES1.EXPERIMENT_ID = ES2.EXPERIMENT_ID
    AND ES1.TIME_OF_STATE_CHANGE < ES2.TIME_OF_STATE_CHANGE)  WHERE ES2.TIME_OF_STATE_CHANGE is NULL;

CREATE VIEW EXPERIMENT_SUMMARY AS
  select E.EXPERIMENT_ID AS EXPERIMENT_ID, E.PROJECT_ID AS PROJECT_ID, E.GATEWAY_ID AS GATEWAY_ID,
  E.USER_NAME AS USER_NAME, E.EXECUTION_ID AS EXECUTION_ID, E.EXPERIMENT_NAME AS EXPERIMENT_NAME,
  E.CREATION_TIME AS CREATION_TIME, E.DESCRIPTION AS DESCRIPTION, ES.STATE AS STATE, UD.RESOURCE_HOST_ID
  AS RESOURCE_HOST_ID, ES.TIME_OF_STATE_CHANGE AS TIME_OF_STATE_CHANGE
    from ((EXPERIMENT E left join LATEST_EXPERIMENT_STATUS ES on((E.EXPERIMENT_ID = ES.EXPERIMENT_ID)))
    left join USER_CONFIGURATION_DATA UD on((E.EXPERIMENT_ID = UD.EXPERIMENT_ID))) where true;

CREATE TABLE PROCESS (
        PROCESS_ID varchar(255),
        EXPERIMENT_ID varchar(255) NOT NULL,
        CREATION_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
        LAST_UPDATE_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
        PROCESS_DETAIL CLOB,
        APPLICATION_INTERFACE_ID varchar(255),
        TASK_DAG CLOB,
        APPLICATION_DEPLOYMENT_ID varchar(255),
        COMPUTE_RESOURCE_ID varchar(255),
        GATEWAY_EXECUTION_ID varchar(255),
        ENABLE_EMAIL_NOTIFICATION SMALLINT,
        EMAIL_ADDRESSES CLOB,
        STORAGE_RESOURCE_ID varchar(255),
        USER_DN varchar(255),
        GENERATE_CERT SMALLINT,
        EXPERIMENT_DATA_DIR VARCHAR (512),
        USERNAME VARCHAR (255),
        GROUP_RESOURCE_PROFILE_ID VARCHAR (255) DEFAULT NULL,
        USE_USER_CR_PREF SMALLINT,
        PRIMARY KEY (PROCESS_ID),
        FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);

CREATE TABLE PROCESS_INPUT
(
        PROCESS_ID varchar(255) NOT NULL,
        INPUT_NAME varchar(255),
        INPUT_VALUE CLOB,
        DATA_TYPE varchar(255),
        APPLICATION_ARGUMENT varchar(255),
        STANDARD_INPUT SMALLINT,
        USER_FRIENDLY_DESCRIPTION CLOB,
        METADATA varchar(4096),
        INPUT_ORDER INT,
        IS_REQUIRED SMALLINT,
        REQUIRED_TO_ADDED_TO_CMD SMALLINT,
        DATA_STAGED SMALLINT,
        STORAGE_RESOURCE_ID varchar(255),
        IS_READ_ONLY SMALLINT,
        OVERRIDE_FILENAME VARCHAR(255),
        PRIMARY KEY(PROCESS_ID,INPUT_NAME),
        FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);

CREATE TABLE PROCESS_OUTPUT
(
        PROCESS_ID varchar(255) NOT NULL,
        OUTPUT_NAME varchar(255),
        OUTPUT_VALUE CLOB,
        DATA_TYPE varchar(255),
        APPLICATION_ARGUMENT varchar(255),
        IS_REQUIRED SMALLINT,
        REQUIRED_TO_ADDED_TO_CMD SMALLINT,
        DATA_MOVEMENT SMALLINT,
        LOCATION varchar(255),
        SEARCH_QUERY varchar(255),
        OUTPUT_STREAMING SMALLINT,
        STORAGE_RESOURCE_ID varchar(255),
        METADATA varchar(4096),
        PRIMARY KEY(PROCESS_ID,OUTPUT_NAME),
        FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);


CREATE TABLE PROCESS_STATUS (
        STATUS_ID varchar(255),
        PROCESS_ID varchar(255) NOT NULL,
        STATE varchar(255),
        TIME_OF_STATE_CHANGE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        REASON CLOB,
        PRIMARY KEY (STATUS_ID, PROCESS_ID),
        FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);


CREATE TABLE PROCESS_ERROR (
        ERROR_ID varchar(255),
        PROCESS_ID varchar(255) NOT NULL,
        CREATION_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
        ACTUAL_ERROR_MESSAGE CLOB,
        USER_FRIENDLY_MESSAGE CLOB,
        TRANSIENT_OR_PERSISTENT SMALLINT,
        ROOT_CAUSE_ERROR_ID_LIST CLOB,
        PRIMARY KEY (ERROR_ID, PROCESS_ID),
        FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);

CREATE TABLE PROCESS_RESOURCE_SCHEDULE (
        PROCESS_ID varchar(255) NOT NULL,
        RESOURCE_HOST_ID varchar(255),
        TOTAL_CPU_COUNT INT,
        NODE_COUNT INT,
        NUMBER_OF_THREADS INT,
        QUEUE_NAME varchar(255),
        WALL_TIME_LIMIT INT,
        TOTAL_PHYSICAL_MEMORY INT,
        STATIC_WORKING_DIR varchar(255),
        OVERRIDE_ALLOCATION_PROJECT_NUMBER varchar(255),
        OVERRIDE_LOGIN_USER_NAME varchar(255),
        OVERRIDE_SCRATCH_LOCATION varchar(255),
        PRIMARY KEY (PROCESS_ID),
        FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);

CREATE TABLE TASK (
  TASK_ID varchar(255),
  TASK_TYPE varchar(255),
  PARENT_PROCESS_ID varchar(255),
  CREATION_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
  LAST_UPDATE_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
  TASK_DETAIL CLOB,
  SUB_TASK_MODEL BLOB,
  MAX_RETRY INT NOT NULL DEFAULT 3,
  CURRENT_RETRY INT NOT NULL DEFAULT 0,
  PRIMARY KEY (TASK_ID),
  FOREIGN KEY (PARENT_PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);

CREATE TABLE TASK_STATUS (
        STATUS_ID varchar(255),
        TASK_ID varchar(255) NOT NULL,
        STATE varchar(255),
        TIME_OF_STATE_CHANGE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        REASON CLOB,
        PRIMARY KEY (STATUS_ID, TASK_ID),
        FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);


CREATE TABLE TASK_ERROR (
        ERROR_ID varchar(255),
        TASK_ID varchar(255) NOT NULL,
        CREATION_TIME timestamp DEFAULT CURRENT_TIMESTAMP,
        ACTUAL_ERROR_MESSAGE CLOB,
        USER_FRIENDLY_MESSAGE CLOB,
        TRANSIENT_OR_PERSISTENT SMALLINT,
        ROOT_CAUSE_ERROR_ID_LIST CLOB,
        PRIMARY KEY (ERROR_ID, TASK_ID),
        FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);

CREATE TABLE JOB (
        JOB_ID varchar(255),
        TASK_ID varchar(255) NOT NULL,
        PROCESS_ID varchar(255),
        JOB_DESCRIPTION CLOB NOT NULL,
        CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        COMPUTE_RESOURCE_CONSUMED varchar(255),
        JOB_NAME varchar(255),
        WORKING_DIR varchar(255),
        STD_OUT CLOB,
        STD_ERR CLOB,
        EXIT_CODE INT,
        PRIMARY KEY (JOB_ID, TASK_ID),
        FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);

CREATE TABLE JOB_STATUS (
        STATUS_ID varchar(255),
        JOB_ID varchar(255) NOT NULL,
        TASK_ID varchar(255) NOT NULL,
        STATE varchar(255),
        TIME_OF_STATE_CHANGE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        REASON CLOB,
        PRIMARY KEY (STATUS_ID, JOB_ID, TASK_ID),
        FOREIGN KEY (JOB_ID, TASK_ID) REFERENCES JOB(JOB_ID, TASK_ID) ON DELETE CASCADE
);

CREATE TABLE QUEUE_STATUS(
        HOST_NAME VARCHAR(255) NOT NULL,
        QUEUE_NAME VARCHAR(255) NOT NULL,
        CREATED_TIME BIGINT NOT NULL,
        QUEUE_UP SMALLINT,
        RUNNING_JOBS INT,
        QUEUED_JOBS INT,
        PRIMARY KEY (HOST_NAME, QUEUE_NAME, CREATED_TIME)
);

CREATE TABLE CONFIGURATION
(
        CONFIG_KEY VARCHAR(255),
        CONFIG_VAL VARCHAR(255),
        EXPIRE_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        CATEGORY_ID VARCHAR (255),
        PRIMARY KEY(CONFIG_KEY, CONFIG_VAL, CATEGORY_ID)
);

INSERT INTO CONFIGURATION (CONFIG_KEY, CONFIG_VAL, EXPIRE_DATE, CATEGORY_ID) VALUES('registry.version', '0.16', CURRENT_TIMESTAMP ,'SYSTEM');

CREATE TABLE PROCESS_WORKFLOW
(
  PROCESS_ID varchar(255) NOT NULL,
  WORKFLOW_ID varchar(255) NOT NULL,
  TYPE varchar(255) DEFAULT NULL,
  CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (PROCESS_ID, WORKFLOW_ID),
  FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);

CREATE TABLE GATEWAY_USAGE_REPORTING_COMMAND (
  GATEWAY_ID VARCHAR(255) NOT NULL,
  COMPUTE_RESOURCE_ID VARCHAR(255) NOT NULL,
  COMMAND LONG VARCHAR NOT NULL,
  PRIMARY KEY (GATEWAY_ID, COMPUTE_RESOURCE_ID)
)