I tend to agree with those who believe that a good ORM will give you 90% of what you need. For me, with NHibernate and ActiveRecord it’s been closer to 95%. The remaining 5% tends to involve getting a DbConnection instance and executing some legacy stored procedure or something of that nature. For this post, I’ll describe a recent scenario that fell into that lesser bucket…

I recently decided to add full-text search to Jeopardy Story. I’d previously used full-text search for my articles on Code Voyeur (wow, two shameless plugs in one paragraph). Code Voyeur uses Spring.NET and SQL Server for data access. So a simple DAO implementation worked seamlessly. Jeopardy Story by contrast is built on Castle ActiveRecord and MySQL, which makes Full-text search a little more challenging.

First, MySQL supports full-text search only in tables using the MyISAM storage engine. MyISAM is really fast, but doesn’t support referential integrity or transactions. My full-text search was to be against a table that needed both. I chose not to give up InnoDB and its features. Instead, created a table to mirror the content of the transactional table, minus the columns not needed for the search. The MyISAM table is populated through a scheduled task. Admittedly, this is less preferable than a real time solution (triggers, etc.), but it works well enough and has minimal overhead. Also, triggers require SUPER privileges with MySQL. That privilege isn’t going to be handed out (hopefully) on a shared host… I did read on some forum that this requirement will change with the next release though.

The next challenge I had was trying to write the HQL query to execute the full-text search. Long story short - NHibernate (more accurately its SQL dialect providers) doesn’t support full-text search. After struggling with the HQL for a while, I finally looked at the Castle support forum and somewhere found a post informing me that full-text isn’t supported in the dialects (NHibernate uses SQL dialect providers to ensure the generated SQL is compliant with the target database). The only practical option I had was to use AR to get a DbConnection instance and run the SQL using plain-old-ADO. Something like:


ISession session = ActiveRecordMediator.GetSessionFactoryHolder().CreateSession(typeof(YourType));

string sql = @”SELECT your_columns_here
FROM your_table
WHERE MATCH(columns_in_your_index) AGAINST (?searchText)”;

IDbCommand cmd = session.Connection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new MySqlParameter(”searchText”, text));

IDataReader reader = cmd.ExecuteReader();

while (reader.Read())
{
doYourStuffHere();
}

cmd.Dispose();
cmd.Connection.Dispose();
session.Dispose();

My actual query returns an ids from the copy table that I then use to get the actual ActiveRecord class instances through standard AR calls (e.g. FindAll). It’s a two step query I’d rather perform in one, but I’ve yet to work through an alternate solution. Should I find one, I’ll of course post it or reference it here…

Also, it’s a good idea to take a look at MySQL’s stop word list. Your unit tests might appear to fail when all that really happened is that you just chose the wrong word for a search…

Finally, it’s worth noting that MyISAM issues aside, it’s really easy to create a full-text index with MySQL - especially when compared with SQL Server. OK, SQL Server full-text isn’t all that difficult to setup. But with the MySQL GUI tools, it was click -> Add Index -> select type full text index -> drag and drop columns for the index. Done.