/
Search.pm
599 lines (509 loc) · 19.6 KB
/
Search.pm
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
# This code is a part of Slash, and is released under the GPL.
# Copyright 1997-2004 by Open Source Development Network. See README
# and COPYING for more information, or see http://slashcode.com/.
# $Id$
package Slash::Search;
use strict;
use Slash::Utility;
use Slash::DB::Utility;
use vars qw($VERSION);
use base 'Slash::DB::Utility';
($VERSION) = ' $Revision$ ' =~ /\$Revision:\s+([^\s]+)/;
# FRY: And where would a giant nerd be? THE LIBRARY!
#################################################################
sub SelectDataBases {
my $search_db_user = getCurrentStatic('search_db_user');
my($slashdb, $searchDB);
if ($search_db_user) {
$slashdb = getObject('Slash::DB', $search_db_user);
$searchDB = getObject('Slash::Search', $search_db_user);
} else {
$slashdb = getCurrentDB();
$searchDB = Slash::Search->new(getCurrentVirtualUser());
}
return($slashdb, $searchDB);
}
#################################################################
sub new {
my($class, $user) = @_;
my $self = {};
my $plugin = getCurrentStatic('plugin');
return unless $plugin->{'Search'};
bless($self, $class);
$self->{virtual_user} = $user;
$self->sqlConnect();
return $self;
}
####################################################################################
# This has been changed. Since we no longer delete comments
# it is safe to have this run against stories.
sub findComments {
my($self, $form, $start, $limit, $sort) = @_;
# select comment ID, comment Title, Author, Email, link to comment
# and SID, article title, type and a link to the article
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $constants = getCurrentStatic();
my $columns;
$columns .= "primaryskid, url, discussions.uid AS author_uid, discussions.title AS title, ";
$columns .= "pid, subject, ts, date, comments.uid AS uid, cid, ";
$columns .= "discussions.id AS did, ";
$columns .= "TRUNCATE( "
. $self->_score('comments.subject', $form->{query}, $constants->{search_method})
. ", 1) AS score "
if $form->{query};
my $tables = "comments, discussions";
my $key = " MATCH (comments.subject) AGAINST ($query) ";
# Welcome to the join from hell -Brian
my $where;
$where .= " comments.sid = discussions.id ";
$where .= " AND $key " if $form->{query};
if ($form->{sid}) {
if ($form->{sid} !~ /^\d+$/) {
$where .= " AND discussions.sid=" . $self->sqlQuote($form->{sid})
} else {
$where .= " AND discussions.id=" . $self->sqlQuote($form->{sid})
}
}
if (defined $form->{threshold}){
my $threshold = $form->{threshold};
my $threshold_q = $self->sqlQuote($threshold);
$where .= " AND GREATEST((points + tweak), $constants->{comment_minscore}) >= $threshold_q ";
}
my $gSkin = getCurrentSkin();
my $reader = getObject('Slash::DB', { db_type => 'reader' });
my $skin = $reader->getSkin($form->{section} || $gSkin->{skid});
if ($skin->{skid} != $constants->{mainpage_skid}) {
$where .= " AND primaryskid = $skin->{skid}";
}
my $other;
$other .= " HAVING score > 0 "
if $form->{query};
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC ";
} else {
$other .= " ORDER BY cid DESC ";
}
$other .= " LIMIT $start, $limit" if $limit;
my $search = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other );
return $search;
}
####################################################################################
# I am beginning to hate all the options.
sub findUsers {
my($self, $form, $start, $limit, $sort, $with_journal) = @_;
# userSearch REALLY doesn't need to be ordered by keyword since you
# only care if the substring is found.
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $constants = getCurrentStatic();
my $columns = 'fakeemail,nickname,users.uid as uid,journal_last_entry_date ';
$columns .= ", TRUNCATE( " . $self->_score('nickname', $form->{query}, $constants->{search_method}) . ", 1) as score "
if $form->{query};
my $key = " MATCH (nickname) AGAINST ($query) ";
my $tables = 'users';
my $where .= ' seclev > 0 ';
$where .= " AND $key"
if $form->{query};
$where .= " AND journal_last_entry_date IS NOT NULL"
if $with_journal;
my $other;
$other .= " HAVING score > 0 "
if $form->{query};
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score "
} else {
$other .= " ORDER BY users.uid "
}
$other .= " LIMIT $start, $limit" if $limit;
my $users = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other );
return $users;
}
####################################################################################
sub findStory {
my($self, $form, $start, $limit, $sort) = @_;
$start ||= 0;
my $constants = getCurrentStatic();
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $columns;
$columns .= "title, stories.stoid AS stoid, sid, ";
$columns .= "time, commentcount, stories.primaryskid AS skid, ";
$columns .= "introtext, ";
$columns .= "TRUNCATE((( " . $self->_score('title', $form->{query}, $constants->{search_method}) . " + " . $self->_score('introtext,bodytext', $form->{query}, $constants->{search_method}) .") / 2), 1) as score "
if $form->{query};
my $tables = "stories, story_text";
my $other;
$other .= " HAVING score > 0 "
if $form->{query};
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC";
} else {
$other .= " ORDER BY time DESC";
}
# The big old searching WHERE clause, fear it
# XXX This can be a single MATCH now if we do a FULLTEXT
# index on all three columns. - Jamie 2004/04/06
my $where = "stories.stoid = story_text.stoid ";
$where .= " AND ( MATCH (title) AGAINST ($query)
OR MATCH (introtext,bodytext) AGAINST ($query) ) "
if $form->{query};
$where .= " AND time < NOW() AND stories.in_trash = 'no' AND primaryskid != 0 ";
$where .= " AND stories.uid=" . $self->sqlQuote($form->{author})
if $form->{author};
$where .= " AND stories.submitter=" . $self->sqlQuote($form->{submitter})
if $form->{submitter};
my $gSkin = getCurrentSkin();
my $reader = getObject('Slash::DB', { db_type => 'reader' });
my $skin = $reader->getSkin($form->{section} || $gSkin->{skid});
if ($skin->{skid} != $constants->{mainpage_skid}) {
# XXXSKIN this is wrong, we want to join on story_topics_rendered
# by putting $skin->{nexus} into the list of tids we demand
$where .= " AND stories.primaryskid = $skin->{skid}";
}
# Here we find the possible sids that could have this tid and
# then search only those.
# ...but there are two ways to do this. The proper way is to
# do a "LEFT JOIN story_topics ON stories.sid=story_topics.sid" and
# put a "story_topics.id IS NOT NULL" into the WHERE clause. But
# my guess is that on the searches by the larger topics, this will
# be too slow. The other way (which we have done so far) is to do
# one select to pull out *all* sids with the topic(s) in question,
# and then not join on story_topics, just use a "sid IN" clause.
# The problem is that, for large topics, this may be very many sids;
# on OSDN sites, we're seeing some topics with 4,000 to 13,000
# stories in them. That makes the SELECT too large to be efficient.
# So I'm fixing this in a not very good way: limiting the number
# of stories we search, on any search that includes a topic
# limitation. This sucks and should be replaced with a real
# solution ASAP -- this is only a stopgap! - Jamie 2003/11/10
#
# Changed sorting of story sids by time instead of sid. This prevents
# only dated stories from showing up when there were > 1000
# sids that were created prior to 2000
#
# Added support for more correct left join method. Also added vars
# so you can choose to lose left join method or change the limit on
# sids returned in the two select method
#
# Tweak to your site size and performance needs
#
#-- Vroom 2003/12/08
if ($form->{tid}) {
my @tids;
if (ref($form->{_multi}{tid}) eq 'ARRAY') {
push @tids, @{$form->{_multi}{tid}};
} else {
push @tids, $form->{tid};
}
my $string = join(',', @{$self->sqlQuote(\@tids)});
if ($constants->{topic_search_use_join}) {
$tables.= " LEFT JOIN story_topics_rendered ON stories.stoid = story_topics_rendered.stoid ";
# XXXSKIN - no more id in schema, just yank?
# $where .= " AND story_topics_rendered.id IS NOT NULL";
$where .= " AND story_topics_rendered.tid IN ($string)";
$other = "GROUP by stoid $other";
} else {
my $topic_search_sid_limit = $constants->{topic_search_sid_limit} || 1000;
my $sids = $self->sqlSelectColArrayref(
'story_topics_rendered.stoid',
'story_topics_rendered, stories',
"story_topics_rendered.stoid = stories.stoid AND story_topics_rendered.tid IN ($string)",
"ORDER BY time DESC LIMIT $topic_search_sid_limit");
if ($sids && @$sids) {
$string = join(',', @{$self->sqlQuote($sids)});
$where .= " AND stories.stoid IN ($string) ";
} else {
return; # No results could possibly match
}
}
}
$other .= " LIMIT $start, $limit" if $limit;
my $stories = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other);
# Don't return just one topic id in tid, make it an arrayref
# to all topic ids -- in the preferred order.
for my $story (@$stories) {
$story->{tid} = $reader->getTopiclistForStory($story->{stoid});
}
return $stories;
}
################################################################################
# Dead code at the moment -Brian
sub findRetrieveSite {
# my($self, $query, $start, $limit, $sort) = @_;
# $query = $self->sqlQuote($query);
# $limit = " LIMIT $start, $limit" if $limit;
#
# # Welcome to the join from hell -Brian
# my $sql = " SELECT bid,title, MATCH (description,title,block) AGAINST($query) as score FROM blocks WHERE rdf IS NOT NULL AND url IS NOT NULL and retrieve=1 AND MATCH (description,title,block) AGAINST ($query) $limit";
#
#
# $self->sqlConnect();
# my $cursor = $self->{_dbh}->prepare($sql);
# $cursor->execute;
#
# my $search = $cursor->fetchall_arrayref;
# return $search;
}
####################################################################################
sub findJournalEntry {
my($self, $form, $start, $limit, $sort) = @_;
$start ||= 0;
my $constants = getCurrentStatic();
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $columns;
$columns .= "users.nickname as nickname, journals.description as description, ";
$columns .= "journals.id as id, date, users.uid as uid, article";
$columns .= ", TRUNCATE((( " . $self->_score('description', $form->{query}, $constants->{search_method}) . " + " . $self->_score('article', $form->{query}, $constants->{search_method}) .") / 2), 1) as score "
if $form->{query};
my $tables = "journals, journals_text, users";
my $other;
$other .= " HAVING score > 0 "
if ($form->{query});
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC";
} else {
$other .= " ORDER BY date DESC";
}
# The big old searching WHERE clause, fear it
my $key = " (MATCH (description) AGAINST ($query) or MATCH (article) AGAINST ($query)) ";
my $where = "journals.id = journals_text.id AND journals.uid = users.uid ";
$where .= " AND $key" if $form->{query};
$where .= " AND users.nickname=" . $self->sqlQuote($form->{nickname})
if $form->{nickname};
$where .= " AND users.uid=" . $self->sqlQuote($form->{uid})
if $form->{uid};
$where .= " AND tid=" . $self->sqlQuote($form->{tid})
if $form->{tid};
$other .= " LIMIT $start, $limit" if $limit;
my $stories = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other );
return $stories;
}
####################################################################################
sub findPollQuestion {
my($self, $form, $start, $limit, $sort) = @_;
$start ||= 0;
my $constants = getCurrentStatic();
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $columns = "*";
$columns .= ", TRUNCATE( " . $self->_score('question', $form->{query}, $constants->{search_method}) . ", 1) as score "
if $form->{query};
my $tables = "pollquestions";
my $other;
$other .= " HAVING score > 0 "
if ($form->{query});
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC";
} else {
$other .= " ORDER BY date DESC";
}
# The big old searching WHERE clause, fear it
my $key = " MATCH (question) AGAINST ($query) ";
my $where = " 1 = 1 AND autopoll = 'no' ";
$where .= " AND $key" if $form->{query};
$where .= " AND date < now() ";
$where .= " AND uid=" . $self->sqlQuote($form->{uid})
if $form->{uid};
$where .= " AND topic=" . $self->sqlQuote($form->{tid})
if $form->{tid};
my $reader = getObject('Slash::DB', { db_type => 'reader' });
my $skid = $reader->getSkidFromName($form->{section});
$where .= " AND pollquestions.primaryskid = " . $skid if $skid;
my $sql = "SELECT $columns FROM $tables WHERE $where $other";
$other .= " LIMIT $start, $limit" if $limit;
my $stories = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other);
return $stories;
}
####################################################################################
sub findSubmission {
my($self, $form, $start, $limit, $sort) = @_;
$start ||= 0;
my $constants = getCurrentStatic();
my $reader = getObject('Slash::DB', { db_type => 'reader' });
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $columns = "*";
$columns .= ", TRUNCATE( " . $self->_score('subj,story', $form->{query}, $constants->{search_method}) . ", 1) as score "
if $form->{query};
my $tables = "submissions";
my $other;
$other .= " HAVING score > 0 "
if ($form->{query});
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC";
} else {
$other .= " ORDER BY subid DESC";
}
# The big old searching WHERE clause, fear it
my $key = " MATCH (subj,story) AGAINST ($query) ";
my $where = " 1 = 1 ";
$where .= " AND $key" if $form->{query};
$where .= " AND uid=" . $self->sqlQuote($form->{uid})
if $form->{uid};
# XXXSKIN - needs to be replaced with select on submission_topics_rendered
# not sure why need to have multiple topics for submissions though ...
# regardless, tid must end up as an arrayref now, in $stories
# $where .= " AND tid=" . $self->sqlQuote($form->{tid})
# if $form->{tid};
$where .= " AND note=" . $self->sqlQuote($form->{note})
if $form->{note};
my $skid = $reader->getSkidFromName($form->{section});
$where .= " AND primaryskid=$skid" if $skid;
$other .= " LIMIT $start, $limit" if $limit;
my $stories = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other );
return $stories;
}
####################################################################################
sub findRSS {
my($self, $form, $start, $limit, $sort) = @_;
$start ||= 0;
my $constants = getCurrentStatic();
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $columns = "title, link, description, created";
$columns .= ", TRUNCATE( " . $self->_score('title,description', $form->{query}, $constants->{search_method}) . ", 1) as score "
if $form->{query};
my $tables = "rss_raw";
my $other;
$other .= " HAVING score > 0 "
if ($form->{query});
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC";
} else {
$other .= " ORDER BY created DESC";
}
# The big old searching WHERE clause, fear it
my $key = " MATCH (title,description) AGAINST ($query) ";
my $where = " 1 = 1 ";
$where .= " AND $key" if $form->{query};
$where .= " AND bid=" . $self->sqlQuote($form->{bid})
if $form->{bid};
$other .= " LIMIT $start, $limit" if $limit;
my $stories = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other );
return $stories;
}
####################################################################################
sub findDiscussion {
my($self, $form, $start, $limit, $sort) = @_;
$form->{query} = $self->_cleanQuery($form->{query});
my $query = $self->sqlQuote($form->{query});
my $constants = getCurrentStatic();
$start ||= 0;
my $columns = "*";
$columns .= ", TRUNCATE( " . $self->_score('title', $form->{query}, $constants->{search_method}) . ", 1) as score "
if $form->{query};
my $tables = "discussions";
my $other;
$other .= " HAVING score > 0 "
if ($form->{query});
if ($form->{query} && $sort == 2) {
$other .= " ORDER BY score DESC";
} elsif ($sort == 3) {
$other .= " ORDER BY last_update DESC";
} else {
$other .= " ORDER BY ts DESC";
}
# The big old searching WHERE clause, fear it
my $key = " MATCH (title) AGAINST ($query) ";
my $where = " ts <= now() ";
$where .= " AND $key"
if $form->{query};
$where .= " AND type=" . $self->sqlQuote($form->{type})
if $form->{type};
$where .= " AND topic=" . $self->sqlQuote($form->{tid})
if $form->{tid};
my $gSkin = getCurrentSkin();
my $reader = getObject('Slash::DB', { db_type => 'reader' });
my $skin = $reader->getSkin($form->{section} || $gSkin->{skid});
if ($skin->{skid} != $constants->{mainpage_skid}) {
$where .= " AND discussions.primaryskid = $skin->{skid}";
}
$where .= " AND uid=" . $self->sqlQuote($form->{uid})
if $form->{uid};
$where .= " AND approved = " . $self->sqlQuote($form->{approved})
if defined($form->{approved})
&& $constants->{discussion_approval};
$other .= " LIMIT $start, $limit" if $limit;
# print STDERR "select $columns from $tables where $where $other\n";
my $stories = $self->sqlSelectAllHashrefArray($columns, $tables, $where, $other );
return $stories;
}
sub _score {
my ($self, $col, $query, $method) = @_;
my $constants = getCurrentStatic();
if ($method) {
# We were getting malformed SQL queries with the previous
# way this was done, so I made it a bit more robust. If
# no search terms are passed in with $query, instead of
# crashing it returns "0" which of course will assign a
# score of 0 to all hits. I'd prefer to see the callers
# massage $form->{query} themselves, stripping leading
# and trailing spaces before passing it in here, but this
# will do for now. - Jamie 2002/10/20
# Nope, the caller should be unaware of all of this. We can extend
# and use different methods for searching and never have to modify
# all of the above code. -Brian
my @terms = ( );
for my $term (split / /, $query) {
$term =~ /^\s*(.*?)\s*$/;
$term = $1;
next unless $term;
push @terms, $self->sqlQuote($term);
}
my @cols = ();
for my $c (split /,/, $col) {
$c =~ /^\s*(.*?)\s*$/;
$c = $1;
next unless $c;
push @cols, $c;
}
return "0" if !@terms || !@cols;
my $terms = join(",", @terms);
if ($method eq "scour") {
# This is a fix to do separate SCOUR()s on each
# column; it only applies if your mysqld is set
# up to use Brian's special function.
my @scour = map { $_ = "($method($_, $terms))" } @cols;
my $scour = join " + ", @scour;
my $n_scour = scalar(@scour);
$scour = "( ( $scour ) / $n_scour )" if $n_scour > 1;
return $scour;
}
return "($method($col, $terms))";
} else {
$query = $self->sqlQuote($query);
return "\n(MATCH ($col) AGAINST ($query))\n";
}
}
#################################################################
sub _cleanQuery {
my ($self, $query) = @_;
# This next line could be removed -Brian
# get rid of bad characters
$query =~ s/[^A-Z0-9'. :\/_]/ /gi;
# This should be configurable -Brian
# truncate query length
if (length($query) > 40) {
$query = substr($query, 0, 40);
}
return $query;
}
#################################################################
sub DESTROY {
my($self) = @_;
$self->{_dbh}->disconnect if $self->{_dbh} && !$ENV{GATEWAY_INTERFACE};
}
1;
# Below is the stub of documentation for your module. You better edit it!
=head1 NAME
Slash::Search - Slash Search module
=head1 SYNOPSIS
use Slash::Search;
=head1 DESCRIPTION
Slash search module.
Blah blah blah.
=head1 SEE ALSO
Slash(3).
=cut