What should be done with constraint only tables when using an ORM?

[NotionCommotion]

Instead of using MySQL's ENUM(), I typically create another table with the allowed values and have other tables include a FK to that table.

When using an ORM (i.e. Doctrine) should I create an entity for these?   It seems excessive, but if I don't, is there risk of the Doctrine deleting the tables?

Thanks

[requinix]

I can't say I know Doctrine very well but I'm rather confident it will not delete tables that it is missing configuration for. It's one of those "principle of least astonishment" things.

If you need to manage the table or its data through code then set up an entity. If not then don't.

[NotionCommotion]

4 hours ago, requinix said:

I can't say I know Doctrine very well but I'm rather confident it will not delete tables that it is missing configuration for. It's one of those "principle of least astonishment" things.

Yes, I expect you are technically right.  But if you follow their advice and use their schema-tool, I think it will.

Quote

Note that as you modify your entities' metadata during the development process, you'll need to update your database schema to stay in sync with the metadata. You can easily recreate the database using the following commands:

vendor/bin/doctrine orm:schema-tool:drop --force
vendor/bin/doctrine orm:schema-tool:create

Or you can use the update functionality:

vendor/bin/doctrine orm:schema-tool:update --force

The updating of databases uses a diff algorithm for a given database schema. This is a cornerstone of the Doctrine\DBAL package, which can even be used without the Doctrine ORM package.

 

[NotionCommotion]

I still haven't really checked this one off my list, and hoping confirmation or criticism of a few examples will set me straight. 

I have a table which has a column called "sign" which could only contain +1 and -1.  Creating a "sign" table with these values use a FK to enforces is fine, but don't create a Sign object.

What if I needed to store time units in a table (s for seconds, i for minutes, h for hours, etc) but also wanted the name for the given unit (i.e. "seconds" for s)?  I can create a Units object which holds this mapping in the DB, but it seems that I will not be managing this table through code, and thus shouldn't create this object.

If I currently don't need an entity but "think" I might sometime in the future, don't create an entity and only do so if it turns out to be needed.

Yea or nay?

Edited March 27, 2019 by NotionCommotion

[requinix]

1 hour ago, NotionCommotion said:

I have a table which has a column called "sign" which could only contain +1 and -1.  Creating a "sign" table with these values use a FK to enforces is fine, but don't create a Sign object.

Why do you need an object for something that is essentially a boolean value?

Quote

What if I needed to store time units in a table (s for seconds, i for minutes, h for hours, etc) but also wanted the name for the given unit (i.e. "seconds" for s)?  I can create a Units object which holds this mapping in the DB, but it seems that I will not be managing this table through code, and thus shouldn't create this object.

You could have the table. Lets you set up a foreign key constraint. Otherwise it's an enum backed by some hardcoded strings. Again, back to why the need for an object.

[NotionCommotion]

8 hours ago, requinix said:

 

Again, back to why the need for an object.

That is the response I was hoping for.

Why I thought maybe so?  Doctrine object migrator automatically creates these classes, but more so that "maybe" I would add some methods.  I don't know, maybe Units::toSeconds($v) or something.

[kicken]

For enum like fields I usually just map them as regular text/int fields.  I create a class with constants to represent the values and sometimes have a static method to get a human friendly name.

eg:

class AccountStatus {
    const PENDING = 1;
    const VALID = 2;
    const SUSPENDED = 3;
    const BANNED = 4;
    const DELETED = 5;

    public static function toText($status){
        switch ($status){
            case self::PENDING:
                return 'Pending';
            case self::VALID:
                return 'Valid';
            case self::SUSPENDED:
                return 'Suspended';
            case self::BANNED:
                return 'Banned';
            case self::DELETED:
                return 'Deleted';
            default:
                throw new \InvalidArgumentException;
        }
    }
}

AppBundle\Entity\User:
  type: entity
  table: user

  fields:
    accountStatus:
      type: smallint
      column: AccountStatus

It'd be nice if PHP had typed enum  support.

[NotionCommotion]

Thanks kicken,

I get your static function, but am assuming that this class is also responsible to ensure that user.accountStatus can only have values 1 through 5 written to it, true? How does this happen?  Looking at solution-2-defining-a-type, it appears I one can assign a type, but have never done so before and don't know for sure.

I was hoping the DB would contain the enforcement, and planned on creating a table called allowedAccountStatusValues with PK name AccountStatus, adding a record for each allowed value, and then adding a foreign key users.AccountStatus references allowedAccountStatusValues.AccountStatus.  This shouldn't be an issue for the allowedAccountStatusValues table because I can instruct Doctrine to ignore this table so migration tools will not complain, but I don't think I will be able to instruct Doctrine to ignore the foreign key constraints, and will look to using your approach.

[kicken]

5 hours ago, NotionCommotion said:

but am assuming that this class is also responsible to ensure that user.accountStatus can only have values 1 through 5 written to it, true? How does this happen?

No, it doesn't.  Such a thing is possible, but it's messy which is why I don't bother.  The fields are just standard types so they can hold any value, it's up to you to ensure that as you write code you only write valid values.  Having the constants helps there because you can reference those in your code rather than raw strings/numbers resulting in less possibility for mistakes.

If you want to enforce the values at the DB level then you'll want to do the extra table with foreign keys.

If you're ok with jut enforcing it in code then you could add some methods to validate the values you want to write are valid.  For example you could define your own type for doctrine that checks against the defined constants.

For things where my extra table would essentially be only a name and/or id number I generally just do like the above with class constants and enforce things through the code only.  It's easier to deal with in the code most of the time than having to reference entity objects.

[NotionCommotion]

