Perl DBIx::Class - Is it possible to provide a default value for inserting by using column attributes

Question

I have a (MySQL) table, import, defined as:

CREATE TABLE `import` (
  `importId` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `stuff`    varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`importId`),
  KEY `stuff` (`stuff`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I have used DBIx::Class::Schema::Loader->make_schema_at to generate my DBIx::Class schema.

I want to be able to issue $schema->resultset('Import')->create({}); (i.e., not providing a primary key) and have the importId field automatically populated with the result of a call to MySQL's uuid() function. To attempt to do this, I added the following to the bottom of Schema/Result/Import.pm:

# DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:cKY/6hdjrNaMgdhm6SlzoQ

use Data::Dumper;
print Dumper __PACKAGE__->columns_info();

__PACKAGE__->add_columns(
    '+importId' =>
    {
        default_value => \'uuid()',                              #'
    },
);

print Dumper __PACKAGE__->columns_info();

The data dumps show that the default_value attribute was added to importId, as expected:

$VAR1 = { 
      'importId' => { 
                      'data_type' => 'varchar',
                      'default_value' => \'uuid()',              #'
                      'is_nullable' => 0,
                      'size' => 36,
                      'accessor' => 'import_id'
                    },
      'stuff' => { 
                   'data_type' => 'varchar',
                   'is_nullable' => 1,
                   'size' => 12
                 }
};

But if I don't provide a value for importId in a create() call, DBIx::Class still issues INSERT INTO import () VALUES ().

I did achieve what I wanted by modifying the insert() method by placing the following at the end of Schema/Result/Import.pm:

use Moo;

before 'insert' => sub
{   
    my $self = $_[0];
    my $column_data = $self->{_column_data};
    $column_data->{importId} = \'uuid()'                         #'
        unless exists $column_data->{importId} && defined $column_data->{importId};
};

I had thought I should be able to achieve this with the default_value column attribute. Am I misunderstanding how this attribute works, or have I just implemented it incorrectly?

Primary keys are a database level construct, not an application level construct, so you might want ot consider performing this at the DB level a la this SO thread: http://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql — Len Jaffe, Jun 12 '14 at 14:06

score 1 · Accepted Answer · answered Jun 15 '14 at 16:41

You have to read docs better.

Look into L<DBIx::Class::ResultSource/add_columns> for default_value and you will find that

default_value
Set this to the default value which will be inserted into a column by the database. Can contain either a value or a function (use a reference to a scalar e.g. \'now()' if you want a function). This is currently only used to create tables from your schema, see "deploy" in DBIx::Class::Schema.

So, if you don't use deployment via DBIC, you rather want to add DEFAULT uuid() into your SQL schema for corresponded column.

Thanks. I'd obviously glanced over the phrase "currently only used to create tables from your schema" without letting its meaning sink in. — Waz, Jun 17 '14 at 07:23

Perl DBIx::Class - Is it possible to provide a default value for inserting by using column attributes

1 Answers1