source: trunk/forum/includes/search/fulltext_mysql.php

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