9 hours ago, kicken said:

If you want to enforce the values at the DB level then you'll want to do the extra table with foreign keys.

Guess I don't need to.  Just that I came from prioritizing DB schema first and OOP second, and now things seem to be swapped.

9 hours ago, kicken said:

It's easier to deal with in the code most of the time than having to reference entity objects.

Agree.  Where it hit home for is when creating a new entity which contained one of these objects needing for foreign key integrity only.

9 hours ago, kicken said:

No, it doesn't.  Such a thing is possible, but it's messy which is why I don't bother. 

18 hours ago, kicken said:

It'd be nice if PHP had typed enum  support.

Really?  I am obviously surprised, but am certain you did your homework.  Maybe a mistake, but I am going to give it a try.  My desire is a class which 1) restricts setter values 2) can provide a key/name array used to populate HTML menus 3) performs your human readable mapping.  It shouldn't be part of an entity class as it maybe be used by more than one entity, and I while I currently don't know how, will likely use Doctrine\DBAL\Types\Type::addType().  Not positive, but thinking of utilizing https://github.com/myclabs/php-enum.  Any words of wisdom before I start?

Last item.  With your earlier example as well as the myclabs/php-enum class and others, class constants are used.  While I guess that is fine, it doesn't really seem necessary to me and a simple preset array variable with allowed keys/human-names would do just fine.  Since everyone uses class constants, I expect there is really a good reason and I am missing some big picture concept.  Any ideas what I might be missing?

[NotionCommotion]

Okay, I understand the class constants I think.  Just so one can do $code=AccountStatusEnum::PENDING, right?

As far as enforcing allowed valves, was thinking of creating a custom type like the following.  See any issues?  Note that the type isn't changing the value to PHP or the DB, but just confirming that it is valid which seems fine.  Also, I am not using ENUM because of its baggage, and just letting the app enforce unique values.  Recommend changing to ENUM? 

Never been confident when to use static method, properties, classes, etc.  Does this part look reasonable?  Evidently, I need to change my approach to using self::$strict as self relates to the current class and not the final class, but I can't use $this if it is static, so not sure how to handle.

<?php
namespace My\Project\Types;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use My\Project\AccountStatusEnum;

class AccountStatusType extends Type
{
    const TYPE = 'account_status';

    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        //While I "could" use ENUM, I am thinking maybe not and just having the application enforce.  Recommendations
        return 'SMALLINT(6)';
    }

    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        //No converstion???
        return $value;
    }

    public function convertToDatabaseValue($value, AbstractPlatform $platform)
    {
        //No converstion and just do check???
        if(!AccountStatusEnum::isValidValue($value)) {
            throw new \InvalidArgumentException;
        }
    }

    public function getName()
    {
        return self::TYPE;
    }
}

<?php
namespace My\Project;

abstract class AbstractEnum
{

    private static $constCacheArray = NULL;
    protected static $strict = true;

    private static function getConstants() {
        if (self::$constCacheArray == NULL) {
            self::$constCacheArray = [];
        }
        $calledClass = get_called_class();
        if (!array_key_exists($calledClass, self::$constCacheArray)) {
            $reflect = new \ReflectionClass($calledClass);
            self::$constCacheArray[$calledClass] = $reflect->getConstants();
        }
        return self::$constCacheArray[$calledClass];
    }

    public static function isValidName($name, $strict = null) {
        $constants = self::getConstants();

        if ($strict??self::$strict) {   //Doesn't use child's value?
            return array_key_exists($name, $constants);
        }

        $keys = array_map('strtolower', array_keys($constants));
        return in_array(strtolower($name), $keys);
    }

    public static function isValidValue($value) {
        $values = array_values(self::getConstants());
        return in_array($value, $values, $strict = true);
    }

    public static function toText($status){
        $map=array_flip(self::getConstants());
        if(!isset($map[$status])) {
            throw new \InvalidArgumentException;
        }
        return ucfirst(strtolower($map[$status]));
    }

    public static function getMap(bool $asNameValue=false) {
        if($asNameValue) {
            $arr=[];
            foreach(self::getConstants() as $key => $value) {
                $arr[]=['name'=>$key, 'value'=>$value];
            }
            return $arr;
        }
        else {
            return array_flip(self::getConstants());
        }
    }
}

<?php
namespace My\Project;

abstract class AccountStatusEnum extends AbstractEnum
{
    const PENDING = 1;
    const VALID = 2;
    const SUSPENDED = 3;
    const BANNED = 4;
    const DELETED = 5;

    protected static $strict = true;   //Optional
    /*
    //Override if desired
    public static function toText($status){
        switch ($status){
            case self::PENDING:
                return 'Pending';
            case self::VALID:
                return 'Valid';
            case self::SUSPENDED:
                return 'Suspended';
            case self::BANNED:
                return 'Banned';
            case self::DELETED:
                return 'Deleted';
            default:
                throw new \InvalidArgumentException;
        }
    }
    */
}

 

Edited March 28, 2019 by NotionCommotion

[kicken]

1 hour ago, NotionCommotion said:

Evidently, I need to change my approach to using self::$strict as self relates to the current class and not the final class, but I can't use $this if it is static, so not sure how to handle.

You can use static::$strict to reference the final class.

1 hour ago, NotionCommotion said:

Also, I am not using ENUM because of its baggage, and just letting the app enforce unique values.  Recommend changing to ENUM?

I wouldn't bother with enum.  I just stick to the standard types for the database.

[NotionCommotion]

Thanks right, I forgot about static::xxx.  Thanks for the ENUM advise.  I take it no glaring problems with my proposed solution and will get it working and post the final implementation.