There used to be a table named FOO with a VARCHAR primary key. The schema then changed so that the primary key of FOO became BIGINT. There is also a second table in the system (call it BAR) which has a foreign-key into FOO’s primary key. A classic master/details scenario.

However, the table BAR was obsoleted from the schema.

The integration tests runner is dropping all tables and recreating them before running the test suite. It is inferring the schema from the persisted classes using NHibernate’s mapper and the Schema creation feature of NHibernate.
Sleeves up
We cranked open the mysql console and started to look around:

When doing “SHOW TABLES”, the FOO table was not listed.

CREATE TABLE FOO (Id BIGINT) - fail with error 105.

CREATE TABLE FOO (Id VARCHAR) – success !!

huh?

DROP TABLE FOO – success

encouraging !

CREATE TABLE FOO (Id BIGINT) - fail with error 105 – again

huh ???

DROP TABLE FOO – fail with “cannot delete … foreign key …”

but SHOW TABLES still does not list FOO

huh ?????

DROP DATABASE dev; CREATE DATABASE dev;

now everything works.
Back to work
Luckily this was not a production database, and even more lucky – the said DB change (change that PK from VARVHAR to BIGINT) would need to run on production within a separate DB instance that can be recreated on deploy.

And while we’re at it

Looking into
When using an OR/M of any kind, it is quite worthwhile to be able to look at the SQL generated by the tool, for various reasons (such as tuning the DB, finding SELECT N+1 issues, and sheer curiosity).

Solution #1
One way of doing that is to start a profiler on the DB engine, but it has its downsides. For one, you would need a profiler tool, which is not always freely available. You might also not be able to access the DB engine with that kind of tool on various hosted environments.

In NHibernate’s configuration (and it is also exposed to Castle’s ActiveRecord users) you can set a property names “show_sql” to true. This will cause NHibernate to spit every SQL query, along with its parameters, onto the Console. Very useful when running Tests, but when running within a Web Application, you do not have access to the Console window, and can’t really see what is going on.

That also leads to another problem with using a profiler on the DB engine – you won’t be able to figure out which queries belong to which web request.

Solution #2
One comprehensive solution is to use the excellent tool from Oren Eini – NhProf. I will not cover this tool here; it does lots of great stuff, and can help your development cycle. However not everyone will be willing to pay the price for using it.

not to worry, I hereby offer you two more options, which gives you less options, but are good enough for the problem at hand, and are free.

Free solution #1
NHibernate is using log4net. it stores a lot of what it’s doing there. So, one can always setup a logger named “NHibernate.SQL” and get a hold of the queries. I do not cover log4net usage here. Google it up, and then set up the NHibernate.SQL logger.

You’d also want to setup a log4net variable for you to group the queries it gets by the web-request that needed them. You can do so by setting a property on log4net’s GlobalContext. e.g., in Application_BeginRequest:

then you’d set the log appender to write the page_url value, and you’ll be able to Group By it.

But this suck. You need to depend on log4net even if you do not want to, and setup that hack-ish global property, then read it from the log4net storage, and lots of complexities. wouldn’t it be great if you could simply got a hold of the Console’s output (or at least the NH parts of it?)

Free solution #2 – even simpler
The Console object allow you to set a custom Writer to its Out stream. Meaning, you can grab the Console’s output into an in memory string during a request, and then at the end of the request, grab all lines that starts with “NHibernate:” and you’re done:

within DoSomethingWith you can do whatever you like with the queries string collection

The more complete solution that I use, is taking advantage of a feature in Monorail’s AspView called ViewFilter (you can do this with ASP.NET’s output filter; look up HttpFilter. it’s not as clean, but workable). I create a filter that wrap the stuff that’s on the Application_EndRequest, turn the queries collection into a bunch of <pre> elements, and stick it within the view-engine’s output by simple string.Replace call, injecting these <pre> elements into a marker location in the markup.

I’d then use jQuery to make these <pre> elements visible when clicking somewhere secret on the screen.

explanation (before the wife kills me): I have some free time in the coming months, so I’m looking for interesting consulting gigs.

So, if you’re in a company / dev team, and looking for some help with Castle (Windsor, MonoRail), NHibernate, or general information system design and architecture advices or training, setting up build and test environments, or any other of the things I rant about in this blog, then I’m your guy.

To make things interesting I decided to create a large-ish mount of fake data. Well, at least in terms of a non-production university-demo app. My DB currently holds 600K students and over 500 possible modules.

I’ll blog a bit on the ways I used to populate that data, and other data as well (such as the ModulePrerequisites table which is an adjacency table).

The base concepts to understand are (my opinion):- The Cache is not the panacea of performance.- Don’t use the Cache like the base of your app; add the management of Cache at the end of your development process to increase the performance only where you really need do it.- Implementing a method named GetAll is, in most cases, a bad idea; an acceptable mediation is PaginateAll(pageSize).- InMemoryFilter can have less performance than filter trough RDBMS (especially when you intent to do it trough Cache with a large amount of entities).- Take care on what happen to the memory usage of your app when you are using Cache.

Ayende:

The cache is not magic, and should not be treated in such a fashion. I refuse to use the 2nd level cache in my applications until I have a perf problem that can’t be solved by creating smarter queries.Think about the cache as band aid, and good design as avoiding the need for that.

I always need to look it up, so I might as well put it here for future reference:

What is a DetachedCriteria?

This is an ICriteria object, that is detached from any ISession, therefore is suitable to be passed around as a specification that is being built up dynamically.

Why would I want to clone it?

