コード例 #1
0
 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);
 }
コード例 #2
0
 /**
  * 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);
             }
         }
     }
 }
コード例 #3
0
/**
* 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');
        }
    }
}
コード例 #4
0
    /**
     * 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;
    }