On Mon Dec 04 17:42:22 2006, CDOLAN wrote:
Show quoted text> This seems like it must be a common request...
>
> Some versions of MySQL lose foreign key information.
If you actually manage to convince MySQL to enforce a Foreign Key
constraint, it will not be lost. The trick lies in actually ensuring
that MySQL really pays attention to your FK definitions and uses them.
The first and most obvious thing one must do is use the InnoDB engine
instead of MyISAM, as MyISAM doesn't support FKs. Secondarily, even
when using InnoDB, MySQL has what I consider to be a bug where it
ignores inline FK definitions.
An inline FK definition gets ignored silently, as in this example:
CREATE TABLE foo (
foocol integer not null primary key,
barcol integer not null references bar(baz)
);
Whereas an out-of-line FK definition will be taken seriously, as in this
example:
CREATE TABLE foo (
foocol integer not null primary key,
barcol integer not null,
FOREIGN KEY (barcol) REFERENCES bar(baz)
);
Show quoted text> It would be cool
> if this package could read from an SQL file instead of from a
> DBI-connected database. This would let DBIx::Class support more
> features than can be inferred from the database.
>
> I've found that SQL::Translator is a good schema parser. Ideally, one
> could translate from it's representation of the schema to something
> DBIx::Class::Schema::Loader can use?
>
> I might try the latter idea on my own, but thought I'd ask here before
> starting.
You've basically answered your own question. SQL::Translator would be
the route to support such things, rather than
DBIx::Class::Schema::Loader. Loader and SQLT are considered to be
orthogonal methods for obtaining the same result (which is to define
everything in one place, instead of repeating information manually in
your DDL ("CREATE TABLE", etc statements) and in your DBIx::Class schema).
Using SQLT with DBIx::Class's ->deploy method is considered the best
route. In this scenario, you manually define your DBIx::Class schema,
and use SQLT ->deploy support to generate the CREATE TABLE statements
from the schema. Loader goes about it in the opposite fashion: you
create and manage your DDL statements manually, and use the Loader to
extract information from the database and create your DBIx::Class schema
for you. We support the Loader route only because it's easier to use
for simple projects and legacy databases, and lots of people want
something of the sort, even if its not an ideal solution.
The SQLT method is considered superior because it is vendor-neutral,
because the potential information in a DBIx::Class schema is a superset
of the potential information in your DDL, and because it will support
versioning your schema.
I see you've found irc.perl.org #dbix-class, feel free to follow up
there for more detailed information :)
