Wednesday, February 28, 2007

Wading through mud with Castle ActiveRecord

Lately, it feels like I've been wading through mud. As if the ActiveRecord library has been doing nothing but preventing me from getting my work done. In my dreams, I could call MyEntity.Find() with a list of ICriterion objects, and get back what I want. I was having problems with that, because I want to select several object, left joining to a notes table, and return only the most recent note (with max(NoteDate)). While I can think it, and write it in raw SQL, it has been tough doing this in an ActiveRecord query. Just to see if it is possible, I tried HQL, and finally resorted to raw SQL. I learned that "raw SQL" has several requirements imposed on it:

* The returned result set must include the complete field list of all entities that you want to return (as far as I can tell).
* NHibernate will replace {nt.*} with a complete field list if you include "nt" as an alias for typeof(MyNote).
* Do not list an alias unless you are returning that entity in your SELECT.

I ended up succeeding using a statement similar to this:
IQuery q = sess.CreateSQLQuery(sql, new string[] { "t1", "t2", "t3"}
new Type[] { typeof(ActHistory), typeof(ActStatus), typeof(ActNotes) } );

Now, this is 2/28/2007, and I am using the trunk version of NHibernate, and the trunk version of ActiveRecord with a few custom patches, so your milage might vary.

I am strongly considering creating a view and an ActiveRecord class marked as read-only that maps to the view and has a few helpers for creating the "real" objects when needed. I'll post the results soon.

Labels: ,

Friday, February 23, 2007

Castle ActiveRecord with Criteria and Alias

Update May 25, 2007: ActiveRecord now supports DetachedCriteria, which eliminates the need for the SlicedFindAll that I wrote below. It is nice when a library moves to add support for such commonly needed functions. So in summary, use Detached criteria instead of the code below. It is still a nice example of using NHibernate sessions.

I have a history log, where each history record "belongs to" a service record. I have to treat this as a child-to-parent join, since some children are orphans. I wanted to use the FindAll(Criteria), but I wanted the option to have optional criteria, orders and aliases. My solution was to create an ARAlias class to represent an Associated Entity and an alias, and then build an ARBusinessBase class with the following method:

public static T[] SlicedFindAll(int firstResult, int maxResults,
Order[] orders, ARAlias[] aliases, params ICriterion[] criteria)
{
IList list = null;
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
ISession session = holder.CreateSession(typeof(T));

try
{
ICriteria sessionCriteria = session.CreateCriteria(typeof(T));

if (aliases != null)
{
foreach (ARAlias alias in aliases)
{
alias.AttachTo(sessionCriteria);
}
}

if (criteria != null)
{
foreach (ICriterion cond in criteria)
{
sessionCriteria.Add(cond);
}
}

if (orders != null)
{
foreach (Order order in orders)
{
sessionCriteria.AddOrder(order);
}
}

sessionCriteria.SetFirstResult(firstResult);
sessionCriteria.SetMaxResults(maxResults);

list = SupportingUtils.BuildArray(typeof(T), sessionCriteria.List());
}
catch (ValidationException)
{
throw;
}
catch (Exception ex)
{
throw new ActiveRecordException("Could not perform SlicedFindAll for "
+ typeof(T).Name, ex);
}
finally
{
holder.ReleaseSession(session);
}
return (T[])list;
}


