DROP TABLE observation;
DROP TABLE patient;
DROP TABLE person;
DROP TABLE ps_object;
DROP TABLE ps_class;
DROP TABLE ps_field;
DROP SEQUENCE ps_oidsq;

CREATE TABLE ps_class (
       jname  VARCHAR(32) PRIMARY KEY, 
       name   VARCHAR(32), 
       make   VARCHAR(32)
);

CREATE TABLE ps_field (
       jcname	     VARCHAR(32), 
       jname	     VARCHAR(32), 
       name          VARCHAR(32), 
       modifier      INTEGER, 
       distal_jcname VARCHAR(32), 
       distal_jname  VARCHAR(32)
);

INSERT INTO ps_class VALUES (
       'rmrs.persist.test.Person', 
       'person', NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Person', 
       'name', 'pname', 1, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Person', 
       'address', 'paddress', 1, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Person', 
       'birth_dttm', 'pdob', 1, NULL, NULL);

INSERT INTO ps_class VALUES (
       'rmrs.persist.test.Patient', 
       'patient', 'newInstance');

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Patient', 
       'mrn', 'patmrn', 1, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Patient', 
       'doctor', 'pdoctor', 26, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Patient', 
       'observations', 'subject', 1834, 
       'rmrs.persist.test.Observation', 'patient');

INSERT INTO ps_class VALUES (
       'rmrs.persist.test.Observation',
       'observation', NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Observation', 
       'patient', 'subject', 26, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Observation', 
       'name', 'name', 1, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Observation', 
       'value', 'value', 6, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Observation',
       'unit', 'unit', 1, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Observation',
       'time', 'dttm', 1, NULL, NULL);

INSERT INTO ps_class VALUES (
       'rmrs.persist.test.Interval', 
       'interval', NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Interval', 
       'low', '_low', 1, NULL, NULL);

INSERT INTO ps_field VALUES (
       'rmrs.persist.test.Interval', 
       'high', '_high', 1, NULL, NULL);

CREATE SEQUENCE ps_oidsq;

CREATE TABLE ps_object (
       ps_oid    INT8 PRIMARY KEY, 
       ps_class	 VARCHAR(32), 
       ps_table	 VARCHAR(32), 
       ps_flags	 INTEGER, 
       ps_cotime TIMESTAMP, 
       ps_lstime TIMESTAMP
); 

CREATE TABLE person (
       pname    VARCHAR(32),
       paddress VARCHAR(32), 
       pdob     TIMESTAMP
) INHERITS (ps_object);

CREATE TABLE patient (
       patmrn   VARCHAR(32), 
       pdoctor  INT8
) INHERITS (person);

CREATE INDEX patient_doctor_idx ON patient (pdoctor);

CREATE TABLE observation (
       subject	  INT8, 
       name	  VARCHAR(32), 
       value_low  REAL, 
       value_high REAL, 
       unit	  VARCHAR(16), 
       dttm	  TIMESTAMP
) INHERITS (ps_object);

CREATE INDEX observation_patient_idx ON observation (subject);

GRANT ALL ON ps_class TO PUBLIC;
GRANT ALL ON ps_field TO PUBLIC;
GRANT ALL ON ps_oidsq TO PUBLIC;
GRANT ALL ON ps_object TO PUBLIC;
GRANT ALL ON person TO PUBLIC;
GRANT ALL ON patient TO PUBLIC;
GRANT ALL ON patient_doctor_idx TO PUBLIC;
GRANT ALL ON observation TO PUBLIC;
GRANT ALL ON observation_patient_idx TO PUBLIC;