~azzar1/unity/add-show-desktop-key

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
DO NOT APPLY THIS MIGRATION WITHOUT READING THE FOLLOWING;
-- This migration will delete all problem attempts and saves.
-- The new database schema links attempts and saves to specific worksheets.
-- Worksheets are linked to specific offerings.
-- Problems are no longer referenced by and id number, instead they are
-- referenced by an identifier TEXT field.
-- This means that in order to save your current data, you must link its
-- worksheet to an offering, and link the attempt to a problem identifier.
-- TODO: Write a script to save the problem attempts somehow.

BEGIN;
-- Move the exercises from being stored as flat files, to being stored in
-- The Database
-- Drop Old, Unused tables.
DROP TABLE problem_attempt_breakdown;
DROP TABLE problem_test_case_tag;
DROP TABLE problem_tag;
DROP TABLE problem_test_case;
DROP TABLE problem_prerequisite; 
TRUNCATE worksheet_problem, worksheet;

-- Remove References to problemid
ALTER TABLE problem_attempt DROP CONSTRAINT problem_attempt_problemid_fkey;
ALTER TABLE problem_save DROP CONSTRAINT problem_save_problemid_fkey;
ALTER TABLE worksheet_problem DROP CONSTRAINT worksheet_problem_problemid_fkey;

-- Add fields to problem necessary to store all exercise information in non-xml
ALTER TABLE problem ADD COLUMN name        TEXT;
ALTER TABLE problem ADD COLUMN description TEXT;
ALTER TABLE problem ADD COLUMN partial     TEXT;
ALTER TABLE problem ADD COLUMN solution    TEXT;
ALTER TABLE problem ADD COLUMN include     TEXT;
ALTER TABLE problem ADD COLUMN num_rows    INT4;
-- Drop (now) unused columns spec and problemid
ALTER TABLE problem DROP COLUMN spec;
ALTER TABLE problem DROP COLUMN problemid;

-- Set problems and worksheets to reference exercises by identifier
ALTER TABLE problem_attempt ADD COLUMN worksheetid INT4 REFERENCES worksheet (worksheetid);
ALTER TABLE problem_attempt DROP COLUMN problemid;
ALTER TABLE problem_attempt ADD COLUMN problemid TEXT REFERENCES problem (identifier);

ALTER TABLE problem_save ADD COLUMN worksheetid INT4 REFERENCES worksheet (worksheetid);
ALTER TABLE problem_save DROP COLUMN problemid;
ALTER TABLE problem_save ADD COLUMN problemid TEXT references problem (identifier);

ALTER TABLE worksheet_problem DROP COLUMN problemid;
ALTER TABLE worksheet_problem ADD COLUMN problemid TEXT REFERENCES problem (identifier);

CREATE TABLE test_suite (
    suiteid     SERIAL UNIQUE NOT NULL,
    problemid   TEXT REFERENCES problem (identifier) NOT NULL,
    description text,
    seq_no      INT4,
    PRIMARY KEY (problemid, suiteid)
);

CREATE TABLE test_case (
    testid      SERIAL UNIQUE NOT NULL,
    suiteid     INT4 REFERENCES test_suite (suiteid) NOT NULL,
    passmsg     TEXT,
    failmsg     TEXT,
    init        TEXT,
    code_type   TEXT,
    code        TEXT,
    testtype    TEXT,
    seq_no    INT4,
    PRIMARY KEY (testid, suiteid)
);

-- Link worksheets to offerings
ALTER TABLE worksheet ADD COLUMN offeringid INT4 REFERENCES offering (offeringid) NOT NULL; 
COMMIT;