summaryrefslogtreecommitdiff
path: root/tests/unit/Data/DbCommon/CommandBuilderMssqlTest.php
blob: 893a8ca63ca924a405184e40e8623c835ad0c53b (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<?php

Prado::using('System.Data.*');
Prado::using('System.Data.Common.Mssql.TMssqlCommandBuilder');

/**
 * @package System.Data.DbCommon
 */
class CommandBuilderMssqlTest extends PHPUnit_Framework_TestCase
{
	protected static $sql = array(
		'simple' => 'SELECT username, age FROM accounts',
		'multiple' => 'select a.username, b.name from accounts a, table1 b where a.age = b.id1',
		'ordering' => 'select a.username, b.name, a.age from accounts a, table1 b where a.age = b.id1 order by age DESC, name',
		'index' => 'select a.username, b.name, a.age from accounts a, table1 b where a.age = b.id1 ORDER BY 1 DESC, 2 ASC',
		//'compute' => 'SELECT username, age FROM accounts order by age compute avg(age)',
	);

	function test_limit()
	{
		$builder = new TMssqlCommandBuilder();

		$sql = $builder->applyLimitOffset(self::$sql['simple'], 3);
		$expect = 'SELECT TOP 3 username, age FROM accounts';
		$this->assertEqual($expect, $sql);


		$sql = $builder->applyLimitOffset(self::$sql['simple'], 3, 2);
		$expect = 'SELECT * FROM (SELECT TOP 3 * FROM (SELECT TOP 5 username, age FROM accounts) as [__inner top table__] ) as [__outer top table__] ';
		$this->assertEqual($expect, $sql);

		$sql = $builder->applyLimitOffset(self::$sql['multiple'], 3, 2);
		$expect = 'SELECT * FROM (SELECT TOP 3 * FROM (SELECT TOP 5 a.username, b.name from accounts a, table1 b where a.age = b.id1) as [__inner top table__] ) as [__outer top table__] ';
		$this->assertEqual($sql, $expect);

		$sql = $builder->applyLimitOffset(self::$sql['ordering'], 3, 2);
		$expect = 'SELECT * FROM (SELECT TOP 3 * FROM (SELECT TOP 5 a.username, b.name, a.age from accounts a, table1 b where a.age = b.id1 order by age DESC, name) as [__inner top table__] ORDER BY age ASC, name DESC) as [__outer top table__] ORDER BY age DESC, name ASC';
		$this->assertEqual($sql, $expect);

		$sql = $builder->applyLimitOffset(self::$sql['index'], 3, 2);
		$expect = 'SELECT * FROM (SELECT TOP 3 * FROM (SELECT TOP 5 a.username, b.name, a.age from accounts a, table1 b where a.age = b.id1 ORDER BY 1 DESC, 2 ASC) as [__inner top table__] ORDER BY 1 ASC, 2 DESC) as [__outer top table__] ORDER BY 1 DESC, 2 ASC';
		$this->assertEqual($expect, $sql);

	//	$sql = $builder->applyLimitOffset(self::$sql['compute'], 3, 2);
	//	var_dump($sql);
	}
}