You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airavata.apache.org by sm...@apache.org on 2015/05/01 02:56:07 UTC
[11/19] airavata git commit: Adding registry script files as
resources in airavata-api-server module for testing
Adding registry script files as resources in airavata-api-server module for testing
Project: http://git-wip-us.apache.org/repos/asf/airavata/repo
Commit: http://git-wip-us.apache.org/repos/asf/airavata/commit/c6ee5ce8
Tree: http://git-wip-us.apache.org/repos/asf/airavata/tree/c6ee5ce8
Diff: http://git-wip-us.apache.org/repos/asf/airavata/diff/c6ee5ce8
Branch: refs/heads/master
Commit: c6ee5ce892a560432dbe800debebeeeb58dbb0ba
Parents: 0a3d857
Author: Supun Nakandala <su...@gmail.com>
Authored: Mon Apr 27 20:57:47 2015 +0530
Committer: Supun Nakandala <su...@gmail.com>
Committed: Mon Apr 27 20:57:47 2015 +0530
----------------------------------------------------------------------
.../src/main/resources/registry-derby.sql | 391 ++++++++++++++++++
.../src/main/resources/registry-mysql.sql | 392 +++++++++++++++++++
2 files changed, 783 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/airavata/blob/c6ee5ce8/airavata-api/airavata-api-server/src/main/resources/registry-derby.sql
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/resources/registry-derby.sql b/airavata-api/airavata-api-server/src/main/resources/registry-derby.sql
new file mode 100644
index 0000000..7ab3755
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/resources/registry-derby.sql
@@ -0,0 +1,391 @@
+/*
+ *
+ * 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),
+ PRIMARY KEY (GATEWAY_ID)
+);
+
+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.15', CURRENT_TIMESTAMP ,'SYSTEM');
+
+CREATE TABLE USERS
+(
+ USER_NAME VARCHAR(255),
+ PASSWORD VARCHAR(255),
+ PRIMARY KEY(USER_NAME)
+);
+
+CREATE TABLE GATEWAY_WORKER
+(
+ GATEWAY_ID VARCHAR(255),
+ USER_NAME VARCHAR(255),
+ PRIMARY KEY (GATEWAY_ID, USER_NAME),
+ FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PROJECT
+(
+ GATEWAY_ID VARCHAR(255),
+ USER_NAME VARCHAR(255) NOT NULL,
+ PROJECT_ID VARCHAR(255),
+ PROJECT_NAME VARCHAR(255) NOT NULL,
+ DESCRIPTION VARCHAR(255),
+ CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ PRIMARY KEY (PROJECT_ID),
+ FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PROJECT_USER
+(
+ PROJECT_ID VARCHAR(255),
+ USER_NAME VARCHAR(255),
+ PRIMARY KEY (PROJECT_ID,USER_NAME),
+ FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT
+(
+ EXPERIMENT_ID VARCHAR(255),
+ GATEWAY_ID VARCHAR(255),
+ EXECUTION_USER VARCHAR(255) NOT NULL,
+ PROJECT_ID VARCHAR(255) NOT NULL,
+ CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ EXPERIMENT_NAME VARCHAR(255) NOT NULL,
+ 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),
+ ALLOW_NOTIFICATION SMALLINT,
+ GATEWAY_EXECUTION_ID VARCHAR(255),
+ PRIMARY KEY(EXPERIMENT_ID),
+ FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
+ FOREIGN KEY (EXECUTION_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE,
+ FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_INPUT
+(
+ EXPERIMENT_ID VARCHAR(255),
+ INPUT_KEY VARCHAR(255) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ METADATA VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ STANDARD_INPUT SMALLINT,
+ USER_FRIENDLY_DESC VARCHAR(255),
+ VALUE CLOB,
+ INPUT_ORDER INTEGER,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_STAGED SMALLINT,
+ PRIMARY KEY(EXPERIMENT_ID,INPUT_KEY),
+ FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_OUTPUT
+(
+ EXPERIMENT_ID VARCHAR(255),
+ OUTPUT_KEY VARCHAR(255) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ VALUE CLOB,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_MOVEMENT SMALLINT,
+ DATA_NAME_LOCATION VARCHAR(255),
+ SEARCH_QUERY VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ PRIMARY KEY(EXPERIMENT_ID,OUTPUT_KEY),
+ FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+
+CREATE TABLE WORKFLOW_NODE_DETAIL
+(
+ EXPERIMENT_ID VARCHAR(255) NOT NULL,
+ NODE_INSTANCE_ID VARCHAR(255),
+ CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ NODE_NAME VARCHAR(255) NOT NULL,
+ EXECUTION_UNIT VARCHAR(255) NOT NULL,
+ EXECUTION_UNIT_DATA 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),
+ APPLICATION_DEPLOYMENT_ID VARCHAR(255),
+ ALLOW_NOTIFICATION SMALLINT,
+ PRIMARY KEY(TASK_ID),
+ FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE NOTIFICATION_EMAIL
+(
+ EMAIL_ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
+ EXPERIMENT_ID VARCHAR(255),
+ TASK_ID VARCHAR(255),
+ EMAIL_ADDRESS VARCHAR(255),
+ PRIMARY KEY(EMAIL_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 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),
+ JOB_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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ METADATA VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ STANDARD_INPUT SMALLINT,
+ USER_FRIENDLY_DESC VARCHAR(255),
+ VALUE CLOB,
+ INPUT_ORDER INTEGER,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_STAGED SMALLINT,
+ 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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ VALUE CLOB,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_MOVEMENT SMALLINT,
+ DATA_NAME_LOCATION VARCHAR(255),
+ SEARCH_QUERY VARCHAR(255),
+ APP_ARGUMENT 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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ METADATA VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ STANDARD_INPUT SMALLINT,
+ USER_FRIENDLY_DESC VARCHAR(255),
+ VALUE VARCHAR(255),
+ INPUT_ORDER INTEGER,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_STAGED SMALLINT,
+ 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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ VALUE VARCHAR(255),
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_MOVEMENT SMALLINT,
+ DATA_NAME_LOCATION VARCHAR(255),
+ SEARCH_QUERY VARCHAR(255),
+ APP_ARGUMENT 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 CLOB NOT NULL,
+ CREATION_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+ COMPUTE_RESOURCE_CONSUMED VARCHAR(255),
+ JOBNAME VARCHAR (255),
+ WORKING_DIR VARCHAR(255),
+ 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) NOT NULL,
+ 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,
+ FOREIGN KEY (TRANSFER_ID) REFERENCES DATA_TRANSFER_DETAIL(TRANSFER_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE CONFIG_DATA
+(
+ EXPERIMENT_ID VARCHAR(255),
+ AIRAVATA_AUTO_SCHEDULE SMALLINT NOT NULL,
+ OVERRIDE_MANUAL_SCHEDULE_PARAMS SMALLINT NOT NULL,
+ SHARE_EXPERIMENT SMALLINT,
+ USER_DN VARCHAR(255),
+ GENERATE_CERT SMALLINT,
+ PRIMARY KEY(EXPERIMENT_ID)
+);
+
+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),
+ CHESSIS_NAME 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 QOS_PARAM
+(
+ 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_ID 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_ID, 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/c6ee5ce8/airavata-api/airavata-api-server/src/main/resources/registry-mysql.sql
----------------------------------------------------------------------
diff --git a/airavata-api/airavata-api-server/src/main/resources/registry-mysql.sql b/airavata-api/airavata-api-server/src/main/resources/registry-mysql.sql
new file mode 100644
index 0000000..14d7fc8
--- /dev/null
+++ b/airavata-api/airavata-api-server/src/main/resources/registry-mysql.sql
@@ -0,0 +1,392 @@
+/*
+ *
+ * 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),
+ PRIMARY KEY (GATEWAY_ID)
+);
+
+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.15', CURRENT_TIMESTAMP ,'SYSTEM');
+
+CREATE TABLE USERS
+(
+ USER_NAME VARCHAR(255),
+ PASSWORD VARCHAR(255),
+ PRIMARY KEY(USER_NAME)
+);
+
+CREATE TABLE GATEWAY_WORKER
+(
+ GATEWAY_ID VARCHAR(255),
+ USER_NAME VARCHAR(255),
+ PRIMARY KEY (GATEWAY_ID, USER_NAME),
+ FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PROJECT
+(
+ GATEWAY_ID VARCHAR(255),
+ USER_NAME VARCHAR(255),
+ PROJECT_NAME VARCHAR(255) NOT NULL,
+ PROJECT_ID VARCHAR(255),
+ DESCRIPTION VARCHAR(255),
+ CREATION_TIME TIMESTAMP DEFAULT NOW(),
+ PRIMARY KEY (PROJECT_ID),
+ FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE PROJECT_USER
+(
+ PROJECT_ID VARCHAR(255),
+ USER_NAME VARCHAR(255),
+ PRIMARY KEY (PROJECT_ID,USER_NAME),
+ FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE,
+ FOREIGN KEY (USER_NAME) REFERENCES USERS(USER_NAME) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT
+(
+ EXPERIMENT_ID VARCHAR(255),
+ GATEWAY_ID VARCHAR(255),
+ EXECUTION_USER VARCHAR(255) NOT NULL,
+ PROJECT_ID VARCHAR(255) NOT NULL,
+ CREATION_TIME TIMESTAMP DEFAULT NOW(),
+ EXPERIMENT_NAME VARCHAR(255) NOT NULL,
+ 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),
+ ALLOW_NOTIFICATION SMALLINT,
+ GATEWAY_EXECUTION_ID VARCHAR(255),
+ PRIMARY KEY(EXPERIMENT_ID),
+ FOREIGN KEY (GATEWAY_ID) REFERENCES GATEWAY(GATEWAY_ID) ON DELETE CASCADE,
+ FOREIGN KEY (EXECUTION_USER) REFERENCES USERS(USER_NAME) ON DELETE CASCADE,
+ FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT(PROJECT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_INPUT
+(
+ EXPERIMENT_ID VARCHAR(255),
+ INPUT_KEY VARCHAR(255) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ STANDARD_INPUT SMALLINT,
+ USER_FRIENDLY_DESC VARCHAR(255),
+ METADATA VARCHAR(255),
+ VALUE LONGTEXT,
+ INPUT_ORDER INTEGER,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_STAGED SMALLINT,
+ PRIMARY KEY(EXPERIMENT_ID,INPUT_KEY),
+ FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE EXPERIMENT_OUTPUT
+(
+ EXPERIMENT_ID VARCHAR(255),
+ OUTPUT_KEY VARCHAR(255) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ VALUE LONGTEXT,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_MOVEMENT SMALLINT,
+ DATA_NAME_LOCATION VARCHAR(255),
+ SEARCH_QUERY VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ PRIMARY KEY(EXPERIMENT_ID,OUTPUT_KEY),
+ FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE WORKFLOW_NODE_DETAIL
+(
+ EXPERIMENT_ID VARCHAR(255) NOT NULL,
+ NODE_INSTANCE_ID VARCHAR(255),
+ CREATION_TIME TIMESTAMP DEFAULT NOW(),
+ NODE_NAME VARCHAR(255) NOT NULL,
+ EXECUTION_UNIT VARCHAR(255) NOT NULL,
+ EXECUTION_UNIT_DATA 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 NOW(),
+ APPLICATION_ID VARCHAR(255),
+ APPLICATION_VERSION VARCHAR(255),
+ APPLICATION_DEPLOYMENT_ID VARCHAR(255),
+ ALLOW_NOTIFICATION SMALLINT,
+ PRIMARY KEY(TASK_ID),
+ FOREIGN KEY (NODE_INSTANCE_ID) REFERENCES WORKFLOW_NODE_DETAIL(NODE_INSTANCE_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE NOTIFICATION_EMAIL
+(
+ EMAIL_ID INTEGER NOT NULL AUTO_INCREMENT,
+ EXPERIMENT_ID VARCHAR(255),
+ TASK_ID VARCHAR(255),
+ EMAIL_ADDRESS VARCHAR(255),
+ PRIMARY KEY(EMAIL_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 APPLICATION_INPUT
+(
+ TASK_ID VARCHAR(255),
+ INPUT_KEY VARCHAR(255) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ STANDARD_INPUT SMALLINT,
+ USER_FRIENDLY_DESC VARCHAR(255),
+ METADATA VARCHAR(255),
+ VALUE LONGTEXT,
+ INPUT_ORDER INTEGER,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_STAGED SMALLINT,
+ 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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ VALUE LONGTEXT,
+ DATA_MOVEMENT SMALLINT,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_NAME_LOCATION VARCHAR(255),
+ SEARCH_QUERY VARCHAR(255),
+ APP_ARGUMENT 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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ APP_ARGUMENT VARCHAR(255),
+ STANDARD_INPUT SMALLINT,
+ USER_FRIENDLY_DESC VARCHAR(255),
+ METADATA VARCHAR(255),
+ VALUE VARCHAR(255),
+ INPUT_ORDER INTEGER,
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_STAGED SMALLINT,
+ 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) NOT NULL,
+ DATA_TYPE VARCHAR(255),
+ VALUE VARCHAR(255),
+ IS_REQUIRED SMALLINT,
+ REQUIRED_TO_COMMANDLINE SMALLINT,
+ DATA_MOVEMENT SMALLINT,
+ DATA_NAME_LOCATION VARCHAR(255),
+ SEARCH_QUERY VARCHAR(255),
+ APP_ARGUMENT 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 LONGTEXT NOT NULL,
+ CREATION_TIME TIMESTAMP DEFAULT NOW(),
+ COMPUTE_RESOURCE_CONSUMED VARCHAR(255),
+ JOBNAME VARCHAR (255),
+ WORKING_DIR VARCHAR(255),
+ 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 NOW(),
+ TRANSFER_DESC VARCHAR(255) NOT NULL,
+ PRIMARY KEY(TRANSFER_ID),
+ FOREIGN KEY (TASK_ID) REFERENCES TASK_DETAIL(TASK_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE ERROR_DETAIL
+(
+ ERROR_ID INTEGER NOT NULL AUTO_INCREMENT,
+ EXPERIMENT_ID VARCHAR(255),
+ TASK_ID VARCHAR(255),
+ NODE_INSTANCE_ID VARCHAR(255),
+ JOB_ID VARCHAR(255),
+ CREATION_TIME TIMESTAMP DEFAULT NOW(),
+ ACTUAL_ERROR_MESSAGE LONGTEXT,
+ 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 STATUS
+(
+ STATUS_ID INTEGER NOT NULL AUTO_INCREMENT,
+ 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' ON UPDATE now(),
+ 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,
+ FOREIGN KEY (TRANSFER_ID) REFERENCES DATA_TRANSFER_DETAIL(TRANSFER_ID) ON DELETE CASCADE
+);
+
+CREATE TABLE CONFIG_DATA
+(
+ EXPERIMENT_ID VARCHAR(255),
+ AIRAVATA_AUTO_SCHEDULE SMALLINT NOT NULL,
+ OVERRIDE_MANUAL_SCHEDULE_PARAMS SMALLINT NOT NULL,
+ SHARE_EXPERIMENT SMALLINT,
+ USER_DN VARCHAR(255),
+ GENERATE_CERT SMALLINT,
+ PRIMARY KEY(EXPERIMENT_ID),
+ FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
+
+);
+
+CREATE TABLE COMPUTATIONAL_RESOURCE_SCHEDULING
+(
+ RESOURCE_SCHEDULING_ID INTEGER NOT NULL AUTO_INCREMENT,
+ 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),
+ CHESSIS_NAME 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 AUTO_INCREMENT,
+ 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 AUTO_INCREMENT,
+ 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 QOS_PARAM
+(
+ QOS_ID INTEGER NOT NULL AUTO_INCREMENT,
+ 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_ID 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_ID, 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 NOW() ON UPDATE NOW(),
+ PRIMARY KEY (GATEWAY_ID, TOKEN_ID)
+);
+
+