public SqlChangeTrackingBuilderTrackingTable(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup)
{
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.sqlDbMetadata = new SqlDbMetadata();
}
public static bool TriggerExists(SqlConnection connection, SqlTransaction transaction, string quotedTriggerName)
{
bool triggerExist;
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
using (var sqlCommand = new SqlCommand("IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@triggerName", triggerName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
triggerExist = (int)sqlCommand.ExecuteScalar() != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(triggerExist);
}
/// <summary>
/// Get columns for table
/// </summary>
public static SyncTable ColumnsForTable(SqlConnection connection, SqlTransaction transaction, string tableName, string schemaName)
{
var commandColumn = $"Select col.name as name, " +
$"col.column_id, " +
$"typ.name as [type], " +
$"col.max_length, " +
$"col.precision, " +
$"col.scale, " +
$"col.is_nullable, " +
$"col.is_computed, " +
$"col.is_identity, " +
$"ind.is_unique, " +
$"ident_seed(sch.name + '.' + tbl.name) AS seed, " +
$"ident_incr(sch.name + '.' + tbl.name) AS step, " +
$"object_definition(col.default_object_id) AS defaultvalue " +
$" from sys.columns as col " +
$" Inner join sys.tables as tbl on tbl.object_id = col.object_id " +
$" Inner join sys.schemas as sch on tbl.schema_id = sch.schema_id " +
$" Inner Join sys.systypes typ on typ.xusertype = col.system_type_id " +
$" Left outer join sys.indexes ind on ind.object_id = col.object_id and ind.index_id = col.column_id " +
$" Where tbl.name = @tableName and sch.name = @schemaName ";
var tableNameNormalized = ParserName.Parse(tableName).Unquoted().Normalized().ToString();
var tableNameString = ParserName.Parse(tableName).ToString();
var schemaNameString = "dbo";
if (!string.IsNullOrEmpty(schemaName))
{
schemaNameString = ParserName.Parse(schemaName).ToString();
schemaNameString = string.IsNullOrWhiteSpace(schemaNameString) ? "dbo" : schemaNameString;
}
var syncTable = new SyncTable(tableNameNormalized);
using (var sqlCommand = new SqlCommand(commandColumn, connection))
{
sqlCommand.Parameters.AddWithValue("@tableName", tableNameString);
sqlCommand.Parameters.AddWithValue("@schemaName", schemaNameString);
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
using (var reader = sqlCommand.ExecuteReader())
syncTable.Load(reader);
if (!alreadyOpened)
{
connection.Close();
}
}
return(syncTable);
}
public SqlBuilderTrackingTable(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as NpgsqlConnection;
this.transaction = transaction as NpgsqlTransaction;
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.sqlDbMetadata = new NpgsqlDbMetadata();
}
public static void DropTypeIfExists(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTypeName)
{
var typeName = ParserName.Parse(quotedTypeName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) DROP TYPE {0}", quotedTypeName), connection))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@typeName", typeName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTypeName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
sqlCommand.ExecuteNonQuery();
if (!alreadyOpened)
{
connection.Close();
}
}
}
//------------------------------------------------------------------
// Select Row command
//------------------------------------------------------------------
protected override SqlCommand BuildSelectRowCommand()
{
var sqlCommand = new SqlCommand();
this.AddPkColumnParametersToCommand(sqlCommand);
var sqlParameter = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(sqlParameter);
var stringBuilder1 = new StringBuilder();
var stringBuilder11 = new StringBuilder();
var stringBuilder2 = new StringBuilder();
var stringBuilder22 = new StringBuilder();
var stringBuilder3 = new StringBuilder();
var stringBuilder4 = new StringBuilder();
string empty = string.Empty;
string comma = string.Empty;
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
var parameterName = ParserName.Parse(pkColumn).Unquoted().Normalized().ToString();
stringBuilder1.Append($"{empty}[side].{columnName} = @{parameterName}");
stringBuilder11.Append($"{empty}[base].{columnName} = @{parameterName}");
stringBuilder2.AppendLine($"\t[side].{columnName}, ");
stringBuilder22.AppendLine($"\t[base].{columnName}, ");
stringBuilder3.Append($"{comma}{columnName}");
stringBuilder4.Append($"{empty}[base].{columnName} = [side].{columnName}");
empty = " AND ";
comma = ", ";
}
var stringBuilderColumns = new StringBuilder();
foreach (var mutableColumn in this.tableDescription.GetMutableColumns())
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderColumns.AppendLine($"\t[base].{columnName}, ");
}
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"IF (SELECT TOP 1 1 FROM CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, 0) AS [side] WHERE ({stringBuilder1.ToString()})) > 0");
stringBuilder.AppendLine($"BEGIN");
stringBuilder.AppendLine($"\tSELECT");
// add side pkeys
stringBuilder.Append(stringBuilder2.ToString());
// add columns
stringBuilder.Append(stringBuilderColumns.ToString());
stringBuilder.AppendLine($"\tCAST([side].SYS_CHANGE_CONTEXT as uniqueidentifier) AS [update_scope_id],");
stringBuilder.AppendLine($"\tCASE [side].SYS_CHANGE_OPERATION WHEN 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.AppendLine($"\tFROM CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, 0) AS [side]");
stringBuilder.AppendLine($"\tLEFT JOIN {tableName.Schema().Quoted().ToString()} [base] ON");
stringBuilder.AppendLine($"\t\t{stringBuilder4.ToString()}");
stringBuilder.AppendLine($"\tWHERE {stringBuilder1.ToString()}");
stringBuilder.AppendLine($"END");
stringBuilder.AppendLine($"ELSE");
stringBuilder.AppendLine($"BEGIN");
stringBuilder.AppendLine($"\tSELECT");
// add base pkeys
stringBuilder.Append(stringBuilder22.ToString());
// add base columns
stringBuilder.Append(stringBuilderColumns.ToString());
stringBuilder.AppendLine($"\tnull as update_scope_id, ");
stringBuilder.AppendLine($"\t0 as sync_row_is_tombstone ");
stringBuilder.AppendLine($"\tFROM {tableName.Schema().Quoted().ToString()} as [base] ");
stringBuilder.Append(string.Concat("\tWHERE ", stringBuilder11.ToString()));
stringBuilder.AppendLine();
stringBuilder.AppendLine($"END");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
//------------------------------------------------------------------
// Bulk Delete command
//------------------------------------------------------------------
protected override SqlCommand BuildBulkDeleteCommand()
{
var sqlCommand = new SqlCommand();
var sqlParameter = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
sqlCommand.Parameters.Add(sqlParameter);
var sqlParameter1 = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(sqlParameter1);
var sqlParameter2 = new SqlParameter("@changeTable", SqlDbType.Structured)
{
TypeName = this.sqlObjectNames.GetCommandName(DbCommandType.BulkTableType).name
};
sqlCommand.Parameters.Add(sqlParameter2);
string str4 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[p]", "[CT]");
string str5 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[changes]", "[base]");
string str6 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[t]", "[side]");
string str7 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[p]", "[side]");
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("-- use a temp table to store the list of PKs that successfully got deleted");
stringBuilder.Append("declare @dms_changed TABLE (");
foreach (var c in this.tableDescription.GetPrimaryKeysColumns())
{
// Get the good SqlDbType (even if we are not from Sql Server def)
var sqlDbTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(c.OriginalDbType, c.GetDbType(), false, false, c.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var quotedColumnType = ParserName.Parse(sqlDbTypeString).Quoted().ToString();
quotedColumnType += this.sqlDbMetadata.TryGetOwnerDbTypePrecision(c.OriginalDbType, c.GetDbType(), false, false, c.MaxLength, c.Precision, c.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
stringBuilder.Append($"{ParserName.Parse(c).Quoted().ToString()} {quotedColumnType}, ");
}
stringBuilder.Append(" PRIMARY KEY (");
var pkeyComma = " ";
foreach (var primaryKeyColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(primaryKeyColumn).Quoted().ToString();
stringBuilder.Append($"{pkeyComma}{columnName}");
pkeyComma = ", ";
}
stringBuilder.AppendLine("));");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"DECLARE @var_sync_scope_id varbinary(128) = cast(@sync_scope_id as varbinary(128));");
stringBuilder.AppendLine();
stringBuilder.AppendLine($";WITH ");
stringBuilder.AppendLine($" CHANGE_TRACKING_CONTEXT(@var_sync_scope_id),");
stringBuilder.AppendLine($" {trackingName.Quoted().ToString()} AS (");
stringBuilder.Append($"\tSELECT ");
foreach (var c in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(c).Quoted().ToString();
stringBuilder.Append($"[p].{columnName}, ");
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tCAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id], ");
stringBuilder.AppendLine($"\t[CT].[SYS_CHANGE_VERSION] as [timestamp],");
stringBuilder.AppendLine($"\tCASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.AppendLine($"\tFROM @changeTable AS [p] ");
stringBuilder.AppendLine($"\tLEFT JOIN CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, @sync_min_timestamp) AS [CT] ON {str4}");
stringBuilder.AppendLine($"\t)");
stringBuilder.AppendLine($"DELETE {tableName.Schema().Quoted().ToString()}");
stringBuilder.Append($"OUTPUT ");
pkeyComma = " ";
foreach (var primaryKeyColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(primaryKeyColumn).Quoted().ToString();
stringBuilder.Append($"{pkeyComma}DELETED.{columnName}");
pkeyComma = ", ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INTO @dms_changed ");
stringBuilder.AppendLine($"FROM {tableName.Quoted().ToString()} [base]");
stringBuilder.AppendLine($"JOIN {trackingName.Quoted().ToString()} [changes] ON {str5}");
stringBuilder.AppendLine("WHERE [changes].[timestamp] <= @sync_min_timestamp OR [changes].[timestamp] IS NULL OR [changes].[update_scope_id] = @sync_scope_id;");
stringBuilder.AppendLine();
stringBuilder.AppendLine();
stringBuilder.AppendLine();
stringBuilder.Append(BulkSelectUnsuccessfulRows());
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
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);
}
public DbCommand CreateDeleteTriggerCommand(DbConnection connection, DbTransaction transaction)
{
var triggerName = this.mySqlObjectNames.GetTriggerCommandName(DbTriggerType.Delete);
StringBuilder createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE TRIGGER {triggerName} AFTER DELETE ON {tableName.Quoted().ToString()} FOR EACH ROW ");
createTrigger.AppendLine();
createTrigger.AppendLine();
createTrigger.AppendLine("BEGIN");
createTrigger.AppendLine($"\tINSERT INTO {this.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}old.{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,1");
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` = 1, ");
createTrigger.AppendLine($"\t`timestamp` = {MySqlObjectNames.TimestampValue}, ");
createTrigger.AppendLine("\t`last_change_datetime` = utc_timestamp()");
createTrigger.Append(";");
createTrigger.AppendLine("END");
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = createTrigger.ToString();
return(command);
}
public static async Task DropTriggerIfExistsAsync(SqlConnection connection, SqlTransaction transaction, int commandTimeout, string quotedTriggerName)
{
var triggerName = ParserName.Parse(quotedTriggerName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT tr.name FROM sys.triggers tr JOIN sys.tables t ON tr.parent_id = t.object_id JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE tr.name = @triggerName and s.name = @schemaName) DROP TRIGGER {0}", quotedTriggerName), connection))
{
sqlCommand.CommandTimeout = commandTimeout;
sqlCommand.Parameters.AddWithValue("@triggerName", triggerName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTriggerName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
sqlCommand.Transaction = transaction;
await sqlCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!alreadyOpened)
{
connection.Close();
}
}
}
public static async Task <bool> TypeExistsAsync(SqlConnection connection, SqlTransaction transaction, string quotedTypeName)
{
bool typeExist;
var columnName = ParserName.Parse(quotedTypeName).ToString();
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@typeName", columnName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTypeName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
sqlCommand.Transaction = transaction;
var result = await sqlCommand.ExecuteScalarAsync().ConfigureAwait(false);
typeExist = (int)result != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(typeExist);
}
public static async Task DropProcedureIfExistsAsync(SqlConnection connection, SqlTransaction transaction, int commandTimout, string quotedProcedureName)
{
var procName = ParserName.Parse(quotedProcedureName).ToString();
using (var sqlCommand = new SqlCommand(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) DROP PROCEDURE {0}", quotedProcedureName), connection))
{
sqlCommand.CommandTimeout = commandTimout;
sqlCommand.Parameters.AddWithValue("@procName", procName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedProcedureName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
sqlCommand.Transaction = transaction;
await sqlCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
if (!alreadyOpened)
{
connection.Close();
}
}
}
public MySqlScopeInfoBuilder(string scopeTableName, DbConnection connection, DbTransaction transaction = null)
{
this.connection = connection as MySqlConnection;
this.transaction = transaction as MySqlTransaction;
this.scopeTableName = ParserName.Parse(scopeTableName, "`");
}
public Task <DbCommand> GetRenameTrackingTableCommandAsync(ParserName oldTableName, DbConnection connection, DbTransaction transaction) => Task.FromResult <DbCommand>(null);
public SqlChangeTrackingBuilderProcedure(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, DbConnection connection, DbTransaction transaction = null)
: base(tableDescription, tableName, trackingName, setup, connection, transaction)
{
this.connection = connection as SqlConnection;
this.transaction = transaction as SqlTransaction;
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.sqlObjectNames = new SqlObjectNames(this.tableDescription, this.setup);
this.sqlDbMetadata = new SqlDbMetadata();
}
private void SetSelecteChangesParameters(DbCommand command, SyncFilter filter = null)
{
var p = command.CreateParameter();
p.ParameterName = "sync_min_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "sync_scope_id";
p.DbType = DbType.Guid;
command.Parameters.Add(p);
if (filter == null)
{
return;
}
var parameters = filter.Parameters;
if (parameters.Count == 0)
{
return;
}
foreach (var param in parameters)
{
if (param.DbType.HasValue)
{
// Get column name and type
var columnName = ParserName.Parse(param.Name, "`").Unquoted().Normalized().ToString();
var sqlDbType = (MySqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(null, param.DbType.Value, false, false, param.MaxLength, MySqlSyncProvider.ProviderType, MySqlSyncProvider.ProviderType);
var customParameterFilter = new MySqlParameter($"in_{columnName}", sqlDbType);
customParameterFilter.Size = param.MaxLength;
customParameterFilter.IsNullable = param.AllowNull;
customParameterFilter.Value = param.DefaultValue;
command.Parameters.Add(customParameterFilter);
}
else
{
var tableFilter = this.TableDescription.Schema.Tables[param.TableName, param.SchemaName];
if (tableFilter == null)
{
throw new FilterParamTableNotExistsException(param.TableName);
}
var columnFilter = tableFilter.Columns[param.Name];
if (columnFilter == null)
{
throw new FilterParamColumnNotExistsException(param.Name, param.TableName);
}
// Get column name and type
var columnName = ParserName.Parse(columnFilter).Unquoted().Normalized().ToString();
var sqlDbType = (SqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(columnFilter.OriginalDbType, columnFilter.GetDbType(), false, false, columnFilter.MaxLength, tableFilter.OriginalProvider, MySqlSyncProvider.ProviderType);
// Add it as parameter
var sqlParamFilter = new MySqlParameter($"in_{columnName}", sqlDbType);
sqlParamFilter.Size = columnFilter.MaxLength;
sqlParamFilter.IsNullable = param.AllowNull;
sqlParamFilter.Value = param.DefaultValue;
command.Parameters.Add(sqlParamFilter);
}
}
}
//------------------------------------------------------------------
// Delete command
//------------------------------------------------------------------
protected override SqlCommand BuildDeleteCommand()
{
var sqlCommand = new SqlCommand();
this.AddPkColumnParametersToCommand(sqlCommand);
var sqlParameter = new SqlParameter("@sync_force_write", SqlDbType.Int);
sqlCommand.Parameters.Add(sqlParameter);
var sqlParameter1 = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
sqlCommand.Parameters.Add(sqlParameter1);
var sqlParameter3 = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(sqlParameter3);
var sqlParameter2 = new SqlParameter("@sync_row_count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
sqlCommand.Parameters.Add(sqlParameter2);
string str4 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[CT]", "[p]");
string str5 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[changes]", "[base]");
string str6 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[t]", "[side]");
string str7 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[p]", "[side]");
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"SET {sqlParameter2.ParameterName} = 0;");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"DECLARE @var_sync_scope_id varbinary(128) = cast(@sync_scope_id as varbinary(128));");
stringBuilder.AppendLine();
stringBuilder.AppendLine($";WITH ");
stringBuilder.AppendLine($" CHANGE_TRACKING_CONTEXT(@var_sync_scope_id),");
stringBuilder.AppendLine($" {trackingName.Quoted().ToString()} AS (");
stringBuilder.Append("\tSELECT ");
foreach (var c in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(c).Quoted().ToString();
stringBuilder.Append($"[p].{columnName}, ");
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tCAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id], ");
stringBuilder.AppendLine($"\t[CT].[SYS_CHANGE_VERSION] as [timestamp],");
stringBuilder.AppendLine($"\tCASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.Append($"\tFROM (SELECT ");
string comma = "";
foreach (var c in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(c).Quoted().ToString();
var columnParameterName = ParserName.Parse(c).Unquoted().Normalized().ToString();
stringBuilder.Append($"{comma}@{columnParameterName} as {columnName}");
comma = ", ";
}
stringBuilder.AppendLine($") AS [p]");
stringBuilder.Append($"\tLEFT JOIN CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, @sync_min_timestamp) AS [CT] ON {str4}");
stringBuilder.AppendLine($"\t)");
stringBuilder.AppendLine($"DELETE {tableName.Schema().Quoted().ToString()}");
stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} [base]");
stringBuilder.Append($"JOIN {trackingName.Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[base]", "[side]"));
stringBuilder.AppendLine("WHERE ([side].[timestamp] <= @sync_min_timestamp OR [side].[timestamp] IS NULL OR [side].[update_scope_id] = @sync_scope_id OR @sync_force_write = 1)");
stringBuilder.Append("AND ");
stringBuilder.AppendLine(string.Concat("(", SqlManagementUtils.ColumnsAndParameters(this.tableDescription.PrimaryKeys, "[base]"), ");"));
stringBuilder.AppendLine();
stringBuilder.AppendLine(string.Concat("SET ", sqlParameter2.ParameterName, " = @@ROWCOUNT;"));
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
//------------------------------------------------------------------
// Select changes command
//------------------------------------------------------------------
private MySqlCommand BuildSelectInitializedChangesCommand(SyncFilter filter)
{
var sqlCommand = new MySqlCommand();
// Add filter parameters
if (filter != null)
{
CreateFilterParameters(sqlCommand, filter);
}
var stringBuilder = new StringBuilder("SELECT DISTINCT");
var columns = this.tableDescription.GetMutableColumns(false, true).ToList();
for (var i = 0; i < columns.Count; i++)
{
var mutableColumn = columns[i];
var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString();
stringBuilder.AppendLine($"\t`base`.{columnName}");
if (i < columns.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.AppendLine($"FROM {tableName.Quoted().ToString()} `base`");
if (filter != null)
{
// ----------------------------------
// Custom Joins
// ----------------------------------
stringBuilder.Append(CreateFilterCustomJoins(filter));
// ----------------------------------
// Where filters on [side]
// ----------------------------------
var whereString = CreateFilterWhereSide(filter);
var customWhereString = CreateFilterCustomWheres(filter);
if (!string.IsNullOrEmpty(whereString) || !string.IsNullOrEmpty(customWhereString))
{
stringBuilder.AppendLine("WHERE");
if (!string.IsNullOrEmpty(whereString))
{
stringBuilder.AppendLine(whereString);
}
if (!string.IsNullOrEmpty(whereString) && !string.IsNullOrEmpty(customWhereString))
{
stringBuilder.AppendLine("AND");
}
if (!string.IsNullOrEmpty(customWhereString))
{
stringBuilder.AppendLine(customWhereString);
}
}
}
// ----------------------------------
stringBuilder.Append(";");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
//------------------------------------------------------------------
// Update command
//------------------------------------------------------------------
protected override SqlCommand BuildUpdateCommand(bool hasMutableColumns)
{
var sqlCommand = new SqlCommand();
var stringBuilderArguments = new StringBuilder();
var stringBuilderParameters = new StringBuilder();
this.AddColumnParametersToCommand(sqlCommand);
string str4 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[CT]", "[p]");
string str5 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[changes]", "[base]");
string str6 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[t]", "[side]");
string str7 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[p]", "[side]");
var sqlParameter1 = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
sqlCommand.Parameters.Add(sqlParameter1);
var sqlParameter2 = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(sqlParameter2);
var sqlParameter3 = new SqlParameter("@sync_force_write", SqlDbType.Int);
sqlCommand.Parameters.Add(sqlParameter3);
var sqlParameter4 = new SqlParameter("@sync_row_count", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
sqlCommand.Parameters.Add(sqlParameter4);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
// Check if we have auto inc column
if (this.tableDescription.HasAutoIncrementColumns)
{
stringBuilder.AppendLine($"SET IDENTITY_INSERT {tableName.Schema().Quoted().ToString()} ON;");
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"DECLARE @var_sync_scope_id varbinary(128) = cast(@sync_scope_id as varbinary(128));");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"SET {sqlParameter4.ParameterName} = 0;");
stringBuilder.AppendLine();
stringBuilder.AppendLine($";WITH ");
stringBuilder.AppendLine($" CHANGE_TRACKING_CONTEXT(@var_sync_scope_id),");
stringBuilder.AppendLine($" {trackingName.Quoted().ToString()} AS (");
stringBuilder.Append("\tSELECT ");
foreach (var c in this.tableDescription.Columns.Where(col => !col.IsReadOnly))
{
var columnName = ParserName.Parse(c).Quoted().ToString();
stringBuilder.Append($"[p].{columnName}, ");
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tCAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id], ");
stringBuilder.AppendLine($"\t[CT].[SYS_CHANGE_VERSION] as [timestamp],");
stringBuilder.AppendLine($"\tCASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.AppendLine($"\tFROM (SELECT ");
stringBuilder.Append($"\t\t ");
string comma = "";
foreach (var c in this.tableDescription.Columns.Where(col => !col.IsReadOnly))
{
var columnName = ParserName.Parse(c).Quoted().ToString();
var columnParameterName = ParserName.Parse(c).Unquoted().Normalized().ToString();
stringBuilder.Append($"{comma}@{columnParameterName} as {columnName}");
comma = ", ";
}
stringBuilder.AppendLine($") AS [p]");
stringBuilder.Append($"\tLEFT JOIN CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, @sync_min_timestamp) AS [CT] ON {str4}");
stringBuilder.AppendLine($"\t)");
stringBuilder.AppendLine($"MERGE {tableName.Schema().Quoted().ToString()} AS [base]");
stringBuilder.AppendLine($"USING {trackingName.Quoted().ToString()} as [changes] ON {str5}");
if (hasMutableColumns)
{
stringBuilder.AppendLine("WHEN MATCHED AND ([changes].[timestamp] <= @sync_min_timestamp OR [changes].[timestamp] IS NULL OR [changes].[update_scope_id] = @sync_scope_id OR @sync_force_write = 1) THEN");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tUPDATE SET");
string strSeparator = "";
foreach (var mutableColumn in this.tableDescription.GetMutableColumns(false))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t{strSeparator}{columnName} = [changes].{columnName}");
strSeparator = ", ";
}
}
stringBuilder.AppendLine("WHEN NOT MATCHED BY TARGET AND ([changes].[timestamp] <= @sync_min_timestamp OR [changes].[timestamp] IS NULL OR @sync_force_write = 1) THEN");
stringBuilderArguments = new StringBuilder();
stringBuilderParameters = new StringBuilder();
var empty = string.Empty;
foreach (var mutableColumn in this.tableDescription.Columns.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.Append(string.Concat(empty, columnName));
stringBuilderParameters.Append(string.Concat(empty, $"[changes].{columnName}"));
empty = ", ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tINSERT");
stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})");
stringBuilder.AppendLine($"\tVALUES ({stringBuilderParameters.ToString()});");
stringBuilder.AppendLine();
// GET row count BEFORE make identity insert off again
stringBuilder.AppendLine($"SET {sqlParameter4.ParameterName} = @@ROWCOUNT;");
// Check if we have auto inc column
if (this.tableDescription.HasAutoIncrementColumns)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine($"SET IDENTITY_INSERT {tableName.Schema().Quoted().ToString()} OFF;");
stringBuilder.AppendLine();
}
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 = " AND ";
var lstPrimaryKeysColumns = this.tableDescription.GetPrimaryKeysColumns().ToList();
foreach (var column in lstPrimaryKeysColumns)
{
if (lstPrimaryKeysColumns.IndexOf(column) == lstPrimaryKeysColumns.Count - 1)
{
and = "";
}
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($"t_{param.ParameterName} IS NULL {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.PrimaryKeys, 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.PrimaryKeys, "")}");
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.PrimaryKeys, "")};");
stringBuilder.AppendLine($"END IF;");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
//------------------------------------------------------------------
// Select changes command
//------------------------------------------------------------------
protected override SqlCommand BuildSelectIncrementalChangesCommand(SyncFilter filter)
{
var sqlCommand = new SqlCommand();
var pTimestamp = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
var pScopeId = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(pTimestamp);
sqlCommand.Parameters.Add(pScopeId);
// Add filter parameters
if (filter != null)
{
CreateFilterParameters(sqlCommand, filter);
}
var stringBuilder = new StringBuilder("");
stringBuilder.AppendLine($";WITH ");
stringBuilder.AppendLine($" {trackingName.Quoted().ToString()} AS (");
stringBuilder.Append($"\tSELECT ");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append($"[CT].{columnName}, ");
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tCAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id], ");
stringBuilder.AppendLine($"\t[CT].[SYS_CHANGE_VERSION] as [timestamp],");
stringBuilder.AppendLine($"\tCASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.AppendLine($"\tFROM CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, @sync_min_timestamp) AS [CT]");
stringBuilder.AppendLine($"\t)");
stringBuilder.AppendLine("SELECT DISTINCT");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t[side].{columnName}, ");
}
foreach (var mutableColumn in this.tableDescription.GetMutableColumns())
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t[base].{columnName}, ");
}
stringBuilder.AppendLine($"\t[side].[sync_row_is_tombstone], ");
stringBuilder.AppendLine($"\t[side].[update_scope_id] ");
stringBuilder.AppendLine($"FROM {tableName.Schema().Quoted().ToString()} [base]");
stringBuilder.Append($"RIGHT JOIN {trackingName.Quoted().ToString()} [side]");
stringBuilder.Append($"ON ");
string empty = "";
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append($"{empty}[base].{columnName} = [side].{columnName}");
empty = " AND ";
}
// ----------------------------------
// Custom Joins
// ----------------------------------
if (filter != null)
{
stringBuilder.Append(CreateFilterCustomJoins(filter));
}
stringBuilder.AppendLine();
stringBuilder.AppendLine("WHERE (");
string str = string.Empty;
// ----------------------------------
// Where filters on [side]
// ----------------------------------
if (filter != null)
{
var createFilterWhereSide = CreateFilterWhereSide(filter, true);
stringBuilder.Append(createFilterWhereSide);
if (!string.IsNullOrEmpty(createFilterWhereSide))
{
stringBuilder.AppendLine($"AND ");
}
}
// ----------------------------------
// ----------------------------------
// Custom Where
// ----------------------------------
if (filter != null)
{
var createFilterCustomWheres = CreateFilterCustomWheres(filter);
stringBuilder.Append(createFilterCustomWheres);
if (!string.IsNullOrEmpty(createFilterCustomWheres))
{
stringBuilder.AppendLine($"AND ");
}
}
// ----------------------------------
stringBuilder.AppendLine("\t[side].[timestamp] > @sync_min_timestamp");
stringBuilder.AppendLine("\tAND ([side].[update_scope_id] <> @sync_scope_id OR [side].[update_scope_id] IS NULL)");
stringBuilder.AppendLine(")");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
/// <summary>
/// Create all side where criteria from within a filter
/// </summary>
protected string CreateFilterWhereSide(SyncFilter filter, bool checkTombstoneRows = false)
{
var sideWhereFilters = filter.Wheres;
if (sideWhereFilters.Count == 0)
{
return(string.Empty);
}
var stringBuilder = new StringBuilder();
// Managing when state is tombstone
if (checkTombstoneRows)
{
stringBuilder.AppendLine($"(");
}
stringBuilder.AppendLine($" (");
var and2 = " ";
foreach (var whereFilter in sideWhereFilters)
{
var tableFilter = this.tableDescription.Schema.Tables[whereFilter.TableName, whereFilter.SchemaName];
if (tableFilter == null)
{
throw new FilterParamTableNotExistsException(whereFilter.TableName);
}
var columnFilter = tableFilter.Columns[whereFilter.ColumnName];
if (columnFilter == null)
{
throw new FilterParamColumnNotExistsException(whereFilter.ColumnName, whereFilter.TableName);
}
var tableName = ParserName.Parse(tableFilter, "`").Unquoted().ToString();
if (string.Equals(tableName, filter.TableName, SyncGlobalization.DataSourceStringComparison))
{
tableName = "`base`";
}
else
{
tableName = ParserName.Parse(tableFilter, "`").Quoted().ToString();
}
var columnName = ParserName.Parse(columnFilter, "`").Quoted().ToString();
var parameterName = ParserName.Parse(whereFilter.ParameterName, "`").Unquoted().Normalized().ToString();
var sqlDbType = (MySqlDbType)this.mySqlDbMetadata.TryGetOwnerDbType(columnFilter.OriginalDbType, columnFilter.GetDbType(), false, false, columnFilter.MaxLength, tableFilter.OriginalProvider, MySqlSyncProvider.ProviderType);
var param = filter.Parameters[parameterName];
if (param == null)
{
throw new FilterParamColumnNotExistsException(columnName, whereFilter.TableName);
}
stringBuilder.Append($"{and2}({tableName}.{columnName} = in_{parameterName}");
if (param.AllowNull)
{
stringBuilder.Append($" OR in_{parameterName} IS NULL");
}
stringBuilder.Append($")");
and2 = " AND ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($" )");
if (checkTombstoneRows)
{
stringBuilder.AppendLine($" OR `side`.`sync_row_is_tombstone` = 1");
stringBuilder.AppendLine($")");
}
// Managing when state is tombstone
return(stringBuilder.ToString());
}
public static string DropTableIfExistsScriptText(string quotedTableName)
{
var tableName = ParserName.Parse(quotedTableName).ToString();
object[] escapedString = new object[] { tableName, SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTableName)), quotedTableName };
return(string.Format(CultureInfo.InvariantCulture, "IF EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = N'{0}' AND s.name = N'{1}') DROP TABLE {2}\n", escapedString));
}
//------------------------------------------------------------------
// Select changes command
//------------------------------------------------------------------
private MySqlCommand BuildSelectIncrementalChangesCommand(SyncFilter filter = null)
{
var sqlCommand = new MySqlCommand();
var sqlParameter1 = new MySqlParameter();
sqlParameter1.ParameterName = "sync_min_timestamp";
sqlParameter1.MySqlDbType = MySqlDbType.Int64;
sqlParameter1.Value = 0;
sqlCommand.Parameters.Add(sqlParameter1);
var sqlParameter3 = new MySqlParameter();
sqlParameter3.ParameterName = "sync_scope_id";
sqlParameter3.MySqlDbType = MySqlDbType.Guid;
sqlParameter3.Size = 36;
sqlParameter3.Value = "NULL";
sqlCommand.Parameters.Add(sqlParameter3);
// Add filter parameters
if (filter != null)
{
CreateFilterParameters(sqlCommand, filter);
}
var stringBuilder = new StringBuilder("SELECT DISTINCT");
// ----------------------------------
// Add all columns
// ----------------------------------
foreach (var pkColumn in this.tableDescription.PrimaryKeys)
{
var pkColumnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
stringBuilder.AppendLine($"\t`side`.{pkColumnName}, ");
}
foreach (var mutableColumn in this.tableDescription.GetMutableColumns())
{
var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString();
stringBuilder.AppendLine($"\t`base`.{columnName}, ");
}
stringBuilder.AppendLine($"\t`side`.`sync_row_is_tombstone`, ");
stringBuilder.AppendLine($"\t`side`.`update_scope_id` ");
stringBuilder.AppendLine($"FROM {tableName.Quoted().ToString()} `base`");
// ----------------------------------
// Make Right Join
// ----------------------------------
stringBuilder.Append($"RIGHT JOIN {trackingName.Quoted().ToString()} `side` ON ");
string empty = "";
foreach (var pkColumn in this.tableDescription.PrimaryKeys)
{
var pkColumnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
stringBuilder.Append($"{empty}`base`.{pkColumnName} = `side`.{pkColumnName}");
empty = " AND ";
}
// ----------------------------------
// Custom Joins
// ----------------------------------
if (filter != null)
{
stringBuilder.Append(CreateFilterCustomJoins(filter));
}
stringBuilder.AppendLine();
stringBuilder.AppendLine("WHERE (");
// ----------------------------------
// Where filters and Custom Where string
// ----------------------------------
if (filter != null)
{
var createFilterWhereSide = CreateFilterWhereSide(filter, true);
stringBuilder.Append(createFilterWhereSide);
if (!string.IsNullOrEmpty(createFilterWhereSide))
{
stringBuilder.AppendLine($"AND ");
}
var createFilterCustomWheres = CreateFilterCustomWheres(filter);
stringBuilder.Append(createFilterCustomWheres);
if (!string.IsNullOrEmpty(createFilterCustomWheres))
{
stringBuilder.AppendLine($"AND ");
}
}
// ----------------------------------
stringBuilder.AppendLine("\t`side`.`timestamp` > sync_min_timestamp");
stringBuilder.AppendLine("\tAND (`side`.`update_scope_id` <> sync_scope_id OR `side`.`update_scope_id` IS NULL) ");
stringBuilder.AppendLine(");");
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public static bool ProcedureExists(SqlConnection connection, SqlTransaction transaction, string quotedProcedureName)
{
bool flag;
var procedureName = ParserName.Parse(quotedProcedureName).ToString();
using (var sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.procedures p JOIN sys.schemas s ON s.schema_id = p.schema_id WHERE p.name = @procName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@procName", procedureName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedProcedureName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
flag = (int)sqlCommand.ExecuteScalar() != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(flag);
}
internal static SqlParameter[] DeriveParameters(this SqlConnection connection, SqlCommand cmd, bool includeReturnValueParameter = false, SqlTransaction transaction = null)
{
if (cmd == null)
{
throw new ArgumentNullException("SqlCommand");
}
var textParser = ParserName.Parse(cmd.CommandText);
string schemaName = textParser.SchemaName;
string spName = textParser.ObjectName;
var alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
try
{
var getParamsCommand = new SqlCommand("sp_procedure_params_rowset", connection);
getParamsCommand.CommandType = CommandType.StoredProcedure;
if (transaction != null)
{
getParamsCommand.Transaction = transaction;
}
var p = new SqlParameter("@procedure_name", SqlDbType.NVarChar);
p.Value = spName;
getParamsCommand.Parameters.Add(p);
p = new SqlParameter("@procedure_schema", SqlDbType.NVarChar);
p.Value = schemaName;
getParamsCommand.Parameters.Add(p);
var sdr = getParamsCommand.ExecuteReader();
// Do we have any rows?
if (sdr.HasRows)
{
using (sdr)
{
// Read the parameter information
int ParamNameCol = sdr.GetOrdinal("PARAMETER_NAME");
int ParamSizeCol = sdr.GetOrdinal("CHARACTER_MAXIMUM_LENGTH");
int ParamTypeCol = sdr.GetOrdinal("TYPE_NAME");
int ParamNullCol = sdr.GetOrdinal("IS_NULLABLE");
int ParamPrecCol = sdr.GetOrdinal("NUMERIC_PRECISION");
int ParamDirCol = sdr.GetOrdinal("PARAMETER_TYPE");
int ParamScaleCol = sdr.GetOrdinal("NUMERIC_SCALE");
// Loop through and read the rows
while (sdr.Read())
{
string name = sdr.GetString(ParamNameCol);
string datatype = sdr.GetString(ParamTypeCol);
// Is this xml?
// ADO.NET 1.1 does not support XML, replace with text
//if (0 == String.Compare("xml", datatype, true))
// datatype = "Text";
if (0 == String.Compare("table", datatype, true))
{
datatype = "Structured";
}
// TODO : Should we raise an error here ??
if (!Enum.TryParse(datatype, true, out SqlDbType type))
{
type = SqlDbType.Variant;
}
bool Nullable = sdr.GetBoolean(ParamNullCol);
var param = new SqlParameter(name, type);
// Determine parameter direction
int dir = sdr.GetInt16(ParamDirCol);
switch (dir)
{
case 1:
param.Direction = ParameterDirection.Input;
break;
case 2:
param.Direction = ParameterDirection.Output;
break;
case 3:
param.Direction = ParameterDirection.InputOutput;
break;
case 4:
param.Direction = ParameterDirection.ReturnValue;
break;
}
param.IsNullable = Nullable;
if (!sdr.IsDBNull(ParamPrecCol))
{
param.Precision = (Byte)sdr.GetInt16(ParamPrecCol);
}
if (!sdr.IsDBNull(ParamSizeCol))
{
param.Size = sdr.GetInt32(ParamSizeCol);
}
if (!sdr.IsDBNull(ParamScaleCol))
{
param.Scale = (Byte)sdr.GetInt16(ParamScaleCol);
}
cmd.Parameters.Add(param);
}
}
}
}
finally
{
if (!alreadyOpened)
{
connection.Close();
}
}
if (!includeReturnValueParameter && cmd.Parameters.Count > 0)
{
cmd.Parameters.RemoveAt(0);
}
SqlParameter[] discoveredParameters = new SqlParameter[cmd.Parameters.Count];
cmd.Parameters.CopyTo(discoveredParameters, 0);
// Init the parameters with a DBNull value
foreach (var discoveredParameter in discoveredParameters)
{
discoveredParameter.Value = DBNull.Value;
}
return(discoveredParameters);
}
public static bool TypeExists(SqlConnection connection, SqlTransaction transaction, string quotedTypeName)
{
bool typeExist;
var columnName = ParserName.Parse(quotedTypeName).ToString();
using (SqlCommand sqlCommand = new SqlCommand("IF EXISTS (SELECT * FROM sys.types t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @typeName AND s.name = @schemaName) SELECT 1 ELSE SELECT 0", connection))
{
sqlCommand.Parameters.AddWithValue("@typeName", columnName);
sqlCommand.Parameters.AddWithValue("@schemaName", SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(quotedTypeName)));
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
connection.Open();
}
if (transaction != null)
{
sqlCommand.Transaction = transaction;
}
typeExist = (int)sqlCommand.ExecuteScalar() != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(typeExist);
}
//------------------------------------------------------------------
// Bulk Update command
//------------------------------------------------------------------
protected override SqlCommand BuildBulkUpdateCommand(bool hasMutableColumns)
{
var sqlCommand = new SqlCommand();
var stringBuilderArguments = new StringBuilder();
var stringBuilderParameters = new StringBuilder();
string empty = string.Empty;
var sqlParameter = new SqlParameter("@sync_min_timestamp", SqlDbType.BigInt);
sqlCommand.Parameters.Add(sqlParameter);
var sqlParameter1 = new SqlParameter("@sync_scope_id", SqlDbType.UniqueIdentifier);
sqlCommand.Parameters.Add(sqlParameter1);
var sqlParameter2 = new SqlParameter("@changeTable", SqlDbType.Structured)
{
TypeName = this.sqlObjectNames.GetCommandName(DbCommandType.BulkTableType).name
};
sqlCommand.Parameters.Add(sqlParameter2);
string str4 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[CT]", "[p]");
string str5 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[changes]", "[base]");
string str6 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[t]", "[side]");
string str7 = SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[p]", "[side]");
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("-- use a temp table to store the list of PKs that successfully got updated/inserted");
stringBuilder.Append("declare @dms_changed TABLE (");
foreach (var c in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(c).Quoted().ToString();
// Get the good SqlDbType (even if we are not from Sql Server def)
var sqlDbTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(c.OriginalDbType, c.GetDbType(), false, false, c.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var quotedColumnType = ParserName.Parse(sqlDbTypeString).Quoted().ToString();
quotedColumnType += this.sqlDbMetadata.TryGetOwnerDbTypePrecision(c.OriginalDbType, c.GetDbType(), false, false, c.MaxLength, c.Precision, c.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
stringBuilder.Append($"{columnName} {quotedColumnType}, ");
}
stringBuilder.Append(" PRIMARY KEY (");
var pkeyComma = " ";
foreach (var primaryKeyColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(primaryKeyColumn).Quoted().ToString();
stringBuilder.Append($"{pkeyComma}{columnName}");
pkeyComma = ", ";
}
stringBuilder.AppendLine("));");
stringBuilder.AppendLine();
// Check if we have auto inc column
if (this.tableDescription.HasAutoIncrementColumns)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine($"SET IDENTITY_INSERT {tableName.Schema().Quoted().ToString()} ON;");
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"DECLARE @var_sync_scope_id varbinary(128) = cast(@sync_scope_id as varbinary(128));");
stringBuilder.AppendLine();
stringBuilder.AppendLine($";WITH ");
stringBuilder.AppendLine($" CHANGE_TRACKING_CONTEXT(@var_sync_scope_id),");
stringBuilder.AppendLine($" {trackingName.Quoted().ToString()} AS (");
stringBuilder.Append($"\tSELECT ");
foreach (var c in this.tableDescription.Columns.Where(col => !col.IsReadOnly))
{
var columnName = ParserName.Parse(c).Quoted().ToString();
stringBuilder.Append($"[p].{columnName}, ");
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tCAST([CT].[SYS_CHANGE_CONTEXT] as uniqueidentifier) AS [update_scope_id], ");
stringBuilder.AppendLine($"\t[CT].[SYS_CHANGE_VERSION] as [timestamp],");
stringBuilder.AppendLine($"\tCASE WHEN [CT].[SYS_CHANGE_OPERATION] = 'D' THEN 1 ELSE 0 END AS [sync_row_is_tombstone]");
stringBuilder.AppendLine($"\tFROM @changeTable AS [p] ");
stringBuilder.AppendLine($"\tLEFT JOIN CHANGETABLE(CHANGES {tableName.Schema().Quoted().ToString()}, @sync_min_timestamp) AS [CT] ON {str4}");
stringBuilder.AppendLine($"\t)");
stringBuilder.AppendLine($"MERGE {tableName.Schema().Quoted().ToString()} AS [base]");
stringBuilder.AppendLine($"USING {trackingName.Quoted().ToString()} as [changes] ON {str5}");
if (hasMutableColumns)
{
stringBuilder.AppendLine("WHEN MATCHED AND ([changes].[timestamp] <= @sync_min_timestamp OR [changes].[timestamp] IS NULL OR [changes].[update_scope_id] = @sync_scope_id) THEN");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tUPDATE SET");
string strSeparator = "";
foreach (var mutableColumn in this.tableDescription.GetMutableColumns(false))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t{strSeparator}{columnName} = [changes].{columnName}");
strSeparator = ", ";
}
}
stringBuilder.AppendLine("WHEN NOT MATCHED BY TARGET AND ([changes].[timestamp] <= @sync_min_timestamp OR [changes].[timestamp] IS NULL) THEN");
stringBuilderArguments = new StringBuilder();
stringBuilderParameters = new StringBuilder();
empty = string.Empty;
foreach (var mutableColumn in this.tableDescription.Columns.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.Append(string.Concat(empty, columnName));
stringBuilderParameters.Append(string.Concat(empty, $"[changes].{columnName}"));
empty = ", ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tINSERT");
stringBuilder.AppendLine($"\t({stringBuilderArguments.ToString()})");
stringBuilder.AppendLine($"\tVALUES ({stringBuilderParameters.ToString()})");
stringBuilder.AppendLine();
stringBuilder.Append($"OUTPUT ");
pkeyComma = " ";
foreach (var primaryKeyColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(primaryKeyColumn).Quoted().ToString();
stringBuilder.Append($"{pkeyComma}INSERTED.{columnName}");
pkeyComma = ", ";
}
stringBuilder.AppendLine();
stringBuilder.AppendLine($"\tINTO @dms_changed; -- populates the temp table with successful PKs");
stringBuilder.AppendLine();
// Check if we have auto inc column
if (this.tableDescription.HasAutoIncrementColumns)
{
stringBuilder.AppendLine();
stringBuilder.AppendLine($"SET IDENTITY_INSERT {tableName.Schema().Quoted().ToString()} OFF;");
stringBuilder.AppendLine();
}
stringBuilder.Append(BulkSelectUnsuccessfulRows());
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
public MySqlBuilderProcedure(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup)
{
this.tableDescription = tableDescription;
this.setup = setup;
this.tableName = tableName;
this.trackingName = trackingName;
this.mySqlObjectNames = new MySqlObjectNames(this.tableDescription, tableName, trackingName, this.setup);
this.mySqlDbMetadata = new MySqlDbMetadata();
}
public SqlChangeTrackingBuilderTrigger(SyncTable tableDescription, ParserName tableName, ParserName trackingName, SyncSetup setup, string scopeName)
: base(tableDescription, tableName, trackingName, setup, scopeName)
{
this.tableName = tableName;
}