Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-2413

orm:schema-tool:update want to drop&create PK on join table using composite keys

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Invalid
    • Affects Version/s: Git Master, 2.3.3
    • Fix Version/s: None
    • Component/s: Mapping Drivers, Tools
    • Security Level: All
    • Labels:
    • Environment:
      Ubuntu 12.04, php 5.4.9

      Description

      Given these entities :

      /**
       * Class Domain
       *
       * @Entity
       * @Table(name="profils_domains")
       */
      class Domain
      {
          /**
           * @var string
           *
           * @Id
           * @Column(type="string", length=22, nullable=false)
           */
          protected $name = '';
      }
      
      /**
       * Class User
       *
       * @Entity
       * @Table(name="profils_users")
       */
      class User
      {
          /**
           * @var string
           *
           * @Id
           * @Column(type="string", length=22, nullable=false)
           */
          protected $name = '';
      
          /**
           * @var Domain
           *
           * @Id
           * @ManyToOne(targetEntity="Domain", fetch="LAZY")
           * @JoinColumn(name="domain", referencedColumnName="name", onDelete="CASCADE")
           */
          protected $domain;
          
          /**
           * @var Group[]|ArrayCollection
           *
           * @ManyToMany(targetEntity="Group", mappedBy="users")
           */
          protected $groups;
      }
      
      /**
       * Class Group
       *
       * @Entity
       * @Table(name="profils_groups")
       */
      class Group
      {
          /**
           * @var string
           *
           * @Id
           * @Column(type="string", length=22, nullable=false)
           */
          protected $name = '';
      
          /**
           * @var Domain
           *
           * @Id
           * @ManyToOne(targetEntity="Domain", fetch="LAZY")
           * @JoinColumn(name="domain", referencedColumnName="name", onDelete="CASCADE")
           */
          protected $domain;
          
          /**
           * @var User[]|ArrayCollection
           *
           * @ManyToMany(targetEntity="User", indexBy="name", fetch="EXTRA_LAZY")
           * @JoinTable(name="profils_groups_users",
           *      joinColumns={
           * @JoinColumn(name="group_name", referencedColumnName="name", onDelete="CASCADE"),
           * @JoinColumn(name="domain", referencedColumnName="domain", onDelete="CASCADE")
           *          },
           *      inverseJoinColumns={
           * @JoinColumn(name="user_name", referencedColumnName="name", onDelete="CASCADE"),
           * @JoinColumn(name="domain", referencedColumnName="domain", onDelete="CASCADE")
           *          }
           *      )
           */
          protected $users;
      }
      

      I want to link users and groups but only from the same domain.

      orm:schema-tool:create generate correct SQL :

      CREATE TABLE profils_users (name VARCHAR(22) NOT NULL, domain VARCHAR(22) NOT NULL, INDEX IDX_E75993CFA7A91E0B (domain), PRIMARY KEY(name, domain)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
      CREATE TABLE profils_domains (name VARCHAR(22) NOT NULL, PRIMARY KEY(name)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
      CREATE TABLE profils_groups (name VARCHAR(22) NOT NULL, domain VARCHAR(22) NOT NULL, INDEX IDX_229366BBA7A91E0B (domain), PRIMARY KEY(name, domain)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
      CREATE TABLE profils_groups_users (group_name VARCHAR(22) NOT NULL, domain VARCHAR(22) NOT NULL, user_name VARCHAR(22) NOT NULL, INDEX IDX_6CF8F4EA77792576A7A91E0B (group_name, domain), INDEX IDX_6CF8F4EA24A232CFA7A91E0B (user_name, domain), PRIMARY KEY(group_name, domain, user_name)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
      ALTER TABLE profils_users ADD CONSTRAINT FK_E75993CFA7A91E0B FOREIGN KEY (domain) REFERENCES profils_domains (name) ON DELETE CASCADE;
      ALTER TABLE profils_groups ADD CONSTRAINT FK_229366BBA7A91E0B FOREIGN KEY (domain) REFERENCES profils_domains (name) ON DELETE CASCADE;
      ALTER TABLE profils_groups_users ADD CONSTRAINT FK_6CF8F4EA77792576A7A91E0B FOREIGN KEY (group_name, domain) REFERENCES profils_groups (name, domain) ON DELETE CASCADE;
      ALTER TABLE profils_groups_users ADD CONSTRAINT FK_6CF8F4EA24A232CFA7A91E0B FOREIGN KEY (user_name, domain) REFERENCES profils_users (name, domain) ON DELETE CASCADE
      

      but I make a orm:schema-too:update it want to drop and recreate the PK of the join table each time, and with a wrong sql query :

      ALTER TABLE profils_groups_users DROP PRIMARY KEY;
      ALTER TABLE profils_groups_users ADD PRIMARY KEY (group_name, domain, user_name, domain)
      

      To avoid the duplication of the same field in primary key creation, you can modify getIndexFieldDeclarationListSQL from Doctrine\DBAL\Platforms\AbstractPlatform like :

          public function getIndexFieldDeclarationListSQL(array $fields)
          {
              $ret = array();
      
              foreach ($fields as $field => $definition) {
                  if (is_array($definition)) {
                      $ret[$field] = true;
                  } else {
                      $ret[$definition] = true;
                  }
              }
              $ret = array_keys($ret);
      
              return implode(', ', $ret);
          }
      

      But it would also be nice that doctrine don't try to drop the PK on each update.

        Activity

        Hide
        Bruno CHALOPIN added a comment -

        From the mysql documentation : The CHECK clause is parsed but ignored by all storage engines
        So there's no other ways to have a consistent database than to include this functionality in Doctrine.

        Show
        Bruno CHALOPIN added a comment - From the mysql documentation : The CHECK clause is parsed but ignored by all storage engines So there's no other ways to have a consistent database than to include this functionality in Doctrine.
        Hide
        Bruno CHALOPIN added a comment -

        I know this kind of normalization is not supported by Doctrine. That's why I've made a PR (which doesn't introduce any BC breaks). I don't see why it wouldn't be introduce in Doctrine as it's way simpler that adding a CHECK constraint (which I don't find any trace in the annotation documentation)

        Show
        Bruno CHALOPIN added a comment - I know this kind of normalization is not supported by Doctrine. That's why I've made a PR (which doesn't introduce any BC breaks). I don't see why it wouldn't be introduce in Doctrine as it's way simpler that adding a CHECK constraint (which I don't find any trace in the annotation documentation)
        Hide
        Benjamin Eberlei added a comment -

        Yes, this kind of normalization is not supported by Doctrine. You can enforce this with a CHECK constraint or in your domain code.

        Show
        Benjamin Eberlei added a comment - Yes, this kind of normalization is not supported by Doctrine. You can enforce this with a CHECK constraint or in your domain code.
        Hide
        Bruno CHALOPIN added a comment -

        With a domain_user and a domain_group you can no longer be sure that a user can only be in groups from the same domain which is mandatory for me. That's why I don't want the domain to be here twice as it must be the same for the user and the group.

        Show
        Bruno CHALOPIN added a comment - With a domain_user and a domain_group you can no longer be sure that a user can only be in groups from the same domain which is mandatory for me. That's why I don't want the domain to be here twice as it must be the same for the user and the group.
        Hide
        Benjamin Eberlei added a comment -

        You are thinking wrong here, domain can be present in the many to many table twice, with different domains. That you don't have this use-case, because you know the constraint to be 1:1 doesn't matter to Doctrine.

        It only works with you having a domain_user and a domain_group column, and then it will work in the ManyToManyPersister and in the SchemaTool Update

        Show
        Benjamin Eberlei added a comment - You are thinking wrong here, domain can be present in the many to many table twice, with different domains. That you don't have this use-case, because you know the constraint to be 1:1 doesn't matter to Doctrine. It only works with you having a domain_user and a domain_group column, and then it will work in the ManyToManyPersister and in the SchemaTool Update
        Hide
        Bruno CHALOPIN added a comment -

        It's even worse than I thought. When persisting new users, it create this SQL query :

        INSERT INTO profils_groups_users (group_name, domain, user_name, domain) VALUES (?, ?, ?, ?)
        

        I've made a quick fix by adding $columns = array_keys(array_flip($columns)); before the return in Doctrine\ORM\Persisters\ManyToManyPersister::_getInsertRowSQL and $mapping['joinTableColumns'] = array_keys(array_flip($mapping['joinTableColumns'])); before the foreach in Doctrine\ORM\Persisters\ManyToManyPersister::_collectJoinTableColumnParameters

        It works but I don't know if it's a proper solution.

        Show
        Bruno CHALOPIN added a comment - It's even worse than I thought. When persisting new users, it create this SQL query : INSERT INTO profils_groups_users (group_name, domain, user_name, domain) VALUES (?, ?, ?, ?) I've made a quick fix by adding $columns = array_keys(array_flip($columns)); before the return in Doctrine\ORM\Persisters\ManyToManyPersister::_getInsertRowSQL and $mapping ['joinTableColumns'] = array_keys(array_flip($mapping ['joinTableColumns'] )); before the foreach in Doctrine\ORM\Persisters\ManyToManyPersister::_collectJoinTableColumnParameters It works but I don't know if it's a proper solution.
        Hide
        Bruno CHALOPIN added a comment -

        I don't think it is a sort order problem (that is to say, i don't care the order).

        Firstable I don't know why it want to delete the PK as the generation of the join table is correct.
        Secondly, the regeneration of the PK is clearly buggy as it use 2 times the domain field.

        Show
        Bruno CHALOPIN added a comment - I don't think it is a sort order problem (that is to say, i don't care the order). Firstable I don't know why it want to delete the PK as the generation of the join table is correct. Secondly, the regeneration of the PK is clearly buggy as it use 2 times the domain field.
        Hide
        Marco Pivetta added a comment -

        Bruno CHALOPIN do I get this right if I say it's a problem in how columns are sorted in the index definition? Is that the problem?

        Show
        Marco Pivetta added a comment - Bruno CHALOPIN do I get this right if I say it's a problem in how columns are sorted in the index definition? Is that the problem?

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Bruno CHALOPIN
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: