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

          People

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

            Dates

            • Created:
              Updated:
              Resolved: