[Dbix-class] RFC: Load from SQL

[Brandon Black]

On 7/10/07, Jeff Zucker <jeff at vpservices.com> wrote:
> Brandon Black wrote:
> > On 7/10/07, Jeff Zucker <jeff at vpservices.com> wrote:
> >  If you want to treat those oddball DBD's as
> > if they were not DBI-like, you could make a new
> > DBIx::Class::Schema::Loader::Foo.   However, if your backends are in
> > fact DBD's, one way to support them would be to bring them up to speed
> > with regard to the optional DBI metadata methods [1].
> >
> Yep, the problem is that they are so oddball that they never store any
> metadata other than the column names (think of a standard CSV file - no
> primary key info, no column types ...).  So with no metadata stored,
> there's nothing for the DBI methods to query.  The cool thing about the
> Loader is that you can use e.g. MySQL DDL syntax to make relationships
> between CSV files that have no metadata ... the example at perlmonks
> works with CSV even though DBD::CSV a) doesn't understand Foreign Keys
> and b) doesn't natively support three table joins.  It works because I
> use SQLT's MySQL parser on the DDL.  Eventutally I can make the DBDs
> store their DDL so that the metadata will be saved (in a schema.cfg file
> or some such) but that's too much work for summetime :-).
>

If you want to make Schema::Loader::SQLT, by all means go ahead, and
we'll put it in the main distro, I just think the DBI metadata route
is better in the long term.

My understanding (just based on a glance at the DBD::CSV docs) is that
what little metadata DBD::CSV knows about, it would obtain by parsing
"CREATE TABLE" statements executed via the $dbh (which have to be sent
every time one makes a new connection).  DBIx::Class (just the core,
not Loader/SQLT) would support this sort of thing reliably via
on_connect_do or connection via coderef.

If you put extra DDL parsing to extract info that DBD::CSV normally
ignores (PKs, UKs, FKs, etc) into Schema::Loader (technically, by
handing off most of the work to SQLT), you gain Schema::Loader
support, but that's the end of the story.

On the other hand, if you were to put this extra DDL parsing into
DBD::CSV itself when the CREATE TABLEs are executed (and again, it
might be possible to offload most of it to SQLT, and make it optional
for those that don't care via an extra connect() attribute), store the
extra metadata in the driver handle, and support querying it via the
standard DBI metadata methods, Schema::Loader would automatically
support you, and all kinds of other modules could use the info too
(for instance, other Loaders like Class::DBI::Loader or RDBO's
loader-like code).

-- Brandon