WorkingGrid/DataProviderSetup/PMLOct06: ndg_db.sql

-- NDG Database Schema
--
-- Author(s):  mggr, mhen
-- Date:       2007-07-19
--
-- SQL file describing the ndg schema


--\connect - postgres

CREATE SCHEMA ndg;
REVOKE ALL ON SCHEMA ndg FROM PUBLIC;
REVOKE ALL ON SCHEMA ndg FROM postgres;

--\connect - rsguser

SET search_path = public, ndg, pg_catalog;


CREATE TABLE ndg.metadata_desc (
    metadata_desc_id integer UNIQUE NOT NULL,
    abstract character varying(32767) NOT NULL,
    description_section character varying(32767),
    last_updated timestamp with time zone NOT NULL,

    PRIMARY KEY (metadata_desc_id)
);
REVOKE ALL ON TABLE ndg.metadata_desc FROM PUBLIC;

CREATE TABLE ndg.structured_keyword (
    structured_keyword_id integer NOT NULL,
    parent_keyword_id     integer,

    valid_term            character varying(1024) NOT NULL,
    valid_term_abbrev     character varying(1024),
    parent_list_id        character varying(1024) NOT NULL,
    term_id               character varying(64) NOT NULL,
    
    PRIMARY KEY (structured_keyword_id)
);
REVOKE ALL ON TABLE ndg.structured_keyword FROM PUBLIC;

CREATE TABLE ndg.contact_details (
    contact_details_id integer UNIQUE NOT NULL,
    email character varying(100),
    fax character varying(25),
    telephone character varying(25),
    address character varying(200),
    postcode character varying(10),
    uri character varying(100),
    city character varying(50),
    country character varying(50),

    PRIMARY KEY (contact_details_id)
);
REVOKE ALL ON TABLE ndg.contact_details FROM PUBLIC;

CREATE TABLE ndg.person (
    person_id integer UNIQUE NOT NULL,
    contact_details_id integer NOT NULL,
    title character varying(5),
    known_as character varying(50),
    personal_name character varying(50),
    initials character varying(10) NOT NULL,
    family_name character varying(50) NOT NULL,
    name_order character varying(10),

    PRIMARY KEY (person_id),
    FOREIGN KEY (contact_details_id) REFERENCES contact_details
);
REVOKE ALL ON TABLE ndg.person FROM PUBLIC;

CREATE TABLE ndg.organisation (
    organisation_id integer UNIQUE NOT NULL,
    contact_details_id integer NOT NULL,
    name character varying(100) NOT NULL,
    abbreviation character varying(20) NOT NULL,

    PRIMARY KEY (organisation_id),
    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
);
REVOKE ALL ON TABLE ndg.organisation FROM PUBLIC;

CREATE TABLE ndg.role (
    role_id integer UNIQUE NOT NULL,
    role_name character varying(150) NOT NULL,
    abbreviation character varying(75) NOT NULL,
    contact_details_id integer NOT NULL,

    PRIMARY KEY (role_id),
    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
);
REVOKE ALL ON TABLE ndg.role FROM PUBLIC;

CREATE TABLE ndg.organisation_role (
    organisation_id integer NOT NULL,
    role_id integer NOT NULL,

    PRIMARY KEY (organisation_id, role_id),
    FOREIGN KEY (organisation_id) REFERENCES ndg.organisation,
    FOREIGN KEY (role_id) REFERENCES ndg.role
);
REVOKE ALL ON TABLE ndg.organisation_role FROM PUBLIC;


CREATE TABLE ndg.role_holder (
    role_holder_id integer UNIQUE NOT NULL,
    role_id integer NOT NULL,
    start_date date NOT NULL,
    end_date date,
    organisation_id integer,
    person_id integer,

    PRIMARY KEY (role_holder_id),
    FOREIGN KEY (role_id) REFERENCES ndg.role,
    FOREIGN KEY (organisation_id) REFERENCES ndg.organisation,
    FOREIGN KEY (person_id) REFERENCES ndg.person
);
REVOKE ALL ON TABLE ndg.role_holder FROM PUBLIC;

CREATE TABLE ndg.scheme_type (
    scheme_type_id integer UNIQUE NOT NULL,
    scheme_type character varying(100) NOT NULL,

    PRIMARY KEY (scheme_type_id)
);
REVOKE ALL ON TABLE ndg.scheme_type FROM PUBLIC;


-- We dont really need this, as the XML schema defines units as structured keywords... (mhen)
--CREATE TABLE ndg.unit (
--    unit_id integer NOT NULL,
--    name character varying(100) NOT NULL,
--    abbreviation character varying(50) NOT NULL,
--    unit_list character varying(256),
--    unit_list_id character varying(12),
--
--    PRIMARY KEY (unit_id)
--);
--REVOKE ALL ON TABLE ndg.unit FROM PUBLIC;

