RH should have the ability to seamlessly and automatically apply changes to its internal tables without requiring the user to have to understand what is going on.

For instance, in adding a column to a RoundhousE table should not make us need to work harder to get there or even need to tell the user about a breaking change. It should automatically upgrade the table on its next run. This is not extremely difficult and can be achieved.

It is not completely easy to do since the schemas are different between different types of databases. There is a tool out there that does this with ease: NHibernate. It has a schema generation tool. We are going to need to start out looking at the database type and making decisions about what those schemas should look like and loading those. Then apply changes to the database using the tool (which adds and/or converts our tables).

This will require heavy testing as well.

This does mean that we will be converting RH to using more of NHibernate now that we can. This also means that this change will deprecate SMO.

Maybe RH should be eating it's own dog-food.

This means that RH will run twice, the first time for the scripts of it's own and a second time for the scripts provided by the user. It doesn't matter what system RH is using to do it's own update (SMO, ADO.NET, NHibernate or diff), but maybe the same as the users prefers for it's own scripts.

On the other hand, I don't know if you have used the NHibernate Schema generation, but the reason we use RH is that NH wasn't sufficient for doing migrations.

I agree that upgrades to RH should be seamless, and that deprecating SMO is a good thing!

I like the idea to perform changes to the RH tables with RH itself, but maybe not as a seperate run. Maybe the scripts could be embedded resource files that get executed as a first step in RH?

There is another enhancement out there to make RH able to look at sql in DLLs, which will allow us to put RH migration scripts in the RH dll itself and eat our own dog food. I also like the idea of automatic conversion.

Converting to NHibernate will also allow us to have one model for all database types. Each database type will contain the mapping for how to save it to that particular database. It is a much cleaner approach.

The SQLScripts files will become more of the additional type requirements, like the batch splitter and possibly the sequences for oracle.

Issue 38 has been merged into this issue.

This is complete. As of revision 268 merging in.