Commit | Line | Data |
327368bc |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | |
6 | use lib qw(t/lib); |
7 | use DBICTest; |
8 | use DBIC::SqlMakerTest; |
fcb7fcbb |
9 | use DBIx::Class::SQLMaker::LimitDialects; |
10 | |
11 | my ($TOTAL, $OFFSET) = ( |
12 | DBIx::Class::SQLMaker::LimitDialects->__total_bindtype, |
13 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
14 | ); |
327368bc |
15 | |
16 | my $s = DBICTest->init_schema (no_deploy => 1, ); |
17 | $s->storage->sql_maker->limit_dialect ('RowNum'); |
18 | |
19 | my $rs = $s->resultset ('CD'); |
20 | |
21 | is_same_sql_bind ( |
22 | $rs->search ({}, { rows => 1, offset => 3,columns => [ |
23 | { id => 'foo.id' }, |
24 | { 'bar.id' => 'bar.id' }, |
25 | { bleh => \ 'TO_CHAR (foo.womble, "blah")' }, |
26 | ]})->as_query, |
d9672fb9 |
27 | '( |
28 | SELECT id, bar__id, bleh |
327368bc |
29 | FROM ( |
30 | SELECT id, bar__id, bleh, ROWNUM rownum__index |
31 | FROM ( |
32 | SELECT foo.id AS id, bar.id AS bar__id, TO_CHAR(foo.womble, "blah") AS bleh |
33 | FROM cd me |
34 | ) me |
fcb7fcbb |
35 | WHERE ROWNUM <= ? |
327368bc |
36 | ) me |
fcb7fcbb |
37 | WHERE rownum__index >= ? |
327368bc |
38 | )', |
fcb7fcbb |
39 | [ |
40 | [ $TOTAL => 4 ], |
41 | [ $OFFSET => 4 ], |
42 | ], |
327368bc |
43 | 'Rownum subsel aliasing works correctly' |
44 | ); |
45 | |
f8583f8f |
46 | is_same_sql_bind ( |
d9672fb9 |
47 | $rs->search ({}, { rows => 2, offset => 3,columns => [ |
f8583f8f |
48 | { id => 'foo.id' }, |
49 | { 'ends_with_me.id' => 'ends_with_me.id' }, |
50 | ]})->as_query, |
51 | '(SELECT id, ends_with_me__id |
52 | FROM ( |
53 | SELECT id, ends_with_me__id, ROWNUM rownum__index |
54 | FROM ( |
55 | SELECT foo.id AS id, ends_with_me.id AS ends_with_me__id |
56 | FROM cd me |
57 | ) me |
fcb7fcbb |
58 | WHERE ROWNUM <= ? |
f8583f8f |
59 | ) me |
fcb7fcbb |
60 | WHERE rownum__index >= ? |
f8583f8f |
61 | )', |
fcb7fcbb |
62 | [ |
63 | [ $TOTAL => 5 ], |
64 | [ $OFFSET => 4 ], |
65 | ], |
f8583f8f |
66 | 'Rownum subsel aliasing works correctly' |
67 | ); |
68 | |
d7632687 |
69 | { |
69d3c270 |
70 | my $subq = $s->resultset('Owners')->search({ |
71 | 'count.id' => { -ident => 'owner.id' }, |
72 | }, { alias => 'owner' })->count_rs; |
73 | |
74 | my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search ({}, { |
75 | columns => [ |
76 | { owner_name => 'owner.name' }, |
77 | { owner_books => $subq->as_query }, |
78 | ], |
79 | join => 'owner', |
80 | rows => 2, |
81 | offset => 3, |
82 | }); |
83 | |
84 | is_same_sql_bind( |
85 | $rs_selectas_rel->as_query, |
86 | '( |
87 | SELECT owner_name, owner_books |
88 | FROM ( |
89 | SELECT owner_name, owner_books, ROWNUM rownum__index |
90 | FROM ( |
91 | SELECT owner.name AS owner_name, |
92 | ( SELECT COUNT( * ) FROM owners owner WHERE (count.id = owner.id)) AS owner_books |
93 | FROM books me |
94 | JOIN owners owner ON owner.id = me.owner |
95 | WHERE ( source = ? ) |
96 | ) me |
97 | WHERE ROWNUM <= ? |
98 | ) me |
99 | WHERE rownum__index >= ? |
100 | )', |
101 | [ |
102 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
103 | => 'Library' ], |
104 | [ $TOTAL => 5 ], |
105 | [ $OFFSET => 4 ], |
106 | ], |
107 | |
108 | 'pagination with subquery works' |
109 | ); |
110 | |
111 | } |
112 | |
113 | { |
d7632687 |
114 | $rs = $s->resultset('Artist')->search({}, { |
115 | columns => 'name', |
116 | offset => 1, |
117 | order_by => 'name', |
118 | }); |
119 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
120 | |
121 | like ( |
122 | ${$rs->as_query}->[0], |
123 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
124 | 'Newlines/spaces preserved in final sql', |
125 | ); |
126 | } |
127 | |
f74d22e2 |
128 | { |
129 | my $subq = $s->resultset('Owners')->search({ |
130 | 'books.owner' => { -ident => 'owner.id' }, |
131 | }, { alias => 'owner', select => ['id'] } )->count_rs; |
132 | |
133 | my $rs_selectas_rel = $s->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } ); |
134 | |
135 | is_same_sql_bind( |
136 | $rs_selectas_rel->as_query, |
137 | '( SELECT id, owner FROM ( |
138 | SELECT me.id, me.owner |
139 | FROM books me |
140 | WHERE ( ( (EXISTS (SELECT COUNT( * ) FROM owners owner WHERE ( books.owner = owner.id ))) AND source = ? ) ) |
141 | ) me WHERE ROWNUM <= ? |
142 | )', |
143 | [ |
144 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
145 | [ $TOTAL => 1 ], |
146 | ], |
147 | 'Pagination with sub-query in WHERE works' |
148 | ); |
149 | |
150 | } |
151 | |
d7632687 |
152 | |
327368bc |
153 | done_testing; |