
--DROP TABLE gaintbl_roc;
--DROP TABLE gaintbl;


-- create the main entry point for gain tables
CREATE TABLE gaintbl(
	PRIMARY KEY(gaintbl_id),
	gaintbl_id	NUMBER(6) NOT NULL,
	name		VARCHAR(100) NOT NULL,
	description	VARCHAR(4000) NOT NULL,
	CONSTRAINT gaintbl_name_unique UNIQUE (name)
);

-- the data for one ROC is stored in one blob, along with 
-- references to the ROC and the gaintbl
CREATE TABLE gaintbl_roc(
	gaintbl_id	NUMBER(6) NOT NULL,
        roc_type	CHAR(4),
        roc_serial	NUMBER,
	data		BLOB,
	CONSTRAINT gaintbl_roc_ref_id 
		FOREIGN KEY (gaintbl_id) REFERENCES gaintbl,
	CONSTRAINT  gaintbl_roc_unique
		UNIQUE(gaintbl_id,roc_type,roc_serial)
);

ALTER TABLE cfdat_tag ADD
	gaintbl_id NUMBER(6) REFERENCES gaintbl;





CREATE SEQUENCE gaintbl_id_seq
     INCREMENT BY 1 START WITH 1 CACHE 2;

CREATE TRIGGER gaintbl_id_trg
BEFORE INSERT ON gaintbl
FOR EACH ROW
BEGIN
  SELECT gaintbl_id_seq.nextval INTO :new.gaintbl_id FROM DUAL;
END;
/



