aspose file tools*
The moose likes OO, Patterns, UML and Refactoring and the fly likes Refactoring Databases: Schema Evolution and Maintaining Data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "Refactoring Databases: Schema Evolution and Maintaining Data" Watch "Refactoring Databases: Schema Evolution and Maintaining Data" New topic
Author

Refactoring Databases: Schema Evolution and Maintaining Data

Darya Akbari
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
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 ]

SCJP, SCJD, SCWCD, SCBCD
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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...


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2906
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.


"Don't succumb to the false authority of a tool or model. There is no substitute for thinking."
Andy Hunt, Pragmatic Thinking & Learning: Refactor Your Wetware p.41
Mike Farnham
Ranch Hand

Joined: Sep 25, 2001
Posts: 76
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
Bartender

Joined: Aug 19, 2005
Posts: 2906
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
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
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
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
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
Bartender

Joined: Aug 19, 2005
Posts: 2906
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
author
Ranch Hand

Joined: Dec 12, 2003
Posts: 608
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


<a href="http://www-306.ibm.com/software/rational/bios/ambler.html" target="_blank" rel="nofollow">Scott W. Ambler</a><br />Practice Leader Agile Development, IBM Rational<br /> <br />Now available: <a href="http://www.ambysoft.com/books/refactoringDatabases.html" target="_blank" rel="nofollow">Refactoring Databases: Evolutionary Database Design</a>
Darya Akbari
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
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
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
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
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
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
Ranch Hand

Joined: Aug 21, 2004
Posts: 1855
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Refactoring Databases: Schema Evolution and Maintaining Data
 
Similar Threads
Refactoring Databases: Do you present frameworks for schema evolution in the book?
HFDP: Where to put Network Connection in MVC?
B&S: Handling different data files (format, schema)?
book expectations... (refactoring databases)
Refactoring databases: top-down vs bottom-up