1 #!/opt/perl/bin/perl -w
3 # tests relating to searching. Especially around custom fields, and
9 use Test::More tests => 44;
16 my $q = RT::Queue->new($RT::SystemUser);
17 my $queue = 'SearchTests-'.$$;
18 $q->Create(Name => $queue);
19 ok ($q->id, "Created the queue");
23 # we believe the Type shouldn't matter.
25 my $cf = RT::CustomField->new($RT::SystemUser);
26 $cf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
27 ok($cf->id, "Created the SearchTest CF");
28 my $cflabel = "CustomField-".$cf->id;
30 my $cf2 = RT::CustomField->new($RT::SystemUser);
31 $cf2->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
32 ok($cf2->id, "Created the SearchTest2 CF");
33 my $cflabel2 = "CustomField-".$cf2->id;
35 my $cf3 = RT::CustomField->new($RT::SystemUser);
36 $cf3->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
37 ok($cf3->id, "Created the SearchTest3 CF");
38 my $cflabel3 = "CustomField-".$cf3->id;
41 # There was a bug involving a missing join to ObjectCustomFields that
42 # caused spurious results on negative searches if another custom field
43 # with the same name existed on a different queue. Hence, we make
44 # duplicate CFs on a different queue here
45 my $dup = RT::Queue->new($RT::SystemUser);
46 $dup->Create(Name => $queue . "-Copy");
47 ok ($dup->id, "Created the duplicate queue");
48 my $dupcf = RT::CustomField->new($RT::SystemUser);
49 $dupcf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
50 ok($dupcf->id, "Created the duplicate SearchTest CF");
51 $dupcf = RT::CustomField->new($RT::SystemUser);
52 $dupcf->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
53 ok($dupcf->id, "Created the SearchTest2 CF");
54 $dupcf = RT::CustomField->new($RT::SystemUser);
55 $dupcf->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
56 ok($dupcf->id, "Created the SearchTest3 CF");
60 # we'll need a small pile of them, to test various combinations and nulls.
61 # there's probably a way to think harder and do this with fewer
64 my $t1 = RT::Ticket->new($RT::SystemUser);
65 my ( $id, undef $msg ) = $t1->Create(
67 Subject => 'SearchTest1',
68 Requestor => ['search1@example.com'],
76 my $t2 = RT::Ticket->new($RT::SystemUser);
77 ( $id, undef, $msg ) = $t2->Create(
79 Subject => 'SearchTest2',
80 Requestor => ['search2@example.com'],
87 my $t3 = RT::Ticket->new($RT::SystemUser);
88 ( $id, undef, $msg ) = $t3->Create(
90 Subject => 'SearchTest3',
91 Requestor => ['search3@example.com'],
93 # $cflabel2 => 'bar3',
98 my $t4 = RT::Ticket->new($RT::SystemUser);
99 ( $id, undef, $msg ) = $t4->Create(
101 Subject => 'SearchTest4',
102 Requestor => ['search4@example.com'],
105 # $cflabel3 => 'qux4',
109 my $t5 = RT::Ticket->new($RT::SystemUser);
110 ( $id, undef, $msg ) = $t5->Create(
112 # Subject => 'SearchTest5',
113 Requestor => ['search5@example.com'],
120 my $t6 = RT::Ticket->new($RT::SystemUser);
121 ( $id, undef, $msg ) = $t6->Create(
123 Subject => 'SearchTest6',
124 # Requestor => ['search6@example.com'],
131 my $t7 = RT::Ticket->new($RT::SystemUser);
132 ( $id, undef, $msg ) = $t7->Create(
134 Subject => 'SearchTest7',
135 Requestor => ['search7@example.com'],
136 # $cflabel => 'foo7',
137 # $cflabel2 => 'bar7',
142 # we have tickets. start searching
143 my $tix = RT::Tickets->new($RT::SystemUser);
144 $tix->FromSQL("Queue = '$queue'");
145 is($tix->Count, 7, "found all the tickets")
146 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
149 # very simple searches. both CF and normal
151 $tix = RT::Tickets->new($RT::SystemUser);
152 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo1'");
153 is($tix->Count, 1, "matched identical subject")
154 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
156 $tix = RT::Tickets->new($RT::SystemUser);
157 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo1'");
158 is($tix->Count, 1, "matched LIKE subject")
159 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
161 $tix = RT::Tickets->new($RT::SystemUser);
162 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo'");
163 is($tix->Count, 0, "IS a regexp match")
164 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
166 $tix = RT::Tickets->new($RT::SystemUser);
167 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo'");
168 is($tix->Count, 5, "matched LIKE subject")
169 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
172 $tix = RT::Tickets->new($RT::SystemUser);
173 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL");
174 is($tix->Count, 2, "IS null CF")
175 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
177 $tix = RT::Tickets->new($RT::SystemUser);
178 $tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search1'");
179 is($tix->Count, 1, "LIKE requestor")
180 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
182 $tix = RT::Tickets->new($RT::SystemUser);
183 $tix->FromSQL("Queue = '$queue' AND Requestors = 'search1\@example.com'");
184 is($tix->Count, 1, "IS requestor")
185 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
187 $tix = RT::Tickets->new($RT::SystemUser);
188 $tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search'");
189 is($tix->Count, 6, "LIKE requestor")
190 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
192 $tix = RT::Tickets->new($RT::SystemUser);
193 $tix->FromSQL("Queue = '$queue' AND Requestors IS NULL");
194 is($tix->Count, 1, "Search for no requestor")
195 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
197 $tix = RT::Tickets->new($RT::SystemUser);
198 $tix->FromSQL("Queue = '$queue' AND Subject = 'SearchTest1'");
199 is($tix->Count, 1, "IS subject")
200 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
202 $tix = RT::Tickets->new($RT::SystemUser);
203 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest1'");
204 is($tix->Count, 1, "LIKE subject")
205 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
207 $tix = RT::Tickets->new($RT::SystemUser);
208 $tix->FromSQL("Queue = '$queue' AND Subject = ''");
209 is($tix->Count, 1, "found one ticket")
210 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
212 $tix = RT::Tickets->new($RT::SystemUser);
213 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest'");
214 is($tix->Count, 6, "found two ticket")
215 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
217 $tix = RT::Tickets->new($RT::SystemUser);
218 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'qwerty'");
219 is($tix->Count, 0, "found zero ticket")
220 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
225 # various combinations
227 $tix = RT::Tickets->new($RT::SystemUser);
228 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar1'");
229 is($tix->Count, 1, "LIKE cf and LIKE cf");
231 $tix = RT::Tickets->new($RT::SystemUser);
232 $tix->FromSQL("CF.SearchTest = 'foo1' AND CF.SearchTest2 = 'bar1'");
233 is($tix->Count, 1, "is cf and is cf");
235 $tix = RT::Tickets->new($RT::SystemUser);
236 $tix->FromSQL("CF.SearchTest = 'foo' AND CF.SearchTest2 LIKE 'bar1'");
237 is($tix->Count, 0, "is 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 'qux'");
241 is($tix->Count, 3, "like cf and like cf and like cf");
243 $tix = RT::Tickets->new($RT::SystemUser);
244 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar' AND CF.SearchTest3 LIKE 'qux6'");
245 is($tix->Count, 1, "like cf and like cf and is cf");
247 $tix = RT::Tickets->new($RT::SystemUser);
248 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND Subject LIKE 'SearchTest'");
249 is($tix->Count, 4, "like cf and like subject");
251 $tix = RT::Tickets->new($RT::SystemUser);
252 $tix->FromSQL("CF.SearchTest IS NULL AND CF.SearchTest2 = 'bar2'");
253 is($tix->Count, 1, "null cf and is cf");
256 $tix = RT::Tickets->new($RT::SystemUser);
257 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL AND CF.SearchTest2 IS NULL");
258 is($tix->Count, 1, "null cf and null cf");
260 # tests with the same CF listed twice
262 $tix = RT::Tickets->new($RT::SystemUser);
263 $tix->FromSQL("CF.{SearchTest} = 'foo1'");
264 is($tix->Count, 1, "is cf.{name} format");
266 $tix = RT::Tickets->new($RT::SystemUser);
267 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3'");
268 is($tix->Count, 2, "is cf1 or is cf1");
270 $tix = RT::Tickets->new($RT::SystemUser);
271 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest IS NULL");
272 is($tix->Count, 3, "is cf1 or null cf1");
274 $tix = RT::Tickets->new($RT::SystemUser);
275 $tix->FromSQL("(CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3') AND (CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2')");
276 is($tix->Count, 1, "(is cf1 or is cf1) and (is cf2 or is cf2)");
278 $tix = RT::Tickets->new($RT::SystemUser);
279 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3' OR CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2'");
280 is($tix->Count, 3, "is cf1 or is cf1 or is cf2 or is cf2");