6 use Test::More tests => 119;
12 my $q = RT::Queue->new( $RT::SystemUser );
13 my $queue = 'SearchTests-'. rand(200);
14 $q->Create( Name => $queue );
16 my ($total, @data, @tickets, %test) = (0, ());
18 sub add_tix_from_data {
21 my $t = RT::Ticket->new($RT::SystemUser);
22 my ( $id, undef $msg ) = $t->Create(
26 ok( $id, "ticket created" ) or diag("error: $msg");
34 my $query_prefix = join ' OR ', map 'id = '. $_->id, @tickets;
35 foreach my $key ( sort keys %test ) {
36 my $tix = RT::Tickets->new($RT::SystemUser);
37 $tix->FromSQL( "( $query_prefix ) AND ( $key )" );
42 $count++ foreach grep $_, values %{ $test{$key} };
43 is($tix->Count, $count, "found correct number of ticket(s) by '$key'") or $error = 1;
46 while ( my $ticket = $tix->Next ) {
47 next if $test{$key}->{ $ticket->Subject };
48 diag $ticket->Subject ." ticket has been found when it's not expected";
51 ok( $good_tickets, "all tickets are good with '$key'" ) or $error = 1;
53 diag "Wrong SQL query for '$key':". $tix->BuildSelectQuery if $error;
58 { Subject => 'xy', Requestor => ['x@example.com', 'y@example.com'] },
59 { Subject => 'x', Requestor => 'x@example.com' },
60 { Subject => 'y', Requestor => 'y@example.com' },
62 { Subject => 'z', Requestor => 'z@example.com' },
65 'Requestor = "x@example.com"' => { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
66 'Requestor != "x@example.com"' => { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
68 'Requestor = "y@example.com"' => { xy => 1, x => 0, y => 1, '-' => 0, z => 0 },
69 'Requestor != "y@example.com"' => { xy => 0, x => 1, y => 0, '-' => 1, z => 1 },
71 'Requestor LIKE "@example.com"' => { xy => 1, x => 1, y => 1, '-' => 0, z => 1 },
72 'Requestor NOT LIKE "@example.com"' => { xy => 0, x => 0, y => 0, '-' => 1, z => 0 },
74 'Requestor IS NULL' => { xy => 0, x => 0, y => 0, '-' => 1, z => 0 },
75 'Requestor IS NOT NULL' => { xy => 1, x => 1, y => 1, '-' => 0, z => 1 },
77 # this test is a todo, we run it later
78 # 'Requestor = "x@example.com" AND Requestor = "y@example.com"' => { xy => 1, x => 0, y => 0, '-' => 0, z => 0 },
79 'Requestor = "x@example.com" OR Requestor = "y@example.com"' => { xy => 1, x => 1, y => 1, '-' => 0, z => 0 },
81 'Requestor != "x@example.com" AND Requestor != "y@example.com"' => { xy => 0, x => 0, y => 0, '-' => 1, z => 1 },
82 'Requestor != "x@example.com" OR Requestor != "y@example.com"' => { xy => 0, x => 1, y => 1, '-' => 1, z => 1 },
84 'Requestor = "x@example.com" AND Requestor != "y@example.com"' => { xy => 0, x => 1, y => 0, '-' => 0, z => 0 },
85 'Requestor = "x@example.com" OR Requestor != "y@example.com"' => { xy => 1, x => 1, y => 0, '-' => 1, z => 1 },
87 'Requestor != "x@example.com" AND Requestor = "y@example.com"' => { xy => 0, x => 0, y => 1, '-' => 0, z => 0 },
88 'Requestor != "x@example.com" OR Requestor = "y@example.com"' => { xy => 1, x => 0, y => 1, '-' => 1, z => 1 },
90 @tickets = add_tix_from_data();
92 my $tix = RT::Tickets->new($RT::SystemUser);
93 $tix->FromSQL("Queue = '$queue'");
94 is($tix->Count, $total, "found $total tickets");
98 # mixing searches by watchers with other conditions
99 # http://rt3.fsck.com/Ticket/Display.html?id=9322
101 'Subject LIKE "x" AND Requestor = "y@example.com"' =>
102 { xy => 1, x => 0, y => 0, '-' => 0, z => 0 },
103 'Subject NOT LIKE "x" AND Requestor = "y@example.com"' =>
104 { xy => 0, x => 0, y => 1, '-' => 0, z => 0 },
105 'Subject LIKE "x" AND Requestor != "y@example.com"' =>
106 { xy => 0, x => 1, y => 0, '-' => 0, z => 0 },
107 'Subject NOT LIKE "x" AND Requestor != "y@example.com"' =>
108 { xy => 0, x => 0, y => 0, '-' => 1, z => 1 },
110 'Subject LIKE "x" OR Requestor = "y@example.com"' =>
111 { xy => 1, x => 1, y => 1, '-' => 0, z => 0 },
112 'Subject NOT LIKE "x" OR Requestor = "y@example.com"' =>
113 { xy => 1, x => 0, y => 1, '-' => 1, z => 1 },
114 'Subject LIKE "x" OR Requestor != "y@example.com"' =>
115 { xy => 1, x => 1, y => 0, '-' => 1, z => 1 },
116 'Subject NOT LIKE "x" OR Requestor != "y@example.com"' =>
117 { xy => 0, x => 1, y => 1, '-' => 1, z => 1 },
119 # group of cases when user doesn't exist in DB at all
120 'Subject LIKE "x" AND Requestor = "not-exist@example.com"' =>
121 { xy => 0, x => 0, y => 0, '-' => 0, z => 0 },
122 'Subject NOT LIKE "x" AND Requestor = "not-exist@example.com"' =>
123 { xy => 0, x => 0, y => 0, '-' => 0, z => 0 },
124 'Subject LIKE "x" AND Requestor != "not-exist@example.com"' =>
125 { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
126 'Subject NOT LIKE "x" AND Requestor != "not-exist@example.com"' =>
127 { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
128 'Subject LIKE "x" OR Requestor = "not-exist@example.com"' =>
129 { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
130 'Subject NOT LIKE "x" OR Requestor = "not-exist@example.com"' =>
131 { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
132 'Subject LIKE "x" OR Requestor != "not-exist@example.com"' =>
133 { xy => 1, x => 1, y => 1, '-' => 1, z => 1 },
134 'Subject NOT LIKE "x" OR Requestor != "not-exist@example.com"' =>
135 { xy => 1, x => 1, y => 1, '-' => 1, z => 1 },
137 'Subject LIKE "z" AND (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
138 { xy => 0, x => 0, y => 0, '-' => 0, z => 0 },
139 'Subject NOT LIKE "z" AND (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
140 { xy => 1, x => 1, y => 1, '-' => 0, z => 0 },
141 'Subject LIKE "z" OR (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
142 { xy => 1, x => 1, y => 1, '-' => 0, z => 1 },
143 'Subject NOT LIKE "z" OR (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
144 { xy => 1, x => 1, y => 1, '-' => 1, z => 0 },
149 local $TODO = "we can't generate this query yet";
151 'Requestor = "x@example.com" AND Requestor = "y@example.com"'
152 => { xy => 1, x => 0, y => 0, '-' => 0, z => 0 },
158 { Subject => 'xy', Cc => ['x@example.com'], Requestor => [ 'y@example.com' ] },
159 { Subject => 'x-', Cc => ['x@example.com'], Requestor => [] },
160 { Subject => '-y', Cc => [], Requestor => [ 'y@example.com' ] },
162 { Subject => 'zz', Cc => ['z@example.com'], Requestor => [ 'z@example.com' ] },
163 { Subject => 'z-', Cc => ['z@example.com'], Requestor => [] },
164 { Subject => '-z', Cc => [], Requestor => [ 'z@example.com' ] },
167 'Cc = "x@example.com" AND Requestor = "y@example.com"' =>
168 { xy => 1, 'x-' => 0, '-y' => 0, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
169 'Cc = "x@example.com" OR Requestor = "y@example.com"' =>
170 { xy => 1, 'x-' => 1, '-y' => 1, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
172 'Cc != "x@example.com" AND Requestor = "y@example.com"' =>
173 { xy => 0, 'x-' => 0, '-y' => 1, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
174 'Cc != "x@example.com" OR Requestor = "y@example.com"' =>
175 { xy => 1, 'x-' => 0, '-y' => 1, '-' => 1, zz => 1, 'z-' => 1, '-z' => 1 },
177 'Cc IS NULL AND Requestor = "y@example.com"' =>
178 { xy => 0, 'x-' => 0, '-y' => 1, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
179 'Cc IS NULL OR Requestor = "y@example.com"' =>
180 { xy => 1, 'x-' => 0, '-y' => 1, '-' => 1, zz => 0, 'z-' => 0, '-z' => 1 },
182 'Cc IS NOT NULL AND Requestor = "y@example.com"' =>
183 { xy => 1, 'x-' => 0, '-y' => 0, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
184 'Cc IS NOT NULL OR Requestor = "y@example.com"' =>
185 { xy => 1, 'x-' => 1, '-y' => 1, '-' => 0, zz => 1, 'z-' => 1, '-z' => 0 },
187 @tickets = add_tix_from_data();
189 my $tix = RT::Tickets->new($RT::SystemUser);
190 $tix->FromSQL("Queue = '$queue'");
191 is($tix->Count, $total, "found $total tickets");
196 # owner is special watcher because reference is duplicated in two places,
197 # owner was an ENUM field now it's WATCHERFIELD, but should support old
198 # style ENUM searches for backward compatibility
199 my $nobody = RT::Nobody();
201 my $tix = RT::Tickets->new($RT::SystemUser);
202 $tix->FromSQL("Queue = '$queue' AND Owner = '". $nobody->id ."'");
203 ok($tix->Count, "found ticket(s)");
206 my $tix = RT::Tickets->new($RT::SystemUser);
207 $tix->FromSQL("Queue = '$queue' AND Owner = '". $nobody->Name ."'");
208 ok($tix->Count, "found ticket(s)");
211 my $tix = RT::Tickets->new($RT::SystemUser);
212 $tix->FromSQL("Queue = '$queue' AND Owner != '". $nobody->id ."'");
213 is($tix->Count, 0, "found ticket(s)");
216 my $tix = RT::Tickets->new($RT::SystemUser);
217 $tix->FromSQL("Queue = '$queue' AND Owner != '". $nobody->Name ."'");
218 is($tix->Count, 0, "found ticket(s)");
222 my $tix = RT::Tickets->new($RT::SystemUser);
223 $tix->FromSQL("Queue = '$queue' AND Owner.Name LIKE 'nob'");
224 ok($tix->Count, "found ticket(s)");
228 # create ticket and force type to not a 'ticket' value
229 # bug #6898@rt3.fsck.com
230 # and http://marc.theaimsgroup.com/?l=rt-devel&m=112662934627236&w=2
231 @data = ( { Subject => 'not a ticket' } );
232 my($t) = add_tix_from_data();
233 $t->_Set( Field => 'Type',
234 Value => 'not a ticket',
236 RecordTransaction => 0,
240 my $tix = RT::Tickets->new($RT::SystemUser);
241 $tix->FromSQL("Queue = '$queue' AND Owner = 'Nobody'");
242 is($tix->Count, $total, "found ticket(s)");
246 my $everyone = RT::Group->new( $RT::SystemUser );
247 $everyone->LoadSystemInternalGroup('Everyone');
248 ok($everyone->id, "loaded 'everyone' group");
249 my($id, $msg) = $everyone->PrincipalObj->GrantRight( Right => 'OwnTicket',
252 ok($id, "granted OwnTicket right to Everyone on '$queue'") or diag("error: $msg");
254 my $u = RT::User->new( $RT::SystemUser );
255 $u->LoadOrCreateByEmail('alpha@example.com');
256 ok($u->id, "loaded user");
257 @data = ( { Subject => '4', Owner => $u->id } );
258 my($t) = add_tix_from_data();
259 is( $t->Owner, $u->id, "created ticket with custom owner" );
260 my $u_alpha_id = $u->id;
262 $u = RT::User->new( $RT::SystemUser );
263 $u->LoadOrCreateByEmail('bravo@example.com');
264 ok($u->id, "loaded user");
265 @data = ( { Subject => '5', Owner => $u->id } );
266 ($t) = add_tix_from_data();
267 is( $t->Owner, $u->id, "created ticket with custom owner" );
268 my $u_bravo_id = $u->id;
270 my $tix = RT::Tickets->new($RT::SystemUser);
271 $tix->FromSQL("Queue = '$queue' AND
272 ( Owner = '$u_alpha_id' OR
273 Owner = '$u_bravo_id' )"
275 is($tix->Count, 2, "found ticket(s)");