/*
 * These commands create a new PostGres SQL database for archiving.
 *
 * THIS WILL DELETE ANY DATA THAT MIGHT BE IN THERE!
 *
 * The Oracle schema should be similar, except:
 * - Exact data types can vary (length of strings etc.)
 * - Oracle TIMESTAMP already includes the nanosecond detail, so
 *   no separate "nanosecs" column is required
 * - Support for partitioning
 *
 * Test setup:
 * postgres 8.1.20 on a Linux Laptop
 * (1.6 GHz, disk access hampered by pointsec)
 * and INSERTs performed from a Java program
 * on a 1.3 GHz OS X PowerBook G4
 *
 * Result of 10 min test run with MyISAM (no referential integrity check)
 * About 2500 inserts per second,
 * using ~33 bytes per value
 * (overall database size / pure sample count).
 * accl2 (about 20% CPU used by other people) -> ics-srv-softioc4: 1900 / sec
 * fe-ics-opi1 (otherwise ~idle CPU) -> ics-srv-softioc4: 3400 / sec
 * local, all on ics-srv-softioc4: 11000 / sec
 *
 * Result of 10 min test run with InnoDB (check sample.channel_id)
 * About 750 inserts per second.
 * Byte/sample unclear because not all data in a mysql/data subdir?
 * accl2 -> ics-srv-softioc4: 1200 / sec
 * local, all on ics-srv-softioc4: 3500 / sec
 */

/*
 * Before using this file to create tables, you must change hostname, 
 * username, password to the real name.
 * Under the directory containing this file, use this command to create the database:
 * su postgres -c "psql -q -f ARCHIVER_POSTGRES.sql"
 */


------------------------

set client_min_messages='warning';

DROP DATABASE IF EXISTS archive;

CREATE DATABASE archive;

\connect archive

set client_min_messages='warning';

CREATE SCHEMA archive;

------------------------
-- Sample engine
CREATE SEQUENCE archive.smpl_eng_engid_seq;

create table archive.archive_schema(
  id serial primary key,
  version varchar(10)
);

CREATE TABLE archive.smpl_eng
(
   eng_id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('archive.smpl_eng_engid_seq'),
   name VARCHAR(100) NOT NULL,
   descr VARCHAR(100) NOT NULL,
   url VARCHAR(100) NOT NULL
);
INSERT INTO archive.smpl_eng VALUES (1, 'Demo', 'Demo Engine', 'http://localhost:4812');
SELECT * FROM archive.smpl_eng;

------------------------
-- Retention
-- Not used at this time
CREATE SEQUENCE archive.retent_retentid_seq;

CREATE TABLE  archive.retent
(
   retent_id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('archive.retent_retentid_seq'),
   descr VARCHAR(255) NOT NULL
);
INSERT INTO archive.retent VALUES (30, 'Months');
INSERT INTO archive.retent VALUES (9999, 'Forever');
SELECT * FROM archive.retent;

------------------------
-- Channel Group

CREATE SEQUENCE archive.chan_grp_grpid_seq;

CREATE TABLE  archive.chan_grp
(
   grp_id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('archive.chan_grp_grpid_seq'),
   name VARCHAR(100) NOT NULL,
   eng_id BIGINT NOT NULL,
   descr VARCHAR(100) NULL,
   enabling_chan_id BIGINT NULL
);
INSERT INTO archive.chan_grp VALUES (1, 'Demo', 1, 'Demo Group', NULL);
SELECT * FROM archive.chan_grp;

------------------------
-- Sample modes
CREATE TABLE  archive.smpl_mode
(
   smpl_mode_id BIGINT NOT NULL PRIMARY KEY,
   name VARCHAR(100) NOT NULL,
   descr VARCHAR(100) NOT NULL
);
INSERT INTO archive.smpl_mode VALUES (1, 'Monitor', 'Store every received update');
INSERT INTO archive.smpl_mode VALUES (2, 'Scan', 'Periodic scan');
SELECT * FROM archive.smpl_mode;

------------------------
-- Channel: ID and name
CREATE SEQUENCE archive.channel_chid;

CREATE TABLE  archive.channel
(
   channel_id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('archive.channel_chid'),
   name VARCHAR(100) NOT NULL,
   descr VARCHAR(100) NULL,
   grp_id BIGINT NULL,
   smpl_mode_id BIGINT NULL,
   smpl_val double precision NULL,
   smpl_per double precision NULL, 
   retent_id BIGINT NULL,
   retent_val DOUBLE precision NULL
);
INSERT INTO archive.channel(channel_id, name) VALUES (1, 'sim://sine(0, 10, 50, 0.1)');
INSERT INTO archive.channel(channel_id, name) VALUES (2, 'sim://noiseWaveform(0,10,100,10)');
INSERT INTO archive.channel(channel_id, name) VALUES (3, 'freddy');
INSERT INTO archive.channel(channel_id, name) VALUES (4, 'jane');
UPDATE archive.channel SET retent_val=9999 WHERE channel_id < 4;
UPDATE archive.channel SET grp_id=1 WHERE channel_id < 4;
UPDATE archive.channel SET smpl_val=1 WHERE channel_id = 1;
SELECT * FROM archive.channel;

------------------------
-- Severity mapping of severity ID to string
CREATE SEQUENCE archive.severity_sevid; 

CREATE TABLE archive.severity
(
   severity_id BIGINT NOT NULL PRIMARY KEY default nextval('archive.severity_sevid'),
   name VARCHAR(100) NOT NULL
);
INSERT INTO archive.severity VALUES (1, 'OK'), (2, 'MINOR'), (3, 'MAJOR'), (4, 'INVALID');
SELECT * FROM archive.severity;

