--
--


-- Started on 2016-05-04 21:45:22 EDT


DROP DATABASE IF EXISTS ratdb;
--
-- TOC entry 2413 (class 1262 OID 16387)
-- Name: ratdb; Type: DATABASE; Schema: -; Owner: -
--

CREATE DATABASE ratdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';


\connect ratdb


--
-- TOC entry 2414 (class 1262 OID 16387)
-- Dependencies: 2413
-- Name: ratdb; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON DATABASE ratdb IS 'RATDB prototype database';


--
-- TOC entry 6 (class 2615 OID 2200)
-- Name: ratdb; Type: SCHEMA; Schema: -; Owner: -
--

CREATE SCHEMA IF NOT EXISTS ratdb;


--
-- TOC entry 2415 (class 0 OID 0)
-- Dependencies: 6
-- Name: SCHEMA ratdb; Type: COMMENT; Schema: -; Owner: -
--

COMMENT ON SCHEMA ratdb IS 'standard RATDB schema';


--
-- TOC entry 1 (class 3079 OID 12623)
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- TOC entry 2416 (class 0 OID 0)
-- Dependencies: 1
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
--

-- COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';



alter database ratdb set search_path to ratdb,pg_catalog; 
--
-- TOC entry 201 (class 1255 OID 16574)
-- Name: ratdb_data_validation(); Type: FUNCTION; Schema: ratdb; Owner: -
--

CREATE FUNCTION ratdb_data_validation() RETURNS trigger
    LANGUAGE plpgsql
    AS $$declare 

-- define the constant infinity
infinity CONSTANT integer := 2147483647;

num_entries int := 0;
aux_integer int := 0;
aux_text text;
rkey text;

username text := '';
tbl_row RECORD;
json_obj_raw json := NEW.data;
json_obj_new json;
json_obj_comp json;
json_obj_old json;

--keys cursor for 
--select * from json_object_keys(NEW.data);

base_entries text array[8] := '{"type","index","pass","version","run_range", "comment","timestamp"}';
obj_type text;
obj_index text;
obj_pass integer;
obj_version integer;
obj_rbegin integer;
obj_rend integer;
obj_comment text;
obj_timestamp text;

-- Changeable parameters (just the pass number)
new_obj_pass integer;
new_obj_key int;

json_obj_str text[] default '{}';
json_obj_str2 text[] default '{}';
begin

   raise notice 'Starting the trigger with object %',NEW.data;
-- TODO: Check permissions for the user
-- select CURRENT_USER into username;

-- 1b1) Check that the mandatory entries do exist
-- Get the list of keys:
foreach aux_text in array base_entries loop
--for "entry" in base_entries loop
	--raise notice 'Testing entry % with result %', aux_text, (select aux_text != any (array(select json_object_keys(json_obj_raw))));
	--raise notice 'Object keys : %',(array(select json_object_keys(json_obj_raw)));

	if (select aux_text = any (array(select json_object_keys(json_obj_raw)))) = false then
		raise exception 'Object structure error' using detail = 'Entry [' || aux_text || '] not found in object.';
	end if;
end loop;
-- Base structure passed

-- 1b2) extract the important bits from the object that is being inserted
obj_type := json_obj_raw->'type';
obj_index := json_obj_raw->'index';
obj_pass := json_obj_raw->'pass';
obj_version := json_obj_raw->'version';
obj_rbegin := json_obj_raw->'run_range'->>0;
obj_rend := json_obj_raw->'run_range'->>1;
obj_comment := json_obj_raw->'comment';
obj_timestamp := json_obj_raw->'timestamp';
 
