You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airavata.apache.org by sc...@apache.org on 2015/06/20 23:01:53 UTC
[02/11] airavata git commit: modifying the data models to contain all
the statuses and errors
http://git-wip-us.apache.org/repos/asf/airavata/blob/3a003666/modules/registry/registry-core/src/main/resources/expcatalog-derby.sql
----------------------------------------------------------------------
diff --git a/modules/registry/registry-core/src/main/resources/expcatalog-derby.sql b/modules/registry/registry-core/src/main/resources/expcatalog-derby.sql
index a867b91..be1a62d 100644
--- a/modules/registry/registry-core/src/main/resources/expcatalog-derby.sql
+++ b/modules/registry/registry-core/src/main/resources/expcatalog-derby.sql
@@ -106,6 +106,7 @@ CREATE TABLE EXPERIMENT_OUTPUT
(
EXPERIMENT_ID varchar(255),
OUTPUT_NAME varchar(255),
+ OUTPUT_VALUE CLOB,
DATA_TYPE varchar(255),
APPLICATION_ARGUMENT varchar(255),
IS_REQUIRED BOOLEAN,
@@ -119,24 +120,25 @@ CREATE TABLE EXPERIMENT_OUTPUT
CREATE TABLE EXPERIMENT_STATUS (
+ STATUS_ID varchar(255),
EXPERIMENT_ID varchar(255),
STATE varchar(255),
TIME_OF_STATE_CHANGE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
REASON varchar(255),
- PRIMARY KEY (EXPERIMENT_ID),
+ PRIMARY KEY (STATUS_ID, EXPERIMENT_ID),
FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);
CREATE TABLE EXPERIMENT_ERROR (
- ERROR_ID int(11) NOT NULL AUTO_INCREMENT,
- EXPERIMENT_ID varchar(255) NOT NULL,
+ ERROR_ID varchar(255),
+ EXPERIMENT_ID varchar(255),
CREATION_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ACTUAL_ERROR_MESSAGE CLOB,
USER_FRIENDLY_MESSAGE CLOB,
TRANSIENT_OR_PERSISTENT BOOLEAN,
ROOT_CAUSE_ERROR_ID_LIST CLOB,
- PRIMARY KEY (ERROR_ID),
+ PRIMARY KEY (ERROR_ID, EXPERIMENT_ID),
FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);
@@ -159,12 +161,17 @@ CREATE TABLE USER_CONFIGURATION_DATA (
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.USER_NAME AS USER_NAME, E.APPLICATION_ID AS APPLICATION_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 EXPERIMENT_STATUS ES on((E.EXPERIMENT_ID = ES.EXPERIMENT_ID)))
+ 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 1;
CREATE TABLE PROCESS (
@@ -201,6 +208,7 @@ CREATE TABLE PROCESS_OUTPUT
(
PROCESS_ID varchar(255),
OUTPUT_NAME varchar(255),
+ OUTPUT_VALUE CLOB,
DATA_TYPE varchar(255),
APPLICATION_ARGUMENT varchar(255),
IS_REQUIRED BOOLEAN,
@@ -214,24 +222,25 @@ CREATE TABLE PROCESS_OUTPUT
CREATE TABLE PROCESS_STATUS (
+ STATUS_ID varchar(255),
PROCESS_ID varchar(255),
STATE varchar(255),
TIME_OF_STATE_CHANGE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
REASON varchar(255),
- PRIMARY KEY (PROCESS_ID),
+ PRIMARY KEY (STATUS_ID, PROCESS_ID),
FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);
CREATE TABLE PROCESS_ERROR (
- ERROR_ID int(11) NOT NULL AUTO_INCREMENT,
- PROCESS_ID varchar(255) NOT NULL,
+ ERROR_ID varchar(255),
+ PROCESS_ID varchar(255),
CREATION_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ACTUAL_ERROR_MESSAGE CLOB,
USER_FRIENDLY_MESSAGE CLOB,
TRANSIENT_OR_PERSISTENT BOOLEAN,
ROOT_CAUSE_ERROR_ID_LIST CLOB,
- PRIMARY KEY (ERROR_ID),
+ PRIMARY KEY (ERROR_ID, PROCESS_ID),
FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);
@@ -260,23 +269,24 @@ CREATE TABLE TASK (
);
CREATE TABLE TASK_STATUS (
+ STATUS_ID varchar(255),
TASK_ID varchar(255),
STATE varchar(255),
TIME_OF_STATE_CHANGE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
REASON varchar(255),
- PRIMARY KEY (TASK_ID),
+ PRIMARY KEY (STATUS_ID, TASK_ID),
FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);
-CREATE TABLE EXPERIMENT_ERROR (
- ERROR_ID int(11) NOT NULL AUTO_INCREMENT,
- TASK_ID varchar(255) NOT NULL,
+CREATE TABLE TASK_ERROR (
+ ERROR_ID varchar(255),
+ TASK_ID varchar(255),
CREATION_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ACTUAL_ERROR_MESSAGE CLOB,
USER_FRIENDLY_MESSAGE CLOB,
TRANSIENT_OR_PERSISTENT BOOLEAN,
ROOT_CAUSE_ERROR_ID_LIST CLOB,
- PRIMARY KEY (ERROR_ID),
+ PRIMARY KEY (ERROR_ID, TASK_ID),
FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);
http://git-wip-us.apache.org/repos/asf/airavata/blob/3a003666/modules/registry/registry-core/src/main/resources/expcatalog-mysql.sql
----------------------------------------------------------------------
diff --git a/modules/registry/registry-core/src/main/resources/expcatalog-mysql.sql b/modules/registry/registry-core/src/main/resources/expcatalog-mysql.sql
index 4d19f51..1edb50c 100644
--- a/modules/registry/registry-core/src/main/resources/expcatalog-mysql.sql
+++ b/modules/registry/registry-core/src/main/resources/expcatalog-mysql.sql
@@ -106,6 +106,7 @@ CREATE TABLE EXPERIMENT_OUTPUT
(
EXPERIMENT_ID varchar(255),
OUTPUT_NAME varchar(255),
+ OUTPUT_VALUE text,
DATA_TYPE varchar(255),
APPLICATION_ARGUMENT varchar(255),
IS_REQUIRED tinyint(1),
@@ -119,24 +120,25 @@ CREATE TABLE EXPERIMENT_OUTPUT
CREATE TABLE EXPERIMENT_STATUS (
+ STATUS_ID varchar(255),
EXPERIMENT_ID varchar(255),
STATE varchar(255),
TIME_OF_STATE_CHANGE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
REASON varchar(255),
- PRIMARY KEY (EXPERIMENT_ID),
+ PRIMARY KEY (STATUS_ID, EXPERIMENT_ID),
FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);
CREATE TABLE EXPERIMENT_ERROR (
- ERROR_ID int(11) NOT NULL AUTO_INCREMENT,
- EXPERIMENT_ID varchar(255) NOT NULL,
+ ERROR_ID varchar(255),
+ EXPERIMENT_ID varchar(255),
CREATION_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ACTUAL_ERROR_MESSAGE text,
USER_FRIENDLY_MESSAGE text,
TRANSIENT_OR_PERSISTENT tinyint(1),
ROOT_CAUSE_ERROR_ID_LIST text,
- PRIMARY KEY (ERROR_ID),
+ PRIMARY KEY (ERROR_ID, EXPERIMENT_ID),
FOREIGN KEY (EXPERIMENT_ID) REFERENCES EXPERIMENT(EXPERIMENT_ID) ON DELETE CASCADE
);
@@ -159,14 +161,21 @@ CREATE TABLE USER_CONFIGURATION_DATA (
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.USER_NAME AS USER_NAME, E.APPLICATION_ID AS APPLICATION_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 EXPERIMENT_STATUS ES on((E.EXPERIMENT_ID = ES.EXPERIMENT_ID)))
+ 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 1;
+
CREATE TABLE PROCESS (
PROCESS_ID varchar(255),
EXPERIMENT_ID varchar(255),
@@ -201,6 +210,7 @@ CREATE TABLE PROCESS_OUTPUT
(
PROCESS_ID varchar(255),
OUTPUT_NAME varchar(255),
+ OUTPUT_VALUE text,
DATA_TYPE varchar(255),
APPLICATION_ARGUMENT varchar(255),
IS_REQUIRED tinyint(1),
@@ -214,24 +224,25 @@ CREATE TABLE PROCESS_OUTPUT
CREATE TABLE PROCESS_STATUS (
+ STATUS_ID varchar(255),
PROCESS_ID varchar(255),
STATE varchar(255),
TIME_OF_STATE_CHANGE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
REASON varchar(255),
- PRIMARY KEY (PROCESS_ID),
+ PRIMARY KEY (STATUS_ID, PROCESS_ID),
FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);
CREATE TABLE PROCESS_ERROR (
- ERROR_ID int(11) NOT NULL AUTO_INCREMENT,
- PROCESS_ID varchar(255) NOT NULL,
+ ERROR_ID varchar(255),
+ PROCESS_ID varchar(255),
CREATION_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ACTUAL_ERROR_MESSAGE text,
USER_FRIENDLY_MESSAGE text,
TRANSIENT_OR_PERSISTENT tinyint(1),
ROOT_CAUSE_ERROR_ID_LIST text,
- PRIMARY KEY (ERROR_ID),
+ PRIMARY KEY (ERROR_ID, PROCESS_ID),
FOREIGN KEY (PROCESS_ID) REFERENCES PROCESS(PROCESS_ID) ON DELETE CASCADE
);
@@ -260,23 +271,24 @@ CREATE TABLE TASK (
);
CREATE TABLE TASK_STATUS (
+ STATUS_ID varchar(255),
TASK_ID varchar(255),
STATE varchar(255),
TIME_OF_STATE_CHANGE timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
REASON varchar(255),
- PRIMARY KEY (TASK_ID),
+ PRIMARY KEY (STATUS_ID, TASK_ID),
FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);
-CREATE TABLE EXPERIMENT_ERROR (
- ERROR_ID int(11) NOT NULL AUTO_INCREMENT,
- TASK_ID varchar(255) NOT NULL,
+CREATE TABLE TASK_ERROR (
+ ERROR_ID varchar(255),
+ TASK_ID varchar(255),
CREATION_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
ACTUAL_ERROR_MESSAGE text,
USER_FRIENDLY_MESSAGE text,
TRANSIENT_OR_PERSISTENT tinyint(1),
ROOT_CAUSE_ERROR_ID_LIST text,
- PRIMARY KEY (ERROR_ID),
+ PRIMARY KEY (ERROR_ID, TASK_ID),
FOREIGN KEY (TASK_ID) REFERENCES TASK(TASK_ID) ON DELETE CASCADE
);