| [ Index ] |
PHP Cross Reference of phpBB-3.1.12-deutsch |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * 4 * This file is part of the phpBB Forum Software package. 5 * 6 * @copyright (c) phpBB Limited <https://www.phpbb.com> 7 * @license GNU General Public License, version 2 (GPL-2.0) 8 * 9 * For full copyright and license information, please see 10 * the docs/CREDITS.txt file. 11 * 12 */ 13 14 namespace phpbb\search; 15 16 /** 17 * Fulltext search for MySQL 18 */ 19 class fulltext_mysql extends \phpbb\search\base 20 { 21 /** 22 * Associative array holding index stats 23 * @var array 24 */ 25 protected $stats = array(); 26 27 /** 28 * Holds the words entered by user, obtained by splitting the entered query on whitespace 29 * @var array 30 */ 31 protected $split_words = array(); 32 33 /** 34 * Config object 35 * @var \phpbb\config\config 36 */ 37 protected $config; 38 39 /** 40 * Database connection 41 * @var \phpbb\db\driver\driver_interface 42 */ 43 protected $db; 44 45 /** 46 * phpBB event dispatcher object 47 * @var \phpbb\event\dispatcher_interface 48 */ 49 protected $phpbb_dispatcher; 50 51 /** 52 * User object 53 * @var \phpbb\user 54 */ 55 protected $user; 56 57 /** 58 * Associative array stores the min and max word length to be searched 59 * @var array 60 */ 61 protected $word_length = array(); 62 63 /** 64 * Contains tidied search query. 65 * Operators are prefixed in search query and common words excluded 66 * @var string 67 */ 68 protected $search_query; 69 70 /** 71 * Contains common words. 72 * Common words are words with length less/more than min/max length 73 * @var array 74 */ 75 protected $common_words = array(); 76 77 /** 78 * Constructor 79 * Creates a new \phpbb\search\fulltext_mysql, which is used as a search backend 80 * 81 * @param string|bool $error Any error that occurs is passed on through this reference variable otherwise false 82 * @param string $phpbb_root_path Relative path to phpBB root 83 * @param string $phpEx PHP file extension 84 * @param \phpbb\auth\auth $auth Auth object 85 * @param \phpbb\config\config $config Config object 86 * @param \phpbb\db\driver\driver_interface Database object 87 * @param \phpbb\user $user User object 88 * @param \phpbb\event\dispatcher_interface $phpbb_dispatcher Event dispatcher object 89 */ 90 public function __construct(&$error, $phpbb_root_path, $phpEx, $auth, $config, $db, $user, $phpbb_dispatcher) 91 { 92 $this->config = $config; 93 $this->db = $db; 94 $this->phpbb_dispatcher = $phpbb_dispatcher; 95 $this->user = $user; 96 97 $this->word_length = array('min' => $this->config['fulltext_mysql_min_word_len'], 'max' => $this->config['fulltext_mysql_max_word_len']); 98 99 /** 100 * Load the UTF tools 101 */ 102 if (!function_exists('utf8_strlen')) 103 { 104 include($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx); 105 } 106 107 $error = false; 108 } 109 110 /** 111 * Returns the name of this search backend to be displayed to administrators 112 * 113 * @return string Name 114 */ 115 public function get_name() 116 { 117 return 'MySQL Fulltext'; 118 } 119 120 /** 121 * Returns the search_query 122 * 123 * @return string search query 124 */ 125 public function get_search_query() 126 { 127 return $this->search_query; 128 } 129 130 /** 131 * Returns the common_words array 132 * 133 * @return array common words that are ignored by search backend 134 */ 135 public function get_common_words() 136 { 137 return $this->common_words; 138 } 139 140 /** 141 * Returns the word_length array 142 * 143 * @return array min and max word length for searching 144 */ 145 public function get_word_length() 146 { 147 return $this->word_length; 148 } 149 150 /** 151 * Checks for correct MySQL version and stores min/max word length in the config 152 * 153 * @return string|bool Language key of the error/incompatiblity occurred 154 */ 155 public function init() 156 { 157 if ($this->db->get_sql_layer() != 'mysql4' && $this->db->get_sql_layer() != 'mysqli') 158 { 159 return $this->user->lang['FULLTEXT_MYSQL_INCOMPATIBLE_DATABASE']; 160 } 161 162 $result = $this->db->sql_query('SHOW TABLE STATUS LIKE \'' . POSTS_TABLE . '\''); 163 $info = $this->db->sql_fetchrow($result); 164 $this->db->sql_freeresult($result); 165 166 $engine = ''; 167 if (isset($info['Engine'])) 168 { 169 $engine = $info['Engine']; 170 } 171 else if (isset($info['Type'])) 172 { 173 $engine = $info['Type']; 174 } 175 176 $fulltext_supported = 177 $engine === 'MyISAM' || 178 // FULLTEXT is supported on InnoDB since MySQL 5.6.4 according to 179 // http://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html 180 // We also require https://bugs.mysql.com/bug.php?id=67004 to be 181 // fixed for proper overall operation. Hence we require 5.6.8. 182 $engine === 'InnoDB' && 183 phpbb_version_compare($this->db->sql_server_info(true), '5.6.8', '>='); 184 185 if (!$fulltext_supported) 186 { 187 return $this->user->lang['FULLTEXT_MYSQL_NOT_SUPPORTED']; 188 } 189 190 $sql = 'SHOW VARIABLES 191 LIKE \'ft\_%\''; 192 $result = $this->db->sql_query($sql); 193 194 $mysql_info = array(); 195 while ($row = $this->db->sql_fetchrow($result)) 196 { 197 $mysql_info[$row['Variable_name']] = $row['Value']; 198 } 199 $this->db->sql_freeresult($result); 200 201 set_config('fulltext_mysql_max_word_len', $mysql_info['ft_max_word_len']); 202 set_config('fulltext_mysql_min_word_len', $mysql_info['ft_min_word_len']); 203 204 return false; 205 } 206 207 /** 208 * Splits keywords entered by a user into an array of words stored in $this->split_words 209 * Stores the tidied search query in $this->search_query 210 * 211 * @param string &$keywords Contains the keyword as entered by the user 212 * @param string $terms is either 'all' or 'any' 213 * @return bool false if no valid keywords were found and otherwise true 214 */ 215 public function split_keywords(&$keywords, $terms) 216 { 217 if ($terms == 'all') 218 { 219 $match = array('#\sand\s#iu', '#\sor\s#iu', '#\snot\s#iu', '#(^|\s)\+#', '#(^|\s)-#', '#(^|\s)\|#'); 220 $replace = array(' +', ' |', ' -', ' +', ' -', ' |'); 221 222 $keywords = preg_replace($match, $replace, $keywords); 223 } 224 225 // Filter out as above 226 $split_keywords = preg_replace("#[\n\r\t]+#", ' ', trim(htmlspecialchars_decode($keywords))); 227 228 // Split words 229 $split_keywords = preg_replace('#([^\p{L}\p{N}\'*"()])#u', '$1$1', str_replace('\'\'', '\' \'', trim($split_keywords))); 230 $matches = array(); 231 preg_match_all('#(?:[^\p{L}\p{N}*"()]|^)([+\-|]?(?:[\p{L}\p{N}*"()]+\'?)*[\p{L}\p{N}*"()])(?:[^\p{L}\p{N}*"()]|$)#u', $split_keywords, $matches); 232 $this->split_words = $matches[1]; 233 234 // We limit the number of allowed keywords to minimize load on the database 235 if ($this->config['max_num_search_keywords'] && sizeof($this->split_words) > $this->config['max_num_search_keywords']) 236 { 237 trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], sizeof($this->split_words))); 238 } 239 240 // to allow phrase search, we need to concatenate quoted words 241 $tmp_split_words = array(); 242 $phrase = ''; 243 foreach ($this->split_words as $word) 244 { 245 if ($phrase) 246 { 247 $phrase .= ' ' . $word; 248 if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1) 249 { 250 $tmp_split_words[] = $phrase; 251 $phrase = ''; 252 } 253 } 254 else if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1) 255 { 256 $phrase = $word; 257 } 258 else 259 { 260 $tmp_split_words[] = $word; 261 } 262 } 263 if ($phrase) 264 { 265 $tmp_split_words[] = $phrase; 266 } 267 268 $this->split_words = $tmp_split_words; 269 270 unset($tmp_split_words); 271 unset($phrase); 272 273 foreach ($this->split_words as $i => $word) 274 { 275 // Check for not allowed search queries for InnoDB. 276 // We assume similar restrictions for MyISAM, which is usually even 277 // slower but not as restrictive as InnoDB. 278 // InnoDB full-text search does not support the use of a leading 279 // plus sign with wildcard ('+*'), a plus and minus sign 280 // combination ('+-'), or leading a plus and minus sign combination. 281 // InnoDB full-text search only supports leading plus or minus signs. 282 // For example, InnoDB supports '+apple' but does not support 'apple+'. 283 // Specifying a trailing plus or minus sign causes InnoDB to report 284 // a syntax error. InnoDB full-text search does not support the use 285 // of multiple operators on a single search word, as in this example: 286 // '++apple'. Use of multiple operators on a single search word 287 // returns a syntax error to standard out. 288 // Also, ensure that the wildcard character is only used at the 289 // end of the line as it's intended by MySQL. 290 if (preg_match('#^(\+[+-]|\+\*|.+[+-]$|.+\*(?!$))#', $word)) 291 { 292 unset($this->split_words[$i]); 293 continue; 294 } 295 296 $clean_word = preg_replace('#^[+\-|"]#', '', $word); 297 298 // check word length 299 $clean_len = utf8_strlen(str_replace('*', '', $clean_word)); 300 if (($clean_len < $this->config['fulltext_mysql_min_word_len']) || ($clean_len > $this->config['fulltext_mysql_max_word_len'])) 301 { 302 $this->common_words[] = $word; 303 unset($this->split_words[$i]); 304 } 305 } 306 307 if ($terms == 'any') 308 { 309 $this->search_query = ''; 310 foreach ($this->split_words as $word) 311 { 312 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0) || (strpos($word, '|') === 0)) 313 { 314 $word = substr($word, 1); 315 } 316 $this->search_query .= $word . ' '; 317 } 318 } 319 else 320 { 321 $this->search_query = ''; 322 foreach ($this->split_words as $word) 323 { 324 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0)) 325 { 326 $this->search_query .= $word . ' '; 327 } 328 else if (strpos($word, '|') === 0) 329 { 330 $this->search_query .= substr($word, 1) . ' '; 331 } 332 else 333 { 334 $this->search_query .= '+' . $word . ' '; 335 } 336 } 337 } 338 339 $this->search_query = utf8_htmlspecialchars($this->search_query); 340 341 if ($this->search_query) 342 { 343 $this->split_words = array_values($this->split_words); 344 sort($this->split_words); 345 return true; 346 } 347 return false; 348 } 349 350 /** 351 * Turns text into an array of words 352 * @param string $text contains post text/subject 353 */ 354 public function split_message($text) 355 { 356 // Split words 357 $text = preg_replace('#([^\p{L}\p{N}\'*])#u', '$1$1', str_replace('\'\'', '\' \'', trim($text))); 358 $matches = array(); 359 preg_match_all('#(?:[^\p{L}\p{N}*]|^)([+\-|]?(?:[\p{L}\p{N}*]+\'?)*[\p{L}\p{N}*])(?:[^\p{L}\p{N}*]|$)#u', $text, $matches); 360 $text = $matches[1]; 361 362 // remove too short or too long words 363 $text = array_values($text); 364 for ($i = 0, $n = sizeof($text); $i < $n; $i++) 365 { 366 $text[$i] = trim($text[$i]); 367 if (utf8_strlen($text[$i]) < $this->config['fulltext_mysql_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_mysql_max_word_len']) 368 { 369 unset($text[$i]); 370 } 371 } 372 373 return array_values($text); 374 } 375 376 /** 377 * Performs a search on keywords depending on display specific params. You have to run split_keywords() first 378 * 379 * @param string $type contains either posts or topics depending on what should be searched for 380 * @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) 381 * @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) 382 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 383 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 384 * @param string $sort_dir is either a or d representing ASC and DESC 385 * @param string $sort_days specifies the maximum amount of days a post may be old 386 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 387 * @param string $post_visibility specifies which types of posts the user can view in which forums 388 * @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 389 * @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty 390 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 391 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 392 * @param int $start indicates the first index of the page 393 * @param int $per_page number of ids each page is supposed to contain 394 * @return boolean|int total number of results 395 */ 396 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) 397 { 398 // No keywords? No posts 399 if (!$this->search_query) 400 { 401 return false; 402 } 403 404 // generate a search_key from all the options to identify the results 405 $search_key_array = array( 406 implode(', ', $this->split_words), 407 $type, 408 $fields, 409 $terms, 410 $sort_days, 411 $sort_key, 412 $topic_id, 413 implode(',', $ex_fid_ary), 414 $post_visibility, 415 implode(',', $author_ary) 416 ); 417 418 /** 419 * Allow changing the search_key for cached results 420 * 421 * @event core.search_mysql_by_keyword_modify_search_key 422 * @var array search_key_array Array with search parameters to generate the search_key 423 * @var string type Searching type ('posts', 'topics') 424 * @var string fields Searching fields ('titleonly', 'msgonly', 'firstpost', 'all') 425 * @var string terms Searching terms ('all', 'any') 426 * @var int sort_days Time, in days, of the oldest possible post to list 427 * @var string sort_key The sort type used from the possible sort types 428 * @var int topic_id Limit the search to this topic_id only 429 * @var array ex_fid_ary Which forums not to search on 430 * @var string post_visibility Post visibility data 431 * @var array author_ary Array of user_id containing the users to filter the results to 432 * @since 3.1.7-RC1 433 */ 434 $vars = array( 435 'search_key_array', 436 'type', 437 'fields', 438 'terms', 439 'sort_days', 440 'sort_key', 441 'topic_id', 442 'ex_fid_ary', 443 'post_visibility', 444 'author_ary', 445 ); 446 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_keyword_modify_search_key', compact($vars))); 447 448 $search_key = md5(implode('#', $search_key_array)); 449 450 if ($start < 0) 451 { 452 $start = 0; 453 } 454 455 // try reading the results from cache 456 $result_count = 0; 457 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 458 { 459 return $result_count; 460 } 461 462 $id_ary = array(); 463 464 $join_topic = ($type == 'posts') ? false : true; 465 466 // Build sql strings for sorting 467 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 468 $sql_sort_table = $sql_sort_join = ''; 469 470 switch ($sql_sort[0]) 471 { 472 case 'u': 473 $sql_sort_table = USERS_TABLE . ' u, '; 474 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 475 break; 476 477 case 't': 478 $join_topic = true; 479 break; 480 481 case 'f': 482 $sql_sort_table = FORUMS_TABLE . ' f, '; 483 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 484 break; 485 } 486 487 // Build some display specific sql strings 488 switch ($fields) 489 { 490 case 'titleonly': 491 $sql_match = 'p.post_subject'; 492 $sql_match_where = ' AND p.post_id = t.topic_first_post_id'; 493 $join_topic = true; 494 break; 495 496 case 'msgonly': 497 $sql_match = 'p.post_text'; 498 $sql_match_where = ''; 499 break; 500 501 case 'firstpost': 502 $sql_match = 'p.post_subject, p.post_text'; 503 $sql_match_where = ' AND p.post_id = t.topic_first_post_id'; 504 $join_topic = true; 505 break; 506 507 default: 508 $sql_match = 'p.post_subject, p.post_text'; 509 $sql_match_where = ''; 510 break; 511 } 512 513 $search_query = $this->search_query; 514 515 /** 516 * Allow changing the query used to search for posts using fulltext_mysql 517 * 518 * @event core.search_mysql_keywords_main_query_before 519 * @var string search_query The parsed keywords used for this search 520 * @var int result_count The previous result count for the format of the query. 521 * Set to 0 to force a re-count 522 * @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED 523 * @var array author_ary Array of user_id containing the users to filter the results to 524 * @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant) 525 * @var array ex_fid_ary Which forums not to search on 526 * @var int topic_id Limit the search to this topic_id only 527 * @var string sql_sort_table Extra tables to include in the SQL query. 528 * Used in conjunction with sql_sort_join 529 * @var string sql_sort_join SQL conditions to join all the tables used together. 530 * Used in conjunction with sql_sort_table 531 * @var int sort_days Time, in days, of the oldest possible post to list 532 * @var string sql_match Which columns to do the search on. 533 * @var string sql_match_where Extra conditions to use to properly filter the matching process 534 * @var string sort_by_sql The possible predefined sort types 535 * @var string sort_key The sort type used from the possible sort types 536 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 537 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 538 * @var int start How many posts to skip in the search results (used for pagination) 539 * @since 3.1.5-RC1 540 */ 541 $vars = array( 542 'search_query', 543 'result_count', 544 'join_topic', 545 'author_ary', 546 'author_name', 547 'ex_fid_ary', 548 'topic_id', 549 'sql_sort_table', 550 'sql_sort_join', 551 'sort_days', 552 'sql_match', 553 'sql_match_where', 554 'sort_by_sql', 555 'sort_key', 556 'sort_dir', 557 'sql_sort', 558 'start', 559 ); 560 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_keywords_main_query_before', compact($vars))); 561 562 $sql_select = (!$result_count) ? 'SQL_CALC_FOUND_ROWS ' : ''; 563 $sql_select = ($type == 'posts') ? $sql_select . 'p.post_id' : 'DISTINCT ' . $sql_select . 't.topic_id'; 564 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : ''; 565 $field = ($type == 'posts') ? 'post_id' : 'topic_id'; 566 if (sizeof($author_ary) && $author_name) 567 { 568 // first one matches post of registered users, second one guests and deleted users 569 $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 . ')'; 570 } 571 else if (sizeof($author_ary)) 572 { 573 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary); 574 } 575 else 576 { 577 $sql_author = ''; 578 } 579 580 $sql_where_options = $sql_sort_join; 581 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : ''; 582 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : ''; 583 $sql_where_options .= (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 584 $sql_where_options .= ' AND ' . $post_visibility; 585 $sql_where_options .= $sql_author; 586 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 587 $sql_where_options .= $sql_match_where; 588 589 $sql = "SELECT $sql_select 590 FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p 591 WHERE MATCH ($sql_match) AGAINST ('" . $this->db->sql_escape(htmlspecialchars_decode($this->search_query)) . "' IN BOOLEAN MODE) 592 $sql_where_options 593 ORDER BY $sql_sort"; 594 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 595 596 while ($row = $this->db->sql_fetchrow($result)) 597 { 598 $id_ary[] = (int) $row[$field]; 599 } 600 $this->db->sql_freeresult($result); 601 602 $id_ary = array_unique($id_ary); 603 604 // if the total result count is not cached yet, retrieve it from the db 605 if (!$result_count) 606 { 607 $sql_found_rows = 'SELECT FOUND_ROWS() as result_count'; 608 $result = $this->db->sql_query($sql_found_rows); 609 $result_count = (int) $this->db->sql_fetchfield('result_count'); 610 $this->db->sql_freeresult($result); 611 612 if (!$result_count) 613 { 614 return false; 615 } 616 } 617 618 if ($start >= $result_count) 619 { 620 $start = floor(($result_count - 1) / $per_page) * $per_page; 621 622 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 623 624 while ($row = $this->db->sql_fetchrow($result)) 625 { 626 $id_ary[] = (int) $row[$field]; 627 } 628 $this->db->sql_freeresult($result); 629 630 $id_ary = array_unique($id_ary); 631 } 632 633 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page 634 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir); 635 $id_ary = array_slice($id_ary, 0, (int) $per_page); 636 637 return $result_count; 638 } 639 640 /** 641 * Performs a search on an author's posts without caring about message contents. Depends on display specific params 642 * 643 * @param string $type contains either posts or topics depending on what should be searched for 644 * @param boolean $firstpost_only if true, only topic starting posts will be considered 645 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 646 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 647 * @param string $sort_dir is either a or d representing ASC and DESC 648 * @param string $sort_days specifies the maximum amount of days a post may be old 649 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 650 * @param string $post_visibility specifies which types of posts the user can view in which forums 651 * @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 652 * @param array $author_ary an array of author ids 653 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 654 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 655 * @param int $start indicates the first index of the page 656 * @param int $per_page number of ids each page is supposed to contain 657 * @return boolean|int total number of results 658 */ 659 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) 660 { 661 // No author? No posts 662 if (!sizeof($author_ary)) 663 { 664 return 0; 665 } 666 667 // generate a search_key from all the options to identify the results 668 $search_key_array = array( 669 '', 670 $type, 671 ($firstpost_only) ? 'firstpost' : '', 672 '', 673 '', 674 $sort_days, 675 $sort_key, 676 $topic_id, 677 implode(',', $ex_fid_ary), 678 $post_visibility, 679 implode(',', $author_ary), 680 $author_name, 681 ); 682 683 /** 684 * Allow changing the search_key for cached results 685 * 686 * @event core.search_mysql_by_author_modify_search_key 687 * @var array search_key_array Array with search parameters to generate the search_key 688 * @var string type Searching type ('posts', 'topics') 689 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered 690 * @var int sort_days Time, in days, of the oldest possible post to list 691 * @var string sort_key The sort type used from the possible sort types 692 * @var int topic_id Limit the search to this topic_id only 693 * @var array ex_fid_ary Which forums not to search on 694 * @var string post_visibility Post visibility data 695 * @var array author_ary Array of user_id containing the users to filter the results to 696 * @var string author_name The username to search on 697 * @since 3.1.7-RC1 698 */ 699 $vars = array( 700 'search_key_array', 701 'type', 702 'firstpost_only', 703 'sort_days', 704 'sort_key', 705 'topic_id', 706 'ex_fid_ary', 707 'post_visibility', 708 'author_ary', 709 'author_name', 710 ); 711 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_author_modify_search_key', compact($vars))); 712 713 $search_key = md5(implode('#', $search_key_array)); 714 715 if ($start < 0) 716 { 717 $start = 0; 718 } 719 720 // try reading the results from cache 721 $result_count = 0; 722 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 723 { 724 return $result_count; 725 } 726 727 $id_ary = array(); 728 729 // Create some display specific sql strings 730 if ($author_name) 731 { 732 // first one matches post of registered users, second one guests and deleted users 733 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 734 } 735 else 736 { 737 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); 738 } 739 $sql_fora = (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 740 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : ''; 741 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 742 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : ''; 743 744 // Build sql strings for sorting 745 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 746 $sql_sort_table = $sql_sort_join = ''; 747 switch ($sql_sort[0]) 748 { 749 case 'u': 750 $sql_sort_table = USERS_TABLE . ' u, '; 751 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 752 break; 753 754 case 't': 755 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : ''; 756 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : ''; 757 break; 758 759 case 'f': 760 $sql_sort_table = FORUMS_TABLE . ' f, '; 761 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 762 break; 763 } 764 765 $m_approve_fid_sql = ' AND ' . $post_visibility; 766 767 /** 768 * Allow changing the query used to search for posts by author in fulltext_mysql 769 * 770 * @event core.search_mysql_author_query_before 771 * @var int result_count The previous result count for the format of the query. 772 * Set to 0 to force a re-count 773 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen 774 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table 775 * @var string type Either "posts" or "topics" specifying the type of search being made 776 * @var array author_ary Array of user_id containing the users to filter the results to 777 * @var string author_name An extra username to search on 778 * @var string sql_author SQL WHERE condition for the post author ids 779 * @var int topic_id Limit the search to this topic_id only 780 * @var string sql_topic_id SQL of topic_id 781 * @var string sort_by_sql The possible predefined sort types 782 * @var string sort_key The sort type used from the possible sort types 783 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 784 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 785 * @var string sort_days Time, in days, that the oldest post showing can have 786 * @var string sql_time The SQL to search on the time specifyed by sort_days 787 * @var bool firstpost_only Wether or not to search only on the first post of the topics 788 * @var string sql_firstpost The SQL with the conditions to join the tables when using firstpost_only 789 * @var array ex_fid_ary Forum ids that must not be searched on 790 * @var array sql_fora SQL query for ex_fid_ary 791 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions 792 * @var int start How many posts to skip in the search results (used for pagination) 793 * @since 3.1.5-RC1 794 */ 795 $vars = array( 796 'result_count', 797 'sql_sort_table', 798 'sql_sort_join', 799 'type', 800 'author_ary', 801 'author_name', 802 'sql_author', 803 'topic_id', 804 'sql_topic_id', 805 'sort_by_sql', 806 'sort_key', 807 'sort_dir', 808 'sql_sort', 809 'sort_days', 810 'sql_time', 811 'firstpost_only', 812 'sql_firstpost', 813 'ex_fid_ary', 814 'sql_fora', 815 'm_approve_fid_sql', 816 'start', 817 ); 818 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars))); 819 820 // If the cache was completely empty count the results 821 $calc_results = ($result_count) ? '' : 'SQL_CALC_FOUND_ROWS '; 822 823 // Build the query for really selecting the post_ids 824 if ($type == 'posts') 825 { 826 $sql = "SELECT {$calc_results}p.post_id 827 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . " 828 WHERE $sql_author 829 $sql_topic_id 830 $sql_firstpost 831 $m_approve_fid_sql 832 $sql_fora 833 $sql_sort_join 834 $sql_time 835 ORDER BY $sql_sort"; 836 $field = 'post_id'; 837 } 838 else 839 { 840 $sql = "SELECT {$calc_results}t.topic_id 841 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p 842 WHERE $sql_author 843 $sql_topic_id 844 $sql_firstpost 845 $m_approve_fid_sql 846 $sql_fora 847 AND t.topic_id = p.topic_id 848 $sql_sort_join 849 $sql_time 850 GROUP BY t.topic_id 851 ORDER BY $sql_sort"; 852 $field = 'topic_id'; 853 } 854 855 // Only read one block of posts from the db and then cache it 856 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 857 858 while ($row = $this->db->sql_fetchrow($result)) 859 { 860 $id_ary[] = (int) $row[$field]; 861 } 862 $this->db->sql_freeresult($result); 863 864 // retrieve the total result count if needed 865 if (!$result_count) 866 { 867 $sql_found_rows = 'SELECT FOUND_ROWS() as result_count'; 868 $result = $this->db->sql_query($sql_found_rows); 869 $result_count = (int) $this->db->sql_fetchfield('result_count'); 870 $this->db->sql_freeresult($result); 871 872 if (!$result_count) 873 { 874 return false; 875 } 876 } 877 878 if ($start >= $result_count) 879 { 880 $start = floor(($result_count - 1) / $per_page) * $per_page; 881 882 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 883 while ($row = $this->db->sql_fetchrow($result)) 884 { 885 $id_ary[] = (int) $row[$field]; 886 } 887 $this->db->sql_freeresult($result); 888 889 $id_ary = array_unique($id_ary); 890 } 891 892 if (sizeof($id_ary)) 893 { 894 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); 895 $id_ary = array_slice($id_ary, 0, $per_page); 896 897 return $result_count; 898 } 899 return false; 900 } 901 902 /** 903 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated 904 * 905 * @param string $mode contains the post mode: edit, post, reply, quote ... 906 * @param int $post_id contains the post id of the post to index 907 * @param string $message contains the post text of the post 908 * @param string $subject contains the subject of the post to index 909 * @param int $poster_id contains the user id of the poster 910 * @param int $forum_id contains the forum id of parent forum of the post 911 */ 912 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id) 913 { 914 // Split old and new post/subject to obtain array of words 915 $split_text = $this->split_message($message); 916 $split_title = ($subject) ? $this->split_message($subject) : array(); 917 918 $words = array_unique(array_merge($split_text, $split_title)); 919 920 unset($split_text); 921 unset($split_title); 922 923 // destroy cached search results containing any of the words removed or added 924 $this->destroy_cache($words, array($poster_id)); 925 926 unset($words); 927 } 928 929 /** 930 * Destroy cached results, that might be outdated after deleting a post 931 */ 932 public function index_remove($post_ids, $author_ids, $forum_ids) 933 { 934 $this->destroy_cache(array(), array_unique($author_ids)); 935 } 936 937 /** 938 * Destroy old cache entries 939 */ 940 public function tidy() 941 { 942 // destroy too old cached search results 943 $this->destroy_cache(array()); 944 945 set_config('search_last_gc', time(), true); 946 } 947 948 /** 949 * Create fulltext index 950 * 951 * @return string|bool error string is returned incase of errors otherwise false 952 */ 953 public function create_index($acp_module, $u_action) 954 { 955 // Make sure we can actually use MySQL with fulltext indexes 956 if ($error = $this->init()) 957 { 958 return $error; 959 } 960 961 if (empty($this->stats)) 962 { 963 $this->get_stats(); 964 } 965 966 $alter_list = array(); 967 968 if (!isset($this->stats['post_subject'])) 969 { 970 $alter_entry = array(); 971 if ($this->db->get_sql_layer() == 'mysqli' || version_compare($this->db->sql_server_info(true), '4.1.3', '>=')) 972 { 973 $alter_entry[] = 'MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL'; 974 } 975 else 976 { 977 $alter_entry[] = 'MODIFY post_subject text NOT NULL'; 978 } 979 $alter_entry[] = 'ADD FULLTEXT (post_subject)'; 980 $alter_list[] = $alter_entry; 981 } 982 983 if (!isset($this->stats['post_content'])) 984 { 985 $alter_entry = array(); 986 if ($this->db->get_sql_layer() == 'mysqli' || version_compare($this->db->sql_server_info(true), '4.1.3', '>=')) 987 { 988 $alter_entry[] = 'MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL'; 989 } 990 else 991 { 992 $alter_entry[] = 'MODIFY post_text mediumtext NOT NULL'; 993 } 994 995 $alter_entry[] = 'ADD FULLTEXT post_content (post_text, post_subject)'; 996 $alter_list[] = $alter_entry; 997 } 998 999 if (sizeof($alter_list)) 1000 { 1001 foreach ($alter_list as $alter) 1002 { 1003 $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter)); 1004 } 1005 } 1006 1007 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1008 1009 return false; 1010 } 1011 1012 /** 1013 * Drop fulltext index 1014 * 1015 * @return string|bool error string is returned incase of errors otherwise false 1016 */ 1017 public function delete_index($acp_module, $u_action) 1018 { 1019 // Make sure we can actually use MySQL with fulltext indexes 1020 if ($error = $this->init()) 1021 { 1022 return $error; 1023 } 1024 1025 if (empty($this->stats)) 1026 { 1027 $this->get_stats(); 1028 } 1029 1030 $alter = array(); 1031 1032 if (isset($this->stats['post_subject'])) 1033 { 1034 $alter[] = 'DROP INDEX post_subject'; 1035 } 1036 1037 if (isset($this->stats['post_content'])) 1038 { 1039 $alter[] = 'DROP INDEX post_content'; 1040 } 1041 1042 if (sizeof($alter)) 1043 { 1044 $this->db->sql_query('ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter)); 1045 } 1046 1047 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1048 1049 return false; 1050 } 1051 1052 /** 1053 * Returns true if both FULLTEXT indexes exist 1054 */ 1055 public function index_created() 1056 { 1057 if (empty($this->stats)) 1058 { 1059 $this->get_stats(); 1060 } 1061 1062 return isset($this->stats['post_subject']) && isset($this->stats['post_content']); 1063 } 1064 1065 /** 1066 * Returns an associative array containing information about the indexes 1067 */ 1068 public function index_stats() 1069 { 1070 if (empty($this->stats)) 1071 { 1072 $this->get_stats(); 1073 } 1074 1075 return array( 1076 $this->user->lang['FULLTEXT_MYSQL_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0, 1077 ); 1078 } 1079 1080 /** 1081 * Computes the stats and store them in the $this->stats associative array 1082 */ 1083 protected function get_stats() 1084 { 1085 if (strpos($this->db->get_sql_layer(), 'mysql') === false) 1086 { 1087 $this->stats = array(); 1088 return; 1089 } 1090 1091 $sql = 'SHOW INDEX 1092 FROM ' . POSTS_TABLE; 1093 $result = $this->db->sql_query($sql); 1094 1095 while ($row = $this->db->sql_fetchrow($result)) 1096 { 1097 // deal with older MySQL versions which didn't use Index_type 1098 $index_type = (isset($row['Index_type'])) ? $row['Index_type'] : $row['Comment']; 1099 1100 if ($index_type == 'FULLTEXT') 1101 { 1102 if ($row['Key_name'] == 'post_subject') 1103 { 1104 $this->stats['post_subject'] = $row; 1105 } 1106 else if ($row['Key_name'] == 'post_content') 1107 { 1108 $this->stats['post_content'] = $row; 1109 } 1110 } 1111 } 1112 $this->db->sql_freeresult($result); 1113 1114 $this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE); 1115 } 1116 1117 /** 1118 * Display a note, that UTF-8 support is not available with certain versions of PHP 1119 * 1120 * @return associative array containing template and config variables 1121 */ 1122 public function acp() 1123 { 1124 $tpl = ' 1125 <dl> 1126 <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> 1127 <dd>' . $this->config['fulltext_mysql_min_word_len'] . '</dd> 1128 </dl> 1129 <dl> 1130 <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> 1131 <dd>' . $this->config['fulltext_mysql_max_word_len'] . '</dd> 1132 </dl> 1133 '; 1134 1135 // These are fields required in the config table 1136 return array( 1137 'tpl' => $tpl, 1138 'config' => array() 1139 ); 1140 } 1141 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Jan 11 00:25:41 2018 | Cross-referenced by PHPXref 0.7.1 |