Welcome Guest, you are in: Login

Castle Project

RSS RSS

Navigation (Active Record)





Search the wiki
»

PoweredBy

Primary Key Mapping

RSS
Modified on 2011/01/03 02:36 by Jan Wilson Categorized as Uncategorized
Regular ActiveRecord types must have a primary key, a key that uniquely indentifies any row in a table. Single surrogate keys are favoured over composite keys, but both are supported. When having control over the database schema, adding a surrogate primary key to the tables is the recommended way of implementing primary keys.

Single Primary Key

A single primary key is a column used as a row identifier. If it has no business meaning and can be chosen freely among unused values, the key is called a surrogate primary key. Primary keys can be assigned by the using code, auto generated by the database or by ActiveRecord using one of the strategies to generated non duplicate values.

To declare a primary key in a class a property to hold it must be created and decorated with the PrimaryKeyAttribute. This attribute holds information for mapping the primary key column. Most importantly, it determines the generation strategy which defaults to native, using an auto generation method supported by the used database. The following table script shows a simple entity:

CREATE TABLE Entity (
    [id] [int] IDENTITY (1, 1) NOT NULL
    -- payload ommitted for clarity
) ON [PRIMARY]

This table would be easily mapped to an ActiveRecord class:

using Castle.ActiveRecord;

[ActiveRecord]
public class Entity : ActiveRecordBase<Entity>
{
    private int id;
    
    [PrimaryKey(PrimaryKeyType.Native)]
    private int Id
    {
        get { return id; }
        set { id = value; }
    }
    
    // payload ommitted for clarity
}

For this case, the PrimaryKeyType could be omitted as it will default to Native anyway. ActiveRecord will correctly assume that the column name is Id. If the column had a different name, for example EntityId, it must be explicitly specified:

    private int id;
    
    [PrimaryKey(PrimaryKeyType.Native, "EntityId")]
    private int Id
    {
        get { return id; }
        set { id = value; }
    }

A setter is not needed for the primary key, but ActiveRecord needs to set the value somehow. For example the key can be directly set by using the backing field. This requires that the Access is specified:

    private int id;
    
    [PrimaryKey(Access=PropertyAccess.FieldCamelcase)]
    private int Id
    {
        get { return id; }
    }

Key Generation strategies

ActiveRecord supports all strategies implemented by NHibernate for creating primary keys. The possible strategies are listed below with a short explanation. For some more details please refer to this posting.

NameEnum ValueDescription
IdentityPrimaryKeyType.IdentityUses an identity column if available. Objects will be instantly inserted when saved.
SequencePrimaryKeyType.SequenceUses sequences where supported by the database. Two database calls are necessary for saving an object. The sequence name can be specified with the sequence-parameter or the SequenceName- property.
Hi/LoPrimaryKeyType.HiLoUses a Hi/Lo algorithm. Hi values are stored in a special database table. A hi value is fetched once and incremented locally. table, column and max_lo can be specified as parameters, with max_lo specifying the number of values locally incremented before fetching a new high value.
Sequence Hi/LoPrimaryKeyType.SeqHiLoUses a Hi/Lo algorithm. The hi values are fetched from an Oracle-style sequence and incremented locally. Parameters are sequence and max_lo.
UUID (hex representation)PrimaryKeyType.UuidHexThis strategy uses a guid and converts it into a hex representation, creating a readable string, customized by format and separator specification. This allows to use guids on databases that do not support GUIDs. format and separator must be specified as parameters.
UUID (compact representation)PrimaryKeyType.UuidStringThis strategy uses a guid and converts it into a byte-array, casting it into a string. It is written to the database as a CHAR(16), using up less space than UuidHex, but creating unprintable representations.
GUIDPrimaryKeyType.GuidUses GUIDs where supported by the database.
Combined GUIDPrimaryKeyType.GuidCombUses GUIDs where supported by the database. The GUIDs are created by taking system time into account. The risk for a key collision is therefore slightly higher, though still neglectable. The resulting GUIDs are already sorted after creation, increasing database performance.
IncrementPrimaryKeyType.IncrementIncrements values locally. This is neither safe in clusters nor in applications with multiple clients.
User AssignedPrimaryKeyType.AssignedThe key has a business meaning and is not generated but must be assigned by the user before Create() or Update() is called.

