6 use DBIC::SqlMakerTest;
7 use DBIx::Class::SQLMaker::LimitDialects;
8 my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
9 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
11 my $schema = DBICTest->init_schema (
12 storage_type => 'DBIx::Class::Storage::DBI::MSSQL',
17 $schema->storage->sql_maker;
19 # more involved limit dialect torture testcase migrated from the
22 pref_hm_and_page_and_group_rs => {
24 rs => scalar $schema->resultset ('Owners')->search (
26 'books.id' => { '!=', undef },
27 'me.name' => { '!=', 'somebogusstring' },
31 order_by => [ { -asc => \['name + ?', [ test => 'xxx' ]] }, 'me.id' ], # test bindvar propagation
32 group_by => [ map { "me.$_" } $schema->source('Owners')->columns ], # the literal order_by requires an explicit group_by
34 unsafe_subselect_ok => 1,
41 SELECT TOP 2147483647 [me].[id], [me].[name],
42 [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
44 SELECT TOP 2147483647 [me].[id], [me].[name]
46 SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001]
48 SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
50 LEFT JOIN [books] [books]
51 ON [books].[owner] = [me].[id]
52 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
53 GROUP BY [me].[id], [me].[name]
54 ORDER BY name + ? ASC, [me].[id]
56 ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC
58 ORDER BY [ORDER__BY__001] ASC, [me].[id]
60 LEFT JOIN [books] [books]
61 ON [books].[owner] = [me].[id]
62 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
63 ORDER BY name + ? ASC, [me].[id]
66 [ { dbic_colname => 'test' }
69 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
70 => 'somebogusstring' ],
72 [ { dbic_colname => 'test' } => 'xxx' ], # the extra re-order bind
74 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
75 => 'somebogusstring' ],
77 [ { dbic_colname => 'test' }
84 SELECT TOP 2147483647 [me].[id], [me].[name],
85 [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
87 SELECT TOP 2147483647 [me].[id], [me].[name]
89 SELECT [me].[id], [me].[name],
90 ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index]
92 SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
94 LEFT JOIN [books] [books]
95 ON [books].[owner] = [me].[id]
96 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
97 GROUP BY [me].[id], [me].[name]
100 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
102 LEFT JOIN [books] [books]
103 ON [books].[owner] = [me].[id]
104 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
105 ORDER BY name + ? ASC, [me].[id]
108 [ { dbic_colname => 'test' }
111 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
112 => 'somebogusstring' ],
114 [ $OFFSET => 7 ], # parameterised RNO
118 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
119 => 'somebogusstring' ],
121 [ { dbic_colname => 'test' }
128 pref_bt_and_page_and_group_rs => {
130 rs => scalar $schema->resultset ('BooksInLibrary')->search (
132 'owner.name' => [qw/wiggle woggle/],
136 having => \['1 = ?', [ test => 1 ] ], #test having propagation
138 rows => 2, # 3 results total
139 order_by => [{ -desc => 'me.owner' }, 'me.id'],
140 unsafe_subselect_ok => 1,
147 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
148 [owner].[id], [owner].[name]
150 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
152 SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
154 SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
156 JOIN [owners] [owner]
157 ON [owner].[id] = [me].[owner]
158 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
159 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
161 ORDER BY [me].[owner] DESC, [me].[id]
163 ORDER BY [me].[owner] ASC, [me].[id] DESC
165 ORDER BY [me].[owner] DESC, [me].[id]
167 JOIN [owners] [owner]
168 ON [owner].[id] = [me].[owner]
169 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
170 ORDER BY [me].[owner] DESC, [me].[id]
174 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
176 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
178 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
180 [ { dbic_colname => 'test' }
184 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
186 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
188 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
194 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
195 [owner].[id], [owner].[name]
197 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
199 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
200 ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index]
202 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
204 JOIN [owners] [owner]
205 ON [owner].[id] = [me].[owner]
206 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
207 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
211 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
213 JOIN [owners] [owner]
214 ON [owner].[id] = [me].[owner]
215 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
216 ORDER BY [me].[owner] DESC, [me].[id]
220 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
222 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
224 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
226 [ { dbic_colname => 'test' }
233 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
235 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
237 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
245 for my $tname (keys %$tests) {
246 for my $limtype (keys %{$tests->{$tname}{result}} ) {
248 delete $schema->storage->_sql_maker->{_cached_syntax};
249 $schema->storage->_sql_maker->limit_dialect ($limtype);
252 $tests->{$tname}{rs}->as_query,
253 @{ $tests->{$tname}{result}{$limtype} },
254 "Correct SQL for $limtype on $tname",