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.
When the code gets deployed to a new server, we'd like to be able to run a test as part of the normal 'make test' that tells us whether or not the schema on that server is the same as what's in the code. So if someone makes a change that adds a new column to a table, for example, but forgets to make this change on one of the servers, the test will fail. 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. The two "best" ideas we've had so far are to either run the SQL in the code against a temporary database, and then compare both SHOW CREATE TABLE outputs, or to use something like SQL::Translator to convert both lots of SQL to a common format. Both seem much too cumbersome, however. Anyone have any brighter ideas? Thanks, Tony
