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

--
-- 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: 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: organization; Type: TABLE; Schema: public; Owner: dkg; Tablespace: 
--

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


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,
    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
);


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: 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: 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,
    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))
);


ALTER TABLE public.workshop 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: log_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY log
    ADD CONSTRAINT log_pkey PRIMARY KEY (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: 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: workshop_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY workshop
    ADD CONSTRAINT workshop_pkey PRIMARY KEY (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: 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 SELECT ON TABLE affiliation 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 SELECT ON TABLE attendance 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 SELECT ON TABLE organization 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: 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: 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_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
--