Search code examples
perldbix-class

How do you specify index length when using DBIx::Class?

I'm using DBIx::Class and am using the sqlt_deploy_hook to:

sub {
    my ($self, $sqlt_table) = @_;
    $sqlt_table->add_index(name => 'indexes', fields => [keys %for_indexing]);
}

Some of the columns I want to index are of type text, or are varchars > 255 length. MySQL doesn't like text indexes unless you specify them with a length, like this:

index (long_field(996))

But looking at SQL::Translator::Schema::Table (which has the add_index method) and Index, I see no way to specify the length.

add_index(name => 'indexes', fields => ['long_field'])

generates this SQL:

INDEX `indexes` (`long_field`)

and this:

add_index(name => 'indexes', fields => ['long_field(996)'])

generates this SQL:

INDEX `indexes` (`long_field(996)`)

which doesn't work, since there is not column with that name.

Up to now I've gotten around this by simply not indexing my text columns. However I'm now trying to use DBIx::Class::DeploymentHandler which is converting my long varchars to text columns when I "install", and I really need those varchar columns indexed.

What can I do?

Solution

  • This is a drop late, but the latest version of SQL::Translator (1.64) now has direct support for this, via passing in a hashref for the field consisting of { name => $name, prefix_length => $num } (which would be { name => 'long_field', prefix_length => 996 } in the example from OP.