/*
 * 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
 */

-- Create 'archive' user who can remotely access the 'archive' tables,
-- but only change the table layout locally
--
-- Assume you are connected as the 'postgres' super user

/*
CREATE USER archive WITH PASSWORD '$archive';
ALTER USER archive WITH PASSWORD '$archive';

CREATE USER report WITH PASSWORD '$report';

SELECT * FROM pg_user;

-- The following would have to be executed _after_ creating the tables:
GRANT SELECT, INSERT, UPDATE, DELETE
  ON smpl_eng, retent, smpl_mode, chan_grp, channel, status, severity, sample, array_val, num_metadata, enum_metadata 
  TO archive;

GRANT SELECT
  ON smpl_eng, retent, smpl_mode, chan_grp, channel, status, severity, sample, array_val, num_metadata, 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
  chan_grp_grpid_seq, channel_chid, retent_retentid_seq,
  severity_sevid, smpl_eng_engid_seq, status_statid 
  TO archive;
*/

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

DROP DATABASE IF EXISTS archive;

CREATE DATABASE archive;

\connect archive

------------------------
-- Sample engine
CREATE SEQUENCE smpl_eng_engid_seq;

DROP TABLE IF EXISTS archive_schema;
create table archive_schema(
	id serial primary key,
	version varchar(10)
)

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

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

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

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

CREATE SEQUENCE chan_grp_grpid_seq;

DROP TABLE IF EXISTS chan_grp;
CREATE TABLE  chan_grp
(
   grp_id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('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 chan_grp VALUES (1, 'Demo', 1, 'Demo Group', NULL);
SELECT * FROM chan_grp;

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

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

DROP TABLE IF EXISTS channel;
CREATE TABLE  channel
(
   channel_id BIGINT NOT NULL PRIMARY KEY DEFAULT nextval('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 channel(channel_id, name) VALUES (1, 'sim://sine(0, 10, 50, 0.1)');
INSERT INTO channel(channel_id, name) VALUES (2, 'sim://noiseWaveform(0,10,100,10)');
INSERT INTO channel(channel_id, name) VALUES (3, 'freddy');
INSERT INTO channel(channel_id, name) VALUES (4, 'jane');
UPDATE channel SET retent_val=9999 WHERE channel_id < 4;
UPDATE channel SET grp_id=1 WHERE channel_id < 4;
UPDATE channel SET smpl_val=1 WHERE channel_id = 1;
SELECT * FROM channel;

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

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

------------------------
-- Status mapping of status ID to string
create sequence status_statid;
DROP TABLE IF EXISTS status;
CREATE TABLE  status
(
   status_id BIGINT PRIMARY KEY default nextval('status_statid'),
   name VARCHAR(100) NOT NULL UNIQUE
);
INSERT INTO status (name) VALUES ('OK'), ('disconnected');
SELECT * FROM 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.
DROP TABLE IF EXISTS sample;
CREATE TABLE 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 channel (channel_id) ON DELETE CASCADE,
   FOREIGN KEY (severity_id) REFERENCES severity (severity_id) ON DELETE CASCADE,
   FOREIGN KEY (status_id) REFERENCES status (status_id) ON DELETE CASCADE
);

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

-- These inserts are in reverse time order to check retrieval
INSERT INTO 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
DROP TABLE IF EXISTS array_val;
CREATE TABLE  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 channel (channel_id) ON DELETE CASCADE
);

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


------------------------
-- Channel Meta data: Units etc. for numeric channels
DROP TABLE IF EXISTS num_metadata;
CREATE TABLE  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 num_metadata VALUES (1, 0, 10, 2, 8, 1, 9, 2, 'mA');
SELECT * FROM num_metadata;

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

------------------------
------------------------
------------------------
-- Dump all values for all channels
SELECT channel.name, smpl_time, severity.name, status.name, float_val
   FROM channel, severity, status, 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 sample INNER JOIN channel ON sample.channel_id = channel.channel_id INNER JOIN severity ON sample.severity_id = severity.severity_id INNER JOIN status
   ON sample.status_id = status.status_id
   ORDER BY smpl_time
   LIMIT 50;
   
