Create SQL Tables for JobRepository

[hamtho]

Hi,

is there any documentation on how to create the data tables for the JobRepository. Unfortunately the create-statements in the documentation do not match the expected structure.

I tryied to do it manually, but there has to be a better way, because this is what I read from the exception and there are still some nullable fields, that should be non nullable.

DROP TABLE  BATCH_STEP_EXECUTION_CONTEXT IF EXISTS;
DROP TABLE  BATCH_JOB_EXECUTION_CONTEXT IF EXISTS;
DROP TABLE  BATCH_STEP_EXECUTION IF EXISTS;
DROP TABLE  BATCH_JOB_EXECUTION IF EXISTS;
DROP TABLE  BATCH_JOB_PARAMS IF EXISTS;
DROP TABLE  BATCH_JOB_INSTANCE IF EXISTS;


CREATE TABLE BATCH_JOB_INSTANCE  (
	JOB_INSTANCE_ID BIGINT IDENTITY NOT NULL PRIMARY KEY ,  
	VERSION BIGINT ,  
	JOB_NAME VARCHAR(100) NOT NULL, 
	JOB_KEY VARCHAR(2500) 
) ;

CREATE TABLE BATCH_JOB_EXECUTION  (
	JOB_EXECUTION_ID BIGINT IDENTITY NOT NULL PRIMARY KEY ,
	VERSION BIGINT  ,  
	JOB_INSTANCE_ID BIGINT NOT NULL,
	CREATE_TIME TIMESTAMP NOT NULL,
	START_TIME TIMESTAMP DEFAULT NULL , 
	END_TIME TIMESTAMP DEFAULT NULL ,
	STATUS VARCHAR(10) ,
	CONTINUABLE CHAR(1),
	EXIT_CODE VARCHAR(20) ,
	EXIT_MESSAGE VARCHAR(2500) ,
	LAST_UPDATED TIMESTAMP,
	constraint JOB_INST_EXEC_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;
	
CREATE TABLE BATCH_JOB_PARAMS  (
	JOB_INSTANCE_ID BIGINT NOT NULL ,
	TYPE_CD VARCHAR(6) NOT NULL ,
	KEY_NAME VARCHAR(100) NOT NULL , 
	STRING_VAL VARCHAR(250) , 
	DATE_VAL TIMESTAMP DEFAULT NULL ,
	LONG_VAL BIGINT ,
	DOUBLE_VAL DOUBLE PRECISION ,
	constraint JOB_INST_PARAMS_FK foreign key (JOB_INSTANCE_ID)
	references BATCH_JOB_INSTANCE(JOB_INSTANCE_ID)
) ;
	
CREATE TABLE BATCH_STEP_EXECUTION  (
  STEP_EXECUTION_ID BIGINT  PRIMARY KEY ,
  VERSION BIGINT NOT NULL,  
  STEP_NAME VARCHAR(100) NOT NULL,
  JOB_EXECUTION_ID BIGINT NOT NULL,
  START_TIME TIMESTAMP NOT NULL , 
  END_TIME TIMESTAMP DEFAULT NULL,  
  STATUS VARCHAR(10),
  COMMIT_COUNT BIGINT , 
  ITEM_COUNT BIGINT , 
  CONTINUABLE CHAR(1),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  READ_COUNT BIGINT ,
  FILTER_COUNT BIGINT ,
  WRITE_COUNT BIGINT ,
  READ_SKIP_COUNT BIGINT , 
  WRITE_SKIP_COUNT BIGINT ,
  PROCESS_SKIP_COUNT BIGINT ,
  ROLLBACK_COUNT BIGINT ,
  LAST_UPDATED TIMESTAMP DEFAULT NULL,
  constraint JOB_EXECUTION_STEP_FK foreign key (JOB_EXECUTION_ID)
  references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

CREATE TABLE BATCH_STEP_EXECUTION_CONTEXT  (
	STEP_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY,
	SHORT_CONTEXT VARCHAR(2500) NOT NULL,
	SERIALIZED_CONTEXT LONGVARCHAR , 
	constraint STEP_EXEC_CTX_FK foreign key (STEP_EXECUTION_ID)
	references BATCH_STEP_EXECUTION(STEP_EXECUTION_ID)
) ;

CREATE TABLE BATCH_JOB_EXECUTION_CONTEXT  (
	JOB_EXECUTION_ID BIGINT NOT NULL PRIMARY KEY,
	JOB_INSTANCE_ID BIGINT ,
	SHORT_CONTEXT VARCHAR(2500),
	SERIALIZED_CONTEXT LONGVARCHAR , 
	START_TIME TIMESTAMP, 
	END_TIME TIMESTAMP DEFAULT NULL ,  
	STATUS VARCHAR(10) ,
	CONTINUABLE CHAR(1),
  EXIT_CODE VARCHAR(20),
  EXIT_MESSAGE VARCHAR(2500),
  CREATE_TIME TIMESTAMP DEFAULT NULL,
  LAST_UPDATED TIMESTAMP DEFAULT NULL,
  VERSION BIGINT DEFAULT 0,  
	constraint JOB_EXEC_CTX_FK foreign key (JOB_EXECUTION_ID)
	references BATCH_JOB_EXECUTION(JOB_EXECUTION_ID)
) ;

CREATE TABLE BATCH_STEP_EXECUTION_SEQ (ID INTEGER);
INSERT INTO BATCH_STEP_EXECUTION_SEQ VALUES(0);
CREATE SEQUENCE BATCH_STEP_EXECUTION_SEQ AS BIGINT START WITH 0;

CREATE TABLE BATCH_JOB_EXECUTION_SEQ (ID INTEGER);
INSERT INTO BATCH_JOB_EXECUTION_SEQ VALUES(0);
CREATE SEQUENCE BATCH_JOB_EXECUTION_SEQ AS BIGINT START WITH 0;

CREATE TABLE BATCH_JOB_SEQ (ID INTEGER);
INSERT INTO BATCH_JOB_SEQ VALUES(0);
CREATE SEQUENCE BATCH_JOB_SEQ AS BIGINT START WITH 0;

Thanks
Thomas

[trisberg]

There are scripts for different databases in the distribution. Look in sources/spring-batch-core/src/main/resources and pick the schema-${dbname}.sql that matches your database.