NAME

DBICx::Indexing - Easy way do declare extra indices to your tables

VERSION

version 0.002

SYNOPSIS

  package My::App::Schema::Result::Table;
  
  use strict;
  use warnings;
  use bsae 'DBIx::Class';
  
  ## include the DBICx::Indexing component
  __PACKAGE__->load_components('+DBICx::Indexing', 'Core');
  
  ## declare your table, fields, primary key, and unique constraints
  
  ## declare your extra indices
  __PACKAGE__->indices(
    important_field_idx => 'date_added',
    another_idx         => ['type', 'date_modified'],
  );

DESCRIPTION

Sometimes you need some extra indices on your tables. With DBIx::Class there is no easy way to declare them. The appropriate cookbook entry is "Adding Indexes And Functions To Your SQL" in DBIx::Class::Manual::Cookbook.

This component makes the process easier with some extra niceties.

In the source that needs extra indices, just call indices() with a hashref describing them.

The keys are the indices names, and the value for each key is the list of fields included in the index.

Covering indexes

If you specify an index specification that is already covered by any of the automatic indices created by the database (primary keys, unique constraints, or even other indices created manually via a per-source sqlt_deploy_hook()), then we will not create another index.

For example, if your table has 8 columns, a through h, with the following constraints:

• primary key: a, b, c

• unique constraint: d

• extra index: e, f

With that constraints, if you ask for these indices, you'll get these replies:

idx1 for a

This index will be ignored, covered by the primary key.

idx2 for a, c

This index will be created, it is not covered by any of the other indices. The a is covered by the primary key, but not the c, not in that order.

idx3 for d, a

This index will be created, it is not covered by any of the other indices. The unique constraint covers the d column, but not the a.

idx4 for e, f

Ignored, covered by the extra index.

Per-source sqlt_deploy_hook()

If you need to define a sqlt_deploy_hook() for a specific source, make sure that your code calls the next sqlt_deploy_hook() in turn.

You own sqlt_deploy_hook() should look something like this:

    sub sqlt_deploy_hook {
      my $self = shift;
      my ($table) = @_;
      
      ... your code goes here ...
      
      $self->next::method(@_) if $self->next::can;
    }

METHODS

indices()

    package My::Schema::Results::MySource;
    
    __PACKAGE__->load_components('+DBICx::Indexing', 'Core');
    ... rest of source initialization here...
    __PACKAGE__->indices({
      idx1 => ['field1', 'field2'],
      idx2 => 'field3',
    });

The indices() method accepts a hashref (or a hash) with the list of indices to create. The keys will be used as the index name. The values are the list of fields that each index will cover.

If the field list is only one element, you can just use a single scalar (like the idx2 index in the example above).

SEE ALSO

DBIx::Class, SQL::Translator

AUTHOR

Pedro Melo, <melo@simplicidade.org>

COPYRIGHT & LICENSE

Copyright 2010 Pedro Melo

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

cpanm DBICx::Indexing

perl -MCPAN -e shell
install DBICx::Indexing