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

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

INSERT INTO ps_object (ps_oid) VALUES (-1);

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 TABLE person (ps_oid BIGINT PRIMARY KEY, pname VARCHAR(32), paddress VARCHAR(32), pdob TIMESTAMP);

CREATE TABLE patient (ps_oid BIGINT PRIMARY KEY, patmrn VARCHAR(32), pdoctor BIGINT);

CREATE TABLE observation (ps_oid BIGINT PRIMARY KEY, subject BIGINT, name VARCHAR(32), value_low REAL, value_high REAL, unit VARCHAR(16), dttm TIMESTAMP);

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