5 use DBICTest ':DiffSQL';
6 use DBIx::Class::SQLMaker::LimitDialects;
7 my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
8 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
10 my $schema = DBICTest->init_schema (
11 storage_type => 'DBIx::Class::Storage::DBI::MSSQL',
16 $schema->storage->sql_maker;
18 # more involved limit dialect torture testcase migrated from the
21 pref_hm_and_page_and_group_rs => {
23 rs => scalar $schema->resultset ('Owners')->search (
25 'books.id' => { '!=', undef },
26 'me.name' => { '!=', 'somebogusstring' },
30 order_by => [ { -asc => \['name + ?', [ test => 'xxx' ]] }, 'me.id' ], # test bindvar propagation
31 group_by => [ map { "me.$_" } $schema->source('Owners')->columns ], # the literal order_by requires an explicit group_by
33 unsafe_subselect_ok => 1,
40 SELECT TOP 2147483647 [me].[id], [me].[name],
41 [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
43 SELECT TOP 2147483647 [me].[id], [me].[name]
45 SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001]
47 SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
49 LEFT JOIN [books] [books]
50 ON [books].[owner] = [me].[id]
51 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
52 GROUP BY [me].[id], [me].[name]
53 ORDER BY name + ? ASC, [me].[id]
55 ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC
57 ORDER BY [ORDER__BY__001] ASC, [me].[id]
59 LEFT JOIN [books] [books]
60 ON [books].[owner] = [me].[id]
61 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
62 ORDER BY name + ? ASC, [me].[id]
65 [ { dbic_colname => 'test' }
68 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
69 => 'somebogusstring' ],
71 [ { dbic_colname => 'test' } => 'xxx' ], # the extra re-order bind
73 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
74 => 'somebogusstring' ],
76 [ { dbic_colname => 'test' }
83 SELECT TOP 2147483647 [me].[id], [me].[name],
84 [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
86 SELECT TOP 2147483647 [me].[id], [me].[name]
88 SELECT [me].[id], [me].[name],
89 ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index]
91 SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
93 LEFT JOIN [books] [books]
94 ON [books].[owner] = [me].[id]
95 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
96 GROUP BY [me].[id], [me].[name]
99 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
101 LEFT JOIN [books] [books]
102 ON [books].[owner] = [me].[id]
103 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
104 ORDER BY name + ? ASC, [me].[id]
107 [ { dbic_colname => 'test' }
110 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
111 => 'somebogusstring' ],
113 [ $OFFSET => 7 ], # parameterised RNO
117 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
118 => 'somebogusstring' ],
120 [ { dbic_colname => 'test' }
127 pref_bt_and_page_and_group_rs => {
129 rs => scalar $schema->resultset ('BooksInLibrary')->search (
131 'owner.name' => [qw/wiggle woggle/],
135 having => \['1 = ?', [ test => 1 ] ], #test having propagation
137 rows => 2, # 3 results total
138 order_by => [{ -desc => 'me.owner' }, 'me.id'],
139 unsafe_subselect_ok => 1,
146 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
147 [owner].[id], [owner].[name]
149 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
151 SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
153 SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
155 JOIN [owners] [owner]
156 ON [owner].[id] = [me].[owner]
157 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
158 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
160 ORDER BY [me].[owner] DESC, [me].[id]
162 ORDER BY [me].[owner] ASC, [me].[id] DESC
164 ORDER BY [me].[owner] DESC, [me].[id]
166 JOIN [owners] [owner]
167 ON [owner].[id] = [me].[owner]
168 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
169 ORDER BY [me].[owner] DESC, [me].[id]
173 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
175 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
177 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
179 [ { dbic_colname => 'test' }
183 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
185 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
187 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
193 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
194 [owner].[id], [owner].[name]
196 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
198 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
199 ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index]
201 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
203 JOIN [owners] [owner]
204 ON [owner].[id] = [me].[owner]
205 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
206 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
210 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
212 JOIN [owners] [owner]
213 ON [owner].[id] = [me].[owner]
214 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
215 ORDER BY [me].[owner] DESC, [me].[id]
219 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
221 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
223 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
225 [ { dbic_colname => 'test' }
232 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
234 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
236 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
244 for my $tname (keys %$tests) {
245 for my $limtype (keys %{$tests->{$tname}{result}} ) {
247 delete $schema->storage->_sql_maker->{_cached_syntax};
248 $schema->storage->_sql_maker->limit_dialect ($limtype);
251 $tests->{$tname}{rs}->as_query,
252 @{ $tests->{$tname}{result}{$limtype} },
253 "Correct SQL for $limtype on $tname",