
/* This script will migrate a StrataBugs database from v2.1 to v2.1.1 */

ALTER TABLE CHTBLOCK
ADD WELL_ID NUMBER(11,0);

ALTER TABLE CHTBLOCK
ADD FOREIGN KEY (WELL_ID) REFERENCES WELL_IDENT(WELL_ID);

ALTER TABLE CHTBLOCK
ADD PARENT_ID NUMBER(11,0);

ALTER TABLE CHTBLOCK
ADD FOREIGN KEY (PARENT_ID) REFERENCES CHTBLOCK(BLOCK_ID);

/* The control table *may* be present already in some databases, it was optional in v2.1 */
CREATE TABLE CONTROL (
    TABLE_NAME    VARCHAR2(30) PRIMARY KEY NOT NULL, 
    NEXT_ID       NUMBER(7,0) 
);

/* The error +/- values are both positive numbers which are added/subtracted from the depth or age */
/* Added scale here 9 Nov 2018, missed in the original update meaning values were rounded! */

ALTER TABLE EVENTS ADD DEPTHPLUS NUMBER(7,3);
ALTER TABLE EVENTS ADD DEPTHMINUS NUMBER(7,3);
ALTER TABLE CMPSTDEV ADD CSUPLUS NUMBER(6,3);
ALTER TABLE CMPSTDEV ADD CSUMINUS NUMBER(6,3);

CREATE TABLE DBVERSION (
	VERSION 	VARCHAR2(10),
	DESCR		VARCHAR2(80),
	MODIFIED	DATE
);

INSERT INTO DBVERSION VALUES ('2.1.1', 'Chart block overrides, control table, event err ranges', sysdate);
COMMIT;


