Hello

I am currently building an ASP.NET web application using NHibernate. While developing I am using hbm2ddl to generate my schema and then have a pile of code that uses the objects to repopulate the database.

Once I release a beta of my app I am going to need some way to be able to upgrade the database of the installed instances. Because NHibernate supports many databases I would prefer not to create a SQL script to upgrade the database because each database would be different.

I have heard that Hibernate has SchemaUpdate but is this even able to make changes that move the data. For example you have a string property but decide to make it a many-to-one in your next release, would the current implementation of SchemaUpdate in Hibernate be able to do things like that?

Because this isn't consulting work with one client I am unable to use DB Diff'ing tools to update the DB with a staging DB server.

Any suggestions will be very much appreciated.

Regards, Jono

NHibernate doesn't support SchemaUpdate and there are no plans to add support for it.

Thanks for your reply however I never said NHibernate supported SchemaUpdate. I said Hibernate does and I was just asking to see if Hibernate's implementation would even work with the example I posted. If it would work with that example and is really good I'd look at trying to port it myself; however I doubt it would be able to work with the example I provided.

Thanks again, Jono

Try posting in the Hibernate Users forum, there should be more people who would know there. As far as I know, SchemaUpdate is not smart enough to move data around. I believe it can only generate simple ALTER TABLE ... ADD COLUMN or REMOVE COLUMN statements (besides creating new tables).

That's what I thought. For SchemaUpdate to be smarter it would need both copies of the mapping files so it can compare and even then it would make mistakes.

I guess that I am stuck with creating an SQL Server query file to upgrade the database. Then when customers want to use Access, Oracle or MySql I will have to make scripts for those databases too.

Are you aware of any other way this could be achieved? Or some program I can use to auto-generate the upgrade script with access to the 2 copies of the DB schema.

Jono

I was hoping for a reply. Is there any software out there that can generate a SQL upgrade type script from 2 databases?

Thanks, Jono

Try AdeptSQL Diff.

There are others out there too...

Cheers,

Symon.

Thanks for that I'll try it out.