private string CreateUpdateMetadataCommand()
{
var stringBuilder = new StringBuilder();
var pkeysForUpdate = new StringBuilder();
var pkeySelectForInsert = new StringBuilder();
var pkeyISelectForInsert = new StringBuilder();
var pkeyAliasSelectForInsert = new StringBuilder();
var pkeysLeftJoinForInsert = new StringBuilder();
var pkeysIsNullForInsert = new StringBuilder();
string and = string.Empty;
string comma = string.Empty;
foreach (var pkColumn in TableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
var parameterName = ParserName.Parse(pkColumn).Unquoted().Normalized().ToString();
pkeysForUpdate.Append($"{and}[side].{columnName} = @{parameterName}");
pkeySelectForInsert.Append($"{comma}{columnName}");
pkeyISelectForInsert.Append($"{comma}[i].{columnName}");
pkeyAliasSelectForInsert.Append($"{comma}@{parameterName} as {columnName}");
pkeysLeftJoinForInsert.Append($"{and}[side].{columnName} = [i].{columnName}");
pkeysIsNullForInsert.Append($"{and}[side].{columnName} IS NULL");
and = " AND ";
comma = ", ";
}
stringBuilder.AppendLine($"UPDATE [side] SET ");
stringBuilder.AppendLine($" [update_scope_id] = @sync_scope_id, ");
stringBuilder.AppendLine($" [sync_row_is_tombstone] = @sync_row_is_tombstone, ");
stringBuilder.AppendLine($" [last_change_datetime] = GETUTCDATE() ");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"WHERE ");
stringBuilder.Append(pkeysForUpdate.ToString());
stringBuilder.AppendLine($";");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine(pkeySelectForInsert.ToString());
stringBuilder.AppendLine(",[update_scope_id], [sync_row_is_tombstone],[last_change_datetime] )");
stringBuilder.AppendLine($"SELECT {pkeyISelectForInsert.ToString()} ");
stringBuilder.AppendLine($" , i.sync_scope_id, i.sync_row_is_tombstone, i.UtcDate");
stringBuilder.AppendLine("FROM (");
stringBuilder.AppendLine($" SELECT {pkeyAliasSelectForInsert}");
stringBuilder.AppendLine($" ,@sync_scope_id as sync_scope_id, @sync_row_is_tombstone as sync_row_is_tombstone, GETUTCDATE() as UtcDate) as i");
stringBuilder.AppendLine($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON {pkeysLeftJoinForInsert.ToString()} ");
stringBuilder.AppendLine($"WHERE {pkeysIsNullForInsert.ToString()};");
return(stringBuilder.ToString());
}
public Task <DbCommand> GetCreateTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var commandText = $"ALTER TABLE {tableName.Schema().Quoted().ToString()} ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF);";
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandText;
return(Task.FromResult(command));
}
public virtual Task CreateDeleteTriggerAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET [sync_row_is_tombstone] = 1");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- scope id is always NULL when update is made locally");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[d]"));
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}[d].{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}[side].{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,[update_scope_id]");
stringBuilder.AppendLine("\t,[sync_row_is_tombstone]");
stringBuilder.AppendLine("\t,[last_change_datetime]");
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("SELECT");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,1");
stringBuilder.AppendLine("\t,GetUtcDate()");
stringBuilder.AppendLine("FROM DELETED [d]");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(CreateTriggerAsync(connection, transaction, DbCommandType.DeleteTrigger, stringBuilder.ToString()));
}
private string DeleteTriggerBodyText()
{
var stringBuilder = new StringBuilder();
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringBuilderArguments3 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = " ";
string argAnd = string.Empty;
var primaryKeys = this.tableDescription.GetPrimaryKeysColumns();
foreach (var mutableColumn in primaryKeys.Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn, "\"").Quoted().ToString();
stringBuilderArguments.AppendLine($"\t{argComma}OLD.{columnName}");
stringBuilderArguments2.AppendLine($"\t{argComma}{columnName}");
stringBuilderArguments3.Append($"{argComma}{columnName}");
stringPkAreNull.Append($"{argAnd}side.{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.AppendLine($"INSERT INTO {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine($"\t,update_scope_id");
stringBuilder.AppendLine($"\t,sync_row_is_tombstone");
stringBuilder.AppendLine($"\t,last_change_datetime");
stringBuilder.AppendLine($"\t,timestamp");
stringBuilder.AppendLine($") ");
stringBuilder.AppendLine($"VALUES (");
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine($"\t,NULL");
stringBuilder.AppendLine($"\t,true");
stringBuilder.AppendLine($"\t,now()");
stringBuilder.AppendLine($"\t,{NpgsqlScopeInfoBuilder.TimestampValue}");
stringBuilder.AppendLine($")");
stringBuilder.AppendLine($"ON CONFLICT({stringBuilderArguments3.ToString()})");
stringBuilder.AppendLine($"DO UPDATE SET");
stringBuilder.AppendLine($"\tsync_row_is_tombstone = true");
stringBuilder.AppendLine($"\t,update_scope_id = NULL");
stringBuilder.AppendLine($"\t,last_change_datetime = now()");
stringBuilder.AppendLine($"\t,timestamp = {NpgsqlScopeInfoBuilder.TimestampValue};");
return(stringBuilder.ToString());
}
public async Task CreateTableAsync(DbConnection connection, DbTransaction transaction)
{
var changetrackingtable = await SqlChangeTrackingManagementUtils.ChangeTrackingTableAsync(
(SqlConnection)connection, (SqlTransaction)transaction, tableName.ToString(), tableName.SchemaName);
if (changetrackingtable != null && changetrackingtable.Rows != null && changetrackingtable.Rows.Count > 0)
{
return;
}
var commandText = $"ALTER TABLE {tableName.Schema().Quoted().ToString()} ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF);";
using var command = new SqlCommand(commandText, (SqlConnection)connection, (SqlTransaction)transaction);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private async Task CreateTriggerAsync(DbConnection connection, DbTransaction transaction, DbCommandType triggerType, string commandText)
{
var commandTriggerName = this.sqlObjectNames.GetCommandName(triggerType).name;
var triggerName = ParserName.Parse(commandTriggerName).ToString();
var triggerSchemaName = SqlManagementUtils.GetUnquotedSqlSchemaName(ParserName.Parse(commandTriggerName));
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("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)");
stringBuilder.AppendLine($"DROP TRIGGER {commandTriggerName};");
using var commandDrop = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
commandDrop.Parameters.AddWithValue("@triggerName", triggerName);
commandDrop.Parameters.AddWithValue("@schemaName", triggerSchemaName);
await commandDrop.ExecuteNonQueryAsync().ConfigureAwait(false);
string triggerFor = triggerType == DbCommandType.DeleteTrigger ? "DELETE"
: triggerType == DbCommandType.UpdateTrigger ? "UPDATE"
: "INSERT";
stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TRIGGER {commandTriggerName} ON {tableName.Schema().Quoted().ToString()} FOR {triggerFor} AS");
stringBuilder.AppendLine(commandText);
using var commandCreate = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
await commandCreate.ExecuteNonQueryAsync().ConfigureAwait(false);
}
private string CreateIndexCommandText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{trackingName.Schema().Unquoted().Normalized().ToString()}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t [timestamp_bigint] ASC");
stringBuilder.AppendLine($"\t, [update_scope_id] ASC");
stringBuilder.AppendLine($"\t, [sync_row_is_tombstone] ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
public virtual Task <DbCommand> GetCreateTriggerCommandAsync(DbTriggerType triggerType, DbConnection connection, DbTransaction transaction)
{
var commandTriggerCommandString = triggerType switch
{
DbTriggerType.Delete => CreateDeleteTriggerAsync(),
DbTriggerType.Insert => CreateInsertTriggerAsync(),
DbTriggerType.Update => CreateUpdateTriggerAsync(),
_ => throw new NotImplementedException()
};
string triggerFor = triggerType == DbTriggerType.Delete ? "DELETE"
: triggerType == DbTriggerType.Update ? "UPDATE"
: "INSERT";
var commandTriggerName = this.sqlObjectNames.GetTriggerCommandName(triggerType);
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TRIGGER {commandTriggerName} ON {tableName.Schema().Quoted().ToString()} FOR {triggerFor} AS");
stringBuilder.AppendLine(commandTriggerCommandString);
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = stringBuilder.ToString();
return(Task.FromResult(command));
}
}
private string CreateIndexCommandText()
{
var stringBuilder = new StringBuilder();
var indexName = trackingName.Schema().Unquoted().Normalized().ToString();
var tableName = trackingName.Schema().Quoted().ToString();
stringBuilder.AppendLine($"CREATE INDEX {indexName}_timestamp_index ON {tableName} (");
stringBuilder.AppendLine($"\t timestamp ASC");
stringBuilder.AppendLine($"\t, update_scope_id ASC");
stringBuilder.AppendLine($"\t, sync_row_is_tombstone ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn, "\"").Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
public Task <DbCommand> GetCreateTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var command = connection.CreateCommand();
if (setup.HasTableWithColumns(tableDescription.TableName))
{
command.CommandText = $"ALTER TABLE {tableName.Schema().Quoted().ToString()} ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON);";
}
else
{
command.CommandText = $"ALTER TABLE {tableName.Schema().Quoted().ToString()} ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = OFF);";
}
command.Connection = connection;
command.Transaction = transaction;
return(Task.FromResult(command));
}
public string CreateTableCommandText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE IF NOT EXISTS {trackingName.Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
var columnTypeString = this.sqliteDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, SqliteSyncProvider.ProviderType);
var columnPrecisionString = this.sqliteDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, SqliteSyncProvider.ProviderType);
var quotedColumnType = ParserName.Parse(columnTypeString).Quoted().ToString();
quotedColumnType += columnPrecisionString;
stringBuilder.AppendLine($"{quotedColumnName} {quotedColumnType} NOT NULL COLLATE NOCASE, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"[update_scope_id] [text] NULL COLLATE NOCASE, ");
stringBuilder.AppendLine($"[timestamp] [integer] NULL, ");
stringBuilder.AppendLine($"[sync_row_is_tombstone] [integer] NOT NULL default(0), ");
stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, ");
stringBuilder.Append(" PRIMARY KEY (");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
stringBuilder.Append(");");
stringBuilder.AppendLine($"CREATE INDEX IF NOT EXISTS [{trackingName.Schema().Unquoted().Normalized().ToString()}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t [timestamp] ASC");
stringBuilder.AppendLine($"\t,[update_scope_id] ASC");
stringBuilder.AppendLine($"\t,[sync_row_is_tombstone] ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(");");
return(stringBuilder.ToString());
}
private SqlCommand BuildPkCommand()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"ALTER TABLE {tableName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{tableName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(new SqlCommand(stringBuilder.ToString()));
}
private NpgsqlCommand BuildPkCommand(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tableNameString = tableName.Schema().Quoted().ToString();
var primaryKeyNameString = tableName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"ALTER TABLE {tableNameString} ADD CONSTRAINT \"PK_{primaryKeyNameString}\" PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn, "\"").Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(")");
return(new NpgsqlCommand(stringBuilder.ToString(), (NpgsqlConnection)connection, (NpgsqlTransaction)transaction));
}
private string DeleteTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET \t[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] = @@DBTS+1");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
// Filter columns
if (this.Filters != null && Filters.Count > 0)
{
foreach (var filter in this.Filters)
{
var columnFilter = this.tableDescription.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}");
}
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == columnFilter.ColumnName))
{
continue;
}
var columnName = ParserName.Parse(columnFilter).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} = [d].{columnName}");
}
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(SqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, "[side]", "[d]"));
return(stringBuilder.ToString());
}
public void CreateDeleteTrigger()
{
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
using (var command = new SqlCommand())
{
if (!alreadyOpened)
{
this.connection.Open();
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
var createTrigger = new StringBuilder($"CREATE TRIGGER {delTriggerName} ON {tableName.Schema().Quoted().ToString()} FOR DELETE AS");
createTrigger.AppendLine();
createTrigger.AppendLine(this.DeleteTriggerBodyText());
command.CommandText = createTrigger.ToString();
command.Connection = this.connection;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateDeleteTrigger : {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
private void CreateUpdateUntrackedRowsCommand()
{
var stringBuilder = new StringBuilder();
var str1 = new StringBuilder();
var str2 = new StringBuilder();
var str3 = new StringBuilder();
var str4 = SqliteManagementUtils.JoinTwoTablesOnClause(this.TableDescription.PrimaryKeys, "[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, {SqliteObjectNames.TimestampValue}, datetime('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();
this.AddName(DbCommandType.UpdateUntrackedRows, r);
}
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);
}
public virtual async Task CreateDeleteTriggerAsync(DbConnection connection, DbTransaction transaction)
{
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
var createTrigger = new StringBuilder($"CREATE TRIGGER {delTriggerName} ON {tableName.Schema().Quoted().ToString()} FOR DELETE AS");
createTrigger.AppendLine();
createTrigger.AppendLine(this.DeleteTriggerBodyText());
using (var command = new SqlCommand(createTrigger.ToString(), (SqlConnection)connection, (SqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public virtual async Task CreateDeleteTriggerAsync(DbConnection connection, DbTransaction transaction)
{
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
var delTriggerParseName = ParserName.Parse(delTriggerName, "\"");
var trigger = new StringBuilder();
trigger.AppendLine($"CREATE OR REPLACE FUNCTION {delTriggerName}()");
trigger.AppendLine($"RETURNS TRIGGER AS");
trigger.AppendLine($"$BODY$");
trigger.AppendLine($"BEGIN");
trigger.AppendLine(this.DeleteTriggerBodyText());
trigger.AppendLine($"RETURN NULL;");
trigger.AppendLine($"END;");
trigger.AppendLine($"$BODY$");
trigger.AppendLine($"lANGUAGE 'plpgsql';");
trigger.AppendLine($"DROP TRIGGER IF EXISTS {delTriggerParseName.Quoted().ToString()} on {tableName.Schema().Quoted().ToString()};");
trigger.AppendLine($"CREATE TRIGGER {delTriggerParseName.Quoted().ToString()} AFTER DELETE ON {tableName.Schema().Quoted().ToString()}");
trigger.AppendLine($"FOR EACH ROW EXECUTE FUNCTION {delTriggerName}();");
trigger.AppendLine($"");
using (var command = new NpgsqlCommand(trigger.ToString(), (NpgsqlConnection)connection, (NpgsqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public Task <DbCommand> GetCreateTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tbl = trackingName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(trackingName);
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Schema().Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
var columnType = this.sqlDbMetadata.GetCompatibleColumnTypeDeclarationString(pkColumn, this.tableDescription.OriginalProvider);
var nullableColumn = pkColumn.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"[update_scope_id] [uniqueidentifier] NULL, ");
stringBuilder.AppendLine($"[timestamp] [timestamp] NULL, ");
stringBuilder.AppendLine($"[timestamp_bigint] AS (CONVERT([bigint],[timestamp])) PERSISTED, ");
stringBuilder.AppendLine($"[sync_row_is_tombstone] [bit] NOT NULL default(0), ");
stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, ");
stringBuilder.AppendLine(");");
// Primary Keys
stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{trackingName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY (");
var primaryKeysColumns = this.tableDescription.GetPrimaryKeysColumns().ToList();
for (int i = 0; i < primaryKeysColumns.Count; i++)
{
var pkColumn = primaryKeysColumns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < primaryKeysColumns.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.AppendLine(");");
// Index
var indexName = trackingName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{indexName}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t [timestamp_bigint] ASC");
stringBuilder.AppendLine($"\t, [update_scope_id] ASC");
stringBuilder.AppendLine($"\t, [sync_row_is_tombstone] ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(");");
var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction);
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
return(Task.FromResult((DbCommand)command));
}
//------------------------------------------------------------------
// 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 @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 (");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var cc = ParserName.Parse(this.tableDescription.PrimaryKeys[i]).Quoted().ToString();
stringBuilder.Append($"{cc}");
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
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 ");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var cc = ParserName.Parse(this.tableDescription.PrimaryKeys[i]).Quoted().ToString();
stringBuilder.Append($"DELETED.{cc}");
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
else
{
stringBuilder.AppendLine();
}
}
stringBuilder.AppendLine($"INTO @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 virtual async Task CreateDeleteTriggerAsync()
{
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
using (var command = new NpgsqlCommand())
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
var delTriggerParseName = ParserName.Parse(delTriggerName, "\"");
var createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE OR REPLACE FUNCTION {delTriggerName}()");
createTrigger.AppendLine($"RETURNS TRIGGER AS");
createTrigger.AppendLine($"$BODY$");
createTrigger.AppendLine($"BEGIN");
createTrigger.AppendLine(this.DeleteTriggerBodyText());
createTrigger.AppendLine($"RETURN NULL;");
createTrigger.AppendLine($"END;");
createTrigger.AppendLine($"$BODY$");
createTrigger.AppendLine($"lANGUAGE 'plpgsql';");
createTrigger.AppendLine($"DROP TRIGGER IF EXISTS {delTriggerParseName.Quoted().ToString()} on {tableName.Schema().Quoted().ToString()};");
createTrigger.AppendLine($"CREATE TRIGGER {delTriggerParseName.Quoted().ToString()} AFTER DELETE ON {tableName.Schema().Quoted().ToString()}");
createTrigger.AppendLine($"FOR EACH ROW EXECUTE FUNCTION {delTriggerName}();");
createTrigger.AppendLine($"");
command.CommandText = createTrigger.ToString();
command.Connection = this.connection;
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateDeleteTrigger : {ex}");
throw;
}
finally
{
if (!alreadyOpened && this.connection.State != ConnectionState.Closed)
{
this.connection.Close();
}
}
}
private string CreateIndexCommandText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{trackingName.Schema().Unquoted().Normalized().ToString()}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t[update_timestamp] ASC");
stringBuilder.AppendLine($"\t,[update_scope_id] ASC");
stringBuilder.AppendLine($"\t,[sync_row_is_tombstone] ASC");
// Filter columns
if (this.Filters != null && this.Filters.Count > 0)
{
foreach (var filterColumn in this.Filters)
{
// check if the filter column is already a primary key.
// in this case, we will add it as an index in the next foreach
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
if (!this.tableDescription.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = ParserName.Parse(filterColumn.ColumnName).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
}
foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns)
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(")");
return(stringBuilder.ToString());
}
private string CreateDropTableCommandText() => $"ALTER TABLE {tableName.Schema().Quoted().ToString()} DISABLE CHANGE_TRACKING;";
public async Task CreateTableAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tbl = trackingName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(trackingName);
stringBuilder.AppendLine("IF NOT EXISTS (SELECT t.name FROM sys.tables t JOIN sys.schemas s ON s.schema_id = t.schema_id WHERE t.name = @tableName AND s.name = @schemaName) ");
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Schema().Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
var columnTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var quotedColumnType = ParserName.Parse(columnTypeString).Quoted().ToString();
var columnPrecisionString = this.sqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnType = $"{quotedColumnType} {columnPrecisionString}";
var nullableColumn = pkColumn.AllowDBNull ? "NULL" : "NOT NULL";
stringBuilder.AppendLine($"{quotedColumnName} {columnType} {nullableColumn}, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"[update_scope_id] [uniqueidentifier] NULL, ");
stringBuilder.AppendLine($"[timestamp] [timestamp] NULL, ");
stringBuilder.AppendLine($"[timestamp_bigint] AS (CONVERT([bigint],[timestamp])) PERSISTED, ");
stringBuilder.AppendLine($"[sync_row_is_tombstone] [bit] NOT NULL default(0), ");
stringBuilder.AppendLine($"[last_change_datetime] [datetime] NULL, ");
stringBuilder.Append(");");
// Primary Keys
stringBuilder.Append($"ALTER TABLE {trackingName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{trackingName.Schema().Unquoted().Normalized().ToString()}] PRIMARY KEY (");
var primaryKeysColumns = this.tableDescription.GetPrimaryKeysColumns().ToList();
for (int i = 0; i < primaryKeysColumns.Count; i++)
{
var pkColumn = primaryKeysColumns[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < primaryKeysColumns.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.Append(");");
// Index
var indexName = trackingName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"CREATE NONCLUSTERED INDEX [{indexName}_timestamp_index] ON {trackingName.Schema().Quoted().ToString()} (");
stringBuilder.AppendLine($"\t [timestamp_bigint] ASC");
stringBuilder.AppendLine($"\t, [update_scope_id] ASC");
stringBuilder.AppendLine($"\t, [sync_row_is_tombstone] ASC");
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} ASC");
}
stringBuilder.Append(");");
stringBuilder.AppendLine("END");
using (var command = new SqlCommand(stringBuilder.ToString(), (SqlConnection)connection, (SqlTransaction)transaction))
{
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
private SqlCommand BuildCreateTableCommand(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
var tbl = tableName.ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
stringBuilder.AppendLine($"CREATE TABLE {tableName.Schema().Quoted().ToString()} (");
string empty = string.Empty;
stringBuilder.AppendLine();
foreach (var column in this.tableDescription.Columns)
{
var columnName = ParserName.Parse(column).Quoted().ToString();
var columnTypeString = this.sqlDbMetadata.TryGetOwnerDbTypeString(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnPrecisionString = this.sqlDbMetadata.TryGetOwnerDbTypePrecision(column.OriginalDbType, column.GetDbType(), false, false, column.MaxLength, column.Precision, column.Scale, this.tableDescription.OriginalProvider, SqlSyncProvider.ProviderType);
var columnType = $"{columnTypeString} {columnPrecisionString}";
var identity = string.Empty;
if (column.IsAutoIncrement)
{
var s = column.GetAutoIncrementSeedAndStep();
identity = $"IDENTITY({s.Seed},{s.Step})";
}
var nullString = column.AllowDBNull ? "NULL" : "NOT NULL";
// if we have a computed column, we should allow null
if (column.IsReadOnly)
{
nullString = "NULL";
}
string defaultValue = string.Empty;
if (this.tableDescription.OriginalProvider == SqlSyncProvider.ProviderType)
{
if (!string.IsNullOrEmpty(column.DefaultValue))
{
defaultValue = "DEFAULT " + column.DefaultValue;
}
}
stringBuilder.AppendLine($"\t{empty}{columnName} {columnType} {identity} {nullString} {defaultValue}");
empty = ",";
}
stringBuilder.AppendLine(");");
// Primary Keys
var primaryKeyNameString = tableName.Schema().Unquoted().Normalized().ToString();
stringBuilder.AppendLine($"ALTER TABLE {tableName.Schema().Quoted().ToString()} ADD CONSTRAINT [PK_{primaryKeyNameString}] PRIMARY KEY(");
for (int i = 0; i < this.tableDescription.PrimaryKeys.Count; i++)
{
var pkColumn = this.tableDescription.PrimaryKeys[i];
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder.Append(quotedColumnName);
if (i < this.tableDescription.PrimaryKeys.Count - 1)
{
stringBuilder.Append(", ");
}
}
stringBuilder.AppendLine(");");
// Foreign Keys
foreach (var constraint in this.tableDescription.GetRelations())
{
var tableName = ParserName.Parse(constraint.GetTable()).Quoted().Schema().ToString();
var parentTableName = ParserName.Parse(constraint.GetParentTable()).Quoted().Schema().ToString();
var relationName = NormalizeRelationName(constraint.RelationName);
stringBuilder.Append("ALTER TABLE ");
stringBuilder.Append(tableName);
stringBuilder.AppendLine(" WITH NOCHECK");
stringBuilder.Append("ADD CONSTRAINT ");
stringBuilder.AppendLine($"[{relationName}]");
stringBuilder.Append("FOREIGN KEY (");
empty = string.Empty;
foreach (var column in constraint.Keys)
{
var childColumnName = ParserName.Parse(column.ColumnName).Quoted().ToString();
stringBuilder.Append($"{empty} {childColumnName}");
empty = ", ";
}
stringBuilder.AppendLine(" )");
stringBuilder.Append("REFERENCES ");
stringBuilder.Append(parentTableName).Append(" (");
empty = string.Empty;
foreach (var parentdColumn in constraint.ParentKeys)
{
var parentColumnName = ParserName.Parse(parentdColumn.ColumnName).Quoted().ToString();
stringBuilder.Append($"{empty} {parentColumnName}");
empty = ", ";
}
stringBuilder.Append(" ) ");
}
string createTableCommandString = stringBuilder.ToString();
var command = new SqlCommand(createTableCommandString, (SqlConnection)connection, (SqlTransaction)transaction);
SqlParameter sqlParameter = new SqlParameter()
{
ParameterName = "@tableName",
Value = tbl
};
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter()
{
ParameterName = "@schemaName",
Value = schema
};
command.Parameters.Add(sqlParameter);
return(command);
}
private string CreatePopulateFromBaseTableCommandText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine(string.Concat("INSERT INTO ", trackingName.Schema().Quoted().ToString(), " ("));
var stringBuilder1 = new StringBuilder();
var stringBuilder2 = new StringBuilder();
string empty = string.Empty;
var stringBuilderOnClause = new StringBuilder("ON ");
var stringBuilderWhereClause = new StringBuilder("WHERE ");
string str = string.Empty;
string baseTable = "[base]";
string sideTable = "[side]";
foreach (var pkColumn in this.tableDescription.PrimaryKey.Columns)
{
var quotedColumnName = ParserName.Parse(pkColumn).Quoted().ToString();
stringBuilder1.Append(string.Concat(empty, quotedColumnName));
stringBuilder2.Append(string.Concat(empty, baseTable, ".", quotedColumnName));
string[] quotedName = new string[] { str, baseTable, ".", quotedColumnName, " = ", sideTable, ".", quotedColumnName };
stringBuilderOnClause.Append(string.Concat(quotedName));
string[] strArrays = new string[] { str, sideTable, ".", quotedColumnName, " IS NULL" };
stringBuilderWhereClause.Append(string.Concat(strArrays));
empty = ", ";
str = " AND ";
}
var stringBuilder5 = new StringBuilder();
var stringBuilder6 = new StringBuilder();
if (Filters != null && Filters.Count > 0)
{
foreach (var filter in this.Filters)
{
var columnFilter = this.tableDescription.Columns[filter.ColumnName];
if (columnFilter == null)
{
throw new InvalidExpressionException($"Column {filter.ColumnName} does not exist in Table {this.tableDescription.TableName}");
}
var isPk = this.tableDescription.PrimaryKey.Columns.Any(dm => this.tableDescription.IsEqual(dm.ColumnName, columnFilter.ColumnName));
if (isPk)
{
continue;
}
var quotedColumnName = ParserName.Parse(columnFilter).Quoted().ToString();
stringBuilder6.Append(string.Concat(empty, quotedColumnName));
stringBuilder5.Append(string.Concat(empty, baseTable, ".", quotedColumnName));
}
}
// (list of pkeys)
stringBuilder.Append(string.Concat(stringBuilder1.ToString(), ", "));
stringBuilder.Append("[create_scope_id], ");
stringBuilder.Append("[update_scope_id], ");
stringBuilder.Append("[create_timestamp], ");
stringBuilder.Append("[update_timestamp], ");
//stringBuilder.Append("[timestamp], "); // timestamp is not a column we update, it's auto
stringBuilder.Append("[sync_row_is_tombstone] ");
stringBuilder.AppendLine(string.Concat(stringBuilder6.ToString(), ") "));
stringBuilder.Append(string.Concat("SELECT ", stringBuilder2.ToString(), ", "));
stringBuilder.Append("NULL, ");
stringBuilder.Append("NULL, ");
stringBuilder.Append("@@DBTS-1, ");
stringBuilder.Append("0, ");
//stringBuilder.Append("@@DBTS+1, "); // timestamp is not a column we update, it's auto
stringBuilder.Append("0");
stringBuilder.AppendLine(string.Concat(stringBuilder5.ToString(), " "));
string[] localName = new string[] { "FROM ", tableName.Schema().Quoted().ToString(), " ", baseTable, " LEFT OUTER JOIN ", trackingName.Schema().Quoted().ToString(), " ", sideTable, " " };
stringBuilder.AppendLine(string.Concat(localName));
stringBuilder.AppendLine(string.Concat(stringBuilderOnClause.ToString(), " "));
stringBuilder.AppendLine(string.Concat(stringBuilderWhereClause.ToString(), "; \n"));
return(stringBuilder.ToString());
}
