--
-- PostgreSQL database dump
--

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

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: attachments; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE attachments (
    id text NOT NULL,
    elt_id text NOT NULL,
    created_on timestamp with time zone DEFAULT now() NOT NULL,
    content_type text,
    file text
);


--
-- Name: attachments_id_seq; Type: SEQUENCE; Schema: public; Owner: manu
--

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


--
-- Name: choices; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE choices (
    id serial NOT NULL,
    elt_id character varying(255) NOT NULL,
    person_id character varying(255),
    created_on timestamp without time zone NOT NULL,
    value integer DEFAULT 1 NOT NULL
);


--
-- Name: delegations; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE delegations (
    id serial NOT NULL,
    elt_id text NOT NULL,
    person_id text NOT NULL,
    created_on timestamp with time zone DEFAULT now() NOT NULL,
    "temporary" boolean DEFAULT false NOT NULL,
    delegate_to text NOT NULL
);


--
-- Name: elts; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE elts (
    id text NOT NULL,
    parent_id text,
    "position" double precision,
    created_on timestamp with time zone DEFAULT now() NOT NULL,
    person_id text,
    subject text,
    body text,
    lft integer,
    rgt integer,
    elts_count integer DEFAULT 0
);


--
-- Name: elts_id_seq; Type: SEQUENCE; Schema: public; Owner: manu
--

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


--
-- Name: engine_schema_info; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE engine_schema_info (
    engine_name character varying(255),
    version integer
);


--
-- Name: mails; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE mails (
    id text NOT NULL,
    elt_id text NOT NULL,
    message text,
    mail_parents text,
    file text
);


--
-- Name: mails_id_seq; Type: SEQUENCE; Schema: public; Owner: manu
--

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


--
-- Name: people; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE people (
    id text NOT NULL,
    created_on timestamp with time zone DEFAULT now() NOT NULL,
    name text,
    email text,
    image text
);


--
-- Name: people_id_seq; Type: SEQUENCE; Schema: public; Owner: manu
--

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


--
-- Name: schema_info; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE schema_info (
    version integer
);


--
-- Name: subscribers; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE subscribers (
    elt_id text NOT NULL,
    person_id text NOT NULL
);


--
-- Name: subscribers_id_seq; Type: SEQUENCE; Schema: public; Owner: manu
--

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


--
-- Name: users; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE users (
    id serial NOT NULL,
    "login" text NOT NULL,
    salted_password character varying(40) NOT NULL,
    email character varying(60),
    firstname character varying(40),
    lastname character varying(40),
    salt character(40) NOT NULL,
    verified integer DEFAULT 0,
    "role" character varying(40),
    security_token character(40),
    token_expiry timestamp without time zone,
    deleted integer DEFAULT 0,
    delete_after timestamp without time zone
);


--
-- Name: usersold; Type: TABLE; Schema: public; Owner: manu; Tablespace: 
--

CREATE TABLE usersold (
    id serial NOT NULL,
    person_id text NOT NULL,
    salted_password text,
    salt text,
    verified integer DEFAULT 0,
    new_email text,
    security_token text
);


--
-- Name: attachments_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY attachments
    ADD CONSTRAINT attachments_pkey PRIMARY KEY (id);


--
-- Name: choices_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY choices
    ADD CONSTRAINT choices_pkey PRIMARY KEY (id);


--
-- Name: delegations_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY delegations
    ADD CONSTRAINT delegations_pkey PRIMARY KEY (id);


--
-- Name: elt_person_key; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY choices
    ADD CONSTRAINT elt_person_key UNIQUE (elt_id, person_id);


--
-- Name: elts_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY elts
    ADD CONSTRAINT elts_pkey PRIMARY KEY (id);


--
-- Name: mails_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY mails
    ADD CONSTRAINT mails_pkey PRIMARY KEY (id);


--
-- Name: people_name_key; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY people
    ADD CONSTRAINT people_name_key UNIQUE (name);


--
-- Name: people_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY people
    ADD CONSTRAINT people_pkey PRIMARY KEY (id);


--
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: usersold_person_id_key; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY usersold
    ADD CONSTRAINT usersold_person_id_key UNIQUE (person_id);


--
-- Name: usersold_pkey; Type: CONSTRAINT; Schema: public; Owner: manu; Tablespace: 
--

ALTER TABLE ONLY usersold
    ADD CONSTRAINT usersold_pkey PRIMARY KEY (id);


--
-- Name: elts_parent; Type: INDEX; Schema: public; Owner: manu; Tablespace: 
--

CREATE INDEX elts_parent ON elts USING btree (parent_id);


--
-- Name: elts_parent_key; Type: INDEX; Schema: public; Owner: manu; Tablespace: 
--

CREATE INDEX elts_parent_key ON elts USING btree (parent_id);


--
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY usersold
    ADD CONSTRAINT "$1" FOREIGN KEY (person_id) REFERENCES people(id);


--
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY subscribers
    ADD CONSTRAINT "$1" FOREIGN KEY (elt_id) REFERENCES elts(id);


--
-- Name: $1; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY delegations
    ADD CONSTRAINT "$1" FOREIGN KEY (elt_id) REFERENCES elts(id);


--
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY subscribers
    ADD CONSTRAINT "$2" FOREIGN KEY (person_id) REFERENCES people(id);


--
-- Name: $2; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY delegations
    ADD CONSTRAINT "$2" FOREIGN KEY (person_id) REFERENCES people(id);


--
-- Name: $3; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY delegations
    ADD CONSTRAINT "$3" FOREIGN KEY (delegate_to) REFERENCES people(id);


--
-- Name: fk_elt_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY choices
    ADD CONSTRAINT fk_elt_id FOREIGN KEY (elt_id) REFERENCES elts(id) ON UPDATE CASCADE;


--
-- Name: fk_elt_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY attachments
    ADD CONSTRAINT fk_elt_id FOREIGN KEY (elt_id) REFERENCES elts(id) ON UPDATE CASCADE;


--
-- Name: fk_elt_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY elts
    ADD CONSTRAINT fk_elt_id FOREIGN KEY (parent_id) REFERENCES elts(id) ON UPDATE CASCADE;


--
-- Name: fk_elt_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY mails
    ADD CONSTRAINT fk_elt_id FOREIGN KEY (elt_id) REFERENCES elts(id) ON UPDATE CASCADE;


--
-- Name: fk_person_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY users
    ADD CONSTRAINT fk_person_id FOREIGN KEY ("login") REFERENCES people(name) ON UPDATE CASCADE;


--
-- Name: fk_person_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY elts
    ADD CONSTRAINT fk_person_id FOREIGN KEY (person_id) REFERENCES people(id) ON UPDATE CASCADE;


--
-- Name: fk_person_id; Type: FK CONSTRAINT; Schema: public; Owner: manu
--

ALTER TABLE ONLY choices
    ADD CONSTRAINT fk_person_id FOREIGN KEY (person_id) REFERENCES people(id) ON UPDATE CASCADE;


--
-- PostgreSQL database dump complete
--

INSERT INTO schema_info (version) VALUES (4)