[Dbix-class] SQL::Translator 0.08 Unique/Index clash problem

[Jon Schutz]

This original post was on the DBIx::Class list, copying now to SQLFairy-
developers with patch...

On Wed, 2007-01-31 at 14:36 +1030, Jon Schutz wrote:
> With SQL::Translator 0.07, a DBIx::Class declaration such as this:
> 
> __PACKAGE__->add_columns('plugin_id' => {
>                              'data_type' => 'integer',
> 			     'is_nullable' => 0,
> 			     'is_auto_increment' => 1,
> 			 },
> 			 'class' => {
>                              'data_type' => 'varchar',
> 			     'size' => 100,
> 			     'is_nullable' => 0,
> 			     'is_auto_increment' => 0,
> 			 },
> 			 );
> __PACKAGE__->set_primary_key('plugin_id');
> __PACKAGE__->add_unique_constraint('class' => [ 'class' ]);
> 
> would result in SQL that looks like this:
> 
>   plugin_id integer NOT NULL auto_increment,
>   class varchar(100) NOT NULL,
>   PRIMARY KEY (plugin_id),
>   UNIQUE (class)
> 
> and life was good.  After upgrading to SQL::Translator 0.08, I get:
> 
>   `plugin_id` integer NOT NULL auto_increment,
>   `class` varchar(100) NOT NULL,
>   INDEX (`plugin_id`),
>   INDEX (`class`),
>   PRIMARY KEY (`plugin_id`),
>   UNIQUE `class` (`class`)
> 
> which results in the error:
> 
> Duplicate key name 'class' at /usr/lib/perl5/site_perl/5.8.8/DBIx/Class/Storage/DBI.pm line 1120.
> 
> If I change the name of the key to class_xx, I get
> 
>   `plugin_id` integer NOT NULL auto_increment,
>   `class` varchar(100) NOT NULL,
>   INDEX (`plugin_id`),
>   INDEX (`class`),
>   PRIMARY KEY (`plugin_id`),
>   UNIQUE `class_xx` (`class`)
> 
> which is gets around the error but is wasteful of MySQL resources.
> 
> It seems to me that the old version got it right, and the new one is
> wrong.
> 
> I have the same problem whether using SQL::Translator directly or using
> the 'deploy' method.
> 
> I don't really know whether its a problem with SQL::Translator or the
> way DBIx::Class uses it, so here I am appealing to the wisdom of the
> list.
> 
> Other than downgrading to 0.07, is anyone aware of a fix for this?
> 

Time passes, and I find I'm looking at the same problem again.

Having dug deeper, SQL::Translator::Producer::MySQL in 0.07 added an
INDEX declaration for every FOREIGN_KEY constraint.  In 0.08, that bit
of code moved so that it now adds an INDEX declaration for every
constraint type unless an index has already been defined.

The valid types of constraint are PRIMARY_KEY, UNIQUE, CHECK_C,
FOREIGN_KEY, NOT_NULL.

I humbly suggest that there is no reason to add an index except in the
FOREIGN_KEY case.  PRIMARY_KEY and UNIQUE implicitly have an index
anyway, CHECK_C is used in Oracle/Postgres/DB2 - not quite sure what it
does but my guess is it refers so some stored procedure for validation
so doesn't require an indexed lookup - and NOT_NULL likewise doesn't
need an indexed lookup.

Thus I propose that that little bit of code only be invoked on
FOREIGN_KEY types, per patch (source & test) attached.

Jess, if it looks OK to you, could you please put this where it needs to
be?

Thanks,

-- 

Jon

-------------- next part --------------
A non-text attachment was scrubbed...
Name: patch.txt
Type: text/x-patch
Size: 1377 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070730/6bf204f9/patch.bin