public virtual async Task <bool> NeedToCreateTriggerAsync(DbTriggerType type, DbConnection connection, DbTransaction transaction)
{
var updTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.UpdateTrigger).name;
var delTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.DeleteTrigger).name;
var insTriggerName = this.sqlObjectNames.GetCommandName(DbCommandType.InsertTrigger).name;
string triggerName = string.Empty;
switch (type)
{
case DbTriggerType.Insert:
{
triggerName = insTriggerName;
break;
}
case DbTriggerType.Update:
{
triggerName = updTriggerName;
break;
}
case DbTriggerType.Delete:
{
triggerName = delTriggerName;
break;
}
}
return(!await NpgsqlManagementUtils.TriggerExistsAsync((NpgsqlConnection)connection, (NpgsqlTransaction)transaction, triggerName).ConfigureAwait(false));
}
/// <summary>
/// Check if we need to create the table in the current database
/// </summary>
public async Task <bool> NeedToCreateSchemaAsync()
{
if (string.IsNullOrEmpty(tableName.SchemaName) || tableName.SchemaName.ToLowerInvariant() == "public")
{
return(false);
}
return(!await NpgsqlManagementUtils.SchemaExistsAsync(connection, transaction, tableName.SchemaName).ConfigureAwait(false));
}
public override async Task EnsureDatabaseAsync(DbConnection connection, DbTransaction transaction = null)
{
// Chek if db exists
var exists = await NpgsqlManagementUtils.DatabaseExistsAsync(connection as NpgsqlConnection, transaction as NpgsqlTransaction).ConfigureAwait(false);
if (!exists)
{
throw new MissingDatabaseException(connection.Database);
}
}
private string DeleteTriggerBodyText()
{
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(NpgsqlManagementUtils.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(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}
public async Task <bool> NeedToCreateForeignKeyConstraintsAsync(SyncRelation relation)
{
// Don't want foreign key on same table since it could be a problem on first
// sync. We are not sure that parent row will be inserted in first position
//if (relation.GetParentTable() == relation.GetTable())
// return false;
string tableName = relation.GetTable().TableName;
string schemaName = relation.GetTable().SchemaName;
string fullName = string.IsNullOrEmpty(schemaName) ? tableName : $"{schemaName}.{tableName}";
var relationName = NormalizeRelationName(relation.RelationName);
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
await connection.OpenAsync().ConfigureAwait(false);
}
var syncTable = await NpgsqlManagementUtils.GetRelationsForTableAsync(connection, transaction, tableName, schemaName).ConfigureAwait(false);
var foreignKeyExist = syncTable.Rows.Any(r =>
string.Equals(r["ForeignKey"].ToString(), relationName, SyncGlobalization.DataSourceStringComparison));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public virtual async Task <bool> NeedToCreateClientScopeInfoTableAsync()
{
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 exist = await NpgsqlManagementUtils.TableExistsAsync(connection, transaction, this.scopeTableName.Quoted().ToString());
return(!exist);
}
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();
}
}
}
/// <summary>
/// Check if we need to create the table in the current database
/// </summary>
public async Task <bool> NeedToCreateTableAsync()
{
return(!await NpgsqlManagementUtils.TableExistsAsync(connection, transaction, tableName.Schema().Quoted().ToString()).ConfigureAwait(false));
}
public virtual async Task <bool> NeedToCreateClientScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var exist = await NpgsqlManagementUtils.TableExistsAsync((NpgsqlConnection)connection, (NpgsqlTransaction)transaction, this.scopeTableName.Quoted().ToString());
return(!exist);
}
public override async Task <(string DatabaseName, string Version)> GetHelloAsync(DbConnection connection, DbTransaction transaction = null)
{
return(await NpgsqlManagementUtils.GetHelloAsync(connection as NpgsqlConnection, transaction as NpgsqlTransaction).ConfigureAwait(false));
}
private string UpdateTriggerBodyText()
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("SET NOCOUNT ON;");
stringBuilder.AppendLine();
stringBuilder.AppendLine("UPDATE [side] ");
stringBuilder.AppendLine("SET \t[update_scope_id] = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine("\t,[last_change_datetime] = GetUtcDate()");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} [side]");
stringBuilder.Append($"JOIN INSERTED AS [i] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[side]", "[i]"));
if (this.tableDescription.GetMutableColumns().Count() > 0)
{
stringBuilder.Append($"JOIN DELETED AS [d] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[d]", "[i]"));
stringBuilder.AppendLine("WHERE (");
string or = "";
foreach (var column in this.tableDescription.GetMutableColumns())
{
var quotedColumn = ParserName.Parse(column).Quoted().ToString();
stringBuilder.Append("\t");
stringBuilder.Append(or);
stringBuilder.Append("ISNULL(");
stringBuilder.Append("NULLIF(");
stringBuilder.Append("[d].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(", ");
stringBuilder.Append("[i].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(")");
stringBuilder.Append(", ");
stringBuilder.Append("NULLIF(");
stringBuilder.Append("[i].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(", ");
stringBuilder.Append("[d].");
stringBuilder.Append(quotedColumn);
stringBuilder.Append(")");
stringBuilder.AppendLine(") IS NOT NULL");
or = " OR ";
}
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}[i].{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,0");
stringBuilder.AppendLine("\t,GetUtcDate()");
stringBuilder.AppendLine("FROM INSERTED [i]");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} [side] ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "[i]", "[side]"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}
private string InsertTriggerBodyText()
{
//CREATE OR REPLACE FUNCTION "public"."Address_trigger_insert_function"()
//RETURNS TRIGGER AS
//$BODY$
//BEGIN
// UPDATE "side"
// SET "sync_row_is_tombstone" = 0
// ,"update_scope_id" = NULL -- scope id is always NULL when update is made locally
// ,"last_change_datetime" = GetUtcDate()
// FROM "tProduct" "side"
// JOIN NEW AS "i" ON "side"."ProductID" = "i"."ProductID";
// INSERT INTO "tProduct" (
// "ProductID"
// ,"update_scope_id"
// ,"sync_row_is_tombstone"
// ,"last_change_datetime"
// )
// SELECT
// "i"."ProductID"
// ,NULL
// ,0
// ,GetUtcDate()
// FROM NEW AS "i"
// LEFT JOIN "tProduct" "side" ON "i"."ProductID" = "side"."ProductID"
// WHERE "side"."ProductID" IS NULL;
// END;
//$BODY$
//LANGUAGE 'plpgsql';
//CREATE TRIGGER "Address_trigger_insert" AFTER INSERT ON "public"."Address"
//EXECUTE PROCEDURE "public"."Address_trigger_insert_function"();
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine();
stringBuilder.AppendLine("-- If row was deleted before, it already exists, so just make an update");
stringBuilder.AppendLine("UPDATE side ");
stringBuilder.AppendLine("SET sync_row_is_tombstone = 0");
stringBuilder.AppendLine("\t,update_scope_id = NULL -- scope id is always NULL when update is made locally");
stringBuilder.AppendLine("\t,last_change_datetime = now()");
stringBuilder.AppendLine($"FROM {trackingName.Schema().Quoted().ToString()} AS side");
stringBuilder.Append($"JOIN NEW AS i ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "side", "i"));
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}[i].{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,0");
stringBuilder.AppendLine("\t,now() at time zone 'utc'");
stringBuilder.AppendLine("FROM NEW i");
stringBuilder.Append($"LEFT JOIN {trackingName.Schema().Quoted().ToString()} side ON ");
stringBuilder.AppendLine(NpgsqlManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKeys, "i", "side"));
stringBuilder.Append("WHERE ");
stringBuilder.AppendLine(stringPkAreNull.ToString());
return(stringBuilder.ToString());
}
public async Task <bool> NeedToCreateTrackingTableAsync(DbConnection connection, DbTransaction transaction) => !await NpgsqlManagementUtils.TableExistsAsync((NpgsqlConnection)connection, (NpgsqlTransaction)transaction, trackingName.Schema().Quoted().ToString()).ConfigureAwait(false);
