PageRenderTime 292ms CodeModel.GetById 141ms app.highlight 66ms RepoModel.GetById 75ms app.codeStats 1ms

/wwwroot/phpbb/phpbb/search/fulltext_mysql.php

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