Posted At : July 19, 2007 12:30 AM
4 Comments
Despite all these tools, one area that can be difficult to manage is changes to database schema's as applications evolve.
For example - as there are seperate dev, staging and production db's you need to be meticulous in scripting all changes and remembering to apply all changes across all db's at appropriate times.
I've been reading about how Ruby on Rails manages db changes with "Migrations" which seem like a pretty good solution but they have significant limits and are not perfect solution.
In my ongoing quest to make my life easier I wanted a tool which could tell me the difference between two databases - eg dev and staging db's - and save me having to check each table and row.
Initially I used mysqldump and a diff tool to see the differences - which worked a treat but was a lot of repetitive steps to get the report I needed. Perfect for some automation. After a bit of searching I found a CFMX diff cfc by Rick Osborne which was allowed me to very simply create a super simple Datasource compare tool as shown in the screenshot.
I can now quickly compare the different db's by setting up datasources on my machine for each one, and ensure that they are always in a consistent state.
Download it, give it a spin and let me know if it's any use. Note that it only works in MySQL 5 at the moment, but would likely be simple to port to other dbs.
Also very happy to hear if anyone has better solutions or processes for keeping DB's in sync.
Cheers, Mark
4 Comments
I have a tool for CRUD work that can also be used to easily keep database structure in synch. It is more automated, but will only add columns/tables. It won't catch other database differences like yours will.
http://steve.coldfusionjournal.com/schema_synchron...
Barney Boisvert has a tool that is perhaps less automated than yours but will allow you to set a version-specific database schema.
http://www.barneyb.com/barneyblog/2007/07/13/schem...
I don't know that any solution is necessarily better as they all take a slightly different approach to the same general problem.
Hi Steve,
Thanks for the links - I've had a quick look through your DataMgr and it looks pretty cool. I'll have to spend some time to go through it. You're absolutely right - it's similar but different approaches to the same problem.
Cheers,
Mark
so far I have been using Quest's SQL comparison suite which is free but only for Microsoft SQL server: http://www.quest.com/Comparison-Suite-for-SQL-Serv...
Since I primarily use MS SQL it works pretty well. It might be nice to have a tool that will work through coldfusion without installing a separate application though.
Not free, but for anyone using just SQL Server, I have heard great reviews for SQL Compare by Red Gate.
http://www.red-gate.com/products/SQL_Compare/index...