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