//------------------------------------------------------------------ // Update Metadata command //------------------------------------------------------------------ private MySqlCommand BuildUpdateMetadataCommand() { MySqlCommand sqlCommand = new MySqlCommand(); StringBuilder stringBuilder = new StringBuilder(); this.AddPkColumnParametersToCommand(sqlCommand); MySqlParameter sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_scope_id"; sqlParameter.MySqlDbType = MySqlDbType.Guid; sqlParameter.Size = 36; sqlCommand.Parameters.Add(sqlParameter); MySqlParameter sqlParameter1 = new MySqlParameter(); sqlParameter1.ParameterName = "sync_row_is_tombstone"; sqlParameter1.MySqlDbType = MySqlDbType.Int32; sqlCommand.Parameters.Add(sqlParameter1); MySqlParameter sqlParameter3 = new MySqlParameter(); sqlParameter3.ParameterName = "create_timestamp"; sqlParameter3.MySqlDbType = MySqlDbType.Int64; sqlCommand.Parameters.Add(sqlParameter3); MySqlParameter sqlParameter5 = new MySqlParameter(); sqlParameter5.ParameterName = "update_timestamp"; sqlParameter5.MySqlDbType = MySqlDbType.Int64; sqlCommand.Parameters.Add(sqlParameter5); stringBuilder.AppendLine($"DECLARE was_tombstone int;"); stringBuilder.AppendLine($"SET was_tombstone = 1;"); stringBuilder.AppendLine($"SELECT `sync_row_is_tombstone` " + $"FROM {trackingName.QuotedObjectName} " + $"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} " + $"LIMIT 1 INTO was_tombstone;"); stringBuilder.AppendLine($"IF (was_tombstone is not null and was_tombstone = 1 and sync_row_is_tombstone = 0) THEN"); stringBuilder.AppendLine($"UPDATE {trackingName.FullQuotedString}"); stringBuilder.AppendLine($"SET `create_scope_id` = sync_scope_id, "); stringBuilder.AppendLine($"\t `update_scope_id` = sync_scope_id, "); stringBuilder.AppendLine($"\t `create_timestamp` = create_timestamp, "); stringBuilder.AppendLine($"\t `update_timestamp` = update_timestamp, "); stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = sync_row_is_tombstone, "); stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, "); stringBuilder.AppendLine($"\t `last_change_datetime` = now() "); stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")};"); stringBuilder.AppendLine($"ELSE"); stringBuilder.AppendLine($"UPDATE {trackingName.FullQuotedString}"); stringBuilder.AppendLine($"SET `update_scope_id` = sync_scope_id, "); stringBuilder.AppendLine($"\t `update_timestamp` = update_timestamp, "); stringBuilder.AppendLine($"\t `sync_row_is_tombstone` = sync_row_is_tombstone, "); stringBuilder.AppendLine($"\t `timestamp` = {MySqlObjectNames.TimestampValue}, "); stringBuilder.AppendLine($"\t `last_change_datetime` = now() "); stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")};"); stringBuilder.AppendLine("END IF;"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
//------------------------------------------------------------------ // Update command //------------------------------------------------------------------ private MySqlCommand BuildUpdateCommand() { MySqlCommand sqlCommand = new MySqlCommand(); StringBuilder stringBuilder = new StringBuilder(); this.AddColumnParametersToCommand(sqlCommand); MySqlParameter sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_force_write"; sqlParameter.MySqlDbType = MySqlDbType.Int32; sqlCommand.Parameters.Add(sqlParameter); MySqlParameter sqlParameter1 = new MySqlParameter(); sqlParameter1.ParameterName = "sync_min_timestamp"; sqlParameter1.MySqlDbType = MySqlDbType.Int64; sqlCommand.Parameters.Add(sqlParameter1); stringBuilder.AppendLine("DECLARE ts BIGINT;"); stringBuilder.AppendLine("SET ts = 0;"); stringBuilder.AppendLine($"SELECT `timestamp` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} LIMIT 1 INTO ts;"); stringBuilder.AppendLine($"UPDATE {tableName.FullQuotedString}"); stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.tableDescription)}"); stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")}"); stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR sync_force_write = 1);"); stringBuilder.AppendLine(); // Can't rely on rows count since MySql will return 0 if an update don't update any columns //stringBuilder.AppendLine($"/* Since the update 'could' potentially returns 0 as row affected count when we make a double update with the same values, to be sure, make a fake update on metadatas time column */"); //stringBuilder.AppendLine($"UPDATE {trackingName.QuotedObjectName} "); //stringBuilder.AppendLine($"SET `timestamp` = {MySqlObjectNames.TimestampValue}"); //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")} AND (ts <= sync_min_timestamp OR sync_force_write = 1);"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
//------------------------------------------------------------------ // Delete command //------------------------------------------------------------------ private MySqlCommand BuildDeleteCommand() { MySqlCommand sqlCommand = new MySqlCommand(); this.AddPkColumnParametersToCommand(sqlCommand); MySqlParameter sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_force_write"; sqlParameter.MySqlDbType = MySqlDbType.Int32; sqlCommand.Parameters.Add(sqlParameter); MySqlParameter sqlParameter1 = new MySqlParameter(); sqlParameter1.ParameterName = "sync_min_timestamp"; sqlParameter1.MySqlDbType = MySqlDbType.Int64; sqlCommand.Parameters.Add(sqlParameter1); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("DECLARE ts BIGINT;"); stringBuilder.AppendLine("SET ts = 0;"); stringBuilder.AppendLine($"SELECT `timestamp` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} LIMIT 1 INTO ts;"); stringBuilder.AppendLine($"DELETE FROM {tableName.FullQuotedString} WHERE"); stringBuilder.AppendLine(MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")); stringBuilder.AppendLine("AND (ts <= sync_min_timestamp OR sync_force_write = 1);"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
//------------------------------------------------------------------ // Update command //------------------------------------------------------------------ private MySqlCommand BuildUpdateCommand(bool hasMutableColumns) { var sqlCommand = new MySqlCommand(); var stringBuilder = new StringBuilder(); this.AddColumnParametersToCommand(sqlCommand); var sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_scope_id"; sqlParameter.MySqlDbType = MySqlDbType.Guid; sqlParameter.Size = 36; sqlCommand.Parameters.Add(sqlParameter); sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_force_write"; sqlParameter.MySqlDbType = MySqlDbType.Int32; sqlCommand.Parameters.Add(sqlParameter); sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_min_timestamp"; sqlParameter.MySqlDbType = MySqlDbType.Int64; sqlCommand.Parameters.Add(sqlParameter); sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_row_count"; sqlParameter.MySqlDbType = MySqlDbType.Int32; sqlParameter.Direction = ParameterDirection.Output; sqlCommand.Parameters.Add(sqlParameter); var listColumnsTmp = new StringBuilder(); var listColumnsTmp2 = new StringBuilder(); var listColumnsTmp3 = new StringBuilder(); var and = ""; foreach (var column in this.tableDescription.GetPrimaryKeysColumns()) { var param = GetMySqlParameter(column); var declar = CreateParameterDeclaration(param); var columnName = ParserName.Parse(column, "`").Quoted().ToString(); // Primary keys column name, with quote listColumnsTmp.Append($"{columnName}, "); // param name without type listColumnsTmp2.Append($"t_{param.ParameterName}, "); // param name with type stringBuilder.AppendLine($"DECLARE t_{declar};"); // Param equal IS NULL listColumnsTmp3.Append($"{and}t_{param.ParameterName} IS NULL"); and = " AND "; } stringBuilder.AppendLine("DECLARE ts BIGINT;"); stringBuilder.AppendLine("DECLARE t_update_scope_id VARCHAR(36);"); stringBuilder.AppendLine("SET ts = 0;"); stringBuilder.AppendLine($"SELECT {listColumnsTmp.ToString()}"); stringBuilder.AppendLine($"`timestamp`, `update_scope_id` FROM {trackingName.Quoted().ToString()} "); stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString())} LIMIT 1 "); stringBuilder.AppendLine($"INTO {listColumnsTmp2.ToString()} ts, t_update_scope_id;"); stringBuilder.AppendLine(); if (hasMutableColumns) { stringBuilder.AppendLine($"UPDATE {tableName.Quoted().ToString()}"); stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.tableDescription)}"); stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")}"); stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR ts IS NULL OR t_update_scope_id = sync_scope_id OR sync_force_write = 1);"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"SELECT ROW_COUNT() LIMIT 1 INTO sync_row_count;"); stringBuilder.AppendLine($"IF (sync_row_count = 0) THEN"); } string empty = string.Empty; var stringBuilderArguments = new StringBuilder(); var stringBuilderParameters = new StringBuilder(); foreach (var mutableColumn in this.tableDescription.Columns.Where(c => !c.IsReadOnly)) { var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString(); var parameterName = ParserName.Parse(mutableColumn, "`").Unquoted().Normalized().ToString(); stringBuilderArguments.Append(string.Concat(empty, columnName)); stringBuilderParameters.Append(string.Concat(empty, $"{MYSQL_PREFIX_PARAMETER}{parameterName}")); empty = ", "; } // If we don't have any mutable column, we can't update, and the Insert // will fail if we don't ignore the insert (on Reinitialize for example) var ignoreKeyWord = hasMutableColumns ? "" : "IGNORE"; stringBuilder.AppendLine($"\tINSERT {ignoreKeyWord} INTO {tableName.Quoted().ToString()}"); stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})"); stringBuilder.AppendLine($"\tSELECT * FROM ( SELECT {stringBuilderParameters.ToString()}) as TMP "); stringBuilder.AppendLine($"\tWHERE ( {listColumnsTmp3.ToString()} )"); stringBuilder.AppendLine($"\tOR (ts <= sync_min_timestamp OR ts IS NULL OR t_update_scope_id = sync_scope_id OR sync_force_write = 1)"); stringBuilder.AppendLine($"\tLIMIT 1;"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"SELECT ROW_COUNT() LIMIT 1 INTO sync_row_count;"); stringBuilder.AppendLine(); if (hasMutableColumns) { stringBuilder.AppendLine("END IF;"); } stringBuilder.AppendLine(); stringBuilder.AppendLine($"IF (sync_row_count > 0) THEN"); stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()}"); stringBuilder.AppendLine($"\tSET `update_scope_id` = sync_scope_id, "); stringBuilder.AppendLine($"\t\t `sync_row_is_tombstone` = 0, "); stringBuilder.AppendLine($"\t\t `timestamp` = {MySqlObjectNames.TimestampValue}, "); stringBuilder.AppendLine($"\t\t `last_change_datetime` = now() "); stringBuilder.AppendLine($"\tWHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")};"); stringBuilder.AppendLine($"END IF;"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
//------------------------------------------------------------------ // Delete command //------------------------------------------------------------------ private MySqlCommand BuildDeleteCommand() { MySqlCommand sqlCommand = new MySqlCommand(); this.AddPkColumnParametersToCommand(sqlCommand); var sqlParameter1 = new MySqlParameter(); sqlParameter1.ParameterName = "sync_scope_id"; sqlParameter1.MySqlDbType = MySqlDbType.Guid; sqlParameter1.Size = 36; sqlCommand.Parameters.Add(sqlParameter1); var sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_force_write"; sqlParameter.MySqlDbType = MySqlDbType.Int32; sqlCommand.Parameters.Add(sqlParameter); var sqlParameter2 = new MySqlParameter(); sqlParameter2.ParameterName = "sync_min_timestamp"; sqlParameter2.MySqlDbType = MySqlDbType.Int64; sqlCommand.Parameters.Add(sqlParameter2); sqlParameter = new MySqlParameter(); sqlParameter.ParameterName = "sync_row_count"; sqlParameter.MySqlDbType = MySqlDbType.Int32; sqlParameter.Direction = ParameterDirection.Output; sqlCommand.Parameters.Add(sqlParameter); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine(); stringBuilder.AppendLine("DECLARE ts BIGINT;"); stringBuilder.AppendLine("DECLARE t_update_scope_id VARCHAR(36);"); stringBuilder.AppendLine("SET ts = 0;"); stringBuilder.AppendLine($"SELECT `timestamp`, `update_scope_id` FROM {trackingName.Quoted().ToString()} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString())} LIMIT 1 INTO ts, t_update_scope_id;"); stringBuilder.AppendLine($"DELETE FROM {tableName.Quoted().ToString()} WHERE"); stringBuilder.AppendLine(MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")); stringBuilder.AppendLine("AND (ts <= sync_min_timestamp OR ts IS NULL OR t_update_scope_id = sync_scope_id OR sync_force_write = 1);"); stringBuilder.AppendLine(); stringBuilder.AppendLine($"SELECT ROW_COUNT() LIMIT 1 INTO sync_row_count;"); stringBuilder.AppendLine(); stringBuilder.AppendLine(); stringBuilder.AppendLine($"IF (sync_row_count > 0) THEN"); stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()}"); stringBuilder.AppendLine($"\tSET `update_scope_id` = sync_scope_id, "); stringBuilder.AppendLine($"\t\t `sync_row_is_tombstone` = 1, "); stringBuilder.AppendLine($"\t\t `timestamp` = {MySqlObjectNames.TimestampValue}, "); stringBuilder.AppendLine($"\t\t `last_change_datetime` = now() "); stringBuilder.AppendLine($"\tWHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.GetPrimaryKeysColumns(), "")};"); stringBuilder.AppendLine($"END IF;"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }
//------------------------------------------------------------------ // Update command //------------------------------------------------------------------ private MySqlCommand BuildUpdateCommand() { MySqlCommand sqlCommand = new MySqlCommand(); StringBuilder stringBuilder = new StringBuilder(); this.AddColumnParametersToCommand(sqlCommand); MySqlParameter sqlParameter = new MySqlParameter("sync_force_write", MySqlDbType.Int32); sqlCommand.Parameters.Add(sqlParameter); MySqlParameter sqlParameter1 = new MySqlParameter("sync_min_timestamp", MySqlDbType.Int64); sqlCommand.Parameters.Add(sqlParameter1); stringBuilder.AppendLine("DECLARE ts BIGINT;"); stringBuilder.AppendLine("SET ts = 0;"); stringBuilder.AppendLine($"SELECT `timestamp` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)} LIMIT 1 INTO ts;"); stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}"); stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.tableDescription)}"); stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, "")}"); stringBuilder.AppendLine($" AND (ts <= sync_min_timestamp OR sync_force_write = 1);"); sqlCommand.CommandText = stringBuilder.ToString(); return(sqlCommand); }