-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathdatabase.php
198 lines (171 loc) · 7.19 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
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
<?php
// The SQL to uninstall this tool
$DATABASE_UNINSTALL = array(
// Nothing yet.
);
// The SQL to create the tables if they don't exist
$DATABASE_INSTALL = array(
array( "{$CFG->dbprefix}iv_video",
"create table {$CFG->dbprefix}iv_video (
video_id INTEGER NOT NULL AUTO_INCREMENT,
link_id INTEGER NOT NULL,
context_id INTEGER NULL,
user_id INTEGER NULL,
video_url VARCHAR(4000),
video_type INTEGER NOT NULL,
video_title varchar(255) NOT NULL,
UNIQUE(link_id, context_id),
PRIMARY KEY(video_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}iv_question",
"create table {$CFG->dbprefix}iv_question (
question_id INTEGER NOT NULL AUTO_INCREMENT,
video_id INTEGER NOT NULL,
q_time INTEGER NOT NULL,
q_type TINYINT NOT NULL DEFAULT 1,
q_text TEXT NULL,
randomize BOOL NOT NULL DEFAULT 0,
correct_fb TEXT NULL,
incorrect_fb TEXT NULL,
CONSTRAINT `{$CFG->dbprefix}iv_question_ibfk_1`
FOREIGN KEY (`video_id`)
REFERENCES `{$CFG->dbprefix}iv_video` (`video_id`)
ON DELETE CASCADE,
PRIMARY KEY(question_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}iv_answer",
"create table {$CFG->dbprefix}iv_answer (
answer_id INTEGER NOT NULL AUTO_INCREMENT,
question_id INTEGER NOT NULL,
answer_order INTEGER NOT NULL,
is_correct BOOL NOT NULL DEFAULT 0,
a_text TEXT NULL,
CONSTRAINT `{$CFG->dbprefix}iv_answer_ibfk_1`
FOREIGN KEY (`question_id`)
REFERENCES `{$CFG->dbprefix}iv_question` (`question_id`)
ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}iv_response",
"create table {$CFG->dbprefix}iv_response (
response_id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
question_id INTEGER NOT NULL,
answer_id INTEGER NOT NULL,
CONSTRAINT `{$CFG->dbprefix}iv_response_ibfk_1`
FOREIGN KEY (`question_id`)
REFERENCES `{$CFG->dbprefix}iv_question` (`question_id`)
ON DELETE CASCADE,
CONSTRAINT `{$CFG->dbprefix}iv_response_ibfk_2`
FOREIGN KEY (`answer_id`)
REFERENCES `{$CFG->dbprefix}iv_answer` (`answer_id`)
ON DELETE CASCADE,
UNIQUE(user_id, question_id, answer_id),
PRIMARY KEY(response_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}iv_shortanswer",
"create table {$CFG->dbprefix}iv_shortanswer (
shortanswer_id INTEGER NOT NULL AUTO_INCREMENT,
user_id INTEGER NOT NULL,
question_id INTEGER NOT NULL,
response TEXT NULL,
CONSTRAINT `{$CFG->dbprefix}iv_shortanswer_ibfk_1`
FOREIGN KEY (`question_id`)
REFERENCES `{$CFG->dbprefix}iv_question` (`question_id`)
ON DELETE CASCADE,
PRIMARY KEY(shortanswer_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}iv_finished",
"create table {$CFG->dbprefix}iv_finished (
finished_id INTEGER NOT NULL AUTO_INCREMENT,
video_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
num_correct INTEGER NOT NULL DEFAULT 0,
started BOOL NOT NULL DEFAULT 0,
finished BOOL NOT NULL DEFAULT 0,
started_at DATETIME NULL,
finished_at DATETIME NULL,
updated_at DATETIME NULL,
CONSTRAINT `{$CFG->dbprefix}iv_finished_ibfk_1`
FOREIGN KEY (`video_id`)
REFERENCES `{$CFG->dbprefix}iv_video` (`video_id`)
ON DELETE CASCADE,
UNIQUE(user_id, video_id),
PRIMARY KEY(finished_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8")
);
$DATABASE_UPGRADE = function($oldversion) {
global $CFG, $PDOX;
// Add question type column
if (!$PDOX->columnExists('q_type', "{$CFG->dbprefix}iv_question")) {
$sql = "ALTER TABLE {$CFG->dbprefix}iv_question ADD q_type TINYINT NOT NULL DEFAULT 1";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add started_at column
if (!$PDOX->columnExists('started_at', "{$CFG->dbprefix}iv_finished")) {
$sql = "ALTER TABLE {$CFG->dbprefix}iv_finished ADD started_at TIMESTAMP NULL";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add finished_at column
if (!$PDOX->columnExists('finished_at', "{$CFG->dbprefix}iv_finished")) {
$sql = "ALTER TABLE {$CFG->dbprefix}iv_finished ADD finished_at TIMESTAMP NULL";
echo("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Add updated_at column
if (!$PDOX->columnExists('updated_at', "{$CFG->dbprefix}iv_finished")) {
$sql = "ALTER TABLE {$CFG->dbprefix}iv_finished ADD updated_at DATETIME NULL";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Migrate updated_at columns to iv_finished if the tool user had the intermediary state
// of having the updated_at on each of the response and short answer tables
if (
$PDOX->columnExists('updated_at', "{$CFG->dbprefix}iv_response") &&
$PDOX->columnExists('updated_at', "{$CFG->dbprefix}iv_shortanswer")
) {
// Migrate the updated_at data
$sql = "UPDATE {$CFG->dbprefix}iv_finished as t1
SET updated_at = (
SELECT DISTINCT a.updated_at
FROM (
SELECT * FROM {$CFG->dbprefix}iv_response
UNION
SELECT * FROM {$CFG->dbprefix}iv_shortanswer
) AS a
LEFT JOIN {$CFG->dbprefix}iv_question q
ON q.question_id = a.question_id
WHERE a.user_id = t1.user_id AND q.video_id = t1.video_id ORDER BY a.updated_at DESC LIMIT 1
)";
$q = $PDOX->queryDie($sql);
// Remove the old columns once the data was moved
$sql = "ALTER TABLE {$CFG->dbprefix}iv_response DROP COLUMN updated_at";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
$sql = "ALTER TABLE {$CFG->dbprefix}iv_shortanswer DROP COLUMN updated_at";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
}
// Change tables to DATETIME instead of TIMESTAMP
$sql = "ALTER TABLE {$CFG->dbprefix}iv_finished MODIFY COLUMN started_at DATETIME";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
$sql = "ALTER TABLE {$CFG->dbprefix}iv_finished MODIFY COLUMN finished_at DATETIME";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
$sql = "ALTER TABLE {$CFG->dbprefix}iv_finished MODIFY COLUMN updated_at DATETIME";
echo ("Upgrading: " . $sql . "<br/>\n");
error_log("Upgrading: " . $sql);
$q = $PDOX->queryDie($sql);
return '202206231241';
};