WF/maisexemplos: Sistema_Normativo_12.sql

--
-- PostgreSQL database dump
--

-- Started on 2007-11-26 14:34:37 BRST

SET client_encoding = 'LATIN1';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- TOC entry 11 (class 2615 OID 42906)
-- Name: normas; Type: SCHEMA; Schema: -; Owner: admin_normas
--

CREATE USER admin_normas WITH PASSWORD 'admin_normas' NOCREATEDB NOCREATEUSER VALID UNTIL 'infinity';

CREATE SCHEMA normas;


ALTER SCHEMA normas OWNER TO admin_normas;

SET search_path = normas, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1591 (class 1259 OID 42943)
-- Dependencies: 2222 11
-- Name: arquivo; Type: TABLE; Schema: normas; Owner: admin_normas; Tablespace: 
--

CREATE TABLE arquivo (
    arquivo_id smallint DEFAULT nextval(('normas.seq_arquivo'::text)::regclass) NOT NULL,
    conteudo bytea,
    norma_id smallint NOT NULL,
    nome character varying(50),
    tipo character varying(50),
    tamanho integer,
    is_anexo smallint
);


ALTER TABLE normas.arquivo OWNER TO admin_normas;

--
-- TOC entry 1592 (class 1259 OID 42949)
-- Dependencies: 2223 11
-- Name: macroprocesso; Type: TABLE; Schema: normas; Owner: admin_normas; Tablespace: 
--

CREATE TABLE macroprocesso (
    macroprocesso_id smallint DEFAULT nextval(('normas.seq_macroprocesso'::text)::regclass) NOT NULL,
    sigla character(2),
    descricao character varying(40)
);


ALTER TABLE normas.macroprocesso OWNER TO admin_normas;

--
-- TOC entry 1593 (class 1259 OID 42952)
-- Dependencies: 2224 11
-- Name: norma; Type: TABLE; Schema: normas; Owner: admin_normas; Tablespace: 
--

CREATE TABLE norma (
    norma_id smallint DEFAULT nextval(('normas.seq_norma'::text)::regclass) NOT NULL,
    area_gestora_id smallint,
    diretoria_id smallint,
    diretor_juridico_id integer,
    elaborador_id integer,
    gerente_id integer,
    diretor_id integer,
    titulo character varying(150),
    objetivo text,
    palavras_chave character varying(100),
    tipo_documento_id smallint,
    processo_id smallint,
    macroprocesso_id smallint,
    data_vigencia timestamp without time zone,
    historico text,
    ambito_aplicacao integer,
    area_elaborador_id smallint NOT NULL,
    ultima_revisao timestamp without time zone,
    status smallint,
    num_revisao smallint,
    sequencia smallint
);


ALTER TABLE normas.norma OWNER TO admin_normas;

--
-- TOC entry 1684 (class 1259 OID 53119)
-- Dependencies: 2226 11
-- Name: processo; Type: TABLE; Schema: normas; Owner: admin_normas; Tablespace: 
--

CREATE TABLE processo (
    processo_id smallint DEFAULT nextval(('normas.seq_processo'::text)::regclass) NOT NULL,
    macroprocesso_id smallint NOT NULL,
    descricao character varying(40)
);


ALTER TABLE normas.processo OWNER TO admin_normas;

--
-- TOC entry 1686 (class 1259 OID 53132)
-- Dependencies: 11
-- Name: seq_arquivo; Type: SEQUENCE; Schema: normas; Owner: admin_normas
--

CREATE SEQUENCE seq_arquivo
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE normas.seq_arquivo OWNER TO admin_normas;

--
-- TOC entry 1665 (class 1259 OID 50693)
-- Dependencies: 11
-- Name: seq_macroprocesso; Type: SEQUENCE; Schema: normas; Owner: admin_normas
--

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


ALTER TABLE normas.seq_macroprocesso OWNER TO admin_normas;

--
-- TOC entry 1685 (class 1259 OID 53130)
-- Dependencies: 11
-- Name: seq_norma; Type: SEQUENCE; Schema: normas; Owner: admin_normas
--

CREATE SEQUENCE seq_norma
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE normas.seq_norma OWNER TO admin_normas;

--
-- TOC entry 1666 (class 1259 OID 50719)
-- Dependencies: 11
-- Name: seq_processo; Type: SEQUENCE; Schema: normas; Owner: admin_normas
--

CREATE SEQUENCE seq_processo
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE normas.seq_processo OWNER TO admin_normas;

--
-- TOC entry 1667 (class 1259 OID 50726)
-- Dependencies: 11
-- Name: seq_tipo_documento; Type: SEQUENCE; Schema: normas; Owner: admin_normas
--

CREATE SEQUENCE seq_tipo_documento
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE normas.seq_tipo_documento OWNER TO admin_normas;

--
-- TOC entry 1594 (class 1259 OID 42961)
-- Dependencies: 2225 11
-- Name: tipo_documento; Type: TABLE; Schema: normas; Owner: admin_normas; Tablespace: 
--

CREATE TABLE tipo_documento (
    tipo_documento_id smallint DEFAULT nextval(('normas.seq_tipo_documento'::text)::regclass) NOT NULL,
    sigla character(2),
    descricao character varying(40)
);


ALTER TABLE normas.tipo_documento OWNER TO admin_normas;

--
-- TOC entry 2228 (class 2606 OID 53091)
-- Dependencies: 1591 1591
-- Name: pk_arquivo; Type: CONSTRAINT; Schema: normas; Owner: admin_normas; Tablespace: 
--

ALTER TABLE ONLY arquivo
    ADD CONSTRAINT pk_arquivo PRIMARY KEY (arquivo_id);


