6 use RT::Test tests => 119;
9 my $q = RT::Test->load_or_create_queue( Name => 'Regression' );
10 ok $q && $q->id, 'loaded or created queue';
13 my ($total, @data, @tickets, %test) = (0, ());
15 sub add_tix_from_data {
18 my $t = RT::Ticket->new($RT::SystemUser);
19 my ( $id, undef $msg ) = $t->Create(
23 ok( $id, "ticket created" ) or diag("error: $msg");
31 my $query_prefix = join ' OR ', map 'id = '. $_->id, @tickets;
32 foreach my $key ( sort keys %test ) {
33 my $tix = RT::Tickets->new($RT::SystemUser);
34 $tix->FromSQL( "( $query_prefix ) AND ( $key )" );
39 $count++ foreach grep $_, values %{ $test{$key} };
40 is($tix->Count, $count, "found correct number of ticket(s) by '$key'") or $error = 1;
42 my $good_tickets = ($tix->Count == $count);
43 while ( my $ticket = $tix->Next ) {
44 next if $test{$key}->{ $ticket->Subject };
45 diag $ticket->Subject ." ticket has been found when it's not expected";
48 ok( $good_tickets, "all tickets are good with '$key'" ) or $error = 1;
50 diag "Wrong SQL query for '$key':". $tix->BuildSelectQuery if $error;
55 { Subject => 'xy', Requestor => ['x@example.com', 'y@example.com'] },
56 { Subject => 'x', Requestor => 'x@example.com' },
57 { Subject => 'y', Requestor => 'y@example.com' },
59 { Subject => 'z', Requestor => 'z@example.com' },
62 'Requestor = "x@example.com"' => { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
63 'Requestor != "x@example.com"' => { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
65 'Requestor = "y@example.com"' => { xy => 1, x => 0, y => 1, '-' => 0, z => 0 },
66 'Requestor != "y@example.com"' => { xy => 0, x => 1, y => 0, '-' => 1, z => 1 },
68 'Requestor LIKE "@example.com"' => { xy => 1, x => 1, y => 1, '-' => 0, z => 1 },
69 'Requestor NOT LIKE "@example.com"' => { xy => 0, x => 0, y => 0, '-' => 1, z => 0 },
71 'Requestor IS NULL' => { xy => 0, x => 0, y => 0, '-' => 1, z => 0 },
72 'Requestor IS NOT NULL' => { xy => 1, x => 1, y => 1, '-' => 0, z => 1 },
74 # this test is a todo, we run it later
75 # 'Requestor = "x@example.com" AND Requestor = "y@example.com"' => { xy => 1, x => 0, y => 0, '-' => 0, z => 0 },
76 'Requestor = "x@example.com" OR Requestor = "y@example.com"' => { xy => 1, x => 1, y => 1, '-' => 0, z => 0 },
78 'Requestor != "x@example.com" AND Requestor != "y@example.com"' => { xy => 0, x => 0, y => 0, '-' => 1, z => 1 },
79 'Requestor != "x@example.com" OR Requestor != "y@example.com"' => { xy => 0, x => 1, y => 1, '-' => 1, z => 1 },
81 'Requestor = "x@example.com" AND Requestor != "y@example.com"' => { xy => 0, x => 1, y => 0, '-' => 0, z => 0 },
82 'Requestor = "x@example.com" OR Requestor != "y@example.com"' => { xy => 1, x => 1, y => 0, '-' => 1, z => 1 },
84 'Requestor != "x@example.com" AND Requestor = "y@example.com"' => { xy => 0, x => 0, y => 1, '-' => 0, z => 0 },
85 'Requestor != "x@example.com" OR Requestor = "y@example.com"' => { xy => 1, x => 0, y => 1, '-' => 1, z => 1 },
87 @tickets = add_tix_from_data();
89 my $tix = RT::Tickets->new($RT::SystemUser);
90 $tix->FromSQL("Queue = '$queue'");
91 is($tix->Count, $total, "found $total tickets");
95 # mixing searches by watchers with other conditions
96 # http://rt3.fsck.com/Ticket/Display.html?id=9322
98 'Subject LIKE "x" AND Requestor = "y@example.com"' =>
99 { xy => 1, x => 0, y => 0, '-' => 0, z => 0 },
100 'Subject NOT LIKE "x" AND Requestor = "y@example.com"' =>
101 { xy => 0, x => 0, y => 1, '-' => 0, z => 0 },
102 'Subject LIKE "x" AND Requestor != "y@example.com"' =>
103 { xy => 0, x => 1, y => 0, '-' => 0, z => 0 },
104 'Subject NOT LIKE "x" AND Requestor != "y@example.com"' =>
105 { xy => 0, x => 0, y => 0, '-' => 1, z => 1 },
107 'Subject LIKE "x" OR Requestor = "y@example.com"' =>
108 { xy => 1, x => 1, y => 1, '-' => 0, z => 0 },
109 'Subject NOT LIKE "x" OR Requestor = "y@example.com"' =>
110 { xy => 1, x => 0, y => 1, '-' => 1, z => 1 },
111 'Subject LIKE "x" OR Requestor != "y@example.com"' =>
112 { xy => 1, x => 1, y => 0, '-' => 1, z => 1 },
113 'Subject NOT LIKE "x" OR Requestor != "y@example.com"' =>
114 { xy => 0, x => 1, y => 1, '-' => 1, z => 1 },
116 # group of cases when user doesn't exist in DB at all
117 'Subject LIKE "x" AND Requestor = "not-exist@example.com"' =>
118 { xy => 0, x => 0, y => 0, '-' => 0, z => 0 },
119 'Subject NOT LIKE "x" AND Requestor = "not-exist@example.com"' =>
120 { xy => 0, x => 0, y => 0, '-' => 0, z => 0 },
121 'Subject LIKE "x" AND Requestor != "not-exist@example.com"' =>
122 { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
123 'Subject NOT LIKE "x" AND Requestor != "not-exist@example.com"' =>
124 { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
125 # 'Subject LIKE "x" OR Requestor = "not-exist@example.com"' =>
126 # { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
127 # 'Subject NOT LIKE "x" OR Requestor = "not-exist@example.com"' =>
128 # { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
129 'Subject LIKE "x" OR Requestor != "not-exist@example.com"' =>
130 { xy => 1, x => 1, y => 1, '-' => 1, z => 1 },
131 'Subject NOT LIKE "x" OR Requestor != "not-exist@example.com"' =>
132 { xy => 1, x => 1, y => 1, '-' => 1, z => 1 },
134 'Subject LIKE "z" AND (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
135 { xy => 0, x => 0, y => 0, '-' => 0, z => 0 },
136 'Subject NOT LIKE "z" AND (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
137 { xy => 1, x => 1, y => 1, '-' => 0, z => 0 },
138 'Subject LIKE "z" OR (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
139 { xy => 1, x => 1, y => 1, '-' => 0, z => 1 },
140 'Subject NOT LIKE "z" OR (Requestor = "x@example.com" OR Requestor = "y@example.com")' =>
141 { xy => 1, x => 1, y => 1, '-' => 1, z => 0 },
146 local $TODO = "we can't generate this query yet";
148 'Requestor = "x@example.com" AND Requestor = "y@example.com"'
149 => { xy => 1, x => 0, y => 0, '-' => 0, z => 0 },
150 'Subject LIKE "x" OR Requestor = "not-exist@example.com"' =>
151 { xy => 1, x => 1, y => 0, '-' => 0, z => 0 },
152 'Subject NOT LIKE "x" OR Requestor = "not-exist@example.com"' =>
153 { xy => 0, x => 0, y => 1, '-' => 1, z => 1 },
159 { Subject => 'xy', Cc => ['x@example.com'], Requestor => [ 'y@example.com' ] },
160 { Subject => 'x-', Cc => ['x@example.com'], Requestor => [] },
161 { Subject => '-y', Cc => [], Requestor => [ 'y@example.com' ] },
163 { Subject => 'zz', Cc => ['z@example.com'], Requestor => [ 'z@example.com' ] },
164 { Subject => 'z-', Cc => ['z@example.com'], Requestor => [] },
165 { Subject => '-z', Cc => [], Requestor => [ 'z@example.com' ] },
168 'Cc = "x@example.com" AND Requestor = "y@example.com"' =>
169 { xy => 1, 'x-' => 0, '-y' => 0, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
170 'Cc = "x@example.com" OR Requestor = "y@example.com"' =>
171 { xy => 1, 'x-' => 1, '-y' => 1, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
173 'Cc != "x@example.com" AND Requestor = "y@example.com"' =>
174 { xy => 0, 'x-' => 0, '-y' => 1, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
175 'Cc != "x@example.com" OR Requestor = "y@example.com"' =>
176 { xy => 1, 'x-' => 0, '-y' => 1, '-' => 1, zz => 1, 'z-' => 1, '-z' => 1 },
178 'Cc IS NULL AND Requestor = "y@example.com"' =>
179 { xy => 0, 'x-' => 0, '-y' => 1, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
180 'Cc IS NULL OR Requestor = "y@example.com"' =>
181 { xy => 1, 'x-' => 0, '-y' => 1, '-' => 1, zz => 0, 'z-' => 0, '-z' => 1 },
183 'Cc IS NOT NULL AND Requestor = "y@example.com"' =>
184 { xy => 1, 'x-' => 0, '-y' => 0, '-' => 0, zz => 0, 'z-' => 0, '-z' => 0 },
185 'Cc IS NOT NULL OR Requestor = "y@example.com"' =>
186 { xy => 1, 'x-' => 1, '-y' => 1, '-' => 0, zz => 1, 'z-' => 1, '-z' => 0 },
188 @tickets = add_tix_from_data();
190 my $tix = RT::Tickets->new($RT::SystemUser);
191 $tix->FromSQL("Queue = '$queue'");
192 is($tix->Count, $total, "found $total tickets");
197 # owner is special watcher because reference is duplicated in two places,
198 # owner was an ENUM field now it's WATCHERFIELD, but should support old
199 # style ENUM searches for backward compatibility
200 my $nobody = RT::Nobody();
202 my $tix = RT::Tickets->new($RT::SystemUser);
203 $tix->FromSQL("Queue = '$queue' AND Owner = '". $nobody->id ."'");
204 ok($tix->Count, "found ticket(s)");
207 my $tix = RT::Tickets->new($RT::SystemUser);
208 $tix->FromSQL("Queue = '$queue' AND Owner = '". $nobody->Name ."'");
209 ok($tix->Count, "found ticket(s)");
212 my $tix = RT::Tickets->new($RT::SystemUser);
213 $tix->FromSQL("Queue = '$queue' AND Owner != '". $nobody->id ."'");
214 is($tix->Count, 0, "found ticket(s)");
217 my $tix = RT::Tickets->new($RT::SystemUser);
218 $tix->FromSQL("Queue = '$queue' AND Owner != '". $nobody->Name ."'");
219 is($tix->Count, 0, "found ticket(s)");
223 my $tix = RT::Tickets->new($RT::SystemUser);
224 $tix->FromSQL("Queue = '$queue' AND Owner.Name LIKE 'nob'");
225 ok($tix->Count, "found ticket(s)");
229 # create ticket and force type to not a 'ticket' value
230 # bug #6898@rt3.fsck.com
231 # and http://marc.theaimsgroup.com/?l=rt-devel&m=112662934627236&w=2
232 @data = ( { Subject => 'not a ticket' } );
233 my($t) = add_tix_from_data();
234 $t->_Set( Field => 'Type',
235 Value => 'not a ticket',
237 RecordTransaction => 0,
241 my $tix = RT::Tickets->new($RT::SystemUser);
242 $tix->FromSQL("Queue = '$queue' AND Owner = 'Nobody'");
243 is($tix->Count, $total, "found ticket(s)");
247 my $everyone = RT::Group->new( $RT::SystemUser );
248 $everyone->LoadSystemInternalGroup('Everyone');
249 ok($everyone->id, "loaded 'everyone' group");
250 my($id, $msg) = $everyone->PrincipalObj->GrantRight( Right => 'OwnTicket',
253 ok($id, "granted OwnTicket right to Everyone on '$queue'") or diag("error: $msg");
255 my $u = RT::User->new( $RT::SystemUser );
256 $u->LoadOrCreateByEmail('alpha@example.com');
257 ok($u->id, "loaded user");
258 @data = ( { Subject => '4', Owner => $u->id } );
259 my($t) = add_tix_from_data();
260 is( $t->Owner, $u->id, "created ticket with custom owner" );
261 my $u_alpha_id = $u->id;
263 $u = RT::User->new( $RT::SystemUser );
264 $u->LoadOrCreateByEmail('bravo@example.com');
265 ok($u->id, "loaded user");
266 @data = ( { Subject => '5', Owner => $u->id } );
267 ($t) = add_tix_from_data();
268 is( $t->Owner, $u->id, "created ticket with custom owner" );
269 my $u_bravo_id = $u->id;
271 my $tix = RT::Tickets->new($RT::SystemUser);
272 $tix->FromSQL("Queue = '$queue' AND
273 ( Owner = '$u_alpha_id' OR
274 Owner = '$u_bravo_id' )"
276 is($tix->Count, 2, "found ticket(s)");