| [ Index ] |
PHP Cross Reference of phpBB-3.3.14-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 $db 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/incompatibility occurred 154 */ 155 public function init() 156 { 157 if ($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(html_entity_decode($keywords, ENT_COMPAT))); 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 = ($type == 'posts') ? 'DISTINCT p.post_id' : 'DISTINCT t.topic_id'; 572 $sql_select .= $sort_by_sql[$sort_key] ? ", {$sort_by_sql[$sort_key]}" : ''; 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(html_entity_decode($this->search_query, ENT_COMPAT)) . "' 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 // if the total result count is not cached yet, retrieve it from the db 614 if (!$result_count && count($id_ary)) 615 { 616 $sql_found_rows = str_replace("SELECT $sql_select", "SELECT COUNT($sql_select) as result_count", $sql); 617 $result = $this->db->sql_query($sql_found_rows); 618 $result_count = (int) $this->db->sql_fetchfield('result_count'); 619 $this->db->sql_freeresult($result); 620 621 if (!$result_count) 622 { 623 return false; 624 } 625 } 626 627 if ($start >= $result_count) 628 { 629 $start = floor(($result_count - 1) / $per_page) * $per_page; 630 631 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 632 633 while ($row = $this->db->sql_fetchrow($result)) 634 { 635 $id_ary[] = (int) $row[$field]; 636 } 637 $this->db->sql_freeresult($result); 638 639 $id_ary = array_unique($id_ary); 640 } 641 642 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page 643 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir); 644 $id_ary = array_slice($id_ary, 0, (int) $per_page); 645 646 return $result_count; 647 } 648 649 /** 650 * Performs a search on an author's posts without caring about message contents. Depends on display specific params 651 * 652 * @param string $type contains either posts or topics depending on what should be searched for 653 * @param boolean $firstpost_only if true, only topic starting posts will be considered 654 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 655 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 656 * @param string $sort_dir is either a or d representing ASC and DESC 657 * @param string $sort_days specifies the maximum amount of days a post may be old 658 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 659 * @param string $post_visibility specifies which types of posts the user can view in which forums 660 * @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 661 * @param array $author_ary an array of author ids 662 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 663 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 664 * @param int $start indicates the first index of the page 665 * @param int $per_page number of ids each page is supposed to contain 666 * @return boolean|int total number of results 667 */ 668 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) 669 { 670 // No author? No posts 671 if (!count($author_ary)) 672 { 673 return 0; 674 } 675 676 // generate a search_key from all the options to identify the results 677 $search_key_array = array( 678 '', 679 $type, 680 ($firstpost_only) ? 'firstpost' : '', 681 '', 682 '', 683 $sort_days, 684 $sort_key, 685 $topic_id, 686 implode(',', $ex_fid_ary), 687 $post_visibility, 688 implode(',', $author_ary), 689 $author_name, 690 ); 691 692 /** 693 * Allow changing the search_key for cached results 694 * 695 * @event core.search_mysql_by_author_modify_search_key 696 * @var array search_key_array Array with search parameters to generate the search_key 697 * @var string type Searching type ('posts', 'topics') 698 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered 699 * @var int sort_days Time, in days, of the oldest possible post to list 700 * @var string sort_key The sort type used from the possible sort types 701 * @var int topic_id Limit the search to this topic_id only 702 * @var array ex_fid_ary Which forums not to search on 703 * @var string post_visibility Post visibility data 704 * @var array author_ary Array of user_id containing the users to filter the results to 705 * @var string author_name The username to search on 706 * @since 3.1.7-RC1 707 */ 708 $vars = array( 709 'search_key_array', 710 'type', 711 'firstpost_only', 712 'sort_days', 713 'sort_key', 714 'topic_id', 715 'ex_fid_ary', 716 'post_visibility', 717 'author_ary', 718 'author_name', 719 ); 720 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_by_author_modify_search_key', compact($vars))); 721 722 $search_key = md5(implode('#', $search_key_array)); 723 724 if ($start < 0) 725 { 726 $start = 0; 727 } 728 729 // try reading the results from cache 730 $result_count = 0; 731 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 732 { 733 return $result_count; 734 } 735 736 $id_ary = array(); 737 738 // Create some display specific sql strings 739 if ($author_name) 740 { 741 // first one matches post of registered users, second one guests and deleted users 742 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 743 } 744 else 745 { 746 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); 747 } 748 $sql_fora = (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 749 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : ''; 750 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 751 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : ''; 752 753 // Build sql strings for sorting 754 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 755 $sql_sort_table = $sql_sort_join = ''; 756 switch ($sql_sort[0]) 757 { 758 case 'u': 759 $sql_sort_table = USERS_TABLE . ' u, '; 760 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 761 break; 762 763 case 't': 764 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : ''; 765 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : ''; 766 break; 767 768 case 'f': 769 $sql_sort_table = FORUMS_TABLE . ' f, '; 770 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 771 break; 772 } 773 774 $m_approve_fid_sql = ' AND ' . $post_visibility; 775 776 /** 777 * Allow changing the query used to search for posts by author in fulltext_mysql 778 * 779 * @event core.search_mysql_author_query_before 780 * @var int result_count The previous result count for the format of the query. 781 * Set to 0 to force a re-count 782 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen 783 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table 784 * @var string type Either "posts" or "topics" specifying the type of search being made 785 * @var array author_ary Array of user_id containing the users to filter the results to 786 * @var string author_name An extra username to search on 787 * @var string sql_author SQL WHERE condition for the post author ids 788 * @var int topic_id Limit the search to this topic_id only 789 * @var string sql_topic_id SQL of topic_id 790 * @var string sort_by_sql The possible predefined sort types 791 * @var string sort_key The sort type used from the possible sort types 792 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 793 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 794 * @var string sort_days Time, in days, that the oldest post showing can have 795 * @var string sql_time The SQL to search on the time specifyed by sort_days 796 * @var bool firstpost_only Wether or not to search only on the first post of the topics 797 * @var string sql_firstpost The SQL with the conditions to join the tables when using firstpost_only 798 * @var array ex_fid_ary Forum ids that must not be searched on 799 * @var array sql_fora SQL query for ex_fid_ary 800 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions 801 * @var int start How many posts to skip in the search results (used for pagination) 802 * @since 3.1.5-RC1 803 */ 804 $vars = array( 805 'result_count', 806 'sql_sort_table', 807 'sql_sort_join', 808 'type', 809 'author_ary', 810 'author_name', 811 'sql_author', 812 'topic_id', 813 'sql_topic_id', 814 'sort_by_sql', 815 'sort_key', 816 'sort_dir', 817 'sql_sort', 818 'sort_days', 819 'sql_time', 820 'firstpost_only', 821 'sql_firstpost', 822 'ex_fid_ary', 823 'sql_fora', 824 'm_approve_fid_sql', 825 'start', 826 ); 827 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_author_query_before', compact($vars))); 828 829 // If the cache was completely empty count the results 830 $sql_select = ($type == 'posts') ? 'p.post_id' : 't.topic_id'; 831 $sql_select .= $sort_by_sql[$sort_key] ? ", {$sort_by_sql[$sort_key]}" : ''; 832 833 // Build the query for really selecting the post_ids 834 if ($type == 'posts') 835 { 836 $sql = "SELECT $sql_select 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 $sql_select 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 $sql_select 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 = str_replace("SELECT $sql_select", "SELECT COUNT(*) as result_count", $sql); 878 $result = $this->db->sql_query($sql_found_rows); 879 $result_count = ($type == 'posts') ? (int) $this->db->sql_fetchfield('result_count') : count($this->db->sql_fetchrowset($result)); 880 881 $this->db->sql_freeresult($result); 882 883 if (!$result_count) 884 { 885 return false; 886 } 887 } 888 889 if ($start >= $result_count) 890 { 891 $start = floor(($result_count - 1) / $per_page) * $per_page; 892 893 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 894 while ($row = $this->db->sql_fetchrow($result)) 895 { 896 $id_ary[] = (int) $row[$field]; 897 } 898 $this->db->sql_freeresult($result); 899 900 $id_ary = array_unique($id_ary); 901 } 902 903 if (count($id_ary)) 904 { 905 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); 906 $id_ary = array_slice($id_ary, 0, $per_page); 907 908 return $result_count; 909 } 910 return false; 911 } 912 913 /** 914 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated 915 * 916 * @param string $mode contains the post mode: edit, post, reply, quote ... 917 * @param int $post_id contains the post id of the post to index 918 * @param string $message contains the post text of the post 919 * @param string $subject contains the subject of the post to index 920 * @param int $poster_id contains the user id of the poster 921 * @param int $forum_id contains the forum id of parent forum of the post 922 */ 923 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id) 924 { 925 // Split old and new post/subject to obtain array of words 926 $split_text = $this->split_message($message); 927 $split_title = ($subject) ? $this->split_message($subject) : array(); 928 929 $words = array_unique(array_merge($split_text, $split_title)); 930 931 /** 932 * Event to modify method arguments and words before the MySQL search index is updated 933 * 934 * @event core.search_mysql_index_before 935 * @var string mode Contains the post mode: edit, post, reply, quote 936 * @var int post_id The id of the post which is modified/created 937 * @var string message New or updated post content 938 * @var string subject New or updated post subject 939 * @var int poster_id Post author's user id 940 * @var int forum_id The id of the forum in which the post is located 941 * @var array words List of words added to the index 942 * @var array split_text Array of words from the message 943 * @var array split_title Array of words from the title 944 * @since 3.2.3-RC1 945 */ 946 $vars = array( 947 'mode', 948 'post_id', 949 'message', 950 'subject', 951 'poster_id', 952 'forum_id', 953 'words', 954 'split_text', 955 'split_title', 956 ); 957 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_index_before', compact($vars))); 958 959 unset($split_text); 960 unset($split_title); 961 962 // destroy cached search results containing any of the words removed or added 963 $this->destroy_cache($words, array($poster_id)); 964 965 unset($words); 966 } 967 968 /** 969 * Destroy cached results, that might be outdated after deleting a post 970 */ 971 public function index_remove($post_ids, $author_ids, $forum_ids) 972 { 973 $this->destroy_cache(array(), array_unique($author_ids)); 974 } 975 976 /** 977 * Destroy old cache entries 978 */ 979 public function tidy() 980 { 981 // destroy too old cached search results 982 $this->destroy_cache(array()); 983 984 $this->config->set('search_last_gc', time(), false); 985 } 986 987 /** 988 * Create fulltext index 989 * 990 * @return string|bool error string is returned incase of errors otherwise false 991 */ 992 public function create_index($acp_module, $u_action) 993 { 994 // Make sure we can actually use MySQL with fulltext indexes 995 if ($error = $this->init()) 996 { 997 return $error; 998 } 999 1000 if (empty($this->stats)) 1001 { 1002 $this->get_stats(); 1003 } 1004 1005 $alter_list = array(); 1006 1007 if (!isset($this->stats['post_subject'])) 1008 { 1009 $alter_entry = array(); 1010 $alter_entry[] = 'MODIFY post_subject varchar(255) COLLATE utf8_unicode_ci DEFAULT \'\' NOT NULL'; 1011 $alter_entry[] = 'ADD FULLTEXT (post_subject)'; 1012 $alter_list[] = $alter_entry; 1013 } 1014 1015 if (!isset($this->stats['post_content'])) 1016 { 1017 $alter_entry = array(); 1018 $alter_entry[] = 'MODIFY post_text mediumtext COLLATE utf8_unicode_ci NOT NULL'; 1019 $alter_entry[] = 'ADD FULLTEXT post_content (post_text, post_subject)'; 1020 $alter_list[] = $alter_entry; 1021 } 1022 1023 $sql_queries = []; 1024 1025 foreach ($alter_list as $alter) 1026 { 1027 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter); 1028 } 1029 1030 if (!isset($this->stats['post_text'])) 1031 { 1032 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ADD FULLTEXT post_text (post_text)'; 1033 } 1034 1035 $stats = $this->stats; 1036 1037 /** 1038 * Event to modify SQL queries before the MySQL search index is created 1039 * 1040 * @event core.search_mysql_create_index_before 1041 * @var array sql_queries Array with queries for creating the search index 1042 * @var array stats Array with statistics of the current index (read only) 1043 * @since 3.2.3-RC1 1044 */ 1045 $vars = array( 1046 'sql_queries', 1047 'stats', 1048 ); 1049 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_create_index_before', compact($vars))); 1050 1051 foreach ($sql_queries as $sql_query) 1052 { 1053 $this->db->sql_query($sql_query); 1054 } 1055 1056 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1057 1058 return false; 1059 } 1060 1061 /** 1062 * Drop fulltext index 1063 * 1064 * @return string|bool error string is returned incase of errors otherwise false 1065 */ 1066 public function delete_index($acp_module, $u_action) 1067 { 1068 // Make sure we can actually use MySQL with fulltext indexes 1069 if ($error = $this->init()) 1070 { 1071 return $error; 1072 } 1073 1074 if (empty($this->stats)) 1075 { 1076 $this->get_stats(); 1077 } 1078 1079 $alter = array(); 1080 1081 if (isset($this->stats['post_subject'])) 1082 { 1083 $alter[] = 'DROP INDEX post_subject'; 1084 } 1085 1086 if (isset($this->stats['post_content'])) 1087 { 1088 $alter[] = 'DROP INDEX post_content'; 1089 } 1090 1091 if (isset($this->stats['post_text'])) 1092 { 1093 $alter[] = 'DROP INDEX post_text'; 1094 } 1095 1096 $sql_queries = []; 1097 1098 if (count($alter)) 1099 { 1100 $sql_queries[] = 'ALTER TABLE ' . POSTS_TABLE . ' ' . implode(', ', $alter); 1101 } 1102 1103 $stats = $this->stats; 1104 1105 /** 1106 * Event to modify SQL queries before the MySQL search index is deleted 1107 * 1108 * @event core.search_mysql_delete_index_before 1109 * @var array sql_queries Array with queries for deleting the search index 1110 * @var array stats Array with statistics of the current index (read only) 1111 * @since 3.2.3-RC1 1112 */ 1113 $vars = array( 1114 'sql_queries', 1115 'stats', 1116 ); 1117 extract($this->phpbb_dispatcher->trigger_event('core.search_mysql_delete_index_before', compact($vars))); 1118 1119 foreach ($sql_queries as $sql_query) 1120 { 1121 $this->db->sql_query($sql_query); 1122 } 1123 1124 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1125 1126 return false; 1127 } 1128 1129 /** 1130 * Returns true if both FULLTEXT indexes exist 1131 */ 1132 public function index_created() 1133 { 1134 if (empty($this->stats)) 1135 { 1136 $this->get_stats(); 1137 } 1138 1139 return isset($this->stats['post_subject']) && isset($this->stats['post_content']) && isset($this->stats['post_text']); 1140 } 1141 1142 /** 1143 * Returns an associative array containing information about the indexes 1144 */ 1145 public function index_stats() 1146 { 1147 if (empty($this->stats)) 1148 { 1149 $this->get_stats(); 1150 } 1151 1152 return array( 1153 $this->user->lang['FULLTEXT_MYSQL_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0, 1154 ); 1155 } 1156 1157 /** 1158 * Computes the stats and store them in the $this->stats associative array 1159 */ 1160 protected function get_stats() 1161 { 1162 if (strpos($this->db->get_sql_layer(), 'mysql') === false) 1163 { 1164 $this->stats = array(); 1165 return; 1166 } 1167 1168 $sql = 'SHOW INDEX 1169 FROM ' . POSTS_TABLE; 1170 $result = $this->db->sql_query($sql); 1171 1172 while ($row = $this->db->sql_fetchrow($result)) 1173 { 1174 // deal with older MySQL versions which didn't use Index_type 1175 $index_type = (isset($row['Index_type'])) ? $row['Index_type'] : $row['Comment']; 1176 1177 if ($index_type == 'FULLTEXT') 1178 { 1179 if ($row['Key_name'] == 'post_subject') 1180 { 1181 $this->stats['post_subject'] = $row; 1182 } 1183 else if ($row['Key_name'] == 'post_text') 1184 { 1185 $this->stats['post_text'] = $row; 1186 } 1187 else if ($row['Key_name'] == 'post_content') 1188 { 1189 $this->stats['post_content'] = $row; 1190 } 1191 } 1192 } 1193 $this->db->sql_freeresult($result); 1194 1195 $this->stats['total_posts'] = empty($this->stats) ? 0 : $this->db->get_estimated_row_count(POSTS_TABLE); 1196 } 1197 1198 /** 1199 * Display a note, that UTF-8 support is not available with certain versions of PHP 1200 * 1201 * @return associative array containing template and config variables 1202 */ 1203 public function acp() 1204 { 1205 $tpl = ' 1206 <dl> 1207 <dt><label>' . $this->user->lang['MIN_SEARCH_CHARS'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_MYSQL_MIN_SEARCH_CHARS_EXPLAIN'] . '</span></dt> 1208 <dd>' . $this->config['fulltext_mysql_min_word_len'] . '</dd> 1209 </dl> 1210 <dl> 1211 <dt><label>' . $this->user->lang['MAX_SEARCH_CHARS'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_MYSQL_MAX_SEARCH_CHARS_EXPLAIN'] . '</span></dt> 1212 <dd>' . $this->config['fulltext_mysql_max_word_len'] . '</dd> 1213 </dl> 1214 '; 1215 1216 // These are fields required in the config table 1217 return array( 1218 'tpl' => $tpl, 1219 'config' => array() 1220 ); 1221 } 1222 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Mon Nov 25 19:05:08 2024 | Cross-referenced by PHPXref 0.7.1 |