http://www.perlmonks.org?node_id=1012551

blue_cowdawg has asked for the wisdom of the Perl Monks concerning the following question:

I must be missing something here. I was reading the documentation for dbicdump and in there I read that by default relationships are preserved by default.

Thinking that I understand all that I presume that there should be "belongs_to" relationships built into the generated DBIx::Class result classes and I don't. What the heck am I missing?


Peter L. Berghold -- Unix Professional
Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

Replies are listed 'Best First'.
Re: dbicdump and relationships
by zwon (Abbot) on Jan 12, 2013 at 03:59 UTC

    Can you provide more details, so we could reproduce the problem? It works for me with sqlite:

    $ cat test.sql create table boo (id integer primary key, name text); create table foo (id integer primary key, boo_id integer, foreign key +(boo_id) references boo(id)); $ sqlite3 test.db <test.sql $ dbicdump Foo dbi:SQLite:test.db Dumping manual schema for Foo to directory . ... Schema dump completed. $ grep -RE "belongs_to|has_many" . ./Foo/Result/Boo.pm:Type: has_many ./Foo/Result/Boo.pm:__PACKAGE__->has_many( ./Foo/Result/Foo.pm:Type: belongs_to ./Foo/Result/Foo.pm:__PACKAGE__->belongs_to(
          Can you provide more details, so we could reproduce the problem? It works for me with sqlite:

      This is being done against MySQL and here is an abbreviated version of the schema I'm using:

      $ cat ../sql-src/schema.sql drop table if exists density; create table density ( density_id integer not null auto_increment primary key, density varchar(25) not null ); drop table if exists startype; create table startype ( startype_id integer not null auto_increment primary key, startype varchar(10) not null ); drop table if exists starsize; create table starsize ( starsize_id integer not null auto_increment primary key, starsize varchar(10) not null ); drop table if exists sector; create table sector ( sector_id integer not null auto_increment primary key, name varchar(80) not null ); drop table if exists subsector; create table subsector ( subsector_id integer not null auto_increment primary key, name varchar(80) not null default 'unnamed', location char not null default 'A', startx integer not null default 1, starty integer not null default 1, endx integer not null default 8, endy integer not null default 10, density integer not null references density(density_id), sector integer not null references sector(sector_id) );
      The relationship should be sector->has_many_subsector and subsector belongs_to sector. Here is a sanitized version of the config file for dbicdump:
      $ cat schema.conf schema_class RPG::Traveller::DB::Schema use_moose 1 <naming> relationships v6 monikers v6 column_accessors v6 force_ascii 1 </naming> <connect_info> dsn dbi:mysql:host=mydatabasehost.mydomain.com;database=st +armapper user AUSER pass APASSWORD </connect_info>
      and by the way use_moose doesn't seem to be doing anything. If I run dbicdump against that config file I see:
      $ dbicdump schema.conf Dumping manual schema for RPG::Traveller::DB::Schema to directory . .. +. Schema dump completed.
      and the grep shows: <code> $ grep -RE "belongs_to|has_many" . $

      Any pointers would be appreciated.


      Peter L. Berghold -- Unix Professional
      Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

        I see that DBIx::Class::Schema::Loader::DBI::mysql uses SHOW CREATE TABLE to get table definition, and then looking for

        CONSTRAINT ... FOREIGN KEY ... REFERENCES ...
        to get information about foreign keys. I tried to create some tables in mysql and check the output of SHOW CREATE TABLES:
        mysql> create table foo (id integer primary key, name varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> create table boo (id integer primary key, foo_id integer refere +nces foo(id)); Query OK, 0 rows affected (0.00 sec) mysql> create table bar (id integer primary key, foo_id integer, const +raint foo_id_fk foreign key (foo_id) references foo(id)); Query OK, 0 rows affected (0.00 sec) mysql> show create table boo; +-------+------------------------------------------------------------- +--------------------------------------------------------------------- +----------+ | Table | Create Table + + | +-------+------------------------------------------------------------- +--------------------------------------------------------------------- +----------+ | boo | CREATE TABLE `boo` ( `id` int(11) NOT NULL, `foo_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------- +--------------------------------------------------------------------- +----------+ 1 row in set (0.00 sec) mysql> show create table bar; +-------+------------------------------------------------------------- +--------------------------------------------------------------------- +--------------------------------------------------------------------- +--------------------------------------------+ | Table | Create Table + + + | +-------+------------------------------------------------------------- +--------------------------------------------------------------------- +--------------------------------------------------------------------- +--------------------------------------------+ | bar | CREATE TABLE `bar` ( `id` int(11) NOT NULL, `foo_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `foo_id_fk` (`foo_id`), CONSTRAINT `foo_id_fk` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id` +) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------- +--------------------------------------------------------------------- +--------------------------------------------------------------------- +--------------------------------------------+ 1 row in set (0.00 sec) mysql> drop table foo; ERROR 1217 (23000): Cannot delete or update a parent row: a foreign ke +y constraint fails mysql> drop table bar; Query OK, 0 rows affected (0.00 sec) mysql> drop table foo; Query OK, 0 rows affected (0.00 sec)

        As you can see, it looks like mysql silently ignores references in your SQL statements and doesn't create foreign keys. I would say it is rather nasty behaviour, if it doesn't accept your syntax it should throw an error.