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

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