Well, say I want to run two similar queries (same WHERE part) but with a different projection or aggregation. For example - when fetching a paged collection, you’d want to create the spec criteria, then add Count projection on A to get the ‘total number of records’ ,and add the paging restrictions (sort, then from row 101 take 10 records). Now if you’d try to add the restrictions to the criteria to which you have already applied Count on, and error would occur.

One of the methods of querying the DB when using NHibernate, is to issue HQL queries. HQL stands for “Hibernate Query Language”. It has a SQL-like syntax and is very intuitive for people with SQL background.

The way this works is that NHibernate ‘compiles’ the HQL query into SQL, and then issues the SQL query (using ADO.NET’s facilities) to the DB.

Sounds pricey?

enter Named Queries.

now these are HQL (or SQL) queries, each has a name (obviously), that are been supplied to NH through the mapping. The queries are being translated and cached as IDbCommand objects as part of the framework initialisation, which mean that you get rid of the HQL->SQL overhead throughout the life of the process.

One other major benefit, is that the mechanism to actually execute these named queries, does not differentiate between HQL and SQL queries (for the simple fact that these queries have already been transferred to SQL at runtime). That gives you the possibility to replace HQL queries into tighter SQL queries (with the same parameters and which returns the same resultset) should your DBA figure out a better one.

But if you’re using ActiveRecord, you usually do not have direct access into the mapping (.hbm) files. So how would you use named queries with AR?

enter HqlNamedQueryAttribute (not such a great name, as I did state that it would also work for SQL queries).

So, for an example, on this blog’s source code, within PostRepository.cs you’d see this code:

Queries class is marked as partial, as other queries might be presented on other repositories or services that would need to add more named queries. I considered grouping of queries into groups by the using repository, or by aggregate roots, but the thing is - having all of the queries under the same namespace helps discoverability, and helps with preventing duplications

I tried Linq for SQL (on a VS C# 2008 Express Beta2). No designer. Hand coded the entity, and have used the attributes for mapping.

First problem encountered: in order to make a column lazy, I need to change the underlying type to Link<MyOriginalType>, and then I can tell the context (using a LoadingOptions) about whether to load the lazy properties.

Couldn’t yet find a way to actually lazy load that property once the instance has already been loaded.

I much better like the way NH is handling things, with a runtime-generated proxy that takes care of lazy loading (among other stuff), so I get it without hassling my entities code.

Didn’t even mention First and Second Level Caches.

I guess I’d have to try and hop into NHibernate.Linq, and try to help Ayende with bringing it forward. That would mean diving into NH code, something I haven’t done for quite some time now …

Today at work I did a session with my team, showing them several methods to query the DB in a NH/AR enviroment.

When we talked about eager fetching, I said that doing it for more than one collection in one query isn’t good (as advised in hibernate’s site), since it might build up a rather large cartesian product.

How it works? Using a MultiCriteria,he isgettinghis entities,eagerly fetching one collection at a time. So it would return ei+earows. So for the first query, NH will populate the Entity instances, with the first collection (Items) populated. For the second query, NH will populate another Entity instances with the second collection (AnotherItems) populated. But, since it’s in the same session, actually the first Entity instances (with Items already populated) will get their AnotherItems collection populated.

That’s why at the end of the snippet, you see that he actually deals with list[0]. That’s where the first set of references is placed, and the other items in that list (list[1] … list[n]) are just copies of the references to the same Entity objects.

Quite a similar approach was seen when Ayende has shown us how to eager fetch using SQL (at the end of the post). The query there returns an array of Message items and an array of User items, but the User instances actually are wired to their Message instances, so he is using only the first array.

ActiveWriter is a VS2005 plugin, that adds a new item type for your projects. This item is actually a visual designer for ActiveRecord classes. Quite neat, and hopefully will increase the penetration of Castle’s ActiveRecord to the “If there’s no VS wizard, I do not use it” kinda guys.

There are two facts here:1. I love NHibernate.2. I hate NHibernate’s exception messages.

And here’s my story.

On a project I’m working on, I need to show a projection of “top 10” from the database. let’s show this on the good old Blog scenario:

So I want to show the posts with the longest comments measured by the comment’s length. Stupid, huh? but it’s a demo only (I cannot expose the actual ERD). Let’s say I want the top 5.

I am using Castle ActiveRecord. There is a Post and a Comment classes. However, I do not wish to load Posts objects, since It will load the Comments, too, and maybe other stuff that the Post class is related to. So I have defined a PostProjection class:

1: publicclass PostProjection

2: {

3: publicstring Title;

4: publicint Length;

5: public PostProjection(string title, int length)

6: {

7: Title = title;

8: Length = length;

9: }

10: }

I have also added an [Import] attribute on the Post. The actual querying is done using the next hql:

1: publicstatic PostProjection[] GetTopPosts(int postsToGet)

2: {

3: SimpleQuery&lt;PostProjection&gt; q =

4: new SimpleQuery&lt;PostProjection&gt;(typeof(Post), @"

5: select new PostProjection(p.Title, sum(c.LineCount))

6: from

7: Post p inner join

8: p.Comments c

9: order by sum(o.LineCount) desc

10: group by p.Title");

11: q.SetQueryRange(postsToGet);

12: return q.Execute();

13: }

It worked great.

Yesterday I’ve upgraded my Castle dll’s to the ones from build 229. It includes NHibernate 1.2.0.2002

Now the “select new” started to fail, and NHibernate started to claim than “Could not find constructor for: PostProjection”.

I’ve been scratching my head, trying various approaches, and even was keen to skip the “new” and use an object[ ] and populate the Projection Array by hand, but then I tried changing the “length” parameter of the constructor from “int” to “long”. Magically it solved the problem.