I am looking at seeing if implementing migrations like ActiveRecord\RoR has is possible with NHibernate. If you haven't heard of these it is a way to upgrade and downgrade your database schema and data.

The best part about Migrations is that they are database agnostic because they are implemented onto of the framework instead of running a externally generated SQL script. The second big thing is that they are checked into your SCM system with your code. I only read about them just before and I really like the idea, when you look at what they can do you will really see they are the best way to keep your database schema and data up to date.

I have had a look at NHibernate and there are some things in NHibernate.Mapping like Table and Column that could be used. However, there is one major problem I see straight away with trying to implement this.

ActiveRecord Migrations support upgrading a database that is 1, 2 or 10 versions behind the current version. Each migration is executed in order from the current+1 to the newest (which makes sense ;)). In each migration you can add columns but you can also set the value of the field using the domain model. Since .NET is a static language you would need each copy of your domain model DLL (only where the schema changes) to perform each migration. I'm not sure how else you could do this in a nice way. The other problem would be you would not be able to compile your project if you removed a field from an object because .NET would say that it doesn't exist so I think that you would have to not allow the use of the domain model objects. This would only happen on older migrations which you are going to want to keep in your solution.

Here is the type of thing that I would expect you would do in .NET. To make it easier to read I wanted to keep syntax highlighting and I can't upload an image so check it out here:

http://www.jonorossi.com/images/externa ... ernate.png

I didn't show the code above that because they are just empty classes and methods. The main focus is on how you would write your migrations

If you have any comments about this idea then I'd like to here them. If you want to help me attempt to build this then I'd also like to hear from you.

