Verzeichnisstruktur phpBB-3.3.15
- Veröffentlicht
- 28.08.2024
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 $id_ary = array_unique($id_ary);
0613 // if the total result count is not cached yet, retrieve it from the db
0614 if (!$result_count && count($id_ary))
0615 {
0616 $sql_found_rows = str_replace("SELECT $sql_select", "SELECT COUNT($sql_select) as result_count", $sql);
0617 $result = $this->db->sql_query($sql_found_rows);
0618 $result_count = (int) $this->db->sql_fetchfield('result_count');
0619 $this->db->sql_freeresult($result);
0620
0621 if (!$result_count)
0622 {
0623 return false;
0624 }
0625 }
0626
0627 if ($start >= $result_count)
0628 {
0629 $start = floor(($result_count - 1) / $per_page) * $per_page;
0630
0631 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0632
0633 while ($row = $this->db->sql_fetchrow($result))
0634 {
0635 $id_ary[] = (int) $row[$field];
0636 }
0637 $this->db->sql_freeresult($result);
0638
0639 $id_ary = array_unique($id_ary);
0640 }
0641
0642 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page
0643 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir);
0644 $id_ary = array_slice($id_ary, 0, (int) $per_page);
0645
0646 return $result_count;
0647 }
0648
0649 /**
0650 * Performs a search on an author's posts without caring about message contents. Depends on display specific params
0651 *
0652 * @param string $type contains either posts or topics depending on what should be searched for
0653 * @param boolean $firstpost_only if true, only topic starting posts will be considered
0654 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0655 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0656 * @param string $sort_dir is either a or d representing ASC and DESC
0657 * @param string $sort_days specifies the maximum amount of days a post may be old
0658 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0659 * @param string $post_visibility specifies which types of posts the user can view in which forums
0660 * @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
0661 * @param array $author_ary an array of author ids
0662 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0663 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0664 * @param int $start indicates the first index of the page
0665 * @param int $per_page number of ids each page is supposed to contain
0666 * @return boolean|int total number of results
0667 */
0668 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)
0669 {
0670 // No author? No posts
0671 if (!count($author_ary))
0672 {
0673 return 0;
0674 }
0675
0676 // generate a search_key from all the options to identify the results
0677 $search_key_array = array(
0678 '',
0679 $type,
0680 ($firstpost_only) ? 'firstpost' : '',
0681 '',
0682 '',
0683 $sort_days,
0684 $sort_key,
0685 $topic_id,
0686 implode(',', $ex_fid_ary),
0687 $post_visibility,
0688 implode(',', $author_ary),
0689 $author_name,
0690 );
0691
0692 /**
0693 * Allow changing the search_key for cached results
0694 *
0695 * @event core.search_mysql_by_author_modify_search_key
0696 * @var array search_key_array Array with search parameters to generate the search_key
0697 * @var string type Searching type ('posts', 'topics')
0698 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered
0699 * @var int sort_days Time, in days, of the oldest possible post to list
0700 * @var string sort_key The sort type used from the possible sort types
0701 * @var int topic_id Limit the search to this topic_id only
0702 * @var array ex_fid_ary Which forums not to search on
0703 * @var string post_visibility Post visibility data
0704 * @var array author_ary Array of user_id containing the users to filter the results to
0705 * @var string author_name The username to search on
0706 * @since 3.1.7-RC1
0707 */
0708 $vars = array(
0709 'search_key_array',
0710 'type',
0711 'firstpost_only',
0712 'sort_days',
0713 'sort_key',
0714 'topic_id',
0715 'ex_fid_ary',
0716 'post_visibility',
0717 'author_ary',
0718 'author_name',
0719 );
0720 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_author_modify_search_key', compact($vars)));
0721
0722 $search_key = md5(implode('#', $search_key_array));
0723
0724 if ($start < 0)
0725 {
0726 $start = 0;
0727 }
0728
0729 // try reading the results from cache
0730 $result_count = 0;
0731 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0732 {
0733 return $result_count;
0734 }
0735
0736 $id_ary = array();
0737
0738 // Create some display specific sql strings
0739 if ($author_name)
0740 {
0741 // first one matches post of registered users, second one guests and deleted users
0742 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
0743 }
0744 else
0745 {
0746 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
0747 }
0748 $sql_fora = (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0749 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : '';
0750 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0751 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : '';
0752
0753 // Build sql strings for sorting
0754 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0755 $sql_sort_table = $sql_sort_join = '';
0756 switch ($sql_sort[0])
0757 {
0758 case 'u':
0759 $sql_sort_table = USERS_TABLE . ' u, ';
0760 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0761 break;
0762
0763 case 't':
0764 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : '';
0765 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : '';
0766 break;
0767
0768 case 'f':
0769 $sql_sort_table = FORUMS_TABLE . ' f, ';
0770 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0771 break;
0772 }
0773
0774 $m_approve_fid_sql = ' AND ' . $post_visibility;
0775
0776 /**
0777 * Allow changing the query used to search for posts by author in fulltext_mysql
0778 *
0779 * @event core.search_mysql_author_query_before
0780 * @var int result_count The previous result count for the format of the query.
0781 * Set to 0 to force a re-count
0782 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen
0783 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table
0784 * @var string type Either "posts" or "topics" specifying the type of search being made
0785 * @var array author_ary Array of user_id containing the users to filter the results to
0786 * @var string author_name An extra username to search on
0787 * @var string sql_author SQL WHERE condition for the post author ids
0788 * @var int topic_id Limit the search to this topic_id only
0789 * @var string sql_topic_id SQL of topic_id
0790 * @var string sort_by_sql The possible predefined sort types
0791 * @var string sort_key The sort type used from the possible sort types
0792 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0793 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0794 * @var string sort_days Time, in days, that the oldest post showing can have
0795 * @var string sql_time The SQL to search on the time specifyed by sort_days
0796 * @var bool firstpost_only Wether or not to search only on the first post of the topics
0797 * @var string sql_firstpost The SQL with the conditions to join the tables when using firstpost_only
0798 * @var array ex_fid_ary Forum ids that must not be searched on
0799 * @var array sql_fora SQL query for ex_fid_ary
0800 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions
0801 * @var int start How many posts to skip in the search results (used for pagination)
0802 * @since 3.1.5-RC1
0803 */
0804 $vars = array(
0805 'result_count',
0806 'sql_sort_table',
0807 'sql_sort_join',
0808 'type',
0809 'author_ary',
0810 'author_name',
0811 'sql_author',
0812 'topic_id',
0813 'sql_topic_id',
0814 'sort_by_sql',
0815 'sort_key',
0816 'sort_dir',
0817 'sql_sort',
0818 'sort_days',
0819 'sql_time',
0820 'firstpost_only',
0821 'sql_firstpost',
0822 'ex_fid_ary',
0823 'sql_fora',
0824 'm_approve_fid_sql',
0825 'start',
0826 );
0827 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars)));
0828
0829 // If the cache was completely empty count the results
0830 $sql_select = ($type == 'posts') ? 'p.post_id' : 't.topic_id';
0831 $sql_select .= $sort_by_sql[$sort_key] ? ", {$sort_by_sql[$sort_key]}" : '';
0832
0833 // Build the query for really selecting the post_ids
0834 if ($type == 'posts')
0835 {
0836 $sql = "SELECT $sql_select
0837 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "
0838 WHERE $sql_author
0839 $sql_topic_id
0840 $sql_firstpost
0841 $m_approve_fid_sql
0842 $sql_fora
0843 $sql_sort_join
0844 $sql_time
0845 ORDER BY $sql_sort";
0846 $field = 'post_id';
0847 }
0848 else
0849 {
0850 $sql = "SELECT $sql_select
0851 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
0852 WHERE $sql_author
0853 $sql_topic_id
0854 $sql_firstpost
0855 $m_approve_fid_sql
0856 $sql_fora
0857 AND t.topic_id = p.topic_id
0858 $sql_sort_join
0859 $sql_time
0860 GROUP BY $sql_select
0861 ORDER BY $sql_sort";
0862 $field = 'topic_id';
0863 }
0864
0865 // Only read one block of posts from the db and then cache it
0866 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0867
0868 while ($row = $this->db->sql_fetchrow($result))
0869 {
0870 $id_ary[] = (int) $row[$field];
0871 }
0872 $this->db->sql_freeresult($result);
0873
0874 // retrieve the total result count if needed
0875 if (!$result_count)
0876 {
0877 $sql_found_rows = str_replace("SELECT $sql_select", "SELECT COUNT(*) as result_count", $sql);
0878 $result = $this->db->sql_query($sql_found_rows);
0879 $result_count = ($type == 'posts') ? (int) $this->db->sql_fetchfield('result_count') : count($this->db->sql_fetchrowset($result));
0880
0881 $this->db->sql_freeresult($result);
0882
0883 if (!$result_count)
0884 {
0885 return false;
0886 }
0887 }
0888
0889 if ($start >= $result_count)
0890 {
0891 $start = floor(($result_count - 1) / $per_page) * $per_page;
0892
0893 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0894 while ($row = $this->db->sql_fetchrow($result))
0895 {
0896 $id_ary[] = (int) $row[$field];
0897 }
0898 $this->db->sql_freeresult($result);
0899
0900 $id_ary = array_unique($id_ary);
0901 }
0902
0903 if (count($id_ary))
0904 {
0905 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
0906 $id_ary = array_slice($id_ary, 0, $per_page);
0907
0908 return $result_count;
0909 }
0910 return false;
0911 }
0912
0913 /**
0914 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated
0915 *
0916 * @param string $mode contains the post mode: edit, post, reply, quote ...
0917 * @param int $post_id contains the post id of the post to index
0918 * @param string $message contains the post text of the post
0919 * @param string $subject contains the subject of the post to index
0920 * @param int $poster_id contains the user id of the poster
0921 * @param int $forum_id contains the forum id of parent forum of the post
0922 */
0923 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id)
0924 {
0925 // Split old and new post/subject to obtain array of words
0926 $split_text = $this->split_message($message);
0927 $split_title = ($subject) ? $this->split_message($subject) : array();
0928
0929 $words = array_unique(array_merge($split_text, $split_title));
0930
0931 /**
0932 * Event to modify method arguments and words before the MySQL search index is updated
0933 *
0934 * @event core.search_mysql_index_before
0935 * @var string mode Contains the post mode: edit, post, reply, quote
0936 * @var int post_id The id of the post which is modified/created
0937 * @var string message New or updated post content
0938 * @var string subject New or updated post subject
0939 * @var int poster_id Post author's user id
0940 * @var int forum_id The id of the forum in which the post is located
0941 * @var array words List of words added to the index
0942 * @var array split_text Array of words from the message
0943 * @var array split_title Array of words from the title
0944 * @since 3.2.3-RC1
0945 */
0946 $vars = array(
0947 'mode',
0948 'post_id',
0949 'message',
0950 'subject',
0951 'poster_id',
0952 'forum_id',
0953 'words',
0954 'split_text',
0955 'split_title',
0956 );
0957 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_index_before', compact($vars)));
0958
0959 unset($split_text);
0960 unset($split_title);
0961
0962 // destroy cached search results containing any of the words removed or added
0963 $this->destroy_cache($words, array($poster_id));
0964
0965 unset($words);
0966 }
0967
0968 /**
0969 * Destroy cached results, that might be outdated after deleting a post
0970 */
0971 public function index_remove($post_ids, $author_ids, $forum_ids)
0972 {
0973 $this->destroy_cache(array(), array_unique($author_ids));
0974 }
0975
0976 /**
0977 * Destroy old cache entries
0978 */
0979 public function tidy()
0980 {
0981 // destroy too old cached search results
0982 $this->destroy_cache(array());
0983
0984 $this->config->set('search_last_gc', time(), false);
0985 }
0986
0987 /**
0988 * Create fulltext index
0989 *
0990 * @return string|bool error string is returned incase of errors otherwise false
0991 */
0992 public function create_index($acp_module, $u_action)
0993 {
0994 // Make sure we can actually use MySQL with fulltext indexes
0995 if ($error = $this->init())
0996 {
0997 return $error;
0998 }
0999
1000 if (empty($this->stats))
1001 {
1002 $this->get_stats();
1003 }
1004
1005 $alter_list = array();
1006
1007 if (!isset($this->stats['post_subject']))
1008 {
1009 $alter_entry = array();
1010 $alter_entry[] = 'MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL';
1011 $alter_entry[] = 'ADD FULLTEXT (post_subject)';
1012 $alter_list[] = $alter_entry;
1013 }
1014
1015 if (!isset($this->stats['post_content']))
1016 {
1017 $alter_entry = array();
1018 $alter_entry[] = 'MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL';
1019 $alter_entry[] = 'ADD FULLTEXT post_content (post_text, post_subject)';
1020 $alter_list[] = $alter_entry;
1021 }
1022
1023 $sql_queries = [];
1024
1025 foreach ($alter_list as $alter)
1026 {
1027 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter);
1028 }
1029
1030 if (!isset($this->stats['post_text']))
1031 {
1032 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)';
1033 }
1034
1035 $stats = $this->stats;
1036
1037 /**
1038 * Event to modify SQL queries before the MySQL search index is created
1039 *
1040 * @event core.search_mysql_create_index_before
1041 * @var array sql_queries Array with queries for creating the search index
1042 * @var array stats Array with statistics of the current index (read only)
1043 * @since 3.2.3-RC1
1044 */
1045 $vars = array(
1046 'sql_queries',
1047 'stats',
1048 );
1049 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_create_index_before', compact($vars)));
1050
1051 foreach ($sql_queries as $sql_query)
1052 {
1053 $this->db->sql_query($sql_query);
1054 }
1055
1056 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1057
1058 return false;
1059 }
1060
1061 /**
1062 * Drop fulltext index
1063 *
1064 * @return string|bool error string is returned incase of errors otherwise false
1065 */
1066 public function delete_index($acp_module, $u_action)
1067 {
1068 // Make sure we can actually use MySQL with fulltext indexes
1069 if ($error = $this->init())
1070 {
1071 return $error;
1072 }
1073
1074 if (empty($this->stats))
1075 {
1076 $this->get_stats();
1077 }
1078
1079 $alter = array();
1080
1081 if (isset($this->stats['post_subject']))
1082 {
1083 $alter[] = 'DROP INDEX post_subject';
1084 }
1085
1086 if (isset($this->stats['post_content']))
1087 {
1088 $alter[] = 'DROP INDEX post_content';
1089 }
1090
1091 if (isset($this->stats['post_text']))
1092 {
1093 $alter[] = 'DROP INDEX post_text';
1094 }
1095
1096 $sql_queries = [];
1097
1098 if (count($alter))
1099 {
1100 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter);
1101 }
1102
1103 $stats = $this->stats;
1104
1105 /**
1106 * Event to modify SQL queries before the MySQL search index is deleted
1107 *
1108 * @event core.search_mysql_delete_index_before
1109 * @var array sql_queries Array with queries for deleting the search index
1110 * @var array stats Array with statistics of the current index (read only)
1111 * @since 3.2.3-RC1
1112 */
1113 $vars = array(
1114 'sql_queries',
1115 'stats',
1116 );
1117 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_delete_index_before', compact($vars)));
1118
1119 foreach ($sql_queries as $sql_query)
1120 {
1121 $this->db->sql_query($sql_query);
1122 }
1123
1124 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1125
1126 return false;
1127 }
1128
1129 /**
1130 * Returns true if both FULLTEXT indexes exist
1131 */
1132 public function index_created()
1133 {
1134 if (empty($this->stats))
1135 {
1136 $this->get_stats();
1137 }
1138
1139 return isset($this->stats['post_subject']) && isset($this->stats['post_content']) && isset($this->stats['post_text']);
1140 }
1141
1142 /**
1143 * Returns an associative array containing information about the indexes
1144 */
1145 public function index_stats()
1146 {
1147 if (empty($this->stats))
1148 {
1149 $this->get_stats();
1150 }
1151
1152 return array(
1153 $this->user->lang['FULLTEXT_MYSQL_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0,
1154 );
1155 }
1156
1157 /**
1158 * Computes the stats and store them in the $this->stats associative array
1159 */
1160 protected function get_stats()
1161 {
1162 if (strpos($this->db->get_sql_layer(), 'mysql') === false)
1163 {
1164 $this->stats = array();
1165 return;
1166 }
1167
1168 $sql = 'SHOW INDEX
1169 FROM ' . POSTS_TABLE;
1170 $result = $this->db->sql_query($sql);
1171
1172 while ($row = $this->db->sql_fetchrow($result))
1173 {
1174 // deal with older MySQL versions which didn't use Index_type
1175 $index_type = (isset($row['Index_type'])) ? $row['Index_type'] : $row['Comment'];
1176
1177 if ($index_type == 'FULLTEXT')
1178 {
1179 if ($row['Key_name'] == 'post_subject')
1180 {
1181 $this->stats['post_subject'] = $row;
1182 }
1183 else if ($row['Key_name'] == 'post_text')
1184 {
1185 $this->stats['post_text'] = $row;
1186 }
1187 else if ($row['Key_name'] == 'post_content')
1188 {
1189 $this->stats['post_content'] = $row;
1190 }
1191 }
1192 }
1193 $this->db->sql_freeresult($result);
1194
1195 $this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE);
1196 }
1197
1198 /**
1199 * Display a note, that UTF-8 support is not available with certain versions of PHP
1200 *
1201 * @return associative array containing template and config variables
1202 */
1203 public function acp()
1204 {
1205 $tpl = '
1206 <dl>
1207 <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>
1208 <dd>' . $this->config['fulltext_mysql_min_word_len'] . '</dd>
1209 </dl>
1210 <dl>
1211 <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>
1212 <dd>' . $this->config['fulltext_mysql_max_word_len'] . '</dd>
1213 </dl>
1214 ';
1215
1216 // These are fields required in the config table
1217 return array(
1218 'tpl' => $tpl,
1219 'config' => array()
1220 );
1221 }
1222 }
1223