| [ Index ] |
PHP Cross Reference of phpBB-3.1.12-deutsch |
[Summary view] [Print] [Text view]
1 <?php 2 /** 3 * 4 * This file is part of the phpBB Forum Software package. 5 * 6 * @copyright (c) phpBB Limited <https://www.phpbb.com> 7 * @license GNU General Public License, version 2 (GPL-2.0) 8 * 9 * For full copyright and license information, please see 10 * the docs/CREDITS.txt file. 11 * 12 */ 13 14 namespace phpbb\search; 15 16 /** 17 * Fulltext search for 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 = sizeof($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'; 502 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : ''; 503 $field = ($type == 'posts') ? 'post_id' : 'topic_id'; 504 $sql_author = (sizeof($author_ary) == 1) ? ' = ' . $author_ary[0] : 'IN (' . implode(', ', $author_ary) . ')'; 505 506 if (sizeof($author_ary) && $author_name) 507 { 508 // first one matches post of registered users, second one guests and deleted users 509 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 510 } 511 else if (sizeof($author_ary)) 512 { 513 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary); 514 } 515 else 516 { 517 $sql_author = ''; 518 } 519 520 $sql_where_options = $sql_sort_join; 521 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : ''; 522 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : ''; 523 $sql_where_options .= (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 524 $sql_where_options .= ' AND ' . $post_visibility; 525 $sql_where_options .= $sql_author; 526 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 527 $sql_where_options .= $sql_match_where; 528 529 $tmp_sql_match = array(); 530 $sql_match = str_replace(',', " || ' ' ||", $sql_match); 531 $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) . "')"; 532 533 $this->db->sql_transaction('begin'); 534 535 $sql_from = "FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p"; 536 $sql_where = "WHERE (" . $tmp_sql_match . ") 537 $sql_where_options"; 538 $sql = "SELECT $sql_select 539 $sql_from 540 $sql_where 541 ORDER BY $sql_sort"; 542 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 543 544 while ($row = $this->db->sql_fetchrow($result)) 545 { 546 $id_ary[] = $row[$field]; 547 } 548 $this->db->sql_freeresult($result); 549 550 $id_ary = array_unique($id_ary); 551 552 // if the total result count is not cached yet, retrieve it from the db 553 if (!$result_count) 554 { 555 $sql_count = "SELECT COUNT(*) as result_count 556 $sql_from 557 $sql_where"; 558 $result = $this->db->sql_query($sql_count); 559 $result_count = (int) $this->db->sql_fetchfield('result_count'); 560 $this->db->sql_freeresult($result); 561 562 if (!$result_count) 563 { 564 return false; 565 } 566 } 567 568 $this->db->sql_transaction('commit'); 569 570 if ($start >= $result_count) 571 { 572 $start = floor(($result_count - 1) / $per_page) * $per_page; 573 574 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 575 576 while ($row = $this->db->sql_fetchrow($result)) 577 { 578 $id_ary[] = $row[$field]; 579 } 580 $this->db->sql_freeresult($result); 581 582 $id_ary = array_unique($id_ary); 583 } 584 585 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page 586 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir); 587 $id_ary = array_slice($id_ary, 0, (int) $per_page); 588 589 return $result_count; 590 } 591 592 /** 593 * Performs a search on an author's posts without caring about message contents. Depends on display specific params 594 * 595 * @param string $type contains either posts or topics depending on what should be searched for 596 * @param boolean $firstpost_only if true, only topic starting posts will be considered 597 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query 598 * @param string $sort_key is the key of $sort_by_sql for the selected sorting 599 * @param string $sort_dir is either a or d representing ASC and DESC 600 * @param string $sort_days specifies the maximum amount of days a post may be old 601 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched 602 * @param string $post_visibility specifies which types of posts the user can view in which forums 603 * @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 604 * @param array $author_ary an array of author ids 605 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match 606 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered 607 * @param int $start indicates the first index of the page 608 * @param int $per_page number of ids each page is supposed to contain 609 * @return boolean|int total number of results 610 */ 611 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) 612 { 613 // No author? No posts 614 if (!sizeof($author_ary)) 615 { 616 return 0; 617 } 618 619 // generate a search_key from all the options to identify the results 620 $search_key_array = array( 621 '', 622 $type, 623 ($firstpost_only) ? 'firstpost' : '', 624 '', 625 '', 626 $sort_days, 627 $sort_key, 628 $topic_id, 629 implode(',', $ex_fid_ary), 630 $post_visibility, 631 implode(',', $author_ary), 632 $author_name, 633 ); 634 635 /** 636 * Allow changing the search_key for cached results 637 * 638 * @event core.search_postgres_by_author_modify_search_key 639 * @var array search_key_array Array with search parameters to generate the search_key 640 * @var string type Searching type ('posts', 'topics') 641 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered 642 * @var int sort_days Time, in days, of the oldest possible post to list 643 * @var string sort_key The sort type used from the possible sort types 644 * @var int topic_id Limit the search to this topic_id only 645 * @var array ex_fid_ary Which forums not to search on 646 * @var string post_visibility Post visibility data 647 * @var array author_ary Array of user_id containing the users to filter the results to 648 * @var string author_name The username to search on 649 * @since 3.1.7-RC1 650 */ 651 $vars = array( 652 'search_key_array', 653 'type', 654 'firstpost_only', 655 'sort_days', 656 'sort_key', 657 'topic_id', 658 'ex_fid_ary', 659 'post_visibility', 660 'author_ary', 661 'author_name', 662 ); 663 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_author_modify_search_key', compact($vars))); 664 665 $search_key = md5(implode('#', $search_key_array)); 666 667 if ($start < 0) 668 { 669 $start = 0; 670 } 671 672 // try reading the results from cache 673 $result_count = 0; 674 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE) 675 { 676 return $result_count; 677 } 678 679 $id_ary = array(); 680 681 // Create some display specific sql strings 682 if ($author_name) 683 { 684 // first one matches post of registered users, second one guests and deleted users 685 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')'; 686 } 687 else 688 { 689 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary); 690 } 691 $sql_fora = (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : ''; 692 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : ''; 693 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : ''; 694 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : ''; 695 696 // Build sql strings for sorting 697 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC'); 698 $sql_sort_table = $sql_sort_join = ''; 699 switch ($sql_sort[0]) 700 { 701 case 'u': 702 $sql_sort_table = USERS_TABLE . ' u, '; 703 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster '; 704 break; 705 706 case 't': 707 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : ''; 708 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : ''; 709 break; 710 711 case 'f': 712 $sql_sort_table = FORUMS_TABLE . ' f, '; 713 $sql_sort_join = ' AND f.forum_id = p.forum_id '; 714 break; 715 } 716 717 $m_approve_fid_sql = ' AND ' . $post_visibility; 718 719 /** 720 * Allow changing the query used to search for posts by author in fulltext_postgres 721 * 722 * @event core.search_postgres_author_count_query_before 723 * @var int result_count The previous result count for the format of the query. 724 * Set to 0 to force a re-count 725 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen 726 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table 727 * @var array author_ary Array of user_id containing the users to filter the results to 728 * @var string author_name An extra username to search on 729 * @var string sql_author SQL WHERE condition for the post author ids 730 * @var int topic_id Limit the search to this topic_id only 731 * @var string sql_topic_id SQL of topic_id 732 * @var string sort_by_sql The possible predefined sort types 733 * @var string sort_key The sort type used from the possible sort types 734 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used 735 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir 736 * @var string sort_days Time, in days, that the oldest post showing can have 737 * @var string sql_time The SQL to search on the time specifyed by sort_days 738 * @var bool firstpost_only Wether or not to search only on the first post of the topics 739 * @var array ex_fid_ary Forum ids that must not be searched on 740 * @var array sql_fora SQL query for ex_fid_ary 741 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions 742 * @var int start How many posts to skip in the search results (used for pagination) 743 * @since 3.1.5-RC1 744 */ 745 $vars = array( 746 'result_count', 747 'sql_sort_table', 748 'sql_sort_join', 749 'author_ary', 750 'author_name', 751 'sql_author', 752 'topic_id', 753 'sql_topic_id', 754 'sort_by_sql', 755 'sort_key', 756 'sort_dir', 757 'sql_sort', 758 'sort_days', 759 'sql_time', 760 'firstpost_only', 761 'ex_fid_ary', 762 'sql_fora', 763 'm_approve_fid_sql', 764 'start', 765 ); 766 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_author_count_query_before', compact($vars))); 767 768 // Build the query for really selecting the post_ids 769 if ($type == 'posts') 770 { 771 $sql = "SELECT p.post_id 772 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . " 773 WHERE $sql_author 774 $sql_topic_id 775 $sql_firstpost 776 $m_approve_fid_sql 777 $sql_fora 778 $sql_sort_join 779 $sql_time 780 ORDER BY $sql_sort"; 781 $field = 'post_id'; 782 } 783 else 784 { 785 $sql = "SELECT t.topic_id 786 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p 787 WHERE $sql_author 788 $sql_topic_id 789 $sql_firstpost 790 $m_approve_fid_sql 791 $sql_fora 792 AND t.topic_id = p.topic_id 793 $sql_sort_join 794 $sql_time 795 GROUP BY t.topic_id, $sort_by_sql[$sort_key] 796 ORDER BY $sql_sort"; 797 $field = 'topic_id'; 798 } 799 800 $this->db->sql_transaction('begin'); 801 802 // Only read one block of posts from the db and then cache it 803 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 804 805 while ($row = $this->db->sql_fetchrow($result)) 806 { 807 $id_ary[] = $row[$field]; 808 } 809 $this->db->sql_freeresult($result); 810 811 // retrieve the total result count if needed 812 if (!$result_count) 813 { 814 if ($type == 'posts') 815 { 816 $sql_count = "SELECT COUNT(*) as result_count 817 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . " 818 WHERE $sql_author 819 $sql_topic_id 820 $sql_firstpost 821 $m_approve_fid_sql 822 $sql_fora 823 $sql_sort_join 824 $sql_time"; 825 } 826 else 827 { 828 $sql_count = "SELECT COUNT(*) as result_count 829 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p 830 WHERE $sql_author 831 $sql_topic_id 832 $sql_firstpost 833 $m_approve_fid_sql 834 $sql_fora 835 AND t.topic_id = p.topic_id 836 $sql_sort_join 837 $sql_time 838 GROUP BY t.topic_id, $sort_by_sql[$sort_key]"; 839 } 840 841 $result = $this->db->sql_query($sql_count); 842 $result_count = (int) $this->db->sql_fetchfield('result_count'); 843 844 if (!$result_count) 845 { 846 return false; 847 } 848 } 849 850 $this->db->sql_transaction('commit'); 851 852 if ($start >= $result_count) 853 { 854 $start = floor(($result_count - 1) / $per_page) * $per_page; 855 856 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start); 857 while ($row = $this->db->sql_fetchrow($result)) 858 { 859 $id_ary[] = (int) $row[$field]; 860 } 861 $this->db->sql_freeresult($result); 862 863 $id_ary = array_unique($id_ary); 864 } 865 866 if (sizeof($id_ary)) 867 { 868 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir); 869 $id_ary = array_slice($id_ary, 0, $per_page); 870 871 return $result_count; 872 } 873 return false; 874 } 875 876 /** 877 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated 878 * 879 * @param string $mode contains the post mode: edit, post, reply, quote ... 880 * @param int $post_id contains the post id of the post to index 881 * @param string $message contains the post text of the post 882 * @param string $subject contains the subject of the post to index 883 * @param int $poster_id contains the user id of the poster 884 * @param int $forum_id contains the forum id of parent forum of the post 885 */ 886 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id) 887 { 888 // Split old and new post/subject to obtain array of words 889 $split_text = $this->split_message($message); 890 $split_title = ($subject) ? $this->split_message($subject) : array(); 891 892 $words = array_unique(array_merge($split_text, $split_title)); 893 894 unset($split_text); 895 unset($split_title); 896 897 // destroy cached search results containing any of the words removed or added 898 $this->destroy_cache($words, array($poster_id)); 899 900 unset($words); 901 } 902 903 /** 904 * Destroy cached results, that might be outdated after deleting a post 905 */ 906 public function index_remove($post_ids, $author_ids, $forum_ids) 907 { 908 $this->destroy_cache(array(), $author_ids); 909 } 910 911 /** 912 * Destroy old cache entries 913 */ 914 public function tidy() 915 { 916 // destroy too old cached search results 917 $this->destroy_cache(array()); 918 919 set_config('search_last_gc', time(), true); 920 } 921 922 /** 923 * Create fulltext index 924 * 925 * @return string|bool error string is returned incase of errors otherwise false 926 */ 927 public function create_index($acp_module, $u_action) 928 { 929 // Make sure we can actually use PostgreSQL with fulltext indexes 930 if ($error = $this->init()) 931 { 932 return $error; 933 } 934 935 if (empty($this->stats)) 936 { 937 $this->get_stats(); 938 } 939 940 if (!isset($this->stats['post_subject'])) 941 { 942 $this->db->sql_query("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))"); 943 } 944 945 if (!isset($this->stats['post_content'])) 946 { 947 $this->db->sql_query("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 || ' ' || post_subject))"); 948 } 949 950 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 951 952 return false; 953 } 954 955 /** 956 * Drop fulltext index 957 * 958 * @return string|bool error string is returned incase of errors otherwise false 959 */ 960 public function delete_index($acp_module, $u_action) 961 { 962 // Make sure we can actually use PostgreSQL with fulltext indexes 963 if ($error = $this->init()) 964 { 965 return $error; 966 } 967 968 if (empty($this->stats)) 969 { 970 $this->get_stats(); 971 } 972 973 if (isset($this->stats['post_subject'])) 974 { 975 $this->db->sql_query('DROP INDEX ' . $this->stats['post_subject']['relname']); 976 } 977 978 if (isset($this->stats['post_content'])) 979 { 980 $this->db->sql_query('DROP INDEX ' . $this->stats['post_content']['relname']); 981 } 982 983 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE); 984 985 return false; 986 } 987 988 /** 989 * Returns true if both FULLTEXT indexes exist 990 */ 991 public function index_created() 992 { 993 if (empty($this->stats)) 994 { 995 $this->get_stats(); 996 } 997 998 return (isset($this->stats['post_subject']) && isset($this->stats['post_content'])) ? true : false; 999 } 1000 1001 /** 1002 * Returns an associative array containing information about the indexes 1003 */ 1004 public function index_stats() 1005 { 1006 if (empty($this->stats)) 1007 { 1008 $this->get_stats(); 1009 } 1010 1011 return array( 1012 $this->user->lang['FULLTEXT_POSTGRES_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0, 1013 ); 1014 } 1015 1016 /** 1017 * Computes the stats and store them in the $this->stats associative array 1018 */ 1019 protected function get_stats() 1020 { 1021 if ($this->db->get_sql_layer() != 'postgres') 1022 { 1023 $this->stats = array(); 1024 return; 1025 } 1026 1027 $sql = "SELECT c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef 1028 FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2 1029 WHERE c1.relname = '" . POSTS_TABLE . "' 1030 AND pg_catalog.pg_table_is_visible(c1.oid) 1031 AND c1.oid = i.indrelid 1032 AND i.indexrelid = c2.oid"; 1033 $result = $this->db->sql_query($sql); 1034 1035 while ($row = $this->db->sql_fetchrow($result)) 1036 { 1037 // deal with older PostgreSQL versions which didn't use Index_type 1038 if (strpos($row['indexdef'], 'to_tsvector') !== false) 1039 { 1040 if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_subject' || $row['relname'] == POSTS_TABLE . '_post_subject') 1041 { 1042 $this->stats['post_subject'] = $row; 1043 } 1044 else if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_content' || $row['relname'] == POSTS_TABLE . '_post_content') 1045 { 1046 $this->stats['post_content'] = $row; 1047 } 1048 } 1049 } 1050 $this->db->sql_freeresult($result); 1051 1052 $this->stats['total_posts'] = $this->config['num_posts']; 1053 } 1054 1055 /** 1056 * Display various options that can be configured for the backend from the acp 1057 * 1058 * @return associative array containing template and config variables 1059 */ 1060 public function acp() 1061 { 1062 $tpl = ' 1063 <dl> 1064 <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK_EXPLAIN'] . '</span></dt> 1065 <dd>' . (($this->db->get_sql_layer() == 'postgres') ? $this->user->lang['YES'] : $this->user->lang['NO']) . '</dd> 1066 </dl> 1067 <dl> 1068 <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME_EXPLAIN'] . '</span></dt> 1069 <dd><select name="config[fulltext_postgres_ts_name]">'; 1070 1071 if ($this->db->get_sql_layer() == 'postgres') 1072 { 1073 $sql = 'SELECT cfgname AS ts_name 1074 FROM pg_ts_config'; 1075 $result = $this->db->sql_query($sql); 1076 1077 while ($row = $this->db->sql_fetchrow($result)) 1078 { 1079 $tpl .= '<option value="' . $row['ts_name'] . '"' . ($row['ts_name'] === $this->config['fulltext_postgres_ts_name'] ? ' selected="selected"' : '') . '>' . $row['ts_name'] . '</option>'; 1080 } 1081 $this->db->sql_freeresult($result); 1082 } 1083 else 1084 { 1085 $tpl .= '<option value="' . $this->config['fulltext_postgres_ts_name'] . '" selected="selected">' . $this->config['fulltext_postgres_ts_name'] . '</option>'; 1086 } 1087 1088 $tpl .= '</select></dd> 1089 </dl> 1090 <dl> 1091 <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> 1092 <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> 1093 </dl> 1094 <dl> 1095 <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> 1096 <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> 1097 </dl> 1098 '; 1099 1100 // These are fields required in the config table 1101 return array( 1102 'tpl' => $tpl, 1103 'config' => array('fulltext_postgres_ts_name' => 'string', 'fulltext_postgres_min_word_len' => 'integer:0:255', 'fulltext_postgres_max_word_len' => 'integer:0:255') 1104 ); 1105 } 1106 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Jan 11 00:25:41 2018 | Cross-referenced by PHPXref 0.7.1 |