External links about ActiveRecord Migration:
Screencast - Evolving your database schema without a sweat ( http://www.rubyonrails.org/screencasts )
API Doco ( http://rubyonrails.org/api/classes/Acti ... ation.html )

Regards, Jono

Edit: One major thing I just noticed that you loose with not using the domain model is that you don't have any validation or any way to perform business logic (eg. calculate a total). However, these things could still be coded into or called from the migration class.

Castle's Contrib already has migrations.
You can check it out here:
http://svn.castleproject.org/svn/castle ... Migration/

Thanks for that. Have you ever used it?

I found the DLL with the migration stuff in it but I can't find the source:
http://svn.castleproject.org/svn/castle ... grator.dll

By looking through the assembly I can see it isn't implemented on NHibernate because it has its own providers for MySQL, PostgreSQL and SQL Server. I assume that the NHibernate classes do not provided enough features to support migrations.

The version that's on SVN is 0.1.0.38948 which means that this is probably far from being finished. Do you know what state it is at because I was reading their site yesterday and didn't see a mention of the migrator.

I can see all the log messages it will print are in another language other than English so that would be a bit of a pain.

The TransformationProvider provides these methods which I think miss the point of making it database agnostic. Because for example dates are handled differently in different databases.

protected int ExecuteNonQuery(string sql);
protected IDataReader ExecuteQuery(string sql);
protected object ExecuteScalar(string sql);

I proposed other methods; example to update a column's value. You obviously would need to to build quite a few of these to provide you with all the things you could do in SQL.

I'm still considering building my own because I'd like to make it use the NHibernate configuration and use NHibernate's core. Do you know what licence that code would be under? Because some of it may be useful to reuse.

What is your personal opinion of Castle Project's Migrator? Do you think I am silly making one when this exists?

Thanks for your time, Jono

Edit: I found out the license and info about maintainance:
- "The author donates his/her work and releases it under a license compatible with the Apache Software License. (In other words, no GPL or LGPL license, please.)"
- "It's not officialy supported and/or maintained by the Castle team."

NHibernate features don't allow for supporting migrations - no support for "ALTER" statements. It never will.

The Migrations dll is from a SourceForge project: http://nproject.svn.sourceforge.net/viewvc/nproject/. IMHO it would be better to start with the one project and only make a duplicate if you feel you must.

What is NHibernate.Mapping.Table.SqlAlterStrings() for? I don't know the NHibernate internals very well so I'm not sure what it is for.

I found that SF project but wasn't sure if that was where the development repo was located. Thanks for confirming that.

Looking at it more I can see that it would be hard to implement using NHibernate. Can you see any way that I could implement select, update, insert and delete queries using NHibernate in a DB agnostic way or would I have to build that into the migration providers so it can be DB agnostic?

Thanks for your time.

I'm not sure what you're asking. NHibernate handles select, insert, update, and delete already, you don't have to build that. Migrations are for creating tables and indexes, altering tables, and dropping tables - nothing more. Migrations manage your schema, NHibernate manages your data.

I am aware NHibernate handles select, insert, update and delete but it does it via the mappings. Is there any way to call a method of NHibernate to create a SQL query without using the mappings? For example by giving it the table name, a 'where' clause (using DB columns names) and a 'set' clause to be able to be able to perform an update?

Basically, I'd like to use the NHibernate dialects to perform CRUD operations but using DB table, column, etc names instead of object mapped names.

I was expecting a reply so maybe you didn't get the email or you are busy. I'll try to explain it from why I want to do this.

The Castle Project's Migrations provides these 3 methods as I said earlier in this topic. The problem with these is you are back to square one with being able to run them on every database. I am looking for a way to use what is already built in NHibernate to be able to perform certain operations like select and update without writing SQL or using the domain model. Do you think this is possible with what is already in NHibernate? Or would I need to build a separate set of classes so I can for example create SELECT statements with WHERE clauses.

protected int ExecuteNonQuery(string sql);
protected IDataReader ExecuteQuery(string sql);
protected object ExecuteScalar(string sql);

Thanks for your time
Regards, Jono

If I understand you correctly (which I doubt), what you want to do is not possible with NHibernate. NHibernate can't generate SQL without mapping your objects to the database schema.

Yes I think you do understand me.

I can see one method in NHibernate.Dialect.Dialect that could be used. One is GetDropTableString(string tableName) which would create a the query to drop a table. But just one method seems a little pointless.

I was also looking at the Expressions; the SimpleExpression (I know it is abstract) takes a propertyName which you could pass in a columnName but you wouldn't be able to use the ToSqlString so it would become really messy.

That means I'll have to build my own classes to generate SQL which is a bit of work because you have to implement at least datatypes, alter table, FindAll, UpdateAttribute, Insert and Delete. Expressions would be optional because migrations don't need to be really fast so you could just pull back all the rows.

I'd be really interested in finding out how RoR does it when you remove an object or even a single property from your domain model and need to execute a migration which is a little old or even build it (RoR isn't compiled so it wouldn't have that problem).

Jono

Edit: It looks like I could use some code from NHibernate.SqlCommand.QuerySelect to build a select with more power. But I doubt I would ever need to order rows when doing a migration; I could easily not do the rest too because migrations can be more simple.

Bump. I am nearly finished bugging everyone ;), just wanted to see what you thought about my last post.

From the video I watched the other day it looks like RoR's schema is dynamic. So when you change the schema using migrations the schema.rb file updates to reflect the updated schema. It would be strange if you tried to let the user use the system while you had coded against your newest schema and you current schema file is old.

Sorry to keep bugging you k-dub, maybe you didn't get the email or you are away.

NHibernate doesn't do migrations. There are already tools out there that will allow you to do migrations in .NET. They even operate like RoR, which it seems to be the behavior you are looking for. I don't understand what else you need.

I know NHibernate doesn't support migrations.

The Castle Migrations project has methods like add column, add table, etc; but does not have any ability to modify the data. It just provides these 3 methods which defeat the point of using migrations instead of a separate SQL script for each database:

protected int ExecuteNonQuery(string sql);
protected IDataReader ExecuteQuery(string sql);
protected object ExecuteScalar(string sql);


I said that before. I basically wanted to know if there is anything in NHibernate that I could use so I wouldn't have to recode it. I think the only thing that I might be able to use is the datatypes but I'm not even sure if I can use that and I think the Castle Migration project has some code for datatypes already so I might not need that.

Sorry to keep talking about trying to use NHibernate for this but it would save me writing heaps of code.

I really appreciate your time, Jono

Hi Jono,

Did you ever get any further with this? I think this is a really good idea for a project, and I would definitely be interested in it. I have my own hand rolled ROR inspired database migration system in NAnt with some custom tasks, which works well but only works on sql. I have a project coming up that will need to support other databases as well, so this would be very handy.

I think if there was a DB agnostic way to modify tables, and a way to reference your domain model you could use HQL to do data migration, or any DAL classes you have in your persistence layer.