/// <summary>
/// Set the default stored procedures names
/// </summary>
private void SetDefaultNames()
{
var spPref = this.Setup.StoredProceduresPrefix != null ? this.Setup.StoredProceduresPrefix : "";
var spSuf = this.Setup.StoredProceduresSuffix != null ? this.Setup.StoredProceduresSuffix : "";
var trigPref = this.Setup.TriggersPrefix != null ? this.Setup.TriggersPrefix : "";
var trigSuf = this.Setup.TriggersSuffix != null ? this.Setup.TriggersSuffix : "";
var scopeNameWithoutDefaultScope = ScopeName == SyncOptions.DefaultScopeName ? "" : $"{ScopeName}_";
var storedProcedureName = $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}_";
var triggerName = $"{trigPref}{tableName.Unquoted().Normalized().ToString()}{trigSuf}_";
this.AddTriggerName(DbTriggerType.Insert, string.Format(insertTriggerName, triggerName));
this.AddTriggerName(DbTriggerType.Update, string.Format(updateTriggerName, triggerName));
this.AddTriggerName(DbTriggerType.Delete, string.Format(deleteTriggerName, triggerName));
this.AddStoredProcedureName(DbStoredProcedureType.SelectChanges, string.Format(selectChangesProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddStoredProcedureName(DbStoredProcedureType.SelectChangesWithFilters, string.Format(selectChangesProcNameWithFilters, storedProcedureName, scopeNameWithoutDefaultScope, "{0}_"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectInitializedChanges, string.Format(initializeChangesProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddStoredProcedureName(DbStoredProcedureType.SelectInitializedChangesWithFilters, string.Format(initializeChangesProcNameWithFilters, storedProcedureName, scopeNameWithoutDefaultScope, "{0}_"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectRow, string.Format(selectRowProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddStoredProcedureName(DbStoredProcedureType.UpdateRow, string.Format(updateProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddStoredProcedureName(DbStoredProcedureType.DeleteRow, string.Format(deleteProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddStoredProcedureName(DbStoredProcedureType.DeleteMetadata, string.Format(deleteMetadataProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddStoredProcedureName(DbStoredProcedureType.Reset, string.Format(resetProcName, storedProcedureName, scopeNameWithoutDefaultScope));
this.AddCommandName(DbCommandType.DisableConstraints, disableConstraintsText);
this.AddCommandName(DbCommandType.EnableConstraints, enableConstraintsText);
this.AddCommandName(DbCommandType.UpdateUntrackedRows, CreateUpdateUntrackedRowsCommand());
this.AddCommandName(DbCommandType.UpdateMetadata, CreateUpdateMetadataCommand());
}
public async Task CreateServerHistoryScopeInfoTableAsync()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
command.CommandText =
$@"CREATE TABLE `{tableName}`(
sync_scope_id varchar(36) NOT NULL,
sync_scope_name varchar(100) NOT NULL,
scope_last_sync_timestamp bigint NULL,
scope_last_sync_duration bigint NULL,
scope_last_sync datetime NULL,
PRIMARY KEY (sync_scope_id)
)";
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during CreateServerHistoryScopeInfoTableAsync : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public Task <DbCommand> GetExistsTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var commandText = $"IF EXISTS (Select top 1 tbl.name as TableName, " +
$"sch.name as SchemaName " +
$" from sys.change_tracking_tables tr " +
$" Inner join sys.tables as tbl on tbl.object_id = tr.object_id " +
$" Inner join sys.schemas as sch on tbl.schema_id = sch.schema_id " +
$" Where tbl.name = @tableName and sch.name = @schemaName) SELECT 1 ELSE SELECT 0;";
var tbl = tableName.Unquoted().ToString();
var schema = SqlManagementUtils.GetUnquotedSqlSchemaName(tableName);
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = commandText;
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = tbl;
command.Parameters.Add(parameter);
parameter = command.CreateParameter();
parameter.ParameterName = "@schemaName";
parameter.Value = schema;
command.Parameters.Add(parameter);
return(Task.FromResult(command));
}
public static bool TableExists(MySqlConnection connection, MySqlTransaction transaction, ParserName table)
{
bool tableExist;
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "select COUNT(*) from information_schema.TABLES where TABLE_NAME = @tableName and TABLE_SCHEMA = schema() and TABLE_TYPE = 'BASE TABLE'";
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
MySqlParameter sqlParameter = new MySqlParameter()
{
ParameterName = "@tableName",
Value = table.Unquoted().ToString()
};
dbCommand.Parameters.Add(sqlParameter);
tableExist = (Int64)dbCommand.ExecuteScalar() != 0;
}
return(tableExist);
}
public static async Task <bool> TableExistsAsync(SqliteConnection connection, SqliteTransaction transaction, ParserName tableName)
{
bool tableExist;
var quotedTableName = tableName.Unquoted().ToString();
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "select count(*) from sqlite_master where name = @tableName AND type='table'";
var SqliteParameter = new SqliteParameter()
{
ParameterName = "@tableName",
Value = quotedTableName
};
dbCommand.Parameters.Add(SqliteParameter);
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
tableExist = ((long)await dbCommand.ExecuteScalarAsync()) != 0L;
}
return(tableExist);
}
public async Task CreateServerHistoryScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_history";
var commandText =
$@"CREATE TABLE `{tableName}`(
sync_scope_id varchar(36) NOT NULL,
sync_scope_name varchar(100) NOT NULL,
scope_last_sync_timestamp bigint NULL,
scope_last_sync_duration bigint NULL,
scope_last_sync datetime NULL,
PRIMARY KEY (sync_scope_id)
)";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
public void DropScopeInfoTable()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText = $"DROP Table {scopeTableName.Unquoted().ToString()}";
command.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine($"Error during DropScopeInfoTable : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public async Task DropClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var commandText = $"DROP Table {scopeTableName.Unquoted().ToString()}";
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 [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();
}
}
}
public Task <DbCommand> GetExistsTrackingTableCommandAsync(DbConnection connection, DbTransaction transaction)
{
var command = connection.CreateCommand();
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "select COUNT(*) from information_schema.TABLES where TABLE_NAME = @tableName and TABLE_SCHEMA = schema() and TABLE_TYPE = 'BASE TABLE'";
var parameter = command.CreateParameter();
parameter.ParameterName = "@tableName";
parameter.Value = trackingName.Unquoted().ToString();
command.Parameters.Add(parameter);
return(Task.FromResult(command));
}
public static async Task <bool> TableExistsAsync(MySqlConnection connection, MySqlTransaction transaction, ParserName table)
{
bool tableExist;
using (DbCommand dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = "select COUNT(*) from information_schema.TABLES where TABLE_NAME = @tableName and TABLE_SCHEMA = schema() and TABLE_TYPE = 'BASE TABLE'";
bool alreadyOpened = connection.State == ConnectionState.Open;
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (transaction != null)
{
dbCommand.Transaction = transaction;
}
var sqlParameter = new MySqlParameter()
{
ParameterName = "@tableName",
Value = table.Unquoted().ToString()
};
dbCommand.Parameters.Add(sqlParameter);
tableExist = ((Int64)await dbCommand.ExecuteScalarAsync().ConfigureAwait(false)) != 0;
if (!alreadyOpened)
{
connection.Close();
}
}
return(tableExist);
}
public DbCommand CreateInsertTriggerCommand(DbConnection connection, DbTransaction transaction)
{
var insTriggerName = string.Format(this.mySqlObjectNames.GetTriggerCommandName(DbTriggerType.Insert), tableName.Unquoted().Normalized().ToString());
StringBuilder createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE TRIGGER {insTriggerName} AFTER INSERT ON {tableName.Quoted().ToString()} FOR EACH ROW ");
createTrigger.AppendLine();
createTrigger.AppendLine();
createTrigger.AppendLine("-- If row was deleted before, it already exists, so just make an update");
createTrigger.AppendLine("BEGIN");
createTrigger.AppendLine($"\tINSERT INTO {trackingName.Quoted().ToString()} (");
var stringBuilderArguments = new StringBuilder();
var stringBuilderArguments2 = new StringBuilder();
var 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,{this.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` = {this.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 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 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 bool NeedToCreateScopeInfoTable()
{
var command = connection.CreateCommand();
if (transaction != null)
{
command.Transaction = transaction;
}
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
command.CommandText = $"select count(*) from information_schema.TABLES where TABLE_NAME = '{scopeTableName.Unquoted().ToString()}' and TABLE_SCHEMA = schema() and TABLE_TYPE = 'BASE TABLE'";
return((long)command.ExecuteScalar() != 1);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during NeedToCreateScopeInfoTable command : {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
if (command != null)
{
command.Dispose();
}
}
}
public async Task CreateInsertTriggerAsync()
{
bool alreadyOpened = this.connection.State == ConnectionState.Open;
try
{
using (var command = new SqliteCommand())
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
if (this.transaction != null)
{
command.Transaction = this.transaction;
}
var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.Unquoted().ToString());
StringBuilder createTrigger = new StringBuilder($"CREATE TRIGGER IF NOT EXISTS {insTriggerName} AFTER INSERT ON {tableName.Quoted().ToString()} ");
createTrigger.AppendLine();
createTrigger.AppendLine(this.InsertTriggerBodyText());
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();
}
}
}
public void CreateInsertTrigger()
{
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 insTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name, tableName.Unquoted().Normalized().ToString());
StringBuilder createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE TRIGGER {insTriggerName} AFTER INSERT ON {tableName.Quoted().ToString()} FOR EACH ROW ");
createTrigger.AppendLine();
createTrigger.AppendLine(this.InsertTriggerBodyText());
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();
}
}
}
/// <summary>
/// Set the default stored procedures names
/// </summary>
private void SetDefaultNames()
{
var tpref = this.Setup.TriggersPrefix != null ? this.Setup.TriggersPrefix : "";
var tsuf = this.Setup.TriggersSuffix != null ? this.Setup.TriggersSuffix : "";
this.AddName(DbCommandType.InsertTrigger, string.Format(insertTriggerName, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddName(DbCommandType.UpdateTrigger, string.Format(updateTriggerName, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddName(DbCommandType.DeleteTrigger, string.Format(deleteTriggerName, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
// Check if we have mutables columns
var hasMutableColumns = TableDescription.GetMutableColumns(false).Any();
// Select changes
this.CreateSelectChangesCommandText();
this.CreateSelectRowCommandText();
this.CreateSelectInitializedCommandText();
this.CreateDeleteCommandText();
this.CreateDeleteMetadataCommandText();
this.CreateUpdateCommandText(hasMutableColumns);
this.CreateResetCommandText();
this.CreateUpdateUntrackedRowsCommand();
// Sqlite does not have any constraints, so just return a simple statement
this.AddName(DbCommandType.DisableConstraints, "Select 0"); // PRAGMA foreign_keys = OFF
this.AddName(DbCommandType.EnableConstraints, "Select 0");
}
/// <summary>
/// Set the default stored procedures names
/// </summary>
private void SetDefaultNames()
{
var tpref = this.TableDescription.TriggersPrefix != null ? this.TableDescription.TriggersPrefix : "";
var tsuf = this.TableDescription.TriggersSuffix != null ? this.TableDescription.TriggersSuffix : "";
this.AddName(DbCommandType.InsertTrigger, string.Format(insertTriggerName, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddName(DbCommandType.UpdateTrigger, string.Format(updateTriggerName, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
this.AddName(DbCommandType.DeleteTrigger, string.Format(deleteTriggerName, $"{tpref}{tableName.Unquoted().Normalized().ToString()}{tsuf}"));
// Select changes
this.CreateSelectChangesCommandText();
this.CreateSelectRowCommandText();
this.CreateDeleteCommandText();
this.CreateDeleteMetadataCommandText();
this.CreateInsertCommandText();
this.CreateInsertMetadataCommandText();
this.CreateUpdateCommandText();
this.CreateUpdatedMetadataCommandText();
this.CreateResetCommandText();
// SQLite does not have any constraints, so just return a simple statement
this.AddName(DbCommandType.DisableConstraints, "Select 0"); // PRAGMA foreign_keys = OFF
this.AddName(DbCommandType.EnableConstraints, "Select 0");
}
/// <summary>
/// Set the default stored procedures names
/// </summary>
private void SetDefaultNames()
{
var spPref = this.Setup.StoredProceduresPrefix != null ? this.Setup.StoredProceduresPrefix : "";
var spSuf = this.Setup.StoredProceduresSuffix != null ? this.Setup.StoredProceduresSuffix : "";
var trigPref = this.Setup.TriggersPrefix != null ? this.Setup.TriggersPrefix : "";
var trigSuf = this.Setup.TriggersSuffix != null ? this.Setup.TriggersSuffix : "";
this.AddTriggerName(DbTriggerType.Insert, string.Format(insertTriggerName, $"{trigPref}{tableName.Unquoted().Normalized().ToString()}{trigSuf}"));
this.AddTriggerName(DbTriggerType.Update, string.Format(updateTriggerName, $"{trigPref}{tableName.Unquoted().Normalized().ToString()}{trigSuf}"));
this.AddTriggerName(DbTriggerType.Delete, string.Format(deleteTriggerName, $"{trigPref}{tableName.Unquoted().Normalized().ToString()}{trigSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectChanges, string.Format(selectChangesProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectChangesWithFilters, string.Format(selectChangesProcNameWithFilters, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}", "{0}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectInitializedChanges, string.Format(initializeChangesProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectInitializedChangesWithFilters, string.Format(initializeChangesProcNameWithFilters, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}", "{0}"));
this.AddStoredProcedureName(DbStoredProcedureType.SelectRow, string.Format(selectRowProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.UpdateRow, string.Format(updateProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.DeleteRow, string.Format(deleteProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.DeleteMetadata, string.Format(deleteMetadataProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddStoredProcedureName(DbStoredProcedureType.Reset, string.Format(resetProcName, $"{spPref}{tableName.Unquoted().Normalized().ToString()}{spSuf}"));
this.AddCommandName(DbCommandType.DisableConstraints, string.Format(disableConstraintsText, ParserName.Parse(TableDescription).Quoted().ToString()));
this.AddCommandName(DbCommandType.EnableConstraints, string.Format(enableConstraintsText, ParserName.Parse(TableDescription).Quoted().ToString()));
this.AddCommandName(DbCommandType.UpdateUntrackedRows, CreateUpdateUntrackedRowsCommand());
this.AddCommandName(DbCommandType.UpdateMetadata, CreateUpdateMetadataCommand());
}
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 async Task CreateInsertTriggerAsync(DbConnection connection, DbTransaction transaction)
{
var insTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.InsertTrigger), tableName.Unquoted().ToString());
StringBuilder createTrigger = new StringBuilder($"CREATE TRIGGER IF NOT EXISTS {insTriggerName} AFTER INSERT ON {tableName.Quoted().ToString()} ");
createTrigger.AppendLine();
createTrigger.AppendLine(this.InsertTriggerBodyText());
using (var command = new SqliteCommand(createTrigger.ToString(), (SqliteConnection)connection, (SqliteTransaction)transaction))
{
await command.ExecuteNonQueryAsync().ConfigureAwait(false);
}
}
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 string CreateDeleteTriggerScriptText()
{
var delTriggerName = string.Format(this.mySqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name, tableName.Unquoted().Normalized().ToString());
StringBuilder createTrigger = new StringBuilder();
createTrigger.AppendLine($"CREATE TRIGGER {delTriggerName} AFTER DELETE ON {tableName.Quoted().ToString()} FOR EACH ROW ");
createTrigger.AppendLine();
createTrigger.AppendLine(this.DeleteTriggerBodyText());
string str = $"Delete Trigger for table {tableName.Quoted().ToString()}";
return(MySqlBuilder.WrapScriptTextWithComments(createTrigger.ToString(), str));
}
public string CreateDeleteTriggerScriptText()
{
var delTriggerName = string.Format(this.sqliteObjectNames.GetCommandName(DbCommandType.DeleteTrigger), tableName.Unquoted().ToString());
StringBuilder createTrigger = new StringBuilder($"CREATE TRIGGER IF NOT EXISTS {delTriggerName} AFTER DELETE ON {tableName.Quoted().ToString()} ");
createTrigger.AppendLine();
createTrigger.AppendLine(this.DeleteTriggerBodyText());
string str = $"Delete Trigger for table {tableName.Quoted().ToString()}";
return(SqliteBuilder.WrapScriptTextWithComments(createTrigger.ToString(), str));
}
