Rob Conery asked that we share some of the things that we’ve done with the new Subsonic, in celebration of its 2.1 release.  I’ve been using various svn versions of Subsonic for some time now, and feel that I have a decent grasp of its new features. 

One thing that seems to keep coming up is the need to implement an archive table for CMS type data.  So I whipped up some generic utility functions, that make use of the new query engine api, to handle the archive process automatically.  If the latest archive object does not match the object specified, these helpers will automatically rip through all the columns of the object and set the values for a new archive type.  To use this properly, you’ll want to have the same column names in the archive table that you do in your original table.

There are a few assumptions with these utility helpers:

1. The archive table needs a column CreatedOn that is a datetime – probably could be modified to sort by primary column, but I don’t think this is a major issue
2. Your model needs to be based on RepositoryRecord – could be modified easily to support ActiveRecord

Hope this helps and if you use this, please include a note for where you found it…

To make use of the magic, you would do something like:

DB.Save(somePost, User.Identity.Name); // Save the post as normal
ArchiveHelper.CheckAndCreateArchive<Post, PostArchive>(somePost, User.Identity.Name)

And now the helper methods (I put in a sealed class called ArchiveHelper)…

/// <summary>
/// Checks to see if changes have been made and if so, creates an archive.
/// </summary>
/// <typeparam name="T">Type of the item to archive</typeparam>
/// <typeparam name="TArchiveType">The type of the archive type.</typeparam>
/// <param name="item">The latest item</param>
/// <param name="modifiedBy">Username doing the modifications</param>
public static void CheckAndCreateArchive<T, TArchiveType>(T item, string modifiedBy) where T : IRecordBase where TArchiveType : RepositoryRecord<TArchiveType>, IRecordBase, new()
{
    if (HasChanged<T, TArchiveType>(item))
    {
        TArchiveType archive = CreateArchive<T, TArchiveType>(item);
        DB.Save(archive, modifiedBy);
    }
}

/// <summary>
/// Determines whether the specified item has changed since its last archive.
/// </summary>
/// <typeparam name="T">Type of the item to archive</typeparam>
/// <typeparam name="TArchiveType">The type of the archive type.</typeparam>
/// <param name="item">The latest item</param>
/// <returns>
///     <c>true</c> if the specified item has changed; otherwise, <c>false</c>.
/// </returns>
public static bool HasChanged<T, TArchiveType>(T item) where T : IRecordBase where TArchiveType : RecordBase<TArchiveType>, IRecordBase, new()
{
    TableSchema.Table itemSchema = item.GetSchema();

    SqlQuery latestArchiveQuery = DB.Select().Top("(1)").From<TArchiveType>().Where(itemSchema.PrimaryKey.ColumnName).IsEqualTo(item.GetPrimaryKeyValue()).OrderDesc("CreatedOn");
    List<TArchiveType> archives = latestArchiveQuery.ExecuteTypedList<TArchiveType>();

    if (archives.Count == 0)
        return true;

    TArchiveType latestArchive = archives[0];
    TableSchema.Table archiveSchema = latestArchive.GetSchema();

    foreach (SubSonic.TableSchema.TableColumn column in itemSchema.Columns)
    {
        if (IsReservedColumnName(column.ColumnName))
            continue;

        bool containsColumn = false;
        foreach (SubSonic.TableSchema.TableColumn c in archiveSchema.Columns)
        {
            if (c.ColumnName.Equals(column.ColumnName, StringComparison.OrdinalIgnoreCase))
            {
                containsColumn = true;
                break;
            }
        }

        if (containsColumn)
        {
            if (!item.GetColumnValue(column.ColumnName).Equals(latestArchive.GetColumnValue(column.ColumnName)))
                return true;
        }
    }
    return false;
}

/// <summary>
/// Creates an archive of the specified item.
/// </summary>
/// <typeparam name="T">Type of the item to archive</typeparam>
/// <typeparam name="TArchiveType">The type of the archive type.</typeparam>
/// <param name="item">The item used to create the archive</param>
/// <returns></returns>
public static TArchiveType CreateArchive<T, TArchiveType>(T item) where T : IRecordBase where TArchiveType : IRecordBase, new()
{
    TableSchema.Table itemSchema = item.GetSchema();

    TArchiveType archive = new TArchiveType();
    TableSchema.Table archiveSchema = archive.GetSchema();

    foreach (SubSonic.TableSchema.TableColumn column in itemSchema.Columns)
    {
        if (IsReservedColumnName(column.ColumnName))
            continue;

        bool containsColumn = false;
        foreach (SubSonic.TableSchema.TableColumn c in archiveSchema.Columns)
        {
            if (c.ColumnName.Equals(column.ColumnName, StringComparison.OrdinalIgnoreCase))
            {
                containsColumn = true;
                break;
            }
        }

        if (containsColumn)
        {
            archive.SetColumnValue(column.ColumnName, item.GetColumnValue(column.ColumnName));
        }
    }

    return archive;
}

private static bool IsReservedColumnName(string column)
{
    return (Utility.IsAuditField(column) || Utility.IsLogicalDeleteColumn(column));
}