6 local $TODO = 'Temporarily todo-ed for dq2eb';
11 use DBIC::SqlMakerTest;
12 use DBIx::Class::SQLMaker::LimitDialects;
13 my ($ROWS, $TOTAL, $OFFSET) = (
14 DBIx::Class::SQLMaker::LimitDialects->__rows_bindtype,
15 DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
16 DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
20 my $schema = DBICTest->init_schema;
22 $schema->storage->_sql_maker->limit_dialect ('GenericSubQ');
24 my $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
25 '+columns' => [{ owner_name => 'owner.name' }],
28 order_by => 'me.title',
34 SELECT me.id, me.source, me.owner, me.title, me.price,
37 SELECT me.id, me.source, me.owner, me.title, me.price,
38 owner.name AS owner_name
40 JOIN owners owner ON owner.id = me.owner
46 FROM books rownum__emulation
47 WHERE rownum__emulation.title < me.title
52 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
58 [ $rs->get_column ('title')->all ],
59 ['Best Recipe Cookbook', 'Dynamical Systems'],
60 'Correct columns selected with rows',
63 $schema->storage->_sql_maker->quote_char ('"');
64 $schema->storage->_sql_maker->name_sep ('.');
66 $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
67 order_by => { -desc => 'title' },
68 '+select' => ['owner.name'],
69 '+as' => ['owner.name'],
78 SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
81 SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
82 "owner"."name" AS "owner__name"
84 JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
85 WHERE ( "source" = ? )
90 FROM "books" "rownum__emulation"
91 WHERE "rownum__emulation"."title" > "me"."title"
93 ORDER BY "me"."title" DESC
96 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
103 [ $rs->get_column ('title')->all ],
104 [ 'Dynamical Systems', 'Best Recipe Cookbook' ],
105 'Correct columns selected with rows',
108 $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
110 'select' => ['owner.name'],
111 'as' => ['owner_name'],
121 SELECT "owner"."name" AS "owner_name", "title" AS "ORDER__BY__001"
123 JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
124 WHERE ( "source" = ? )
129 FROM "books" "rownum__emulation"
130 WHERE "rownum__emulation"."title" < "ORDER__BY__001"
132 ORDER BY "ORDER__BY__001" ASC
135 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
137 [ $TOTAL => 2147483647 ],
142 [ $rs->get_column ('owner_name')->all ],
144 'Correct columns selected with rows',
147 $rs = $schema->resultset('CD')->search({}, {
148 columns => [qw( me.cdid me.title me.genreid me.year tracks.position tracks.title )],
151 order_by => [ { -asc => 'me.genreid' }, { -desc => 'year' }, 'me.title', \ 'single_track DESC', { -desc => [qw( me.cdid tracks.position )] } ],
154 my @full_res = @{$rs->all_hri};
156 is (@full_res, 5, 'Expected amount of CDs');
161 { cdid => 2, genreid => undef, title => "Forkful of bees", year => 2001, tracks => [
162 { position => 3, title => "Sticky Honey" },
163 { position => 2, title => "Stripy" },
164 { position => 1, title => "Stung with Success" },
166 { cdid => 4, genreid => undef, title => "Generic Manufactured Singles", year => 2001, tracks => [
167 { position => 3, title => "No More Ideas" },
168 { position => 2, title => "Boring Song" },
169 { position => 1, title => "Boring Name" },
171 { cdid => 5, genreid => undef, title => "Come Be Depressed With Us", year => 1998, tracks => [
172 { position => 3, title => "Suicidal" },
173 { position => 2, title => "Under The Weather" },
174 { position => 1, title => "Sad" },
176 { cdid => 3, genreid => undef, title => "Caterwaulin' Blues", year => 1997, tracks => [
177 { position => 3, title => "Fowlin" },
178 { position => 2, title => "Howlin" },
179 { position => 1, title => "Yowlin" },
181 { cdid => 1, genreid => 1, title => "Spoonful of bees", year => 1999, tracks => [
182 { position => 3, title => "Beehind You" },
183 { position => 2, title => "Apiary" },
184 { position => 1, title => "The Bees Knees" },
187 'Complex ordered gensubq limited cds and tracks in expected sqlite order'
198 my $rownum_cmp_op = $slice->[0]
204 $rs->slice(@$slice)->all_hri,
205 [ @full_res[ $slice->[0] .. min($#full_res, $slice->[1]) ] ],
206 "Expected array slice on complex ordered limited gensubq ($slice->[0] : $slice->[1])",
210 $rs->slice(@$slice)->as_query,
212 SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year",
213 "tracks"."position", "tracks"."title"
215 SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
217 SELECT "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
219 LEFT JOIN "track" "tracks"
220 ON "tracks"."cd" = "me"."cdid"
221 GROUP BY "me"."cdid", "me"."title", "me"."genreid", "me"."year", "me"."single_track"
225 FROM cd "rownum__emulation"
227 ( "me"."genreid" IS NOT NULL AND "rownum__emulation"."genreid" IS NULL )
230 "rownum__emulation"."genreid" < "me"."genreid"
232 "me"."genreid" IS NOT NULL
234 "rownum__emulation"."genreid" IS NOT NULL
239 "me"."genreid" = "rownum__emulation"."genreid"
241 ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
244 "rownum__emulation"."year" > "me"."year"
249 "me"."genreid" = "rownum__emulation"."genreid"
251 ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
254 "me"."year" = "rownum__emulation"."year"
256 "rownum__emulation"."title" < "me"."title"
261 "me"."genreid" = "rownum__emulation"."genreid"
263 ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
266 "me"."year" = "rownum__emulation"."year"
268 "me"."title" = "rownum__emulation"."title"
271 ("me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NOT NULL )
274 "rownum__emulation"."single_track" > "me"."single_track"
276 "me"."single_track" IS NOT NULL
278 "rownum__emulation"."single_track" IS NOT NULL
285 "me"."genreid" = "rownum__emulation"."genreid"
287 ( "me"."genreid" IS NULL AND "rownum__emulation"."genreid" IS NULL )
290 "me"."year" = "rownum__emulation"."year"
292 "me"."title" = "rownum__emulation"."title"
295 ( "me"."single_track" = "rownum__emulation"."single_track" )
297 ( "me"."single_track" IS NULL AND "rownum__emulation"."single_track" IS NULL )
300 "rownum__emulation"."cdid" > "me"."cdid"
304 ORDER BY "me"."genreid" ASC, "me"."year" DESC, "me"."title" ASC, "me"."single_track" DESC, "me"."cdid" DESC
306 LEFT JOIN "track" "tracks"
307 ON "tracks"."cd" = "me"."cdid"
308 ORDER BY "me"."genreid" ASC, "year" DESC, "me"."title", single_track DESC, "me"."cdid" DESC, "tracks"."position" DESC
311 ( $slice->[0] ? [ $OFFSET => $slice->[0] ] : () ),
312 [ $TOTAL => $slice->[1] + ($slice->[0] ? 0 : 1 ) ],
314 "Expected sql on complex ordered limited gensubq ($slice->[0] : $slice->[1])",
319 $rs = $schema->resultset('Artist')->search({}, {
320 columns => 'artistid',
322 order_by => 'artistid',
324 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
327 ${$rs->as_query}->[0],
328 qr| weird \s \n \s newline/multi \s \t \s \t \s space \s containing \s \n \s table|x,
329 'Newlines/spaces preserved in final sql',