In a previous post I showed a solution that uses the Composite design pattern in an attempt to answer the following question by Sitecore MVP Kyle Heon:

Although I enjoyed building that solution, it isn’t ideal for synchronizing IDTable entries across multiple Sitecore databases — entries are added to all configured IDTables even when Items might not exist in all databases of those IDTables (e.g. the Sitecore Items have not been published to those databases).

I came up with another solution to avoid the aforementioned problem — one that synchronizes IDTable entries using a custom <publishItem> pipeline processor, and the following class contains code for that processor:

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

using Sitecore.Configuration;
using Sitecore.Data;
using Sitecore.Data.IDTables;
using Sitecore.Diagnostics;
using Sitecore.Publishing.Pipelines.PublishItem;

namespace Sitecore.Sandbox.Pipelines.Publishing
{
    public class SynchronizeIDTables : PublishItemProcessor
    {
        private IEnumerable<string> _IDTablePrefixes;
        private IEnumerable<string> IDTablePrefixes
        {
            get
            {
                if (_IDTablePrefixes == null)
                {
                    _IDTablePrefixes = GetIDTablePrefixes();
                }

                return _IDTablePrefixes;
            }
        }

        private string IDTablePrefixesConfigPath { get; set; }

        public override void Process(PublishItemContext context)
        {
            Assert.ArgumentNotNull(context, "context");
            Assert.ArgumentNotNull(context.PublishOptions, "context.PublishOptions");
            Assert.ArgumentNotNull(context.PublishOptions.SourceDatabase, "context.PublishOptions.SourceDatabase");
            Assert.ArgumentNotNull(context.PublishOptions.TargetDatabase, "context.PublishOptions.TargetDatabase");
            IDTableProvider sourceProvider = CreateNewIDTableProvider(context.PublishOptions.SourceDatabase);
            IDTableProvider targetProvider = CreateNewIDTableProvider(context.PublishOptions.TargetDatabase);
            RemoveEntries(targetProvider, GetAllEntries(targetProvider, context.ItemId));
            AddEntries(targetProvider, GetAllEntries(sourceProvider, context.ItemId));
        }

        protected virtual IDTableProvider CreateNewIDTableProvider(Database database)
        {
            Assert.ArgumentNotNull(database, "database");
            return Factory.CreateObject(string.Format("IDTable[@id='{0}']", database.Name), true) as IDTableProvider;
        }

        protected virtual IEnumerable<IDTableEntry> GetAllEntries(IDTableProvider provider, ID itemId)
        {
            Assert.ArgumentNotNull(provider, "provider");
            Assert.ArgumentCondition(!ID.IsNullOrEmpty(itemId), "itemId", "itemId cannot be null or empty!");
            List<IDTableEntry> entries = new List<IDTableEntry>();
            foreach(string prefix in IDTablePrefixes)
            {
                IEnumerable<IDTableEntry> entriesForPrefix = provider.GetKeys(prefix, itemId);
                if (entriesForPrefix.Any())
                {
                    entries.AddRange(entriesForPrefix);
                }
            }

            return entries;
        }

        private static void RemoveEntries(IDTableProvider provider, IEnumerable<IDTableEntry> entries)
        {
            Assert.ArgumentNotNull(provider, "provider");
            Assert.ArgumentNotNull(entries, "entries");
            foreach (IDTableEntry entry in entries)
            {
                provider.Remove(entry.Prefix, entry.Key);
            }
        }

        private static void AddEntries(IDTableProvider provider, IEnumerable<IDTableEntry> entries)
        {
            Assert.ArgumentNotNull(provider, "provider");
            Assert.ArgumentNotNull(entries, "entries");
            foreach (IDTableEntry entry in entries)
            {
                provider.Add(entry);
            }
        }

        protected virtual IEnumerable<string> GetIDTablePrefixes()
        {
            Assert.ArgumentNotNullOrEmpty(IDTablePrefixesConfigPath, "IDTablePrefixConfigPath");
            return Factory.GetStringSet(IDTablePrefixesConfigPath);
        }
    }
}

The Process method above grabs all IDTable entries for all defined IDTable prefixes — these are pulled from the configuration file that is shown later on in this post — from the source database for the Item being published, and pushes them all to the target database after deleting all preexisting entries from the target database for the Item (the code is doing a complete overwrite for the Item’s IDTable entries in the target database).

I also added the following code to serve as an item:deleted event handler (if you would like to learn more about events and their handlers, check out John West‘s post about them, and also take a look at this page on the
Sitecore Developer Network (SDN)) to remove entries for the Item when it’s being deleted:

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

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

