private string UpdateTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine($"Begin ");
stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} ");
stringBuilder.AppendLine("\tSET `update_scope_id` = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine($"\t\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()");
if (this.Filters != null && Filters.Count > 0)
{
foreach (var filterColumn in this.Filters)
{
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString();
stringBuilder.AppendLine($"\t\t,{columnName} = new.{columnName}");
}
stringBuilder.AppendLine();
}
stringBuilder.Append($"\tWhere ");
stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "new"));
stringBuilder.AppendLine($"; ");
stringBuilder.AppendLine($"End; ");
return(stringBuilder.ToString());
}
private string DeleteTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"UPDATE {trackingName.Quoted().ToString()} ");
stringBuilder.AppendLine("SET `sync_row_is_tombstone` = 1");
stringBuilder.AppendLine("\t,`update_scope_id` = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine($"\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,`last_change_datetime` = utc_timestamp()");
// Filter columns
if (this.Filters != null)
{
foreach (var filterColumn in this.Filters)
{
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} = old.{columnName}");
}
stringBuilder.AppendLine();
}
stringBuilder.Append($"WHERE ");
stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "old"));
stringBuilder.AppendLine(";");
stringBuilder.AppendLine("END;");
return(stringBuilder.ToString());
}
private void CreateDeleteCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"DELETE FROM {tableName.QuotedString} ");
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.DeleteRow, stringBuilder.ToString());
}
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, {MySqlObjectNames.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);
}
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());
}
private string UpdateTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine($"Begin ");
stringBuilder.AppendLine($"\tUPDATE {trackingName.Quoted().ToString()} ");
stringBuilder.AppendLine("\tSET `update_scope_id` = NULL ");
stringBuilder.AppendLine($"\t\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t\t,`last_change_datetime` = utc_timestamp()");
stringBuilder.Append($"\tWhere ");
stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.GetPrimaryKeysColumns(), trackingName.Quoted().ToString(), "new"));
if (this.tableDescription.GetMutableColumns().Count() > 0)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine("\t AND (");
string or = " ";
foreach (var column in this.tableDescription.GetMutableColumns())
{
var quotedColumn = ParserName.Parse(column, "`").Quoted().ToString();
stringBuilder.Append("\t");
stringBuilder.Append(or);
stringBuilder.Append("IFNULL(");
stringBuilder.Append("NULLIF(");
stringBuilder.Append("`old`.");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(", ");
stringBuilder.Append("`new`.");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(")");
stringBuilder.Append(", ");
stringBuilder.Append("NULLIF(");
stringBuilder.Append("`new`.");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(", ");
stringBuilder.Append("`old`.");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(")");
stringBuilder.AppendLine(") IS NOT NULL");
or = " OR ";
}
stringBuilder.AppendLine("\t ) ");
}
stringBuilder.AppendLine($"; ");
stringBuilder.AppendLine("IF (SELECT ROW_COUNT() = 0) THEN ");
stringBuilder.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 ";
}
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t\t,`update_scope_id`");
stringBuilder.AppendLine("\t\t,`timestamp`");
stringBuilder.AppendLine("\t\t,`sync_row_is_tombstone`");
stringBuilder.AppendLine("\t\t,`last_change_datetime`");
var filterColumnsString = new StringBuilder();
var filterColumnsString2 = new StringBuilder();
var filterColumnsString3 = new StringBuilder();
stringBuilder.AppendLine("\t) ");
stringBuilder.AppendLine("\tVALUES (");
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t\t,NULL");
stringBuilder.AppendLine($"\t\t,{MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t\t,0");
stringBuilder.AppendLine("\t\t,utc_timestamp()");
stringBuilder.AppendLine("\t)");
stringBuilder.AppendLine("ON DUPLICATE KEY UPDATE");
stringBuilder.AppendLine("\t`update_scope_id` = NULL, ");
stringBuilder.AppendLine("\t`sync_row_is_tombstone` = 0, ");
stringBuilder.AppendLine($"\t`timestamp` = {MySqlObjectNames.TimestampValue}, ");
stringBuilder.AppendLine("\t`last_change_datetime` = utc_timestamp();");
stringBuilder.AppendLine("END IF;");
stringBuilder.AppendLine($"End; ");
return stringBuilder.ToString();
}
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` = {MySqlObjectNames.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("\tVALUES (");
createTrigger.Append(stringBuilderArguments2.ToString());
createTrigger.AppendLine("\t\t,NULL");
createTrigger.AppendLine($"\t\t,{MySqlObjectNames.TimestampValue}");
createTrigger.AppendLine("\t\t,0");
createTrigger.AppendLine("\t\t,utc_timestamp()");
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` = {MySqlObjectNames.TimestampValue}, ");
createTrigger.AppendLine("\t`last_change_datetime` = utc_timestamp();");
createTrigger.AppendLine("END IF;");
createTrigger.AppendLine($"End; ");
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = createTrigger.ToString();
return(command);
}
