--
-- PostgreSQL database dump
--

SET client_encoding = 'LATIN1';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: debug; Type: SCHEMA; Schema: -; Owner: pgadmin
--

CREATE SCHEMA debug;


ALTER SCHEMA debug OWNER TO pgadmin;

SET search_path = debug, pg_catalog;

--
-- Name: add_course_form(integer, integer); Type: FUNCTION; Schema: debug; Owner: pgadmin
--

CREATE FUNCTION add_course_form(integer, integer) RETURNS integer
    AS $_$
DECLARE
   my_fid ALIAS FOR $1;
   my_cid ALIAS FOR $2;
   finst integer;   
BEGIN
   select nextval('debug.form_instance_id') into finst;
   insert into debug.form_instance (finstance, fid, type)
	values(finst, my_fid, 'COURSE');
   insert into debug.course_form (finstance, cid)
	values (finst, my_cid);
   return finst;
END
$_$
    LANGUAGE plpgsql;


ALTER FUNCTION debug.add_course_form(integer, integer) OWNER TO pgadmin;

--
-- Name: get_atype(integer); Type: FUNCTION; Schema: debug; Owner: pgadmin
--

CREATE FUNCTION get_atype(integer) RETURNS text
    AS $_$
DECLARE
   my_qid ALIAS FOR $1;
   my_qtype integer;
BEGIN
   select qtype 
       from debug.form_q 
       where qid=my_qid
       into my_qtype;  
   if my_qtype is null then
      return '';
   end if;
   return 
      (select atype
       from debug.form_qtypes
       where qtype=my_qtype);
END
$_$
    LANGUAGE plpgsql STABLE;


ALTER FUNCTION debug.get_atype(integer) OWNER TO pgadmin;

--
-- Name: match_q_instance(integer, integer); Type: FUNCTION; Schema: debug; Owner: pgadmin
--

CREATE FUNCTION match_q_instance(integer, integer) RETURNS boolean
    AS $_$
DECLARE
   my_finstance ALIAS FOR $1;
   my_qid ALIAS FOR $2;
   q_fid integer;
   finst_fid integer;
BEGIN
   select fid
       from debug.form_instance
       where finstance=my_finstance
       into finst_fid;  
   select fid
       from debug.form_q
       where qid=my_qid
       into q_fid;  

   return finst_fid = q_fid;
END
$_$
    LANGUAGE plpgsql STABLE;


ALTER FUNCTION debug.match_q_instance(integer, integer) OWNER TO pgadmin;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: course_form; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE course_form (
    finstance integer NOT NULL,
    cid integer NOT NULL
);


ALTER TABLE debug.course_form OWNER TO pgadmin;

--
-- Name: form_a_int; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_a_int (
    finstance integer NOT NULL,
    qid integer NOT NULL,
    uid integer NOT NULL,
    a_int integer,
    CONSTRAINT form_a_int_check CHECK (match_q_instance(finstance, qid)),
    CONSTRAINT form_a_int_qid_check CHECK ((get_atype(qid) = 'INT'::text))
);


ALTER TABLE debug.form_a_int OWNER TO pgadmin;

--
-- Name: form_a_text; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_a_text (
    finstance integer NOT NULL,
    qid integer NOT NULL,
    uid integer NOT NULL,
    a_text text,
    CONSTRAINT form_a_text_check CHECK (match_q_instance(finstance, qid)),
    CONSTRAINT form_a_text_qid_check CHECK ((get_atype(qid) = 'TEXT'::text))
);


ALTER TABLE debug.form_a_text OWNER TO pgadmin;

--
-- Name: form_q; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_q (
    qid integer DEFAULT nextval('debug.form_q_id'::text) NOT NULL,
    fid integer NOT NULL,
    qno integer NOT NULL,
    qtype integer NOT NULL,
    qtext text,
    CONSTRAINT form_q_qno_check CHECK ((qno >= 0))
);


