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