/// <summary> /// Builds the name of the put stored procedure. /// </summary> /// <param name="streamName">Name of the stream.</param> /// <param name="asAlter">An optional value indicating whether or not to generate a ALTER versus CREATE; DEFAULT is false and will generate a CREATE script.</param> /// <returns>Creation script for creating the stored procedure.</returns> public static string BuildCreationScript( string streamName, bool asAlter = false) { var createOrModify = asAlter ? "ALTER" : "CREATE"; const string recordIdsToConsiderTable = "RecordIdsToConsiderTable"; var result = Invariant( $@" {createOrModify} PROCEDURE [{streamName}].[{GetInternalRecordIds.Name}]( @{InputParamName.InternalRecordIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IdentifierTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.ObjectTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.StringIdentifiersXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.TagIdsToMatchCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.TagMatchStrategy} {new StringSqlDataTypeRepresentation(false, 40).DeclarationInSqlSyntax} , @{InputParamName.VersionMatchStrategy} {new StringSqlDataTypeRepresentation(false, 20).DeclarationInSqlSyntax} , @{InputParamName.DeprecatedIdEventTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{OutputParamName.InternalRecordIdsCsvOutput} AS {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} OUTPUT ) AS BEGIN {RecordFilterLogic.BuildRecordFilterToBuildRecordsToConsiderTable(streamName, recordIdsToConsiderTable)} SELECT @{OutputParamName.InternalRecordIdsCsvOutput} = STRING_AGG([{Tables.Record.Id.Name}], ',') FROM @{recordIdsToConsiderTable} END"); return(result); }
public static string BuildCreationScript( string streamName, bool asAlter = false) { var createOrModify = asAlter ? "ALTER" : "CREATE"; const string recordIdsToConsiderTable = "RecordIdsToConsiderTable"; const string resultTableName = "ResultsTable"; var result = Invariant( $@" {createOrModify} PROCEDURE [{streamName}].[{GetDistinctStringSerializedIds.Name}]( @{InputParamName.InternalRecordIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IdentifierTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.ObjectTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.StringIdentifiersXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.TagIdsToMatchCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.TagMatchStrategy} {new StringSqlDataTypeRepresentation(false, 40).DeclarationInSqlSyntax} , @{InputParamName.VersionMatchStrategy} {new StringSqlDataTypeRepresentation(false, 20).DeclarationInSqlSyntax} , @{InputParamName.DeprecatedIdEventTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{OutputParamName.StringIdentifiersOutputXml} AS {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} OUTPUT ) AS BEGIN DECLARE @{resultTableName} TABLE ([{Tables.Record.StringSerializedId.Name}] {Tables.Record.StringSerializedId.SqlDataType.DeclarationInSqlSyntax} NULL, [{Tables.TypeWithVersion.Id.Name}] {Tables.TypeWithVersion.Id.SqlDataType.DeclarationInSqlSyntax} NOT NULL) {RecordFilterLogic.BuildRecordFilterToBuildRecordsToConsiderTable(streamName, recordIdsToConsiderTable)} INSERT INTO @{resultTableName} ([{Tables.TypeWithVersion.Id.Name}], [{Tables.Record.StringSerializedId.Name}]) SELECT DISTINCT ( CASE @{InputParamName.VersionMatchStrategy} WHEN '{VersionMatchStrategy.Any}' THEN r.[{Tables.Record.IdentifierTypeWithoutVersionId.Name}] WHEN '{VersionMatchStrategy.SpecifiedVersion}' THEN r.[{Tables.Record.IdentifierTypeWithVersionId.Name}] ELSE CONVERT({new StringSqlDataTypeRepresentation(false, 1).DeclarationInSqlSyntax}, CONVERT({new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax}, '@{InputParamName.VersionMatchStrategy} is not a supported {nameof(VersionMatchStrategy)}.')) END) , r.[{Tables.Record.StringSerializedId.Name}] FROM [{streamName}].[{Tables.Record.Table.Name}] r WITH (NOLOCK) INNER JOIN @{recordIdsToConsiderTable} rtc ON r.[{Tables.Record.Id.Name}] = rtc.[{Tables.Record.Id.Name}] LEFT OUTER JOIN [{streamName}].[{Tables.Record.Table.Name}] r1 WITH (NOLOCK) ON r.[{Tables.Record.StringSerializedId.Name}] = r1.[{Tables.Record.StringSerializedId.Name}] AND r.[{Tables.Record.Id.Name}] < r1.[{Tables.Record.Id.Name}] AND r1.[{Tables.Record.Id.Name}] = rtc.[{Tables.Record.Id.Name}] WHERE r1.[{Tables.Record.Id.Name}] IS NULL AND rtc.[{Tables.Record.Id.Name}] IS NOT NULL SELECT @{OutputParamName.StringIdentifiersOutputXml} = (SELECT e.[{Tables.TypeWithVersion.Id.Name}] AS [@{TagConversionTool.TagEntryKeyAttributeName}] , ISNULL(e.[{Tables.Record.StringSerializedId.Name}], '{TagConversionTool.NullCanaryValue}') AS [@{TagConversionTool.TagEntryValueAttributeName}] FROM @{resultTableName} e FOR XML PATH ('{TagConversionTool.TagEntryElementName}'), ROOT('{TagConversionTool.TagSetElementName}')) END " ); return(result); }
/// <summary> /// Builds the name of the put stored procedure. /// </summary> /// <param name="streamName">Name of the stream.</param> /// <param name="asAlter">An optional value indicating whether or not to generate a ALTER versus CREATE; DEFAULT is false and will generate a CREATE script.</param> /// <returns>Creation script for creating the stored procedure.</returns> public static string BuildCreationScript( string streamName, bool asAlter = false) { var createOrModify = asAlter ? "ALTER" : "CREATE"; const string recordIdsToConsiderTable = "RecordIdsToConsiderTable"; const string mostRecentMatchingRecordId = "MostRecentMatchingRecordId"; var result = Invariant( $@" {createOrModify} PROCEDURE [{streamName}].[{GetLatestStringSerializedObject.Name}]( @{InputParamName.InternalRecordIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IdentifierTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.ObjectTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.StringIdentifiersXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.TagIdsToMatchCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.TagMatchStrategy} {new StringSqlDataTypeRepresentation(false, 40).DeclarationInSqlSyntax} , @{InputParamName.VersionMatchStrategy} {new StringSqlDataTypeRepresentation(false, 20).DeclarationInSqlSyntax} , @{InputParamName.DeprecatedIdEventTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{OutputParamName.StringSerializedObject} AS {new StringSqlDataTypeRepresentation(true, StringSqlDataTypeRepresentation.MaxUnicodeLengthConstant).DeclarationInSqlSyntax} OUTPUT ) AS BEGIN {RecordFilterLogic.BuildRecordFilterToBuildRecordsToConsiderTable(streamName, recordIdsToConsiderTable)} DECLARE @{mostRecentMatchingRecordId} {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} SELECT @{mostRecentMatchingRecordId} = (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} ORDER BY [{Tables.Record.Id.Name}] DESC) SELECT @{OutputParamName.StringSerializedObject} = [{Tables.Record.StringSerializedObject.Name}] FROM [{streamName}].[{Tables.Record.Table.Name}] r WITH (NOLOCK) WHERE r.[{Tables.Record.Id.Name}] = @{mostRecentMatchingRecordId} END"); return(result); }
/// <summary> /// Builds the name of the put stored procedure. /// </summary> /// <param name="streamName">Name of the stream.</param> /// <param name="asAlter">An optional value indicating whether or not to generate a ALTER versus CREATE; DEFAULT is false and will generate a CREATE script.</param> /// <returns>Creation script for creating the stored procedure.</returns> public static string BuildCreationScript( string streamName, bool asAlter = false) { const string streamBlockedStatus = "StreamBlockedStatus"; const string recordIdsToConsiderTable = "RecordIdsToConsiderTable"; var createOrModify = asAlter ? "ALTER" : "CREATE"; var result = Invariant( $@" {createOrModify} PROCEDURE [{streamName}].[{Name}]( @{InputParamName.Concern} {Tables.Handling.Concern.SqlDataType.DeclarationInSqlSyntax} , @{InputParamName.InternalRecordIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IdentifierTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.ObjectTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.StringIdentifiersXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.TagIdsToMatchCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.TagMatchStrategy} {new StringSqlDataTypeRepresentation(false, 40).DeclarationInSqlSyntax} , @{InputParamName.VersionMatchStrategy} {new StringSqlDataTypeRepresentation(false, 20).DeclarationInSqlSyntax} , @{InputParamName.DeprecatedIdEventTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{OutputParamName.RecordIdHandlingStatusXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} OUTPUT ) AS BEGIN {RecordFilterLogic.BuildRecordFilterToBuildRecordsToConsiderTable(streamName, recordIdsToConsiderTable)} DECLARE @{streamBlockedStatus} {Tables.Handling.Status.SqlDataType.DeclarationInSqlSyntax} SELECT TOP 1 @{streamBlockedStatus} = [{Tables.Handling.Status.Name}] FROM [{streamName}].[{Tables.Handling.Table.Name}] WHERE [{Tables.Handling.Concern.Name}] = '{Concerns.StreamHandlingDisabledConcern}' IF(@{streamBlockedStatus} = '{HandlingStatus.DisabledForStream}') BEGIN SELECT @{OutputParamName.RecordIdHandlingStatusXml} = ( SELECT rids.[{Tables.Record.Id.Name}] AS [@{TagConversionTool.TagEntryKeyAttributeName}] , '{HandlingStatus.DisabledForStream}' AS [@{TagConversionTool.TagEntryValueAttributeName}] FROM @{recordIdsToConsiderTable} rids ORDER BY rids.[{Tables.Record.Id.Name}] FOR XML PATH ('{TagConversionTool.TagEntryElementName}'), ROOT('{TagConversionTool.TagSetElementName}') ) END ELSE BEGIN SELECT @{OutputParamName.RecordIdHandlingStatusXml} = ( SELECT rids.[{Tables.Record.Id.Name}] AS [@{TagConversionTool.TagEntryKeyAttributeName}] , ISNULL(h.[{Tables.Handling.Status.Name}], '{HandlingStatus.AvailableByDefault}') AS [@{TagConversionTool.TagEntryValueAttributeName}] FROM @{recordIdsToConsiderTable} rids LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h ON rids.[{Tables.Record.Id.Name}] = h.[{Tables.Handling.RecordId.Name}] AND (h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} OR h.[{Tables.Handling.Concern.Name}] = '{Concerns.RecordHandlingDisabledConcern}') LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] AND (h1.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} OR h1.[{Tables.Handling.Concern.Name}] = '{Concerns.RecordHandlingDisabledConcern}') WHERE h1.[{Tables.Handling.Id.Name}] IS NULL ORDER BY h.[{Tables.Handling.Id.Name}] FOR XML PATH ('{TagConversionTool.TagEntryElementName}'), ROOT('{TagConversionTool.TagSetElementName}') ) END END"); return(result); }
/// <summary> /// Builds the creation script for put sproc. /// </summary> /// <param name="streamName">Name of the stream.</param> /// <param name="maxConcurrentHandlingCount">The optional maximum concurrent handling count; DEFAULT is no limit.</param> /// <param name="asAlter">An optional value indicating whether or not to generate a ALTER versus CREATE; DEFAULT is false and will generate a CREATE script.</param> /// <returns>Creation script for creating the stored procedure.</returns> public static string BuildCreationScript( string streamName, int?maxConcurrentHandlingCount, bool asAlter = false) { const string recordIdsToConsiderTable = "RecordIdsToConsiderTable"; const string recordIdToAttemptToClaim = "RecordIdToAttemptToClaim"; const string candidateRecordIds = "CandidateRecordIds"; const string streamBlockedStatus = "StreamBlockedStatus"; const string currentRunningCount = "CurrentRunningCount"; const string isUnhandledRecord = "IsUnhandledRecord"; var acceptableStatusesCsv = new[] { HandlingStatus.AvailableByDefault.ToString(), HandlingStatus.AvailableAfterSelfCancellation.ToString(), HandlingStatus.AvailableAfterExternalCancellation.ToString(), HandlingStatus.AvailableAfterFailure.ToString(), }.ToCsv(); var shouldAttemptHandling = "ShouldAttemptHandling"; var concurrentCheckBlock = maxConcurrentHandlingCount == null ? string.Empty : Invariant($@" DECLARE @{currentRunningCount} INT SELECT @{currentRunningCount} = COUNT(*) FROM [{streamName}].[{Tables.Handling.Table.Name}] h LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] WHERE h1.[{Tables.Handling.Status.Name}] IS NULL AND h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.Running}' IF (@{currentRunningCount} >= {maxConcurrentHandlingCount}) BEGIN SET @{shouldAttemptHandling} = 0 END "); var createOrModify = asAlter ? "ALTER" : "CREATE"; var result = Invariant($@" {createOrModify} PROCEDURE [{streamName}].[{TryHandleRecord.Name}]( @{InputParamName.Concern} AS {Tables.Handling.Concern.SqlDataType.DeclarationInSqlSyntax} , @{InputParamName.Details} AS {Tables.Handling.Details.SqlDataType.DeclarationInSqlSyntax} , @{InputParamName.InternalRecordIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IdentifierTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.ObjectTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.StringIdentifiersXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.TagIdsToMatchCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.TagMatchStrategy} {new StringSqlDataTypeRepresentation(false, 40).DeclarationInSqlSyntax} , @{InputParamName.VersionMatchStrategy} {new StringSqlDataTypeRepresentation(false, 20).DeclarationInSqlSyntax} , @{InputParamName.DeprecatedIdEventTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.OrderRecordsBy} AS {new StringSqlDataTypeRepresentation(false, 50).DeclarationInSqlSyntax} , @{InputParamName.TagIdsForEntryCsv} AS {Tables.Record.TagIdsCsv.SqlDataType.DeclarationInSqlSyntax} , @{InputParamName.InheritRecordTags} AS {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.MinimumInternalRecordId} AS {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} , @{InputParamName.IncludePayload} {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{OutputParamName.Id} AS {Tables.Handling.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.InternalRecordId} AS {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.SerializerRepresentationId} AS {Tables.SerializerRepresentation.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.IdentifierTypeWithVersionId} AS {Tables.TypeWithoutVersion.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.ObjectTypeWithVersionId} AS {Tables.TypeWithoutVersion.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.StringSerializedId} AS {Tables.Record.StringSerializedId.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.StringSerializedObject} AS {Tables.Record.StringSerializedObject.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.BinarySerializedObject} AS {Tables.Record.BinarySerializedObject.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.ObjectDateTime} AS {Tables.Record.ObjectDateTimeUtc.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.RecordDateTime} AS {Tables.Record.RecordCreatedUtc.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.TagIdsCsv} AS {Tables.Record.TagIdsCsv.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.ShouldHandle} AS {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.IsBlocked} AS {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax} OUTPUT ) AS BEGIN DECLARE @{streamBlockedStatus} {Tables.Handling.Status.SqlDataType.DeclarationInSqlSyntax} SELECT TOP 1 @{streamBlockedStatus} = [{Tables.Handling.Status.Name}] FROM [{streamName}].[{Tables.Handling.Table.Name}] WHERE [{Tables.Handling.Concern.Name}] = '{Concerns.StreamHandlingDisabledConcern}' -- Check if global handling block has been applied DECLARE @{shouldAttemptHandling} BIT IF(@{streamBlockedStatus} = '{HandlingStatus.DisabledForStream}') BEGIN SET @{OutputParamName.IsBlocked} = 1 END ELSE BEGIN SET @{shouldAttemptHandling} = 1 SET @{OutputParamName.IsBlocked} = 0 END {concurrentCheckBlock} IF (@{shouldAttemptHandling} = 1) BEGIN {RecordFilterLogic.BuildRecordFilterToBuildRecordsToConsiderTable(streamName, recordIdsToConsiderTable)} DELETE FROM @{recordIdsToConsiderTable} WHERE [{Tables.Record.Id.Name}] IN ( SELECT DISTINCT h.[{Tables.Handling.RecordId.Name}] FROM [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 WITH (NOLOCK) -- the most recent handling status is disabled ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] WHERE h1.[{Tables.Handling.Id.Name}] IS NULL AND h.[{Tables.Handling.Concern.Name}] = '{Concerns.RecordHandlingDisabledConcern}' AND h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.DisabledForRecord}' ) DECLARE @{candidateRecordIds} TABLE([{Tables.Record.Id.Name}] {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} NOT NULL) DECLARE @{isUnhandledRecord} {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax} IF (@{InputParamName.OrderRecordsBy} = '{OrderRecordsBy.InternalRecordIdAscending}') BEGIN -- See if any reprocessing is needed INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc INNER JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] AND h1.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} WHERE h1.[{Tables.Handling.Id.Name}] IS NULL AND ( h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterFailure}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterExternalCancellation}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterSelfCancellation}') -- See if any new records IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN -- Found records to reprocess SET @{isUnhandledRecord} = 0 END ELSE BEGIN INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} WHERE h.[{Tables.Handling.Id.Name}] IS NULL IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN -- Found new records to process SET @{isUnhandledRecord} = 1 END END -- Check for new records END -- If ascending ELSE IF (@{InputParamName.OrderRecordsBy} = '{OrderRecordsBy.InternalRecordIdDescending}') BEGIN -- See if any new records INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} WHERE h.[{Tables.Handling.Id.Name}] IS NULL -- See if any reprocessing is needed IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN SET @{isUnhandledRecord} = 1 END ELSE BEGIN INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc INNER JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] WHERE h1.[{Tables.Handling.Id.Name}] IS NULL AND ( h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterFailure}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterExternalCancellation}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterSelfCancellation}') IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN SET @{isUnhandledRecord} = 0 END END -- Check for re-run END -- Descending ELSE IF (@{InputParamName.OrderRecordsBy} = '{OrderRecordsBy.Random}') BEGIN -- Choose to handle old or new first IF (RAND() > .5) BEGIN -- See if any new records INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} WHERE h.[{Tables.Handling.Id.Name}] IS NULL -- See if any reprocessing is needed IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN SET @{isUnhandledRecord} = 1 END ELSE BEGIN INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc INNER JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] WHERE h1.[{Tables.Handling.Id.Name}] IS NULL AND ( h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterFailure}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterExternalCancellation}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterSelfCancellation}') IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN SET @{isUnhandledRecord} = 0 END END -- Check for re-run END -- New First ELSE BEGIN -- See if any reprocessing is needed INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc INNER JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h1 WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = h1.[{Tables.Handling.RecordId.Name}] AND h.[{Tables.Handling.Id.Name}] < h1.[{Tables.Handling.Id.Name}] WHERE h1.[{Tables.Handling.Id.Name}] IS NULL AND ( h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterFailure}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterExternalCancellation}' OR h.[{Tables.Handling.Status.Name}] = '{HandlingStatus.AvailableAfterSelfCancellation}') -- See if any new records IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN -- Found records to reprocess SET @{isUnhandledRecord} = 0 END ELSE BEGIN INSERT INTO @{candidateRecordIds} SELECT rtc.[{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} rtc LEFT JOIN [{streamName}].[{Tables.Handling.Table.Name}] h WITH (NOLOCK) ON h.[{Tables.Handling.RecordId.Name}] = rtc.[{Tables.Record.Id.Name}] AND h.[{Tables.Handling.Concern.Name}] = @{InputParamName.Concern} WHERE h.[{Tables.Handling.Id.Name}] IS NULL IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN -- Found new records to process SET @{isUnhandledRecord} = 1 END END -- Check for new records END -- Retry First END -- Random ELSE BEGIN DECLARE @NotValidStrategyErrorMessage varchar(100) SET @NotValidStrategyErrorMessage = CONCAT('Invalid {InputParamName.OrderRecordsBy}: ', @{InputParamName.OrderRecordsBy}, '.'); THROW 60000, @NotValidStrategyErrorMessage, 1 END IF EXISTS (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{candidateRecordIds}) BEGIN DECLARE @{recordIdToAttemptToClaim} {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} -- TODO: add logic here, loop through candidates until we have one or out of options... IF (@{InputParamName.OrderRecordsBy} = '{OrderRecordsBy.InternalRecordIdAscending}') BEGIN SELECT TOP 1 @{recordIdToAttemptToClaim} = [{Tables.Record.Id.Name}] FROM @{candidateRecordIds} ORDER BY [{Tables.Record.Id.Name}] ASC END ELSE IF (@{InputParamName.OrderRecordsBy} = '{OrderRecordsBy.InternalRecordIdDescending}') BEGIN SELECT TOP 1 @{recordIdToAttemptToClaim} = [{Tables.Record.Id.Name}] FROM @{candidateRecordIds} ORDER BY [{Tables.Record.Id.Name}] DESC END ELSE IF (@{InputParamName.OrderRecordsBy} = '{OrderRecordsBy.Random}') BEGIN SELECT TOP 1 @{recordIdToAttemptToClaim} = [{Tables.Record.Id.Name}] FROM @{candidateRecordIds} ORDER BY NEWID() END ELSE BEGIN DECLARE @NotValidStrategyClaimErrorMessage varchar(100) SET @NotValidStrategyClaimErrorMessage = CONCAT('Invalid {InputParamName.OrderRecordsBy}: ', @{InputParamName.OrderRecordsBy}, '.'); THROW 60000, @NotValidStrategyClaimErrorMessage, 1 END EXEC [{streamName}].[{PutHandling.Name}] @{PutHandling.InputParamName.Concern} = @{InputParamName.Concern}, @{PutHandling.InputParamName.Details} = @{InputParamName.Details}, @{PutHandling.InputParamName.RecordId} = @{recordIdToAttemptToClaim}, @{PutHandling.InputParamName.NewStatus} = '{HandlingStatus.Running}', @{PutHandling.InputParamName.AcceptableCurrentStatusesCsv} = '{acceptableStatusesCsv}', @{PutHandling.InputParamName.TagIdsForEntryCsv} = @{InputParamName.TagIdsForEntryCsv}, @{PutHandling.InputParamName.InheritRecordTags} = @{InputParamName.InheritRecordTags}, @{PutHandling.InputParamName.IsUnHandledRecord} = @{isUnhandledRecord}, @{PutHandling.InputParamName.IsClaimingRecordId} = 1, @{PutHandling.OutputParamName.Id} = @{OutputParamName.Id} OUTPUT IF (@{OutputParamName.Id} IS NULL) BEGIN SET @{OutputParamName.ShouldHandle} = 0 END ELSE BEGIN SET @{OutputParamName.ShouldHandle} = 1 SET @{OutputParamName.InternalRecordId} = @{recordIdToAttemptToClaim} END END -- Found record END -- Should attempt handling IF (@{OutputParamName.ShouldHandle} = 1) BEGIN -- Fetch record to handle to output params SELECT TOP 1 @{OutputParamName.SerializerRepresentationId} = [{Tables.Record.SerializerRepresentationId.Name}] , @{OutputParamName.IdentifierTypeWithVersionId} = [{Tables.Record.IdentifierTypeWithVersionId.Name}] , @{OutputParamName.ObjectTypeWithVersionId} = [{Tables.Record.ObjectTypeWithVersionId.Name}] , @{OutputParamName.StringSerializedId} = [{Tables.Record.StringSerializedId.Name}] , @{OutputParamName.StringSerializedObject} = ( CASE @{InputParamName.IncludePayload} WHEN 1 THEN [{Tables.Record.StringSerializedObject.Name}] WHEN 0 THEN NULL ELSE CONVERT({new StringSqlDataTypeRepresentation(false, 1).DeclarationInSqlSyntax}, CONVERT({new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax}, '@{InputParamName.IncludePayload} is used as a bit flag and should only be 1 or 0.')) END) , @{OutputParamName.BinarySerializedObject} = ( CASE @{InputParamName.IncludePayload} WHEN 1 THEN [{Tables.Record.BinarySerializedObject.Name}] WHEN 0 THEN NULL ELSE CONVERT({new BinarySqlDataTypeRepresentation(1).DeclarationInSqlSyntax}, CONVERT({new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax}, '@{InputParamName.IncludePayload} is used as a bit flag and should only be 1 or 0.')) END) , @{OutputParamName.TagIdsCsv} = [{Tables.Record.TagIdsCsv.Name}] , @{OutputParamName.RecordDateTime} = [{Tables.Record.RecordCreatedUtc.Name}] , @{OutputParamName.ObjectDateTime} = [{Tables.Record.ObjectDateTimeUtc.Name}] FROM [{streamName}].[{Tables.Record.Table.Name}] WHERE [{Tables.Record.Id.Name}] = @{OutputParamName.InternalRecordId} END ELSE BEGIN SET @{OutputParamName.ShouldHandle} = 0 SET @{OutputParamName.Id} = {Tables.Handling.NullId} SET @{OutputParamName.InternalRecordId} = {Tables.Record.NullId} SET @{OutputParamName.SerializerRepresentationId} = {Tables.SerializerRepresentation.NullId} SET @{OutputParamName.IdentifierTypeWithVersionId} = {Tables.TypeWithVersion.NullId} SET @{OutputParamName.ObjectTypeWithVersionId} = {Tables.TypeWithVersion.NullId} SET @{OutputParamName.StringSerializedId} = 'Fake' SET @{OutputParamName.ObjectDateTime} = GETUTCDATE() SET @{OutputParamName.RecordDateTime} = GETUTCDATE() SET @{OutputParamName.TagIdsCsv} = null END END "); return(result); }
public static string BuildCreationScript( string streamName, bool asAlter = false) { var createOrModify = asAlter ? "ALTER" : "CREATE"; const string recordIdsToConsiderTable = "RecordIdsToConsiderTable"; const string mostRecentMatchingRecordId = "MostRecentMatchingRecordId"; var result = Invariant( $@" {createOrModify} PROCEDURE [{streamName}].[{GetLatestRecord.Name}]( @{InputParamName.InternalRecordIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IdentifierTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.ObjectTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.StringIdentifiersXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{InputParamName.TagIdsToMatchCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.TagMatchStrategy} {new StringSqlDataTypeRepresentation(false, 40).DeclarationInSqlSyntax} , @{InputParamName.VersionMatchStrategy} {new StringSqlDataTypeRepresentation(false, 20).DeclarationInSqlSyntax} , @{InputParamName.DeprecatedIdEventTypeIdsCsv} {new StringSqlDataTypeRepresentation(false, StringSqlDataTypeRepresentation.MaxNonUnicodeLengthConstant).DeclarationInSqlSyntax} , @{InputParamName.IncludePayload} {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax} , @{OutputParamName.InternalRecordId} AS {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.SerializerRepresentationId} AS {Tables.SerializerRepresentation.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.IdentifierTypeWithVersionId} AS {Tables.TypeWithVersion.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.ObjectTypeWithVersionId} AS {Tables.TypeWithVersion.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.StringSerializedId} AS {Tables.Record.StringSerializedId.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.StringSerializedObject} AS {Tables.Record.StringSerializedObject.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.BinarySerializedObject} AS {Tables.Record.BinarySerializedObject.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.RecordDateTime} AS {Tables.Record.RecordCreatedUtc.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.ObjectDateTime} AS {Tables.Record.ObjectDateTimeUtc.SqlDataType.DeclarationInSqlSyntax} OUTPUT , @{OutputParamName.TagIdsCsv} AS {Tables.Record.TagIdsCsv.SqlDataType.DeclarationInSqlSyntax} OUTPUT ) AS BEGIN {RecordFilterLogic.BuildRecordFilterToBuildRecordsToConsiderTable(streamName, recordIdsToConsiderTable)} DECLARE @{mostRecentMatchingRecordId} {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} SELECT @{mostRecentMatchingRecordId} = (SELECT TOP 1 [{Tables.Record.Id.Name}] FROM @{recordIdsToConsiderTable} ORDER BY [{Tables.Record.Id.Name}] DESC) SELECT TOP 1 @{OutputParamName.SerializerRepresentationId} = [{Tables.Record.SerializerRepresentationId.Name}] , @{OutputParamName.IdentifierTypeWithVersionId} = [{Tables.Record.IdentifierTypeWithVersionId.Name}] , @{OutputParamName.ObjectTypeWithVersionId} = [{Tables.Record.ObjectTypeWithVersionId.Name}] , @{OutputParamName.StringSerializedId} = [{Tables.Record.StringSerializedId.Name}] , @{OutputParamName.StringSerializedObject} = ( CASE @{InputParamName.IncludePayload} WHEN 1 THEN [{Tables.Record.StringSerializedObject.Name}] WHEN 0 THEN NULL ELSE CONVERT({new StringSqlDataTypeRepresentation(false, 1).DeclarationInSqlSyntax}, CONVERT({new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax}, '@{InputParamName.IncludePayload} is used as a bit flag and should only be 1 or 0.')) END) , @{OutputParamName.BinarySerializedObject} = ( CASE @{InputParamName.IncludePayload} WHEN 1 THEN [{Tables.Record.BinarySerializedObject.Name}] WHEN 0 THEN NULL ELSE CONVERT({new BinarySqlDataTypeRepresentation(1).DeclarationInSqlSyntax}, CONVERT({new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax}, '@{InputParamName.IncludePayload} is used as a bit flag and should only be 1 or 0.')) END) , @{OutputParamName.InternalRecordId} = [{Tables.Record.Id.Name}] , @{OutputParamName.TagIdsCsv} = [{Tables.Record.TagIdsCsv.Name}] , @{OutputParamName.RecordDateTime} = [{Tables.Record.RecordCreatedUtc.Name}] , @{OutputParamName.ObjectDateTime} = [{Tables.Record.ObjectDateTimeUtc.Name}] FROM [{streamName}].[{Tables.Record.Table.Name}] WHERE [{Tables.Record.Id.Name}] = @{mostRecentMatchingRecordId} IF (@{OutputParamName.InternalRecordId} IS NULL) BEGIN SET @{OutputParamName.SerializerRepresentationId} = {Tables.SerializerRepresentation.NullId} SET @{OutputParamName.IdentifierTypeWithVersionId} = {Tables.TypeWithVersion.NullId} SET @{OutputParamName.ObjectTypeWithVersionId} = {Tables.TypeWithVersion.NullId} SET @{OutputParamName.InternalRecordId} = {Tables.Record.NullId} SET @{OutputParamName.ObjectDateTime} = NULL SET @{OutputParamName.TagIdsCsv} = NULL SET @{OutputParamName.RecordDateTime} = GETUTCDATE() END END " ); return(result); }