function init_queries($tableslc)
{
$tbl1 = qa_db_add_table_prefix('chat_posts');
$tbl2 = qa_db_add_table_prefix('chat_users');
if (in_array($tbl1, $tableslc) && in_array($tbl2, $tableslc)) {
qa_opt($this->optactive, '1');
return null;
}
return array('CREATE TABLE IF NOT EXISTS ^chat_posts (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`posted` datetime NOT NULL,
`message` varchar(800) NOT NULL,
PRIMARY KEY (`postid`),
KEY `posted` (`posted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8', 'CREATE TABLE IF NOT EXISTS ^chat_users (
`userid` int(10) unsigned NOT NULL,
`lastposted` datetime NOT NULL,
`lastpolled` datetime NOT NULL,
`kickeduntil` datetime NOT NULL DEFAULT "2012-01-01 00:00:00",
PRIMARY KEY (`userid`),
KEY `active` (`lastpolled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8', 'CREATE TABLE IF NOT EXISTS ^chat_kicks (
`userid` int(10) unsigned NOT NULL,
`kickedby` int(10) unsigned NOT NULL,
`whenkicked` datetime NOT NULL,
PRIMARY KEY (`userid`,`kickedby`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8');
}
function init_queries($tableslc)
{
$tbl1 = qa_db_add_table_prefix('homepage');
if (in_array($tbl1, $tableslc)) {
return null;
}
return "CREATE TABLE IF NOT EXISTS ^homepage (\n\t\t\t\t\t\t`questionid` int(10) unsigned NOT NULL,\n\t\t\t\t\t\t`childid` int(10) unsigned,\n\t\t\t\t\t\t`updated` datetime NOT NULL,\n\t\t\t\t\t\tPRIMARY KEY (questionid)\n\t\t\t\t\t) ENGINE=MyISAM DEFAULT CHARSET=utf8";
}
function init_queries($tableslc)
{
$tablename = qa_db_add_table_prefix($this->pluginkey);
if (!in_array($tablename, $tableslc)) {
return 'CREATE TABLE IF NOT EXISTS ^' . $this->pluginkey . ' ( ' . '`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, ' . '`title` varchar(30) NOT NULL, ' . '`pages` varchar(800) NOT NULL, ' . '`position` varchar(30) NOT NULL, ' . '`ordering` smallint(5) unsigned NOT NULL, ' . '`content` text NOT NULL, ' . 'PRIMARY KEY (`id`), ' . 'KEY `position` (`position`,`ordering`) ' . ' ) ENGINE=InnoDB DEFAULT CHARSET=utf8';
}
// we're already set up
qa_opt($this->opt, '1');
return null;
}
public function init_queries($table_list)
{
if (qa_opt('event_logger_to_database')) {
$tablename = qa_db_add_table_prefix('eventlog');
if (!in_array($tablename, $table_list)) {
require_once QA_INCLUDE_DIR . 'app/users.php';
require_once QA_INCLUDE_DIR . 'db/maxima.php';
return 'CREATE TABLE ^eventlog (' . 'datetime DATETIME NOT NULL,' . 'ipaddress VARCHAR (15) CHARACTER SET ascii,' . 'userid ' . qa_get_mysql_user_column_type() . ',' . 'handle VARCHAR(' . QA_DB_MAX_HANDLE_LENGTH . '),' . 'cookieid BIGINT UNSIGNED,' . 'event VARCHAR (20) CHARACTER SET ascii NOT NULL,' . 'params VARCHAR (800) NOT NULL,' . 'KEY datetime (datetime),' . 'KEY ipaddress (ipaddress),' . 'KEY userid (userid),' . 'KEY event (event)' . ') ENGINE=MyISAM DEFAULT CHARSET=utf8';
}
}
}
function init_queries($tableslc)
{
//qw_check_for_new_version(false);
include_once QA_INCLUDE_DIR . 'qa-app-users.php';
//if(qa_get_logged_in_level() >= QA_USER_LEVEL_ADMIN){
$queries = array();
$queries = qw_apply_filter('init_queries', $queries, $tableslc);
$tablename = qa_db_add_table_prefix('ra_userevent');
if (!in_array($tablename, $tableslc)) {
require_once QA_INCLUDE_DIR . 'qa-app-users.php';
require_once QA_INCLUDE_DIR . 'qa-db-maxima.php';
$queries[] = 'CREATE TABLE ^ra_userevent (' . 'id bigint(20) NOT NULL AUTO_INCREMENT,' . 'datetime DATETIME NOT NULL,' . 'userid ' . qa_get_mysql_user_column_type() . ',' . 'postid int(10) unsigned DEFAULT NULL,' . 'effecteduserid ' . qa_get_mysql_user_column_type() . ' unsigned DEFAULT NULL,' . 'event VARCHAR (20) CHARACTER SET utf8 NOT NULL,' . 'params text NOT NULL,' . '`read` tinyint(1) NOT NULL DEFAULT "0",' . 'PRIMARY KEY (id),' . 'KEY datetime (datetime),' . 'KEY userid (userid),' . 'KEY event (event)' . ') ENGINE=MyISAM DEFAULT CHARSET=utf8';
}
return $queries;
///}
}
function init_queries($tableslc)
{
require_once QA_INCLUDE_DIR . 'qa-app-users.php';
require_once QA_INCLUDE_DIR . 'qa-db-maxima.php';
qa_opt('event_logger_to_database', 1);
/* remove evnet logger plugin if exist */
if (file_exists(QA_INCLUDE_DIR . 'qa-plugin/event-logger')) {
unlink(QA_INCLUDE_DIR . 'qa-plugin/event-logger');
}
$tablename = qa_db_add_table_prefix('eventlog');
$quries = array();
if (!in_array($tablename, $tableslc)) {
require_once QA_INCLUDE_DIR . 'qa-app-users.php';
require_once QA_INCLUDE_DIR . 'qa-db-maxima.php';
$quries[] = 'CREATE TABLE ^eventlog (' . 'datetime DATETIME NOT NULL,' . 'ipaddress VARCHAR (15) CHARACTER SET ascii,' . 'userid INT(10),' . 'handle VARCHAR(' . QA_DB_MAX_HANDLE_LENGTH . '),' . 'cookieid BIGINT UNSIGNED,' . 'event VARCHAR (20) CHARACTER SET ascii NOT NULL,' . 'params LONGTEXT NOT NULL,' . 'KEY datetime (datetime),' . 'KEY ipaddress (ipaddress),' . 'KEY userid (userid),' . 'KEY event (event)' . ') ENGINE=MyISAM DEFAULT CHARSET=utf8';
}
if (in_array($tablename, $tableslc) && !qa_opt('qw_eventlog_param_datatype')) {
qa_opt('qw_eventlog_param_datatype', 1);
$quries[] = 'ALTER TABLE ^eventlog MODIFY params LONGTEXT;';
}
return $quries;
}
function init_queries($tableslc)
{
$tablename = qa_db_add_table_prefix('eventlog');
// check if event logger has been initialized already (check for one of the options and existing table)
require_once QA_INCLUDE_DIR . 'qa-app-options.php';
if (qa_opt('event_logger_to_database') && in_array($tablename, $tableslc)) {
// options exist, but check if really enabled
if (qa_opt('event_logger_to_database') == '' && qa_opt('event_logger_to_files') == '') {
// enabled database logging
qa_opt('event_logger_to_database', 1);
}
} else {
// not enabled, let's enable the event logger
// set option values for event logger
qa_opt('event_logger_to_database', 1);
qa_opt('event_logger_to_files', '');
qa_opt('event_logger_directory', '');
qa_opt('event_logger_hide_header', '');
if (!in_array($tablename, $tableslc)) {
require_once QA_INCLUDE_DIR . 'qa-app-users.php';
require_once QA_INCLUDE_DIR . 'qa-db-maxima.php';
return 'CREATE TABLE IF NOT EXISTS ^eventlog (' . 'datetime DATETIME NOT NULL,' . 'ipaddress VARCHAR (15) CHARACTER SET ascii,' . 'userid ' . qa_get_mysql_user_column_type() . ',' . 'handle VARCHAR(' . QA_DB_MAX_HANDLE_LENGTH . '),' . 'cookieid BIGINT UNSIGNED,' . 'event VARCHAR (20) CHARACTER SET ascii NOT NULL,' . 'params VARCHAR (800) NOT NULL,' . 'KEY datetime (datetime),' . 'KEY ipaddress (ipaddress),' . 'KEY userid (userid),' . 'KEY event (event)' . ') ENGINE=MyISAM DEFAULT CHARSET=utf8';
}
}
// memo: would be best to check if plugin is installed in qa-plugin folder or using plugin_exists()
// however this functionality is not available in q2a v1.6.3
// create table qa_usermeta which stores the last visit of each user
$tablename2 = qa_db_add_table_prefix('usermeta');
if (!in_array($tablename2, $tableslc)) {
qa_db_query_sub('CREATE TABLE IF NOT EXISTS ^usermeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
user_id bigint(20) unsigned NOT NULL,
meta_key varchar(255) DEFAULT NULL,
meta_value longtext,
PRIMARY KEY (meta_id),
UNIQUE (user_id,meta_key)
) ENGINE=MyISAM DEFAULT CHARSET=utf8');
}
}
function init_queries($tableslc)
{
$queries = array();
if (!in_array(qa_db_add_table_prefix('caching'), $tableslc)) {
$queries[] = 'CREATE TABLE IF NOT EXISTS `^caching` (
`sign` TINYINT UNSIGNED DEFAULT NULL,
`format` CHAR(8) DEFAULT NULL,
`type` CHAR(8) DEFAULT NULL,
`device` CHAR(8) DEFAULT NULL,
`filename` VARCHAR(255) DEFAULT NULL,
`content` MEDIUMBLOB NOT NULL,
`userid` INT(10) UNSIGNED DEFAULT NULL,
`cookieid` BIGINT(20) UNSIGNED DEFAULT NULL,
`createip` INT(10) UNSIGNED DEFAULT NULL,
`created` datetime NOT NULL,
PRIMARY KEY (`sign`, `format`, `type`, `device`, `filename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;';
}
if (count($queries)) {
return $queries;
} else {
return null;
}
}
function init_queries($tableslc)
{
$tbl1 = qa_db_add_table_prefix('blog_posts');
$tbl2 = qa_db_add_table_prefix('blog_comments');
if (in_array($tbl1, $tableslc) && in_array($tbl2, $tableslc)) {
return null;
}
return array('CREATE TABLE IF NOT EXISTS ^blog_posts (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`userid` int(10) unsigned NOT NULL,
`title` varchar(300),
`type` int(10) unsigned,
`content` text,
`posted` datetime,
`views` int(10) unsigned,
`updated` datetime,
`tags` varchar(300) NOT NULL,
`notify` varchar(300) NOT NULL,
`format` varchar(100) NOT NULL,
PRIMARY KEY (`postid`),
KEY `posted` (`posted`),
KEY `updated` (`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8', 'CREATE TABLE IF NOT EXISTS ^blog_comments (
`postid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parentid` int(10) unsigned NOT NULL,
`posted` datetime NOT NULL,
`comment` text NOT NULL,
`userid` int(10) unsigned NOT NULL,
`updated` datetime,
`format` varchar(100) NOT NULL,
PRIMARY KEY (`postid`),
KEY `parentid` (`parentid`),
KEY `posted` (`posted`),
KEY `updated` (`updated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8');
}
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());
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)
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
// 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
// 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
// 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)
// 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
// 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
// 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
// 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
// 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
// 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) {
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^userfields')));
// might be using userfields table shared with another installation, so check if we need to upgrade
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
// Up to here: Version 1.6 beta 1
case 55:
if (!QA_FINAL_EXTERNAL_USERS) {
$keycolumns = qa_array_to_keys(qa_db_read_all_values(qa_db_query_sub('SHOW COLUMNS FROM ^users')));
// might be using messages table shared with another installation, so check if we need to upgrade
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
// 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)
// 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: Verison 1.7
// Up to here: Verison 1.7
case 59:
// Upgrade from alpha version removed
break;
// Up to here: Verison 1.7.1
}
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));
}
}
}
public function init_queries($queries, $tableslc)
{
$tablename = qa_db_add_table_prefix('ra_email_queue');
if (!in_array($tablename, $tableslc)) {
$queries[] = '
CREATE TABLE IF NOT EXISTS ^ra_email_queue (
id int(6) NOT NULL AUTO_INCREMENT,
event varchar(250) NOT NULL,
body text NOT NULL,
created_by varchar(250) NOT NULL,
created_ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
status tinyint(1) DEFAULT "0",
sent_on timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
';
}
$tablename = qa_db_add_table_prefix('ra_email_queue_receiver');
if (!in_array($tablename, $tableslc)) {
$queries[] = '
CREATE TABLE IF NOT EXISTS ^ra_email_queue_receiver (
id int(6) NOT NULL AUTO_INCREMENT,
userid int(10) NOT NULL,
email varchar(250) NOT NULL,
name varchar(250) NOT NULL,
handle varchar(20) NULL,
queue_id int(6) NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
';
}
return $queries;
}
/**
* Callback function to add table prefixes, as used in qa_db_apply_sub().
*/
function qa_db_prefix_callback($matches)
{
return qa_db_add_table_prefix($matches[1]);
}
function qa_db_upgrade_tables()
{
require_once QA_INCLUDE_DIR . 'qa-app-recalc.php';
$definitions = qa_db_table_definitions();
$keyrecalc = array();
// Write-lock all QA tables before we start so no one can read or write anything
$keydbtables = qa_array_to_lower_keys(qa_db_read_all_values(qa_db_query_raw('SHOW TABLES')));
foreach ($definitions as $rawname => $definition) {
if (isset($keydbtables[strtolower(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)
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'] . ', downvotes ' . $definitions['posts']['downvotes'] . ')');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecountposts'] = true;
break;
case 3:
qa_db_upgrade_query('ALTER TABLE ^userpoints ADD COLUMN (upvoteds ' . $definitions['userpoints']['upvoteds'] . ', downvoteds ' . $definitions['userpoints']['downvoteds'] . ')');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecalcpoints'] = true;
break;
case 4:
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN lastuserid ' . $definitions['posts']['lastuserid'] . ', 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'] . ', questionid ' . $definitions['contentwords']['questionid'] . ')');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['doreindexposts'] = true;
break;
// Up to here: Version 1.0 beta 2
// Up to here: Version 1.0 beta 2
case 6:
qa_db_upgrade_query('ALTER TABLE ^userpoints ADD COLUMN cposts ' . $definitions['userpoints']['cposts']);
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']);
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
// 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']);
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('UPDATE ^users SET flags=1');
}
break;
case 10:
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'] . ', 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(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'] . ', 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'] . ', qdownvotes ' . $definitions['userpoints']['qdownvotes'] . ', aupvotes ' . $definitions['userpoints']['aupvotes'] . ', adownvotes ' . $definitions['userpoints']['adownvotes'] . ')');
qa_db_upgrade_query($locktablesquery);
$keyrecalc['dorecalcpoints'] = true;
break;
// Up to here: Version 1.2 beta 1
// 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)
// Up to here: Version 1.2 (release)
case 16:
qa_db_upgrade_table_columns($definitions, 'posts', array('format'));
qa_db_upgrade_query($locktablesquery);
$keyrecalc['doreindexposts'] = 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'] . ', 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'] . ', ADD COLUMN avatarwidth ' . $definitions['users']['avatarwidth'] . ', ADD COLUMN avatarheight ' . $definitions['users']['avatarheight']);
}
// hard-code list of columns and indexes to ensure we ignore any added at a later stage
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(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(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
// 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']);
qa_db_upgrade_query($locktablesquery);
}
break;
// Up to here: Version 1.3 beta 2 and release
// Up to here: Version 1.3 beta 2 and release
case 23:
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(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']);
qa_db_upgrade_query($locktablesquery);
$keyrecalc['doreindexposts'] = true;
break;
// Up to here: Version 1.4 developer preview
// Up to here: Version 1.4 developer preview
case 25:
$keycolumns = qa_array_to_lower_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 QA site sharing it.');
} else {
qa_db_upgrade_query('ALTER TABLE ^blobs ADD COLUMN filename ' . $definitions['blobs']['filename'] . ', ADD COLUMN userid ' . $definitions['blobs']['userid'] . ', ADD COLUMN cookieid ' . $definitions['blobs']['cookieid'] . ', ADD COLUMN createip ' . $definitions['blobs']['createip'] . ', ADD COLUMN created ' . $definitions['blobs']['created']);
qa_db_upgrade_query($locktablesquery);
}
break;
case 26:
qa_db_upgrade_query('ALTER TABLE ^uservotes ADD COLUMN flag ' . $definitions['uservotes']['flag']);
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^posts ADD COLUMN flagcount ' . $definitions['posts']['flagcount'] . ', 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'] . ', 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'] . ', ADD COLUMN hotness ' . $definitions['posts']['hotness'] . ', 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']);
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'] . ', ADD COLUMN backpath ' . $definitions['categories']['backpath'] . ', ADD COLUMN content ' . $definitions['categories']['content'] . ', 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'] . ', ADD COLUMN catidpath2 ' . $definitions['posts']['catidpath2'] . ', ADD COLUMN catidpath3 ' . $definitions['posts']['catidpath3']);
// 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 beta 1 and 2
}
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));
}
}
}