Some strategies require more parameters, which can be specified by using the Params property. The parameters needed are specified as name=value-pairs separated by commas as shown in the following example:

    private String id;
    
    [PrimaryKey(PrimaryKeyType.UuidHex, Params="format=D,seperator=-")]
    public String Id
    {
        get { return id; }
        set { id = value; }
    }

The Identity Problem

Despite being the default generator, using IDENTITY keys is discouraged. The reason for this is an exception to the regular lifecycle when identity keys are used.

When an entity is saved, a primary key must be assigned. However, the only possibility to determine a key using a database assigned identity value is inserting a row into a table. As a consequence, the entity is instantly saved to the database outside of any coordinated database flush.

This is not perceived in the code unless a SessionScope is used which is mandatory for lazy loading and a common pattern in web applications. Since the scope can be defined far away from the code that actually performs the call to Save(), this code behaves differently based on the context it is executed in. This is a maintenance nightmare which should be avoided by using other key generation strategies. Also IDENTITY generates an overhead of database calls, as described in this blog article.

Further Information

Please refer to the Reference Manual's Attributes article for further information.

Composite Primary Keys

Composite keys, also known as natural keys, consist of a set of columns that define the identifier of a row.

Composite keys are highly discouraged and should not be used unless there is no other alternative.

Composite keys are highly discouraged and should not be used unless there is no other alternative.

To use composite keys with ActiveRecord two things are necessary:

1. Creating a class to hold the properties and fields for the columns that make up the key.
  • The class must be Serializable
  • Equals and GetHashCode must be overridden

2. Declaring the property on the ActiveRecord type, using the CompositeKeyAttribute.

This is shown for the following table script:

