1 #!/opt/perl/bin/perl -w
3 # tests relating to searching. Especially around custom fields, and
9 use RT::Test tests => 43;
13 my $q = RT::Queue->new($RT::SystemUser);
14 my $queue = 'SearchTests-'.$$;
15 $q->Create(Name => $queue);
16 ok ($q->id, "Created the queue");
20 # we believe the Type shouldn't matter.
22 my $cf = RT::CustomField->new($RT::SystemUser);
23 $cf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
24 ok($cf->id, "Created the SearchTest CF");
25 my $cflabel = "CustomField-".$cf->id;
27 my $cf2 = RT::CustomField->new($RT::SystemUser);
28 $cf2->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
29 ok($cf2->id, "Created the SearchTest2 CF");
30 my $cflabel2 = "CustomField-".$cf2->id;
32 my $cf3 = RT::CustomField->new($RT::SystemUser);
33 $cf3->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $q->id);
34 ok($cf3->id, "Created the SearchTest3 CF");
35 my $cflabel3 = "CustomField-".$cf3->id;
38 # There was a bug involving a missing join to ObjectCustomFields that
39 # caused spurious results on negative searches if another custom field
40 # with the same name existed on a different queue. Hence, we make
41 # duplicate CFs on a different queue here
42 my $dup = RT::Queue->new($RT::SystemUser);
43 $dup->Create(Name => $queue . "-Copy");
44 ok ($dup->id, "Created the duplicate queue");
45 my $dupcf = RT::CustomField->new($RT::SystemUser);
46 $dupcf->Create(Name => 'SearchTest', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
47 ok($dupcf->id, "Created the duplicate SearchTest CF");
48 $dupcf = RT::CustomField->new($RT::SystemUser);
49 $dupcf->Create(Name => 'SearchTest2', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
50 ok($dupcf->id, "Created the SearchTest2 CF");
51 $dupcf = RT::CustomField->new($RT::SystemUser);
52 $dupcf->Create(Name => 'SearchTest3', Type => 'Freeform', MaxValues => 0, Queue => $dup->id);
53 ok($dupcf->id, "Created the SearchTest3 CF");
57 # we'll need a small pile of them, to test various combinations and nulls.
58 # there's probably a way to think harder and do this with fewer
61 my $t1 = RT::Ticket->new($RT::SystemUser);
62 my ( $id, undef $msg ) = $t1->Create(
64 Subject => 'SearchTest1',
65 Requestor => ['search1@example.com'],
73 my $t2 = RT::Ticket->new($RT::SystemUser);
74 ( $id, undef, $msg ) = $t2->Create(
76 Subject => 'SearchTest2',
77 Requestor => ['search2@example.com'],
84 my $t3 = RT::Ticket->new($RT::SystemUser);
85 ( $id, undef, $msg ) = $t3->Create(
87 Subject => 'SearchTest3',
88 Requestor => ['search3@example.com'],
90 # $cflabel2 => 'bar3',
95 my $t4 = RT::Ticket->new($RT::SystemUser);
96 ( $id, undef, $msg ) = $t4->Create(
98 Subject => 'SearchTest4',
99 Requestor => ['search4@example.com'],
102 # $cflabel3 => 'qux4',
106 my $t5 = RT::Ticket->new($RT::SystemUser);
107 ( $id, undef, $msg ) = $t5->Create(
109 # Subject => 'SearchTest5',
110 Requestor => ['search5@example.com'],
117 my $t6 = RT::Ticket->new($RT::SystemUser);
118 ( $id, undef, $msg ) = $t6->Create(
120 Subject => 'SearchTest6',
121 # Requestor => ['search6@example.com'],
128 my $t7 = RT::Ticket->new($RT::SystemUser);
129 ( $id, undef, $msg ) = $t7->Create(
131 Subject => 'SearchTest7',
132 Requestor => ['search7@example.com'],
133 # $cflabel => 'foo7',
134 # $cflabel2 => 'bar7',
139 # we have tickets. start searching
140 my $tix = RT::Tickets->new($RT::SystemUser);
141 $tix->FromSQL("Queue = '$queue'");
142 is($tix->Count, 7, "found all the tickets")
143 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
146 # very simple searches. both CF and normal
148 $tix = RT::Tickets->new($RT::SystemUser);
149 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo1'");
150 is($tix->Count, 1, "matched identical subject")
151 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
153 $tix = RT::Tickets->new($RT::SystemUser);
154 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo1'");
155 is($tix->Count, 1, "matched LIKE subject")
156 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
158 $tix = RT::Tickets->new($RT::SystemUser);
159 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest = 'foo'");
160 is($tix->Count, 0, "IS a regexp match")
161 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
163 $tix = RT::Tickets->new($RT::SystemUser);
164 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest LIKE 'foo'");
165 is($tix->Count, 5, "matched LIKE subject")
166 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
169 $tix = RT::Tickets->new($RT::SystemUser);
170 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL");
171 is($tix->Count, 2, "IS null CF")
172 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
174 $tix = RT::Tickets->new($RT::SystemUser);
175 $tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search1'");
176 is($tix->Count, 1, "LIKE requestor")
177 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
179 $tix = RT::Tickets->new($RT::SystemUser);
180 $tix->FromSQL("Queue = '$queue' AND Requestors = 'search1\@example.com'");
181 is($tix->Count, 1, "IS requestor")
182 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
184 $tix = RT::Tickets->new($RT::SystemUser);
185 $tix->FromSQL("Queue = '$queue' AND Requestors LIKE 'search'");
186 is($tix->Count, 6, "LIKE requestor")
187 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
189 $tix = RT::Tickets->new($RT::SystemUser);
190 $tix->FromSQL("Queue = '$queue' AND Requestors IS NULL");
191 is($tix->Count, 1, "Search for no requestor")
192 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
194 $tix = RT::Tickets->new($RT::SystemUser);
195 $tix->FromSQL("Queue = '$queue' AND Subject = 'SearchTest1'");
196 is($tix->Count, 1, "IS subject")
197 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
199 $tix = RT::Tickets->new($RT::SystemUser);
200 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest1'");
201 is($tix->Count, 1, "LIKE subject")
202 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
204 $tix = RT::Tickets->new($RT::SystemUser);
205 $tix->FromSQL("Queue = '$queue' AND Subject = ''");
206 is($tix->Count, 1, "found one ticket")
207 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
209 $tix = RT::Tickets->new($RT::SystemUser);
210 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'SearchTest'");
211 is($tix->Count, 6, "found two ticket")
212 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
214 $tix = RT::Tickets->new($RT::SystemUser);
215 $tix->FromSQL("Queue = '$queue' AND Subject LIKE 'qwerty'");
216 is($tix->Count, 0, "found zero ticket")
217 or diag "wrong results from SQL:\n". $tix->BuildSelectCountQuery;
222 # various combinations
224 $tix = RT::Tickets->new($RT::SystemUser);
225 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar1'");
226 is($tix->Count, 1, "LIKE cf and LIKE cf");
228 $tix = RT::Tickets->new($RT::SystemUser);
229 $tix->FromSQL("CF.SearchTest = 'foo1' AND CF.SearchTest2 = 'bar1'");
230 is($tix->Count, 1, "is cf and is cf");
232 $tix = RT::Tickets->new($RT::SystemUser);
233 $tix->FromSQL("CF.SearchTest = 'foo' AND CF.SearchTest2 LIKE 'bar1'");
234 is($tix->Count, 0, "is cf and like cf");
236 $tix = RT::Tickets->new($RT::SystemUser);
237 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar' AND CF.SearchTest3 LIKE 'qux'");
238 is($tix->Count, 3, "like cf and like cf and like cf");
240 $tix = RT::Tickets->new($RT::SystemUser);
241 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND CF.SearchTest2 LIKE 'bar' AND CF.SearchTest3 LIKE 'qux6'");
242 is($tix->Count, 1, "like cf and like cf and is cf");
244 $tix = RT::Tickets->new($RT::SystemUser);
245 $tix->FromSQL("CF.SearchTest LIKE 'foo' AND Subject LIKE 'SearchTest'");
246 is($tix->Count, 4, "like cf and like subject");
248 $tix = RT::Tickets->new($RT::SystemUser);
249 $tix->FromSQL("CF.SearchTest IS NULL AND CF.SearchTest2 = 'bar2'");
250 is($tix->Count, 1, "null cf and is cf");
253 $tix = RT::Tickets->new($RT::SystemUser);
254 $tix->FromSQL("Queue = '$queue' AND CF.SearchTest IS NULL AND CF.SearchTest2 IS NULL");
255 is($tix->Count, 1, "null cf and null cf");
257 # tests with the same CF listed twice
259 $tix = RT::Tickets->new($RT::SystemUser);
260 $tix->FromSQL("CF.{SearchTest} = 'foo1'");
261 is($tix->Count, 1, "is cf.{name} format");
263 $tix = RT::Tickets->new($RT::SystemUser);
264 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3'");
265 is($tix->Count, 2, "is cf1 or is cf1");
267 $tix = RT::Tickets->new($RT::SystemUser);
268 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest IS NULL");
269 is($tix->Count, 3, "is cf1 or null cf1");
271 $tix = RT::Tickets->new($RT::SystemUser);
272 $tix->FromSQL("(CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3') AND (CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2')");
273 is($tix->Count, 1, "(is cf1 or is cf1) and (is cf2 or is cf2)");
275 $tix = RT::Tickets->new($RT::SystemUser);
276 $tix->FromSQL("CF.SearchTest = 'foo1' OR CF.SearchTest = 'foo3' OR CF.SearchTest2 = 'bar1' OR CF.SearchTest2 = 'bar2'");
277 is($tix->Count, 3, "is cf1 or is cf1 or is cf2 or is cf2");