private SqlStream GetCreatedSqlStream(
TimeSpan?commandTimeout = null,
RecordTagAssociationManagementStrategy recordTagAssociationManagementStrategy = RecordTagAssociationManagementStrategy.AssociatedDuringPutInSprocInTransaction,
int?maxConcurrentHandlingCount = null,
SerializationFormat defaultSerializationFormat = SerializationFormat.Binary)
{
var sqlServerLocator = GetSqlServerLocator();
var resourceLocatorProtocol = new SingleResourceLocatorProtocols(sqlServerLocator);
var defaultSerializerRepresentation = GetSerializerRepresentation();
var stream = new SqlStream(
this.streamName,
TimeSpan.FromMinutes(1),
commandTimeout ?? TimeSpan.FromMinutes(3),
defaultSerializerRepresentation,
defaultSerializationFormat,
new JsonSerializerFactory(),
resourceLocatorProtocol);
stream.Execute(new StandardCreateStreamOp(stream.StreamRepresentation, ExistingStreamStrategy.Skip));
stream.Execute(new UpdateStreamStoredProceduresOp(recordTagAssociationManagementStrategy, maxConcurrentHandlingCount));
return(stream);
}
/// <summary>
/// Initializes a new instance of the <see cref="UpdateStreamStoredProceduresOp"/> class.
/// </summary>
/// <param name="recordTagAssociationManagementStrategy">OPTIONAL record tag association management strategy. DEFAULT is AssociatedDuringPutInSprocInTransaction."/>.</param>
/// <param name="maxConcurrentHandlingCount">OPTIONAL maximum concurrent handling count. DEFAULT is no limit.</param>
public UpdateStreamStoredProceduresOp(
RecordTagAssociationManagementStrategy recordTagAssociationManagementStrategy = RecordTagAssociationManagementStrategy.AssociatedDuringPutInSprocInTransaction,
int?maxConcurrentHandlingCount = null)
{
maxConcurrentHandlingCount.MustForArg(nameof(maxConcurrentHandlingCount)).BeGreaterThanWhenNotNull((int?)0);
this.RecordTagAssociationManagementStrategy = recordTagAssociationManagementStrategy;
this.MaxConcurrentHandlingCount = maxConcurrentHandlingCount;
}
/// <summary>
/// Builds the name of the put stored procedure.
/// </summary>
/// <param name="streamName">Name of the stream.</param>
/// <param name="recordTagAssociationManagementStrategy">The optional record tag association management strategy; DEFAULT is AssociatedDuringPutInSprocInTransaction."/>.</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,
RecordTagAssociationManagementStrategy recordTagAssociationManagementStrategy,
int?maxConcurrentHandlingCount,
bool asAlter = false)
{
var createOrModify = asAlter ? "ALTER" : "CREATE";
var result = Invariant(
$@"
{createOrModify} PROCEDURE [{streamName}].[{GetStreamDetails.Name}](
@{OutputParamName.DetailsXml} {new XmlSqlDataTypeRepresentation().DeclarationInSqlSyntax} OUTPUT
)
AS
BEGIN
SELECT @{OutputParamName.DetailsXml} = '<Tags><Tag Key=""{VersionKey}"" Value=""{typeof(GetStreamDetails).Assembly.GetName().Version}"" /><Tag Key=""{nameof(UpdateStreamStoredProceduresOp.RecordTagAssociationManagementStrategy)}"" Value=""{recordTagAssociationManagementStrategy}"" /><Tag Key=""{nameof(UpdateStreamStoredProceduresOp.MaxConcurrentHandlingCount)}"" Value=""{maxConcurrentHandlingCount}"" /></Tags>'
END");
return(result);
}
/// <summary>
/// Builds the creation script for put stored procedure.
/// </summary>
/// <param name="streamName">Name of the stream.</param>
/// <param name="recordTagAssociationManagementStrategy">The record tag association management strategy.</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,
RecordTagAssociationManagementStrategy recordTagAssociationManagementStrategy,
bool asAlter = false)
{
const string recordCreatedUtc = "RecordCreatedUtc";
const string existingIdsTable = "ExistingIdsTable";
const string existingIdsCount = "ExistingIdsCount";
const string prunedIdsTable = "PrunedIdsTable";
var transaction = Invariant($"{nameof(PutRecord)}Transaction");
var pruneTransaction = Invariant($"PruneTransaction");
var createOrModify = asAlter ? "ALTER" : "CREATE";
string insertRowsBlock;
switch (recordTagAssociationManagementStrategy)
{
case RecordTagAssociationManagementStrategy.AssociatedDuringPutInSprocInTransaction:
insertRowsBlock = Invariant($@"
BEGIN TRANSACTION [{transaction}]
BEGIN TRY
INSERT INTO [{streamName}].[{Tables.Record.Table.Name}] (
[{nameof(Tables.Record.IdentifierTypeWithoutVersionId)}]
, [{nameof(Tables.Record.IdentifierTypeWithVersionId)}]
, [{nameof(Tables.Record.ObjectTypeWithoutVersionId)}]
, [{nameof(Tables.Record.ObjectTypeWithVersionId)}]
, [{nameof(Tables.Record.SerializerRepresentationId)}]
, [{nameof(Tables.Record.StringSerializedId)}]
, [{nameof(Tables.Record.StringSerializedObject)}]
, [{nameof(Tables.Record.BinarySerializedObject)}]
, [{nameof(Tables.Record.TagIdsCsv)}]
, [{nameof(Tables.Record.ObjectDateTimeUtc)}]
, [{nameof(Tables.Record.RecordCreatedUtc)}]
) VALUES (
@{InputParamName.IdentifierTypeWithoutVersionId}
, @{InputParamName.IdentifierTypeWithVersionId}
, @{InputParamName.ObjectTypeWithoutVersionId}
, @{InputParamName.ObjectTypeWithVersionId}
, @{InputParamName.SerializerRepresentationId}
, @{InputParamName.StringSerializedId}
, @{InputParamName.StringSerializedObject}
, @{InputParamName.BinarySerializedObject}
, @{InputParamName.TagIdsCsv}
, @{InputParamName.ObjectDateTimeUtc}
, @{recordCreatedUtc}
)
SET @{OutputParamName.Id} = SCOPE_IDENTITY()
INSERT INTO [{streamName}].[{Tables.RecordTag.Table.Name}](
[{Tables.RecordTag.RecordId.Name}]
, [{Tables.RecordTag.TagId.Name}]
, [{Tables.RecordTag.RecordCreatedUtc.Name}])
SELECT
@{OutputParamName.Id}
, value AS [{Tables.Tag.Id.Name}]
, @{recordCreatedUtc}
FROM STRING_SPLIT(@{InputParamName.TagIdsCsv}, ',')
COMMIT TRANSACTION [{transaction}]
END TRY
BEGIN CATCH
DECLARE @PruneErrorMessage nvarchar(max),
@PruneErrorSeverity int,
@PruneErrorState int
SELECT @PruneErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @PruneErrorSeverity = ERROR_SEVERITY(), @PruneErrorState = ERROR_STATE()
IF (@@trancount > 0)
BEGIN
ROLLBACK TRANSACTION [{transaction}]
END
RAISERROR (@PruneErrorMessage, @PruneErrorSeverity, @PruneErrorState)
END CATCH" );
break;
case RecordTagAssociationManagementStrategy.AssociatedDuringPutInSprocOutOfTransaction:
insertRowsBlock = Invariant($@"
INSERT INTO [{streamName}].[{Tables.Record.Table.Name}] (
[{nameof(Tables.Record.IdentifierTypeWithoutVersionId)}]
, [{nameof(Tables.Record.IdentifierTypeWithVersionId)}]
, [{nameof(Tables.Record.ObjectTypeWithoutVersionId)}]
, [{nameof(Tables.Record.ObjectTypeWithVersionId)}]
, [{nameof(Tables.Record.SerializerRepresentationId)}]
, [{nameof(Tables.Record.StringSerializedId)}]
, [{nameof(Tables.Record.StringSerializedObject)}]
, [{nameof(Tables.Record.BinarySerializedObject)}]
, [{nameof(Tables.Record.TagIdsCsv)}]
, [{nameof(Tables.Record.ObjectDateTimeUtc)}]
, [{nameof(Tables.Record.RecordCreatedUtc)}]
) VALUES (
@{InputParamName.IdentifierTypeWithoutVersionId}
, @{InputParamName.IdentifierTypeWithVersionId}
, @{InputParamName.ObjectTypeWithoutVersionId}
, @{InputParamName.ObjectTypeWithVersionId}
, @{InputParamName.SerializerRepresentationId}
, @{InputParamName.StringSerializedId}
, @{InputParamName.StringSerializedObject}
, @{InputParamName.BinarySerializedObject}
, @{InputParamName.TagIdsCsv}
, @{InputParamName.ObjectDateTimeUtc}
, @{recordCreatedUtc}
)
SET @{OutputParamName.Id} = SCOPE_IDENTITY()
INSERT INTO [{streamName}].[{Tables.RecordTag.Table.Name}](
[{Tables.RecordTag.RecordId.Name}]
, [{Tables.RecordTag.TagId.Name}]
, [{Tables.RecordTag.RecordCreatedUtc.Name}])
SELECT
@{OutputParamName.Id}
, value AS [{Tables.Tag.Id.Name}]
, @{recordCreatedUtc}
FROM STRING_SPLIT(@{InputParamName.TagIdsCsv}, ',')
");
break;
case RecordTagAssociationManagementStrategy.ExternallyManaged:
insertRowsBlock = Invariant($@"
INSERT INTO [{streamName}].[{Tables.Record.Table.Name}] (
[{nameof(Tables.Record.IdentifierTypeWithoutVersionId)}]
, [{nameof(Tables.Record.IdentifierTypeWithVersionId)}]
, [{nameof(Tables.Record.ObjectTypeWithoutVersionId)}]
, [{nameof(Tables.Record.ObjectTypeWithVersionId)}]
, [{nameof(Tables.Record.SerializerRepresentationId)}]
, [{nameof(Tables.Record.StringSerializedId)}]
, [{nameof(Tables.Record.StringSerializedObject)}]
, [{nameof(Tables.Record.BinarySerializedObject)}]
, [{nameof(Tables.Record.TagIdsCsv)}]
, [{nameof(Tables.Record.ObjectDateTimeUtc)}]
, [{nameof(Tables.Record.RecordCreatedUtc)}]
) VALUES (
@{InputParamName.IdentifierTypeWithoutVersionId}
, @{InputParamName.IdentifierTypeWithVersionId}
, @{InputParamName.ObjectTypeWithoutVersionId}
, @{InputParamName.ObjectTypeWithVersionId}
, @{InputParamName.SerializerRepresentationId}
, @{InputParamName.StringSerializedId}
, @{InputParamName.StringSerializedObject}
, @{InputParamName.BinarySerializedObject}
, @{InputParamName.TagIdsCsv}
, @{InputParamName.ObjectDateTimeUtc}
, @{recordCreatedUtc}
)
SET @{OutputParamName.Id} = SCOPE_IDENTITY()
" );
break;
default:
throw new NotSupportedException(Invariant($"{nameof(RecordTagAssociationManagementStrategy)} '{recordTagAssociationManagementStrategy}' is not supported."));
}
var result = Invariant($@"
{createOrModify} PROCEDURE [{streamName}].[{PutRecord.Name}](
@{InputParamName.SerializerRepresentationId} AS {Tables.SerializerRepresentation.Id.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.IdentifierTypeWithoutVersionId} AS {Tables.TypeWithoutVersion.Id.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.IdentifierTypeWithVersionId} AS {Tables.TypeWithVersion.Id.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.ObjectTypeWithoutVersionId} AS {Tables.TypeWithoutVersion.Id.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.ObjectTypeWithVersionId} AS {Tables.TypeWithVersion.Id.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.InternalRecordId} AS {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.StringSerializedId} AS {Tables.Record.StringSerializedId.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.StringSerializedObject} AS {Tables.Record.StringSerializedObject.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.BinarySerializedObject} AS {Tables.Record.BinarySerializedObject.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.ObjectDateTimeUtc} AS {Tables.Record.ObjectDateTimeUtc.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.TagIdsCsv} AS {Tables.Record.TagIdsCsv.SqlDataType.DeclarationInSqlSyntax}
, @{InputParamName.ExistingRecordStrategy} AS {new StringSqlDataTypeRepresentation(false, 50).DeclarationInSqlSyntax}
, @{InputParamName.RecordRetentionCount} AS {new IntSqlDataTypeRepresentation().DeclarationInSqlSyntax}
, @{InputParamName.VersionMatchStrategy} AS {new StringSqlDataTypeRepresentation(false, 50).DeclarationInSqlSyntax}
, @{OutputParamName.Id} AS {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} OUTPUT
, @{OutputParamName.ExistingRecordIdsCsv} AS {Tables.Record.TagIdsCsv.SqlDataType.DeclarationInSqlSyntax} OUTPUT
, @{OutputParamName.PrunedRecordIdsCsv} AS {Tables.Record.TagIdsCsv.SqlDataType.DeclarationInSqlSyntax} OUTPUT
)
AS
BEGIN
-- If two actors try to both insert for the same ID with the '{nameof(ExistingRecordStrategy)}'
-- set to e.g. {ExistingRecordStrategy.DoNotWriteIfFoundByIdAndTypeAndContent}; they could both
-- write the same payload; this does work in a single actor re-entrant scenario and is the expected usage.
DECLARE @{existingIdsTable} TABLE({Tables.Record.Id.Name} {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} NOT NULL)
DECLARE @{prunedIdsTable} TABLE({Tables.Record.Id.Name} {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax} NOT NULL)
IF (@{InputParamName.ExistingRecordStrategy} <> '{ExistingRecordStrategy.None}')
BEGIN
INSERT INTO @{existingIdsTable}
SELECT [{Tables.Record.Id.Name}] FROM [{streamName}].[{Tables.Record.Table.Name}]
WHERE
([{Tables.Record.StringSerializedId.Name}] = @{InputParamName.StringSerializedId})
AND
(
(
(@{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.DoNotWriteIfFoundById}' OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.ThrowIfFoundById}' OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.PruneIfFoundById}')
)
OR
(
(@{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.DoNotWriteIfFoundByIdAndType}' OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.ThrowIfFoundByIdAndType}' OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.PruneIfFoundByIdAndType}')
AND
(
(
@{InputParamName.VersionMatchStrategy} = '{VersionMatchStrategy.Any}'
AND [{Tables.Record.IdentifierTypeWithoutVersionId.Name}] = @{InputParamName.IdentifierTypeWithoutVersionId}
AND [{Tables.Record.ObjectTypeWithoutVersionId.Name}] = @{InputParamName.ObjectTypeWithoutVersionId}
)
OR
(
@{InputParamName.VersionMatchStrategy} = '{VersionMatchStrategy.SpecifiedVersion}'
AND [{Tables.Record.IdentifierTypeWithVersionId.Name}] = @{InputParamName.IdentifierTypeWithVersionId}
AND [{Tables.Record.ObjectTypeWithVersionId.Name}] = @{InputParamName.ObjectTypeWithVersionId}
)
)
)
OR
(
(@{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.DoNotWriteIfFoundByIdAndTypeAndContent}' OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.ThrowIfFoundByIdAndTypeAndContent}')
AND
(
(
@{InputParamName.VersionMatchStrategy} = '{VersionMatchStrategy.Any}'
AND [{Tables.Record.IdentifierTypeWithoutVersionId.Name}] = @{InputParamName.IdentifierTypeWithoutVersionId}
AND [{Tables.Record.ObjectTypeWithoutVersionId.Name}] = @{InputParamName.ObjectTypeWithoutVersionId}
AND ([{Tables.Record.StringSerializedObject.Name}] = @{InputParamName.StringSerializedObject} OR [{Tables.Record.BinarySerializedObject.Name}] = @{InputParamName.BinarySerializedObject})
)
OR
(
@{InputParamName.VersionMatchStrategy} = '{VersionMatchStrategy.SpecifiedVersion}'
AND [{Tables.Record.IdentifierTypeWithVersionId.Name}] = @{InputParamName.IdentifierTypeWithVersionId}
AND [{Tables.Record.ObjectTypeWithVersionId.Name}] = @{InputParamName.ObjectTypeWithVersionId}
AND ([{Tables.Record.StringSerializedObject.Name}] = @{InputParamName.StringSerializedObject} OR [{Tables.Record.BinarySerializedObject.Name}] = @{InputParamName.BinarySerializedObject})
)
)
)
)
END
IF EXISTS (SELECT TOP 1 * FROM @{existingIdsTable})
BEGIN
SELECT @{OutputParamName.ExistingRecordIdsCsv} = STRING_AGG([{Tables.Tag.Id.Name}], ',') FROM @{existingIdsTable}
END
IF (@{OutputParamName.ExistingRecordIdsCsv} IS NULL OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.PruneIfFoundById}' OR @{InputParamName.ExistingRecordStrategy} = '{ExistingRecordStrategy.PruneIfFoundByIdAndType}')
BEGIN
DECLARE @{recordCreatedUtc} {Tables.Record.RecordCreatedUtc.SqlDataType.DeclarationInSqlSyntax}
SET @RecordCreatedUtc = GETUTCDATE()
{insertRowsBlock}
IF (@{OutputParamName.ExistingRecordIdsCsv} IS NOT NULL)
BEGIN
-- must be a prune scenario to get here as this is checked above...
DECLARE @{existingIdsCount} {Tables.Record.Id.SqlDataType.DeclarationInSqlSyntax}
SELECT @{existingIdsCount} = COUNT(*) FROM @{existingIdsTable}
IF (@{existingIdsCount} >= (@{InputParamName.RecordRetentionCount} - 1))
BEGIN
-- have records to prune
INSERT INTO @{prunedIdsTable}
SELECT TOP (@{existingIdsCount} - @{InputParamName.RecordRetentionCount} + 1)
[{Tables.Record.Id.Name}] FROM @{existingIdsTable}
ORDER BY [{Tables.Record.Id.Name}] ASC
BEGIN TRANSACTION [{pruneTransaction}]
BEGIN TRY
DELETE FROM [{streamName}].[{Tables.HandlingTag.Table.Name}] WHERE [{Tables.HandlingTag.HandlingId.Name}]
IN (
SELECT [{Tables.Handling.Id.Name}] FROM [{streamName}].[{Tables.Handling.Table.Name}]
WHERE [{Tables.Handling.RecordId.Name}] IN (SELECT [{Tables.Tag.Id.Name}] FROM @{prunedIdsTable})
)
DELETE FROM [{streamName}].[{Tables.Handling.Table.Name}]
WHERE [{Tables.Handling.RecordId.Name}] IN (SELECT [{Tables.Tag.Id.Name}] FROM @{prunedIdsTable})
DELETE FROM [{streamName}].[{Tables.RecordTag.Table.Name}]
WHERE [{Tables.RecordTag.RecordId.Name}] IN (SELECT [{Tables.Tag.Id.Name}] FROM @{prunedIdsTable})
DELETE FROM [{streamName}].[{Tables.Record.Table.Name}]
WHERE [{Tables.Record.Id.Name}] IN (SELECT [{Tables.Tag.Id.Name}] FROM @{prunedIdsTable})
COMMIT TRANSACTION [{pruneTransaction}]
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(max),
@ErrorSeverity int,
@ErrorState int
SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
IF (@@trancount > 0)
BEGIN
ROLLBACK TRANSACTION [{pruneTransaction}]
END
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
SELECT @{OutputParamName.PrunedRecordIdsCsv} = STRING_AGG([{Tables.Tag.Id.Name}], ',') FROM @{prunedIdsTable}
END -- have enough records to delete - the actual prune
END -- have existing records - check for pruning
END -- need to insert a record
END
" );
return(result);
}
public UpdateStreamStoredProceduresOp DeepCloneWithRecordTagAssociationManagementStrategy(RecordTagAssociationManagementStrategy recordTagAssociationManagementStrategy)
{
var result = new UpdateStreamStoredProceduresOp(
recordTagAssociationManagementStrategy,
this.MaxConcurrentHandlingCount?.DeepClone());
return(result);
}
