--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.4
-- Dumped by pg_dump version 9.0.4
-- Started on 2011-06-29 18:45:35

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 342 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 18 (class 1255 OID 17866)
-- Dependencies: 342 6
-- Name: truncate_tables(character varying); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION truncate_tables(username character varying) RETURNS void
    LANGUAGE plpgsql
    AS $$
DECLARE
    stmt RECORD;
    statements CURSOR FOR SELECT tablename FROM pg_tables WHERE tableowner = username;
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;                         
$$;


ALTER FUNCTION public.truncate_tables(username character varying) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1534 (class 1259 OID 17867)
-- Dependencies: 6
-- Name: actions; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE actions (
    action character varying(64) NOT NULL
);


ALTER TABLE public.actions OWNER TO test;

--
-- TOC entry 1535 (class 1259 OID 17870)
-- Dependencies: 6
-- Name: answers; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE answers (
    answerid bigint NOT NULL,
    points smallint,
    usercomments text,
    atestid bigint
);


ALTER TABLE public.answers OWNER TO test;

--
-- TOC entry 1536 (class 1259 OID 17876)
-- Dependencies: 6
-- Name: questions; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE questions (
    questionid bigint NOT NULL,
    points smallint,
    correctanswer text,
    qmodelid bigint
);


ALTER TABLE public.questions OWNER TO test;

--
-- TOC entry 1537 (class 1259 OID 17882)
-- Dependencies: 1536 6
-- Name: answers_answerid_seq; Type: SEQUENCE; Schema: public; Owner: test
--

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


ALTER TABLE public.answers_answerid_seq OWNER TO test;

--
-- TOC entry 1885 (class 0 OID 0)
-- Dependencies: 1537
-- Name: answers_answerid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--

ALTER SEQUENCE answers_answerid_seq OWNED BY questions.questionid;


--
-- TOC entry 1538 (class 1259 OID 17884)
-- Dependencies: 6 1535
-- Name: answers_answerid_seq1; Type: SEQUENCE; Schema: public; Owner: test
--

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


ALTER TABLE public.answers_answerid_seq1 OWNER TO test;

--
-- TOC entry 1886 (class 0 OID 0)
-- Dependencies: 1538
-- Name: answers_answerid_seq1; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--

ALTER SEQUENCE answers_answerid_seq1 OWNED BY answers.answerid;


--
-- TOC entry 1539 (class 1259 OID 17886)
-- Dependencies: 6
-- Name: groups; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE groups (
    grusername character varying(64) NOT NULL,
    grrole character varying(64)
);


ALTER TABLE public.groups OWNER TO test;

--
-- TOC entry 1540 (class 1259 OID 17889)
-- Dependencies: 6
-- Name: models; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE models (
    modelid bigint NOT NULL,
    modelname character varying(64),
    "group" character varying(64)
);


ALTER TABLE public.models OWNER TO test;

--
-- TOC entry 1541 (class 1259 OID 17892)
-- Dependencies: 6 1540
-- Name: models_modelid_seq; Type: SEQUENCE; Schema: public; Owner: test
--

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


ALTER TABLE public.models_modelid_seq OWNER TO test;

--
-- TOC entry 1887 (class 0 OID 0)
-- Dependencies: 1541
-- Name: models_modelid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--

ALTER SEQUENCE models_modelid_seq OWNED BY models.modelid;


--
-- TOC entry 1542 (class 1259 OID 17894)
-- Dependencies: 6
-- Name: objects; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE objects (
    objects character varying(64) NOT NULL
);


ALTER TABLE public.objects OWNER TO test;

--
-- TOC entry 1543 (class 1259 OID 17897)
-- Dependencies: 6
-- Name: privileges; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE privileges (
    privileges character varying(64) NOT NULL,
    proleid character varying(64)
);


ALTER TABLE public.privileges OWNER TO test;

--
-- TOC entry 1544 (class 1259 OID 17900)
-- Dependencies: 6
-- Name: roles; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE roles (
    role character varying(64) NOT NULL,
    rolename character varying(64)
);


