Re: Testing Database Schema

[James Mastros]

Tony Bowden wrote:
We have an in-house procedure that says that the SQL definition for a
table should be included in the __DATA__ section of the class that
represents it (we're using Class::DBI), and is to be treated as the
definitive version of the schema.
[cut]
We're having too much difficulty thinking of a sane way to do this,
however. For now it just needs to cope with MySQL. But MySQL has an
interesting 'feature' where the CREATE TABLE schema you feed it, isn't
the same as the SHOW CREATE TABLE schema you get back - as it fills in
lots of extra defaults, quotes column names etc.
Change the procedure to require the bit after __DATA__ to match what 
mysql gives you back?  This is actually better then what you do anyway, 
as what mysql gives you is significantly more detailed.

Contrarywise, parse the SQL at the bottom of the file, treat it as a 
bunch of assertations, and attempt to verify each of them using 
non-modifying SQL and/or using DBI's standard introspection mechinsimis.

(Hint: One way to get a list of columns in a table is to select * limit 
1 from it, and inspect what you get back.)

	-=- James Mastros