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