ALTER TABLE public.roles OWNER TO test;

--
-- TOC entry 1545 (class 1259 OID 17903)
-- Dependencies: 1834 6
-- Name: tasks; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE tasks (
    taskid bigint NOT NULL,
    tasktype character varying(64),
    predefined boolean DEFAULT false,
    taction character varying(64),
    tobject character varying(64),
    tprivilegeid character varying(64)
);


ALTER TABLE public.tasks OWNER TO test;

--
-- TOC entry 1546 (class 1259 OID 17907)
-- Dependencies: 6 1545
-- Name: tasks_taskid_seq; Type: SEQUENCE; Schema: public; Owner: test
--

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


ALTER TABLE public.tasks_taskid_seq OWNER TO test;

--
-- TOC entry 1888 (class 0 OID 0)
-- Dependencies: 1546
-- Name: tasks_taskid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--

ALTER SEQUENCE tasks_taskid_seq OWNED BY tasks.taskid;


--
-- TOC entry 1547 (class 1259 OID 17909)
-- Dependencies: 6
-- Name: testassignments; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE testassignments (
    username character varying(64),
    tatestid bigint,
    taid bigint NOT NULL
);


ALTER TABLE public.testassignments OWNER TO test;

--
-- TOC entry 1548 (class 1259 OID 17912)
-- Dependencies: 6 1547
-- Name: testassignments_taid_seq; Type: SEQUENCE; Schema: public; Owner: test
--

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


ALTER TABLE public.testassignments_taid_seq OWNER TO test;

--
-- TOC entry 1889 (class 0 OID 0)
-- Dependencies: 1548
-- Name: testassignments_taid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--

ALTER SEQUENCE testassignments_taid_seq OWNED BY testassignments.taid;


--
-- TOC entry 1549 (class 1259 OID 17914)
-- Dependencies: 6
-- Name: testgroups; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE testgroups (
    "group" character varying(64) NOT NULL
);


ALTER TABLE public.testgroups OWNER TO test;

--
-- TOC entry 1550 (class 1259 OID 17917)
-- Dependencies: 1837 6
-- Name: tests; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE tests (
    testid bigint NOT NULL,
    testmodelid bigint,
    marked boolean DEFAULT false,
    testimage oid
);


ALTER TABLE public.tests OWNER TO test;

--
-- TOC entry 1551 (class 1259 OID 17921)
-- Dependencies: 6 1550
-- Name: tests_testid_seq; Type: SEQUENCE; Schema: public; Owner: test
--

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


ALTER TABLE public.tests_testid_seq OWNER TO test;

--
-- TOC entry 1890 (class 0 OID 0)
-- Dependencies: 1551
-- Name: tests_testid_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--

ALTER SEQUENCE tests_testid_seq OWNED BY tests.testid;


--
-- TOC entry 1552 (class 1259 OID 17923)
-- Dependencies: 6
-- Name: types; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE types (
    tasktype character varying(64) NOT NULL
);


ALTER TABLE public.types OWNER TO test;

--
-- TOC entry 1553 (class 1259 OID 17926)
-- Dependencies: 6
-- Name: users; Type: TABLE; Schema: public; Owner: test; Tablespace: 
--

CREATE TABLE users (
    username character varying(64) NOT NULL,
    password character varying(64),
    email character varying(40),
    lastname character varying(50),
    firstname character varying(40)
);


ALTER TABLE public.users OWNER TO test;

--
-- TOC entry 1831 (class 2604 OID 17929)
-- Dependencies: 1538 1535
-- Name: answerid; Type: DEFAULT; Schema: public; Owner: test
--

ALTER TABLE answers ALTER COLUMN answerid SET DEFAULT nextval('answers_answerid_seq1'::regclass);


--
-- TOC entry 1833 (class 2604 OID 17930)
-- Dependencies: 1541 1540
-- Name: modelid; Type: DEFAULT; Schema: public; Owner: test
--

