Home » Data » Synchronize IDTable Entries Across Multiple Sitecore Databases Using a Custom publishItem Pipeline Processor

Synchronize IDTable Entries Across Multiple Sitecore Databases Using a Custom publishItem Pipeline Processor

Sitecore Technology MVP 2016
Sitecore MVP 2015
Sitecore MVP 2014

Enter your email address to follow this blog and receive notifications of new posts by email.

Tweets

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):

idtables-before-publish

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

idtables-after-publish-both-items

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:

idtables-deleted-from-master

It was removed from the IDTable in the master database.

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

idtables-published-deletion

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.

Advertisements

6 Comments

  1. […] Synchronize IDTable Entries Across Multiple Sitecore Databases Using a Custom publishItem Pipeline&n… […]

    • John Seaman says:

      Thank you for your solution!

      I’ve run into an issue that I can’t seem to solve. We are sporadically receiving this error when publishing items that are using the IDTable:

      Job started: Publish to ‘web’|#Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Exception: Cannot insert duplicate key row in object ‘dbo.IDTable’ with unique index ‘ndxID’. The duplicate key value is (629c0dbc-01ca-475f-8323-a7b983b063bf).
      The statement has been terminated. —> System.Data.DataException: Error executing SQL command: INSERT INTO [IDTable] ([Prefix], [Key], [ID], [ParentID], [CustomData]) VALUES (@prefix, @key, @id, @parentID, @customData) —> System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object ‘dbo.IDTable’ with unique index ‘ndxID’. The duplicate key value is (629c0dbc-01ca-475f-8323-a7b983b063bf).

      Any ideas as to what might be happening here? Have you seen this before?

      It looks to me like when provider.Add(entry); is being called, a row with that particular ID already exists in the table somehow. What I don’t understand is you are calling RemoveEntries(targetProvider, GetAllEntries(targetProvider, context.ItemId)); prior to adding anything, so all exsiting entries should be gone.

      • Hi John,

        I have not seen this before.

        I wonder if rows are still being deleted in the Database when Add(entry) is being called.

        Mike

  2. dthrasher says:

    Interesting solutions! I’ve implemented several Sitecore custom data providers that use the IDTable, and I’d been wondering about how to fix up the per-server table values.

    I like your publishing hooks, but I think I may have a simpler solution: Set the default IDTable storage to the Core database rather than the Master. Then it should be accessible from both the Sitecore Content Management (CM) and the Content Delivery (CD) servers.

    It seems like a bit of a hack, but it’d save a lot of custom code.

    • Thanks for the comment Dean!

      Sure, you have a point: storing entries in the IDTable in core would save time on coding, and would only require a configuration change.

      However, there could be a scenario where you wouldn’t want an entry to be accessible from the CD servers: the item might be new, and has not yet been published.

      In that case, it might be ideal to only have that item’s entry in the IDTable in master until it’s ready to be pushed out to the IDTable in web — or whatever database the CD servers are using — through a publish.

      • dthrasher says:

        Ah, that makes sense. I hadn’t thought about the fact that putting IDTable entries directly in Core would bypass the usual publishing workflow.
        For some of my data providers, this wouldn’t matter, but for others it would be a deal-breaker. Thanks!

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: