NAME

DBIx::Class::Schema::Loader::DBI::RelPatterns - Relationship patterns for DBIx::Class::Schema::Loader

SYNOPSIS

### DBIx::Class::Schema::Loader synopsis with emphasis on
### loader_class argument and the added constructor options

# in a script
use DBIx::Class::Schema::Loader qw/ make_schema_at /;
make_schema_at(
    'My::Schema',
    { debug => 1,
      dump_directory => './lib',
      rel_constraint => [
          'bar_id' => 'bars.id',
          qr/(.*?)s?_?id$/i => qr/(.*?)s?$/i,
      ],
      rel_exclude => [
          'foo_id' => 'foos.',
          'foos.' => '',
      ],
    },
    [ 'dbi:mysql:dbname="foo"', 'myuser', 'mypassword',
      { loader_class => '::DBI::RelPatterns' }
    ],
);

# from the command line or a shell script with dbicdump (distributed
# with DBIx::Class::Schema::Loader). Do `perldoc dbicdump` for usage.
dbicdump -o dump_directory=./lib \
         -o components='["InflateColumn::DateTime"]' \
         -o rel_constraint='[qr/(.*?)s?_?id$/i => qr/(.*?)s?$/i]' \
         -o rel_exclude='["foo_id" => "foos."]' \
         -o debug=1 \
         My::Schema \
         'dbi:mysql:dbname=foo' \
         myuser \
         mypassword \
         '{ loader_class => "::DBI::RelPatterns" }'

### or generate and load classes at runtime
# note: this technique is not recommended
# for use in production code

package My::Schema;
use base qw/DBIx::Class::Schema::Loader/;

__PACKAGE__->loader_options(
    rel_constraint => [ qr/(.*?)s?_?id$/i => qr/(.*?)s?$/i ],
    rel_exclude    => [ 'foo_id' => 'foos.' ],
    # debug        => 1,
);

### in application code elsewhere:

use My::Schema;

my $schema1 = My::Schema->connect($dsn, $user, $password,
       { loader_class => '::DBI::RelPatterns', %attrs });
# -or-
my $schema1 = "My::Schema";
$schema1->connection(as above);
# -or-
my $schema1 = "My::Schema";
$schema1->loader_class('::DBI::RelPatterns');
$schema1->connection($dsn, $user, $password, $attrs);

DESCRIPTION

DBIx::Class::Schema::Loader::DBI::RelPatterns is a pseudo loader class that provides the means to set up the table relationships when DBIx::Class::Schema::Loader fails to for any reason. It is designed for use with storage engines that do not support foreign keys, such as MySQL's MyISAM; but should work with pretty much any DBI driver that:

• properly supports statistics_info method (DBD::mysql does starting from version 4.029; DBD::SQLite - from 1.40)
• and, more important, is explicitly supported by DBIx::Class::Schema::Loader DBI implementation.

Unlike conventional loader classes, DBIx::Class::Schema::Loader::DBI::RelPatterns allows DBIx::Class::Schema::Loader::DBI to load a driver-specific class, then extends it and wraps some of its methods (hence the word "pseudo"), adding to the mix the relationship patterns, user-definable via "rel_constraint" and "rel_exclude" options (which are added to the base options of DBIx::Class::Schema::Loader). If "rel_constraint" option is not specified, DBIx::Class::Schema::Loader::DBI::RelPatterns becomes a no-op, passing through to the driver-specific class. Otherwise it helps to set up the relationships whenever corresponding columns in the referencing key and the referenced key meet all of the following conditions:

• they match any of the rel_constraint patterns;
• they match none of the rel_exclude patterns;
• they have exactly the same or similar data types.

In general, the columns also have to be indexed. However, rel_constraint patterns allow one to explicitly specify that being indexed is not mandatory. This seems like a bad idea, but you may want to (or even have to) do this if the DBI driver in use does not support statistics_info method, which is required to obtain the non-unique index information (which is useless to DBIx::Class but can help to avoid the false-positive rel_constraint pattern matches when patterns are not specific enough). Although in such a case the composite-key relationships may be left out, thus limiting the resulting DBIx::Class schema to simple-key relationships.

When multiple columns in the referenced table meet the conditions, preference is given - in order of priority - to column that is listed in:

• primary key;
• unique key;
• single-column index;
• composite index as the first column or closer to the first column;
• largest composite index.

If a relationship pattern is way too vague, you may be warned that multiple columns or even tables meet the conditions for some foreign key and have equal priority. To avoid such warnings, either come up with a more specific relationship pattern or exclude the unwanted columns or tables via "rel_exclude" option.

By design, all determined relationships are considered to be simple-key relationships. However, when multiple relationships between two tables are identified, and columns of these relationships are listed in the corresponding composite indexes as the first columns (i.e., they form the leftmost prefixes), then a composite-key relationship is set up instead of multiple simple-key ones.