ALTER TABLE models ALTER COLUMN modelid SET DEFAULT nextval('models_modelid_seq'::regclass);


--
-- TOC entry 1832 (class 2604 OID 17931)
-- Dependencies: 1537 1536
-- Name: questionid; Type: DEFAULT; Schema: public; Owner: test
--

ALTER TABLE questions ALTER COLUMN questionid SET DEFAULT nextval('answers_answerid_seq'::regclass);


--
-- TOC entry 1835 (class 2604 OID 17932)
-- Dependencies: 1546 1545
-- Name: taskid; Type: DEFAULT; Schema: public; Owner: test
--

ALTER TABLE tasks ALTER COLUMN taskid SET DEFAULT nextval('tasks_taskid_seq'::regclass);


--
-- TOC entry 1836 (class 2604 OID 17933)
-- Dependencies: 1548 1547
-- Name: taid; Type: DEFAULT; Schema: public; Owner: test
--

ALTER TABLE testassignments ALTER COLUMN taid SET DEFAULT nextval('testassignments_taid_seq'::regclass);


--
-- TOC entry 1838 (class 2604 OID 17934)
-- Dependencies: 1551 1550
-- Name: testid; Type: DEFAULT; Schema: public; Owner: test
--

ALTER TABLE tests ALTER COLUMN testid SET DEFAULT nextval('tests_testid_seq'::regclass);


--
-- TOC entry 1840 (class 2606 OID 17936)
-- Dependencies: 1534 1534
-- Name: actions_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY actions
    ADD CONSTRAINT actions_pkey PRIMARY KEY (action);


--
-- TOC entry 1844 (class 2606 OID 17938)
-- Dependencies: 1536 1536
-- Name: answers_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY questions
    ADD CONSTRAINT answers_pkey PRIMARY KEY (questionid);


--
-- TOC entry 1842 (class 2606 OID 17940)
-- Dependencies: 1535 1535
-- Name: answers_pkey1; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY answers
    ADD CONSTRAINT answers_pkey1 PRIMARY KEY (answerid);


--
-- TOC entry 1846 (class 2606 OID 17942)
-- Dependencies: 1539 1539
-- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (grusername);


--
-- TOC entry 1848 (class 2606 OID 17944)
-- Dependencies: 1540 1540
-- Name: models_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY models
    ADD CONSTRAINT models_pkey PRIMARY KEY (modelid);


--
-- TOC entry 1850 (class 2606 OID 17946)
-- Dependencies: 1542 1542
-- Name: objects_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY objects
    ADD CONSTRAINT objects_pkey PRIMARY KEY (objects);


--
-- TOC entry 1852 (class 2606 OID 17948)
-- Dependencies: 1543 1543
-- Name: privileges_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY privileges
    ADD CONSTRAINT privileges_pkey PRIMARY KEY (privileges);


--
-- TOC entry 1854 (class 2606 OID 17950)
-- Dependencies: 1544 1544
-- Name: roles_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY roles
    ADD CONSTRAINT roles_pkey PRIMARY KEY (role);


--
-- TOC entry 1856 (class 2606 OID 17952)
-- Dependencies: 1545 1545
-- Name: tasks_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_pkey PRIMARY KEY (taskid);


--
-- TOC entry 1858 (class 2606 OID 17954)
-- Dependencies: 1547 1547
-- Name: testassignments_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY testassignments
    ADD CONSTRAINT testassignments_pkey PRIMARY KEY (taid);


--
-- TOC entry 1860 (class 2606 OID 17956)
-- Dependencies: 1549 1549
-- Name: testgroup_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY testgroups
    ADD CONSTRAINT testgroup_pkey PRIMARY KEY ("group");


--
-- TOC entry 1862 (class 2606 OID 17958)
-- Dependencies: 1550 1550
-- Name: tests_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY tests
    ADD CONSTRAINT tests_pkey PRIMARY KEY (testid);


