Batch support schemas does not include RI and primary keys [BATCH-400]

[spring-projects-issues]

Kyrill Alyoshin opened BATCH-400 and commented

It looks like BATCH_JOB_PARAMS and BATCH_STEP_EXECUTION_ATTRS do not include primary keys. (It also appears that these two tables are not yet used anywhere). Also, the schema does include referential integrity between tables.

It becomes difficult to get an approval from DBA's to approve such table structure...

Of course, RI can be manually added as an easy workaround, but still...

---

Affects: 1.0.0.m4

Attachments:

• batch-support-schema.sql (3.37 kB)

Referenced from: commits 9336443, 1373d56, 651a536

[spring-projects-issues]

Kyrill Alyoshin commented

Of course I meant that "the schema does NOT include referential integrity".

[spring-projects-issues]

Dave Syer commented

Those tables are clearly used and very important (JobParameters and ExecutionContext are stored in them). Can you suggest RI constraints that would make sense?

[spring-projects-issues]

Kyrill Alyoshin commented

Hmm... I did a 'grep -ri BATCH_STEP_EXECUTION_ATTRS *' in m4 distribution directory... Right... The actual table names are referenced as '%PREFIX% table name'. Right.

As for RI, something along these lines would make sense, I think (in Oracle lingo):

DROP TABLE BATCH_STEP_EXECUTION;
DROP TABLE BATCH_JOB_EXECUTION;
DROP TABLE BATCH_STEP_INSTANCE;
DROP TABLE BATCH_JOB_INSTANCE;
DROP TABLE BATCH_JOB_INSTANCE_PARAMS;
DROP TABLE BATCH_STEP_EXECUTION_ATTRS;

DROP SEQUENCE BATCH_STEP_EXECUTION_SEQ;
DROP SEQUENCE BATCH_STEP_SEQ;
DROP SEQUENCE BATCH_JOB_EXECUTION_SEQ;
DROP SEQUENCE BATCH_JOB_SEQ;

CREATE TABLE BATCH_JOB_INSTANCE (
JOB_INSTANCE_ID NUMBER(38) PRIMARY KEY,
VERSION NUMBER(38),
JOB_NAME VARCHAR(100) NOT NULL,
JOB_KEY VARCHAR(250)
LAST_JOB_EXECUTION_I BIGINT
);

CREATE TABLE BATCH_JOB_EXECUTION (
JOB_EXECUTION_ID NUMBER(38) PRIMARY KEY,
VERSION NUMBER(38),
JOB_INSTANCE_ID NUMBER(38) NOT NULL,
START_TIME TIMESTAMP NOT NULL,
END_TIME TIMESTAMP,
STATUS VARCHAR(10),
CONTINUABLE CHAR(1),
EXIT_CODE VARCHAR(20),
EXIT_MESSAGE VARCHAR(2500),
constraint JOB_INSTANCE_FK foreign key (JOB_INSTANCE_ID)
references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_JOB_PARAMS (
JOB_INSTANCE_ID NUMBER(38) NOT NULL,
TYPE_CD VARCHAR(6) NOT NULL,
KEY_NAME VARCHAR(100) NOT NULL,
STRING_VAL VARCHAR(250),
DATE_VAL TIMESTAMP,
LONG_VAL NUMBER(38),
constraint JOB_INSTANCE_FK foreign key (JOB_INSTANCE_ID)
references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_STEP_INSTANCE (
STEP_INSTANCE_ID NUMBER(38) PRIMARY KEY,
VERSION NUMBER(38),
JOB_INSTANCE_ID NUMBER(38) NOT NULL,
STEP_NAME VARCHAR(100) NOT NULL,
LAST_STEP_EXECUTION_ID BIGINT,
constraint JOB_INSTANCE_FK foreign key (JOB_INSTANCE_ID)
references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
);

CREATE TABLE BATCH_STEP_EXECUTION (
STEP_EXECUTION_ID NUMBER(38) PRIMARY KEY,
VERSION NUMBER(38) NOT NULL,
STEP_INSTANCE_ID NUMBER(38) NOT NULL,
JOB_EXECUTION_ID NUMBER(38) NOT NULL,
START_TIME TIMESTAMP NOT NULL,
END_TIME TIMESTAMP,
STATUS VARCHAR(10),
COMMIT_COUNT NUMBER(38),
TASK_COUNT NUMBER(38),
TASK_STATISTICS VARCHAR(2500),
CONTINUABLE CHAR(1),
EXIT_CODE VARCHAR(20),
EXIT_MESSAGE VARCHAR(2500),
constraint STEP_INSTANCE_FK foreign key (STEP_INSTANCE_ID)
references BATCH_STEP_INSTANCE(STEP_INSTANCE_ID),
constraint JOB_EXECUTION_FK foreign key (JOB_EXECUTION_ID)
references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
);

[spring-projects-issues]

Kyrill Alyoshin commented

Here it is attached with RI checking.

[spring-projects-issues]

Dave Syer commented

Nice try ;-). There are some duplicate constraint ids in your code.

I fixed them and migrated to the current m5 schema. I only tested on HSQL - I think this is ANSI, so widely supported.

The next thing you'll want will be separate constraints in different Oracle tablespaces. At that point I think we might just say - do it yourself.

[spring-projects-issues]

Kyrill Alyoshin commented

Thanks a lot, Dave!

Yes, this is ANSI SQL. Sorry, I didn't test the SQL script myself. Didn't have a DB ready...

Come on... That was a good RFE... :-)

[spring-projects-issues]

Dave Syer commented

Assume closed as resolved and released