PageRenderTime 56ms CodeModel.GetById 25ms RepoModel.GetById 0ms app.codeStats 0ms

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