--------------------------------------------------------------------------------
-- Instructions de base de l'applicatif 
--
-- @package openscrutin
-- @version SVN : $Id$
--------------------------------------------------------------------------------

--
-- PostgreSQL database dump
--

--
-- Name: affectation; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE affectation (
    affectation integer NOT NULL,
    elu integer NOT NULL,
    scrutin character varying(15) NOT NULL,
    periode character varying(20) NOT NULL,
    poste character varying(20) NOT NULL,
    bureau character varying(4) NOT NULL,
    note text,
    decision boolean,
    candidat integer
);


--
-- Name: affectation_seq; Type: SEQUENCE; Schema: openscrutin; Owner: -
--

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


--
-- Name: affectation_seq; Type: SEQUENCE OWNED BY; Schema: openscrutin; Owner: -
--

ALTER SEQUENCE affectation_seq OWNED BY affectation.affectation;


--
-- Name: agent; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE agent (
    agent character varying(10) NOT NULL,
    nom character varying(40) NOT NULL,
    prenom character varying(40) NOT NULL,
    adresse character varying(80) NOT NULL,
    cp character varying(5) NOT NULL,
    ville character varying(40) NOT NULL,
    telephone character varying(14),
    service character varying(10),
    telephone_pro character varying(14),
    grade character varying(10)
);


--
-- Name: bureau; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE bureau (
    bureau character varying(4) NOT NULL,
    libelle character varying(30) NOT NULL,
    canton character varying(8) NOT NULL,
    adresse1 character varying(60),
    adresse2 character varying(60),
    cp character varying(5),
    ville character varying(40)
);


--
-- Name: candidat; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE candidat (
    candidat integer NOT NULL,
    nom character varying(50) NOT NULL,
    scrutin character varying(15) NOT NULL
);


--
-- Name: candidat_seq; Type: SEQUENCE; Schema: openscrutin; Owner: -
--

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


--
-- Name: candidat_seq; Type: SEQUENCE OWNED BY; Schema: openscrutin; Owner: -
--

ALTER SEQUENCE candidat_seq OWNED BY candidat.candidat;


--
-- Name: candidature; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE candidature (
    candidature integer NOT NULL,
    agent character varying(10) NOT NULL,
    scrutin character varying(15) NOT NULL,
    periode character varying(20) NOT NULL,
    poste character varying(20) NOT NULL,
    bureau character varying(4) NOT NULL,
    recuperation boolean,
    note text,
    decision boolean,
    debut time without time zone,
    fin time without time zone
);


--
-- Name: candidature_seq; Type: SEQUENCE; Schema: openscrutin; Owner: -
--

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


--
-- Name: candidature_seq; Type: SEQUENCE OWNED BY; Schema: openscrutin; Owner: -
--

ALTER SEQUENCE candidature_seq OWNED BY candidature.candidature;


--
-- Name: canton; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE canton (
    canton character varying(8) NOT NULL,
    libelle character varying(40) NOT NULL
);


--
-- Name: composition_bureau; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE composition_bureau (
    scrutin character varying(15),
    bureau character varying(4),
    president character varying(80),
    president_suppleant character varying(80),
    secretaire character varying(80)
);


--
-- Name: composition_bureau_agent; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE composition_bureau_agent (
    scrutin character varying(15),
    bureau character varying(4),
    secretaire_matin character varying(80),
    secretaire_apm character varying(80),
    planton_matin character varying(80),
    planton_apm character varying(80)
);


--
-- Name: elu; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE elu (
    elu integer NOT NULL,
    nom character varying(40) NOT NULL,
    prenom character varying(40) NOT NULL,
    nomjf character varying(40),
    date_naissance date,
    lieu_naissance character varying(40),
    adresse character varying(80) NOT NULL,
    cp character varying(5) NOT NULL,
    ville character varying(40) NOT NULL
);


--
-- Name: elu_seq; Type: SEQUENCE; Schema: openscrutin; Owner: -
--

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


--
-- Name: elu_seq; Type: SEQUENCE OWNED BY; Schema: openscrutin; Owner: -
--

ALTER SEQUENCE elu_seq OWNED BY elu.elu;


--
-- Name: grade; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE grade (
    grade character varying(10) NOT NULL,
    libelle character varying(40) NOT NULL
);


--
-- Name: periode; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE periode (
    periode character varying(20) NOT NULL,
    libelle character varying(40) NOT NULL,
    debut time without time zone NOT NULL,
    fin time without time zone NOT NULL
);


--
-- Name: poste; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE poste (
    poste character varying(20) NOT NULL,
    libelle character varying(20) NOT NULL,
    nature character varying(15) NOT NULL,
    ordre integer NOT NULL
);


