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());
}
public bool NeedToCreateTrigger(DbTriggerType type)
{
var updTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name, tableName.Unquoted().Normalized().ToString());
var delTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name, tableName.Unquoted().Normalized().ToString());
var insTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name, tableName.Unquoted().Normalized().ToString());
string triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!MySqlManagementUtils.TriggerExists(connection, transaction, triggerName));
}
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());
}
//------------------------------------------------------------------
// 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()
{
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);
}
public bool NeedToCreateForeignKeyConstraints(SyncRelation relation)
{
string tableName = relation.GetTable().TableName;
var relationName = NormalizeRelationName(relation.RelationName);
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
this.connection.Open();
}
var relations = MySqlManagementUtils.RelationsForTable(this.connection, this.transaction, tableName);
var foreignKeyExist = relations.Rows.Any(r =>
string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
//------------------------------------------------------------------
// Delete Metadata command
//------------------------------------------------------------------
private MySqlCommand BuildDeleteMetadataCommand()
{
MySqlCommand sqlCommand = new MySqlCommand();
this.AddPkColumnParametersToCommand(sqlCommand);
MySqlParameter sqlParameter = new MySqlParameter();
sqlParameter.ParameterName = "sync_check_concurrency";
sqlParameter.MySqlDbType = MySqlDbType.Int32;
sqlCommand.Parameters.Add(sqlParameter);
MySqlParameter sqlParameter1 = new MySqlParameter();
sqlParameter1.ParameterName = "sync_row_timestamp";
sqlParameter1.MySqlDbType = MySqlDbType.Int64;
sqlCommand.Parameters.Add(sqlParameter1);
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine($"DELETE FROM {trackingName.FullQuotedString} ");
stringBuilder.Append($"WHERE ");
stringBuilder.AppendLine(MySqlManagementUtils.ColumnsAndParameters(this.tableDescription.PrimaryKey.Columns, ""));
stringBuilder.Append(";");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
//------------------------------------------------------------------
// Insert command
//------------------------------------------------------------------
private MySqlCommand BuildInsertCommand()
{
MySqlCommand sqlCommand = new MySqlCommand();
StringBuilder stringBuilder = new StringBuilder();
StringBuilder stringBuilderArguments = new StringBuilder();
StringBuilder stringBuilderParameters = new StringBuilder();
this.AddColumnParametersToCommand(sqlCommand);
stringBuilder.Append(string.Concat("IF ((SELECT COUNT(*) FROM ", trackingName.FullQuotedString, " WHERE "));
stringBuilder.Append(MySqlManagementUtils.ColumnsAndParameters(this.tableDescription.PrimaryKey.Columns, string.Empty));
stringBuilder.AppendLine(") <= 0) THEN");
string empty = string.Empty;
foreach (var mutableColumn in this.tableDescription.Columns.Where(c => !c.IsReadOnly))
{
ObjectNameParser columnName = new ObjectNameParser(mutableColumn.ColumnName, "`", "`");
stringBuilderArguments.Append(string.Concat(empty, columnName.FullQuotedString));
stringBuilderParameters.Append(string.Concat(empty, $"{MYSQL_PREFIX_PARAMETER}{columnName.FullUnquotedString}"));
empty = ", ";
}
stringBuilder.AppendLine($"\tINSERT INTO {tableName.FullQuotedString}");
stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})");
stringBuilder.AppendLine($"\tVALUES ({stringBuilderParameters.ToString()});");
stringBuilder.AppendLine();
stringBuilder.AppendLine("END IF;");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
{
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
var dmTable = MySqlManagementUtils.RelationsForTable(connection, transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), foreignKey.RelationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type, DbConnection connection, DbTransaction transaction)
{
var updTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name, tableName.Unquoted().Normalized().ToString());
var delTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name, tableName.Unquoted().Normalized().ToString());
var insTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name, tableName.Unquoted().Normalized().ToString());
string triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!await MySqlManagementUtils.TriggerExistsAsync((MySqlConnection)connection, (MySqlTransaction)transaction, triggerName).ConfigureAwait(false));
}
public bool NeedToCreateTrackingTable(DbBuilderOption builderOption)
{
if (builderOption.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
{
return(!MySqlManagementUtils.TableExists(connection, transaction, trackingName.UnquotedString));
}
return(false);
}
/// <summary>
/// Check if we need to create the stored procedure
/// </summary>
public async Task <bool> NeedToCreateProcedureAsync(DbCommandType commandType)
{
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("Here, we need an opened connection please");
}
var commandName = this.mySqlObjectNames.GetCommandName(commandType).name;
return(!(await MySqlManagementUtils.ProcedureExistsAsync(connection, transaction, commandName).ConfigureAwait(false)));
}
private void CreateDeleteMetadataCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"DELETE FROM {trackingName.QuotedString} ");
stringBuilder.Append($"WHERE ");
stringBuilder.AppendLine(MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, ""));
stringBuilder.Append(";");
this.AddName(DbCommandType.DeleteMetadata, stringBuilder.ToString());
}
/// <summary>
/// Check if we need to create the stored procedure
/// </summary>
public bool NeedToCreateProcedure(DbCommandType commandType)
{
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("Here, we need an opened connection please");
}
var commandName = this.sqlObjectNames.GetCommandName(commandType);
return(!MySqlManagementUtils.ProcedureExists(connection, transaction, commandName));
}
public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation, DbConnection connection, DbTransaction transaction)
{
string tableName = relation.GetTable().TableName;
var relationName = NormalizeRelationName(relation.RelationName);
var relations = await MySqlManagementUtils.GetRelationsForTableAsync((MySqlConnection)connection, (MySqlTransaction)transaction, tableName).ConfigureAwait(false);
var foreignKeyExist = relations.Rows.Any(r =>
string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison));
return(!foreignKeyExist);
}
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 void CreateUpdatedMetadataCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString}");
stringBuilder.AppendLine($"SET `update_scope_id` = @update_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.Append($"WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.TableDescription.PrimaryKey.Columns, "")}");
this.AddName(DbCommandType.UpdateMetadata, stringBuilder.ToString());
}
//------------------------------------------------------------------
// Update Metadata command
//------------------------------------------------------------------
private MySqlCommand BuildUpdateMetadataCommand()
{
MySqlCommand sqlCommand = new MySqlCommand();
StringBuilder stringBuilder = new StringBuilder();
this.AddPkColumnParametersToCommand(sqlCommand);
MySqlParameter sqlParameter = new MySqlParameter("sync_scope_id", MySqlDbType.Guid);
sqlParameter.Size = 36;
sqlCommand.Parameters.Add(sqlParameter);
MySqlParameter sqlParameter1 = new MySqlParameter("sync_row_is_tombstone", MySqlDbType.Int32);
sqlCommand.Parameters.Add(sqlParameter1);
MySqlParameter sqlParameter3 = new MySqlParameter("create_timestamp", MySqlDbType.Int64);
sqlCommand.Parameters.Add(sqlParameter3);
MySqlParameter sqlParameter5 = new MySqlParameter("update_timestamp", MySqlDbType.Int64);
sqlCommand.Parameters.Add(sqlParameter5);
stringBuilder.AppendLine($"DECLARE was_tombstone int;");
stringBuilder.AppendLine($"SET was_tombstone = 1;");
stringBuilder.AppendLine($"SELECT was_tombstone = `sync_row_is_tombstone` FROM {trackingName.QuotedObjectName} WHERE {MySqlManagementUtils.WhereColumnAndParameters(this.tableDescription.PrimaryKey.Columns, trackingName.QuotedObjectName)};");
stringBuilder.AppendLine($"IF (was_tombstone is not null and was_tombstone = 1 and sync_row_is_tombstone = 0) THEN");
stringBuilder.AppendLine($"UPDATE {trackingName.QuotedString}");
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.QuotedString}");
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);
}
/// <summary>
/// Check if we need to create the stored procedure
/// </summary>
public bool NeedToCreateProcedure(DbCommandType commandType, DbBuilderOption option)
{
if (connection.State != ConnectionState.Open)
{
throw new ArgumentException("Here, we need an opened connection please");
}
var commandName = this.sqlObjectNames.GetCommandName(commandType);
if (option.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
{
return(!MySqlManagementUtils.ProcedureExists(connection, transaction, commandName));
}
return(false);
}
/// <summary>
/// For a foreign key, check if the Parent table exists
/// </summary>
private bool EnsureForeignKeysTableExist(DmRelation foreignKey)
{
var childTable = foreignKey.ChildTable;
var parentTable = foreignKey.ParentTable;
// The foreignkey comes from the child table
var ds = foreignKey.ChildTable.DmSet;
if (ds == null)
{
return(false);
}
// Check if the parent table is part of the sync configuration
var exist = ds.Tables.Any(t => ds.IsEqual(t.TableName.ToLowerInvariant(), parentTable.TableName.ToLowerInvariant()));
if (!exist)
{
return(false);
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
return(MySqlManagementUtils.TableExists(connection, transaction, parentTable.TableName.ToLowerInvariant()));
}
catch (Exception ex)
{
Debug.WriteLine($"Error during EnsureForeignKeysTableExist : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public bool NeedToCreateForeignKeyConstraints(DmRelation foreignKey)
{
string parentTable = foreignKey.ParentTable.TableName;
string parentSchema = foreignKey.ParentTable.Schema;
string parentFullName = string.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
var relationName = NormalizeRelationName(foreignKey.RelationName);
bool alreadyOpened = this.connection.State == ConnectionState.Open;
// Don't want foreign key on same table since it could be a problem on first
// sync. We are not sure that parent row will be inserted in first position
if (string.Equals(parentTable, foreignKey.ChildTable.TableName, StringComparison.CurrentCultureIgnoreCase))
{
return(false);
}
try
{
if (!alreadyOpened)
{
this.connection.Open();
}
var dmTable = MySqlManagementUtils.RelationsForTable(this.connection, this.transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), relationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
//------------------------------------------------------------------
// 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);
}
public bool NeedToCreateTrigger(DbTriggerType type, DbBuilderOption option)
{
var updTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.UpdateTrigger), tableName.UnquotedStringWithUnderScore);
var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.UnquotedStringWithUnderScore);
var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.UnquotedStringWithUnderScore);
if (option.HasFlag(DbBuilderOption.CreateOrUseExistingSchema))
{
string triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!MySqlManagementUtils.TriggerExists(connection, transaction, triggerName));
}
if (option.HasFlag(DbBuilderOption.UseExistingSchema))
{
return(false);
}
return(false);
}
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());
}
public bool NeedToCreateTrackingTable()
{
return(!MySqlManagementUtils.TableExists(connection, transaction, trackingName.FullUnquotedString));
}
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();
}
//------------------------------------------------------------------
// 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);
}
public bool NeedToCreateTrackingTable() => !MySqlManagementUtils.TableExists(connection, transaction, trackingName);
//------------------------------------------------------------------
// 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);
}