Re: Script creation: Manual vs. Automated
- Hi Milen,
I took a quick look at both tools and I felt that although both are oriented towards an incremental approach, they still represent the two main methods being debated here.
DbMaintain uses the scripts created by the developers themselves.
LiquiBase depends on an alternate representation (proprietary XML) from which a script can be generated. The XML itself can also be generated through a "diff" option but that is not recommended.
I found the fact that LiquiBase does not provide a proper tool to create the XML change sets as a major drawback; I'm really not fond of writing XML. It seems they're working on it, though.
DbMaintain seems to be a bit primitive and as you mentioned has some usability issues. In particular, I found the fact that it can not recover from errors on its own to be a limitation. Also, I haven't seen any mention in the documentation of how rollback scripts are utilized.
I guess the perfect tool is not there yet.
--- In agileDatabases@yahoogroups.com, "milenvk" <milenvk@...> wrote:
> Hi tnabil76,
> The tool that I am using for Oracle is dbMaintain (http://www.dbmaintain.org). It's relatively new on the incremental tools stage, but it offers a simple and stable approach to the incremental scripts scenario. It has its own usability problems, but it works. I am also developing an open source tool for evolutionary database development via change scripts, that will support a variety of databases (Oracle included), but it's in its infancy and I would not recommend it at this point. Another tool that looks robust in terms of features and should work well with Oracle is liquibase (http://www.liquibase.org/), however I have never used it.
> Milen Kovachev
> --- In agileDatabases@yahoogroups.com, "tnabil76" <tarekmnabil@> wrote:
> > Dear Milen,
> > Could you please name some of your favorite tools which offer those capabilties on Oracle databases?
> > --- In agileDatabases@yahoogroups.com, "milenvk" <milenvk@> wrote:
> > >
> > > And for these two points:
> > >
> > > > Another issue is how to version control the incremental scripts. What we've been doing is create sub-folders for every version which contains the incremental scripts for that particular version, e.g. from 0.10.1.1 to 0.10.1.2. I see some issues with this approach:
> > > > 1. It seems to be in direct contrast with most version control practices where versions should be expressed as tags, not subfolders of the branch.
> > >
> > > The reason for this is that you are not versioning a statically changed entity like source code, text files, etc. You are rather versioning just certain axis of change of a volatile environment, like a running database, complete with data, concurrent users, etc. With incremental scripts you are simply doing what version control systems do internally, i.e. keeping just incremental changes to a versioned entity.
> > > Consider the alternative: keeping the entire database schema under source control and recreating the database from scratch each time. That approach has very limited use as it only works for initial development and cannot be applied on a production database.
> > >
> > > > 2. Another problem is at deployment time, you have to manually come up with a list of those scripts, which is error prone.
> > >
> > > Practically all the tools out there keep information in the database indicating what incremental scripts have been already run on it, so at deployment time, only the scripts needed to roll to the desired version get run. Some tools also offer generating the SQL scripts to apply to a given version of a database to roll it to another version without actually executing those scripts. In fact the incremental method is less error prone than the alternatives, as it automates the upgrade and avoids error prone ad hoc updates.
> > >
> > > Regards,
> > > Milen Kovachev
> > >
first of all, this is a interesting thread. I'm doing my bachelor-thesis for such a scenario.
I'm actually trying to convince the core dev / dba team, that the incremental versioning way has more benefits than drawbacks.
The main problem in our discussions is, like you said in your first post, the execution time of the migration, if there are many not optimized changes. If you add 1 column to the same table in 3 different steps and then remove 1 column in another step, the "diff"-way is much faster, because the resulting changescript will do ONE add column 1 and 2 in one step, instead of doing 3 or 4 steps.
So I think, in the world of schemas, the "diff"-way seems to be better for our BIG tables.
But, ..., a migration is not only to change some schema-informations. You want to include complex convertfunctions, like converting the content of 1 column in the OLD schema to 2 columns in the NEW one. In the diff"-world you have to do custom converts for each patch. It is a difference between the migration from 1.1 to 1.3 and 1.2 to 1.3. Theres no 100% repeatablity.
At the moment I'm thinking about hybrid-ways, but I don't think there is one. Are there ways to optimize one of this two ways with the ideas of the other one?
I have an idea for that. First I thought, it is too time-consuming to analyse the "changelog"-path for optimizations. But actually I think it could be possible. Maybe I could order the changes into a graph-like structure, where i can look at the changed objects from top to down. There I will find changes, which are potentially optimizable. It isn't too important to eliminate all unoptimized changetype. It would be a great benefit, if it takes care about the column problem and some others.
What do you think?
Your feedback is appreciated.
Thank you all for this great thread with many interessting standpoints to those ways of migration.
--- In agileDatabases@yahoogroups.com, "tnabil76" <tarekmnabil@...> wrote:
> Thank you all for your responses, I didn't really expect that much feedback.
> I must say I'm overwhelmed by the consensus on the incremental approach, which I had already completely dismissed. I have to clarify that we are currently using the incremental approach and that my research of alternate approaches is driven by what I felt were shortcomings of that approach.
> The thing is I believe there are cases which can not be dealt with in this fashion. For example, if there was a decision to drop a column and then this decision was canceled, there would be a script to drop the column and another one to restore it. If that is done incrementally, then how will one be able to restore the data in the restored column?
> I believe if I dig deep, I can come up with numerous other similar examples.
> Also, even if we assume that the incremental approach guarantees the correctness of the "roll forward" scripts, it doesn't do the same for the "roll back" scripts. If your rollback scripts are not perfect, then how can you revert back in case one of the changes you made caused problems?
> Another issue is how to version control the incremental scripts. What we've been doing is create sub-folders for every version which contains the incremental scripts for that particular version, e.g. from 0.10.1.1 to 0.10.1.2. I see some issues with this approach:
> 1. It seems to be in direct contrast with most version control practices where versions should be expressed as tags, not subfolders of the branch.
> 2. Another problem is at deployment time, you have to manually come up with a list of those scripts, which is error prone.
> I feel that this is going to be a long discussion thread, although hopefully, quite a useful one.
> Thank you again for your help.