--
-- Name: scrutin; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE scrutin (
    scrutin character varying(15) NOT NULL,
    libelle character varying(40) NOT NULL,
    tour character(1) NOT NULL,
    canton character varying(8) NOT NULL,
    date_scrutin date NOT NULL,
    solde boolean,
    convocation_agent character varying(100),
    convocation_president character varying(100)
);


--
-- Name: service; Type: TABLE; Schema: openscrutin; Owner: -; Tablespace: 
--

CREATE TABLE service (
    service character varying(10) NOT NULL,
    libelle character varying(40) NOT NULL
);


--
-- Name: affectation_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_pkey PRIMARY KEY (affectation);


--
-- Name: agent_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY agent
    ADD CONSTRAINT agent_pkey PRIMARY KEY (agent);


--
-- Name: bureau_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY bureau
    ADD CONSTRAINT bureau_pkey PRIMARY KEY (bureau);


--
-- Name: candidat_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY candidat
    ADD CONSTRAINT candidat_pkey PRIMARY KEY (candidat);


--
-- Name: candidature_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY candidature
    ADD CONSTRAINT candidature_pkey PRIMARY KEY (candidature);


--
-- Name: canton_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY canton
    ADD CONSTRAINT canton_pkey PRIMARY KEY (canton);


--
-- Name: elu_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY elu
    ADD CONSTRAINT elu_pkey PRIMARY KEY (elu);


--
-- Name: grade_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY grade
    ADD CONSTRAINT grade_pkey PRIMARY KEY (grade);


--
-- Name: periode_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY periode
    ADD CONSTRAINT periode_pkey PRIMARY KEY (periode);


--
-- Name: poste_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY poste
    ADD CONSTRAINT poste_pkey PRIMARY KEY (poste);


--
-- Name: scrutin_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY scrutin
    ADD CONSTRAINT scrutin_pkey PRIMARY KEY (scrutin);


--
-- Name: service_pkey; Type: CONSTRAINT; Schema: openscrutin; Owner: -; Tablespace: 
--

ALTER TABLE ONLY service
    ADD CONSTRAINT service_pkey PRIMARY KEY (service);


--
-- Name: affectation_bureau_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_bureau_fkey FOREIGN KEY (bureau) REFERENCES bureau(bureau);


--
-- Name: affectation_candidat_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_candidat_fkey FOREIGN KEY (candidat) REFERENCES candidat(candidat);


--
-- Name: affectation_elu_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_elu_fkey FOREIGN KEY (elu) REFERENCES elu(elu);


--
-- Name: affectation_periode_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_periode_fkey FOREIGN KEY (periode) REFERENCES periode(periode);


--
-- Name: affectation_poste_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_poste_fkey FOREIGN KEY (poste) REFERENCES poste(poste);


--
-- Name: affectation_scrutin_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY affectation
    ADD CONSTRAINT affectation_scrutin_fkey FOREIGN KEY (scrutin) REFERENCES scrutin(scrutin);


--
-- Name: agent_grade_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY agent
    ADD CONSTRAINT agent_grade_fkey FOREIGN KEY (grade) REFERENCES grade(grade);


--
-- Name: agent_service_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY agent
    ADD CONSTRAINT agent_service_fkey FOREIGN KEY (service) REFERENCES service(service);


--
-- Name: bureau_canton_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY bureau
    ADD CONSTRAINT bureau_canton_fkey FOREIGN KEY (canton) REFERENCES canton(canton);


--
-- Name: candidat_scrutin_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY candidat
    ADD CONSTRAINT candidat_scrutin_fkey FOREIGN KEY (scrutin) REFERENCES scrutin(scrutin);


--
-- Name: candidature_agent_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY candidature
    ADD CONSTRAINT candidature_agent_fkey FOREIGN KEY (agent) REFERENCES agent(agent);


--
-- Name: candidature_bureau_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY candidature
    ADD CONSTRAINT candidature_bureau_fkey FOREIGN KEY (bureau) REFERENCES bureau(bureau);


--
-- Name: candidature_periode_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY candidature
    ADD CONSTRAINT candidature_periode_fkey FOREIGN KEY (periode) REFERENCES periode(periode);


--
-- Name: candidature_poste_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY candidature
    ADD CONSTRAINT candidature_poste_fkey FOREIGN KEY (poste) REFERENCES poste(poste);


--
-- Name: candidature_scrutin_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY candidature
    ADD CONSTRAINT candidature_scrutin_fkey FOREIGN KEY (scrutin) REFERENCES scrutin(scrutin);


--
-- Name: scrutin_canton_fkey; Type: FK CONSTRAINT; Schema: openscrutin; Owner: -
--

ALTER TABLE ONLY scrutin
    ADD CONSTRAINT scrutin_canton_fkey FOREIGN KEY (canton) REFERENCES canton(canton);


--
-- PostgreSQL database dump complete
--

