private MySqlCommand CreateDeleteCommand()
{
var mySqlCommand = new MySqlCommand();
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"DELETE FROM {this.tableName.Quoted()} WHERE");
stringBuilder.AppendLine($"{MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.GetPrimaryKeysColumns(), "", "@")};");
mySqlCommand.CommandText = stringBuilder.ToString();
return(mySqlCommand);
}
private string CreateUpdateUntrackedRowsCommand()
{
var stringBuilder = new StringBuilder();
var str1 = new StringBuilder();
var str2 = new StringBuilder();
var str3 = new StringBuilder();
var str4 = MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.GetPrimaryKeysColumns(), "`side`", "`base`");
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var comma = "";
foreach (var pkeyColumn in TableDescription.GetPrimaryKeysColumns())
{
var pkeyColumnName = ParserName.Parse(pkeyColumn, "`").Quoted().ToString();
str1.Append($"{comma}{pkeyColumnName}");
str2.Append($"{comma}`base`.{pkeyColumnName}");
str3.Append($"{comma}`side`.{pkeyColumnName}");
comma = ", ";
}
stringBuilder.Append(str1.ToString());
stringBuilder.AppendLine($", `update_scope_id`, `sync_row_is_tombstone`, `timestamp`, `last_change_datetime`");
stringBuilder.AppendLine($")");
stringBuilder.Append($"SELECT ");
stringBuilder.Append(str2.ToString());
stringBuilder.AppendLine($", NULL, 0, {TimestampValue}, now()");
stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} as `base` WHERE NOT EXISTS");
stringBuilder.Append($"(SELECT ");
stringBuilder.Append(str3.ToString());
stringBuilder.AppendLine($" FROM {trackingName.Schema().Quoted().ToString()} as `side` ");
stringBuilder.AppendLine($"WHERE {str4})");
var r = stringBuilder.ToString();
return(r);
}
public async Task InitializeAndSync()
{
// Arrange
// insert 10 test rows
this.fixture.InsertTestData();
// Act
var session = await agent.SynchronizeAsync();
// Assert
Assert.Equal(10, session.TotalChangesDownloaded);
Assert.Equal(0, session.TotalChangesUploaded);
DmTable dmColumnsListServer;
DmTable dmColumnsListClient;
// check if all types are correct
using (var sqlConnection = new SqlConnection(fixture.ServerConnectionString))
{
sqlConnection.Open();
dmColumnsListServer = SqlManagementUtils.ColumnsForTable(sqlConnection, null, "AllColumns");
sqlConnection.Close();
}
using (var mysqlConnection = new MySqlConnection(fixture.ClientMySqlConnectionString))
{
mysqlConnection.Open();
dmColumnsListClient = MySqlManagementUtils.ColumnsForTable(mysqlConnection, null, "allcolumns");
mysqlConnection.Close();
}
// check if all columns are replicated
Assert.Equal(dmColumnsListServer.Rows.Count, dmColumnsListClient.Rows.Count);
// check if all types are correct
foreach (var serverRow in dmColumnsListServer.Rows.OrderBy(r => (int)r["column_id"]))
{
var name = serverRow["name"].ToString();
var ordinal = (int)serverRow["column_id"];
var typeString = serverRow["type"].ToString();
var maxLength = (Int16)serverRow["max_length"];
var precision = (byte)serverRow["precision"];
var scale = (byte)serverRow["scale"];
var isNullable = (bool)serverRow["is_nullable"];
var isIdentity = (bool)serverRow["is_identity"];
var clientRow = dmColumnsListClient.Rows.FirstOrDefault(cr => Convert.ToInt32(cr["ordinal_position"]) == ordinal);
Assert.NotNull(clientRow);
Assert.Equal(name.ToLowerInvariant(), clientRow["COLUMN_NAME"].ToString());
Assert.Equal(ordinal, Convert.ToInt32(clientRow["ordinal_position"]));
// TODO: Types can't be the same. Maybe check the types equality between providers ?
//Assert.Equal(typeString, cTypeString);
var maxLengthLong = clientRow["character_octet_length"] != DBNull.Value ? Convert.ToInt64(clientRow["character_octet_length"]) : 0;
var maxLengthMySql = maxLengthLong > Int32.MaxValue ? Int32.MaxValue : (Int32)maxLengthLong;
var mySqlPrecision = clientRow["numeric_precision"] != DBNull.Value ? Convert.ToByte(clientRow["numeric_precision"]) : (byte)0;
var mySqlScale = clientRow["numeric_scale"] != DBNull.Value ? Convert.ToByte(clientRow["numeric_scale"]) : (byte)0;
var mySqlAllowDBNull = (String)clientRow["is_nullable"] == "NO" ? false : true;
var mySqlAutoIncrement = clientRow["extra"] != DBNull.Value ? ((string)clientRow["extra"]).Contains("auto increment") : false;
// TODO : For some type (like GUID) length can't be the same
//Assert.Equal(maxLength, maxLengthMySql);
// for type like bit transformed to tinyint, precision can differ
//Assert.Equal(precision, mySqlPrecision);
//Assert.Equal(scale, mySqlScale);
Assert.Equal(isNullable, mySqlAllowDBNull);
Assert.Equal(isIdentity, mySqlAutoIncrement);
}
}
public DbCommand CreateUpdateTriggerCommand(DbConnection connection, DbTransaction transaction)
{
var updTriggerName = string.Format(this.mySqlObjectNames.GetTriggerCommandName(DbTriggerType.Update), tableName.Unquoted().Normalized().ToString());
StringBuilder createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE TRIGGER {updTriggerName} AFTER UPDATE ON {tableName.Quoted().ToString()} FOR EACH ROW ");
createTrigger.AppendLine();
createTrigger.AppendLine();
createTrigger.AppendLine($"Begin ");
createTrigger.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} ");
createTrigger.AppendLine("\tSET `update_scope_id` = NULL ");
createTrigger.AppendLine($"\t\t,`timestamp` = {this.timestampValue}");
createTrigger.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()");
createTrigger.Append($"\tWhere ");
createTrigger.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString(), "new"));
//if (this.tableDescription.GetMutableColumns().Count() > 0)
//{
// createTrigger.AppendLine();
// createTrigger.AppendLine("\t AND (");
// string or = " ";
// foreach (var column in this.tableDescription.GetMutableColumns())
// {
// var quotedColumn = ParserName.Parse(column, "`").Quoted().ToString();
// createTrigger.Append("\t");
// createTrigger.Append(or);
// createTrigger.Append("IFNULL(");
// createTrigger.Append("NULLIF(");
// createTrigger.Append("`old`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(", ");
// createTrigger.Append("`new`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(")");
// createTrigger.Append(", ");
// createTrigger.Append("NULLIF(");
// createTrigger.Append("`new`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(", ");
// createTrigger.Append("`old`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(")");
// createTrigger.AppendLine(") IS NOT NULL");
// or = " OR ";
// }
// createTrigger.AppendLine("\t ) ");
//}
createTrigger.AppendLine($"; ");
createTrigger.AppendLine("IF (SELECT ROW_COUNT() = 0) THEN ");
createTrigger.AppendLine($"\tINSERT INTO {trackingName.Quoted().ToString()} (");
StringBuilder stringBuilderArguments = new StringBuilder();
StringBuilder stringBuilderArguments2 = new StringBuilder();
StringBuilder stringPkAreNull = new StringBuilder();
string argComma = string.Empty;
string argAnd = string.Empty;
foreach (var mutableColumn in this.tableDescription.GetPrimaryKeysColumns().Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString();
stringBuilderArguments.AppendLine($"\t\t{argComma}{columnName}");
stringBuilderArguments2.AppendLine($"\t\t{argComma}new.{columnName}");
stringPkAreNull.Append($"{argAnd}{trackingName.Quoted().ToString()}.{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
createTrigger.Append(stringBuilderArguments.ToString());
createTrigger.AppendLine("\t\t,`update_scope_id`");
createTrigger.AppendLine("\t\t,`timestamp`");
createTrigger.AppendLine("\t\t,`sync_row_is_tombstone`");
createTrigger.AppendLine("\t\t,`last_change_datetime`");
var filterColumnsString = new StringBuilder();
var filterColumnsString2 = new StringBuilder();
var filterColumnsString3 = new StringBuilder();
createTrigger.AppendLine("\t) ");
createTrigger.AppendLine("\tSELECT ");
createTrigger.Append(stringBuilderArguments2.ToString());
createTrigger.AppendLine("\t\t,NULL");
createTrigger.AppendLine($"\t\t,{this.timestampValue}");
createTrigger.AppendLine("\t\t,0");
createTrigger.AppendLine("\t\t,utc_timestamp()");
createTrigger.AppendLine($"\tFROM {tableName.Quoted().ToString()}"); //[AB] added to be compatible with MariaDB 10.3.x
//if (this.tableDescription.GetMutableColumns().Count() > 0)
//{
// createTrigger.AppendLine();
// createTrigger.AppendLine("\t WHERE (");
// string or = " ";
// foreach (var column in this.tableDescription.GetMutableColumns())
// {
// var quotedColumn = ParserName.Parse(column, "`").Quoted().ToString();
// createTrigger.Append("\t");
// createTrigger.Append(or);
// createTrigger.Append("IFNULL(");
// createTrigger.Append("NULLIF(");
// createTrigger.Append("`old`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(", ");
// createTrigger.Append("`new`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(")");
// createTrigger.Append(", ");
// createTrigger.Append("NULLIF(");
// createTrigger.Append("`new`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(", ");
// createTrigger.Append("`old`.");
// createTrigger.Append(quotedColumn);
// createTrigger.Append(")");
// createTrigger.AppendLine(") IS NOT NULL");
// or = " OR ";
// }
// createTrigger.AppendLine("\t ) ");
//}
createTrigger.AppendLine("ON DUPLICATE KEY UPDATE");
createTrigger.AppendLine("\t`update_scope_id` = NULL, ");
createTrigger.AppendLine("\t`sync_row_is_tombstone` = 0, ");
createTrigger.AppendLine($"\t`timestamp` = {this.timestampValue}, ");
createTrigger.AppendLine("\t`last_change_datetime` = utc_timestamp()");
createTrigger.AppendLine(";");
createTrigger.AppendLine("END IF;");
createTrigger.AppendLine($"End; ");
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = createTrigger.ToString();
return(command);
}
public override async Task <(string DatabaseName, string Version)> GetHelloAsync(DbConnection connection, DbTransaction transaction = null)
{
return(await MySqlManagementUtils.GetHelloAsync(connection as MySqlConnection, transaction as MySqlTransaction).ConfigureAwait(false));
}
public override async Task <SyncSetup> GetAllTablesAsync(DbConnection connection, DbTransaction transaction = null)
{
var setup = await MySqlManagementUtils.GetAllTablesAsync(connection as MySqlConnection, transaction as MySqlTransaction).ConfigureAwait(false);
return(setup);
}
