Database Version Control help needed.
Development and Programming
Hello everyone, i wonder how you all deal with this challenge and if someone has a smart solution.
Here is the case:
You got site A live. Then the customer wants some massive changes, so you make a copy of Site A and its database in a test environment.
After doing all the modifications (new add-ons, new custom stuff, etc) you want to update site A again.
The scripts are no problem, they are in GIT and we can merge them. A bigger problem is the database, and especially the metadata which is stored in the records itself.
How can you manage this in a way the client can keep his site live and that upgrading to the new version doesn’t give to much headaches.
Some solutions we thought of: - A diff tool like Navicat (problem with that is that it drops and inserts a table, when you only renamed it). - A tool which records all queries on the test database in a file (or table), so you can run those queries as soon as you update the live environment. We haven’t found a good tool yet. Liquibase (http://www.liquibase.org/) is a step in the right direction, only the script with queries should populate itself.
Suggestions are more then welcome. 😊
Thanks for any suggestion in the right direction.
the elephant in the room. The person that solves this issue will do well. It’s not unique to EE but the same for all relational dbs.
I’ve found http://schemasync.org/ there was another tool that did something else but was never able to get at their beta to test it.
Hi,
I recommend you to look on dbMaestro TeamWork (http://www.dbmaestro.com).
dbMaestro TeamWork is a DCM solution. DCM - Database Change Management, which allow you to control table structure, database code and content of lookup tables.
You start with document all your changes in the database in your development environment using database version control. Then you generate the deploy script based on analysis of the history and the target environment (the customer site). The deploy engine uses 3-way analysis which tell you the source of the change.
Let’s review your requirements: 1. Change the database structure, code and lookup tables as part of the next release development. 2. Chaneg the database structure, code and lookup tables as customer customization.
Well, this is exactly what dbMaestro TeamWork does: 1. Any change in the development is document using the SCM methods (Check-Out / Check-In). 2. When you customize your database structure, you need to create a branch of the database (copy the database) - in order to develop / test / etc… dbMaestro TeamWork support database branching and the continue of the development. 3. Deploy the main release to the customer while not revert any customized objects. dbMaestro TeamWork Deployment Engine uses 3-way analysis, which compares the source (development), target (customer site) and baseline (a previously archived version of the database) and recommends what to do with the changes. for example:
Source Target Action vs. vs. Baseline Baseline = = no action <> = Promote the change = <> Ignore the change (this happens for customized code) <> <> Merge the conflict code
- Control Who can do What, When, Where and Why
- Have reports
- Disclosure - I work in the dbMaestro
isn’t this only for Oracle DB?
It is only for Oracle DB’s. But if EE would only change database structures this would not have been a problem. The difficulty is that EE stores metadata within a table that can change all the time.
I have found a few tools that can see changes to database structures but non of them can see the changes made in tables.
Is there a change in a next release of EE this problem will be solved?
Thanks for your replies,
@Steven: Does schemasync als sync the metadata which is stored as records in the EE db? @UriMargalit: Thanks, i’m gonna check it out. COuld you give me an indication about pricing for 10 developers?