2009/06/05

Understand ActiveRecord mapping: Collections of simple objects

Recently I had a post in the Castle user’s group about mapping a collection of enums with ActiveRecord. Since I know it’s just as mapping other simple types, I only referred to the documentation of the Element and ElementType properties of the HasMany attribute.

But, it turned out it is not really clear to many how to map a collection of simple values using HasMany. Therefore I decided to prepare an explanatory post, which is what you are reading now.

Mapping Basics

The problem: I want to hold a collection of enum values, say I have a status enum and want a history of that status persisted. Here is my model:

namespace EnumMapping
{
	[ActiveRecord]
	public class Article
	{
		[PrimaryKey]
		public virtual int Id { get; set; }

		[Property]
		public virtual string Author { get; set; }

		[Property]
		public virtual string Title { get; set; }

		[Property]
		public virtual Status CurrentStatus { get;set;}

		[HasMany(...)]
		public virtual IList StatusHistory
		{
			get { return statusHistory; }
			set { statusHistory = value; }
		}
		private IList statusHistory = new List();

	}

	public enum Status
	{
		None, Planned, InWriting, InEditing, Released 
	}
}

The ellipsis in the HasMany attribute is by design. I will work out in this post what you have to put here, so I let it out to make you understand the post, and not just read the code and copy and paste it.

To understand what information ActiveRecord requires to map this collection, we first will take a look into how these collections are saved in the database:

image

As expected, we have to tables. The Article table stores all the simple properties, but it cannot store collections. In a relational model, collections are always modeled by foreign key relations. That means we need a second table that stores both the values and the link to the article in question.

Note: You might have noticed that the StatusHistory table does not have a primary key. This is because of the bag semantics we currently have for this collection. More efficient semantics are shown later.

Looking at the StatusHistory table again, we see what we have to tell ActiveRecord to fetch the values for us and populate our List:

What is the table’s name?

 image

We take that name and put it into the respective property of the HasMany attribute:

[HasMany(Table="StatusHistory",
	...)]
public virtual IList StatusHistory {...}

As you can see from the code, we are not through yet.

Which column refers to the article?

image

[HasMany(Table = "StatusHistory",
	ColumnKey = "article",
	...)]
public virtual IList StatusHistory {...}
Which column holds the value?

image

[HasMany(Table = "StatusHistory",
	ColumnKey = "article",
	Element = "status",
	...)]
public virtual IList StatusHistory {...}

What? There is even more to specify even though we already have all columns included?

Yes. ActiveRecord also needs to know the types of the columns. The foreign key’s column can be inferred from the types primary key, but we need to know what type the value actually has.

[HasMany(Table = "StatusHistory",
	ColumnKey = "article",
	Element = "status",
	ElementType = typeof(Status))]
public virtual IList StatusHistory {...}

That’s it (for now). We now can take that mapping and go for the database.

The Collection Types

I already mentioned the missing primary key on the collection’s table and that the collection uses bag semantics. So what are these semantics? There are a few available in ActiveRecord:

  • Bag: All elements are unordered and may appear multiple. Total chaos.
  • Set: The elements are unordered, but each one can be present only once. This is the typical relational collection.
  • List: Elements are ordered and may be there multiple times.
  • Dictionary: Elements have a key that identifies them.

If the collection type is not specified, ActiveRecord assumes that you want bag semantics. After all, that’s what a collection is. Put things in there and get them out. No one cares for ordering or duplication.

Unfortunately, this is quite inefficient with databases:Items in a bag cannot be updated individually. Recall that there is no primary key in the collection table. Since the value can be in the collection more than once, an update or delete would effect all rows with that value. As a result, every time the collection is updated, it is completely deleted from the DB and inserted again.

To get around this, we could use a set. However, this requires us to use another Collection type, Set<Status> from Iesi.Collections. So instead of a set, I will use a list semantic. This is suitable, because a status history has an implicit ordering.

To use the list semantic, the schema must be changed first, because an index column is needed to store the list’s index.

image

We now don’t have only an additional column, we also have a nice primary key consisting of the combination of the article’s id and the list’s index. So every value in the list can now be updated individually.

Now we must tell ActiveRecord to use list semantics and how to find the index:

[HasMany(Table = "StatusHistory",
	ColumnKey = "article",
	Element = "status",
	ElementType = typeof(Status),
	RelationType = RelationType.List,
	Index = "idx")]
public virtual IList StatusHistory {...}

That’s it.

3 comments:

Ariel said...

Isn't it better to declare it generic list?

Markus Zywitza said...

Well, I did, but LiveWriter isn't good at pasting code...

Ibsta said...

Bl00dy awesome. saved my arse. thanks mate