C# library for database diff(ing)

Whilst sorting out migration scripts for incremental database updates, I discovered that none of the off-the-shelf database diff tools (that I have researched) will produce the migration scripts needed by our development proce开发者_Go百科ss.

In line with pretty much every other development project, we need the ability to bring a production schema up to date with the latest development schema without losing production data.

The approach, so far, is to drop and recreate any object that is not a table (taking care to update sequence nextvals to an appropriate value) and to then deal with table differences by dropping columns that no longer exist, adding columns that have been created, renaming columns whose type or length have changed, adding the new column definitions and then populating changed columns with data using an appropriate type transformation.

The current approach is completely automated but it does not take into account columns that have simply been renamed. Diffing the table definitions (DBMS_METADATA_DIFF) does not give any machine-understandable clues that a column-drop followed by a column-add is actually a column-rename. To a human, a column-rename might be obvious, especially if the rename is simply a grammatical correction or choice of more appropriate name.

I am attempting to resolve this by writing my own database diff script generator and adding logic for spotting column renames (with support for human intervention, confirmation, etc.). Now, I'm quite sure that I must be reinventing another wheel.

Are there any good open source libraries that I should take a look at? I prefer C# but as it's Oracle I'm working with, there is probably some good Java implementations around. Any pointers appreciated.

We use the RedGate SQL Compare and SQL Data Compare APIs to generate differential schema and data update scripts between our bootstrap database and our target (client) databases at install time. It works very well. There's a comparable Oracle product offered by RedGate (haven't used it, so your mileage may vary)

http://www.red-gate.com/products/oracle-development/schema-compare-for-oracle/.

PS, I know it's not open source, but I think you'll be hard pressed to find as easy to use library as this.