<?php
namespace Doctrine\Tests\DBAL\Platforms;
use Doctrine\DBAL\Platforms\SQLServer2012Platform;
use Doctrine\DBAL\Schema\Sequence;
class SQLServer2012PlatformTest extends AbstractSQLServerPlatformTestCase
{
public function createPlatform()
{
return new SQLServer2012Platform;
}
public function testSupportsSequences()
{
self::assertTrue($this->_platform->supportsSequences());
}
public function testDoesNotPreferSequences()
{
self::assertFalse($this->_platform->prefersSequences());
}
public function testGeneratesSequenceSqlCommands()
{
$sequence = new Sequence('myseq', 20, 1);
self::assertEquals(
'CREATE SEQUENCE myseq START WITH 1 INCREMENT BY 20 MINVALUE 1',
$this->_platform->getCreateSequenceSQL($sequence)
);
self::assertEquals(
'ALTER SEQUENCE myseq INCREMENT BY 20',
$this->_platform->getAlterSequenceSQL($sequence)
);
self::assertEquals(
'DROP SEQUENCE myseq',
$this->_platform->getDropSequenceSQL('myseq')
);
self::assertEquals(
"SELECT NEXT VALUE FOR myseq",
$this->_platform->getSequenceNextValSQL('myseq')
);
}
public function testModifyLimitQuery()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user', 10, 0);
self::assertEquals('SELECT * FROM user ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithEmptyOffset()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user', 10);
self::assertEquals('SELECT * FROM user ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithOffset()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10, 5);
self::assertEquals('SELECT * FROM user ORDER BY username DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithAscOrderBy()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username ASC', 10);
self::assertEquals('SELECT * FROM user ORDER BY username ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithLowercaseOrderBy()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user order by username', 10);
self::assertEquals('SELECT * FROM user order by username OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithDescOrderBy()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10);
self::assertEquals('SELECT * FROM user ORDER BY username DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithMultipleOrderBy()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC, usereamil ASC', 10);
self::assertEquals('SELECT * FROM user ORDER BY username DESC, usereamil ASC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithSubSelect()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result', 10);
self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithSubSelectAndOrder()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC', 10);
self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC', 10);
self::assertEquals('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithSubSelectAndMultipleOrder()
{
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC, uid ASC', 10, 5);
self::assertEquals('SELECT * FROM (SELECT u.id as uid, u.name as uname) dctrn_result ORDER BY uname DESC, uid ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id uid, u.name uname) dctrn_result ORDER BY uname DESC, uid ASC', 10, 5);
self::assertEquals('SELECT * FROM (SELECT u.id uid, u.name uname) dctrn_result ORDER BY uname DESC, uid ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
$sql = $this->_platform->modifyLimitQuery('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC, id ASC', 10, 5);
self::assertEquals('SELECT * FROM (SELECT u.id, u.name) dctrn_result ORDER BY name DESC, id ASC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
public function testModifyLimitQueryWithFromColumnNames()
{
$sql = $this->_platform->modifyLimitQuery('SELECT a.fromFoo, fromBar FROM foo', 10);
self::assertEquals('SELECT a.fromFoo, fromBar FROM foo ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY', $sql);
}
/**
* @group DBAL-927
*/
public function testModifyLimitQueryWithExtraLongQuery()
{
$query = 'SELECT table1.column1, table2.column2, table3.column3, table4.column4, table5.column5, table6.column6, table7.column7, table8.column8 FROM table1, table2, table3, table4, table5, table6, table7, table8 ';
$query.= 'WHERE (table1.column1 = table2.column2) AND (table1.column1 = table3.column3) AND (table1.column1 = table4.column4) AND (table1.column1 = table5.column5) AND (table1.column1 = table6.column6) AND (table1.column1 = table7.column7) AND (table1.column1 = table8.column8) AND (table2.column2 = table3.column3) AND (table2.column2 = table4.column4) AND (table2.column2 = table5.column5) AND (table2.column2 = table6.column6) ';
$query.= 'AND (table2.column2 = table7.column7) AND (table2.column2 = table8.column8) AND (table3.column3 = table4.column4) AND (table3.column3 = table5.column5) AND (table3.column3 = table6.column6) AND (table3.column3 = table7.column7) AND (table3.column3 = table8.column8) AND (table4.column4 = table5.column5) AND (table4.column4 = table6.column6) AND (table4.column4 = table7.column7) AND (table4.column4 = table8.column8) ';
$query.= 'AND (table5.column5 = table6.column6) AND (table5.column5 = table7.column7) AND (table5.column5 = table8.column8) AND (table6.column6 = table7.column7) AND (table6.column6 = table8.column8) AND (table7.column7 = table8.column8)';
$sql = $this->_platform->modifyLimitQuery($query, 10);
$expected = 'SELECT table1.column1, table2.column2, table3.column3, table4.column4, table5.column5, table6.column6, table7.column7, table8.column8 FROM table1, table2, table3, table4, table5, table6, table7, table8 ';
$expected.= 'WHERE (table1.column1 = table2.column2) AND (table1.column1 = table3.column3) AND (table1.column1 = table4.column4) AND (table1.column1 = table5.column5) AND (table1.column1 = table6.column6) AND (table1.column1 = table7.column7) AND (table1.column1 = table8.column8) AND (table2.column2 = table3.column3) AND (table2.column2 = table4.column4) AND (table2.column2 = table5.column5) AND (table2.column2 = table6.column6) ';
$expected.= 'AND (table2.column2 = table7.column7) AND (table2.column2 = table8.column8) AND (table3.column3 = table4.column4) AND (table3.column3 = table5.column5) AND (table3.column3 = table6.column6) AND (table3.column3 = table7.column7) AND (table3.column3 = table8.column8) AND (table4.column4 = table5.column5) AND (table4.column4 = table6.column6) AND (table4.column4 = table7.column7) AND (table4.column4 = table8.column8) ';
$expected.= 'AND (table5.column5 = table6.column6) AND (table5.column5 = table7.column7) AND (table5.column5 = table8.column8) AND (table6.column6 = table7.column7) AND (table6.column6 = table8.column8) AND (table7.column7 = table8.column8) ';
$expected.= 'ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
self::assertEquals($expected, $sql);
}
/**
* @group DDC-2470
*/
public function testModifyLimitQueryWithOrderByClause()
{
$sql = 'SELECT m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ? ORDER BY m0_.FECHAINICIO DESC';
$expected = 'SELECT m0_.NOMBRE AS NOMBRE0, m0_.FECHAINICIO AS FECHAINICIO1, m0_.FECHAFIN AS FECHAFIN2 FROM MEDICION m0_ WITH (NOLOCK) INNER JOIN ESTUDIO e1_ ON m0_.ESTUDIO_ID = e1_.ID INNER JOIN CLIENTE c2_ ON e1_.CLIENTE_ID = c2_.ID INNER JOIN USUARIO u3_ ON c2_.ID = u3_.CLIENTE_ID WHERE u3_.ID = ? ORDER BY m0_.FECHAINICIO DESC OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY';
$actual = $this->_platform->modifyLimitQuery($sql, 10, 5);
self::assertEquals($expected, $actual);
}
/**
* @group DBAL-713
*/
public function testModifyLimitQueryWithSubSelectInSelectList()
{
$sql = $this->_platform->modifyLimitQuery(
"SELECT " .
"u.id, " .
"(u.foo/2) foodiv, " .
"CONCAT(u.bar, u.baz) barbaz, " .
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " .
"FROM user u " .
"WHERE u.status = 'disabled'",
10
);
self::assertEquals(
"SELECT " .
"u.id, " .
"(u.foo/2) foodiv, " .
"CONCAT(u.bar, u.baz) barbaz, " .
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " .
"FROM user u " .
"WHERE u.status = 'disabled' " .
"ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY",
$sql
);
}
/**
* @group DBAL-713
*/
public function testModifyLimitQueryWithSubSelectInSelectListAndOrderByClause()
{
$sql = $this->_platform->modifyLimitQuery(
"SELECT " .
"u.id, " .
"(u.foo/2) foodiv, " .
"CONCAT(u.bar, u.baz) barbaz, " .
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " .
"FROM user u " .
"WHERE u.status = 'disabled' " .
"ORDER BY u.username DESC",
10,
5
);
self::assertEquals(
"SELECT " .
"u.id, " .
"(u.foo/2) foodiv, " .
"CONCAT(u.bar, u.baz) barbaz, " .
"(SELECT (SELECT COUNT(*) FROM login l WHERE l.profile_id = p.id) FROM profile p WHERE p.user_id = u.id) login_count " .
"FROM user u " .
"WHERE u.status = 'disabled' " .
"ORDER BY u.username DESC " .
"OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY",
$sql
);
}
/**
* @group DBAL-834
*/
public function testModifyLimitQueryWithAggregateFunctionInOrderByClause()
{
$sql = $this->_platform->modifyLimitQuery(
"SELECT " .
"MAX(heading_id) aliased, " .
"code " .
"FROM operator_model_operator " .
"GROUP BY code " .
"ORDER BY MAX(heading_id) DESC",
1,
0
);
self::assertEquals(
"SELECT " .
"MAX(heading_id) aliased, " .
"code " .
"FROM operator_model_operator " .
"GROUP BY code " .
"ORDER BY MAX(heading_id) DESC " .
"OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY",
$sql
);
}
public function testModifyLimitQueryWithFromSubquery()
{
$sql = $this->_platform->modifyLimitQuery("SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result", 10);
$expected = "SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
self::assertEquals($sql, $expected);
}
public function testModifyLimitQueryWithFromSubqueryAndOrder()
{
$sql = $this->_platform->modifyLimitQuery("SELECT DISTINCT id_0, value_1 FROM (SELECT k0_.id AS id_0, k0_.value AS value_1 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY value_1 DESC", 10);
$expected = "SELECT DISTINCT id_0, value_1 FROM (SELECT k0_.id AS id_0, k0_.value AS value_1 FROM key_measure k0_ WHERE (k0_.id_zone in(2))) dctrn_result ORDER BY value_1 DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
self::assertEquals($sql, $expected);
}
public function testModifyLimitQueryWithComplexOrderByExpression()
{
$sql = $this->_platform->modifyLimitQuery("SELECT * FROM table ORDER BY (table.x * table.y) DESC", 10);
$expected = "SELECT * FROM table ORDER BY (table.x * table.y) DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
self::assertEquals($sql, $expected);
}
/**
* @throws \Doctrine\DBAL\DBALException
*/
public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromBaseTable()
{
$querySql = "SELECT DISTINCT id_0, name_1 "
. "FROM ("
. "SELECT t1.id AS id_0, t2.name AS name_1 "
. "FROM table_parent t1 "
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id"
. ") dctrn_result "
. "ORDER BY id_0 ASC";
$alteredSql = "SELECT DISTINCT id_0, name_1 "
. "FROM ("
. "SELECT t1.id AS id_0, t2.name AS name_1 "
. "FROM table_parent t1 "
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id"
. ") dctrn_result "
. "ORDER BY id_0 ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY";
$sql = $this->_platform->modifyLimitQuery($querySql, 5);
self::assertEquals($alteredSql, $sql);
}
/**
* @throws \Doctrine\DBAL\DBALException
*/
public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnFromJoinTable()
{
$querySql = "SELECT DISTINCT id_0, name_1 "
. "FROM ("
. "SELECT t1.id AS id_0, t2.name AS name_1 "
. "FROM table_parent t1 "
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id"
. ") dctrn_result "
. "ORDER BY name_1 ASC";
$alteredSql = "SELECT DISTINCT id_0, name_1 "
. "FROM ("
. "SELECT t1.id AS id_0, t2.name AS name_1 "
. "FROM table_parent t1 "
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id"
. ") dctrn_result "
. "ORDER BY name_1 ASC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY";
$sql = $this->_platform->modifyLimitQuery($querySql, 5);
self::assertEquals($alteredSql, $sql);
}
/**
* @throws \Doctrine\DBAL\DBALException
*/
public function testModifyLimitSubqueryWithJoinAndSubqueryOrderedByColumnsFromBothTables()
{
$querySql = "SELECT DISTINCT id_0, name_1, foo_2 "
. "FROM ("
. "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 "
. "FROM table_parent t1 "
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id"
. ") dctrn_result "
. "ORDER BY name_1 ASC, foo_2 DESC";
$alteredSql = "SELECT DISTINCT id_0, name_1, foo_2 "
. "FROM ("
. "SELECT t1.id AS id_0, t2.name AS name_1, t2.foo AS foo_2 "
. "FROM table_parent t1 "
. "LEFT JOIN join_table t2 ON t1.id = t2.table_id"
. ") dctrn_result "
. "ORDER BY name_1 ASC, foo_2 DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY";
$sql = $this->_platform->modifyLimitQuery($querySql, 5);
self::assertEquals($alteredSql, $sql);
}
public function testModifyLimitSubquerySimple()
{
$querySql = "SELECT DISTINCT id_0 FROM "
. "(SELECT k0_.id AS id_0, k0_.field AS field_1 "
. "FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result";
$alteredSql = "SELECT DISTINCT id_0 FROM (SELECT k0_.id AS id_0, k0_.field AS field_1 "
. "FROM key_table k0_ WHERE (k0_.where_field IN (1))) dctrn_result ORDER BY 1 OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY";
$sql = $this->_platform->modifyLimitQuery($querySql, 20);
self::assertEquals($alteredSql, $sql);
}
public function testModifyLimitQueryWithTopNSubQueryWithOrderBy()
{
$querySql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC)';
$expectedSql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY (SELECT 0) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
$sql = $this->_platform->modifyLimitQuery($querySql, 10);
self::assertEquals($expectedSql, $sql);
$querySql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY t.data2 DESC';
$expectedSql = 'SELECT * FROM test t WHERE t.id = (SELECT TOP 1 t2.id FROM test t2 ORDER BY t2.data DESC) ORDER BY t.data2 DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY';
$sql = $this->_platform->modifyLimitQuery($querySql, 10);
self::assertEquals($expectedSql, $sql);
}
public function testModifyLimitQueryWithNewlineBeforeOrderBy()
{
$querySql = "SELECT * FROM test\nORDER BY col DESC";
$expectedSql = "SELECT * FROM test\nORDER BY col DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY";
$sql = $this->_platform->modifyLimitQuery($querySql, 10);
self::assertEquals($expectedSql, $sql);
}
}