7 use DBIC::SqlMakerTest;
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,
16 my $schema = DBICTest->init_schema;
18 $schema->storage->_sql_maker->limit_dialect ('GenericSubQ');
20 my $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
21 '+columns' => [{ owner_name => 'owner.name' }],
24 order_by => 'me.title',
30 SELECT me.id, me.source, me.owner, me.title, me.price,
33 SELECT me.id, me.source, me.owner, me.title, me.price,
34 owner.name AS owner_name
36 JOIN owners owner ON owner.id = me.owner
42 FROM books rownum__emulation
43 WHERE rownum__emulation.title < me.title
48 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
54 [ $rs->get_column ('title')->all ],
55 ['Best Recipe Cookbook', 'Dynamical Systems'],
56 'Correct columns selected with rows',
59 $schema->storage->_sql_maker->quote_char ('"');
60 $schema->storage->_sql_maker->name_sep ('.');
62 $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
63 order_by => { -desc => 'title' },
64 '+select' => ['owner.name'],
65 '+as' => ['owner.name'],
74 SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
77 SELECT "me"."id", "me"."source", "me"."owner", "me"."title", "me"."price",
78 "owner"."name" AS "owner__name"
80 JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
81 WHERE ( "source" = ? )
86 FROM "books" "rownum__emulation"
87 WHERE "rownum__emulation"."title" > "me"."title"
92 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
99 [ $rs->get_column ('title')->all ],
100 [ 'Dynamical Systems', 'Best Recipe Cookbook' ],
101 'Correct columns selected with rows',
104 $rs = $schema->resultset ('BooksInLibrary')->search ({}, {
106 'select' => ['owner.name'],
107 'as' => ['owner_name'],
117 SELECT "owner"."name" AS "owner_name", "title"
119 JOIN "owners" "owner" ON "owner"."id" = "me"."owner"
120 WHERE ( "source" = ? )
125 FROM "books" "rownum__emulation"
126 WHERE "rownum__emulation"."title" < "me"."title"
131 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
133 [ $TOTAL => 2147483647 ],
138 [ $rs->get_column ('owner_name')->all ],
140 'Correct columns selected with rows',
144 $rs = $schema->resultset('Artist')->search({}, {
145 columns => 'artistid',
147 order_by => 'artistid',
149 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
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',