Patrick Steele's .NET Blog : ActiveRecord

ActiveRecord: Loading Records by Primary Key

PSteele — Tue, 02 Jun 2009 22:53:14 GMT

I was reviewing some code today for a project that uses ActiveRecord. One of the things that stood out for me was the use of a query to find objects by their primary key. There's two reasons this raised a red flag for me:

ActiveRecord already contains a built-in method for locating objects by their primary key: Find().
Ayende just made a post about a month ago about why you shouldn't use queries to find objects by their primary key in NHibernate (which ActiveRecord uses under the hood). You loose some optimizations that are built-in to NHibernate. Read Ayende's post for more details.

So if you're an ActiveRecord user, how can you control which Session method to use – Get() or Load()? Easy:

ActiveRecordBase.Find() uses Session.Load to load objects by primary key.
ActiveRecordBase.TryFind() uses Session.Get to load objects by primary key.

And if you're ever wondering how ActiveRecord works under the hood (or how some NHibernate concepts are used), grab the source and look around!

Technorati Tags: .NET,ActiveRecord,Nhibernate

NHibernate Queries: HQL vs. Criteria API

PSteele — Mon, 01 Jun 2009 16:37:24 GMT

As someone who did a lot of SQL a few years ago, I was immediately more comfortable using HQL for my ActiveRecord queries than the Criteria API. But from a documentation/samples standpoint, I see a much higher percentage of the Criteria API being used vs. HQL. I still use HQL most of the time.

This morning, Ayende had a post that answered the question I've always wondered: Which should I use?

Technorati Tags: .NET,ActiveRecord,HQL,nHibernate

OutputDebugStringAppender in a web app?

PSteele — Tue, 05 May 2009 12:19:16 GMT

I spent a couple of hours yesterday trying to get log4net to log to an OutputDebugStringAppender with no success. At first, I thought I had a configuration error in log4net so I added a file appender and that works fine. It's a Monorail application that uses ActiveRecord for database access. There's a couple of spots where I wanted to check out the SQL being generated by ActiveRecord (NHibernate). I did all my set up for log4net and even got it logging to a FileAppender. But I wanted to log the SQL to an OutputDebugStringAppender.

Here's my logging set up:

        1: <log4net>

       2:     <!-- Define some output appenders -->

       3:     <appender name="logfile" type="log4net.Appender.FileAppender, log4net">

       4:         <file value="log-file.txt" />

       5:         <appendToFile value="true" />

       6:         <layout type="log4net.Layout.PatternLayout">

       7:             <conversionPattern value="%d [%t] %-5p %c - %m%n"/>

       8:         </layout>

       9:     </appender>

      10:     <appender name="ods" type="log4net.Appender.OutputDebugStringAppender, log4net">

      11:         <layout type="log4net.Layout.PatternLayout,log4net">

      12:             <conversionPattern value="%d [%t] %-5p %c - %m%n"/>

      13:         </layout>

      14:     </appender>

      15:     <logger name="NHibernate.SQL">

      16:         <level value="ALL" />

      17:         <appender-ref ref="logfile" />

      18:         <appender-ref ref="ods" />

      19:     </logger>

      20:     <root>

      21:         <!-- priority value can be set to ALL|INFO|WARN|ERROR -->

      22:         <priority value="ALL" />

      23:         <appender-ref ref="ods" />

      24:     </root>

      25: </log4net>

The log-file.txt gets all the logging I want, but I really just wanted to be able to view these statements in DebugView (gives me a bit more control in clearing out past queries, setting filters/highlight colors, etc…)

I'm positive I had something like this working before but I get NOTHING in DebugView. Anyone have any ideas as to what I'm doing wrong? Is there a permission issue that makes this not possible?

Technorati Tags: Log4Net,MonoRail,ActiveRecord,NHibernate

ActiveRecord: Never forget there's a database

PSteele — Thu, 06 Nov 2008 14:44:30 GMT

Some lessons learned while using ActiveRecord.

I really like ActiveRecord and I would recommend using it on .NET projects when there is the need an OR Mapping tool. The fault lay was us, the developers. We didn't pay enough attention to the fact that there was a database at the end of the calls that the repository was making. We didn't spot that some of the calls which our test code was making were potentially very expensive in real usage scenarios. And so we got bitten by the law of leaky abstractions, which manifest itself through a number of bad usage patterns which caused immediate performance problems.

Read the full post for other lessons.

Technorati tags: ActiveRecord

Generate ActiveRecord Classes from Existing DB Schema

PSteele — Fri, 31 Oct 2008 14:02:13 GMT