CREATE TABLE ndg.spatial_coverage (
    spatial_coverage_id integer UNIQUE NOT NULL,
    area_code character varying(20) NOT NULL,

    PRIMARY KEY (spatial_coverage_id)
);
REVOKE ALL ON TABLE ndg.spatial_coverage FROM PUBLIC;


CREATE TABLE ndg.temporal_coverage (
    temporal_coverage_id integer UNIQUE NOT NULL,

    PRIMARY KEY (temporal_coverage_id)
);
REVOKE ALL ON TABLE ndg.temporal_coverage FROM PUBLIC;

CREATE TABLE ndg.date_range (
    date_range_id integer UNIQUE NOT NULL,
    date_start timestamp without time zone NOT NULL,
    date_end timestamp without time zone,
    temporal_coverage_id integer NOT NULL,

    PRIMARY KEY (date_range_id),
    FOREIGN KEY (temporal_coverage_id) REFERENCES ndg.temporal_coverage
);
REVOKE ALL ON TABLE ndg.date_range FROM PUBLIC;

CREATE TABLE ndg.date_instance (
    date_instance timestamp without time zone NOT NULL,
    temporal_coverage_id integer NOT NULL,

    PRIMARY KEY(date_instance, temporal_coverage_id),
    FOREIGN KEY(temporal_coverage_id) REFERENCES ndg.temporal_coverage
);
REVOKE ALL ON TABLE ndg.date_instance FROM PUBLIC;


CREATE TABLE ndg.coverage (
    coverage_id integer UNIQUE NOT NULL,
    spatial_coverage_id integer,
    temporal_coverage_id integer,

    PRIMARY KEY (coverage_id),
    FOREIGN KEY (spatial_coverage_id) REFERENCES ndg.spatial_coverage,
    FOREIGN KEY (temporal_coverage_id) REFERENCES ndg.temporal_coverage
);
REVOKE ALL ON TABLE ndg.coverage FROM PUBLIC;

CREATE TABLE ndg.parameter (
    parameter_id      integer UNIQUE NOT NULL,
    is_output         boolean                NOT NULL,
    standard_unit     integer                NOT NULL,
    high_value        real                   NOT NULL,
    low_value         real                   NOT NULL,
    parameter_name    character varying(100) NOT NULL,
    parameter_abbrev  character varying(32)  NOT NULL,

    PRIMARY KEY (parameter_id),
    FOREIGN KEY (standard_unit) REFERENCES ndg.structured_keyword
);
REVOKE ALL ON TABLE ndg.parameter FROM PUBLIC;

CREATE TABLE ndg.parameter_structured_keyword (
    structured_keyword_id integer NOT NULL,
    parameter_id integer NOT NULL,

    PRIMARY KEY (structured_keyword_id, parameter_id),
    FOREIGN KEY (structured_keyword_id) REFERENCES ndg.structured_keyword,
    FOREIGN KEY (parameter_id) REFERENCES ndg.parameter
);
REVOKE ALL ON TABLE ndg.parameter_structured_keyword FROM PUBLIC;


CREATE TABLE ndg.activity (
    activity_id integer UNIQUE NOT NULL,
    description character varying(50),
    start_date date NOT NULL,
    end_date date,
    coverage_id integer,

    PRIMARY KEY (activity_id),
    FOREIGN KEY (coverage_id) REFERENCES ndg.coverage
);
REVOKE ALL ON TABLE ndg.activity FROM PUBLIC;

CREATE TABLE ndg.activity_type_list (
    activity_id integer UNIQUE NOT NULL,
    activity_type character varying(40) NOT NULL,

    PRIMARY KEY(activity_id, activity_type),
    FOREIGN KEY(activity_id) REFERENCES ndg.activity,
    CHECK (activity_type IN ('dgActivityDataCollection',
                             'dgActivityDataProject',
                             'dgActivityDataCampaign',
                             'dgActivityDataInvestigation_Cruise',
                             'dgActivityDataInvestigation_Flight'))
);
REVOKE ALL ON TABLE ndg.activity_type_list FROM PUBLIC;


CREATE TABLE ndg.activity_role (
    activity_id integer NOT NULL,
    role_id integer NOT NULL,
    role_type character varying(50),

    PRIMARY KEY (activity_id, role_id),
    FOREIGN KEY (activity_id) REFERENCES activity,
    FOREIGN KEY (role_id) REFERENCES ndg.role
);
REVOKE ALL ON TABLE ndg.activity_role FROM PUBLIC;