ALTER TABLE debug.form_q OWNER TO pgadmin;

--
-- Name: form_qtypes; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_qtypes (
    qtype integer NOT NULL,
    atype text NOT NULL,
    CONSTRAINT form_qtypes_atype_check CHECK (((atype = 'INT'::text) OR (atype = 'TEXT'::text)))
);


ALTER TABLE debug.form_qtypes OWNER TO pgadmin;

--
-- Name: form_answers; Type: VIEW; Schema: debug; Owner: pgadmin
--

CREATE VIEW form_answers AS
    SELECT form_q.fid, finstance, form_q.qno, qid, form_q.qtype, form_qtypes.atype, uid, "at".a_text, ai.a_int FROM (((form_a_int ai NATURAL FULL JOIN form_a_text "at") NATURAL JOIN form_q) NATURAL JOIN form_qtypes) ORDER BY form_q.fid, finstance, form_q.qno, uid;


ALTER TABLE debug.form_answers OWNER TO pgadmin;

--
-- Name: form_id; Type: SEQUENCE; Schema: debug; Owner: pgadmin
--

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


ALTER TABLE debug.form_id OWNER TO pgadmin;

--
-- Name: form_id; Type: SEQUENCE SET; Schema: debug; Owner: pgadmin
--

SELECT pg_catalog.setval('form_id', 1, true);


--
-- Name: form_instance; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_instance (
    finstance integer DEFAULT nextval('debug.form_instance_id'::text) NOT NULL,
    fid integer NOT NULL,
    "type" text,
    CONSTRAINT form_instance_type_check CHECK (("type" = 'COURSE'::text))
);


ALTER TABLE debug.form_instance OWNER TO pgadmin;

--
-- Name: form_instance_id; Type: SEQUENCE; Schema: debug; Owner: pgadmin
--

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


ALTER TABLE debug.form_instance_id OWNER TO pgadmin;

--
-- Name: form_instance_id; Type: SEQUENCE SET; Schema: debug; Owner: pgadmin
--

SELECT pg_catalog.setval('form_instance_id', 1, true);


--
-- Name: form_q_alt; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_q_alt (
    qid integer NOT NULL,
    altno integer NOT NULL,
    altvalue text NOT NULL,
    alttext text,
    CONSTRAINT form_q_alt_altno_check CHECK ((altno >= 0))
);


ALTER TABLE debug.form_q_alt OWNER TO pgadmin;

--
-- Name: form_q_id; Type: SEQUENCE; Schema: debug; Owner: pgadmin
--

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


ALTER TABLE debug.form_q_id OWNER TO pgadmin;

--
-- Name: form_q_id; Type: SEQUENCE SET; Schema: debug; Owner: pgadmin
--

SELECT pg_catalog.setval('form_q_id', 2, true);


--
-- Name: form_template; Type: TABLE; Schema: debug; Owner: pgadmin; Tablespace: 
--

CREATE TABLE form_template (
    fid integer DEFAULT nextval('debug.form_id'::text) NOT NULL,
    name text
);


ALTER TABLE debug.form_template OWNER TO pgadmin;

--
-- Data for Name: course_form; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY course_form (finstance, cid) FROM stdin;
1	2
\.


--
-- Data for Name: form_a_int; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_a_int (finstance, qid, uid, a_int) FROM stdin;
1	2	1109	5
\.


--
-- Data for Name: form_a_text; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_a_text (finstance, qid, uid, a_text) FROM stdin;
1	1	1109	foo
\.


--
-- Data for Name: form_instance; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_instance (finstance, fid, "type") FROM stdin;
1	1	COURSE
\.


--
-- Data for Name: form_q; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_q (qid, fid, qno, qtype, qtext) FROM stdin;
1	1	1	1	Fie
2	1	2	2	Fum
\.


--
-- Data for Name: form_q_alt; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_q_alt (qid, altno, altvalue, alttext) FROM stdin;
\.


