Commit | Line | Data |
c0329273 |
1 | BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) } |
2 | |
a5f843e3 |
3 | use strict; |
4 | use warnings; |
5 | |
6 | use Test::More; |
c0329273 |
7 | |
a5a7bb73 |
8 | use DBICTest ':DiffSQL'; |
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 | ); |
a5f843e3 |
15 | |
16 | my $schema = DBICTest->init_schema; |
17 | |
a5f843e3 |
18 | $schema->storage->_sql_maker->limit_dialect ('RowNumberOver'); |
19 | |
41eac664 |
20 | my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, { |
21 | '+select' => ['owner.name'], |
22 | '+as' => ['owner.name'], |
23 | join => 'owner', |
24 | rows => 1, |
25 | }); |
a5f843e3 |
26 | |
41eac664 |
27 | is_same_sql_bind( |
28 | $rs_selectas_col->as_query, |
29 | '( |
90ed89cb |
30 | SELECT me.id, me.source, me.owner, me.title, me.price, |
760545fa |
31 | owner__name |
41eac664 |
32 | FROM ( |
90ed89cb |
33 | SELECT me.id, me.source, me.owner, me.title, me.price, |
760545fa |
34 | owner__name, |
41eac664 |
35 | ROW_NUMBER() OVER( ) AS rno__row__index |
36 | FROM ( |
37 | SELECT me.id, me.source, me.owner, me.title, me.price, |
760545fa |
38 | owner.name AS owner__name |
41eac664 |
39 | FROM books me |
40 | JOIN owners owner ON owner.id = me.owner |
41 | WHERE ( source = ? ) |
42 | ) me |
43 | ) me |
fcb7fcbb |
44 | WHERE rno__row__index >= ? AND rno__row__index <= ? |
41eac664 |
45 | )', |
fcb7fcbb |
46 | [ |
47 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
48 | [ $OFFSET => 1 ], |
49 | [ $TOTAL => 1 ], |
50 | ], |
41eac664 |
51 | ); |
52 | |
760545fa |
53 | $schema->storage->_sql_maker->quote_char ([qw/ [ ] /]); |
54 | $schema->storage->_sql_maker->name_sep ('.'); |
41eac664 |
55 | |
56 | my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, { |
57 | '+select' => ['owner.name'], |
58 | '+as' => ['owner_name'], |
59 | join => 'owner', |
760545fa |
60 | rows => 1, |
41eac664 |
61 | }); |
62 | |
63 | is_same_sql_bind( |
64 | $rs_selectas_rel->as_query, |
65 | '( |
90ed89cb |
66 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
760545fa |
67 | [owner_name] |
41eac664 |
68 | FROM ( |
90ed89cb |
69 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
760545fa |
70 | [owner_name], |
71 | ROW_NUMBER() OVER( ) AS [rno__row__index] |
41eac664 |
72 | FROM ( |
760545fa |
73 | SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price], |
74 | [owner].[name] AS [owner_name] |
75 | FROM [books] [me] |
76 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
77 | WHERE ( [source] = ? ) |
78 | ) [me] |
79 | ) [me] |
fcb7fcbb |
80 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
41eac664 |
81 | )', |
fcb7fcbb |
82 | [ |
83 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
84 | [ $OFFSET => 1 ], |
85 | [ $TOTAL => 1 ], |
86 | ], |
41eac664 |
87 | ); |
a5f843e3 |
88 | |
d7632687 |
89 | { |
69d3c270 |
90 | my $subq = $schema->resultset('Owners')->search({ |
91 | 'count.id' => { -ident => 'owner.id' }, |
92 | 'count.name' => 'fail', # no one would do this in real life |
93 | }, { alias => 'owner' })->count_rs; |
94 | |
95 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
96 | columns => [ |
97 | { owner_name => 'owner.name' }, |
98 | { owner_books => $subq->as_query }, |
99 | ], |
100 | join => 'owner', |
101 | rows => 1, |
071dea12 |
102 | order_by => 'me.id', |
69d3c270 |
103 | }); |
104 | |
105 | is_same_sql_bind( |
106 | $rs_selectas_rel->as_query, |
107 | '( |
108 | SELECT [owner_name], [owner_books] |
109 | FROM ( |
08a1eaad |
110 | SELECT [owner_name], [owner_books], ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ) AS [rno__row__index] |
69d3c270 |
111 | FROM ( |
112 | SELECT [owner].[name] AS [owner_name], |
113 | ( SELECT COUNT( * ) FROM [owners] [owner] |
071dea12 |
114 | WHERE [count].[id] = [owner].[id] and [count].[name] = ? ) AS [owner_books], |
08a1eaad |
115 | [me].[id] AS [ORDER__BY__001] |
071dea12 |
116 | FROM [books] [me] |
117 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
69d3c270 |
118 | WHERE ( [source] = ? ) |
119 | ) [me] |
120 | ) [me] |
121 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
122 | )', |
123 | [ |
124 | [ { dbic_colname => 'count.name' } => 'fail' ], |
125 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
126 | [ $OFFSET => 1 ], |
127 | [ $TOTAL => 1 ], |
128 | ], |
129 | ); |
130 | |
131 | }{ |
132 | my $subq = $schema->resultset('Owners')->search({ |
133 | 'count.id' => { -ident => 'owner.id' }, |
134 | }, { alias => 'owner' })->count_rs; |
135 | |
136 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, { |
137 | columns => [ |
138 | { owner_name => 'owner.name' }, |
139 | { owner_books => $subq->as_query }, |
140 | ], |
141 | join => 'owner', |
142 | rows => 1, |
143 | }); |
144 | |
145 | is_same_sql_bind( |
146 | $rs_selectas_rel->as_query, |
147 | '( |
148 | SELECT [owner_name], [owner_books] |
149 | FROM ( |
150 | SELECT [owner_name], [owner_books], ROW_NUMBER() OVER( ) AS [rno__row__index] |
151 | FROM ( |
152 | SELECT [owner].[name] AS [owner_name], |
153 | ( SELECT COUNT( * ) FROM [owners] [owner] WHERE [count].[id] = [owner].[id] ) AS [owner_books] |
154 | FROM [books] [me] |
155 | JOIN [owners] [owner] ON [owner].[id] = [me].[owner] |
156 | WHERE ( [source] = ? ) |
157 | ) [me] |
158 | ) [me] |
159 | WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
160 | )', |
161 | [ |
162 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } |
163 | => 'Library' ], |
164 | [ $OFFSET => 1 ], |
165 | [ $TOTAL => 1 ], |
166 | ], |
167 | ); |
168 | |
169 | } |
170 | |
171 | { |
d7632687 |
172 | my $rs = $schema->resultset('Artist')->search({}, { |
173 | columns => 'name', |
174 | offset => 1, |
175 | order_by => 'name', |
176 | }); |
177 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
178 | |
179 | like ( |
180 | ${$rs->as_query}->[0], |
181 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
182 | 'Newlines/spaces preserved in final sql', |
183 | ); |
184 | } |
185 | |
f74d22e2 |
186 | { |
187 | my $subq = $schema->resultset('Owners')->search({ |
188 | 'books.owner' => { -ident => 'owner.id' }, |
189 | }, { alias => 'owner', select => ['id'] } )->count_rs; |
190 | |
191 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } ); |
192 | |
193 | is_same_sql_bind( |
194 | $rs_selectas_rel->as_query, |
195 | '( |
90ed89cb |
196 | SELECT [me].[id], [me].[owner] FROM ( |
197 | SELECT [me].[id], [me].[owner], ROW_NUMBER() OVER( ) AS [rno__row__index] FROM ( |
f74d22e2 |
198 | SELECT [me].[id], [me].[owner] |
199 | FROM [books] [me] |
200 | WHERE ( ( (EXISTS ( |
201 | SELECT COUNT( * ) FROM [owners] [owner] WHERE ( [books].[owner] = [owner].[id] ) |
202 | )) AND [source] = ? ) ) |
203 | ) [me] |
204 | ) [me] WHERE [rno__row__index] >= ? AND [rno__row__index] <= ? |
205 | )', |
206 | [ |
207 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
208 | [ $OFFSET => 1 ], |
209 | [ $TOTAL => 1 ], |
210 | ], |
211 | 'Pagination with sub-query in WHERE works' |
212 | ); |
213 | |
214 | } |
215 | |
d7632687 |
216 | |
a5f843e3 |
217 | done_testing; |