Database Structure

[vahid-sohrabloo]

I not found any Database Structure for doctrine

[bakura10]

What ?

[spiffyjr]

I believe he's wanting default schema or instructions on creating the database structure for Doctrine. A valid request - and should be added to the README for all provider types.

[vahid-sohrabloo]

Thanks @spiffyjr

[jhuet]

Hi guys, it sure would help going to have the schema :)

[spiffyjr]

See #23 for further discussion.

[webdevilopers]

While I can see the schema.sql in the pull https://github.com/ZF-Commons/zfc-rbac/pull/23/files that has been merged a year ago I cannot see it in the master tree https://github.com/ZF-Commons/zfc-rbac/tree/master/data

Am I missing something @spiffyjr ?

I suggest this schema won't fit into the current entities provided in the master tree?

Or is a mapping required as mentioned here: http://programming.com/path/3805299346866440059/tutorial/3768613594611058508

[danizord]

@webdevilopers You can use Doctrine schema tool to generate the SQL for you based on mapping of entities provided in https://github.com/ZF-Commons/zfc-rbac/tree/master/data.

Just run: vendor/bin/doctrine-module orm:schema-tool:create

[webdevilopers]

Now I get it, thanks @danizord .

I wasn't used to generating the schema with doctrine. How does doctrine decide which data / entities to use for generating - every vendor entity (without .dist) or even every entity in my other modules or does it depend on the folder I am in to run the command?

Sorry for asking, I know this is no support forum. Maybe you have a short answer for me.

In addition the hint how to generate the structure could be added to the docs for other users not familiar with this procedure?

[danizord]

@webdevilopers it generates the schema for all entities in your app. If you want to generate for a specific entity, then you should run with --dump-sql, copy the output and run in your database manually removing the parts you don't want.

Yeah, we could add a note in the docs, can you open a PR?

[webdevilopers]

Thanks for the --dump-sql hint, @danizord !

This is the sql generated for the role and permission tables:

