protected virtual void MergeData(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext,
BulkItemsAndFieldsReader itemAndFieldReader)
{
var sql = sqlContext.GetEmbeddedSql(loadContext, "Sql.08.MergeTempData.sql");
var stopwatch = Stopwatch.StartNew();
// Execute merge and collect imported items.
// Using sql parameters resets temp tables.
sql = sqlContext.ReplaceOneLineSqlBitParameter(sql, "@ProcessDependingFields",
itemAndFieldReader.HasFieldDependencies);
sql = sqlContext.ReplaceOneLineSqlBitParameter(sql, "@CleanupBlobs",
itemAndFieldReader.HasBlobFields);
sql = sqlContext.ReplaceOneLineSqlBitParameter(sql, "@AllowTemplateChanges",
loadContext.AllowTemplateChanges);
sql = sqlContext.ReplaceOneLineSqlStringParameter(sql, "@DefaultLanguage",
LanguageManager.DefaultLanguage.Name);
using (var cmd = sqlContext.NewSqlCommand(sql))
{
cmd.CommandTimeout = int.MaxValue;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
loadContext.ItemChanges.AddLast(new ItemChange(reader));
}
}
}
loadContext.Log.Info($"Merged loaded data: {(int)stopwatch.Elapsed.TotalSeconds}s");
}
protected virtual bool StageData(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext,
IEnumerable <BulkItem> items, out BulkItemsAndFieldsReader itemAndFieldReader)
{
var sql = sqlContext.GetEmbeddedSql(loadContext, "Sql.01.CreateTempTable.sql");
// Cleanup left-over staging tables.
if (loadContext.StageDataWithoutProcessing)
{
sqlContext.DropStageTables();
}
// Create temp table.
// We don't use table valued parameters because we don't want to change the database schema.
sqlContext.ExecuteSql(sql);
// Load data into temp table.
itemAndFieldReader = NewReader(items);
if (!BulkCopyToTempTable(loadContext, sqlContext, itemAndFieldReader, NewFieldRulesReader(loadContext)))
{
return(false);
}
loadContext.OnDataStaged?.Invoke(loadContext);
Event.RaiseEvent("bulkloader:datastaged", loadContext);
return(true);
}
protected virtual void LookupIds(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext,
BulkItemsAndFieldsReader itemAndFieldReader)
{
var lookupBlobsSql = sqlContext.GetEmbeddedSql(loadContext, "Sql.02.LookupBlobs.sql");
var lookupItemsSql = sqlContext.GetEmbeddedSql(loadContext, "Sql.03.LookupItems.sql");
var stopwatch = Stopwatch.StartNew();
if (loadContext.LookupBlobIds)
{
sqlContext.ExecuteSql(lookupBlobsSql);
}
if (loadContext.LookupItemIds)
{
// Using sql parameters resets temp tables.
if (loadContext.Destination != null)
{
lookupItemsSql = sqlContext.ReplaceOneLineSqlStringParameter(lookupItemsSql, "@destinationPath",
loadContext.Destination.ItemPath);
lookupItemsSql = sqlContext.ReplaceOneLineSqlStringParameter(lookupItemsSql, "@destinationId",
loadContext.Destination.ItemId.ToString("D"));
}
sqlContext.ExecuteSql(lookupItemsSql);
}
loadContext.Log.Info($"Looked up ids: {(int)stopwatch.Elapsed.TotalSeconds}s");
}
protected virtual bool ValidateAndPrepareData(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
var splitTempTablesSql = sqlContext.GetEmbeddedSql(loadContext, "Sql.04.SplitTempTable.sql");
var indexesSql = sqlContext.GetEmbeddedSql(loadContext, "Sql.06.CreateIndexes.sql");
var stopwatch = Stopwatch.StartNew();
sqlContext.ExecuteSql(splitTempTablesSql);
if (!OnStagedDataValidating.Execute(p => p.ValidateLoadStage(loadContext, sqlContext),
breakOnDefault: true))
{
return(false);
}
sqlContext.ExecuteSql(indexesSql);
if (!OnTempDataValidating.Execute(p => p.ValidateLoadStage(loadContext, sqlContext), breakOnDefault: true))
{
return(false);
}
loadContext.Log.Info($"Validated and prepared loaded data: {(int)stopwatch.Elapsed.TotalSeconds}s");
return(true);
}
public void Process(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
if (!loadContext.UpdateHistory.GetValueOrDefault())
{
return;
}
if (loadContext.ItemChanges.Count == 0)
{
return;
}
// In Sitecore 9, history engine is disabled by default
if (!HistoryEngineEnabled(loadContext))
{
loadContext.Log.Info($"Skipped updating history because history engine is not enabled.");
return;
}
var stopwatch = Stopwatch.StartNew();
var sql = sqlContext.GetEmbeddedSql(loadContext, "Sql.09.UpdateHistory.sql");
sqlContext.ExecuteSql(sql,
commandProcessor: cmd => cmd.Parameters.AddWithValue("@UserName", Sitecore.Context.User.Name));
loadContext.Log.Info($"Updated history: {(int)stopwatch.Elapsed.TotalSeconds}s");
}
public virtual void Process(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext, ICollection <ItemChange> changes)
{
if (!loadContext.UpdateIndexes)
{
return;
}
if (loadContext.IndexesToUpdate == null || loadContext.IndexesToUpdate.Count == 0)
{
return;
}
// We don't index bucket folders.
changes = changes?.Where(x => x.TemplateId != BucketFolderTemplate)?.ToList();
if (changes == null || changes.Count == 0)
{
return;
}
var stopwatch = Stopwatch.StartNew();
UpdateIndexes(loadContext, changes);
loadContext.Log.Info($"Updated content search indexes: {(int)stopwatch.Elapsed.TotalSeconds}s");
loadContext.OnDataIndexed?.Invoke(loadContext, changes);
Event.RaiseEvent("bulkloader:dataindexed", loadContext);
}
public bool ValidateLoadStage(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
if (!CheckDuplicates(loadContext, sqlContext))
{
loadContext.StageFailed(Stage.Load, "Duplicate items were found in set to load.");
return(false);
}
return(true);
}
public bool ValidateLoadStage(BulkLoadContext context, BulkLoadSqlContext sqlContext)
{
if (!CheckTempData(context, sqlContext))
{
context.StageFailed(Stage.Load, "Found missing templates, parents, items or fields.");
return(false);
}
return(true);
}
public bool ValidateLoadStage(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
return(true);
//if (!CheckDuplicates(loadContext, sqlContext))
//{
// loadContext.StageFailed(Stage.Load, "Duplicate items were found in set to load.");
// return false;
//}
//return true;
}
public void Process(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext, ICollection<ItemChange> changes)
{
if (!loadContext.RemoveItemsFromCaches) return;
var stopwatch = Stopwatch.StartNew();
// Remove items from database cache.
// We don't do this within the transaction so that items will be re-read from the committed data.
var db = Factory.GetDatabase(loadContext.Database, true);
_cachUtil.RemoveItemsFromCachesInBulk(db, GetCacheClearEntries(loadContext.ItemChanges));
loadContext.Log.Info($"Caches cleared: {(int)stopwatch.Elapsed.TotalSeconds}s");
}
protected virtual void UpdateLinkDatabase(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext,
LinkedList <BulkItemLink> links, ICollection <ItemChange> changes)
{
// Index all items that were actually changed in db.
var touchedItemsMap = changes
.GroupBy(x => x.ItemId)
.ToDictionary(x => x.First().OriginalItemId, x => x.First().ItemId);
// Get links and filter and map them by touched items.
if (links.Count == 0)
{
return;
}
var linksForTouchedItems = links
.Where(x => touchedItemsMap.ContainsKey(x.SourceItemID.Guid))
.Select(x => x.Map(x.SourceItemID.Guid, touchedItemsMap[x.SourceItemID.Guid]));
// Create temp table, bulk load temp table, merge records in link database.
// The link database is probably not the same physical db as the one were loading data to.
var createLinkTempTable = sqlContext.GetEmbeddedSql(loadContext, "Sql.20.CreateLinkTempTable.sql");
var mergeLinkData = sqlContext.GetEmbeddedSql(loadContext, "Sql.21.MergeLinkTempData.sql");
var connectionString = ((SqlServerLinkDatabase)Factory.GetLinkDatabase()).ConnectionString;
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var linkSqlContext = new SqlContext(conn);
linkSqlContext.ExecuteSql(createLinkTempTable);
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.BulkCopyTimeout = int.MaxValue;
bulkCopy.EnableStreaming = true;
bulkCopy.DestinationTableName = sqlContext.PostProcessSql(loadContext, "#ItemLinks");
try
{
bulkCopy.WriteToServer(new ItemLinksReader(() => linksForTouchedItems.GetEnumerator()));
}
catch (Exception exception)
{
loadContext.StageFailed(Stage.Load, exception,
$"Write to #ItemLinks failed with message: {exception.Message}");
return;
}
}
linkSqlContext.ExecuteSql(mergeLinkData);
}
}
public void Process(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext, ICollection<ItemChange> changes)
{
if (!loadContext.UpdateLinkDatabase.GetValueOrDefault()) return;
if (changes.Count == 0) return;
var stopwatch = Stopwatch.StartNew();
// Update link database, is in core database, so we can't do this within the transaction.
// Links are detected when reading the bulk item stream,
// so we assume that the same set will be presented again after a crash.
UpdateLinkDatabase(loadContext, sqlContext, GetItemLinksFromContext(loadContext), changes);
loadContext.Log.Info($"Updated link database: {(int)stopwatch.Elapsed.TotalSeconds}s");
}
public bool ValidateLoadStage(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
if (HasItemsWithoutFields)
{
loadContext.StageFailed(Stage.Load, "Items without fields were found .");
return(false);
}
if (HasItemsWithoutPaths)
{
loadContext.StageFailed(Stage.Load, "Items were found without paths.");
return(false);
}
return(true);
}
private bool CheckTempData(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
var check = sqlContext.GetEmbeddedSql(loadContext, "Sql.07.CheckTempData.sql");
var hasErrors = false;
using (var cmd = sqlContext.NewSqlCommand(check))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
if (!reader.GetBoolean(reader.GetOrdinal("HasParent")))
{
loadContext.Log.Error(
$"Unable to find parent '{reader["ParentId"]}' for item with id '{reader["Id"]}', " +
$"item path '{reader["ItemPath"]}' and source info '{reader["SourceInfo"]}'.");
}
if (!reader.GetBoolean(reader.GetOrdinal("HasTemplate")))
{
loadContext.Log.Error(
$"Unable to find template '{reader["TemplateName"]}' with id '{reader["TemplateId"]}' " +
$"for item with id '{reader["Id"]}', item path '{reader["ItemPath"]}' and source info '{reader["SourceInfo"]}'.");
}
hasErrors = true;
}
reader.NextResult();
while (reader.Read())
{
if (!reader.GetBoolean(reader.GetOrdinal("HasItem")))
{
loadContext.Log.Error(
$"Unable to find item with id '{reader["ItemId"]}', item path '{reader["ItemPath"]}' " +
$"and source info '{reader["SourceInfo"]}' for field '{reader["FieldName"]}' with id '{reader["FieldId"]}'.");
}
if (!reader.GetBoolean(reader.GetOrdinal("HasField")))
{
loadContext.Log.Error(
$"Unable to find field '{reader["FieldName"]}' with id '{reader["FieldId"]}' " +
$"for item with id '{reader["ItemId"]}', item path '{reader["ItemPath"]}' and source info '{reader["SourceInfo"]}'.");
}
hasErrors = true;
}
}
return(!hasErrors);
}
private bool CheckDuplicates(BulkLoadContext context, BulkLoadSqlContext sqlContext)
{
var check = sqlContext.GetEmbeddedSql(context, "Sql.05.CheckDuplicates.sql");
var hasErrors = false;
using (var cmd = sqlContext.NewSqlCommand(check))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
context.Log.Error(
$"Duplicate item found with id '{reader["Id"]}', item path '{reader["ItemPath"]}' " +
$"and source info '{reader["SourceInfo"]}'.");
hasErrors = true;
}
}
return(!hasErrors);
}
public void Process(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
if (!loadContext.UpdatePublishQueue.GetValueOrDefault())
{
return;
}
if (loadContext.ItemChanges.Count == 0)
{
return;
}
var stopwatch = Stopwatch.StartNew();
var sql = sqlContext.GetEmbeddedSql(loadContext, "Sql.10.UpdatePublishQueue.sql");
sqlContext.ExecuteSql(sql,
commandProcessor: cmd => cmd.Parameters.AddWithValue("@UserName", global::Sitecore.Context.User.Name));
loadContext.Log.Info($"Updated publish queue: {(int)stopwatch.Elapsed.TotalSeconds}s");
}
protected virtual bool BulkCopyToTempTable(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext,
BulkItemsAndFieldsReader itemAndFieldReader, FieldRulesReader fieldRulesReader)
{
var stopwatch = Stopwatch.StartNew();
// Bulk copy into temp tables, so that we don't have to buffer stuff,
// blobs can give OutOfMemoryExceptions.
using (var bulkCopy = new SqlBulkCopy(sqlContext.Connection))
{
bulkCopy.BulkCopyTimeout = int.MaxValue;
bulkCopy.EnableStreaming = true;
bulkCopy.DestinationTableName = sqlContext.PostProcessSql(loadContext, "#BulkItemsAndFields");
try
{
bulkCopy.WriteToServer(itemAndFieldReader);
}
catch (Exception exception)
{
loadContext.StageFailed(Stage.Load, exception,
$"Write to #BulkItemsAndFields failed with message: {exception.Message}");
return(false);
}
if (fieldRulesReader != null)
{
bulkCopy.DestinationTableName = sqlContext.PostProcessSql(loadContext, "#FieldRules");
try
{
bulkCopy.WriteToServer(fieldRulesReader);
}
catch (Exception exception)
{
loadContext.StageFailed(Stage.Load, exception,
$"Write to #FieldRules failed with message: {exception}");
}
}
}
loadContext.Log.Info($"Loaded data in database: {(int)stopwatch.Elapsed.TotalSeconds}s");
stopwatch.Restart();
return(true);
}
public void Process(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext, ICollection <ItemChange> changes)
{
loadContext.Log.Info(
$"Item changes in database: created: {loadContext.ItemChanges.Count(ic => ic.Created)}, " +
$"saved: {loadContext.ItemChanges.Count(ic => ic.Saved)}, moved: {loadContext.ItemChanges.Count(ic => ic.Moved)}, " +
$"deleted: {loadContext.ItemChanges.Count(ic => ic.Deleted)}");
if (!loadContext.Log.Logger.IsEnabledFor(Level.TRACE))
{
return;
}
foreach (var change in loadContext.ItemChanges)
{
if (change.Created)
{
loadContext.Log.Trace(
$"Created item with path '{change.ItemPath ?? "UNKNOWN"}', id '{change.ItemId}', " +
$"language '{change.Language ?? "NULL"}' and source info '{change.SourceInfo}' in database.");
}
if (change.Saved & !change.Created)
{
loadContext.Log.Trace(
$"Saved item with path '{change.ItemPath ?? "UNKNOWN"}', id '{change.ItemId}', " +
$"language '{change.Language ?? "NULL"}' and source info '{change.SourceInfo}' in database.");
}
if (change.Moved)
{
loadContext.Log.Trace(
$"Moved item with path '{change.ItemPath ?? "UNKNOWN"}', id '{change.ItemId}', " +
$"language '{change.Language ?? "NULL"}' and source info '{change.SourceInfo}' in database.");
}
if (change.Deleted)
{
loadContext.Log.Trace(
$"Deleted item with path '{change.ItemPath ?? "UNKNOWN"}', id '{change.ItemId}', " +
$"language '{change.Language ?? "NULL"}' and source info '{change.SourceInfo}' in database.");
}
}
}
protected virtual ICollection <ItemChange> GetChanges(BulkLoadContext loadContext, BulkLoadSqlContext sqlContext)
{
// By putting this in a virtual method, overriders can implement e.g. crash recovery.
return(loadContext.ItemChanges);
}
public virtual void LoadItems(BulkLoadContext context, IEnumerable <BulkLoadItem> items)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
if (items == null)
{
throw new ArgumentNullException(nameof(items));
}
items = OnItemProcessing.Execute(items, (p, itms) => p.Process(context, itms));
var db = Factory.GetDatabase(context.Database, true);
var connectionString = ConfigurationManager.ConnectionStrings[db.ConnectionStringName].ConnectionString;
var infoMessageHandler = new SqlInfoMessageEventHandler((s, e) => OnSqlInfoMessage(context, s, e));
using (var conn = new SqlConnection(connectionString))
{
var sqlContext = new BulkLoadSqlContext(conn, typeof(BulkLoader));
SqlTransaction transaction = null;
try
{
conn.InfoMessage += infoMessageHandler;
conn.Open();
BulkItemsAndFieldsReader itemAndFieldReader;
if (!StageData(context, sqlContext, items, out itemAndFieldReader))
{
return;
}
if (context.StageDataWithoutProcessing)
{
context.Log.Info("Data to import is staged in database, no processing done.");
context.StageSucceeded(Stage.Load);
return;
}
if (itemAndFieldReader.ReadItemCount > 0)
{
LookupIds(context, sqlContext, itemAndFieldReader);
if (!ValidateAndPrepareData(context, sqlContext))
{
return;
}
sqlContext.Transaction = transaction = conn.BeginTransaction();
MergeData(context, sqlContext, itemAndFieldReader);
}
OnTransactionCommitting.Execute(p => p.Process(context, sqlContext));
// After this point, there's no use in keeping the transaction arround,
// because we cannot sync everything transactionally (e.g. indexes, publshing, ...)
// Be aware that after this point the process may halt and not everything is consistent.
// We mitigate this inconsistency with crash recovery, see below.
transaction?.Commit();
transaction = null;
// Allow clearing caches before raising event so that reading the item API in event will result in fresh reads.
OnItemsLoading.Execute(p => p.Process(context, sqlContext, context.ItemChanges));
// Databases are now entirely in sync.
context.OnDataLoaded?.Invoke(context);
Event.RaiseEvent("bulkloader:dataloaded", context);
// Execute post processors.y.
var itemChanges = GetChanges(context, sqlContext);
OnItemsLoaded.Execute(p => p.Process(context, sqlContext, itemChanges));
context.StageSucceeded(Stage.Load);
}
catch (Exception ex)
{
transaction?.Rollback();
context.StageFailed(Stage.Load, ex.Message);
}
finally
{
conn.InfoMessage -= infoMessageHandler;
}
}
}