--
-- Name: app_catalog; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE app_catalog (
    row_id serial NOT NULL,
    short_name character varying(50) NOT NULL,
    long_name character varying(200),
    description character varying(1000)
);


ALTER TABLE public.app_catalog OWNER TO postgres;

--
-- Name: m_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE m_type (
    row_id integer NOT NULL,
    obs_type_id integer,
    uom_type_id integer
);


ALTER TABLE public.m_type OWNER TO postgres;

--
-- Name: multi_obs; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE multi_obs (
    row_id serial NOT NULL,
    row_entry_date timestamp without time zone,
    row_update_date timestamp without time zone,
    platform_handle character varying(100) NOT NULL,
    sensor_id integer NOT NULL,
    m_type_id integer NOT NULL,
    m_date timestamp without time zone,
    m_lon double precision,
    m_lat double precision,
    m_z double precision,
    m_value double precision,
    m_desc character varying(50),
    qc_level integer,
    qc_flag character varying(100)
);


ALTER TABLE public.multi_obs OWNER TO postgres;

--
-- Name: obs_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE obs_type (
    row_id integer NOT NULL,
    standard_name character varying(50),
    definition character varying(1000)
);


ALTER TABLE public.obs_type OWNER TO postgres;

--
-- Name: organization; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE organization (
    row_id serial NOT NULL,
    short_name character varying(50) NOT NULL,
    active boolean,
    long_name character varying(200),
    description character varying(1000),
    url character varying(200),
    opendap_url character varying(200)
);


ALTER TABLE public.organization OWNER TO postgres;

--
-- Name: platform; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE platform (
    row_id serial NOT NULL,
    organization_id integer NOT NULL,
    type_id integer NOT NULL,
    short_name character varying(50) NOT NULL,
    platform_handle character varying(100),
    fixed_longitude double precision,
    fixed_latitude double precision,
    active boolean,
    begin_date timestamp without time zone,
    end_date timestamp without time zone,
    project_id integer,
    app_catalog_id integer,
    long_name character varying(200),
    description character varying(1000),
    url character varying(200)
);


ALTER TABLE public.platform OWNER TO postgres;

--
-- Name: platform_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE platform_type (
    row_id serial NOT NULL,
    type_name character varying(50) NOT NULL,
    description character varying(1000)
);


ALTER TABLE public.platform_type OWNER TO postgres;

--
-- Name: project; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE project (
    row_id serial NOT NULL,
    short_name character varying(50) NOT NULL,
    long_name character varying(200),
    description character varying(1000)
);


ALTER TABLE public.project OWNER TO postgres;

--
-- Name: sensor; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sensor (
    row_id serial NOT NULL,
    platform_id integer NOT NULL,
    type_id integer NOT NULL,
    short_name character varying(50) NOT NULL,
    m_type_id integer NOT NULL,
    fixed_z double precision,
    active boolean,
    begin_date timestamp without time zone,
    end_date timestamp without time zone,
    s_order integer NOT NULL,
    url character varying(200)
);


ALTER TABLE public.sensor OWNER TO postgres;

--
-- Name: sensor_attr; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sensor_attr (
    row_id serial NOT NULL,
    sensor_id integer NOT NULL,
    attr_name character varying(50),
    attr_value character varying(50)
);


ALTER TABLE public.sensor_attr OWNER TO postgres;

--
-- Name: sensor_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE sensor_type (
    row_id serial NOT NULL,
    type_name character varying(50) NOT NULL,
    description character varying(1000)
);


ALTER TABLE public.sensor_type OWNER TO postgres;

--
-- Name: uom_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE uom_type (
    row_id integer NOT NULL,
    standard_name character varying(50),
    definition character varying(1000),
    display character varying(50)
);


ALTER TABLE public.uom_type OWNER TO postgres;

-- table m_type_display_order is used to determine the order in which measurement types are listed for various displays

CREATE TABLE m_type_display_order (
    row_id serial NOT NULL,
    m_type_id integer NOT NULL
);
ALTER TABLE public.m_type_display_order OWNER TO postgres;
ALTER TABLE ONLY m_type_display_order
    ADD CONSTRAINT m_type_display_order_pkey PRIMARY KEY (row_id);
ALTER INDEX public.m_type_display_order_pkey OWNER TO postgres;

--
-- Name: app_catalog_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY app_catalog
    ADD CONSTRAINT app_catalog_pkey PRIMARY KEY (row_id);


ALTER INDEX public.app_catalog_pkey OWNER TO postgres;

--
-- Name: m_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY m_type
    ADD CONSTRAINT m_type_pkey PRIMARY KEY (row_id);


