[Question] Why is the User table named "users" and not "user"? about bjyauthorize HOT 20 CLOSED

I'm a "fan" of singular table names, so "role" is IMO 👍

http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names
http://stackoverflow.com/questions/808992/singular-or-plural-database-table-names

from bjyauthorize.

or roles and not role :D

from bjyauthorize.

I'd indeed ask why role and not roles :)

Normal inconsistencies, btw: can't change due to BC policies.

from bjyauthorize.

I agree with @Ocramius I use plural for table name

from bjyauthorize.

@websafe a table represents a collection in the domain of SQL: don't make it singular, as it does not reflect the domain concept, and also exposes loads of issues with singularization/pluralization as well.

from bjyauthorize.

Maybe because "user" is a reserved word in most RDBMS.

from bjyauthorize.

@unkind Ok, but that's not a problem:

MySQL [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

and on the other hand using plural will force to use @ORM\Table(name="users") in entities.

I don't want to start a discussion about singular/plural ;-) I just wanted to know what's the cause of the current inconsistency and @Ocramius pointed this out. And I'm fine with it. I guess I will be able to use singular in my projects without any problems (BC, etc.).

from bjyauthorize.

and on the other hand using plural will force to use @ORM\Table(name="users") in entities.

Doctrine doesn't have autoescaping of reserved words, so you'd have to write @ORM\Table(name="user"). Anyway, that was just guess.

from bjyauthorize.

And what happens when we omit @ORM\Table(name="user") because the entity is already named User?

https://github.com/ZF-Commons/ZfcUserDoctrineORM/blob/master/src/ZfcUserDoctrineORM/Entity/User.php

I guess Doctrine will handle this, or am I wrong?

from bjyauthorize.

@websafe it will likely lead to crashes

from bjyauthorize.

Auto-quoting is not done for identifiers due to the fact that it is not portable.

from bjyauthorize.

Auto-quoting is not done for identifiers due to the fact that it is not portable.

What do you mean, there is RDBMS without quoting of identifiers? If so, it is possible to handle this case somehow for that drivers (add prefixes, suffixes, etc.)? Manual quoting doesn't solve portability issue at all, does it?

from bjyauthorize.

Auto-quoting won't work due to:

• MySQL behaves differently depending on the OS it runs on
• MySQL behaves differently depending on config settings in my.ini
• PostgreSQL behaves in a strict case-sensitive mode when quoting is used

Additionally, manual quoting has cut it so far, and there are no security implications anyway.

from bjyauthorize.

MySQL behaves differently depending on ...

Not sure what do you mean again. Backtick works always, no?

PostgreSQL behaves in a strict case-sensitive mode when quoting is used

I mean auto-quoting for reserved words, it is better than syntax error anyway :)

from bjyauthorize.

I'm trying now the following

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

on the default ZfcUserDoctrineORMs User entity:

<?php

namespace ZfcUserDoctrineORM\Entity;

use ZfcUser\Entity\User as ZfcUserEntity;

class User extends ZfcUserEntity
{}

which extends:

<?php

namespace ZfcUser\Entity;

class User implements UserInterface
{
    /**
     * @var int
     */
    protected $id;

    /**
     * @var string
     */
    protected $username;

    /**
     * @var string
     */
    protected $email;

    /**
     * @var string
     */
    protected $displayName;

    /**
     * @var string
     */
    protected $password;

    /**
     * @var int
     */
    protected $state;

    /**
     * Get id.
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set id.
     *
     * @param int $id
     * @return UserInterface
     */
    public function setId($id)
    {
        $this->id = (int) $id;
        return $this;
    }

    /**
     * Get username.
     *
     * @return string
     */
    public function getUsername()
    {
        return $this->username;
    }

    /**
     * Set username.
     *
     * @param string $username
     * @return UserInterface
     */
    public function setUsername($username)
    {
        $this->username = $username;
        return $this;
    }

    /**
     * Get email.
     *
     * @return string
     */
    public function getEmail()
    {
        return $this->email;
    }

