--
--


-- Started on 2017-05-18 08:24:30 CEST

-- SET check_function_bodies = false;
-- SET client_min_messages = warning;
-- SET row_security = off;

--
-- TOC entry 10 (class 2615 OID 49542)
-- Name: app; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA app;


ALTER SCHEMA app OWNER TO postgres;


--
-- TOC entry 254 (class 1259 OID 49568)
-- Name: navtree_itemid_seq; Type: SEQUENCE; Schema: app; Owner: postgres
--

CREATE SEQUENCE navtree_itemid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE navtree_itemid_seq OWNER TO postgres;


--
-- TOC entry 253 (class 1259 OID 49549)
-- Name: navtree; Type: TABLE; Schema: app; Owner: postgres
--

CREATE TABLE navtree (
    itemid integer DEFAULT nextval('navtree_itemid_seq'::regclass) NOT NULL,
    text character varying(25) NOT NULL,
    title character varying NOT NULL,
    iconcls character varying(50) NOT NULL,
    classcolor character varying(25) DEFAULT 'plain'::character varying NOT NULL,
    viewtype character varying(100) DEFAULT 'panel'::character varying NOT NULL,
    route character varying(25),
    children boolean DEFAULT false NOT NULL,
    parentid integer DEFAULT 0 NOT NULL,
    "position" integer,
    modulename character varying(50)
);


ALTER TABLE navtree OWNER TO postgres;

--
-- TOC entry 255 (class 1259 OID 49571)
-- Name: role_roleid_seq; Type: SEQUENCE; Schema: app; Owner: postgres
--

CREATE SEQUENCE role_roleid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE role_roleid_seq OWNER TO postgres;

--
-- TOC entry 256 (class 1259 OID 49573)
-- Name: role; Type: TABLE; Schema: app; Owner: postgres
--

CREATE TABLE role (
    roleid integer DEFAULT nextval('role_roleid_seq'::regclass) NOT NULL,
    rolename character varying(100) NOT NULL,
    description character varying(150)
);


ALTER TABLE role OWNER TO postgres;

--
-- TOC entry 257 (class 1259 OID 49579)
-- Name: role_navtree; Type: TABLE; Schema: app; Owner: postgres
--

CREATE TABLE role_navtree (
    roleid integer NOT NULL,
    itemid integer NOT NULL,
    readwrite boolean DEFAULT false NOT NULL
);


ALTER TABLE role_navtree OWNER TO postgres;

--
-- TOC entry 262 (class 1259 OID 74221)
-- Name: spatialfilter; Type: TABLE; Schema: app; Owner: postgres
--

CREATE TABLE spatialfilter (
    gid integer NOT NULL,
    areatype character varying(50),
    areaname character varying(50),
    geom public.geometry(MultiPolygon,4326)
);


ALTER TABLE spatialfilter OWNER TO postgres;

--
-- TOC entry 261 (class 1259 OID 74219)
-- Name: spatialfilter_gid_seq; Type: SEQUENCE; Schema: app; Owner: postgres
--

CREATE SEQUENCE spatialfilter_gid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE spatialfilter_gid_seq OWNER TO postgres;

--
-- TOC entry 3667 (class 0 OID 0)
-- Dependencies: 261
-- Name: spatialfilter_gid_seq; Type: SEQUENCE OWNED BY; Schema: app; Owner: postgres
--

ALTER SEQUENCE spatialfilter_gid_seq OWNED BY spatialfilter.gid;


--
-- TOC entry 258 (class 1259 OID 65986)
-- Name: userid_seq; Type: SEQUENCE; Schema: app; Owner: postgres
--

CREATE SEQUENCE userid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE userid_seq OWNER TO postgres;

--
-- TOC entry 260 (class 1259 OID 74204)
-- Name: user_spatialfilter; Type: TABLE; Schema: app; Owner: postgres
--

CREATE TABLE user_spatialfilter (
    userid integer NOT NULL,
    gid integer NOT NULL
);


ALTER TABLE user_spatialfilter OWNER TO postgres;

--
-- TOC entry 3500 (class 2604 OID 74224)
-- Name: gid; Type: DEFAULT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY spatialfilter ALTER COLUMN gid SET DEFAULT nextval('spatialfilter_gid_seq'::regclass);


--
-- TOC entry 3648 (class 0 OID 49549)
-- Dependencies: 253
-- Data for Name: navtree; Type: TABLE DATA; Schema: app; Owner: postgres
--

