usna78

Following up on my previous post (MariaDB 10 and Perl DBIx::Class::Schema::Loader), I wanted to try the 'deploy' feature to create database tables from Schema/Result classes.

I was surprised that I could not create a table in the database when a timestamp field had a default of current_time(). The problem was that the generated CREATE TABLE entry placed quotes around 'current_timestamp()' causing an error and rejected entry.

As mentioned in a previous post, I had created file SQL/Translator/Producer/MariDB.pm as part of the effort to get MariaDB 10 clients to work correctly with DBIx::Class::Schema::Loader. Initially it was a clone of the MySQL.pm file with name substitutions. To correct the current_timestamp problem I added a search/replace in the existing create_field subroutine in the MariaDB.pm file to remove the quotes.

# current_timestamp ?
# current_timestamp (possibly as a default entry for a
# new record field), must not be quoted in the CREATE TABLE command
# provided to the database. Convert 'current_timestamp()'
# to current_timestamp() (no quotes) to prevent CREATE TABLE failure

if ( $field_def =~ /'current_timestamp\(\)'/ ) {
$field_def =~ s/'current_timestamp\(\)'/current_timestamp\(\)/;
}

This entry is made just before the subroutine returns $field_def. Now $schema->deploy(); works correctly to create the entire database.

The code shown below was tested satisfactorily to generate CREATE TABLE output (on a per table or multi-table basis) suitable for exporting (using tables Task and User as example table names):

My $schema = db_connect();

my $trans  = SQL::Translator->new (
     parser      => 'SQL::Translator::Parser::DBIx::Class',
     quote_identifiers => 1,
     parser_args => {
         dbic_schema => $schema,
         add_fk_index => 1,
         sources => [qw/
           Task User
         /],
     },
     producer    => 'MariaDB',
    ) or die SQL::Translator->error;

my $out = $trans->translate() or die $trans->error;

I believe the SQL/Translator/Producer/MySQL.pm file would benefit from this same code addition but I have not tested using a MySQL database and DBD::mysql.