-
Notifications
You must be signed in to change notification settings - Fork 5
/
database.php
174 lines (152 loc) · 6.24 KB
/
database.php
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
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
<?php
// The SQL to uninstall this tool
$DATABASE_UNINSTALL = array(
array( "{$CFG->dbprefix}ct_grade",
"drop table if exists {$CFG->dbprefix}ct_grade"),
array( "{$CFG->dbprefix}ct_sql_question",
"drop table if exists {$CFG->dbprefix}ct_sql_question"),
array( "{$CFG->dbprefix}ct_code_question",
"drop table if exists {$CFG->dbprefix}ct_code_question"),
array( "{$CFG->dbprefix}ct_answer",
"drop table if exists {$CFG->dbprefix}ct_answer"),
array( "{$CFG->dbprefix}ct_question",
"drop table if exists {$CFG->dbprefix}ct_question"),
array( "{$CFG->dbprefix}ct_main",
"drop table if exists {$CFG->dbprefix}ct_main"),
);
// The SQL to create the tables if they don't exist
$DATABASE_INSTALL = array(
array( "{$CFG->dbprefix}ct_main",
"create table {$CFG->dbprefix}ct_main (
ct_id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
context_id INTEGER NOT NULL,
link_id INTEGER NOT NULL,
title VARCHAR(255) NULL,
type INTEGER NOT NULL DEFAULT 0,
seen_splash BOOL NOT NULL DEFAULT 0,
shuffle BOOL NOT NULL DEFAULT 0,
points FLOAT NOT NULL DEFAULT 100,
modified datetime NULL,
PRIMARY KEY(ct_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}ct_question",
"create table {$CFG->dbprefix}ct_question (
question_id INTEGER NOT NULL AUTO_INCREMENT,
ct_id INTEGER NOT NULL,
question_num INTEGER NULL,
question_txt TEXT NULL,
question_must VARCHAR(255) NULL,
question_musnt VARCHAR(255) NULL,
modified datetime NULL,
CONSTRAINT `{$CFG->dbprefix}ct_ibfk_1`
FOREIGN KEY (`ct_id`)
REFERENCES `{$CFG->dbprefix}ct_main` (`ct_id`)
ON DELETE CASCADE,
PRIMARY KEY(question_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}ct_answer",
"create table {$CFG->dbprefix}ct_answer (
answer_id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
question_id INTEGER NOT NULL,
answer_txt TEXT NULL,
answer_success BOOL NOT NULL DEFAULT 0,
modified datetime NULL,
CONSTRAINT `{$CFG->dbprefix}ct_ibfk_2`
FOREIGN KEY (`question_id`)
REFERENCES `{$CFG->dbprefix}ct_question` (`question_id`)
ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}ct_code_question",
"create table {$CFG->dbprefix}ct_code_question (
question_id INTEGER NOT NULL,
question_language INTEGER NOT NULL DEFAULT '1',
question_input_test TEXT NULL DEFAULT NULL,
question_input_grade TEXT NULL DEFAULT NULL,
question_output_test TEXT NULL DEFAULT NULL,
question_output_grade TEXT NULL DEFAULT NULL,
question_solution TEXT NULL DEFAULT NULL,
PRIMARY KEY (question_id),
CONSTRAINT `{$CFG->dbprefix}ct_ibfk_3`
FOREIGN KEY (`question_id`)
REFERENCES `{$CFG->dbprefix}ct_question` (`question_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8"),
array( "{$CFG->dbprefix}ct_sql_question",
"create table {$CFG->dbprefix}ct_sql_question (
question_id INT(11) NOT NULL,
question_dbms TINYINT NOT NULL DEFAULT 0,
question_type VARCHAR(20) NULL DEFAULT 'SELECT',
question_database VARCHAR(100) NULL DEFAULT NULL,
question_solution TEXT NULL DEFAULT NULL,
question_probe TEXT NULL DEFAULT NULL,
PRIMARY KEY (question_id),
CONSTRAINT `{$CFG->dbprefix}ct_ibfk_4`
FOREIGN KEY (`question_id`)
REFERENCES `{$CFG->dbprefix}ct_question` (`question_id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB DEFAULT CHARACTER SET = utf8"),
array( "{$CFG->dbprefix}ct_grade",
"create table {$CFG->dbprefix}ct_grade (
grade_id INTEGER NOT NULL AUTO_INCREMENT,
ct_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
grade FLOAT NOT NULL DEFAULT 0,
modified datetime NULL,
CONSTRAINT `{$CFG->dbprefix}ct_ibfk_5`
FOREIGN KEY (`ct_id`)
REFERENCES `{$CFG->dbprefix}ct_main` (`ct_id`)
ON DELETE CASCADE,
PRIMARY KEY(grade_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8")
);
$DATABASE_UPGRADE = function($oldversion) {
global $CFG, $PDOX;
// Add splash column
if (!$PDOX->columnExists('seen_splash', "{$CFG->dbprefix}ct_main")) {
$sql = "ALTER TABLE {$CFG->dbprefix}ct_main ADD seen_splash BOOL NOT NULL DEFAULT 0";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Remove splash table
if($PDOX->describe("{$CFG->dbprefix}ct_splash")) {
$sql = "DROP TABLE {$CFG->dbprefix}ct_splash;";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add points column
if (!$PDOX->columnExists('points', "{$CFG->dbprefix}ct_main")) {
$sql = "ALTER TABLE {$CFG->dbprefix}ct_main ADD points FLOAT NOT NULL DEFAULT 100";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add title column
if (!$PDOX->columnExists('title', "{$CFG->dbprefix}ct_main")) {
$sql = "ALTER TABLE {$CFG->dbprefix}ct_main ADD title VARCHAR(255) NULL";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add onfly column in question_sql
if (!$PDOX->columnExists('question_onfly', "{$CFG->dbprefix}ct_sql_question")) {
$sql = "ALTER TABLE {$CFG->dbprefix}ct_sql_question ADD question_onfly LONGTEXT NULL DEFAULT NULL";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add answer_language column to allow student select the language of the answer
if (!$PDOX->columnExists('answer_language', "{$CFG->dbprefix}ct_answer")) {
$sql = "ALTER TABLE {$CFG->dbprefix}ct_answer ADD answer_language INTEGER NULL DEFAULT NULL";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
return '202012201622';
};