2 # tests relating to searching. Especially around custom fields, and
8 use RT::Test nodata => 1, tests => undef;
12 my $q = RT::Queue->new(RT->SystemUser);
13 my $queue = 'SearchTests-'.$$;
14 $q->Create(Name => $queue);
15 ok ($q->id, "Created the queue");
19 # we believe the Type shouldn't matter.
21 my $cf = RT::CustomField->new(RT->SystemUser);
22 $cf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
23 ok($cf->id, "Created the SearchTest CF");
24 my $cflabel = "CustomField-".$cf->id;
26 my $cf2 = RT::CustomField->new(RT->SystemUser);
27 $cf2->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
28 ok($cf2->id, "Created the SearchTest2 CF");
29 my $cflabel2 = "CustomField-".$cf2->id;
31 my $cf3 = RT::CustomField->new(RT->SystemUser);
32 $cf3->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
33 ok($cf3->id, "Created the SearchTest3 CF");
34 my $cflabel3 = "CustomField-".$cf3->id;
37 # There was a bug involving a missing join to ObjectCustomFields that
38 # caused spurious results on negative searches if another custom field
39 # with the same name existed on a different queue. Hence, we make
40 # duplicate CFs on a different queue here
41 my $dup = RT::Queue->new(RT->SystemUser);
42 $dup->Create(Name => $queue . "-Copy");
43 ok ($dup->id, "Created the duplicate queue");
44 my $dupcf = RT::CustomField->new(RT->SystemUser);
45 $dupcf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
46 ok($dupcf->id, "Created the duplicate SearchTest CF");
47 $dupcf = RT::CustomField->new(RT->SystemUser);
48 $dupcf->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
49 ok($dupcf->id, "Created the SearchTest2 CF");
50 $dupcf = RT::CustomField->new(RT->SystemUser);
51 $dupcf->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
52 ok($dupcf->id, "Created the SearchTest3 CF");
56 # we'll need a small pile of them, to test various combinations and nulls.
57 # there's probably a way to think harder and do this with fewer
60 my $t1 = RT::Ticket->new(RT->SystemUser);
61 my ( $id, undef $msg ) = $t1->Create(
63 Subject => 'SearchTest1',
64 Requestor => ['search1@example.com'],
72 my $t2 = RT::Ticket->new(RT->SystemUser);
73 ( $id, undef, $msg ) = $t2->Create(
75 Subject => 'SearchTest2',
76 Requestor => ['search2@example.com'],
83 my $t3 = RT::Ticket->new(RT->SystemUser);
84 ( $id, undef, $msg ) = $t3->Create(
86 Subject => 'SearchTest3',
87 Requestor => ['search3@example.com'],
89 # $cflabel2 => 'bar3',
94 my $t4 = RT::Ticket->new(RT->SystemUser);
95 ( $id, undef, $msg ) = $t4->Create(
97 Subject => 'SearchTest4',
98 Requestor => ['search4@example.com'],
101 # $cflabel3 => 'qux4',
105 my $t5 = RT::Ticket->new(RT->SystemUser);
106 ( $id, undef, $msg ) = $t5->Create(
108 # Subject => 'SearchTest5',
109 Requestor => ['search5@example.com'],
116 my $t6 = RT::Ticket->new(RT->SystemUser);
117 ( $id, undef, $msg ) = $t6->Create(
119 Subject => 'SearchTest6',
120 # Requestor => ['search6@example.com'],
127 my $t7 = RT::Ticket->new(RT->SystemUser);
128 ( $id, undef, $msg ) = $t7->Create(
130 Subject => 'SearchTest7',
131 Requestor => ['search7@example.com'],
132 # $cflabel => 'foo7',
133 # $cflabel2 => 'bar7',
138 # we have tickets. start searching
139 my $tix = RT::Tickets->new(RT->SystemUser);
140 $tix->FromSQL("Queue = '$queue'");
141 is($tix->Count, 7, "found all the tickets")
142 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
145 # very simple searches. both CF and normal
147 $tix = RT::Tickets->new(RT->SystemUser);
148 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo1'");
149 is($tix->Count, 1, "matched identical subject")
150 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
152 $tix = RT::Tickets->new(RT->SystemUser);
153 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo1'");
154 is($tix->Count, 1, "matched LIKE subject")
155 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
157 $tix = RT::Tickets->new(RT->SystemUser);
158 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo'");
159 is($tix->Count, 0, "IS a regexp match")
160 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
162 $tix = RT::Tickets->new(RT->SystemUser);
163 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo'");
164 is($tix->Count, 5, "matched LIKE subject")
165 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
168 $tix = RT::Tickets->new(RT->SystemUser);
169 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL");
170 is($tix->Count, 2, "IS null CF")
171 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
173 $tix = RT::Tickets->new(RT->SystemUser);
174 $tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search1'");
175 is($tix->Count, 1, "LIKE requestor")
176 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
178 $tix = RT::Tickets->new(RT->SystemUser);
179 $tix->FromSQL("Queue = '$queue' AND Requestors = 'search1\@example.com'");
180 is($tix->Count, 1, "IS requestor")
181 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
183 $tix = RT::Tickets->new(RT->SystemUser);
184 $tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search'");
185 is($tix->Count, 6, "LIKE requestor")
186 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
188 $tix = RT::Tickets->new(RT->SystemUser);
189 $tix->FromSQL("Queue = '$queue' AND Requestors IS NULL");
190 is($tix->Count, 1, "Search for no requestor")
191 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
193 $tix = RT::Tickets->new(RT->SystemUser);
194 $tix->FromSQL("Queue = '$queue' AND Subject = 'SearchTest1'");
195 is($tix->Count, 1, "IS subject")
196 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
198 $tix = RT::Tickets->new(RT->SystemUser);
199 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest1'");
200 is($tix->Count, 1, "LIKE subject")
201 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
203 $tix = RT::Tickets->new(RT->SystemUser);
204 $tix->FromSQL("Queue = '$queue' AND Subject = ''");
205 is($tix->Count, 1, "found one ticket")
206 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
208 $tix = RT::Tickets->new(RT->SystemUser);
209 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest'");
210 is($tix->Count, 6, "found two ticket")
211 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
213 $tix = RT::Tickets->new(RT->SystemUser);
214 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'qwerty'");
215 is($tix->Count, 0, "found zero ticket")
216 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
221 # various combinations
223 $tix = RT::Tickets->new(RT->SystemUser);
224 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar1'");
225 is($tix->Count, 1, "LIKE cf and LIKE cf");
227 $tix = RT::Tickets->new(RT->SystemUser);
228 $tix->FromSQL("CF.SearchTest = 'foo1' AND CF.SearchTest2 = 'bar1'");
229 is($tix->Count, 1, "is cf and is cf");
231 $tix = RT::Tickets->new(RT->SystemUser);
232 $tix->FromSQL("CF.SearchTest = 'foo' AND CF.SearchTest2 LIKE 'bar1'");
233 is($tix->Count, 0, "is cf and like cf");
235 $tix = RT::Tickets->new(RT->SystemUser);
236 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar' AND CF.SearchTest3 LIKE 'qux'");
237 is($tix->Count, 3, "like cf and like cf and like cf");
239 $tix = RT::Tickets->new(RT->SystemUser);
240 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar' AND CF.SearchTest3 LIKE 'qux6'");
241 is($tix->Count, 1, "like cf and like cf and is cf");
243 $tix = RT::Tickets->new(RT->SystemUser);
244 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND Subject LIKE 'SearchTest'");
245 is($tix->Count, 4, "like cf and like subject");
247 $tix = RT::Tickets->new(RT->SystemUser);
248 $tix->FromSQL("CF.SearchTest IS NULL AND CF.SearchTest2 = 'bar2'");
249 is($tix->Count, 1, "null cf and is cf");
252 $tix = RT::Tickets->new(RT->SystemUser);
253 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL AND CF.SearchTest2 IS NULL");
254 is($tix->Count, 1, "null cf and null cf");
256 # tests with the same CF listed twice
258 $tix = RT::Tickets->new(RT->SystemUser);
259 $tix->FromSQL("CF.{SearchTest} = 'foo1'");
260 is($tix->Count, 1, "is cf.{name} format");
262 $tix = RT::Tickets->new(RT->SystemUser);
263 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3'");
264 is($tix->Count, 2, "is cf1 or is cf1");
266 $tix = RT::Tickets->new(RT->SystemUser);
267 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest IS NULL");
268 is($tix->Count, 3, "is cf1 or null cf1");
270 $tix = RT::Tickets->new(RT->SystemUser);
271 $tix->FromSQL("(CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3') AND (CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2')");
272 is($tix->Count, 1, "(is cf1 or is cf1) and (is cf2 or is cf2)");
274 $tix = RT::Tickets->new(RT->SystemUser);
275 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3' OR CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2'");
276 is($tix->Count, 3, "is cf1 or is cf1 or is cf2 or is cf2");
278 # tests with lower cased NULL
279 $tix = RT::Tickets->new(RT->SystemUser);
280 $tix->FromSQL('Requestor.Name IS null');
281 is($tix->Count, 1, "t6 doesn't have a Requestor");
282 like($tix->BuildSelectCountQuery, qr/\bNULL\b/, "Contains upper-case NULL");
283 unlike($tix->BuildSelectCountQuery, qr/\bnull\b/, "Lacks lower-case NULL");