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