[Dbix-class] DBIC usage smoke/sanity check

[Ben Tilly]

On Sat, Dec 22, 2012 at 11:46 AM, fREW Schmidt <frioux at gmail.com> wrote:
> On Tue, Dec 18, 2012 at 01:59:51PM -0800, Bill Moseley wrote:
>> On Tue, Dec 18, 2012 at 12:20 AM, Peter Rabbitson <rabbit+dbic at rabbit.us>wrote:
>>
>> > On Sat, Dec 15, 2012 at 10:05:07AM -0800, Bill Moseley wrote:
>> > > I've been asked to add something to our build process that does some kind
>> > > of sanity check on the database.   For example, make sure all columns
>> > > defined in the result class exist in the target database.
>> > >
>> > > I realize this won't catch every potential problem, but should catch
>> > > missing columns (or tables) which is our biggest risk.
>> > >
>> > > This seems like a reasonable way to do this?
>> >
>> > I personally would opt for an in-memory DBIC::Schema::Loader dump, and
>> > then running comparisons between the corresponding resultsource instances.
>> > A little bit more work- but much more robust.
>> >
>>
>> Our sources were created by hand, so I doubt the dump would match.    But,
>> maybe that's a good reason to update our sources.   We have been pretty
>> lazy and not defining that many attributes on our columns.
>>
>
> FWIW I've been planning on making a helper that would do this for a
> while now.  My plan was to use Schema::Loader to generate the
> representation of database and then make two
> SQL::Translator::Producers that would generate an introspectable data
> structure and a human readable description of the differences,
> respectively.

Unfortunately the migration of data from the database to DBIx::Class
is extremely lossy.  So even if it works, it cannot be used as a
useful tool to verify a lack of differences between databases.

I have found that even a round trip from DBIx::Class to the database
back to DBIx::Class through Schema::Loader tends to have spurious
differences show up.

> I've manually done something similar by using DBI to read the
> INFORMATION_SCHEMA and looking at the sources, but the above is more
> generic and may shake out bugs in SL and (of course) SQLT.

What would be awesome is to be able to do a diff between, say, a
PostgreSQL and a MySQL database.  However I think that the toolchain
isn't robust enough to do this for useful applications in a reliable
way.