| [ 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 PostgreSQL 18 */ 19 class fulltext_postgres 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 * Stores the tsearch query 35 * @var string 36 */ 37 protected $tsearch_query; 38 39 /** 40 * True if phrase search is supported. 41 * PostgreSQL fulltext currently doesn't support it 42 * @var boolean 43 */ 44 protected $phrase_search = false; 45 46 /** 47 * Config object 48 * @var \phpbb\config\config 49 */ 50 protected $config; 51 52 /** 53 * Database connection 54 * @var \phpbb\db\driver\driver_interface 55 */ 56 protected $db; 57 58 /** 59 * phpBB event dispatcher object 60 * @var \phpbb\event\dispatcher_interface 61 */ 62 protected $phpbb_dispatcher; 63 64 /** 65 * User object 66 * @var \phpbb\user 67 */ 68 protected $user; 69 70 /** 71 * Contains tidied search query. 72 * Operators are prefixed in search query and common words excluded 73 * @var string 74 */ 75 protected $search_query; 76 77 /** 78 * Contains common words. 79 * Common words are words with length less/more than min/max length 80 * @var array 81 */ 82 protected $common_words = array(); 83 84 /** 85 * Associative array stores the min and max word length to be searched 86 * @var array 87 */ 88 protected $word_length = array(); 89 90 /** 91 * Constructor 92 * Creates a new \phpbb\search\fulltext_postgres, which is used as a search backend 93 * 94 * @param string|bool $error Any error that occurs is passed on through this reference variable otherwise false 95 * @param string $phpbb_root_path Relative path to phpBB root 96 * @param string $phpEx PHP file extension 97 * @param \phpbb\auth\auth $auth Auth object 98 * @param \phpbb\config\config $config Config object 99 * @param \phpbb\db\driver\driver_interface Database object 100 * @param \phpbb\user $user User object 101 * @param \phpbb\event\dispatcher_interface $phpbb_dispatcher Event dispatcher object 102 */ 103 public function __construct(&$error, $phpbb_root_path, $phpEx, $auth, $config, $db, $user, $phpbb_dispatcher) 104 { 105 $this->config = $config; 106 $this->db = $db; 107 $this->phpbb_dispatcher = $phpbb_dispatcher; 108 $this->user = $user; 109 110 $this->word_length = array('min' => $this->config['fulltext_postgres_min_word_len'], 'max' => $this->config['fulltext_postgres_max_word_len']); 111 112 /** 113 * Load the UTF tools 114 */ 115 if (!function_exists('utf8_strlen')) 116 { 117 include($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx); 118 } 119 120 $error = false; 121 } 122 123 /** 124 * Returns the name of this search backend to be displayed to administrators 125 * 126 * @return string Name 127 */ 128 public function get_name() 129 { 130 return 'PostgreSQL Fulltext'; 131 } 132 133 /** 134 * Returns the search_query 135 * 136 * @return string search query 137 */ 138 public function get_search_query() 139 { 140 return $this->search_query; 141 } 142 143 /** 144 * Returns the common_words array 145 * 146 * @return array common words that are ignored by search backend 147 */ 148 public function get_common_words() 149 { 150 return $this->common_words; 151 } 152 153 /** 154 * Returns the word_length array 155 * 156 * @return array min and max word length for searching 157 */ 158 public function get_word_length() 159 { 160 return $this->word_length; 161 } 162 163 /** 164 * Returns if phrase search is supported or not 165 * 166 * @return bool 167 */ 168 public function supports_phrase_search() 169 { 170 return $this->phrase_search; 171 } 172 173 /** 174 * Checks for correct PostgreSQL version and stores min/max word length in the config 175 * 176 * @return string|bool Language key of the error/incompatiblity occurred 177 */ 178 public function init() 179 { 180 if ($this->db->get_sql_layer() != 'postgres') 181 { 182 return $this->user->lang['FULLTEXT_POSTGRES_INCOMPATIBLE_DATABASE']; 183 } 184 185 return false; 186 } 187 188 /** 189 * Splits keywords entered by a user into an array of words stored in $this->split_words 190 * Stores the tidied search query in $this->search_query 191 * 192 * @param string &$keywords Contains the keyword as entered by the user 193 * @param string $terms is either 'all' or 'any' 194 * @return bool false if no valid keywords were found and otherwise true 195 */ 196 public function split_keywords(&$keywords, $terms) 197 { 198 if ($terms == 'all') 199 { 200 $match = array('#\sand\s#iu', '#\sor\s#iu', '#\snot\s#iu', '#(^|\s)\+#', '#(^|\s)-#', '#(^|\s)\|#'); 201 $replace = array(' +', ' |', ' -', ' +', ' -', ' |'); 202 203 $keywords = preg_replace($match, $replace, $keywords); 204 } 205 206 // Filter out as above 207 $split_keywords = preg_replace("#[\"\n\r\t]+#", ' ', trim(htmlspecialchars_decode($keywords))); 208 209 // Split words 210 $split_keywords = preg_replace('#([^\p{L}\p{N}\'*"()])#u', '$1$1', str_replace('\'\'', '\' \'', trim($split_keywords))); 211 $matches = array(); 212 preg_match_all('#(?:[^\p{L}\p{N}*"()]|^)([+\-|]?(?:[\p{L}\p{N}*"()]+\'?)*[\p{L}\p{N}*"()])(?:[^\p{L}\p{N}*"()]|$)#u', $split_keywords, $matches); 213 $this->split_words = $matches[1]; 214 215 foreach ($this->split_words as $i => $word) 216 { 217 $clean_word = preg_replace('#^[+\-|"]#', '', $word); 218 219 // check word length 220 $clean_len = utf8_strlen(str_replace('*', '', $clean_word)); 221 if (($clean_len < $this->config['fulltext_postgres_min_word_len']) || ($clean_len > $this->config['fulltext_postgres_max_word_len'])) 222 { 223 $this->common_words[] = $word; 224 unset($this->split_words[$i]); 225 } 226 } 227 228 if ($terms == 'any') 229 { 230 $this->search_query = ''; 231 $this->tsearch_query = ''; 232 foreach ($this->split_words as $word) 233 { 234 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0) || (strpos($word, '|') === 0)) 235 { 236 $word = substr($word, 1); 237 } 238 $this->search_query .= $word . ' '; 239 $this->tsearch_query .= '|' . $word . ' '; 240 } 241 } 242 else 243 { 244 $this->search_query = ''; 245 $this->tsearch_query = ''; 246 foreach ($this->split_words as $word) 247 { 248 if (strpos($word, '+') === 0) 249 { 250 $this->search_query .= $word . ' '; 251 $this->tsearch_query .= '&' . substr($word, 1) . ' '; 252 } 253 else if (strpos($word, '-') === 0) 254 { 255 $this->search_query .= $word . ' '; 256 $this->tsearch_query .= '&!' . substr($word, 1) . ' '; 257 } 258 else if (strpos($word, '|') === 0) 259 { 260 $this->search_query .= $word . ' '; 261 $this->tsearch_query .= '|' . substr($word, 1) . ' '; 262 } 263 else 264 { 265 $this->search_query .= '+' . $word . ' '; 266 $this->tsearch_query .= '&' . $word . ' '; 267 } 268 } 269 } 270 271 $this->tsearch_query = substr($this->tsearch_query, 1); 272 $this->search_query = utf8_htmlspecialchars($this->search_query); 273 274 if ($this->search_query) 275 { 276 $this->split_words = array_values($this->split_words); 277 sort($this->split_words); 278 return true; 279 } 280 return false; 281 } 282 283 /** 284 * Turns text into an array of words 285 * @param string $text contains post text/subject 286 */ 287 public function split_message($text) 288 { 289 // Split words 290 $text = preg_replace('#([^\p{L}\p{N}\'*])#u', '$1$1', str_replace('\'\'', '\' \'', trim($text))); 291 $matches = array(); 292 preg_match_all('#(?:[^\p{L}\p{N}*]|^)([+\-|]?(?:[\p{L}\p{N}*]+\'?)*[\p{L}\p{N}*])(?:[^\p{L}\p{N}*]|$)#u', $text, $matches); 293 $text = $matches[1]; 294 295 // remove too short or too long words 296 $text = array_values($text); 297 for ($i = 0, $n = count($text); $i < $n; $i++) 298 { 299 $text[$i] = trim($text[$i]); 300 if (utf8_strlen($text[$i]) < $this->config['fulltext_postgres_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_postgres_max_word_len']) 301 { 302 unset($text[$i]); 303 } 304 } 305 306 return array_values($text); 307 } 308 309 /** 310 * Performs a search on keywords depending on display specific params. You have to run split_keywords() first 311 * 312 * @param string $type contains either posts or topics depending on what should be searched for 313 * @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) 314 * @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) 315 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 316 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 317 * @param string $sort_dir is either a or d representing ASC and DESC 318 * @param string $sort_days specifies the maximum amount of days a post may be old 319 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 320 * @param string $post_visibility specifies which types of posts the user can view in which forums 321 * @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 322 * @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty 323 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 324 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 325 * @param int $start indicates the first index of the page 326 * @param int $per_page number of ids each page is supposed to contain 327 * @return boolean|int total number of results 328 */ 329 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) 330 { 331 // No keywords? No posts 332 if (!$this->search_query) 333 { 334 return false; 335 } 336 337 // When search query contains queries like -foo 338 if (strpos($this->search_query, '+') === false) 339 { 340 return false; 341 } 342 343 // generate a search_key from all the options to identify the results 344 $search_key_array = array( 345 implode(', ', $this->split_words), 346 $type, 347 $fields, 348 $terms, 349 $sort_days, 350 $sort_key, 351 $topic_id, 352 implode(',', $ex_fid_ary), 353 $post_visibility, 354 implode(',', $author_ary) 355 ); 356 357 /** 358 * Allow changing the search_key for cached results 359 * 360 * @event core.search_postgres_by_keyword_modify_search_key 361 * @var array search_key_array Array with search parameters to generate the search_key 362 * @var string type Searching type ('posts', 'topics') 363 * @var string fields Searching fields ('titleonly', 'msgonly', 'firstpost', 'all') 364 * @var string terms Searching terms ('all', 'any') 365 * @var int sort_days Time, in days, of the oldest possible post to list 366 * @var string sort_key The sort type used from the possible sort types 367 * @var int topic_id Limit the search to this topic_id only 368 * @var array ex_fid_ary Which forums not to search on 369 * @var string post_visibility Post visibility data 370 * @var array author_ary Array of user_id containing the users to filter the results to 371 * @since 3.1.7-RC1 372 */ 373 $vars = array( 374 'search_key_array', 375 'type', 376 'fields', 377 'terms', 378 'sort_days', 379 'sort_key', 380 'topic_id', 381 'ex_fid_ary', 382 'post_visibility', 383 'author_ary', 384 ); 385 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_keyword_modify_search_key', compact($vars))); 386 387 $search_key = md5(implode('#', $search_key_array)); 388 389 if ($start < 0) 390 { 391 $start = 0; 392 } 393 394 // try reading the results from cache 395 $result_count = 0; 396 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 397 { 398 return $result_count; 399 } 400 401 $id_ary = array(); 402 403 $join_topic = ($type == 'posts') ? false : true; 404 405 // Build sql strings for sorting 406 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 407 $sql_sort_table = $sql_sort_join = ''; 408 409 switch ($sql_sort[0]) 410 { 411 case 'u': 412 $sql_sort_table = USERS_TABLE . ' u, '; 413 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 414 break; 415 416 case 't': 417 $join_topic = true; 418 break; 419 420 case 'f': 421 $sql_sort_table = FORUMS_TABLE . ' f, '; 422 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 423 break; 424 } 425 426 // Build some display specific sql strings 427 switch ($fields) 428 { 429 case 'titleonly': 430 $sql_match = 'p.post_subject'; 431 $sql_match_where = ' AND p.post_id = t.topic_first_post_id'; 432 $join_topic = true; 433 break; 434 435 case 'msgonly': 436 $sql_match = 'p.post_text'; 437 $sql_match_where = ''; 438 break; 439 440 case 'firstpost': 441 $sql_match = 'p.post_subject, p.post_text'; 442 $sql_match_where = ' AND p.post_id = t.topic_first_post_id'; 443 $join_topic = true; 444 break; 445 446 default: 447 $sql_match = 'p.post_subject, p.post_text'; 448 $sql_match_where = ''; 449 break; 450 } 451 452 $tsearch_query = $this->tsearch_query; 453 454 /** 455 * Allow changing the query used to search for posts using fulltext_postgres 456 * 457 * @event core.search_postgres_keywords_main_query_before 458 * @var string tsearch_query The parsed keywords used for this search 459 * @var int result_count The previous result count for the format of the query. 460 * Set to 0 to force a re-count 461 * @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED 462 * @var array author_ary Array of user_id containing the users to filter the results to 463 * @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant) 464 * @var array ex_fid_ary Which forums not to search on 465 * @var int topic_id Limit the search to this topic_id only 466 * @var string sql_sort_table Extra tables to include in the SQL query. 467 * Used in conjunction with sql_sort_join 468 * @var string sql_sort_join SQL conditions to join all the tables used together. 469 * Used in conjunction with sql_sort_table 470 * @var int sort_days Time, in days, of the oldest possible post to list 471 * @var string sql_match Which columns to do the search on. 472 * @var string sql_match_where Extra conditions to use to properly filter the matching process 473 * @var string sort_by_sql The possible predefined sort types 474 * @var string sort_key The sort type used from the possible sort types 475 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 476 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 477 * @var int start How many posts to skip in the search results (used for pagination) 478 * @since 3.1.5-RC1 479 */ 480 $vars = array( 481 'tsearch_query', 482 'result_count', 483 'join_topic', 484 'author_ary', 485 'author_name', 486 'ex_fid_ary', 487 'topic_id', 488 'sql_sort_table', 489 'sql_sort_join', 490 'sort_days', 491 'sql_match', 492 'sql_match_where', 493 'sort_by_sql', 494 'sort_key', 495 'sort_dir', 496 'sql_sort', 497 'start', 498 ); 499 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_keywords_main_query_before', compact($vars))); 500 501 $sql_select = ($type == 'posts') ? 'p.post_id' : 'DISTINCT t.topic_id, ' . $sort_by_sql[$sort_key]; 502 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : ''; 503 $field = ($type == 'posts') ? 'post_id' : 'topic_id'; 504 505 if (count($author_ary) && $author_name) 506 { 507 // first one matches post of registered users, second one guests and deleted users 508 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 509 } 510 else if (count($author_ary)) 511 { 512 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary); 513 } 514 else 515 { 516 $sql_author = ''; 517 } 518 519 $sql_where_options = $sql_sort_join; 520 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : ''; 521 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : ''; 522 $sql_where_options .= (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 523 $sql_where_options .= ' AND ' . $post_visibility; 524 $sql_where_options .= $sql_author; 525 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 526 $sql_where_options .= $sql_match_where; 527 528 $sql_match = str_replace(',', " || ' ' ||", $sql_match); 529 $tmp_sql_match = "to_tsvector ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', " . $sql_match . ") @@ to_tsquery ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', '" . $this->db->sql_escape($this->tsearch_query) . "')"; 530 531 $this->db->sql_transaction('begin'); 532 533 $sql_from = "FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p"; 534 $sql_where = "WHERE (" . $tmp_sql_match . ") 535 $sql_where_options"; 536 $sql = "SELECT $sql_select 537 $sql_from 538 $sql_where 539 ORDER BY $sql_sort"; 540 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 541 542 while ($row = $this->db->sql_fetchrow($result)) 543 { 544 $id_ary[] = $row[$field]; 545 } 546 $this->db->sql_freeresult($result); 547 548 $id_ary = array_unique($id_ary); 549 550 // if the total result count is not cached yet, retrieve it from the db 551 if (!$result_count) 552 { 553 $sql_count = "SELECT COUNT(*) as result_count 554 $sql_from 555 $sql_where"; 556 $result = $this->db->sql_query($sql_count); 557 $result_count = (int) $this->db->sql_fetchfield('result_count'); 558 $this->db->sql_freeresult($result); 559 560 if (!$result_count) 561 { 562 return false; 563 } 564 } 565 566 $this->db->sql_transaction('commit'); 567 568 if ($start >= $result_count) 569 { 570 $start = floor(($result_count - 1) / $per_page) * $per_page; 571 572 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 573 574 while ($row = $this->db->sql_fetchrow($result)) 575 { 576 $id_ary[] = $row[$field]; 577 } 578 $this->db->sql_freeresult($result); 579 580 $id_ary = array_unique($id_ary); 581 } 582 583 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page 584 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir); 585 $id_ary = array_slice($id_ary, 0, (int) $per_page); 586 587 return $result_count; 588 } 589 590 /** 591 * Performs a search on an author's posts without caring about message contents. Depends on display specific params 592 * 593 * @param string $type contains either posts or topics depending on what should be searched for 594 * @param boolean $firstpost_only if true, only topic starting posts will be considered 595 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 596 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 597 * @param string $sort_dir is either a or d representing ASC and DESC 598 * @param string $sort_days specifies the maximum amount of days a post may be old 599 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 600 * @param string $post_visibility specifies which types of posts the user can view in which forums 601 * @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 602 * @param array $author_ary an array of author ids 603 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 604 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 605 * @param int $start indicates the first index of the page 606 * @param int $per_page number of ids each page is supposed to contain 607 * @return boolean|int total number of results 608 */ 609 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) 610 { 611 // No author? No posts 612 if (!count($author_ary)) 613 { 614 return 0; 615 } 616 617 // generate a search_key from all the options to identify the results 618 $search_key_array = array( 619 '', 620 $type, 621 ($firstpost_only) ? 'firstpost' : '', 622 '', 623 '', 624 $sort_days, 625 $sort_key, 626 $topic_id, 627 implode(',', $ex_fid_ary), 628 $post_visibility, 629 implode(',', $author_ary), 630 $author_name, 631 ); 632 633 /** 634 * Allow changing the search_key for cached results 635 * 636 * @event core.search_postgres_by_author_modify_search_key 637 * @var array search_key_array Array with search parameters to generate the search_key 638 * @var string type Searching type ('posts', 'topics') 639 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered 640 * @var int sort_days Time, in days, of the oldest possible post to list 641 * @var string sort_key The sort type used from the possible sort types 642 * @var int topic_id Limit the search to this topic_id only 643 * @var array ex_fid_ary Which forums not to search on 644 * @var string post_visibility Post visibility data 645 * @var array author_ary Array of user_id containing the users to filter the results to 646 * @var string author_name The username to search on 647 * @since 3.1.7-RC1 648 */ 649 $vars = array( 650 'search_key_array', 651 'type', 652 'firstpost_only', 653 'sort_days', 654 'sort_key', 655 'topic_id', 656 'ex_fid_ary', 657 'post_visibility', 658 'author_ary', 659 'author_name', 660 ); 661 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_author_modify_search_key', compact($vars))); 662 663 $search_key = md5(implode('#', $search_key_array)); 664 665 if ($start < 0) 666 { 667 $start = 0; 668 } 669 670 // try reading the results from cache 671 $result_count = 0; 672 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 673 { 674 return $result_count; 675 } 676 677 $id_ary = array(); 678 679 // Create some display specific sql strings 680 if ($author_name) 681 { 682 // first one matches post of registered users, second one guests and deleted users 683 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 684 } 685 else 686 { 687 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); 688 } 689 $sql_fora = (count($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 690 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : ''; 691 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 692 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : ''; 693 694 // Build sql strings for sorting 695 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 696 $sql_sort_table = $sql_sort_join = ''; 697 switch ($sql_sort[0]) 698 { 699 case 'u': 700 $sql_sort_table = USERS_TABLE . ' u, '; 701 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 702 break; 703 704 case 't': 705 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : ''; 706 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : ''; 707 break; 708 709 case 'f': 710 $sql_sort_table = FORUMS_TABLE . ' f, '; 711 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 712 break; 713 } 714 715 $m_approve_fid_sql = ' AND ' . $post_visibility; 716 717 /** 718 * Allow changing the query used to search for posts by author in fulltext_postgres 719 * 720 * @event core.search_postgres_author_count_query_before 721 * @var int result_count The previous result count for the format of the query. 722 * Set to 0 to force a re-count 723 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen 724 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table 725 * @var array author_ary Array of user_id containing the users to filter the results to 726 * @var string author_name An extra username to search on 727 * @var string sql_author SQL WHERE condition for the post author ids 728 * @var int topic_id Limit the search to this topic_id only 729 * @var string sql_topic_id SQL of topic_id 730 * @var string sort_by_sql The possible predefined sort types 731 * @var string sort_key The sort type used from the possible sort types 732 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 733 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 734 * @var string sort_days Time, in days, that the oldest post showing can have 735 * @var string sql_time The SQL to search on the time specifyed by sort_days 736 * @var bool firstpost_only Wether or not to search only on the first post of the topics 737 * @var array ex_fid_ary Forum ids that must not be searched on 738 * @var array sql_fora SQL query for ex_fid_ary 739 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions 740 * @var int start How many posts to skip in the search results (used for pagination) 741 * @since 3.1.5-RC1 742 */ 743 $vars = array( 744 'result_count', 745 'sql_sort_table', 746 'sql_sort_join', 747 'author_ary', 748 'author_name', 749 'sql_author', 750 'topic_id', 751 'sql_topic_id', 752 'sort_by_sql', 753 'sort_key', 754 'sort_dir', 755 'sql_sort', 756 'sort_days', 757 'sql_time', 758 'firstpost_only', 759 'ex_fid_ary', 760 'sql_fora', 761 'm_approve_fid_sql', 762 'start', 763 ); 764 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_author_count_query_before', compact($vars))); 765 766 // Build the query for really selecting the post_ids 767 if ($type == 'posts') 768 { 769 $sql = "SELECT p.post_id 770 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . " 771 WHERE $sql_author 772 $sql_topic_id 773 $sql_firstpost 774 $m_approve_fid_sql 775 $sql_fora 776 $sql_sort_join 777 $sql_time 778 ORDER BY $sql_sort"; 779 $field = 'post_id'; 780 } 781 else 782 { 783 $sql = "SELECT t.topic_id 784 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p 785 WHERE $sql_author 786 $sql_topic_id 787 $sql_firstpost 788 $m_approve_fid_sql 789 $sql_fora 790 AND t.topic_id = p.topic_id 791 $sql_sort_join 792 $sql_time 793 GROUP BY t.topic_id, $sort_by_sql[$sort_key] 794 ORDER BY $sql_sort"; 795 $field = 'topic_id'; 796 } 797 798 $this->db->sql_transaction('begin'); 799 800 // Only read one block of posts from the db and then cache it 801 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 802 803 while ($row = $this->db->sql_fetchrow($result)) 804 { 805 $id_ary[] = $row[$field]; 806 } 807 $this->db->sql_freeresult($result); 808 809 // retrieve the total result count if needed 810 if (!$result_count) 811 { 812 if ($type == 'posts') 813 { 814 $sql_count = "SELECT COUNT(*) as result_count 815 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . " 816 WHERE $sql_author 817 $sql_topic_id 818 $sql_firstpost 819 $m_approve_fid_sql 820 $sql_fora 821 $sql_sort_join 822 $sql_time"; 823 } 824 else 825 { 826 $sql_count = "SELECT COUNT(*) as result_count 827 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p 828 WHERE $sql_author 829 $sql_topic_id 830 $sql_firstpost 831 $m_approve_fid_sql 832 $sql_fora 833 AND t.topic_id = p.topic_id 834 $sql_sort_join 835 $sql_time 836 GROUP BY t.topic_id, $sort_by_sql[$sort_key]"; 837 } 838 839 $this->db->sql_query($sql_count); 840 $result_count = (int) $this->db->sql_fetchfield('result_count'); 841 842 if (!$result_count) 843 { 844 return false; 845 } 846 } 847 848 $this->db->sql_transaction('commit'); 849 850 if ($start >= $result_count) 851 { 852 $start = floor(($result_count - 1) / $per_page) * $per_page; 853 854 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 855 while ($row = $this->db->sql_fetchrow($result)) 856 { 857 $id_ary[] = (int) $row[$field]; 858 } 859 $this->db->sql_freeresult($result); 860 861 $id_ary = array_unique($id_ary); 862 } 863 864 if (count($id_ary)) 865 { 866 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); 867 $id_ary = array_slice($id_ary, 0, $per_page); 868 869 return $result_count; 870 } 871 return false; 872 } 873 874 /** 875 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated 876 * 877 * @param string $mode contains the post mode: edit, post, reply, quote ... 878 * @param int $post_id contains the post id of the post to index 879 * @param string $message contains the post text of the post 880 * @param string $subject contains the subject of the post to index 881 * @param int $poster_id contains the user id of the poster 882 * @param int $forum_id contains the forum id of parent forum of the post 883 */ 884 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id) 885 { 886 // Split old and new post/subject to obtain array of words 887 $split_text = $this->split_message($message); 888 $split_title = ($subject) ? $this->split_message($subject) : array(); 889 890 $words = array_unique(array_merge($split_text, $split_title)); 891 892 /** 893 * Event to modify method arguments and words before the PostgreSQL search index is updated 894 * 895 * @event core.search_postgres_index_before 896 * @var string mode Contains the post mode: edit, post, reply, quote 897 * @var int post_id The id of the post which is modified/created 898 * @var string message New or updated post content 899 * @var string subject New or updated post subject 900 * @var int poster_id Post author's user id 901 * @var int forum_id The id of the forum in which the post is located 902 * @var array words Array of words added to the index 903 * @var array split_text Array of words from the message 904 * @var array split_title Array of words from the title 905 * @since 3.2.3-RC1 906 */ 907 $vars = array( 908 'mode', 909 'post_id', 910 'message', 911 'subject', 912 'poster_id', 913 'forum_id', 914 'words', 915 'split_text', 916 'split_title', 917 ); 918 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_index_before', compact($vars))); 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(), $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 $this->config->set('search_last_gc', time(), false); 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 PostgreSQL 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 $sql_queries = []; 967 968 if (!isset($this->stats['post_subject'])) 969 { 970 $sql_queries[] = "CREATE INDEX " . POSTS_TABLE . "_" . $this->config['fulltext_postgres_ts_name'] . "_post_subject ON " . POSTS_TABLE . " USING gin (to_tsvector ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', post_subject))"; 971 } 972 973 if (!isset($this->stats['post_content'])) 974 { 975 $sql_queries[] = "CREATE INDEX " . POSTS_TABLE . "_" . $this->config['fulltext_postgres_ts_name'] . "_post_content ON " . POSTS_TABLE . " USING gin (to_tsvector ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', post_text))"; 976 } 977 978 if (!isset($this->stats['post_subject_content'])) 979 { 980 $sql_queries[] = "CREATE INDEX " . POSTS_TABLE . "_" . $this->config['fulltext_postgres_ts_name'] . "_post_subject_content ON " . POSTS_TABLE . " USING gin (to_tsvector ('" . $this->db->sql_escape($this->config['fulltext_postgres_ts_name']) . "', post_subject || ' ' || post_text))"; 981 } 982 983 $stats = $this->stats; 984 985 /** 986 * Event to modify SQL queries before the Postgres search index is created 987 * 988 * @event core.search_postgres_create_index_before 989 * @var array sql_queries Array with queries for creating the search index 990 * @var array stats Array with statistics of the current index (read only) 991 * @since 3.2.3-RC1 992 */ 993 $vars = array( 994 'sql_queries', 995 'stats', 996 ); 997 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_create_index_before', compact($vars))); 998 999 foreach ($sql_queries as $sql_query) 1000 { 1001 $this->db->sql_query($sql_query); 1002 } 1003 1004 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1005 1006 return false; 1007 } 1008 1009 /** 1010 * Drop fulltext index 1011 * 1012 * @return string|bool error string is returned incase of errors otherwise false 1013 */ 1014 public function delete_index($acp_module, $u_action) 1015 { 1016 // Make sure we can actually use PostgreSQL with fulltext indexes 1017 if ($error = $this->init()) 1018 { 1019 return $error; 1020 } 1021 1022 if (empty($this->stats)) 1023 { 1024 $this->get_stats(); 1025 } 1026 1027 $sql_queries = []; 1028 1029 if (isset($this->stats['post_subject'])) 1030 { 1031 $sql_queries[] = 'DROP INDEX ' . $this->stats['post_subject']['relname']; 1032 } 1033 1034 if (isset($this->stats['post_content'])) 1035 { 1036 $sql_queries[] = 'DROP INDEX ' . $this->stats['post_content']['relname']; 1037 } 1038 1039 if (isset($this->stats['post_subject_content'])) 1040 { 1041 $sql_queries[] = 'DROP INDEX ' . $this->stats['post_subject_content']['relname']; 1042 } 1043 1044 $stats = $this->stats; 1045 1046 /** 1047 * Event to modify SQL queries before the Postgres search index is created 1048 * 1049 * @event core.search_postgres_delete_index_before 1050 * @var array sql_queries Array with queries for deleting the search index 1051 * @var array stats Array with statistics of the current index (read only) 1052 * @since 3.2.3-RC1 1053 */ 1054 $vars = array( 1055 'sql_queries', 1056 'stats', 1057 ); 1058 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_delete_index_before', compact($vars))); 1059 1060 foreach ($sql_queries as $sql_query) 1061 { 1062 $this->db->sql_query($sql_query); 1063 } 1064 1065 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 1066 1067 return false; 1068 } 1069 1070 /** 1071 * Returns true if both FULLTEXT indexes exist 1072 */ 1073 public function index_created() 1074 { 1075 if (empty($this->stats)) 1076 { 1077 $this->get_stats(); 1078 } 1079 1080 return (isset($this->stats['post_subject']) && isset($this->stats['post_content'])) ? true : false; 1081 } 1082 1083 /** 1084 * Returns an associative array containing information about the indexes 1085 */ 1086 public function index_stats() 1087 { 1088 if (empty($this->stats)) 1089 { 1090 $this->get_stats(); 1091 } 1092 1093 return array( 1094 $this->user->lang['FULLTEXT_POSTGRES_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0, 1095 ); 1096 } 1097 1098 /** 1099 * Computes the stats and store them in the $this->stats associative array 1100 */ 1101 protected function get_stats() 1102 { 1103 if ($this->db->get_sql_layer() != 'postgres') 1104 { 1105 $this->stats = array(); 1106 return; 1107 } 1108 1109 $sql = "SELECT c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef 1110 FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2 1111 WHERE c1.relname = '" . POSTS_TABLE . "' 1112 AND pg_catalog.pg_table_is_visible(c1.oid) 1113 AND c1.oid = i.indrelid 1114 AND i.indexrelid = c2.oid"; 1115 $result = $this->db->sql_query($sql); 1116 1117 while ($row = $this->db->sql_fetchrow($result)) 1118 { 1119 // deal with older PostgreSQL versions which didn't use Index_type 1120 if (strpos($row['indexdef'], 'to_tsvector') !== false) 1121 { 1122 if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_subject' || $row['relname'] == POSTS_TABLE . '_post_subject') 1123 { 1124 $this->stats['post_subject'] = $row; 1125 } 1126 else if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_content' || $row['relname'] == POSTS_TABLE . '_post_content') 1127 { 1128 $this->stats['post_content'] = $row; 1129 } 1130 else if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_subject_content' || $row['relname'] == POSTS_TABLE . '_post_subject_content') 1131 { 1132 $this->stats['post_subject_content'] = $row; 1133 } 1134 } 1135 } 1136 $this->db->sql_freeresult($result); 1137 1138 $this->stats['total_posts'] = $this->config['num_posts']; 1139 } 1140 1141 /** 1142 * Display various options that can be configured for the backend from the acp 1143 * 1144 * @return associative array containing template and config variables 1145 */ 1146 public function acp() 1147 { 1148 $tpl = ' 1149 <dl> 1150 <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK_EXPLAIN'] . '</span></dt> 1151 <dd>' . (($this->db->get_sql_layer() == 'postgres') ? $this->user->lang['YES'] : $this->user->lang['NO']) . '</dd> 1152 </dl> 1153 <dl> 1154 <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME_EXPLAIN'] . '</span></dt> 1155 <dd><select name="config[fulltext_postgres_ts_name]">'; 1156 1157 if ($this->db->get_sql_layer() == 'postgres') 1158 { 1159 $sql = 'SELECT cfgname AS ts_name 1160 FROM pg_ts_config'; 1161 $result = $this->db->sql_query($sql); 1162 1163 while ($row = $this->db->sql_fetchrow($result)) 1164 { 1165 $tpl .= '<option value="' . $row['ts_name'] . '"' . ($row['ts_name'] === $this->config['fulltext_postgres_ts_name'] ? ' selected="selected"' : '') . '>' . $row['ts_name'] . '</option>'; 1166 } 1167 $this->db->sql_freeresult($result); 1168 } 1169 else 1170 { 1171 $tpl .= '<option value="' . $this->config['fulltext_postgres_ts_name'] . '" selected="selected">' . $this->config['fulltext_postgres_ts_name'] . '</option>'; 1172 } 1173 1174 $tpl .= '</select></dd> 1175 </dl> 1176 <dl> 1177 <dt><label for="fulltext_postgres_min_word_len">' . $this->user->lang['FULLTEXT_POSTGRES_MIN_WORD_LEN'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_MIN_WORD_LEN_EXPLAIN'] . '</span></dt> 1178 <dd><input id="fulltext_postgres_min_word_len" type="number" min="0" max="255" name="config[fulltext_postgres_min_word_len]" value="' . (int) $this->config['fulltext_postgres_min_word_len'] . '" /></dd> 1179 </dl> 1180 <dl> 1181 <dt><label for="fulltext_postgres_max_word_len">' . $this->user->lang['FULLTEXT_POSTGRES_MAX_WORD_LEN'] . $this->user->lang['COLON'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_MAX_WORD_LEN_EXPLAIN'] . '</span></dt> 1182 <dd><input id="fulltext_postgres_max_word_len" type="number" min="0" max="255" name="config[fulltext_postgres_max_word_len]" value="' . (int) $this->config['fulltext_postgres_max_word_len'] . '" /></dd> 1183 </dl> 1184 '; 1185 1186 // These are fields required in the config table 1187 return array( 1188 'tpl' => $tpl, 1189 'config' => array('fulltext_postgres_ts_name' => 'string', 'fulltext_postgres_min_word_len' => 'integer:0:255', 'fulltext_postgres_max_word_len' => 'integer:0:255') 1190 ); 1191 } 1192 }
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 |