[DBAL-833] [GH-542] [DBAL-825] Drop default constraints before altering column type on SQL Server Created: 07/Mar/14  Updated: 08/May/14  Resolved: 08/May/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: sqlserver, sqlsrv

Issue Links:
Duplicate
duplicates DBAL-825 ALTER COLUMN on mssql is failing if d... Resolved
duplicates DBAL-826 [GH-536] [WIP] unit test for DBAL-825 Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of deeky666:

Url: https://github.com/doctrine/dbal/pull/542

Message:

SQL Server implements column default values as constraints and therefore requires them to be dropped before a column type change.
This PR implements the recreation of default constraints on column type alterations.
Additionally some code in `SQLServerPlatform::getAlterTableSQL()` has been refactored to avoid code duplication and unnecessary SQL generation.
Please note that due to the issue's tests the PostgreSQL platform had to be altered to fulfill the same behaviour. PostgreSQL returned some strange default value like `666:smallint` when reverse engineering a column type change with a default value of `666` from type `smallint` to `integer`. So I think this PR fixes a bug in this platform, too. Additionally I had to change the deprecated default value retrieval SQL in PostgreSQL in order to work flawlessly. See the [documentation](http://www.postgresql.org/docs/9.3/static/catalog-pg-attrdef.html) at the very end.

I would also like to note that this PR is definitely not the end of the line concerning default values and column alterations. Some weird errors were revealed on other platforms while fixing this issue. MySQL for example denies default values on BLOB/TEXT type columns, DB2 just throws non-understandable syntax errors around and Oracle seems to map decimal/numeric types without a defined scale to integer when reverse engineering (not really related to this issue but it came up while testing).

Otherwise the tests pass on all platforms.



 Comments   
Comment by Doctrine Bot [ 07/Mar/14 ]

A related Github Pull-Request [GH-536] was closed:
https://github.com/doctrine/dbal/pull/536

Comment by Doctrine Bot [ 08/May/14 ]

A related Github Pull-Request [GH-542] was closed:
https://github.com/doctrine/dbal/pull/542

Comment by Steve Müller [ 08/May/14 ]

Fixed in commit: https://github.com/doctrine/dbal/commit/171a8762673ee61a89e3d6ce891cd2b475e7b5f7





[DBAL-830] [GH-539] unit test added for altering a column's default where the column name is... Created: 04/Mar/14  Updated: 10/Sep/14  Resolved: 10/Sep/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.4.2
Fix Version/s: 2.5
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Marco Pivetta
Resolution: Fixed Votes: 0
Labels: sqlsrv


 Description   

This issue is created automatically through a Github pull request on behalf of DeepDiver1975:

Url: https://github.com/doctrine/dbal/pull/539

Message:

... a keyword - fails on mssql:

Exception : [Doctrine\DBAL\DBALException] An exception occurred while executing 'ALTER TABLE column_keyword_test DROP CONSTRAINT DF_D3D4D2F1_4BF2EAC0':

SQLSTATE [42000, 3728]: [Microsoft][SQL Server Native Client 11.0][SQL Server]'DF_D3D4D2F1_4BF2EAC0' is not a constraint.
SQLSTATE [42000, 3727]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Could not drop constraint. See previous errors.

With queries:
5. SQL: 'ALTER TABLE column_keyword_test DROP CONSTRAINT DF_D3D4D2F1_4BF2EAC0' Params: 
4. SQL: 'SELECT    col.name,
                          type.name AS type,
                          col.max_length AS length,
                          ~col.is_nullable AS notnull,
                          def.definition AS [default],
                          col.scale,
                          col.precision,
                          col.is_identity AS autoincrement,
                          col.collation_name AS collation,
                          CAST(prop.value AS NVARCHAR(MAX)) AS comment -- CAST avoids driver error for sql_variant type
                FROM      sys.columns AS col
                JOIN      sys.types AS type
                ON        col.user_type_id = type.user_type_id
                JOIN      sys.objects AS obj
                ON        col.object_id = obj.object_id
                JOIN      sys.schemas AS scm
                ON        obj.schema_id = scm.schema_id
                LEFT JOIN sys.default_constraints def
                ON        col.default_object_id = def.object_id
                AND       col.object_id = def.parent_object_id
                LEFT JOIN sys.extended_properties AS prop
                ON        obj.object_id = prop.major_id
                AND       col.column_id = prop.minor_id
                AND       prop.name = 'MS_Description'
                WHERE     obj.type = 'U'
                AND       (obj.name = 'column_keyword_test' AND scm.name = SCHEMA_NAME())' Params: 
3. SQL: 'ALTER TABLE column_keyword_test ADD CONSTRAINT DF_D3D4D2F1_ACF51D19 DEFAULT 23 FOR [select]' Params: 
2. SQL: 'CREATE TABLE column_keyword_test ([select] INT NOT NULL)' Params: 

Trace:
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Connection.php:988
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:971
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\AbstractSchemaManager.php:612
C:\projects\doctrine\dbal\lib\Doctrine\DBAL\Schema\SQLServerSchemaManager.php:232
C:\projects\doctrine\dbal\tests\Doctrine\Tests\DBAL\Functional\Schema\SchemaManagerFunctionalTestCase.php:619
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:976
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:831
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php:648
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php:776
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:775
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php:745
C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php:349
C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php:176
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:268
C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php:506

