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
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
<?php
/*
Question2Answer by Gideon Greenspan and contributors
http://www.question2answer.org/
Description: Database-level functions for installation and upgrading
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
More about this license: http://www.question2answer.org/license.php
*/
if (!defined('QA_VERSION')) { // don't allow this page to be requested directly from browser
header('Location: ../../');
exit;
}
define('QA_DB_VERSION_CURRENT', 67);
/**
* Return the column type for user ids after verifying it is one of the legal options
*/
function qa_db_user_column_type_verify()
{
$coltype = strtoupper(qa_get_mysql_user_column_type());
switch ($coltype) {
case 'SMALLINT':
case 'MEDIUMINT':
case 'INT':
case 'BIGINT':
case 'SMALLINT UNSIGNED':
case 'MEDIUMINT UNSIGNED':
case 'INT UNSIGNED':
case 'BIGINT UNSIGNED':
// these are all OK
break;
default:
if (!preg_match('/VARCHAR\([0-9]+\)/', $coltype))
qa_fatal_error('Specified user column type is not one of allowed values - please read documentation');
break;
}
return $coltype;
}
/**
* Return an array of table definitions. For each element of the array, the key is the table name (without prefix)
* and the value is an array of column definitions, [column name] => [definition]. The column name is omitted for indexes.
*/
function qa_db_table_definitions()
{
if (qa_to_override(__FUNCTION__)) { $args=func_get_args(); return qa_call_override(__FUNCTION__, $args); }
require_once QA_INCLUDE_DIR . 'db/maxima.php';
require_once QA_INCLUDE_DIR . 'app/users.php';
/*
Important note on character encoding in database and PHP connection to MySQL
[this note is no longer relevant since we *do* explicitly set the connection character set since Q2A 1.5 - see qa-db.php
*/
/*
Other notes on the definitions below
* In MySQL versions prior to 5.0.3, VARCHAR(x) columns will be silently converted to TEXT where x>255
* See box at top of /qa-include/app/recalc.php for a list of redundant (non-normal) information in the database
* Starting in version 1.2, we explicitly name keys and foreign key constraints, instead of allowing MySQL
to name these by default. Our chosen names match the default names that MySQL would have assigned, and
indeed *did* assign for people who installed an earlier version of Q2A. By naming them explicitly, we're
on more solid ground for possible future changes to indexes and foreign keys in the schema.
* There are other foreign key constraints that it would be valid to add, but that would not serve much
purpose in terms of preventing inconsistent data being retrieved, and would just slow down some queries.
* We name some columns here in a not entirely intuitive way. The reason is to match the names of columns in
other tables which are of a similar nature. This will save time and space when combining several SELECT
queries together via a UNION in qa_db_multi_select() - see comments in qa-db.php for more information.
*/
$useridcoltype = qa_db_user_column_type_verify();
$tables = array(
'users' => array(
'userid' => $useridcoltype . ' NOT NULL AUTO_INCREMENT',
'created' => 'DATETIME NOT NULL',
'createip' => 'VARBINARY(16) NOT NULL', // INET6_ATON of IP address when created
'email' => 'VARCHAR(' . QA_DB_MAX_EMAIL_LENGTH . ') NOT NULL',
'handle' => 'VARCHAR(' . QA_DB_MAX_HANDLE_LENGTH . ') NOT NULL', // username
'avatarblobid' => 'BIGINT UNSIGNED', // blobid of stored avatar
'avatarwidth' => 'SMALLINT UNSIGNED', // pixel width of stored avatar
'avatarheight' => 'SMALLINT UNSIGNED', // pixel height of stored avatar
'passsalt' => 'BINARY(16)', // salt used to calculate passcheck - null if no password set for direct login
'passcheck' => 'BINARY(20)', // checksum from password and passsalt - null if no password set for direct login
'passhash' => 'VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL', // password_hash
'level' => 'TINYINT UNSIGNED NOT NULL', // basic, editor, admin, etc...
'loggedin' => 'DATETIME NOT NULL', // time of last login
'loginip' => 'VARBINARY(16) NOT NULL', // INET6_ATON of IP address of last login
'written' => 'DATETIME', // time of last write action done by user
'writeip' => 'VARBINARY(16)', // INET6_ATON of IP address of last write action done by user
'emailcode' => 'CHAR(8) CHARACTER SET ascii NOT NULL DEFAULT \'\'', // for email confirmation or password reset
'sessioncode' => 'CHAR(8) CHARACTER SET ascii NOT NULL DEFAULT \'\'', // for comparing against session cookie in browser
'sessionsource' => 'VARCHAR (16) CHARACTER SET ascii DEFAULT \'\'', // e.g. facebook, openid, etc...
'flags' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', // see constants at top of /qa-include/app/users.php
'wallposts' => 'MEDIUMINT NOT NULL DEFAULT 0', // cached count of wall posts
'PRIMARY KEY (userid)',
'KEY email (email)',
'KEY handle (handle)',
'KEY level (level)',
'KEY created (created, level, flags)',
),
'userlogins' => array(
'userid' => $useridcoltype . ' NOT NULL',
'source' => 'VARCHAR (16) CHARACTER SET ascii NOT NULL', // e.g. facebook, openid, etc...
'identifier' => 'VARBINARY (1024) NOT NULL', // depends on source, e.g. Facebook uid or OpenID url
'identifiermd5' => 'BINARY (16) NOT NULL', // used to reduce size of index on identifier
'KEY source (source, identifiermd5)',
'KEY userid (userid)',
),
'userlevels' => array(
'userid' => $useridcoltype . ' NOT NULL', // the user who has this level
'entitytype' => 'CHAR(1) CHARACTER SET ascii NOT NULL', // see /qa-include/app/updates.php
'entityid' => 'INT UNSIGNED NOT NULL', // relevant postid / userid / tag wordid / categoryid
'level' => 'TINYINT UNSIGNED', // if not NULL, special permission level for that user and that entity
'UNIQUE userid (userid, entitytype, entityid)',
'KEY entitytype (entitytype, entityid)',
),
'userprofile' => array(
'userid' => $useridcoltype . ' NOT NULL',
'title' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ') NOT NULL', // profile field name
'content' => 'VARCHAR(' . QA_DB_MAX_PROFILE_CONTENT_LENGTH . ') NOT NULL', // profile field value
'UNIQUE userid (userid,title)',
),
'userfields' => array(
'fieldid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT',
'title' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ') NOT NULL', // to match title column in userprofile table
'content' => 'VARCHAR(' . QA_DB_MAX_PROFILE_TITLE_LENGTH . ')', // label for display on user profile pages - NULL means use default
'position' => 'SMALLINT UNSIGNED NOT NULL',
'flags' => 'TINYINT UNSIGNED NOT NULL', // QA_FIELD_FLAGS_* at top of /qa-include/app/users.php
'permit' => 'TINYINT UNSIGNED', // minimum user level required to view (uses QA_PERMIT_* constants), null means no restriction
'PRIMARY KEY (fieldid)',
),
'messages' => array(
'messageid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT',
'type' => "ENUM('PUBLIC', 'PRIVATE') NOT NULL DEFAULT 'PRIVATE'",
'fromuserid' => $useridcoltype,
'touserid' => $useridcoltype,
'fromhidden' => 'TINYINT(1) UNSIGNED NOT NULL DEFAULT 0',
'tohidden' => 'TINYINT(1) UNSIGNED NOT NULL DEFAULT 0',
'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL',
'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL',
'created' => 'DATETIME NOT NULL',
'PRIMARY KEY (messageid)',
'KEY type (type, fromuserid, touserid, created)',
'KEY touserid (touserid, type, created)',
'KEY fromhidden (fromhidden)',
'KEY tohidden (tohidden)',
),
'userfavorites' => array(
'userid' => $useridcoltype . ' NOT NULL', // the user who favorited the entity
'entitytype' => 'CHAR(1) CHARACTER SET ascii NOT NULL', // see /qa-include/app/updates.php
'entityid' => 'INT UNSIGNED NOT NULL', // favorited postid / userid / tag wordid / categoryid
'nouserevents' => 'TINYINT UNSIGNED NOT NULL', // do we skip writing events to the user stream?
'PRIMARY KEY (userid, entitytype, entityid)',
'KEY userid (userid, nouserevents)',
'KEY entitytype (entitytype, entityid, nouserevents)',
),
'usernotices' => array(
'noticeid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT',
'userid' => $useridcoltype . ' NOT NULL', // the user to whom the notice is directed
'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL',
'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL',
'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ')', // any additional information for a plugin to access
'created' => 'DATETIME NOT NULL',
'PRIMARY KEY (noticeid)',
'KEY userid (userid, created)',
),
'userevents' => array(
'userid' => $useridcoltype . ' NOT NULL', // the user to be informed about this event in their updates
'entitytype' => 'CHAR(1) CHARACTER SET ascii NOT NULL', // see /qa-include/app/updates.php
'entityid' => 'INT UNSIGNED NOT NULL', // favorited source of event - see userfavorites table - 0 means not from a favorite
'questionid' => 'INT UNSIGNED NOT NULL', // the affected question
'lastpostid' => 'INT UNSIGNED NOT NULL', // what part of question was affected
'updatetype' => 'CHAR(1) CHARACTER SET ascii', // what was done to this part - see /qa-include/app/updates.php
'lastuserid' => $useridcoltype, // which user (if any) did this action
'updated' => 'DATETIME NOT NULL', // when the event happened
'KEY userid (userid, updated)', // for truncation
'KEY questionid (questionid, userid)', // to limit number of events per question per stream
),
'sharedevents' => array(
'entitytype' => 'CHAR(1) CHARACTER SET ascii NOT NULL', // see /qa-include/app/updates.php
'entityid' => 'INT UNSIGNED NOT NULL', // see userfavorites table
'questionid' => 'INT UNSIGNED NOT NULL', // see userevents table
'lastpostid' => 'INT UNSIGNED NOT NULL', // see userevents table
'updatetype' => 'CHAR(1) CHARACTER SET ascii', // see userevents table
'lastuserid' => $useridcoltype, // see userevents table
'updated' => 'DATETIME NOT NULL', // see userevents table
'KEY entitytype (entitytype, entityid, updated)', // for truncation
'KEY questionid (questionid, entitytype, entityid)', // to limit number of events per question per stream
),
'cookies' => array(
'cookieid' => 'BIGINT UNSIGNED NOT NULL',
'created' => 'DATETIME NOT NULL',
'createip' => 'VARBINARY(16) NOT NULL', // INET6_ATON of IP address when cookie created
'written' => 'DATETIME', // time of last write action done by anon user with cookie
'writeip' => 'VARBINARY(16)', // INET6_ATON of IP address of last write action done by anon user with cookie
'PRIMARY KEY (cookieid)',
),
'categories' => array(
'categoryid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT',
'parentid' => 'INT UNSIGNED',
'title' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TITLE_LENGTH . ') NOT NULL', // category name
'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ') NOT NULL', // slug (url fragment) used to identify category
'content' => 'VARCHAR(' . QA_DB_MAX_CAT_CONTENT_LENGTH . ') NOT NULL DEFAULT \'\'', // description of category
'qcount' => 'INT UNSIGNED NOT NULL DEFAULT 0',
'position' => 'SMALLINT UNSIGNED NOT NULL',
// full slug path for category, with forward slash separators, in reverse order to make index from effective
'backpath' => 'VARCHAR(' . (QA_CATEGORY_DEPTH * (QA_DB_MAX_CAT_PAGE_TAGS_LENGTH + 1)) . ') NOT NULL DEFAULT \'\'',
'PRIMARY KEY (categoryid)',
'UNIQUE parentid (parentid, tags)',
'UNIQUE parentid_2 (parentid, position)',
'KEY backpath (backpath(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . '))',
),
'pages' => array(
'pageid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT',
'title' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TITLE_LENGTH . ') NOT NULL', // title for navigation
'nav' => 'CHAR(1) CHARACTER SET ascii NOT NULL', // which navigation does it go in (M=main, F=footer, B=before main, O=opposite main, other=none)
'position' => 'SMALLINT UNSIGNED NOT NULL', // global ordering, which allows links to be ordered within each nav area
'flags' => 'TINYINT UNSIGNED NOT NULL', // local or external, open in new window?
'permit' => 'TINYINT UNSIGNED', // is there a minimum user level required for it (uses QA_PERMIT_* constants), null means no restriction
'tags' => 'VARCHAR(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . ') NOT NULL', // slug (url fragment) for page, or url for external pages
'heading' => 'VARCHAR(' . QA_DB_MAX_TITLE_LENGTH . ')', // for display within <h1> tags
'content' => 'MEDIUMTEXT', // remainder of page HTML
'PRIMARY KEY (pageid)',
'KEY tags (tags)',
'UNIQUE position (position)',
),
'widgets' => array(
'widgetid' => 'SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT',
// full region: FT=very top of page, FH=below nav area, FL=above footer, FB = very bottom of page
// side region: ST=top of side, SH=below sidebar, SL=below categories, SB=very bottom of side
// main region: MT=top of main, MH=below page title, ML=above links, MB=very bottom of main region
'place' => 'CHAR(2) CHARACTER SET ascii NOT NULL',
'position' => 'SMALLINT UNSIGNED NOT NULL', // global ordering, which allows widgets to be ordered within each place
'tags' => 'VARCHAR(' . QA_DB_MAX_WIDGET_TAGS_LENGTH . ') CHARACTER SET ascii NOT NULL', // comma-separated list of templates to display on
'title' => 'VARCHAR(' . QA_DB_MAX_WIDGET_TITLE_LENGTH . ') NOT NULL', // name of widget module that should be displayed
'PRIMARY KEY (widgetid)',
'UNIQUE position (position)',
),
'posts' => array(
'postid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT',
'type' => "ENUM('Q', 'A', 'C', 'Q_HIDDEN', 'A_HIDDEN', 'C_HIDDEN', 'Q_QUEUED', 'A_QUEUED', 'C_QUEUED', 'NOTE') NOT NULL",
'parentid' => 'INT UNSIGNED', // for follow on questions, all answers and comments
'categoryid' => 'INT UNSIGNED', // this is the canonical final category id
'catidpath1' => 'INT UNSIGNED', // the catidpath* columns are calculated from categoryid, for the full hierarchy of that category
'catidpath2' => 'INT UNSIGNED', // note that QA_CATEGORY_DEPTH=4
'catidpath3' => 'INT UNSIGNED',
'acount' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', // number of answers (for questions)
'amaxvote' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0', // highest netvotes of child answers (for questions)
'selchildid' => 'INT UNSIGNED', // selected answer (for questions)
// if closed due to being a duplicate, this is the postid of that other question
// if closed for another reason, that reason should be added as a comment on the question, and this field is the comment's id
'closedbyid' => 'INT UNSIGNED', // not null means question is closed
'userid' => $useridcoltype, // which user wrote it
'cookieid' => 'BIGINT UNSIGNED', // which cookie wrote it, if an anonymous post
'createip' => 'VARBINARY(16)', // INET6_ATON of IP address used to create the post
'lastuserid' => $useridcoltype, // which user last modified it
'lastip' => 'VARBINARY(16)', // INET6_ATON of IP address which last modified the post
'upvotes' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0',
'downvotes' => 'SMALLINT UNSIGNED NOT NULL DEFAULT 0',
'netvotes' => 'SMALLINT NOT NULL DEFAULT 0',
'lastviewip' => 'VARBINARY(16)', // INET6_ATON of IP address which last viewed the post
'views' => 'INT UNSIGNED NOT NULL DEFAULT 0',
'hotness' => 'FLOAT',
'flagcount' => 'TINYINT UNSIGNED NOT NULL DEFAULT 0',
'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL DEFAULT \'\'', // format of content, e.g. 'html'
'created' => 'DATETIME NOT NULL',
'updated' => 'DATETIME', // time of last update
'updatetype' => 'CHAR(1) CHARACTER SET ascii', // see /qa-include/app/updates.php
'title' => 'VARCHAR(' . QA_DB_MAX_TITLE_LENGTH . ')',
'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ')',
'tags' => 'VARCHAR(' . QA_DB_MAX_TAGS_LENGTH . ')', // string of tags separated by commas
'name' => 'VARCHAR(' . QA_DB_MAX_NAME_LENGTH . ')', // name of author if post anonymonus
'notify' => 'VARCHAR(' . QA_DB_MAX_EMAIL_LENGTH . ')', // email address, or @ to get from user, or NULL for none
'PRIMARY KEY (postid)',
'KEY type (type, created)', // for getting recent questions, answers, comments
'KEY type_2 (type, acount, created)', // for getting unanswered questions
'KEY type_4 (type, netvotes, created)', // for getting posts with the most votes
'KEY type_5 (type, views, created)', // for getting questions with the most views
'KEY type_6 (type, hotness)', // for getting 'hot' questions
'KEY type_7 (type, amaxvote, created)', // for getting questions with no upvoted answers
'KEY parentid (parentid, type)', // for getting a question's answers, any post's comments and follow-on questions
'KEY userid (userid, type, created)', // for recent questions, answers or comments by a user
'KEY selchildid (selchildid, type, created)', // for counting how many of a user's answers have been selected, unselected qs
'KEY closedbyid (closedbyid)', // for the foreign key constraint
'KEY catidpath1 (catidpath1, type, created)', // for getting question, answers or comments in a specific level category
'KEY catidpath2 (catidpath2, type, created)', // note that QA_CATEGORY_DEPTH=4
'KEY catidpath3 (catidpath3, type, created)',
'KEY categoryid (categoryid, type, created)', // this can also be used for searching the equivalent of catidpath4
'KEY createip (createip, created)', // for getting posts created by a specific IP address
'KEY updated (updated, type)', // for getting recent edits across all categories
'KEY flagcount (flagcount, created, type)', // for getting posts with the most flags
'KEY catidpath1_2 (catidpath1, updated, type)', // for getting recent edits in a specific level category
'KEY catidpath2_2 (catidpath2, updated, type)', // note that QA_CATEGORY_DEPTH=4
'KEY catidpath3_2 (catidpath3, updated, type)',
'KEY categoryid_2 (categoryid, updated, type)',
'KEY lastuserid (lastuserid, updated, type)', // for getting posts edited by a specific user
'KEY lastip (lastip, updated, type)', // for getting posts edited by a specific IP address
'CONSTRAINT ^posts_ibfk_2 FOREIGN KEY (parentid) REFERENCES ^posts(postid)', // ^posts_ibfk_1 is set later on userid
'CONSTRAINT ^posts_ibfk_3 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE SET NULL',
'CONSTRAINT ^posts_ibfk_4 FOREIGN KEY (closedbyid) REFERENCES ^posts(postid)',
),
'blobs' => array(
'blobid' => 'BIGINT UNSIGNED NOT NULL',
'format' => 'VARCHAR(' . QA_DB_MAX_FORMAT_LENGTH . ') CHARACTER SET ascii NOT NULL', // format e.g. 'jpeg', 'gif', 'png'
'content' => 'MEDIUMBLOB', // null means it's stored on disk in QA_BLOBS_DIRECTORY
'filename' => 'VARCHAR(' . QA_DB_MAX_BLOB_FILE_NAME_LENGTH . ')', // name of source file (if appropriate)
'userid' => $useridcoltype, // which user created it
'cookieid' => 'BIGINT UNSIGNED', // which cookie created it
'createip' => 'VARBINARY(16)', // INET6_ATON of IP address that created it
'created' => 'DATETIME', // when it was created
'PRIMARY KEY (blobid)',
),
'words' => array(
'wordid' => 'INT UNSIGNED NOT NULL AUTO_INCREMENT',
'word' => 'VARCHAR(' . QA_DB_MAX_WORD_LENGTH . ') NOT NULL',
'titlecount' => 'INT UNSIGNED NOT NULL DEFAULT 0', // only counts one per post
'contentcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', // only counts one per post
'tagwordcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', // for words in tags - only counts one per post
'tagcount' => 'INT UNSIGNED NOT NULL DEFAULT 0', // for tags as a whole - only counts one per post (though no duplicate tags anyway)
'PRIMARY KEY (wordid)',
'KEY word (word)',
'KEY tagcount (tagcount)', // for sorting by most popular tags
),
'titlewords' => array(
'postid' => 'INT UNSIGNED NOT NULL',
'wordid' => 'INT UNSIGNED NOT NULL',
'KEY postid (postid)',
'KEY wordid (wordid)',
'CONSTRAINT ^titlewords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
'CONSTRAINT ^titlewords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)',
),
'contentwords' => array(
'postid' => 'INT UNSIGNED NOT NULL',
'wordid' => 'INT UNSIGNED NOT NULL',
'count' => 'TINYINT UNSIGNED NOT NULL', // how many times word appears in the post - anything over 255 can be ignored
'type' => "ENUM('Q', 'A', 'C', 'NOTE') NOT NULL", // the post's type (copied here for quick searching)
'questionid' => 'INT UNSIGNED NOT NULL', // the id of the post's antecedent parent (here for quick searching)
'KEY postid (postid)',
'KEY wordid (wordid)',
'CONSTRAINT ^contentwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
'CONSTRAINT ^contentwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)',
),
'tagwords' => array(
'postid' => 'INT UNSIGNED NOT NULL',
'wordid' => 'INT UNSIGNED NOT NULL',
'KEY postid (postid)',
'KEY wordid (wordid)',
'CONSTRAINT ^tagwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
'CONSTRAINT ^tagwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)',
),
'posttags' => array(
'postid' => 'INT UNSIGNED NOT NULL',
'wordid' => 'INT UNSIGNED NOT NULL',
'postcreated' => 'DATETIME NOT NULL', // created time of post (copied here for tag page's list of recent questions)
'KEY postid (postid)',
'KEY wordid (wordid, postcreated)',
'CONSTRAINT ^posttags_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
'CONSTRAINT ^posttags_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)',
),
'uservotes' => array(
'postid' => 'INT UNSIGNED NOT NULL',
'userid' => $useridcoltype . ' NOT NULL',
'vote' => 'TINYINT NOT NULL', // -1, 0 or 1
'flag' => 'TINYINT NOT NULL', // 0 or 1
'votecreated' => 'DATETIME', // time of first vote
'voteupdated' => 'DATETIME', // time of last vote change
'UNIQUE userid (userid, postid)',
'KEY postid (postid)',
'KEY voted (votecreated, voteupdated)',
'CONSTRAINT ^uservotes_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
),
// many userpoints columns could be unsigned but MySQL appears to mess up points calculations that go negative as a result
'userpoints' => array(
'userid' => $useridcoltype . ' NOT NULL',
'points' => 'INT NOT NULL DEFAULT 0', // user's points as displayed, after final multiple
'qposts' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of questions by user (excluding hidden/queued)
'aposts' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of answers by user (excluding hidden/queued)
'cposts' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of comments by user (excluding hidden/queued)
'aselects' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of questions by user where they've selected an answer
'aselecteds' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of answers by user that have been selected as the best
'qupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of questions the user has voted up
'qdownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of questions the user has voted down
'aupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of answers the user has voted up
'adownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of answers the user has voted down
'cupvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of comments the user has voted up
'cdownvotes' => 'MEDIUMINT NOT NULL DEFAULT 0', // number of comments the user has voted down
'qvoteds' => 'INT NOT NULL DEFAULT 0', // points from votes on this user's questions (applying per-question limits), before final multiple
'avoteds' => 'INT NOT NULL DEFAULT 0', // points from votes on this user's answers (applying per-answer limits), before final multiple
'cvoteds' => 'INT NOT NULL DEFAULT 0', // points from votes on this user's comments (applying per-comment limits), before final multiple
'upvoteds' => 'INT NOT NULL DEFAULT 0', // number of up votes received on this user's questions or answers
'downvoteds' => 'INT NOT NULL DEFAULT 0', // number of down votes received on this user's questions or answers
'bonus' => 'INT NOT NULL DEFAULT 0', // bonus assigned by administrator to a user
'PRIMARY KEY (userid)',
'KEY points (points)',
),
'userlimits' => array(
'userid' => $useridcoltype . ' NOT NULL',
'action' => 'CHAR(1) CHARACTER SET ascii NOT NULL', // see constants at top of /qa-include/app/limits.php
'period' => 'INT UNSIGNED NOT NULL', // integer representing hour of last action
'count' => 'SMALLINT UNSIGNED NOT NULL', // how many of this action has been performed within that hour
'UNIQUE userid (userid, action)',
),
// most columns in iplimits have the same meaning as those in userlimits
'iplimits' => array(
'ip' => 'VARBINARY(16) NOT NULL', // INET6_ATON of IP address
'action' => 'CHAR(1) CHARACTER SET ascii NOT NULL',
'period' => 'INT UNSIGNED NOT NULL',
'count' => 'SMALLINT UNSIGNED NOT NULL',
'UNIQUE ip (ip, action)',
),
'options' => array(
'title' => 'VARCHAR(' . QA_DB_MAX_OPTION_TITLE_LENGTH . ') NOT NULL', // name of option
'content' => 'VARCHAR(' . QA_DB_MAX_CONTENT_LENGTH . ') NOT NULL', // value of option
'PRIMARY KEY (title)',
),
'cache' => array(
'type' => 'CHAR(8) CHARACTER SET ascii NOT NULL', // e.g. 'avXXX' for avatar sized to XXX pixels square
'cacheid' => 'BIGINT UNSIGNED DEFAULT 0', // optional further identifier, e.g. blobid on which cache entry is based
'content' => 'MEDIUMBLOB NOT NULL',
'created' => 'DATETIME NOT NULL',
'lastread' => 'DATETIME NOT NULL',
'PRIMARY KEY (type, cacheid)',
'KEY (lastread)',
),
'usermetas' => array(
'userid' => $useridcoltype . ' NOT NULL',
'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL',
'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL',
'PRIMARY KEY (userid, title)',
),
'postmetas' => array(
'postid' => 'INT UNSIGNED NOT NULL',
'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL',
'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL',
'PRIMARY KEY (postid, title)',
'CONSTRAINT ^postmetas_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
),
'categorymetas' => array(
'categoryid' => 'INT UNSIGNED NOT NULL',
'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL',
'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL',
'PRIMARY KEY (categoryid, title)',
'CONSTRAINT ^categorymetas_ibfk_1 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE CASCADE',
),
'tagmetas' => array(
'tag' => 'VARCHAR(' . QA_DB_MAX_WORD_LENGTH . ') NOT NULL',
'title' => 'VARCHAR(' . QA_DB_MAX_META_TITLE_LENGTH . ') NOT NULL',
'content' => 'VARCHAR(' . QA_DB_MAX_META_CONTENT_LENGTH . ') NOT NULL',
'PRIMARY KEY (tag, title)',
),
);
if (QA_FINAL_EXTERNAL_USERS) {
unset($tables['users']);
unset($tables['userlogins']);
unset($tables['userprofile']);
unset($tables['userfields']);
unset($tables['messages']);
} else {
$userforeignkey = 'FOREIGN KEY (userid) REFERENCES ^users(userid)';
$tables['userlogins'][] = 'CONSTRAINT ^userlogins_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userprofile'][] = 'CONSTRAINT ^userprofile_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['posts'][] = 'CONSTRAINT ^posts_ibfk_1 ' . $userforeignkey . ' ON DELETE SET NULL';
$tables['uservotes'][] = 'CONSTRAINT ^uservotes_ibfk_2 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userlimits'][] = 'CONSTRAINT ^userlimits_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userfavorites'][] = 'CONSTRAINT ^userfavorites_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['usernotices'][] = 'CONSTRAINT ^usernotices_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userevents'][] = 'CONSTRAINT ^userevents_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['userlevels'][] = 'CONSTRAINT ^userlevels_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['usermetas'][] = 'CONSTRAINT ^usermetas_ibfk_1 ' . $userforeignkey . ' ON DELETE CASCADE';
$tables['messages'][] = 'CONSTRAINT ^messages_ibfk_1 FOREIGN KEY (fromuserid) REFERENCES ^users(userid) ON DELETE SET NULL';
$tables['messages'][] = 'CONSTRAINT ^messages_ibfk_2 FOREIGN KEY (touserid) REFERENCES ^users(userid) ON DELETE SET NULL';
}
return $tables;
}
/**
* Return array with all values from $array as keys
* @param $array
* @return array
*/
function qa_array_to_keys($array)
{
return empty($array) ? array() : array_combine($array, array_fill(0, count($array), true));
}
/**
* Return a list of tables missing from the database, [table name] => [column/index definitions]
* @param $definitions
* @return array
*/
function qa_db_missing_tables($definitions)
{
$keydbtables = qa_array_to_keys(qa_db_list_tables(true));
$missing = array();
foreach ($definitions as $rawname => $definition)
if (!isset($keydbtables[qa_db_add_table_prefix($rawname)]))
$missing[$rawname] = $definition;
return $missing;
}
/**
* Return a list of columns missing from $table in the database, given the full definition set in $definition
* @param $table
* @param $definition
* @return array
*/
function qa_db_missing_columns($table, $definition)
{
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^' . $table)));
$missing = array();
foreach ($definition as $colname => $coldefn)
if (!is_int($colname) && !isset($keycolumns[$colname]))
$missing[$colname] = $coldefn;
return $missing;
}
/**
* Return the current version of the Q2A database, to determine need for DB upgrades
*/
function qa_db_get_db_version()
{
$definitions = qa_db_table_definitions();
if (count(qa_db_missing_columns('options', $definitions['options'])) == 0) {
$version = (int)qa_db_read_one_value(qa_db_query_sub("SELECT content FROM ^options WHERE title='db_version'"), true);
if ($version > 0)
return $version;
}
return null;
}
/**
* Set the current version in the database
* @param $version
*/
function qa_db_set_db_version($version)
{
require_once QA_INCLUDE_DIR . 'db/options.php';
qa_db_set_option('db_version', $version);
}
/**
* Return a string describing what is wrong with the database, or false if everything is just fine
*/
function qa_db_check_tables()
{
qa_db_query_raw('UNLOCK TABLES'); // we could be inside a lock tables block
$version = qa_db_read_one_value(qa_db_query_raw('SELECT VERSION()'));
if (((float)$version) < 4.1)
qa_fatal_error('MySQL version 4.1 or later is required - you appear to be running MySQL ' . $version);
$definitions = qa_db_table_definitions();
$missing = qa_db_missing_tables($definitions);
if (count($missing) == count($definitions))
return 'none';
else {
if (!isset($missing['options'])) {
$version = qa_db_get_db_version();
if (isset($version) && ($version < QA_DB_VERSION_CURRENT))
return 'old-version';
}
if (count($missing)) {
if (defined('QA_MYSQL_USERS_PREFIX')) { // special case if two installations sharing users
$datacount = 0;
$datamissing = 0;
foreach ($definitions as $rawname => $definition) {
if (qa_db_add_table_prefix($rawname) == (QA_MYSQL_TABLE_PREFIX . $rawname)) {
$datacount++;
if (isset($missing[$rawname]))
$datamissing++;
}
}
if ($datacount == $datamissing && $datamissing == count($missing))
return 'non-users-missing';
}
return 'table-missing';
} else
foreach ($definitions as $table => $definition)
if (count(qa_db_missing_columns($table, $definition)))
return 'column-missing';
}
return false;
}
/**
* Install any missing database tables and/or columns and automatically set version as latest.
* This is not suitable for use if the database needs upgrading.
*/
function qa_db_install_tables()
{
$definitions = qa_db_table_definitions();
$missingtables = qa_db_missing_tables($definitions);
foreach ($missingtables as $rawname => $definition) {
qa_db_query_sub(qa_db_create_table_sql($rawname, $definition));
if ($rawname == 'userfields')
qa_db_query_sub(qa_db_default_userfields_sql());
}
foreach ($definitions as $table => $definition) {
$missingcolumns = qa_db_missing_columns($table, $definition);
foreach ($missingcolumns as $colname => $coldefn)
qa_db_query_sub('ALTER TABLE ^' . $table . ' ADD COLUMN ' . $colname . ' ' . $coldefn);
}
qa_db_set_db_version(QA_DB_VERSION_CURRENT);
}
/**
* Return the SQL command to create a table with $rawname and $definition obtained from qa_db_table_definitions()
* @param $rawname
* @param $definition
* @return string
*/
function qa_db_create_table_sql($rawname, $definition)
{
$querycols = '';
foreach ($definition as $colname => $coldef)
if (isset($coldef))
$querycols .= (strlen($querycols) ? ', ' : '') . (is_int($colname) ? $coldef : ($colname . ' ' . $coldef));
return 'CREATE TABLE ^' . $rawname . ' (' . $querycols . ') ENGINE=InnoDB CHARSET=utf8';
}
/**
* Return the SQL to create the default entries in the userfields table (before 1.3 these were hard-coded in PHP)
*/
function qa_db_default_userfields_sql()
{
require_once QA_INCLUDE_DIR . 'app/options.php';
$profileFields = array(
array(
'title' => 'name',
'position' => 1,
'flags' => 0,
'permit' => QA_PERMIT_ALL,
),
array(
'title' => 'location',
'position' => 2,
'flags' => 0,
'permit' => QA_PERMIT_ALL,
),
array(
'title' => 'website',
'position' => 3,
'flags' => QA_FIELD_FLAGS_LINK_URL,
'permit' => QA_PERMIT_ALL,
),
array(
'title' => 'about',
'position' => 4,
'flags' => QA_FIELD_FLAGS_MULTI_LINE,
'permit' => QA_PERMIT_ALL,
),
);
$sql = 'INSERT INTO ^userfields (title, position, flags, permit) VALUES'; // content column will be NULL, meaning use default from lang files
foreach ($profileFields as $field) {
$sql .= sprintf('("%s", %d, %d, %d), ', qa_db_escape_string($field['title']), $field['position'], $field['flags'], $field['permit']);
}
$sql = substr($sql, 0, -2);
return $sql;
}
/**
* Upgrade the database schema to the latest version, outputting progress to the browser
*/
function qa_db_upgrade_tables()
{
require_once QA_INCLUDE_DIR . 'app/recalc.php';
$definitions = qa_db_table_definitions();
$keyrecalc = array();
// Write-lock all Q2A tables before we start so no one can read or write anything
$keydbtables = qa_array_to_keys(qa_db_list_tables(true));
foreach ($definitions as $rawname => $definition)
if (isset($keydbtables[qa_db_add_table_prefix($rawname)]))
$locks[] = '^' . $rawname . ' WRITE';
$locktablesquery = 'LOCK TABLES ' . implode(', ', $locks);
qa_db_upgrade_query($locktablesquery);
// Upgrade it step-by-step until it's up to date (do LOCK TABLES after ALTER TABLE because the lock can sometimes be lost)
// message (used in sprintf) for skipping shared user tables
$skipMessage = 'Skipping upgrading %s table since it was already upgraded by another Q2A site sharing it.';
while (1) {
$version = qa_db_get_db_version();
if ($version >= QA_DB_VERSION_CURRENT)
break;
$newversion = $version + 1;
qa_db_upgrade_progress(QA_DB_VERSION_CURRENT - $version . ' upgrade step/s remaining...');
switch ($newversion) {
// Up to here: Version 1.0 beta 1
case 2:
qa_db_upgrade_query('ALTER TABLE ^posts DROP COLUMN votes, ADD COLUMN upvotes ' . $definitions['posts']['upvotes'] .
' AFTER cookieid, ADD COLUMN downvotes ' . $definitions['posts']['downvotes'] . ' AFTER upvotes');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
break;
case 3:
qa_db_upgrade_query('ALTER TABLE ^userpoints ADD COLUMN upvoteds ' . $definitions['userpoints']['upvoteds'] .
' AFTER avoteds, ADD COLUMN downvoteds ' . $definitions['userpoints']['downvoteds'] . ' AFTER upvoteds');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecalcpoints'] = true;
break;
case 4:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN lastuserid ' . $definitions['posts']['lastuserid'] . ' AFTER cookieid, CHANGE COLUMN updated updated ' . $definitions['posts']['updated']);
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('UPDATE ^posts SET updated=NULL WHERE updated=0 OR updated=created');
break;
case 5:
qa_db_upgrade_query('ALTER TABLE ^contentwords ADD COLUMN type ' . $definitions['contentwords']['type'] . ' AFTER count, ADD COLUMN questionid ' . $definitions['contentwords']['questionid'] . ' AFTER type');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['doreindexcontent'] = true;
break;
// Up to here: Version 1.0 beta 2
case 6:
qa_db_upgrade_query('ALTER TABLE ^userpoints ADD COLUMN cposts ' . $definitions['userpoints']['cposts'] . ' AFTER aposts');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecalcpoints'] = true;
break;
case 7:
if (!QA_FINAL_EXTERNAL_USERS) {
qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN sessioncode ' . $definitions['users']['sessioncode'] . ' AFTER writeip');
qa_db_upgrade_query($locktablesquery);
}
break;
case 8:
qa_db_upgrade_query('ALTER TABLE ^posts ADD KEY (type, acount, created)');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true; // for unanswered question count
break;
// Up to here: Version 1.0 beta 3, 1.0, 1.0.1 beta, 1.0.1
case 9:
if (!QA_FINAL_EXTERNAL_USERS) {
qa_db_upgrade_query('ALTER TABLE ^users CHANGE COLUMN resetcode emailcode ' . $definitions['users']['emailcode'] . ', ADD COLUMN flags ' . $definitions['users']['flags'] . ' AFTER sessioncode');
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('UPDATE ^users SET flags=1');
}
break;
case 10:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('categories', array(
'categoryid' => $definitions['categories']['categoryid'],
'title' => $definitions['categories']['title'],
'tags' => $definitions['categories']['tags'],
'qcount' => $definitions['categories']['qcount'],
'position' => $definitions['categories']['position'],
'PRIMARY KEY (categoryid)',
'UNIQUE tags (tags)',
'UNIQUE position (position)',
))); // hard-code list of columns and indexes to ensure we ignore any added at a later stage
$locktablesquery .= ', ^categories WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 11:
qa_db_upgrade_query('ALTER TABLE ^posts ADD CONSTRAINT ^posts_ibfk_2 FOREIGN KEY (parentid) REFERENCES ^posts(postid), ADD COLUMN categoryid ' . $definitions['posts']['categoryid'] . ' AFTER parentid, ADD KEY categoryid (categoryid, type, created), ADD CONSTRAINT ^posts_ibfk_3 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE SET NULL');
// foreign key on parentid important now that deletion is possible
qa_db_upgrade_query($locktablesquery);
break;
case 12:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('pages', array(
'pageid' => $definitions['pages']['pageid'],
'title' => $definitions['pages']['title'],
'nav' => $definitions['pages']['nav'],
'position' => $definitions['pages']['position'],
'flags' => $definitions['pages']['flags'],
'tags' => $definitions['pages']['tags'],
'heading' => $definitions['pages']['heading'],
'content' => $definitions['pages']['content'],
'PRIMARY KEY (pageid)',
'UNIQUE tags (tags)',
'UNIQUE position (position)',
))); // hard-code list of columns and indexes to ensure we ignore any added at a later stage
$locktablesquery .= ', ^pages WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 13:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN createip ' . $definitions['posts']['createip'] . ' AFTER cookieid, ADD KEY createip (createip, created)');
qa_db_upgrade_query($locktablesquery);
break;
case 14:
qa_db_upgrade_query('ALTER TABLE ^userpoints DROP COLUMN qvotes, DROP COLUMN avotes, ADD COLUMN qupvotes ' . $definitions['userpoints']['qupvotes'] . ' AFTER aselecteds, ADD COLUMN qdownvotes ' . $definitions['userpoints']['qdownvotes'] . ' AFTER qupvotes, ADD COLUMN aupvotes ' . $definitions['userpoints']['aupvotes'] . ' AFTER qdownvotes, ADD COLUMN adownvotes ' . $definitions['userpoints']['adownvotes'] . ' AFTER aupvotes');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecalcpoints'] = true;
break;
// Up to here: Version 1.2 beta 1
case 15:
if (!QA_FINAL_EXTERNAL_USERS)
qa_db_upgrade_table_columns($definitions, 'users', array('emailcode', 'sessioncode', 'flags'));
qa_db_upgrade_table_columns($definitions, 'posts', array('acount', 'upvotes', 'downvotes', 'format'));
qa_db_upgrade_table_columns($definitions, 'categories', array('qcount'));
qa_db_upgrade_table_columns($definitions, 'words', array('titlecount', 'contentcount', 'tagcount'));
qa_db_upgrade_table_columns($definitions, 'userpoints', array('points', 'qposts', 'aposts', 'cposts',
'aselects', 'aselecteds', 'qupvotes', 'qdownvotes', 'aupvotes', 'adownvotes', 'qvoteds', 'avoteds', 'upvoteds', 'downvoteds'));
qa_db_upgrade_query($locktablesquery);
break;
// Up to here: Version 1.2 (release)
case 16:
qa_db_upgrade_table_columns($definitions, 'posts', array('format'));
qa_db_upgrade_query($locktablesquery);
$keyrecalc['doreindexcontent'] = true; // because of new treatment of apostrophes in words
break;
case 17:
qa_db_upgrade_query('ALTER TABLE ^posts ADD KEY updated (updated, type), ADD KEY categoryid_2 (categoryid, updated, type)');
qa_db_upgrade_query($locktablesquery);
break;
case 18:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN lastip ' . $definitions['posts']['lastip'] . ' AFTER lastuserid, ADD KEY lastip (lastip, updated, type)');
qa_db_upgrade_query($locktablesquery);
break;
case 19:
if (!QA_FINAL_EXTERNAL_USERS)
qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN avatarblobid ' . $definitions['users']['avatarblobid'] . ' AFTER handle, ADD COLUMN avatarwidth ' . $definitions['users']['avatarwidth'] . ' AFTER avatarblobid, ADD COLUMN avatarheight ' . $definitions['users']['avatarheight'] . ' AFTER avatarwidth');
// hard-code list of columns and indexes to ensure we ignore any added at a later stage
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('blobs', array(
'blobid' => $definitions['blobs']['blobid'],
'format' => $definitions['blobs']['format'],
'content' => $definitions['blobs']['content'],
'PRIMARY KEY (blobid)',
)));
qa_db_upgrade_query(qa_db_create_table_sql('cache', array(
'type' => $definitions['cache']['type'],
'cacheid' => $definitions['cache']['cacheid'],
'content' => $definitions['cache']['content'],
'created' => $definitions['cache']['created'],
'lastread' => $definitions['cache']['lastread'],
'PRIMARY KEY (type, cacheid)',
'KEY (lastread)',
))); // hard-code list of columns and indexes to ensure we ignore any added at a later stage
$locktablesquery .= ', ^blobs WRITE, ^cache WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 20:
if (!QA_FINAL_EXTERNAL_USERS) {
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('userlogins', array(
'userid' => $definitions['userlogins']['userid'],
'source' => $definitions['userlogins']['source'],
'identifier' => $definitions['userlogins']['identifier'],
'identifiermd5' => $definitions['userlogins']['identifiermd5'],
'KEY source (source, identifiermd5)',
'KEY userid (userid)',
'CONSTRAINT ^userlogins_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',
)));
qa_db_upgrade_query('ALTER TABLE ^users CHANGE COLUMN passsalt passsalt ' . $definitions['users']['passsalt'] . ', CHANGE COLUMN passcheck passcheck ' . $definitions['users']['passcheck']);
$locktablesquery .= ', ^userlogins WRITE';
qa_db_upgrade_query($locktablesquery);
}
break;
case 21:
if (!QA_FINAL_EXTERNAL_USERS) {
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('userfields', array(
'fieldid' => $definitions['userfields']['fieldid'],
'title' => $definitions['userfields']['title'],
'content' => $definitions['userfields']['content'],
'position' => $definitions['userfields']['position'],
'flags' => $definitions['userfields']['flags'],
'PRIMARY KEY (fieldid)',
)));
$locktablesquery .= ', ^userfields WRITE';
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query(qa_db_default_userfields_sql());
}
break;
// Up to here: Version 1.3 beta 1
case 22:
if (!QA_FINAL_EXTERNAL_USERS) {
qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN sessionsource ' . $definitions['users']['sessionsource'] . ' AFTER sessioncode');
qa_db_upgrade_query($locktablesquery);
}
break;
// Up to here: Version 1.3 beta 2 and release
case 23:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('widgets', array(
'widgetid' => $definitions['widgets']['widgetid'],
'place' => $definitions['widgets']['place'],
'position' => $definitions['widgets']['position'],
'tags' => $definitions['widgets']['tags'],
'title' => $definitions['widgets']['title'],
'PRIMARY KEY (widgetid)',
'UNIQUE position (position)',
)));
$locktablesquery .= ', ^widgets WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 24:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('tagwords', array(
'postid' => $definitions['tagwords']['postid'],
'wordid' => $definitions['tagwords']['wordid'],
'KEY postid (postid)',
'KEY wordid (wordid)',
'CONSTRAINT ^tagwords_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
'CONSTRAINT ^tagwords_ibfk_2 FOREIGN KEY (wordid) REFERENCES ^words(wordid)',
)));
$locktablesquery .= ', ^tagwords WRITE';
qa_db_upgrade_query('ALTER TABLE ^words ADD COLUMN tagwordcount ' . $definitions['words']['tagwordcount'] . ' AFTER contentcount');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['doreindexcontent'] = true;
break;
// Up to here: Version 1.4 developer preview
case 25:
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^blobs')));
// might be using blobs table shared with another installation, so check if we need to upgrade
if (isset($keycolumns['filename']))
qa_db_upgrade_progress('Skipping upgrading blobs table since it was already upgraded by another Q2A site sharing it.');
else {
qa_db_upgrade_query('ALTER TABLE ^blobs ADD COLUMN filename ' . $definitions['blobs']['filename'] . ' AFTER content, ADD COLUMN userid ' . $definitions['blobs']['userid'] . ' AFTER filename, ADD COLUMN cookieid ' . $definitions['blobs']['cookieid'] . ' AFTER userid, ADD COLUMN createip ' . $definitions['blobs']['createip'] . ' AFTER cookieid, ADD COLUMN created ' . $definitions['blobs']['created'] . ' AFTER createip');
qa_db_upgrade_query($locktablesquery);
}
break;
case 26:
qa_db_upgrade_query('ALTER TABLE ^uservotes ADD COLUMN flag ' . $definitions['uservotes']['flag'] . ' AFTER vote');
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN flagcount ' . $definitions['posts']['flagcount'] . ' AFTER downvotes, ADD KEY type_3 (type, flagcount, created)');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
break;
case 27:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN netvotes ' . $definitions['posts']['netvotes'] . ' AFTER downvotes, ADD KEY type_4 (type, netvotes, created)');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
break;
case 28:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN views ' . $definitions['posts']['views'] . ' AFTER netvotes, ADD COLUMN hotness ' . $definitions['posts']['hotness'] . ' AFTER views, ADD KEY type_5 (type, views, created), ADD KEY type_6 (type, hotness)');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
break;
case 29:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN lastviewip ' . $definitions['posts']['lastviewip'] . ' AFTER netvotes');
qa_db_upgrade_query($locktablesquery);
break;
case 30:
qa_db_upgrade_query('ALTER TABLE ^posts DROP FOREIGN KEY ^posts_ibfk_3'); // to allow category column types to be changed
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^posts DROP KEY categoryid, DROP KEY categoryid_2');
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^categories CHANGE COLUMN categoryid categoryid ' . $definitions['categories']['categoryid'] . ', ADD COLUMN parentid ' . $definitions['categories']['parentid'] . ' AFTER categoryid, ADD COLUMN backpath ' . $definitions['categories']['backpath'] . ' AFTER position, ADD COLUMN content ' . $definitions['categories']['content'] . ' AFTER tags, DROP INDEX tags, DROP INDEX position, ADD UNIQUE parentid (parentid, tags), ADD UNIQUE parentid_2 (parentid, position), ADD KEY backpath (backpath(' . QA_DB_MAX_CAT_PAGE_TAGS_LENGTH . '))');
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^posts CHANGE COLUMN categoryid categoryid ' . $definitions['posts']['categoryid'] . ', ADD COLUMN catidpath1 ' . $definitions['posts']['catidpath1'] . ' AFTER categoryid, ADD COLUMN catidpath2 ' . $definitions['posts']['catidpath2'] . ' AFTER catidpath1, ADD COLUMN catidpath3 ' . $definitions['posts']['catidpath3'] . ' AFTER catidpath2'); // QA_CATEGORY_DEPTH=4
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^posts ADD KEY catidpath1 (catidpath1, type, created), ADD KEY catidpath2 (catidpath2, type, created), ADD KEY catidpath3 (catidpath3, type, created), ADD KEY categoryid (categoryid, type, created), ADD KEY catidpath1_2 (catidpath1, updated, type), ADD KEY catidpath2_2 (catidpath2, updated, type), ADD KEY catidpath3_2 (catidpath3, updated, type), ADD KEY categoryid_2 (categoryid, updated, type)');
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^posts ADD CONSTRAINT ^posts_ibfk_3 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE SET NULL');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecalccategories'] = true;
break;
// Up to here: Version 1.4 betas and release
case 31:
qa_db_upgrade_query('ALTER TABLE ^posts CHANGE COLUMN type type ' . $definitions['posts']['type'] . ', ADD COLUMN updatetype ' . $definitions['posts']['updatetype'] . ' AFTER updated, ADD COLUMN closedbyid ' . $definitions['posts']['closedbyid'] . ' AFTER selchildid, ADD KEY closedbyid (closedbyid), ADD CONSTRAINT ^posts_ibfk_4 FOREIGN KEY (closedbyid) REFERENCES ^posts(postid)');
qa_db_upgrade_query($locktablesquery);
break;
case 32:
qa_db_upgrade_query("UPDATE ^posts SET updatetype=IF(INSTR(type, '_HIDDEN')>0, 'H', 'E') WHERE updated IS NOT NULL");
break;
case 33:
qa_db_upgrade_query('ALTER TABLE ^contentwords CHANGE COLUMN type type ' . $definitions['contentwords']['type']);
qa_db_upgrade_query($locktablesquery);
break;
case 34:
if (!QA_FINAL_EXTERNAL_USERS) {
$keytables = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW TABLES')));
// might be using messages table shared with another installation, so check if we need to upgrade
if (isset($keytables[qa_db_add_table_prefix('messages')]))
qa_db_upgrade_progress('Skipping messages table since it was already added by another Q2A site sharing these users.');
else {
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('messages', array(
'messageid' => $definitions['messages']['messageid'],
'fromuserid' => $definitions['messages']['fromuserid'],
'touserid' => $definitions['messages']['touserid'],
'content' => $definitions['messages']['content'],
'format' => $definitions['messages']['format'],
'created' => $definitions['messages']['created'],
'PRIMARY KEY (messageid)',
'KEY fromuserid (fromuserid, touserid, created)',
)));
$locktablesquery .= ', ^messages WRITE';
qa_db_upgrade_query($locktablesquery);
}
}
break;
case 35:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('userfavorites', array(
'userid' => $definitions['userfavorites']['userid'],
'entitytype' => $definitions['userfavorites']['entitytype'],
'entityid' => $definitions['userfavorites']['entityid'],
'nouserevents' => $definitions['userfavorites']['nouserevents'],
'PRIMARY KEY (userid, entitytype, entityid)',
'KEY userid (userid, nouserevents)',
'KEY entitytype (entitytype, entityid, nouserevents)',
QA_FINAL_EXTERNAL_USERS ? null : 'CONSTRAINT ^userfavorites_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^userfavorites WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 36:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('userevents', array(
'userid' => $definitions['userevents']['userid'],
'entitytype' => $definitions['userevents']['entitytype'],
'entityid' => $definitions['userevents']['entityid'],
'questionid' => $definitions['userevents']['questionid'],
'lastpostid' => $definitions['userevents']['lastpostid'],
'updatetype' => $definitions['userevents']['updatetype'],
'lastuserid' => $definitions['userevents']['lastuserid'],
'updated' => $definitions['userevents']['updated'],
'KEY userid (userid, updated)',
'KEY questionid (questionid, userid)',
QA_FINAL_EXTERNAL_USERS ? null : 'CONSTRAINT ^userevents_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^userevents WRITE';
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorefillevents'] = true;
break;
case 37:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('sharedevents', array(
'entitytype' => $definitions['sharedevents']['entitytype'],
'entityid' => $definitions['sharedevents']['entityid'],
'questionid' => $definitions['sharedevents']['questionid'],
'lastpostid' => $definitions['sharedevents']['lastpostid'],
'updatetype' => $definitions['sharedevents']['updatetype'],
'lastuserid' => $definitions['sharedevents']['lastuserid'],
'updated' => $definitions['sharedevents']['updated'],
'KEY entitytype (entitytype, entityid, updated)',
'KEY questionid (questionid, entitytype, entityid)',
)));
$locktablesquery .= ', ^sharedevents WRITE';
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorefillevents'] = true;
break;
case 38:
qa_db_upgrade_query('ALTER TABLE ^posts ADD KEY lastuserid (lastuserid, updated, type)');
qa_db_upgrade_query($locktablesquery);
break;
case 39:
qa_db_upgrade_query('ALTER TABLE ^posts DROP KEY type_3, ADD KEY flagcount (flagcount, created, type)');
qa_db_upgrade_query($locktablesquery);
break;
case 40:
qa_db_upgrade_query('ALTER TABLE ^userpoints ADD COLUMN bonus ' . $definitions['userpoints']['bonus'] . ' AFTER downvoteds');
qa_db_upgrade_query($locktablesquery);
break;
case 41:
qa_db_upgrade_query('ALTER TABLE ^pages ADD COLUMN permit ' . $definitions['pages']['permit'] . ' AFTER flags');
qa_db_upgrade_query($locktablesquery);
break;
case 42:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('usermetas', array(
'userid' => $definitions['usermetas']['userid'],
'title' => $definitions['usermetas']['title'],
'content' => $definitions['usermetas']['content'],
'PRIMARY KEY (userid, title)',
QA_FINAL_EXTERNAL_USERS ? null : 'CONSTRAINT ^usermetas_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^usermetas WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 43:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('postmetas', array(
'postid' => $definitions['postmetas']['postid'],
'title' => $definitions['postmetas']['title'],
'content' => $definitions['postmetas']['content'],
'PRIMARY KEY (postid, title)',
'CONSTRAINT ^postmetas_ibfk_1 FOREIGN KEY (postid) REFERENCES ^posts(postid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^postmetas WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 44:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('categorymetas', array(
'categoryid' => $definitions['categorymetas']['categoryid'],
'title' => $definitions['categorymetas']['title'],
'content' => $definitions['categorymetas']['content'],
'PRIMARY KEY (categoryid, title)',
'CONSTRAINT ^categorymetas_ibfk_1 FOREIGN KEY (categoryid) REFERENCES ^categories(categoryid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^categorymetas WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 45:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('tagmetas', array(
'tag' => $definitions['tagmetas']['tag'],
'title' => $definitions['tagmetas']['title'],
'content' => $definitions['tagmetas']['content'],
'PRIMARY KEY (tag, title)',
)));
$locktablesquery .= ', ^tagmetas WRITE';
qa_db_upgrade_query($locktablesquery);
break;
case 46:
qa_db_upgrade_query('ALTER TABLE ^posts DROP KEY selchildid, ADD KEY selchildid (selchildid, type, created), ADD COLUMN amaxvote SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER acount, ADD KEY type_7 (type, amaxvote, created)');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
break;
case 47:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query(qa_db_create_table_sql('usernotices', array(
'noticeid' => $definitions['usernotices']['noticeid'],
'userid' => $definitions['usernotices']['userid'],
'content' => $definitions['usernotices']['content'],
'format' => $definitions['usernotices']['format'],
'tags' => $definitions['usernotices']['tags'],
'created' => $definitions['usernotices']['created'],
'PRIMARY KEY (noticeid)',
'KEY userid (userid, created)',
QA_FINAL_EXTERNAL_USERS ? null : 'CONSTRAINT ^usernotices_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^usernotices WRITE';
qa_db_upgrade_query($locktablesquery);
break;
// Up to here: Version 1.5.x
case 48:
if (!QA_FINAL_EXTERNAL_USERS) {
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^messages')));
// might be using messages table shared with another installation, so check if we need to upgrade
if (isset($keycolumns['type']))
qa_db_upgrade_progress('Skipping upgrading messages table since it was already upgraded by another Q2A site sharing it.');
else {
qa_db_upgrade_query('ALTER TABLE ^messages ADD COLUMN type ' . $definitions['messages']['type'] . ' AFTER messageid, DROP KEY fromuserid, ADD key type (type, fromuserid, touserid, created), ADD KEY touserid (touserid, type, created)');
qa_db_upgrade_query($locktablesquery);
}
}
break;
case 49:
if (!QA_FINAL_EXTERNAL_USERS) {
qa_db_upgrade_query('ALTER TABLE ^users CHANGE COLUMN flags flags ' . $definitions['users']['flags']);
qa_db_upgrade_query($locktablesquery);
}
break;
case 50:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN name ' . $definitions['posts']['name'] . ' AFTER tags');
qa_db_upgrade_query($locktablesquery);
break;
case 51:
if (!QA_FINAL_EXTERNAL_USERS) {
// might be using userfields table shared with another installation, so check if we need to upgrade
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^userfields')));
if (isset($keycolumns['permit']))
qa_db_upgrade_progress('Skipping upgrading userfields table since it was already upgraded by another Q2A site sharing it.');
else {
qa_db_upgrade_query('ALTER TABLE ^userfields ADD COLUMN permit ' . $definitions['userfields']['permit'] . ' AFTER flags');
qa_db_upgrade_query($locktablesquery);
}
}
break;
case 52:
if (!QA_FINAL_EXTERNAL_USERS) {
$keyindexes = qa_array_to_keys(qa_db_read_all_assoc(qa_db_query_sub('SHOW INDEX FROM ^users'), null, 'Key_name'));
if (isset($keyindexes['created']))
qa_db_upgrade_progress('Skipping upgrading users table since it was already upgraded by another Q2A site sharing it.');
else {
qa_db_upgrade_query('ALTER TABLE ^users ADD KEY created (created, level, flags)');
qa_db_upgrade_query($locktablesquery);
}
}
break;
case 53:
qa_db_upgrade_query('ALTER TABLE ^blobs CHANGE COLUMN content content ' . $definitions['blobs']['content']);
qa_db_upgrade_query($locktablesquery);
break;
case 54:
qa_db_upgrade_query('UNLOCK TABLES');
qa_db_upgrade_query('SET FOREIGN_KEY_CHECKS=0'); // in case InnoDB not available
qa_db_upgrade_query(qa_db_create_table_sql('userlevels', array(
'userid' => $definitions['userlevels']['userid'],
'entitytype' => $definitions['userlevels']['entitytype'],
'entityid' => $definitions['userlevels']['entityid'],
'level' => $definitions['userlevels']['level'],
'UNIQUE userid (userid, entitytype, entityid)',
'KEY entitytype (entitytype, entityid)',
QA_FINAL_EXTERNAL_USERS ? null : 'CONSTRAINT ^userlevels_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',
)));
$locktablesquery .= ', ^userlevels WRITE';
qa_db_upgrade_query($locktablesquery);
break;
// Up to here: Version 1.6 beta 1
case 55:
if (!QA_FINAL_EXTERNAL_USERS) {
// might be using users table shared with another installation, so check if we need to upgrade
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^users')));
if (isset($keycolumns['wallposts']))
qa_db_upgrade_progress('Skipping upgrading users table since it was already upgraded by another Q2A site sharing it.');
else {
qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN wallposts ' . $definitions['users']['wallposts'] . ' AFTER flags');
qa_db_upgrade_query($locktablesquery);
}
}
break;
// Up to here: Version 1.6 beta 2
case 56:
qa_db_upgrade_query('ALTER TABLE ^pages DROP INDEX tags, ADD KEY tags (tags)');
qa_db_upgrade_query($locktablesquery);
break;
// Up to here: Version 1.6 (release)
case 57:
if (!QA_FINAL_EXTERNAL_USERS) {
// might be using messages table shared with another installation, so check if we need to upgrade
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^messages')));
if (isset($keycolumns['fromhidden']))
qa_db_upgrade_progress('Skipping upgrading messages table since it was already upgraded by another Q2A site sharing it.');
else {
qa_db_upgrade_query('ALTER TABLE ^messages ADD COLUMN fromhidden ' . $definitions['messages']['fromhidden'] . ' AFTER touserid');
qa_db_upgrade_query('ALTER TABLE ^messages ADD COLUMN tohidden ' . $definitions['messages']['tohidden'] . ' AFTER fromhidden');
qa_db_upgrade_query('ALTER TABLE ^messages ADD KEY fromhidden (fromhidden), ADD KEY tohidden (tohidden)');
qa_db_upgrade_query($locktablesquery);
}
}
break;
case 58:
// note: need to use full table names here as aliases trigger error "Table 'x' was not locked with LOCK TABLES"
qa_db_upgrade_query('DELETE FROM ^userfavorites WHERE entitytype="U" AND userid=entityid');
qa_db_upgrade_query('DELETE ^uservotes FROM ^uservotes JOIN ^posts ON ^uservotes.postid=^posts.postid AND ^uservotes.userid=^posts.userid');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
$keyrecalc['dorecalcpoints'] = true;
break;
// Up to here: Version 1.7
case 59:
// upgrade from alpha version removed
break;
// Up to here: Version 1.7.1
case 60:
// add new category widget - note title must match that from qa_register_core_modules()
if (qa_using_categories()) {
$widgetid = qa_db_widget_create('Categories', 'all');
qa_db_widget_move($widgetid, 'SL', 1);
}
break;
case 61:
// upgrade length of qa_posts.content field to 12000
$newlength = QA_DB_MAX_CONTENT_LENGTH;
$query = 'SELECT CHARACTER_MAXIMUM_LENGTH FROM information_schema.COLUMNS WHERE table_schema=$ AND table_name=$ AND column_name="content"';
$tablename = qa_db_add_table_prefix('posts');
$oldlength = qa_db_read_one_value(qa_db_query_sub($query, QA_FINAL_MYSQL_DATABASE, $tablename));
if ($oldlength < $newlength) {
qa_db_upgrade_query('ALTER TABLE ^posts MODIFY content ' . $definitions['posts']['content']);
}
break;
case 62:
if (!QA_FINAL_EXTERNAL_USERS) {
// might be using users table shared with another installation, so check if we need to upgrade
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^users')));
if (isset($keycolumns['passhash']))
qa_db_upgrade_progress(sprintf($skipMessage, 'users'));
else {
// add column to qa_users to handle new bcrypt passwords
qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN passhash ' . $definitions['users']['passhash'] . ' AFTER passcheck');
qa_db_upgrade_query($locktablesquery);
}
}
break;
case 63:
// check for shared cookies table
$fieldDef = qa_db_read_one_assoc(qa_db_query_sub('SHOW COLUMNS FROM ^cookies WHERE Field="createip"'));
if (strtolower($fieldDef['Type']) === 'varbinary(16)')
qa_db_upgrade_progress(sprintf($skipMessage, 'cookies'));
else {
qa_db_upgrade_query('ALTER TABLE ^cookies MODIFY writeip ' . $definitions['cookies']['writeip'] . ', MODIFY createip ' . $definitions['cookies']['createip']);
qa_db_upgrade_query('UPDATE ^cookies SET writeip = UNHEX(HEX(CAST(writeip AS UNSIGNED))), createip = UNHEX(HEX(CAST(createip AS UNSIGNED)))');
}
qa_db_upgrade_query('ALTER TABLE ^iplimits MODIFY ip ' . $definitions['iplimits']['ip']);
qa_db_upgrade_query('UPDATE ^iplimits SET ip = UNHEX(HEX(CAST(ip AS UNSIGNED)))');
// check for shared blobs table
$fieldDef = qa_db_read_one_assoc(qa_db_query_sub('SHOW COLUMNS FROM ^blobs WHERE Field="createip"'));
if (strtolower($fieldDef['Type']) === 'varbinary(16)')
qa_db_upgrade_progress(sprintf($skipMessage, 'blobs'));
else {
qa_db_upgrade_query('ALTER TABLE ^blobs MODIFY createip ' . $definitions['blobs']['createip']);
qa_db_upgrade_query('UPDATE ^blobs SET createip = UNHEX(HEX(CAST(createip AS UNSIGNED)))');
}
qa_db_upgrade_query('ALTER TABLE ^posts MODIFY lastviewip ' . $definitions['posts']['lastviewip'] . ', MODIFY lastip ' . $definitions['posts']['lastip'] . ', MODIFY createip ' . $definitions['posts']['createip']);
qa_db_upgrade_query('UPDATE ^posts SET lastviewip = UNHEX(HEX(CAST(lastviewip AS UNSIGNED))), lastip = UNHEX(HEX(CAST(lastip AS UNSIGNED))), createip = UNHEX(HEX(CAST(createip AS UNSIGNED)))');
if (!QA_FINAL_EXTERNAL_USERS) {
// check for shared users table
$fieldDef = qa_db_read_one_assoc(qa_db_query_sub('SHOW COLUMNS FROM ^users WHERE Field="createip"'));
if (strtolower($fieldDef['Type']) === 'varbinary(16)')
qa_db_upgrade_progress(sprintf($skipMessage, 'users'));
else {
qa_db_upgrade_query('ALTER TABLE ^users MODIFY createip ' . $definitions['users']['createip'] . ', MODIFY loginip ' . $definitions['users']['loginip'] . ', MODIFY writeip ' . $definitions['users']['writeip']);
qa_db_upgrade_query('UPDATE ^users SET createip = UNHEX(HEX(CAST(createip AS UNSIGNED))), loginip = UNHEX(HEX(CAST(loginip AS UNSIGNED))), writeip = UNHEX(HEX(CAST(writeip AS UNSIGNED)))');
}
}
qa_db_upgrade_query($locktablesquery);
break;
case 64:
$pluginManager = new Q2A_Plugin_PluginManager();
$allPlugins = $pluginManager->getFilesystemPlugins();
$pluginManager->setEnabledPlugins($allPlugins);
break;
case 65:
qa_db_upgrade_query('ALTER TABLE ^uservotes ADD COLUMN votecreated ' . $definitions['uservotes']['votecreated'] . ' AFTER flag');
qa_db_upgrade_query('ALTER TABLE ^uservotes ADD COLUMN voteupdated ' . $definitions['uservotes']['voteupdated'] . ' AFTER votecreated');
qa_db_upgrade_query('ALTER TABLE ^uservotes ADD KEY voted (votecreated, voteupdated)');
qa_db_upgrade_query($locktablesquery);
// for old votes, set a default date of when that post was made
qa_db_upgrade_query('UPDATE ^uservotes, ^posts SET ^uservotes.votecreated=^posts.created WHERE ^uservotes.postid=^posts.postid AND (^uservotes.vote != 0 OR ^uservotes.flag=0)');
break;
case 66:
$newColumns = array(
'ADD COLUMN cupvotes ' . $definitions['userpoints']['cupvotes'] . ' AFTER adownvotes',
'ADD COLUMN cdownvotes ' . $definitions['userpoints']['cdownvotes'] . ' AFTER cupvotes',
'ADD COLUMN cvoteds ' . $definitions['userpoints']['cvoteds'] . ' AFTER avoteds',
);
qa_db_upgrade_query('ALTER TABLE ^userpoints ' . implode(', ', $newColumns));
qa_db_upgrade_query($locktablesquery);
break;
case 67:
// ensure we don't have old userids lying around
qa_db_upgrade_query('ALTER TABLE ^messages MODIFY fromuserid ' . $definitions['messages']['fromuserid']);
qa_db_upgrade_query('ALTER TABLE ^messages MODIFY touserid ' . $definitions['messages']['touserid']);
qa_db_upgrade_query('UPDATE ^messages SET fromuserid=NULL WHERE fromuserid NOT IN (SELECT userid FROM ^users)');
qa_db_upgrade_query('UPDATE ^messages SET touserid=NULL WHERE touserid NOT IN (SELECT userid FROM ^users)');
// set up foreign key on messages table
qa_db_upgrade_query('ALTER TABLE ^messages ADD CONSTRAINT ^messages_ibfk_1 FOREIGN KEY (fromuserid) REFERENCES ^users(userid) ON DELETE SET NULL');
qa_db_upgrade_query('ALTER TABLE ^messages ADD CONSTRAINT ^messages_ibfk_2 FOREIGN KEY (touserid) REFERENCES ^users(userid) ON DELETE SET NULL');
qa_db_upgrade_query($locktablesquery);
break;
// Up to here: Version 1.8 beta1
}
qa_db_set_db_version($newversion);
if (qa_db_get_db_version() != $newversion)
qa_fatal_error('Could not increment database version');
}
qa_db_upgrade_query('UNLOCK TABLES');
// Perform any necessary recalculations, as determined by upgrade steps
foreach ($keyrecalc as $state => $dummy) {
while ($state) {
set_time_limit(60);
$stoptime = time() + 2;
while (qa_recalc_perform_step($state) && (time() < $stoptime))
;
qa_db_upgrade_progress(qa_recalc_get_message($state));
}
}
}
/**
* Reset the definitions of $columns in $table according to the $definitions array
* @param $definitions
* @param $table
* @param $columns
*/
function qa_db_upgrade_table_columns($definitions, $table, $columns)
{
$sqlchanges = array();
foreach ($columns as $column)
$sqlchanges[] = 'CHANGE COLUMN ' . $column . ' ' . $column . ' ' . $definitions[$table][$column];
qa_db_upgrade_query('ALTER TABLE ^' . $table . ' ' . implode(', ', $sqlchanges));
}
/**
* Perform upgrade $query and output progress to the browser
* @param $query
*/
function qa_db_upgrade_query($query)
{
qa_db_upgrade_progress('Running query: ' . qa_db_apply_sub($query, array()) . ' ...');
qa_db_query_sub($query);
}
/**
* Output $text to the browser (after converting to HTML) and do all we can to get it displayed
* @param $text
*/
function qa_db_upgrade_progress($text)
{
echo qa_html($text) . str_repeat(' ', 1024) . "<br><br>\n";
flush();
}