Note that rel_constraint and rel_exclude patterns do not affect the relationships that DBIx::Class::Schema::Loader is able to identify unaided. That is, DBIx::Class::Schema::Loader::DBI::RelPatterns helps to add missing relationships but not alter or remove the ones already identified.

ADDED CONSTRUCTOR OPTIONS

rel_constraint

Specifies the relationship patterns between any two tables. Table relationship is set up only if its condition matches any of the specified patterns. The patterns are processed in the order in which they are specified - first in, first out.

This option takes an arrayref with even number of elements (like in a hashref). Every odd element (pattern's left-hand side, a key) refers to the referencing table, while every even element (pattern's right-hand side, a value) refers to the table being referenced. Elements can be strings, qr// regexps, arrayrefs or hashrefs.

Simplified syntax:

rel_constraint => [
    # column foo_id in table bars references column id in table foos
    'bars.foo_id' => 'foos.id',
    
    # column foo_id in any table references primary key in table foos
    'foo_id' => 'foos.',
    
    # column (.+)_id in any table references primary key in table ${1}s
    # e.g., foo_id => foos.id, bar_id => bars.id, baz_id => bazs.id etc.
    qr/(.+)_id$/i => qr/(.+)s$/i,
    
    # column (.+)_id in any table references column id in table ${1}s
    # including self-referential relationships
    [ qr/(.+)s$/i, qr/(.+)_id$/i ] => [ qr/(.+)s$/i, 'id' ],
]

Strings, qr// regexps and arrayrefs actually are shortcuts to the hashrefs:

rel_constraint => [
    'bar.foo_id' => 'db1.foos.id',
    # hashref equivalents:
    { tab=>'bar', col=>'foo_id' }
        => { sch=>'db1', tab=>'foos', col=>'id' },
    
    'foo_id' => 'foos.id',
    # hashref equivalents:
    { col=>'foo_id' }
        => { tab=>'foos', col=>'id' },
    
    qr/(.*?)s?_?id$/i => qr/(.*?)s?$/i,
    # hashref equivalents:
    { col=>qr/(.*?)s?_?id$/i }
        => { tab=>qr/(.*?)s?$/i },
    
    [ qr/(.+)s$/i, qr/(.+)_id$/i ] => [ qr/(.+)s$/i, 'id' ],
    # hashref equivalents:
    { tab=>qr/(.+)s$/i, col=>qr/(.+)_id$/i }
        => { tab=>qr/(.+)s$/i, col=>'id' },
]

If elements are qr// regexps, then the key (pattern's left-hand side) refers to the referencing column name, while the value (pattern's right-hand side) refers to the referenced table name.

If element is a string in 'schema.table.column' format, then it gets split from right to left into column name, table name and schema name. That is, 'foo' would be column, 'bar.' would be table, 'baz..' would be schema.

The same principle applies to arrayrefs in ['schema','table','column'] format: ['foo'] would be column, ['bar',''] would be table, ['baz','',''] would be schema. Such an arrayref can contain strings and qr// regexps.

If element is not a shortcut but a hashref, then it can have the following keys:

• sch

  Schema name; string or qr// regexp.

• tab

  Table name; string or qr// regexp.

• col

  Column name; string or qr// regexp.

• index

  Index restrictions. Accepted values:

  • 'primary' - match only primary keys;
  • 'unique' - match unique keys as well;
  • 'any' (default) - match also non-unique indexes (in other words, all indexed columns);
  • 'optional' (forced when "tab" and "col" are non-empty strings) - match everything, including columns that are not indexed.

• type

  Level of similarity between column data types; applies to pattern's right-hand side. Accepted values:

  • 'similar' - ignore the size of column data types with size restriction (e.g., allow varchar(10) to reference varchar(15));
  • 'exact' (default) - require the size to match as well.

• diag

  Diagnostics of failures; false (default) or true, applies to pattern's right-hand side. If true, some diagnostic messages may be emitted, unless suppressed by the base quiet option.

"sch", "index", "type" and "diag" defaults can be adjusted by omitting "tab" and "col". The following two are equivalent:

rel_constraint => [
    # specify sch, index, type and diag explicitly, without touching the defaults
    { sch=>qr/(.*)/, col=>qr/(.*?)s?_?id$/i, index=>'optional' }
        => { sch=>qr/(.*)/, tab=>qr/(.*?)s?$/i, index=>'unique', type=>'similar', diag=>1 },
]

rel_constraint => [
    # adjust the defaults to forbid cross-schema relationships
    { sch=>qr/(.*)/ } => { sch=>qr/(.*)/ },
    # adjust other defaults
    { index=>'optional' } => { index=>'unique', type=>'similar', diag=>1 },
    # the adjusted defaults are applied to all patterns below
    qr/(.*?)s?_?id$/i => qr/(.*?)s?$/i,
]

