Repository: airavata Updated Branches: refs/heads/master f94f9f878 -> 997d5efe1
data model for AIRAVATA-1017 Project: http://git-wip-us.apache.org/repos/asf/airavata/repo Commit: http://git-wip-us.apache.org/repos/asf/airavata/commit/997d5efe Tree: http://git-wip-us.apache.org/repos/asf/airavata/tree/997d5efe Diff: http://git-wip-us.apache.org/repos/asf/airavata/diff/997d5efe Branch: refs/heads/master Commit: 997d5efe14ed91382bd9c9d5df9d84bbd3a7d711 Parents: f94f9f8 Author: chathuri <[email protected]> Authored: Fri Feb 14 17:42:57 2014 -0500 Committer: chathuri <[email protected]> Committed: Fri Feb 14 17:42:57 2014 -0500 ---------------------------------------------------------------------- .../src/main/resources/registry-derby.sql | 376 +++++++++++++++++++ .../test/resources/airavata-registry-derby.sql | 26 +- 2 files changed, 389 insertions(+), 13 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/airavata/blob/997d5efe/modules/registry/airavata-jpa-registry/src/main/resources/registry-derby.sql ---------------------------------------------------------------------- diff --git a/modules/registry/airavata-jpa-registry/src/main/resources/registry-derby.sql b/modules/registry/airavata-jpa-registry/src/main/resources/registry-derby.sql new file mode 100644 index 0000000..37d746f --- /dev/null +++ b/modules/registry/airavata-jpa-registry/src/main/resources/registry-derby.sql @@ -0,0 +1,376 @@ +/* + * + * 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_NAME VARCHAR(255), + OWNER VARCHAR(255), + PRIMARY KEY (GATEWAY_NAME) +); + +CREATE TABLE CONFIGURATION +( + CONFIG_KEY VARCHAR(255), + CONFIG_VAL VARCHAR(255), + EXPIRE_DATE TIMESTAMP DEFAULT '0000-00-00 00:00:00', + 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.12', CURRENT_TIMESTAMP ,'SYSTEM'); + +CREATE TABLE USERS +( + USER_NAME VARCHAR(255), + PASSWORD VARCHAR(255), + PRIMARY KEY(USER_NAME) +); + +CREATE TABLE GATEWAY_WORKER +( + GATEWAY_NAME VARCHAR(255), + USER_NAME VARCHAR(255), + PRIMARY KEY (GATEWAY_NAME, USER_NAME), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE PROJECT +( + GATEWAY_NAME VARCHAR(255), + USER_NAME VARCHAR(255), + PROJECT_NAME VARCHAR(255), + PRIMARY KEY (PROJECT_NAME), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE PUBLISHED_WORKFLOW +( + GATEWAY_NAME VARCHAR(255), + CREATED_USER VARCHAR(255), + PUBLISH_WORKFLOW_NAME VARCHAR(255), + VERSION VARCHAR(255), + PUBLISHED_DATE TIMESTAMP DEFAULT '0000-00-00 00:00:00', + PATH VARCHAR (255), + WORKFLOW_CONTENT BLOB, + PRIMARY KEY(GATEWAY_NAME, PUBLISH_WORKFLOW_NAME), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (CREATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE USER_WORKFLOW +( + GATEWAY_NAME VARCHAR(255), + OWNER VARCHAR(255), + TEMPLATE_NAME VARCHAR(255), + LAST_UPDATED_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP, + PATH VARCHAR (255), + WORKFLOW_GRAPH BLOB, + PRIMARY KEY(GATEWAY_NAME, OWNER, TEMPLATE_NAME), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (OWNER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE HOST_DESCRIPTOR +( + GATEWAY_NAME VARCHAR(255), + UPDATED_USER VARCHAR(255), + HOST_DESCRIPTOR_ID VARCHAR(255), + HOST_DESCRIPTOR_XML BLOB, + PRIMARY KEY(GATEWAY_NAME, HOST_DESCRIPTOR_ID), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE SERVICE_DESCRIPTOR +( + GATEWAY_NAME VARCHAR(255), + UPDATED_USER VARCHAR(255), + SERVICE_DESCRIPTOR_ID VARCHAR(255), + SERVICE_DESCRIPTOR_XML BLOB, + PRIMARY KEY(GATEWAY_NAME,SERVICE_DESCRIPTOR_ID), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE APPLICATION_DESCRIPTOR +( + GATEWAY_NAME VARCHAR(255), + UPDATED_USER VARCHAR(255), + APPLICATION_DESCRIPTOR_ID VARCHAR(255), + HOST_DESCRIPTOR_ID VARCHAR(255), + SERVICE_DESCRIPTOR_ID VARCHAR(255), + APPLICATION_DESCRIPTOR_XML BLOB, + PRIMARY KEY(GATEWAY_NAME,APPLICATION_DESCRIPTOR_ID), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (UPDATED_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE +); + +CREATE TABLE EXPERIMENT +( + EXPERIMENT_ID VARCHAR(255), + GATEWAY_NAME VARCHAR(255), + EXECUTION_USER VARCHAR(255), + PROJECT_NAME VARCHAR(255), + CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + EXPERIMENT_NAME VARCHAR(255), + EXPERIMENT_DESCRIPTION VARCHAR(255), + APPLICATION_ID VARCHAR(255), + APPLICATION_VERSION VARCHAR(255), + WORKFLOW_TEMPLATE_ID VARCHAR(255), + WORKFLOW_TEMPLATE_VERSION VARCHAR(255), + WORKFLOW_EXECUTION_ID VARCHAR(255), + PRIMARY KEY(EXPERIMENT_ID), + FOREIGN KEY (GATEWAY_NAME) REFERENCES GATEWAY(GATEWAY_NAME) ON DELETE CASCADE, + FOREIGN KEY (PROJECT_NAME) REFERENCES PROJECT(PROJECT_NAME) ON DELETE CASCADE +); + +CREATE TABLE EXPERIMENT_INPUTS +( + EXPERIMENT_ID VARCHAR(255), + INPUT_KEY VARCHAR(255), + INPUT_TYPE VARCHAR(255), + METADATA VARCHAR(255), + VALUE VARCHAR(255), + PRIMARY KEY(EXPERIMENT_ID,INPUT_KEY), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE +); + +CREATE TABLE EXPERIMENT_OUTPUTS +( + EXPERIMENT_ID VARCHAR(255), + OUTPUT_KEY VARCHAR(255), + OUTPUT_KEY_TYPE VARCHAR(255), + METADATA VARCHAR(255), + VALUE VARCHAR(255), + PRIMARY KEY(EXPERIMENT_ID,OUTPUT_KEY), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE +); + +CREATE TABLE COMPUTATIONAL_RESOURCE_SCHEDULING +( + RESOURCE_SCHEDULING_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, + EXPERIMENT_ID VARCHAR(255), + TASK_ID VARCHAR(255), + RESOURCE_HOST_ID VARCHAR(255), + CPU_COUNT INTEGER, + NODE_COUNT INTEGER, + NO_OF_THREADS INTEGER, + QUEUE_NAME VARCHAR(255), + WALLTIME_LIMIT INTEGER, + JOB_START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00', + TOTAL_PHYSICAL_MEMORY INTEGER, + COMPUTATIONAL_PROJECT_ACCOUNT VARCHAR(255), + PRIMARY KEY(RESOURCE_SCHEDULING_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE, + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE ADVANCE_INPUT_DATA_HANDLING +( + INPUT_DATA_HANDLING_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, + EXPERIMENT_ID VARCHAR(255), + TASK_ID VARCHAR(255), + WORKING_DIR_PARENT VARCHAR(255), + UNIQUE_WORKING_DIR VARCHAR(255), + STAGE_INPUT_FILES_TO_WORKING_DIR SMALLINT, + CLEAN_AFTER_JOB SMALLINT, + PRIMARY KEY(INPUT_DATA_HANDLING_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE, + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE ADVANCE_OUTPUT_DATA_HANDLING +( + OUTPUT_DATA_HANDLING_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, + EXPERIMENT_ID VARCHAR(255), + TASK_ID VARCHAR(255), + OUTPUT_DATA_DIR VARCHAR(255), + DATA_REG_URL VARCHAR (255), + PERSIST_OUTPUT_DATA SMALLINT, + PRIMARY KEY(OUTPUT_DATA_HANDLING_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE, + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE WORKFLOW_NODE_DETAIL +( + EXPERIMENT_ID VARCHAR(255), + NODE_INSTANCE_ID VARCHAR(255), + CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + NODE_NAME VARCHAR(255), + PRIMARY KEY(NODE_INSTANCE_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE +); + +CREATE TABLE TASK_DETAIL +( + TASK_ID VARCHAR(255), + NODE_INSTANCE_ID VARCHAR(255), + CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,, + APPLICATION_ID VARCHAR(255), + APPLICATION_VERSION VARCHAR(255), + PRIMARY KEY(TASK_ID), + FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE +); + +CREATE TABLE ERROR_DETAIL +( + ERROR_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, + EXPERIMENT_ID VARCHAR(255), + TASK_ID VARCHAR(255), + NODE_INSTANCE_ID VARCHAR(255), + CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,, + ACTUAL_ERROR_MESSAGE CLOB, + USER_FRIEDNLY_ERROR_MSG VARCHAR(255), + TRANSIENT_OR_PERSISTENT SMALLINT, + ERROR_CATEGORY VARCHAR(255), + CORRECTIVE_ACTION VARCHAR(255), + ACTIONABLE_GROUP VARCHAR(255), + PRIMARY KEY(ERROR_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE, + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE, + FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE +); + +CREATE TABLE APPLICATION_INPUT +( + TASK_ID VARCHAR(255), + INPUT_KEY VARCHAR(255), + INPUT_KEY_TYPE VARCHAR(255), + METADATA VARCHAR(255), + VALUE VARCHAR(255), + PRIMARY KEY(TASK_ID,INPUT_KEY), + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE APPLICATION_OUTPUT +( + TASK_ID VARCHAR(255), + OUTPUT_KEY VARCHAR(255), + OUTPUT_KEY_TYPE VARCHAR(255), + METADATA VARCHAR(255), + VALUE VARCHAR(255), + PRIMARY KEY(TASK_ID,OUTPUT_KEY), + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE NODE_INPUT +( + NODE_INSTANCE_ID VARCHAR(255), + INPUT_KEY VARCHAR(255), + INPUT_KEY_TYPE VARCHAR(255), + METADATA VARCHAR(255), + VALUE VARCHAR(255), + PRIMARY KEY(NODE_INSTANCE_ID,INPUT_KEY), + FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE +); + +CREATE TABLE NODE_OUTPUT +( + NODE_INSTANCE_ID VARCHAR(255), + OUTPUT_KEY VARCHAR(255), + OUTPUT_KEY_TYPE VARCHAR(255), + METADATA VARCHAR(255), + VALUE VARCHAR(255), + PRIMARY KEY(NODE_INSTANCE_ID,OUTPUT_KEY), + FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE +); + +CREATE TABLE JOB_DETAIL +( + JOB_ID VARCHAR(255), + TASK_ID VARCHAR(255), + JOB_DESCRIPTION VARCHAR(255), + CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (TASK_ID, JOB_ID), + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE DATA_TRANSFER_DETAIL +( + TRANSFER_ID VARCHAR(255), + TASK_ID VARCHAR(255), + CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + TRANSFER_DESC VARCHAR(255), + PRIMARY KEY(TRANSFER_ID), + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE STATUS +( + STATUS_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, + EXPERIMENT_ID VARCHAR(255), + NODE_INSTANCE_ID VARCHAR(255), + TRANSFER_ID VARCHAR(255), + TASK_ID VARCHAR(255), + JOB_ID VARCHAR(255), + STATE VARCHAR(255), + STATUS_UPDATE_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00', + STATUS_TYPE VARCHAR(255), + PRIMARY KEY(STATUS_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE, + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE, + FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE, +); + +CREATE TABLE CONFIG_DATA +( + EXPERIMENT_ID VARCHAR(255), + AIRAVATA_AUTO_SCHEDULE SMALLINT, + OVERRIDE_MANUAL_SCHEDULE_PARAMS SMALLINT, + SHARE_EXPERIMENT SMALLINT, + PRIMARY KEY(EXPERIMENT_ID) +); + +CREATE TABLE QOS_PARAMS +( + QOS_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY, + EXPERIMENT_ID VARCHAR(255), + TASK_ID VARCHAR(255), + START_EXECUTION_AT VARCHAR(255), + EXECUTE_BEFORE VARCHAR(255), + NO_OF_RETRIES INTEGER, + PRIMARY KEY(QOS_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE, + FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE +); + +CREATE TABLE COMMUNITY_USER +( + GATEWAY_NAME VARCHAR(256) NOT NULL, + COMMUNITY_USER_NAME VARCHAR(256) NOT NULL, + TOKEN_ID VARCHAR(256) NOT NULL, + COMMUNITY_USER_EMAIL VARCHAR(256) NOT NULL, + PRIMARY KEY (GATEWAY_NAME, COMMUNITY_USER_NAME, TOKEN_ID) +); + +CREATE TABLE CREDENTIALS +( + GATEWAY_ID VARCHAR(256) NOT NULL, + TOKEN_ID VARCHAR(256) NOT NULL, + CREDENTIAL BLOB NOT NULL, + PORTAL_USER_ID VARCHAR(256) NOT NULL, + TIME_PERSISTED TIMESTAMP DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY (GATEWAY_ID, TOKEN_ID) +); + + http://git-wip-us.apache.org/repos/asf/airavata/blob/997d5efe/modules/registry/airavata-registry-test/src/test/resources/airavata-registry-derby.sql ---------------------------------------------------------------------- diff --git a/modules/registry/airavata-registry-test/src/test/resources/airavata-registry-derby.sql b/modules/registry/airavata-registry-test/src/test/resources/airavata-registry-derby.sql index 04e25c0..8a8a2cf 100644 --- a/modules/registry/airavata-registry-test/src/test/resources/airavata-registry-derby.sql +++ b/modules/registry/airavata-registry-test/src/test/resources/airavata-registry-derby.sql @@ -195,34 +195,34 @@ CREATE TABLE EXPERIMENT_OUTPUT EXPERIMENT_ID VARCHAR(255) NOT NULL, EX_KEY VARCHAR (255) NOT NULL, VALUE VARCHAR (255), + OUTPUT_TYPE VARCHAR(255), PRIMARY KEY (EXPERIMENT_ID, EX_KEY), FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE ); -CREATE TABLE WORKFLOW_DATA +CREATE TABLE EXPERIMENT_GENERATED_DATA ( - EXPERIMENT_ID VARCHAR(255), - WORKFLOW_INSTANCE_ID VARCHAR(255), - TEMPLATE_NAME VARCHAR(255), - STATUS VARCHAR(255), - START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00', - LAST_UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - PRIMARY KEY(WORKFLOW_INSTANCE_ID), - FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE + EXPERIMENT_ID VARCHAR(255), + WORKFLOW_INSTANCE_ID VARCHAR(255), + WORKFLOW_TEMPLATE_NAME VARCHAR(255), + START_TIME TIMESTAMP, + PRIMARY KEY (WORKFLOW_INSTANCE_ID), + FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT_METADATA(EXPERIMENT_ID) ON DELETE CASCADE ); CREATE TABLE NODE_DATA ( - WORKFLOW_INSTANCE_ID VARCHAR(255), - NODE_ID VARCHAR(255), + WORKFLOW_INSTANCE_ID VARCHAR(255) NOT NULL, + NODE_INSTANCE_ID VARCHAR(255) NOT NULL, + EXECUTION_INDEX INTEGER NOT NULL, + NODE_NAME VARCHAR(255), NODE_TYPE VARCHAR(255), INPUTS BLOB, OUTPUTS BLOB, STATUS VARCHAR(255), START_TIME TIMESTAMP DEFAULT '0000-00-00 00:00:00', LAST_UPDATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP, - EXECUTION_INDEX INTEGER NOT NULL, - PRIMARY KEY(WORKFLOW_INSTANCE_ID, NODE_ID, EXECUTION_INDEX), + PRIMARY KEY(NODE_INSTANCE_ID), FOREIGN KEY (WORKFLOW_INSTANCE_ID) REFERENCES WORKFLOW_DATA(WORKFLOW_INSTANCE_ID) ON DELETE CASCADE );
