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

Closed
doctrinebot opened this Issue Apr 25, 2013 · 9 comments

2 participants

@doctrinebot

Jira issue originally created by user jedi_bc:

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.

@doctrinebot

Comment created by @ocramius:

[~jedi_bc] 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?

@doctrinebot

Comment created by jedi_bc:

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.

@doctrinebot

Comment created by jedi_bc:

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.

@doctrinebot

Comment created by @beberlei:

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 domainuser and a domaingroup column, and then it will work in the ManyToManyPersister and in the SchemaTool Update

@doctrinebot

Issue was closed with resolution "Invalid"

@doctrinebot

Comment created by jedi_bc:

With a domainuser and a domaingroup 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.

@doctrinebot

Comment created by @beberlei:

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

@doctrinebot

Comment created by jedi_bc:

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)

@doctrinebot

Comment created by jedi_bc:

@beberlei beberlei was assigned by doctrinebot Dec 6, 2015
@doctrinebot doctrinebot closed this Dec 6, 2015
@doctrinebot doctrinebot added the Bug label Dec 7, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment