public async Task TableWithForeignKeyButNotParentTable()
{
serverProvider = new MySqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new MySqlSyncProvider(fixture.Client2ConnectionString);
agent = new SyncAgent(clientProvider, serverProvider, new[] {
"ProductCategory",
"Product"
});
var session = await agent.SynchronizeAsync();
Assert.Equal(3, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
int fkeysCount = 0;
using (var sqlConnection = new MySqlConnection(fixture.Client2ConnectionString))
{
using (var sqlCmd = new MySqlCommand("SELECT count(*) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " +
"WHERE TABLE_SCHEMA = @schema AND REFERENCED_TABLE_NAME is not null", sqlConnection))
{
sqlCmd.Parameters.AddWithValue("@schema", fixture.client2DbName);
sqlConnection.Open();
fkeysCount = Convert.ToInt32(sqlCmd.ExecuteScalar());
sqlConnection.Close();
}
}
Assert.Equal(2, fkeysCount);
}
public async Task UpdateFromServer(SyncConfiguration conf)
{
Guid guid = Guid.NewGuid();
var updateRowScript =
$@" Declare @id uniqueidentifier;
Select top 1 @id = ServiceTicketID from ServiceTickets;
Update [ServiceTickets] Set [Title] = 'Updated from server {guid.ToString()}' Where ServiceTicketId = @id";
using (var sqlConnection = new SqlConnection(fixture.ServerConnectionString))
{
using (var sqlCmd = new SqlCommand(updateRowScript, sqlConnection))
{
sqlConnection.Open();
sqlCmd.ExecuteNonQuery();
sqlConnection.Close();
}
}
var serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
var clientProvider = new MySqlSyncProvider(fixture.ClientMySqlConnectionString);
//var simpleConfiguration = new SyncConfiguration(Tables);
var agent = new SyncAgent(clientProvider, serverProvider);
agent.Configuration.DownloadBatchSizeInKB = conf.DownloadBatchSizeInKB;
agent.Configuration.UseBulkOperations = conf.UseBulkOperations;
agent.Configuration.SerializationFormat = conf.SerializationFormat;
agent.Configuration.Add(fixture.Tables);
var session = await agent.SynchronizeAsync();
Assert.Equal(1, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
}
public MySqlAllColumnsTests(MySqlAllColumnsFixture fixture)
{
this.fixture = fixture;
SqlSyncProvider serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
MySqlSyncProvider clientProvider = new MySqlSyncProvider(fixture.ClientMySqlConnectionString);
agent = new SyncAgent(clientProvider, serverProvider, new[] { "AllColumns" });
}
public MySqlSyncIdentityColumnNotPkTests(MySqlSyncIdentityColumnNotPkFixture fixture)
{
this.fixture = fixture;
serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new MySqlSyncProvider(fixture.Client1ConnectionString);
agent = new SyncAgent(clientProvider, serverProvider, fixture.Tables);
}
public MySqlSyncOneTableOneFieldTests(MySqlSyncOneTableOneFieldFixture fixture)
{
this.fixture = fixture;
serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new MySqlSyncProvider(fixture.Client1ConnectionString);
agent = new SyncAgent(clientProvider, serverProvider, fixture.Tables);
}
public static async Task SyncWordPress()
{
var serverConfig = "Server=tlsemysql.mysql.database.azure.com; Port=3306; Database=mysqldatabase165; Uid=spertus@tlsemysql; Pwd=azerty31$; SslMode=Preferred;";
MySqlSyncProvider serverProvider = new MySqlSyncProvider(serverConfig);
//var clientConfig = @"Data Source=(localdb)\MSSQLLocalDB; Initial Catalog=WordPress;Integrated Security=true;";
//SqlSyncProvider clientProvider = new SqlSyncProvider(clientConfig);
var clientConfig = @"wordpress.sqlite";
SQLiteSyncProvider clientProvider = new SQLiteSyncProvider(clientConfig);
// With a config when we are in local mode (no proxy)
var tables = new string[] { "wp_users", "wp_usermeta", "wp_terms", "wp_termmeta", "wp_term_taxonomy",
"wp_term_relationships", "wp_posts", "wp_postmeta", "wp_options", "wp_links",
"wp_comments", "wp_commentmeta" };
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, tables);
// Setting special properties on Configuration tables
//agent.Configuration["wp_users"].SyncDirection = SyncDirection.DownloadOnly;
//agent.Configuration["wp_users"].Schema = "SalesLT";
agent.SyncProgress += SyncProgress;
agent.ApplyChangedFailed += ApplyChangedFailed;
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
CancellationTokenSource cts = new CancellationTokenSource();
CancellationToken token = cts.Token;
var s = await agent.SynchronizeAsync(token);
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
public MySqlSyncPrefixSuffixWithSchemaTests(MySqlSyncPreffixAndSuffixWithSchemaFixture fixture)
{
this.fixture = fixture;
serverProvider = new SqlSyncProvider(fixture.ServerConnectionString);
clientProvider = new MySqlSyncProvider(fixture.Client1ConnectionString);
var simpleConfiguration = new SyncConfiguration();
agent = new SyncAgent(clientProvider, serverProvider, fixture.Tables);
agent.Configuration.StoredProceduresPrefix = "ddl_";
agent.Configuration.StoredProceduresSuffix = "_sync";
agent.Configuration.TrackingTablesPrefix = "sync_";
agent.Configuration.TrackingTablesSuffix = "_tr";
}
public MySqlSyncSimpleFixture()
{
// create databases
helperDb.CreateDatabase(serverDbName);
// create table
helperDb.ExecuteScript(serverDbName, createTableScript);
// insert table
helperDb.ExecuteScript(serverDbName, datas);
helperDb.CreateMySqlDatabase(clientDbName);
var serverProvider = new SqlSyncProvider(ServerConnectionString);
var clientProvider = new MySqlSyncProvider(ClientMySqlConnectionString);
Agent = new SyncAgent(clientProvider, serverProvider, Tables);
}
private static async Task TestMySqlSync()
{
// Get SQL Server connection string
var serverConfig = ConfigurationManager.ConnectionStrings["SqlServerConnectionString"].ConnectionString;
var clientConfig = ConfigurationManager.ConnectionStrings["MySqlLocalClientConnectionString"].ConnectionString;
SqlSyncProvider serverProvider = new SqlSyncProvider(serverConfig);
MySqlSyncProvider clientProvider = new MySqlSyncProvider(clientConfig);
// With a config when we are in local mode (no proxy)
SyncConfiguration configuration = new SyncConfiguration(new string[] { "Customers", "ServiceTickets" });
//configuration.OverwriteConfiguration = true;
//configuration.DownloadBatchSizeInKB = 500;
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, configuration);
agent.SyncProgress += SyncProgress;
agent.ApplyChangedFailed += ApplyChangedFailed;
do
{
Console.Clear();
Console.WriteLine("Sync Start");
try
{
CancellationTokenSource cts = new CancellationTokenSource();
CancellationToken token = cts.Token;
var s = await agent.SynchronizeAsync(token);
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("Sync Ended. Press a key to start again, or Escapte to end");
} while (Console.ReadKey().Key != ConsoleKey.Escape);
Console.WriteLine("End");
}
private void Init()
{
// Servers providers
masterSqlSyncProvider = new SqlSyncProvider(
settingsHelper[ConnectionType.Server_SqlServer]);
webProxyProvider = new WebClientOrchestrator(settingsHelper[ConnectionType.WebProxy]);
// clients providers
sqlSyncProvider = new SqlSyncProvider(
settingsHelper[ConnectionType.Client_SqlServer]);
sqliteSyncProvider = new SqliteSyncProvider(
settingsHelper[ConnectionType.Client_Sqlite]);
mySqlSyncProvider = new MySqlSyncProvider(
settingsHelper[ConnectionType.Client_MySql]);
}
public WordPressFixture()
{
// create client database
helperDb.DropMySqlDatabase(clientDbName);
helperDb.CreateMySqlDatabase(clientDbName);
helperDb.DropMySqlDatabase(serverDbName);
// restore server database
var wordpressscript = Path.Combine(Directory.GetCurrentDirectory(), "Backup", "Wordpress.sql");
var fs = File.OpenText(wordpressscript);
var script = fs.ReadToEnd();
helperDb.ExecuteMySqlScript("sys", script);
var serverProvider = new MySqlSyncProvider(ServerConnectionString);
var clientProvider = new MySqlSyncProvider(ClientMySqlConnectionString);
var simpleConfiguration = new SyncConfiguration(Tables);
Agent = new SyncAgent(clientProvider, serverProvider, simpleConfiguration);
}
/// <summary>
/// Test a client syncing through a web api
/// </summary>
private static async Task TestOracleSyncThroughWebApi()
{
string line = "";
var oracleClientProvider = new OracleSyncProvider(GetOracleDatabaseConnectionString());
var mySqlClientProvider = new MySqlSyncProvider(GetMySQLConnectionBuilder());
var agent = new SyncAgent(mySqlClientProvider, oracleClientProvider, new string[] { "T_PERSONNE", "T_ENFANT" });
agent.Configuration.UseBulkOperations = true;
//agent.Configuration.ScopeSet.Tables["T_PERSONNE"].SyncDirection = SyncDirection.DownloadOnly;
Console.WriteLine("Press a key to start synchronization ... ");
Console.ReadKey();
do
{
Console.Clear();
Console.WriteLine("Sync start");
try
{
var s = await agent.SynchronizeAsync();
Console.WriteLine(s);
}
catch (SyncException e)
{
Console.WriteLine(e.ToString());
}
catch (Exception e)
{
Console.WriteLine("UNKNOW EXCEPTION : " + e.Message);
}
Console.WriteLine("Sync Ended. Press a key to start again, or EXIT to end");
line = Console.ReadLine();
} while (!line.ToUpper().Contains("EXIT"));
Console.WriteLine("End");
}
/// <summary>
/// Syncs local database
/// </summary>
public static async Task<string> SyncAsync(IProgress<ProgressArgs> progress)
{
// Create serverprovider and sync agent
LogManager.log("Creating server provider and synchronization provider for synchronization", LogCategory.info, LogTopic.Database);
MySqlSyncProvider serverProvider = new MySqlSyncProvider(Settings.Settings.MYSQLConnectionString);
SyncAgent agent = new SyncAgent(clientProvider, serverProvider, tables);
SyncResult result = null;
// Synchronize
LogManager.log("Synchronizing data with sync agent", LogCategory.info, LogTopic.Database);
if (progress != null)
{
result = await agent.SynchronizeAsync(progress);
}
else
{
result = await agent.SynchronizeAsync();
}
LogManager.log("Sync-Result: " + result.ToString());
return result.ToString();
}
internal SyncContext Sync(string value)
{
if (String.IsNullOrEmpty(value))
{
throw new Exception("Loading a project requires a name. Ex : dotnet sync --load project01");
}
Project project = DataStore.Current.LoadProject(value);
if (project == null)
{
throw new Exception($"Project {value} does not exists.");
}
if (project.ServerProvider == null || string.IsNullOrEmpty(project.ServerProvider.ConnectionString))
{
throw new Exception($"Server provider for project {project.Name} is not correctly defined. See help: dotnet sync provider --help");
}
if (project.ClientProvider == null || string.IsNullOrEmpty(project.ClientProvider.ConnectionString))
{
throw new Exception($"Client provider for project {project.Name} is not correctly defined. See help: dotnet sync provider --help");
}
if (project.ServerProvider.ProviderType != ProviderType.Web && (project.Tables == null || project.Tables.Count <= 0))
{
throw new Exception($"No table configured for project {project.Name}. See help: dotnet sync table --help");
}
IProvider serverProvider;
CoreProvider clientprovider;
switch (project.ServerProvider.ProviderType)
{
case ProviderType.Sqlite:
throw new Exception("Can't use Sqlite as a server provider");
case ProviderType.Web:
serverProvider = new WebProxyClientProvider(new Uri(project.ServerProvider.ConnectionString));
break;
case ProviderType.MySql:
serverProvider = new MySqlSyncProvider(project.ServerProvider.ConnectionString);
break;
case ProviderType.SqlServer:
default:
serverProvider = new SqlSyncProvider(project.ServerProvider.ConnectionString);
break;
}
switch (project.ClientProvider.ProviderType)
{
case ProviderType.Web:
throw new Exception("Web proxy is used as a proxy server. You have to use an ASP.NET web backend. CLI uses a proxy as server provider");
case ProviderType.Sqlite:
clientprovider = new SqliteSyncProvider(project.ClientProvider.ConnectionString);
break;
case ProviderType.MySql:
clientprovider = new MySqlSyncProvider(project.ClientProvider.ConnectionString);
break;
case ProviderType.SqlServer:
default:
clientprovider = new SqlSyncProvider(project.ClientProvider.ConnectionString);
break;
}
SyncAgent agent = null;
if (project.ServerProvider.ProviderType != ProviderType.Web)
{
agent = new SyncAgent(clientprovider, serverProvider);
var syncConfiguration = agent.LocalProvider.Configuration;
foreach (var t in project.Tables.OrderBy(tbl => tbl.Order))
{
// Potentially user can pass something like [SalesLT].[Product]
// or SalesLT.Product or Product. ParserName will handle it
var parser = ParserName.Parse(t.Name);
var tableName = parser.ObjectName;
var schema = string.IsNullOrEmpty(t.Schema) ? parser.SchemaName : t.Schema;
var dmTable = new DmTable(tableName);
if (!String.IsNullOrEmpty(schema))
{
dmTable.Schema = schema;
}
dmTable.SyncDirection = t.Direction;
syncConfiguration.Add(dmTable);
}
agent.LocalProvider.Options.BatchDirectory = string.IsNullOrEmpty(project.Configuration.BatchDirectory) ? null : project.Configuration.BatchDirectory;
agent.LocalProvider.Options.BatchSize = (int)Math.Min(Int32.MaxValue, project.Configuration.DownloadBatchSizeInKB);
//agent.Options.UseBulkOperations = project.Configuration.UseBulkOperation;
syncConfiguration.SerializationFormat = project.Configuration.SerializationFormat;
syncConfiguration.ConflictResolutionPolicy = project.Configuration.ConflictResolutionPolicy;
}
else
{
agent = new SyncAgent(clientprovider, serverProvider);
}
var interceptor = new Interceptor <TableChangesSelectedArgs, TableChangesAppliedArgs>(
tcs => Console.WriteLine($"Changes selected for table {tcs.TableChangesSelected.TableName}: {tcs.TableChangesSelected.TotalChanges}"),
tca => Console.WriteLine($"Changes applied for table {tca.TableChangesApplied.Table.TableName}: [{tca.TableChangesApplied.State}] {tca.TableChangesApplied.Applied}")
);
agent.SetInterceptor(interceptor);
// synchronous call
var syncContext = agent.SynchronizeAsync().GetAwaiter().GetResult();
var tsEnded = TimeSpan.FromTicks(syncContext.CompleteTime.Ticks);
var tsStarted = TimeSpan.FromTicks(syncContext.StartTime.Ticks);
var durationTs = tsEnded.Subtract(tsStarted);
var durationstr = $"{durationTs.Hours}:{durationTs.Minutes}:{durationTs.Seconds}.{durationTs.Milliseconds}";
Console.ForegroundColor = ConsoleColor.Green;
var s = $"Synchronization done. " + Environment.NewLine +
$"\tTotal changes downloaded: {syncContext.TotalChangesDownloaded} " + Environment.NewLine +
$"\tTotal changes uploaded: {syncContext.TotalChangesUploaded}" + Environment.NewLine +
$"\tTotal duration :{durationstr} ";
Console.WriteLine(s);
Console.ResetColor();
return(syncContext);
}
