//------------------------------------------------------------------ // 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); }
//------------------------------------------------------------------ // 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); }
private void CreateUpdateCommandText() { //StringBuilder stringBuilder = new StringBuilder(); //stringBuilder.AppendLine($"SELECT @ts := `timestamp` "); //stringBuilder.AppendLine($"FROM {trackingName.QuotedObjectName}"); //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")};"); //stringBuilder.AppendLine(); //stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}"); //stringBuilder.AppendLine($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}"); //stringBuilder.AppendLine($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); //stringBuilder.AppendLine($"AND (@ts <= @sync_min_timestamp OR @sync_force_write = 1);"); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendLine($"UPDATE {tableName.QuotedString}"); stringBuilder.Append($"SET {MySqlManagementUtils.CommaSeparatedUpdateFromParameters(this.TableDescription)}"); stringBuilder.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}"); stringBuilder.AppendLine($" AND ((SELECT `timestamp` FROM {trackingName.QuotedObjectName} "); stringBuilder.AppendLine($" WHERE {MySqlManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKey.Columns, tableName.QuotedObjectName, trackingName.QuotedObjectName)}"); stringBuilder.AppendLine(" ) <= @sync_min_timestamp OR @sync_force_write = 1"); stringBuilder.AppendLine(");"); this.AddName(DbCommandType.UpdateRow, stringBuilder.ToString()); }
//------------------------------------------------------------------ // 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); }