
CREATE TABLE cfdat_version (
	PRIMARY KEY (ver_id),
	ver_id		NUMBER(10) NOT NULL,
	name		VARCHAR(255) UNIQUE NOT NULL,
	svn_url		VARCHAR(100),
	svn_rel		VARCHAR(20)
);

CREATE TABLE cfdat_config (
	PRIMARY KEY (cfg_id),
	cfg_id		NUMBER(10) NOT NULL,
	name		VARCHAR(255) NOT NULL,
	ver_id		NUMBER(10) NOT NULL REFERENCES cfdat_version,
	UNIQUE(name,ver_id)
);

CREATE TABLE cfdat_script (
	PRIMARY KEY (script_id),
	script_id	NUMBER(8) NOT NULL,
	name		VARCHAR(255) NOT NULL,
	ver_id		NUMBER(10) NOT NULL REFERENCES cfdat_version,
	UNIQUE(name,ver_id)
);

CREATE TABLE cfdat_config_scripts (
	--	PRIMARY KEY (cfg_name,ver_id,seq),
	ver_id		NUMBER(10) NOT NULL REFERENCES cfdat_version,
	cfg_id		NUMBER(10) NOT NULL,
	seq		NUMBER(6) NOT NULL,
	script_id	NUMBER(8) NOT NULL
	--cfg_name	VARCHAR(100) NOT NULL,
	--scr_name	VARCHAR(100) NOT NULL,
	--FOREIGN KEY (cfg_name,ver_id) REFERENCES cfdat_config(name,ver_id),
	--FOREIGN KEY (scr_name,ver_id) REFERENCES cfdat_script(name,ver_id)
);

CREATE TABLE cfdat_command (
	PRIMARY KEY (script_id, seq),
	script_id	NUMBER(8) NOT NULL REFERENCES cfdat_script,
	seq		NUMBER(6),  
	cmd		NUMBER(2),  --  5 bit: 0 -         32
	dest		NUMBER(4),  -- 11 bit: 0 -       2047
	addr		NUMBER(5),  -- 16 bit: 0 -      65535
	data		NUMBER(10)  -- 32 bit: 0 - 4294967295
);

CREATE TABLE cfdat_tag (
	PRIMARY KEY(tag),
	tag	NUMBER(10) NOT NULL,
	command VARCHAR(7),
	cfg_id	NUMBER(10) REFERENCES cfdat_config
);


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

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

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

CREATE OR REPLACE FUNCTION get_curr_ver_id(acc_no IN NUMBER)
        RETURN NUMBER 
        IS id NUMBER(10);
        BEGIN 
                SELECT cfdat_version_id_seq.CURRVAL INTO id FROM DUAL;
                RETURN(id);
        END;
/

