modules/ide-integration/src/main/resources/database_scripts/airavataworkflowcatalog-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 AIRAVATA_WORKFLOW
(
        ID VARCHAR (255) NOT NULL,
        EXPERIMENT_ID varchar(255),
        DESCRIPTION VARCHAR (255),
        CREATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT timestamp,
        PRIMARY KEY (ID)
);

CREATE TABLE AIRAVATA_WORKFLOW_ERROR
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_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 (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE AIRAVATA_WORKFLOW_STATUS
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        STATE VARCHAR (255) NOT NULL,
        DESCRIPTION VARCHAR (255),
        UPDATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE WORKFLOW_APPLICATION
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        EXPERIMENT_ID varchar(255),
        APPLICATION_INTERFACE_ID VARCHAR (255),
        COMPUTE_RESOURCE_ID VARCHAR (255),
        QUEUE_NAME VARCHAR (255),
        NODE_COUNT INTEGER,
        CORE_COUNT INTEGER,
        WALL_TIME_LIMIT INTEGER,
        PHYSICAL_MEMORY INTEGER,
        CREATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT timestamp,
        PRIMARY KEY (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE APPLICATION_ERROR
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_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 (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE APPLICATION_STATUS
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        STATE VARCHAR (255) NOT NULL,
        DESCRIPTION VARCHAR (255),
        UPDATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE WORKFLOW_HANDLER
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        TYPE VARCHAR (255),
        CREATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT timestamp,
        PRIMARY KEY (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE HANDLER_ERROR
(
        ERROR_ID VARCHAR (255) NOT NULL,
        HANDLER_ID VARCHAR (255) NOT NULL,
        WORKFLOW_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, HANDLER_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE,
        FOREIGN KEY (HANDLER_ID, WORKFLOW_ID) REFERENCES WORKFLOW_HANDLER(ID, WORKFLOW_ID) ON DELETE CASCADE
);

CREATE TABLE HANDLER_INPUT (
        HANDLER_ID VARCHAR(255) NOT NULL,
        NAME VARCHAR(255) NOT NULL,
        APPLICATION_ARGUMENT VARCHAR(255),
        DATA_STAGED SMALLINT,
        INPUT_ORDER INTEGER,
        IS_READ_ONLY SMALLINT,
        IS_REQUIRED SMALLINT,
        METADATA VARCHAR(4096),
        REQUIRED_TO_ADDED_TO_COMMAND_LINE SMALLINT,
        STANDARD_INPUT SMALLINT,
        STORAGE_RESOURCE_ID VARCHAR(255),
        TYPE VARCHAR(20),
        USER_FRIENDLY_DESCRIPTION VARCHAR(255),
        VALUE CLOB,
        WORKFLOW_ID VARCHAR(255),
        PRIMARY KEY (HANDLER_ID, NAME),
        FOREIGN KEY (HANDLER_ID, WORKFLOW_ID) REFERENCES WORKFLOW_HANDLER(ID, WORKFLOW_ID) ON DELETE CASCADE
);

CREATE TABLE HANDLER_OUTPUT (HANDLER_ID VARCHAR(255) NOT NULL,
        NAME VARCHAR(255) NOT NULL,
        APPLICATION_ARGUMENT VARCHAR(255),
        DATA_MOVEMENT SMALLINT,
        IS_REQUIRED SMALLINT,
        LOCATION VARCHAR(255),
        OUTPUT_STREAMING SMALLINT,
        REQUIRED_TO_ADDED_TO_COMMAND_LINE SMALLINT,
        SEARCH_QUERY VARCHAR(255),
        STORAGE_RESOURCE_ID VARCHAR(255),
        TYPE VARCHAR(20),
        VALUE CLOB,
        WORKFLOW_ID VARCHAR(255),
        PRIMARY KEY (HANDLER_ID, NAME),
        FOREIGN KEY (HANDLER_ID, WORKFLOW_ID) REFERENCES WORKFLOW_HANDLER(ID, WORKFLOW_ID) ON DELETE CASCADE
);

CREATE TABLE HANDLER_STATUS
(
        ID VARCHAR (255) NOT NULL,
        HANDLER_ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        STATE VARCHAR (255) NOT NULL,
        DESCRIPTION VARCHAR (255),
        UPDATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (HANDLER_ID, ID),
        FOREIGN KEY (HANDLER_ID, WORKFLOW_ID) REFERENCES WORKFLOW_HANDLER(ID, WORKFLOW_ID) ON DELETE CASCADE
);

CREATE TABLE WORKFLOW_DATA_BLOCK
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        VALUE VARCHAR (255),
        DATA_TYPE VARCHAR (255),
        CREATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT timestamp,
        PRIMARY KEY (ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE
);

CREATE TABLE WORKFLOW_CONNECTION
(
        ID VARCHAR (255) NOT NULL,
        WORKFLOW_ID VARCHAR (255) NOT NULL,
        DATA_BLOCK_ID VARCHAR (255),
        FROM_TYPE VARCHAR (255),
        FROM_ID VARCHAR (255),
        FROM_OUTPUT_NAME VARCHAR (255),
        TO_TYPE VARCHAR (255),
        TO_ID VARCHAR (255),
        TO_INPUT_NAME VARCHAR (255),
        CREATED_AT timestamp DEFAULT CURRENT_TIMESTAMP,
        UPDATED_AT timestamp,
        PRIMARY KEY (ID, WORKFLOW_ID),
        FOREIGN KEY (WORKFLOW_ID) REFERENCES AIRAVATA_WORKFLOW(ID) ON DELETE CASCADE,
        FOREIGN KEY (DATA_BLOCK_ID) REFERENCES WORKFLOW_DATA_BLOCK(ID) ON DELETE CASCADE
);

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('airavata.workflow.version', '0.17', CURRENT_TIMESTAMP ,'SYSTEM');