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