vendor/doctrine/dbal/tests/Doctrine/Tests/DBAL/Platforms/SQLServerPlatformTest.php

   1 <?php
   2
   3 namespace Doctrine\Tests\DBAL\Platforms;
   4
   5 use Doctrine\DBAL\Platforms\SQLServer2008Platform;
   6 use Doctrine\DBAL\Types\Type;
   7
   8 class SQLServerPlatformTest extends AbstractPlatformTestCase
   9 {
  10     public function createPlatform()
  11     {
  12         return new SQLServer2008Platform;
  13     }
  14
  15     public function getGenerateTableSql()
  16     {
  17         return 'CREATE TABLE test (id INT IDENTITY NOT NULL, test NVARCHAR(255) DEFAULT NULL, PRIMARY KEY (id))';
  18     }
  19
  20     public function getGenerateTableWithMultiColumnUniqueIndexSql()
  21     {
  22         return array(
  23             'CREATE TABLE test (foo NVARCHAR(255) DEFAULT NULL, bar NVARCHAR(255) DEFAULT NULL)',
  24             'CREATE UNIQUE INDEX UNIQ_D87F7E0C8C73652176FF8CAA ON test (foo, bar) WHERE foo IS NOT NULL AND bar IS NOT NULL'
  25         );
  26     }
  27
  28     public function getGenerateAlterTableSql()
  29     {
  30         return array(
  31             'ALTER TABLE mytable ADD quota INT DEFAULT NULL',
  32             'ALTER TABLE mytable DROP COLUMN foo',
  33             'ALTER TABLE mytable ALTER COLUMN baz NVARCHAR(255) DEFAULT \'def\' NOT NULL',
  34             'ALTER TABLE mytable ALTER COLUMN bloo BIT DEFAULT \'0\' NOT NULL',
  35             "sp_RENAME 'mytable', 'userlist'",
  36         );
  37     }
  38
  39     public function testGeneratesSqlSnippets()
  40     {
  41         $this->assertEquals('RLIKE', $this->_platform->getRegexpExpression(), 'Regular expression operator is not correct');
  42         $this->assertEquals('"', $this->_platform->getIdentifierQuoteCharacter(), 'Identifier quote character is not correct');
  43         $this->assertEquals('(column1 + column2 + column3)', $this->_platform->getConcatExpression('column1', 'column2', 'column3'), 'Concatenation expression is not correct');
  44     }
  45
  46     public function testGeneratesTransactionsCommands()
  47     {
  48         $this->assertEquals(
  49                 'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED',
  50                 $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_READ_UNCOMMITTED)
  51         );
  52         $this->assertEquals(
  53                 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED',
  54                 $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_READ_COMMITTED)
  55         );
  56         $this->assertEquals(
  57                 'SET TRANSACTION ISOLATION LEVEL REPEATABLE READ',
  58                 $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_REPEATABLE_READ)
  59         );
  60         $this->assertEquals(
  61                 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE',
  62                 $this->_platform->getSetTransactionIsolationSQL(\Doctrine\DBAL\Connection::TRANSACTION_SERIALIZABLE)
  63         );
  64     }
  65
  66     public function testGeneratesDDLSnippets()
  67     {
  68         $dropDatabaseExpectation = 'DROP DATABASE foobar';
  69
  70         $this->assertEquals('SHOW DATABASES', $this->_platform->getShowDatabasesSQL());
  71         $this->assertEquals('CREATE DATABASE foobar', $this->_platform->getCreateDatabaseSQL('foobar'));
  72         $this->assertEquals($dropDatabaseExpectation, $this->_platform->getDropDatabaseSQL('foobar'));
  73         $this->assertEquals('DROP TABLE foobar', $this->_platform->getDropTableSQL('foobar'));
  74     }
  75
  76     public function testGeneratesTypeDeclarationForIntegers()
  77     {
  78         $this->assertEquals(
  79                 'INT',
  80                 $this->_platform->getIntegerTypeDeclarationSQL(array())
  81         );
  82         $this->assertEquals(
  83                 'INT IDENTITY',
  84                 $this->_platform->getIntegerTypeDeclarationSQL(array('autoincrement' => true)
  85         ));
  86         $this->assertEquals(
  87                 'INT IDENTITY',
  88                 $this->_platform->getIntegerTypeDeclarationSQL(
  89                         array('autoincrement' => true, 'primary' => true)
  90         ));
  91     }
  92
  93     public function testGeneratesTypeDeclarationsForStrings()
  94     {
  95         $this->assertEquals(
  96                 'NCHAR(10)',
  97                 $this->_platform->getVarcharTypeDeclarationSQL(
  98                         array('length' => 10, 'fixed' => true)
  99         ));
 100         $this->assertEquals(
 101                 'NVARCHAR(50)',
 102                 $this->_platform->getVarcharTypeDeclarationSQL(array('length' => 50)),
 103                 'Variable string declaration is not correct'
 104         );
 105         $this->assertEquals(
 106                 'NVARCHAR(255)',
 107                 $this->_platform->getVarcharTypeDeclarationSQL(array()),
 108                 'Long string declaration is not correct'
 109         );
 110     }
 111
 112     public function testPrefersIdentityColumns()
 113     {
 114         $this->assertTrue($this->_platform->prefersIdentityColumns());
 115     }
 116
 117     public function testSupportsIdentityColumns()
 118     {
 119         $this->assertTrue($this->_platform->supportsIdentityColumns());
 120     }
 121
 122     public function testDoesNotSupportSavePoints()
 123     {
 124         $this->assertTrue($this->_platform->supportsSavepoints());
 125     }
 126
 127     public function getGenerateIndexSql()
 128     {
 129         return 'CREATE INDEX my_idx ON mytable (user_name, last_login)';
 130     }
 131
 132     public function getGenerateUniqueIndexSql()
 133     {
 134         return 'CREATE UNIQUE INDEX index_name ON test (test, test2) WHERE test IS NOT NULL AND test2 IS NOT NULL';
 135     }
 136
 137     public function getGenerateForeignKeySql()
 138     {
 139         return 'ALTER TABLE test ADD FOREIGN KEY (fk_name_id) REFERENCES other_table (id)';
 140     }
 141
 142     public function testModifyLimitQuery()
 143     {
 144         $sql = $this->_platform->modifyLimitQuery('SELECT * FROM user', 10, 0);
 145         $this->assertEquals('SELECT TOP 10 * FROM user', $sql);
 146     }
 147
 148     public function testModifyLimitQueryWithEmptyOffset()
 149     {
 150         $sql = $this->_platform->modifyLimitQuery('SELECT * FROM user', 10);
 151         $this->assertEquals('SELECT TOP 10 * FROM user', $sql);
 152     }
 153
 154     public function testModifyLimitQueryWithOffset()
 155     {
 156         $sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10, 5);
 157         $this->assertEquals('SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY username DESC) AS doctrine_rownum, * FROM user) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 6 AND 15', $sql);
 158     }
 159
 160     public function testModifyLimitQueryWithAscOrderBy()
 161     {
 162         $sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username ASC', 10);
 163         $this->assertEquals('SELECT TOP 10 * FROM user ORDER BY username ASC', $sql);
 164     }
 165
 166     public function testModifyLimitQueryWithDescOrderBy()
 167     {
 168         $sql = $this->_platform->modifyLimitQuery('SELECT * FROM user ORDER BY username DESC', 10);
 169         $this->assertEquals('SELECT TOP 10 * FROM user ORDER BY username DESC', $sql);
 170     }
 171
 172     /**
 173      * @group DDC-1360
 174      */
 175     public function testQuoteIdentifier()
 176     {
 177         $this->assertEquals('[fo][o]', $this->_platform->quoteIdentifier('fo]o'));
 178         $this->assertEquals('[test]', $this->_platform->quoteIdentifier('test'));
 179         $this->assertEquals('[test].[test]', $this->_platform->quoteIdentifier('test.test'));
 180     }
 181
 182     /**
 183      * @group DDC-1360
 184      */
 185     public function testQuoteSingleIdentifier()
 186     {
 187         $this->assertEquals('[fo][o]', $this->_platform->quoteSingleIdentifier('fo]o'));
 188         $this->assertEquals('[test]', $this->_platform->quoteSingleIdentifier('test'));
 189         $this->assertEquals('[test.test]', $this->_platform->quoteSingleIdentifier('test.test'));
 190     }
 191
 192     /**
 193      * @group DBAL-220
 194      */
 195     public function testCreateClusteredIndex()
 196     {
 197         $idx = new \Doctrine\DBAL\Schema\Index('idx', array('id'));
 198         $idx->addFlag('clustered');
 199         $this->assertEquals('CREATE CLUSTERED INDEX idx ON tbl (id)', $this->_platform->getCreateIndexSQL($idx, 'tbl'));
 200     }
 201
 202     /**
 203      * @group DBAL-220
 204      */
 205     public function testCreateNonClusteredPrimaryKeyInTable()
 206     {
 207         $table = new \Doctrine\DBAL\Schema\Table("tbl");
 208         $table->addColumn("id", "integer");
 209         $table->setPrimaryKey(Array("id"));
 210         $table->getIndex('primary')->addFlag('nonclustered');
 211
 212         $this->assertEquals(array('CREATE TABLE tbl (id INT NOT NULL, PRIMARY KEY NONCLUSTERED (id))'), $this->_platform->getCreateTableSQL($table));
 213     }
 214
 215     /**
 216      * @group DBAL-220
 217      */
 218     public function testCreateNonClusteredPrimaryKey()
 219     {
 220         $idx = new \Doctrine\DBAL\Schema\Index('idx', array('id'), false, true);
 221         $idx->addFlag('nonclustered');
 222         $this->assertEquals('ALTER TABLE tbl ADD PRIMARY KEY NONCLUSTERED (id)', $this->_platform->getCreatePrimaryKeySQL($idx, 'tbl'));
 223     }
 224 }