function get_item()
{
if (!isset($this->result)) {
if (!$this->get_sql()) {
return false;
}
// Query database
$sql = $this->db->sql_build_query('SELECT', $this->sql);
$this->result = $this->db->sql_query_limit($sql, $this->num_items);
}
return $this->db->sql_fetchrow($this->result);
}
/**
* Updates wordlist and wordmatch tables when a message is posted or changed
*
* @param string $mode Contains the post mode: edit, post, reply, quote
* @param int $post_id The id of the post which is modified/created
* @param string &$message New or updated post content
* @param string &$subject New or updated post subject
* @param int $poster_id Post author's user id
* @param int $forum_id The id of the forum in which the post is located
*/
public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id)
{
if ($mode == 'edit') {
$this->sphinx->UpdateAttributes($this->indexes, array('forum_id', 'poster_id'), array((int) $post_id => array((int) $forum_id, (int) $poster_id)));
} else {
if ($mode != 'post' && $post_id) {
// Update topic_last_post_time for full topic
$sql_array = array('SELECT' => 'p1.post_id', 'FROM' => array(POSTS_TABLE => 'p1'), 'LEFT_JOIN' => array(array('FROM' => array(POSTS_TABLE => 'p2'), 'ON' => 'p1.topic_id = p2.topic_id')), 'WHERE' => 'p2.post_id = ' . (int) $post_id);
$sql = $this->db->sql_build_query('SELECT', $sql_array);
$result = $this->db->sql_query($sql);
$post_updates = array();
$post_time = time();
while ($row = $this->db->sql_fetchrow($result)) {
$post_updates[(int) $row['post_id']] = array($post_time);
}
$this->db->sql_freeresult($result);
if (sizeof($post_updates)) {
$this->sphinx->UpdateAttributes($this->indexes, array('topic_last_post_time'), $post_updates);
}
}
}
}
/**
* Handles authentication when downloading attachments from a post or topic
*
* @param \src\db\driver\driver_interface $db The database object
* @param \src\auth\auth $auth The authentication object
* @param int $topic_id The id of the topic that we are downloading from
*
* @return null
*/
function src_download_handle_forum_auth($db, $auth, $topic_id)
{
$sql_array = array('SELECT' => 't.topic_visibility, t.forum_id, f.forum_name, f.forum_password, f.parent_id', 'FROM' => array(TOPICS_TABLE => 't', FORUMS_TABLE => 'f'), 'WHERE' => 't.topic_id = ' . (int) $topic_id . '
AND t.forum_id = f.forum_id');
$sql = $db->sql_build_query('SELECT', $sql_array);
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
if ($row && $row['topic_visibility'] != ITEM_APPROVED && !$auth->acl_get('m_approve', $row['forum_id'])) {
send_status_line(404, 'Not Found');
trigger_error('ERROR_NO_ATTACHMENT');
} else {
if ($row && $auth->acl_get('u_download') && $auth->acl_get('f_download', $row['forum_id'])) {
if ($row['forum_password']) {
// Do something else ... ?
login_forum_box($row);
}
} else {
send_status_line(403, 'Forbidden');
trigger_error('SORRY_AUTH_VIEW_ATTACH');
}
}
}
/**
* Performs a search on keywords depending on display specific params. You have to run split_keywords() first
*
* @param string $type contains either posts or topics depending on what should be searched for
* @param string $fields contains either titleonly (topic titles should be searched), msgonly (only message bodies should be searched), firstpost (only subject and body of the first post should be searched) or all (all post bodies and subjects should be searched)
* @param string $terms is either 'all' (use query as entered, words without prefix should default to "have to be in field") or 'any' (ignore search query parts and just return all posts that contain any of the specified words)
* @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
* @param string $sort_key is the key of $sort_by_sql for the selected sorting
* @param string $sort_dir is either a or d representing ASC and DESC
* @param string $sort_days specifies the maximum amount of days a post may be old
* @param array $ex_fid_ary specifies an array of forum ids which should not be searched
* @param string $post_visibility specifies which types of posts the user can view in which forums
* @param int $topic_id is set to 0 or a topic id, if it is not 0 then only posts in this topic should be searched
* @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty
* @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
* @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
* @param int $start indicates the first index of the page
* @param int $per_page number of ids each page is supposed to contain
* @return boolean|int total number of results
*/
public function keyword_search($type, $fields, $terms, $sort_by_sql, $sort_key, $sort_dir, $sort_days, $ex_fid_ary, $post_visibility, $topic_id, $author_ary, $author_name, &$id_ary, &$start, $per_page)
{
// No keywords? No posts.
if (empty($this->search_query)) {
return false;
}
// we can't search for negatives only
if (empty($this->must_contain_ids)) {
return false;
}
$must_contain_ids = $this->must_contain_ids;
$must_not_contain_ids = $this->must_not_contain_ids;
$must_exclude_one_ids = $this->must_exclude_one_ids;
sort($must_contain_ids);
sort($must_not_contain_ids);
sort($must_exclude_one_ids);
// generate a search_key from all the options to identify the results
$search_key = md5(implode('#', array(serialize($must_contain_ids), serialize($must_not_contain_ids), serialize($must_exclude_one_ids), $type, $fields, $terms, $sort_days, $sort_key, $topic_id, implode(',', $ex_fid_ary), $post_visibility, implode(',', $author_ary), $author_name)));
// try reading the results from cache
$total_results = 0;
if ($this->obtain_ids($search_key, $total_results, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) {
return $total_results;
}
$id_ary = array();
$sql_where = array();
$group_by = false;
$m_num = 0;
$w_num = 0;
$sql_array = array('SELECT' => $type == 'posts' ? 'p.post_id' : 'p.topic_id', 'FROM' => array(SEARCH_WORDMATCH_TABLE => array(), SEARCH_WORDLIST_TABLE => array()), 'LEFT_JOIN' => array(array('FROM' => array(POSTS_TABLE => 'p'), 'ON' => 'm0.post_id = p.post_id')));
$title_match = '';
$left_join_topics = false;
$group_by = true;
// Build some display specific sql strings
switch ($fields) {
case 'titleonly':
$title_match = 'title_match = 1';
$group_by = false;
// no break
// no break
case 'firstpost':
$left_join_topics = true;
$sql_where[] = 'p.post_id = t.topic_first_post_id';
break;
case 'msgonly':
$title_match = 'title_match = 0';
$group_by = false;
break;
}
if ($type == 'topics') {
$left_join_topics = true;
$group_by = true;
}
/**
* @todo Add a query optimizer (handle stuff like "+(4|3) +4")
*/
foreach ($this->must_contain_ids as $subquery) {
if (is_array($subquery)) {
$group_by = true;
$word_id_sql = array();
$word_ids = array();
foreach ($subquery as $id) {
if (is_string($id)) {
$sql_array['LEFT_JOIN'][] = array('FROM' => array(SEARCH_WORDLIST_TABLE => 'w' . $w_num), 'ON' => "w{$w_num}.word_text LIKE {$id}");
$word_ids[] = "w{$w_num}.word_id";
$w_num++;
} else {
$word_ids[] = $id;
}
}
$sql_where[] = $this->db->sql_in_set("m{$m_num}.word_id", $word_ids);
unset($word_id_sql);
unset($word_ids);
} else {
if (is_string($subquery)) {
$sql_array['FROM'][SEARCH_WORDLIST_TABLE][] = 'w' . $w_num;
$sql_where[] = "w{$w_num}.word_text LIKE {$subquery}";
$sql_where[] = "m{$m_num}.word_id = w{$w_num}.word_id";
$group_by = true;
$w_num++;
} else {
$sql_where[] = "m{$m_num}.word_id = {$subquery}";
}
}
$sql_array['FROM'][SEARCH_WORDMATCH_TABLE][] = 'm' . $m_num;
if ($title_match) {
$sql_where[] = "m{$m_num}.{$title_match}";
}
if ($m_num != 0) {
$sql_where[] = "m{$m_num}.post_id = m0.post_id";
}
$m_num++;
}
foreach ($this->must_not_contain_ids as $key => $subquery) {
if (is_string($subquery)) {
$sql_array['LEFT_JOIN'][] = array('FROM' => array(SEARCH_WORDLIST_TABLE => 'w' . $w_num), 'ON' => "w{$w_num}.word_text LIKE {$subquery}");
$this->must_not_contain_ids[$key] = "w{$w_num}.word_id";
$group_by = true;
$w_num++;
}
}
if (sizeof($this->must_not_contain_ids)) {
$sql_array['LEFT_JOIN'][] = array('FROM' => array(SEARCH_WORDMATCH_TABLE => 'm' . $m_num), 'ON' => $this->db->sql_in_set("m{$m_num}.word_id", $this->must_not_contain_ids) . ($title_match ? " AND m{$m_num}.{$title_match}" : '') . " AND m{$m_num}.post_id = m0.post_id");
$sql_where[] = "m{$m_num}.word_id IS NULL";
$m_num++;
}
foreach ($this->must_exclude_one_ids as $ids) {
$is_null_joins = array();
foreach ($ids as $id) {
if (is_string($id)) {
$sql_array['LEFT_JOIN'][] = array('FROM' => array(SEARCH_WORDLIST_TABLE => 'w' . $w_num), 'ON' => "w{$w_num}.word_text LIKE {$id}");
$id = "w{$w_num}.word_id";
$group_by = true;
$w_num++;
}
$sql_array['LEFT_JOIN'][] = array('FROM' => array(SEARCH_WORDMATCH_TABLE => 'm' . $m_num), 'ON' => "m{$m_num}.word_id = {$id} AND m{$m_num}.post_id = m0.post_id" . ($title_match ? " AND m{$m_num}.{$title_match}" : ''));
$is_null_joins[] = "m{$m_num}.word_id IS NULL";
$m_num++;
}
$sql_where[] = '(' . implode(' OR ', $is_null_joins) . ')';
}
$sql_where[] = $post_visibility;
$search_query = $this->search_query;
$must_exclude_one_ids = $this->must_exclude_one_ids;
$must_not_contain_ids = $this->must_not_contain_ids;
$must_contain_ids = $this->must_contain_ids;
/**
* Allow changing the query used for counting for posts using fulltext_native
*
* @event core.search_native_keywords_count_query_before
* @var string search_query The parsed keywords used for this search
* @var array must_not_contain_ids Ids that cannot be taken into account for the results
* @var array must_exclude_one_ids Ids that cannot be on the results
* @var array must_contain_ids Ids that must be on the results
* @var int result_count The previous result count for the format of the query
* Set to 0 to force a re-count
* @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED
* @var array author_ary Array of user_id containing the users to filter the results to
* @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant)
* @var array ex_fid_ary Which forums not to search on
* @var int topic_id Limit the search to this topic_id only
* @var string sql_sort_table Extra tables to include in the SQL query.
* Used in conjunction with sql_sort_join
* @var string sql_sort_join SQL conditions to join all the tables used together.
* Used in conjunction with sql_sort_table
* @var int sort_days Time, in days, of the oldest possible post to list
* @var string sql_where An array of the current WHERE clause conditions
* @var string sql_match Which columns to do the search on
* @var string sql_match_where Extra conditions to use to properly filter the matching process
* @var string group_by Whether or not the SQL query requires a GROUP BY for the elements in the SELECT clause
* @var string sort_by_sql The possible predefined sort types
* @var string sort_key The sort type used from the possible sort types
* @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
* @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
* @var int start How many posts to skip in the search results (used for pagination)
* @since 3.1.5-RC1
*/
$vars = array('search_query', 'must_not_contain_ids', 'must_exclude_one_ids', 'must_contain_ids', 'result_count', 'join_topic', 'author_ary', 'author_name', 'ex_fid_ary', 'topic_id', 'sql_sort_table', 'sql_sort_join', 'sort_days', 'sql_where', 'sql_match', 'sql_match_where', 'group_by', 'sort_by_sql', 'sort_key', 'sort_dir', 'sql_sort', 'start');
extract($this->src_dispatcher->trigger_event('core.search_native_keywords_count_query_before', compact($vars)));
if ($topic_id) {
$sql_where[] = 'p.topic_id = ' . $topic_id;
}
if (sizeof($author_ary)) {
if ($author_name) {
// first one matches post of registered users, second one guests and deleted users
$sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
} else {
$sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
}
$sql_where[] = $sql_author;
}
if (sizeof($ex_fid_ary)) {
$sql_where[] = $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true);
}
if ($sort_days) {
$sql_where[] = 'p.post_time >= ' . (time() - $sort_days * 86400);
}
$sql_array['WHERE'] = implode(' AND ', $sql_where);
$is_mysql = false;
// if the total result count is not cached yet, retrieve it from the db
if (!$total_results) {
$sql = '';
$sql_array_count = $sql_array;
if ($left_join_topics) {
$sql_array_count['LEFT_JOIN'][] = array('FROM' => array(TOPICS_TABLE => 't'), 'ON' => 'p.topic_id = t.topic_id');
}
switch ($this->db->get_sql_layer()) {
case 'mysql4':
case 'mysqli':
// 3.x does not support SQL_CALC_FOUND_ROWS
// $sql_array['SELECT'] = 'SQL_CALC_FOUND_ROWS ' . $sql_array['SELECT'];
$is_mysql = true;
break;
case 'sqlite':
case 'sqlite3':
$sql_array_count['SELECT'] = $type == 'posts' ? 'DISTINCT p.post_id' : 'DISTINCT p.topic_id';
$sql = 'SELECT COUNT(' . ($type == 'posts' ? 'post_id' : 'topic_id') . ') as total_results
FROM (' . $this->db->sql_build_query('SELECT', $sql_array_count) . ')';
// no break
// no break
default:
$sql_array_count['SELECT'] = $type == 'posts' ? 'COUNT(DISTINCT p.post_id) AS total_results' : 'COUNT(DISTINCT p.topic_id) AS total_results';
$sql = !$sql ? $this->db->sql_build_query('SELECT', $sql_array_count) : $sql;
$result = $this->db->sql_query($sql);
$total_results = (int) $this->db->sql_fetchfield('total_results');
$this->db->sql_freeresult($result);
if (!$total_results) {
return false;
}
break;
}
unset($sql_array_count, $sql);
}
// Build sql strings for sorting
$sql_sort = $sort_by_sql[$sort_key] . ($sort_dir == 'a' ? ' ASC' : ' DESC');
switch ($sql_sort[0]) {
case 'u':
$sql_array['FROM'][USERS_TABLE] = 'u';
$sql_where[] = 'u.user_id = p.poster_id ';
break;
case 't':
$left_join_topics = true;
break;
case 'f':
$sql_array['FROM'][FORUMS_TABLE] = 'f';
$sql_where[] = 'f.forum_id = p.forum_id';
break;
}
if ($left_join_topics) {
$sql_array['LEFT_JOIN'][] = array('FROM' => array(TOPICS_TABLE => 't'), 'ON' => 'p.topic_id = t.topic_id');
}
// if using mysql and the total result count is not calculated yet, get it from the db
if (!$total_results && $is_mysql) {
// Also count rows for the query as if there was not LIMIT. Add SQL_CALC_FOUND_ROWS to SQL
$sql_array['SELECT'] = 'SQL_CALC_FOUND_ROWS ' . $sql_array['SELECT'];
}
$sql_array['WHERE'] = implode(' AND ', $sql_where);
$sql_array['GROUP_BY'] = $group_by ? ($type == 'posts' ? 'p.post_id' : 'p.topic_id') . ', ' . $sort_by_sql[$sort_key] : '';
$sql_array['ORDER_BY'] = $sql_sort;
unset($sql_where, $sql_sort, $group_by);
$sql = $this->db->sql_build_query('SELECT', $sql_array);
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = (int) $row[$type == 'posts' ? 'post_id' : 'topic_id'];
}
$this->db->sql_freeresult($result);
if (!$total_results && $is_mysql) {
// Get the number of results as calculated by MySQL
$sql_count = 'SELECT FOUND_ROWS() as total_results';
$result = $this->db->sql_query($sql_count);
$total_results = (int) $this->db->sql_fetchfield('total_results');
$this->db->sql_freeresult($result);
if (!$total_results) {
return false;
}
}
if ($start >= $total_results) {
$start = floor(($total_results - 1) / $per_page) * $per_page;
$result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
while ($row = $this->db->sql_fetchrow($result)) {
$id_ary[] = (int) $row[$type == 'posts' ? 'post_id' : 'topic_id'];
}
$this->db->sql_freeresult($result);
}
// store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page
$this->save_ids($search_key, $this->search_query, $author_ary, $total_results, $id_ary, $start, $sort_dir);
$id_ary = array_slice($id_ary, 0, (int) $per_page);
return $total_results;
}
