[ Index ]

PHP Cross Reference of phpBB-3.2.8-deutsch

title

Body

[close]

/phpbb/search/ -> fulltext_postgres.php (source)

   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 || ' ' || post_subject))";
 976          }
 977  
 978          $stats = $this->stats;
 979  
 980          /**
 981          * Event to modify SQL queries before the Postgres search index is created
 982          *
 983          * @event core.search_postgres_create_index_before
 984          * @var array    sql_queries            Array with queries for creating the search index
 985          * @var array    stats                Array with statistics of the current index (read only)
 986          * @since 3.2.3-RC1
 987          */
 988          $vars = array(
 989              'sql_queries',
 990              'stats',
 991          );
 992          extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_create_index_before', compact($vars)));
 993  
 994          foreach ($sql_queries as $sql_query)
 995          {
 996              $this->db->sql_query($sql_query);
 997          }
 998  
 999          $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1000  
1001          return false;
1002      }
1003  
1004      /**
1005      * Drop fulltext index
1006      *
1007      * @return string|bool error string is returned incase of errors otherwise false
1008      */
1009  	public function delete_index($acp_module, $u_action)
1010      {
1011          // Make sure we can actually use PostgreSQL with fulltext indexes
1012          if ($error = $this->init())
1013          {
1014              return $error;
1015          }
1016  
1017          if (empty($this->stats))
1018          {
1019              $this->get_stats();
1020          }
1021  
1022          $sql_queries = [];
1023  
1024          if (isset($this->stats['post_subject']))
1025          {
1026              $sql_queries[] = 'DROP INDEX ' . $this->stats['post_subject']['relname'];
1027          }
1028  
1029          if (isset($this->stats['post_content']))
1030          {
1031              $sql_queries[] = 'DROP INDEX ' . $this->stats['post_content']['relname'];
1032          }
1033  
1034          $stats = $this->stats;
1035  
1036          /**
1037          * Event to modify SQL queries before the Postgres search index is created
1038          *
1039          * @event core.search_postgres_delete_index_before
1040          * @var array    sql_queries            Array with queries for deleting the search index
1041          * @var array    stats                Array with statistics of the current index (read only)
1042          * @since 3.2.3-RC1
1043          */
1044          $vars = array(
1045              'sql_queries',
1046              'stats',
1047          );
1048          extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_delete_index_before', compact($vars)));
1049  
1050          foreach ($sql_queries as $sql_query)
1051          {
1052              $this->db->sql_query($sql_query);
1053          }
1054  
1055          $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
1056  
1057          return false;
1058      }
1059  
1060      /**
1061      * Returns true if both FULLTEXT indexes exist
1062      */
1063  	public function index_created()
1064      {
1065          if (empty($this->stats))
1066          {
1067              $this->get_stats();
1068          }
1069  
1070          return (isset($this->stats['post_subject']) && isset($this->stats['post_content'])) ? true : false;
1071      }
1072  
1073      /**
1074      * Returns an associative array containing information about the indexes
1075      */
1076  	public function index_stats()
1077      {
1078          if (empty($this->stats))
1079          {
1080              $this->get_stats();
1081          }
1082  
1083          return array(
1084              $this->user->lang['FULLTEXT_POSTGRES_TOTAL_POSTS']            => ($this->index_created()) ? $this->stats['total_posts'] : 0,
1085          );
1086      }
1087  
1088      /**
1089       * Computes the stats and store them in the $this->stats associative array
1090       */
1091  	protected function get_stats()
1092      {
1093          if ($this->db->get_sql_layer() != 'postgres')
1094          {
1095              $this->stats = array();
1096              return;
1097          }
1098  
1099          $sql = "SELECT c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef
1100                FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2
1101               WHERE c1.relname = '" . POSTS_TABLE . "'
1102                 AND pg_catalog.pg_table_is_visible(c1.oid)
1103                 AND c1.oid = i.indrelid
1104                 AND i.indexrelid = c2.oid";
1105          $result = $this->db->sql_query($sql);
1106  
1107          while ($row = $this->db->sql_fetchrow($result))
1108          {
1109              // deal with older PostgreSQL versions which didn't use Index_type
1110              if (strpos($row['indexdef'], 'to_tsvector') !== false)
1111              {
1112                  if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_subject' || $row['relname'] == POSTS_TABLE . '_post_subject')
1113                  {
1114                      $this->stats['post_subject'] = $row;
1115                  }
1116                  else if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_content' || $row['relname'] == POSTS_TABLE . '_post_content')
1117                  {
1118                      $this->stats['post_content'] = $row;
1119                  }
1120              }
1121          }
1122          $this->db->sql_freeresult($result);
1123  
1124          $this->stats['total_posts'] = $this->config['num_posts'];
1125      }
1126  
1127      /**
1128      * Display various options that can be configured for the backend from the acp
1129      *
1130      * @return associative array containing template and config variables
1131      */
1132  	public function acp()
1133      {
1134          $tpl = '
1135          <dl>
1136              <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK_EXPLAIN'] . '</span></dt>
1137              <dd>' . (($this->db->get_sql_layer() == 'postgres') ? $this->user->lang['YES'] : $this->user->lang['NO']) . '</dd>
1138          </dl>
1139          <dl>
1140              <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME_EXPLAIN'] . '</span></dt>
1141              <dd><select name="config[fulltext_postgres_ts_name]">';
1142  
1143          if ($this->db->get_sql_layer() == 'postgres')
1144          {
1145              $sql = 'SELECT cfgname AS ts_name
1146                    FROM pg_ts_config';
1147              $result = $this->db->sql_query($sql);
1148  
1149              while ($row = $this->db->sql_fetchrow($result))
1150              {
1151                  $tpl .= '<option value="' . $row['ts_name'] . '"' . ($row['ts_name'] === $this->config['fulltext_postgres_ts_name'] ? ' selected="selected"' : '') . '>' . $row['ts_name'] . '</option>';
1152              }
1153              $this->db->sql_freeresult($result);
1154          }
1155          else
1156          {
1157              $tpl .= '<option value="' . $this->config['fulltext_postgres_ts_name'] . '" selected="selected">' . $this->config['fulltext_postgres_ts_name'] . '</option>';
1158          }
1159  
1160          $tpl .= '</select></dd>
1161          </dl>
1162                  <dl>
1163                          <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>
1164                          <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>
1165                  </dl>
1166                  <dl>
1167                          <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>
1168                          <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>
1169                  </dl>
1170          ';
1171  
1172          // These are fields required in the config table
1173          return array(
1174              'tpl'        => $tpl,
1175              'config'    => array('fulltext_postgres_ts_name' => 'string', 'fulltext_postgres_min_word_len' => 'integer:0:255', 'fulltext_postgres_max_word_len' => 'integer:0:255')
1176          );
1177      }
1178  }


Generated: Sat Oct 26 11:28:42 2019 Cross-referenced by PHPXref 0.7.1