Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-233

Signed / unsigned types are ignored by Comparator for MySQL schemas

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: 2.2.1
    • Fix Version/s: None
    • Component/s: Schema Managers
    • Security Level: All
    • Labels:
      None
    • Environment:
      MySQL 5.5

      Description

      E.g. one schema has an column1 which is unsigned tinyint, another schema doesn't.
      The sql that is generated is the following:

      ALTER TABLE table1 ADD column1 TINYINT(1) NOT NULL DEFAULT '1'

      And it should be:

      ALTER TABLE table1 ADD column1 TINYINT(1) UNSIGNED NOT NULL DEFAULT '1'

      If in one schema there is an unsigned column and in the other it is signed (that being the only difference), the difference is not noticed at all.

        Activity

        Hide
        Artem Goutsoul added a comment - - edited

        Fixed typo in the issue name

        Show
        Artem Goutsoul added a comment - - edited Fixed typo in the issue name
        Hide
        Benjamin Eberlei added a comment -

        Due to the infinite amount of datatypes and their special options we cannot support everything in Doctrine DBAL Schema. Since signed/unsigned is not a part of the Doctrine ORM API we also do not support it in the DBAL. There are extension hooks which make this available to you, if you want to extend Doctrine in that regard.

        See \Doctrine\DBAL\Schema\Column#getCustomSchemaOptions() and how Doctrine\DBAL\Schema\Comparator::diffColumn() uses them. You can fill these fields from events that are fired inside \Doctrine\DBAL\Schema\MySQLSchemaManager.

        Show
        Benjamin Eberlei added a comment - Due to the infinite amount of datatypes and their special options we cannot support everything in Doctrine DBAL Schema. Since signed/unsigned is not a part of the Doctrine ORM API we also do not support it in the DBAL. There are extension hooks which make this available to you, if you want to extend Doctrine in that regard. See \Doctrine\DBAL\Schema\Column#getCustomSchemaOptions() and how Doctrine\DBAL\Schema\Comparator::diffColumn() uses them. You can fill these fields from events that are fired inside \Doctrine\DBAL\Schema\MySQLSchemaManager.
        Hide
        Artem Goutsoul added a comment -

        Thank you for your answer! and for the info of where to look, I'll try to extend the DBAL myself.
        It just seemed based on the site and the docs that DBAL could be used as a more general purpose DBAL without the Doctrine ORM. Seems like a robust PHP based schema sync is an unattainable holy grail

        Show
        Artem Goutsoul added a comment - Thank you for your answer! and for the info of where to look, I'll try to extend the DBAL myself. It just seemed based on the site and the docs that DBAL could be used as a more general purpose DBAL without the Doctrine ORM. Seems like a robust PHP based schema sync is an unattainable holy grail
        Hide
        Artem Goutsoul added a comment -

        I studied the code, and it seems that there is already support for UNSIGNED in the data structures and comparison logic, however, it is simply not picked up from the column type and not saved.

        I added the following code to MySqlSchemaManager::_getPortableTableColumnDefinition($tableColumn):

        if (strpos(strtolower($tableColumn['type']), 'unsigned') !== false) $unsigned = 'unsigned';

        and the replacing the following code to AbstractPlatfrom::getColumnDeclarationSQL($name, array $field)

        $columnDef = $typeDecl . $charset . $default . $notnull . $unique . $check . $collation;

        with:
        $unsigned = (isset($field['unsigned']) && $field['unsigned']) ? ' ' . $this->getUnsignedFieldDeclarationSQL() : '';
        $columnDef = $typeDecl . $unsigned . $charset . $default . $notnull . $unique . $check . $collation;

        And implemented getUnsignedFieldDeclarationSQL to return 'UNSIGNED'

        After this UNSIGNED field types were generating correct ALTER TABLE statements.

        It seems like this kind of approach could safely be integrated into Doctrine DBAL without breaking existing functionality, and making it more usable outside of ORM.

        Show
        Artem Goutsoul added a comment - I studied the code, and it seems that there is already support for UNSIGNED in the data structures and comparison logic, however, it is simply not picked up from the column type and not saved. I added the following code to MySqlSchemaManager::_getPortableTableColumnDefinition($tableColumn): if (strpos(strtolower($tableColumn ['type'] ), 'unsigned') !== false) $unsigned = 'unsigned'; and the replacing the following code to AbstractPlatfrom::getColumnDeclarationSQL($name, array $field) $columnDef = $typeDecl . $charset . $default . $notnull . $unique . $check . $collation; with: $unsigned = (isset($field ['unsigned'] ) && $field ['unsigned'] ) ? ' ' . $this->getUnsignedFieldDeclarationSQL() : ''; $columnDef = $typeDecl . $unsigned . $charset . $default . $notnull . $unique . $check . $collation; And implemented getUnsignedFieldDeclarationSQL to return 'UNSIGNED' After this UNSIGNED field types were generating correct ALTER TABLE statements. It seems like this kind of approach could safely be integrated into Doctrine DBAL without breaking existing functionality, and making it more usable outside of ORM.
        Hide
        Stefano Kowalke added a comment - - edited

        Hi Benjamin,

        this is an old issue and I like to try your approach to use the events. Unfortunately I can not find any event code inside MySqlSchemaManager.php but in AbstractSchemaManager.php but I have no idea how to use it. The documentation at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/events.html describes only PostConnect events but. Can you give me some more hints here? I have the same issue like the OP.

        Show
        Stefano Kowalke added a comment - - edited Hi Benjamin, this is an old issue and I like to try your approach to use the events. Unfortunately I can not find any event code inside MySqlSchemaManager.php but in AbstractSchemaManager.php but I have no idea how to use it. The documentation at http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/events.html describes only PostConnect events but. Can you give me some more hints here? I have the same issue like the OP.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Artem Goutsoul
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: