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