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


in reply to Re: dbicdump and relationships
in thread dbicdump and relationships

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

Replies are listed 'Best First'.
Re^3: dbicdump and relationships
by Anonymous Monk on Jan 14, 2013 at 14:33 UTC
      $ grep -E "belongs_to|has_many" 2 __PACKAGE__->belongs_to('density', 'RPG::Traveller::DB::Schema::densit +y'); __PACKAGE__->belongs_to('sector', 'RPG::Traveller::DB::Schema::sector' +); __PACKAGE__->has_many('get_subsector', 'RPG::Traveller::DB::Schema::su +bsector', 'sector'); __PACKAGE__->has_many('get_subsector', 'RPG::Traveller::DB::Schema::su +bsector', 'density');

      That works nicely. I'd still like to see dbicdump work properly though, but it is nice to have a backup plan. :-)


      Peter L. Berghold -- Unix Professional
      Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
Re^3: dbicdump and relationships
by zwon (Abbot) on Jan 14, 2013 at 15:01 UTC

    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.

          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.

      I knew there was something I didn't like about MySQL. I don't think I have that issue with PostgreSQL. :-(


      Peter L. Berghold -- Unix Professional
      Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg
        :) sqlt -f MySQL -t MySQL < in > out
        ... INDEX (`sector`), PRIMARY KEY (`subsector_id`), CONSTRAINT `subsector_fk` FOREIGN KEY (`density`) REFERENCES `densit +y` (`density_id`), CONSTRAINT `subsector_fk_1` FOREIGN KEY (`sector`) REFERENCES `secto +r` (`sector_id`) ) ENGINE=InnoDB; SET foreign_key_checks=1;

      OK after massively modifications to my tables this now works. I wonder if I would have run into the same issues with PostgreSQL?


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