Ludicrous Database Idea of the Day: Branching, Tagging, and Diffs
migration changecontrol database sql
This morning I was thinking about database migration issues - maintaining update scripts, rolling back development databases etc. etc.
Whilst it's no major issue to keep a dump of a database around to restore to a known state and test your update scripts and application against, but I was thinking about how this could work at the database layer itself.
The crazy idea I had was to bring over features commonly found in version control systems - tagging, branching, diffing, imagine you could do the following:
CREATE BRANCH marks_dev_branch_1_5_4;
USE BRANCH marks_dev_branch_1_5_4;
ALTER TABLE users ADD COLUMN account_disabled TIMESTAMP;
The database in question now has a new branch with a modified schema that we can play with, if we decide to scrap these database changes, we've not commited them so we could issue:
ALTER BRANCH marks_dev_branch_1_5_4 REVERT CHANGES;
This would simply revert all schema and data changes that remain uncommited. If we wanted to keep them...
ALTER BRANCH marks_dev_branch_1_5_4 COMMIT CHANGES;
How about getting a diff?
SELECT BRANCH DIFF BETWEEEN HEAD AND marks_dev_branch_1_5_4;
> -- Differences from HEAD and marks_dev_branch_1_5_4
> ALTER TABLE users ADD COLUMN account_disabled TIMESTAMP;
SELECT BRANCH DIFF BETWEEEN marks_dev_branch_1_5_4 AND HEAD;
> -- Differences from marks_dev_branch_1_5_4 and HEAD
> ALTER TABLE users DROP COLUMN account_disabled;
Similar to version control systems, switching the order in the diff gives you different DDL queries. What if we no longer want the branch?
USE BRANCH HEAD;
DROP BRANCH marks_dev_branch_1_5_4;
Am I smoking crack? Or would something like this actually be usefull or plain fancifull overkill?
Comments (3)
the SQL2005 allows partial restores which might allow you to implement some crude branching. see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.aspYou're not on crack - it's definitely useful to have versioning for your db schemata.
On the other hand, there's nothing stopping people storing DDL .sql scripts in a versioning system now. If the functionality was within the db itself, or it could integrate with a version system, so much the better. Perhaps there's further need for it in general. I'm speaking from a web-application development point of view, where the db layer is usually simpler than the source code running in the web tier.
What would be cool from my point of view would be a way of creating database updates from db diffs. I'm talking about schema only. Once you have done a bit of work adding tables etc, you need to write a scipt to update db's to the new structure. It would be good to do this reliably.