--
-- PostgreSQL database cluster dump
--

\connect postgres

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET escape_string_warning = 'off';

--
-- Roles
--

DROP ROLE apache;
CREATE ROLE apache;
ALTER ROLE apache WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;
DROP ROLE postgres;
CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;
DROP ROLE shaun;
CREATE ROLE shaun;
ALTER ROLE shaun WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN;




--
-- Database creation
--

DROP DATABASE apache;
CREATE DATABASE apache WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'SQL_ASCII';
DROP DATABASE shaun;
CREATE DATABASE shaun WITH TEMPLATE = template0 OWNER = postgres ENCODING = 'SQL_ASCII';
REVOKE ALL ON DATABASE template1 FROM PUBLIC;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT CREATE,TEMPORARY ON DATABASE template1 TO postgres WITH GRANT OPTION;


\connect apache

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: icc_admin_areas; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_admin_areas (
    id integer DEFAULT nextval('public.icc_admin_areas_id_seq'::text) NOT NULL,
    name character varying(255) NOT NULL,
    label_template character varying(255),
    label_body_template character varying(255)
);


ALTER TABLE public.icc_admin_areas OWNER TO apache;

--
-- Name: icc_admin_areas_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_admin_areas_id_seq OWNER TO apache;

--
-- Name: icc_admin_areas_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_admin_areas_id_seq OWNED BY icc_admin_areas.id;


--
-- Name: icc_countries; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_countries (
    id integer DEFAULT nextval('public.icc_countries_id_seq'::text) NOT NULL,
    name character varying(255) NOT NULL,
    admin_by integer,
    iso character(2),
    iso3 character(3),
    numcode integer
);


ALTER TABLE public.icc_countries OWNER TO apache;

--
-- Name: icc_countries_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_countries_id_seq OWNER TO apache;

--
-- Name: icc_countries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_countries_id_seq OWNED BY icc_countries.id;


--
-- Name: icc_globals; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_globals (
    id integer DEFAULT nextval('public.icc_globals_id_seq'::text) NOT NULL,
    name character varying(255),
    value text
);


ALTER TABLE public.icc_globals OWNER TO apache;

--
-- Name: icc_globals_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_globals_id_seq OWNER TO apache;

--
-- Name: icc_globals_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_globals_id_seq OWNED BY icc_globals.id;


--
-- Name: icc_members; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_members (
    id integer DEFAULT nextval('public.icc_members_id_seq'::text) NOT NULL,
    firstname character varying,
    lastname character varying NOT NULL,
    address text,
    postcode character varying,
    city character varying,
    county character varying,
    state_id integer,
    country_id integer NOT NULL,
    unpaid integer DEFAULT 0 NOT NULL
);


ALTER TABLE public.icc_members OWNER TO apache;

--
-- Name: icc_members_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_members_id_seq OWNER TO apache;

--
-- Name: icc_members_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_members_id_seq OWNED BY icc_members.id;


--
-- Name: icc_states; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_states (
    id integer DEFAULT nextval('public.icc_states_id_seq'::text) NOT NULL,
    name character varying NOT NULL,
    abbrev character(2),
    country_id integer NOT NULL
);


ALTER TABLE public.icc_states OWNER TO apache;

--
-- Name: icc_members_test; Type: VIEW; Schema: public; Owner: apache
--

CREATE VIEW icc_members_test AS
    SELECT icc_members.id, icc_members.firstname, icc_members.lastname, icc_members.address, icc_members.city, icc_members.postcode, icc_members.county, icc_states.name AS state, icc_countries.name AS country FROM (icc_members LEFT JOIN icc_states ON ((icc_members.state_id = icc_states.id))) WHERE (icc_members.country_id = icc_countries.id) ORDER BY icc_members.id DESC;


ALTER TABLE public.icc_members_test OWNER TO apache;

--
-- Name: icc_members_with_admin; Type: VIEW; Schema: public; Owner: apache
--

CREATE VIEW icc_members_with_admin AS
    SELECT icc_members.id, icc_members.firstname, icc_members.lastname, icc_members.address, icc_members.city, icc_members.postcode, icc_members.county, icc_states.name AS state, icc_states.abbrev AS state_abbrev, icc_countries.admin_by, icc_admin_areas.label_template, icc_admin_areas.label_body_template, icc_countries.name AS country FROM (icc_members LEFT JOIN icc_states ON ((icc_members.state_id = icc_states.id))) WHERE ((icc_members.country_id = icc_countries.id) AND (icc_countries.admin_by = icc_admin_areas.id)) ORDER BY icc_members.lastname, icc_members.firstname;


