Leverage existing DB Migration tools for UpdateSchema

Jan 16, 2009 at 10:54 PM
Hello Mike,

I look at updating XPO schema as having two parts: the part that knows about XPO attributes, etc; and the part that knows how to make changes to the database. The second part is often called database migration and there is quite a bit of work already available. Ben Scheirman has a nice roundup of available tools in ".NET Database Migration Tool Roundup".

You might consider leveraging one of those to make the actual changes.  That would let you/us focus on enhancing the first part, which is figuring out what needs to change.
Jan 17, 2009 at 9:32 PM

First, I have been thinking about your comment since I first saw it yesterday, considering how it would fit into my model. That said, thank you very much for the link. I had not seen these tools but they look extremely interesting and I am still sorting through the possibilities.

I'll get back to you as soon as I've worked it out.


Jan 24, 2009 at 5:34 PM

I have thought about the data migration tools and I don’t think that they fit into the structure that I was attempting to create.

First, these tools would require another set of objects that would have to be maintained. These objects would be separate from the XPBaseObject descendents that represent the data layer in the application. My idea was to embed the instructions right into the XPBaseObject descendents enhancing readability and maintainability. The object and the information about changes is kept in one class.

Second, for my work, I am using Microsoft SQL Server, Sybase SQL Anywhere (ASA), VistaDB, and Microsoft Access. Most of the tools handled SQL Server but none of them handle VistaDB. While I didn’t investigate how difficult it would be to create a provider, I would assume that the provider would require all types of updates; I was concentrating on just a few small important updates.

Third, XPO does a very good job of creating tables, adding columns, indexes and relationships. If you allow XPO to add these elements, then the migration tools would update only those things that XPO could not provide. You would also have to implement the “down” functionality without the “up” functionality for those XPO added elements. That seems very awkward to me.

In my opinion, using standard database migration tools with XPO would be problematic. On my last project we didn’t use XPO. I would have loved to take advantage of one of these database migration tools on that project.

With all of that said, XPO.UpdateSchema appears to be the first part of DevExpress Contrib that will be deprecated. I’m happy to see that DevExpress will be providing this functionality this year. See XPO What's Coming in 2009 the section labeled “Provider Independent Data Definition Language”.

Thanks again for introducing me to these tools,

Jan 27, 2009 at 11:19 AM
No problem. After reading your reasoning, I'd like to withdraw my suggestion. They are not a good match for your goals.