GeneratorStudio is a project on CodePlex used to automate the creation of Castle ActiveRecord classes from existing database schema. I haven't downloaded it yet to play around with it, but it looks like it would be a nice thing to have if you have a large (or even medium) schema that's already in production and you'd like to start using Castle ActiveRecord for DB access.

More info here and here.

Technorati tags: ActiveRecord, ORM, CodePlex

Mock Databases and ActiveRecord

PSteele — Fri, 31 Oct 2008 02:51:30 GMT

Fellow SRT employee Jay Harris has a great post on using SQLite's in-memory database feature to mock his database. He points out a few of the factors that drove him to investigate such an approach:

I did not want a SQL Server installation to be a requirement for me, the other developers, and my Continuous Integration server.
I wanted something fast. I didn't want to have to wait for SQL Server to build / tear down my schema.
I wanted something isolated, so the other developers, and my CI server, and I wouldn't have contention over the same database, but didn't want to have to deal with independent SQL Server instances for everyone.

Check it out.

Technorati Tags: ActiveRecord,SQLite,Mock

ActiveRecord and a Custom NHibernate PrimitiveType

PSteele — Wed, 08 Oct 2008 01:54:29 GMT

I was intrigued by Steve Smith's blog post yesterday about reducing SQL Lookup tables in nHibernate. He gave an example of a WorkOrderStatus class the exposed the actual status as a POCO object that wasn't stored in the database. What really piqued my interest was the following comment:

NHibernate can map this status directly if you create a WorkOrderStatusType class that inherits from NHibernate.Type.PrimitiveType and overrides its methods.

I never knew nHibernate supported this type of feature (never needed it or thought about it). As I'm an avid ActiveRecord user, I decided to see how I would implement a custom nHibernate PrimitiveType and utilize it via ActiveRecord. Turns out it was pretty easy! The full source is available from my GoogleCode page either through SVN or simply a ZIP download.

A quick note before we begin: I didn't find a whole lot of documentation on extending PrimitiveType and implementing your own. I reviewed some nHibernate code and I think I got the general implementation right, but can't be sure it'll work 100% of the time. It was a proof-of-concept project.

ActiveRecord Setup

I decided I'd use SQLite for this sample since it's perfect for this type of job -- small, compact and no install required. I can poke around the database to check schema and data using the SQLite addon for Firefox.

Instead of stealing Steve's WorkOrderStatus, I decided to go with a schema that has a simple Company object, and that Company object has a CompanyType defined. Instead of defining a lookup table just for company types, I'll create a CompanyType class that derives from NHibernate.Type.PrimitiveType and let nHibernate do the loading/saving.

First, the CompanyType. For this demo, it's a simple object with a Description (string) and a Value (integer). The Value is what is actually saved to the database (note: this isn't the entire class -- just the basics):

public class CompanyType : NHibernate.Type.PrimitiveType{    public static readonly CompanyType Software = new CompanyType() { Description = "Software", Value = 1 };    public static readonly CompanyType Manufacturing = new CompanyType() { Description = "Manufacturing", Value = 2 };    public static readonly CompanyType Insurance = new CompanyType() { Description = "Insurance", Value = 3 };     private static readonly CompanyType[] AllTypes = new CompanyType[] { Software, Manufacturing, Insurance };     public string Description { get; set; }    public int Value { get; set; }     public override string ToString()    {        return this.Description;    }     public CompanyType()        : base(SqlTypeFactory.Int32)    {    }}

I've defined an AllTypes[] that I'll use to find the matching CompanyType when nHibernate reads the integer from the database. The ctor calls the base class ctor and tells nHibernate what data type this new PrimitiveType is based on (the schema in the database will be an integer). I also overrode ToString() to return the Description property to make debugging easier.

The Company record is pretty simple too. When we get to the CompanyType, we tell ActiveRecord (which works through nHibernate) the column type for the column (our custom PrimitiveType):

[ActiveRecord]public class Company : ActiveRecordBase<Company>{    [PrimaryKey(Generator = PrimaryKeyType.Identity)]    public int Id { get; set; }     [Property]    public string Name { get; set; }     [Property]    public DateTime InceptionDate { get; set; }     [Property(ColumnType = "ARPrimitiveType.Model.CompanyType, ARPrimitiveType")]    public CompanyType CompanyType { get; set; }     public override string ToString()    {        return this.Name;    }}

Implementing the required methods in CompanyType was pretty easy. I'm not sure when DefaultValue is used, so I just return a CompanyType of Software:

public override object DefaultValue{    get { return CompanyType.Software; }}

ObjectToSQLString seems to want to convert your PrimitiveType (CompanyType) to a string value that can be used by the database. So we'll convert our Value property to a string:

public override string ObjectToSQLString(object value, NHibernate.Dialect.Dialect dialect){    CompanyType type = value as CompanyType;    return type.Value.ToString();}

The PrimitiveType class indicated the actual type of data stored in the database (a 32-bit integer for our CompanyType):

public override Type PrimitiveClass{    get { return typeof(Int32); }}

The FromStringValue and two Get overloads both need to do the same thing: Take a representation of the database value and convert it to our PrimitiveType (a CompanyType). For this I created a single method that converts the database integer back to a CompanyType instance using a LINQ query on the AllTypes array:

public override object FromStringValue(string xml){    return GetCompanyType(xml);} public override object Get(System.Data.IDataReader rs, string name){    return GetCompanyType(rs[name]);} public override object Get(System.Data.IDataReader rs, int index){    return GetCompanyType(rs[index]);} private object GetCompanyType(object val){    if (val == null)    {        return DefaultValue;    }     int v = Int32.Parse(val.ToString());    return AllTypes.First(x => x.Value == v);}

Next, nHibernate needs to know how to stick a CompanyType into the database. In the Set method, we take our "Value" property and place it in the IDbCommand.Parameters collection:

public override void Set(System.Data.IDbCommand cmd, object value, int index){    CompanyType type = value as CompanyType;    DbParameter param = cmd.Parameters[index] as DbParameter;    param.Value = type.Value;}

Finally, nHibernate wants to know that type of data this PrimitiveType is exposing to the outside world (our application):

public override Type ReturnedClass{    get { return typeof(CompanyType); }}

We've now implemented a PrimitiveType that will allow our application to program against a CompanyType object while the database deals with an integer.

Sample Code

Now we can create Company objects like this:

Company c = new Company();c.Name = "ABC Software";c.InceptionDate = new DateTime(2008, 1, 1);c.CompanyType = CompanyType.Insurance;c.Save();

If you look in the database, the Company table has a field called "CompanyType". It's an integer and the record above will populate the CompanyType with a value of 3.

You can use this object in HQL queries too:

private static void HQLTest(){    SimpleQuery<Company> query = new SimpleQuery<Company>(        "from Company c where c.CompanyType = ?", CompanyType.Manufacturing);    Company[] results = query.Execute();}

Or, if you prefer, you can use nHibernate's DetachedCriteria:

private static void CriteriaTest(){    DetachedCriteria query = DetachedCriteria.For<Company>("Company")        .Add(Expression.Eq("CompanyType", CompanyType.Manufacturing));     Company[] results = Company.FindAll(query);}

Conclusion

This was a fun demo project to create. It's always nice to learn something new about a tool you use often (ActiveRecord/nHibernate). This was a good proof-of-concept, but needs a lot more testing and error handling before it could be used in production. You're free to use the code as-is, but there are no guarantees as to its correctness.

If anyone is aware of any errors I've made in implementing a custom PrimitiveType, please feel free to let me know.

Technorati tags: ActiveRecord, nHibernate, PrimitiveType

ActiveRecord + HQL and an "IN" clause

PSteele — Thu, 02 Oct 2008 02:26:57 GMT

Late last year as I was using MonoRail and ActiveRecord for a simple web application. I was helping my local church find volunteers with various skills to teach some basic computer courses to the the church staff. I wanted to keep track of the volunteers along with the skills they had. I used this as another opportunity to learn more about MonoRail and ActiveRecord.

The Database

The database couldn't be any simpler: I had a Trainer table, a Skill table and a join table to keep track of the 1:M relationship between a Trainer and their skills.

ActiveRecord

The ActiveRecord classes were equally easy to define (in fact, I created the ActiveRecord objects first and then used schema generation to generate the actual database tables).

The Problem Query

I used MonoRail to put together a couple of web pages for editing of the data. Then I created a "Report" page that allowed me to pick an arbritrary set of skills and get a list of all Trainers that had that particular skill.

In SQL, I'd use an "IN" clause like this:

select t.*from Trainer tinner join TrainerSkills ts on ts.TrainerId = t.id and ts.SkillId in (2,6)

In the query above, the IN clause of (2,6) contains the primary keys of the two Skill records selected by the user. Pretty simple SQL. I needed to figure out how to get this in HQL (Hibernate Query Language).

My first attempt was an almost exact port of the SQL syntax (HQL is very similar to SQL anyway):

