Verzeichnisstruktur phpBB-3.3.16
- Veröffentlicht
- 27.04.2026
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
fulltext_mysql.php
0001 <?php
0002 /**
0003 *
0004 * This file is part of the phpBB Forum Software package.
0005 *
0006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
0007 * @license GNU General Public License, version 2 (GPL-2.0)
0008 *
0009 * For full copyright and license information, please see
0010 * the docs/CREDITS.txt file.
0011 *
0012 */
0013
0014 namespace phpbb\search;
0015
0016 /**
0017 * Fulltext search for MySQL
0018 */
0019 class fulltext_mysql extends \phpbb\search\base
0020 {
0021 /**
0022 * Associative array holding index stats
0023 * @var array
0024 */
0025 protected $stats = array();
0026
0027 /**
0028 * Holds the words entered by user, obtained by splitting the entered query on whitespace
0029 * @var array
0030 */
0031 protected $split_words = array();
0032
0033 /**
0034 * Config object
0035 * @var \phpbb\config\config
0036 */
0037 protected $config;
0038
0039 /**
0040 * Database connection
0041 * @var \phpbb\db\driver\driver_interface
0042 */
0043 protected $db;
0044
0045 /**
0046 * phpBB event dispatcher object
0047 * @var \phpbb\event\dispatcher_interface
0048 */
0049 protected $phpbb_dispatcher;
0050
0051 /**
0052 * User object
0053 * @var \phpbb\user
0054 */
0055 protected $user;
0056
0057 /**
0058 * Associative array stores the min and max word length to be searched
0059 * @var array
0060 */
0061 protected $word_length = array();
0062
0063 /**
0064 * Contains tidied search query.
0065 * Operators are prefixed in search query and common words excluded
0066 * @var string
0067 */
0068 protected $search_query;
0069
0070 /**
0071 * Contains common words.
0072 * Common words are words with length less/more than min/max length
0073 * @var array
0074 */
0075 protected $common_words = array();
0076
0077 /**
0078 * Constructor
0079 * Creates a new \phpbb\search\fulltext_mysql, which is used as a search backend
0080 *
0081 * @param string|bool $error Any error that occurs is passed on through this reference variable otherwise false
0082 * @param string $phpbb_root_path Relative path to phpBB root
0083 * @param string $phpEx PHP file extension
0084 * @param \phpbb\auth\auth $auth Auth object
0085 * @param \phpbb\config\config $config Config object
0086 * @param \phpbb\db\driver\driver_interface $db Database object
0087 * @param \phpbb\user $user User object
0088 * @param \phpbb\event\dispatcher_interface $phpbb_dispatcher Event dispatcher object
0089 */
0090 public function __construct(&$error, $phpbb_root_path, $phpEx, $auth, $config, $db, $user, $phpbb_dispatcher)
0091 {
0092 $this->config = $config;
0093 $this->db = $db;
0094 $this->phpbb_dispatcher = $phpbb_dispatcher;
0095 $this->user = $user;
0096
0097 $this->word_length = array('min' => $this->config['fulltext_mysql_min_word_len'], 'max' => $this->config['fulltext_mysql_max_word_len']);
0098
0099 /**
0100 * Load the UTF tools
0101 */
0102 if (!function_exists('utf8_strlen'))
0103 {
0104 include($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx);
0105 }
0106
0107 $error = false;
0108 }
0109
0110 /**
0111 * Returns the name of this search backend to be displayed to administrators
0112 *
0113 * @return string Name
0114 */
0115 public function get_name()
0116 {
0117 return 'MySQL Fulltext';
0118 }
0119
0120 /**
0121 * Returns the search_query
0122 *
0123 * @return string search query
0124 */
0125 public function get_search_query()
0126 {
0127 return $this->search_query;
0128 }
0129
0130 /**
0131 * Returns the common_words array
0132 *
0133 * @return array common words that are ignored by search backend
0134 */
0135 public function get_common_words()
0136 {
0137 return $this->common_words;
0138 }
0139
0140 /**
0141 * Returns the word_length array
0142 *
0143 * @return array min and max word length for searching
0144 */
0145 public function get_word_length()
0146 {
0147 return $this->word_length;
0148 }
0149
0150 /**
0151 * Checks for correct MySQL version and stores min/max word length in the config
0152 *
0153 * @return string|bool Language key of the error/incompatibility occurred
0154 */
0155 public function init()
0156 {
0157 if ($this->db->get_sql_layer() != 'mysqli')
0158 {
0159 return $this->user->lang['FULLTEXT_MYSQL_INCOMPATIBLE_DATABASE'];
0160 }
0161
0162 $result = $this->db->sql_query('SHOW TABLE STATUS LIKE \'' . POSTS_TABLE . '\'');
0163 $info = $this->db->sql_fetchrow($result);
0164 $this->db->sql_freeresult($result);
0165
0166 $engine = '';
0167 if (isset($info['Engine']))
0168 {
0169 $engine = $info['Engine'];
0170 }
0171 else if (isset($info['Type']))
0172 {
0173 $engine = $info['Type'];
0174 }
0175
0176 $fulltext_supported = $engine === 'Aria' || $engine === 'MyISAM'
0177 /**
0178 * FULLTEXT is supported on InnoDB since MySQL 5.6.4 according to
0179 * http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html
0180 * We also require https://bugs.mysql.com/bug.php?id=67004 to be
0181 * fixed for proper overall operation. Hence we require 5.6.8.
0182 */
0183 || $engine === 'InnoDB'
0184 && phpbb_version_compare($this->db->sql_server_info(true), '5.6.8', '>=');
0185
0186 if (!$fulltext_supported)
0187 {
0188 return $this->user->lang['FULLTEXT_MYSQL_NOT_SUPPORTED'];
0189 }
0190
0191 $sql = 'SHOW VARIABLES
0192 LIKE \'%ft\_%\'';
0193 $result = $this->db->sql_query($sql);
0194
0195 $mysql_info = array();
0196 while ($row = $this->db->sql_fetchrow($result))
0197 {
0198 $mysql_info[$row['Variable_name']] = $row['Value'];
0199 }
0200 $this->db->sql_freeresult($result);
0201
0202 if ($engine === 'MyISAM')
0203 {
0204 $this->config->set('fulltext_mysql_max_word_len', $mysql_info['ft_max_word_len']);
0205 $this->config->set('fulltext_mysql_min_word_len', $mysql_info['ft_min_word_len']);
0206 }
0207 else if ($engine === 'InnoDB')
0208 {
0209 $this->config->set('fulltext_mysql_max_word_len', $mysql_info['innodb_ft_max_token_size']);
0210 $this->config->set('fulltext_mysql_min_word_len', $mysql_info['innodb_ft_min_token_size']);
0211 }
0212
0213 return false;
0214 }
0215
0216 /**
0217 * Splits keywords entered by a user into an array of words stored in $this->split_words
0218 * Stores the tidied search query in $this->search_query
0219 *
0220 * @param string &$keywords Contains the keyword as entered by the user
0221 * @param string $terms is either 'all' or 'any'
0222 * @return bool false if no valid keywords were found and otherwise true
0223 */
0224 public function split_keywords(&$keywords, $terms)
0225 {
0226 if ($terms == 'all')
0227 {
0228 $match = array('#\sand\s#iu', '#\sor\s#iu', '#\snot\s#iu', '#(^|\s)\+#', '#(^|\s)-#', '#(^|\s)\|#');
0229 $replace = array(' +', ' |', ' -', ' +', ' -', ' |');
0230
0231 $keywords = preg_replace($match, $replace, $keywords);
0232 }
0233
0234 // Filter out as above
0235 $split_keywords = preg_replace("#[\n\r\t]+#", ' ', trim(html_entity_decode($keywords, ENT_COMPAT)));
0236
0237 // Split words
0238 $split_keywords = preg_replace('#([^\p{L}\p{N}\'*"()])#u', '$1$1', str_replace('\'\'', '\' \'', trim($split_keywords)));
0239 $matches = array();
0240 preg_match_all('#(?:[^\p{L}\p{N}*"()]|^)([+\-|]?(?:[\p{L}\p{N}*"()]+\'?)*[\p{L}\p{N}*"()])(?:[^\p{L}\p{N}*"()]|$)#u', $split_keywords, $matches);
0241 $this->split_words = $matches[1];
0242
0243 // We limit the number of allowed keywords to minimize load on the database
0244 if ($this->config['max_num_search_keywords'] && count($this->split_words) > $this->config['max_num_search_keywords'])
0245 {
0246 trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], count($this->split_words)));
0247 }
0248
0249 // to allow phrase search, we need to concatenate quoted words
0250 $tmp_split_words = array();
0251 $phrase = '';
0252 foreach ($this->split_words as $word)
0253 {
0254 if ($phrase)
0255 {
0256 $phrase .= ' ' . $word;
0257 if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1)
0258 {
0259 $tmp_split_words[] = $phrase;
0260 $phrase = '';
0261 }
0262 }
0263 else if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1)
0264 {
0265 $phrase = $word;
0266 }
0267 else
0268 {
0269 $tmp_split_words[] = $word;
0270 }
0271 }
0272 if ($phrase)
0273 {
0274 $tmp_split_words[] = $phrase;
0275 }
0276
0277 $this->split_words = $tmp_split_words;
0278
0279 unset($tmp_split_words);
0280 unset($phrase);
0281
0282 foreach ($this->split_words as $i => $word)
0283 {
0284 // Check for not allowed search queries for InnoDB.
0285 // We assume similar restrictions for MyISAM, which is usually even
0286 // slower but not as restrictive as InnoDB.
0287 // InnoDB full-text search does not support the use of a leading
0288 // plus sign with wildcard ('+*'), a plus and minus sign
0289 // combination ('+-'), or leading a plus and minus sign combination.
0290 // InnoDB full-text search only supports leading plus or minus signs.
0291 // For example, InnoDB supports '+apple' but does not support 'apple+'.
0292 // Specifying a trailing plus or minus sign causes InnoDB to report
0293 // a syntax error. InnoDB full-text search does not support the use
0294 // of multiple operators on a single search word, as in this example:
0295 // '++apple'. Use of multiple operators on a single search word
0296 // returns a syntax error to standard out.
0297 // Also, ensure that the wildcard character is only used at the
0298 // end of the line as it's intended by MySQL.
0299 if (preg_match('#^(\+[+-]|\+\*|.+[+-]$|.+\*(?!$))#', $word))
0300 {
0301 unset($this->split_words[$i]);
0302 continue;
0303 }
0304
0305 $clean_word = preg_replace('#^[+\-|"]#', '', $word);
0306
0307 // check word length
0308 $clean_len = utf8_strlen(str_replace('*', '', $clean_word));
0309 if (($clean_len < $this->config['fulltext_mysql_min_word_len']) || ($clean_len > $this->config['fulltext_mysql_max_word_len']))
0310 {
0311 $this->common_words[] = $word;
0312 unset($this->split_words[$i]);
0313 }
0314 }
0315
0316 if ($terms == 'any')
0317 {
0318 $this->search_query = '';
0319 foreach ($this->split_words as $word)
0320 {
0321 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0) || (strpos($word, '|') === 0))
0322 {
0323 $word = substr($word, 1);
0324 }
0325 $this->search_query .= $word . ' ';
0326 }
0327 }
0328 else
0329 {
0330 $this->search_query = '';
0331 foreach ($this->split_words as $word)
0332 {
0333 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0))
0334 {
0335 $this->search_query .= $word . ' ';
0336 }
0337 else if (strpos($word, '|') === 0)
0338 {
0339 $this->search_query .= substr($word, 1) . ' ';
0340 }
0341 else
0342 {
0343 $this->search_query .= '+' . $word . ' ';
0344 }
0345 }
0346 }
0347
0348 $this->search_query = utf8_htmlspecialchars($this->search_query);
0349
0350 if ($this->search_query)
0351 {
0352 $this->split_words = array_values($this->split_words);
0353 sort($this->split_words);
0354 return true;
0355 }
0356 return false;
0357 }
0358
0359 /**
0360 * Turns text into an array of words
0361 * @param string $text contains post text/subject
0362 */
0363 public function split_message($text)
0364 {
0365 // Split words
0366 $text = preg_replace('#([^\p{L}\p{N}\'*])#u', '$1$1', str_replace('\'\'', '\' \'', trim($text)));
0367 $matches = array();
0368 preg_match_all('#(?:[^\p{L}\p{N}*]|^)([+\-|]?(?:[\p{L}\p{N}*]+\'?)*[\p{L}\p{N}*])(?:[^\p{L}\p{N}*]|$)#u', $text, $matches);
0369 $text = $matches[1];
0370
0371 // remove too short or too long words
0372 $text = array_values($text);
0373 for ($i = 0, $n = count($text); $i < $n; $i++)
0374 {
0375 $text[$i] = trim($text[$i]);
0376 if (utf8_strlen($text[$i]) < $this->config['fulltext_mysql_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_mysql_max_word_len'])
0377 {
0378 unset($text[$i]);
0379 }
0380 }
0381
0382 return array_values($text);
0383 }
0384
0385 /**
0386 * Performs a search on keywords depending on display specific params. You have to run split_keywords() first
0387 *
0388 * @param string $type contains either posts or topics depending on what should be searched for
0389 * @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)
0390 * @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)
0391 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0392 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0393 * @param string $sort_dir is either a or d representing ASC and DESC
0394 * @param string $sort_days specifies the maximum amount of days a post may be old
0395 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0396 * @param string $post_visibility specifies which types of posts the user can view in which forums
0397 * @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
0398 * @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty
0399 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0400 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0401 * @param int $start indicates the first index of the page
0402 * @param int $per_page number of ids each page is supposed to contain
0403 * @return boolean|int total number of results
0404 */
0405 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)
0406 {
0407 // No keywords? No posts
0408 if (!$this->search_query)
0409 {
0410 return false;
0411 }
0412
0413 // generate a search_key from all the options to identify the results
0414 $search_key_array = array(
0415 implode(', ', $this->split_words),
0416 $type,
0417 $fields,
0418 $terms,
0419 $sort_days,
0420 $sort_key,
0421 $topic_id,
0422 implode(',', $ex_fid_ary),
0423 $post_visibility,
0424 implode(',', $author_ary)
0425 );
0426
0427 /**
0428 * Allow changing the search_key for cached results
0429 *
0430 * @event core.search_mysql_by_keyword_modify_search_key
0431 * @var array search_key_array Array with search parameters to generate the search_key
0432 * @var string type Searching type ('posts', 'topics')
0433 * @var string fields Searching fields ('titleonly', 'msgonly', 'firstpost', 'all')
0434 * @var string terms Searching terms ('all', 'any')
0435 * @var int sort_days Time, in days, of the oldest possible post to list
0436 * @var string sort_key The sort type used from the possible sort types
0437 * @var int topic_id Limit the search to this topic_id only
0438 * @var array ex_fid_ary Which forums not to search on
0439 * @var string post_visibility Post visibility data
0440 * @var array author_ary Array of user_id containing the users to filter the results to
0441 * @since 3.1.7-RC1
0442 */
0443 $vars = array(
0444 'search_key_array',
0445 'type',
0446 'fields',
0447 'terms',
0448 'sort_days',
0449 'sort_key',
0450 'topic_id',
0451 'ex_fid_ary',
0452 'post_visibility',
0453 'author_ary',
0454 );
0455 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_keyword_modify_search_key', compact($vars)));
0456
0457 $search_key = md5(implode('#', $search_key_array));
0458
0459 if ($start < 0)
0460 {
0461 $start = 0;
0462 }
0463
0464 // try reading the results from cache
0465 $result_count = 0;
0466 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0467 {
0468 return $result_count;
0469 }
0470
0471 $id_ary = array();
0472
0473 $join_topic = ($type == 'posts') ? false : true;
0474
0475 // Build sql strings for sorting
0476 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0477 $sql_sort_table = $sql_sort_join = '';
0478
0479 switch ($sql_sort[0])
0480 {
0481 case 'u':
0482 $sql_sort_table = USERS_TABLE . ' u, ';
0483 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0484 break;
0485
0486 case 't':
0487 $join_topic = true;
0488 break;
0489
0490 case 'f':
0491 $sql_sort_table = FORUMS_TABLE . ' f, ';
0492 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0493 break;
0494 }
0495
0496 // Build some display specific sql strings
0497 switch ($fields)
0498 {
0499 case 'titleonly':
0500 $sql_match = 'p.post_subject';
0501 $sql_match_where = ' AND p.post_id = t.topic_first_post_id';
0502 $join_topic = true;
0503 break;
0504
0505 case 'msgonly':
0506 $sql_match = 'p.post_text';
0507 $sql_match_where = '';
0508 break;
0509
0510 case 'firstpost':
0511 $sql_match = 'p.post_subject, p.post_text';
0512 $sql_match_where = ' AND p.post_id = t.topic_first_post_id';
0513 $join_topic = true;
0514 break;
0515
0516 default:
0517 $sql_match = 'p.post_subject, p.post_text';
0518 $sql_match_where = '';
0519 break;
0520 }
0521
0522 $search_query = $this->search_query;
0523
0524 /**
0525 * Allow changing the query used to search for posts using fulltext_mysql
0526 *
0527 * @event core.search_mysql_keywords_main_query_before
0528 * @var string search_query The parsed keywords used for this search
0529 * @var int result_count The previous result count for the format of the query.
0530 * Set to 0 to force a re-count
0531 * @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED
0532 * @var array author_ary Array of user_id containing the users to filter the results to
0533 * @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant)
0534 * @var array ex_fid_ary Which forums not to search on
0535 * @var int topic_id Limit the search to this topic_id only
0536 * @var string sql_sort_table Extra tables to include in the SQL query.
0537 * Used in conjunction with sql_sort_join
0538 * @var string sql_sort_join SQL conditions to join all the tables used together.
0539 * Used in conjunction with sql_sort_table
0540 * @var int sort_days Time, in days, of the oldest possible post to list
0541 * @var string sql_match Which columns to do the search on.
0542 * @var string sql_match_where Extra conditions to use to properly filter the matching process
0543 * @var string sort_by_sql The possible predefined sort types
0544 * @var string sort_key The sort type used from the possible sort types
0545 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0546 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0547 * @var int start How many posts to skip in the search results (used for pagination)
0548 * @since 3.1.5-RC1
0549 */
0550 $vars = array(
0551 'search_query',
0552 'result_count',
0553 'join_topic',
0554 'author_ary',
0555 'author_name',
0556 'ex_fid_ary',
0557 'topic_id',
0558 'sql_sort_table',
0559 'sql_sort_join',
0560 'sort_days',
0561 'sql_match',
0562 'sql_match_where',
0563 'sort_by_sql',
0564 'sort_key',
0565 'sort_dir',
0566 'sql_sort',
0567 'start',
0568 );
0569 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_keywords_main_query_before', compact($vars)));
0570
0571 $sql_select = ($type == 'posts') ? 'DISTINCT p.post_id' : 'DISTINCT t.topic_id';
0572 $sql_select .= $sort_by_sql[$sort_key] ? ", {$sort_by_sql[$sort_key]}" : '';
0573 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : '';
0574 $field = ($type == 'posts') ? 'post_id' : 'topic_id';
0575 if (count($author_ary) && $author_name)
0576 {
0577 // first one matches post of registered users, second one guests and deleted users
0578 $sql_author = ' AND (' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
0579 }
0580 else if (count($author_ary))
0581 {
0582 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary);
0583 }
0584 else
0585 {
0586 $sql_author = '';
0587 }
0588
0589 $sql_where_options = $sql_sort_join;
0590 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : '';
0591 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : '';
0592 $sql_where_options .= (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0593 $sql_where_options .= ' AND ' . $post_visibility;
0594 $sql_where_options .= $sql_author;
0595 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0596 $sql_where_options .= $sql_match_where;
0597
0598 $sql = "SELECT $sql_select
0599 FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p
0600 WHERE MATCH ($sql_match) AGAINST ('" . $this->db->sql_escape(html_entity_decode($this->search_query, ENT_COMPAT)) . "' IN BOOLEAN MODE)
0601 $sql_where_options
0602 ORDER BY $sql_sort";
0603 $this->db->sql_return_on_error(true);
0604 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0605
0606 while ($row = $this->db->sql_fetchrow($result))
0607 {
0608 $id_ary[] = (int) $row[$field];
0609 }
0610 $this->db->sql_freeresult($result);
0611
0612 // if the total result count is not cached yet, retrieve it from the db
0613 if (!$result_count && count($id_ary))
0614 {
0615 $sql_found_rows = str_replace("SELECT $sql_select", "SELECT COUNT($sql_select) as result_count", $sql);
0616 $result = $this->db->sql_query($sql_found_rows);
0617 $result_count = (int) $this->db->sql_fetchfield('result_count');
0618 $this->db->sql_freeresult($result);
0619
0620 if (!$result_count)
0621 {
0622 return false;
0623 }
0624 }
0625
0626 if ($start >= $result_count)
0627 {
0628 $start = floor(($result_count - 1) / $per_page) * $per_page;
0629
0630 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0631
0632 while ($row = $this->db->sql_fetchrow($result))
0633 {
0634 $id_ary[] = (int) $row[$field];
0635 }
0636 $this->db->sql_freeresult($result);
0637 }
0638
0639 $id_ary = array_unique($id_ary);
0640
0641 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page
0642 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir);
0643 $id_ary = array_slice($id_ary, 0, (int) $per_page);
0644
0645 return $result_count;
0646 }
0647
0648 /**
0649 * Performs a search on an author's posts without caring about message contents. Depends on display specific params
0650 *
0651 * @param string $type contains either posts or topics depending on what should be searched for
0652 * @param boolean $firstpost_only if true, only topic starting posts will be considered
0653 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0654 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0655 * @param string $sort_dir is either a or d representing ASC and DESC
0656 * @param string $sort_days specifies the maximum amount of days a post may be old
0657 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0658 * @param string $post_visibility specifies which types of posts the user can view in which forums
0659 * @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
0660 * @param array $author_ary an array of author ids
0661 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0662 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0663 * @param int $start indicates the first index of the page
0664 * @param int $per_page number of ids each page is supposed to contain
0665 * @return boolean|int total number of results
0666 */
0667 public function author_search($type, $firstpost_only, $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)
0668 {
0669 // No author? No posts
0670 if (!count($author_ary))
0671 {
0672 return 0;
0673 }
0674
0675 // generate a search_key from all the options to identify the results
0676 $search_key_array = array(
0677 '',
0678 $type,
0679 ($firstpost_only) ? 'firstpost' : '',
0680 '',
0681 '',
0682 $sort_days,
0683 $sort_key,
0684 $topic_id,
0685 implode(',', $ex_fid_ary),
0686 $post_visibility,
0687 implode(',', $author_ary),
0688 $author_name,
0689 );
0690
0691 /**
0692 * Allow changing the search_key for cached results
0693 *
0694 * @event core.search_mysql_by_author_modify_search_key
0695 * @var array search_key_array Array with search parameters to generate the search_key
0696 * @var string type Searching type ('posts', 'topics')
0697 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered
0698 * @var int sort_days Time, in days, of the oldest possible post to list
0699 * @var string sort_key The sort type used from the possible sort types
0700 * @var int topic_id Limit the search to this topic_id only
0701 * @var array ex_fid_ary Which forums not to search on
0702 * @var string post_visibility Post visibility data
0703 * @var array author_ary Array of user_id containing the users to filter the results to
0704 * @var string author_name The username to search on
0705 * @since 3.1.7-RC1
0706 */
0707 $vars = array(
0708 'search_key_array',
0709 'type',
0710 'firstpost_only',
0711 'sort_days',
0712 'sort_key',
0713 'topic_id',
0714 'ex_fid_ary',
0715 'post_visibility',
0716 'author_ary',
0717 'author_name',
0718 );
0719 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_author_modify_search_key', compact($vars)));
0720
0721 $search_key = md5(implode('#', $search_key_array));
0722
0723 if ($start < 0)
0724 {
0725 $start = 0;
0726 }
0727
0728 // try reading the results from cache
0729 $result_count = 0;
0730 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0731 {
0732 return $result_count;
0733 }
0734
0735 $id_ary = array();
0736
0737 // Create some display specific sql strings
0738 if ($author_name)
0739 {
0740 // first one matches post of registered users, second one guests and deleted users
0741 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
0742 }
0743 else
0744 {
0745 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
0746 }
0747 $sql_fora = (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0748 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : '';
0749 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0750 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : '';
0751
0752 // Build sql strings for sorting
0753 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0754 $sql_sort_table = $sql_sort_join = '';
0755 switch ($sql_sort[0])
0756 {
0757 case 'u':
0758 $sql_sort_table = USERS_TABLE . ' u, ';
0759 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0760 break;
0761
0762 case 't':
0763 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : '';
0764 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : '';
0765 break;
0766
0767 case 'f':
0768 $sql_sort_table = FORUMS_TABLE . ' f, ';
0769 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0770 break;
0771 }
0772
0773 $m_approve_fid_sql = ' AND ' . $post_visibility;
0774
0775 /**
0776 * Allow changing the query used to search for posts by author in fulltext_mysql
0777 *
0778 * @event core.search_mysql_author_query_before
0779 * @var int result_count The previous result count for the format of the query.
0780 * Set to 0 to force a re-count
0781 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen
0782 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table
0783 * @var string type Either "posts" or "topics" specifying the type of search being made
0784 * @var array author_ary Array of user_id containing the users to filter the results to
0785 * @var string author_name An extra username to search on
0786 * @var string sql_author SQL WHERE condition for the post author ids
0787 * @var int topic_id Limit the search to this topic_id only
0788 * @var string sql_topic_id SQL of topic_id
0789 * @var string sort_by_sql The possible predefined sort types
0790 * @var string sort_key The sort type used from the possible sort types
0791 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0792 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0793 * @var string sort_days Time, in days, that the oldest post showing can have
0794 * @var string sql_time The SQL to search on the time specifyed by sort_days
0795 * @var bool firstpost_only Wether or not to search only on the first post of the topics
0796 * @var string sql_firstpost The SQL with the conditions to join the tables when using firstpost_only
0797 * @var array ex_fid_ary Forum ids that must not be searched on
0798 * @var array sql_fora SQL query for ex_fid_ary
0799 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions
0800 * @var int start How many posts to skip in the search results (used for pagination)
0801 * @since 3.1.5-RC1
0802 */
0803 $vars = array(
0804 'result_count',
0805 'sql_sort_table',
0806 'sql_sort_join',
0807 'type',
0808 'author_ary',
0809 'author_name',
0810 'sql_author',
0811 'topic_id',
0812 'sql_topic_id',
0813 'sort_by_sql',
0814 'sort_key',
0815 'sort_dir',
0816 'sql_sort',
0817 'sort_days',
0818 'sql_time',
0819 'firstpost_only',
0820 'sql_firstpost',
0821 'ex_fid_ary',
0822 'sql_fora',
0823 'm_approve_fid_sql',
0824 'start',
0825 );
0826 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars)));
0827
0828 // If the cache was completely empty count the results
0829 $sql_select = ($type == 'posts') ? 'p.post_id' : 't.topic_id';
0830 $sql_select .= $sort_by_sql[$sort_key] ? ", {$sort_by_sql[$sort_key]}" : '';
0831
0832 // Build the query for really selecting the post_ids
0833 if ($type == 'posts')
0834 {
0835 // For sorting by non-unique columns, add unique sort key to avoid duplicated rows in results
0836 $sql_sort .= ', p.post_id' . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0837 $sql = "SELECT $sql_select
0838 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "
0839 WHERE $sql_author
0840 $sql_topic_id
0841 $sql_firstpost
0842 $m_approve_fid_sql
0843 $sql_fora
0844 $sql_sort_join
0845 $sql_time
0846 ORDER BY $sql_sort";
0847 $field = 'post_id';
0848 }
0849 else
0850 {
0851 $sql = "SELECT $sql_select
0852 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
0853 WHERE $sql_author
0854 $sql_topic_id
0855 $sql_firstpost
0856 $m_approve_fid_sql
0857 $sql_fora
0858 AND t.topic_id = p.topic_id
0859 $sql_sort_join
0860 $sql_time
0861 GROUP BY $sql_select
0862 ORDER BY $sql_sort";
0863 $field = 'topic_id';
0864 }
0865
0866 // Only read one block of posts from the db and then cache it
0867 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0868
0869 while ($row = $this->db->sql_fetchrow($result))
0870 {
0871 $id_ary[] = (int) $row[$field];
0872 }
0873 $this->db->sql_freeresult($result);
0874
0875 // retrieve the total result count if needed
0876 if (!$result_count)
0877 {
0878 $sql_found_rows = str_replace("SELECT $sql_select", "SELECT COUNT(*) as result_count", $sql);
0879 $result = $this->db->sql_query($sql_found_rows);
0880 $result_count = ($type == 'posts') ? (int) $this->db->sql_fetchfield('result_count') : count($this->db->sql_fetchrowset($result));
0881
0882 $this->db->sql_freeresult($result);
0883
0884 if (!$result_count)
0885 {
0886 return false;
0887 }
0888 }
0889
0890 if ($start >= $result_count)
0891 {
0892 $start = floor(($result_count - 1) / $per_page) * $per_page;
0893
0894 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0895 while ($row = $this->db->sql_fetchrow($result))
0896 {
0897 $id_ary[] = (int) $row[$field];
0898 }
0899 $this->db->sql_freeresult($result);
0900 }
0901
0902 $id_ary = array_unique($id_ary);
0903
0904 if (count($id_ary))
0905 {
0906 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
0907 $id_ary = array_slice($id_ary, 0, $per_page);
0908
0909 return $result_count;
0910 }
0911 return false;
0912 }
0913
0914 /**
0915 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated
0916 *
0917 * @param string $mode contains the post mode: edit, post, reply, quote ...
0918 * @param int $post_id contains the post id of the post to index
0919 * @param string $message contains the post text of the post
0920 * @param string $subject contains the subject of the post to index
0921 * @param int $poster_id contains the user id of the poster
0922 * @param int $forum_id contains the forum id of parent forum of the post
0923 */
0924 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id)
0925 {
0926 // Split old and new post/subject to obtain array of words
0927 $split_text = $this->split_message($message);
0928 $split_title = ($subject) ? $this->split_message($subject) : array();
0929
0930 $words = array_unique(array_merge($split_text, $split_title));
0931
0932 /**
0933 * Event to modify method arguments and words before the MySQL search index is updated
0934 *
0935 * @event core.search_mysql_index_before
0936 * @var string mode Contains the post mode: edit, post, reply, quote
0937 * @var int post_id The id of the post which is modified/created
0938 * @var string message New or updated post content
0939 * @var string subject New or updated post subject
0940 * @var int poster_id Post author's user id
0941 * @var int forum_id The id of the forum in which the post is located
0942 * @var array words List of words added to the index
0943 * @var array split_text Array of words from the message
0944 * @var array split_title Array of words from the title
0945 * @since 3.2.3-RC1
0946 */
0947 $vars = array(
0948 'mode',
0949 'post_id',
0950 'message',
0951 'subject',
0952 'poster_id',
0953 'forum_id',
0954 'words',
0955 'split_text',
0956 'split_title',
0957 );
0958 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_index_before', compact($vars)));
0959
0960 unset($split_text);
0961 unset($split_title);
0962
0963 // destroy cached search results containing any of the words removed or added
0964 $this->destroy_cache($words, array($poster_id));
0965
0966 unset($words);
0967 }
0968
0969 /**
0970 * Destroy cached results, that might be outdated after deleting a post
0971 */
0972 public function index_remove($post_ids, $author_ids, $forum_ids)
0973 {
0974 $this->destroy_cache(array(), array_unique($author_ids));
0975 }
0976
0977 /**
0978 * Destroy old cache entries
0979 */
0980 public function tidy()
0981 {
0982 // destroy too old cached search results
0983 $this->destroy_cache(array());
0984
0985 $this->config->set('search_last_gc', time(), false);
0986 }
0987
0988 /**
0989 * Create fulltext index
0990 *
0991 * @return string|bool error string is returned incase of errors otherwise false
0992 */
0993 public function create_index($acp_module, $u_action)
0994 {
0995 // Make sure we can actually use MySQL with fulltext indexes
0996 if ($error = $this->init())
0997 {
0998 return $error;
0999 }
1000
1001 if (empty($this->stats))
1002 {
1003 $this->get_stats();
1004 }
1005
1006 $alter_list = array();
1007
1008 if (!isset($this->stats['post_subject']))
1009 {
1010 $alter_entry = array();
1011 $alter_entry[] = 'MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL';
1012 $alter_entry[] = 'ADD FULLTEXT (post_subject)';
1013 $alter_list[] = $alter_entry;
1014 }
1015
1016 if (!isset($this->stats['post_content']))
1017 {
1018 $alter_entry = array();
1019 $alter_entry[] = 'MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL';
1020 $alter_entry[] = 'ADD FULLTEXT post_content (post_text, post_subject)';
1021 $alter_list[] = $alter_entry;
1022 }
1023
1024 $sql_queries = [];
1025
1026 foreach ($alter_list as $alter)
1027 {
1028 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter);
1029 }
1030
1031 if (!isset($this->stats['post_text']))
1032 {
1033 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)';
1034 }
1035
1036 $stats = $this->stats;
1037
1038 /**
1039 * Event to modify SQL queries before the MySQL search index is created
1040 *
1041 * @event core.search_mysql_create_index_before
1042 * @var array sql_queries Array with queries for creating the search index
1043 * @var array stats Array with statistics of the current index (read only)
1044 * @since 3.2.3-RC1
1045 */
1046 $vars = array(
1047 'sql_queries',
1048 'stats',
1049 );
1050 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_create_index_before', compact($vars)));
1051
1052 foreach ($sql_queries as $sql_query)
1053 {
1054 $this->db->sql_query($sql_query);
1055 }
1056
1057 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1058
1059 return false;
1060 }
1061
1062 /**
1063 * Drop fulltext index
1064 *
1065 * @return string|bool error string is returned incase of errors otherwise false
1066 */
1067 public function delete_index($acp_module, $u_action)
1068 {
1069 // Make sure we can actually use MySQL with fulltext indexes
1070 if ($error = $this->init())
1071 {
1072 return $error;
1073 }
1074
1075 if (empty($this->stats))
1076 {
1077 $this->get_stats();
1078 }
1079
1080 $alter = array();
1081
1082 if (isset($this->stats['post_subject']))
1083 {
1084 $alter[] = 'DROP INDEX post_subject';
1085 }
1086
1087 if (isset($this->stats['post_content']))
1088 {
1089 $alter[] = 'DROP INDEX post_content';
1090 }
1091
1092 if (isset($this->stats['post_text']))
1093 {
1094 $alter[] = 'DROP INDEX post_text';
1095 }
1096
1097 $sql_queries = [];
1098
1099 if (count($alter))
1100 {
1101 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter);
1102 }
1103
1104 $stats = $this->stats;
1105
1106 /**
1107 * Event to modify SQL queries before the MySQL search index is deleted
1108 *
1109 * @event core.search_mysql_delete_index_before
1110 * @var array sql_queries Array with queries for deleting the search index
1111 * @var array stats Array with statistics of the current index (read only)
1112 * @since 3.2.3-RC1
1113 */
1114 $vars = array(
1115 'sql_queries',
1116 'stats',
1117 );
1118 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_delete_index_before', compact($vars)));
1119
1120 foreach ($sql_queries as $sql_query)
1121 {
1122 $this->db->sql_query($sql_query);
1123 }
1124
1125 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1126
1127 return false;
1128 }
1129
1130 /**
1131 * Returns true if both FULLTEXT indexes exist
1132 */
1133 public function index_created()
1134 {
1135 if (empty($this->stats))
1136 {
1137 $this->get_stats();
1138 }
1139
1140 return isset($this->stats['post_subject']) && isset($this->stats['post_content']) && isset($this->stats['post_text']);
1141 }
1142
1143 /**
1144 * Returns an associative array containing information about the indexes
1145 */
1146 public function index_stats()
1147 {
1148 if (empty($this->stats))
1149 {
1150 $this->get_stats();
1151 }
1152
1153 return array(
1154 $this->user->lang['FULLTEXT_MYSQL_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0,
1155 );
1156 }
1157
1158 /**
1159 * Computes the stats and store them in the $this->stats associative array
1160 */
1161 protected function get_stats()
1162 {
1163 if (strpos($this->db->get_sql_layer(), 'mysql') === false)
1164 {
1165 $this->stats = array();
1166 return;
1167 }
1168
1169 $sql = 'SHOW INDEX
1170 FROM ' . POSTS_TABLE;
1171 $result = $this->db->sql_query($sql);
1172
1173 while ($row = $this->db->sql_fetchrow($result))
1174 {
1175 // deal with older MySQL versions which didn't use Index_type
1176 $index_type = (isset($row['Index_type'])) ? $row['Index_type'] : $row['Comment'];
1177
1178 if ($index_type == 'FULLTEXT')
1179 {
1180 if ($row['Key_name'] == 'post_subject')
1181 {
1182 $this->stats['post_subject'] = $row;
1183 }
1184 else if ($row['Key_name'] == 'post_text')
1185 {
1186 $this->stats['post_text'] = $row;
1187 }
1188 else if ($row['Key_name'] == 'post_content')
1189 {
1190 $this->stats['post_content'] = $row;
1191 }
1192 }
1193 }
1194 $this->db->sql_freeresult($result);
1195
1196 $this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE);
1197 }
1198
1199 /**
1200 * Display a note, that UTF-8 support is not available with certain versions of PHP
1201 *
1202 * @return associative array containing template and config variables
1203 */
1204 public function acp()
1205 {
1206 $tpl = '
1207 <dl>
1208 <dt><label>' . $this->user->lang['MIN_SEARCH_CHARS'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_MYSQL_MIN_SEARCH_CHARS_EXPLAIN'] . '</span></dt>
1209 <dd>' . $this->config['fulltext_mysql_min_word_len'] . '</dd>
1210 </dl>
1211 <dl>
1212 <dt><label>' . $this->user->lang['MAX_SEARCH_CHARS'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_MYSQL_MAX_SEARCH_CHARS_EXPLAIN'] . '</span></dt>
1213 <dd>' . $this->config['fulltext_mysql_max_word_len'] . '</dd>
1214 </dl>
1215 ';
1216
1217 // These are fields required in the config table
1218 return array(
1219 'tpl' => $tpl,
1220 'config' => array()
1221 );
1222 }
1223 }
1224