public async Task Table_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);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
localOrchestrator.OnTableCreating(ttca =>
{
var addingID = Environment.NewLine + $"ALTER TABLE {ttca.TableName.Schema().Quoted()} ADD internal_id int identity(1,1)";
ttca.Command.CommandText += addingID;
onCreating = true;
});
localOrchestrator.OnTableCreated(ttca =>
{
onCreated = true;
});
var isCreated = await localOrchestrator.CreateTableAsync(table);
Assert.True(isCreated);
Assert.True(onCreating);
Assert.True(onCreated);
// Check we have a new column in tracking table
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var cols = await SqlManagementUtils.GetColumnsForTableAsync(c, null, "Product", "SalesLT").ConfigureAwait(false);
Assert.Equal(4, cols.Rows.Count);
Assert.NotNull(cols.Rows.FirstOrDefault(r => r["name"].ToString() == "internal_id"));
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Table_Drop_One_Cancel()
{
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);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
// Overwrite existing table with this new one
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
// Call create a first time to have an existing table
var isCreated = await localOrchestrator.CreateTableAsync(table);
Assert.True(isCreated);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
var onDropping = false;
var onDropped = false;
localOrchestrator.OnTableCreating(ttca => onCreating = true);
localOrchestrator.OnTableCreated(ttca => onCreated = true);
localOrchestrator.OnTableDropped(ttca => onDropped = true);
localOrchestrator.OnTableDropping(ttca =>
{
ttca.Cancel = true;
onDropping = true;
});
var isDropped = await localOrchestrator.DropTableAsync(setup.Tables[0]);
Assert.True(onDropping);
Assert.False(isDropped);
Assert.False(onDropped);
Assert.False(onCreating);
Assert.False(onCreated);
using (var c = new SqlConnection(cs))
{
await c.OpenAsync().ConfigureAwait(false);
var stable = await SqlManagementUtils.GetTableAsync(c, null, "Product", "SalesLT").ConfigureAwait(false);
Assert.Single(stable.Rows);
c.Close();
}
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Table_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);
var options = new SyncOptions();
var setup = new SyncSetup(new string[] { "SalesLT.Product" });
// Overwrite existing table with this new one
var table = new SyncTable("Product", "SalesLT");
var colID = new SyncColumn("ID", typeof(Guid));
var colName = new SyncColumn("Name", typeof(string));
table.Columns.Add(colID);
table.Columns.Add(colName);
table.Columns.Add("Number", typeof(int));
table.PrimaryKeys.Add("ID");
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
// Call create a first time to have an existing table
var isCreated = await localOrchestrator.CreateTableAsync(table);
// Ensuring we have a clean new instance
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = false;
var onCreated = false;
var onDropping = false;
var onDropped = false;
localOrchestrator.OnTableCreating(ttca => onCreating = true);
localOrchestrator.OnTableCreated(ttca => onCreated = true);
localOrchestrator.OnTableDropping(ttca => onDropping = true);
localOrchestrator.OnTableDropped(ttca => onDropped = true);
isCreated = await localOrchestrator.CreateTableAsync(table);
Assert.False(isCreated);
Assert.False(onDropping);
Assert.False(onDropped);
Assert.False(onCreating);
Assert.False(onCreated);
onCreating = false;
onCreated = false;
onDropping = false;
onDropped = false;
isCreated = await localOrchestrator.CreateTableAsync(table, true);
Assert.True(isCreated);
Assert.True(onDropping);
Assert.True(onDropped);
Assert.True(onCreating);
Assert.True(onCreated);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Table_Create_All()
{
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);
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.ProductCategory", "SalesLT.ProductModel", "SalesLT.Product", "dbo.Sql", "Posts" });
var localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var schema = await localOrchestrator.GetSchemaAsync();
// new empty db
dbName = HelperDatabase.GetRandomName("tcp_lo_");
await HelperDatabase.CreateDatabaseAsync(ProviderType.Sql, dbName, true);
cs = HelperDatabase.GetConnectionString(ProviderType.Sql, dbName);
sqlProvider = new SqlSyncProvider(cs);
localOrchestrator = new LocalOrchestrator(sqlProvider, options, setup);
var onCreating = 0;
var onCreated = 0;
var onDropping = 0;
var onDropped = 0;
localOrchestrator.OnTableCreating(ttca => onCreating++);
localOrchestrator.OnTableCreated(ttca => onCreated++);
localOrchestrator.OnTableDropping(ttca => onDropping++);
localOrchestrator.OnTableDropped(ttca => onDropped++);
await localOrchestrator.CreateTablesAsync(schema);
Assert.Equal(5, onCreating);
Assert.Equal(5, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
onCreating = 0;
onCreated = 0;
onDropping = 0;
onDropped = 0;
await localOrchestrator.CreateTablesAsync(schema);
Assert.Equal(0, onCreating);
Assert.Equal(0, onCreated);
Assert.Equal(0, onDropping);
Assert.Equal(0, onDropped);
await localOrchestrator.CreateTablesAsync(schema, true);
Assert.Equal(5, onCreating);
Assert.Equal(5, onCreated);
Assert.Equal(5, onDropping);
Assert.Equal(5, onDropped);
HelperDatabase.DropDatabase(ProviderType.Sql, dbName);
}
public async Task Scenario_Using_ExistingClientDatabase_ProvisionDeprovision_WithoutAccessToServerSide(SyncOptions options)
{
// This test works only if we have the same exact provider on both sides
// create client orchestrator that is the same as server
var clientDatabaseName = HelperDatabase.GetRandomName("tcpfilt_cli_");
var clientProvider = this.CreateProvider(this.ServerType, clientDatabaseName);
var client = (clientDatabaseName, Server.ProviderType, Provider : clientProvider);
// create a client schema without seeding
await this.EnsureDatabaseSchemaAndSeedAsync(client, false, UseFallbackSchema);
// Since we don't have access to remote orchestrator,
// we can simulate a server scope
var localOrchestrator = new LocalOrchestrator(client.Provider, options);
// Get the local scope
var localScopeInfo = await localOrchestrator.GetClientScopeInfoAsync();
// getting local scope did not get the schema
var schema = await localOrchestrator.GetSchemaAsync(this.FilterSetup);
// getting local schema from these provider will not fill the schema name for each table
// and we need the exact same name even if it's not used on client
if (client.ProviderType == ProviderType.MySql || client.ProviderType == ProviderType.MariaDB || client.ProviderType == ProviderType.Sqlite)
{
foreach (var table in schema.Tables)
{
var setupTable = this.FilterSetup.Tables.First(t => t.TableName == table.TableName);
table.SchemaName = setupTable.SchemaName;
}
}
// Simulate a server scope
var serverScope = new ServerScopeInfo
{
Name = localScopeInfo.Name,
Schema = schema,
Setup = this.FilterSetup,
Version = localScopeInfo.Version
};
// just check interceptor
var onTableCreatedCount = 0;
localOrchestrator.OnTableCreated(args => onTableCreatedCount++);
// Provision the database with all tracking tables, stored procedures, triggers and scope
var clientScope = await localOrchestrator.ProvisionAsync(serverScope);
//--------------------------
// ASSERTION
//--------------------------
// check if scope table is correctly created
var scopeInfoTableExists = await localOrchestrator.ExistScopeInfoTableAsync(clientScope.Name, DbScopeType.Client);
Assert.True(scopeInfoTableExists);
// get the db manager
foreach (var setupTable in this.FilterSetup.Tables)
{
Assert.True(await localOrchestrator.ExistTrackingTableAsync(clientScope, setupTable.TableName, setupTable.SchemaName));
Assert.True(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Delete));
Assert.True(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Insert));
Assert.True(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Update));
if (client.ProviderType == ProviderType.Sql)
{
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkDeleteRows));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkTableType));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkUpdateRows));
}
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteMetadata));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteRow));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.Reset));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChanges));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChanges));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectRow));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.UpdateRow));
// Filters here
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChangesWithFilters));
Assert.True(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChangesWithFilters));
}
//localOrchestrator.OnTableProvisioned(null);
//// Deprovision the database with all tracking tables, stored procedures, triggers and scope
await localOrchestrator.DeprovisionAsync(SyncProvision.StoredProcedures | SyncProvision.Triggers | SyncProvision.ClientScope | SyncProvision.TrackingTable);
// check if scope table is correctly created
scopeInfoTableExists = await localOrchestrator.ExistScopeInfoTableAsync(clientScope.Name, DbScopeType.Client);
Assert.False(scopeInfoTableExists);
// get the db manager
foreach (var setupTable in this.FilterSetup.Tables)
{
Assert.False(await localOrchestrator.ExistTrackingTableAsync(clientScope, setupTable.TableName, setupTable.SchemaName));
Assert.False(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Delete));
Assert.False(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Insert));
Assert.False(await localOrchestrator.ExistTriggerAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbTriggerType.Update));
if (client.ProviderType == ProviderType.Sql)
{
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkDeleteRows));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkTableType));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.BulkUpdateRows));
}
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteMetadata));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.DeleteRow));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.Reset));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChanges));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChanges));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectRow));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.UpdateRow));
// check filters are deleted
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectChangesWithFilters));
Assert.False(await localOrchestrator.ExistStoredProcedureAsync(clientScope, setupTable.TableName, setupTable.SchemaName, DbStoredProcedureType.SelectInitializedChangesWithFilters));
}
}