CREATE TABLE Users (
    [OrgID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [Name] [varchar] (50) NULL,
    [Address] [varchar] (50) NULL,
    [City] [varchar] (50) NULL,
    [State] [varchar] (50) NULL
) ON [PRIMARY]

The following is the definition of the composite key class ProductSupplierKey and next is the ActiveRecord type:

using Castle.ActiveRecord;

[Serializable]
public class UserKey
{
    private int orgID;
    private int userID;
    
    [KeyProperty]
    public int OrgID
    {
        get { return orgID; }
        set { orgID = value; }
    }

    [KeyProperty]
    public int UserID
    {
        get { return userID; }
        set { userID = value; }
    }
    
    public override int GetHashCode()
    {
        return orgID ^ userID;
    }

    public override bool Equals(object obj)
    {
        if (this == obj)
        {
            return true;
        }
        UserKey key = obj as UserKey;
        if (key == null)
        {
            return false;
        }
        if (orgID != key.orgID || userID != key.userID)
        {
            return false;
        }
        return true;
    }
}

[ActiveRecord("Users")]
public class User : ActiveRecordBase
{
    private UserKey key;
    
    [CompositeKey]
    public UserKey Key
    {
        get { return key; }
        set { key = value; }
    }
}

Image
Warning

There are implications on using composite keys discussed below. As was mentioned above, Composite keys are discouraged. However, if they must be used, additional complexity in how the model is mapped to the database must be addressed.

Implications of using composite keys

An assigned identifier (like all CompositeKeys and assigned single PrimaryKeys) cannot be used to determine whether an instance is detached or transient - since its value is assigned by the application, it is never null. Therefore, one of the strategies below must be used or NHibernate will misbehave around the way it persists the instance to the database.

To ensure that the data is persisted properly, two methods for managing persistence are available:

1. Using the VersionAttribute to set the UnsavedValue. Normally, the UnsavedValue is used with the PrimaryKeyAttribute, where the UnsavedValue is checked by NHibernate to determine the state of the instance: if the field or property is equal to the UnsavedValue, then the object has not yet been persisted. However, because the field or property marked with the CompositeKeyAttribute cannot have an UnsavedValue that is understood by NHibernate, another field or property must be used - the one that was marked by the VersionAttribute. This allows the use of the Save() method. 2. Not using the Save() method. Create and Update can be used to force NHibernate to correctly persisting the objects.

Relations with composite keys

Because a composite key is by nature multi-field, there are additional requirements when building the relations between objects that include these keys. The largest part of those requirements is that the HasMany, BelongsTo and HasAndBelongsToMany attributes will use different properties to determine the Columns, ColumnKeys and ColumnKeyRefs.

To continue the example used above, the User class will be redefined and Org and Group classes added including their relationships.

First, some DDL is necessary to create the Org and Group tables, as well as the association table for the many-to-many relationship between Users and Groups.

CREATE TABLE Orgs (
    [ID] [int] NOT NULL,
    [Name] [varchar] (50) NULL
) ON [PRIMARY]

CREATE TABLE Groups (
    [ID] [int] NOT NULL,
    [Name] [varchar] (50) NULL
) ON [PRIMARY]

CREATE TABLE UserGroups (
    [OrgID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [GroupID] [int] NOT NULL
) ON [PRIMARY]

Next, the User class will be redefined, adding the appropriate markup to maintain the mapping relationships.

using Castle.ActiveRecord;

ActiveRecord("Users")]
public class User : ActiveRecordBase
{
    private UserKey key;
    private ISet groups;
    private Org org;

    public User()
    {
        groups = new HybridSet();
    }

    public User(UserKey userKey) : this()
    {
        key = userKey;
    }

    [CompositeKey]
    public UserKey Key
    {
        get { return key; }
        set { key = value; }
    }

    [HasAndBelongsToMany(typeof(Group),
        Table="UserGroups",
        ColumnRef="GroupID",
        CompositeKeyColumnKeys=new string[]{"OrgID","UserID"},
        Lazy=true,
        Cascade=ManyRelationCascadeEnum.SaveUpdate)
    public ISet Groups
    {
        get { return groups; }
    }

    [BelongsTo("OrgID", Insert=false, Update=false)
    public Org Org
    {
        get { return org; }
        set { org = value; }
    }
}

Note the CompositeKeyColumnKeys array, these are the fields that make up the foreign composite key in the association table.

Another interesting item when dealing with composite keys is building a relationship using only a single field of the composite key as the foreign key in a traditional one-to-many or many-to-one relationship. The "one" side of that mapping in the definition of the "Org" property has set Insert/Update to false. This is not optional. It prevents the other side of the relation from attempting to insert or update a portion of the composite key (in this case, the "OrgID" field).

Next step is defining the other two classes, illustrating the other side of the relationships.

[ActiveRecord("Orgs")
public class Org : ActiveRecordBase
{
    private int id;
    private ISet users;

    public Org()
    {
        users = new HybridSet();
    }

    [PrimaryKey(PrimaryKeyType.Native)]
    public int ID
    {
        get { return id; }
        set { id = value; }
    }

    [HasMany(typeof(User), Lazy=true)
    public ISet Users
    {
        get { return users; }
    }
}

[ActiveRecord("Groups")]
public class Group : ActiveRecordBase
{
    private int id;
    private ISet users;

    public Group()
    {
        users = new HybridSet();
    }

    [PrimaryKey(PrimaryKeyType.Native)]
    public int ID
    {
        get { return id; }
        set { id = value; }
    }

    [HasAndBelongsToMany(typeof(User),
        Table="UserGroups",
        CompositeKeyColumnRefs=new string[]{"OrgID","UserID"},
        ColumnKey="GroupID",
        Lazy=true,
        Inverse=true,
        Cascade=ManyRelationCascadeEnum.SaveUpdate)
    public ISet Users
    {
        get { return users; }
    }
}

On the side of the relationship without the composite key, an array of column refs is used, while on the side of the relationship with the composite key, it is an array of column keys.

ScrewTurn Wiki version 3.0.4.560. Some of the icons created by FamFamFam.