1 BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }
7 use DBICTest ':DiffSQL';
8 use DBIx::Class::SQLMaker::LimitDialects;
9 my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
10 my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
12 my $schema = DBICTest->init_schema (
13 storage_type => 'DBIx::Class::Storage::DBI::MSSQL',
18 $schema->storage->sql_maker;
20 # more involved limit dialect torture testcase migrated from the
23 pref_hm_and_page_and_group_rs => {
25 rs => scalar $schema->resultset ('Owners')->search (
27 'books.id' => { '!=', undef },
28 'me.name' => { '!=', 'somebogusstring' },
32 order_by => [ { -asc => \['name + ?', [ test => 'xxx' ]] }, 'me.id' ], # test bindvar propagation
33 group_by => [ map { "me.$_" } $schema->source('Owners')->columns ], # the literal order_by requires an explicit group_by
35 unsafe_subselect_ok => 1,
42 SELECT TOP 2147483647 [me].[id], [me].[name],
43 [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
45 SELECT TOP 2147483647 [me].[id], [me].[name]
47 SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001]
49 SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
51 LEFT JOIN [books] [books]
52 ON [books].[owner] = [me].[id]
53 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
54 GROUP BY [me].[id], [me].[name]
55 ORDER BY name + ? ASC, [me].[id]
57 ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC
59 ORDER BY [ORDER__BY__001] ASC, [me].[id]
61 LEFT JOIN [books] [books]
62 ON [books].[owner] = [me].[id]
63 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
64 ORDER BY name + ? ASC, [me].[id]
67 [ { dbic_colname => 'test' }
70 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
71 => 'somebogusstring' ],
73 [ { dbic_colname => 'test' } => 'xxx' ], # the extra re-order bind
75 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
76 => 'somebogusstring' ],
78 [ { dbic_colname => 'test' }
85 SELECT TOP 2147483647 [me].[id], [me].[name],
86 [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
88 SELECT TOP 2147483647 [me].[id], [me].[name]
90 SELECT [me].[id], [me].[name],
91 ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index]
93 SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
95 LEFT JOIN [books] [books]
96 ON [books].[owner] = [me].[id]
97 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
98 GROUP BY [me].[id], [me].[name]
101 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
103 LEFT JOIN [books] [books]
104 ON [books].[owner] = [me].[id]
105 WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
106 ORDER BY name + ? ASC, [me].[id]
109 [ { dbic_colname => 'test' }
112 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
113 => 'somebogusstring' ],
115 [ $OFFSET => 7 ], # parameterised RNO
119 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
120 => 'somebogusstring' ],
122 [ { dbic_colname => 'test' }
129 pref_bt_and_page_and_group_rs => {
131 rs => scalar $schema->resultset ('BooksInLibrary')->search (
133 'owner.name' => [qw/wiggle woggle/],
137 having => \['1 = ?', [ test => 1 ] ], #test having propagation
139 rows => 2, # 3 results total
140 order_by => [{ -desc => 'me.owner' }, 'me.id'],
141 unsafe_subselect_ok => 1,
148 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
149 [owner].[id], [owner].[name]
151 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
153 SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
155 SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
157 JOIN [owners] [owner]
158 ON [owner].[id] = [me].[owner]
159 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
160 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
162 ORDER BY [me].[owner] DESC, [me].[id]
164 ORDER BY [me].[owner] ASC, [me].[id] DESC
166 ORDER BY [me].[owner] DESC, [me].[id]
168 JOIN [owners] [owner]
169 ON [owner].[id] = [me].[owner]
170 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
171 ORDER BY [me].[owner] DESC, [me].[id]
175 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
177 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
179 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
181 [ { dbic_colname => 'test' }
185 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
187 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
189 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
195 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
196 [owner].[id], [owner].[name]
198 SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
200 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
201 ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index]
203 SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
205 JOIN [owners] [owner]
206 ON [owner].[id] = [me].[owner]
207 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
208 GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
212 WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
214 JOIN [owners] [owner]
215 ON [owner].[id] = [me].[owner]
216 WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
217 ORDER BY [me].[owner] DESC, [me].[id]
221 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
223 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
225 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
227 [ { dbic_colname => 'test' }
234 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
236 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
238 [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
246 for my $tname (keys %$tests) {
247 for my $limtype (keys %{$tests->{$tname}{result}} ) {
249 delete $schema->storage->_sql_maker->{_cached_syntax};
250 $schema->storage->_sql_maker->limit_dialect ($limtype);
253 $tests->{$tname}{rs}->as_query,
254 @{ $tests->{$tname}{result}{$limtype} },
255 "Correct SQL for $limtype on $tname",