[dbsrgits/DBIx-Class.git] / t / sqlmaker / limit_dialects / mssql_torture.t

use strict;
use warnings;
use Test::More;
use lib qw(t/lib);
use DBICTest ':DiffSQL';
use DBIx::Class::SQLMaker::LimitDialects;
my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
my $TOTAL  = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;

my $schema = DBICTest->init_schema (
  storage_type => 'DBIx::Class::Storage::DBI::MSSQL',
  no_deploy => 1,
  quote_names => 1
);
# prime caches
$schema->storage->sql_maker;

# more involved limit dialect torture testcase migrated from the
# live mssql tests
my $tests = {
  pref_hm_and_page_and_group_rs => {

    rs => scalar $schema->resultset ('Owners')->search (
      {
        'books.id' => { '!=', undef },
        'me.name' => { '!=', 'somebogusstring' },
      },
      {
        prefetch => 'books',
        order_by => [ { -asc => \['name + ?', [ test => 'xxx' ]] }, 'me.id' ], # test bindvar propagation
        group_by => [ map { "me.$_" } $schema->source('Owners')->columns ], # the literal order_by requires an explicit group_by
        rows     => 3,
        unsafe_subselect_ok => 1,
      },
    )->page(3),

    result => {
      Top => [
        '(
          SELECT TOP 2147483647 [me].[id], [me].[name],
                                [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
            FROM (
              SELECT TOP 2147483647 [me].[id], [me].[name]
                FROM (
                  SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001]
                    FROM (
                      SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
                        FROM [owners] [me]
                        LEFT JOIN [books] [books]
                          ON [books].[owner] = [me].[id]
                      WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
                      GROUP BY [me].[id], [me].[name]
                      ORDER BY name + ? ASC, [me].[id]
                    ) [me]
                  ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC
                ) [me]
              ORDER BY [ORDER__BY__001] ASC, [me].[id]
            ) [me]
            LEFT JOIN [books] [books]
              ON [books].[owner] = [me].[id]
          WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
          ORDER BY name + ? ASC, [me].[id]
        )',
        [
          [ { dbic_colname => 'test' }
            => 'xxx' ],

          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
            => 'somebogusstring' ],

          [ { dbic_colname => 'test' } => 'xxx' ],  # the extra re-order bind

          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
            => 'somebogusstring' ],

          [ { dbic_colname => 'test' }
            => 'xxx' ],
        ],
      ],

      RowNumberOver => [
        '(
          SELECT TOP 2147483647 [me].[id], [me].[name],
                                [books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
            FROM (
              SELECT TOP 2147483647 [me].[id], [me].[name]
                FROM (
                  SELECT [me].[id], [me].[name],
                         ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index]
                    FROM (
                      SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
                        FROM [owners] [me]
                        LEFT JOIN [books] [books]
                          ON [books].[owner] = [me].[id]
                      WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
                      GROUP BY [me].[id], [me].[name]
                    ) [me]
                ) [me]
              WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
            ) [me]
            LEFT JOIN [books] [books]
              ON [books].[owner] = [me].[id]
          WHERE [books].[id] IS NOT NULL AND [me].[name] != ?
          ORDER BY name + ? ASC, [me].[id]
        )',
        [
          [ { dbic_colname => 'test' }
            => 'xxx' ],

          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
            => 'somebogusstring' ],

          [ $OFFSET => 7 ], # parameterised RNO

          [ $TOTAL => 9 ],

          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
            => 'somebogusstring' ],

          [ { dbic_colname => 'test' }
            => 'xxx' ],
        ],
      ],
    }
  },

  pref_bt_and_page_and_group_rs => {

    rs => scalar $schema->resultset ('BooksInLibrary')->search (
      {
        'owner.name' => [qw/wiggle woggle/],
      },
      {
        distinct => 1,
        having => \['1 = ?', [ test => 1 ] ], #test having propagation
        prefetch => 'owner',
        rows     => 2,  # 3 results total
        order_by => [{ -desc => 'me.owner' }, 'me.id'],
        unsafe_subselect_ok => 1,
      },
    )->page(3),

    result => {
      Top => [
        '(
          SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
                                [owner].[id], [owner].[name]
            FROM (
              SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                FROM (
                  SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                    FROM (
                      SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                        FROM [books] [me]
                        JOIN [owners] [owner]
                          ON [owner].[id] = [me].[owner]
                      WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
                      GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                      HAVING 1 = ?
                      ORDER BY [me].[owner] DESC, [me].[id]
                    ) [me]
                  ORDER BY [me].[owner] ASC, [me].[id] DESC
                ) [me]
              ORDER BY [me].[owner] DESC, [me].[id]
            ) [me]
            JOIN [owners] [owner]
              ON [owner].[id] = [me].[owner]
          WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
          ORDER BY [me].[owner] DESC, [me].[id]
        )',
        [
          # inner
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'wiggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'woggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
            => 'Library' ],
          [ { dbic_colname => 'test' }
            => '1' ],

          # outer
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'wiggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'woggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
            => 'Library' ],
        ],
      ],
      RowNumberOver => [
        '(
          SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
                                [owner].[id], [owner].[name]
            FROM (
              SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                FROM (
                  SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
                         ROW_NUMBER() OVER( ORDER BY [me].[owner] DESC, [me].[id] ) AS [rno__row__index]
                    FROM (
                      SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                        FROM [books] [me]
                        JOIN [owners] [owner]
                          ON [owner].[id] = [me].[owner]
                      WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
                      GROUP BY [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
                      HAVING 1 = ?
                    ) [me]
                ) [me]
              WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
            ) [me]
            JOIN [owners] [owner]
              ON [owner].[id] = [me].[owner]
          WHERE ( [owner].[name] = ? OR [owner].[name] = ? ) AND [source] = ?
          ORDER BY [me].[owner] DESC, [me].[id]
        )',
        [
          # inner
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'wiggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'woggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
            => 'Library' ],
          [ { dbic_colname => 'test' }
            => '1' ],

          [ $OFFSET => 5 ],
          [ $TOTAL => 6 ],

          # outer
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'wiggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
            => 'woggle' ],
          [ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
            => 'Library' ],
        ],
      ],
    },
  },
};

