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_upgrade_query($query) { qa_db_upgrade_progress('Running query: ' . qa_db_apply_sub($query, array()) . ' ...'); qa_db_query_sub($query); }
function qa_db_category_delete($categoryid) { qa_db_ordered_delete('categories', 'categoryid', $categoryid, qa_db_apply_sub('parentid<=>#', array(qa_db_category_get_parent($categoryid)))); }
/** * 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_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(); } } }