Note that self-referential relationships are set up only if "tab" is specified on both sides of the relationship pattern:

rel_constraint => [
    # self-referential relationship (tab on both sides)
    'foos.foo_id' => 'foos.id',
    
    # not including self-referential relationships;
    # i.e. does not imply the relationship above
    'foo_id' => 'foos.id',
    
    # self-referential relationships (tab on both sides)
    [ qr/(.+)s$/i, qr/(.+)_id$/i ] => [ qr/(.+)s$/i, 'id' ],
    
    # not including self-referential relationships;
    # i.e. does not imply the relationships above
    { col=>qr/(.+)_id$/i } => { tab=>qr/(.+)s$/i, col=>'id' },
]

If qr// regexp creates capture groups, then the relationship is set up only when the captured contents of each regular expression within the given relationship pattern do match - with the exception of the captured contents of "sch" regular expressions because they are matched separately. For example, the following relationship pattern references column (foo|bar|baz)_id with column ${1}id in table ${1}s:

rel_constraint => [
    { col=>qr/^(foo|bar|baz)_id$/ }
        => { tab=>qr/^(foo|bar|baz)s$/, col=>qr/^(foo|bar|baz)id$/ },
]

Readable equivalent:

rel_constraint => [
    'foo_id' => 'foos.fooid',
    'bar_id' => 'bars.barid',
    'baz_id' => 'bazs.bazid',
]

Generic version:

rel_constraint => [
    qr/(.+)_id$/i => [ qr/(.+)s$/i, qr/(.+)id$/i ],
]

rel_exclude

Specifies the relationship pattern exclusions. Table relationship is set up only if its condition matches none of the specified patterns.

The syntax is borrowed from "rel_constraint"; however, only "sch", "tab", "col" keys in hashref elements are supported, and default "sch" cannot be set.

rel_exclude => [
    # column foo_id in any table should not reference column id in table foos
    'foo_id' => 'foos.id',
    
    # column (.+)_id in any table should not reference column id in table ${1}s
    qr/(.+)_id$/ => [ qr/(.+)s$/, 'id' ],
    
    # any column in table baz should not reference anything
    'baz.' => '',
    
    # any column in tables like 'foo%' should not reference anything
    { tab=>qr/^foo/ } => '',
    
    # anything in schema db1 should not reference anything in schema db2
    'db1..' => 'db2..',
    
    # self-referential relationships should not be set up
    { sch=>qr/(.*)/, tab=>qr/(.+)/ } => { sch=>qr/(.*)/, tab=>qr/(.+)/ },
]

DIAGNOSTICS

The following diagnostic messages, provided that diag => 1 is in effect, may be emitted to clarify the reasons for some relationships not being set up:

• index mismatch

  Referenced column is not listed in an index that meets the chosen "index" restrictions.

• unknown data type

  Data type of at least one of the corresponding columns is unknown while type => 'exact' is in effect.

• data type mismatch

  Corresponding columns in the referencing key and the referenced key have different data types.

• data type size mismatch

  Size restrictions of the corresponding columns' data types do not match while type => 'exact' is in effect.

• matched but excluded

  Relationship is excluded by a rel_exclude pattern.

• matched but not leftmost

  At least one of the corresponding columns is listed in a composite index not as the first column while a composite-key relationship cannot be set up and index => 'optional' is not in effect.

• matched but duplicated

  Duplicate relationship exists.

CAVEATS

When DBIx::Class::Schema::Loader::DBI::RelPatterns is unable to obtain the non-unique index information, a warning is emitted, unless suppressed by the base quiet option. This happens if the DBI driver in use does not support statistics_info method. In such a situation, basically, index => 'any', which is the default, has exactly the same effect as index => 'unique'. Most relationships cannot be identified with such restrictions because referencing keys seldom have unique constraints on them. To alleviate this problem, assuming updating the driver is not an option, "index" defaults can be adjusted the following way:

rel_constraint => [
    { index=>'optional' } => { index=>'unique' },
    # ...
]

Bear in mind that with index => 'optional' the patterns have to be more specific to avoid the false-positive matches.

PREREQUISITES

DBIx::Class::Schema::Loader::DBI::RelPatterns cannot be used with DBIx::Class::Schema::Loader versions prior to 0.05 because the ability to specify the loader class was not supported in the earlier versions.

SEE ALSO

DBIx::Class::Schema::Loader, DBIx::Class::Relationship, DBIx::Class.

AUTHOR

Aleksey Dvoriannikov lewa::cpan.org

COPYRIGHT AND LICENSE

Copyright (C) 2015 Aleksey Dvoriannikov

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

This program is distributed in the hope that it will be useful, but without any warranty; without even the implied warranty of merchantability or fitness for a particular purpose. See either the GNU General Public License or the Artistic License for more details.