1# --
2# Copyright (C) 2001-2020 OTRS AG, https://otrs.com/
3# --
4# This software comes with ABSOLUTELY NO WARRANTY. For details, see
5# the enclosed file COPYING for license information (GPL). If you
6# did not receive this file, see https://www.gnu.org/licenses/gpl-3.0.txt.
7# --
8
9package Kernel::System::Ticket::ArticleSearchIndex::DB;
10
11use strict;
12use warnings;
13
14use Kernel::System::VariableCheck qw(:all);
15
16our @ObjectDependencies = (
17    'Kernel::Config',
18    'Kernel::System::DB',
19    'Kernel::System::Log',
20    'Kernel::System::Ticket::Article',
21);
22
23=head1 NAME
24
25Kernel::System::Ticket::ArticleSearchIndex::DB - DB based ticket article search index module
26
27=head1 DESCRIPTION
28
29This class provides functions to index articles for searching in the database.
30The methods are currently documented in L<Kernel::System::Ticket::Article>.
31
32=cut
33
34sub new {
35    my ( $Type, %Param ) = @_;
36
37    my $Self = {};
38    bless( $Self, $Type );
39
40    return $Self;
41}
42
43sub ArticleSearchIndexBuild {
44    my ( $Self, %Param ) = @_;
45
46    for my $Needed (qw(TicketID ArticleID UserID)) {
47        if ( !$Param{$Needed} ) {
48            $Kernel::OM->Get('Kernel::System::Log')->Log(
49                Priority => 'error',
50                Message  => "Need $Needed!"
51            );
52            return;
53        }
54    }
55
56    my $ArticleBackendObject = $Kernel::OM->Get('Kernel::System::Ticket::Article')->BackendForArticle(
57        TicketID  => $Param{TicketID},
58        ArticleID => $Param{ArticleID},
59    );
60
61    my %ArticleSearchableContent = $ArticleBackendObject->ArticleSearchableContentGet(
62        TicketID  => $Param{TicketID},
63        ArticleID => $Param{ArticleID},
64        UserID    => $Param{UserID},
65    );
66
67    return 1 if !%ArticleSearchableContent;
68
69    # clear old data from search index table
70    my $Success = $Self->ArticleSearchIndexDelete(
71        ArticleID => $Param{ArticleID},
72        UserID    => $Param{UserID},
73    );
74
75    if ( !$Success ) {
76        $Kernel::OM->Get('Kernel::System::Log')->Log(
77            Priority => 'error',
78            Message  => "Could not delete ArticleID '$Param{ArticleID}' from article search index!"
79        );
80        return;
81    }
82
83    my $ForceUnfilteredStorage = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::ForceUnfilteredStorage')
84        // 0;
85
86    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
87
88    # Use regular multi-inserts for MySQL and PostgreSQL:
89    # INSERT INTO table (field1, field2) VALUES (?, ?), (?, ?);
90    my $SQLStart  = 'INSERT INTO article_search_index (ticket_id, article_id, article_key, article_value) VALUES ';
91    my $SQLInsert = '(?, ?, ?, ?) ';
92    my $SQLInsertConnector = ', ';
93    my $SQLEnd             = '';
94
95    # Oracle has a special syntax:
96    # INSERT ALL
97    #   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
98    #   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
99    #   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
100    # SELECT * FROM dual;
101    if ( lc $DBObject->GetDatabaseFunction('Type') eq 'oracle' ) {
102        $SQLStart  = 'INSERT ALL ';
103        $SQLInsert = '
104            INTO article_search_index (
105                ticket_id, article_id, article_key, article_value
106            )
107            VALUES (?, ?, ?, ?) ';
108        $SQLInsertConnector = ' ';
109        $SQLEnd             = 'SELECT * FROM DUAL';
110    }
111
112    my $SQL = $SQLStart;
113    my $Counter;
114    my @Bind;
115
116    for my $FieldKey ( sort keys %ArticleSearchableContent ) {
117
118        if (
119            !$ForceUnfilteredStorage
120            && $ArticleSearchableContent{$FieldKey}->{Filterable}
121            )
122        {
123            $ArticleSearchableContent{$FieldKey}->{String} = $Self->_ArticleSearchIndexString(
124                %{ $ArticleSearchableContent{$FieldKey} }
125            );
126        }
127
128        # Indexed content will be saved lowercase, even if it is not filterable to avoid
129        # LOWER() statements on search time, which increases the search performance.
130        # (this will be done automatically on filterable fields)
131        else {
132            $ArticleSearchableContent{$FieldKey}->{String} = lc $ArticleSearchableContent{$FieldKey}->{String};
133        }
134
135        my @CurrentBind = (
136            \$Param{TicketID},
137            \$Param{ArticleID},
138            \$ArticleSearchableContent{$FieldKey}->{Key},
139            \$ArticleSearchableContent{$FieldKey}->{String},
140        );
141
142        $SQL .= $SQLInsertConnector if $Counter++;
143        $SQL .= $SQLInsert;
144        push @Bind, @CurrentBind;
145    }
146
147    $SQL .= $SQLEnd;
148
149    return if !$DBObject->Do(
150        SQL  => $SQL,
151        Bind => \@Bind,
152    );
153
154    return 1;
155}
156
157sub ArticleSearchIndexDelete {
158    my ( $Self, %Param ) = @_;
159
160    if ( !$Param{UserID} ) {
161        $Kernel::OM->Get('Kernel::System::Log')->Log(
162            Priority => 'error',
163            Message  => 'Need UserID!',
164        );
165        return;
166    }
167
168    if ( !$Param{ArticleID} && !$Param{TicketID} ) {
169        $Kernel::OM->Get('Kernel::System::Log')->Log(
170            Priority => 'error',
171            Message  => 'Need either ArticleID or TicketID!',
172        );
173        return;
174    }
175
176    # Delete articles.
177    if ( $Param{ArticleID} ) {
178        return if !$Kernel::OM->Get('Kernel::System::DB')->Do(
179            SQL  => 'DELETE FROM article_search_index WHERE article_id = ?',
180            Bind => [ \$Param{ArticleID} ],
181        );
182    }
183    elsif ( $Param{TicketID} ) {
184        return if !$Kernel::OM->Get('Kernel::System::DB')->Do(
185            SQL  => 'DELETE FROM article_search_index WHERE ticket_id = ?',
186            Bind => [ \$Param{TicketID} ],
187        );
188    }
189
190    return 1;
191}
192
193sub ArticleSearchIndexSQLJoinNeeded {
194    my ( $Self, %Param ) = @_;
195
196    if ( !$Param{SearchParams} ) {
197        $Kernel::OM->Get('Kernel::System::Log')->Log(
198            Priority => 'error',
199            Message  => 'Need SearchParams!',
200        );
201        return;
202    }
203
204    my %SearchableFields = $Kernel::OM->Get('Kernel::System::Ticket::Article')->ArticleSearchableFieldsList();
205
206    for my $Field (
207        sort keys %SearchableFields,
208        qw(
209        ArticleCreateTimeOlderMinutes ArticleCreateTimeNewerMinutes
210        ArticleCreateTimeOlderDate ArticleCreateTimeNewerDate Fulltext
211        )
212        )
213    {
214        if ( IsStringWithData( $Param{SearchParams}->{$Field} ) ) {
215            return 1;
216        }
217    }
218
219    return;
220}
221
222sub ArticleSearchIndexSQLJoin {
223    my ( $Self, %Param ) = @_;
224
225    if ( !$Param{SearchParams} ) {
226        $Kernel::OM->Get('Kernel::System::Log')->Log(
227            Priority => 'error',
228            Message  => 'Need SearchParams!',
229        );
230        return;
231    }
232
233    my $ArticleSearchIndexSQLJoin = ' ';
234
235    # join article search table for fulltext searches
236    if ( IsStringWithData( $Param{SearchParams}->{Fulltext} ) ) {
237        $ArticleSearchIndexSQLJoin
238            .= 'LEFT JOIN article_search_index ArticleFulltext ON art.id = ArticleFulltext.article_id ';
239    }
240
241    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
242
243    # Run through all article fields, that have assigned values and add additional LEFT JOINS
244    # to the string, to access them later for the conditions.
245    my %SearchableFields = $Kernel::OM->Get('Kernel::System::Ticket::Article')->ArticleSearchableFieldsList();
246
247    ARTICLEFIELD:
248    for my $ArticleField ( sort keys %SearchableFields ) {
249
250        next ARTICLEFIELD if !IsStringWithData( $Param{SearchParams}->{$ArticleField} );
251
252        my $Label = $ArticleField;
253        $ArticleField = $DBObject->Quote($ArticleField);
254
255        $ArticleSearchIndexSQLJoin
256            .= "LEFT JOIN article_search_index $Label ON art.id = $Label.article_id AND $Label.article_key = '$ArticleField' ";
257    }
258
259    return $ArticleSearchIndexSQLJoin;
260}
261
262sub ArticleSearchIndexWhereCondition {
263    my ( $Self, %Param ) = @_;
264
265    if ( !$Param{SearchParams} ) {
266        $Kernel::OM->Get('Kernel::System::Log')->Log(
267            Priority => 'error',
268            Message  => 'Need SearchParams!',
269        );
270        return;
271    }
272
273    # get database object
274    my $DBObject = $Kernel::OM->Get('Kernel::System::DB');
275
276    my $SQLCondition = '';
277    my $SQLQuery     = '';
278
279    my %SearchableFields = $Kernel::OM->Get('Kernel::System::Ticket::Article')->ArticleSearchableFieldsList();
280    my @Fields           = keys %SearchableFields;
281
282    push @Fields, 'Fulltext' if IsStringWithData( $Param{SearchParams}->{Fulltext} );
283
284    FIELD:
285    for my $Field (@Fields) {
286
287        next FIELD if !IsStringWithData( $Param{SearchParams}->{$Field} );
288
289        # replace * by % for SQL like
290        $Param{SearchParams}->{$Field} =~ s/\*/%/gi;
291
292        # check search attribute, we do not need to search for *
293        next FIELD if $Param{SearchParams}->{$Field} =~ /^\%{1,3}$/;
294
295        if ($SQLQuery) {
296            $SQLQuery .= ' ' . $Param{SearchParams}->{ContentSearch} . ' ';
297        }
298
299        # check if search condition extension is used
300        if ( $Param{SearchParams}->{ConditionInline} ) {
301
302            $SQLQuery .= $DBObject->QueryCondition(
303                Key => $Field eq 'Fulltext' ? [ 'ArticleFulltext.article_value', 'st.title' ] : "$Field.article_value",
304                Value         => lc $Param{SearchParams}->{$Field},
305                SearchPrefix  => $Param{SearchParams}->{ContentSearchPrefix},
306                SearchSuffix  => $Param{SearchParams}->{ContentSearchSuffix},
307                Extended      => 1,
308                CaseSensitive => 1,
309            );
310        }
311        else {
312
313            my $Label = $Field eq 'Fulltext' ? 'ArticleFulltext' : $Field;
314            my $Value = $Param{SearchParams}->{$Field};
315
316            if ( $Param{SearchParams}->{ContentSearchPrefix} ) {
317                $Value = $Param{SearchParams}->{ContentSearchPrefix} . $Value;
318            }
319            if ( $Param{SearchParams}->{ContentSearchSuffix} ) {
320                $Value .= $Param{SearchParams}->{ContentSearchSuffix};
321            }
322
323            # replace * with % (for SQL)
324            $Value =~ s/\*/%/g;
325
326            # replace %% by % for SQL
327            $Value =~ s/%%/%/gi;
328
329            $Value = lc $DBObject->Quote( $Value, 'Like' );
330
331            $SQLQuery .= " $Label.article_value LIKE '$Value'";
332
333            if ( $Field eq 'Fulltext' ) {
334                $SQLQuery .= " OR st.title LIKE '$Value'";
335            }
336        }
337    }
338
339    if ($SQLQuery) {
340        $SQLCondition = ' AND (' . $SQLQuery . ') ';
341    }
342
343    return $SQLCondition;
344}
345
346sub SearchStringStopWordsFind {
347    my ( $Self, %Param ) = @_;
348
349    # check needed stuff
350    for my $Key (qw(SearchStrings)) {
351        if ( !$Param{$Key} ) {
352            $Kernel::OM->Get('Kernel::System::Log')->Log(
353                Priority => 'error',
354                Message  => "Need $Key!",
355            );
356            return;
357        }
358    }
359
360    my $StopWordRaw = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::StopWords') || {};
361    if ( !$StopWordRaw || ref $StopWordRaw ne 'HASH' ) {
362
363        $Kernel::OM->Get('Kernel::System::Log')->Log(
364            Priority => 'error',
365            Message  => "Invalid config option Ticket::SearchIndex::StopWords! "
366                . "Please reset the search index options to reactivate the factory defaults.",
367        );
368
369        return;
370    }
371
372    my %StopWord;
373    LANGUAGE:
374    for my $Language ( sort keys %{$StopWordRaw} ) {
375
376        if ( !$Language || !$StopWordRaw->{$Language} || ref $StopWordRaw->{$Language} ne 'ARRAY' ) {
377
378            $Kernel::OM->Get('Kernel::System::Log')->Log(
379                Priority => 'error',
380                Message  => "Invalid config option Ticket::SearchIndex::StopWords###$Language! "
381                    . "Please reset this option to reactivate the factory defaults.",
382            );
383
384            next LANGUAGE;
385        }
386
387        WORD:
388        for my $Word ( @{ $StopWordRaw->{$Language} } ) {
389
390            next WORD if !defined $Word || !length $Word;
391
392            $Word = lc $Word;
393
394            $StopWord{$Word} = 1;
395        }
396    }
397
398    my $SearchIndexAttributes = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::Attribute');
399    my $WordLengthMin         = $SearchIndexAttributes->{WordLengthMin} || 3;
400    my $WordLengthMax         = $SearchIndexAttributes->{WordLengthMax} || 30;
401
402    my %StopWordsFound;
403    SEARCHSTRING:
404    for my $Key ( sort keys %{ $Param{SearchStrings} } ) {
405        my $SearchString = $Param{SearchStrings}->{$Key};
406        my %Result       = $Kernel::OM->Get('Kernel::System::DB')->QueryCondition(
407            'Key'      => '.',             # resulting SQL is irrelevant
408            'Value'    => $SearchString,
409            'BindMode' => 1,
410        );
411
412        next SEARCHSTRING if !%Result || ref $Result{Values} ne 'ARRAY' || !@{ $Result{Values} };
413
414        my %Words;
415        for my $Value ( @{ $Result{Values} } ) {
416            my @Words = split '\s+', $$Value;
417            for my $Word (@Words) {
418                $Words{ lc $Word } = 1;
419            }
420        }
421
422        @{ $StopWordsFound{$Key} }
423            = grep { $StopWord{$_} || length $_ < $WordLengthMin || length $_ > $WordLengthMax } sort keys %Words;
424    }
425
426    return \%StopWordsFound;
427}
428
429sub SearchStringStopWordsUsageWarningActive {
430    my ( $Self, %Param ) = @_;
431
432    my $WarnOnStopWordUsage = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::WarnOnStopWordUsage') || 0;
433
434    return 1 if $WarnOnStopWordUsage;
435
436    return 0;
437}
438
439sub _ArticleSearchIndexString {
440    my ( $Self, %Param ) = @_;
441
442    if ( !defined $Param{String} ) {
443        $Kernel::OM->Get('Kernel::System::Log')->Log(
444            Priority => 'error',
445            Message  => "Need String!",
446        );
447        return;
448    }
449
450    my $SearchIndexAttributes = $Kernel::OM->Get('Kernel::Config')->Get('Ticket::SearchIndex::Attribute');
451
452    my $WordCountMax = $SearchIndexAttributes->{WordCountMax} || 1000;
453
454    # get words (use eval to prevend exits on damaged utf8 signs)
455    my $ListOfWords = eval {
456        $Self->_ArticleSearchIndexStringToWord(
457            String        => \$Param{String},
458            WordLengthMin => $Param{WordLengthMin} || $SearchIndexAttributes->{WordLengthMin} || 3,
459            WordLengthMax => $Param{WordLengthMax} || $SearchIndexAttributes->{WordLengthMax} || 30,
460        );
461    };
462
463    return if !$ListOfWords;
464
465    # find ranking of words
466    my %List;
467    my $IndexString = '';
468    my $Count       = 0;
469    WORD:
470    for my $Word ( @{$ListOfWords} ) {
471
472        $Count++;
473
474        # only index the first 1000 words
475        last WORD if $Count > $WordCountMax;
476
477        if ( $List{$Word} ) {
478
479            $List{$Word}++;
480
481            next WORD;
482        }
483        else {
484
485            $List{$Word} = 1;
486
487            if ($IndexString) {
488                $IndexString .= ' ';
489            }
490
491            $IndexString .= $Word;
492        }
493    }
494
495    return $IndexString;
496}
497
498sub _ArticleSearchIndexStringToWord {
499    my ( $Self, %Param ) = @_;
500
501    if ( !defined $Param{String} ) {
502        $Kernel::OM->Get('Kernel::System::Log')->Log(
503            Priority => 'error',
504            Message  => "Need String!",
505        );
506        return;
507    }
508
509    # get config object
510    my $ConfigObject = $Kernel::OM->Get('Kernel::Config');
511
512    my $SearchIndexAttributes = $ConfigObject->Get('Ticket::SearchIndex::Attribute');
513    my @Filters               = @{ $ConfigObject->Get('Ticket::SearchIndex::Filters') || [] };
514    my $StopWordRaw           = $ConfigObject->Get('Ticket::SearchIndex::StopWords') || {};
515
516    # error handling
517    if ( !$StopWordRaw || ref $StopWordRaw ne 'HASH' ) {
518
519        $Kernel::OM->Get('Kernel::System::Log')->Log(
520            Priority => 'error',
521            Message  => "Invalid config option Ticket::SearchIndex::StopWords! "
522                . "Please reset the search index options to reactivate the factory defaults.",
523        );
524
525        return;
526    }
527
528    my %StopWord;
529    LANGUAGE:
530    for my $Language ( sort keys %{$StopWordRaw} ) {
531
532        if ( !$Language || !$StopWordRaw->{$Language} || ref $StopWordRaw->{$Language} ne 'ARRAY' ) {
533
534            $Kernel::OM->Get('Kernel::System::Log')->Log(
535                Priority => 'error',
536                Message  => "Invalid config option Ticket::SearchIndex::StopWords###$Language! "
537                    . "Please reset this option to reactivate the factory defaults.",
538            );
539
540            next LANGUAGE;
541        }
542
543        WORD:
544        for my $Word ( @{ $StopWordRaw->{$Language} } ) {
545
546            next WORD if !defined $Word || !length $Word;
547
548            $Word = lc $Word;
549
550            $StopWord{$Word} = 1;
551        }
552    }
553
554    # get words
555    my $LengthMin = $Param{WordLengthMin} || $SearchIndexAttributes->{WordLengthMin} || 3;
556    my $LengthMax = $Param{WordLengthMax} || $SearchIndexAttributes->{WordLengthMax} || 30;
557    my @ListOfWords;
558
559    WORD:
560    for my $Word ( split /\s+/, ${ $Param{String} } ) {
561
562        # apply filters
563        FILTER:
564        for my $Filter (@Filters) {
565            next FILTER if !defined $Word || !length $Word;
566            $Word =~ s/$Filter//g;
567        }
568
569        next WORD if !defined $Word || !length $Word;
570
571        # convert to lowercase to avoid LOWER()/LCASE() in the DB query
572        $Word = lc $Word;
573
574        next WORD if $StopWord{$Word};
575
576        # only index words/strings within length boundaries
577        my $Length = length $Word;
578
579        next WORD if $Length < $LengthMin;
580        next WORD if $Length > $LengthMax;
581
582        push @ListOfWords, $Word;
583    }
584
585    return \@ListOfWords;
586}
587
5881;
589