I am investigating using NHibernate for a rather large web application, and I have a research question before I invest tons of time into porting my app to use NHibernate instead of my custom-built library.

The app needs to add/modify/drop certain tables at will while running. The rest of the tables will be stable and static at runtime. How well can NHibernate handle/help my app to do this? My app can already generate a System.Data.DataTable to represent any new or modified table, and I could potentially write code to automate transforming a DataTable to the xml that NHibernate wants. But can NHibernate handle a structurally changing database at runtime?

Thanks in advance!

Andrew

I think NHibernate cant handle the changes...
or u cant generate the mapping file programaticly..

There is a tool, hbm2ddl, used to generate DDL statements from HBM files. There's a class called SchemaExport which will execute those DDL statements against the database. It's being routinely used to run the NHibernate tests. It might not suit your needs exactly, but it may inspire your own solution.

One issue that remains is dynamic reconfiguration of the session factory - it's not supported, so you'll probably have to re-create it after tables have been added.

Thank you for your quick responses! A couple of follow up questions below:



That makes it sound like the piece I have to write in this process is a DataTable schema to .hbm file converter. Is that right?



I'm afraid I don't yet know what a session factory is, but if it doesn't support dynamic reconfiguration, does that mean my web app has to restart, or that the one user session that caused the new tables has to have its transaction restarted, or that all users with transactions (sessions?) have to finish up so something can be restarted before any new sessions can be started?

I hope those questions make sense.

I think I can maybe answer part of that. An ISession is your basis for interacting with the database, and the SessionFactory is how you get them. In a basic sense they're kind of like IDbConnections with methods to create or chain IDbCommands. The analogy doesn't get you real far, but hopefully it helps to explain the role of the SessionFactory. It would be kind of like an IDbConnectionFactory in a straight ADO.NET based DAL.

I really have to question the wisdom of reconfiguring your database schema at runtime, but you sound like you know more than your average bear, so I'll lay off. :)

Yes, if your tables don't all follow some predefined structure, you would probably need a generic DataTable -> hbm converter. But then it would of course be easier to write DataTable -> SQL converter than to use SchemaExport.

By the way, are you going to dynamically add new entity classes, too? I don't see any big problems with it, I'm just curious.



As ssmoot said above, session factories are used to create sessions. They contain the configuration information, so if you want to change the configuration, you'd have to probably replace the entire factory (they are pretty much immutable). You will probably have one global ISessionFactory instance, then when you change the schema you would replace it with the newly-configured one. This would leave two session factories around for a while, until all the sessions using the first one are finished and it is garbage-collected.

Of course you'll have problems if you change the schema so that the older sessions are unable to function at all, but I guess you know that already :)

Yes, I know it's unusual, and often unwise. But I'll tell you why I'm doing it, because maybe you or others know a good way to accomplish what I need. In this case, I am writing a web app that accepts file uploads where the file describes a questionnaire that needs to be made available online. The questionnaires may have any number and any type of questions on it, and a database table needs to be created to store the answers collected with the questionnaire.

One thought I had rather than creating a table for each questionnaire is to just have one table for all questionnaires, and have a binary field in the table where a HashTable or some other object that stores all the answers serializes itself. The problem with this is that I anticipate hundreds of thousands of rows to these questionnaires, and I'll want to do analysis on the answers, and using the database server's built-in aggregate functions to operate on the variables makes more sense.



So, as long as I am adding tables, the leftover sessions that are running when I create a new session factory should be able to finish their work just fine? There will be use cases of modifying and even removing tables, so perhaps during those operations, I can write a Monitor to prevent new sessions from starting, then wait for the concurrent ones to die out, and then create the new session factory so I don't have the concurrency issues. Does that sound best?



Hmmm... I already have a DataTable -> SQL Server 2000 DDL converter, but wouldn't it be better for me to write a DataTable -> hbm converter so I can leverage nHibernate's knowledge of multiple database server dialects? I'm actually surprised that/if nHibernate doesn't already have a DataSet <-> hbm converter written. It seems that it would be very generally useful. Maybe I'll write a good, general one and offer it to the project. Interested, PMs?



No. I'll have a generic entity class for those dynamically created tables, and it will have a NameValueCollection or some other map in it so I can access all the questionnaire's variables and their values at runtime.

Thank you all for your ideas and suggestions. Incidentally, what I'm building here will soon be turned into an open source project. I'm excited, as it will be my first.

You will need <dynamic-component> mapping which isn't implemented in NHibernate yet (but should be relatively easy to add).

By the way, wouldn't it be better to use a generic structure like this:

Then you can also run aggregating queries on the Answer field. You will only have to check that the answer is of the right type before storing it in the field. You will be able to map this Answers table to a collection as well, and won't need to mess with dynamic tables.

As for dataset to hbm conversion, there's no such thing yet. NHibernate is actually pretty ignorant of datasets, that's one of the many areas in which it can be improved.

AFAIK, most RDBMS can support this load (with a good server)...
(32 bits Identifiers => 2.000.000.000 rows)

Have you considered to make an XML schema for the structure and make a table in SQL Server 2005 which has a column which must contain XML according to that schema. In this way the different questionaries could probably be implemented as different XML document all adhering to the same schema?


Best regards,

Henrik Dahl

sergey wrote:

hdal wrote:

I did exactly this for an application. The schema defines legal types of questions like radio/checkbox/textbox and an ASP.NET app interprets the schema into an HTML page displaying the questions with js navigation (the "questionaires" are decision trees). I currently use a set of tables like those described by sergey and have had good performance. However, I have had had only ~600k total questions answered from ~7k responses to 300+ "questionaires" (including versions).

Still I would think that your performance concern lies largely with presentation and response, not reporting. As such i think good indexing will get you what you need with perhaps a short wait for reporting.

Yes, I'm sure they can. I was concerned about storing entire questionnaire response rows into a single column of a single row for the sake of not creating custom tables for each questionnaire. RDBMS's may handle that too, but they wouldn't be able to perform aggregate functions on the data, since they'd have no idea what was stuffed into that little cell.

Is that on the near horizon for the developers who are already familiar with NHibernate?



That's an interesting way of storing the answers that I have never considered. I can see some merit to it. I'm curious: how easy is it to transform the format the questions and answers are stored in using this schema into a more traditional table with all the questions for a particular survey going across the top, and the rows becoming difference instances of the questionnaire? I can't conjure up the SQL that would be required at the moment.

aarnott wrote:

I'm not familiar with MS SQL, but I know MS Access can do those cross-tab queries, so maybe MS SQL Server can do too? This approach is of course less "typed", all your answers will be strings instead of maybe more appropriate types.