INSERT INTO navtree VALUES (1, 'HOME', 'HOME', 'x-fa fa-home', 'blue', 'homeguest', 'home', false, 0, 0, 'homeguest');
INSERT INTO navtree VALUES (2, 'ABOUT', 'ABOUT', 'x-fa fa-info', 'blue', 'aboutcredits', 'about', false, 0, 200000, 'about');
INSERT INTO navtree VALUES (3, 'HOME', 'HOME', 'x-fa fa-home', 'blue', 'homehome', 'home', false, 0, 0, 'home');
INSERT INTO navtree VALUES (4, 'BASE DATA', 'DEFINITION OF TIME SERIES COMPONENTS', 'x-fa fa-database', 'blue', 'basedatadashboard', 'basedata', false, 0, 100, 'basedata');
INSERT INTO navtree VALUES (5, 'DATA ENTRY', 'MANUAL DATA ENTRY', 'x-fa fa-edit', 'blue', 'dataentry', 'dataentry', false, 0, 200, 'dataentry');
INSERT INTO navtree VALUES (6, 'TIME SERIES', 'TIME SERIES MANAGEMENT', 'x-fa fa-line-chart', 'blue', 'timeserieskeys', 'timeseries', false, 0, 300, 'timeseries');
INSERT INTO navtree VALUES (7, 'RATING CURVES', 'STAGE-DISCHARGE COEFICIENTS', 'x-fa fa-stack-overflow', 'blue', 'ratingcurveseditor', 'ratingcurves', false, 0, 400, 'raitingcurves');
INSERT INTO navtree VALUES (8, 'VISUALIZATION', 'VISUALIZATION', 'x-fa fa-desktop', 'blue', 'panel', NULL, true, 0, 500, 'visualization');
INSERT INTO navtree VALUES (9, 'ANALYSIS', 'FUNCTIONAL ANALYSIS OF TIME SERIES', 'x-fa fa-sliders', 'blue', 'visualizationanalysis', 'analysis', false, 8, 510, 'visualizationanalysis');
INSERT INTO navtree VALUES (10, 'CROSSTABS', 'AGGRGATED TIME SERIES DISPLAY', 'x-fa fa-table', 'blue', 'visualizationcrosstabs', 'crosstabs', false, 8, 520, 'visualizationcrosstabs');
INSERT INTO navtree VALUES (11, 'HELP', 'HELP', 'x-fa fa-question', 'blue', 'helpbase', 'help', false, 0, 190000, 'help');
INSERT INTO navtree VALUES (12, 'MANAGEMENT', 'USER ACCOUNTS AND USER ROLES', 'x-fa fa-user', 'blue', 'managementbase', 'management', false, 0, 10000, 'management');


--
-- TOC entry 3651 (class 0 OID 49573)
-- Dependencies: 256
-- Data for Name: role; Type: TABLE DATA; Schema: app; Owner: postgres
--

INSERT INTO role VALUES (1, 'admin', 'User Administration');


--
-- TOC entry 3652 (class 0 OID 49579)
-- Dependencies: 257
-- Data for Name: role_navtree; Type: TABLE DATA; Schema: app; Owner: postgres
--

INSERT INTO role_navtree VALUES (1, 12, false);


--
-- TOC entry 3657 (class 0 OID 74221)
-- Dependencies: 262
-- Data for Name: spatialfilter; Type: TABLE DATA; Schema: app; Owner: postgres
--

INSERT INTO spatialfilter VALUES (1, 'World', 'All the Surface of the Earth', '0106000020E6100000010000000103000000010000000500000000000000008066C000000000000054C000000000008066C0000000000000544000000000008066400000000000005440000000000080664000000000000054C000000000008066C000000000000054C0');


--
-- TOC entry 3502 (class 2606 OID 49558)
-- Name: pk_navtree; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY navtree
    ADD CONSTRAINT pk_navtree PRIMARY KEY (itemid);


--
-- TOC entry 3510 (class 2606 OID 49593)
-- Name: pk_navtree_role; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY role_navtree
    ADD CONSTRAINT pk_navtree_role PRIMARY KEY (roleid, itemid);


--
-- TOC entry 3506 (class 2606 OID 49578)
-- Name: pk_role; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY role
    ADD CONSTRAINT pk_role PRIMARY KEY (roleid);


--
-- TOC entry 3516 (class 2606 OID 74218)
-- Name: pk_user_spatialfilter; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY user_spatialfilter
    ADD CONSTRAINT pk_user_spatialfilter PRIMARY KEY (userid, gid);