ALTER TABLE public.icc_members_with_admin OWNER TO apache;

--
-- Name: icc_members_with_country; Type: VIEW; Schema: public; Owner: apache
--

CREATE VIEW icc_members_with_country AS
    SELECT icc_members.id, icc_members.unpaid, icc_members.firstname, icc_members.lastname, icc_members.address, icc_members.city, icc_members.postcode, icc_members.county, icc_states.name AS state, icc_countries.name AS country FROM (icc_members LEFT JOIN icc_states ON ((icc_members.state_id = icc_states.id))) WHERE (icc_members.country_id = icc_countries.id) ORDER BY icc_members.lastname, icc_members.firstname;


ALTER TABLE public.icc_members_with_country OWNER TO apache;

--
-- Name: icc_positions; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_positions (
    id integer DEFAULT nextval('public.icc_positions_id_seq'::text) NOT NULL,
    member_id integer,
    title character varying(255)
);


ALTER TABLE public.icc_positions OWNER TO apache;

--
-- Name: icc_positions_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_positions_id_seq OWNER TO apache;

--
-- Name: icc_positions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_positions_id_seq OWNED BY icc_positions.id;


--
-- Name: icc_session_vars; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_session_vars (
    id integer DEFAULT nextval('public.icc_session_vars_id_seq'::text) NOT NULL,
    name text NOT NULL,
    sid integer,
    value text
);


ALTER TABLE public.icc_session_vars OWNER TO apache;

--
-- Name: icc_session_vars_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_session_vars_id_seq OWNER TO apache;

--
-- Name: icc_session_vars_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_session_vars_id_seq OWNED BY icc_session_vars.id;


--
-- Name: icc_sessions; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_sessions (
    id integer DEFAULT nextval('public.icc_sessions_id_seq'::text) NOT NULL,
    sesstime integer,
    rand integer,
    ip text
);


ALTER TABLE public.icc_sessions OWNER TO apache;

--
-- Name: icc_sessions_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_sessions_id_seq OWNER TO apache;

--
-- Name: icc_sessions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_sessions_id_seq OWNED BY icc_sessions.id;


--
-- Name: icc_states_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_states_id_seq OWNER TO apache;

--
-- Name: icc_states_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_states_id_seq OWNED BY icc_states.id;


--
-- Name: icc_users; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE icc_users (
    id integer DEFAULT nextval('public.icc_users_id_seq'::text) NOT NULL,
    username character varying(20) NOT NULL,
    privs text,
    password character(32) NOT NULL
);


ALTER TABLE public.icc_users OWNER TO apache;

--
-- Name: icc_users_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.icc_users_id_seq OWNER TO apache;

--
-- Name: icc_users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE icc_users_id_seq OWNED BY icc_users.id;


--
-- Name: shaun_globals; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE shaun_globals (
    id integer DEFAULT nextval('public.shaun_globals_id_seq'::text) NOT NULL,
    name character varying(255),
    value text
);


ALTER TABLE public.shaun_globals OWNER TO apache;

--
-- Name: shaun_globals_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.shaun_globals_id_seq OWNER TO apache;

--
-- Name: shaun_globals_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE shaun_globals_id_seq OWNED BY shaun_globals.id;


--
-- Name: shaun_info; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE shaun_info (
    id integer DEFAULT nextval('public.shaun_info_id_seq'::text) NOT NULL,
    name character varying(255),
    data text
);


ALTER TABLE public.shaun_info OWNER TO apache;

--
-- Name: shaun_info_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.shaun_info_id_seq OWNER TO apache;

--
-- Name: shaun_info_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE shaun_info_id_seq OWNED BY shaun_info.id;


--
-- Name: shaun_session_vars; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE shaun_session_vars (
    id integer DEFAULT nextval('public.shaun_session_vars_id_seq'::text) NOT NULL,
    name text NOT NULL,
    sid integer,
    value text
);


ALTER TABLE public.shaun_session_vars OWNER TO apache;

--
-- Name: shaun_session_vars_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.shaun_session_vars_id_seq OWNER TO apache;

--
-- Name: shaun_session_vars_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE shaun_session_vars_id_seq OWNED BY shaun_session_vars.id;


--
-- Name: shaun_sessions; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE shaun_sessions (
    id integer DEFAULT nextval('public.shaun_sessions_id_seq'::text) NOT NULL,
    sesstime integer,
    rand integer,
    ip text
);


ALTER TABLE public.shaun_sessions OWNER TO apache;

--
-- Name: shaun_sessions_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.shaun_sessions_id_seq OWNER TO apache;