CREATE TABLE roles (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(48) NOT NULL, UNIQUE INDEX UNIQ_B63E2EC75E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE hierarchicalrole_hierarchicalrole (hierarchicalrole_source INT NOT NULL, hierarchicalrole_target INT NOT NULL, INDEX IDX_5707BC75CD934D59 (hierarchicalrole_source), INDEX IDX_5707BC75D4761DD6 (hierarchicalrole_target), PRIMARY KEY(hierarchicalrole_source, hierarchicalrole_target)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE hierarchicalrole_permission (hierarchicalrole_id INT NOT NULL, permission_id INT NOT NULL, INDEX IDX_8D28B77E83B93C19 (hierarchicalrole_id), INDEX IDX_8D28B77EFED90CCA (permission_id), PRIMARY KEY(hierarchicalrole_id, permission_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
CREATE TABLE permissions (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(128) NOT NULL, UNIQUE INDEX UNIQ_2DEDCC6F5E237E06 (name), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;
ALTER TABLE hierarchicalrole_hierarchicalrole ADD CONSTRAINT FK_5707BC75CD934D59 FOREIGN KEY (hierarchicalrole_source) REFERENCES roles (id) ON DELETE CASCADE;
ALTER TABLE hierarchicalrole_hierarchicalrole ADD CONSTRAINT FK_5707BC75D4761DD6 FOREIGN KEY (hierarchicalrole_target) REFERENCES roles (id) ON DELETE CASCADE;
ALTER TABLE hierarchicalrole_permission ADD CONSTRAINT FK_8D28B77E83B93C19 FOREIGN KEY (hierarchicalrole_id) REFERENCES roles (id) ON DELETE CASCADE;
ALTER TABLE hierarchicalrole_permission ADD CONSTRAINT FK_8D28B77EFED90CCA FOREIGN KEY (permission_id) REFERENCES permissions (id) ON DELETE CASCADE;

But I guess I will have to remove the _hierarchicalrole__ prefixes etc. Still I don't see any parent / children relations that are configured in the entities:

/**
 * @ORM\Entity
 * @ORM\Table(name="roles")
 */
class HierarchicalRole implements HierarchicalRoleInterface
{
    /**
     * @var int|null
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    /**
     * @var string|null
     *
     * @ORM\Column(type="string", length=48, unique=true)
     */
    protected $name;

    /**
     * @var HierarchicalRoleInterface[]|\Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="HierarchicalRole")
     */
    protected $children = [];

    /**
     * @var PermissionInterface[]|\Doctrine\Common\Collections\Collection
     *
     * @ORM\ManyToMany(targetEntity="Permission", indexBy="name", fetch="EAGER")
     */
    protected $permissions;
}

Is there anything else I will have to add?

[danizord]

@webdevilopers That's done in hierarchicalrole_hierarchicalrole table.

[danizord]

Example: Role 1 has children 2, 3 and 4 Role 2 has children 3 and 5

Join table (hierarchicalrole_hierarchicalrole):

Parent	Child
1	2
1	3
1	4
2	3
2	5

[webdevilopers]

Ok, without going into the tables, I added the _roleprovider:

            'ZfcRbac\Role\ObjectRepositoryRoleProvider' => [
                'object_manager'     => 'doctrine.entitymanager.orm_default', // alias for doctrine ObjectManager
                'class_name'         => 'Application\Entity\HierarchicalRole', // FQCN for your role entity class
                'role_name_property' => 'name', // Name to show
            ],

And followed the cookbook to add the roles to my entity:

namespace Application\Entity;

use ZfcUser\Entity\User as ZfcUserEntity;
use ZfcRbac\Identity\IdentityInterface;
use Doctrine\ORM\Mapping as ORM;

class User extends ZfcUserEntity implements IdentityInterface
{
    /**
     * @var Collection
     * @ORM\ManyToMany(targetEntity="HierarchicalRole")
     */
    private $roles;

    public function __construct()
    {
        $this->roles = new ArrayCollection();
    }

    /**
     * {@inheritDoc}
     */
    public function getRoles()
    {
        return $this->roles->toArray();
    }

    /**
     * Set the list of roles
     * @param Collection $roles
     */
    public function setRoles(Collection $roles)
    {
        $this->roles->clear();
        foreach ($roles as $role) {
            $this->roles[] = $role;
        }
    }

    /**
     * Add one role to roles list
     * @param \Rbac\Role\RoleInterface $role
     */
    public function addRole(RoleInterface $role)
    {
        $this->roles[] = $role;
    }
}

But there seems to be something missing on the ManyToMany relation: Fatal error: Uncaught exception 'Doctrine\ORM\Mapping\MappingException' with message 'The column id must be mapped to a field in class Application\Entity\User since it is referenced by a join column of another class.'

My main question is how the roles are connected to the User?

[danizord]

@webdevilopers That seems to be a issue with your Doctrine mapping. Did you run vendor/bin/doctrine-module orm:validate-schema?

[webdevilopers]

I just got back to give it another chance. The error seems to be gone. What I still don't understand is how the User is linked to the Role - am I missing a table, @danizord ?

Something like this? https://github.com/esserj/RbacUserDoctrineOrm/blob/master/data/schema-full.sql#L87-92

[danizord]

@webdevilopers Yes, that's the table that links User to Role. But again, it's better to let Doctrine Schema Tool generate the tables for you. Don't worry about it :smile:

[webdevilopers]

I wish I could give it a try, @danizord , but my Entities are so messed up with duplicate table names and other configuration problems that I can't run the console! ;) I will start a clean setup next time! :)

[webdevilopers]

Ok, finally could start from scratch, @danizord . Sorry in advance for blowing up this isse. Anyway I think it can be closed.

I started a new Application based on the current ZfcUser *1.1. version. I copied the desired entities HierarchicalRole.php.dist and Permission.php.dist from the /data directory to my Entities folder. Then I ran the Doctrine Schema Tool:

vendor/bin/doctrine-module orm:schema-tool:create --dump-sql

The schema was created successfull! I added some sample data:

INSERT INTO `zf2_development`.`roles` (`id`, `name`) VALUES ('1', 'guest'), ('2', 'user') ('3', 'admin');
INSERT INTO `zf2_development`.`hierarchicalrole_hierarchicalrole` (`hierarchicalrole_source`, `hierarchicalrole_target`) VALUES ('2', '3');

I had to make some changes to my User entity that differ from the Cookbook example. I created this PR https://github.com/ZF-Commons/zfc-rbac/pull/239 hoping everything is correct.

The main problem was linking the User to the Role. I havn't found any ZfcRbac example for this. That's why I tried the following modules:

• RoleUserBridge: https://github.com/darkmatus/roleuserbridge
• RbacUserDoctrineOrm: https://github.com/esserj/RbacUserDoctrineOrm

Unfortunately both only work with ZfcUser **0.***.

I then used the schema provided by RbacUserDoctrineOrm here: https://github.com/esserj/RbacUserDoctrineOrm/blob/master/data/schema-full.sql#L87-92

To make it work with my User entity and get arond this error Fatal error: Uncaught exception 'Doctrine\ORM\Mapping\MappingException' with message 'The column id must be mapped to a field in class Application\Entity\User since it is referenced by a join column of another class.'

I changed my annotations:

class User extends ZfcUserEntity implements IdentityInterface
{
    /**
     * @var Collection
     * @ORM\ManyToMany(targetEntity="HierarchicalRole")
     * @ORM\JoinTable(name="user_rbac_role",
     *      joinColumns={@ORM\JoinColumn(name="user_id", referencedColumnName="user_id")},
     *      inverseJoinColumns={@ORM\JoinColumn(name="role_id", referencedColumnName="id")}
     * )
     */
    private $roles;
}

Finally it worked but that took some doing.

I still hope that some of the modules mentioned above will soon update to work wit the current ZfcUser version.

Any comments on my steps ? Am I missing something / some docs?