1
2use strict;
3use warnings;
4
5use RT::Test nodata => 1, tests => undef;
6
7my $q = RT::Test->load_or_create_queue( Name => 'Regression' );
8ok $q && $q->id, 'loaded or created queue';
9my $queue = $q->Name;
10
11diag "create a CF";
12my ($cf_name, $cf_id, $cf) = ("Test", 0, undef);
13{
14    $cf = RT::CustomField->new( RT->SystemUser );
15    my ($ret, $msg) = $cf->Create(
16        Name  => $cf_name,
17        Queue => $q->id,
18        Type  => 'FreeformMultiple',
19    );
20    ok($ret, "Custom Field Order created");
21    $cf_id = $cf->id;
22}
23
24my $ylong = "y" x 300;
25subtest "Creating tickets" => sub {
26    RT::Test->create_tickets( { Queue => $q->id },
27        { Subject => '-' },
28        { Subject => 'x', "CustomField-$cf_id" => 'x', },
29        { Subject => 'y', "CustomField-$cf_id" => 'y', },
30        { Subject => 'z', "CustomField-$cf_id" => 'z', },
31        { Subject => 'xy', "CustomField-$cf_id" => [ 'x', 'y' ], },
32        { Subject => 'xz', "CustomField-$cf_id" => [ 'x', 'z' ], },
33        { Subject => 'yz', "CustomField-$cf_id" => [ 'y', 'z' ], },
34        { Subject => 'x_ylong', "CustomField-$cf_id" => [ 'x', $ylong ], },
35        { Subject => 'ylong', "CustomField-$cf_id" => $ylong, },
36    );
37};
38
39my @tests = (
40    "CF.{$cf_id} IS NULL"                      => { '-' => 1, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
41    "CF.{$cf_id}.Content IS NULL"              => { '-' => 1, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
42    "CF.{$cf_id}.LargeContent IS NULL"         => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
43    "'CF.{$cf_name}' IS NULL"                  => { '-' => 1, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
44    "'CF.{$cf_name}.Content' IS NULL"          => { '-' => 1, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
45    "'CF.{$cf_name}.LargeContent' IS NULL"     => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
46    "'CF.$queue.{$cf_id}' IS NULL"             => { '-' => 1, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
47    "'CF.$queue.{$cf_name}' IS NULL"           => { '-' => 1, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
48
49    "CF.{$cf_id} IS NOT NULL"                  => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
50    "CF.{$cf_id}.Content IS NOT NULL"          => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
51    "CF.{$cf_id}.LargeContent IS NOT NULL"     => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
52    "'CF.{$cf_name}' IS NOT NULL"              => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
53    "'CF.{$cf_name}.Content' IS NOT NULL"      => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
54    "'CF.{$cf_name}.LargeContent' IS NOT NULL" => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
55    "'CF.$queue.{$cf_id}' IS NOT NULL"         => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
56    "'CF.$queue.{$cf_name}' IS NOT NULL"       => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
57
58    "CF.{$cf_id} = 'x'"                   => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
59    "CF.{$cf_id}.Content = 'x'"           => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
60    "CF.{$cf_id}.LargeContent = 'x'"      => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
61    "CF.{$cf_id} = '$ylong'"              => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
62    "CF.{$cf_id}.Content = '$ylong'"      => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
63    "CF.{$cf_id}.LargeContent = '$ylong'" => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
64    "CF.{$cf_id} LIKE 'yyy'"              => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
65    "CF.{$cf_id}.Content LIKE 'yyy'"      => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
66    "CF.{$cf_id}.LargeContent LIKE 'yyy'" => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 1, ylong => 1 },
67    "'CF.{$cf_name}' = 'x'"               => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
68    "'CF.{$cf_name}.Content' = 'x'"       => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
69    "'CF.{$cf_name}.LargeContent' = 'x'"  => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
70    "'CF.$queue.{$cf_id}' = 'x'"          => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
71    "'CF.$queue.{$cf_name}' = 'x'"        => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
72
73    "CF.{$cf_id} != 'x'"                   => { '-' => 1, x => 0, y => 1, z => 1, xy => 0, xz => 0, yz => 1, x_ylong => 0, ylong => 1 },
74    "CF.{$cf_id}.Content != 'x'"           => { '-' => 1, x => 0, y => 1, z => 1, xy => 0, xz => 0, yz => 1, x_ylong => 0, ylong => 1 },
75    "CF.{$cf_id}.LargeContent != 'x'"      => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
76    "CF.{$cf_id} != '$ylong'"              => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 0, ylong => 0 },
77    "CF.{$cf_id}.Content != '$ylong'"      => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
78    "CF.{$cf_id}.LargeContent != '$ylong'" => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 0, ylong => 0 },
79"TODO: CF.{$cf_id} NOT LIKE 'yyy'"         => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 0, ylong => 0 },
80    "CF.{$cf_id}.Content NOT LIKE 'yyy'"   => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
81    "CF.{$cf_id}.LargeContent NOT LIKE 'yyy'" => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 0, ylong => 0 },
82    "'CF.{$cf_name}' != 'x'"               => { '-' => 1, x => 0, y => 1, z => 1, xy => 0, xz => 0, yz => 1, x_ylong => 0, ylong => 1 },
83    "'CF.{$cf_name}.Content' != 'x'"       => { '-' => 1, x => 0, y => 1, z => 1, xy => 0, xz => 0, yz => 1, x_ylong => 0, ylong => 1 },
84    "'CF.{$cf_name}.LargeContent' != 'x'"  => { '-' => 1, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
85    "'CF.$queue.{$cf_id}' != 'x'"          => { '-' => 1, x => 0, y => 1, z => 1, xy => 0, xz => 0, yz => 1, x_ylong => 0, ylong => 1 },
86    "'CF.$queue.{$cf_name}' != 'x'"        => { '-' => 1, x => 0, y => 1, z => 1, xy => 0, xz => 0, yz => 1, x_ylong => 0, ylong => 1 },
87
88    "CF.{$cf_id} = 'x' OR CF.{$cf_id} = 'y'"                        => { '-' => 0, x => 1, y => 1, z => 0, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
89    "'CF.{$cf_name}' = 'x' OR 'CF.{$cf_name}' = 'y'"                => { '-' => 0, x => 1, y => 1, z => 0, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
90    "'CF.$queue.{$cf_id}' = 'x' OR 'CF.$queue.{$cf_id}' = 'y'"      => { '-' => 0, x => 1, y => 1, z => 0, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
91    "'CF.$queue.{$cf_name}' = 'x' OR 'CF.$queue.{$cf_name}' = 'y'"  => { '-' => 0, x => 1, y => 1, z => 0, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 0 },
92
93    "CF.{$cf_id} = 'x' AND CF.{$cf_id} = 'y'"                        => { '-' => 0, x => 0, y => 0, z => 0, xy => 1, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
94    "'CF.{$cf_name}' = 'x' AND 'CF.{$cf_name}' = 'y'"                => { '-' => 0, x => 0, y => 0, z => 0, xy => 1, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
95    "'CF.$queue.{$cf_id}' = 'x' AND 'CF.$queue.{$cf_id}' = 'y'"      => { '-' => 0, x => 0, y => 0, z => 0, xy => 1, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
96    "'CF.$queue.{$cf_name}' = 'x' AND 'CF.$queue.{$cf_name}' = 'y'"  => { '-' => 0, x => 0, y => 0, z => 0, xy => 1, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
97
98    "CF.{$cf_id} != 'x' AND CF.{$cf_id} != 'y'"                        => { '-' => 1, x => 0, y => 0, z => 1, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 1 },
99    "'CF.{$cf_name}' != 'x' AND 'CF.{$cf_name}' != 'y'"                => { '-' => 1, x => 0, y => 0, z => 1, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 1 },
100    "'CF.$queue.{$cf_id}' != 'x' AND 'CF.$queue.{$cf_id}' != 'y'"      => { '-' => 1, x => 0, y => 0, z => 1, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 1 },
101    "'CF.$queue.{$cf_name}' != 'x' AND 'CF.$queue.{$cf_name}' != 'y'"  => { '-' => 1, x => 0, y => 0, z => 1, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 1 },
102
103    "CF.{$cf_id} = 'x' AND CF.{$cf_id} IS NULL"                        => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
104    "'CF.{$cf_name}' = 'x' AND 'CF.{$cf_name}' IS NULL"                => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
105    "'CF.$queue.{$cf_id}' = 'x' AND 'CF.$queue.{$cf_id}' IS NULL"      => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
106    "'CF.$queue.{$cf_name}' = 'x' AND 'CF.$queue.{$cf_name}' IS NULL"  => { '-' => 0, x => 0, y => 0, z => 0, xy => 0, xz => 0, yz => 0, x_ylong => 0, ylong => 0 },
107
108    "CF.{$cf_id} = 'x' OR CF.{$cf_id} IS NULL"                        => { '-' => 1, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
109    "'CF.{$cf_name}' = 'x' OR 'CF.{$cf_name}' IS NULL"                => { '-' => 1, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
110    "'CF.$queue.{$cf_id}' = 'x' OR 'CF.$queue.{$cf_id}' IS NULL"      => { '-' => 1, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
111    "'CF.$queue.{$cf_name}' = 'x' OR 'CF.$queue.{$cf_name}' IS NULL"  => { '-' => 1, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
112
113    "CF.{$cf_id} = 'x' AND CF.{$cf_id} IS NOT NULL"                        => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
114    "'CF.{$cf_name}' = 'x' AND 'CF.{$cf_name}' IS NOT NULL"                => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
115    "'CF.$queue.{$cf_id}' = 'x' AND 'CF.$queue.{$cf_id}' IS NOT NULL"      => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
116    "'CF.$queue.{$cf_name}' = 'x' AND 'CF.$queue.{$cf_name}' IS NOT NULL"  => { '-' => 0, x => 1, y => 0, z => 0, xy => 1, xz => 1, yz => 0, x_ylong => 1, ylong => 0 },
117
118    "CF.{$cf_id} = 'x' OR CF.{$cf_id} IS NOT NULL"                        => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
119    "'CF.{$cf_name}' = 'x' OR 'CF.{$cf_name}' IS NOT NULL"                => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
120    "'CF.$queue.{$cf_id}' = 'x' OR 'CF.$queue.{$cf_id}' IS NOT NULL"      => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
121    "'CF.$queue.{$cf_name}' = 'x' OR 'CF.$queue.{$cf_name}' IS NOT NULL"  => { '-' => 0, x => 1, y => 1, z => 1, xy => 1, xz => 1, yz => 1, x_ylong => 1, ylong => 1 },
122);
123run_tests(@tests);
124
125
126sub run_tests {
127    my @tests = @_;
128    while (@tests) {
129        my $query = shift @tests;
130        my %results = %{ shift @tests };
131        local $TODO = "Not implemented correctly" if $query =~ s/^TODO:\s*//;
132        subtest $query => sub {
133            my $tix = RT::Tickets->new(RT->SystemUser);
134            $tix->FromSQL( "$query" );
135
136            my $error = 0;
137
138            my $count = 0;
139            $count++ foreach grep $_, values %results;
140            is($tix->Count, $count, "found correct number of ticket(s)") or $error = 1;
141
142            my $good_tickets = ($tix->Count == $count);
143            while ( my $ticket = $tix->Next ) {
144                next if $results{ $ticket->Subject };
145                diag $ticket->Subject ." ticket has been found when it's not expected";
146                $good_tickets = 0;
147            }
148            ok( $good_tickets, "all tickets are good" ) or $error = 1;
149
150            diag "Wrong SQL: ". $tix->BuildSelectQuery if $error;
151        };
152    }
153}
154
155
156done_testing;
157