--
-- Name: shaun_sessions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE shaun_sessions_id_seq OWNED BY shaun_sessions.id;


--
-- Name: shaun_users; Type: TABLE; Schema: public; Owner: apache; Tablespace: 
--

CREATE TABLE shaun_users (
    id integer DEFAULT nextval('public.shaun_users_id_seq'::text) NOT NULL,
    username character varying(20) NOT NULL,
    privs text,
    password character(32) NOT NULL
);


ALTER TABLE public.shaun_users OWNER TO apache;

--
-- Name: shaun_users_id_seq; Type: SEQUENCE; Schema: public; Owner: apache
--

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


ALTER TABLE public.shaun_users_id_seq OWNER TO apache;

--
-- Name: shaun_users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: apache
--

ALTER SEQUENCE shaun_users_id_seq OWNED BY shaun_users.id;


--
-- Name: icc_admin_areas_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_admin_areas
    ADD CONSTRAINT icc_admin_areas_id_key UNIQUE (id);


--
-- Name: icc_countries_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_countries
    ADD CONSTRAINT icc_countries_id_key UNIQUE (id);


--
-- Name: icc_countries_name_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_countries
    ADD CONSTRAINT icc_countries_name_key UNIQUE (name);


--
-- Name: icc_globals_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_globals
    ADD CONSTRAINT icc_globals_id_key UNIQUE (id);


--
-- Name: icc_members_pkey; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_members
    ADD CONSTRAINT icc_members_pkey PRIMARY KEY (id);


--
-- Name: icc_positions_pkey; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_positions
    ADD CONSTRAINT icc_positions_pkey PRIMARY KEY (id);


--
-- Name: icc_session_vars_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_session_vars
    ADD CONSTRAINT icc_session_vars_id_key UNIQUE (id);


--
-- Name: icc_sessions_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_sessions
    ADD CONSTRAINT icc_sessions_id_key UNIQUE (id);


--
-- Name: icc_states_pkey; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_states
    ADD CONSTRAINT icc_states_pkey PRIMARY KEY (id);


--
-- Name: icc_users_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY icc_users
    ADD CONSTRAINT icc_users_id_key UNIQUE (id);


--
-- Name: shaun_globals_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY shaun_globals
    ADD CONSTRAINT shaun_globals_id_key UNIQUE (id);


--
-- Name: shaun_session_vars_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY shaun_session_vars
    ADD CONSTRAINT shaun_session_vars_id_key UNIQUE (id);


--
-- Name: shaun_sessions_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY shaun_sessions
    ADD CONSTRAINT shaun_sessions_id_key UNIQUE (id);


--
-- Name: shaun_users_id_key; Type: CONSTRAINT; Schema: public; Owner: apache; Tablespace: 
--

ALTER TABLE ONLY shaun_users
    ADD CONSTRAINT shaun_users_id_key UNIQUE (id);


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

ALTER TABLE ONLY icc_session_vars
    ADD CONSTRAINT "$1" FOREIGN KEY (sid) REFERENCES icc_sessions(id) ON UPDATE CASCADE ON DELETE CASCADE;


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

ALTER TABLE ONLY icc_countries
    ADD CONSTRAINT "$1" FOREIGN KEY (admin_by) REFERENCES icc_admin_areas(id) ON UPDATE CASCADE;


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

ALTER TABLE ONLY icc_states
    ADD CONSTRAINT "$1" FOREIGN KEY (country_id) REFERENCES icc_countries(id) ON UPDATE CASCADE;


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

ALTER TABLE ONLY icc_members
    ADD CONSTRAINT "$1" FOREIGN KEY (country_id) REFERENCES icc_countries(id) ON UPDATE CASCADE;


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

ALTER TABLE ONLY icc_positions
    ADD CONSTRAINT "$1" FOREIGN KEY (member_id) REFERENCES icc_members(id) ON UPDATE CASCADE;


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

ALTER TABLE ONLY shaun_session_vars
    ADD CONSTRAINT "$1" FOREIGN KEY (sid) REFERENCES shaun_sessions(id) ON UPDATE CASCADE ON DELETE CASCADE;


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

ALTER TABLE ONLY icc_members
    ADD CONSTRAINT "$2" FOREIGN KEY (state_id) REFERENCES icc_states(id) ON UPDATE CASCADE;


--
-- 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 apache;


--
-- PostgreSQL database dump complete
--

\connect postgres

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

\connect shaun

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

\connect template1

--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON DATABASE template1 IS 'Default template database';


--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database cluster dump complete
--