| [ Index ] |
PHP Cross Reference of phpBB-3.2.11-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 = $engine === 'Aria' || $engine === 'MyISAM' 177 /** 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 */ 183 || $engine === 'InnoDB' 184 && phpbb_version_compare($this->db->sql_server_info(true), '5.6.8', '>='); 185 186 if (!$fulltext_supported) 187 { 188 return $this->user->lang['FULLTEXT_MYSQL_NOT_SUPPORTED']; 189 } 190 191 $sql = 'SHOW VARIABLES 192 LIKE \'%ft\_%\''; 193 $result = $this->db->sql_query($sql); 194 195 $mysql_info = array(); 196 while ($row = $this->db->sql_fetchrow($result)) 197 { 198 $mysql_info[$row['Variable_name']] = $row['Value']; 199 } 200 $this->db->sql_freeresult($result); 201 202 if ($engine === 'MyISAM') 203 { 204 $this->config->set('fulltext_mysql_max_word_len', $mysql_info['ft_max_word_len']); 205 $this->config->set('fulltext_mysql_min_word_len', $mysql_info['ft_min_word_len']); 206 } 207 else if ($engine === 'InnoDB') 208 { 209 $this->config->set('fulltext_mysql_max_word_len', $mysql_info['innodb_ft_max_token_size']); 210 $this->config->set('fulltext_mysql_min_word_len', $mysql_info['innodb_ft_min_token_size']); 211 } 212 213 return false; 214 } 215 216 /** 217 * Splits keywords entered by a user into an array of words stored in $this->split_words 218 * Stores the tidied search query in $this->search_query 219 * 220 * @param string &$keywords Contains the keyword as entered by the user 221 * @param string $terms is either 'all' or 'any' 222 * @return bool false if no valid keywords were found and otherwise true 223 */ 224 public function split_keywords(&$keywords, $terms) 225 { 226 if ($terms == 'all') 227 { 228 $match = array('#\sand\s#iu', '#\sor\s#iu', '#\snot\s#iu', '#(^|\s)\+#', '#(^|\s)-#', '#(^|\s)\|#'); 229 $replace = array(' +', ' |', ' -', ' +', ' -', ' |'); 230 231 $keywords = preg_replace($match, $replace, $keywords); 232 } 233 234 // Filter out as above 235 $split_keywords = preg_replace("#[\n\r\t]+#", ' ', trim(htmlspecialchars_decode($keywords))); 236 237 // Split words 238 $split_keywords = preg_replace('#([^\p{L}\p{N}\'*"()])#u', '$1$1', str_replace('\'\'', '\' \'', trim($split_keywords))); 239 $matches = array(); 240 preg_match_all('#(?:[^\p{L}\p{N}*"()]|^)([+\-|]?(?:[\p{L}\p{N}*"()]+\'?)*[\p{L}\p{N}*"()])(?:[^\p{L}\p{N}*"()]|$)#u', $split_keywords, $matches); 241 $this->split_words = $matches[1]; 242 243 // We limit the number of allowed keywords to minimize load on the database 244 if ($this->config['max_num_search_keywords'] && count($this->split_words) > $this->config['max_num_search_keywords']) 245 { 246 trigger_error($this->user->lang('MAX_NUM_SEARCH_KEYWORDS_REFINE', (int) $this->config['max_num_search_keywords'], count($this->split_words))); 247 } 248 249 // to allow phrase search, we need to concatenate quoted words 250 $tmp_split_words = array(); 251 $phrase = ''; 252 foreach ($this->split_words as $word) 253 { 254 if ($phrase) 255 { 256 $phrase .= ' ' . $word; 257 if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1) 258 { 259 $tmp_split_words[] = $phrase; 260 $phrase = ''; 261 } 262 } 263 else if (strpos($word, '"') !== false && substr_count($word, '"') % 2 == 1) 264 { 265 $phrase = $word; 266 } 267 else 268 { 269 $tmp_split_words[] = $word; 270 } 271 } 272 if ($phrase) 273 { 274 $tmp_split_words[] = $phrase; 275 } 276 277 $this->split_words = $tmp_split_words; 278 279 unset($tmp_split_words); 280 unset($phrase); 281 282 foreach ($this->split_words as $i => $word) 283 { 284 // Check for not allowed search queries for InnoDB. 285 // We assume similar restrictions for MyISAM, which is usually even 286 // slower but not as restrictive as InnoDB. 287 // InnoDB full-text search does not support the use of a leading 288 // plus sign with wildcard ('+*'), a plus and minus sign 289 // combination ('+-'), or leading a plus and minus sign combination. 290 // InnoDB full-text search only supports leading plus or minus signs. 291 // For example, InnoDB supports '+apple' but does not support 'apple+'. 292 // Specifying a trailing plus or minus sign causes InnoDB to report 293 // a syntax error. InnoDB full-text search does not support the use 294 // of multiple operators on a single search word, as in this example: 295 // '++apple'. Use of multiple operators on a single search word 296 // returns a syntax error to standard out. 297 // Also, ensure that the wildcard character is only used at the 298 // end of the line as it's intended by MySQL. 299 if (preg_match('#^(\+[+-]|\+\*|.+[+-]$|.+\*(?!$))#', $word)) 300 { 301 unset($this->split_words[$i]); 302 continue; 303 } 304 305 $clean_word = preg_replace('#^[+\-|"]#', '', $word); 306 307 // check word length 308 $clean_len = utf8_strlen(str_replace('*', '', $clean_word)); 309 if (($clean_len < $this->config['fulltext_mysql_min_word_len']) || ($clean_len > $this->config['fulltext_mysql_max_word_len'])) 310 { 311 $this->common_words[] = $word; 312 unset($this->split_words[$i]); 313 } 314 } 315 316 if ($terms == 'any') 317 { 318 $this->search_query = ''; 319 foreach ($this->split_words as $word) 320 { 321 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0) || (strpos($word, '|') === 0)) 322 { 323 $word = substr($word, 1); 324 } 325 $this->search_query .= $word . ' '; 326 } 327 } 328 else 329 { 330 $this->search_query = ''; 331 foreach ($this->split_words as $word) 332 { 333 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0)) 334 { 335 $this->search_query .= $word . ' '; 336 } 337 else if (strpos($word, '|') === 0) 338 { 339 $this->search_query .= substr($word, 1) . ' '; 340 } 341 else 342 { 343 $this->search_query .= '+' . $word . ' '; 344 } 345 } 346 } 347 348 $this->search_query = utf8_htmlspecialchars($this->search_query); 349 350 if ($this->search_query) 351 { 352 $this->split_words = array_values($this->split_words); 353 sort($this->split_words); 354 return true; 355 } 356 return false; 357 } 358 359 /** 360 * Turns text into an array of words 361 * @param string $text contains post text/subject 362 */ 363 public function split_message($text) 364 { 365 // Split words 366 $text = preg_replace('#([^\p{L}\p{N}\'*])#u', '$1$1', str_replace('\'\'', '\' \'', trim($text))); 367 $matches = array(); 368 preg_match_all('#(?:[^\p{L}\p{N}*]|^)([+\-|]?(?:[\p{L}\p{N}*]+\'?)*[\p{L}\p{N}*])(?:[^\p{L}\p{N}*]|$)#u', $text, $matches); 369 $text = $matches[1]; 370 371 // remove too short or too long words 372 $text = array_values($text); 373 for ($i = 0, $n = count($text); $i < $n; $i++) 374 { 375 $text[$i] = trim($text[$i]); 376 if (utf8_strlen($text[$i]) < $this->config['fulltext_mysql_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_mysql_max_word_len']) 377 { 378 unset($text[$i]); 379 } 380 } 381 382 return array_values($text); 383 } 384 385 /** 386 * Performs a search on keywords depending on display specific params. You have to run split_keywords() first 387 * 388 * @param string $type contains either posts or topics depending on what should be searched for 389 * @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) 390 * @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) 391 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 392 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 393 * @param string $sort_dir is either a or d representing ASC and DESC 394 * @param string $sort_days specifies the maximum amount of days a post may be old 395 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 396 * @param string $post_visibility specifies which types of posts the user can view in which forums 397 * @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 398 * @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty 399 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 400 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 401 * @param int $start indicates the first index of the page 402 * @param int $per_page number of ids each page is supposed to contain 403 * @return boolean|int total number of results 404 */ 405 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) 406 { 407 // No keywords? No posts 408 if (!$this->search_query) 409 { 410 return false; 411 } 412 413 // generate a search_key from all the options to identify the results 414 $search_key_array = array( 415 implode(', ', $this->split_words), 416 $type, 417 $fields, 418 $terms, 419 $sort_days, 420 $sort_key, 421 $topic_id, 422 implode(',', $ex_fid_ary), 423 $post_visibility, 424 implode(',', $author_ary) 425 ); 426 427 /** 428 * Allow changing the search_key for cached results 429 * 430 * @event core.search_mysql_by_keyword_modify_search_key 431 * @var array search_key_array Array with search parameters to generate the search_key 432 * @var string type Searching type ('posts', 'topics') 433 * @var string fields Searching fields ('titleonly', 'msgonly', 'firstpost', 'all') 434 * @var string terms Searching terms ('all', 'any') 435 * @var int sort_days Time, in days, of the oldest possible post to list 436 * @var string sort_key The sort type used from the possible sort types 437 * @var int topic_id Limit the search to this topic_id only 438 * @var array ex_fid_ary Which forums not to search on 439 * @var string post_visibility Post visibility data 440 * @var array author_ary Array of user_id containing the users to filter the results to 441 * @since 3.1.7-RC1 442 */ 443 $vars = array( 444 'search_key_array', 445 'type', 446 'fields', 447 'terms', 448 'sort_days', 449 'sort_key', 450 'topic_id', 451 'ex_fid_ary', 452 'post_visibility', 453 'author_ary', 454 ); 455 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_keyword_modify_search_key', compact($vars))); 456 457 $search_key = md5(implode('#', $search_key_array)); 458 459 if ($start < 0) 460 { 461 $start = 0; 462 } 463 464 // try reading the results from cache 465 $result_count = 0; 466 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 467 { 468 return $result_count; 469 } 470 471 $id_ary = array(); 472 473 $join_topic = ($type == 'posts') ? false : true; 474 475 // Build sql strings for sorting 476 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 477 $sql_sort_table = $sql_sort_join = ''; 478 479 switch ($sql_sort[0]) 480 { 481 case 'u': 482 $sql_sort_table = USERS_TABLE . ' u, '; 483 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 484 break; 485 486 case 't': 487 $join_topic = true; 488 break; 489 490 case 'f': 491 $sql_sort_table = FORUMS_TABLE . ' f, '; 492 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 493 break; 494 } 495 496 // Build some display specific sql strings 497 switch ($fields) 498 { 499 case 'titleonly': 500 $sql_match = 'p.post_subject'; 501 $sql_match_where = ' AND p.post_id = t.topic_first_post_id'; 502 $join_topic = true; 503 break; 504 505 case 'msgonly': 506 $sql_match = 'p.post_text'; 507 $sql_match_where = ''; 508 break; 509 510 case 'firstpost': 511 $sql_match = 'p.post_subject, p.post_text'; 512 $sql_match_where = ' AND p.post_id = t.topic_first_post_id'; 513 $join_topic = true; 514 break; 515 516 default: 517 $sql_match = 'p.post_subject, p.post_text'; 518 $sql_match_where = ''; 519 break; 520 } 521 522 $search_query = $this->search_query; 523 524 /** 525 * Allow changing the query used to search for posts using fulltext_mysql 526 * 527 * @event core.search_mysql_keywords_main_query_before 528 * @var string search_query The parsed keywords used for this search 529 * @var int result_count The previous result count for the format of the query. 530 * Set to 0 to force a re-count 531 * @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED 532 * @var array author_ary Array of user_id containing the users to filter the results to 533 * @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant) 534 * @var array ex_fid_ary Which forums not to search on 535 * @var int topic_id Limit the search to this topic_id only 536 * @var string sql_sort_table Extra tables to include in the SQL query. 537 * Used in conjunction with sql_sort_join 538 * @var string sql_sort_join SQL conditions to join all the tables used together. 539 * Used in conjunction with sql_sort_table 540 * @var int sort_days Time, in days, of the oldest possible post to list 541 * @var string sql_match Which columns to do the search on. 542 * @var string sql_match_where Extra conditions to use to properly filter the matching process 543 * @var string sort_by_sql The possible predefined sort types 544 * @var string sort_key The sort type used from the possible sort types 545 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 546 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 547 * @var int start How many posts to skip in the search results (used for pagination) 548 * @since 3.1.5-RC1 549 */ 550 $vars = array( 551 'search_query', 552 'result_count', 553 'join_topic', 554 'author_ary', 555 'author_name', 556 'ex_fid_ary', 557 'topic_id', 558 'sql_sort_table', 559 'sql_sort_join', 560 'sort_days', 561 'sql_match', 562 'sql_match_where', 563 'sort_by_sql', 564 'sort_key', 565 'sort_dir', 566 'sql_sort', 567 'start', 568 ); 569 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_keywords_main_query_before', compact($vars))); 570 571 $sql_select = (!$result_count) ? 'SQL_CALC_FOUND_ROWS ' : ''; 572 $sql_select = ($type == 'posts') ? $sql_select . 'p.post_id' : 'DISTINCT ' . $sql_select . 't.topic_id'; 573 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : ''; 574 $field = ($type == 'posts') ? 'post_id' : 'topic_id'; 575 if (count($author_ary) && $author_name) 576 { 577 // first one matches post of registered users, second one guests and deleted users 578 $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 . ')'; 579 } 580 else if (count($author_ary)) 581 { 582 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary); 583 } 584 else 585 { 586 $sql_author = ''; 587 } 588 589 $sql_where_options = $sql_sort_join; 590 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : ''; 591 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : ''; 592 $sql_where_options .= (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 593 $sql_where_options .= ' AND ' . $post_visibility; 594 $sql_where_options .= $sql_author; 595 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 596 $sql_where_options .= $sql_match_where; 597 598 $sql = "SELECT $sql_select 599 FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p 600 WHERE MATCH ($sql_match) AGAINST ('" . $this->db->sql_escape(htmlspecialchars_decode($this->search_query)) . "' IN BOOLEAN MODE) 601 $sql_where_options 602 ORDER BY $sql_sort"; 603 $this->db->sql_return_on_error(true); 604 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 605 606 while ($row = $this->db->sql_fetchrow($result)) 607 { 608 $id_ary[] = (int) $row[$field]; 609 } 610 $this->db->sql_freeresult($result); 611 612 $id_ary = array_unique($id_ary); 613 614 // if the total result count is not cached yet, retrieve it from the db 615 if (!$result_count && count($id_ary)) 616 { 617 $sql_found_rows = 'SELECT FOUND_ROWS() as result_count'; 618 $result = $this->db->sql_query($sql_found_rows); 619 $result_count = (int) $this->db->sql_fetchfield('result_count'); 620 $this->db->sql_freeresult($result); 621 622 if (!$result_count) 623 { 624 return false; 625 } 626 } 627 628 if ($start >= $result_count) 629 { 630 $start = floor(($result_count - 1) / $per_page) * $per_page; 631 632 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 633 634 while ($row = $this->db->sql_fetchrow($result)) 635 { 636 $id_ary[] = (int) $row[$field]; 637 } 638 $this->db->sql_freeresult($result); 639 640 $id_ary = array_unique($id_ary); 641 } 642 643 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page 644 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir); 645 $id_ary = array_slice($id_ary, 0, (int) $per_page); 646 647 return $result_count; 648 } 649 650 /** 651 * Performs a search on an author's posts without caring about message contents. Depends on display specific params 652 * 653 * @param string $type contains either posts or topics depending on what should be searched for 654 * @param boolean $firstpost_only if true, only topic starting posts will be considered 655 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 656 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 657 * @param string $sort_dir is either a or d representing ASC and DESC 658 * @param string $sort_days specifies the maximum amount of days a post may be old 659 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 660 * @param string $post_visibility specifies which types of posts the user can view in which forums 661 * @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 662 * @param array $author_ary an array of author ids 663 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 664 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 665 * @param int $start indicates the first index of the page 666 * @param int $per_page number of ids each page is supposed to contain 667 * @return boolean|int total number of results 668 */ 669 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) 670 { 671 // No author? No posts 672 if (!count($author_ary)) 673 { 674 return 0; 675 } 676 677 // generate a search_key from all the options to identify the results 678 $search_key_array = array( 679 '', 680 $type, 681 ($firstpost_only) ? 'firstpost' : '', 682 '', 683 '', 684 $sort_days, 685 $sort_key, 686 $topic_id, 687 implode(',', $ex_fid_ary), 688 $post_visibility, 689 implode(',', $author_ary), 690 $author_name, 691 ); 692 693 /** 694 * Allow changing the search_key for cached results 695 * 696 * @event core.search_mysql_by_author_modify_search_key 697 * @var array search_key_array Array with search parameters to generate the search_key 698 * @var string type Searching type ('posts', 'topics') 699 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered 700 * @var int sort_days Time, in days, of the oldest possible post to list 701 * @var string sort_key The sort type used from the possible sort types 702 * @var int topic_id Limit the search to this topic_id only 703 * @var array ex_fid_ary Which forums not to search on 704 * @var string post_visibility Post visibility data 705 * @var array author_ary Array of user_id containing the users to filter the results to 706 * @var string author_name The username to search on 707 * @since 3.1.7-RC1 708 */ 709 $vars = array( 710 'search_key_array', 711 'type', 712 'firstpost_only', 713 'sort_days', 714 'sort_key', 715 'topic_id', 716 'ex_fid_ary', 717 'post_visibility', 718 'author_ary', 719 'author_name', 720 ); 721 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_author_modify_search_key', compact($vars))); 722 723 $search_key = md5(implode('#', $search_key_array)); 724 725 if ($start < 0) 726 { 727 $start = 0; 728 } 729 730 // try reading the results from cache 731 $result_count = 0; 732 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 733 { 734 return $result_count; 735 } 736 737 $id_ary = array(); 738 739 // Create some display specific sql strings 740 if ($author_name) 741 { 742 // first one matches post of registered users, second one guests and deleted users 743 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 744 } 745 else 746 { 747 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); 748 } 749 $sql_fora = (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 750 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : ''; 751 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 752 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : ''; 753 754 // Build sql strings for sorting 755 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 756 $sql_sort_table = $sql_sort_join = ''; 757 switch ($sql_sort[0]) 758 { 759 case 'u': 760 $sql_sort_table = USERS_TABLE . ' u, '; 761 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 762 break; 763 764 case 't': 765 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : ''; 766 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : ''; 767 break; 768 769 case 'f': 770 $sql_sort_table = FORUMS_TABLE . ' f, '; 771 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 772 break; 773 } 774 775 $m_approve_fid_sql = ' AND ' . $post_visibility; 776 777 /** 778 * Allow changing the query used to search for posts by author in fulltext_mysql 779 * 780 * @event core.search_mysql_author_query_before 781 * @var int result_count The previous result count for the format of the query. 782 * Set to 0 to force a re-count 783 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen 784 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table 785 * @var string type Either "posts" or "topics" specifying the type of search being made 786 * @var array author_ary Array of user_id containing the users to filter the results to 787 * @var string author_name An extra username to search on 788 * @var string sql_author SQL WHERE condition for the post author ids 789 * @var int topic_id Limit the search to this topic_id only 790 * @var string sql_topic_id SQL of topic_id 791 * @var string sort_by_sql The possible predefined sort types 792 * @var string sort_key The sort type used from the possible sort types 793 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 794 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 795 * @var string sort_days Time, in days, that the oldest post showing can have 796 * @var string sql_time The SQL to search on the time specifyed by sort_days 797 * @var bool firstpost_only Wether or not to search only on the first post of the topics 798 * @var string sql_firstpost The SQL with the conditions to join the tables when using firstpost_only 799 * @var array ex_fid_ary Forum ids that must not be searched on 800 * @var array sql_fora SQL query for ex_fid_ary 801 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions 802 * @var int start How many posts to skip in the search results (used for pagination) 803 * @since 3.1.5-RC1 804 */ 805 $vars = array( 806 'result_count', 807 'sql_sort_table', 808 'sql_sort_join', 809 'type', 810 'author_ary', 811 'author_name', 812 'sql_author', 813 'topic_id', 814 'sql_topic_id', 815 'sort_by_sql', 816 'sort_key', 817 'sort_dir', 818 'sql_sort', 819 'sort_days', 820 'sql_time', 821 'firstpost_only', 822 'sql_firstpost', 823 'ex_fid_ary', 824 'sql_fora', 825 'm_approve_fid_sql', 826 'start', 827 ); 828 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars))); 829 830 // If the cache was completely empty count the results 831 $calc_results = ($result_count) ? '' : 'SQL_CALC_FOUND_ROWS '; 832 833 // Build the query for really selecting the post_ids 834 if ($type == 'posts') 835 { 836 $sql = "SELECT {$calc_results}p.post_id 837 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . " 838 WHERE $sql_author 839 $sql_topic_id 840 $sql_firstpost 841 $m_approve_fid_sql 842 $sql_fora 843 $sql_sort_join 844 $sql_time 845 ORDER BY $sql_sort"; 846 $field = 'post_id'; 847 } 848 else 849 { 850 $sql = "SELECT {$calc_results}t.topic_id 851 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p 852 WHERE $sql_author 853 $sql_topic_id 854 $sql_firstpost 855 $m_approve_fid_sql 856 $sql_fora 857 AND t.topic_id = p.topic_id 858 $sql_sort_join 859 $sql_time 860 GROUP BY t.topic_id 861 ORDER BY $sql_sort"; 862 $field = 'topic_id'; 863 } 864 865 // Only read one block of posts from the db and then cache it 866 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 867 868 while ($row = $this->db->sql_fetchrow($result)) 869 { 870 $id_ary[] = (int) $row[$field]; 871 } 872 $this->db->sql_freeresult($result); 873 874 // retrieve the total result count if needed 875 if (!$result_count) 876 { 877 $sql_found_rows = 'SELECT FOUND_ROWS() as result_count'; 878 $result = $this->db->sql_query($sql_found_rows); 879 $result_count = (int) $this->db->sql_fetchfield('result_count'); 880 $this->db->sql_freeresult($result); 881 882 if (!$result_count) 883 { 884 return false; 885 } 886 } 887 888 if ($start >= $result_count) 889 { 890 $start = floor(($result_count - 1) / $per_page) * $per_page; 891 892 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 893 while ($row = $this->db->sql_fetchrow($result)) 894 { 895 $id_ary[] = (int) $row[$field]; 896 } 897 $this->db->sql_freeresult($result); 898 899 $id_ary = array_unique($id_ary); 900 } 901 902 if (count($id_ary)) 903 { 904 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); 905 $id_ary = array_slice($id_ary, 0, $per_page); 906 907 return $result_count; 908 } 909 return false; 910 } 911 912 /** 913 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated 914 * 915 * @param string $mode contains the post mode: edit, post, reply, quote ... 916 * @param int $post_id contains the post id of the post to index 917 * @param string $message contains the post text of the post 918 * @param string $subject contains the subject of the post to index 919 * @param int $poster_id contains the user id of the poster 920 * @param int $forum_id contains the forum id of parent forum of the post 921 */ 922 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id) 923 { 924 // Split old and new post/subject to obtain array of words 925 $split_text = $this->split_message($message); 926 $split_title = ($subject) ? $this->split_message($subject) : array(); 927 928 $words = array_unique(array_merge($split_text, $split_title)); 929 930 /** 931 * Event to modify method arguments and words before the MySQL search index is updated 932 * 933 * @event core.search_mysql_index_before 934 * @var string mode Contains the post mode: edit, post, reply, quote 935 * @var int post_id The id of the post which is modified/created 936 * @var string message New or updated post content 937 * @var string subject New or updated post subject 938 * @var int poster_id Post author's user id 939 * @var int forum_id The id of the forum in which the post is located 940 * @var array words List of words added to the index 941 * @var array split_text Array of words from the message 942 * @var array split_title Array of words from the title 943 * @since 3.2.3-RC1 944 */ 945 $vars = array( 946 'mode', 947 'post_id', 948 'message', 949 'subject', 950 'poster_id', 951 'forum_id', 952 'words', 953 'split_text', 954 'split_title', 955 ); 956 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_index_before', compact($vars))); 957 958 unset($split_text); 959 unset($split_title); 960 961 // destroy cached search results containing any of the words removed or added 962 $this->destroy_cache($words, array($poster_id)); 963 964 unset($words); 965 } 966 967 /** 968 * Destroy cached results, that might be outdated after deleting a post 969 */ 970 public function index_remove($post_ids, $author_ids, $forum_ids) 971 { 972 $this->destroy_cache(array(), array_unique($author_ids)); 973 } 974 975 /** 976 * Destroy old cache entries 977 */ 978 public function tidy() 979 { 980 // destroy too old cached search results 981 $this->destroy_cache(array()); 982 983 $this->config->set('search_last_gc', time(), false); 984 } 985 986 /** 987 * Create fulltext index 988 * 989 * @return string|bool error string is returned incase of errors otherwise false 990 */ 991 public function create_index($acp_module, $u_action) 992 { 993 // Make sure we can actually use MySQL with fulltext indexes 994 if ($error = $this->init()) 995 { 996 return $error; 997 } 998 999 if (empty($this->stats)) 1000 { 1001 $this->get_stats(); 1002 } 1003 1004 $alter_list = array(); 1005 1006 if (!isset($this->stats['post_subject'])) 1007 { 1008 $alter_entry = array(); 1009 if ($this->db->get_sql_layer() == 'mysqli' || version_compare($this->db->sql_server_info(true), '4.1.3', '>=')) 1010 { 1011 $alter_entry[] = 'MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL'; 1012 } 1013 else 1014 { 1015 $alter_entry[] = 'MODIFY post_subject text NOT NULL'; 1016 } 1017 $alter_entry[] = 'ADD FULLTEXT (post_subject)'; 1018 $alter_list[] = $alter_entry; 1019 } 1020 1021 if (!isset($this->stats['post_content'])) 1022 { 1023 $alter_entry = array(); 1024 if ($this->db->get_sql_layer() == 'mysqli' || version_compare($this->db->sql_server_info(true), '4.1.3', '>=')) 1025 { 1026 $alter_entry[] = 'MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL'; 1027 } 1028 else 1029 { 1030 $alter_entry[] = 'MODIFY post_text mediumtext NOT NULL'; 1031 } 1032 1033 $alter_entry[] = 'ADD FULLTEXT post_content (post_text, post_subject)'; 1034 $alter_list[] = $alter_entry; 1035 } 1036 1037 $sql_queries = []; 1038 1039 foreach ($alter_list as $alter) 1040 { 1041 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter); 1042 } 1043 1044 if (!isset($this->stats['post_text'])) 1045 { 1046 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)'; 1047 } 1048 1049 $stats = $this->stats; 1050 1051 /** 1052 * Event to modify SQL queries before the MySQL search index is created 1053 * 1054 * @event core.search_mysql_create_index_before 1055 * @var array sql_queries Array with queries for creating the search index 1056 * @var array stats Array with statistics of the current index (read only) 1057 * @since 3.2.3-RC1 1058 */ 1059 $vars = array( 1060 'sql_queries', 1061 'stats', 1062 ); 1063 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_create_index_before', compact($vars))); 1064 1065 foreach ($sql_queries as $sql_query) 1066 { 1067 $this->db->sql_query($sql_query); 1068 } 1069 1070 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1071 1072 return false; 1073 } 1074 1075 /** 1076 * Drop fulltext index 1077 * 1078 * @return string|bool error string is returned incase of errors otherwise false 1079 */ 1080 public function delete_index($acp_module, $u_action) 1081 { 1082 // Make sure we can actually use MySQL with fulltext indexes 1083 if ($error = $this->init()) 1084 { 1085 return $error; 1086 } 1087 1088 if (empty($this->stats)) 1089 { 1090 $this->get_stats(); 1091 } 1092 1093 $alter = array(); 1094 1095 if (isset($this->stats['post_subject'])) 1096 { 1097 $alter[] = 'DROP INDEX post_subject'; 1098 } 1099 1100 if (isset($this->stats['post_content'])) 1101 { 1102 $alter[] = 'DROP INDEX post_content'; 1103 } 1104 1105 if (isset($this->stats['post_text'])) 1106 { 1107 $alter[] = 'DROP INDEX post_text'; 1108 } 1109 1110 $sql_queries = []; 1111 1112 if (count($alter)) 1113 { 1114 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter); 1115 } 1116 1117 $stats = $this->stats; 1118 1119 /** 1120 * Event to modify SQL queries before the MySQL search index is deleted 1121 * 1122 * @event core.search_mysql_delete_index_before 1123 * @var array sql_queries Array with queries for deleting the search index 1124 * @var array stats Array with statistics of the current index (read only) 1125 * @since 3.2.3-RC1 1126 */ 1127 $vars = array( 1128 'sql_queries', 1129 'stats', 1130 ); 1131 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_delete_index_before', compact($vars))); 1132 1133 foreach ($sql_queries as $sql_query) 1134 { 1135 $this->db->sql_query($sql_query); 1136 } 1137 1138 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1139 1140 return false; 1141 } 1142 1143 /** 1144 * Returns true if both FULLTEXT indexes exist 1145 */ 1146 public function index_created() 1147 { 1148 if (empty($this->stats)) 1149 { 1150 $this->get_stats(); 1151 } 1152 1153 return isset($this->stats['post_subject']) && isset($this->stats['post_content']) && isset($this->stats['post_text']); 1154 } 1155 1156 /** 1157 * Returns an associative array containing information about the indexes 1158 */ 1159 public function index_stats() 1160 { 1161 if (empty($this->stats)) 1162 { 1163 $this->get_stats(); 1164 } 1165 1166 return array( 1167 $this->user->lang['FULLTEXT_MYSQL_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0, 1168 ); 1169 } 1170 1171 /** 1172 * Computes the stats and store them in the $this->stats associative array 1173 */ 1174 protected function get_stats() 1175 { 1176 if (strpos($this->db->get_sql_layer(), 'mysql') === false) 1177 { 1178 $this->stats = array(); 1179 return; 1180 } 1181 1182 $sql = 'SHOW INDEX 1183 FROM ' . POSTS_TABLE; 1184 $result = $this->db->sql_query($sql); 1185 1186 while ($row = $this->db->sql_fetchrow($result)) 1187 { 1188 // deal with older MySQL versions which didn't use Index_type 1189 $index_type = (isset($row['Index_type'])) ? $row['Index_type'] : $row['Comment']; 1190 1191 if ($index_type == 'FULLTEXT') 1192 { 1193 if ($row['Key_name'] == 'post_subject') 1194 { 1195 $this->stats['post_subject'] = $row; 1196 } 1197 else if ($row['Key_name'] == 'post_text') 1198 { 1199 $this->stats['post_text'] = $row; 1200 } 1201 else if ($row['Key_name'] == 'post_content') 1202 { 1203 $this->stats['post_content'] = $row; 1204 } 1205 } 1206 } 1207 $this->db->sql_freeresult($result); 1208 1209 $this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE); 1210 } 1211 1212 /** 1213 * Display a note, that UTF-8 support is not available with certain versions of PHP 1214 * 1215 * @return associative array containing template and config variables 1216 */ 1217 public function acp() 1218 { 1219 $tpl = ' 1220 <dl> 1221 <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> 1222 <dd>' . $this->config['fulltext_mysql_min_word_len'] . '</dd> 1223 </dl> 1224 <dl> 1225 <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> 1226 <dd>' . $this->config['fulltext_mysql_max_word_len'] . '</dd> 1227 </dl> 1228 '; 1229 1230 // These are fields required in the config table 1231 return array( 1232 'tpl' => $tpl, 1233 'config' => array() 1234 ); 1235 } 1236 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Nov 11 20:33:01 2020 | Cross-referenced by PHPXref 0.7.1 |