#0 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(946): Doctrine\Tests\DbalFunctionalTestCase->onNotSuccessfulTest(Object(Doctrine\DBAL\DBALException))
#1 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestResult.php(648): PHPUnit_Framework_TestCase->runBare()
#2 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestCase.php(776): PHPUnit_Framework_TestResult->run(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest))
#3 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(775): PHPUnit_Framework_TestCase->run(Object(PHPUnit_Framework_TestResult))
#4 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\Framework\TestSuite.php(745): PHPUnit_Framework_TestSuite->runTest(Object(Doctrine\Tests\DBAL\Functional\Schema\SQLServerSchemaManagerTest), Object(PHPUnit_Framework_TestResult))
#5 C:\projects\doctrine\dbal\vendor\phpunit\phpunit\PHPUnit\TextUI\TestRunner.php(349): PHPUnit_Framework_TestSuite->run(Object(PHPUnit_Framework_TestResult), false, Array, Array, false)
#6 C:\Program Files (x86)\PHP\v5.3\pear\PHPUnit\TextUI\Command.php(176): PHPUnit_TextUI_TestRunner->doRun(Object(PHPUnit_Framework_TestSuite), Array)
#7 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(268): PHPUnit_TextUI_Command->run(Array, true)
#8 C:\Users\deepdiver\AppData\Local\Temp\ide-phpunit.php(506): IDE_Base_PHPUnit_TextUI_Command::main()
#9 {main}


 Comments   
Comment by Doctrine Bot [ 02/Sep/14 ]

A related Github Pull-Request [GH-539] was closed:
https://github.com/doctrine/dbal/pull/539





[DBAL-826] [GH-536] [WIP] unit test for DBAL-825 Created: 03/Mar/14  Updated: 08/May/14  Resolved: 07/Mar/14

Status: Resolved
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: None
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Doctrine Bot Assignee: Steve Müller
Resolution: Duplicate Votes: 0
Labels: sqlserver, sqlsrv

Issue Links:
Duplicate
duplicates DBAL-825 ALTER COLUMN on mssql is failing if d... Resolved
is duplicated by DBAL-833 [GH-542] [DBAL-825] Drop default cons... Resolved

 Description   

This issue is created automatically through a Github pull request on behalf of DeepDiver1975:

Url: https://github.com/doctrine/dbal/pull/536

Message:

Here is the unit test for http://www.doctrine-project.org/jira/browse/DBAL-825

Let's see if I can come up with a solution as well ....



 Comments   
Comment by Doctrine Bot [ 07/Mar/14 ]

A related Github Pull-Request [GH-536] was closed:
https://github.com/doctrine/dbal/pull/536

Comment by Steve Müller [ 07/Mar/14 ]

Duplicated by DBAL-825 and addressed in DBAL-833

Comment by Doctrine Bot [ 08/May/14 ]

A related Github Pull-Request [GH-542] was closed:
https://github.com/doctrine/dbal/pull/542





[DBAL-482] SQL Server Schema Manager returns incorrect value for autoincrement on IDENTITY columns Created: 03/Apr/13  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine DBAL
Component/s: Schema Managers
Affects Version/s: 2.2, 2.3, 2.3.3
Fix Version/s: 2.3.4
Security Level: All

Type: Bug Priority: Minor
Reporter: William Schaller Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: schematool, sqlserver, sqlsrv
Environment:

SQL Server



 Description   

When calculating table diffs, SQLServerSchemaManager returns column definitions for identity columns with _autoincrement set to FALSE.

This causes the schema update SQL generation to pump out a
ALTER TABLE x ALTER COLUMN id INT IDENTITY NOT NULL
for every single identity column in the schema.

The culprit is in DBAL\Schema\SQLServerSchemaManager, starting at line 43:

        $dbType = strtolower($tableColumn['TYPE_NAME']);
        $dbType = strtok($dbType, '(), ');

        $autoincrement = false;
        if (stripos($dbType, 'identity')) {
            $dbType = trim(str_ireplace('identity', '', $dbType));
            $autoincrement = true;
        }

When the column in question is an identity int column, the TYPE_NAME is "int identity". The second line of the snippet drops the "identity" signifier, causing the following lines that determine autoincrement to do nothing.

I simply moved the second line to below the autoincrement block ie:

        $dbType = strtolower($tableColumn['TYPE_NAME']);

        $autoincrement = false;
        if (stripos($dbType, 'identity')) {
            $dbType = trim(str_ireplace('identity', '', $dbType));
            $autoincrement = true;
        }

        $dbType = strtok($dbType, '(), ');

This change solves this issue for me, and as far as I can tell, has no other consequences.



 Comments   
Comment by Benjamin Eberlei [ 01/May/13 ]

Fixed for 2.3.4 and was fixed for 2.4 in a different way already.





[DBAL-422] Wrong VARCHAR default length in SQLServerPlatform Created: 24/Jan/13  Updated: 27/Dec/13

Status: Open
Project: Doctrine DBAL
Component/s: Platforms
Affects Version/s: 2.3.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Steve Müller Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: platform, sqlserver, sqlsrv, varchar


 Description   

In SQLServerPlatform the default length for a VARCHAR declaration is set to "255". But according to the SQLServer documentation from Microsoft the default length is "1", if omitted in the declaration.
See remarks: http://msdn.microsoft.com/en-us/library/ms186939.aspx
Also the default length is hardcoded in the "getVarcharTypeDeclarationSQLSnippet" method which in my opinion should be evaluated through "getVarcharDefaultLength".

I don't exactly know if the current implementation is intended, otherwise it should be fixed. I would then create an PR if desired.



 Comments   
Comment by Steve Müller [ 27/Dec/13 ]

The implementation is inconsistent on other platforms, too and can first be addressed in 3.0 I think. Otherwise this would be a BC break.





Generated at Thu Oct 02 14:54:25 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.