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; } } }