The other day Sitecore MVP Kyle Heon asked:

This tweet got the wheels turning — more like got the hamster wheel spinning in my head — and I began experimenting on ways to synchronize IDTable entries across different Sitecore databases.

In this post, I will show the first solution I had come up with — yes I’ve come up with two solutions to this although no doubt there are more (if you have ideas for other solutions, or have tackled this problem in the past, please share in a comment) — but before I show that solution, I’d like to explain what the IDTable in Sitecore is, and why you might want to use it.

Assuming you’re using SQL Server for Sitecore, the “out of the box” IDTable in Sitecore is a database table that lives in all Sitecore databases — though Sitecore’s prepackaged configuration only points to the IDTable in the master database.

One has the ability to store key/value pairs in this table in the event you don’t want to “roll your own” custom database table — or use some other data store — and don’t want to expose these key/value pair relationships in Items in Sitecore.

The key must be a character string, and the value must be a Sitecore Item ID.

Plus, one has the ability to couple these key/value pairs with “custom data” — this, like the key, is stored in the database as a string which makes it cumbersome around storing complex data structures (having some sort of serialization/deserialization paradigm in place would be required to make this work).

Alex Shyba showed how one could leverage the IDTable for URLs for fictitious products stored in Sitecore in this post, and this article employed the IDTable for a custom Data Provider.

If you would like to know more about the IDTable, please leave a comment, and I will devote a future post to it.

Let’s take a look at the first solution I came up with:

using System;
using System.Collections.Generic;
using System.Xml;

using Sitecore.Configuration;
using Sitecore.Data;
using Sitecore.Data.IDTables;
using Sitecore.Diagnostics;

using Sitecore.Sandbox.Data.IDTables;

namespace Sitecore.Sandbox.Data.SqlServer
{
    public class CompositeSqlServerIDTable : IDTableProvider
    {
        private IDictionary<string, IDTableProvider> _IDTableProviders;
        private IDictionary<string, IDTableProvider> IDTableProviders
        {
            get
            {
                if (_IDTableProviders == null)
                {
                    _IDTableProviders = new Dictionary<string, IDTableProvider>();
                }

                return _IDTableProviders;
            }
        }

        private string DatabaseName { get; set; }

        public CompositeSqlServerIDTable()
            : this(GetDefaultDatabaseName())
        {
        }

        public CompositeSqlServerIDTable(string databaseName)
        {
            SetDatabaseName(databaseName);
        }

        private void SetDatabaseName(string databaseName)
        {
            Assert.ArgumentNotNullOrEmpty(databaseName, "databaseName");
            DatabaseName = databaseName;
        }

        public override void Add(IDTableEntry entry)
        {
            foreach (IDTableProvider provider in IDTableProviders.Values)
            {
                provider.Add(entry);
            }
        }

        public override IDTableEntry GetID(string prefix, string key)
        {
            return GetContextIDTableProvider().GetID(prefix, key);
        }

        public override IDTableEntry[] GetKeys(string prefix)
        {
            return GetContextIDTableProvider().GetKeys(prefix);
        }

        public override IDTableEntry[] GetKeys(string prefix, ID id)
        {
            return GetContextIDTableProvider().GetKeys(prefix, id);
        }

        protected virtual IDTableProvider GetContextIDTableProvider()
        {
            IDTableProvider provider;
            if (IDTableProviders.TryGetValue(DatabaseName, out provider))
            {
                return provider;
            }

            return new NullIDTable();
        }

        public override void Remove(string prefix, string key)
        {
            foreach (IDTableProvider provider in IDTableProviders.Values)
            {
                provider.Remove(prefix, key);
            }
        }

        protected virtual void AddIDTable(XmlNode configNode)
        {
            if (configNode == null || string.IsNullOrWhiteSpace(configNode.InnerText))
            {
                return;
            }

            IDTableProvider idTable = Factory.CreateObject<IDTableProvider>(configNode);
            if (idTable == null)
            {
                Log.Error("Configuration invalid for an IDTable!", this);
                return;
            }

            XmlAttribute idAttribute = configNode.Attributes["id"];
            if (idAttribute == null || string.IsNullOrWhiteSpace(idAttribute.Value))
            {
                Log.Error("IDTable configuration should have an id attribute set!", this);
                return;
            }

            if(IDTableProviders.ContainsKey(idAttribute.Value))
            {
                Log.Error("Duplicate IDTable id encountered!", this);
                return;
            }

            IDTableProviders.Add(idAttribute.Value, idTable);
        }

        private static string GetDefaultDatabaseName()
        {
            if (Context.ContentDatabase != null)
            {
                return Context.ContentDatabase.Name;
            }

            return Context.Database.Name;
        }
    }
}

The above class uses the Composite design pattern — it adds/removes entries in multiple IDTableProvider instances (these instances are specified in the configuration file that is shown later in this post), and delegates calls to the GetKeys and GetID methods of the instance that is referenced by the database name passed in to the class’ constructor.

The CompositeSqlServerIDTable class also utilizes a Null Object by creating an instance of the following class when the context database does not exist in the collection:

using System.Collections.Generic;

