[Dbix-class] Examples, MySQL, and DBIx::Class::Schema::Loader

[Brandon Black]

On 9/27/06, Thomas L. Shinnick <tshinnic at io.com> wrote:
>
> Summary: need to document how to force SQL dialects to retain
> relationship information, and have more 'foolproof' SQL examples.
>
> I wanted to define my tables such that I'd be able to use
> DBIC::Schema::Loader eventually.  And so I wanted to be explicit in
> relationships, such as "belongs_to".
>
> So I coded up input to MySQL (4.1.18) that included
>       state_id    INTEGER   NOT NULL  REFERENCES api_state_codes(id),
>
> Then wanting to check that Schema::Loader would later be able to
> 'see' that description, I used "show create table" and was amazed to
> see _nothing_, no mention at all of the relationship.
>
> Peeking into Schema::Loader::DBI::mysql I saw it also used "show
> create table" to retrieve information, but expected to see a fuller
> form of the declaration.  So I changed the code to read
>        state_id      INTEGER     NOT NULL,
>        FOREIGN KEY (state_id) REFERENCES api_state_codes(id),
> and now "show create table" displayed
>      `state_id` int(11) NOT NULL default '0',
>      KEY `state_id` (`state_id`),
>      CONSTRAINT `api_county_codes_ibfk_1` FOREIGN KEY (`state_id`)
> REFERENCES `api_state_codes` (`id`)
>
> So at the very least the various DBIC::Schema::Loader::DBI::* modules
> need to mention what is required to include relationship information
> in SQL table creation sources.
>
> And it would be good, where Schema::Loader is mentioned in the DBIC
> docs, to remind that the relationships need to be explicit enough
> that the particular SQL engine retains that information for
> Schema::Loader to pick up later.


A doc patch for this to help people not get trapped by the stupidity of
MySQL would be appreciated.

As you have said in a few more words, MySQL accepts both of the standard SQL
syntaxes for declaring a relationship, but it silently ignores one of them.
There's nothing the Loader can do about that, and certainly no sane user
should expect the Loader to magically infer information that MySQL itself
does not possess.

-- Brandon
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060927/bb394059/attachment.htm