source: trunk/jpdd/sql/db.sql @ 89

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
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: affiliation; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE affiliation (
    id serial NOT NULL,
    person_id integer NOT NULL,
    organization_id integer NOT NULL
);


ALTER TABLE public.affiliation OWNER TO dkg;

--
-- Name: attendance; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE attendance (
    id serial NOT NULL,
    workshop_id integer NOT NULL,
    person_id integer NOT NULL,
    flavor character varying NOT NULL,
    CONSTRAINT attendance_flavor_check CHECK ((((flavor)::text = 'presenter'::text) OR ((flavor)::text = 'audience'::text)))
);


ALTER TABLE public.attendance OWNER TO dkg;

--
-- Name: audience; Type: VIEW; Schema: public; Owner: dkg
--

CREATE VIEW audience AS
    SELECT attendance.workshop_id, attendance.person_id FROM attendance WHERE ((attendance.flavor)::text = 'audience'::text);


ALTER TABLE public.audience OWNER TO dkg;

--
-- Name: category; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE category (
    id serial NOT NULL,
    title character varying NOT NULL,
    description text
);


ALTER TABLE public.category OWNER TO dkg;

--
-- Name: log; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE log (
    id serial NOT NULL,
    date timestamp with time zone DEFAULT now() NOT NULL,
    data text,
    backtrace text,
    servervars text
);


ALTER TABLE public.log OWNER TO dkg;