-- 1c) Check if the structure of the object is valid
select into num_entries count(*) from ratdb_object_structure s where s.type = obj_type and s.version = obj_version;
if num_entries = 0 then
	-- raise notice 'Object structure does not exist yet. Adding new entry.'; -- for keys %',keys;
	-- build a dummy JSON object 
	json_obj_str = '{}';
	for rkey in (select * from json_object_keys(NEW.data)) loop
                -- raise notice 'Storing key %',rkey;
		json_obj_str = array_append(json_obj_str,'"' || rkey || '" : ""');
	end loop;
	-- now insert the new entry in the structure table
	raise notice 'Storing the following object in the structure database [%]',array_to_string(json_obj_str,',');
        json_obj_new = '{' || array_to_string(json_obj_str,',') || '}';
	insert into ratdb_object_structure ("type","version","structure") values (obj_type,obj_version,json_obj_new);
elsif num_entries = 1 then
	-- object structure already exists. Check that it is correct.
	select structure into json_obj_comp from ratdb_object_structure s where s.type = obj_type and s.version = obj_version;
--	if (select json_object_keys(json_obj_comp)) != (select json_object_keys(json_obj_raw)) then
	-- Compare the objects
	json_obj_str2 := '{}';
	for aux_text in (select json_object_keys(json_obj_comp)) loop
		-- raise notice 'Found key in dummy %',aux_text;
		json_obj_str2 = array_append(json_obj_str2,aux_text);
	end loop;
	json_obj_str := '{}';
	for aux_text in (select json_object_keys(json_obj_raw)) loop
		-- raise notice 'Found key in raw %',aux_text;
		json_obj_str = array_append(json_obj_str,aux_text);
	end loop;

	if json_obj_str2 != json_obj_str then
		raise exception 'Object structure error' using detail = 'Object structure does not match expected.';
	end if;
end if;
-- Passed the structure check
raise notice 'Object structure validated.';


--  2) Look for overlaps

-- 2a) Objects with bad ranges
-- If the new object expands over the existing object then fail.
-- 2a.1 : Check for existing objects with run_begin higher than new object (initial extrusions).
--          ****
--            xx
select count(h.obj_key) into num_entries 
 from ratdb_header h 
where h."type" = obj_type and h."index" = obj_index and h.run_begin > obj_rbegin and h.run_begin <= obj_rend;

if num_entries != 0 then
	raise exception 'Bad range' using detail = 'RATDB has ' || num_entries || ' objects with run_begin within [' 
			|| obj_rbegin+1 || ',' || obj_rend || '].';
end if;

-- there are no objects with run_begin extrusions
--2a.2 : Check for objects that end before the validity of the new object (terminal extrusions)
--              *****
--              xxx

select count(h.obj_key) into num_entries from ratdb_header h
where h."type" = obj_type and h."index" = obj_index and h.run_end < obj_rend and h.run_end >= obj_rbegin;

if num_entries != 0 then
	raise exception 'Bad range' using detail = 'RATDB has ' || num_entries || ' objects with run_end within [' 
			|| obj_rbegin || ',' || obj_rend-1 || ']';
else
	raise notice 'Range seems to be generally good.';
end if;

-- 2b : Check for that actually matter (with correct ranges)
select count(h.obj_key) into num_entries from ratdb_header h 
where h."type" = obj_type and h."index" = obj_index and h.run_begin <= obj_rbegin and h.run_end >= obj_rend;

-- 3 : Deal with existing data based on type of object (pass) being inserted
if obj_pass = -1 then -- increment_pass type
	--
	-- 5: Just assign the new pass as the number of existing objects and it is ready for insertion
	new_obj_pass = num_entries;
	raise notice 'Processing increment pass type. New pass %',new_obj_pass;

