/
database_search.php
1211 lines (1083 loc) · 45.1 KB
/
database_search.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
<?php /*
ocPortal
Copyright (c) ocProducts, 2004-2012
See text/EN/licence.txt for full licencing information.
NOTE TO PROGRAMMERS:
Do not edit this file. If you need to make changes, save your changed file to the appropriate *_custom folder
**** If you ignore this advice, then your website upgrades (e.g. for bug fixes) will likely kill your changes ****
*/
/**
* @license http://opensource.org/licenses/cpal_1.0 Common Public Attribution License
* @copyright ocProducts Ltd
* @package core
*/
/*EXTRA FUNCTIONS: var_export*/
/**
* Standard code module initialisation function.
*/
function init__database_search()
{
$GLOBALS['TOTAL_RESULTS']=0;
}
/**
* Highlight keywords in an extracted portion of a piece of text.
*
* @param string What was searched
* @param array List of words searched
* @return string Highlighted portion
*/
function generate_text_summary($_temp_summary,$words_searched)
{
require_code('xhtml');
$summary='';
global $SEARCH__CONTENT_BITS;
$_temp_summary_lower=strtolower($_temp_summary);
// Add in some highlighting direct to XHTML
$all_occurrences=array();
foreach ($words_searched as $content_bit)
{
if ($content_bit=='') continue;
$last_pos=0;
$content_bit_pos=0;
do
{
$content_bit_matched=$content_bit;
if (strtoupper($content_bit)==$content_bit) // all upper case so don't want case sensitive
{
$content_bit_pos=strpos($_temp_summary,$content_bit,$last_pos);
} else
{
$content_bit_pos=strpos($_temp_summary_lower,strtolower($content_bit),$last_pos);
if (strpos($content_bit,'-')!==false)
{
$content_bit_pos_2=strpos($_temp_summary_lower,strtolower(str_replace('-','',$content_bit)),$last_pos);
if (($content_bit_pos_2!==false) && (($content_bit_pos===false) || ($content_bit_pos_2<$content_bit_pos)))
{
$content_bit_pos=$content_bit_pos_2;
$content_bit_matched=str_replace('-','',$content_bit);
}
}
}
if ($content_bit_pos!==false)
{
$last_gt=strrpos(substr($_temp_summary,0,$content_bit_pos),'>');
$last_lt=strrpos(substr($_temp_summary,0,$content_bit_pos),'<');
if (($last_gt!==false) && ($last_gt>$last_lt))
{
$extra_pre='<span class="comcode_highlight">';
$extra_post='</span>';
$_temp_summary=substr($_temp_summary,0,$content_bit_pos).
$extra_pre.
substr($_temp_summary,$content_bit_pos,strlen($content_bit_matched)).
$extra_post.
substr($_temp_summary,$content_bit_pos+strlen($content_bit_matched));
$_temp_summary_lower=strtolower($_temp_summary);
$last_pos=$content_bit_pos+strlen($extra_pre)+strlen($content_bit_matched)+strlen($extra_post);
// Adjust all stores occurrence offsets
foreach ($all_occurrences as $i=>$occ)
{
if ($occ[0]>$last_pos)
{
$all_occurrences[$i][0]+=strlen($extra_pre)+strlen($extra_post);
$all_occurrences[$i][1]+=strlen($extra_pre)+strlen($extra_post);
}
elseif ($occ[0]>$content_bit_pos)
{
$all_occurrences[$i][0]+=strlen($extra_pre);
$all_occurrences[$i][1]+=strlen($extra_pre);
}
}
$all_occurrences[]=array($content_bit_pos,$last_pos);
} else
{
$last_pos=$content_bit_pos+strlen($content_bit_matched);
}
}
}
while ($content_bit_pos!==false);
}
if (strlen($_temp_summary)<500)
{
$summary=$_temp_summary;
} else
{
// Find optimal position
$len=strlen($_temp_summary);
$best_yet=0;
$best_pos_min=250;
$best_pos_max=250;
if (count($all_occurrences)<60) // Only bother doing this if we need to dig for the keyword
{
for ($i=250;$i<$len-250;$i++) // Move window along all possible positions
{
$count=0;
$i_pre=$i-250;
$i_post=$i+250;
foreach ($all_occurrences as $occ)
{
$occ_pre=$occ[0];
$occ_post=$occ[1];
if (($occ_pre>=$i_pre) && ($occ_pre<=$i_post) && ($occ_post>=$i_pre) && ($occ_post<=$i_post))
{
$count++;
if ($count>5) break; // Good enough
}
}
if (($count>$best_yet) || (($best_yet==$count) && ($i-500<$best_pos_min)))
{
if ($best_yet==$count)
{
$best_pos_max=$i;
} else
{
$best_yet=$count;
$best_pos_min=$i;
$best_pos_max=$i;
}
if ($count>5) break; // Good enough
}
}
$best_pos=intval(floatval($best_pos_min+$best_pos_max)/2.0)-250; // Move it from center pos, to where we want to start from
} else
{
$best_pos=0;
}
// Render (with ellipses if required)
if (false)
{ // Far far too slow
$summary=xhtml_substr($_temp_summary,$best_pos,min(500,$len-$best_pos),true,true);
} else
{
$summary=substr($_temp_summary,$best_pos,min(500,$len-$best_pos));
$summary=xhtmlise_html($summary,true);
if ($best_pos>0) $summary='…'.$summary;
if ($best_pos+500<strlen($_temp_summary)) $summary.='…';
}
}
return $summary;
}
/**
* Server opensearch requests.
*/
function opensearch_script()
{
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
if (!has_actual_page_access(get_member(),'search')) return; // No access
$type=get_param('type','misc');
switch ($type)
{
// Make a search suggestion (like Google Suggest)
case 'suggest':
header('Content-type: text/plain; charset='.get_charset());
$request=get_param('request',false,true);
if (strlen($request)<3) return;
$suggestions=array();
$q='SELECT s_primary,COUNT(*) as cnt,MAX(s_num_results) AS s_num_results FROM '.get_table_prefix().'searches_logged WHERE ';
if ((db_has_full_text($GLOBALS['SITE_DB']->connection_read)) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_full_text_boolean')) && ($GLOBALS['SITE_DB']->static_ob->db_has_full_text_boolean()) && (!is_under_radar($request)))
{
$q.=preg_replace('#\?#','s_primary',db_full_text_assemble($request,false));
} else
{
$q.='s_primary LIKE \''./*ideally we would put an % in front, but too slow*/db_encode_like($request.'%').'\'';
}
$q.=' AND s_primary NOT LIKE \''.db_encode_like('%<%').'\' AND '.db_string_not_equal_to('s_primary','').' GROUP BY s_primary ORDER BY cnt DESC';
$past_searches=$GLOBALS['SITE_DB']->query($q,20);
foreach ($past_searches as $search)
{
if ($search['cnt']>5)
$suggestions[$search['s_primary']]=$search['s_num_results'];
}
require_lang('search');
@ini_set('ocproducts.xss_detect','0');
// JSON format
echo '['.chr(10);
// Original request
echo '"'.php_addslashes($request).'",'.chr(10);
// Suggestions
echo '[';
foreach (array_keys($suggestions) as $i=>$suggestion)
{
if ($i!=0) echo ',';
echo '"'.php_addslashes($suggestion).'"';
}
echo '],'.chr(10);
// Descriptions of suggestions
echo '[';
foreach (array_values($suggestions) as $i=>$suggestion)
{
if ($i!=0) echo ',';
echo '"'.php_addslashes(do_lang('NUM_RESULTS',integer_format($suggestion))).'"';
}
echo '],'.chr(10);
// URLs to search suggestions
$filter=get_param('filter','');
$filter_map=array();
if ($filter!='')
{
foreach (explode(':',$filter) as $f)
{
if ($f!='')
{
$parts=explode('=',$f,2);
if (count($parts)==1) $parts=array($parts[0],'1');
$filter_map[$parts[0]]=$parts[1];
}
}
}
echo '[';
foreach (array_keys($suggestions) as $i=>$suggestion)
{
if ($i!=0) echo ',';
$map=array('page'=>'search','type'=>'results','content'=>$suggestion)+$filter_map;
$_search_url=build_url($map,get_param('zone',get_module_zone('search')));
$search_url=$_search_url->evaluate();
echo '"'.php_addslashes($search_url).'"';
}
echo ']'.chr(10);
echo ']'.chr(10);
break;
// Provide details about the site search engine
default:
//header('Content-Type: application/opensearchdescription+xml');
header('Content-Type: text/xml');
$tpl=do_template('OPENSEARCH',array('DESCRIPTION'=>get_option('description')));
$tpl->evaluate_echo();
break;
}
}
/**
* Build up a submitter search clause, taking into account members, authors, usernames, and usergroups.
*
* @param ?ID_TEXT The field name for member IDs (NULL: Cannot match against member IDs)
* @param ?MEMBER Member ID (NULL: Unknown, so cannot search)
* @param ID_TEXT Author
* @param ?ID_TEXT The field name for authors (NULL: Cannot match against member IDs)
* @return ?string An SQL fragment (NULL: block query)
*/
function build_search_submitter_clauses($member_field_name,$member_id,$author,$author_field_name=NULL)
{
$clauses='';
// Member ID
if ((!is_null($member_id)) && (!is_null($member_field_name)))
{
if ($clauses!='') $clauses.=' OR ';
$clauses.=$member_field_name.'='.strval($member_id);
}
// Groups
if ((!is_null($member_field_name)) && ($author!=''))
{
$all_usergroups=$GLOBALS['FORUM_DRIVER']->get_usergroup_list(true);
foreach ($all_usergroups as $usergroup=>$usergroup_name)
{
if ($usergroup_name==$author)
{
$members_in_group=$GLOBALS['FORUM_DRIVER']->member_group_query(array($usergroup),50);
if (count($members_in_group)<50) // Let's be reasonable with how long the SQL could get!
{
foreach (array_keys($members_in_group) as $group_member_id)
{
if ($clauses!='') $clauses.=' OR ';
$clauses.=$member_field_name.'='.strval($group_member_id);
}
}
break;
}
}
}
// Author
if ((!is_null($author_field_name)) && ($author!=''))
{
if ($clauses!='') $clauses.=' OR ';
$clauses.=db_string_equal_to($author_field_name,$author);
}
if ($clauses=='')
{
if ($author!='') return NULL; // Query should never succeed
return '';
}
return ' AND ('.$clauses.')';
}
/**
* Get special SQL from POSTed parameters for a catalogue search field that is to be exact-matched.
*
* @param array The row for the field to input
* @param integer We're processing for the ith row
* @param ID_TEXT Table type
* @set short long
* @param ?string Search term (NULL: lookup from environment)
* @return ?array Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (NULL: nothing special)
*/
function exact_match_sql($row,$i,$type='short',$param=NULL)
{
$table=' JOIN '.$GLOBALS['SITE_DB']->get_table_prefix().'catalogue_efv_'.$type.' f'.strval($i).' ON (f'.strval($i).'.ce_id=r.id AND f'.strval($i).'.cf_id='.strval($row['id']).')';
$search_field='f'.strval($i).'.cv_value';
if (is_null($param)) $param=get_param('option_'.strval($row['id']),'');
$where_clause='';
if ($param!='')
{
if ($type=='float')
{
$where_clause.=$search_field.'='.$param;
}
elseif ($type=='integer')
{
$where_clause.=$search_field.'='.$param;
} else
{
$where_clause=db_string_equal_to($search_field,$param);
}
}
return array(array(),array('f'.strval($i).'.cv_value'),$table,$search_field,$where_clause);
}
/**
* Get special SQL from POSTed parameters for a catalogue search field for a multi-input field that is to be exact-matched.
*
* @param array The row for the field to input
* @param integer We're processing for the ith row
* @param ID_TEXT Table type
* @set short long
* @param ?string Search term (NULL: lookup from environment)
* @return ?array Tuple of SQL details (array: extra trans fields to search, array: extra plain fields to search, string: an extra table segment for a join, string: the name of the field to use as a title, if this is the title, extra WHERE clause stuff) (NULL: nothing special)
*/
function nl_delim_match_sql($row,$i,$type='short',$param=NULL)
{
$table=' JOIN '.$GLOBALS['SITE_DB']->get_table_prefix().'catalogue_efv_'.$type.' f'.strval($i).' ON (f'.strval($i).'.ce_id=r.id AND f'.strval($i).'.cf_id='.strval($row['id']).')';
$search_field='f'.strval($i).'.cv_value';
if (is_null($param)) $param=get_param('option_'.strval($row['id']),'');
$where_clause='';
if ($param!='') $where_clause='('.$search_field.' LIKE \''.db_encode_like($param).'\' OR '.$search_field.' LIKE \''.db_encode_like('%'.chr(10).$param).'\' OR '.$search_field.' LIKE \''.db_encode_like($param.chr(10).'%').'\' OR '.$search_field.' LIKE \''.db_encode_like('%'.chr(10).$param.chr(10).'%').'\')';
return array(array(),array('f'.strval($i).'.cv_value'),$table,$search_field,$where_clause);
}
/**
* Get some rows, queried from the database according to the search parameters.
*
* @param ?ID_TEXT The META type used by our content (NULL: Cannot support META search)
* @param ?ID_TEXT The name of the field that retrieved META IDs will relate to (NULL: Cannot support META search)
* @param string Search string
* @param boolean Whether to do a boolean search.
* @param ID_TEXT Boolean operator
* @set OR AND
* @param boolean Whether to only do a META (tags) search
* @param ID_TEXT Order direction
* @param integer Start position in total results
* @param integer Maximum results to return in total
* @param boolean Whether to only search titles (as opposed to both titles and content)
* @param ID_TEXT The table name
* @param array The translateable fields to search over (or an ! which is skipped). The first of these must be the title field or an '!'; if it is '!' then the title field will be the first raw-field
* @param string The WHERE clause
* @param string The WHERE clause that applies specifically for content (this will be duplicated to check against multiple fields). ? refers to the yet-unknown field name
* @param ID_TEXT What to order by
* @param string What to select
* @param ?array The non-translateable fields to search over (NULL: there are none)
* @param ?string The permission module to check category access for (NULL: none)
* @param ?string The field that specifies the permissions ID to check category access for (NULL: none)
* @param boolean Whether the permissions field is a string
* @return array The rows found
*/
function get_search_rows($meta_type,$meta_id_field,$content,$boolean_search,$boolean_operator,$only_search_meta,$direction,$max,$start,$only_titles,$table,$fields,$where_clause,$content_where,$order,$select='*',$raw_fields=NULL,$permissions_module=NULL,$permissions_field=NULL,$permissions_field_is_string=false)
{
@ignore_user_abort(false); // If the user multi-submits a search, we don't want to run parallel searches (very slow!). That said, this currently doesn't work in PHP, because PHP does not realise the connection has died until way too late :(. So we also use a different tact (dedupe_mode) but hope PHP will improve with time.
if (substr($where_clause,0,5)==' AND ') $where_clause=substr($where_clause,5);
if (substr($where_clause,-5)==' AND ') $where_clause=substr($where_clause,0,strlen($where_clause)-5);
$where_alternative_matches=array();
if ((!is_null($permissions_module)) && (!$GLOBALS['FORUM_DRIVER']->is_super_admin(get_member())))
{
$g_or=_get_where_clause_groups(get_member());
// this destroys mysqls query optimiser by forcing complexed OR's into the join, so we'll do this in PHP code
// $table.=' LEFT JOIN '.$GLOBALS['SITE_DB']->get_table_prefix().'group_category_access z ON ('.db_string_equal_to('z.module_the_name',$permissions_module).' AND z.category_name='.$permissions_field.(($g_or!='')?(' AND '.str_replace('group_id','z.group_id',$g_or)):'').')';
// $where_clause.=' AND ';
// $where_clause.='z.category_name IS NOT NULL';
$cat_access=list_to_map('category_name',$GLOBALS['SITE_DB']->query('SELECT category_name FROM '.$GLOBALS['SITE_DB']->get_table_prefix().'group_category_access WHERE '.db_string_equal_to('module_the_name',$permissions_module).(($g_or!='')?(' AND ('.$g_or.')'):'')));
}
if (($only_titles) && (array_key_exists(0,$fields)) && ($fields[0]=='')) return array();
if (is_null($raw_fields)) $raw_fields=array();
$db=(substr($table,0,2)!='f_')?$GLOBALS['SITE_DB']:$GLOBALS['FORUM_DB'];
// This is so for example catalogue_entries.php can use brackets in it's table specifier whilst avoiding the table prefix after the first bracket. A bit weird, but that's our convention and it does save a small amount of typing
$table_clause=$db->get_table_prefix().(($table[0]=='(')?(substr($table,1)):$table);
if ($table[0]=='(') $table_clause='('.$table_clause;
$t_rows=array();
$t_count=0;
// Rating ordering, via special encoding
if (substr($order,0,7)=='_rating')
{
list(,$rating_type,$meta_rating_id_field)=explode(':',$order);
$select.=',(SELECT AVG(rating) FROM '.get_table_prefix().'rating WHERE '.db_string_equal_to('rating_for_type',$rating_type).' AND rating_for_id='.$meta_rating_id_field.') AS compound_rating';
$order='compound_rating';
}
$translate_join_type=(get_value('alternate_search_join_type')==='1')?'LEFT JOIN':'JOIN';
// Defined-keywords/tags search
if ((get_param_integer('keep_just_show_query',0)==0) && (!is_null($meta_type)) && ($content!=''))
{
$keywords_where=preg_replace('#\?#','tm.text_original',build_content_where($content,$boolean_search,$boolean_operator,true));
$keywords_where=str_replace(' AND (tm.text_original IS NOT NULL)','',$keywords_where); // Not needed for translate joins, as these won't be NULL's. Fixes performance issue.
if ($keywords_where!='')
{
if ($meta_id_field=='the_zone:the_page') // Special case
{
$meta_join='m.meta_for_id=CONCAT(r.the_zone,\':\',r.the_page)';
} else
{
$meta_join='m.meta_for_id=r.'.$meta_id_field;
}
$extra_join='';
foreach ($fields as $i=>$field) // Translatable fields present in 'select'
{
if (($field=='') || ($field=='!') || (strpos($select,'t1.text_original')===false)) continue;
$extra_join.=' '.$translate_join_type.' '.$db->get_table_prefix().'translate t'.strval($i).' ON t'.strval($i).'.id='.$field.' AND '.db_string_equal_to('t'.strval($i).'.language',user_lang());
}
if (!db_has_subqueries($db->connection_read) || true /* Forced this old code to run because the "optimisation" does not work for larger result sets */)
{
$_keywords_query=$table_clause.' '.$translate_join_type.' '.$db->get_table_prefix().'seo_meta m ON ('.db_string_equal_to('m.meta_for_type',$meta_type).' AND '.$meta_join.') '.$translate_join_type.' '.$db->get_table_prefix().'translate tm ON tm.id=m.meta_keywords AND '.db_string_equal_to('tm.language',user_lang()).$extra_join;
$_keywords_query.=' WHERE '.$keywords_where;
$_keywords_query.=(($where_clause!='')?(' AND '.$where_clause):'');
} else
{
$_keywords_query=$table_clause.' '.$translate_join_type.' '.$db->get_table_prefix().'seo_meta m ON ('.db_string_equal_to('m.meta_for_type',$meta_type).' AND '.$meta_join.') '.$translate_join_type.' '.$db->get_table_prefix().'translate tm ON tm.id=m.meta_keywords AND '.db_string_equal_to('tm.language',user_lang()).$extra_join;
$_keywords_query.=' WHERE '.$keywords_where;
$_keywords_query.=(($where_clause!='')?(' AND tm.id IN (SELECT m.id FROM '.$table_clause.' '.$translate_join_type.' '.$db->get_table_prefix().'seo_meta m ON ('.db_string_equal_to('m.meta_for_type',$meta_type).' AND '.$meta_join.') '.$translate_join_type.' '.$db->get_table_prefix().'translate tm ON tm.id=m.meta_keywords AND '.db_string_equal_to('tm.language',user_lang()).' WHERE '.$where_clause.' AND '.$keywords_where.')'):'');
}
$keywords_query='SELECT '.$select.' FROM '.$_keywords_query;
if (!db_has_subqueries($db->connection_read))
{
$_count_query_keywords_search='SELECT COUNT(*) FROM '.$_keywords_query;
} else
{
$_count_query_keywords_search='(SELECT COUNT(*) FROM (';
$_count_query_keywords_search.='SELECT 1 FROM '.$_keywords_query;
$_count_query_keywords_search.=' LIMIT 1000) counter)';
}
$group_by_ok=(can_arbitrary_groupby() && $meta_id_field==='id');
if (strpos($table,' LEFT JOIN')===false) $group_by_ok=false; // Don't actually need to do a group by, as no duplication possible
$keywords_query.=($group_by_ok?' GROUP BY r.id':'');
if (($order!='') && ($order.' '.$direction!='contextual_relevance DESC'))
{
$keywords_query.=' ORDER BY '.$order;
if ($direction=='DESC') $keywords_query.=' DESC';
}
/*if ($group_by_ok) This accuracy is not needed, and does not work with the "LIMIT 1000" subquery optimisation
{
$_count_query_keywords_search=str_replace('COUNT(*)','COUNT(DISTINCT r.id)',$_count_query_keywords_search);
}*/
$db->dedupe_mode=true;
$t_keyword_search_rows_count=$db->query_value_null_ok_full($_count_query_keywords_search);
$t_keyword_search_rows=$db->query($keywords_query,$max+$start);
$db->dedupe_mode=false;
$t_count+=$t_keyword_search_rows_count;
$t_rows=array_merge($t_rows,$t_keyword_search_rows);
} else $_count_query_keywords_search=NULL;
} else $_count_query_keywords_search=NULL;
$orig_table_clause=$table_clause;
// Main content search
if (!$only_search_meta)
{
if (($content_where!='') || (preg_match('#t\d+\.text_original#',$where_clause)!=0) || (preg_match('#t\d+\.text_original#',$select)!=0))
{
// Each of the fields represents an 'OR' match, so we put it together into a list ($where_alternative_matches) of specifiers for each. Hopefully we will 'UNION' them rather than 'OR' them as it is much more efficient in terms of table index usage
$where_alternative_matches=array();
foreach ($fields as $i=>$field) // Referenced fields in where condition must result in the shared table clause having a reference to the translate for that
{
if ((strpos($select,'t'.strval($i).'.text_original')!==false) || (strpos($where_clause,'t'.strval($i).'.text_original')!==false))
{
$tc_add=' '.$translate_join_type.' '.$db->get_table_prefix().'translate t'.strval($i).' ON t'.strval($i).'.id='.$field.' AND '.db_string_equal_to('t'.strval($i).'.language',user_lang());
$orig_table_clause.=$tc_add;
}
}
foreach ($fields as $i=>$field) // Translatable fields
{
if (($field=='') || ($field=='!')) continue;
if ($field==$order) $order='t'.$i.'.text_original'; // Ah, remap to the textual equivalent then
$tc_add=' '.$translate_join_type.' '.$db->get_table_prefix().'translate t'.strval($i).' ON t'.strval($i).'.id='.$field.' AND '.db_string_equal_to('t'.strval($i).'.language',user_lang());
if (strpos($orig_table_clause,$tc_add)!==false) $tc_add='';
if (($only_titles) && ($i!=0)) break;
$where_clause_2=preg_replace('#\?#','t'.strval($i).'.text_original',$content_where);
$where_clause_2=str_replace(' AND (t'.strval($i).'.text_original IS NOT NULL)','',$where_clause_2); // Not needed for translate joins, as these won't be NULL's. Fixes performance issue.
$where_clause_3=$where_clause;
if (($table=='f_members') && (substr($field,0,6)=='field_') && (db_has_subqueries($db->connection_read)))
$where_clause_3.=(($where_clause=='')?'':' AND ').'NOT EXISTS (SELECT * FROM '.$db->get_table_prefix().'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id='.substr($field,6).' AND cpfp.guest_view=0)';
if (($order=='') && (db_has_expression_ordering($db->connection_read)) && ($content_where!=''))
{
$_select=preg_replace('#\?#','t'.strval($i).'.text_original',$content_where).' AS contextual_relevance';
$_select=str_replace(' AND (t'.strval($i).'.text_original IS NOT NULL)','',$_select); // Not needed for translate joins, as these won't be NULL's. Fixes performance issue.
} else
{
$_select='';
}
$_table_clause=$orig_table_clause.$tc_add;
$where_alternative_matches[]=array($where_clause_2,$where_clause_3,$_select,$_table_clause,'t'.strval($i));
}
if ($content_where!='') // Non-translatable fields
{
foreach ($raw_fields as $i=>$field)
{
if (($only_titles) && ($i!=0)) break;
$where_clause_2=preg_replace('#\?#',$field,$content_where);
$where_clause_3=$where_clause;
if (($table=='f_members') && (substr($field,0,6)=='field_') && (db_has_subqueries($db->connection_read)))
$where_clause_3.=(($where_clause=='')?'':' AND ').'NOT EXISTS (SELECT * FROM '.$db->get_table_prefix().'f_cpf_perms cpfp WHERE cpfp.member_id=r.id AND cpfp.field_id='.substr($field,6).' AND cpfp.guest_view=0)';
if (($order=='') && (db_has_expression_ordering($db->connection_read)) && ($content_where!=''))
{
$_select=preg_replace('#\?#',$field,$content_where).' AS contextual_relevance';
} else
{
$_select='';
}
$_table_clause=$orig_table_clause;
$where_alternative_matches[]=array($where_clause_2,$where_clause_3,$_select,$_table_clause,NULL);
}
}
}
if (count($where_alternative_matches)==0)
{
$where_alternative_matches[]=array($where_clause,'','',$table_clause,NULL);
} else
{
if (($order=='') && (db_has_expression_ordering($db->connection_read)) && ($content_where!=''))
{
$order='contextual_relevance DESC';
}
}
$group_by_ok=(can_arbitrary_groupby() && $meta_id_field==='id');
if (strpos($table,' LEFT JOIN')===false) $group_by_ok=false; // Don't actually need to do a group by, as no duplication possible. We want to avoid GROUP BY as it forces MySQL to create a temporary table, slowing things down a lot.
// Work out main query
global $SITE_INFO;
if (((isset($SITE_INFO['mysql_old'])) && ($SITE_INFO['mysql_old']=='1')) || ((!isset($SITE_INFO['mysql_old'])) && (is_file(get_file_base().'/mysql_old'))))
{
$_query='';
foreach ($where_alternative_matches as $parts)
{
list($where_clause_2,$where_clause_3,$_select,,)=$parts;
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$select.=(($_select=='')?'':',').$_select;
$_query.=(($where_clause_3!='')?((($_query=='')?' WHERE ':' OR ').$where_clause_3):'');
}
$query='SELECT '.$select.' FROM '.$table_clause.$_query;
} else
{
$query='(';
foreach ($where_alternative_matches as $parts) // We UNION them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
{
list($where_clause_2,$where_clause_3,$_select,$_table_clause,$tid)=$parts;
if ($query!='(')
{
if (($order!='') && ($order.' '.$direction!='contextual_relevance DESC') && ($order!='contextual_relevance DESC'))
{
$query.=' ORDER BY '.$order;
if (($direction=='DESC') && (substr($order,-4)!=' ASC') && (substr($order,-5)!=' DESC')) $query.=' DESC';
}
$query.=' LIMIT '.strval($max+$start);
$query.=') UNION (';
}
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$query.='SELECT '.$select.(($_select=='')?'':',').$_select.' FROM '.$_table_clause.(($where_clause_3=='')?'':' WHERE '.$where_clause_3);
}
if (($order!='') && ($order.' '.$direction!='contextual_relevance DESC') && ($order!='contextual_relevance DESC'))
{
$query.=' ORDER BY '.$order;
if (($direction=='DESC') && (substr($order,-4)!=' ASC') && (substr($order,-5)!=' DESC')) $query.=' DESC';
}
$query.=($group_by_ok?' GROUP BY r.id':'');
$query.=' LIMIT '.strval($max+$start);
$query.=')';
}
// Work out COUNT(*) query using one of a few possible methods. It's not efficient and stops us doing proper merge-sorting between content types (and possible not accurate - if we use an efficient but non-deduping COUNT strategy) if we have to use this, so we only do it if there are too many rows to fetch in one go.
$_query='';
if (((isset($SITE_INFO['mysql_old'])) && ($SITE_INFO['mysql_old']=='1')) || ((!isset($SITE_INFO['mysql_old'])) && (is_file(get_file_base().'/mysql_old'))) || (strpos(get_db_type(),'mysql')===false))
{
foreach ($where_alternative_matches as $parts)
{
list($where_clause_2,$where_clause_3,,$_table_clause,$tid)=$parts;
if ((!db_has_subqueries($db->connection_read)) || (is_null($tid)) || ($content_where=='') || true)
{
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
$_query.=(($where_clause_3!='')?((($_query=='')?' WHERE ':' OR ').$where_clause_3):'');
} else
{
if (($where_clause_2!='') || ($where_clause_3!=''))
{
$_query.=(($_query=='')?' WHERE ':' OR ').$where_clause_2;
$_query.=(($where_clause_3!='')?((($where_clause_2=='')?'':' AND ').$tid.'.id IN (SELECT '.$tid.'.id FROM '.$_table_clause.' WHERE '.$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3)).')'):'');
}
}
}
$_count_query_main_search='SELECT COUNT(*) FROM '.$table_clause.$_query;
} else // This is inaccurate (does not filter dupes from each +'d query) but much more efficient on MySQL
{
foreach ($where_alternative_matches as $parts) // We "+" them, because doing OR's on MATCH's is insanely slow in MySQL (sometimes I hate SQL...)
{
list($where_clause_2,$where_clause_3,$_select,$_table_clause,$tid)=$parts;
if ($_query!='') $_query.='+';
$where_clause_3=$where_clause_2.(($where_clause_3=='')?'':((($where_clause_2=='')?'':' AND ').$where_clause_3));
if (!db_has_subqueries($db->connection_read))
{
$_query.='(SELECT COUNT(*) FROM '.$_table_clause.(($where_clause_3=='')?'':(' WHERE '.$where_clause_3)).')';
} else // Has to do a nested subquery to reduce scope of COUNT(*), because the unbounded full-text's binary tree descendence can be extremely slow on physical disks if common words exist that aren't defined as MySQL stop words
{
$_query.='(SELECT COUNT(*) FROM (';
$_query.='SELECT 1 FROM '.$_table_clause.(($where_clause_3=='')?'':(' WHERE '.$where_clause_3));
$_query.=' LIMIT 1000) counter)';
}
}
$_count_query_main_search='SELECT ('.$_query.')';
}
if (($order!='') && ($order.' '.$direction!='contextual_relevance DESC') && ($order!='contextual_relevance DESC'))
{
$query.=' ORDER BY '.$order;
if (($direction=='DESC') && (substr($order,-4)!=' ASC') && (substr($order,-5)!=' DESC')) $query.=' DESC';
}
if (get_param_integer('keep_show_query',0)==1)
{
attach_message($query,'inform');
}
if (get_param_integer('keep_just_show_query',0)==1)
{
@ini_set('ocproducts.xss_detect','0');
header('Content-type: text/plain; charset='.get_charset());
exit($query);
}
/*if ($group_by_ok) This accuracy is not needed, and does not work with the "LIMIT 1000" subquery optimisation
{
$_count_query_main_search=str_replace('COUNT(*)','COUNT(DISTINCT r.id)',$_count_query_main_search);
}*/
$db->dedupe_mode=true;
$t_main_search_rows_count=$db->query_value_null_ok_full($_count_query_main_search);
$t_main_search_rows=$db->query($query,$max+$start,NULL,false,true);
$db->dedupe_mode=false;
$t_count+=$t_main_search_rows_count;
} else
{
$t_main_search_rows=array();
}
// Clean results and return
// NB: We don't use the count_query's any more (except when using huge data sets, see above), because you can't actually just add them because they overlap. So instead we fetch all results and throw some away.
$t_rows=array_merge($t_rows,$t_main_search_rows);
if (count($t_rows)>0)
{
$t_rows_new=array();
if ((array_key_exists('id',$t_rows[0])) || (array_key_exists('_primary_id',$t_rows[0])))
{
$done=array();
foreach ($t_rows as $t_row)
{
if (array_key_exists('id',$t_row))
{
if (array_key_exists($t_row['id'],$done)) continue;
$done[$t_row['id']]=1;
}
elseif (array_key_exists('_primary_id',$t_row))
{
if (array_key_exists($t_row['_primary_id'],$done)) continue;
$done[$t_row['_primary_id']]=1;
}
$t_rows_new[]=$t_row;
}
} else
{
foreach ($t_rows as $t_row)
{
unset($t_row['contextual_relevance']);
foreach ($t_rows_new as $_t_row)
{
if (($_t_row==$t_row) || ((array_key_exists('id',$t_row)) && (array_key_exists('id',$_t_row)) && (!array_key_exists('_primary_id',$t_row)) && (!array_key_exists('_primary_id',$_t_row)) && ($t_row['id']==$_t_row['id'])) || ((array_key_exists('_primary_id',$t_row)) && (array_key_exists('_primary_id',$_t_row)) && ($t_row['_primary_id']==$_t_row['_primary_id'])))
continue 2;
}
$t_rows_new[]=$t_row;
}
}
$t_rows=$t_rows_new;
}
if (get_param_integer('keep_show_query',0)==1)
{
if ((array_key_exists(0,$t_rows)) && (array_key_exists('id',$t_rows[0])))
{
$results=var_export(array_unique(collapse_1d_complexity('id',$t_rows)),true);
} else
{
$results=var_export($t_rows,true);
}
attach_message(do_lang('_RESULTS').': '.$results,'inform');
}
if (isset($cat_access))
{
$before=count($t_rows);
foreach ($t_rows as $i=>$row)
{
if (!array_key_exists(strval($row[$permissions_field]),$cat_access)) unset($t_rows[$i]);
}
}
$final_result_rows=$t_rows;
$GLOBALS['TOTAL_RESULTS']+=$t_count;
array_splice($final_result_rows,$max*2+$start); // We return more than max in case our search hook does some extra in-code filtering (Catalogues, Comcode pages). It shouldn't really but sometimes it has to, and it certainly shouldn't filter more than 50%. Also so our overall ordering can be better.
return $final_result_rows;
}
/**
* Take a search string and find boolean search parameters from it.
*
* @param string The search string
* @return array Words to search under the boolean operator, words that must be included, words that must not be included.
*/
function _boolean_search_prepare($search_filter)
{
$search_filter=str_replace('?','_',$search_filter);
$search_filter=str_replace('*','%',$search_filter);
$content_explode=explode(' ',$search_filter);
$body_words=array();
$include_words=array();
$disclude_words=array();
for ($i=0;$i<count($content_explode);$i++)
{
$word=trim($content_explode[$i]);
if (($word=='') || ($word=='+') || ($word=='-')) continue;
// Handle quotes
if ($word[0]=='"')
{
$i++;
while ($i<count($content_explode))
{
$word2=trim($content_explode[$i]);
if ($word2!='')
{
$word.=' '.$word2;
}
if (substr($word2,-1)=='"')
{
break;
} else
{
$i++;
}
}
if (substr($word,-1)!='"') $word.='"';
$word=substr($word,1,strlen($word)-2);
}
if ($word[0]=='+') $include_words[]=$word;
elseif ($word[0]=='-') $disclude_words[]=$word;
else $body_words[]=$word;
}
return array($body_words,$include_words,$disclude_words);
}
/**
* Perform a database-style in-memory boolean search on single item.
*
* @param array A map of POST data in search-form style. May contain 'only_titles', 'content' (the critical one!) and 'conjunctive_operator'
* @param string The title to try and match
* @param ?string The post to try and match (NULL: not used)
* @return boolean Whether we have a match
*/
function in_memory_search_match($filter,$title,$post=NULL)
{
if ((!array_key_exists('content',$filter)) || ($filter['content']=='')) return true;
$search_filter=$filter['content'];
if (((array_key_exists('only_titles',$filter)) && ($filter['only_titles']==1)) || (is_null($post)))
{
$context=$title;
} else
{
$context=$title.' '.$post;
}
$boolean_operator=array_key_exists('conjunctive_operator',$filter)?$filter['conjunctive_operator']:'OR';
list($body_words,$include_words,$disclude_words)=_boolean_search_prepare($search_filter);
foreach ($include_words as $word)
{
if (!simulated_wildcard_match($context,$word)) return false;
}
foreach ($disclude_words as $word)
{
if (simulated_wildcard_match($context,$word)) return false;
}
if ($boolean_operator=='OR')
{
$count=0;
foreach ($body_words as $word)
{
if (simulated_wildcard_match($context,$word)) $count++;
}
if ($count==0) return false;
} else
{
foreach ($body_words as $word)
{
if (!simulated_wildcard_match($context,$word)) return false;
}
}
return true;
}
/**
* Find whether a phrase is too small for fulltext search.
*
* @param string The phrase
* @return boolean Whether it is
*/
function is_under_radar($test)
{
if (get_value('disable_boolean_search')==='1') return false;
static $min_word_length=NULL;
if (is_null($min_word_length))
{
$min_word_length=4;
if (substr(get_db_type(),0,5)=='mysql')
{
$_min_word_length=$GLOBALS['SITE_DB']->query('SHOW VARIABLES LIKE \'ft_min_word_len\'',NULL,NULL);
if (array_key_exists(0,$_min_word_length))
$min_word_length=intval($_min_word_length[0]['Value']);
}
}
return ((strlen($test)<$min_word_length) && ($test!=''));
}
/**
* Build a fulltext query WHERE clause from given content.
*
* @param string The search content
* @param boolean Whether it's a boolean search
* @param string Boolean operation to use
* @set AND OR
* @param boolean Whether we can assume we require full coverage
* @return string WHERE clause
*/
function build_content_where($content,$boolean_search,&$boolean_operator,$full_coverage=false)
{
$content=str_replace('?','',$content);
list($body_words,$include_words,$disclude_words)=_boolean_search_prepare($content);
$under_radar=false;
if ((is_under_radar($content)) && ($content!='')) $under_radar=true;
if (($under_radar) || ($boolean_search) || (!db_has_full_text($GLOBALS['SITE_DB']->connection_read)))
{
if (!in_array(strtoupper($boolean_operator),array('AND','OR'))) log_hack_attack_and_exit('ORDERBY_HACK');
if ($content=='')
{
$content_where='';
} else
{
if ((db_has_full_text($GLOBALS['SITE_DB']->connection_read)) && (method_exists($GLOBALS['SITE_DB']->static_ob,'db_has_full_text_boolean')) && ($GLOBALS['SITE_DB']->static_ob->db_has_full_text_boolean()) && (!$under_radar))
{
$content_where=db_full_text_assemble($content,true);
} else
{
$content_where=db_like_assemble($content,$boolean_operator,$full_coverage);
if ($content_where=='') $content_where='1=1';
}
}
} else
{
if ($content=='')
{
$content_where='';
} else
{
$content_where=db_full_text_assemble($content,false);
}
$boolean_operator='OR';
}
return $content_where;
}
/**
* Generate SQL for a boolean search.
*
* @param string Boolean search string
* @param string Boolean operator to use
* @set AND OR
* @param boolean Whether we can assume we require full coverage
* @return string The SQL (may be blank)
*/
function db_like_assemble($content,$boolean_operator='AND',$full_coverage=false)
{
list($body_words,$include_words,$disclude_words)=_boolean_search_prepare($content);