Fix invalid ORDER BY hash key test
[dbsrgits/SQL-Abstract.git] / t / 00new.t
CommitLineData
41751122 1#!/usr/bin/perl
32eab2da 2
3use strict;
41751122 4use warnings;
5use Test::More;
32eab2da 6
5aad8cf3 7use SQL::Abstract::Test import => ['is_same_sql_bind'];
32eab2da 8
96449e8e 9#LDNOTE: renamed all "bind" into "where" because that's what they are
10
32eab2da 11my @handle_tests = (
12 #1
13 {
14 args => {logic => 'OR'},
96449e8e 15# stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )'
16# LDNOTE: modified the line above (changing the test suite!!!) because
17# the test was not consistent with the doc: hashrefs should not be
18# influenced by the current logic, they always mean 'AND'. So
19# { a => 4, b => 0} should ALWAYS mean ( a = ? AND b = ? ).
e30faf88 20#
21# acked by RIBASUSHI
96449e8e 22 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
32eab2da 23 },
24 #2
25 {
26 args => {},
27 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
28 },
29 #3
30 {
31 args => {case => "upper"},
32 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
33 },
34 #4
35 {
36 args => {case => "upper", cmp => "="},
37 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
38 },
39 #5
40 {
41 args => {cmp => "=", logic => 'or'},
96449e8e 42# LDNOTE idem
43# stmt => 'SELECT * FROM test WHERE ( a = ? OR b = ? )'
e30faf88 44# acked by RIBASUSHI
96449e8e 45 stmt => 'SELECT * FROM test WHERE ( a = ? AND b = ? )'
32eab2da 46 },
47 #6
48 {
49 args => {cmp => "like"},
50 stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )'
51 },
52 #7
53 {
54 args => {logic => "or", cmp => "like"},
96449e8e 55# LDNOTE idem
56# stmt => 'SELECT * FROM test WHERE ( a LIKE ? OR b LIKE ? )'
e30faf88 57# acked by RIBASUSHI
96449e8e 58 stmt => 'SELECT * FROM test WHERE ( a LIKE ? AND b LIKE ? )'
32eab2da 59 },
60 #8
61 {
62 args => {case => "lower"},
63 stmt => 'select * from test where ( a = ? and b = ? )'
64 },
65 #9
66 {
67 args => {case => "lower", cmp => "="},
68 stmt => 'select * from test where ( a = ? and b = ? )'
69 },
70 #10
71 {
72 args => {case => "lower", cmp => "like"},
73 stmt => 'select * from test where ( a like ? and b like ? )'
74 },
75 #11
76 {
77 args => {case => "lower", convert => "lower", cmp => "like"},
78 stmt => 'select * from test where ( lower(a) like lower(?) and lower(b) like lower(?) )'
79 },
80 #12
81 {
82 args => {convert => "Round"},
83 stmt => 'SELECT * FROM test WHERE ( ROUND(a) = ROUND(?) AND ROUND(b) = ROUND(?) )',
84 },
85 #13
86 {
87 args => {convert => "lower"},
88 stmt => 'SELECT * FROM test WHERE ( ( LOWER(ticket) = LOWER(?) ) OR ( LOWER(hostname) = LOWER(?) ) OR ( LOWER(taco) = LOWER(?) ) OR ( LOWER(salami) = LOWER(?) ) )',
96449e8e 89 where => [ { ticket => 11 }, { hostname => 11 }, { taco => 'salad' }, { salami => 'punch' } ],
32eab2da 90 },
91 #14
92 {
93 args => {convert => "upper"},
e30faf88 94 stmt => 'SELECT * FROM test WHERE ( ( UPPER(hostname) IN ( UPPER(?), UPPER(?), UPPER(?), UPPER(?) ) AND ( ( UPPER(ticket) = UPPER(?) ) OR ( UPPER(ticket) = UPPER(?) ) OR ( UPPER(ticket) = UPPER(?) ) ) ) OR ( UPPER(tack) BETWEEN UPPER(?) AND UPPER(?) ) OR ( ( ( UPPER(a) = UPPER(?) ) OR ( UPPER(a) = UPPER(?) ) OR ( UPPER(a) = UPPER(?) ) ) AND ( ( UPPER(e) != UPPER(?) ) OR ( UPPER(e) != UPPER(?) ) ) AND UPPER(q) NOT IN ( UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?), UPPER(?) ) ) )',
96449e8e 95 where => [ { ticket => [11, 12, 13],
96 hostname => { in => ['ntf', 'avd', 'bvd', '123'] } },
32eab2da 97 { tack => { between => [qw/tick tock/] } },
96449e8e 98 { a => [qw/b c d/],
99 e => { '!=', [qw(f g)] },
100 q => { 'not in', [14..20] } } ],
32eab2da 101 },
102);
103
6dcf723c 104
6dcf723c 105use_ok('SQL::Abstract');
106
32eab2da 107for (@handle_tests) {
96449e8e 108 local $" = ', ';
109 #print "creating a handle with args ($_->{args}): ";
110 my $sql = SQL::Abstract->new($_->{args});
111 my $where = $_->{where} || { a => 4, b => 0};
112 my($stmt, @bind) = $sql->select('test', '*', $where);
113
114 # LDNOTE: this original test suite from NWIGER did no comparisons
115 # on @bind values, just checking if @bind is nonempty.
116 # So here we just fake a [1] bind value for the comparison.
117 is_same_sql_bind($stmt, [@bind ? 1 : 0], $_->{stmt}, [1]);
32eab2da 118}
119
10e6c946 120done_testing;