CODEWITHSUNDEEP
javasqloracle-databaseoracle11g
TonySalimi
TonySalimi

Reputation: 8427

How to generate the SQL script for migration from an old DB to a new DB?

We are developing a web-based solution for a customer using java and oracle 11g. The project is installed for the customer, but most of its features are under development. Every month, we release a new version that should be installed at customer site. The problem is that in each release, there are lots of changes in the DB (tables, views, SPs, triggers, etc.). I cannot get the dump from the development line and take that to the customer site.

The question is how can I generate a SQL script that could migrate the customer DB's schema to its new version (development version)? Is there any automated tool for generating this scripts? Is there any open source code in java which could do that? Any best practices for this issue?

Edit

According to the answers, it seems that the best practice is to forbid developers from direct change of DB Scheme and they should write and commit a change script for each DB update. But using this method, after a while, there would exist lots of change scripts, each of them hold an specific update in DB. How can I then manage these bulk of scripts?

Upvotes: 1

Views: 1246

Answers (2)

HLGEM
HLGEM

Reputation: 96600

Database changes should only be made through scripts that you put in source control. If you do that then you do not have lots of problems knowing what to move to Qa and then to prod. Plus the script has been tested unlike creating a script at the end and running it for the first time on prod. Further, if some changes are for a different project, they won't be accidentally put on prod when they aren't ready because they will be in a different branch of the source control. SQL Code is code, it should be treated exactly like all other code in terms of source control.

Upvotes: 2

Peter Bratton
Peter Bratton

Reputation: 6408

The short answer that is that given the state of affairs you describe, you're in for a good bit of work to come up with a point-to-point migration script between your prior releases and your next release.

Database diff tools do exist, but there's a general flaw in the concept best described by this blog post:

http://blog.liquibase.org/2007/06/the-problem-with-database-diffs.html

TL;DR: A diff tool cannot determine the context of a specific change. For example, if, over the course of your development, you corrected a spelling error in a column name, you would want to use a RENAME COLUMN command on an existing database, in order to preserve any data the table already held. A database diff tool could only see an old column that doesn't exist in the new schema, and likewise a new column that didn't exist in the old schema. So the tool would drop the old column, and add the new one, but as a result, all the data originally in that column is now gone.

I would recommend proactively tracking database changes as you go as a best practice. The newer web frameworks such as Django and Ruby on Rails support migration tracking out-of-the-box, but open source solutions such as Liquibase (that link above) exist for the Java world as well. We use Liquibase at my current company, and have had great success minimizing these migration costs so far. We made the switch after slogging through just such a migration you now face.

Good luck!

Upvotes: 5

Related Questions