--
-- Name: mail_log; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE mail_log (
    id serial NOT NULL,
    mail_from character varying NOT NULL,
    mail_to character varying NOT NULL,
    subject character varying NOT NULL,
    body text NOT NULL,
    extra_headers character varying,
    whattime timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.mail_log OWNER TO dkg;

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

CREATE TABLE organization (
    id serial NOT NULL,
    title character varying NOT NULL,
    description text
);


ALTER TABLE public.organization OWNER TO dkg;

--
-- Name: person; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE person (
    id serial NOT NULL,
    first_name character varying,
    last_name character varying NOT NULL,
    email character varying,
    middle_name character varying,
    pass character(40),
    session_value character(30),
    session_touched timestamp with time zone,
    session_created timestamp with time zone,
    resetpass character varying(30),
    resetpass_created timestamp with time zone,
    CONSTRAINT person_email_check CHECK (((email)::text <> ''::text)),
    CONSTRAINT person_first_name_check CHECK (((first_name)::text <> ''::text)),
    CONSTRAINT person_last_name_check CHECK (((last_name)::text <> ''::text)),
    CONSTRAINT person_middle_name_check CHECK (((middle_name)::text <> ''::text))
);


ALTER TABLE public.person OWNER TO dkg;

--
-- Name: person_privilege; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE person_privilege (
    person_id integer NOT NULL,
    privilege_id integer NOT NULL
);


ALTER TABLE public.person_privilege OWNER TO dkg;

--
-- Name: post_log; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE post_log (
    id serial NOT NULL,
    person_id integer,
    whattime timestamp with time zone DEFAULT now() NOT NULL,
    from_ip inet NOT NULL,
    request character varying NOT NULL,
    "action" character varying,
    "type" character varying
);


ALTER TABLE public.post_log OWNER TO dkg;

--
-- Name: presenter; Type: VIEW; Schema: public; Owner: dkg
--

CREATE VIEW presenter AS
    SELECT attendance.workshop_id, attendance.person_id FROM attendance WHERE ((attendance.flavor)::text = 'presenter'::text);


ALTER TABLE public.presenter OWNER TO dkg;

--
-- Name: privilege; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE privilege (
    id serial NOT NULL,
    title character varying NOT NULL,
    description text
);


ALTER TABLE public.privilege OWNER TO dkg;

--
-- Name: room; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE room (
    id serial NOT NULL,
    title character varying(20) NOT NULL,
    description text,
    capacity integer,
    CONSTRAINT room_title_check CHECK (((title)::text <> ''::text))
);


ALTER TABLE public.room OWNER TO dkg;

--
-- Name: workshop; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE workshop (
    id serial NOT NULL,
    title character varying NOT NULL,
    short_title character varying(35),
    description text,
    min_attendees integer DEFAULT 5 NOT NULL,
    max_attendees integer DEFAULT 25 NOT NULL,
    room_id integer,
    CONSTRAINT workshop_check CHECK (((char_length((title)::text) <= 35) OR (short_title IS NOT NULL))),
    CONSTRAINT workshop_check1 CHECK ((max_attendees >= min_attendees)),
    CONSTRAINT workshop_min_attendees_check CHECK ((min_attendees >= 0)),
    CONSTRAINT workshop_title_check CHECK (((title)::text <> ''::text))
);


ALTER TABLE public.workshop OWNER TO dkg;

--
-- Name: workshop_category; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

CREATE TABLE workshop_category (
    workshop_id integer NOT NULL,
    category_id integer NOT NULL
);


ALTER TABLE public.workshop_category OWNER TO dkg;

--
-- Name: affiliation_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY affiliation
    ADD CONSTRAINT affiliation_pkey PRIMARY KEY (id);


--
-- Name: attendance_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY attendance
    ADD CONSTRAINT attendance_pkey PRIMARY KEY (id);


--
-- Name: category_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY category
    ADD CONSTRAINT category_pkey PRIMARY KEY (id);


--
-- Name: category_title_key; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY category
    ADD CONSTRAINT category_title_key UNIQUE (title);


--
-- Name: log_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY log
    ADD CONSTRAINT log_pkey PRIMARY KEY (id);


--
-- Name: mail_log_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY mail_log
    ADD CONSTRAINT mail_log_pkey PRIMARY KEY (id);


--
-- Name: no_double_attendance; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY attendance
    ADD CONSTRAINT no_double_attendance UNIQUE (workshop_id, person_id);


--
-- Name: no_double_category; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY workshop_category
    ADD CONSTRAINT no_double_category UNIQUE (workshop_id, category_id);


--
-- Name: no_double_privileges; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY person_privilege
    ADD CONSTRAINT no_double_privileges UNIQUE (person_id, privilege_id);


--
-- Name: organization_name_key; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY organization
    ADD CONSTRAINT organization_name_key UNIQUE (title);


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

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


--
-- Name: person_email_unique; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY person
    ADD CONSTRAINT person_email_unique UNIQUE (email);


--
-- Name: person_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY person
    ADD CONSTRAINT person_pkey PRIMARY KEY (id);


--
-- Name: post_log_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY post_log
    ADD CONSTRAINT post_log_pkey PRIMARY KEY (id);


--
-- Name: privilege_name_key; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY privilege
    ADD CONSTRAINT privilege_name_key UNIQUE (title);


--
-- Name: privilege_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY privilege
    ADD CONSTRAINT privilege_pkey PRIMARY KEY (id);


--
-- Name: room_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY room
    ADD CONSTRAINT room_pkey PRIMARY KEY (id);


--
-- Name: unique_room_title; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY room
    ADD CONSTRAINT unique_room_title UNIQUE (title);


--
-- Name: workshop_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY workshop
    ADD CONSTRAINT workshop_pkey PRIMARY KEY (id);


--
-- Name: workshop_room_id_key; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY workshop
    ADD CONSTRAINT workshop_room_id_key UNIQUE (room_id);


--
-- Name: workshop_title_key; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY workshop
    ADD CONSTRAINT workshop_title_key UNIQUE (title);


--
-- Name: affiliation_organization_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY affiliation
    ADD CONSTRAINT affiliation_organization_id_fkey FOREIGN KEY (organization_id) REFERENCES organization(id);


--
-- Name: affiliation_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY affiliation
    ADD CONSTRAINT affiliation_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(id);


--
-- Name: attendance_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY attendance
    ADD CONSTRAINT attendance_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(id);


--
-- Name: attendance_workshop_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY attendance
    ADD CONSTRAINT attendance_workshop_id_fkey FOREIGN KEY (workshop_id) REFERENCES workshop(id);


--
-- Name: person_privilege_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY person_privilege
    ADD CONSTRAINT person_privilege_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(id);


--
-- Name: person_privilege_privilege_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY person_privilege
    ADD CONSTRAINT person_privilege_privilege_id_fkey FOREIGN KEY (privilege_id) REFERENCES privilege(id);


--
-- Name: post_log_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY post_log
    ADD CONSTRAINT post_log_person_id_fkey FOREIGN KEY (person_id) REFERENCES person(id);


--
-- Name: workshop_category_category_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY workshop_category
    ADD CONSTRAINT workshop_category_category_id_fkey FOREIGN KEY (category_id) REFERENCES category(id);


--
-- Name: workshop_category_workshop_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY workshop_category
    ADD CONSTRAINT workshop_category_workshop_id_fkey FOREIGN KEY (workshop_id) REFERENCES workshop(id);


--
-- Name: workshop_room_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY workshop
    ADD CONSTRAINT workshop_room_id_fkey FOREIGN KEY (room_id) REFERENCES room(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;


--
-- Name: affiliation; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE affiliation FROM PUBLIC;
REVOKE ALL ON TABLE affiliation FROM dkg;
GRANT ALL ON TABLE affiliation TO dkg;
GRANT INSERT,SELECT,DELETE ON TABLE affiliation TO "www-data";


--
-- Name: affiliation_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE affiliation_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE affiliation_id_seq FROM dkg;
GRANT ALL ON TABLE affiliation_id_seq TO dkg;
GRANT UPDATE ON TABLE affiliation_id_seq TO "www-data";


--
-- Name: attendance; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE attendance FROM PUBLIC;
REVOKE ALL ON TABLE attendance FROM dkg;
GRANT ALL ON TABLE attendance TO dkg;
GRANT INSERT,SELECT,DELETE ON TABLE attendance TO "www-data";


--
-- Name: attendance_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE attendance_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE attendance_id_seq FROM dkg;
GRANT ALL ON TABLE attendance_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE attendance_id_seq TO "www-data";


--
-- Name: audience; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE audience FROM PUBLIC;
REVOKE ALL ON TABLE audience FROM dkg;
GRANT ALL ON TABLE audience TO dkg;
GRANT SELECT ON TABLE audience TO "www-data";


--
-- Name: category; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE category FROM PUBLIC;
REVOKE ALL ON TABLE category FROM dkg;
GRANT ALL ON TABLE category TO dkg;
GRANT SELECT ON TABLE category TO "www-data";


--
-- Name: log; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE log FROM PUBLIC;
REVOKE ALL ON TABLE log FROM dkg;
GRANT ALL ON TABLE log TO dkg;
GRANT INSERT ON TABLE log TO "www-data";


--
-- Name: log_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE log_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE log_id_seq FROM dkg;
GRANT ALL ON TABLE log_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE log_id_seq TO "www-data";


--
-- Name: organization; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE organization FROM PUBLIC;
REVOKE ALL ON TABLE organization FROM dkg;
GRANT ALL ON TABLE organization TO dkg;
GRANT INSERT,SELECT,UPDATE ON TABLE organization TO "www-data";


--
-- Name: organization_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE organization_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE organization_id_seq FROM dkg;
GRANT ALL ON TABLE organization_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE organization_id_seq TO "www-data";


--
-- Name: person; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE person FROM PUBLIC;
REVOKE ALL ON TABLE person FROM dkg;
GRANT ALL ON TABLE person TO dkg;
GRANT INSERT,SELECT,UPDATE ON TABLE person TO "www-data";


--
-- Name: person_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE person_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE person_id_seq FROM dkg;
GRANT ALL ON TABLE person_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE person_id_seq TO "www-data";


--
-- Name: person_privilege; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE person_privilege FROM PUBLIC;
REVOKE ALL ON TABLE person_privilege FROM dkg;
GRANT ALL ON TABLE person_privilege TO dkg;
GRANT SELECT ON TABLE person_privilege TO "www-data";


--
-- Name: presenter; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE presenter FROM PUBLIC;
REVOKE ALL ON TABLE presenter FROM dkg;
GRANT ALL ON TABLE presenter TO dkg;
GRANT SELECT ON TABLE presenter TO "www-data";


--
-- Name: privilege; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE privilege FROM PUBLIC;
REVOKE ALL ON TABLE privilege FROM dkg;
GRANT ALL ON TABLE privilege TO dkg;
GRANT SELECT ON TABLE privilege TO "www-data";


--
-- Name: room; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE room FROM PUBLIC;
REVOKE ALL ON TABLE room FROM dkg;
GRANT ALL ON TABLE room TO dkg;
GRANT INSERT,SELECT,UPDATE,DELETE ON TABLE room TO "www-data";


--
-- Name: room_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE room_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE room_id_seq FROM dkg;
GRANT ALL ON TABLE room_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE room_id_seq TO "www-data";


--
-- Name: workshop; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE workshop FROM PUBLIC;
REVOKE ALL ON TABLE workshop FROM dkg;
GRANT ALL ON TABLE workshop TO dkg;
GRANT INSERT,SELECT,UPDATE ON TABLE workshop TO "www-data";


--
-- Name: workshop_category; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE workshop_category FROM PUBLIC;
REVOKE ALL ON TABLE workshop_category FROM dkg;
GRANT ALL ON TABLE workshop_category TO dkg;
GRANT INSERT,SELECT,DELETE ON TABLE workshop_category TO "www-data";


--
-- Name: workshop_id_seq; Type: ACL; Schema: public; Owner: dkg
--

REVOKE ALL ON TABLE workshop_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE workshop_id_seq FROM dkg;
GRANT ALL ON TABLE workshop_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE workshop_id_seq TO "www-data";


--
-- PostgreSQL database dump complete
--