· 9 years ago · Oct 12, 2016, 11:42 PM
1--
2-- PostgreSQL database dump
3--
4
5-- Dumped from database version 9.3.14
6-- Dumped by pg_dump version 9.5.1
7
8-- Started on 2016-10-12 14:37:10 EDT
9
10SET statement_timeout = 0;
11SET lock_timeout = 0;
12SET client_encoding = 'UTF8';
13SET standard_conforming_strings = on;
14SET check_function_bodies = false;
15SET client_min_messages = warning;
16SET row_security = off;
17
18--
19-- TOC entry 1971 (class 1262 OID 12035)
20-- Dependencies: 1970
21-- Name: postgres; Type: COMMENT; Schema: -; Owner: -
22--
23
24COMMENT ON DATABASE postgres IS 'default administrative connection database';
25
26
27--
28-- TOC entry 2 (class 3079 OID 11756)
29-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: -
30--
31
32CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
33
34
35--
36-- TOC entry 1973 (class 0 OID 0)
37-- Dependencies: 2
38-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: -
39--
40
41COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
42
43
44--
45-- TOC entry 1 (class 3079 OID 17254)
46-- Name: adminpack; Type: EXTENSION; Schema: -; Owner: -
47--
48
49CREATE EXTENSION IF NOT EXISTS adminpack WITH SCHEMA pg_catalog;
50
51
52--
53-- TOC entry 1974 (class 0 OID 0)
54-- Dependencies: 1
55-- Name: EXTENSION adminpack; Type: COMMENT; Schema: -; Owner: -
56--
57
58COMMENT ON EXTENSION adminpack IS 'administrative functions for PostgreSQL';
59
60
61SET search_path = public, pg_catalog;
62
63--
64-- TOC entry 189 (class 1255 OID 17269)
65-- Name: csv_to_numeric_gradeable(text[], text, text); Type: FUNCTION; Schema: public; Owner: -
66--
67
68CREATE FUNCTION csv_to_numeric_gradeable(vcode text[], gradeable_id text, grader_id text) RETURNS boolean
69 LANGUAGE plpgsql
70 AS $$
71 DECLARE
72 -- Size of first array after splitting
73 size INTEGER;
74 -- Array of individual line after splitting
75 line TEXT[];
76 -- Variable to store each line in the array
77 i TEXT;
78 -- Array of gc_ids for this gradeable
79 gcids INTEGER[];
80 -- gradeable_data id for this gradeable for this student
81 gdid INTEGER;
82 -- Array counter
83 j INTEGER;
84 -- Is this gradeable component text?
85 istext BOOLEAN[];
86 --Score to be inserted
87 score NUMERIC;
88 BEGIN
89 gcids := ARRAY(SELECT gc_id FROM gradeable_component WHERE g_id = gradeable_id);
90 istext := ARRAY(SELECT gc_is_text FROM gradeable_component WHERE g_id = gradeable_id);
91 -- Get the number of gradeable components for this gradeable. Will be used to test
92 -- for uniform sized arrays
93 size := array_length(gcids, 1);
94 FOREACH i IN ARRAY vcode
95 LOOP
96 -- Split the current line
97 line := string_to_array(i, ',');
98 -- Check for uniform size
99 IF array_length(line, 1) <> size + 1 THEN
100 RAISE EXCEPTION 'INVALID SIZE: Arrays are jagged.';
101 END IF;
102
103 -- Remove any existing record for this student for this gradeable
104 DELETE FROM gradeable_data WHERE gd_user_id = line[1] AND g_id = gradeable_id;
105
106 INSERT INTO gradeable_data(g_id, gd_user_id, gd_grader_id, gd_overall_comment, gd_status
107 , gd_late_days_used, gd_active_version) VALUES (gradeable_id, line[1],grader_id, '', 0,0,1);
108
109 SELECT gd_id INTO gdid FROM gradeable_data WHERE g_id = gradeable_id AND gd_user_id = line[1];
110
111 FOR j IN 1..size
112 LOOP
113 IF istext[j] THEN
114 INSERT INTO gradeable_component_data(gc_id, gd_id, gcd_score, gcd_component_comment) VALUES (gcids[j], gdid,0, line[j+1]);
115 ELSE
116 score := CAST(line[j+1] AS NUMERIC);
117 INSERT INTO gradeable_component_data(gc_id, gd_id, gcd_score, gcd_component_comment) VALUES (gcids[j], gdid, score, '');
118 END IF;
119 END LOOP;
120
121 END LOOP;
122 RETURN TRUE ;
123 END;
124 $$;
125
126
127SET default_with_oids = false;
128
129--
130-- TOC entry 172 (class 1259 OID 17270)
131-- Name: gradeable_component; Type: TABLE; Schema: public; Owner: -
132--
133
134CREATE TABLE gradeable_component (
135 gc_id integer NOT NULL,
136 g_id character varying(255) NOT NULL,
137 gc_title character varying(255) NOT NULL,
138 gc_ta_comment character varying NOT NULL,
139 gc_student_comment character varying NOT NULL,
140 gc_max_value numeric NOT NULL,
141 gc_is_text boolean NOT NULL,
142 gc_is_extra_credit boolean NOT NULL,
143 gc_order integer NOT NULL
144);
145
146
147--
148-- TOC entry 173 (class 1259 OID 17279)
149-- Name: gradeable_component_data; Type: TABLE; Schema: public; Owner: -
150--
151
152CREATE TABLE gradeable_component_data (
153 gc_id integer NOT NULL,
154 gd_id integer NOT NULL,
155 gcd_score numeric NOT NULL,
156 gcd_component_comment character varying NOT NULL
157);
158
159
160--
161-- TOC entry 174 (class 1259 OID 17285)
162-- Name: gradeable_component_gc_id_seq; Type: SEQUENCE; Schema: public; Owner: -
163--
164
165CREATE SEQUENCE gradeable_component_gc_id_seq
166 START WITH 1
167 INCREMENT BY 1
168 NO MINVALUE
169 NO MAXVALUE
170 CACHE 1;
171
172
173--
174-- TOC entry 1975 (class 0 OID 0)
175-- Dependencies: 174
176-- Name: gradeable_component_gc_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
177--
178
179ALTER SEQUENCE gradeable_component_gc_id_seq OWNED BY gradeable_component.gc_id;
180
181
182--
183-- TOC entry 175 (class 1259 OID 17287)
184-- Name: gradeable_data; Type: TABLE; Schema: public; Owner: -
185--
186
187CREATE TABLE gradeable_data (
188 gd_id integer NOT NULL,
189 g_id character varying(255) NOT NULL,
190 gd_user_id character varying(255) NOT NULL,
191 gd_grader_id character varying(255) NOT NULL,
192 gd_overall_comment character varying NOT NULL,
193 gd_status integer NOT NULL,
194 gd_late_days_used integer NOT NULL,
195 gd_active_version integer NOT NULL
196);
197
198
199--
200-- TOC entry 176 (class 1259 OID 17293)
201-- Name: gradeable_data_gd_id_seq; Type: SEQUENCE; Schema: public; Owner: -
202--
203
204CREATE SEQUENCE gradeable_data_gd_id_seq
205 START WITH 1
206 INCREMENT BY 1
207 NO MINVALUE
208 NO MAXVALUE
209 CACHE 1;
210
211
212--
213-- TOC entry 1976 (class 0 OID 0)
214-- Dependencies: 176
215-- Name: gradeable_data_gd_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
216--
217
218ALTER SEQUENCE gradeable_data_gd_id_seq OWNED BY gradeable_data.gd_id;
219
220
221--
222-- TOC entry 1842 (class 2604 OID 17295)
223-- Name: gc_id; Type: DEFAULT; Schema: public; Owner: -
224--
225
226ALTER TABLE ONLY gradeable_component ALTER COLUMN gc_id SET DEFAULT nextval('gradeable_component_gc_id_seq'::regclass);
227
228
229--
230-- TOC entry 1843 (class 2604 OID 17296)
231-- Name: gd_id; Type: DEFAULT; Schema: public; Owner: -
232--
233
234ALTER TABLE ONLY gradeable_data ALTER COLUMN gd_id SET DEFAULT nextval('gradeable_data_gd_id_seq'::regclass);
235
236
237--
238-- TOC entry 1961 (class 0 OID 17270)
239-- Dependencies: 172
240-- Data for Name: gradeable_component; Type: TABLE DATA; Schema: public; Owner: -
241--
242
243INSERT INTO gradeable_component VALUES (1, 'test', 'Test 1', '', '', 10, false, false, 1);
244INSERT INTO gradeable_component VALUES (2, 'test', 'Test 2', '', '', 10, false, false, 2);
245INSERT INTO gradeable_component VALUES (3, 'test', 'Test 3', '', '', 10, false, false, 3);
246INSERT INTO gradeable_component VALUES (4, 'test', 'Test 4', '', '', 10, false, false, 4);
247INSERT INTO gradeable_component VALUES (5, 'test', 'Test 5', '', '', 10, false, false, 5);
248INSERT INTO gradeable_component VALUES (6, 'test', 'Test 6', '', '', 10, false, false, 6);
249INSERT INTO gradeable_component VALUES (7, 'test', 'Test 7', '', '', 10, false, false, 7);
250INSERT INTO gradeable_component VALUES (8, 'test', 'Test 8', '', '', 10, false, false, 8);
251INSERT INTO gradeable_component VALUES (9, 'test', 'Test 9', '', '', 10, false, false, 9);
252INSERT INTO gradeable_component VALUES (10, 'test', 'Test 10', '', '', 10, false, false, 10);
253INSERT INTO gradeable_component VALUES (11, 'test', 'Test 11', '', '', 0, true, false, 11);
254INSERT INTO gradeable_component VALUES (12, 'test', 'Test 12', '', '', 0, true, false, 12);
255INSERT INTO gradeable_component VALUES (13, 'test', 'Test 13', '', '', 0, true, false, 13);
256
257
258--
259-- TOC entry 1962 (class 0 OID 17279)
260-- Dependencies: 173
261-- Data for Name: gradeable_component_data; Type: TABLE DATA; Schema: public; Owner: -
262--
263
264
265
266--
267-- TOC entry 1977 (class 0 OID 0)
268-- Dependencies: 174
269-- Name: gradeable_component_gc_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
270--
271
272SELECT pg_catalog.setval('gradeable_component_gc_id_seq', 13, true);
273
274
275--
276-- TOC entry 1964 (class 0 OID 17287)
277-- Dependencies: 175
278-- Data for Name: gradeable_data; Type: TABLE DATA; Schema: public; Owner: -
279--
280
281
282
283--
284-- TOC entry 1978 (class 0 OID 0)
285-- Dependencies: 176
286-- Name: gradeable_data_gd_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
287--
288
289SELECT pg_catalog.setval('gradeable_data_gd_id_seq', 11402, true);
290
291
292--
293-- TOC entry 1845 (class 2606 OID 17306)
294-- Name: gc_id_pkey; Type: CONSTRAINT; Schema: public; Owner: -
295--
296
297ALTER TABLE ONLY gradeable_component
298 ADD CONSTRAINT gc_id_pkey PRIMARY KEY (gc_id);
299
300
301--
302-- TOC entry 1849 (class 2606 OID 17314)
303-- Name: gcd_pkey; Type: CONSTRAINT; Schema: public; Owner: -
304--
305
306ALTER TABLE ONLY gradeable_component_data
307 ADD CONSTRAINT gcd_pkey PRIMARY KEY (gc_id, gd_id);
308
309
310--
311-- TOC entry 1851 (class 2606 OID 17298)
312-- Name: gradeable_data_pkey; Type: CONSTRAINT; Schema: public; Owner: -
313--
314
315ALTER TABLE ONLY gradeable_data
316 ADD CONSTRAINT gradeable_data_pkey PRIMARY KEY (gd_id);
317
318
319--
320-- TOC entry 1846 (class 1259 OID 17312)
321-- Name: fki_gcd_gc_id_fkey; Type: INDEX; Schema: public; Owner: -
322--
323
324CREATE INDEX fki_gcd_gc_id_fkey ON gradeable_component_data USING btree (gc_id);
325
326
327--
328-- TOC entry 1847 (class 1259 OID 17304)
329-- Name: fki_gradeable_component_data_fkey; Type: INDEX; Schema: public; Owner: -
330--
331
332CREATE INDEX fki_gradeable_component_data_fkey ON gradeable_component_data USING btree (gd_id);
333
334
335--
336-- TOC entry 1853 (class 2606 OID 17307)
337-- Name: gcd_gc_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
338--
339
340ALTER TABLE ONLY gradeable_component_data
341 ADD CONSTRAINT gcd_gc_id_fkey FOREIGN KEY (gc_id) REFERENCES gradeable_component(gc_id);
342
343
344--
345-- TOC entry 1852 (class 2606 OID 17299)
346-- Name: gradeable_component_data_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
347--
348
349ALTER TABLE ONLY gradeable_component_data
350 ADD CONSTRAINT gradeable_component_data_fkey FOREIGN KEY (gd_id) REFERENCES gradeable_data(gd_id) ON UPDATE CASCADE ON DELETE CASCADE;
351
352
353-- Completed on 2016-10-12 14:37:11 EDT
354
355--
356-- PostgreSQL database dump complete
357--