--
-- TOC entry 2230 (class 2606 OID 53089)
-- Dependencies: 1592 1592
-- Name: pk_macroprocesso; Type: CONSTRAINT; Schema: normas; Owner: admin_normas; Tablespace: 
--

ALTER TABLE ONLY macroprocesso
    ADD CONSTRAINT pk_macroprocesso PRIMARY KEY (macroprocesso_id);


--
-- TOC entry 2232 (class 2606 OID 53085)
-- Dependencies: 1593 1593
-- Name: pk_norma; Type: CONSTRAINT; Schema: normas; Owner: admin_normas; Tablespace: 
--

ALTER TABLE ONLY norma
    ADD CONSTRAINT pk_norma PRIMARY KEY (norma_id);


--
-- TOC entry 2236 (class 2606 OID 53123)
-- Dependencies: 1684 1684
-- Name: pk_processo; Type: CONSTRAINT; Schema: normas; Owner: admin_normas; Tablespace: 
--

ALTER TABLE ONLY processo
    ADD CONSTRAINT pk_processo PRIMARY KEY (processo_id);


--
-- TOC entry 2234 (class 2606 OID 53093)
-- Dependencies: 1594 1594
-- Name: pk_tipo_documento; Type: CONSTRAINT; Schema: normas; Owner: admin_normas; Tablespace: 
--

ALTER TABLE ONLY tipo_documento
    ADD CONSTRAINT pk_tipo_documento PRIMARY KEY (tipo_documento_id);


--
-- TOC entry 2239 (class 2606 OID 53094)
-- Dependencies: 2229 1592 1593
-- Name: fk_macroprocesso; Type: FK CONSTRAINT; Schema: normas; Owner: admin_normas
--

ALTER TABLE ONLY norma
    ADD CONSTRAINT fk_macroprocesso FOREIGN KEY (macroprocesso_id) REFERENCES macroprocesso(macroprocesso_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 2240 (class 2606 OID 53124)
-- Dependencies: 2229 1592 1684
-- Name: fk_macroprocesso; Type: FK CONSTRAINT; Schema: normas; Owner: admin_normas
--

ALTER TABLE ONLY processo
    ADD CONSTRAINT fk_macroprocesso FOREIGN KEY (macroprocesso_id) REFERENCES macroprocesso(macroprocesso_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 2237 (class 2606 OID 53109)
-- Dependencies: 2231 1593 1591
-- Name: fk_norma; Type: FK CONSTRAINT; Schema: normas; Owner: admin_normas
--

ALTER TABLE ONLY arquivo
    ADD CONSTRAINT fk_norma FOREIGN KEY (norma_id) REFERENCES norma(norma_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 2238 (class 2606 OID 53104)
-- Dependencies: 2233 1594 1593
-- Name: fk_tipo_documento; Type: FK CONSTRAINT; Schema: normas; Owner: admin_normas
--

ALTER TABLE ONLY norma
    ADD CONSTRAINT fk_tipo_documento FOREIGN KEY (tipo_documento_id) REFERENCES tipo_documento(tipo_documento_id) ON UPDATE RESTRICT ON DELETE RESTRICT;


--
-- TOC entry 2242 (class 0 OID 0)
-- Dependencies: 11
-- Name: normas; Type: ACL; Schema: -; Owner: admin_normas
--

REVOKE ALL ON SCHEMA normas FROM PUBLIC;


--
-- TOC entry 2243 (class 0 OID 0)
-- Dependencies: 1591
-- Name: arquivo; Type: ACL; Schema: normas; Owner: admin_normas
--

REVOKE ALL ON TABLE arquivo FROM PUBLIC;
REVOKE ALL ON TABLE arquivo FROM admin_normas;
GRANT ALL ON TABLE arquivo TO admin_normas;
GRANT ALL ON TABLE arquivo TO admin_workflow;


--
-- TOC entry 2244 (class 0 OID 0)
-- Dependencies: 1592
-- Name: macroprocesso; Type: ACL; Schema: normas; Owner: admin_normas
--

REVOKE ALL ON TABLE macroprocesso FROM PUBLIC;
REVOKE ALL ON TABLE macroprocesso FROM admin_normas;
GRANT ALL ON TABLE macroprocesso TO admin_normas;
GRANT ALL ON TABLE macroprocesso TO admin_workflow;


--
-- TOC entry 2245 (class 0 OID 0)
-- Dependencies: 1593
-- Name: norma; Type: ACL; Schema: normas; Owner: admin_normas
--

REVOKE ALL ON TABLE norma FROM PUBLIC;
REVOKE ALL ON TABLE norma FROM admin_normas;
GRANT ALL ON TABLE norma TO admin_normas;
GRANT ALL ON TABLE norma TO admin_workflow;


--
-- TOC entry 2246 (class 0 OID 0)
-- Dependencies: 1684
-- Name: processo; Type: ACL; Schema: normas; Owner: admin_normas
--

REVOKE ALL ON TABLE processo FROM PUBLIC;
REVOKE ALL ON TABLE processo FROM admin_normas;
GRANT ALL ON TABLE processo TO admin_normas;
GRANT ALL ON TABLE processo TO admin_workflow;


--
-- TOC entry 2247 (class 0 OID 0)
-- Dependencies: 1594
-- Name: tipo_documento; Type: ACL; Schema: normas; Owner: admin_normas
--

REVOKE ALL ON TABLE tipo_documento FROM PUBLIC;
REVOKE ALL ON TABLE tipo_documento FROM admin_normas;
GRANT ALL ON TABLE tipo_documento TO admin_normas;
GRANT ALL ON TABLE tipo_documento TO admin_workflow;


-- Completed on 2007-11-26 14:34:38 BRST

--
-- PostgreSQL database dump complete
--