elsif obj_pass = -2 then -- truncate_range type
	raise notice 'Processing truncate type. ';
	-- Check if there is an object with exactly the same range. If there is,
	-- raise an exception
	select count(h.obj_key) into aux_integer from ratdb_header h
	where h."type" = obj_type and h."index" = obj_index and h.run_begin = obj_rbegin and h.run_end = obj_rend;

	if aux_integer != 0 then
		raise exception 'Overwrite attempt' using detail = 'Trying to overwrite existing object (objects have same range)';
	else
		raise notice 'No risk of overwrite.';
	end if;

        -- Check if the end range of the object being inserted is infinity. If not fail
        if obj_rend != infinity then
            raise exception 'Bad range' using detail = 'Trying to store finite object in truncate mode (run_range[1] = ' || obj_rend ||' != ' || infinity || ' )';
	end if;
	
	-- 4: Grab all the objects that have a valid range and truncate them all.
	-- this requires truncating both the header and the modified JSON object
	for aux_integer in (select h.obj_key from ratdb_header h where h."index" = obj_index and h."type" = obj_type
	and h.run_begin <= obj_rbegin and h.run_end = obj_rend and h.version = obj_version) loop
		-- update the header table
		update ratdb_header set run_end = obj_rbegin-1 where obj_key = aux_integer;
		-- update the JSON object. This might be a tad more complicated
		-- build a pair of keys, values based on the existing data
		json_obj_str := '{}';
                select dt.data into json_obj_old from ratdb_data dt where dt.key = aux_integer;
                for aux_text,json_obj_comp in (select key,value from json_each(json_obj_old)) loop
                    raise notice 'Processing row % %',aux_text,json_obj_comp;
	
--		for tbl_row in (select json_each(dt.data) from ratdb_data dt where dt.key = aux_integer) loop
--			raise notice 'Processing %',tbl_row;
			if aux_text = 'run_range' then	
				json_obj_str = array_append(json_obj_str,to_json(aux_text)::TEXT || ': [' || (json_obj_old->'run_range'->>0)::TEXT || ',' || (obj_rbegin-1)::TEXT || ']');
			else
				json_obj_str = array_append(json_obj_str,to_json(aux_text)::TEXT || ':' || json_obj_comp::text);
			end if;
		end loop;
		-- We now have a new, updated json object in a string
		-- update the ratdb_data table
		json_obj_new = '{' || array_to_string(json_obj_str,',') || '}';
		update ratdb_data set data = json_obj_new where key = aux_integer;
	end loop;
	 
	-- Old objects are all truncated. Ready to insert the new object
	new_obj_pass = 0;

--	update ratdb_header h set h.run_end = obj_rbegin-1 where h."index" = obj_index and h."type" = obj_type
--	and h.run_begin <= obj_rbegin and h.run_end = obj_rend abd h.version = obj_version;
--
--	raise notice 'Object truncate : Truncating existing objects of type %[%] to %',obj_type,obj_index,obj_rbegin-1;
--	-- The JSON version of the objects in the ratdb_data also needs updating
--	for tbl_row in select ratdb_data.data from ratdb_data where objid

else -- unknown. Raise exception
	raise exception 'Bad pass' using detail = 'New object with pass number ' || obj_pass::text || 
				' which is neither -1 (increment_pass) not -2 (truncate_range).';
end if;

-- All done. All it is needed is to insert the header, the new data object and the new raw object
-- insert the header first (we need the id)
-- grab what would be the next id, so that we make sure that the consistency is maintained in the transaction
select nextval('ratdb_header_obj_key_seq') into new_obj_key;

insert into ratdb_header ("obj_key","type","index","run_begin","run_end","pass","store_time","version","comment","timestamp")
	values (new_obj_key,obj_type,obj_index,obj_rbegin,obj_rend,new_obj_pass,now(),obj_version,obj_comment,obj_timestamp);

-- now insert the raw object, since this is immutable
insert into ratdb_raw_data ("key","data") values (new_obj_key,NEW.data);

-- Finally rebuild the NEW object that is to be inserted into ratdb_data
json_obj_str = '{}';
NEW."key" = new_obj_key;

for aux_text,json_obj_comp in (select key,value from json_each(NEW.data)) loop
        raise notice 'Processing row % %',aux_text,json_obj_comp;
	if aux_text = 'pass' then
		json_obj_str = array_append(json_obj_str,to_json(aux_text)::text || ':' || new_obj_pass::text);
	else
		json_obj_str = array_append(json_obj_str,to_json(aux_text)::text || ':' || json_obj_comp::text);
	end if;
end loop;
	
NEW."data" = '{' || array_to_string(json_obj_str,',') || '}';
	
