PageRenderTime 233ms CodeModel.GetById 81ms app.highlight 81ms RepoModel.GetById 59ms app.codeStats 1ms

/wwwroot/phpbb/phpbb/search/fulltext_postgres.php

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