--
-- Data for Name: form_qtypes; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_qtypes (qtype, atype) FROM stdin;
1	TEXT
2	INT
3	INT
\.


--
-- Data for Name: form_template; Type: TABLE DATA; Schema: debug; Owner: pgadmin
--

COPY form_template (fid, name) FROM stdin;
1	Test template
\.


--
-- Name: course_form_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY course_form
    ADD CONSTRAINT course_form_pkey PRIMARY KEY (finstance);


ALTER INDEX debug.course_form_pkey OWNER TO pgadmin;

--
-- Name: form_a_int_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_a_int
    ADD CONSTRAINT form_a_int_pkey PRIMARY KEY (finstance, qid, uid);


ALTER INDEX debug.form_a_int_pkey OWNER TO pgadmin;

--
-- Name: form_a_text_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_a_text
    ADD CONSTRAINT form_a_text_pkey PRIMARY KEY (finstance, qid, uid);


ALTER INDEX debug.form_a_text_pkey OWNER TO pgadmin;

--
-- Name: form_instance_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_instance
    ADD CONSTRAINT form_instance_pkey PRIMARY KEY (finstance);


ALTER INDEX debug.form_instance_pkey OWNER TO pgadmin;

--
-- Name: form_q_alt_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_q_alt
    ADD CONSTRAINT form_q_alt_pkey PRIMARY KEY (qid, altno);


ALTER INDEX debug.form_q_alt_pkey OWNER TO pgadmin;

--
-- Name: form_q_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_q
    ADD CONSTRAINT form_q_pkey PRIMARY KEY (qid);


ALTER INDEX debug.form_q_pkey OWNER TO pgadmin;

--
-- Name: form_qtypes_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_qtypes
    ADD CONSTRAINT form_qtypes_pkey PRIMARY KEY (qtype);


ALTER INDEX debug.form_qtypes_pkey OWNER TO pgadmin;

--
-- Name: form_template_pkey; Type: CONSTRAINT; Schema: debug; Owner: pgadmin; Tablespace: 
--

ALTER TABLE ONLY form_template
    ADD CONSTRAINT form_template_pkey PRIMARY KEY (fid);


ALTER INDEX debug.form_template_pkey OWNER TO pgadmin;

--
-- Name: course_form_finstance_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY course_form
    ADD CONSTRAINT course_form_finstance_fkey FOREIGN KEY (finstance) REFERENCES form_instance(finstance) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_a_int_finstance_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_int
    ADD CONSTRAINT form_a_int_finstance_fkey FOREIGN KEY (finstance) REFERENCES form_instance(finstance) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_a_int_qid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_int
    ADD CONSTRAINT form_a_int_qid_fkey FOREIGN KEY (qid) REFERENCES form_q(qid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_a_text_finstance_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_text
    ADD CONSTRAINT form_a_text_finstance_fkey FOREIGN KEY (finstance) REFERENCES form_instance(finstance) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_a_text_qid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_a_text
    ADD CONSTRAINT form_a_text_qid_fkey FOREIGN KEY (qid) REFERENCES form_q(qid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_instance_fid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_instance
    ADD CONSTRAINT form_instance_fid_fkey FOREIGN KEY (fid) REFERENCES form_template(fid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_q_alt_qid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_q_alt
    ADD CONSTRAINT form_q_alt_qid_fkey FOREIGN KEY (qid) REFERENCES form_q(qid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_q_fid_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_q
    ADD CONSTRAINT form_q_fid_fkey FOREIGN KEY (fid) REFERENCES form_template(fid) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: form_q_qtype_fkey; Type: FK CONSTRAINT; Schema: debug; Owner: pgadmin
--

ALTER TABLE ONLY form_q
    ADD CONSTRAINT form_q_qtype_fkey FOREIGN KEY (qtype) REFERENCES form_qtypes(qtype) ON UPDATE CASCADE;