Author: jeff.yuchang Date: 2011-01-17 05:47:19 -0500 (Mon, 17 Jan 2011) New Revision: 1242 Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/db2210-220upgrade.sql branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/hsql210-220upgrade.sql branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/mysql210-220upgrade.sql branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/oracle210-220upgrade.sql branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/postgres210-220upgrade.sql branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/sqlserver210-220upgrade.sql Modified: branches/RiftSaw-2.2.x/distribution/src/main/release/db/build.xml branches/RiftSaw-2.2.x/runtime/engine-assembly/src/main/resources/bpel-sql/hsql.sql Log: *RIFTSAW-329, added database migration script. Modified: branches/RiftSaw-2.2.x/distribution/src/main/release/db/build.xml =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/build.xml 2011-01-17 09:47:48 UTC (rev 1241) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/build.xml 2011-01-17 10:47:19 UTC (rev 1242) @@ -71,6 +71,24 @@ </sql> </target> + <!-- ### Upgrade RiftSaw SCHEMA ############################################### --> + <target name="upgrade.riftsaw.schema" + depends="log.properties" + description="upgrade riftsaw schema"> + <sql driver="${driver}" + url="${connection.url}" + userid="${username}" + password="${password}" + onerror="continue" + src="migration/${database}210-220upgrade.sql"> + <classpath> + <fileset dir="drivers"> + <include name="*.jar"/> + </fileset> + </classpath> + </sql> + </target> + <!-- ### Show tables in the database ############################### --> <target name="db.show.tables" depends="log.properties" Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/db2210-220upgrade.sql =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/db2210-220upgrade.sql (rev 0) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/db2210-220upgrade.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -0,0 +1,54 @@ +UPDATE ODE_SCHEMA_VERSION SET VERSION = 220; + +DROP TABLE ODE_JOB; + +CREATE TABLE ODE_JOB ( + jobid varchar(64) NOT NULL, + ts BIGINT NOT NULL, + nodeid varchar(64), + scheduled smallint NOT NULL, + transacted smallint NOT NULL, + + instanceId BIGINT, + mexId varchar(255), + processId varchar(255), + type varchar(255), + channel varchar(255), + correlatorId varchar(255), + correlationKeySet varchar(255), + retryCount integer, + inMem smallint, + detailsExt blob(4096), + + PRIMARY KEY(jobid)); + +CREATE INDEX IDX_ODE_JOB_TS ON ODE_JOB(ts); +CREATE INDEX IDX_ODE_JOB_NODEID ON ODE_JOB(nodeid); + +create table BPAF_EVENT ( + EID bigint generated by default as identity, + ACTIVITY_DEFINITION_ID varchar(255), + ACTIVITY_INSTANCE_ID varchar(255), + ACTIVITY_NAME varchar(255), + CURRENT_STATE varchar(255), + PREVIOUS_STATE varchar(255), + PROCESS_DEFINITION_ID varchar(255), + PROCESS_INSTANCE_ID varchar(255), + PROCESS_NAME varchar(255), + SERVER_ID varchar(255), + TIMESTAMP bigint, + primary key (EID) + ); + +create table BPAF_EVENT_DATA ( + TID bigint generated by default as identity, + NAME varchar(255), + VALUE varchar(255), + EVENT_ID bigint, + primary key (TID) +); + +alter table BPAF_EVENT_DATA + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT; \ No newline at end of file Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/hsql210-220upgrade.sql =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/hsql210-220upgrade.sql (rev 0) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/hsql210-220upgrade.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -0,0 +1,54 @@ +UPDATE ODE_SCHEMA_VERSION SET VERSION = 220; + +DROP TABLE ODE_JOB; + +CREATE TABLE ODE_JOB ( + jobid varchar(64) not null, + ts BIGINT NOT NULL, + nodeid varchar(64), + scheduled boolean NOT NULL DEFAULT 0, + transacted boolean NOT NULL DEFAULT 0, + + instanceId BIGINT, + mexId varchar(255), + processId varchar(255), + type varchar(255), + channel varchar(255), + correlatorId varchar(255), + correlationKeySet varchar(255), + retryCount integer, + inMem boolean, + detailsExt varbinary, + + PRIMARY KEY(jobid)); + +CREATE INDEX IDX_ODE_JOB_TS ON ODE_JOB(ts); +CREATE INDEX IDX_ODE_JOB_NODEID ON ODE_JOB(nodeid); + +create table BPAF_EVENT ( + EID bigint generated by default as identity (start with 1), + ACTIVITY_DEFINITION_ID varchar(255), + ACTIVITY_INSTANCE_ID varchar(255), + ACTIVITY_NAME varchar(255), + CURRENT_STATE varchar(255), + PREVIOUS_STATE varchar(255), + PROCESS_DEFINITION_ID varchar(255), + PROCESS_INSTANCE_ID varchar(255), + PROCESS_NAME varchar(255), + SERVER_ID varchar(255), + TIMESTAMP bigint, + primary key (EID) +); + +create table BPAF_EVENT_DATA ( + TID bigint generated by default as identity (start with 1), + NAME varchar(255), + VALUE varchar(255), + EVENT_ID bigint, + primary key (TID) +); + +alter table BPAF_EVENT_DATA + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT; \ No newline at end of file Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/mysql210-220upgrade.sql =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/mysql210-220upgrade.sql (rev 0) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/mysql210-220upgrade.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -0,0 +1,58 @@ +UPDATE ODE_SCHEMA_VERSION SET VERSION = 220; + +DROP TABLE ODE_JOB; + +CREATE TABLE ODE_JOB ( + jobid CHAR(64) NOT NULL DEFAULT '', + ts BIGINT NOT NULL DEFAULT 0, + nodeid varchar(64), + scheduled bit NOT NULL, + transacted bit NOT NULL, + + instanceId BIGINT, + mexId varchar(255), + processId varchar(255), + type varchar(255), + channel varchar(255), + correlatorId varchar(255), + correlationKeySet varchar(255), + retryCount integer, + inMem bit, + detailsExt blob, + + PRIMARY KEY(jobid), + INDEX IDX_ODE_JOB_TS(ts), + INDEX IDX_ODE_JOB_NODEID(nodeid) +) +TYPE=InnoDB; + +COMMIT; + +create table BPAF_EVENT ( + EID bigint not null auto_increment, + ACTIVITY_DEFINITION_ID varchar(255), + ACTIVITY_INSTANCE_ID varchar(255), + ACTIVITY_NAME varchar(255), + CURRENT_STATE varchar(255), + PREVIOUS_STATE varchar(255), + PROCESS_DEFINITION_ID varchar(255), + PROCESS_INSTANCE_ID varchar(255), + PROCESS_NAME varchar(255), + SERVER_ID varchar(255), + TIMESTAMP bigint, + primary key (EID) + ) type=InnoDB;; + +create table BPAF_EVENT_DATA ( + TID bigint not null auto_increment, + NAME varchar(255), + VALUE varchar(255), + EVENT_ID bigint, + primary key (TID) +) type=InnoDB;; + +alter table BPAF_EVENT_DATA + add index FK3E83D1BCDA848C8 (EVENT_ID), + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT (EID); \ No newline at end of file Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/oracle210-220upgrade.sql =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/oracle210-220upgrade.sql (rev 0) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/oracle210-220upgrade.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -0,0 +1,54 @@ +UPDATE ODE_SCHEMA_VERSION SET VERSION = 220; + +DROP TABLE ODE_JOB; + +CREATE TABLE ODE_JOB ( + jobid VARCHAR2(64) NOT NULL, + ts number(19,0) NOT NULL, + nodeid varchar2(64), + scheduled number(1,0) NOT NULL, + transacted number(1,0) NOT NULL, + + instanceId number(37), + mexId varchar(255), + processId varchar2(255), + type varchar2(255), + channel varchar2(255), + correlatorId varchar2(255), + correlationKeySet varchar2(255), + retryCount number(10,0), + inMem number(1,0), + detailsExt blob, + + PRIMARY KEY(jobid)); + +CREATE INDEX IDX_ODE_JOB_TS ON ODE_JOB(ts); +CREATE INDEX IDX_ODE_JOB_NODEID ON ODE_JOB(nodeid); + +create table BPAF_EVENT ( + EID number(19,0) not null, + ACTIVITY_DEFINITION_ID varchar2(255 char), + ACTIVITY_INSTANCE_ID varchar2(255 char), + ACTIVITY_NAME varchar2(255 char), + CURRENT_STATE varchar2(255 char), + PREVIOUS_STATE varchar2(255 char), + PROCESS_DEFINITION_ID varchar2(255 char), + PROCESS_INSTANCE_ID varchar2(255 char), + PROCESS_NAME varchar2(255 char), + SERVER_ID varchar2(255 char), + TIMESTAMP number(19,0), + primary key (EID) + ); + +create table BPAF_EVENT_DATA ( + TID number(19,0) not null, + NAME varchar2(255 char), + VALUE varchar2(255 char), + EVENT_ID number(19,0), + primary key (TID) +); + +alter table BPAF_EVENT_DATA + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT; \ No newline at end of file Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/postgres210-220upgrade.sql =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/postgres210-220upgrade.sql (rev 0) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/postgres210-220upgrade.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -0,0 +1,54 @@ +UPDATE ODE_SCHEMA_VERSION SET VERSION = 220; + +DROP TABLE ODE_JOB; + +create table ODE_JOB ( + jobid varchar(64) not null, + ts int8 not null, + nodeid varchar(64), + scheduled bool not null, + transacted bool not null, + instanceId int8, + mexId varchar(255), + processId varchar(255), + type varchar(255), + channel varchar(255), + correlatorId varchar(255), + correlationKeySet varchar(255), + retryCount int4, + inMem bool, + detailsExt oid, + + primary key (jobid) +); + +CREATE INDEX IDX_ODE_JOB_TS ON ODE_JOB(ts); +CREATE INDEX IDX_ODE_JOB_NODEID ON ODE_JOB(nodeid); + +create table BPAF_EVENT ( + EID int8 not null, + ACTIVITY_DEFINITION_ID varchar(255), + ACTIVITY_INSTANCE_ID varchar(255), + ACTIVITY_NAME varchar(255), + CURRENT_STATE varchar(255), + PREVIOUS_STATE varchar(255), + PROCESS_DEFINITION_ID varchar(255), + PROCESS_INSTANCE_ID varchar(255), + PROCESS_NAME varchar(255), + SERVER_ID varchar(255), + TIMESTAMP bigint, + primary key (EID) +); + +create table BPAF_EVENT_DATA ( + TID int8 not null, + NAME varchar(255), + VALUE varchar(255), + EVENT_ID bigint, + primary key (TID) +); + +alter table BPAF_EVENT_DATA + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT; \ No newline at end of file Added: branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/sqlserver210-220upgrade.sql =================================================================== --- branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/sqlserver210-220upgrade.sql (rev 0) +++ branches/RiftSaw-2.2.x/distribution/src/main/release/db/migration/sqlserver210-220upgrade.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -0,0 +1,55 @@ + +UPDATE ODE_SCHEMA_VERSION SET VERSION = 220; + +DROP TABLE ODE_JOB; + +CREATE TABLE ODE_JOB ( + jobid varchar(64) NOT NULL, + ts numeric(19,0) NOT NULL, + nodeid varchar(64) null, + scheduled tinyint NOT NULL, + transacted tinyint NOT NULL, + + instanceId numeric(19,0) null, + mexId varchar(255) null, + processId varchar(255) null, + type varchar(255) null, + channel varchar(255) null, + correlatorId varchar(255) null, + correlationKeySet varchar(255) null, + retryCount int null, + inMem tinyint null, + detailsExt image null, + + PRIMARY KEY(jobid)); + +CREATE INDEX IDX_ODE_JOB_TS ON ODE_JOB(ts); +CREATE INDEX IDX_ODE_JOB_NODEID ON ODE_JOB(nodeid); + +create table BPAF_EVENT ( + EID numeric(19,0) identity not null, + ACTIVITY_DEFINITION_ID varchar(255) null, + ACTIVITY_INSTANCE_ID varchar(255) null, + ACTIVITY_NAME varchar(255) null, + CURRENT_STATE varchar(255) null, + PREVIOUS_STATE varchar(255) null, + PROCESS_DEFINITION_ID varchar(255) null, + PROCESS_INSTANCE_ID varchar(255) null, + PROCESS_NAME varchar(255) null, + SERVER_ID varchar(255) null, + TIMESTAMP numeric(19,0) null, + primary key (EID) +); + +create table BPAF_EVENT_DATA ( + TID numeric(19,0) identity not null, + NAME varchar(255) null, + VALUE varchar(255) null, + EVENT_ID numeric(19,0) null, + primary key (TID) +); + +alter table BPAF_EVENT_DATA + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT; \ No newline at end of file Modified: branches/RiftSaw-2.2.x/runtime/engine-assembly/src/main/resources/bpel-sql/hsql.sql =================================================================== --- branches/RiftSaw-2.2.x/runtime/engine-assembly/src/main/resources/bpel-sql/hsql.sql 2011-01-17 09:47:48 UTC (rev 1241) +++ branches/RiftSaw-2.2.x/runtime/engine-assembly/src/main/resources/bpel-sql/hsql.sql 2011-01-17 10:47:19 UTC (rev 1242) @@ -245,30 +245,30 @@ ); - create table BPAF_EVENT ( - EID bigint generated by default as identity (start with 1), - ACTIVITY_DEFINITION_ID varchar(255), - ACTIVITY_INSTANCE_ID varchar(255), - ACTIVITY_NAME varchar(255), - CURRENT_STATE varchar(255), - PREVIOUS_STATE varchar(255), - PROCESS_DEFINITION_ID varchar(255), - PROCESS_INSTANCE_ID varchar(255), - PROCESS_NAME varchar(255), - SERVER_ID varchar(255), - TIMESTAMP bigint, - primary key (EID) - ); +create table BPAF_EVENT ( + EID bigint generated by default as identity (start with 1), + ACTIVITY_DEFINITION_ID varchar(255), + ACTIVITY_INSTANCE_ID varchar(255), + ACTIVITY_NAME varchar(255), + CURRENT_STATE varchar(255), + PREVIOUS_STATE varchar(255), + PROCESS_DEFINITION_ID varchar(255), + PROCESS_INSTANCE_ID varchar(255), + PROCESS_NAME varchar(255), + SERVER_ID varchar(255), + TIMESTAMP bigint, + primary key (EID) +); - create table BPAF_EVENT_DATA ( - TID bigint generated by default as identity (start with 1), - NAME varchar(255), - VALUE varchar(255), - EVENT_ID bigint, - primary key (TID) - ); +create table BPAF_EVENT_DATA ( + TID bigint generated by default as identity (start with 1), + NAME varchar(255), + VALUE varchar(255), + EVENT_ID bigint, + primary key (TID) +); - alter table BPAF_EVENT_DATA - add constraint FK3E83D1BCDA848C8 - foreign key (EVENT_ID) - references BPAF_EVENT; +alter table BPAF_EVENT_DATA + add constraint FK3E83D1BCDA848C8 + foreign key (EVENT_ID) + references BPAF_EVENT;