------------------------
-- Status mapping of status ID to string
create sequence archive.status_statid;
CREATE TABLE  archive.status
(
   status_id BIGINT PRIMARY KEY default nextval('archive.status_statid'),
   name VARCHAR(100) NOT NULL UNIQUE
);
INSERT INTO archive.status (name) VALUES ('OK'), ('disconnected');
SELECT * FROM archive.status;

------------------------
-- Samples of a channel
-- Either the numeric, floating point or string value should be set,
-- not all of them.
--
-- See array_encoding.txt for handling of array data.
CREATE TABLE archive.sample
(
   channel_id BIGINT NOT NULL,
   smpl_time TIMESTAMP NOT NULL,
   nanosecs BIGINT  NOT NULL,
   severity_id BIGINT NOT NULL,
   status_id BIGINT  NOT NULL,
   num_val INT NULL,
   float_val REAL NULL,
   str_val VARCHAR(120) NULL,
   datatype CHAR(1) NULL DEFAULT ' ',
   array_val BYTEA  NULL,
   
   -- Note that these foreign keys are good for data consistency,
   -- but bad for performance.
   -- Writing to the table will be almost twice as fast without
   -- the following constraints
   FOREIGN KEY (channel_id) REFERENCES archive.channel (channel_id) ON DELETE CASCADE,
   FOREIGN KEY (severity_id) REFERENCES archive.severity (severity_id) ON DELETE CASCADE,
   FOREIGN KEY (status_id) REFERENCES archive.status (status_id) ON DELETE CASCADE
);

-- Need index on channel_id and smpl_time?
CREATE INDEX sample_id_time ON archive.sample ( channel_id, smpl_time, nanosecs );

-- These inserts are in reverse time order to check retrieval
INSERT INTO archive.sample (channel_id, smpl_time,  nanosecs, severity_id, status_id, float_val)
   VALUES (1, '2004-01-10 13:01:17', 1,  3, 2, 3.16),
          (1, '2004-01-10 13:01:11', 2,  1, 1, 3.16),
          (1, '2004-01-10 13:01:10', 3, 1, 2, 3.15),
          (1, '2004-01-10 13:01:10', 4, 1, 2, 3.14);

------------------------
-- *** OLD Array element table. Replaced by array_val BLOB in sample table ***
-- See sample table: Array elements 1, 2, 3, ... beyond the element 0
-- that's in the sample table
CREATE TABLE  archive.array_val
(
   channel_id BIGINT  NOT NULL,
   smpl_time TIMESTAMP NOT NULL,
   nanosecs BIGINT  NOT NULL,
   seq_nbr BIGINT  NOT NULL,
   float_val REAL NULL,
   FOREIGN KEY (channel_id) REFERENCES archive.channel (channel_id) ON DELETE CASCADE
);

CREATE INDEX array_val_id_time ON archive.array_val ( channel_id, smpl_time, nanosecs );


------------------------
-- Channel Meta data: Units etc. for numeric channels
CREATE TABLE  archive.num_metadata
(
   channel_id BIGINT  NOT NULL PRIMARY KEY,
   low_disp_rng REAL NULL,
   high_disp_rng REAL NULL,
   low_warn_lmt REAL NULL,
   high_warn_lmt REAL NULL,
   low_alarm_lmt REAL NULL,
   high_alarm_lmt REAL NULL,
   prec INT NULL,
   unit VARCHAR(100) NOT NULL
);
INSERT INTO archive.num_metadata VALUES (1, 0, 10, 2, 8, 1, 9, 2, 'mA');
SELECT * FROM archive.num_metadata;

------------------------
-- Enumerated channels have a sample.num_val that can also be interpreted
-- as an enumeration string via this table
CREATE TABLE archive.enum_metadata
(
   channel_id BIGINT  NOT NULL,
   enum_nbr INT NULL,
   enum_val VARCHAR(120) NULL,
   FOREIGN KEY (channel_id) REFERENCES archive.channel (channel_id) ON DELETE CASCADE
);

-----------------------
-- Grant privilieges to archive users
GRANT USAGE ON SCHEMA archive TO archive;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON archive.smpl_eng, archive.retent, archive.smpl_mode, archive.chan_grp, archive.channel, archive.status,
     archive.severity, archive.sample, archive.array_val, archive.num_metadata, archive.enum_metadata 
  TO archive;

GRANT USAGE ON SCHEMA archive TO report;
GRANT SELECT
  ON archive.smpl_eng, archive.retent, archive.smpl_mode, archive.chan_grp, archive.channel, archive.status,
     archive.severity, archive.sample, archive.array_val, archive.num_metadata, archive.enum_metadata 
  TO report;

-- Might have to check with \d which sequences were
-- created by Postgres to handle the SERIAL columns:
GRANT USAGE ON SEQUENCE
  archive.chan_grp_grpid_seq, archive.channel_chid, archive.retent_retentid_seq,
  archive.severity_sevid, archive.smpl_eng_engid_seq, archive.status_statid 
  TO archive;

------------------------
------------------------
------------------------
-- Dump all values for all channels
SELECT channel.name, smpl_time, severity.name, status.name, float_val
   FROM archive.channel, archive.severity, archive.status, archive.sample 
   WHERE channel.channel_id = sample.channel_id AND
         severity.severity_id = sample.severity_id AND
         status.status_id = sample.status_id
   ORDER BY channel.name, smpl_time
   LIMIT 50;

-- Same with INNER JOIN
SELECT channel.name AS channel,
       smpl_time,
       severity.name AS severity,
       status.name AS status,
       float_val
   FROM archive.sample INNER JOIN archive.channel ON sample.channel_id = channel.channel_id
       INNER JOIN archive.severity ON sample.severity_id = severity.severity_id
       INNER JOIN archive.status ON sample.status_id = status.status_id
   ORDER BY smpl_time
   LIMIT 50;
   
