Not clear how to and when to update schema

[pherrymason]

I've sent you an email but did not recieve any answer, so I write you here...

After configuring dbv for my database I export to disk the schema. Now, I need to do some changes to my database, so I create a revision file. This revision changes a table name:

table: user ---> renamed to users

After I appy this revision, main screen says table user does not exists in DB but in Disk, and lets me create again that table by "Push to database".

What's the required step here? Is table user going to appear in the list forever? Shouldn't new revisions update the schema automatically?

[victorstanciu]

Hello Raúl,

Sorry for the delay, I haven't replied to your email until now because I've actually been looking for a solution to your problem that wouldn't involve adding a lot of new functionality to dbv.php.

Shouldn't new revisions update the schema automatically?

They do, but they don't also update your schema files. Your user table still exists on the disk, but now your database only has a users table. You could remove the user.sql file from your schema directory and then export users to users.sql, but that would mean that when a new developer would build the database, he would start with the users table, and the revision that renames the non-existing user table would throw an error.

The solution (at least for now) would be to change your revision script so that it only renames user to users if user exists. Unfortunately, since MySQL doesn't provide a REPLACE IF EXISTS (like it does for DROP IF EXISTS), this needs a small trick. Try replacing your revision script with this:

SELECT COUNT(*)
INTO @exists
FROM information_schema.tables 
WHERE table_schema = '[YOUR DATABASE NAME]'
AND table_name = 'user';

SET @query = IF (@exists > 0, 'RENAME TABLE user TO users', 'SELECT 1');

PREPARE statement FROM @query;

EXECUTE statement;

And then simply remove the user.sql file and export the new users.sql. When a new developer runs the revision script, because they don't actually have the user table, no error will be raised, while those that actually have it will have it renamed.

Please let me know if this works for you :)

[victorstanciu]

If anyone else is reading this, any suggestion on how to elegantly circumvent this problem is more than welcome!

[pherrymason]

It is ok for me, just wondering what is the "official" path to follow and if that scenario was expected. Thanks for your reply, I will follow this issue.