Commit | Line | Data |
75f025cf |
1 | use strict; |
2 | use warnings; |
3 | |
4 | use Test::More; |
5 | use lib qw(t/lib); |
6 | use DBICTest; |
7 | use DBIC::SqlMakerTest; |
fcb7fcbb |
8 | use DBIx::Class::SQLMaker::LimitDialects; |
9 | my ($ROWS, $TOTAL, $OFFSET) = ( |
10 | DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype, |
11 | DBIx::Class::SQLMaker::LimitDialects->__total_bindtype, |
12 | DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype, |
13 | ); |
14 | |
75f025cf |
15 | |
16 | my $schema = DBICTest->init_schema; |
17 | |
18 | $schema->storage->_sql_maker->limit_dialect ('GenericSubQ'); |
19 | |
20 | my $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
21 | '+columns' => [{ owner_name => 'owner.name' }], |
22 | join => 'owner', |
23 | rows => 2, |
24 | order_by => 'me.title', |
25 | }); |
26 | |
27 | is_same_sql_bind( |
28 | $rs->as_query, |
29 | '( |
90ed89cb |
30 | SELECT me.id, me.source, me.owner, me.title, me.price, |
75f025cf |
31 | owner_name |
32 | FROM ( |
33 | SELECT me.id, me.source, me.owner, me.title, me.price, |
34 | owner.name AS owner_name |
35 | FROM books me |
36 | JOIN owners owner ON owner.id = me.owner |
37 | WHERE ( source = ? ) |
75f025cf |
38 | ) me |
39 | WHERE |
40 | ( |
41 | SELECT COUNT(*) |
42 | FROM books rownum__emulation |
43 | WHERE rownum__emulation.title < me.title |
fcb7fcbb |
44 | ) < ? |
b13d2248 |
45 | ORDER BY me.title |
75f025cf |
46 | )', |
fcb7fcbb |
47 | [ |
48 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
49 | [ $ROWS => 2 ], |
50 | ], |
75f025cf |
51 | ); |
52 | |
53 | is_deeply ( |
54 | [ $rs->get_column ('title')->all ], |
55 | ['Best Recipe Cookbook', 'Dynamical Systems'], |
56 | 'Correct columns selected with rows', |
57 | ); |
58 | |
59 | $schema->storage->_sql_maker->quote_char ('"'); |
60 | $schema->storage->_sql_maker->name_sep ('.'); |
61 | |
62 | $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
63 | order_by => { -desc => 'title' }, |
64 | '+select' => ['owner.name'], |
65 | '+as' => ['owner.name'], |
66 | join => 'owner', |
67 | rows => 3, |
68 | offset => 1, |
69 | }); |
70 | |
71 | is_same_sql_bind( |
72 | $rs->as_query, |
73 | '( |
90ed89cb |
74 | SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price", |
75f025cf |
75 | "owner__name" |
76 | FROM ( |
77 | SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price", |
78 | "owner"."name" AS "owner__name" |
79 | FROM "books" "me" |
80 | JOIN "owners" "owner" ON "owner"."id" = "me"."owner" |
81 | WHERE ( "source" = ? ) |
75f025cf |
82 | ) "me" |
83 | WHERE |
84 | ( |
85 | SELECT COUNT(*) |
86 | FROM "books" "rownum__emulation" |
87 | WHERE "rownum__emulation"."title" > "me"."title" |
fcb7fcbb |
88 | ) BETWEEN ? AND ? |
b13d2248 |
89 | ORDER BY "title" DESC |
75f025cf |
90 | )', |
fcb7fcbb |
91 | [ |
92 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
93 | [ $OFFSET => 1 ], |
94 | [ $TOTAL => 3 ], |
95 | ], |
75f025cf |
96 | ); |
97 | |
98 | is_deeply ( |
99 | [ $rs->get_column ('title')->all ], |
100 | [ 'Dynamical Systems', 'Best Recipe Cookbook' ], |
101 | 'Correct columns selected with rows', |
102 | ); |
103 | |
104 | $rs = $schema->resultset ('BooksInLibrary')->search ({}, { |
105 | order_by => 'title', |
106 | 'select' => ['owner.name'], |
107 | 'as' => ['owner_name'], |
108 | join => 'owner', |
109 | offset => 1, |
110 | }); |
111 | |
112 | is_same_sql_bind( |
113 | $rs->as_query, |
114 | '( |
115 | SELECT "owner_name" |
116 | FROM ( |
117 | SELECT "owner"."name" AS "owner_name", "title" |
118 | FROM "books" "me" |
119 | JOIN "owners" "owner" ON "owner"."id" = "me"."owner" |
120 | WHERE ( "source" = ? ) |
75f025cf |
121 | ) "me" |
122 | WHERE |
123 | ( |
124 | SELECT COUNT(*) |
125 | FROM "books" "rownum__emulation" |
126 | WHERE "rownum__emulation"."title" < "me"."title" |
fcb7fcbb |
127 | ) BETWEEN ? AND ? |
b13d2248 |
128 | ORDER BY "title" |
75f025cf |
129 | )', |
fcb7fcbb |
130 | [ |
131 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
132 | [ $OFFSET => 1 ], |
133 | [ $TOTAL => 2147483647 ], |
134 | ], |
75f025cf |
135 | ); |
136 | |
137 | is_deeply ( |
138 | [ $rs->get_column ('owner_name')->all ], |
139 | [ ('Newton') x 2 ], |
140 | 'Correct columns selected with rows', |
141 | ); |
142 | |
d7632687 |
143 | { |
144 | $rs = $schema->resultset('Artist')->search({}, { |
528e717e |
145 | columns => 'artistid', |
d7632687 |
146 | offset => 1, |
528e717e |
147 | order_by => 'artistid', |
d7632687 |
148 | }); |
149 | local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table"; |
150 | |
151 | like ( |
152 | ${$rs->as_query}->[0], |
153 | qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x, |
154 | 'Newlines/spaces preserved in final sql', |
155 | ); |
156 | } |
157 | |
27a7c422 |
158 | # this is a nonsensical order_by, we are just making sure the bind-transport is correct |
159 | # (not that it'll be useful anywhere in the near future) |
160 | my $attr = {}; |
161 | my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search(undef, { |
162 | columns => 'me.id', |
163 | offset => 3, |
164 | rows => 4, |
165 | '+columns' => { bar => \['? * ?', [ $attr => 11 ], [ $attr => 12 ]], baz => \[ '?', [ $attr => 13 ]] }, |
166 | order_by => [ 'id', \['? / ?', [ $attr => 1 ], [ $attr => 2 ]], \[ '?', [ $attr => 3 ]] ], |
167 | having => \[ '?', [ $attr => 21 ] ], |
168 | }); |
169 | |
170 | is_same_sql_bind( |
171 | $rs_selectas_rel->as_query, |
172 | '( |
173 | SELECT "me"."id", "bar", "baz" |
174 | FROM ( |
175 | SELECT "me"."id", ? * ? AS "bar", ? AS "baz" |
176 | FROM "books" "me" |
177 | WHERE ( "source" = ? ) |
178 | HAVING ? |
179 | ) "me" |
180 | WHERE ( SELECT COUNT(*) FROM "books" "rownum__emulation" WHERE "rownum__emulation"."id" < "me"."id" ) BETWEEN ? AND ? |
181 | ORDER BY "id", ? / ?, ? |
182 | )', |
183 | [ |
184 | [ $attr => 11 ], [ $attr => 12 ], [ $attr => 13 ], |
185 | [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ], |
186 | [ $attr => 21 ], |
187 | [ {%$OFFSET} => 3 ], |
188 | [ {%$TOTAL} => 6 ], |
189 | [ $attr => 1 ], [ $attr => 2 ], [ $attr => 3 ], |
190 | ], |
191 | 'Pagination with sub-query in ORDER BY works' |
192 | ); |
193 | |
75f025cf |
194 | done_testing; |