install.php 69.3 KB
Newer Older
Scott committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
<?php
/*
	Question2Answer by Gideon Greenspan and contributors
	http://www.question2answer.org/

	File: qa-include/qa-db-install.php
	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
*/

Scott committed
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
if (!defined('QA_VERSION')) { // don't allow this page to be requested directly from browser
	header('Location: ../');
	exit;
}

define('QA_DB_VERSION_CURRENT', 64);


/**
 * 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;
Scott committed
54 55
	}

Scott committed
56 57
	return $coltype;
}
Scott committed
58 59


Scott committed
60 61 62 63 64 65 66
/**
 * 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); }
Scott committed
67

Scott committed
68 69
	require_once QA_INCLUDE_DIR . 'db/maxima.php';
	require_once QA_INCLUDE_DIR . 'app/users.php';
Scott committed
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

	/*
		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-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.
	*/

Scott committed
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
	$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', // INET_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', // INET_ATON of IP address of last login
			'written' => 'DATETIME', // time of last write action done by user
			'writeip' => 'VARBINARY(16)', // INET_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-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-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-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 . ' NOT NULL',
			'touserid' => $useridcoltype . ' NOT NULL',
			'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-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-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-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-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', // INET_ATON of IP address when cookie created
			'written' => 'DATETIME', // time of last write action done by anon user with cookie
			'writeip' => 'VARBINARY(16)', // INET_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)', // INET_ATON of IP address used to create the post
			'lastuserid' => $useridcoltype, // which user last modified it
			'lastip' => 'VARBINARY(16)', // INET_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)', // INET_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-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)', // INET_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
			'UNIQUE userid (userid, postid)',
			'KEY postid (postid)',
			'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
			'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
			'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-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', // INET_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';
Scott committed
527 528
	}

Scott committed
529 530
	return $tables;
}
Scott committed
531 532


Scott committed
533 534 535 536 537 538 539 540 541
/**
 * 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));
}
Scott committed
542 543


Scott committed
544 545 546 547 548 549 550 551
/**
 * 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());
Scott committed
552

Scott committed
553
	$missing = array();
Scott committed
554

Scott committed
555 556 557
	foreach ($definitions as $rawname => $definition)
		if (!isset($keydbtables[qa_db_add_table_prefix($rawname)]))
			$missing[$rawname] = $definition;
Scott committed
558

Scott committed
559 560
	return $missing;
}
Scott committed
561 562


Scott committed
563 564 565 566 567 568 569 570 571
/**
 * 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)));
Scott committed
572

Scott committed
573
	$missing = array();
Scott committed
574

Scott committed
575
	foreach ($definition as $colname => $coldefn)
Scott committed
576
		if (!is_int($colname) && !isset($keycolumns[$colname]))
Scott committed
577
			$missing[$colname] = $coldefn;
Scott committed
578

Scott committed
579 580
	return $missing;
}
Scott committed
581 582


Scott committed
583 584 585 586 587 588
/**
 * 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();
Scott committed
589

Scott committed
590 591
	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);
Scott committed
592

Scott committed
593 594
		if ($version > 0)
			return $version;
Scott committed
595 596
	}

Scott committed
597 598
	return null;
}
Scott committed
599

600

Scott committed
601 602 603 604 605 606 607
/**
 * Set the current version in the database
 * @param $version
 */
function qa_db_set_db_version($version)
{
	require_once QA_INCLUDE_DIR . 'db/options.php';
Scott committed
608

Scott committed
609 610
	qa_db_set_option('db_version', $version);
}
Scott committed
611 612


