Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Using DBIx::Class::Schema::Loader to determine constraints

by jds17 (Pilgrim)
on Oct 31, 2012 at 19:32 UTC ( #1001738=perlquestion: print w/ replies, xml ) Need Help??
jds17 has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

I am quite new to DBIx::Class and would like to use DBIx::Class::Schema::Loader to retrieve information about table constraints. I must admit being a bit lost in the documentation for DBIx::Class. I have played around a bit using an SQLite database and found a way how one can get part of the information as follows:

package My::Schema; use strict; use warnings; use DBIx::Class::Schema::Loader qw/ make_schema_at /; my $dsn = 'dbi:SQLite:./testdb'; make_schema_at( 'My::Schema', { debug => 0, dump_directory => './lib', }, [ $dsn, '', '' ], ); my $schema = My::Schema->connect( $dsn, '', '', '' ); my @relationships = $schema->source('T')->_relationships(); #... 1;

when tested against a SQLite database containing the following three tables:

CREATE TABLE a (u integer, PRIMARY KEY (u)); CREATE TABLE t (i integer, j integer, u integer, Primary key (i,j), co +nstraint fkey1 foreign key (u) references a (u)); CREATE TABLE s (j integer, r1 integer, r2 integer, constraint fkey for +eign key (r1,r2) references t (i,j));

the debugger output shows the following for the array @relationships:

DB<2> x @relationships 0 HASH(0x261359c) 'ss' => HASH(0x3c6eef4) 'attrs' => HASH(0x3c6c884) 'accessor' => 'multi' 'cascade_copy' => 0 'cascade_delete' => 0 'join_type' => 'LEFT' 'class' => 'My::Schema::Result::S' 'cond' => HASH(0x3c6926c) 'foreign.r1' => 'self.i' 'foreign.r2' => 'self.j' 'source' => 'My::Schema::Result::S' 'u' => HASH(0x3bfe16c) 'attrs' => HASH(0x3bfb69c) 'accessor' => 'filter' 'is_deferrable' => 0 'is_foreign_key_constraint' => 1 'join_type' => 'LEFT' 'on_delete' => 'NO ACTION' 'on_update' => 'NO ACTION' 'undef_on_null_fk' => 1 'class' => 'My::Schema::Result::A' 'cond' => HASH(0x3bfdd8c) 'foreign.u' => 'self.u' 'source' => 'My::Schema::Result::A'

So it contains a lot of the information I am looking for. But apart from the fact that _relationships() starts with an underscore I am quite sure that rummaging about the innards of this data structure is not the right way to do it.

Is there a better way?

Update: I did not say exactly what information I am looking for, which is: for a specific table, I need the unique constraints/indices together with the constrained columns' names as well as all the foreign keys with constrained columns, referenced tables and referenced columns.

Comment on Using DBIx::Class::Schema::Loader to determine constraints
Select or Download Code
Re: Using DBIx::Class::Schema::Loader to determine constraints
by pokki (Scribe) on Oct 31, 2012 at 21:33 UTC

    If you don't need a full ORM (and it looks like that's not the point for you) you probably just want SQL::Translator instead:

    #!perl use strict; use warnings; use 5.010; use SQL::Translator; use Data::Dumper; use SQL::Translator::Parser::SQLite; my $data = do { local $/; open my $fh, '<', 'data.sql' or die "Can't read file: +$!\n"; <$fh> }; my $sqlt = SQL::Translator->new; SQL::Translator::Parser::SQLite::parse($sqlt, $data); my $schema = $sqlt->schema; print Dumper([ map { $_->name } $schema->get_table('s')->get_constrain +ts ]);

    Gives me:

    $VAR1 = [ 'fkey' ];

    See SQL::Translator::Schema::Constraint for any other methods to call on the Constraint object. It's a bit convoluted to use like this; normally you'd use SQL::Translator to, as the name implies, translate your DDL from a format (e.g. SQLite-style SQL) into another (e.g. MySQL).

      This is a nice alternative, pokki, I really don't need an ORM for my current purpose. What I am trying to achieve is getting the information about constraints in a uniform way for as many DBMSs as possible, and it has been suggested that I try DBIx::Class::Schema::Loader. As I can see, SQL::Translator supports all of the DBMSs I wanted to support, so I am fine with SQL::Translator, too.

      What I could not get from the documentation is in which form the dump of the database structure which is passed as second parameter to parse() is expected (for each supported DBMS). Do you know the expected format?

        (snip) It has been suggested that I try DBIx::Class::Schema::Loader. As I can see, SQL::Translator supports all of the DBMSs I wanted to support, so I am fine with SQL::Translator, too.

        That would be because DBICSL uses SQL::Translator behind the scenes, as far as I can tell. Using SQL::Translator directly is just cutting out the middle man.

        What I could not get from the documentation is in which form the dump of the database structure which is passed as second parameter to parse() is expected (for each supported DBMS). Do you know the expected format?

        The parse() function expects the raw DDL as a string. In my example, data.sql is just your DDL as is:

        CREATE TABLE a (u integer, PRIMARY KEY (u)); CREATE TABLE t (i integer, j integer, u integer, Primary key (i,j), co +nstraint fkey1 foreign key (u) references a (u)); CREATE TABLE s (j integer, r1 integer, r2 integer, constraint fkey for +eign key (r1,r2) references t (i,j));

        The various SQL::Translator::Parser::$FOOPARSER classes document individually what grammar they support. Sometimes a few bits are missing, but the author and the community are rather responsive and it's not too hard to patch it yourself if you know the basics of writing a grammar. "Normal" MySQL syntax is a problem, though. The ANSI compatibility mode of mysqldump seems to produce parser-friendlier output, but even then I usually have to tweak it until the parser accepts it.

        At $work I maintain an SQLite schema as the reference DDL for a database ("if SQLite supports it, everything supports it -- and if they don't they have no excuse"). DBICSL (through SQL::Translator) is pretty good at turning it into a DBIC schema which then produces MySQL-compatible DDL for deployment (and diff files for upgrading). It even knows about a few things not encoded in the grammar itself, e.g. old versions of MySQL don't support VARCHAR columns with a size larger than 255, so VARCHAR(500) in the DDL would eventually turn into a TEXT column. So far everything I've thrown at it, constraints, keys, cascade actions have been supported (cascade action support is recent though). I've only ever had trouble with triggers, and had to work around this by providing custom code for each supported DBMS.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1001738]
Approved by herveus
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2014-07-12 23:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (242 votes), past polls