using Sitecore.Data;
using Sitecore.Data.IDTables;

namespace Sitecore.Sandbox.Data.IDTables
{
    public class NullIDTable : IDTableProvider
    {
        public NullIDTable()
        {
        }

        public override void Add(IDTableEntry entry)
        {
            return;
        }

        public override IDTableEntry GetID(string prefix, string key)
        {
            return null;
        }

        public override IDTableEntry[] GetKeys(string prefix)
        {
            return new List<IDTableEntry>().ToArray();
        }

        public override IDTableEntry[] GetKeys(string prefix, ID id)
        {
            return new List<IDTableEntry>().ToArray();
        }

        public override void Remove(string prefix, string key)
        {
            return;
        }
    }
}

The NullIDTable class above basically has no behavior, returns null for the GetID method, and an empty collection for both GetKeys methods. Having a Null Object helps us avoid checking for nulls when performing operations on the IDTableProvider instance when an instance cannot be found in the IDTableProviders Dictionary property — although we lose visibility around the context database not being present in the Dictionary (I probably should’ve included some logging code to capture this).

I then glued everything together using the following configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <IDTable patch:instead="IDTable[@type='Sitecore.Data.$(database).$(database)IDTable, Sitecore.Kernel']" type="Sitecore.Sandbox.Data.$(database).Composite$(database)IDTable" singleInstance="true">
      <IDTables hint="raw:AddIDTable">
        <IDTable id="core" type="Sitecore.Data.$(database).$(database)IDTable, Sitecore.Kernel" singleInstance="true">
          <param connectionStringName="$(id)"/>
          <param desc="cacheSize">500KB</param>
        </IDTable>
        <IDTable id="master" type="Sitecore.Data.$(database).$(database)IDTable, Sitecore.Kernel" singleInstance="true">
          <param connectionStringName="$(id)"/>
          <param desc="cacheSize">500KB</param>
        </IDTable>
        <IDTable id="web" type="Sitecore.Data.$(database).$(database)IDTable, Sitecore.Kernel" singleInstance="true">
          <param connectionStringName="$(id)"/>
          <param desc="cacheSize">500KB</param>
        </IDTable>
      </IDTables>
    </IDTable>
  </sitecore>
</configuration>

To test the code above, I created the following web form which basically invokes add/remove methods on the instance of our IDTableProvider, and displays the entries in our IDTables after each add/remove operation:

using System;
using System.Collections.Generic;
using System.Linq;

using Sitecore.Configuration;
using Sitecore.Data.IDTables;

namespace Sandbox
{
    public partial class IDTableTest : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            AddEntries();
            ShowEntries();
            RemoveOneEntry();
            ShowEntries();
            RemoveAllEntries();
            ShowEntries();
        }

        private void AddEntries()
        {
            Response.Write("Adding two entries...<br /><br />");
            IDTable.Add("IDTableTest", "/mycoolhomepage1.aspx", Sitecore.Data.ID.Parse("{110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9}"));
            IDTable.Add("IDTableTest", "/someotherpage1.aspx", Sitecore.Data.ID.Parse("{BAB78AE5-8118-4476-B1B5-F8981DAE1779}"));
        }

        private void RemoveOneEntry()
        {
            Response.Write("Removing one entry...<br /><br />");
            IDTable.RemoveKey("IDTableTest", "/mycoolhomepage1.aspx");
        }

        private void RemoveAllEntries()
        {
            Response.Write("Removing all entries...<br /><br />");
            foreach (IDTableEntry entry in IDTable.GetKeys("IDTableTest"))
            {
                IDTable.RemoveKey(entry.Prefix, entry.Key);
            }
        }

        private void ShowEntries()
        {
            ShowEntries("core");
            ShowEntries("master");
            ShowEntries("web");
        }

        private void ShowEntries(string databaseName)
        {
            IDTableProvider provider = CreateNewIDTableProvider(databaseName);
            IEnumerable<IDTableEntry> entries = IDTable.GetKeys("IDTableTest");
            Response.Write(string.Format("{0} entries in IDTable in the {1} database:<br />", entries.Count(), databaseName));
            foreach (IDTableEntry entry in provider.GetKeys("IDTableTest"))
            {
                Response.Write(string.Format("key: {0} id: {1}<br />", entry.Key, entry.ID));
            }
            Response.Write("<br />");
        }

        protected virtual IDTableProvider CreateNewIDTableProvider(string databaseName)
        {
            return Factory.CreateObject(string.Format("//IDTable[@id='{0}']", databaseName), true) as IDTableProvider;
        }
    }
}

When I loaded up the web form above in a browser, I saw that things were working as expected:

Although I had fun in writing all of the code above, I feel this isn’t exactly ideal for synchronizing entries in IDTables across multiple Sitecore databases. I cannot see why one would want an entry for a Item in master that has not yet been published to the web database.

If you know of a scenario where the above code could be useful, or have suggestions on making it better, please drop a comment.

In my next post, I will show you what I feel is a better approach to synchronizing entries across IDTables — a solution that synchronizes entries via publishing and Item deletion.

Until next time, have a Sitecorelicious day!