Scott committed
613 614 615 616 617 618
/**
 * 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
Scott committed
619

Scott committed
620
	$version = qa_db_read_one_value(qa_db_query_raw('SELECT VERSION()'));
Scott committed
621

Scott committed
622 623
	if (((float)$version) < 4.1)
		qa_fatal_error('MySQL version 4.1 or later is required - you appear to be running MySQL ' . $version);
Scott committed
624

Scott committed
625 626
	$definitions = qa_db_table_definitions();
	$missing = qa_db_missing_tables($definitions);
Scott committed
627

Scott committed
628 629
	if (count($missing) == count($definitions))
		return 'none';
Scott committed
630

Scott committed
631 632 633
	else {
		if (!isset($missing['options'])) {
			$version = qa_db_get_db_version();
Scott committed
634

Scott committed
635 636 637
			if (isset($version) && ($version < QA_DB_VERSION_CURRENT))
				return 'old-version';
		}
Scott committed
638

Scott committed
639 640 641 642
		if (count($missing)) {
			if (defined('QA_MYSQL_USERS_PREFIX')) { // special case if two installations sharing users
				$datacount = 0;
				$datamissing = 0;
Scott committed
643

644
				foreach ($definitions as $rawname => $definition) {
Scott committed
645 646
					if (qa_db_add_table_prefix($rawname) == (QA_MYSQL_TABLE_PREFIX . $rawname)) {
						$datacount++;
Scott committed
647

Scott committed
648 649 650
						if (isset($missing[$rawname]))
							$datamissing++;
					}
651
				}
Scott committed
652

Scott committed
653
				if ($datacount == $datamissing && $datamissing == count($missing))
Scott committed
654 655
					return 'non-users-missing';
			}
Scott committed
656

Scott committed
657
			return 'table-missing';
Scott committed
658

Scott committed
659 660 661 662
		} else
			foreach ($definitions as $table => $definition)
				if (count(qa_db_missing_columns($table, $definition)))
					return 'column-missing';
Scott committed
663 664
	}

Scott committed
665 666
	return false;
}
Scott committed
667 668


Scott committed
669 670 671 672 673 674 675
/**
 * 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();
Scott committed
676

Scott committed
677
	$missingtables = qa_db_missing_tables($definitions);
Scott committed
678

Scott committed
679 680
	foreach ($missingtables as $rawname => $definition) {
		qa_db_query_sub(qa_db_create_table_sql($rawname, $definition));
Scott committed
681

Scott committed
682 683 684
		if ($rawname == 'userfields')
			qa_db_query_sub(qa_db_default_userfields_sql());
	}
Scott committed
685

Scott committed
686 687
	foreach ($definitions as $table => $definition) {
		$missingcolumns = qa_db_missing_columns($table, $definition);
Scott committed
688

Scott committed
689 690
		foreach ($missingcolumns as $colname => $coldefn)
			qa_db_query_sub('ALTER TABLE ^' . $table . ' ADD COLUMN ' . $colname . ' ' . $coldefn);
Scott committed
691 692
	}

Scott committed
693 694
	qa_db_set_db_version(QA_DB_VERSION_CURRENT);
}
Scott committed
695 696


Scott committed
697 698 699 700 701 702 703 704 705 706 707 708
/**
 * 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));
Scott committed
709

Scott committed
710 711
	return 'CREATE TABLE ^' . $rawname . ' (' . $querycols . ') ENGINE=InnoDB CHARSET=utf8';
}
Scott committed
712 713


Scott committed
714 715 716 717 718
/**
 * 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()
{
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
	require_once QA_INCLUDE_DIR . 'app/options.php';

	$profileFields = array(
		array(
			'title' => 'name',
			'position' => 0,
			'flags' => 0,
			'permit' => QA_PERMIT_ALL,
		),
		array(
			'title' => 'location',
			'position' => 1,
			'flags' => 0,
			'permit' => QA_PERMIT_ALL,
		),
		array(
			'title' => 'website',
			'position' => 2,
			'flags' => QA_FIELD_FLAGS_LINK_URL,
			'permit' => QA_PERMIT_ALL,
		),
		array(
			'title' => 'about',
			'position' => 3,
			'flags' => QA_FIELD_FLAGS_MULTI_LINE,
			'permit' => QA_PERMIT_ALL,
		),
Scott committed
746
	);
Scott committed
747

748 749 750 751 752
	$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']);
	}
Scott committed
753

754
	$sql = substr($sql, 0, -2);
Scott committed
755

Scott committed
756 757
	return $sql;
}
Scott committed
758 759


Scott committed
760 761 762 763 764 765
/**
 * 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';
Scott committed
766

Scott committed
767 768
	$definitions = qa_db_table_definitions();
	$keyrecalc = array();
Scott committed
769

Scott committed
770
	// Write-lock all Q2A tables before we start so no one can read or write anything
Scott committed
771

Scott committed
772
	$keydbtables = qa_array_to_keys(qa_db_list_tables());
Scott committed
773

Scott committed
774 775 776
	foreach ($definitions as $rawname => $definition)
		if (isset($keydbtables[qa_db_add_table_prefix($rawname)]))
			$locks[] = '^' . $rawname . ' WRITE';
Scott committed
777

Scott committed
778
	$locktablesquery = 'LOCK TABLES ' . implode(', ', $locks);
Scott committed
779

Scott committed
780
	qa_db_upgrade_query($locktablesquery);
Scott committed
781

Scott committed
782
	// Upgrade it step-by-step until it's up to date (do LOCK TABLES after ALTER TABLE because the lock can sometimes be lost)
783

Scott committed
784 785
	// 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.';
Scott committed
786

Scott committed
787 788
	while (1) {
		$version = qa_db_get_db_version();
Scott committed
789

Scott committed
790 791
		if ($version >= QA_DB_VERSION_CURRENT)
			break;
Scott committed
792

Scott committed
793
		$newversion = $version + 1;
Scott committed
794

Scott committed
795
		qa_db_upgrade_progress(QA_DB_VERSION_CURRENT - $version . ' upgrade step/s remaining...');
Scott committed
796

Scott committed
797 798
		switch ($newversion) {
			// Up to here: Version 1.0 beta 1
Scott committed
799

Scott committed
800 801 802 803 804 805
			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;
Scott committed
806

Scott committed
807 808 809 810 811 812
			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;
Scott committed
813

Scott committed
814 815 816 817 818
			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;
Scott committed
819

Scott committed
820 821 822 823 824
			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;
Scott committed
825

Scott committed
826
			// Up to here: Version 1.0 beta 2
Scott committed
827

Scott committed
828 829 830 831 832
			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;
Scott committed
833

Scott committed
834 835 836
			case 7:
				if (!QA_FINAL_EXTERNAL_USERS) {
					qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN sessioncode ' . $definitions['users']['sessioncode'] . ' AFTER writeip');
Scott committed
837
					qa_db_upgrade_query($locktablesquery);
Scott committed
838 839
				}
				break;
Scott committed
840

Scott committed
841 842 843 844 845
			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;
Scott committed
846

Scott committed
847
			// Up to here: Version 1.0 beta 3, 1.0, 1.0.1 beta, 1.0.1
Scott committed
848

Scott committed
849 850 851
			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');
Scott committed
852
					qa_db_upgrade_query($locktablesquery);
Scott committed
853 854 855
					qa_db_upgrade_query('UPDATE ^users SET flags=1');
				}
				break;
Scott committed
856

Scott committed
857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872
			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;
Scott committed
873

Scott committed
874 875 876 877 878
			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;
Scott committed
879

Scott committed
880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897
			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;
Scott committed
898

Scott committed
899 900 901 902
			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;
Scott committed
903

Scott committed
904 905 906 907 908
			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;
Scott committed
909

Scott committed
910
			// Up to here: Version 1.2 beta 1
Scott committed
911

Scott committed
912 913 914
			case 15:
				if (!QA_FINAL_EXTERNAL_USERS)
					qa_db_upgrade_table_columns($definitions, 'users', array('emailcode', 'sessioncode', 'flags'));
Scott committed
915

Scott committed
916 917 918 919 920 921 922
				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;
Scott committed
923

Scott committed
924
			// Up to here: Version 1.2 (release)
Scott committed
925

Scott committed
926 927 928 929 930
			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;
Scott committed
931

Scott committed
932 933 934 935
			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;
Scott committed
936

Scott committed
937 938 939 940
			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;
Scott committed
941

Scott committed
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
			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;
Scott committed
970

Scott committed
971 972
			case 20:
				if (!QA_FINAL_EXTERNAL_USERS) {
Scott committed
973 974
					qa_db_upgrade_query('UNLOCK TABLES');

Scott committed
975 976 977 978 979 980 981 982
					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',
Scott committed
983 984
					)));

Scott committed
985
					qa_db_upgrade_query('ALTER TABLE ^users CHANGE COLUMN passsalt passsalt ' . $definitions['users']['passsalt'] . ', CHANGE COLUMN passcheck passcheck ' . $definitions['users']['passcheck']);
Scott committed
986

Scott committed
987 988 989 990
					$locktablesquery .= ', ^userlogins WRITE';
					qa_db_upgrade_query($locktablesquery);
				}
				break;
Scott committed
991

Scott committed
992 993
			case 21:
				if (!QA_FINAL_EXTERNAL_USERS) {
Scott committed
994 995
					qa_db_upgrade_query('UNLOCK TABLES');

Scott committed
996 997 998 999 1000 1001 1002
					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)',
Scott committed
1003 1004
					)));

Scott committed
1005
					$locktablesquery .= ', ^userfields WRITE';
Scott committed
1006 1007
					qa_db_upgrade_query($locktablesquery);

Scott committed
1008 1009 1010
					qa_db_upgrade_query(qa_db_default_userfields_sql());
				}
				break;
Scott committed
1011

Scott committed
1012
			// Up to here: Version 1.3 beta 1
Scott committed
1013

Scott committed
1014 1015 1016
			case 22:
				if (!QA_FINAL_EXTERNAL_USERS) {
					qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN sessionsource ' . $definitions['users']['sessionsource'] . ' AFTER sessioncode');
Scott committed
1017
					qa_db_upgrade_query($locktablesquery);
Scott committed
1018 1019
				}
				break;
Scott committed
1020

Scott committed
1021
			// Up to here: Version 1.3 beta 2 and release
Scott committed
1022

Scott committed
1023 1024
			case 23:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1025

Scott committed
1026 1027 1028 1029 1030 1031 1032 1033 1034
				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)',
				)));
Scott committed
1035

Scott committed
1036 1037 1038
				$locktablesquery .= ', ^widgets WRITE';
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1039

Scott committed
1040 1041
			case 24:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1042

Scott committed
1043 1044 1045 1046 1047 1048 1049 1050
				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)',
				)));
Scott committed
1051

Scott committed
1052
				$locktablesquery .= ', ^tagwords WRITE';
Scott committed
1053

Scott committed
1054 1055
				qa_db_upgrade_query('ALTER TABLE ^words ADD COLUMN tagwordcount ' . $definitions['words']['tagwordcount'] . ' AFTER contentcount');
				qa_db_upgrade_query($locktablesquery);
Scott committed
1056

Scott committed
1057 1058
				$keyrecalc['doreindexcontent'] = true;
				break;
Scott committed
1059

Scott committed
1060
			// Up to here: Version 1.4 developer preview
Scott committed
1061

Scott committed
1062 1063 1064
			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
Scott committed
1065

Scott committed
1066 1067
				if (isset($keycolumns['filename']))
					qa_db_upgrade_progress('Skipping upgrading blobs table since it was already upgraded by another Q2A site sharing it.');
Scott committed
1068

Scott committed
1069 1070
				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');
Scott committed
1071
					qa_db_upgrade_query($locktablesquery);
Scott committed
1072 1073
				}
				break;
Scott committed
1074

Scott committed
1075 1076 1077
			case 26:
				qa_db_upgrade_query('ALTER TABLE ^uservotes ADD COLUMN flag ' . $definitions['uservotes']['flag'] . ' AFTER vote');
				qa_db_upgrade_query($locktablesquery);
Scott committed
1078

Scott committed
1079 1080
				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);
Scott committed
1081

Scott committed
1082 1083
				$keyrecalc['dorecountposts'] = true;
				break;
Scott committed
1084

Scott committed
1085 1086 1087
			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);
Scott committed
1088

Scott committed
1089 1090
				$keyrecalc['dorecountposts'] = true;
				break;
Scott committed
1091

Scott committed
1092 1093 1094 1095 1096 1097
			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;
Scott committed
1098

Scott committed
1099 1100 1101 1102
			case 29:
				qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN lastviewip ' . $definitions['posts']['lastviewip'] . ' AFTER netvotes');
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1103

Scott committed
1104 1105 1106
			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);
Scott committed
1107

Scott committed
1108 1109
				qa_db_upgrade_query('ALTER TABLE ^posts DROP KEY categoryid, DROP KEY categoryid_2');
				qa_db_upgrade_query($locktablesquery);
Scott committed
1110

Scott committed
1111 1112
				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);
Scott committed
1113

Scott committed
1114 1115
				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);
Scott committed
1116

Scott committed
1117 1118
				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);
Scott committed
1119

Scott committed
1120 1121
				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);
Scott committed
1122

Scott committed
1123 1124
				$keyrecalc['dorecalccategories'] = true;
				break;
Scott committed
1125

Scott committed
1126
			// Up to here: Version 1.4 betas and release
Scott committed
1127

Scott committed
1128 1129 1130 1131
			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;
Scott committed
1132

Scott committed
1133 1134 1135
			case 32:
				qa_db_upgrade_query("UPDATE ^posts SET updatetype=IF(INSTR(type, '_HIDDEN')>0, 'H', 'E') WHERE updated IS NOT NULL");
				break;
Scott committed
1136

Scott committed
1137 1138 1139 1140
			case 33:
				qa_db_upgrade_query('ALTER TABLE ^contentwords CHANGE COLUMN type type ' . $definitions['contentwords']['type']);
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1141

Scott committed
1142 1143 1144 1145
			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
Scott committed
1146

Scott committed
1147 1148
					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.');
Scott committed
1149

Scott committed
1150 1151
					else {
						qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1152

Scott committed
1153 1154 1155 1156 1157 1158 1159 1160 1161 1162
						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)',
						)));
Scott committed
1163

Scott committed
1164 1165 1166 1167 1168
						$locktablesquery .= ', ^messages WRITE';
						qa_db_upgrade_query($locktablesquery);
					}
				}
				break;
Scott committed
1169

Scott committed
1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186
			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;
Scott committed
1187

Scott committed
1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209
			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;
Scott committed
1210

Scott committed
1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230
			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;
Scott committed
1231

Scott committed
1232 1233 1234 1235
			case 38:
				qa_db_upgrade_query('ALTER TABLE ^posts ADD KEY lastuserid (lastuserid, updated, type)');
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1236

Scott committed
1237 1238 1239 1240
			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;
Scott committed
1241

Scott committed
1242 1243 1244 1245
			case 40:
				qa_db_upgrade_query('ALTER TABLE ^userpoints ADD COLUMN bonus ' . $definitions['userpoints']['bonus'] . ' AFTER downvoteds');
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1246

Scott committed
1247 1248 1249 1250
			case 41:
				qa_db_upgrade_query('ALTER TABLE ^pages ADD COLUMN permit ' . $definitions['pages']['permit'] . ' AFTER flags');
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1251

Scott committed
1252 1253
			case 42:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1254

Scott committed
1255 1256 1257 1258 1259 1260 1261
				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',
				)));
Scott committed
1262

Scott committed
1263 1264 1265
				$locktablesquery .= ', ^usermetas WRITE';
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1266

Scott committed
1267 1268
			case 43:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1269

Scott committed
1270 1271 1272 1273 1274 1275 1276
				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',
				)));
Scott committed
1277

Scott committed
1278 1279 1280
				$locktablesquery .= ', ^postmetas WRITE';
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1281

Scott committed
1282 1283
			case 44:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1284

Scott committed
1285 1286 1287 1288 1289 1290 1291
				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',
				)));
Scott committed
1292

Scott committed
1293 1294 1295
				$locktablesquery .= ', ^categorymetas WRITE';
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1296

Scott committed
1297 1298
			case 45:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1299

Scott committed
1300 1301 1302 1303 1304 1305
				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)',
				)));
Scott committed
1306

Scott committed
1307 1308 1309
				$locktablesquery .= ', ^tagmetas WRITE';
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1310

Scott committed
1311 1312 1313
			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);
Scott committed
1314

Scott committed
1315 1316
				$keyrecalc['dorecountposts'] = true;
				break;
Scott committed
1317

Scott committed
1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335
			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;
Scott committed
1336

Scott committed
1337
			// Up to here: Version 1.5.x
Scott committed
1338

Scott committed
1339 1340 1341 1342
			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
Scott committed
1343

Scott committed
1344 1345 1346 1347 1348
					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)');
Scott committed
1349 1350
						qa_db_upgrade_query($locktablesquery);
					}
Scott committed
1351 1352
				}
				break;
Scott committed
1353

Scott committed
1354 1355 1356
			case 49:
				if (!QA_FINAL_EXTERNAL_USERS) {
					qa_db_upgrade_query('ALTER TABLE ^users CHANGE COLUMN flags flags ' . $definitions['users']['flags']);
Scott committed
1357
					qa_db_upgrade_query($locktablesquery);
Scott committed
1358 1359 1360 1361 1362 1363 1364
				}
				break;

			case 50:
				qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN name ' . $definitions['posts']['name'] . ' AFTER tags');
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1365

Scott committed
1366 1367 1368 1369
			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')));
Scott committed
1370

Scott committed
1371 1372
					if (isset($keycolumns['permit']))
						qa_db_upgrade_progress('Skipping upgrading userfields table since it was already upgraded by another Q2A site sharing it.');
Scott committed
1373

Scott committed
1374 1375 1376
					else {
						qa_db_upgrade_query('ALTER TABLE ^userfields ADD COLUMN permit ' . $definitions['userfields']['permit'] . ' AFTER flags');
						qa_db_upgrade_query($locktablesquery);
Scott committed
1377
					}
Scott committed
1378 1379
				}
				break;
Scott committed
1380

Scott committed
1381 1382 1383
			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'));
Scott committed
1384

Scott committed
1385 1386
					if (isset($keyindexes['created']))
						qa_db_upgrade_progress('Skipping upgrading users table since it was already upgraded by another Q2A site sharing it.');
Scott committed
1387

Scott committed
1388 1389 1390
					else {
						qa_db_upgrade_query('ALTER TABLE ^users ADD KEY created (created, level, flags)');
						qa_db_upgrade_query($locktablesquery);
Scott committed
1391
					}
Scott committed
1392 1393
				}
				break;
Scott committed
1394

Scott committed
1395 1396 1397 1398
			case 53:
				qa_db_upgrade_query('ALTER TABLE ^blobs CHANGE COLUMN content content ' . $definitions['blobs']['content']);
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1399

Scott committed
1400 1401
			case 54:
				qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1402

Scott committed
1403
				qa_db_upgrade_query('SET FOREIGN_KEY_CHECKS=0'); // in case InnoDB not available
Scott committed
1404

Scott committed
1405 1406 1407 1408 1409 1410 1411 1412 1413
				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',
				)));
Scott committed
1414

Scott committed
1415 1416 1417
				$locktablesquery .= ', ^userlevels WRITE';
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1418

Scott committed
1419
			// Up to here: Version 1.6 beta 1
Scott committed
1420

Scott committed
1421 1422 1423 1424
			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')));
Scott committed
1425

Scott committed
1426 1427
					if (isset($keycolumns['wallposts']))
						qa_db_upgrade_progress('Skipping upgrading users table since it was already upgraded by another Q2A site sharing it.');
Scott committed
1428

Scott committed
1429 1430 1431
					else {
						qa_db_upgrade_query('ALTER TABLE ^users ADD COLUMN wallposts ' . $definitions['users']['wallposts'] . ' AFTER flags');
						qa_db_upgrade_query($locktablesquery);
Scott committed
1432
					}
Scott committed
1433 1434
				}
				break;
Scott committed
1435

Scott committed
1436
			// Up to here: Version 1.6 beta 2
Scott committed
1437

Scott committed
1438 1439 1440 1441
			case 56:
				qa_db_upgrade_query('ALTER TABLE ^pages DROP INDEX tags, ADD KEY tags (tags)');
				qa_db_upgrade_query($locktablesquery);
				break;
Scott committed
1442

Scott committed
1443
			// Up to here: Version 1.6 (release)
Scott committed
1444

Scott committed
1445 1446 1447 1448
			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')));
Scott committed
1449

Scott committed
1450 1451 1452 1453 1454 1455
					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)');
Scott committed
1456

Scott committed
1457
						qa_db_upgrade_query($locktablesquery);
Scott committed
1458
					}
Scott committed
1459 1460
				}
				break;
Scott committed
1461

Scott committed
1462 1463 1464 1465 1466
			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);
Scott committed
1467

Scott committed
1468 1469 1470
				$keyrecalc['dorecountposts'] = true;
				$keyrecalc['dorecalcpoints'] = true;
				break;
Scott committed
1471

Scott committed
1472
			// Up to here: Version 1.7
1473

Scott committed
1474 1475 1476
			case 59:
				// upgrade from alpha version removed
				break;
1477

Scott committed
1478
			// Up to here: Version 1.7.1
Scott committed
1479

Scott committed
1480 1481 1482 1483 1484 1485 1486
			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;
1487

Scott committed
1488 1489 1490 1491 1492 1493
			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));
Scott committed
1494

Scott committed
1495 1496 1497
				if ($oldlength < $newlength) {
					qa_db_upgrade_query('ALTER TABLE ^posts MODIFY content ' . $definitions['posts']['content']);
				}
Scott committed
1498

Scott committed
1499
				break;
1500

Scott committed
1501 1502 1503 1504
			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')));
1505

Scott committed
1506 1507
					if (isset($keycolumns['passhash']))
						qa_db_upgrade_progress(sprintf($skipMessage, 'users'));
1508
					else {
Scott committed
1509 1510 1511
						// 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);
1512
					}
Scott committed
1513 1514
				}
				break;
1515

Scott committed
1516 1517 1518 1519 1520 1521 1522 1523 1524
			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)))');
				}
1525

Scott committed
1526 1527
				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)))');
1528

Scott committed
1529 1530 1531 1532 1533 1534 1535 1536
				// 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)))');
				}
Scott committed
1537

Scott committed
1538 1539
				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)))');
1540

Scott committed
1541 1542 1543 1544 1545 1546 1547 1548 1549 1550
				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)))');
					}
				}
1551

Scott committed
1552 1553
				qa_db_upgrade_query($locktablesquery);
				break;
1554

Scott committed
1555 1556 1557 1558 1559
			case 64:
				$pluginManager = new Q2A_Plugin_PluginManager();
				$allPlugins = $pluginManager->getFilesystemPlugins();
				$pluginManager->setEnabledPlugins($allPlugins);
				break;
Scott committed
1560 1561


Scott committed
1562
			// Up to here: Version 1.8 alpha
Scott committed
1563 1564
		}

Scott committed
1565
		qa_db_set_db_version($newversion);
Scott committed
1566

Scott committed
1567 1568
		if (qa_db_get_db_version() != $newversion)
			qa_fatal_error('Could not increment database version');
Scott committed
1569 1570
	}

Scott committed
1571
	qa_db_upgrade_query('UNLOCK TABLES');
Scott committed
1572

Scott committed
1573
	// Perform any necessary recalculations, as determined by upgrade steps
Scott committed
1574

1575
	foreach ($keyrecalc as $state => $dummy) {
Scott committed
1576 1577
		while ($state) {
			set_time_limit(60);
Scott committed
1578

Scott committed
1579
			$stoptime = time() + 2;
Scott committed
1580

Scott committed
1581 1582
			while (qa_recalc_perform_step($state) && (time() < $stoptime))
				;
Scott committed
1583

Scott committed
1584 1585
			qa_db_upgrade_progress(qa_recalc_get_message($state));
		}
1586
	}
Scott committed
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
}


/**
 * 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();
}