Refactoring Databases: Schema Evolution and Maintaining Data

[Darya Akbari]

Hi Scott and Pramod,

schema evolution while maintaining the data in a schema. In a productive and also in a development environment I always end up in a mess when it comes to guarantee the data in one schema to be the same after the schema evolves, due to domain model changes.

I think an Object Relational Mapping Framework (ORM) like Hibernate does a perfect job here and keep the schema and the domain model in sync. Tests are another essential part in that.

However for each new field that becomes added to my domain model, a new column will be added to my schema (fortunately the ORM is doing that).

Nevertheless I must take care that these new columns are not empty.

Hence I always have to run SQL to be sure that these new columns are filled with some default values, so that no NULL POINTER EXCEPTION or the like will be thrown .

Do you describe this topic somewhere in your book or what are your thoughts?

Regards,
Darya

[ July 25, 2006: Message edited by: Ilja Preuss ]
[ July 27, 2006: Message edited by: Darya Akbari ]

[Ilja Preuss]

Although it is not a refactoring, the book has a short (three page) description of an "Introduce New Column" transformation, which also shortly discusses the potential problems and possible solutions regarding data migration. Don't expect miracles, though...

[Peer Reynders]

Originally posted by Darya Akbari:

Hence I always have to run SQL to be sure that these new columns are filled with some default values, so that no NULL POINTER EXCEPTION or the like will be thrown

Why not adopt the practice of always specifying a default value for the column as part of the schema for any added column. If necessary make it point to a record that plays the same role as a NullObject.

[Mike Farnham]

Why not adopt the practice of always specifying a default value for the column

Just out of curiosity what value do you use for date type columns?

[Peer Reynders]

Originally posted by Mike Farnham:
Just out of curiosity what value do you use for date type columns?

You could specify a fixed date value. If that isn't appropriate have an insert trigger supply a more appropriate default date value when it detects a null - in that case you will still need to update the existing data to get rid of the null values that have been created when the column was added.

[Darya Akbari]

Hi,

after reading your responses and other threads concerning Scott's and Pramod's book I wonder if that book is of any use for me .

The real beast is data migration from one schema to the other. Especially when you don't have an SQL specialist at hand, it's a horror to always adapt your migration SQL scripts after any so little change to the schema.

The schema change itself is not the big problem as long as one let an ORM framework like Hibernate's SchemaUpdate tool do the job .

So the real challenge is the creation of migration scripts .

If anyone knows about a good book concerning best practice in data migration , please let me know

However as far as I know there is still no such book on the market

Regards,
Darya

[Ilja Preuss]

Originally posted by Darya Akbari:

The schema change itself is not the big problem as long as one let an ORM framework like Hibernate's SchemaUpdate tool do the job .

So the real challenge is the creation of migration scripts .

If anyone knows about a good book concerning best practice in data migration , please let me know

However as far as I know there is still no such book on the market

I'm confused - how did you get the impression that the book doesn't discuss these issues?

[Darya Akbari]

Hi Ilja,

wasn't it you yourself who said, that I should not expect any miracles from the book concerning data migration

On the other hand Scott himself said in another thread that the book is not about migration .

Regards,
Darya

[Peer Reynders]

Originally posted by Darya Akbari:

On the other hand Scott himself said in another thread that the book is not about migration

The exact quote is

We don't cover migrating between database vendors in the book.

Where appropriate each refactoring item has a "Data Migration Mechanics" section.

However I'm still perplexed why so many people in the "programming business" seem bent on avoiding a decent mastery of SQL (and the relevant Stored Procedure variant)? RDBMSs aren't going away.
RMH: First Rule for Planning for the Future: Don�t
And exploration of various paradigms is supposed to improve your overall mastery of your craft.
[ July 25, 2006: Message edited by: Peer Reynders ]

[Scott Ambler]

Data migration really isn't all that hard, but you need to know either how to write SQL code, programming code, and/or use a data migration tool.

The book does in fact provide source code for the data migration aspects of each refactoring.

In Agile Database Techniques I cover the legacy data issues that you should expect to deal with when working with legacy database schemas. You may run into many of these during migration efforts.

- Scott

[Darya Akbari]

Hm :roll: , I'm still in the position that after reading you links that I would not spend my money for it.

All I see is good methodology talk about the issue, but no examples at least not in the book Scott advised and after searching the book at Amazon.com for the word migration. That gave only 10 pages as result.

Regards,
Darya

[Ilja Preuss]

Originally posted by Darya Akbari:

wasn't it you yourself who said, that I should not expect any miracles from the book concerning data migration

Well yes - but just because you shouldn't expect any miracles from *any* book concerning data migration. :roll:

But I guess I don't fully understand what exact problems you have with having to run those SQL scripts...

[Darya Akbari]

Hi,

I think most of us developers do NOT like to get too deeply involved into writing SQL scripts. Maybe because writing good SQL is a science for itself which is often very much underestimated.

So, think of an iterative software process, where you have any one or two weeks adapt your SQL scripts and keep track of all your domain model changes by maintaining the legacy data . How can that be an easy stuff . And whether easy or not, it's tedious isn't it

I would get an SQL specialist involved in that process, but reality looks different. Most often it's you yourself who has to handle the schema evolution and data migration, simply for the reason that no one else can do the job.

And for this simple reason I appreciate a best practice guide with lot of examples on how to challenge the whole thing. Something that is not discussing anymore but presenting broadly accepted best practice.

Many other technologies, frameworks or the like went through this process like JUnit, Maven, POJOs, J2EE Patterns, GOF Patterns, etc.

Data migration however is still far away from that.

Regards,
Darya

[Ilja Preuss]

Originally posted by Darya Akbari:
So, think of an iterative software process, where you have any one or two weeks adapt your SQL scripts and keep track of all your domain model changes by maintaining the legacy data . How can that be an easy stuff . And whether easy or not, it's tedious isn't it

It's still better than doing it less often, and therefore having to do bigger (=more risky) changes with less experience...

[Darya Akbari]

Hi Scott and Pramod,

you mentioned data migration tool. Is there one you can recommend

I know about TOAD which gives you that nice feature, but only for Oracle .

Regards,
Darya