Verzeichnisstruktur phpBB-3.2.0
- Veröffentlicht
- 06.01.2017
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
fulltext_postgres.php
0001 <?php
0002 /**
0003 *
0004 * This file is part of the phpBB Forum Software package.
0005 *
0006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
0007 * @license GNU General Public License, version 2 (GPL-2.0)
0008 *
0009 * For full copyright and license information, please see
0010 * the docs/CREDITS.txt file.
0011 *
0012 */
0013
0014 namespace phpbb\search;
0015
0016 /**
0017 * Fulltext search for PostgreSQL
0018 */
0019 class fulltext_postgres extends \phpbb\search\base
0020 {
0021 /**
0022 * Associative array holding index stats
0023 * @var array
0024 */
0025 protected $stats = array();
0026
0027 /**
0028 * Holds the words entered by user, obtained by splitting the entered query on whitespace
0029 * @var array
0030 */
0031 protected $split_words = array();
0032
0033 /**
0034 * Stores the tsearch query
0035 * @var string
0036 */
0037 protected $tsearch_query;
0038
0039 /**
0040 * True if phrase search is supported.
0041 * PostgreSQL fulltext currently doesn't support it
0042 * @var boolean
0043 */
0044 protected $phrase_search = false;
0045
0046 /**
0047 * Config object
0048 * @var \phpbb\config\config
0049 */
0050 protected $config;
0051
0052 /**
0053 * Database connection
0054 * @var \phpbb\db\driver\driver_interface
0055 */
0056 protected $db;
0057
0058 /**
0059 * phpBB event dispatcher object
0060 * @var \phpbb\event\dispatcher_interface
0061 */
0062 protected $phpbb_dispatcher;
0063
0064 /**
0065 * User object
0066 * @var \phpbb\user
0067 */
0068 protected $user;
0069
0070 /**
0071 * Contains tidied search query.
0072 * Operators are prefixed in search query and common words excluded
0073 * @var string
0074 */
0075 protected $search_query;
0076
0077 /**
0078 * Contains common words.
0079 * Common words are words with length less/more than min/max length
0080 * @var array
0081 */
0082 protected $common_words = array();
0083
0084 /**
0085 * Associative array stores the min and max word length to be searched
0086 * @var array
0087 */
0088 protected $word_length = array();
0089
0090 /**
0091 * Constructor
0092 * Creates a new \phpbb\search\fulltext_postgres, which is used as a search backend
0093 *
0094 * @param string|bool $error Any error that occurs is passed on through this reference variable otherwise false
0095 * @param string $phpbb_root_path Relative path to phpBB root
0096 * @param string $phpEx PHP file extension
0097 * @param \phpbb\auth\auth $auth Auth object
0098 * @param \phpbb\config\config $config Config object
0099 * @param \phpbb\db\driver\driver_interface Database object
0100 * @param \phpbb\user $user User object
0101 * @param \phpbb\event\dispatcher_interface $phpbb_dispatcher Event dispatcher object
0102 */
0103 public function __construct(&$error, $phpbb_root_path, $phpEx, $auth, $config, $db, $user, $phpbb_dispatcher)
0104 {
0105 $this->config = $config;
0106 $this->db = $db;
0107 $this->phpbb_dispatcher = $phpbb_dispatcher;
0108 $this->user = $user;
0109
0110 $this->word_length = array('min' => $this->config['fulltext_postgres_min_word_len'], 'max' => $this->config['fulltext_postgres_max_word_len']);
0111
0112 /**
0113 * Load the UTF tools
0114 */
0115 if (!function_exists('utf8_strlen'))
0116 {
0117 include($phpbb_root_path . 'includes/utf/utf_tools.' . $phpEx);
0118 }
0119
0120 $error = false;
0121 }
0122
0123 /**
0124 * Returns the name of this search backend to be displayed to administrators
0125 *
0126 * @return string Name
0127 */
0128 public function get_name()
0129 {
0130 return 'PostgreSQL Fulltext';
0131 }
0132
0133 /**
0134 * Returns the search_query
0135 *
0136 * @return string search query
0137 */
0138 public function get_search_query()
0139 {
0140 return $this->search_query;
0141 }
0142
0143 /**
0144 * Returns the common_words array
0145 *
0146 * @return array common words that are ignored by search backend
0147 */
0148 public function get_common_words()
0149 {
0150 return $this->common_words;
0151 }
0152
0153 /**
0154 * Returns the word_length array
0155 *
0156 * @return array min and max word length for searching
0157 */
0158 public function get_word_length()
0159 {
0160 return $this->word_length;
0161 }
0162
0163 /**
0164 * Returns if phrase search is supported or not
0165 *
0166 * @return bool
0167 */
0168 public function supports_phrase_search()
0169 {
0170 return $this->phrase_search;
0171 }
0172
0173 /**
0174 * Checks for correct PostgreSQL version and stores min/max word length in the config
0175 *
0176 * @return string|bool Language key of the error/incompatiblity occurred
0177 */
0178 public function init()
0179 {
0180 if ($this->db->get_sql_layer() != 'postgres')
0181 {
0182 return $this->user->lang['FULLTEXT_POSTGRES_INCOMPATIBLE_DATABASE'];
0183 }
0184
0185 return false;
0186 }
0187
0188 /**
0189 * Splits keywords entered by a user into an array of words stored in $this->split_words
0190 * Stores the tidied search query in $this->search_query
0191 *
0192 * @param string &$keywords Contains the keyword as entered by the user
0193 * @param string $terms is either 'all' or 'any'
0194 * @return bool false if no valid keywords were found and otherwise true
0195 */
0196 public function split_keywords(&$keywords, $terms)
0197 {
0198 if ($terms == 'all')
0199 {
0200 $match = array('#\sand\s#iu', '#\sor\s#iu', '#\snot\s#iu', '#(^|\s)\+#', '#(^|\s)-#', '#(^|\s)\|#');
0201 $replace = array(' +', ' |', ' -', ' +', ' -', ' |');
0202
0203 $keywords = preg_replace($match, $replace, $keywords);
0204 }
0205
0206 // Filter out as above
0207 $split_keywords = preg_replace("#[\"\n\r\t]+#", ' ', trim(htmlspecialchars_decode($keywords)));
0208
0209 // Split words
0210 $split_keywords = preg_replace('#([^\p{L}\p{N}\'*"()])#u', '$1$1', str_replace('\'\'', '\' \'', trim($split_keywords)));
0211 $matches = array();
0212 preg_match_all('#(?:[^\p{L}\p{N}*"()]|^)([+\-|]?(?:[\p{L}\p{N}*"()]+\'?)*[\p{L}\p{N}*"()])(?:[^\p{L}\p{N}*"()]|$)#u', $split_keywords, $matches);
0213 $this->split_words = $matches[1];
0214
0215 foreach ($this->split_words as $i => $word)
0216 {
0217 $clean_word = preg_replace('#^[+\-|"]#', '', $word);
0218
0219 // check word length
0220 $clean_len = utf8_strlen(str_replace('*', '', $clean_word));
0221 if (($clean_len < $this->config['fulltext_postgres_min_word_len']) || ($clean_len > $this->config['fulltext_postgres_max_word_len']))
0222 {
0223 $this->common_words[] = $word;
0224 unset($this->split_words[$i]);
0225 }
0226 }
0227
0228 if ($terms == 'any')
0229 {
0230 $this->search_query = '';
0231 $this->tsearch_query = '';
0232 foreach ($this->split_words as $word)
0233 {
0234 if ((strpos($word, '+') === 0) || (strpos($word, '-') === 0) || (strpos($word, '|') === 0))
0235 {
0236 $word = substr($word, 1);
0237 }
0238 $this->search_query .= $word . ' ';
0239 $this->tsearch_query .= '|' . $word . ' ';
0240 }
0241 }
0242 else
0243 {
0244 $this->search_query = '';
0245 $this->tsearch_query = '';
0246 foreach ($this->split_words as $word)
0247 {
0248 if (strpos($word, '+') === 0)
0249 {
0250 $this->search_query .= $word . ' ';
0251 $this->tsearch_query .= '&' . substr($word, 1) . ' ';
0252 }
0253 else if (strpos($word, '-') === 0)
0254 {
0255 $this->search_query .= $word . ' ';
0256 $this->tsearch_query .= '&!' . substr($word, 1) . ' ';
0257 }
0258 else if (strpos($word, '|') === 0)
0259 {
0260 $this->search_query .= $word . ' ';
0261 $this->tsearch_query .= '|' . substr($word, 1) . ' ';
0262 }
0263 else
0264 {
0265 $this->search_query .= '+' . $word . ' ';
0266 $this->tsearch_query .= '&' . $word . ' ';
0267 }
0268 }
0269 }
0270
0271 $this->tsearch_query = substr($this->tsearch_query, 1);
0272 $this->search_query = utf8_htmlspecialchars($this->search_query);
0273
0274 if ($this->search_query)
0275 {
0276 $this->split_words = array_values($this->split_words);
0277 sort($this->split_words);
0278 return true;
0279 }
0280 return false;
0281 }
0282
0283 /**
0284 * Turns text into an array of words
0285 * @param string $text contains post text/subject
0286 */
0287 public function split_message($text)
0288 {
0289 // Split words
0290 $text = preg_replace('#([^\p{L}\p{N}\'*])#u', '$1$1', str_replace('\'\'', '\' \'', trim($text)));
0291 $matches = array();
0292 preg_match_all('#(?:[^\p{L}\p{N}*]|^)([+\-|]?(?:[\p{L}\p{N}*]+\'?)*[\p{L}\p{N}*])(?:[^\p{L}\p{N}*]|$)#u', $text, $matches);
0293 $text = $matches[1];
0294
0295 // remove too short or too long words
0296 $text = array_values($text);
0297 for ($i = 0, $n = sizeof($text); $i < $n; $i++)
0298 {
0299 $text[$i] = trim($text[$i]);
0300 if (utf8_strlen($text[$i]) < $this->config['fulltext_postgres_min_word_len'] || utf8_strlen($text[$i]) > $this->config['fulltext_postgres_max_word_len'])
0301 {
0302 unset($text[$i]);
0303 }
0304 }
0305
0306 return array_values($text);
0307 }
0308
0309 /**
0310 * Performs a search on keywords depending on display specific params. You have to run split_keywords() first
0311 *
0312 * @param string $type contains either posts or topics depending on what should be searched for
0313 * @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)
0314 * @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)
0315 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0316 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0317 * @param string $sort_dir is either a or d representing ASC and DESC
0318 * @param string $sort_days specifies the maximum amount of days a post may be old
0319 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0320 * @param string $post_visibility specifies which types of posts the user can view in which forums
0321 * @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
0322 * @param array $author_ary an array of author ids if the author should be ignored during the search the array is empty
0323 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0324 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0325 * @param int $start indicates the first index of the page
0326 * @param int $per_page number of ids each page is supposed to contain
0327 * @return boolean|int total number of results
0328 */
0329 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)
0330 {
0331 // No keywords? No posts
0332 if (!$this->search_query)
0333 {
0334 return false;
0335 }
0336
0337 // When search query contains queries like -foo
0338 if (strpos($this->search_query, '+') === false)
0339 {
0340 return false;
0341 }
0342
0343 // generate a search_key from all the options to identify the results
0344 $search_key_array = array(
0345 implode(', ', $this->split_words),
0346 $type,
0347 $fields,
0348 $terms,
0349 $sort_days,
0350 $sort_key,
0351 $topic_id,
0352 implode(',', $ex_fid_ary),
0353 $post_visibility,
0354 implode(',', $author_ary)
0355 );
0356
0357 /**
0358 * Allow changing the search_key for cached results
0359 *
0360 * @event core.search_postgres_by_keyword_modify_search_key
0361 * @var array search_key_array Array with search parameters to generate the search_key
0362 * @var string type Searching type ('posts', 'topics')
0363 * @var string fields Searching fields ('titleonly', 'msgonly', 'firstpost', 'all')
0364 * @var string terms Searching terms ('all', 'any')
0365 * @var int sort_days Time, in days, of the oldest possible post to list
0366 * @var string sort_key The sort type used from the possible sort types
0367 * @var int topic_id Limit the search to this topic_id only
0368 * @var array ex_fid_ary Which forums not to search on
0369 * @var string post_visibility Post visibility data
0370 * @var array author_ary Array of user_id containing the users to filter the results to
0371 * @since 3.1.7-RC1
0372 */
0373 $vars = array(
0374 'search_key_array',
0375 'type',
0376 'fields',
0377 'terms',
0378 'sort_days',
0379 'sort_key',
0380 'topic_id',
0381 'ex_fid_ary',
0382 'post_visibility',
0383 'author_ary',
0384 );
0385 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_keyword_modify_search_key', compact($vars)));
0386
0387 $search_key = md5(implode('#', $search_key_array));
0388
0389 if ($start < 0)
0390 {
0391 $start = 0;
0392 }
0393
0394 // try reading the results from cache
0395 $result_count = 0;
0396 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0397 {
0398 return $result_count;
0399 }
0400
0401 $id_ary = array();
0402
0403 $join_topic = ($type == 'posts') ? false : true;
0404
0405 // Build sql strings for sorting
0406 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0407 $sql_sort_table = $sql_sort_join = '';
0408
0409 switch ($sql_sort[0])
0410 {
0411 case 'u':
0412 $sql_sort_table = USERS_TABLE . ' u, ';
0413 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0414 break;
0415
0416 case 't':
0417 $join_topic = true;
0418 break;
0419
0420 case 'f':
0421 $sql_sort_table = FORUMS_TABLE . ' f, ';
0422 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0423 break;
0424 }
0425
0426 // Build some display specific sql strings
0427 switch ($fields)
0428 {
0429 case 'titleonly':
0430 $sql_match = 'p.post_subject';
0431 $sql_match_where = ' AND p.post_id = t.topic_first_post_id';
0432 $join_topic = true;
0433 break;
0434
0435 case 'msgonly':
0436 $sql_match = 'p.post_text';
0437 $sql_match_where = '';
0438 break;
0439
0440 case 'firstpost':
0441 $sql_match = 'p.post_subject, p.post_text';
0442 $sql_match_where = ' AND p.post_id = t.topic_first_post_id';
0443 $join_topic = true;
0444 break;
0445
0446 default:
0447 $sql_match = 'p.post_subject, p.post_text';
0448 $sql_match_where = '';
0449 break;
0450 }
0451
0452 $tsearch_query = $this->tsearch_query;
0453
0454 /**
0455 * Allow changing the query used to search for posts using fulltext_postgres
0456 *
0457 * @event core.search_postgres_keywords_main_query_before
0458 * @var string tsearch_query The parsed keywords used for this search
0459 * @var int result_count The previous result count for the format of the query.
0460 * Set to 0 to force a re-count
0461 * @var bool join_topic Weather or not TOPICS_TABLE should be CROSS JOIN'ED
0462 * @var array author_ary Array of user_id containing the users to filter the results to
0463 * @var string author_name An extra username to search on (!empty(author_ary) must be true, to be relevant)
0464 * @var array ex_fid_ary Which forums not to search on
0465 * @var int topic_id Limit the search to this topic_id only
0466 * @var string sql_sort_table Extra tables to include in the SQL query.
0467 * Used in conjunction with sql_sort_join
0468 * @var string sql_sort_join SQL conditions to join all the tables used together.
0469 * Used in conjunction with sql_sort_table
0470 * @var int sort_days Time, in days, of the oldest possible post to list
0471 * @var string sql_match Which columns to do the search on.
0472 * @var string sql_match_where Extra conditions to use to properly filter the matching process
0473 * @var string sort_by_sql The possible predefined sort types
0474 * @var string sort_key The sort type used from the possible sort types
0475 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0476 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0477 * @var int start How many posts to skip in the search results (used for pagination)
0478 * @since 3.1.5-RC1
0479 */
0480 $vars = array(
0481 'tsearch_query',
0482 'result_count',
0483 'join_topic',
0484 'author_ary',
0485 'author_name',
0486 'ex_fid_ary',
0487 'topic_id',
0488 'sql_sort_table',
0489 'sql_sort_join',
0490 'sort_days',
0491 'sql_match',
0492 'sql_match_where',
0493 'sort_by_sql',
0494 'sort_key',
0495 'sort_dir',
0496 'sql_sort',
0497 'start',
0498 );
0499 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_keywords_main_query_before', compact($vars)));
0500
0501 $sql_select = ($type == 'posts') ? 'p.post_id' : 'DISTINCT t.topic_id';
0502 $sql_from = ($join_topic) ? TOPICS_TABLE . ' t, ' : '';
0503 $field = ($type == 'posts') ? 'post_id' : 'topic_id';
0504
0505 if (sizeof($author_ary) && $author_name)
0506 {
0507 // first one matches post of registered users, second one guests and deleted users
0508 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
0509 }
0510 else if (sizeof($author_ary))
0511 {
0512 $sql_author = ' AND ' . $this->db->sql_in_set('p.poster_id', $author_ary);
0513 }
0514 else
0515 {
0516 $sql_author = '';
0517 }
0518
0519 $sql_where_options = $sql_sort_join;
0520 $sql_where_options .= ($topic_id) ? ' AND p.topic_id = ' . $topic_id : '';
0521 $sql_where_options .= ($join_topic) ? ' AND t.topic_id = p.topic_id' : '';
0522 $sql_where_options .= (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0523 $sql_where_options .= ' AND ' . $post_visibility;
0524 $sql_where_options .= $sql_author;
0525 $sql_where_options .= ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0526 $sql_where_options .= $sql_match_where;
0527
0528 $sql_match = str_replace(',', " || ' ' ||", $sql_match);
0529 $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) . "')";
0530
0531 $this->db->sql_transaction('begin');
0532
0533 $sql_from = "FROM $sql_from$sql_sort_table" . POSTS_TABLE . " p";
0534 $sql_where = "WHERE (" . $tmp_sql_match . ")
0535 $sql_where_options";
0536 $sql = "SELECT $sql_select
0537 $sql_from
0538 $sql_where
0539 ORDER BY $sql_sort";
0540 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0541
0542 while ($row = $this->db->sql_fetchrow($result))
0543 {
0544 $id_ary[] = $row[$field];
0545 }
0546 $this->db->sql_freeresult($result);
0547
0548 $id_ary = array_unique($id_ary);
0549
0550 // if the total result count is not cached yet, retrieve it from the db
0551 if (!$result_count)
0552 {
0553 $sql_count = "SELECT COUNT(*) as result_count
0554 $sql_from
0555 $sql_where";
0556 $result = $this->db->sql_query($sql_count);
0557 $result_count = (int) $this->db->sql_fetchfield('result_count');
0558 $this->db->sql_freeresult($result);
0559
0560 if (!$result_count)
0561 {
0562 return false;
0563 }
0564 }
0565
0566 $this->db->sql_transaction('commit');
0567
0568 if ($start >= $result_count)
0569 {
0570 $start = floor(($result_count - 1) / $per_page) * $per_page;
0571
0572 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0573
0574 while ($row = $this->db->sql_fetchrow($result))
0575 {
0576 $id_ary[] = $row[$field];
0577 }
0578 $this->db->sql_freeresult($result);
0579
0580 $id_ary = array_unique($id_ary);
0581 }
0582
0583 // store the ids, from start on then delete anything that isn't on the current page because we only need ids for one page
0584 $this->save_ids($search_key, implode(' ', $this->split_words), $author_ary, $result_count, $id_ary, $start, $sort_dir);
0585 $id_ary = array_slice($id_ary, 0, (int) $per_page);
0586
0587 return $result_count;
0588 }
0589
0590 /**
0591 * Performs a search on an author's posts without caring about message contents. Depends on display specific params
0592 *
0593 * @param string $type contains either posts or topics depending on what should be searched for
0594 * @param boolean $firstpost_only if true, only topic starting posts will be considered
0595 * @param array $sort_by_sql contains SQL code for the ORDER BY part of a query
0596 * @param string $sort_key is the key of $sort_by_sql for the selected sorting
0597 * @param string $sort_dir is either a or d representing ASC and DESC
0598 * @param string $sort_days specifies the maximum amount of days a post may be old
0599 * @param array $ex_fid_ary specifies an array of forum ids which should not be searched
0600 * @param string $post_visibility specifies which types of posts the user can view in which forums
0601 * @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
0602 * @param array $author_ary an array of author ids
0603 * @param string $author_name specifies the author match, when ANONYMOUS is also a search-match
0604 * @param array &$id_ary passed by reference, to be filled with ids for the page specified by $start and $per_page, should be ordered
0605 * @param int $start indicates the first index of the page
0606 * @param int $per_page number of ids each page is supposed to contain
0607 * @return boolean|int total number of results
0608 */
0609 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)
0610 {
0611 // No author? No posts
0612 if (!sizeof($author_ary))
0613 {
0614 return 0;
0615 }
0616
0617 // generate a search_key from all the options to identify the results
0618 $search_key_array = array(
0619 '',
0620 $type,
0621 ($firstpost_only) ? 'firstpost' : '',
0622 '',
0623 '',
0624 $sort_days,
0625 $sort_key,
0626 $topic_id,
0627 implode(',', $ex_fid_ary),
0628 $post_visibility,
0629 implode(',', $author_ary),
0630 $author_name,
0631 );
0632
0633 /**
0634 * Allow changing the search_key for cached results
0635 *
0636 * @event core.search_postgres_by_author_modify_search_key
0637 * @var array search_key_array Array with search parameters to generate the search_key
0638 * @var string type Searching type ('posts', 'topics')
0639 * @var boolean firstpost_only Flag indicating if only topic starting posts are considered
0640 * @var int sort_days Time, in days, of the oldest possible post to list
0641 * @var string sort_key The sort type used from the possible sort types
0642 * @var int topic_id Limit the search to this topic_id only
0643 * @var array ex_fid_ary Which forums not to search on
0644 * @var string post_visibility Post visibility data
0645 * @var array author_ary Array of user_id containing the users to filter the results to
0646 * @var string author_name The username to search on
0647 * @since 3.1.7-RC1
0648 */
0649 $vars = array(
0650 'search_key_array',
0651 'type',
0652 'firstpost_only',
0653 'sort_days',
0654 'sort_key',
0655 'topic_id',
0656 'ex_fid_ary',
0657 'post_visibility',
0658 'author_ary',
0659 'author_name',
0660 );
0661 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_by_author_modify_search_key', compact($vars)));
0662
0663 $search_key = md5(implode('#', $search_key_array));
0664
0665 if ($start < 0)
0666 {
0667 $start = 0;
0668 }
0669
0670 // try reading the results from cache
0671 $result_count = 0;
0672 if ($this->obtain_ids($search_key, $result_count, $id_ary, $start, $per_page, $sort_dir) == SEARCH_RESULT_IN_CACHE)
0673 {
0674 return $result_count;
0675 }
0676
0677 $id_ary = array();
0678
0679 // Create some display specific sql strings
0680 if ($author_name)
0681 {
0682 // first one matches post of registered users, second one guests and deleted users
0683 $sql_author = '(' . $this->db->sql_in_set('p.poster_id', array_diff($author_ary, array(ANONYMOUS)), false, true) . ' OR p.post_username ' . $author_name . ')';
0684 }
0685 else
0686 {
0687 $sql_author = $this->db->sql_in_set('p.poster_id', $author_ary);
0688 }
0689 $sql_fora = (sizeof($ex_fid_ary)) ? ' AND ' . $this->db->sql_in_set('p.forum_id', $ex_fid_ary, true) : '';
0690 $sql_topic_id = ($topic_id) ? ' AND p.topic_id = ' . (int) $topic_id : '';
0691 $sql_time = ($sort_days) ? ' AND p.post_time >= ' . (time() - ($sort_days * 86400)) : '';
0692 $sql_firstpost = ($firstpost_only) ? ' AND p.post_id = t.topic_first_post_id' : '';
0693
0694 // Build sql strings for sorting
0695 $sql_sort = $sort_by_sql[$sort_key] . (($sort_dir == 'a') ? ' ASC' : ' DESC');
0696 $sql_sort_table = $sql_sort_join = '';
0697 switch ($sql_sort[0])
0698 {
0699 case 'u':
0700 $sql_sort_table = USERS_TABLE . ' u, ';
0701 $sql_sort_join = ($type == 'posts') ? ' AND u.user_id = p.poster_id ' : ' AND u.user_id = t.topic_poster ';
0702 break;
0703
0704 case 't':
0705 $sql_sort_table = ($type == 'posts' && !$firstpost_only) ? TOPICS_TABLE . ' t, ' : '';
0706 $sql_sort_join = ($type == 'posts' && !$firstpost_only) ? ' AND t.topic_id = p.topic_id ' : '';
0707 break;
0708
0709 case 'f':
0710 $sql_sort_table = FORUMS_TABLE . ' f, ';
0711 $sql_sort_join = ' AND f.forum_id = p.forum_id ';
0712 break;
0713 }
0714
0715 $m_approve_fid_sql = ' AND ' . $post_visibility;
0716
0717 /**
0718 * Allow changing the query used to search for posts by author in fulltext_postgres
0719 *
0720 * @event core.search_postgres_author_count_query_before
0721 * @var int result_count The previous result count for the format of the query.
0722 * Set to 0 to force a re-count
0723 * @var string sql_sort_table CROSS JOIN'ed table to allow doing the sort chosen
0724 * @var string sql_sort_join Condition to define how to join the CROSS JOIN'ed table specifyed in sql_sort_table
0725 * @var array author_ary Array of user_id containing the users to filter the results to
0726 * @var string author_name An extra username to search on
0727 * @var string sql_author SQL WHERE condition for the post author ids
0728 * @var int topic_id Limit the search to this topic_id only
0729 * @var string sql_topic_id SQL of topic_id
0730 * @var string sort_by_sql The possible predefined sort types
0731 * @var string sort_key The sort type used from the possible sort types
0732 * @var string sort_dir "a" for ASC or "d" dor DESC for the sort order used
0733 * @var string sql_sort The result SQL when processing sort_by_sql + sort_key + sort_dir
0734 * @var string sort_days Time, in days, that the oldest post showing can have
0735 * @var string sql_time The SQL to search on the time specifyed by sort_days
0736 * @var bool firstpost_only Wether or not to search only on the first post of the topics
0737 * @var array ex_fid_ary Forum ids that must not be searched on
0738 * @var array sql_fora SQL query for ex_fid_ary
0739 * @var string m_approve_fid_sql WHERE clause condition on post_visibility restrictions
0740 * @var int start How many posts to skip in the search results (used for pagination)
0741 * @since 3.1.5-RC1
0742 */
0743 $vars = array(
0744 'result_count',
0745 'sql_sort_table',
0746 'sql_sort_join',
0747 'author_ary',
0748 'author_name',
0749 'sql_author',
0750 'topic_id',
0751 'sql_topic_id',
0752 'sort_by_sql',
0753 'sort_key',
0754 'sort_dir',
0755 'sql_sort',
0756 'sort_days',
0757 'sql_time',
0758 'firstpost_only',
0759 'ex_fid_ary',
0760 'sql_fora',
0761 'm_approve_fid_sql',
0762 'start',
0763 );
0764 extract($this->phpbb_dispatcher->trigger_event('core.search_postgres_author_count_query_before', compact($vars)));
0765
0766 // Build the query for really selecting the post_ids
0767 if ($type == 'posts')
0768 {
0769 $sql = "SELECT p.post_id
0770 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "
0771 WHERE $sql_author
0772 $sql_topic_id
0773 $sql_firstpost
0774 $m_approve_fid_sql
0775 $sql_fora
0776 $sql_sort_join
0777 $sql_time
0778 ORDER BY $sql_sort";
0779 $field = 'post_id';
0780 }
0781 else
0782 {
0783 $sql = "SELECT t.topic_id
0784 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
0785 WHERE $sql_author
0786 $sql_topic_id
0787 $sql_firstpost
0788 $m_approve_fid_sql
0789 $sql_fora
0790 AND t.topic_id = p.topic_id
0791 $sql_sort_join
0792 $sql_time
0793 GROUP BY t.topic_id, $sort_by_sql[$sort_key]
0794 ORDER BY $sql_sort";
0795 $field = 'topic_id';
0796 }
0797
0798 $this->db->sql_transaction('begin');
0799
0800 // Only read one block of posts from the db and then cache it
0801 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0802
0803 while ($row = $this->db->sql_fetchrow($result))
0804 {
0805 $id_ary[] = $row[$field];
0806 }
0807 $this->db->sql_freeresult($result);
0808
0809 // retrieve the total result count if needed
0810 if (!$result_count)
0811 {
0812 if ($type == 'posts')
0813 {
0814 $sql_count = "SELECT COUNT(*) as result_count
0815 FROM " . $sql_sort_table . POSTS_TABLE . ' p' . (($firstpost_only) ? ', ' . TOPICS_TABLE . ' t ' : ' ') . "
0816 WHERE $sql_author
0817 $sql_topic_id
0818 $sql_firstpost
0819 $m_approve_fid_sql
0820 $sql_fora
0821 $sql_sort_join
0822 $sql_time";
0823 }
0824 else
0825 {
0826 $sql_count = "SELECT COUNT(*) as result_count
0827 FROM " . $sql_sort_table . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
0828 WHERE $sql_author
0829 $sql_topic_id
0830 $sql_firstpost
0831 $m_approve_fid_sql
0832 $sql_fora
0833 AND t.topic_id = p.topic_id
0834 $sql_sort_join
0835 $sql_time
0836 GROUP BY t.topic_id, $sort_by_sql[$sort_key]";
0837 }
0838
0839 $this->db->sql_query($sql_count);
0840 $result_count = (int) $this->db->sql_fetchfield('result_count');
0841
0842 if (!$result_count)
0843 {
0844 return false;
0845 }
0846 }
0847
0848 $this->db->sql_transaction('commit');
0849
0850 if ($start >= $result_count)
0851 {
0852 $start = floor(($result_count - 1) / $per_page) * $per_page;
0853
0854 $result = $this->db->sql_query_limit($sql, $this->config['search_block_size'], $start);
0855 while ($row = $this->db->sql_fetchrow($result))
0856 {
0857 $id_ary[] = (int) $row[$field];
0858 }
0859 $this->db->sql_freeresult($result);
0860
0861 $id_ary = array_unique($id_ary);
0862 }
0863
0864 if (sizeof($id_ary))
0865 {
0866 $this->save_ids($search_key, '', $author_ary, $result_count, $id_ary, $start, $sort_dir);
0867 $id_ary = array_slice($id_ary, 0, $per_page);
0868
0869 return $result_count;
0870 }
0871 return false;
0872 }
0873
0874 /**
0875 * Destroys cached search results, that contained one of the new words in a post so the results won't be outdated
0876 *
0877 * @param string $mode contains the post mode: edit, post, reply, quote ...
0878 * @param int $post_id contains the post id of the post to index
0879 * @param string $message contains the post text of the post
0880 * @param string $subject contains the subject of the post to index
0881 * @param int $poster_id contains the user id of the poster
0882 * @param int $forum_id contains the forum id of parent forum of the post
0883 */
0884 public function index($mode, $post_id, &$message, &$subject, $poster_id, $forum_id)
0885 {
0886 // Split old and new post/subject to obtain array of words
0887 $split_text = $this->split_message($message);
0888 $split_title = ($subject) ? $this->split_message($subject) : array();
0889
0890 $words = array_unique(array_merge($split_text, $split_title));
0891
0892 unset($split_text);
0893 unset($split_title);
0894
0895 // destroy cached search results containing any of the words removed or added
0896 $this->destroy_cache($words, array($poster_id));
0897
0898 unset($words);
0899 }
0900
0901 /**
0902 * Destroy cached results, that might be outdated after deleting a post
0903 */
0904 public function index_remove($post_ids, $author_ids, $forum_ids)
0905 {
0906 $this->destroy_cache(array(), $author_ids);
0907 }
0908
0909 /**
0910 * Destroy old cache entries
0911 */
0912 public function tidy()
0913 {
0914 // destroy too old cached search results
0915 $this->destroy_cache(array());
0916
0917 $this->config->set('search_last_gc', time(), false);
0918 }
0919
0920 /**
0921 * Create fulltext index
0922 *
0923 * @return string|bool error string is returned incase of errors otherwise false
0924 */
0925 public function create_index($acp_module, $u_action)
0926 {
0927 // Make sure we can actually use PostgreSQL with fulltext indexes
0928 if ($error = $this->init())
0929 {
0930 return $error;
0931 }
0932
0933 if (empty($this->stats))
0934 {
0935 $this->get_stats();
0936 }
0937
0938 if (!isset($this->stats['post_subject']))
0939 {
0940 $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))");
0941 }
0942
0943 if (!isset($this->stats['post_content']))
0944 {
0945 $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))");
0946 }
0947
0948 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
0949
0950 return false;
0951 }
0952
0953 /**
0954 * Drop fulltext index
0955 *
0956 * @return string|bool error string is returned incase of errors otherwise false
0957 */
0958 public function delete_index($acp_module, $u_action)
0959 {
0960 // Make sure we can actually use PostgreSQL with fulltext indexes
0961 if ($error = $this->init())
0962 {
0963 return $error;
0964 }
0965
0966 if (empty($this->stats))
0967 {
0968 $this->get_stats();
0969 }
0970
0971 if (isset($this->stats['post_subject']))
0972 {
0973 $this->db->sql_query('DROP INDEX ' . $this->stats['post_subject']['relname']);
0974 }
0975
0976 if (isset($this->stats['post_content']))
0977 {
0978 $this->db->sql_query('DROP INDEX ' . $this->stats['post_content']['relname']);
0979 }
0980
0981 $this->db->sql_query('TRUNCATE TABLE ' . SEARCH_RESULTS_TABLE);
0982
0983 return false;
0984 }
0985
0986 /**
0987 * Returns true if both FULLTEXT indexes exist
0988 */
0989 public function index_created()
0990 {
0991 if (empty($this->stats))
0992 {
0993 $this->get_stats();
0994 }
0995
0996 return (isset($this->stats['post_subject']) && isset($this->stats['post_content'])) ? true : false;
0997 }
0998
0999 /**
1000 * Returns an associative array containing information about the indexes
1001 */
1002 public function index_stats()
1003 {
1004 if (empty($this->stats))
1005 {
1006 $this->get_stats();
1007 }
1008
1009 return array(
1010 $this->user->lang['FULLTEXT_POSTGRES_TOTAL_POSTS'] => ($this->index_created()) ? $this->stats['total_posts'] : 0,
1011 );
1012 }
1013
1014 /**
1015 * Computes the stats and store them in the $this->stats associative array
1016 */
1017 protected function get_stats()
1018 {
1019 if ($this->db->get_sql_layer() != 'postgres')
1020 {
1021 $this->stats = array();
1022 return;
1023 }
1024
1025 $sql = "SELECT c2.relname, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) AS indexdef
1026 FROM pg_catalog.pg_class c1, pg_catalog.pg_index i, pg_catalog.pg_class c2
1027 WHERE c1.relname = '" . POSTS_TABLE . "'
1028 AND pg_catalog.pg_table_is_visible(c1.oid)
1029 AND c1.oid = i.indrelid
1030 AND i.indexrelid = c2.oid";
1031 $result = $this->db->sql_query($sql);
1032
1033 while ($row = $this->db->sql_fetchrow($result))
1034 {
1035 // deal with older PostgreSQL versions which didn't use Index_type
1036 if (strpos($row['indexdef'], 'to_tsvector') !== false)
1037 {
1038 if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_subject' || $row['relname'] == POSTS_TABLE . '_post_subject')
1039 {
1040 $this->stats['post_subject'] = $row;
1041 }
1042 else if ($row['relname'] == POSTS_TABLE . '_' . $this->config['fulltext_postgres_ts_name'] . '_post_content' || $row['relname'] == POSTS_TABLE . '_post_content')
1043 {
1044 $this->stats['post_content'] = $row;
1045 }
1046 }
1047 }
1048 $this->db->sql_freeresult($result);
1049
1050 $this->stats['total_posts'] = $this->config['num_posts'];
1051 }
1052
1053 /**
1054 * Display various options that can be configured for the backend from the acp
1055 *
1056 * @return associative array containing template and config variables
1057 */
1058 public function acp()
1059 {
1060 $tpl = '
1061 <dl>
1062 <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_VERSION_CHECK_EXPLAIN'] . '</span></dt>
1063 <dd>' . (($this->db->get_sql_layer() == 'postgres') ? $this->user->lang['YES'] : $this->user->lang['NO']) . '</dd>
1064 </dl>
1065 <dl>
1066 <dt><label>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME'] . '</label><br /><span>' . $this->user->lang['FULLTEXT_POSTGRES_TS_NAME_EXPLAIN'] . '</span></dt>
1067 <dd><select name="config[fulltext_postgres_ts_name]">';
1068
1069 if ($this->db->get_sql_layer() == 'postgres')
1070 {
1071 $sql = 'SELECT cfgname AS ts_name
1072 FROM pg_ts_config';
1073 $result = $this->db->sql_query($sql);
1074
1075 while ($row = $this->db->sql_fetchrow($result))
1076 {
1077 $tpl .= '<option value="' . $row['ts_name'] . '"' . ($row['ts_name'] === $this->config['fulltext_postgres_ts_name'] ? ' selected="selected"' : '') . '>' . $row['ts_name'] . '</option>';
1078 }
1079 $this->db->sql_freeresult($result);
1080 }
1081 else
1082 {
1083 $tpl .= '<option value="' . $this->config['fulltext_postgres_ts_name'] . '" selected="selected">' . $this->config['fulltext_postgres_ts_name'] . '</option>';
1084 }
1085
1086 $tpl .= '</select></dd>
1087 </dl>
1088 <dl>
1089 <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>
1090 <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>
1091 </dl>
1092 <dl>
1093 <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>
1094 <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>
1095 </dl>
1096 ';
1097
1098 // These are fields required in the config table
1099 return array(
1100 'tpl' => $tpl,
1101 'config' => array('fulltext_postgres_ts_name' => 'string', 'fulltext_postgres_min_word_len' => 'integer:0:255', 'fulltext_postgres_max_word_len' => 'integer:0:255')
1102 );
1103 }
1104 }
1105