--
-- TOC entry 3518 (class 2606 OID 74229)
-- Name: spf_pkey; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY spatialfilter
    ADD CONSTRAINT spf_pkey PRIMARY KEY (gid);


--
-- TOC entry 3504 (class 2606 OID 74245)
-- Name: unique_modulename; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY navtree
    ADD CONSTRAINT unique_modulename UNIQUE (modulename);


--
-- TOC entry 3508 (class 2606 OID 66009)
-- Name: unique_rolename; Type: CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY role
    ADD CONSTRAINT unique_rolename UNIQUE (rolename);


--
-- TOC entry 3523 (class 2606 OID 74233)
-- Name: fk_gid; Type: FK CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY user_spatialfilter
    ADD CONSTRAINT fk_gid FOREIGN KEY (gid) REFERENCES spatialfilter(gid);


--
-- TOC entry 3519 (class 2606 OID 49582)
-- Name: fk_navtree_itemid; Type: FK CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY role_navtree
    ADD CONSTRAINT fk_navtree_itemid FOREIGN KEY (itemid) REFERENCES navtree(itemid);


--
-- TOC entry 3520 (class 2606 OID 49587)
-- Name: fk_role_roleid; Type: FK CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY role_navtree
    ADD CONSTRAINT fk_role_roleid FOREIGN KEY (roleid) REFERENCES role(roleid);


--
-- TOC entry 3522 (class 2606 OID 74207)
-- Name: fk_userid; Type: FK CONSTRAINT; Schema: app; Owner: postgres
--

ALTER TABLE ONLY user_spatialfilter
    ADD CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES "user"(userid);


--
-- TOC entry 3662 (class 0 OID 0)
-- Dependencies: 10
-- Name: app; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA app FROM PUBLIC;
REVOKE ALL ON SCHEMA app FROM postgres;
GRANT ALL ON SCHEMA app TO postgres;
GRANT USAGE ON SCHEMA app TO webapp;


--
-- TOC entry 3663 (class 0 OID 0)
-- Dependencies: 253
-- Name: navtree; Type: ACL; Schema: app; Owner: postgres
--

REVOKE ALL ON TABLE navtree FROM PUBLIC;
REVOKE ALL ON TABLE navtree FROM postgres;
GRANT ALL ON TABLE navtree TO postgres;
GRANT SELECT ON TABLE navtree TO webapp;


--
-- TOC entry 3664 (class 0 OID 0)
-- Dependencies: 256
-- Name: role; Type: ACL; Schema: app; Owner: postgres
--

REVOKE ALL ON TABLE role FROM PUBLIC;
REVOKE ALL ON TABLE role FROM postgres;
GRANT ALL ON TABLE role TO postgres;
GRANT SELECT ON TABLE role TO webapp;


--
-- TOC entry 3665 (class 0 OID 0)
-- Dependencies: 257
-- Name: role_navtree; Type: ACL; Schema: app; Owner: postgres
--

REVOKE ALL ON TABLE role_navtree FROM PUBLIC;
REVOKE ALL ON TABLE role_navtree FROM postgres;
GRANT ALL ON TABLE role_navtree TO postgres;
GRANT SELECT ON TABLE role_navtree TO webapp;


--
-- TOC entry 3666 (class 0 OID 0)
-- Dependencies: 262
-- Name: spatialfilter; Type: ACL; Schema: app; Owner: postgres
--

REVOKE ALL ON TABLE spatialfilter FROM PUBLIC;
REVOKE ALL ON TABLE spatialfilter FROM postgres;
GRANT ALL ON TABLE spatialfilter TO postgres;
GRANT SELECT ON TABLE spatialfilter TO webapp;


--
-- TOC entry 3668 (class 0 OID 0)
-- Dependencies: 259
-- Name: user; Type: ACL; Schema: app; Owner: postgres
--

REVOKE ALL ON TABLE "user" FROM PUBLIC;
REVOKE ALL ON TABLE "user" FROM postgres;
GRANT ALL ON TABLE "user" TO postgres;
GRANT SELECT ON TABLE "user" TO webapp;


--
-- TOC entry 3669 (class 0 OID 0)
-- Dependencies: 260
-- Name: user_spatialfilter; Type: ACL; Schema: app; Owner: postgres
--

REVOKE ALL ON TABLE user_spatialfilter FROM PUBLIC;
REVOKE ALL ON TABLE user_spatialfilter FROM postgres;
GRANT ALL ON TABLE user_spatialfilter TO postgres;
GRANT SELECT ON TABLE user_spatialfilter TO webapp;


-- Completed on 2017-05-18 08:24:30 CEST

--
--

