1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
8 use DBICTest ':DiffSQL';
9 use DBIx::Class::SQLMaker::LimitDialects;
11 my ($TOTAL, $OFFSET) = (
12 DBIx::Class::SQLMaker::LimitDialects->__total_bindtype,
13 DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype,
16 my $schema = DBICTest->init_schema;
18 $schema->storage->_sql_maker->limit_dialect ('RowNumberOver');
20 my $rs_selectas_col = $schema->resultset ('BooksInLibrary')->search ({}, {
21 '+select' => ['owner.name'],
22 '+as' => ['owner.name'],
28 $rs_selectas_col->as_query,
30 SELECT me.id, me.source, me.owner, me.title, me.price,
33 SELECT me.id, me.source, me.owner, me.title, me.price,
35 ROW_NUMBER() OVER( ) AS rno__row__index
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
44 WHERE rno__row__index >= ? AND rno__row__index <= ?
47 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
53 $schema->storage->_sql_maker->quote_char ([qw/ [ ] /]);
54 $schema->storage->_sql_maker->name_sep ('.');
56 my $rs_selectas_rel = $schema->resultset ('BooksInLibrary')->search ({}, {
57 '+select' => ['owner.name'],
58 '+as' => ['owner_name'],
64 $rs_selectas_rel->as_query,
66 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
69 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
71 ROW_NUMBER() OVER( ) AS [rno__row__index]
73 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
74 [owner].[name] AS [owner_name]
76 JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
77 WHERE ( [source] = ? )
80 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
83 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
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;
95 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, {
97 { owner_name => 'owner.name' },
98 { owner_books => $subq->as_query },
106 $rs_selectas_rel->as_query,
108 SELECT [owner_name], [owner_books]
110 SELECT [owner_name], [owner_books], ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ) AS [rno__row__index]
112 SELECT [owner].[name] AS [owner_name],
113 ( SELECT COUNT( * ) FROM [owners] [owner]
114 WHERE [count].[id] = [owner].[id] and [count].[name] = ? ) AS [owner_books],
115 [me].[id] AS [ORDER__BY__001]
117 JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
118 WHERE ( [source] = ? )
121 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
124 [ { dbic_colname => 'count.name' } => 'fail' ],
125 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
132 my $subq = $schema->resultset('Owners')->search({
133 'count.id' => { -ident => 'owner.id' },
134 }, { alias => 'owner' })->count_rs;
136 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search ({}, {
138 { owner_name => 'owner.name' },
139 { owner_books => $subq->as_query },
146 $rs_selectas_rel->as_query,
148 SELECT [owner_name], [owner_books]
150 SELECT [owner_name], [owner_books], ROW_NUMBER() OVER( ) AS [rno__row__index]
152 SELECT [owner].[name] AS [owner_name],
153 ( SELECT COUNT( * ) FROM [owners] [owner] WHERE [count].[id] = [owner].[id] ) AS [owner_books]
155 JOIN [owners] [owner] ON [owner].[id] = [me].[owner]
156 WHERE ( [source] = ? )
159 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
162 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
172 my $rs = $schema->resultset('Artist')->search({}, {
177 local $rs->result_source->{name} = "weird \n newline/multi \t \t space containing \n table";
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',
187 my $subq = $schema->resultset('Owners')->search({
188 'books.owner' => { -ident => 'owner.id' },
189 }, { alias => 'owner', select => ['id'] } )->count_rs;
191 my $rs_selectas_rel = $schema->resultset('BooksInLibrary')->search( { -exists => $subq->as_query }, { select => ['id','owner'], rows => 1 } );
194 $rs_selectas_rel->as_query,
196 SELECT [me].[id], [me].[owner] FROM (
197 SELECT [me].[id], [me].[owner], ROW_NUMBER() OVER( ) AS [rno__row__index] FROM (
198 SELECT [me].[id], [me].[owner]
201 SELECT COUNT( * ) FROM [owners] [owner] WHERE ( [books].[owner] = [owner].[id] )
202 )) AND [source] = ? ) )
204 ) [me] WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
207 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' } => 'Library' ],
211 'Pagination with sub-query in WHERE works'