How do I cleanly extract MySQL enum values in Perl?

Question

I have some code which needs to ensure some data is in a mysql enum prior to insertion in the database. The cleanest way I've found of doing this is the following code:

sub enum_values {
    my ( $self, $schema, $table, $column ) = @_;

    # don't eval to let the error bubble up
    my $columns = $schema->storage->dbh->selectrow_hashref(
        "SHOW COLUMNS FROM `$table` like ?",
        {},
        $column
    );

    unless ($columns) {
        X::Internal::Database::UnknownColumn->throw(
            column => $column,
            table  => $table,
        );
    }

    my $type = $columns->{Type} or X::Panic->throw(
        details => "Could not determine type for $table.$column",
    );

    unless ( $type =~ /\Aenum\((.*)\)\z/ ) {
        X::Internal::Database::IncorrectTypeForColumn->throw(
            type_wanted => 'enum',
            type_found  => $type,
        );
    }
    $type = $1;

    require Text::CSV_XS;
    my $csv = Text::CSV_XS->new;
    $csv->parse($type) or X::Panic->throw(
        details => "Could not parse enum CSV data: ".$csv->error_input,
    );
    return map { /\A'(.*)'\z/; $1 }$csv->fields;
}

We're using DBIx::Class. Surely there is a better way of accomplishing this? (Note that the $table variable is coming from our code, not from any external source. Thus, no security issue).

Answer 1

I'd say using Text::CSV_XS may be an overkill, unless you have weird things like commas in enums (a bad idea anyway if you ask me). I'd probably use this instead.

my @fields = $type =~ / ' ([^']+) ' (?:,|\z) /msgx;

Other than that, I don't think there are shortcuts.

Answer 2

No need to be so heroic. Using a reasonably modern version of DBD::mysql, the hash returned by DBI's column info method contains a pre-split version of the valid enum values in the key mysql_values:

my $sth = $dbh->column_info(undef, undef, 'mytable', '%');

foreach my $col_info ($sth->fetchrow_hashref)
{
  if($col_info->{'TYPE_NAME'} eq 'ENUM')
  {
    # The mysql_values key contains a reference to an array of valid enum values
    print "Valid enum values for $col_info->{'COLUMN_NAME'}: ", 
          join(', ', @{$col_info->{'mysql_values'}}), "\n";
  }
  ...
}