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