The ActiveRecordBase methods in svn trunk allow you to supply a null for the Order[] array, but not for Criteria (in that case, they require an empty placeholder array. My method is more consistent, and allows null for any array. So if I do not need to do an "ORDER BY", I can do ...

IList list = History.SlicedFindAll(0, 10, null, null, myCriterionList);

rather than ...

IList list = History.SlicedFindAll(0, 10, new ARAlias[] {}, new Order[] {}, myCriterionList);

Either way works. My ARAlias class just attaches itself to the query's criteria by calling sessionCriteria.CreateAlias(associatedEntityName, aliasName). Here is the code for the ARAlias class:

using System;
using System.Collections.Generic;
using System.Text;

using NHibernate;
using NHibernate.SqlCommand;

namespace LearningByDoing.ActiveRecord.Support
{
// represents an alias to be added to a Criteria search
public class ARAlias
{
private string _associationPath;
private string _alias;
private JoinType _joinType;

public ARAlias(string associationPath, string alias)
{
_associationPath = associationPath;
_alias = alias;
_joinType = JoinType.InnerJoin;
}

public ARAlias(string associationPath, string alias, JoinType joinType)
{
_associationPath = associationPath;
_alias = alias;
_joinType = joinType;
}

public ICriteria AttachTo(ICriteria criteria)
{
// apparently, this creates and attaches the alias to the supplied criteria
criteria.CreateAlias(_associationPath, _alias, _joinType);
return criteria;
}
}
}



I might make contact with the ActiveRecord maintainer, and see if there is any interest in adding something like this to the mainline. I would have to build a patch for ActiveRecordBase, adding the non-generic base methods and calling them from the generic child type. In my code, I have ARSortOrder, ARAlias and ARCriterion. My ARCriterion class is very weak, but the ARSortOrder and ARAlias classes seem to work nicely in my application. My current ARCriterion is more of a "Builder" class that allows the user of my application to select a property, an equality condition like LESS_THAN or EQUALS, and a value or entity, and create a Criterion from that. I will not pretend that my augmentation of ActiveRecord's Criteria support eliminates the need for HQL, but it dramatically reduces it.

Labels: , , , ,

Wednesday, February 21, 2007

What is an Active Record anyway?

While looking for Castle ActiveRecord examples, I found a blog by David Hayden - post 1 (design pattern overview), post 2 (some details on a possible implementation) and post 3 (using the Castle Project). David had a series of articles about Rocky Lhotka's .NET Business Objects (one for each chapter), which I have also read and tried to apply. Don't get me wrong, Rocky's CSLA library works, but as of .Net 1.1 it required a great deal of code on my part. There are reasons why I and many others were investigating code generation tools.

I have been studying Castle's ActiveRecord framework for a week and a half, and it is awesome for new development and clean data. During this discussion, remember that ActiveRecord mostly generates the XML mapping files for you and delegates the heavy lifting to NHibernate. If you have a legacy database with "sometimes there" keys, the framework will punish you severely. Below is an example of a legacy scenario that will result in many extra futile selects, attempting to get data that just isn't there.


CREATE TABLE Users (
ID int identity(1,1) not null primary key,
Username varchar(30) not null unique,
Password varchar(30) not null,
CreatedOn smalldatetime not null default getdate()
) ;

CREATE TABLE LogEntries (
UName varchar(30) null,
UEvent varchar(255) null
) ;

INSERT INTO Users(Username, Password) VALUES('admin' , '123') ;
INSERT INTO Users(Username, Password) VALUES('jsmith', '999') ;

INSERT INTO LogEntries (UName, UDate, UEvent)
VALUES('jsmith', '2007-02-21 07:00', 'Woke up') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
VALUES('tjones', '2007-02-21 07:30', 'Looked out kitchen window') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
VALUES('jsmith', '2007-02-21 07:32', 'Ate breakfast') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
VALUES('tjones', '2007-02-21 08:00', 'Drove to work') ;
INSERT INTO LogEntries (UName, UDate, UEvent)
VALUES('khaus' , '2007-02-21 12:00', 'Ate Lunch') ;

You may have noticed that the LogEntries table does not have a foreign key relation to Users. This is because the table was imported from a text file, and we can't guarantee that all users listed are in our database. For this scenario, NHibernate will do an "N+1" fetch if we try to link LogEntries to users. The HQL statement [SELECT le, us FROM LogEntry le LEFT JOIN le.user] will cause us problems no matter what we try. If we add a [BelongsTo] User property in the LogEntry class (NHibernate <many-to-one\> mapping), we find that the PropertyKey xml attribute is not mapped into BelongsTo. If we add the property and modify ActiveRecord's XmlGenerationVisitor.cs to add PropertyRef, we find that NHibernate's SessionImpl.cs and Loader.cs basically ignore the username link because it is not a primary key, and select the associated User once for each log entry. This happens whether the user was loaded in the initial join or not, and whether the user has been loaded into memory or not. I tried lying and telling ActiveRecord that UserName was the primary key. That helped, and I now only saw extra selects when the UserName was not found in the initial join. I patched NHibernate's SessionImpl.cs to remember failed fetches, and reduced the number of selects to 1 + count(unique missing usernames). I can not submit this patch to NHibernate, because it only works for the scenario of joining on a primary key - it breaks for the PropertyRef scenario. The only way that I found to fix this multi-select problem was to add a foreign key reference to LogEntries (it does not have to have a constraint). So LogEntries would look like:


CREATE TABLE LogEntries (
UName varchar(30) null,
UEvent varchar(255) null,
User_ID int null
) ;


Now we have to run an update every time we import a log file, but the subsequent processes will run with good performance (Any remaining performance issues can be solved with indexes, etc.). Do NOT add a foreign key constraint, or your imports will fail on bad data.


UPDATE LogEntries SET User_ID = u.ID
FROM LogEntries le
JOIN Users u ON u.UserName = le.UName
WHERE User_ID is NULL ;


In the future, I will share my log4net file.

Labels: , , ,

Wednesday, February 07, 2007

Finding database model information in Castle and ActiveRecord

Since this is my first post on the subject, let me introduce the Castle project - a .Net framework that I have been exploring recently. It has a project that tries to mimic ActiveRecord in Ruby-On-Rails, although the Castle version of ActiveRecord uses NHibernate. The good news is that an ActiveRecord class doesn't require any XML configuration or mapping files. The somewhat bad news is that you DO have to declare your classes and populate them with properties, private members and AR mapping attributes. Since I will probably use SQLServer 2000, I will just write a few queries to extract the data type and field list for each table that I want to support. More on this later.

For today, I wanted to extract the class-to-database mapping information that Castle's ActiveRecords feeds to NHibernate. I finally looked through the source code a bit and after a blind alley (NHibernate.Cfg Configuration is mostly for database connections), I ran across the ActiveRecordModel class. This has the primary key, properties, foreign keys, etc. associated with a particular class that inherits from ActiveRecordBase. I wanted to see how hard it would be to offer a generic grid view and/or form view without hand-coding a maintenance module for each form. It looks possible to me. Anyway, here is some C# code to dump the properties of a Blog (as defined in a tutorial on the Castle web site) ...


using Castle.ActiveRecord.Framework;
using Castle.ActiveRecord.Framework.Config;
using Castle.ActiveRecord.Framework.Scopes;
using Castle.ActiveRecord.Framework.Internal;

... inside your application, after calling ActiveRecordStarter.Initialize or Register ...

// get Blog's model
ActiveRecordModel blogModel = ActiveRecordModel.GetModel(typeof(Blog));
foreach (PropertyModel prop in blogModel.Properties)
{
Debug.WriteLine(prop.Property.Name + " is a " + prop.Property.PropertyType.Name);
}

Labels: , , , ,