function init_queries($tableslc)
{
// check if the plugin is initialized
$ok = qa_opt('open_login_ok');
if ($ok == 3) {
return null;
}
$queries = array();
$columns = qa_db_read_all_values(qa_db_query_sub('describe ^userlogins'));
if (!in_array('oemail', $columns)) {
$queries[] = 'ALTER TABLE ^userlogins ADD `oemail` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL';
}
if (!in_array('ohandle', $columns)) {
$queries[] = 'ALTER TABLE ^userlogins ADD `ohandle` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL';
}
$columns = qa_db_read_all_values(qa_db_query_sub('describe ^users'));
if (!in_array('oemail', $columns)) {
$queries[] = 'ALTER TABLE ^users ADD `oemail` VARCHAR( 80 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL';
}
if (count($queries)) {
return $queries;
}
// we're already set up
qa_opt('open_login_ok', '3');
return null;
}
function mp_get_categoryslug()
{
$i = qa_db_read_all_values(qa_db_query_sub('select backpath from ^categories where categoryid=#', mp_get_categoryid()));
if (count($i) > 0) {
return array_reverse(explode('/', $i[0]));
} else {
return "";
}
}
function qa_page_q_post_rules($post, $parentpost = null, $siblingposts = null, $childposts = null)
{
$rules = qa_page_q_post_rules_base($post, $parentpost, $siblingposts, $childposts);
qa_db_query_sub('CREATE TABLE IF NOT EXISTS ^postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL,
meta_key varchar(255) DEFAULT \'\',
meta_value longtext,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=MyISAM DEFAULT CHARSET=utf8');
$expert = qa_db_read_one_value(qa_db_query_sub("SELECT meta_value FROM ^postmeta WHERE meta_key='is_expert_question' AND post_id=#", $post['postid']), true);
if ($expert) {
if (!qa_permit_value_error(qa_opt('expert_question_roles'), qa_get_logged_in_userid(), qa_get_logged_in_level(), qa_get_logged_in_flags())) {
$is_expert = true;
}
$users = qa_opt('expert_question_users');
$users = explode("\n", $users);
$handle = qa_get_logged_in_handle();
foreach ($users as $idx => $user) {
if ($user == $handle) {
$is_expert = true;
break;
}
if (strpos($user, '=')) {
$user = explode('=', $user);
if ($user[0] == $handle) {
$catnames = explode(',', $user[1]);
$cats = qa_db_read_all_values(qa_db_query_sub('SELECT categoryid FROM ^categories WHERE title IN ($)', $catnames));
$is_expert = $cats;
}
}
}
if (isset($is_expert) && !$rules['viewable']) {
// experts that aren't allowed to change hidden questions
if (is_array($is_expert)) {
$in_cats = qa_db_read_one_value(qa_db_query_sub("SELECT COUNT(postid) FROM ^posts WHERE categoryid IN (#) AND postid=#", $is_expert, $post['postid']), true);
if ($in_cats) {
$rules['viewable'] = true;
}
} else {
$rules['viewable'] = true;
}
}
$rules['reshowable'] = false;
$rules['answerbutton'] = true;
$rules['commentbutton'] = true;
$rules['commentable'] = true;
}
return $rules;
}
function process_event($event, $userid, $handle, $cookieid, $params)
{
if (qa_opt('expert_question_enable')) {
switch ($event) {
case 'q_post':
if (qa_post_text('is_expert_question') == 'yes' || in_array(qa_opt('expert_question_type'), array(1, 2)) && !qa_get_logged_in_userid() || qa_opt('expert_question_type') == 3) {
require_once QA_INCLUDE_DIR . 'qa-app-post-update.php';
qa_question_set_hidden($params, true, $userid, $handle, $cookieid, array(), array());
qa_db_query_sub('CREATE TABLE IF NOT EXISTS ^postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL,
meta_key varchar(255) DEFAULT \'\',
meta_value longtext,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=MyISAM DEFAULT CHARSET=utf8');
qa_db_query_sub("INSERT INTO ^postmeta (post_id,meta_key,meta_value) VALUES (#,'is_expert_question','1')", $params['postid']);
if (qa_opt('expert_question_email_experts')) {
$subs = array('^post_title' => $params['title'], '^post_url' => qa_path_html(qa_q_request($params['postid'], $params['title']), null, qa_opt('site_url')), '^questions_list' => qa_path_html(qa_opt('expert_question_page_url'), null, qa_opt('site_url')), '^site_url' => qa_opt('site_url'));
$experts = explode("\n", qa_opt('expert_question_users'));
foreach ($experts as $expert) {
if (strpos($expert, '=')) {
$expert = explode('=', $expert);
$catnames = explode(',', $expert[1]);
$cats = qa_db_read_all_values(qa_db_query_sub('SELECT categoryid FROM ^categories WHERE title IN ($)', $catnames));
if (in_array($params['categoryid'], $cats)) {
qa_send_notification($this->getuserfromhandle($expert[0]), '@', $expert[0], qa_opt('expert_question_email_subject'), qa_opt('expert_question_email_body'), $subs);
}
} else {
qa_send_notification($this->getuserfromhandle($expert), '@', $expert, qa_opt('expert_question_email_subject'), qa_opt('expert_question_email_body'), $subs);
}
}
}
}
break;
default:
break;
}
}
}
function is_expert_user()
{
if (!qa_permit_value_error(qa_opt('expert_question_roles'), qa_get_logged_in_userid(), qa_get_logged_in_level(), qa_get_logged_in_flags())) {
return true;
}
$users = qa_opt('expert_question_users');
$users = explode("\n", $users);
$handle = qa_get_logged_in_handle();
foreach ($users as $idx => $user) {
if ($user == $handle) {
return true;
}
if (strpos($user, '=')) {
$user = explode('=', $user);
if ($user[0] == $handle) {
$catnames = explode(',', $user[1]);
$cats = qa_db_read_all_values(qa_db_query_sub('SELECT categoryid FROM ^categories WHERE title IN ($)', $catnames));
return $cats;
}
}
}
return false;
}
function cs_user_badge($handle)
{
if (qa_opt('badge_active')) {
$userids = qa_handles_to_userids(array($handle));
$userid = $userids[$handle];
// displays small badge widget, suitable for meta
$result = qa_db_read_all_values(qa_db_query_sub('SELECT badge_slug FROM ^userbadges WHERE user_id=#', $userid));
if (count($result) == 0) {
return;
}
$badges = qa_get_badge_list();
foreach ($result as $slug) {
$bcount[$badges[$slug]['type']] = isset($bcount[$badges[$slug]['type']]) ? $bcount[$badges[$slug]['type']] + 1 : 1;
}
$output = '<ul class="user-badge clearfix">';
for ($x = 2; $x >= 0; $x--) {
if (!isset($bcount[$x])) {
continue;
}
$count = $bcount[$x];
if ($count == 0) {
continue;
}
$type = qa_get_badge_type($x);
$types = $type['slug'];
$typed = $type['name'];
$output .= '<li class="badge-medal ' . $types . '"><i class="icon-badge" title="' . $count . ' ' . $typed . '"></i><span class="badge-pointer badge-' . $types . '-count" title="' . $count . ' ' . $typed . '"> ' . $count . '</span></li>';
}
$output = substr($output, 0, -1);
// lazy remove space
$output .= '</ul>';
return $output;
}
}
function qa_db_user_find_by_handle($handle)
{
return qa_db_read_all_values(qa_db_query_sub('SELECT userid FROM ^users WHERE handle=$', $handle));
}
function check_badges($uid)
{
$medals = qa_db_read_all_values(qa_db_query_sub('SELECT user_id FROM ^userbadges WHERE user_id=#', $uid));
$badges = array('medalist', 'champion', 'olympian');
foreach ($badges as $badge_slug) {
if (count($medals) >= (int) qa_opt('badge_' . $badge_slug . '_var') && qa_opt('badge_' . $badge_slug . '_enabled') !== '0') {
$result = qa_db_read_one_value(qa_db_query_sub('SELECT badge_slug FROM ^userbadges WHERE user_id=# AND badge_slug=$', $uid, $badge_slug), true);
if ($result == null) {
// not already awarded this badge
$this->award_badge(null, $uid, $badge_slug, true);
// this is a "badge badge"
}
}
}
}
function qa_news_plugin_send_newsletter($news)
{
$users = qa_db_read_all_values(qa_db_query_sub("SELECT userid FROM qa_usermetas WHERE title = \$ AND content = \$", 'newsletter', '1'));
require_once QA_INCLUDE_DIR . 'qa-app-emails.php';
$handles = qa_userids_to_handles($users);
foreach ($users as $userid) {
$handle = $handles[$userid];
if (QA_FINAL_EXTERNAL_USERS) {
$email = qa_get_user_email($userid);
} else {
$useraccount = qa_db_select_with_pending(qa_db_user_account_selectspec($userid, true));
$email = @$useraccount['email'];
}
qa_send_email(array('fromemail' => qa_opt('from_email'), 'fromname' => qa_opt('site_title'), 'toemail' => $email, 'toname' => $handle, 'subject' => qa_opt('site_title') . ' ' . qa_lang('newsletter/newsletter'), 'body' => $news, 'html' => true));
}
error_log('Q2A Newsletter Sent on ' . date('M j, Y \\a\\t H\\:i\\:s'));
}
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));
}
}
}
function get_expert_question_for_user($uid)
{
qa_db_query_sub('CREATE TABLE IF NOT EXISTS ^postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL,
meta_key varchar(255) DEFAULT \'\',
meta_value longtext,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=MyISAM DEFAULT CHARSET=utf8');
$questions = qa_db_read_all_values(qa_db_query_sub("SELECT ^posts.postid FROM ^postmeta, ^posts WHERE ^postmeta.meta_key='is_expert_question' AND ^postmeta.post_id=^posts.postid AND ^posts.userid=#", $uid), true);
return $questions;
}
function ra_user_followers($handle)
{
$userid = qa_handle_to_userid($handle);
$followers = qa_db_read_all_values(qa_db_query_sub('SELECT ^users.handle FROM ^userfavorites, ^users WHERE (^userfavorites.userid = ^users.userid and ^userfavorites.entityid = #) and ^userfavorites.entitytype = "U" LIMIT 12', $userid));
if (count($followers)) {
$output = '<div class="user-followsers widget">';
$output .= '<h3 class="widget-title">' . _ra_lang('Followers') . '<span class="counts">' . count($followers) . '</span></h3>';
$output .= '<ul class="user-followers clearfix">';
foreach ($followers as $user) {
$id = qa_handle_to_userid($user);
$output .= '<li><div class="avatar" data-handle="' . $user . '" data-id="' . $id . '"><a href="' . qa_path_html('user/' . $user) . '"><img src="' . ra_get_avatar($user, 50, false) . '" /></a></div></li>';
}
$output .= '</ul>';
$output .= '</div>';
echo $output;
}
return;
}
function badge_notify()
{
$userid = qa_get_logged_in_userid();
qa_db_query_sub('CREATE TABLE IF NOT EXISTS ^userbadges (' . 'awarded_at DATETIME NOT NULL,' . 'user_id INT(11) NOT NULL,' . 'notify TINYINT DEFAULT 0 NOT NULL,' . 'object_id INT(10),' . 'badge_slug VARCHAR (64) CHARACTER SET ascii DEFAULT \'\',' . 'id INT(11) NOT NULL AUTO_INCREMENT,' . 'PRIMARY KEY (id)' . ') ENGINE=MyISAM DEFAULT CHARSET=utf8');
$result = qa_db_read_all_values(qa_db_query_sub('SELECT badge_slug FROM ^userbadges WHERE user_id=# AND notify>=1', $userid));
if (count($result) > 0) {
$notice = '<div class="notify-container">';
if (count($result) == 1) {
$slug = $result[0];
$badge_name = qa_lang('badges/' . $slug);
if (!qa_opt('badge_' . $slug . '_name')) {
qa_opt('badge_' . $slug . '_name', $badge_name);
}
$name = qa_opt('badge_' . $slug . '_name');
$notice .= '<div class="badge-notify notify">' . qa_lang('badges/badge_notify') . "'" . $name . '\' ' . qa_lang('badges/badge_notify_profile_pre') . '<a href="' . qa_path_html((QA_FINAL_EXTERNAL_USERS ? qa_path_to_root() : '') . 'user/' . qa_get_logged_in_handle(), array('tab' => 'badges'), qa_opt('site_url')) . '">' . qa_lang('badges/badge_notify_profile') . '</a><div class="notify-close" onclick="jQuery(this).parent().slideUp(\'slow\')">x</div></div>';
} else {
$number_text = count($result) > 2 ? str_replace('#', count($result) - 1, qa_lang('badges/badge_notify_multi_plural')) : qa_lang('badges/badge_notify_multi_singular');
$slug = $result[0];
$badge_name = qa_lang('badges/' . $slug);
if (!qa_opt('badge_' . $slug . '_name')) {
qa_opt('badge_' . $slug . '_name', $badge_name);
}
$name = qa_opt('badge_' . $slug . '_name');
$notice .= '<div class="badge-notify notify">' . qa_lang('badges/badge_notify') . "'" . $name . '\' ' . $number_text . ' ' . qa_lang('badges/badge_notify_profile_pre') . '<a href="' . qa_path_html('user/' . qa_get_logged_in_handle(), array('tab' => 'badges'), qa_opt('site_url')) . '">' . qa_lang('badges/badge_notify_profile') . '</a><div class="notify-close" onclick="jQuery(this).parent().slideUp(\'slow\')">x</div></div>';
}
$notice .= '</div>';
// remove notification flag
qa_db_query_sub('UPDATE ^userbadges SET notify=0 WHERE user_id=# AND notify>=1', $userid);
$this->badge_notice = $notice;
}
}
function qa_db_has_blobs_in_db()
{
return count(qa_db_read_all_values(qa_db_query_sub('SELECT blobid FROM ^blobs WHERE content IS NOT NULL LIMIT 1'))) ? true : false;
}
function qa_db_posts_get_for_deleting($type, $startpostid = 0, $limit = null)
{
$limitsql = isset($limit) ? ' ORDER BY ^posts.postid LIMIT ' . (int) $limit : '';
return qa_db_read_all_values(qa_db_query_sub("SELECT ^posts.postid FROM ^posts LEFT JOIN ^posts AS child ON child.parentid=^posts.postid WHERE ^posts.type=\$ AND ^posts.postid>=# AND child.postid IS NULL" . $limitsql, $type . '_HIDDEN', $startpostid));
}
function qa_post_check_by_fb_id($fbid)
{
return qa_db_read_all_values(qa_db_query_sub('SELECT qa_post_id FROM ^post_facebook_id WHERE qa_post_facebook_id=$', $fbid));
}
function qa_db_list_tables_lc()
{
$tables = qa_db_read_all_values(qa_db_query_raw('SHOW TABLES'));
foreach ($tables as $key => $table) {
$tables[$key] = strtolower($table);
}
return $tables;
}
function qa_db_posttags_get_post_wordids($postid)
{
return qa_db_read_all_values(qa_db_query_sub('SELECT wordid FROM ^posttags WHERE postid=#', $postid));
}
function qa_db_posts_get_userids($postids)
{
if (count($postids)) {
return qa_db_read_all_values(qa_db_query_sub("SELECT DISTINCT userid FROM ^posts WHERE postid IN (#) AND userid IS NOT NULL", $postids));
} else {
return array();
}
}
function qa_db_user_find_by_email_or_oemail__open($email)
{
// Return the ids of all users in the database which match $email (should be one or none)
// Note: we're not verifying the confirmed flag here - all emails should be considered
if (empty($email)) {
return array();
}
return qa_db_read_all_values(qa_db_query_sub('SELECT userid FROM ^users WHERE email=$ or oemail=$', $email, $email));
}
function qa_db_uservoteflag_user_get($userid)
{
return qa_db_read_all_values(qa_db_query_sub('SELECT postid FROM ^uservotes WHERE userid=# AND (vote!=0) OR (flag!=0)', $userid));
}
/**
* Return an array of the names of all tables in the Q2A database.
*/
function qa_db_list_tables()
{
return qa_db_read_all_values(qa_db_query_raw('SHOW TABLES'));
}
function UpdateVote($newevent, $postid, $userid, $params, $eventname, $value)
{
$effecteduserid = $this->GetUseridFromPost($postid);
$posts = qa_db_read_all_values(qa_db_query_sub('SELECT params FROM ^ra_userevent WHERE postid=$ AND event=$', $postid, $newevent));
if (!isset($effecteduserid)) {
return;
}
// post from anonymous user
if (count($posts) == 0) {
// Add New Event
if ($eventname != 'q_vote_nil' && $eventname != 'a_vote_nil' && $eventname != 'unfavorite') {
$question = $this->GetQuestion($params);
$params['qtitle'] = $question['title'];
$params['qid'] = $question['postid'];
$params['newvotes'] = $value;
$params[$eventname] = 1;
$this->AddEvent($postid, $userid, $effecteduserid, $params, $newevent);
if (qw_check_pref_for_event($effecteduserid, $newevent, $all_preferences)) {
qw_do_action('user_event_' . $newevent, $postid, $userid, $effecteduserid, $params, $newevent);
}
}
} else {
$postparams = json_decode($posts[0], true);
if ($eventname == 'q_vote_nil' || $eventname == 'a_vote_nil') {
$netvotes = $this->GetVotesFromPost($postid);
$params['newvotes'] = $netvotes;
$diffrence = (int) $postparams['newvotes'] - (int) $netvotes;
switch ($eventname) {
case 'q_vote_nil':
if ($diffrence == 1) {
//upvote cancelled
$params['q_vote_up'] = (int) $postparams['q_vote_up'] - 1;
} elseif ($diffrence == -1) {
//downvote cancelled
$params['q_vote_down'] = (int) $postparams['q_vote_down'] - 1;
}
break;
case 'a_vote_nil':
if ($diffrence == 1) {
//upvote cancelled
$params['a_vote_up'] = (int) $postparams['a_vote_up'] - 1;
} elseif ($diffrence == -1) {
//downvote cancelled
$params['a_vote_down'] = (int) $postparams['a_vote_down'] - 1;
}
break;
}
} else {
if (isset($postparams[$eventname]) && ($eventname == 'favorite' || $eventname == 'unfavorite')) {
$params[$eventname] = (int) $postparams[$eventname] + $value;
} else {
$params[$eventname] = (int) $postparams[$eventname] + 1;
$params['newvotes'] = (int) $postparams['newvotes'] + $value;
}
}
foreach ($postparams as $key => $value) {
if (!isset($params[$key])) {
$params[$key] = $value;
}
}
$paramstring = $this->ParamToString($params);
qa_db_query_sub("UPDATE ^ra_userevent SET datetime=NOW(), userid=\$, effecteduserid=\$, postid=\$, event=\$, params=\$ WHERE postid=\$ AND event=\$", $userid, $effecteduserid, $postid, $newevent, $paramstring, $postid, $newevent);
if (qw_check_pref_for_event($effecteduserid, $newevent, $all_preferences)) {
qw_do_action('user_event_' . $newevent, $postid, $userid, $effecteduserid, $params, $newevent);
}
}
}
function qa_db_get_ip_visible_postids($ip)
{
return qa_db_read_all_values(qa_db_query_sub("SELECT postid FROM ^posts WHERE createip=INET_ATON(\$) AND type IN ('Q', 'A', 'C', 'Q_QUEUED', 'A_QUEUED', 'C_QUEUED')", $ip));
}
function qa_check_all_users_badges()
{
$awarded = 0;
$users;
$temp = qa_db_query_sub('SELECT * FROM ^posts');
while (($post = qa_db_read_one_assoc($temp, true)) !== null) {
if (!$post['userid']) {
continue;
}
$user = '******' . $post['userid'];
$pid = $post['postid'];
$pt = $post['type'];
// get post count
if (isset($users[$user]) && isset($users[$user][$pt])) {
$users[$user][$pt]++;
} else {
$users[$user][$pt] = 1;
}
// get post votes
if ($post['netvotes'] != 0) {
$users[$user][$pt . 'votes'][] = array('id' => $pid, 'votes' => (int) $post['netvotes'], 'parentid' => $post['parentid'], 'created' => $post['created']);
}
// get post views
if ($post['views']) {
$users[$user]['views'][] = array('id' => $pid, 'views' => $post['views']);
}
}
//votes received and given out
$voter = qa_db_read_all_assoc(qa_db_query_sub('SELECT userid,qupvotes,qdownvotes,aupvotes,adownvotes,upvoteds FROM ^userpoints'));
foreach ($voter as $idx => $votes) {
$user = '******' . $votes['userid'];
// votes
$users[$user]['votes'] = (int) $votes['qupvotes'] + (int) $votes['qdownvotes'] + (int) $votes['aupvotes'] + (int) $votes['adownvotes'];
// voteds
$users[$user]['voted'] = (int) $votes['upvoteds'];
unset($voter[$idx]);
}
// flags
$flag_result = qa_db_read_all_values(qa_db_query_sub('SELECT userid FROM ^uservotes WHERE flag > 0'));
foreach ($flag_result as $idx => $flag) {
$user = '******' . $flag;
// get flag count
if (isset($users[$user]) && isset($users[$user]['flags'])) {
$users[$user]['flags']++;
} else {
$users[$user]['flags'] = 1;
}
unset($flag_result[$idx]);
}
// per user loop
foreach ($users as $user => $data) {
$uid = (int) substr($user, 4);
// bulk posts
$badges = array('Q' => array('asker', 'questioner', 'inquisitor'), 'A' => array('answerer', 'lecturer', 'preacher'), 'C' => array('commenter', 'commentator', 'annotator'));
foreach ($badges as $pt => $slugs) {
if (!isset($data[$pt])) {
continue;
}
$awarded += count(qa_badge_award_check($slugs, $data[$pt], $uid, null, 0));
}
// nice Q&A
$badges = array('nice_question', 'good_question', 'great_question', 'nice_answer', 'good_answer', 'great_answer');
if ($this->badge_activated($badges)) {
$badges = array('Q' => array('nice_question', 'good_question', 'great_question'), 'A' => array('nice_answer', 'good_answer', 'great_answer'));
foreach ($badges as $pt => $slugs) {
foreach ($slugs as $badge_slug) {
if (!isset($data[$pt . 'votes'])) {
continue;
}
foreach ($data[$pt . 'votes'] as $idv) {
// poll plugin integration
if ($pt == 'A' && qa_opt('poll_enable')) {
$poll = qa_db_read_one_value(qa_db_query_sub('SELECT meta_value FROM ^postmeta WHERE post_id=# AND meta_key=$', $idv['id'], 'is_poll'), true);
if ($poll) {
continue;
}
}
if ((int) $idv['votes'] >= (int) qa_opt('badge_' . $badge_slug . '_var') && qa_opt('badge_' . $badge_slug . '_enabled') !== '0') {
$result = qa_db_read_one_value(qa_db_query_sub('SELECT badge_slug FROM ^userbadges WHERE user_id=# AND object_id=# AND badge_slug=$', $uid, $idv['id'], $badge_slug), true);
if ($result == null) {
// not already awarded this badge
$this->award_badge($idv['id'], $uid, $badge_slug, false, true);
$awarded++;
}
// old question answer vote checks
if ($pt == 'A') {
$qid = $idv['parentid'];
$create = strtotime($idv['created']);
$parent = $this->get_post_data($qid);
$pcreate = strtotime($parent['created']);
$diff = round(abs($pcreate - $create) / 60 / 60 / 24);
$badge_slug2 = $badge_slug . '_old';
if ($diff >= (int) qa_opt('badge_' . $badge_slug2 . '_var') && qa_opt('badge_' . $badge_slug2 . '_enabled') !== '0') {
$result = qa_db_read_one_value(qa_db_query_sub('SELECT badge_slug FROM ^userbadges WHERE user_id=# AND object_id=# AND badge_slug=$', $uid, $idv['id'], $badge_slug2), true);
if ($result == null) {
// not already awarded for this answer
$this->award_badge($idv['id'], $uid, $badge_slug2);
$awarded++;
}
}
}
}
}
}
}
}
// votes per user badges
if (isset($data['votes'])) {
$votes = $data['votes'];
$badges = array('voter', 'avid_voter', 'devoted_voter');
$awarded += count(qa_badge_award_check($badges, $votes, $uid, null, 0));
}
// voted per user badges
if (isset($data['voted'])) {
$votes = $data['voted'];
$badges = array('liked', 'loved', 'revered');
$awarded += count(qa_badge_award_check($badges, $votes, $uid, null, 0));
}
// views per post badges
if (isset($data['views'])) {
$badges = array('notable_question', 'popular_question', 'famous_question');
foreach ($data['views'] as $idv) {
$awarded += count(qa_badge_award_check($badges, $idv['views'], $uid, $idv['id'], 0));
}
}
// flags per user
if (isset($data['flags'])) {
$flags = $data['flags'];
$badges = array('watchdog', 'bloodhound', 'pitbull');
$awarded += count(qa_badge_award_check($badges, $flags, $uid, null, 0));
}
unset($users[$user]);
}
// selects, selecteds
$badges = array('gifted', 'wise', 'enlightened', 'grateful', 'respectful', 'reverential');
if ($this->badge_activated($badges)) {
$selects = qa_db_read_all_assoc(qa_db_query_sub('SELECT aselects, aselecteds, userid FROM ^userpoints'));
foreach ($selects as $idx => $s) {
$uid = $s['userid'];
if (isset($s['aselecteds'])) {
$count = $s['aselecteds'];
$badges = array('gifted', 'wise', 'enlightened');
$awarded += count(qa_badge_award_check($badges, $count, $uid, null, 0));
}
if (isset($s['aselects'])) {
$count = $s['aselects'];
$badges = array('grateful', 'respectful', 'reverential');
$awarded += count(qa_badge_award_check($badges, $count, $uid, null, 0));
}
unset($selects[$idx]);
}
}
// achievements
$badges = array('dedicated', 'devoted', 'zealous', 'visitor', 'trouper', 'veteran', 'regular', 'old_timer', 'ancestor', 'reader', 'avid_reader', 'devoted_reader');
if ($this->badge_activated($badges)) {
$userq = qa_db_query_sub('SELECT user_id AS uid,questions_read AS qr,oldest_consec_visit AS ocv,longest_consec_visit AS lcv,total_days_visited AS tdv,last_visit AS lv,first_visit AS fv,posts_edited AS pe FROM ^achievements');
while (($user = qa_db_read_one_assoc($userq, true)) !== null) {
$uid = $user['uid'];
// edits
$count = $user['pe'];
$badges = array('editor', 'copy_editor', 'senior_editor');
$awarded += count(qa_badge_award_check($badges, $count, $uid, null, 0));
// on-sign-in badges
// check lapse in days since last visit
// using julian days
$todayj = GregorianToJD(date('n'), date('j'), date('Y'));
$last_visit = strtotime($user['lv']);
$lastj = GregorianToJD(date('n', $last_visit), date('j', $last_visit), date('Y', $last_visit));
$last_diff = $todayj - $lastj;
$first_visit = strtotime($user['fv']);
$first_visitj = GregorianToJD(date('n', $first_visit), date('j', $first_visit), date('Y', $first_visit));
$first_visit_diff = $todayj - $first_visitj;
$badges = array('dedicated', 'devoted', 'zealous');
$awarded += count(qa_badge_award_check($badges, $user['lcv'], $uid, null, 0));
$badges = array('visitor', 'trouper', 'veteran');
$awarded += count(qa_badge_award_check($badges, $user['tdv'], $uid, null, 0));
$badges = array('regular', 'old_timer', 'ancestor');
$awarded += count(qa_badge_award_check($badges, $first_visit_diff, $uid, null, 0));
// views
$badges = array('reader', 'avid_reader', 'devoted_reader');
$awarded += count(qa_badge_award_check($badges, $user['qr'], $uid, null, 0));
}
}
// points
$badges = array('100_club', '1000_club', '10000_club');
if ($this->badge_activated($badges)) {
$userq = qa_db_query_sub('SELECT userid, points FROM ^userpoints');
while (($user = qa_db_read_one_assoc($userq, true)) !== null) {
$awarded += count(qa_badge_award_check($badges, $user['points'], $user['userid'], null, 0));
}
}
if (!QA_FINAL_EXTERNAL_USERS) {
// verified
$badges = array('verified');
if ($this->badge_activated($badges)) {
$userq = qa_db_query_sub('SELECT userid, flags FROM ^users WHERE flags&#', QA_USER_FLAGS_EMAIL_CONFIRMED);
while (($user = qa_db_read_one_assoc($userq, true)) !== null) {
$awarded += count(qa_badge_award_check($badges, false, $user['userid'], null, 0));
}
}
// profile stuff
$badges = array('avatar', 'profiler');
if ($this->badge_activated($badges)) {
$userq = qa_db_query_sub('SELECT userid FROM ^users');
while (($userid = qa_db_read_one_value($userq, true)) !== null) {
list($useraccount, $userprofile, $userfields) = qa_db_select_with_pending(qa_db_user_account_selectspec($userid, true), qa_db_user_profile_selectspec($userid, true), qa_db_userfields_selectspec());
// avatar badge
if (qa_opt('avatar_allow_upload') && isset($useraccount['avatarblobid'])) {
$badges = array('avatar');
$awarded += count(qa_badge_award_check($badges, false, $userid, null, 0));
}
// profile completion
$missing = false;
foreach ($userfields as $userfield) {
if (!isset($userprofile[$userfield['title']]) || @$userprofile[$userfield['title']] === '') {
$missing = true;
break;
}
}
if (!$missing) {
$badges = array('profiler');
$awarded += count(qa_badge_award_check($badges, false, $userid, null, 0));
}
}
}
}
// rebuild badges from other plugins - experimental! - $module->custom_badges_rebuild() returns number of badges awarded.
$moduletypes = qa_list_module_types();
foreach ($moduletypes as $moduletype) {
$modulenames = qa_list_modules($moduletype);
foreach ($modulenames as $modulename) {
$module = qa_load_module($moduletype, $modulename);
if (method_exists($module, 'custom_badges_rebuild')) {
$awarded += $module->custom_badges_rebuild();
}
}
}
// badges
$badges = array('medalist', 'champion', 'olympian');
if ($this->badge_activated($badges)) {
$badgelist = qa_db_read_all_values(qa_db_query_sub('SELECT user_id FROM ^userbadges'));
$users = array();
foreach ($badgelist as $idx => $medal) {
$user = '******' . $medal;
// get badge count
if (isset($users[$user]) && isset($users[$user]['medals'])) {
$users[$user]['medals']++;
} else {
$users[$user]['medals'] = 1;
}
unset($badgelist[$idx]);
}
foreach ($users as $user => $data) {
$uid = (int) substr($user, 4);
// check badges
if (isset($data['medals'])) {
$uid = (int) substr($user, 4);
$count = $data['medals'];
$awarded += count(qa_badge_award_check($badges, $count, $uid, null, 0));
}
unset($users[$user]);
}
}
// return ok text
return $awarded . ' badge' . ($awarded != 1 ? 's' : '') . ' awarded.';
}
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));
}
}
}
