[Dbix-class] Table indexes for deployment

[J. Shirley]

On 6/19/07, Eric Waters <ewaters at xmission.com> wrote:
> Let's get talking about being able to define table indexes in the DBIC schema in the same way the `extras' field is a passthru to SQL::Translator in the (experimental) Schema->deploy():
>
>   __PACKAGE__->add_columns(
>         id     => { data_type => 'INT', is_auto_increment => 1, extras => { unsigned => 1 }, },
>         author => { data_type => 'INT', extras => { unsigned => 1 }, },
>   );
>
>   __PACKAGE__->add_indexes(
>     author_idx => { columns => [ 'author' ], options => [ ... ] },
>   );
>
> When $schema->deploy(), causes SQL::Translator object to be called like:
>
>   $tr->schema->get_table('table_name')->add_index(
>           name => 'author_idx',
>           fields => [ 'author' ],
>       options => [ ... ],
>   );
>
> foreach table index prior to the deployment of the schema to the storage.  The options (and any other args) could be passthru to SQL::Translator::Schema::Table->add_index(), with the DBIC key 'columns' remapped to the SQLT key 'fields'.
>
> Thoughts?
>
> Eric Waters
>

Good timing, I just started poking at this.  I have a patch that works
for my purposes, lacking tests and all that stuff that dreams are made
of.

The pass-through method should have a variety of other constraints, so
as to not create duplicate indexes that would be created through
add_unique_constraints.  That part I don't have done yet, because
discussing it with Matt revealed my master plan was too dependent on
SQLT and I'd prefer to avoid that.

It needs some further discussion, as well, because it will be
modifying the way that DBIx::Class::ResultSource behaves so we need to
make sure to do the right thing.

I'll see if I can get some time to cleanup the patch and send it off
today or tomorrow.

The other general purpose idea is to add an ->sqlt_deploy_hook call
into your ResultSource classes -- then you get the table and can do
your add_index calls and whatever else directly on the
SQL::Translator::Schema::Table object.  The more I've been looking at
this idea, the more I like it over having an ->add_index method
though.

Thanks,
-Jay

-- 
J. Shirley :: jshirley at gmail.com :: Killing two stones with one bird...
http://www.toeat.com