CREATE TABLE ndg.activity_relationships (
    activity_id integer NOT NULL,
    related_activity_id integer NOT NULL,
    relation_keyword_id integer NOT NULL,

    PRIMARY KEY(activity_id, related_activity_id),
    FOREIGN KEY(activity_id) REFERENCES ndg.activity,
    FOREIGN KEY(related_activity_id) REFERENCES ndg.activity,
    FOREIGN KEY(relation_keyword_id) REFERENCES ndg.structured_keyword
);
REVOKE ALL ON TABLE ndg.activity_relationships FROM PUBLIC;

CREATE TABLE ndg.observation_stn (
    observation_stn_id integer UNIQUE NOT NULL,
    description character varying(50) NOT NULL,
    contact_details_id integer,

    PRIMARY KEY (observation_stn_id),
    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
);
REVOKE ALL ON TABLE ndg.observation_stn FROM PUBLIC;

CREATE TABLE ndg.production_tool (
    production_tool_id integer UNIQUE NOT NULL,
    description character varying(50) NOT NULL,
    contact_details_id integer,

    PRIMARY KEY (production_tool_id),
    FOREIGN KEY (contact_details_id) REFERENCES ndg.contact_details
);
REVOKE ALL ON TABLE ndg.production_tool FROM PUBLIC;


CREATE TABLE ndg.deployment (
    deployment_id integer UNIQUE NOT NULL,
    descriptive_name character varying(50),
    date_start date NOT NULL,
    date_end date,
    activity_id integer,
    production_tool_id integer,
    observation_stn_id integer,

    PRIMARY KEY (deployment_id),
    FOREIGN KEY (activity_id) REFERENCES ndg.activity,
    FOREIGN KEY (production_tool_id) REFERENCES ndg.production_tool,
    FOREIGN KEY (observation_stn_id) REFERENCES ndg.observation_stn
);
REVOKE ALL ON TABLE ndg.deployment FROM PUBLIC;


--CREATE TABLE ndg.dataset_type (
--    dataset_type_id integer UNIQUE NOT NULL,
--    dataset_type_name character varying(50) NOT NULL,
--
--    PRIMARY KEY (dataset_type_id)
--);
--REVOKE ALL ON TABLE ndg.dataset_type FROM PUBLIC;
--INSERT INTO ndg.dataset_type VALUES (0, 'dgSimulation');
--INSERT INTO ndg.dataset_type VALUES (1, 'dgAnalysis');
--INSERT INTO ndg.dataset_type VALUES (2, 'dgMeasurement');


--CREATE TABLE ndg.object_type (
--    object_type_id   integer               NOT NULL,
--    object_type_name character varying(50) NOT NULL,
--    derived_type     boolean               NOT NULL,
--
--    PRIMARY KEY (object_type_id)
--);
--REVOKE ALL ON TABLE ndg.object_type FROM PUBLIC;
--INSERT INTO ndg.object_type VALUES(0, 'dgEnsemble',            false);
--INSERT INTO ndg.object_type VALUES(1, 'dgLaGrangianPath',      false);
--INSERT INTO ndg.object_type VALUES(2, 'dgN-DimensionalDataset',false);
--INSERT INTO ndg.object_type VALUES(3, 'dgSample',              false);
--INSERT INTO ndg.object_type VALUES(4, 'dgProfile',             false);
--INSERT INTO ndg.object_type VALUES(5, 'dgSection',             false);
--INSERT INTO ndg.object_type VALUES(6, 'dgClimatology',         true);
--INSERT INTO ndg.object_type VALUES(7, 'dgTimeSeries',          true);
--INSERT INTO ndg.object_type VALUES(8, 'dgIntegration',         true);


--CREATE TABLE ndg.dataset_status (
--   dataset_status_id    integer               NOT NULL,
--    dataset_status_desc  character varying(32) NOT NULL,
--
--    PRIMARY KEY(dataset_status_id)
--);
--REVOKE ALL ON TABLE ndg.dataset_status FROM PUBLIC;
--INSERT INTO ndg.dataset_status VALUES (0, 'no_data');
--INSERT INTO ndg.dataset_status VALUES (1, 'complete');
--INSERT INTO ndg.dataset_status VALUES (2, 'updating');
--INSERT INTO ndg.dataset_status VALUES (3, 'incomplete');

-- Todo: Data Granule stuff
CREATE TABLE ndg.data_entity (
    data_entity_id integer UNIQUE NOT NULL,
    dataset_type character varying(50),
    object_type character varying(50),
    dataset_status character varying(50),
    coverage_id integer,

    PRIMARY KEY (data_entity_id),
    FOREIGN KEY (coverage_id) REFERENCES ndg.coverage
);
REVOKE ALL ON TABLE ndg.data_entity FROM PUBLIC;

