-- Climate scheme DDL
-- conforms to the ISO/IEC 9075-*:2003 "SQL:2003" standard

-- be an optimist first (database restoration)
truncate table climate.data_temp;
truncate table climate.data_meta;
-- all the partitions
drop table climate.data_1;
drop table climate.data_2;
-- and possible temporary table
drop TABLE climate.data_itempex;




-- Table: climate.data
-- DROP TABLE climate.data;

CREATE TABLE climate.data
(
  rt_id timestamp without time zone NOT NULL DEFAULT now(),
  datum date NOT NULL,
  station integer NOT NULL,
  data integer[],
  CONSTRAINT climate_data_pk PRIMARY KEY (datum, station),
  CONSTRAINT climate_data_stations FOREIGN KEY (station)
      REFERENCES climate.stations (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);

CREATE INDEX climate_data_rt_index ON climate.data(rt_id);


-- Table: climate.data_index
-- DROP TABLE climate.data_index;

CREATE TABLE climate.data_index
(
  rt_id timestamp without time zone NOT NULL DEFAULT now(),
  datum date NOT NULL,
  station integer NOT NULL,
  data integer[],
  CONSTRAINT climate_data_index_pk PRIMARY KEY (datum, station),
  CONSTRAINT climate_data_index_stations FOREIGN KEY (station)
      REFERENCES climate.stations (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
WITH (
  OIDS=FALSE
);

CREATE INDEX climate_data_index_rt_index ON climate.data_index(rt_id);





-- Function: climate.data_insert_trigger()
-- DROP FUNCTION climate.data_insert_trigger();

CREATE OR REPLACE FUNCTION climate.data_insert_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
    stc timestamp; -- current statement timestamp
    rtc timestamp; -- current rtc timestamp
    inscnt integer;  -- number of new
    micros double precision;
BEGIN
    -- insert into the temp table
    INSERT INTO climate.data_temp VALUES (NEW.*);
    -- TODO: some checks

    -- count the statistics and store it
    SELECT CURRENT_TIMESTAMP INTO stc;
    SELECT clock_timestamp() INTO rtc;
    micros := date_part('milliseconds', rtc-stc)*2;

    UPDATE climate.data_meta SET insert_count=(insert_count+1), insert_millis=(insert_millis+micros), insert_stdev=(insert_stdev + (micros*micros)) WHERE id=0 RETURNING insert_count INTO inscnt;

    /* finish the trasaction - notify the partitioning method if appropriate count of insert occured
    IF (inscnt % 1000 = 0) THEN
        -- NOTIFY retin_climate;
        EXEC dbms_alert.signal('retin_climate','block_inserted');
    END IF; */

    -- log
    RAISE NOTICE '%; %; %',  inscnt, (rtc-stc)*2, clock_timestamp()-stc;

    -- and go
    RETURN null;

-- if there was an error (don't update climate.data_meta)
EXCEPTION WHEN unique_violation THEN
    RETURN NEW; -- done nothing

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;





-- Trigger: insert_data_trigger on climate.data
-- DROP TRIGGER insert_data_trigger ON climate.data;

CREATE TRIGGER climate_insert_data_trigger
  BEFORE INSERT
  ON climate.data
  FOR EACH ROW
  EXECUTE PROCEDURE climate.data_insert_trigger();






-- Function: climate.query(character varying)

-- DROP FUNCTION climate.query(character varying);

CREATE OR REPLACE FUNCTION climate.query(q character varying)
  RETURNS SETOF climate.data AS
$BODY$
DECLARE
    stc timestamp; -- current statement timestamp
    ret record;
BEGIN
    -- count the statistics and store it
    SELECT CURRENT_TIMESTAMP INTO stc;

    FOR ret IN EXECUTE q
    LOOP
        -- can do some processing here
        RETURN NEXT ret; -- return current row of SELECT
    END LOOP;

    -- log
    RAISE NOTICE '%',  clock_timestamp()-stc;

    -- and go
    RETURN;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-- SELECT climate.query('SELECT * FROM climate.data LIMIT 3');




-- Function: climate.querycount(character varying)

-- DROP FUNCTION climate.querycount(q character varying);

CREATE OR REPLACE FUNCTION climate.querycount(q character varying)
  RETURNS integer AS
$BODY$
DECLARE
    stc timestamp; -- current statement timestamp
    cnt integer;
BEGIN
    -- count the statistics and store it
    SELECT CURRENT_TIMESTAMP INTO stc;

    EXECUTE q INTO cnt;

    -- log
    RAISE NOTICE '%',  clock_timestamp()-stc;

    -- and go
    RETURN cnt;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;






-- Table: climate.data_temp
-- DROP TABLE climate.data_temp;

CREATE TABLE climate.data_temp
(
  CONSTRAINT climate_data_temp_rt_id_check CHECK (rt_id >= '1900-01-01'::date),
  CONSTRAINT climate_data_temp_pk PRIMARY KEY (datum, station),
  CONSTRAINT climate_data_temp_stations FOREIGN KEY (station)
      REFERENCES climate.stations (id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT
)
INHERITS (climate.data)
WITH (
  OIDS=FALSE
);

CREATE INDEX climate_data_rt_index ON climate.data_temp(rt_id);





-- Table: climate.data_meta
-- DROP TABLE climate.data_meta;

CREATE TABLE climate.data_meta
(
  id integer NOT NULL DEFAULT 0,
  rt_first timestamp with time zone,
  -- rt_last timestamp with time zone, = id
  index_first integer,
  index_last integer,
  insert_count integer NOT NULL DEFAULT 0,
  insert_millis double precision NOT NULL DEFAULT 0,
  insert_stdev double precision NOT NULL DEFAULT 0,
  query_count integer NOT NULL DEFAULT 0,
  query_index_millis double precision NOT NULL DEFAULT 0,
  query_index_stdev double precision NOT NULL DEFAULT 0,
  query_temp_millis double precision NOT NULL DEFAULT 0,
  query_temp_stdev double precision NOT NULL DEFAULT 0,
  CONSTRAINT climate_metadata_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

COMMENT ON TABLE climate.data_meta IS 'RT data metadada

0 means a special value (null)
using postgresql timestamp (1 microseconds)
millis are for query duration (!!! in microseconds !!!)

id - id of the actual scheme.data_N table (0 for the temp)
rt_first/last - CHECK ( rt_id >= rt_first AND rt_id < rt_last )
index_F/L - ids of the indexed scheme.data_N table (F~1, L~actual id)
insert_* - stats of insert commands (using 4*stdev for maximum duration - 99.993666% )
query_* - stats of queries
indexed_from - the very oldest indexed table';