return NEW;
end;
$$;


--
-- TOC entry 2417 (class 0 OID 0)
-- Dependencies: 201
-- Name: FUNCTION ratdb_data_validation(); Type: COMMENT; Schema: ratdb; Owner: -
--

COMMENT ON FUNCTION ratdb_data_validation() IS 'Trigger to validate data inserted into RATDB. Currently implements:

- Range validation
- Structure validation
- Overwrite protection
- Pass number
- Structure object

';



--
-- TOC entry 185 (class 1259 OID 16419)
-- Name: ratdb_data; Type: TABLE; Schema: ratdb; Owner: -
--

CREATE TABLE ratdb_data (
    key integer NOT NULL,
    data json
);


--
-- TOC entry 182 (class 1259 OID 16390)
-- Name: ratdb_header; Type: TABLE; Schema: ratdb; Owner: -
--

CREATE TABLE ratdb_header (
    obj_key integer NOT NULL,
    type text NOT NULL,
    index text DEFAULT ''::text NOT NULL,
    run_begin integer NOT NULL,
    run_end integer NOT NULL,
    pass integer NOT NULL,
    store_time timestamp with time zone DEFAULT now() NOT NULL,
    version integer NOT NULL,
    comment text DEFAULT ''::text NOT NULL,
    "timestamp" text DEFAULT ''::text NOT NULL
);


--
-- TOC entry 2418 (class 0 OID 0)
-- Dependencies: 182
-- Name: TABLE ratdb_header; Type: COMMENT; Schema: ratdb; Owner: -
--

COMMENT ON TABLE ratdb_header IS 'Indexed header';


--
-- TOC entry 2419 (class 0 OID 0)
-- Dependencies: 182
-- Name: COLUMN ratdb_header.obj_key; Type: COMMENT; Schema: ratdb; Owner: -
--

COMMENT ON COLUMN ratdb_header.obj_key IS 'Indexed key that defines the internal ID of an object';


--
-- TOC entry 2420 (class 0 OID 0)
-- Dependencies: 182
-- Name: COLUMN ratdb_header.type; Type: COMMENT; Schema: ratdb; Owner: -
--

COMMENT ON COLUMN ratdb_header.type IS 'Object type';


--
-- TOC entry 181 (class 1259 OID 16388)
-- Name: ratdb_header_obj_key_seq; Type: SEQUENCE; Schema: ratdb; Owner: -
--

CREATE SEQUENCE ratdb_header_obj_key_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 2421 (class 0 OID 0)
-- Dependencies: 181
-- Name: ratdb_header_obj_key_seq; Type: SEQUENCE OWNED BY; Schema: ratdb; Owner: -
--

ALTER SEQUENCE ratdb_header_obj_key_seq OWNED BY ratdb_header.obj_key;


--
-- TOC entry 187 (class 1259 OID 16468)
-- Name: ratdb_log; Type: TABLE; Schema: ratdb; Owner: -
--

CREATE TABLE ratdb_log (
    idx integer NOT NULL,
    tstamp timestamp with time zone DEFAULT statement_timestamp() NOT NULL,
    origin text NOT NULL,
    what text NOT NULL
);


--
-- TOC entry 186 (class 1259 OID 16466)
-- Name: ratdb_log_idx_seq; Type: SEQUENCE; Schema: ratdb; Owner: -
--

CREATE SEQUENCE ratdb_log_idx_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 2422 (class 0 OID 0)
-- Dependencies: 186
-- Name: ratdb_log_idx_seq; Type: SEQUENCE OWNED BY; Schema: ratdb; Owner: -
--

ALTER SEQUENCE ratdb_log_idx_seq OWNED BY ratdb_log.idx;


--
-- TOC entry 188 (class 1259 OID 16478)
-- Name: ratdb_object_structure; Type: TABLE; Schema: ratdb; Owner: -
--

CREATE TABLE ratdb_object_structure (
    type text NOT NULL,
    version integer NOT NULL,
    structure json NOT NULL
);