public static Trainer[] FindBySkillset(Skill[] skills){    SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where t.Skills in (?)", skills);    return q.Execute();}

That didn't work. I got some cryptic error about having an "unindexed collection before []". So then I tried a variation of the above query where I used a named parameter and specifically indicated the parameter was a list:

public static Trainer[] FindBySkillset(Skill[] skills){    SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where t.Skills in (:skills)");    q.SetParameterList("skills", skills);    return q.Execute();}

That gave me another odd error. After digging around in the HQL docs as well as finding a forum post somewhere that showed a slightly different IN clause, I found out that I needed to "flip" the way I use the IN clause. In SQL, you'd say "WHERE xxx IN (values...)". In HQL, you give the list of values first and use the "elements" keyword to indicate which collection to match up those values to. The final working query:

public static Trainer[] FindBySkillset(Skill[] skills){    SimpleQuery<Trainer> q = new SimpleQuery<Trainer>("from Trainer t where ? in elements (t.Skills)", skills);    return q.Execute();}

Hopefully this helps out someone else.

Technorati tags: MonoRail, ActiveRecord, HQL

Lazy Loading/Eager Loading

PSteele — Wed, 07 May 2008 14:00:00 GMT

The NHibernate FAQ has a new post about complex object graphs and lazy loading. If you use NHibernate (or, ActiveRecord -- which makes NHibernate a whole lot easier), it's worth a look. The technique described can greatly improve the way your app interacts with the database. OR mappers are nice, but you need to make sure you understand what is happening with the database calls that are made automatically by your OR framework.

If you're using ActiveRecord, here's a few more items regarding lazy loading:

Enabling Lazy Loading in ActiveRecord

Tuning ActiveRecord

Combating the Select N + 1 Problem In NHibernate

SRT's ActiveRecord Coding Sprint (and a "gotcha" to watch out for)

PSteele — Wed, 26 Sep 2007 16:05:00 GMT

After our open house, a few of SRT's consultants stayed to have a little Coding Sprint with Castle -- specifically, ActiveRecord.

Darrell Hawley, Jay Wren, Rocky Krcatovich and I started with the typical, employee database design: A company, which has many departments and many employees. Each employee exists in a department. Pretty simple. Darrell and Jay have used Castle before. I just played around with it a little this weekend in preparation for this Coding Sprint. Darrell and I did a little pair programming -- I watched him set up the database, the Company class and unit tests for creating a new company. Later, I did the Department and Employee classes along with their unit tests which he watched.

Darrell began by creating a SQL Server database. Jay decided to go the open-source route and use SQLite. He was hoping that SQLite's in-memory database option would speed up his unit tests, but I don't think he ever got that far. Darrell finished his Company set up (table, class and unit tests) before Jay had his database set up. :)

When it came to my turn, I typed out the Department class pretty quickly, but when it came to my unit tests, I kept getting an error from ActiveRecord that I hadn't initialized my Department type. We looked over the code for close to an hour and could not figure out what we did wrong. Here's a super-simple recreation that will show you the error:

using System;
using System.Collections.Generic;
using Castle.ActiveRecord.Framework.Config;
using Castle.ActiveRecord;

namespace ARoops
{
    public class MainApp
    {
        static void Main()
        {
            XmlConfigurationSource source = new XmlConfigurationSource("appconfig.xml");
            ActiveRecordStarter.Initialize(source, typeof(Department));

            Department dept = new Department();
            dept.Name = "Accounting";
            dept.Create();
        }
    }

    public class Department : ActiveRecordBase<Department>
    {
        private int _id;
        private string _name;

        [PrimaryKey]
        public int DeptID
        {
            get { return _id; }
            set { _id = value; }
        }

        [Property]
        public string Name
        {
            get { return _name; }
            set { _name = value; }
        }
    }
}

If you create this ActiveRecord project and run it, you'll get the following error:

You have accessed an ActiveRecord class that wasn't properly initialized. The only explanation is that the call to ActiveRecordStarter.Initialize() didn't include Department class

I'm sure many of you who are familiar with ActiveRecord will spot the error right away. When Darrell finally found it, we were both pretty disappointed that Castle didn't catch this right away. And it was such a simple little error.

What did we forget? We didn't decorate the Department class with the ActiveRecord attribute:

    [ActiveRecord]
    public class Department : ActiveRecordBase<Department>
    {

Once we did that, everything worked fine. I wonder why the ActiveRecordStart.Initialize() method doesn't check to make sure the types passed to it are decorated with the ActiveRecord attribute?