Tuesday, 8 December 2009

Gemini Issue Tracking – Mailbox Processing

One of the reasons I have been very quiet on the blog is I have been extremely busy launching the Gemini Mail Box Processor tool. It is an add-on to Countersoft’s Gemini Issue Tracking / Project Management tool whereby you can submit issues and comments via your email account.

They have a basic version which will allow you to create only new issues from a pop3 box and even then you have to pre-specify the issue type, component etc. So if you think this is restrictive, GMBP should be a breath of fresh air for you.

GMBP will create new issues and also comments from the inbox; let’s take both in turn.

New issues

When a new email arrives, it is imported, along with attachments, into the GMBP database. From here, it is processed into Gemini. This process is complex and will try a variety of comparisons to ascertain the correct Project, Component, Issue Type, Priority, Reporter etc. The first thing it will try is to Smart Match the email against the Gemini data. Comparing text in the subject and body it will predict the most suitable values based on the content of the email. If it fails to find a [unique] match, then it will resort to the defaults set per project, and/or pick the first item (eg component) from the list. These are all based on the GMBP settings.

A failed match will be put into Pending state for review. Adding text to the subject (such as a component name) will ensure pending emails can be processed accurately.

New Comments

Unlike the built in Gemini processor, GMBP will allow comments to be created from the email. If it contains a gemini project code (eg [ACME-23]) in the subject line, then it will be treated as a comment and attached as a comment to Issue 23. This is actually a simple case, as no matching is required, it is merely appended as a comment to the issue Id specified.

Other Features


Truncation is another cool feature of GMBP. Email threads can become very long and cumbersome and the last thing you would want is pages of Gemini issue with entire email threads. Truncation will truncate the email at various points with the option of including the full contents as an attachment – so nothing is lost!

GMBP will automatically truncate text above it’s ----Please reply above this line----- marker, but you can include others. Regular expressions and fixed values can be searched and truncated at that point. For example, you could add an expression for ‘From : support@domain.com’ which could be the top line that appears when people reply to the Gemini notifications. Then, only the actual content of the email will appear in Gemini.


If this was not exciting enough, there is more. GMBP comes with an administration website, and only those people in Gemini Administrators security group (or another group you specify) can access the website. Furthermore, there are options of Blacklisting certain email addresses (again specific, or regular expression) to prevent matches being able to post into Gemini.


Since it is possible that people have many email addresses, such as a work and home email, they might want to make requests to Gemini from any email. It is possible to set up aliases to link numerous email accounts to their Gemini account. Irrespective of which email address the issue is received from, it will still end up being reported from the aliased Gemini account.

Also, Out of Office reply notifications can be configured to be ignored; preventing pointless issues being created.


If you need to get an Issue Tracker / Project Management application, then I would fully recommend Gemini from Countersoft as a serious contender. Combined with the GMBP I don’t think there is much extra you would want!

Wednesday, 2 December 2009

NHibernate Querying across Composite Keys

Error Message: ‘The multi-part identifier “manager1_.MAN_ID” could not be bound’

Firstly, let me say I do not like composite keys. Unique constraints work much better. That said, NHibernate does have support for Composite Keys, for these legacy databases, and it is fairly intuitive with the intellisense. However, I have come across a bizarre issue when querying across these relationships.

Database Diagram

Project-Managers Schema

This is the database diagram. Nothing too special about it. Projects have Managers and managers can manager numerous projects.

It is worth saying that the same issue occurs with a two way composite key as well as this three way version. The project roles could probably be ignored since the ‘role’ is stored in the Managers table.

Classes / Mappings

One thing to note, that you have to override the Equals and GetHashCode methods to use with NHibernate.


again some lines have been removed for brevity.





This is quite interesting example of the composite-id mapping. There are three that make up the key and all are many-to-one elements that map to classes in their own right.  




Some of the above namespaces have been blurred for client confidentiality.

The Problem

I am currently working on a large search mechanism across various fields of the application. I am basing the search on the blog from Ayende Rahien so I create a DetachedCriteria object for the Project and then everything is based from that.

The method in question is as follows:


A little explanation. criteria (IAdvancedSearchCriteria) is my class which holds the search data. In this case, it is the LineManagerIds pulled from a multiple select box.

So this code runs and is then executed against the current NHSession and using NHProfiler I can review the SQL being generated:


If you remember, this should link projects –> Project Members –> Managers in a Many to Many style situation (although it is mapped as Many to one for the audit information)

Look at the inner SQL and you can see there is no mention of the Managers table. Why? I created an Association to the Manager object. It merely results in a list of errors like ‘The multi-part identifier “manager1_.MAN_ID” could not be bound’.

I assume it is something to do with composite-ids (Did I mention I don’t really like these…)

the association to the Manager is mapped up in the <composite-id> section of the mapping file. Since I have made many searches before, I deduce there is a high probability that this may have something to do with it.

So I assume that NHibernate is not using the Class association in the composite-id section. I could either re-design the database to remove the Composite Id (Tempting… did i mention i hate them?) but my remit is to leave the database alone…

So, to fix the problem, I help NHibernate out. If I add another <property> to the ProjectMembers mapping file for the Manager, I can make the query work.


Now, an important thing to note. I assume, NHiberate will do the inserting and updating based on the <composite-id> mapping, so I have set insert and update = “false” otherwise I think we’d have some confusing errors / SQL statements. I will update this post if this proves incorrect! but it has solved the searching mechanism as shown:


As you can see, we are now joining in on the managers table. There was no change to the code, just the mapping file.