Set default value of a column to unhex(replace(uuid(),'-',''))

[mp-improvement-it]

Hi guys,

We're in the process of migrating our database migrations into phinx.

Our tables all use binary(16) as datatype for the Primary Key column. To generate that value we set the default for the primary key to unhex(replace(uuid(),'-','')).

How would I be able to set that particular default using Phinx?

I tried the following (migration generated by https://github.com/odan/phinx-migrations-generator):

 `$this->table('Roles',` [
        'id' => false,
        'primary_key' => ['RoleID'],
        'engine' => 'InnoDB',
        'encoding' => 'utf8mb4',
        'collation' => 'utf8mb4_general_ci',
        'comment' => 'nolink=MenuItems_Roles',
        'row_format' => 'DYNAMIC',
    ])
        ->addColumn('RoleID', 'binary', [
            'null' => false,
            'default' => 'unhex(replace(uuid(),\'-\',\'\'))',
            'limit' => 16,
        ])
        ->addColumn('Name', 'string', [
            'null' => false,
            'limit' => 45,
            'collation' => 'utf8mb4_general_ci',
            'encoding' => 'utf8mb4',
            'after' => 'RoleID',
        ])
        ->addColumn('MenuItemID', 'binary', [
            'null' => true,
            'default' => null,
            'limit' => 16,
            'comment' => 'render=translate',
            'after' => 'Name',
        ])
        ->addColumn('LastModified', 'timestamp', [
            'null' => false,
            'default' => 'current_timestamp()',
            'after' => 'MenuItemID',
        ])
        ->addColumn('LastModifiedByID', 'binary', [
            'null' => true,
            'default' => null,
            'limit' => 16,
            'after' => 'LastModified',
        ])
        ->addIndex(['Name'], [
            'name' => 'Name',
            'unique' => true,
        ])
        `->create();`

This results in:

'PDOException: SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'RoleID' in /vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:198'

Also tried 'default' => 'unhex(replace(uuid(),"-",""))',. Same error.

Any suggestions?

Thanks!

[dereuromark]

There is now a type for it: Binaryuuid see https://github.com/cakephp/phinx/pull/1734

[mp-improvement-it]

Hey deeuromark, appreciate your answer. But it doesn't cover my question.

This works in phinx: 'default' => 'current_timestamp()'

But I want this to work: 'default' => 'unhex(replace(uuid(),"-",""))'

I would like to end up with the following MySQL scheme: CREATE TABLE Roles ( RoleID binary(16) NOT NULL DEFAULT unhex(replace(uuid(),'-','')), Name varchar(45) NOT NULL, MenuItemID binary(16) DEFAULT NULL COMMENT 'render=translate', LastModified timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), LastModifiedByID binary(16) NOT NULL, PRIMARY KEY (RoleID), UNIQUE KEY IdxName (Name) );

If this is currently not possible in Phinx then would you accept a feature in the form of a pull request?

[dereuromark]

Sure, feel free to make a PR for further detailed discussion.

[MasterOdin]

You should be able to use a \Phinx\Util\Literal for a default, and then when phinx does the migration it'll just take that value as-is. See https://book.cakephp.org/phinx/0/en/migrations.html#custom-column-types-default-values for more info.