Re: Testing Database Schema
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
Re: Testing Database Schema
[EMAIL PROTECTED] (Tony Bowden) writes: [...] lots of SQL to a common format. Both seem much too cumbersome, however. Anyone have any brighter ideas? Don't use a temporary database, just a temporary table. Surely that's more work? That depends on how you open your databases and how much trouble it is to use another database compared to munging the table name. :-) - ask -- ask bjoern hansen, http://www.askbjoernhansen.com/ !try; do();
Re: Testing Database Schema
[EMAIL PROTECTED] (Tony Bowden) writes: [...] 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? One variation that might work (and if it does, it'd also work on other databases) would be to use DB::Introspector. http://search.cpan.org/dist/DB-Introspector/ - ask -- ask bjoern hansen, http://www.askbjoernhansen.com/ !try; do();
Re: Testing Database Schema
On Sun, Jul 18, 2004 at 04:49:24PM +0200, James Mastros wrote: 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. We considered that, but, amongst other reasons, I'm not convinced enough that MySQL can be relied on to return exactly the same string for every server version of MySQL. 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. That sounds like *way* too much work! (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.) Yeah, I know that, and use it in several places, but it doesn't really give enough detail for what we're doing here. Tony
Re: Testing Database Schema
On Mon, Jul 19, 2004 at 05:00:05AM -0700, Ask Bjoern Hansen wrote: [EMAIL PROTECTED] (Tony Bowden) writes: [...] 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? One variation that might work (and if it does, it'd also work on other databases) would be to use DB::Introspector. http://search.cpan.org/dist/DB-Introspector/ There's also http://search.cpan.org/dist/SQL-Translator/ and http://search.cpan.org/dist/DBIx-DBSchema/ Tim.
Re: Testing Database Schema
On Sun, Jul 18, 2004 at 10:50:59AM +0100, Tony Bowden wrote: 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? Don't use a temporary database, just a temporary table. -- Michael G Schwern[EMAIL PROTECTED] http://www.pobox.com/~schwern/ Abandon failing tactics.
Re: Testing Database Schema
On Mon, Jul 19, 2004 at 03:22:05AM -0400, Michael G Schwern wrote: 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? Don't use a temporary database, just a temporary table. Surely that's more work? Tony
Re: Testing Database Schema
On Mon, Jul 19, 2004 at 10:19:47AM +0100, Tony Bowden wrote:
On Mon, Jul 19, 2004 at 03:22:05AM -0400, Michael G Schwern wrote:
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?
Don't use a temporary database, just a temporary table.
Surely that's more work?
Doesn't really matter how much code it is as long as you throw it into
a function. Its certainly less permissions to worry about (don't need
perms to create a whole new database).
sub _ck_schema {
my $schema = shift;
(my $temp_schema = $schema) =~
s/CREATE TABLE (\w+)/CREATE TEMPORARY TABLE _chk_$1/;
my $table = $1;
my $temp_table = _chk_$table;
$dbh-do($temp_schema);
my $temp_layout = $dbh-selectrow_array(SHOW CREATE TABLE $temp_table);
my $orig_layout = $dbh-selectrow_array('SHOW CREATE TABLE $table');
$orig_layout =~
s/CREATE TABLE $table/CREATE TEMPORARY TABLE $temp_table/;
return is( $temp_layout, $orig_layout );
}
Something like that. You don't drop the temp table at the end because
MySQL doesn't make a distinction, permissions-wise, between dropping a
temp table and a real table until 4.1. Closing the connection will clean
up the table.
--
Michael G Schwern[EMAIL PROTECTED] http://www.pobox.com/~schwern/
Any sufficiently encapsulated hack is no longer a hack.
Testing Database Schema
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
