Added tests and updated POD for bindtype = 'columns' case in \[$sql, @bind] when...
[dbsrgits/SQL-Abstract.git] / t / 10test.t
CommitLineData
32c34379 1#!/usr/bin/perl
2
3use strict;
4use warnings;
5use List::Util qw(sum);
32c34379 6
7use Test::More;
8
9
25823711 10my @sql_tests = (
11 # WHERE condition - equal
12 {
13 equal => 1,
14 statements => [
15 q/SELECT foo FROM bar WHERE a = 1/,
16 q/SELECT foo FROM bar WHERE a=1/,
17 q/SELECT foo FROM bar WHERE (a = 1)/,
18 q/SELECT foo FROM bar WHERE (a=1)/,
19 q/SELECT foo FROM bar WHERE ( a = 1 )/,
20 q/
21 SELECT
22 foo
23 FROM
24 bar
25 WHERE
26 a = 1
27 /,
28 q/
29 SELECT
30 foo
31 FROM
32 bar
33 WHERE
34 (a = 1)
35 /,
36 q/
37 SELECT
38 foo
39 FROM
40 bar
41 WHERE
42 ( a = 1 )
43 /,
44 q/SELECT foo FROM bar WHERE ((a = 1))/,
45 q/SELECT foo FROM bar WHERE ( (a = 1) )/,
46 q/SELECT foo FROM bar WHERE ( ( a = 1 ) )/,
47 ]
48 },
49 {
50 equal => 1,
51 statements => [
52 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
53 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 1)/,
54 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 1))/,
55 q/SELECT foo FROM bar WHERE (a = 1 AND b = 1)/,
56 q/SELECT foo FROM bar WHERE ((a = 1 AND b = 1))/,
57 q/SELECT foo FROM bar WHERE (((a = 1) AND (b = 1)))/,
58 q/
59 SELECT
60 foo
61 FROM
62 bar
63 WHERE
64 a = 1
65 AND
66 b = 1
67 /,
68 q/
69 SELECT
70 foo
71 FROM
72 bar
73 WHERE
74 (a = 1
75 AND
76 b = 1)
77 /,
78 q/
79 SELECT
80 foo
81 FROM
82 bar
83 WHERE
84 (a = 1)
85 AND
86 (b = 1)
87 /,
88 q/
89 SELECT
90 foo
91 FROM
92 bar
93 WHERE
94 ((a = 1)
95 AND
96 (b = 1))
97 /,
98 ]
99 },
100
101 # WHERE condition - different
102 {
103 equal => 0,
104 statements => [
105 q/SELECT foo FROM bar WHERE a = 1/,
106 q/SELECT quux FROM bar WHERE a = 1/,
107 q/SELECT foo FROM quux WHERE a = 1/,
108 q/FOOBAR foo FROM bar WHERE a = 1/,
109
110 q/SELECT foo FROM bar WHERE a = 2/,
111 q/SELECT foo FROM bar WHERE a < 1/,
112 q/SELECT foo FROM bar WHERE b = 1/,
113 q/SELECT foo FROM bar WHERE (c = 1)/,
114 q/SELECT foo FROM bar WHERE (d = 1)/,
115
116 q/SELECT foo FROM bar WHERE a = 1 AND quux/,
117 q/SELECT foo FROM bar WHERE a = 1 GROUP BY foo/,
118 q/SELECT foo FROM bar WHERE a = 1 ORDER BY foo/,
119 q/SELECT foo FROM bar WHERE a = 1 LIMIT 1/,
120 q/SELECT foo FROM bar WHERE a = 1 OFFSET 1/,
121 q/SELECT foo FROM bar JOIN quux WHERE a = 1/,
122 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1/,
123 ]
124 },
125 {
126 equal => 0,
127 statements => [
128 q/SELECT foo FROM bar WHERE a = 1 AND b = 1/,
129 q/SELECT quux FROM bar WHERE a = 1 AND b = 1/,
130 q/SELECT foo FROM quux WHERE a = 1 AND b = 1/,
131 q/FOOBAR foo FROM bar WHERE a = 1 AND b = 1/,
132
133 q/SELECT foo FROM bar WHERE a = 2 AND b = 1/,
134 q/SELECT foo FROM bar WHERE a = 3 AND (b = 1)/,
135 q/SELECT foo FROM bar WHERE (a = 4) AND b = 1/,
136 q/SELECT foo FROM bar WHERE (a = 5) AND (b = 1)/,
137 q/SELECT foo FROM bar WHERE ((a = 6) AND (b = 1))/,
138 q/SELECT foo FROM bar WHERE ((a = 7) AND (b = 1))/,
139
140 q/SELECT foo FROM bar WHERE a = 1 AND b = 2/,
141 q/SELECT foo FROM bar WHERE a = 1 AND (b = 3)/,
142 q/SELECT foo FROM bar WHERE (a = 1) AND b = 4/,
143 q/SELECT foo FROM bar WHERE (a = 1) AND (b = 5)/,
144 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 6))/,
145 q/SELECT foo FROM bar WHERE ((a = 1) AND (b = 7))/,
146
147 q/SELECT foo FROM bar WHERE a < 1 AND b = 1/,
148 q/SELECT foo FROM bar WHERE b = 1 AND b = 1/,
149 q/SELECT foo FROM bar WHERE (c = 1) AND b = 1/,
150 q/SELECT foo FROM bar WHERE (d = 1) AND b = 1/,
151
152 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 AND quux/,
153 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 GROUP BY foo/,
154 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 ORDER BY foo/,
155 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 LIMIT 1/,
156 q/SELECT foo FROM bar WHERE a = 1 AND b = 1 OFFSET 1/,
157 q/SELECT foo FROM bar JOIN quux WHERE a = 1 AND b = 1/,
158 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE a = 1 AND b = 1/,
159 ]
160 },
161
162 # JOIN condition - equal
163 {
164 equal => 1,
165 statements => [
166 q/SELECT foo FROM bar JOIN baz ON a = 1 WHERE x = 1/,
167 q/SELECT foo FROM bar JOIN baz ON a=1 WHERE x = 1/,
168 q/SELECT foo FROM bar JOIN baz ON (a = 1) WHERE x = 1/,
169 q/SELECT foo FROM bar JOIN baz ON (a=1) WHERE x = 1/,
170 q/SELECT foo FROM bar JOIN baz ON ( a = 1 ) WHERE x = 1/,
171 q/
172 SELECT
173 foo
174 FROM
175 bar
176 JOIN
177 baz
178 ON
179 a = 1
180 WHERE
181 x = 1
182 /,
183 q/
184 SELECT
185 foo
186 FROM
187 bar
188 JOIN
189 baz
190 ON
191 (a = 1)
192 WHERE
193 x = 1
194 /,
195 q/
196 SELECT
197 foo
198 FROM
199 bar
200 JOIN
201 baz
202 ON
203 ( a = 1 )
204 WHERE
205 x = 1
206 /,
207 q/SELECT foo FROM bar JOIN baz ON ((a = 1)) WHERE x = 1/,
208 q/SELECT foo FROM bar JOIN baz ON ( (a = 1) ) WHERE x = 1/,
209 q/SELECT foo FROM bar JOIN baz ON ( ( a = 1 ) ) WHERE x = 1/,
210 ]
211 },
212 {
213 equal => 1,
214 statements => [
215 q/SELECT foo FROM bar JOIN baz ON a = 1 AND b = 1 WHERE x = 1/,
216 q/SELECT foo FROM bar JOIN baz ON (a = 1) AND (b = 1) WHERE x = 1/,
217 q/SELECT foo FROM bar JOIN baz ON ((a = 1) AND (b = 1)) WHERE x = 1/,
218 q/SELECT foo FROM bar JOIN baz ON (a = 1 AND b = 1) WHERE x = 1/,
219 q/SELECT foo FROM bar JOIN baz ON ((a = 1 AND b = 1)) WHERE x = 1/,
220 q/SELECT foo FROM bar JOIN baz ON (((a = 1) AND (b = 1))) WHERE x = 1/,
221 q/
222 SELECT
223 foo
224 FROM
225 bar
226 JOIN
227 baz
228 ON
229 a = 1
230 AND
231 b = 1
232 WHERE
233 x = 1
234 /,
235 q/
236 SELECT
237 foo
238 FROM
239 bar
240 JOIN
241 baz
242 ON
243 (a = 1
244 AND
245 b = 1)
246 WHERE
247 x = 1
248 /,
249 q/
250 SELECT
251 foo
252 FROM
253 bar
254 JOIN
255 baz
256 ON
257 (a = 1)
258 AND
259 (b = 1)
260 WHERE
261 x = 1
262 /,
263 q/
264 SELECT
265 foo
266 FROM
267 bar
268 JOIN
269 baz
270 ON
271 ((a = 1)
272 AND
273 (b = 1))
274 WHERE
275 x = 1
276 /,
277 ]
278 },
279
280 # JOIN condition - different
281 {
282 equal => 0,
283 statements => [
284 q/SELECT foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
285 q/SELECT quux FROM bar JOIN quux ON a = 1 WHERE quuux/,
286 q/SELECT foo FROM quux JOIN quux ON a = 1 WHERE quuux/,
287 q/FOOBAR foo FROM bar JOIN quux ON a = 1 WHERE quuux/,
288
289 q/SELECT foo FROM bar JOIN quux ON a = 2 WHERE quuux/,
290 q/SELECT foo FROM bar JOIN quux ON a < 1 WHERE quuux/,
291 q/SELECT foo FROM bar JOIN quux ON b = 1 WHERE quuux/,
292 q/SELECT foo FROM bar JOIN quux ON (c = 1) WHERE quuux/,
293 q/SELECT foo FROM bar JOIN quux ON (d = 1) WHERE quuux/,
294
295 q/SELECT foo FROM bar JOIN quux ON a = 1 AND quuux/,
296 q/SELECT foo FROM bar JOIN quux ON a = 1 GROUP BY foo/,
297 q/SELECT foo FROM bar JOIN quux ON a = 1 ORDER BY foo/,
298 q/SELECT foo FROM bar JOIN quux ON a = 1 LIMIT 1/,
299 q/SELECT foo FROM bar JOIN quux ON a = 1 OFFSET 1/,
300 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux/,
301 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1/,
302 ]
303 },
304 {
305 equal => 0,
306 statements => [
307 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
308 q/SELECT quux FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
309 q/SELECT foo FROM quux JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
310 q/FOOBAR foo FROM bar JOIN quux ON a = 1 AND b = 1 WHERE quuux/,
311
312 q/SELECT foo FROM bar JOIN quux ON a = 2 AND b = 1 WHERE quuux/,
313 q/SELECT foo FROM bar JOIN quux ON a = 3 AND (b = 1) WHERE quuux/,
314 q/SELECT foo FROM bar JOIN quux ON (a = 4) AND b = 1 WHERE quuux/,
315 q/SELECT foo FROM bar JOIN quux ON (a = 5) AND (b = 1) WHERE quuux/,
316 q/SELECT foo FROM bar JOIN quux ON ((a = 6) AND (b = 1)) WHERE quuux/,
317 q/SELECT foo FROM bar JOIN quux ON ((a = 7) AND (b = 1)) WHERE quuux/,
318
319 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 2 WHERE quuux/,
320 q/SELECT foo FROM bar JOIN quux ON a = 1 AND (b = 3) WHERE quuux/,
321 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND b = 4 WHERE quuux/,
322 q/SELECT foo FROM bar JOIN quux ON (a = 1) AND (b = 5) WHERE quuux/,
323 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 6)) WHERE quuux/,
324 q/SELECT foo FROM bar JOIN quux ON ((a = 1) AND (b = 7)) WHERE quuux/,
325
326 q/SELECT foo FROM bar JOIN quux ON a < 1 AND b = 1 WHERE quuux/,
327 q/SELECT foo FROM bar JOIN quux ON b = 1 AND b = 1 WHERE quuux/,
328 q/SELECT foo FROM bar JOIN quux ON (c = 1) AND b = 1 WHERE quuux/,
329 q/SELECT foo FROM bar JOIN quux ON (d = 1) AND b = 1 WHERE quuux/,
330
331 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 AND quuux/,
332 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 GROUP BY foo/,
333 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 ORDER BY foo/,
334 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 LIMIT 1/,
335 q/SELECT foo FROM bar JOIN quux ON a = 1 AND b = 1 OFFSET 1/,
336 q/SELECT foo FROM bar JOIN quux JOIN quuux ON a = 1 AND b = 1/,
337 q/SELECT foo FROM bar JOIN quux ON a = 1 JOIN quuux ON a = 1 AND b = 1/,
338 ]
339 },
340
341 # DISTINCT ON (...) not confused with JOIN ON (...)
342 {
343 equal => 1,
344 statements => [
345 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a = 1/,
346 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE a=1/,
347 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a = 1)/,
348 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE (a=1)/,
349 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( a = 1 )/,
350 q/
351 SELECT DISTINCT ON (foo, quux)
352 foo,
353 quux
354 FROM
355 bar
356 WHERE
357 a = 1
358 /,
359 q/
360 SELECT DISTINCT ON (foo, quux)
361 foo,
362 quux
363 FROM
364 bar
365 WHERE
366 (a = 1)
367 /,
368 q/
369 SELECT DISTINCT ON (foo, quux)
370 foo,
371 quux
372 FROM
373 bar
374 WHERE
375 ( a = 1 )
376 /,
377 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ((a = 1))/,
378 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( (a = 1) )/,
379 q/SELECT DISTINCT ON (foo, quux) foo, quux FROM bar WHERE ( ( a = 1 ) )/,
380 ]
381 },
382
383 # subselects - equal
384 {
385 equal => 1,
386 statements => [
387 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
388 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 1)/,
389 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 1/,
390 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 1)/,
391 ]
392 },
393 {
394 equal => 1,
395 statements => [
396 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
397 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE a = 1/,
398 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE a = 1/,
399 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE a = 1/,
400 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE a = 1/,
401
402 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE (a = 1)/,
403 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 1)) AS foo WHERE (a = 1)/,
404 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 1) AS foo WHERE (a = 1)/,
405 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 1)) AS foo WHERE (a = 1)/,
406 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 1))) AS foo WHERE (a = 1)/,
407 ]
408 },
409
410 # subselects - different
411 {
412 equal => 0,
413 statements => [
414 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 1/,
415 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE a = 2/,
416 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1) AS foo WHERE (a = 3)/,
417 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE a = 4/,
418 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1)) AS foo WHERE (a = 5)/,
419 q/SELECT * FROM (SELECT * FROM bar WHERE b = 2) AS foo WHERE a = 1/,
420 q/SELECT * FROM (SELECT * FROM bar WHERE b = 3) AS foo WHERE (a = 1)/,
421 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 4)) AS foo WHERE a = 1/,
422 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 5)) AS foo WHERE (a = 1)/,
423 ]
424 },
425 {
426 equal => 0,
427 statements => [
428 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 1/,
429 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 1/,
430 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 1/,
431 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 1/,
432 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 1/,
433
434 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 1)/,
435 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 1)/,
436 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 1)/,
437 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 1)/,
438 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 1)/,
439
440 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 1) AS foo WHERE a = 2/,
441 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 2) AS foo WHERE a = 2/,
442 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 3)) AS foo WHERE a = 2/,
443 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 4)) AS foo WHERE a = 2/,
444 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 5))) AS foo WHERE a = 2/,
445
446 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND c = 6) AS foo WHERE (a = 2)/,
447 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND c = 7) AS foo WHERE (a = 2)/,
448 q/SELECT * FROM (SELECT * FROM bar WHERE b = 1 AND (c = 8)) AS foo WHERE (a = 2)/,
449 q/SELECT * FROM (SELECT * FROM bar WHERE (b = 1) AND (c = 9)) AS foo WHERE (a = 2)/,
450 q/SELECT * FROM (SELECT * FROM bar WHERE ((b = 1) AND (c = 10))) AS foo WHERE (a = 2)/,
451 ]
452 },
453);
454
32c34379 455my @bind_tests = (
456 # scalar - equal
457 {
458 equal => 1,
459 bindvals => [
460 undef,
461 undef,
462 ]
463 },
464 {
465 equal => 1,
466 bindvals => [
467 'foo',
468 'foo',
469 ]
470 },
471 {
472 equal => 1,
473 bindvals => [
474 42,
475 42,
476 '42',
477 ]
478 },
479
480 # scalarref - equal
481 {
482 equal => 1,
483 bindvals => [
484 \'foo',
485 \'foo',
486 ]
487 },
488 {
489 equal => 1,
490 bindvals => [
491 \42,
492 \42,
493 \'42',
494 ]
495 },
496
497 # arrayref - equal
498 {
499 equal => 1,
500 bindvals => [
501 [],
502 []
503 ]
504 },
505 {
506 equal => 1,
507 bindvals => [
508 [42],
509 [42],
510 ['42'],
511 ]
512 },
513 {
514 equal => 1,
515 bindvals => [
516 [1, 42],
517 [1, 42],
518 ['1', 42],
519 [1, '42'],
520 ['1', '42'],
521 ]
522 },
523
524 # hashref - equal
525 {
526 equal => 1,
527 bindvals => [
528 { foo => 42 },
529 { foo => 42 },
530 { foo => '42' },
531 ]
532 },
533 {
534 equal => 1,
535 bindvals => [
536 { foo => 42, bar => 1 },
537 { foo => 42, bar => 1 },
538 { foo => '42', bar => 1 },
539 ]
540 },
541
542 # blessed object - equal
543 {
544 equal => 1,
545 bindvals => [
546 bless(\(local $_ = 42), 'Life::Universe::Everything'),
547 bless(\(local $_ = 42), 'Life::Universe::Everything'),
548 ]
549 },
550 {
551 equal => 1,
552 bindvals => [
553 bless([42], 'Life::Universe::Everything'),
554 bless([42], 'Life::Universe::Everything'),
555 ]
556 },
557 {
558 equal => 1,
559 bindvals => [
560 bless({ answer => 42 }, 'Life::Universe::Everything'),
561 bless({ answer => 42 }, 'Life::Universe::Everything'),
562 ]
563 },
564
565 # complex data structure - equal
566 {
567 equal => 1,
568 bindvals => [
569 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
570 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
571 ]
572 },
573
574
575 # scalar - different
576 {
577 equal => 0,
578 bindvals => [
579 undef,
580 'foo',
581 42,
582 ]
583 },
584
585 # scalarref - different
586 {
587 equal => 0,
588 bindvals => [
589 \undef,
590 \'foo',
591 \42,
592 ]
593 },
594
595 # arrayref - different
596 {
597 equal => 0,
598 bindvals => [
599 [undef],
600 ['foo'],
601 [42],
602 ]
603 },
604
605 # hashref - different
606 {
607 equal => 0,
608 bindvals => [
609 { foo => undef },
610 { foo => 'bar' },
611 { foo => 42 },
612 ]
613 },
614
615 # different types
616 {
617 equal => 0,
618 bindvals => [
619 'foo',
620 \'foo',
621 ['foo'],
622 { foo => 'bar' },
623 ]
624 },
625
626 # complex data structure - different
627 {
628 equal => 0,
629 bindvals => [
630 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
631 [43, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
632 [42, { foo => 'baz', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
633 [42, { bar => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
634 [42, { foo => 'bar', quuux => [1, 2, \3, { quux => [4, 5] } ] }, 8 ],
635 [42, { foo => 'bar', quux => [0, 1, 2, \3, { quux => [4, 5] } ] }, 8 ],
636 [42, { foo => 'bar', quux => [1, 2, 3, { quux => [4, 5] } ] }, 8 ],
637 [42, { foo => 'bar', quux => [1, 2, \4, { quux => [4, 5] } ] }, 8 ],
638 [42, { foo => 'bar', quux => [1, 2, \3, { quuux => [4, 5] } ] }, 8 ],
639 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5, 6] } ] }, 8 ],
640 [42, { foo => 'bar', quux => [1, 2, \3, { quux => 4 } ] }, 8 ],
641 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5], quuux => 1 } ] }, 8 ],
642 [42, { foo => 'bar', quux => [1, 2, \3, { quux => [4, 5] } ] }, 8, 9 ],
643 ]
644 },
645);
646
25823711 647plan tests => 1 +
648 sum(
649 map { $_ * ($_ - 1) / 2 }
650 map { scalar @{$_->{statements}} }
651 @sql_tests
652 ) +
653 sum(
654 map { $_ * ($_ - 1) / 2 }
655 map { scalar @{$_->{bindvals}} }
656 @bind_tests
e7827ba2 657 ) +
658 3;
32c34379 659
e7827ba2 660use_ok('SQL::Abstract::Test', import => [qw(
661 eq_sql_bind eq_sql eq_bind is_same_sql_bind
662)]);
32c34379 663
25823711 664for my $test (@sql_tests) {
665 my $statements = $test->{statements};
666 while (@$statements) {
667 my $sql1 = shift @$statements;
668 foreach my $sql2 (@$statements) {
669 my $equal = eq_sql($sql1, $sql2);
670 if ($test->{equal}) {
671 ok($equal, "equal SQL expressions considered equal");
672 } else {
673 ok(!$equal, "different SQL expressions considered not equal");
674 }
675
676 if ($equal ^ $test->{equal}) {
677 diag("sql1: $sql1");
678 diag("sql2: $sql2");
679 }
680 }
681 }
682}
683
32c34379 684for my $test (@bind_tests) {
685 my $bindvals = $test->{bindvals};
686 while (@$bindvals) {
687 my $bind1 = shift @$bindvals;
688 foreach my $bind2 (@$bindvals) {
689 my $equal = eq_bind($bind1, $bind2);
690 if ($test->{equal}) {
691 ok($equal, "equal bind values considered equal");
692 } else {
693 ok(!$equal, "different bind values considered not equal");
694 }
695
696 if ($equal ^ $test->{equal}) {
697 diag("bind1: " . Dumper($bind1));
698 diag("bind2: " . Dumper($bind2));
699 }
700 }
701 }
702}
e7827ba2 703
704ok(eq_sql_bind(
705 "SELECT * FROM foo WHERE id = ?", [42],
706 "SELECT * FROM foo WHERE (id = ?)", [42],
707 ),
708 "eq_sql_bind considers equal SQL expressions and bind values equal"
709);
710
711
712ok(!eq_sql_bind(
713 "SELECT * FROM foo WHERE id = ?", [42],
714 "SELECT * FROM foo WHERE (id = ?)", [0],
715 ),
716 "eq_sql_bind considers equal SQL expressions and different bind values different"
717);
718
719ok(!eq_sql_bind(
720 "SELECT * FROM foo WHERE id = ?", [42],
721 "SELECT * FROM bar WHERE (id = ?)", [42],
722 ),
723 "eq_sql_bind considers different SQL expressions and equal bind values different"
724);