[DDC-2413] orm:schema-tool:update want to drop&create PK on join table using composite keys Created: 25/Apr/13  Updated: 01/May/13  Resolved: 01/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: Mapping Drivers, Tools
Affects Version/s: Git Master, 2.3.3
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Bruno CHALOPIN Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: composite
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.



 Comments   
Comment by Marco Pivetta [ 25/Apr/13 ]

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?

Comment by Bruno CHALOPIN [ 25/Apr/13 ]

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.

Comment by Bruno CHALOPIN [ 25/Apr/13 ]

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.

Comment by Benjamin Eberlei [ 01/May/13 ]

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

Comment by Bruno CHALOPIN [ 01/May/13 ]

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.

Comment by Benjamin Eberlei [ 01/May/13 ]

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

Comment by Bruno CHALOPIN [ 01/May/13 ]

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)

Comment by Bruno CHALOPIN [ 01/May/13 ]

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.

Generated at Thu Apr 24 04:04:34 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.