table constraints for non 'id' PK on user table

[basz]

In combination with ZfcUser and ZfcUserDoctrine I'm getting incorrect CONSTRAINTS

note the REFERENCES user (id) vs REFERENCES user (user_id)

php public/index.php orm:schema-tool:create --dump-sql | grep user

CREATE TABLE user (user_id INT AUTO_INCREMENT NOT NULL, username VARCHAR(255) DEFAULT NULL, email VARCHAR(255) DEFAULT NULL, display_name VARCHAR(50) DEFAULT NULL, password VARCHAR(128) NOT NULL, state SMALLINT DEFAULT NULL, createdAt DATETIME NOT NULL, updatedAt DATETIME NOT NULL, UNIQUE INDEX UNIQ_8D93D649F85E0677 (username), UNIQUE INDEX UNIQ_8D93D649E7927C74 (email), PRIMARY KEY(user_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE AccessToken_OAuth2 (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, accessToken VARCHAR(255) DEFAULT NULL, expires DATETIME DEFAULT NULL, INDEX IDX_C092BBF419EB6921 (client_id), INDEX IDX_C092BBF4A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE AuthorizationCode_OAuth2 (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, authorizationCode VARCHAR(255) DEFAULT NULL, redirectUri LONGTEXT DEFAULT NULL, expires DATETIME DEFAULT NULL, idToken LONGTEXT DEFAULT NULL, INDEX IDX_7DED2FDD19EB6921 (client_id), INDEX IDX_7DED2FDDA76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE Client_OAuth2 (id INT AUTO_INCREMENT NOT NULL, user_id INT DEFAULT NULL, clientId VARCHAR(255) DEFAULT NULL, secret VARCHAR(255) DEFAULT NULL, redirectUri LONGTEXT DEFAULT NULL, grantType LONGTEXT DEFAULT NULL COMMENT '(DC2Type:array)', INDEX IDX_A66D48A8A76ED395 (user_id), UNIQUE INDEX idx_clientId_unique (clientId), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE RefreshToken_OAuth2 (id INT AUTO_INCREMENT NOT NULL, client_id INT NOT NULL, user_id INT DEFAULT NULL, refreshToken VARCHAR(255) DEFAULT NULL, expires DATETIME DEFAULT NULL, INDEX IDX_EEBE59C919EB6921 (client_id), INDEX IDX_EEBE59C9A76ED395 (user_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
// constraints
ALTER TABLE AccessToken_OAuth2 ADD CONSTRAINT FK_C092BBF4A76ED395 FOREIGN KEY (user_id) REFERENCES user (id);
ALTER TABLE AuthorizationCode_OAuth2 ADD CONSTRAINT FK_7DED2FDDA76ED395 FOREIGN KEY (user_id) REFERENCES user (id);
ALTER TABLE Client_OAuth2 ADD CONSTRAINT FK_A66D48A8A76ED395 FOREIGN KEY (user_id) REFERENCES user (id);
ALTER TABLE RefreshToken_OAuth2 ADD CONSTRAINT FK_EEBE59C9A76ED395 FOREIGN KEY (user_id) REFERENCES user (id);

instead of

// ...
ALTER TABLE AccessToken_OAuth2 ADD CONSTRAINT FK_C092BBF4A76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id);
ALTER TABLE AuthorizationCode_OAuth2 ADD CONSTRAINT FK_7DED2FDDA76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id);
ALTER TABLE Client_OAuth2 ADD CONSTRAINT FK_A66D48A8A76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id);
ALTER TABLE RefreshToken_OAuth2 ADD CONSTRAINT FK_EEBE59C9A76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id);

[TomHAnderson]

Did you change the mapping config entity_field_name? https://github.com/TomHAnderson/zf-oauth2-doctrine/blob/master/config/oauth2.doctrine-orm.global.php.dist#L91

[basz]

yes I tried to change every instance (4) of 'entity_field_name'

[TomHAnderson]

Are you using the default ZfcUserDoctrine entity definition (use_default_entities)? If you're not will you post your User entity XML [or annotation]?

Here's my custom entity def using ZfcUserDoctrine: https://github.com/TomHAnderson/apigility-oauth2-doctrine-skeleton/blob/master/module/Db/config/orm/Db.Entity.User.dcm.xml

[basz]

enable_default_entities is false. I have an entity that extends ZfcUser\Entity\User (https://gist.github.com/basz/7adb871fa9e987b7b308) and I assume that ZfcUserDoctrine handles the actual field definitions (which it does, the create sql for user is correct). I currently only add a Timestampable trait as extra fields.

I noticed that your Db.Entity.User.dcm.xml does not have this 'column="user_id"' defined, so you will not encounter this problem.

I have also seen such an issue before with Doctrine but I can't remember how and what now anymore... :-/

[basz]

hmm, what i now do is not extend ZfcUser\Entity\User and add those annotations to my Entity making sure the PK column is named 'id'. Avoiding this issue...

I believe this is an issue with Doctrine and not with your module or zfc user (doctrine) as I recall to have encountered this before.

thanks for your help! Quick unrelated question. Can I rename the table names via configuration?

[TomHAnderson]

No, you can't rename the tables unless you create your own set of entity definitions. You could copy the existing XML and put them in another module and keep the references to the entities the same then disable default entities (which really means disable default metadata).

The root cause of your problem is you're not using the right namingStrategy in your ClassMetadata. The namingStrategy by default calls the primary key 'id'. I think a longer term answer is the mapping array for the mapManyToOne calls need to be extended to allow additional mapping info.

To fix your problem this way you would add joinColumns:

                'refresh_token_entity' => array(
                    'entity' => 'ZF\OAuth2\Doctrine\Entity\RefreshToken',
                    'field' => 'refreshToken',
                    'join_columns' => array(
                        array(
                            'name' => 'user_id',
                            'referencedColumnName' => 'user_id',
                        ),
                    ),
                ),

[basz]

a I see (sort of :-))

Renaming is possible it seems https://gist.github.com/basz/f6160c207c81742b949b

[TomHAnderson]

Here's a fix for your issue. Use is documented in the README.md in the PR https://github.com/TomHAnderson/zf-oauth2-doctrine/pull/19

Please give it a try.