namespace Sitecore.Sandbox.Data.IDTables
{
    public class ItemEventHandler
    {
        private IEnumerable<string> _IDTablePrefixes;
        private IEnumerable<string> IDTablePrefixes
        {
            get
            {
                if (_IDTablePrefixes == null)
                {
                    _IDTablePrefixes = GetIDTablePrefixes();
                }

                return _IDTablePrefixes;
            }
        }

        private string IDTablePrefixesConfigPath { get; set; }

        protected void OnItemDeleted(object sender, EventArgs args)
        {
            if (args == null)
            {
                return;
            }

            Item item = Event.ExtractParameter(args, 0) as Item;
            if (item == null)
            {
                return;
            }

            DeleteItemEntries(item);
        }

        private void DeleteItemEntries(Item item)
        {
            Assert.ArgumentNotNull(item, "item");
            IDTableProvider provider = CreateNewIDTableProvider(item.Database.Name);
            foreach (IDTableEntry entry in GetAllEntries(provider, item.ID))
            {
                provider.Remove(entry.Prefix, entry.Key);
            }
        }

        protected virtual IEnumerable<IDTableEntry> GetAllEntries(IDTableProvider provider, ID itemId)
        {
            Assert.ArgumentNotNull(provider, "provider");
            Assert.ArgumentCondition(!ID.IsNullOrEmpty(itemId), "itemId", "itemId cannot be null or empty!");
            List<IDTableEntry> entries = new List<IDTableEntry>();
            foreach (string prefix in IDTablePrefixes)
            {
                IEnumerable<IDTableEntry> entriesForPrefix = provider.GetKeys(prefix, itemId);
                if (entriesForPrefix.Any())
                {
                    entries.AddRange(entriesForPrefix);
                }
            }

            return entries;
        }

        private static void RemoveEntries(IDTableProvider provider, IEnumerable<IDTableEntry> entries)
        {
            Assert.ArgumentNotNull(provider, "provider");
            Assert.ArgumentNotNull(entries, "entries");
            foreach (IDTableEntry entry in entries)
            {
                provider.Remove(entry.Prefix, entry.Key);
            }
        }

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

        protected virtual IEnumerable<string> GetIDTablePrefixes()
        {
            Assert.ArgumentNotNullOrEmpty(IDTablePrefixesConfigPath, "IDTablePrefixConfigPath");
            return Factory.GetStringSet(IDTablePrefixesConfigPath);
        }
    }
}

The above code retrieves all IDTable entries for the Item being deleted — filtered by the configuration defined IDTable prefixes — from its database’s IDTable, and calls the Remove method on the IDTableProvider instance that is created for the Item’s database for each entry.

I then registered all of the above in Sitecore using the following configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <events>
      <event name="item:deleted">
        <handler type="Sitecore.Sandbox.Data.IDTables.ItemEventHandler, Sitecore.Sandbox" method="OnItemDeleted">
          <IDTablePrefixesConfigPath>IDTablePrefixes/IDTablePrefix</IDTablePrefixesConfigPath>
        </handler>
      </event>
    </events>
    <IDTable type="Sitecore.Data.$(database).$(database)IDTable, Sitecore.Kernel" singleInstance="true">
      <patch:attribute name="id">master</patch:attribute>
      <param connectionStringName="master"/>
      <param desc="cacheSize">500KB</param>
    </IDTable>
    <IDTable id="web" type="Sitecore.Data.$(database).$(database)IDTable, Sitecore.Kernel" singleInstance="true">
      <param connectionStringName="web"/>
      <param desc="cacheSize">500KB</param>
    </IDTable>
    <IDTablePrefixes>
      <IDTablePrefix>IDTableTest</IDTablePrefix>
    </IDTablePrefixes>
    <pipelines>
      <publishItem>
        <processor type="Sitecore.Sandbox.Pipelines.Publishing.SynchronizeIDTables, Sitecore.Sandbox">
          <IDTablePrefixesConfigPath>IDTablePrefixes/IDTablePrefix</IDTablePrefixesConfigPath>
        </processor>
      </publishItem>
    </pipelines>
  </sitecore>
</configuration>

For testing, I quickly whipped up a web form to add a couple of IDTable entries using an IDTableProvider for the master database — I am omitting that code for brevity — and ran a query to verify the entries were added into the IDTable in my master database (I also ran another query for the IDTable in my web database to show that it contains no entries):

I published both items, and queried the IDTable in the master and web databases:

As you can see, both entries were inserted into the web database’s IDTable.

I then deleted one of the items from the master database via the Sitecore Content Editor:

It was removed from the IDTable in the master database.

I then published the deleted item’s parent with subitems:

As you can see, it was removed from the IDTable in the web database.

If you have any suggestions for making this code better, or have another solution for synchronizing IDTable entries across multiple Sitecore databases, please share in a comment.