public function process_request($request) { require_once QA_INCLUDE_DIR . 'qa-app-posts.php'; // smiley replacement regexes $rxSearch = '<(img|a)([^>]+)(src|href)="([^"]+)/wysiwyg-editor/plugins/smiley/images/([^"]+)"'; $rxReplace = '<$1$2$3="$4/wysiwyg-editor/ckeditor/plugins/smiley/images/$5"'; qa_suspend_event_reports(true); // avoid infinite loop // prevent race conditions $locks = array('posts', 'categories', 'users', 'users AS lastusers', 'userpoints', 'words', 'titlewords', 'contentwords', 'tagwords', 'words AS x', 'posttags', 'options'); foreach ($locks as &$tbl) { $tbl = '^' . $tbl . ' WRITE'; } qa_db_query_sub('LOCK TABLES ' . implode(',', $locks)); $sql = 'SELECT postid, title, content FROM ^posts WHERE format="html" ' . 'AND content LIKE "%/wysiwyg-editor/plugins/smiley/images/%" ' . 'AND content RLIKE \'' . $rxSearch . '\' ' . 'LIMIT 5'; $result = qa_db_query_sub($sql); $numPosts = 0; while (($post = qa_db_read_one_assoc($result, true)) !== null) { $newcontent = preg_replace("#{$rxSearch}#", $rxReplace, $post['content']); qa_post_set_content($post['postid'], $post['title'], $newcontent); $numPosts++; } qa_db_query_raw('UNLOCK TABLES'); qa_suspend_event_reports(false); echo $numPosts; }
function qa_db_table_size() { $statuses = qa_db_read_all_assoc(qa_db_query_raw("SHOW TABLE STATUS")); $size = 0; foreach ($statuses as $status) { $size += $status['Data_length'] + $status['Index_length']; } return $size; }
function qa_db_hotness_update($firstpostid, $lastpostid = null, $viewincrement = false) { if (qa_should_update_counts()) { if (!isset($lastpostid)) { $lastpostid = $firstpostid; } $query = 'UPDATE ^posts AS x, (SELECT parents.postid, parents.created AS qcreated, COALESCE(MAX(children.created), parents.created) as acreated, COUNT(children.postid) AS acount, parents.netvotes, parents.views FROM ^posts AS parents LEFT JOIN ^posts AS children ON parents.postid=children.parentid AND children.type=\'A\' WHERE parents.postid>=# AND parents.postid<=# AND parents.type=\'Q\' GROUP BY postid) AS a SET x.hotness=(' . '((TO_DAYS(a.qcreated)-734138)*86400.0+TIME_TO_SEC(a.qcreated))*# + ' . '((TO_DAYS(a.acreated)-734138)*86400.0+TIME_TO_SEC(a.acreated))*# + ' . '(a.acount+0.0)*# + ' . '(a.netvotes+0.0)*# + ' . '(a.views+0.0+#)*#' . ')' . ($viewincrement ? ', x.views=x.views+1, x.lastviewip=INET_ATON($)' : '') . ' WHERE x.postid=a.postid'; // Additional multiples based on empirical analysis of activity on Q2A meta site to give approx equal influence for all factors $arguments = array($firstpostid, $lastpostid, qa_opt('hot_weight_q_age'), qa_opt('hot_weight_a_age'), qa_opt('hot_weight_answers') * 160000, qa_opt('hot_weight_votes') * 160000, $viewincrement ? 1 : 0, qa_opt('hot_weight_views') * 4000); if ($viewincrement) { $arguments[] = qa_remote_ip_address(); } qa_db_query_raw(qa_db_apply_sub($query, $arguments)); } }
public function process_request($request) { $requestparts = explode('/', qa_request()); $slugs = array_slice($requestparts, 1); $countslugs = count($slugs); $userid = qa_get_logged_in_userid(); $start = qa_get_start(); $count = qa_opt_if_loaded('page_size_activity'); $totalcount = qa_opt('cache_qcount'); $qspec = qa_db_posts_basic_selectspec($userid, false); qa_db_add_selectspec_opost($qspec, 'ra', false, false); qa_db_add_selectspec_ousers($qspec, 'rau', 'raup'); $qspec['source'] .= " JOIN (SELECT questionid, childid FROM ^homepage ORDER BY ^homepage.updated DESC) AS rcaq ON ^posts.postid=rcaq.questionid" . " LEFT JOIN ^posts AS ra ON childid=ra.postid" . (QA_FINAL_EXTERNAL_USERS ? "" : " LEFT JOIN ^users AS rau ON ra.userid=rau.userid") . " LEFT JOIN ^userpoints AS raup ON ra.userid=raup.userid LIMIT #,#"; array_push($qspec['columns'], 'childid'); array_push($qspec['arguments'], $start, $count); $qspec['sortdesc'] = 'otime'; $query = 'SELECT '; foreach ($qspec['columns'] as $columnas => $columnfrom) { $query .= $columnfrom . (is_int($columnas) ? '' : ' AS ' . $columnas) . ', '; } $query = qa_db_apply_sub(substr($query, 0, -2) . (strlen(@$qspec['source']) ? ' FROM ' . $qspec['source'] : ''), @$qspec['arguments']); $results = qa_db_read_all_assoc(qa_db_query_raw($query)); qa_db_post_select($results, $qspec); list($categories, $categoryid) = qa_db_select_with_pending(qa_db_category_nav_selectspec($slugs, false, false, true), $countslugs ? qa_db_slugs_to_category_id_selectspec($slugs) : null); $questions = qa_any_sort_and_dedupe($results); // $questions=qa_any_sort_and_dedupe(array_merge($recentquestions,$recentanswers)); $pagesize = qa_opt('page_size_home'); if ($countslugs) { if (!isset($categoryid)) { return include QA_INCLUDE_DIR . 'qa-page-not-found.php'; } $categorytitlehtml = qa_html($categories[$categoryid]['title']); $sometitle = qa_lang_html_sub('main/recent_qs_as_in_x', $categorytitlehtml); $nonetitle = qa_lang_html_sub('main/no_questions_in_x', $categorytitlehtml); } else { $sometitle = qa_lang_html('main/recent_qs_as_title'); $nonetitle = qa_lang_html('main/no_questions_found'); } require_once QA_INCLUDE_DIR . 'qa-app-q-list.php'; $qa_content = qa_q_list_page_content($questions, $pagesize, $start, $totalcount, $sometitle, $nonetitle, $categories, $categoryid, true, qa_opt('eql_homepage_url'), qa_opt('feed_for_qa') ? qa_opt('eql_homepage_url') : null, count($questions) < $pagesize ? qa_html_suggest_ask($categoryid) : qa_html_suggest_qs_tags(qa_using_tags(), qa_category_path_request($categories, $categoryid)), null, null); return $qa_content; }
function qa_db_limits_get($userid, $ip, $action) { $selects = array(); $arguments = array(); if (isset($userid)) { $selects[] = "(SELECT 'user' AS limitkey, period, count FROM ^userlimits WHERE userid=\$ AND action=\$)"; $arguments[] = $userid; $arguments[] = $action; } if (isset($ip)) { $selects[] = "(SELECT 'ip' AS limitkey, period, count FROM ^iplimits WHERE ip=COALESCE(INET_ATON(\$), 0) AND action=\$)"; $arguments[] = $ip; $arguments[] = $action; } if (count($selects)) { $query = qa_db_apply_sub(implode(' UNION ALL ', $selects), $arguments); return qa_db_read_all_assoc(qa_db_query_raw($query), 'limitkey'); } else { return array(); } }
function qa_db_table_size() { if (defined('QA_MYSQL_USERS_PREFIX')) { // check if one of the prefixes is a prefix itself of the other if (stripos(QA_MYSQL_USERS_PREFIX, QA_MYSQL_TABLE_PREFIX) === 0) { $prefixes = array(QA_MYSQL_TABLE_PREFIX); } elseif (stripos(QA_MYSQL_TABLE_PREFIX, QA_MYSQL_USERS_PREFIX) === 0) { $prefixes = array(QA_MYSQL_USERS_PREFIX); } else { $prefixes = array(QA_MYSQL_TABLE_PREFIX, QA_MYSQL_USERS_PREFIX); } } else { $prefixes = array(QA_MYSQL_TABLE_PREFIX); } $size = 0; foreach ($prefixes as $prefix) { $statuses = qa_db_read_all_assoc(qa_db_query_raw("SHOW TABLE STATUS LIKE '" . $prefix . "%'")); foreach ($statuses as $status) { $size += $status['Data_length'] + $status['Index_length']; } } return $size; }
function qa_db_check_tables() { qa_db_query_raw('UNLOCK TABLES'); // we could be inside a lock tables block $version = qa_db_read_one_value(qa_db_query_raw('SELECT VERSION()')); if ((double) $version < 4.1) { qa_fatal_error('MySQL version 4.1 or later is required - you appear to be running MySQL ' . $version); } $definitions = qa_db_table_definitions(); $missing = qa_db_missing_tables($definitions); if (count($missing) == count($definitions)) { return 'none'; } else { if (!isset($missing['options'])) { $version = qa_db_get_db_version(); if (isset($version) && $version < QA_DB_VERSION_CURRENT) { return 'old-version'; } } if (count($missing)) { if (defined('QA_MYSQL_USERS_PREFIX')) { // special case if two installations sharing users $datacount = 0; $datamissing = 0; foreach ($definitions as $rawname => $definition) { if (qa_db_add_table_prefix($rawname) == QA_MYSQL_TABLE_PREFIX . $rawname) { $datacount++; if (isset($missing[$rawname])) { $datamissing++; } } } if ($datacount == $datamissing && $datamissing == count($missing)) { return 'non-users-missing'; } } return 'table-missing'; } else { foreach ($definitions as $table => $definition) { if (count(qa_db_missing_columns($table, $definition))) { return 'column-missing'; } } } } return false; }
/** * Return the data specified by each element of $selectspecs, where the keys of the * returned array match the keys of the supplied $selectspecs array. See long comment above. */ function qa_db_multi_select($selectspecs) { if (!count($selectspecs)) { return array(); } // Perform simple queries if the database is local or there are only 0 or 1 selectspecs if (QA_OPTIMIZE_LOCAL_DB || count($selectspecs) <= 1) { $outresults = array(); foreach ($selectspecs as $selectkey => $selectspec) { $outresults[$selectkey] = qa_db_single_select($selectspec); } return $outresults; } // Otherwise, parse columns for each spec to deal with columns without an 'AS' specification foreach ($selectspecs as $selectkey => $selectspec) { $selectspecs[$selectkey]['outcolumns'] = array(); $selectspecs[$selectkey]['autocolumn'] = array(); foreach ($selectspec['columns'] as $columnas => $columnfrom) { if (is_int($columnas)) { $periodpos = strpos($columnfrom, '.'); $columnas = is_numeric($periodpos) ? substr($columnfrom, $periodpos + 1) : $columnfrom; $selectspecs[$selectkey]['autocolumn'][$columnas] = true; } if (isset($selectspecs[$selectkey]['outcolumns'][$columnas])) { qa_fatal_error('Duplicate column name in qa_db_multi_select()'); } $selectspecs[$selectkey]['outcolumns'][$columnas] = $columnfrom; } if (isset($selectspec['arraykey'])) { if (!isset($selectspecs[$selectkey]['outcolumns'][$selectspec['arraykey']])) { qa_fatal_error('Used arraykey not in columns in qa_db_multi_select()'); } } if (isset($selectspec['arrayvalue'])) { if (!isset($selectspecs[$selectkey]['outcolumns'][$selectspec['arrayvalue']])) { qa_fatal_error('Used arrayvalue not in columns in qa_db_multi_select()'); } } } // Work out the full list of columns used $outcolumns = array(); foreach ($selectspecs as $selectspec) { $outcolumns = array_unique(array_merge($outcolumns, array_keys($selectspec['outcolumns']))); } // Build the query based on this full list $query = ''; foreach ($selectspecs as $selectkey => $selectspec) { $subquery = "(SELECT '" . qa_db_escape_string($selectkey) . "'" . (empty($query) ? ' AS selectkey' : ''); foreach ($outcolumns as $columnas) { $subquery .= ', ' . (isset($selectspec['outcolumns'][$columnas]) ? $selectspec['outcolumns'][$columnas] : 'NULL'); if (empty($query) && !isset($selectspec['autocolumn'][$columnas])) { $subquery .= ' AS ' . $columnas; } } if (strlen(@$selectspec['source'])) { $subquery .= ' FROM ' . $selectspec['source']; } $subquery .= ')'; if (strlen($query)) { $query .= ' UNION ALL '; } $query .= qa_db_apply_sub($subquery, @$selectspec['arguments']); } // Perform query and extract results $rawresults = qa_db_read_all_assoc(qa_db_query_raw($query)); $outresults = array(); foreach ($selectspecs as $selectkey => $selectspec) { $outresults[$selectkey] = array(); } foreach ($rawresults as $rawresult) { $selectkey = $rawresult['selectkey']; $selectspec = $selectspecs[$selectkey]; $keepresult = array(); foreach ($selectspec['outcolumns'] as $columnas => $columnfrom) { $keepresult[$columnas] = $rawresult[$columnas]; } if (isset($selectspec['arraykey'])) { $outresults[$selectkey][$keepresult[$selectspec['arraykey']]] = $keepresult; } else { $outresults[$selectkey][] = $keepresult; } } // Post-processing to apply various stuff include sorting request, since we can't rely on ORDER BY due to UNION foreach ($selectspecs as $selectkey => $selectspec) { qa_db_post_select($outresults[$selectkey], $selectspec); } // Return results return $outresults; }
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)); } } }
qa_report_event('page_enter', qa_get_logged_in_userid(), qa_get_logged_in_handle(), qa_cookie_get(), array('params' => $_SERVER['QUERY_STRING'], 'path' => $_SERVER['SCRIPT_NAME'])); require_once QA_INCLUDE_DIR . 'qa-db-recalc.php'; require_once QA_INCLUDE_DIR . 'qa-app-admin.php'; require_once QA_INCLUDE_DIR . 'qa-db-admin.php'; // Check admin privileges (do late to allow one DB query) if (!qa_admin_check_privileges($qa_content)) { return $qa_content; } // Get the information to display $qcount = (int) qa_opt('cache_qcount'); $qcount_anon = qa_db_count_posts('Q', false); $acount = (int) qa_opt('cache_acount'); $acount_anon = qa_db_count_posts('A', false); $ccount = (int) qa_opt('cache_ccount'); $ccount_anon = qa_db_count_posts('C', false); $mysqlversion = qa_db_read_one_value(qa_db_query_raw('SELECT VERSION()')); // Prepare content for theme $qa_content = qa_content_prepare(); $qa_content['title'] = qa_lang_html('admin/admin_title') . ' - ' . qa_lang_html('admin/stats_title'); $qa_content['error'] = qa_admin_page_error(); $qa_content['form'] = array('style' => 'wide', 'fields' => array('q2a_version' => array('label' => qa_lang_html('admin/q2a_version'), 'value' => qa_html(QA_VERSION)), 'q2a_latest' => array('label' => qa_lang_html('admin/q2a_latest_version'), 'type' => 'custom', 'html' => '<IFRAME SRC="http://www.question2answer.org/question2answer-latest.php?version=' . urlencode(QA_VERSION) . '&language=' . urlencode(qa_opt('site_language')) . '" WIDTH="100" HEIGHT="16" STYLE="vertical-align:middle; border:0; background:transparent;" allowTransparency="true" SCROLLING="no" FRAMEBORDER="0"></IFRAME>'), 'db_version' => array('label' => qa_lang_html('admin/q2a_db_version'), 'value' => qa_html(qa_opt('db_version'))), 'php_version' => array('label' => qa_lang_html('admin/php_version'), 'value' => qa_html(phpversion())), 'mysql_version' => array('label' => qa_lang_html('admin/mysql_version'), 'value' => qa_html($mysqlversion)), 'break0' => array('type' => 'blank'), 'qcount' => array('label' => qa_lang_html('admin/total_qs'), 'value' => qa_html(number_format($qcount))), 'qcount_users' => array('label' => qa_lang_html('admin/from_users'), 'value' => qa_html(number_format($qcount - $qcount_anon))), 'qcount_anon' => array('label' => qa_lang_html('admin/from_anon'), 'value' => qa_html(number_format($qcount_anon))), 'break1' => array('type' => 'blank'), 'acount' => array('label' => qa_lang_html('admin/total_as'), 'value' => qa_html(number_format($acount))), 'acount_users' => array('label' => qa_lang_html('admin/from_users'), 'value' => qa_html(number_format($acount - $acount_anon))), 'acount_anon' => array('label' => qa_lang_html('admin/from_anon'), 'value' => qa_html(number_format($acount_anon))), 'break2' => array('type' => 'blank'), 'ccount' => array('label' => qa_lang_html('admin/total_cs'), 'value' => qa_html(number_format($ccount))), 'ccount_users' => array('label' => qa_lang_html('admin/from_users'), 'value' => qa_html(number_format($ccount - $ccount_anon))), 'ccount_anon' => array('label' => qa_lang_html('admin/from_anon'), 'value' => qa_html(number_format($ccount_anon))), 'break3' => array('type' => 'blank'), 'users' => array('label' => qa_lang_html('admin/users_registered'), 'value' => QA_FINAL_EXTERNAL_USERS ? '' : qa_html(number_format(qa_db_count_users()))), 'users_active' => array('label' => qa_lang_html('admin/users_active'), 'value' => qa_html(number_format((int) qa_opt('cache_userpointscount')))), 'users_posted' => array('label' => qa_lang_html('admin/users_posted'), 'value' => qa_html(number_format(qa_db_count_active_users('posts')))), 'users_voted' => array('label' => qa_lang_html('admin/users_voted'), 'value' => qa_html(number_format(qa_db_count_active_users('uservotes')))))); if (QA_FINAL_EXTERNAL_USERS) { unset($qa_content['form']['fields']['users']); } else { unset($qa_content['form']['fields']['users_active']); } foreach ($qa_content['form']['fields'] as $index => $field) { if (empty($field['type'])) { $qa_content['form']['fields'][$index]['type'] = 'static'; } }
function qa_db_points_update_ifuser($userid, $columns) { if (qa_to_override(__FUNCTION__)) { $args = func_get_args(); return qa_call_override(__FUNCTION__, $args); } if (qa_should_update_counts() && isset($userid)) { require_once QA_INCLUDE_DIR . 'app/options.php'; require_once QA_INCLUDE_DIR . 'app/cookies.php'; $calculations = qa_db_points_calculations(); if ($columns === true) { $keycolumns = $calculations; } elseif (empty($columns)) { $keycolumns = array(); } elseif (is_array($columns)) { $keycolumns = array_flip($columns); } else { $keycolumns = array($columns => true); } $insertfields = 'userid, '; $insertvalues = '$, '; $insertpoints = (int) qa_opt('points_base'); $updates = ''; $updatepoints = $insertpoints; foreach ($calculations as $field => $calculation) { $multiple = (int) $calculation['multiple']; if (isset($keycolumns[$field])) { $insertfields .= $field . ', '; $insertvalues .= '@_' . $field . ':=(SELECT ' . $calculation['formula'] . '), '; $updates .= $field . '=@_' . $field . ', '; $insertpoints .= '+(' . (int) $multiple . '*@_' . $field . ')'; } $updatepoints .= '+(' . $multiple . '*' . (isset($keycolumns[$field]) ? '@_' : '') . $field . ')'; } $query = 'INSERT INTO ^userpoints (' . $insertfields . 'points) VALUES (' . $insertvalues . $insertpoints . ') ' . 'ON DUPLICATE KEY UPDATE ' . $updates . 'points=' . $updatepoints . '+bonus'; qa_db_query_raw(str_replace('~', "='" . qa_db_escape_string($userid) . "'", qa_db_apply_sub($query, array($userid)))); // build like this so that a #, $ or ^ character in the $userid (if external integration) isn't substituted if (qa_db_insert_on_duplicate_inserted()) { qa_db_userpointscount_update(); } } }