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

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

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


ALTER TABLE public.organization OWNER TO dkg;

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

CREATE VIEW audience_by_organization AS
    SELECT affiliation.organization_id, count(*) AS count FROM ((audience LEFT JOIN affiliation ON ((audience.person_id = affiliation.person_id))) LEFT JOIN organization ON ((affiliation.organization_id = organization.id))) GROUP BY affiliation.organization_id;


ALTER TABLE public.audience_by_organization OWNER TO dkg;

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

CREATE TABLE broadcast (
    id serial NOT NULL,
    subj character varying NOT NULL,
    body text NOT NULL,
    selector character varying NOT NULL,
    sender_id integer NOT NULL
);


ALTER TABLE public.broadcast 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,
    actually_sent boolean DEFAULT true NOT NULL,
    message_type character varying DEFAULT 'newacct'::character varying NOT NULL,
    broadcast_id integer
);


ALTER TABLE public.mail_log 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,
    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,
    private_notes text,
    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: broadcast_pkey; Type: CONSTRAINT; Schema: public; Owner: dkg; Tablespace: 
--

ALTER TABLE ONLY broadcast
    ADD CONSTRAINT broadcast_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: audience_delete; Type: RULE; Schema: public; Owner: dkg
--

CREATE RULE audience_delete AS ON DELETE TO audience DO INSTEAD DELETE FROM attendance WHERE (((attendance.person_id = old.person_id) AND (attendance.workshop_id = old.workshop_id)) AND ((attendance.flavor)::text = 'audience'::text));


--
-- Name: audience_insert; Type: RULE; Schema: public; Owner: dkg
--

CREATE RULE audience_insert AS ON INSERT TO audience DO INSTEAD INSERT INTO attendance (person_id, workshop_id, flavor) VALUES (new.person_id, new.workshop_id, 'audience'::character varying);


--
-- Name: presenter_delete; Type: RULE; Schema: public; Owner: dkg
--

CREATE RULE presenter_delete AS ON DELETE TO presenter DO INSTEAD DELETE FROM attendance WHERE (((attendance.person_id = old.person_id) AND (attendance.workshop_id = old.workshop_id)) AND ((attendance.flavor)::text = 'presenter'::text));


--
-- Name: presenter_insert; Type: RULE; Schema: public; Owner: dkg
--

CREATE RULE presenter_insert AS ON INSERT TO presenter DO INSTEAD INSERT INTO attendance (person_id, workshop_id, flavor) VALUES (new.person_id, new.workshop_id, 'presenter'::character varying);


--
-- 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: broadcast_sender_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: dkg
--

ALTER TABLE ONLY broadcast
    ADD CONSTRAINT broadcast_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES person(id);


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