CREATE TABLE ndg.data_entity_parameter (
    data_entity_id integer NOT NULL,
    parameter_id integer NOT NULL,
    
    PRIMARY KEY (data_entity_id, parameter_id),
    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
    FOREIGN KEY (parameter_id) REFERENCES ndg.parameter
);
REVOKE ALL ON TABLE ndg.data_entity_parameter FROM PUBLIC;

CREATE TABLE ndg.data_entity_deployment (
    data_entity_id integer NOT NULL,
    deployment_id integer NOT NULL,

    PRIMARY KEY (data_entity_id, deployment_id),
    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
    FOREIGN KEY (deployment_id) REFERENCES ndg.deployment
);
REVOKE ALL ON TABLE ndg.data_entity_deployment FROM PUBLIC;

CREATE TABLE ndg.data_entity_role (
    data_entity_id integer NOT NULL,
    role_id integer NOT NULL,
    role_type character varying(50),
    

    PRIMARY KEY (data_entity_id, role_id),
    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
    FOREIGN KEY (role_id) REFERENCES ndg.role
);
REVOKE ALL ON TABLE ndg.data_entity_role FROM PUBLIC;

CREATE TABLE ndg.metadata_record (
    metadata_record_id  integer UNIQUE NOT NULL,
    metadata_desc_id    integer,
    name                character varying(250) NOT NULL,
    abbreviation        character varying(50),
    
    activity_id         integer,
    data_entity_id      integer,
    production_tool_id  integer,
    observation_stn_id  integer,

    PRIMARY KEY (metadata_record_id),
    FOREIGN KEY (metadata_desc_id)   REFERENCES ndg.metadata_desc,
    FOREIGN KEY (activity_id)        REFERENCES ndg.activity,
    FOREIGN KEY (data_entity_id)     REFERENCES ndg.data_entity,
    FOREIGN KEY (production_tool_id) REFERENCES ndg.production_tool,
    FOREIGN KEY (observation_stn_id) REFERENCES ndg.observation_stn
);
REVOKE ALL ON TABLE ndg.metadata_record FROM PUBLIC;


CREATE TABLE ndg.metadata_structured_keyword (
    metadata_record_id integer NOT NULL,
    structured_keyword_id integer NOT NULL,

    PRIMARY KEY (metadata_record_id, structured_keyword_id),
    FOREIGN KEY (metadata_record_id) REFERENCES ndg.metadata_record,
    FOREIGN KEY (structured_keyword_id) REFERENCES ndg.structured_keyword
);
REVOKE ALL ON TABLE ndg.metadata_structured_keyword FROM PUBLIC;

-- Todo: Granule stuff.
--CREATE TABLE ndg.data_granules (
--    granule_id integer NOT NULL,
--    access_control_policy_id integer,
--    unused integer
--);
--REVOKE ALL ON TABLE ndg.data_granules FROM PUBLIC;

-- Todo: I dont think we need this. :- mhen
-- CREATE TABLE ndg.roles_role_holders (
--    role_id integer NOT NULL,
--    role_holder_id integer NOT NULL
-- );
-- REVOKE ALL ON TABLE ndg.roles_role_holders FROM PUBLIC;

-- provides string representation for each role type (creator, curator, project manager, etc.)

-- Todo: Sort out granule stuff.
--CREATE TABLE ndg.data_granule (
--    data_granule_id integer NOT NULL,
--    URI character varying(256) NOT NULL,
--    scheme_type_id integer NOT NULL,
--    id_of_parent_granule integer NOT NULL
--);
--REVOKE ALL ON TABLE ndg.data_granule FROM PUBLIC;

-- Todo: ACP stuff
--CREATE TABLE ndg.access_control_policies (
--    policy_id integer NOT NULL,
--    policy_url character varying NOT NULL,
--    policy_text character varying NOT NULL
--);
--REVOKE ALL ON TABLE ndg.access_control_policies FROM PUBLIC;

-- Todo: Data Granule Stuff
--CREATE TABLE ndg.data_entity_data_granule (
--    data_entity_id integer NOT NULL,
--    data_granule_id integer NOT NULL,

--    PRIMARY KEY (data_entity_id, data_granule_id),
--    FOREIGN KEY (data_entity_id) REFERENCES ndg.data_entity,
--    FOREIGN KEY (data_grangule_id) REFERENCES ndg.data_granule
--);
--REVOKE ALL ON TABLE ndg.data_entity_data_granule FROM PUBLIC;