forked from tsugitools/tdiscus
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase.php
279 lines (227 loc) · 10.2 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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
<?php
// To allow this to be called directly or from admin/upgrade.php
if ( !isset($PDOX) ) {
require_once "../config.php";
$CURRENT_FILE = __FILE__;
require $CFG->dirroot."/admin/migrate-setup.php";
}
// Dropping tables
$DATABASE_UNINSTALL = array(
"drop table if exists {$CFG->dbprefix}tdiscus_read",
"drop table if exists {$CFG->dbprefix}tdiscus_flag",
"drop table if exists {$CFG->dbprefix}tdiscus_closure",
"drop table if exists {$CFG->dbprefix}tdiscus_comment",
"drop table if exists {$CFG->dbprefix}tdiscus_thread",
"drop table if exists {$CFG->dbprefix}tdiscus_user_thread",
"drop table if exists {$CFG->dbprefix}tdiscus_user_comment",
"drop table if exists {$CFG->dbprefix}tdiscus_user_user",
);
// Creating tables
$DATABASE_INSTALL = array(
array( "{$CFG->dbprefix}tdiscus_thread",
"create table {$CFG->dbprefix}tdiscus_thread (
thread_id INTEGER NOT NULL KEY AUTO_INCREMENT,
link_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
title TEXT NULL,
body TEXT NULL,
cleaned TINYINT(1) NOT NULL DEFAULT 0,
json TEXT NULL,
settings TEXT NULL,
thread_type SMALLINT(2) NOT NULL DEFAULT 0,
views INTEGER NOT NULL DEFAULT 0,
comments INTEGER NOT NULL DEFAULT 0,
staffcreate TINYINT(1) NOT NULL DEFAULT 0,
staffread TINYINT(1) NOT NULL DEFAULT 0,
staffanswer TINYINT(1) NOT NULL DEFAULT 0,
edited TINYINT(1) NOT NULL DEFAULT 0,
hidden TINYINT(1) NOT NULL DEFAULT 0,
hidden_global TINYINT(1) NOT NULL DEFAULT 0,
locked TINYINT(1) NOT NULL DEFAULT 0,
pin TINYINT(1) NOT NULL DEFAULT 0,
rank_value SMALLINT(2) NOT NULL DEFAULT 0,
upvote INTEGER NOT NULL DEFAULT 0,
downvote INTEGER NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL,
CONSTRAINT `{$CFG->dbprefix}tdiscus_thread_ibfk_1`
FOREIGN KEY (`link_id`)
REFERENCES `{$CFG->dbprefix}lti_link` (`link_id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}tdiscus_user_thread",
"create table {$CFG->dbprefix}tdiscus_user_thread (
thread_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
subscribe TINYINT(1) NOT NULL DEFAULT 0,
views INTEGER NOT NULL DEFAULT 0,
comments INTEGER NOT NULL DEFAULT 0,
vote TINYINT(1) NOT NULL DEFAULT 0,
mute TINYINT(1) NOT NULL DEFAULT 0,
favorite TINYINT(1) NOT NULL DEFAULT 0,
report TINYINT(1) NOT NULL DEFAULT 0,
read_at TIMESTAMP NULL,
notify TINYINT(1) NOT NULL DEFAULT 0,
notify_at TIMESTAMP NULL,
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_thread_ibfk_1`
UNIQUE (`thread_id`, `user_id`),
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_thread_ibfk_2`
FOREIGN KEY (`thread_id`)
REFERENCES `{$CFG->dbprefix}tdiscus_thread` (`thread_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_thread_ibfk_3`
FOREIGN KEY (`user_id`)
REFERENCES `{$CFG->dbprefix}lti_user` (`user_id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
// parent_id is *not* a real foreign key - just to keep track in case closure
// table gets corrupted
array( "{$CFG->dbprefix}tdiscus_comment",
"create table {$CFG->dbprefix}tdiscus_comment (
comment_id INTEGER NOT NULL KEY AUTO_INCREMENT,
thread_id INTEGER NOT NULL,
parent_id INTEGER NOT NULL DEFAULT 0,
user_id INTEGER NOT NULL,
depth INTEGER NOT NULL DEFAULT 0,
comment TEXT NULL,
cleaned TINYINT(1) NOT NULL DEFAULT 0,
comment_type SMALLINT(2) NOT NULL DEFAULT 0,
children INTEGER NOT NULL DEFAULT 0,
json TEXT NULL,
settings TEXT NULL,
pin TINYINT(1) NOT NULL DEFAULT 0,
rank_value SMALLINT(2) NOT NULL DEFAULT 0,
upvote INTEGER NOT NULL DEFAULT 0,
downvote INTEGER NOT NULL DEFAULT 0,
edited TINYINT(1) NOT NULL DEFAULT 0,
hidden TINYINT(1) NOT NULL DEFAULT 0,
locked TINYINT(1) NOT NULL DEFAULT 0,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL,
CONSTRAINT `{$CFG->dbprefix}tdiscus_comment_ibfk_1`
FOREIGN KEY (`thread_id`)
REFERENCES `{$CFG->dbprefix}tdiscus_thread` (`thread_id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}tdiscus_user_comment",
"create table {$CFG->dbprefix}tdiscus_user_comment (
comment_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
subscribe TINYINT(1) NOT NULL DEFAULT 0,
vote TINYINT(1) NOT NULL DEFAULT 0,
report TINYINT(1) NOT NULL DEFAULT 0,
favorite TINYINT(1) NOT NULL DEFAULT 0,
read_at TIMESTAMP NULL,
notify TINYINT(1) NOT NULL DEFAULT 0,
notify_at TIMESTAMP NULL,
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_comment_ibfk_1`
UNIQUE (`comment_id`, `user_id`),
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_comment_ibfk_2`
FOREIGN KEY (`comment_id`)
REFERENCES `{$CFG->dbprefix}tdiscus_comment` (`comment_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_comment_ibfk_3`
FOREIGN KEY (`user_id`)
REFERENCES `{$CFG->dbprefix}lti_user` (`user_id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
array( "{$CFG->dbprefix}tdiscus_user_user",
"create table {$CFG->dbprefix}tdiscus_user_user (
user_id INTEGER NOT NULL,
other_user_id INTEGER NOT NULL,
mute TINYINT(1) NOT NULL DEFAULT 0,
report TINYINT(1) NOT NULL DEFAULT 0,
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_user_ibfk_1`
UNIQUE (`user_id`, `other_user_id`),
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_user_ibfk_2`
FOREIGN KEY (`user_id`)
REFERENCES `{$CFG->dbprefix}lti_user` (`user_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `{$CFG->dbprefix}tdiscus_user_user_ibfk_3`
FOREIGN KEY (`other_user_id`)
REFERENCES `{$CFG->dbprefix}lti_user` (`user_id`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
/*
https://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462
https://www.slideshare.net/billkarwin/models-for-hierarchical-data
https://stackoverflow.com/questions/8252323/mysql-closure-table-hierarchical-database-how-to-pull-information-out-in-the-c
*/
// A closure table approach to hierarchy
array( "{$CFG->dbprefix}tdiscus_closure",
"create table {$CFG->dbprefix}tdiscus_closure (
parent_id INTEGER NOT NULL,
child_id INTEGER NOT NULL,
depth INTEGER NOT NULL DEFAULT 0,
CONSTRAINT `{$CFG->dbprefix}tdiscus_closure_ibfk_1`
FOREIGN KEY (`parent_id`)
REFERENCES `{$CFG->dbprefix}tdiscus_comment` (`comment_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `{$CFG->dbprefix}tdiscus_closure_ibfk_2`
FOREIGN KEY (`child_id`)
REFERENCES `{$CFG->dbprefix}tdiscus_comment` (`comment_id`)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(parent_id, child_id)
) ENGINE = InnoDB DEFAULT CHARSET=utf8"),
);
// Database upgrade
$DATABASE_UPGRADE = function($oldversion) {
global $CFG, $PDOX;
$sql= "UPDATE {$CFG->dbprefix}tdiscus_comment SET parent_id = 0 WHERE parent_id IS NULL";
echo("Upgrading: ".$sql."<br/>\n");
error_log("Upgrading: ".$sql);
$q = $PDOX->queryReturnError($sql);
// This is a place to make sure added fields are present
// if you add a field to a table, put it in here and it will be auto-added
$add_some_fields = array(
array('tdiscus_thread', 'hidden_global', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_thread', 'thread_type', 'TINYINT(2) NOT NULL DEFAULT 0'),
array('tdiscus_comment', 'cleaned', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_comment', 'children', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_comment', 'parent_id', 'INTEGER DEFAULT 0'),
array('tdiscus_comment', 'parent_id', 'INTEGER NOT NULL DEFAULT 0'),
array('tdiscus_comment', 'comment_type', 'TINYINT(2) NOT NULL DEFAULT 0'),
array('tdiscus_user_thread', 'notify', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_user_thread', 'notify_at', 'TIMESTAMP NULL'),
array('tdiscus_user_comment', 'notify', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_user_comment', 'notify_at', 'TIMESTAMP NULL'),
array('tdiscus_user_thread', 'subscribe', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_user_comment', 'subscribe', 'TINYINT(1) NOT NULL DEFAULT 0'),
array('tdiscus_closure', 'depth', 'INTEGER NOT NULL DEFAULT 0'),
array('tdiscus_closure', 'children', 'DROP'),
array('tdiscus_closure', 'created_at', 'DROP'),
array('tdiscus_closure', 'updated_at', 'DROP'),
array('tdiscus_comment', 'staff', 'DROP'),
array('tdiscus_comment', 'ctype', 'DROP'),
);
foreach ( $add_some_fields as $add_field ) {
if (count($add_field) != 3 ) {
echo("Badly formatted add_field");
var_dump($add_field);
continue;
}
$table = $CFG->dbprefix . $add_field[0];
$column = $add_field[1];
$type = $add_field[2];
$sql = false;
if ( $PDOX->columnExists($column, $table ) ) {
if ( $type == 'DROP' ) {
$sql= "ALTER TABLE {$CFG->dbprefix}$table DROP COLUMN $column";
} else {
// continue;
$sql= "ALTER TABLE {$CFG->dbprefix}$table MODIFY $column $type";
}
} else {
if ( $type == 'DROP' ) continue;
$sql= "ALTER TABLE {$CFG->dbprefix}$table ADD $column $type";
}
echo("Upgrading: ".$sql."<br/>\n");
error_log("Upgrading: ".$sql);
$q = $PDOX->queryReturnError($sql);
}
return 202012101330;
}; // Don't forget the semicolon on anonymous functions :)
// Do the actual migration if we are not in admin/upgrade.php
if ( isset($CURRENT_FILE) ) {
include $CFG->dirroot."/admin/migrate-run.php";
}