Verzeichnisstruktur phpBB-3.2.0
- Veröffentlicht
- 06.01.2017
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 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/incompatiblity occurred
0154 */
0155 public function init()
0156 {
0157 if ($this->db->get_sql_layer() != 'mysql4' && $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 =
0177 $engine === 'MyISAM' ||
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 $engine === 'InnoDB' &&
0183 phpbb_version_compare($this->db->sql_server_info(true), '5.6.8', '>=');
0184
0185 if (!$fulltext_supported)
0186 {
0187 return $this->user->lang['FULLTEXT_MYSQL_NOT_SUPPORTED'];
0188 }
0189
0190 $sql = 'SHOW VARIABLES
0191 LIKE \'ft\_%\'';
0192 $result = $this->db->sql_query($sql);
0193
0194 $mysql_info = array();
0195 while ($row = $this->db->sql_fetchrow($result))
0196 {
0197 $mysql_info[$row['Variable_name']] = $row['Value'];
0198 }
0199 $this->db->sql_freeresult($result);
0200
0201 $this->config->set('fulltext_mysql_max_word_len', $mysql_info['ft_max_word_len']);
0202 $this->config->set('fulltext_mysql_min_word_len', $mysql_info['ft_min_word_len']);
0203
0204 return false;
0205 }
0206
0207 /**
0208 * Splits keywords entered by a user into an array of words stored in $this->split_words
0209 * Stores the tidied search query in $this->search_query
0210 *
0211 * @param string &$keywords Contains the keyword as entered by the user
0212 * @param string $terms is either 'all' or 'any'
0213 * @return bool false if no valid keywords were found and otherwise true
0214 */
0215 public function split_keywords(&$keywords, $terms)
0216 {
0217 if ($terms == 'all')
0218 {
0219 $match = array('#\sand\s#iu', '#\sor\s#iu', '#\snot\s#iu', '#(^|\s)\+#', '#(^|\s)-#', '#(^|\s)\|#');
0220 $replace = array(' +', ' |', ' -', ' +', ' -', ' |');
0221
0222 $keywords = preg_replace($match, $replace, $keywords);
0223 }
0224
0225 // Filter out as above
0226 $split_keywords = preg_replace("#[\n\r\t]+#", ' ', trim(htmlspecialchars_decode($keywords)));
0227
0228 // Split words
0229 $split_keywords = preg_replace('#([^\p{L}\p{N}\'*"()])#u', '$1$1', str_replace('\'\'', '\' \'', trim($split_keywords)));
0230 $matches = array();
0231 preg_match_all('#(?:[^\p{L}\p{N}*"()]|^)([+\-|]?(?:[\p{L}\p{N}*"()]+\'?)*[\p{L}\p{N}*"()])(?:[^\p{L}\p{N}*"()]|$)#u', $split_keywords, $matches);
0232 $this->split_words = $matches[1];
0233
0234 // We limit the number of allowed keywords to minimize load on the database
0235 if ($this->config['max_num_search_keywords'] && sizeof($this->split_words) > $this->config['max_num_search_keywords'])
0236 {
0237 trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], sizeof($this->split_words)));
0238 }
0239
0240 // to allow phrase search, we need to concatenate quoted words
0241 $tmp_split_words = array();
0242 $phrase = '';
0243 foreach ($this->split_words as $word)
0244 {
0245 if ($phrase)
0246 {
0247 $phrase .= ' ' . $word;
0248 if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1)
0249 {
0250 $tmp_split_words[] = $phrase;
0251 $phrase = '';
0252 }
0253 }
0254 else if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1)
0255 {
0256 $phrase = $word;
0257 }
0258 else
0259 {
0260 $tmp_split_words[] = $word;
0261 }
0262 }
0263 if ($phrase)
0264 {
0265 $tmp_split_words[] = $phrase;
0266 }
0267
0268 $this->split_words = $tmp_split_words;
0269
0270 unset($tmp_split_words);
0271 unset($phrase);
0272
0273 foreach ($this->split_words as $i => $word)
0274 {
0275 $clean_word = preg_replace('#^[+\-|"]#', '', $word);
0276
0277 // check word length
0278 $clean_len = utf8_strlen(str_replace('*', '', $clean_word));
0279 if (($clean_len < $this->config['fulltext_mysql_min_word_len']) || ($clean_len > $this->config['fulltext_mysql_max_word_len']))
0280 {
0281 $this->common_words[] = $word;
0282 unset($this->split_words[$i]);
0283 }
0284 }
0285
0286 if ($terms == 'any')
0287 {
0288 $this->search_query = '';
0289 foreach ($this->split_words as $word)
0290 {
0291 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0) || (strpos($word, '|') === 0))
0292 {
0293 $word = substr($word, 1);
0294 }
0295 $this->search_query .= $word . ' ';
0296 }
0297 }
0298 else
0299 {
0300 $this->search_query = '';
0301 foreach ($this->split_words as $word)
0302 {
0303 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0))
0304 {
0305 $this->search_query .= $word . ' ';
0306 }
0307 else if (strpos($word, '|') === 0)
0308 {
0309 $this->search_query .= substr($word, 1) . ' ';
0310 }
0311 else
0312 {
0313 $this->search_query .= '+' . $word . ' ';
0314 }
0315 }
0316 }
0317
0318 $this->search_query = utf8_htmlspecialchars($this->search_query);
0319
0320 if ($this->search_query)
0321 {
0322 $this->split_words = array_values($this->split_words);
0323 sort($this->split_words);
0324 return true;
0325 }
0326 return false;
0327 }
0328
0329 /**
0330 * Turns text into an array of words
0331 * @param string $text contains post text/subject
0332 */
0333 public function split_message($text)
0334 {
0335 // Split words
0336 $text = preg_replace('#([^\p{L}\p{N}\'*])#u', '$1$1', str_replace('\'\'', '\' \'', trim($text)));
0337 $matches = array();
0338 preg_match_all('#(?:[^\p{L}\p{N}*]|^)([+\-|]?(?:[\p{L}\p{N}*]+\'?)*[\p{L}\p{N}*])(?:[^\p{L}\p{N}*]|$)#u', $text, $matches);
0339 $text = $matches[1];
0340
0341 // remove too short or too long words
0342 $text = array_values($text);
0343 for ($i = 0, $n = sizeof($text); $i < $n; $i++)
0344 {
0345 $text[$i] = trim($text[$i]);
0346 if (utf8_strlen($text[$i]) < $this->config['fulltext_mysql_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_mysql_max_word_len'])
0347 {
0348 unset($text[$i]);
0349 }
0350 }
0351
0352 return array_values($text);
0353 }
0354
0355 /**
0356 * Performs a search on keywords depending on display specific params. You have to run split_keywords() first
0357 *
0358 * @param string $type contains either posts or topics depending on what should be searched for
0359 * @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)
0360 * @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)
0361 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0362 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0363 * @param string $sort_dir is either a or d representing ASC and DESC
0364 * @param string $sort_days specifies the maximum amount of days a post may be old
0365 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0366 * @param string $post_visibility specifies which types of posts the user can view in which forums
0367 * @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
0368 * @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty
0369 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0370 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0371 * @param int $start indicates the first index of the page
0372 * @param int $per_page number of ids each page is supposed to contain
0373 * @return boolean|int total number of results
0374 */
0375 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)
0376 {
0377 // No keywords? No posts
0378 if (!$this->search_query)
0379 {
0380 return false;
0381 }
0382
0383 // generate a search_key from all the options to identify the results
0384 $search_key_array = array(
0385 implode(', ', $this->split_words),
0386 $type,
0387 $fields,
0388 $terms,
0389 $sort_days,
0390 $sort_key,
0391 $topic_id,
0392 implode(',', $ex_fid_ary),
0393 $post_visibility,
0394 implode(',', $author_ary)
0395 );
0396
0397 /**
0398 * Allow changing the search_key for cached results
0399 *
0400 * @event core.search_mysql_by_keyword_modify_search_key
0401 * @var array search_key_array Array with search parameters to generate the search_key
0402 * @var string type Searching type ('posts', 'topics')
0403 * @var string fields Searching fields ('titleonly', 'msgonly', 'firstpost', 'all')
0404 * @var string terms Searching terms ('all', 'any')
0405 * @var int sort_days Time, in days, of the oldest possible post to list
0406 * @var string sort_key The sort type used from the possible sort types
0407 * @var int topic_id Limit the search to this topic_id only
0408 * @var array ex_fid_ary Which forums not to search on
0409 * @var string post_visibility Post visibility data
0410 * @var array author_ary Array of user_id containing the users to filter the results to
0411 * @since 3.1.7-RC1
0412 */
0413 $vars = array(
0414 'search_key_array',
0415 'type',
0416 'fields',
0417 'terms',
0418 'sort_days',
0419 'sort_key',
0420 'topic_id',
0421 'ex_fid_ary',
0422 'post_visibility',
0423 'author_ary',
0424 );
0425 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_keyword_modify_search_key', compact($vars)));
0426
0427 $search_key = md5(implode('#', $search_key_array));
0428
0429 if ($start < 0)
0430 {
0431 $start = 0;
0432 }
0433
0434 // try reading the results from cache
0435 $result_count = 0;
0436 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0437 {
0438 return $result_count;
0439 }
0440
0441 $id_ary = array();
0442
0443 $join_topic = ($type == 'posts') ? false : true;
0444
0445 // Build sql strings for sorting
0446 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0447 $sql_sort_table = $sql_sort_join = '';
0448
0449 switch ($sql_sort[0])
0450 {
0451 case 'u':
0452 $sql_sort_table = USERS_TABLE . ' u, ';
0453 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0454 break;
0455
0456 case 't':
0457 $join_topic = true;
0458 break;
0459
0460 case 'f':
0461 $sql_sort_table = FORUMS_TABLE . ' f, ';
0462 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0463 break;
0464 }
0465
0466 // Build some display specific sql strings
0467 switch ($fields)
0468 {
0469 case 'titleonly':
0470 $sql_match = 'p.post_subject';
0471 $sql_match_where = ' AND p.post_id = t.topic_first_post_id';
0472 $join_topic = true;
0473 break;
0474
0475 case 'msgonly':
0476 $sql_match = 'p.post_text';
0477 $sql_match_where = '';
0478 break;
0479
0480 case 'firstpost':
0481 $sql_match = 'p.post_subject, p.post_text';
0482 $sql_match_where = ' AND p.post_id = t.topic_first_post_id';
0483 $join_topic = true;
0484 break;
0485
0486 default:
0487 $sql_match = 'p.post_subject, p.post_text';
0488 $sql_match_where = '';
0489 break;
0490 }
0491
0492 $search_query = $this->search_query;
0493
0494 /**
0495 * Allow changing the query used to search for posts using fulltext_mysql
0496 *
0497 * @event core.search_mysql_keywords_main_query_before
0498 * @var string search_query The parsed keywords used for this search
0499 * @var int result_count The previous result count for the format of the query.
0500 * Set to 0 to force a re-count
0501 * @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED
0502 * @var array author_ary Array of user_id containing the users to filter the results to
0503 * @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant)
0504 * @var array ex_fid_ary Which forums not to search on
0505 * @var int topic_id Limit the search to this topic_id only
0506 * @var string sql_sort_table Extra tables to include in the SQL query.
0507 * Used in conjunction with sql_sort_join
0508 * @var string sql_sort_join SQL conditions to join all the tables used together.
0509 * Used in conjunction with sql_sort_table
0510 * @var int sort_days Time, in days, of the oldest possible post to list
0511 * @var string sql_match Which columns to do the search on.
0512 * @var string sql_match_where Extra conditions to use to properly filter the matching process
0513 * @var string sort_by_sql The possible predefined sort types
0514 * @var string sort_key The sort type used from the possible sort types
0515 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0516 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0517 * @var int start How many posts to skip in the search results (used for pagination)
0518 * @since 3.1.5-RC1
0519 */
0520 $vars = array(
0521 'search_query',
0522 'result_count',
0523 'join_topic',
0524 'author_ary',
0525 'author_name',
0526 'ex_fid_ary',
0527 'topic_id',
0528 'sql_sort_table',
0529 'sql_sort_join',
0530 'sort_days',
0531 'sql_match',
0532 'sql_match_where',
0533 'sort_by_sql',
0534 'sort_key',
0535 'sort_dir',
0536 'sql_sort',
0537 'start',
0538 );
0539 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_keywords_main_query_before', compact($vars)));
0540
0541 $sql_select = (!$result_count) ? 'SQL_CALC_FOUND_ROWS ' : '';
0542 $sql_select = ($type == 'posts') ? $sql_select . 'p.post_id' : 'DISTINCT ' . $sql_select . 't.topic_id';
0543 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : '';
0544 $field = ($type == 'posts') ? 'post_id' : 'topic_id';
0545 if (sizeof($author_ary) && $author_name)
0546 {
0547 // first one matches post of registered users, second one guests and deleted users
0548 $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 . ')';
0549 }
0550 else if (sizeof($author_ary))
0551 {
0552 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary);
0553 }
0554 else
0555 {
0556 $sql_author = '';
0557 }
0558
0559 $sql_where_options = $sql_sort_join;
0560 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : '';
0561 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : '';
0562 $sql_where_options .= (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0563 $sql_where_options .= ' AND ' . $post_visibility;
0564 $sql_where_options .= $sql_author;
0565 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0566 $sql_where_options .= $sql_match_where;
0567
0568 $sql = "SELECT $sql_select
0569 FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p
0570 WHERE MATCH ($sql_match) AGAINST ('" . $this->db->sql_escape(htmlspecialchars_decode($this->search_query)) . "' IN BOOLEAN MODE)
0571 $sql_where_options
0572 ORDER BY $sql_sort";
0573 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0574
0575 while ($row = $this->db->sql_fetchrow($result))
0576 {
0577 $id_ary[] = (int) $row[$field];
0578 }
0579 $this->db->sql_freeresult($result);
0580
0581 $id_ary = array_unique($id_ary);
0582
0583 // if the total result count is not cached yet, retrieve it from the db
0584 if (!$result_count)
0585 {
0586 $sql_found_rows = 'SELECT FOUND_ROWS() as result_count';
0587 $result = $this->db->sql_query($sql_found_rows);
0588 $result_count = (int) $this->db->sql_fetchfield('result_count');
0589 $this->db->sql_freeresult($result);
0590
0591 if (!$result_count)
0592 {
0593 return false;
0594 }
0595 }
0596
0597 if ($start >= $result_count)
0598 {
0599 $start = floor(($result_count - 1) / $per_page) * $per_page;
0600
0601 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0602
0603 while ($row = $this->db->sql_fetchrow($result))
0604 {
0605 $id_ary[] = (int) $row[$field];
0606 }
0607 $this->db->sql_freeresult($result);
0608
0609 $id_ary = array_unique($id_ary);
0610 }
0611
0612 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page
0613 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir);
0614 $id_ary = array_slice($id_ary, 0, (int) $per_page);
0615
0616 return $result_count;
0617 }
0618
0619 /**
0620 * Performs a search on an author's posts without caring about message contents. Depends on display specific params
0621 *
0622 * @param string $type contains either posts or topics depending on what should be searched for
0623 * @param boolean $firstpost_only if true, only topic starting posts will be considered
0624 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0625 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0626 * @param string $sort_dir is either a or d representing ASC and DESC
0627 * @param string $sort_days specifies the maximum amount of days a post may be old
0628 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0629 * @param string $post_visibility specifies which types of posts the user can view in which forums
0630 * @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
0631 * @param array $author_ary an array of author ids
0632 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0633 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0634 * @param int $start indicates the first index of the page
0635 * @param int $per_page number of ids each page is supposed to contain
0636 * @return boolean|int total number of results
0637 */
0638 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)
0639 {
0640 // No author? No posts
0641 if (!sizeof($author_ary))
0642 {
0643 return 0;
0644 }
0645
0646 // generate a search_key from all the options to identify the results
0647 $search_key_array = array(
0648 '',
0649 $type,
0650 ($firstpost_only) ? 'firstpost' : '',
0651 '',
0652 '',
0653 $sort_days,
0654 $sort_key,
0655 $topic_id,
0656 implode(',', $ex_fid_ary),
0657 $post_visibility,
0658 implode(',', $author_ary),
0659 $author_name,
0660 );
0661
0662 /**
0663 * Allow changing the search_key for cached results
0664 *
0665 * @event core.search_mysql_by_author_modify_search_key
0666 * @var array search_key_array Array with search parameters to generate the search_key
0667 * @var string type Searching type ('posts', 'topics')
0668 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered
0669 * @var int sort_days Time, in days, of the oldest possible post to list
0670 * @var string sort_key The sort type used from the possible sort types
0671 * @var int topic_id Limit the search to this topic_id only
0672 * @var array ex_fid_ary Which forums not to search on
0673 * @var string post_visibility Post visibility data
0674 * @var array author_ary Array of user_id containing the users to filter the results to
0675 * @var string author_name The username to search on
0676 * @since 3.1.7-RC1
0677 */
0678 $vars = array(
0679 'search_key_array',
0680 'type',
0681 'firstpost_only',
0682 'sort_days',
0683 'sort_key',
0684 'topic_id',
0685 'ex_fid_ary',
0686 'post_visibility',
0687 'author_ary',
0688 'author_name',
0689 );
0690 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_author_modify_search_key', compact($vars)));
0691
0692 $search_key = md5(implode('#', $search_key_array));
0693
0694 if ($start < 0)
0695 {
0696 $start = 0;
0697 }
0698
0699 // try reading the results from cache
0700 $result_count = 0;
0701 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0702 {
0703 return $result_count;
0704 }
0705
0706 $id_ary = array();
0707
0708 // Create some display specific sql strings
0709 if ($author_name)
0710 {
0711 // first one matches post of registered users, second one guests and deleted users
0712 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
0713 }
0714 else
0715 {
0716 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
0717 }
0718 $sql_fora = (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0719 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : '';
0720 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0721 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : '';
0722
0723 // Build sql strings for sorting
0724 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0725 $sql_sort_table = $sql_sort_join = '';
0726 switch ($sql_sort[0])
0727 {
0728 case 'u':
0729 $sql_sort_table = USERS_TABLE . ' u, ';
0730 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0731 break;
0732
0733 case 't':
0734 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : '';
0735 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : '';
0736 break;
0737
0738 case 'f':
0739 $sql_sort_table = FORUMS_TABLE . ' f, ';
0740 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0741 break;
0742 }
0743
0744 $m_approve_fid_sql = ' AND ' . $post_visibility;
0745
0746 /**
0747 * Allow changing the query used to search for posts by author in fulltext_mysql
0748 *
0749 * @event core.search_mysql_author_query_before
0750 * @var int result_count The previous result count for the format of the query.
0751 * Set to 0 to force a re-count
0752 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen
0753 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table
0754 * @var string type Either "posts" or "topics" specifying the type of search being made
0755 * @var array author_ary Array of user_id containing the users to filter the results to
0756 * @var string author_name An extra username to search on
0757 * @var string sql_author SQL WHERE condition for the post author ids
0758 * @var int topic_id Limit the search to this topic_id only
0759 * @var string sql_topic_id SQL of topic_id
0760 * @var string sort_by_sql The possible predefined sort types
0761 * @var string sort_key The sort type used from the possible sort types
0762 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0763 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0764 * @var string sort_days Time, in days, that the oldest post showing can have
0765 * @var string sql_time The SQL to search on the time specifyed by sort_days
0766 * @var bool firstpost_only Wether or not to search only on the first post of the topics
0767 * @var string sql_firstpost The SQL with the conditions to join the tables when using firstpost_only
0768 * @var array ex_fid_ary Forum ids that must not be searched on
0769 * @var array sql_fora SQL query for ex_fid_ary
0770 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions
0771 * @var int start How many posts to skip in the search results (used for pagination)
0772 * @since 3.1.5-RC1
0773 */
0774 $vars = array(
0775 'result_count',
0776 'sql_sort_table',
0777 'sql_sort_join',
0778 'type',
0779 'author_ary',
0780 'author_name',
0781 'sql_author',
0782 'topic_id',
0783 'sql_topic_id',
0784 'sort_by_sql',
0785 'sort_key',
0786 'sort_dir',
0787 'sql_sort',
0788 'sort_days',
0789 'sql_time',
0790 'firstpost_only',
0791 'sql_firstpost',
0792 'ex_fid_ary',
0793 'sql_fora',
0794 'm_approve_fid_sql',
0795 'start',
0796 );
0797 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars)));
0798
0799 // If the cache was completely empty count the results
0800 $calc_results = ($result_count) ? '' : 'SQL_CALC_FOUND_ROWS ';
0801
0802 // Build the query for really selecting the post_ids
0803 if ($type == 'posts')
0804 {
0805 $sql = "SELECT {$calc_results}p.post_id
0806 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "
0807 WHERE $sql_author
0808 $sql_topic_id
0809 $sql_firstpost
0810 $m_approve_fid_sql
0811 $sql_fora
0812 $sql_sort_join
0813 $sql_time
0814 ORDER BY $sql_sort";
0815 $field = 'post_id';
0816 }
0817 else
0818 {
0819 $sql = "SELECT {$calc_results}t.topic_id
0820 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
0821 WHERE $sql_author
0822 $sql_topic_id
0823 $sql_firstpost
0824 $m_approve_fid_sql
0825 $sql_fora
0826 AND t.topic_id = p.topic_id
0827 $sql_sort_join
0828 $sql_time
0829 GROUP BY t.topic_id
0830 ORDER BY $sql_sort";
0831 $field = 'topic_id';
0832 }
0833
0834 // Only read one block of posts from the db and then cache it
0835 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0836
0837 while ($row = $this->db->sql_fetchrow($result))
0838 {
0839 $id_ary[] = (int) $row[$field];
0840 }
0841 $this->db->sql_freeresult($result);
0842
0843 // retrieve the total result count if needed
0844 if (!$result_count)
0845 {
0846 $sql_found_rows = 'SELECT FOUND_ROWS() as result_count';
0847 $result = $this->db->sql_query($sql_found_rows);
0848 $result_count = (int) $this->db->sql_fetchfield('result_count');
0849 $this->db->sql_freeresult($result);
0850
0851 if (!$result_count)
0852 {
0853 return false;
0854 }
0855 }
0856
0857 if ($start >= $result_count)
0858 {
0859 $start = floor(($result_count - 1) / $per_page) * $per_page;
0860
0861 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0862 while ($row = $this->db->sql_fetchrow($result))
0863 {
0864 $id_ary[] = (int) $row[$field];
0865 }
0866 $this->db->sql_freeresult($result);
0867
0868 $id_ary = array_unique($id_ary);
0869 }
0870
0871 if (sizeof($id_ary))
0872 {
0873 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
0874 $id_ary = array_slice($id_ary, 0, $per_page);
0875
0876 return $result_count;
0877 }
0878 return false;
0879 }
0880
0881 /**
0882 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated
0883 *
0884 * @param string $mode contains the post mode: edit, post, reply, quote ...
0885 * @param int $post_id contains the post id of the post to index
0886 * @param string $message contains the post text of the post
0887 * @param string $subject contains the subject of the post to index
0888 * @param int $poster_id contains the user id of the poster
0889 * @param int $forum_id contains the forum id of parent forum of the post
0890 */
0891 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id)
0892 {
0893 // Split old and new post/subject to obtain array of words
0894 $split_text = $this->split_message($message);
0895 $split_title = ($subject) ? $this->split_message($subject) : array();
0896
0897 $words = array_unique(array_merge($split_text, $split_title));
0898
0899 unset($split_text);
0900 unset($split_title);
0901
0902 // destroy cached search results containing any of the words removed or added
0903 $this->destroy_cache($words, array($poster_id));
0904
0905 unset($words);
0906 }
0907
0908 /**
0909 * Destroy cached results, that might be outdated after deleting a post
0910 */
0911 public function index_remove($post_ids, $author_ids, $forum_ids)
0912 {
0913 $this->destroy_cache(array(), array_unique($author_ids));
0914 }
0915
0916 /**
0917 * Destroy old cache entries
0918 */
0919 public function tidy()
0920 {
0921 // destroy too old cached search results
0922 $this->destroy_cache(array());
0923
0924 $this->config->set('search_last_gc', time(), false);
0925 }
0926
0927 /**
0928 * Create fulltext index
0929 *
0930 * @return string|bool error string is returned incase of errors otherwise false
0931 */
0932 public function create_index($acp_module, $u_action)
0933 {
0934 // Make sure we can actually use MySQL with fulltext indexes
0935 if ($error = $this->init())
0936 {
0937 return $error;
0938 }
0939
0940 if (empty($this->stats))
0941 {
0942 $this->get_stats();
0943 }
0944
0945 $alter_list = array();
0946
0947 if (!isset($this->stats['post_subject']))
0948 {
0949 $alter_entry = array();
0950 if ($this->db->get_sql_layer() == 'mysqli' || version_compare($this->db->sql_server_info(true), '4.1.3', '>='))
0951 {
0952 $alter_entry[] = 'MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL';
0953 }
0954 else
0955 {
0956 $alter_entry[] = 'MODIFY post_subject text NOT NULL';
0957 }
0958 $alter_entry[] = 'ADD FULLTEXT (post_subject)';
0959 $alter_list[] = $alter_entry;
0960 }
0961
0962 if (!isset($this->stats['post_content']))
0963 {
0964 $alter_entry = array();
0965 if ($this->db->get_sql_layer() == 'mysqli' || version_compare($this->db->sql_server_info(true), '4.1.3', '>='))
0966 {
0967 $alter_entry[] = 'MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL';
0968 }
0969 else
0970 {
0971 $alter_entry[] = 'MODIFY post_text mediumtext NOT NULL';
0972 }
0973
0974 $alter_entry[] = 'ADD FULLTEXT post_content (post_text, post_subject)';
0975 $alter_list[] = $alter_entry;
0976 }
0977
0978 if (sizeof($alter_list))
0979 {
0980 foreach ($alter_list as $alter)
0981 {
0982 $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter));
0983 }
0984 }
0985
0986 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
0987
0988 return false;
0989 }
0990
0991 /**
0992 * Drop fulltext index
0993 *
0994 * @return string|bool error string is returned incase of errors otherwise false
0995 */
0996 public function delete_index($acp_module, $u_action)
0997 {
0998 // Make sure we can actually use MySQL with fulltext indexes
0999 if ($error = $this->init())
1000 {
1001 return $error;
1002 }
1003
1004 if (empty($this->stats))
1005 {
1006 $this->get_stats();
1007 }
1008
1009 $alter = array();
1010
1011 if (isset($this->stats['post_subject']))
1012 {
1013 $alter[] = 'DROP INDEX post_subject';
1014 }
1015
1016 if (isset($this->stats['post_content']))
1017 {
1018 $alter[] = 'DROP INDEX post_content';
1019 }
1020
1021 if (sizeof($alter))
1022 {
1023 $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter));
1024 }
1025
1026 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1027
1028 return false;
1029 }
1030
1031 /**
1032 * Returns true if both FULLTEXT indexes exist
1033 */
1034 public function index_created()
1035 {
1036 if (empty($this->stats))
1037 {
1038 $this->get_stats();
1039 }
1040
1041 return isset($this->stats['post_subject']) && isset($this->stats['post_content']);
1042 }
1043
1044 /**
1045 * Returns an associative array containing information about the indexes
1046 */
1047 public function index_stats()
1048 {
1049 if (empty($this->stats))
1050 {
1051 $this->get_stats();
1052 }
1053
1054 return array(
1055 $this->user->lang['FULLTEXT_MYSQL_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0,
1056 );
1057 }
1058
1059 /**
1060 * Computes the stats and store them in the $this->stats associative array
1061 */
1062 protected function get_stats()
1063 {
1064 if (strpos($this->db->get_sql_layer(), 'mysql') === false)
1065 {
1066 $this->stats = array();
1067 return;
1068 }
1069
1070 $sql = 'SHOW INDEX
1071 FROM ' . POSTS_TABLE;
1072 $result = $this->db->sql_query($sql);
1073
1074 while ($row = $this->db->sql_fetchrow($result))
1075 {
1076 // deal with older MySQL versions which didn't use Index_type
1077 $index_type = (isset($row['Index_type'])) ? $row['Index_type'] : $row['Comment'];
1078
1079 if ($index_type == 'FULLTEXT')
1080 {
1081 if ($row['Key_name'] == 'post_subject')
1082 {
1083 $this->stats['post_subject'] = $row;
1084 }
1085 else if ($row['Key_name'] == 'post_content')
1086 {
1087 $this->stats['post_content'] = $row;
1088 }
1089 }
1090 }
1091 $this->db->sql_freeresult($result);
1092
1093 $this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE);
1094 }
1095
1096 /**
1097 * Display a note, that UTF-8 support is not available with certain versions of PHP
1098 *
1099 * @return associative array containing template and config variables
1100 */
1101 public function acp()
1102 {
1103 $tpl = '
1104 <dl>
1105 <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>
1106 <dd>' . $this->config['fulltext_mysql_min_word_len'] . '</dd>
1107 </dl>
1108 <dl>
1109 <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>
1110 <dd>' . $this->config['fulltext_mysql_max_word_len'] . '</dd>
1111 </dl>
1112 ';
1113
1114 // These are fields required in the config table
1115 return array(
1116 'tpl' => $tpl,
1117 'config' => array()
1118 );
1119 }
1120 }
1121