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
9use strict;
10use warnings;
11use utf8;
12
13use vars (qw($Self));
14
15# get needed objects
16my $DBObject  = $Kernel::OM->Get('Kernel::System::DB');
17my $XMLObject = $Kernel::OM->Get('Kernel::System::XML');
18
19# ------------------------------------------------------------ #
20# QueryCondition tests
21# ------------------------------------------------------------ #
22my $XML = '
23<TableCreate Name="test_condition">
24    <Column Name="name_a" Required="true" Size="60" Type="VARCHAR"/>
25    <Column Name="name_b" Required="true" Size="60" Type="VARCHAR"/>
26</TableCreate>
27';
28my @XMLARRAY = $XMLObject->XMLParse( String => $XML );
29my @SQL      = $DBObject->SQLProcessor( Database => \@XMLARRAY );
30$Self->True(
31    $SQL[0],
32    '#8 SQLProcessor() CREATE TABLE',
33);
34
35for my $SQL (@SQL) {
36    $Self->True(
37        $DBObject->Do( SQL => $SQL ) || 0,
38        "#8 Do() CREATE TABLE ($SQL)",
39    );
40}
41
42my %Fill = (
43    Some1  => 'John Smith',
44    Some2  => 'John Meier',
45    Some3  => 'Franz Smith',
46    Some4  => 'Franz Ferdinand Smith',
47    Some5  => 'customer_id_with_underscores',
48    Some6  => 'customer&id&with&ampersands',
49    Some7  => 'Test (with) (brackets)',
50    Some8  => 'Test (with) (brackets) and & and |',
51    Some9  => 'Test for franz!gans merged with exclamation mark',
52    Some10 => 'customer & id with ampersand & spaces',
53    Some11 => 'Test with single quotes \'test\'',
54);
55for my $Key ( sort keys %Fill ) {
56    my $SQL = "INSERT INTO test_condition (name_a, name_b) VALUES (?, ?)";
57    my $Do  = $DBObject->Do(
58        SQL  => $SQL,
59        Bind => [
60            \$Key,
61            \$Fill{$Key},
62        ],
63    );
64    $Self->True(
65        $Do,
66        "#8 Do() INSERT ($SQL)",
67    );
68}
69my @Queries = (
70    {
71        Query  => 'franz ferdinand',
72        Result => {
73            Some1 => 0,
74            Some2 => 0,
75            Some3 => 0,
76            Some4 => 1,
77            Some5 => 0,
78            Some6 => 0,
79            Some7 => 0,
80            Some8 => 0,
81        }
82    },
83    {
84        Query  => 'john+smith',
85        Result => {
86            Some1 => 1,
87            Some2 => 0,
88            Some3 => 0,
89            Some4 => 0,
90            Some5 => 0,
91            Some6 => 0,
92            Some7 => 0,
93            Some8 => 0,
94        },
95    },
96    {
97        Query  => 'john+smith+ ',
98        Result => {
99            Some1 => 1,
100            Some2 => 0,
101            Some3 => 0,
102            Some4 => 0,
103            Some5 => 0,
104            Some6 => 0,
105            Some7 => 0,
106            Some8 => 0,
107        },
108    },
109    {
110        Query  => 'john+smith+',
111        Result => {
112            Some1 => 1,
113            Some2 => 0,
114            Some3 => 0,
115            Some4 => 0,
116            Some5 => 0,
117            Some6 => 0,
118            Some7 => 0,
119            Some8 => 0,
120        },
121    },
122    {
123        Query  => '+john+smith',
124        Result => {
125            Some1 => 1,
126            Some2 => 0,
127            Some3 => 0,
128            Some4 => 0,
129            Some5 => 0,
130            Some6 => 0,
131            Some7 => 0,
132            Some8 => 0,
133        },
134    },
135    {
136        Query  => '(john+smith)',
137        Result => {
138            Some1 => 1,
139            Some2 => 0,
140            Some3 => 0,
141            Some4 => 0,
142            Some5 => 0,
143            Some6 => 0,
144            Some7 => 0,
145            Some8 => 0,
146        },
147    },
148    {
149        Query  => '(john+smith)+',
150        Result => {
151            Some1 => 1,
152            Some2 => 0,
153            Some3 => 0,
154            Some4 => 0,
155            Some5 => 0,
156            Some6 => 0,
157            Some7 => 0,
158            Some8 => 0,
159        },
160    },
161    {
162        Query  => '(john&&smith)',
163        Result => {
164            Some1 => 1,
165            Some2 => 0,
166            Some3 => 0,
167            Some4 => 0,
168            Some5 => 0,
169            Some6 => 0,
170            Some7 => 0,
171            Some8 => 0,
172        },
173    },
174    {
175        Query  => '(john && smith)',
176        Result => {
177            Some1 => 1,
178            Some2 => 0,
179            Some3 => 0,
180            Some4 => 0,
181            Some5 => 0,
182            Some6 => 0,
183            Some7 => 0,
184            Some8 => 0,
185        },
186    },
187    {
188        Query  => '(john && smi*h)',
189        Result => {
190            Some1 => 1,
191            Some2 => 0,
192            Some3 => 0,
193            Some4 => 0,
194            Some5 => 0,
195            Some6 => 0,
196            Some7 => 0,
197            Some8 => 0,
198        },
199    },
200    {
201        Query  => '(john && smi**h)',
202        Result => {
203            Some1 => 1,
204            Some2 => 0,
205            Some3 => 0,
206            Some4 => 0,
207            Some5 => 0,
208            Some6 => 0,
209            Some7 => 0,
210            Some8 => 0,
211        },
212    },
213    {
214        Query  => '(john||smith)',
215        Result => {
216            Some1 => 1,
217            Some2 => 1,
218            Some3 => 1,
219            Some4 => 1,
220            Some5 => 0,
221            Some6 => 0,
222            Some7 => 0,
223            Some8 => 0,
224        },
225    },
226    {
227        Query  => '(john || smith)',
228        Result => {
229            Some1 => 1,
230            Some2 => 1,
231            Some3 => 1,
232            Some4 => 1,
233            Some5 => 0,
234            Some6 => 0,
235            Some7 => 0,
236            Some8 => 0,
237        },
238    },
239    {
240        Query  => '(smith || john)',
241        Result => {
242            Some1 => 1,
243            Some2 => 1,
244            Some3 => 1,
245            Some4 => 1,
246            Some5 => 0,
247            Some6 => 0,
248            Some7 => 0,
249            Some8 => 0,
250        },
251    },
252    {
253        Query  => '(john AND smith)',
254        Result => {
255            Some1 => 1,
256            Some2 => 0,
257            Some3 => 0,
258            Some4 => 0,
259            Some5 => 0,
260            Some6 => 0,
261            Some7 => 0,
262            Some8 => 0,
263        },
264    },
265    {
266        Query  => '(john AND smith)',
267        Result => {
268            Some1 => 1,
269            Some2 => 0,
270            Some3 => 0,
271            Some4 => 0,
272            Some5 => 0,
273            Some6 => 0,
274            Some7 => 0,
275            Some8 => 0,
276        },
277    },
278    {
279        Query  => '(john AND)',
280        Result => {
281            Some1 => 1,
282            Some2 => 1,
283            Some3 => 0,
284            Some4 => 0,
285            Some5 => 0,
286            Some6 => 0,
287            Some7 => 0,
288            Some8 => 0,
289        },
290    },
291    {
292        Query  => '(franz+)',
293        Result => {
294            Some1 => 0,
295            Some2 => 0,
296            Some3 => 1,
297            Some4 => 1,
298            Some5 => 0,
299            Some6 => 0,
300            Some7 => 0,
301            Some8 => 0,
302        },
303    },
304    {
305        Query  => '((john+smith) OR meier)',
306        Result => {
307            Some1 => 1,
308            Some2 => 1,
309            Some3 => 0,
310            Some4 => 0,
311            Some5 => 0,
312            Some6 => 0,
313            Some7 => 0,
314            Some8 => 0,
315        },
316    },
317    {
318        Query  => '((john1+smith1) OR meier)',
319        Result => {
320            Some1 => 0,
321            Some2 => 1,
322            Some3 => 0,
323            Some4 => 0,
324            Some5 => 0,
325            Some6 => 0,
326            Some7 => 0,
327            Some8 => 0,
328        },
329    },
330    {
331        Query  => 'fritz',
332        Result => {
333            Some1 => 0,
334            Some2 => 0,
335            Some3 => 0,
336            Some4 => 0,
337            Some5 => 0,
338            Some6 => 0,
339            Some7 => 0,
340            Some8 => 0,
341        },
342    },
343    {
344        Query  => '!fritz',
345        Result => {
346            Some1 => 1,
347            Some2 => 1,
348            Some3 => 1,
349            Some4 => 1,
350            Some5 => 1,
351            Some6 => 1,
352            Some7 => 1,
353            Some8 => 1,
354        },
355    },
356    {
357        Query  => '!franz',
358        Result => {
359            Some1 => 1,
360            Some2 => 1,
361            Some3 => 0,
362            Some4 => 0,
363            Some5 => 1,
364            Some6 => 1,
365            Some7 => 1,
366            Some8 => 1,
367        },
368    },
369    {
370        Query  => 'franz!gans',
371        Result => {
372            Some1 => 0,
373            Some2 => 0,
374            Some3 => 0,
375            Some4 => 0,
376            Some5 => 0,
377            Some6 => 0,
378            Some7 => 0,
379            Some8 => 0,
380            Some9 => 1,
381        },
382    },
383    {
384        Query  => '!franz*',
385        Result => {
386            Some1 => 1,
387            Some2 => 1,
388            Some3 => 0,
389            Some4 => 0,
390            Some5 => 1,
391            Some6 => 1,
392            Some7 => 1,
393            Some8 => 1,
394        },
395    },
396    {
397        Query  => '!*franz*',
398        Result => {
399            Some1 => 1,
400            Some2 => 1,
401            Some3 => 0,
402            Some4 => 0,
403            Some5 => 1,
404            Some6 => 1,
405            Some7 => 1,
406            Some8 => 1,
407        },
408    },
409    {
410        Query  => '*!*franz*',
411        Result => {
412            Some1 => 1,
413            Some2 => 1,
414            Some3 => 0,
415            Some4 => 0,
416            Some5 => 1,
417            Some6 => 1,
418            Some7 => 1,
419            Some8 => 1,
420        },
421    },
422    {
423        Query  => '*!franz*',
424        Result => {
425            Some1 => 1,
426            Some2 => 1,
427            Some3 => 0,
428            Some4 => 0,
429            Some5 => 1,
430            Some6 => 1,
431            Some7 => 1,
432            Some8 => 1,
433        },
434    },
435    {
436        Query  => '(!fritz+!bob)',
437        Result => {
438            Some1 => 1,
439            Some2 => 1,
440            Some3 => 1,
441            Some4 => 1,
442            Some5 => 1,
443            Some6 => 1,
444            Some7 => 1,
445            Some8 => 1,
446        },
447    },
448    {
449        Query  => '((!fritz+!bob)+i)',
450        Result => {
451            Some1 => 1,
452            Some2 => 1,
453            Some3 => 1,
454            Some4 => 1,
455            Some5 => 1,
456            Some6 => 1,
457            Some7 => 1,
458            Some8 => 1,
459        },
460    },
461    {
462        Query  => '((john+smith) OR (meier+john))',
463        Result => {
464            Some1 => 1,
465            Some2 => 1,
466            Some3 => 0,
467            Some4 => 0,
468            Some5 => 0,
469            Some6 => 0,
470            Some7 => 0,
471            Some8 => 0,
472        },
473    },
474    {
475        Query  => '((john+smith)OR(meier+john))',
476        Result => {
477            Some1 => 1,
478            Some2 => 1,
479            Some3 => 0,
480            Some4 => 0,
481            Some5 => 0,
482            Some6 => 0,
483            Some7 => 0,
484            Some8 => 0,
485        },
486    },
487    {
488        Query  => '((john+smith)  OR     ( meier+ john))',
489        Result => {
490            Some1 => 1,
491            Some2 => 1,
492            Some3 => 0,
493            Some4 => 0,
494            Some5 => 0,
495            Some6 => 0,
496            Some7 => 0,
497            Some8 => 0,
498        },
499    },
500    {
501        Query  => '((john+smith)  OR     (meier+ john))',
502        Result => {
503            Some1 => 1,
504            Some2 => 1,
505            Some3 => 0,
506            Some4 => 0,
507            Some5 => 0,
508            Some6 => 0,
509            Some7 => 0,
510            Some8 => 0,
511        },
512    },
513    {
514        Query  => '(("john smith")  OR     (meier+ john))',
515        Result => {
516            Some1 => 1,
517            Some2 => 1,
518            Some3 => 0,
519            Some4 => 0,
520            Some5 => 0,
521            Some6 => 0,
522            Some7 => 0,
523            Some8 => 0,
524        },
525    },
526    {
527        Query  => '"john smith"',
528        Result => {
529            Some1 => 1,
530            Some2 => 0,
531            Some3 => 0,
532            Some4 => 0,
533            Some5 => 0,
534            Some6 => 0,
535            Some7 => 0,
536            Some8 => 0,
537        },
538    },
539    {
540        Query  => '( "john smith" )',
541        Result => {
542            Some1 => 1,
543            Some2 => 0,
544            Some3 => 0,
545            Some4 => 0,
546            Some5 => 0,
547            Some6 => 0,
548            Some7 => 0,
549            Some8 => 0,
550        },
551    },
552    {
553        Query  => '"smith john"',
554        Result => {
555            Some1 => 0,
556            Some2 => 0,
557            Some3 => 0,
558            Some4 => 0,
559            Some5 => 0,
560            Some6 => 0,
561            Some7 => 0,
562            Some8 => 0,
563        },
564    },
565    {
566        Query  => '(("john NOTHING smith")  OR     (meier+ john))',
567        Result => {
568            Some1 => 0,
569            Some2 => 1,
570            Some3 => 0,
571            Some4 => 0,
572            Some5 => 0,
573            Some6 => 0,
574            Some7 => 0,
575            Some8 => 0,
576        },
577    },
578    {
579        Query  => '((smith+john)|| (meier+john))',
580        Result => {
581            Some1 => 1,
582            Some2 => 1,
583            Some3 => 0,
584            Some4 => 0,
585            Some5 => 0,
586            Some6 => 0,
587            Some7 => 0,
588            Some8 => 0,
589        },
590    },
591    {
592        Query  => '((john+smith)||  (meier+john))',
593        Result => {
594            Some1 => 1,
595            Some2 => 1,
596            Some3 => 0,
597            Some4 => 0,
598            Some5 => 0,
599            Some6 => 0,
600            Some7 => 0,
601            Some8 => 0,
602        },
603    },
604    {
605        Query  => '*',
606        Result => {
607            Some1 => 1,
608            Some2 => 1,
609            Some3 => 1,
610            Some4 => 1,
611            Some5 => 1,
612            Some6 => 1,
613            Some7 => 1,
614            Some8 => 1,
615        },
616    },
617    {
618        Query  => 'Franz Ferdinand',
619        Result => {
620            Some1 => 0,
621            Some2 => 0,
622            Some3 => 0,
623            Some4 => 1,
624            Some5 => 0,
625            Some6 => 0,
626            Some7 => 0,
627            Some8 => 0,
628        },
629    },
630    {
631        Query  => 'ferdinand',
632        Result => {
633            Some1 => 0,
634            Some2 => 0,
635            Some3 => 0,
636            Some4 => 1,
637            Some5 => 0,
638            Some6 => 0,
639            Some7 => 0,
640            Some8 => 0,
641        },
642    },
643    {
644        Query  => 'franz ferdinand smith',
645        Result => {
646            Some1 => 0,
647            Some2 => 0,
648            Some3 => 0,
649            Some4 => 1,
650            Some5 => 0,
651            Some6 => 0,
652            Some7 => 0,
653            Some8 => 0,
654        },
655    },
656    {
657        Query  => 'smith',
658        Result => {
659            Some1 => 1,
660            Some2 => 0,
661            Some3 => 1,
662            Some4 => 1,
663            Some5 => 0,
664            Some6 => 0,
665            Some7 => 0,
666            Some8 => 0,
667        },
668    },
669    {
670        Query  => 'smith ()',
671        Result => {
672            Some1 => 1,
673            Some2 => 0,
674            Some3 => 1,
675            Some4 => 1,
676            Some5 => 0,
677            Some6 => 0,
678            Some7 => 0,
679            Some8 => 0,
680        },
681    },
682    {
683        Query  => 'customer_id_with_underscores',
684        Result => {
685            Some1 => 0,
686            Some2 => 0,
687            Some3 => 0,
688            Some4 => 0,
689            Some5 => 1,
690            Some6 => 0,
691            Some7 => 0,
692            Some8 => 0,
693        },
694    },
695    {
696        Query  => 'customer_*',
697        Result => {
698            Some1 => 0,
699            Some2 => 0,
700            Some3 => 0,
701            Some4 => 0,
702            Some5 => 1,
703            Some6 => 0,
704            Some7 => 0,
705            Some8 => 0,
706        },
707    },
708    {
709        Query  => '*_*',
710        Result => {
711            Some1 => 0,
712            Some2 => 0,
713            Some3 => 0,
714            Some4 => 0,
715            Some5 => 1,
716            Some6 => 0,
717            Some7 => 0,
718            Some8 => 0,
719        },
720    },
721    {
722        Query  => '_',
723        Result => {
724            Some1 => 0,
725            Some2 => 0,
726            Some3 => 0,
727            Some4 => 0,
728            Some5 => 1,
729            Some6 => 0,
730            Some7 => 0,
731            Some8 => 0,
732        },
733    },
734    {
735        Query  => '!_',
736        Result => {
737            Some1 => 1,
738            Some2 => 1,
739            Some3 => 1,
740            Some4 => 1,
741            Some5 => 0,
742            Some6 => 1,
743            Some7 => 1,
744            Some8 => 1,
745        },
746    },
747    {
748        Query  => 'customer&id&with&ampersands',
749        Result => {
750            Some1 => 0,
751            Some2 => 0,
752            Some3 => 0,
753            Some4 => 0,
754            Some5 => 0,
755            Some6 => 1,
756            Some7 => 0,
757            Some8 => 0,
758        },
759    },
760    {
761        Query  => 'customer&*',
762        Result => {
763            Some1 => 0,
764            Some2 => 0,
765            Some3 => 0,
766            Some4 => 0,
767            Some5 => 0,
768            Some6 => 1,
769            Some7 => 0,
770            Some8 => 0,
771        },
772    },
773    {
774        Query  => '*&*',
775        Result => {
776            Some1 => 0,
777            Some2 => 0,
778            Some3 => 0,
779            Some4 => 0,
780            Some5 => 0,
781            Some6 => 1,
782            Some7 => 0,
783            Some8 => 1,
784        },
785    },
786    {
787        Query  => '&',
788        Result => {
789            Some1 => 0,
790            Some2 => 0,
791            Some3 => 0,
792            Some4 => 0,
793            Some5 => 0,
794            Some6 => 1,
795            Some7 => 0,
796            Some8 => 1,
797        },
798    },
799    {
800        Query  => '!&',
801        Result => {
802            Some1 => 1,
803            Some2 => 1,
804            Some3 => 1,
805            Some4 => 1,
806            Some5 => 1,
807            Some6 => 0,
808            Some7 => 1,
809            Some8 => 0,
810        },
811    },
812    {
813        Query  => '\(with\)',
814        Result => {
815            Some1 => 0,
816            Some2 => 0,
817            Some3 => 0,
818            Some4 => 0,
819            Some5 => 0,
820            Some6 => 0,
821            Some7 => 1,
822            Some8 => 1,
823        },
824    },
825    {
826        Query  => 'Test AND ( \(with\) OR \(brackets\) )',
827        Result => {
828            Some1 => 0,
829            Some2 => 0,
830            Some3 => 0,
831            Some4 => 0,
832            Some5 => 0,
833            Some6 => 0,
834            Some7 => 1,
835            Some8 => 1,
836        },
837    },
838    {
839        Query  => 'Test AND ( \(with\) OR \(brackets\) ) AND \|',
840        Result => {
841            Some1 => 0,
842            Some2 => 0,
843            Some3 => 0,
844            Some4 => 0,
845            Some5 => 0,
846            Some6 => 0,
847            Some7 => 0,
848            Some8 => 1,
849        },
850    },
851    {
852        Query => $DBObject->QueryStringEscape(
853            QueryString => 'customer & id with ampersand & spaces',
854        ),
855        Result => {
856            Some1  => 0,
857            Some2  => 0,
858            Some3  => 0,
859            Some4  => 0,
860            Some5  => 0,
861            Some6  => 0,
862            Some7  => 0,
863            Some8  => 0,
864            Some9  => 0,
865            Some10 => 1,
866        },
867    },
868    {
869        Query  => 'customer & id with ampersand & spaces',
870        Result => {
871            Some1  => 0,
872            Some2  => 0,
873            Some3  => 0,
874            Some4  => 0,
875            Some5  => 0,
876            Some6  => 0,
877            Some7  => 0,
878            Some8  => 0,
879            Some9  => 0,
880            Some10 => 0,
881        },
882    },
883    {
884        Query  => 'Test with single quotes \'test\'',
885        Result => {
886            Some11 => 1,
887        },
888    },
889    {
890        Query  => '\'test\'',
891        Result => {
892            Some1  => 0,
893            Some2  => 0,
894            Some3  => 0,
895            Some4  => 0,
896            Some5  => 0,
897            Some6  => 0,
898            Some7  => 0,
899            Some8  => 0,
900            Some9  => 0,
901            Some10 => 0,
902            Some11 => 1,
903        },
904    },
905);
906
907# select's
908for my $Query (@Queries) {
909
910    my $Condition = $DBObject->QueryCondition(
911        Key          => 'name_b',
912        Value        => $Query->{Query},
913        SearchPrefix => '*',
914        SearchSuffix => '*',
915    );
916
917    $DBObject->Prepare(
918        SQL => 'SELECT name_a FROM test_condition WHERE ' . $Condition,
919    );
920
921    my %Result;
922    while ( my @Row = $DBObject->FetchrowArray() ) {
923        $Result{ $Row[0] } = 1;
924    }
925
926    for my $Check ( sort keys %{ $Query->{Result} } ) {
927        $Self->Is(
928            $Result{$Check} || 0,
929            $Query->{Result}->{$Check} || 0,
930            "#8 Do() SQL SELECT $Query->{Query} / $Check",
931        );
932    }
933}
934@Queries = (
935    {
936        Query  => 'john+smith',
937        Result => {
938            Some1 => 1,
939            Some2 => 0,
940            Some3 => 0,
941        },
942    },
943    {
944        Query  => '(john && smi*h)',
945        Result => {
946            Some1 => 1,
947            Some2 => 0,
948            Some3 => 0,
949        },
950    },
951    {
952        Query  => '(john && smi**h*)',
953        Result => {
954            Some1 => 1,
955            Some2 => 0,
956            Some3 => 0,
957        },
958    },
959    {
960        Query  => '(john+smith+some)',
961        Result => {
962            Some1 => 1,
963            Some2 => 0,
964            Some3 => 0,
965        },
966    },
967    {
968        Query  => '(john+smith+!some)',
969        Result => {
970            Some1 => 0,
971            Some2 => 0,
972            Some3 => 0,
973        },
974    },
975    {
976        Query  => '(john+smith+(!some1||!some2))',
977        Result => {
978            Some1 => 1,
979            Some2 => 0,
980            Some3 => 0,
981        },
982    },
983    {
984        Query  => '(john+smith+(!some1||some))',
985        Result => {
986            Some1 => 1,
987            Some2 => 0,
988            Some3 => 0,
989        },
990    },
991    {
992        Query  => '(!smith+some2)',
993        Result => {
994            Some1 => 0,
995            Some2 => 1,
996            Some3 => 0,
997        },
998    },
999    {
1000        Query  => 'smith AND some2 OR some1',
1001        Result => {
1002            Some1 => 1,
1003            Some2 => 0,
1004            Some3 => 0,
1005        },
1006    },
1007    {
1008        Query  => '(john+(!max||!hans))',
1009        Result => {
1010            Some1 => 1,
1011            Some2 => 1,
1012            Some3 => 0,
1013        },
1014    },
1015    {
1016        Query  => '(john+(!max&&!hans))',
1017        Result => {
1018            Some1 => 1,
1019            Some2 => 1,
1020            Some3 => 0,
1021        },
1022    },
1023    {
1024        Query  => '((max||hans)&&!kkk)',
1025        Result => {
1026            Some1 => 0,
1027            Some2 => 0,
1028            Some3 => 0,
1029        },
1030    },
1031    {
1032        Query  => '*',
1033        Result => {
1034            Some1 => 1,
1035            Some2 => 1,
1036            Some3 => 1,
1037        },
1038    },
1039    {
1040        Query  => 'InvalidQuery\\',
1041        Result => {
1042            Some1 => 0,
1043            Some2 => 0,
1044            Some3 => 0,
1045        },
1046    },
1047);
1048
1049# select's
1050for my $Query (@Queries) {
1051
1052    # Without BindMode
1053    my $Condition = $DBObject->QueryCondition(
1054        Key          => [ 'name_a', 'name_b', 'name_a', 'name_a' ],
1055        Value        => $Query->{Query},
1056        SearchPrefix => '*',
1057        SearchSuffix => '*',
1058    );
1059    $DBObject->Prepare(
1060        SQL => 'SELECT name_a FROM test_condition WHERE ' . $Condition,
1061    );
1062    my %Result;
1063    while ( my @Row = $DBObject->FetchrowArray() ) {
1064        $Result{ $Row[0] } = 1;
1065    }
1066    for my $Check ( sort keys %{ $Query->{Result} } ) {
1067        $Self->Is(
1068            $Result{$Check} || 0,
1069            $Query->{Result}->{$Check} || 0,
1070            "#8 Do() SQL SELECT $Query->{Query} / $Check (BindMode 0)",
1071        );
1072    }
1073
1074    # With BindMode
1075    my %Search = $DBObject->QueryCondition(
1076        Key          => [ 'name_a', 'name_b', 'name_a', 'name_a' ],
1077        Value        => $Query->{Query},
1078        SearchPrefix => '*',
1079        SearchSuffix => '*',
1080        BindMode     => 1,
1081    );
1082    $DBObject->Prepare(
1083        SQL  => 'SELECT name_a FROM test_condition WHERE ' . $Search{SQL},
1084        Bind => $Search{Values},
1085    );
1086    while ( my @Row = $DBObject->FetchrowArray() ) {
1087        $Result{ $Row[0] } = 1;
1088    }
1089    for my $Check ( sort keys %{ $Query->{Result} } ) {
1090        $Self->Is(
1091            $Result{$Check} || 0,
1092            $Query->{Result}->{$Check} || 0,
1093            "#8 Do() SQL SELECT $Query->{Query} / $Check (BindMode 1)",
1094        );
1095    }
1096}
1097
1098# extended test
1099%Fill = (
1100    Some0 => '0 otrs',
1101    Some1 => '1 otrs',
1102);
1103for my $Key ( sort keys %Fill ) {
1104    my $SQL = "INSERT INTO test_condition (name_a, name_b) VALUES ('$Key', '$Fill{$Key}')";
1105    my $Do  = $DBObject->Do(
1106        SQL => $SQL,
1107    );
1108    $Self->True(
1109        $Do,
1110        "#8 Do() INSERT ($SQL)",
1111    );
1112}
1113@Queries = (
1114    {
1115        Query  => '0 otrs',
1116        Result => {
1117            Some0 => 1,
1118            Some1 => 0,
1119        },
1120    },
1121    {
1122        Query  => '1 otrs',
1123        Result => {
1124            Some0 => 0,
1125            Some1 => 1,
1126        },
1127    },
1128);
1129for my $Query (@Queries) {
1130    my $Condition = $DBObject->QueryCondition(
1131        Key          => [ 'name_a', 'name_b', 'name_a', 'name_a' ],
1132        Value        => $Query->{Query},
1133        SearchPrefix => '*',
1134        SearchSuffix => '*',
1135        Extended     => 1,
1136    );
1137    $DBObject->Prepare(
1138        SQL => 'SELECT name_a FROM test_condition WHERE ' . $Condition,
1139    );
1140    my %Result;
1141    while ( my @Row = $DBObject->FetchrowArray() ) {
1142        $Result{ $Row[0] } = 1;
1143    }
1144    for my $Check ( sort keys %{ $Query->{Result} } ) {
1145        $Self->Is(
1146            $Result{$Check} || 0,
1147            $Query->{Result}->{$Check} || 0,
1148            "#8 Do() SQL SELECT $Query->{Query} / $Check",
1149        );
1150    }
1151}
1152
1153# Query condition cleanup test - Checks if '* *' is converted correctly to '*'
1154{
1155    my $Condition = $DBObject->QueryCondition(
1156        Key   => 'name_a',
1157        Value => '* *',
1158    );
1159    $DBObject->Prepare(
1160        SQL => 'SELECT name_a FROM test_condition WHERE ' . $Condition,
1161    );
1162    my @Result;
1163    while ( my @Row = $DBObject->FetchrowArray() ) {
1164        push @Result, $Row[0];
1165    }
1166    $Self->True(
1167        scalar @Result,
1168        "#8 QueryCondition cleanup test - Convert '* *' to '*'",
1169    );
1170}
1171
1172# cleanup
1173$XML      = '<TableDrop Name="test_condition"/>';
1174@XMLARRAY = $XMLObject->XMLParse( String => $XML );
1175@SQL      = $DBObject->SQLProcessor( Database => \@XMLARRAY );
1176$Self->True(
1177    $SQL[0],
1178    '#8 SQLProcessor() DROP TABLE',
1179);
1180
1181for my $SQL (@SQL) {
1182    $Self->True(
1183        $DBObject->Do( SQL => $SQL ) || 0,
1184        "#8 Do() DROP TABLE ($SQL)",
1185    );
1186}
1187
11881;
1189