    /**
     * Set email.
     *
     * @param string $email
     * @return UserInterface
     */
    public function setEmail($email)
    {
        $this->email = $email;
        return $this;
    }

    /**
     * Get displayName.
     *
     * @return string
     */
    public function getDisplayName()
    {
        return $this->displayName;
    }

    /**
     * Set displayName.
     *
     * @param string $displayName
     * @return UserInterface
     */
    public function setDisplayName($displayName)
    {
        $this->displayName = $displayName;
        return $this;
    }

    /**
     * Get password.
     *
     * @return string
     */
    public function getPassword()
    {
        return $this->password;
    }

    /**
     * Set password.
     *
     * @param string $password
     * @return UserInterface
     */
    public function setPassword($password)
    {
        $this->password = $password;
        return $this;
    }

    /**
     * Get state.
     *
     * @return int
     */
    public function getState()
    {
        return $this->state;
    }

    /**
     * Set state.
     *
     * @param int $state
     * @return UserInterface
     */
    public function setState($state)
    {
        $this->state = $state;
        return $this;
    }
}

and the result is:

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,
    UNIQUE INDEX UNIQ_8D93D649F85E0677 (username),
    UNIQUE INDEX UNIQ_8D93D649E7927C74 (email),
    PRIMARY KEY(user_id)
)
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;

and after running:

php vendor/bin/doctrine-module orm:schema-tool:create

the result:

ATTENTION: This operation should not be executed in a production environment.                     

Creating database schema...                                                                  
Database schema created successfully! 

and a user table is created.

I've found this:

Sometimes it is necessary to quote a column or table name because of reserved word conflicts. Doctrine does not quote identifiers automatically, because it leads to more problems than it would solve. Quoting tables and column names needs to be done explicitly using ticks in the definition.

here: http://doctrine-orm.readthedocs.org/en/latest/reference/basic-mapping.html

so this is generally what @Ocramius mentioned, but maybe some kind of auto quoting is done, when the entity has no @Table defined?

And continuing, ZfcUserDoctrineORM without BjyAuthorize is using user not users, but again, I don't want to force anything here, just wanted to report the inconsistency I've found :-)

That's all. I guess we can close this?

from bjyauthorize.

You just had luck there: user is recognized as a special token only in some contexts. In DDL it should work.

from bjyauthorize.

Try group for example, then a simple ->find() :P

from bjyauthorize.

OK @Ocramius I trust you :-) So in this case should we expect that https://github.com/ZF-Commons/ZfcUserDoctrineORM @EvanDotPro @spiffyjr will migrate the user table to users by adding a corresponding Doctrine annotation in https://github.com/ZF-Commons/ZfcUserDoctrineORM/blob/master/src/ZfcUserDoctrineORM/Entity/User.php?

from bjyauthorize.

Pretty sure that it "automatically" (don't think it does) use "user" instead "users" due to it's config.

In ZfcUserDoctrineOrm vendor, file: module.config.php

        'zfcuser_entity' => array(
            'class' => 'Doctrine\ORM\Mapping\Driver\XmlDriver',
            'paths' => __DIR__ . '/xml/zfcuser'
        ),

In /config/xml/zfcuser a file: ZfcUser.Entity.User.dcm.xml, below:

<mapped-superclass name="ZfcUser\Entity\User" table="user">

    <id name="id" type="integer" column="user_id">
        <generator strategy="AUTO" />
    </id>

    <field name="username" type="string" length="255" unique="true" nullable="true" />
    <field name="email" type="string" unique="true" length="255" nullable="true"/>
    <field name="displayName" column="display_name" type="string" length="50" nullable="true" />
    <field name="password" type="string" length="128" />
    <field name="state" type="smallint" nullable="true" />

</mapped-superclass>

First line of the Xml states; table="user", pretty sure that's a typo.

from bjyauthorize.

Not a typo, just different conventions. I've met many DBAs that prefer using plural, and many that prefer using singular.

from bjyauthorize.