for my $tname (keys %$tests) {
  for my $limtype (keys %{$tests->{$tname}{result}} ) {

    delete $schema->storage->_sql_maker->{_cached_syntax};
    $schema->storage->_sql_maker->limit_dialect ($limtype);

    is_same_sql_bind(
      $tests->{$tname}{rs}->as_query,
      @{ $tests->{$tname}{result}{$limtype} },
      "Correct SQL for $limtype on $tname",
    );
  }
}

done_testing;
Commit	Line	Data
ac0c0825	1	use strict;
	2	use warnings;
	3	use Test::More;
	4	use lib qw(t/lib);
a5a7bb73	5	use DBICTest ':DiffSQL';
ac0c0825	6	use DBIx::Class::SQLMaker::LimitDialects;
	7	my $OFFSET = DBIx::Class::SQLMaker::LimitDialects->__offset_bindtype;
	8	my $TOTAL = DBIx::Class::SQLMaker::LimitDialects->__total_bindtype;
	9
	10	my $schema = DBICTest->init_schema (
	11	storage_type => 'DBIx::Class::Storage::DBI::MSSQL',
	12	no_deploy => 1,
	13	quote_names => 1
	14	);
	15	# prime caches
	16	$schema->storage->sql_maker;
	17
	18	# more involved limit dialect torture testcase migrated from the
	19	# live mssql tests
	20	my $tests = {
	21	pref_hm_and_page_and_group_rs => {
	22
	23	rs => scalar $schema->resultset ('Owners')->search (
	24	{
	25	'books.id' => { '!=', undef },
	26	'me.name' => { '!=', 'somebogusstring' },
	27	},
	28	{
	29	prefetch => 'books',
	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
	32	rows => 3,
	33	unsafe_subselect_ok => 1,
	34	},
	35	)->page(3),
	36
	37	result => {
	38	Top => [
	39	'(
	40	SELECT TOP 2147483647 [me].[id], [me].[name],
	41	[books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
	42	FROM (
	43	SELECT TOP 2147483647 [me].[id], [me].[name]
	44	FROM (
	45	SELECT TOP 3 [me].[id], [me].[name], [ORDER__BY__001]
	46	FROM (
	47	SELECT TOP 9 [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
	48	FROM [owners] [me]
	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]
	54	) [me]
	55	ORDER BY [ORDER__BY__001] DESC, [me].[id] DESC
	56	) [me]
	57	ORDER BY [ORDER__BY__001] ASC, [me].[id]
	58	) [me]
	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]
	63	)',
	64	[
	65	[ { dbic_colname => 'test' }
	66	=> 'xxx' ],
	67
	68	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
	69	=> 'somebogusstring' ],
70
71	[ { dbic_colname => 'test' } => 'xxx' ], # the extra re-order bind
72
73	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
74	=> 'somebogusstring' ],
75
76	[ { dbic_colname => 'test' }
77	=> 'xxx' ],
78	],
79	],
80
81	RowNumberOver => [
82	'(
83	SELECT TOP 2147483647 [me].[id], [me].[name],
84	[books].[id], [books].[source], [books].[owner], [books].[title], [books].[price]
85	FROM (
86	SELECT TOP 2147483647 [me].[id], [me].[name]
87	FROM (
88	SELECT [me].[id], [me].[name],
89	ROW_NUMBER() OVER( ORDER BY [ORDER__BY__001] ASC, [me].[id] ) AS [rno__row__index]
90	FROM (
91	SELECT [me].[id], [me].[name], name + ? AS [ORDER__BY__001]
92	FROM [owners] [me]
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]
97	) [me]
98	) [me]
99	WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
100	) [me]
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]
105	)',
106	[
107	[ { dbic_colname => 'test' }
108	=> 'xxx' ],
109
110	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
111	=> 'somebogusstring' ],
112
113	[ $OFFSET => 7 ], # parameterised RNO
114
115	[ $TOTAL => 9 ],
116
117	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'me.name' }
118	=> 'somebogusstring' ],
119
120	[ { dbic_colname => 'test' }
121	=> 'xxx' ],
122	],
123	],
124	}
125	},
126
127	pref_bt_and_page_and_group_rs => {
128
129	rs => scalar $schema->resultset ('BooksInLibrary')->search (
130	{
131	'owner.name' => [qw/wiggle woggle/],
132	},
133	{
134	distinct => 1,
135	having => \['1 = ?', [ test => 1 ] ], #test having propagation
136	prefetch => 'owner',
137	rows => 2, # 3 results total
138	order_by => [{ -desc => 'me.owner' }, 'me.id'],
139	unsafe_subselect_ok => 1,
140	},
141	)->page(3),
142
143	result => {
144	Top => [
145	'(
146	SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
147	[owner].[id], [owner].[name]
148	FROM (
149	SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
150	FROM (
151	SELECT TOP 2 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
152	FROM (
153	SELECT TOP 6 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
154	FROM [books] [me]
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]
159	HAVING 1 = ?
160	ORDER BY [me].[owner] DESC, [me].[id]
161	) [me]
162	ORDER BY [me].[owner] ASC, [me].[id] DESC
163	) [me]
164	ORDER BY [me].[owner] DESC, [me].[id]
165	) [me]
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]
170	)',
171	[
172	# inner
173	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
174	=> 'wiggle' ],
175	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
176	=> 'woggle' ],
177	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
178	=> 'Library' ],
179	[ { dbic_colname => 'test' }
180	=> '1' ],
181
182	# outer
183	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
184	=> 'wiggle' ],
185	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
186	=> 'woggle' ],
187	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
188	=> 'Library' ],
189	],
190	],
191	RowNumberOver => [
192	'(
193	SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price],
194	[owner].[id], [owner].[name]
195	FROM (
196	SELECT TOP 2147483647 [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
197	FROM (
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]
200	FROM (
201	SELECT [me].[id], [me].[source], [me].[owner], [me].[title], [me].[price]
202	FROM [books] [me]
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]
207	HAVING 1 = ?
208	) [me]
209	) [me]
210	WHERE [rno__row__index] >= ? AND [rno__row__index] <= ?
211	) [me]
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]
216	)',
217	[
218	# inner
219	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
220	=> 'wiggle' ],
221	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
222	=> 'woggle' ],
223	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
224	=> 'Library' ],
225	[ { dbic_colname => 'test' }
226	=> '1' ],
227
228	[ $OFFSET => 5 ],
229	[ $TOTAL => 6 ],
230
231	# outer
232	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
233	=> 'wiggle' ],
234	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'owner.name' }
235	=> 'woggle' ],
236	[ { sqlt_datatype => 'varchar', sqlt_size => 100, dbic_colname => 'source' }
237	=> 'Library' ],
238	],
239	],
240	},
241	},
242	};
243
244	for my $tname (keys %$tests) {
245	for my $limtype (keys %{$tests->{$tname}{result}} ) {
246
247	delete $schema->storage->_sql_maker->{_cached_syntax};
248	$schema->storage->_sql_maker->limit_dialect ($limtype);
249
250	is_same_sql_bind(
251	$tests->{$tname}{rs}->as_query,
252	@{ $tests->{$tname}{result}{$limtype} },
253	"Correct SQL for $limtype on $tname",
254	);
255	}
256	}
257
258	done_testing;