public async Task RemoteOrchestrator_StoredProcedure_Exists()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
Assert.True(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges));
Assert.False(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.UpdateRow));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_StoredProcedure_ShouldCreate()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
Assert.True(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges));
// Adding a filter to check if stored procedures "with filters" are also generated
setup.Filters.Add("Product", "ProductCategoryID", "SalesLT");
// Create a new scope with this filter
var scopeName2 = "scope2";
var scopeInfo2 = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName2, setup);
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo2, "Product", "SalesLT", DbStoredProcedureType.SelectChangesWithFilters, false);
Assert.True(await remoteOrchestrator.ExistStoredProcedureAsync(scopeInfo2, "Product", "SalesLT", DbStoredProcedureType.SelectChangesWithFilters));
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task RemoteOrchestrator_StoredProcedure_ShouldNotOverwrite()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var scopeName = "scope";
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" })
{
StoredProceduresPrefix = "sp_",
StoredProceduresSuffix = "_sp"
};
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(scopeName, setup);
var storedProcedureSelectChanges = $"SalesLT.{setup.StoredProceduresPrefix}Product{setup.StoredProceduresSuffix}_changes";
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
var assertOverWritten = false;
remoteOrchestrator.OnStoredProcedureCreating(args =>
{
assertOverWritten = true;
});
await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, false);
Assert.False(assertOverWritten);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task StoredProcedure_Create_One()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnStoredProcedureCreating(tca => onCreating++);
remoteOrchestrator.OnStoredProcedureCreated(tca => onCreated++);
remoteOrchestrator.OnStoredProcedureDropping(tca => onDropping++);
remoteOrchestrator.OnStoredProcedureDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo,
"Product", "SalesLT", DbStoredProcedureType.SelectChanges);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
// Check
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var check = await SqlManagementUtils.ProcedureExistsAsync(c, null, "SalesLT.Product_changes").ConfigureAwait(false);
Assert.True(check);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
/// <summary>
/// Create one stored procedure
/// </summary>
private static async Task CreateOneStoredProcedure()
{
var provider = new SqlSyncProvider(serverConnectionString);
var options = new SyncOptions();
var setup = new SyncSetup("ProductCategory", "ProductModel", "Product");
var orchestrator = new RemoteOrchestrator(provider, options);
var serverScope = await orchestrator.GetServerScopeInfoAsync("v1", setup);
var spExists = await orchestrator.ExistStoredProcedureAsync(serverScope, "Product", null, DbStoredProcedureType.SelectChanges);
if (!spExists)
{
await orchestrator.CreateStoredProcedureAsync(serverScope, "Product", null, DbStoredProcedureType.SelectChanges);
}
}
/// <summary>
/// Create one stored procedure
/// </summary>
private static async Task CreateOneStoredProcedure()
{
var provider = new SqlSyncProvider(serverConnectionString);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "ProductCategory", "ProductModel", "Product" });
var orchestrator = new RemoteOrchestrator(provider, options, setup);
// working on the product Table
var productSetupTable = setup.Tables["Product"];
var spExists = await orchestrator.ExistStoredProcedureAsync(productSetupTable, DbStoredProcedureType.SelectChanges);
if (!spExists)
{
await orchestrator.CreateStoredProcedureAsync(productSetupTable, DbStoredProcedureType.SelectChanges);
}
}
public async Task StoredProcedure_Create_One_Overwrite()
{
var dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
var cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
var sqlProvider = new SqlSyncProvider(cs);
// Create default table
var ctx = new AdventureWorksContext((dbName, ProviderType.Sql, sqlProvider), true, false);
await ctx.Database.EnsureCreatedAsync();
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var remoteOrchestrator = new RemoteOrchestrator(sqlProvider, options);
var scopeInfo = await remoteOrchestrator.GetServerScopeInfoAsync(setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
remoteOrchestrator.OnStoredProcedureCreating(tca => onCreating++);
remoteOrchestrator.OnStoredProcedureCreated(tca => onCreated++);
remoteOrchestrator.OnStoredProcedureDropping(tca => onDropping++);
remoteOrchestrator.OnStoredProcedureDropped(tca => onDropped++);
var isCreated = await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
isCreated = await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges);
Assert.False(isCreated);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
isCreated = await remoteOrchestrator.CreateStoredProcedureAsync(scopeInfo, "Product", "SalesLT", DbStoredProcedureType.SelectChanges, true);
Assert.True(isCreated);
Assert.Equal(1, onCreating);
Assert.Equal(1, onCreated);
Assert.Equal(1, onDropping);
Assert.Equal(1, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