--
-- TOC entry 1864 (class 2606 OID 17960)
-- Dependencies: 1552 1552
-- Name: types_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

ALTER TABLE ONLY types
    ADD CONSTRAINT types_pkey PRIMARY KEY (tasktype);


--
-- TOC entry 1866 (class 2606 OID 17962)
-- Dependencies: 1553 1553
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: test; Tablespace: 
--

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


--
-- TOC entry 1867 (class 2606 OID 17963)
-- Dependencies: 1550 1535 1861
-- Name: answers_atestid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY answers
    ADD CONSTRAINT answers_atestid_fkey FOREIGN KEY (atestid) REFERENCES tests(testid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1869 (class 2606 OID 17968)
-- Dependencies: 1539 1544 1853
-- Name: groups_grrole_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_grrole_fkey FOREIGN KEY (grrole) REFERENCES roles(role) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1870 (class 2606 OID 17973)
-- Dependencies: 1865 1539 1553
-- Name: groups_grusername_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY groups
    ADD CONSTRAINT groups_grusername_fkey FOREIGN KEY (grusername) REFERENCES users(username) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1871 (class 2606 OID 17978)
-- Dependencies: 1540 1859 1549
-- Name: models_group_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY models
    ADD CONSTRAINT models_group_fkey FOREIGN KEY ("group") REFERENCES testgroups("group");


--
-- TOC entry 1872 (class 2606 OID 17983)
-- Dependencies: 1544 1543 1853
-- Name: privileges_proleid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY privileges
    ADD CONSTRAINT privileges_proleid_fkey FOREIGN KEY (proleid) REFERENCES roles(role) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1868 (class 2606 OID 17988)
-- Dependencies: 1540 1847 1536
-- Name: questions_qmodelid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY questions
    ADD CONSTRAINT questions_qmodelid_fkey FOREIGN KEY (qmodelid) REFERENCES models(modelid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1873 (class 2606 OID 17993)
-- Dependencies: 1545 1534 1839
-- Name: tasks_taction_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_taction_fkey FOREIGN KEY (taction) REFERENCES actions(action) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- TOC entry 1874 (class 2606 OID 17998)
-- Dependencies: 1863 1545 1552
-- Name: tasks_tasktype_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_tasktype_fkey FOREIGN KEY (tasktype) REFERENCES types(tasktype) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- TOC entry 1875 (class 2606 OID 18003)
-- Dependencies: 1545 1849 1542
-- Name: tasks_tobject_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_tobject_fkey FOREIGN KEY (tobject) REFERENCES objects(objects) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- TOC entry 1876 (class 2606 OID 18008)
-- Dependencies: 1543 1545 1851
-- Name: tasks_tprivilegeid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_tprivilegeid_fkey FOREIGN KEY (tprivilegeid) REFERENCES privileges(privileges) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1877 (class 2606 OID 18013)
-- Dependencies: 1547 1550 1861
-- Name: testassignments_tatestid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY testassignments
    ADD CONSTRAINT testassignments_tatestid_fkey FOREIGN KEY (tatestid) REFERENCES tests(testid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1878 (class 2606 OID 18018)
-- Dependencies: 1547 1553 1865
-- Name: testassignments_username_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY testassignments
    ADD CONSTRAINT testassignments_username_fkey FOREIGN KEY (username) REFERENCES users(username) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- TOC entry 1879 (class 2606 OID 18023)
-- Dependencies: 1540 1847 1550
-- Name: tests_testmodelid_fkey; Type: FK CONSTRAINT; Schema: public; Owner: test
--

ALTER TABLE ONLY tests
    ADD CONSTRAINT tests_testmodelid_fkey FOREIGN KEY (testmodelid) REFERENCES models(modelid) ON UPDATE CASCADE ON DELETE SET NULL;


--
-- TOC entry 1884 (class 0 OID 0)
-- Dependencies: 6
-- 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;


-- Completed on 2011-06-29 18:45:35

--
-- PostgreSQL database dump complete
--