ALTER TABLE ONLY mail_log
    ADD CONSTRAINT mail_log_broadcast_id_fkey FOREIGN KEY (broadcast_id) REFERENCES broadcast(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 INSERT,SELECT,DELETE ON TABLE audience 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: broadcast; Type: ACL; Schema: public; Owner: dkg
--

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


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

REVOKE ALL ON TABLE broadcast_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE broadcast_id_seq FROM dkg;
GRANT ALL ON TABLE broadcast_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE broadcast_id_seq 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 INSERT,SELECT,UPDATE ON TABLE category TO "www-data";


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

REVOKE ALL ON TABLE category_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE category_id_seq FROM dkg;
GRANT ALL ON TABLE category_id_seq TO dkg;
GRANT SELECT,UPDATE ON TABLE category_id_seq 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: mail_log; Type: ACL; Schema: public; Owner: dkg
--

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


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

REVOKE ALL ON TABLE mail_log_id_seq FROM PUBLIC;
REVOKE ALL ON TABLE mail_log_id_seq FROM dkg;
GRANT ALL ON TABLE mail_log_id_seq TO dkg;
GRANT INSERT,UPDATE ON TABLE mail_log_id_seq 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 INSERT,SELECT,DELETE 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
--



CREATE OR REPLACE VIEW workshop_columns AS SELECT workshop_id,
 SUM(CASE WHEN category_id IN(9) THEN 1 ELSE 0 END) > 0 AS math,
 SUM(CASE WHEN category_id IN(15) THEN 1 ELSE 0 END) > 0 AS art,
 SUM(CASE WHEN category_id IN(11) THEN 1 ELSE 0 END) > 0 AS science,
 SUM(CASE WHEN category_id IN(12) THEN 1 ELSE 0 END) > 0 AS history,
 SUM(CASE WHEN category_id IN(25) THEN 1 ELSE 0 END) > 0 AS english,
 SUM(CASE WHEN category_id IN(30) THEN 1 ELSE 0 END) > 0 AS unione
  FROM workshop_category GROUP BY workshop_id;

CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name, last_name, email, workshop_columns.*, organization.shortname AS school, organization.id = 31 as gates, organization.id = 9 as consortium from person LEFT JOIN attendance ON (person.id = person_id) LEFT JOIN workshop_columns USING (workshop_id) LEFT JOIN affiliation ON (person.id = affiliation.person_id) LEFT JOIN organization ON (organization_id = organization.id) ORDER BY last_name,first_name;


CREATE VIEW pv AS select MAX(first) as first, MAX(mid) as mid, MAX(last) as last, email, MAX(school) as school, 
 SUM(CASE WHEN lower(tag) LIKE '%math%' THEN 1 ELSE 0 END) > 0 AS math,
 SUM(CASE WHEN lower(tag) LIKE '%history%' THEN 1 ELSE 0 END) > 0 AS history,
 SUM(CASE WHEN lower(tag) LIKE '%science%' THEN 1 ELSE 0 END) > 0 AS science,
 SUM(CASE WHEN lower(tag) LIKE '%art%' THEN 1 ELSE 0 END) > 0 AS art,
 SUM(CASE WHEN lower(tag) LIKE '%english%' THEN 1 ELSE 0 END) > 0 AS english,
 SUM(CASE WHEN lower(tag) LIKE '%gates%' THEN 1 ELSE 0 END) > 0 AS gates,
 SUM(CASE WHEN lower(tag) LIKE '%empire%' THEN 1 ELSE 0 END) > 0 AS empire,
 SUM(CASE WHEN lower(tag) LIKE '%admin%' THEN 1 ELSE 0 END) > 0 AS admin,
 SUM(CASE WHEN lower(tag) LIKE '%amerhistgrant%' THEN 1 ELSE 0 END) > 0 AS amerhistgrant,
 SUM(CASE WHEN lower(tag) LIKE '%network%' THEN 1 ELSE 0 END) > 0 AS network
 from pp LEFT JOIN pt ON (pp.id = pp_id)  GROUP BY email;

CREATE TABLE event (
 id serial NOT NULL PRIMARY KEY,
 title text NOT NULL,
 start_time timestamp with time zone NOT NULL,
 end_time timestamp with time zone NOT NULL
);

ALTER TABLE workshop ADD COLUMN event_id int;
ALTER TABLE workshop ADD CONSTRAINT workshop_event_fkey FOREIGN KEY (event_id) REFERENCES event(id);

-- fix existing columns here
ALTER TABLE workshop ALTER COLUMN event_id SET NOT NULL;

ALTER TABLE event ADD COLUMN description text;
ALTER TABLE event ADD COLUMN motto text;

CREATE TABLE role (id serial NOT NULL PRIMARY KEY, title text NOT NULL UNIQUE, description text, require_org bool NOT NULL DEFAULT true);

CREATE TABLE person_role (
id serial NOT NULL PRIMARY KEY, 
event_id int NOT NULL REFERENCES event(id),
person_id int NOT NULL REFERENCES person(id),
organization_id int REFERENCES organization(id),
role_id int NOT NULL REFERENCES role(id)
);


CREATE TABLE attendance_requirements (
id serial NOT NULL PRIMARY KEY,
category_id int NOT NULL REFERENCES category(id), 
event_id int NOT NULL REFERENCES event(id),
minimum int NOT NULL,
maximum int NOT NULL,
    CONSTRAINT attendence_requirements_reasonable_limits CHECK (minimum <= maximum)
);

CREATE TABLE application (
id serial NOT NULL PRIMARY KEY,
event_id int NOT NULL REFERENCES event(id),
person_id int NOT NULL REFERENCES person(id),
category_id int REFERENCES category(id)
);

-- record who created a person (if NULL, the account is most likely self-created):
ALTER TABLE person ADD COLUMN created_by int REFERENCES person(id);

-- fix up permissions:

GRANT SELECT,INSERT,UPDATE ON event TO "www-data";
GRANT SELECT,UPDATE ON event_id_seq TO "www-data";
GRANT SELECT,INSERT,UPDATE ON role TO "www-data";
GRANT SELECT,UPDATE ON role_id_seq TO "www-data";
GRANT INSERT,UPDATE,DELETE ON person_role TO "www-data";
GRANT SELECT,UPDATE ON person_role_id_seq TO "www-data";
GRANT SELECT,INSERT,UPDATE,DELETE ON application TO "www-data";
GRANT SELECT,UPDATE ON application_id_seq TO "www-data";
GRANT SELECT ON attendance_requirements TO "www-data";
GRANT SELECT ON broadcast TO "www-data";
GRANT SELECT ON mail_log TO "www-data";

-- make a reasonable limitation on the attendance requirements:

ALTER TABLE attendance_requirements ADD CONSTRAINT attendence_requirements_category_event_unique UNIQUE (category_id,event_id);

-- remember whether each broadcast was sent out as a personalized
-- mail, (false means it was sent out from the site-wide e-mail address):
ALTER TABLE broadcast ADD COLUMN personal boolean;
UPDATE broadcast SET personal = true; 
ALTER TABLE broadcast ALTER COLUMN personal SET NOT NULL;

-- we're allowing editing of broadcast templates now:
GRANT UPDATE ON broadcast TO "www-data";

-- allow the room to be used in multiple events:
ALTER TABLE workshop DROP CONSTRAINT workshop_room_id_key;
ALTER TABLE workshop ADD CONSTRAINT workshop_room_id_key UNIQUE (room_id,event_id);

-- start tracking attendance:
ALTER TABLE attendance ADD COLUMN marked_present timestamp with time zone;
ALTER TABLE attendance ADD COLUMN marked_present_by integer REFERENCES person(id);

-- require each workshop to be linked to an event:
ALTER TABLE workshop ALTER COLUMN event_id SET NOT NULL;

-- indicate if a person was affiliated with a given event associated
-- with some category (at the moment, we just test if they attended or
-- presented a workshop with that category, or if they were "applied"
-- to that category of the event.

CREATE OR REPLACE VIEW person_event_category AS SELECT person_id,
event_id, category_id FROM attendance JOIN workshop_category USING
(workshop_id) JOIN workshop ON (workshop.id = workshop_id) GROUP BY
person_id, category_id, event_id UNION SELECT person_id, event_id,
category_id FROM application;


CREATE OR REPLACE VIEW person_categories AS SELECT person_id,
 COUNT(category_id = 9 OR NULL) > 0 AS math,
 COUNT(category_id = 15 OR NULL) > 0 AS art,
 COUNT(category_id = 11 OR NULL) > 0 AS science,
 COUNT(category_id = 12 OR NULL) > 0 AS history,
 COUNT(category_id = 25 OR NULL) > 0 AS english
  FROM person_event_category GROUP BY person_id;


-- we're redoing the pommo import with our new conception of what data
-- is needed:

DROP VIEW pommo_import;

CREATE OR REPLACE VIEW pommo_import AS SELECT first_name, middle_name,
last_name, email, person_categories.*, organization.title AS
school, event_id FROM

 person JOIN person_categories ON (person.id = person_id) JOIN (SELECT
DISTINCT person_id, event_id FROM person_event_category) AS
person_event USING (person_id) LEFT JOIN affiliation ON (person.id =
affiliation.person_id) LEFT JOIN organization ON (organization_id =
organization.id) ORDER BY last_name,first_name;

-- -- here's the actual export:
-- jpdd=> \pset fieldsep ,
-- Field separator is ",".
-- jpdd=> \a
-- Output format is unaligned.
-- jpdd=> \o pommo-export-2007-10-09
-- jpdd=> select * from pommo_import WHERE event_id = 2;
-- jpdd=>

-- and then i needed to swap out ,"on", and ,, for ,t, and ,f,

-- e-mail addresses will be treated case-insensitively: this is
-- against the RFC, but it is a minor inconvenience for a tiny subset
-- of users (maybe no users at all?), and it helps a substantial
-- number of users who don't remember what case they entered their
-- e-mail address several months ago.

CREATE UNIQUE INDEX person_email_ci_unique ON person (lower(email));
ALTER TABLE person DROP CONSTRAINT person_email_unique;