--
-- TOC entry 184 (class 1259 OID 16405)
-- Name: ratdb_raw_data; Type: TABLE; Schema: ratdb; Owner: -
--

CREATE TABLE ratdb_raw_data (
    key integer NOT NULL,
    data json NOT NULL
);


--
-- TOC entry 183 (class 1259 OID 16403)
-- Name: ratdb_raw_data_key_seq; Type: SEQUENCE; Schema: ratdb; Owner: -
--

CREATE SEQUENCE ratdb_raw_data_key_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


--
-- TOC entry 2423 (class 0 OID 0)
-- Dependencies: 183
-- Name: ratdb_raw_data_key_seq; Type: SEQUENCE OWNED BY; Schema: ratdb; Owner: -
--

ALTER SEQUENCE ratdb_raw_data_key_seq OWNED BY ratdb_raw_data.key;


--
-- TOC entry 2273 (class 2604 OID 16393)
-- Name: obj_key; Type: DEFAULT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_header ALTER COLUMN obj_key SET DEFAULT nextval('ratdb_header_obj_key_seq'::regclass);


--
-- TOC entry 2279 (class 2604 OID 16471)
-- Name: idx; Type: DEFAULT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_log ALTER COLUMN idx SET DEFAULT nextval('ratdb_log_idx_seq'::regclass);


--
-- TOC entry 2278 (class 2604 OID 16408)
-- Name: key; Type: DEFAULT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_raw_data ALTER COLUMN key SET DEFAULT nextval('ratdb_raw_data_key_seq'::regclass);


--
-- TOC entry 2283 (class 2606 OID 16402)
-- Name: ratb_header_pk; Type: CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_header
    ADD CONSTRAINT ratb_header_pk PRIMARY KEY (obj_key);


--
-- TOC entry 2287 (class 2606 OID 16426)
-- Name: ratdb_data_pk; Type: CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_data
    ADD CONSTRAINT ratdb_data_pk PRIMARY KEY (key);


--
-- TOC entry 2289 (class 2606 OID 16477)
-- Name: ratdb_log_pk; Type: CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_log
    ADD CONSTRAINT ratdb_log_pk PRIMARY KEY (idx);


--
-- TOC entry 2291 (class 2606 OID 16485)
-- Name: ratdb_object_structure_pk; Type: CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_object_structure
    ADD CONSTRAINT ratdb_object_structure_pk PRIMARY KEY (type, version);


--
-- TOC entry 2285 (class 2606 OID 16410)
-- Name: ratdb_raw_data_pk; Type: CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_raw_data
    ADD CONSTRAINT ratdb_raw_data_pk PRIMARY KEY (key);


--
-- TOC entry 2281 (class 1259 OID 16491)
-- Name: fki_ratdb_header_version_fk; Type: INDEX; Schema: ratdb; Owner: -
--

CREATE INDEX fki_ratdb_header_version_fk ON ratdb_header USING btree (version, type);


--
-- TOC entry 2294 (class 2620 OID 16580)
-- Name: ratdb_data_trigger_1; Type: TRIGGER; Schema: ratdb; Owner: -
--

CREATE TRIGGER ratdb_data_trigger_1 BEFORE INSERT ON ratdb_data FOR EACH ROW EXECUTE PROCEDURE ratdb_data_validation();


--
-- TOC entry 2292 (class 2606 OID 16486)
-- Name: ratdb_header_version_fk; Type: FK CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_header
    ADD CONSTRAINT ratdb_header_version_fk FOREIGN KEY (version, type) REFERENCES ratdb_object_structure(version, type);


--
-- TOC entry 2293 (class 2606 OID 16411)
-- Name: ratdb_raw_data_fk; Type: FK CONSTRAINT; Schema: ratdb; Owner: -
--

ALTER TABLE ONLY ratdb_raw_data
    ADD CONSTRAINT ratdb_raw_data_fk FOREIGN KEY (key) REFERENCES ratdb_header(obj_key) MATCH FULL;


-- Completed on 2016-05-04 21:45:23 EDT

--
--