ALTER INDEX public.m_type_pkey OWNER TO postgres;

--
-- Name: multi_obs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY multi_obs
    ADD CONSTRAINT multi_obs_pkey PRIMARY KEY (row_id);


ALTER INDEX public.multi_obs_pkey OWNER TO postgres;

--
-- Name: obs_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY obs_type
    ADD CONSTRAINT obs_type_pkey PRIMARY KEY (row_id);


ALTER INDEX public.obs_type_pkey OWNER TO postgres;

--
-- Name: organization_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY organization
    ADD CONSTRAINT organization_pkey PRIMARY KEY (row_id);


ALTER INDEX public.organization_pkey OWNER TO postgres;

--
-- Name: platform_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY platform
    ADD CONSTRAINT platform_pkey PRIMARY KEY (row_id);


ALTER INDEX public.platform_pkey OWNER TO postgres;

--
-- Name: platform_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY platform_type
    ADD CONSTRAINT platform_type_pkey PRIMARY KEY (row_id);


ALTER INDEX public.platform_type_pkey OWNER TO postgres;

--
-- Name: project_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY project
    ADD CONSTRAINT project_pkey PRIMARY KEY (row_id);


ALTER INDEX public.project_pkey OWNER TO postgres;

--
-- Name: sensor_attr_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sensor_attr
    ADD CONSTRAINT sensor_attr_pkey PRIMARY KEY (row_id);


ALTER INDEX public.sensor_attr_pkey OWNER TO postgres;

--
-- Name: sensor_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sensor
    ADD CONSTRAINT sensor_pkey PRIMARY KEY (row_id);


ALTER INDEX public.sensor_pkey OWNER TO postgres;

--
-- Name: sensor_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY sensor_type
    ADD CONSTRAINT sensor_type_pkey PRIMARY KEY (row_id);


ALTER INDEX public.sensor_type_pkey OWNER TO postgres;

--
-- Name: uom_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY uom_type
    ADD CONSTRAINT uom_type_pkey PRIMARY KEY (row_id);


ALTER INDEX public.uom_type_pkey OWNER TO postgres;

--
-- Name: i_multi_obs; Type: INDEX; Schema: public; Owner: postgres; Tablespace: 
--

CREATE UNIQUE INDEX i_multi_obs ON multi_obs USING btree (m_type_id, m_date, sensor_id);


ALTER INDEX public.i_multi_obs OWNER TO postgres;

--
-- Name: m_type_obs_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY m_type
    ADD CONSTRAINT m_type_obs_type_id_fkey FOREIGN KEY (obs_type_id) REFERENCES obs_type(row_id);


--
-- Name: m_type_uom_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY m_type
    ADD CONSTRAINT m_type_uom_type_id_fkey FOREIGN KEY (uom_type_id) REFERENCES uom_type(row_id);


--
-- Name: multi_obs_m_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY multi_obs
    ADD CONSTRAINT multi_obs_m_type_id_fkey FOREIGN KEY (m_type_id) REFERENCES m_type(row_id);


--
-- Name: multi_obs_sensor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY multi_obs
    ADD CONSTRAINT multi_obs_sensor_id_fkey FOREIGN KEY (sensor_id) REFERENCES sensor(row_id);


--
-- Name: platform_app_catalog_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY platform
    ADD CONSTRAINT platform_app_catalog_id_fkey FOREIGN KEY (app_catalog_id) REFERENCES app_catalog(row_id);


--
-- Name: platform_organization_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY platform
    ADD CONSTRAINT platform_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organization(row_id);


--
-- Name: platform_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY platform
    ADD CONSTRAINT platform_project_id_fkey FOREIGN KEY (project_id) REFERENCES project(row_id);


--
-- Name: platform_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY platform
    ADD CONSTRAINT platform_type_id_fkey FOREIGN KEY (type_id) REFERENCES platform_type(row_id);


--
-- Name: sensor_attr_sensor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sensor_attr
    ADD CONSTRAINT sensor_attr_sensor_id_fkey FOREIGN KEY (sensor_id) REFERENCES sensor(row_id);


--
-- Name: sensor_m_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sensor
    ADD CONSTRAINT sensor_m_type_id_fkey FOREIGN KEY (m_type_id) REFERENCES m_type(row_id);


--
-- Name: sensor_platform_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sensor
    ADD CONSTRAINT sensor_platform_id_fkey FOREIGN KEY (platform_id) REFERENCES platform(row_id);


--
-- Name: sensor_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY sensor
    ADD CONSTRAINT sensor_type_id_fkey FOREIGN KEY (type_id) REFERENCES sensor_type(row_id);


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--