private string DeleteTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"UPDATE {trackingName.Quoted().ToString()} ");
stringBuilder.AppendLine("SET `sync_row_is_tombstone` = 1");
stringBuilder.AppendLine("\t,`update_scope_id` = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine($"\t,`update_timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t,`timestamp` = {MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,`last_change_datetime` = utc_timestamp()");
// Filter columns
if (this.Filters != null)
{
foreach (var filterColumn in this.Filters)
{
if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName.ToLowerInvariant() == filterColumn.ColumnName.ToLowerInvariant()))
{
continue;
}
var columnName = ParserName.Parse(filterColumn.ColumnName, "`").Quoted().ToString();
stringBuilder.AppendLine($"\t,{columnName} = old.{columnName}");
}
stringBuilder.AppendLine();
}
stringBuilder.Append($"WHERE ");
stringBuilder.Append(MySqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "old"));
stringBuilder.AppendLine(";");
stringBuilder.AppendLine("END;");
return(stringBuilder.ToString());
}
/// <summary>
/// Gets the init row command without adding rows in tracking table
/// </summary>
private DbCommand CreateInitiliazeRowCommand()
{
var stringBuilder = new StringBuilder();
var columnToInserts = new StringBuilder();
var valuesToInserts = new StringBuilder();
string empty = string.Empty;
foreach (var mutableColumn in this.TableDescription.GetMutableColumns(false, true))
{
var columnName = ParserName.Parse(mutableColumn).Quoted().ToString();
var columnParameterName = ParserName.Parse(mutableColumn).Unquoted().Normalized().ToString();
valuesToInserts.Append($"{empty}@{columnParameterName}");
columnToInserts.Append($"{empty}{columnName}");
empty = ", ";
}
stringBuilder.AppendLine($"INSERT OR IGNORE INTO {tableName.Quoted()} ({columnToInserts})");
stringBuilder.AppendLine($"VALUES ({valuesToInserts});");
var cmdtext = stringBuilder.ToString();
return(new SqliteCommand(cmdtext));
}
private string CreateProcedureCommandScriptText(Func <MySqlCommand> BuildCommand, string procName)
{
var str1 = $"Command {procName} for table {tableName.Quoted().ToString()}";
var str = CreateProcedureCommandText(BuildCommand(), procName);
return(MyTableSqlBuilder.WrapScriptTextWithComments(str, str1));
}
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 string CreateProcedureCommandScriptText(Func <MySqlCommand> BuildCommand, string procName)
{
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
var str1 = $"Command {procName} for table {tableName.Quoted().ToString()}";
var str = CreateProcedureCommandText(BuildCommand(), procName);
return(MyTableSqlBuilder.WrapScriptTextWithComments(str, str1));
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateProcedureCommand : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public Task <DbCommand> GetCreateTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
#if MARIADB
var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, MariaDB.MariaDBSyncProvider.ProviderType);
var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, MariaDB.MariaDBSyncProvider.ProviderType);
#elif MYSQL
var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
#endif
var unQuotedColumnType = ParserName.Parse(columnTypeString, "`").Unquoted().Normalized().ToString();
var columnType = $"{unQuotedColumnType} {columnPrecisionString}";
stringBuilder.AppendLine($"{columnName} {columnType} NOT NULL, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"`update_scope_id` VARCHAR(36) NULL, ");
stringBuilder.AppendLine($"`timestamp` BIGINT NULL, ");
stringBuilder.AppendLine($"`sync_row_is_tombstone` BIT NOT NULL default 0, ");
stringBuilder.AppendLine($"`last_change_datetime` DATETIME NULL, ");
stringBuilder.Append(" PRIMARY KEY (");
var comma = "";
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
stringBuilder.Append(comma);
stringBuilder.Append(quotedColumnName);
comma = ", ";
}
stringBuilder.Append("));");
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = stringBuilder.ToString();
return(Task.FromResult(command));
}
public virtual async Task CreateClientScopeInfoTableAsync()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText =
$@"
CREATE TABLE IF NOT EXISTS public.{scopeTableName.Quoted().ToString()}(
sync_scope_id uuid NOT NULL,
sync_scope_name varchar(100) NOT NULL,
sync_scope_schema varchar NULL,
sync_scope_setup varchar NULL,
sync_scope_version varchar(10) NULL,
scope_last_server_sync_timestamp bigint NULL,
scope_last_sync_timestamp bigint NULL,
scope_last_sync_duration bigint NULL,
scope_last_sync timestamp NULL,
CONSTRAINT PK_{scopeTableName.Unquoted().Normalized().ToString()} PRIMARY KEY (sync_scope_id)
)";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public virtual async Task CreateClientScopeInfoTableAsync()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText =
$@"CREATE TABLE [dbo].{scopeTableName.Quoted().ToString()}(
[sync_scope_id] [uniqueidentifier] NOT NULL,
[sync_scope_name] [nvarchar](100) NOT NULL,
[sync_scope_schema] [nvarchar](max) NULL,
[sync_scope_setup] [nvarchar](max) NULL,
[sync_scope_version] [nvarchar](10) NULL,
[scope_last_server_sync_timestamp] [bigint] NULL,
[scope_last_sync_timestamp] [bigint] NULL,
[scope_last_sync_duration] [bigint] NULL,
[scope_last_sync] [datetime] NULL
CONSTRAINT [PK_{scopeTableName.Unquoted().Normalized().ToString()}] PRIMARY KEY CLUSTERED ([sync_scope_id] ASC)
)";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public void CreateScopeInfoTable()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText =
$@"CREATE TABLE [dbo].{scopeTableName.Quoted().ToString()}(
[sync_scope_id] [uniqueidentifier] NOT NULL,
[sync_scope_name] [nvarchar](100) NOT NULL,
[scope_timestamp] [timestamp] NULL,
[scope_is_local] [bit] NOT NULL DEFAULT(0),
[scope_last_sync_timestamp] [bigint] NULL,
[scope_last_sync_duration] [bigint] NULL,
[scope_last_sync] [datetime] NULL
CONSTRAINT [PK_{scopeTableName.Unquoted().Normalized().ToString()}] PRIMARY KEY CLUSTERED ([sync_scope_id] ASC)
)";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
//------------------------------------------------------------------
// Reset command
//------------------------------------------------------------------
private MySqlCommand BuildResetCommand()
{
var sqlCommand = new MySqlCommand();
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"DELETE FROM {tableName.Quoted().ToString()};");
stringBuilder.AppendLine($"DELETE FROM {trackingName.Quoted().ToString()};");
stringBuilder.AppendLine();
sqlCommand.CommandText = stringBuilder.ToString();
return(sqlCommand);
}
private MySqlCommand CreateUpdateCommand()
{
var mySqlCommand = new MySqlCommand();
var stringBuilder = new StringBuilder();
var hasMutableColumns = this.TableDescription.GetMutableColumns(false).Any();
var selectAllColumnsString = new StringBuilder();
var setUpdateAllColumnsString = new StringBuilder();
string empty = string.Empty;
foreach (var mutableColumn in this.TableDescription.GetMutableColumns())
{
var mutableColumnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString();
var parameterColumnName = ParserName.Parse(mutableColumn, "`").Unquoted().Normalized().ToString();
selectAllColumnsString.Append($"{empty}@{parameterColumnName} as {mutableColumnName}");
setUpdateAllColumnsString.Append($"{empty}{mutableColumnName}=`side`.{mutableColumnName}");
empty = ", ";
}
stringBuilder.AppendLine($"INSERT INTO {tableName.Quoted()}");
stringBuilder.AppendLine($"SELECT * FROM (SELECT {selectAllColumnsString}) as `side`");
if (hasMutableColumns)
{
stringBuilder.AppendLine($"ON DUPLICATE KEY");
stringBuilder.AppendLine($"UPDATE {setUpdateAllColumnsString};");
}
mySqlCommand.CommandText = stringBuilder.ToString();
return(mySqlCommand);
}
private void CreateResetCommandText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine($"DELETE FROM {tableName.Quoted().ToString()};");
stringBuilder.AppendLine($"DELETE FROM {trackingName.Quoted().ToString()};");
this.AddCommandName(DbCommandType.Reset, stringBuilder.ToString());
}
public Task <DbCommand> GetCreateTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
var columnType = this.dbMetadata.GetCompatibleColumnTypeDeclarationString(pkColumn, this.tableDescription.OriginalProvider);
stringBuilder.AppendLine($"{columnName} {columnType} NOT NULL, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"`update_scope_id` VARCHAR(36) NULL, ");
stringBuilder.AppendLine($"`timestamp` BIGINT NULL, ");
stringBuilder.AppendLine($"`sync_row_is_tombstone` BIT NOT NULL default 0, ");
stringBuilder.AppendLine($"`last_change_datetime` DATETIME NULL, ");
stringBuilder.Append(" PRIMARY KEY (");
var comma = "";
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var quotedColumnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
stringBuilder.Append(comma);
stringBuilder.Append(quotedColumnName);
comma = ", ";
}
stringBuilder.Append("));");
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = stringBuilder.ToString();
return(Task.FromResult(command));
}
public Task <DbCommand> GetDropColumnCommandAsync(string columnName, DbConnection connection, DbTransaction transaction)
{
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = $"ALTER TABLE {tableName.Quoted().ToString()} {columnName};";
return(Task.FromResult(command));
}
private string DeleteTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"\tINSERT INTO {trackingName.Quoted().ToString()} (");
StringBuilder stringBuilderArguments = new StringBuilder();
StringBuilder stringBuilderArguments2 = new StringBuilder();
StringBuilder stringPkAreNull = new StringBuilder();
string argComma = string.Empty;
string argAnd = string.Empty;
foreach (var mutableColumn in this.tableDescription.GetPrimaryKeysColumns().Where(c => !c.IsReadOnly))
{
var columnName = ParserName.Parse(mutableColumn, "`").Quoted().ToString();
stringBuilderArguments.AppendLine($"\t\t{argComma}{columnName}");
stringBuilderArguments2.AppendLine($"\t\t{argComma}old.{columnName}");
stringPkAreNull.Append($"{argAnd}{trackingName.Quoted().ToString()}.{columnName} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t\t,`update_scope_id`");
stringBuilder.AppendLine("\t\t,`timestamp`");
stringBuilder.AppendLine("\t\t,`sync_row_is_tombstone`");
stringBuilder.AppendLine("\t\t,`last_change_datetime`");
var filterColumnsString = new StringBuilder();
var filterColumnsString2 = new StringBuilder();
var filterColumnsString3 = new StringBuilder();
stringBuilder.AppendLine("\t) ");
stringBuilder.AppendLine("\tVALUES (");
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t\t,NULL");
stringBuilder.AppendLine($"\t\t,{MySqlObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t\t,1");
stringBuilder.AppendLine("\t\t,utc_timestamp()");
stringBuilder.AppendLine("\t)");
stringBuilder.AppendLine("ON DUPLICATE KEY UPDATE");
stringBuilder.AppendLine("\t`update_scope_id` = NULL, ");
stringBuilder.AppendLine("\t`sync_row_is_tombstone` = 1, ");
stringBuilder.AppendLine($"\t`timestamp` = {MySqlObjectNames.TimestampValue}, ");
stringBuilder.AppendLine("\t`last_change_datetime` = utc_timestamp()");
stringBuilder.Append(";");
stringBuilder.AppendLine("END");
return(stringBuilder.ToString());
}
public void CreateDeleteTrigger()
{
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
using (var command = new MySqlCommand())
{
if (!alreadyOpened)
{
this.connection.Open();
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
var delTriggerName = this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
StringBuilder createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE TRIGGER {delTriggerName} AFTER DELETE ON {tableName.Quoted().ToString()} FOR EACH ROW ");
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();
}
}
}
public string CreateTableCommandText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine($"CREATE TABLE {trackingName.Quoted().ToString()} (");
// Adding the primary key
foreach (var pkColumn in this.tableDescription.GetPrimaryKeysColumns())
{
var columnName = ParserName.Parse(pkColumn, "`").Quoted().ToString();
var columnTypeString = this.mySqlDbMetadata.TryGetOwnerDbTypeString(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var unQuotedColumnType = ParserName.Parse(columnTypeString, "`").Unquoted().Normalized().ToString();
var columnPrecisionString = this.mySqlDbMetadata.TryGetOwnerDbTypePrecision(pkColumn.OriginalDbType, pkColumn.GetDbType(), false, false, pkColumn.MaxLength, pkColumn.Precision, pkColumn.Scale, this.tableDescription.OriginalProvider, MySqlSyncProvider.ProviderType);
var columnType = $"{unQuotedColumnType} {columnPrecisionString}";
stringBuilder.AppendLine($"{columnName} {columnType} NOT NULL, ");
}
// adding the tracking columns
stringBuilder.AppendLine($"`update_scope_id` VARCHAR(36) NULL, ");
stringBuilder.AppendLine($"`timestamp` BIGINT NULL, ");
stringBuilder.AppendLine($"`sync_row_is_tombstone` BIT 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("))");
return(stringBuilder.ToString());
}
public virtual async Task CreateClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var commandText =
$@"CREATE TABLE [dbo].{scopeTableName.Quoted().ToString()}(
[sync_scope_id] [uniqueidentifier] NOT NULL,
[sync_scope_name] [nvarchar](100) NOT NULL,
[sync_scope_schema] [nvarchar](max) NULL,
[sync_scope_setup] [nvarchar](max) NULL,
[sync_scope_version] [nvarchar](10) NULL,
[scope_last_server_sync_timestamp] [bigint] NULL,
[scope_last_sync_timestamp] [bigint] NULL,
[scope_last_sync_duration] [bigint] NULL,
[scope_last_sync] [datetime] NULL
CONSTRAINT [PK_{scopeTableName.Unquoted().Normalized().ToString()}] PRIMARY KEY CLUSTERED ([sync_scope_id] ASC)
)";
using (var command = new SqlCommand(commandText, (SqlConnection)connection, (SqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public async Task RenameTableAsync(ParserName oldTableName, DbConnection connection, DbTransaction transaction)
{
var tableNameString = this.trackingName.Quoted().ToString();
var oldTableNameString = oldTableName.Quoted().ToString();
var commandText = $"ALTER TABLE {oldTableNameString} RENAME TO {tableNameString};";
using (var command = new SqliteCommand(commandText, (SqliteConnection)connection, (SqliteTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
private string DeleteTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"UPDATE {trackingName.Quoted().ToString()} ");
stringBuilder.AppendLine("SET [sync_row_is_tombstone] = 1");
stringBuilder.AppendLine("\t,[update_scope_id] = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine($"\t,[update_timestamp] = {SqliteObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t,[timestamp] = {SqliteObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,[last_change_datetime] = datetime('now')");
// --------------------------------------------------------------------------------
// SQLITE doesnot support (yet) filtering columns, since it's only a client provider
// --------------------------------------------------------------------------------
//// Filter columns
//if (this.Filters != null)
//{
// for (int i = 0; i < this.Filters.Count; i++)
// {
// var filterColumn = this.Filters[i];
// if (this.tableDescription.PrimaryKey.Columns.Any(c => c.ColumnName == filterColumn.ColumnName))
// continue;
// ObjectNameParser columnName = new ObjectNameParser(filterColumn.ColumnName);
// stringBuilder.AppendLine($"\t,{columnName.QuotedString} = [d].{columnName.QuotedString}");
// }
// stringBuilder.AppendLine();
//}
stringBuilder.Append($"WHERE ");
stringBuilder.Append(SqliteManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.Quoted().ToString(), "old"));
stringBuilder.AppendLine(";");
stringBuilder.AppendLine("END;");
return(stringBuilder.ToString());
}
public virtual async Task CreateClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var commandText =
$@"
CREATE TABLE IF NOT EXISTS public.{scopeTableName.Quoted().ToString()}(
sync_scope_id uuid NOT NULL,
sync_scope_name varchar(100) NOT NULL,
sync_scope_schema varchar NULL,
sync_scope_setup varchar NULL,
sync_scope_version varchar(10) NULL,
scope_last_server_sync_timestamp bigint NULL,
scope_last_sync_timestamp bigint NULL,
scope_last_sync_duration bigint NULL,
scope_last_sync timestamp NULL,
CONSTRAINT PK_{scopeTableName.Unquoted().Normalized().ToString()} PRIMARY KEY (sync_scope_id)
)";
using (var command = new NpgsqlCommand(commandText, (NpgsqlConnection)connection, (NpgsqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public override Task <DbCommand> GetRenameTrackingTableCommandAsync(ParserName oldTableName, DbConnection connection, DbTransaction transaction)
{
var tableNameString = this.TrackingTableName.Quoted().ToString();
var oldTableNameString = oldTableName.Quoted().ToString();
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = $"ALTER TABLE {oldTableNameString} RENAME TO {tableNameString};";
return(Task.FromResult(command));
}
public async Task CreateClientScopeInfoTableAsync()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
command.CommandText =
$@"CREATE TABLE {scopeTableName.Quoted().ToString()}(
sync_scope_id blob NOT NULL PRIMARY KEY,
sync_scope_name text NOT NULL,
sync_scope_schema text NULL,
sync_scope_setup text NULL,
sync_scope_version text NULL,
scope_last_server_sync_timestamp integer NULL,
scope_last_sync_timestamp integer NULL,
scope_last_sync_duration integer NULL,
scope_last_sync datetime NULL
)";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public void CreateScopeInfoTable()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText =
$@"CREATE TABLE {scopeTableName.Quoted().ToString()}(
sync_scope_id blob NOT NULL PRIMARY KEY,
sync_scope_name text NOT NULL,
scope_timestamp integer NULL,
scope_is_local integer NOT NULL DEFAULT(0),
scope_last_sync_timestamp integer NULL,
scope_last_sync_duration integer NULL,
scope_last_sync datetime NULL
)";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public async Task CreateClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var commandText =
$@"CREATE TABLE {scopeTableName.Quoted().ToString()}(
sync_scope_id blob NOT NULL PRIMARY KEY,
sync_scope_name text NOT NULL,
sync_scope_schema text NULL,
sync_scope_setup text NULL,
sync_scope_version text NULL,
scope_last_server_sync_timestamp integer NULL,
scope_last_sync_timestamp integer NULL,
scope_last_sync_duration integer NULL,
scope_last_sync datetime NULL
)";
using (var command = new SqliteCommand(commandText, (SqliteConnection)connection, (SqliteTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public void CreateScopeInfoTable()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText =
$@"CREATE TABLE {scopeTableName.Quoted().ToString()}(
sync_scope_id varchar(36) NOT NULL,
sync_scope_name varchar(100) NOT NULL,
sync_scope_schema longtext NULL,
scope_last_sync datetime NULL,
scope_last_server_sync_timestamp bigint NULL,
scope_last_sync_timestamp bigint NULL,
scope_last_sync_duration bigint NULL,
PRIMARY KEY (sync_scope_id)
)";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateTableScope : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public async Task RenameTableAsync(ParserName oldTableName)
{
var tableNameString = this.trackingName.Quoted().ToString();
var oldTableNameString = oldTableName.Quoted().ToString();
var commandText = $"RENAME TABLE {oldTableNameString} TO {tableNameString}; ";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
using (var command = new MySqlCommand(commandText, connection))
{
if (transaction != null)
{
command.Transaction = transaction;
}
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
catch (Exception ex)
{
Debug.WriteLine($"Error during RenameTableAsync : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
private string DeleteTriggerBodyText()
{
var stringBuilder = new StringBuilder();
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var stringPkAreNull = new StringBuilder();
string argComma = string.Empty;
string argAnd = string.Empty;
stringBuilder.AppendLine();
stringBuilder.AppendLine("BEGIN");
stringBuilder.AppendLine($"\tINSERT OR REPLACE INTO {trackingName.Quoted().ToString()} (");
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 ";
}
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t\t,[update_scope_id]");
stringBuilder.AppendLine("\t\t,[timestamp]");
stringBuilder.AppendLine("\t\t,[sync_row_is_tombstone]");
stringBuilder.AppendLine("\t\t,[last_change_datetime]");
stringBuilder.AppendLine("\t) ");
stringBuilder.AppendLine("\tVALUES (");
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t\t,NULL");
stringBuilder.AppendLine($"\t\t,{SqliteObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t\t,1");
stringBuilder.AppendLine("\t\t,datetime('now')");
stringBuilder.AppendLine("\t);");
stringBuilder.AppendLine("END;");
return(stringBuilder.ToString());
}
public async Task CreateClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var commandText =
$@"CREATE TABLE {scopeTableName.Quoted().ToString()}(
sync_scope_id varchar(36) NOT NULL,
sync_scope_name varchar(100) NOT NULL,
sync_scope_schema longtext NULL,
sync_scope_setup longtext NULL,
sync_scope_version varchar(10) NULL,
scope_last_sync datetime NULL,
scope_last_server_sync_timestamp bigint NULL,
scope_last_sync_timestamp bigint NULL,
scope_last_sync_duration bigint NULL,
PRIMARY KEY (sync_scope_id)
)";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
//------------------------------------------------------------------
// 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);
}
