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

BEGIN { do "./t/lib/ANFANG.pm" or die ( $@ || $! ) }

use strict;
use warnings;
use Test::More;

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