public bool NeedToCreateTrigger(DbTriggerType type)
{
var updTriggerName = this.oracleObjectNames.GetCommandName(DbCommandType.UpdateTrigger);
var delTriggerName = this.oracleObjectNames.GetCommandName(DbCommandType.DeleteTrigger);
var insTriggerName = this.oracleObjectNames.GetCommandName(DbCommandType.InsertTrigger);
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(!OracleManagementUtils.TriggerExists(connection, transaction, triggerName));
}
public bool NeedToCreateForeignKeyConstraints(DmRelation constraint)
{
string parentTable = constraint.ParentTable.TableName;
string parentSchema = constraint.ParentTable.Schema;
string parentFullName = String.IsNullOrEmpty(parentSchema) ? parentTable : $"{parentSchema}.{parentTable}";
bool alreadyOpened = connection.State == ConnectionState.Open;
try
{
if (!alreadyOpened)
{
connection.Open();
}
var dmTable = OracleManagementUtils.RelationsForTable(connection, transaction, parentFullName);
var foreignKeyExist = dmTable.Rows.Any(r =>
dmTable.IsEqual(r["ForeignKey"].ToString(), constraint.RelationName));
return(!foreignKeyExist);
}
catch (Exception ex)
{
Debug.WriteLine($"Error during checking foreign keys: {ex}");
throw;
}
finally
{
if (!alreadyOpened && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
public bool NeedToCreateSchema()
{
if (string.IsNullOrEmpty(tableName.SchemaName))
{
return(false);
}
return(!OracleManagementUtils.SchemaExists(connection, transaction, tableName.SchemaName));
}
private string DeleteTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(OracleBuilderTrigger.CreateBeginTrigger());
stringBuilder.AppendLine();
stringBuilder.AppendLine($"UPDATE {trackingName.UnquotedString} ");
stringBuilder.AppendLine("SET \tsync_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 = {OracleObjectNames.TimestampValue}");
stringBuilder.AppendLine($"\t,timestamp = {OracleObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,last_change_datetime = sysdate");
// 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;
}
ObjectNameParser columnName = new ObjectNameParser(columnFilter.ColumnName);
stringBuilder.AppendLine($"\t,{columnName.UnquotedString} = :old.{columnName.UnquotedString}");
}
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"WHERE ");
stringBuilder.AppendLine(OracleManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.UnquotedString, ":old") + " ;");
stringBuilder.AppendLine(OracleBuilderTrigger.CreateEndTrigger());
return(stringBuilder.ToString());
}
public bool NeedToCreateTrackingTable()
{
return(!OracleManagementUtils.TableExists(connection, transaction, trackingName.QuotedString));
}
private string InsertTriggerBodyText()
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(OracleBuilderTrigger.CreateBeginTrigger());
stringBuilder.AppendLine();
stringBuilder.AppendLine("-- If row was deleted before, it already exists, so just make an update");
stringBuilder.AppendLine($"UPDATE {trackingName.UnquotedString} ");
stringBuilder.AppendLine("SET \tsync_row_is_tombstone = 0");
stringBuilder.AppendLine("\t,update_scope_id = NULL -- since the update if from local, it's a NULL");
stringBuilder.AppendLine("\t,last_change_datetime = sysdate ");
// Filter columns
if (this.Filters != null && Filters.Count > 0)
{
foreach (var filter in 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;
}
stringBuilder.AppendLine($"\t,{columnFilter.ColumnName} = :new.{columnFilter.ColumnName}");
}
stringBuilder.AppendLine();
}
stringBuilder.AppendLine($"WHERE ");
stringBuilder.AppendLine(OracleManagementUtils.JoinTwoTablesOnClause(this.tableDescription.PrimaryKey.Columns, trackingName.UnquotedString, ":new") + " ;");
stringBuilder.AppendLine();
stringBuilder.AppendLine($"INSERT INTO {trackingName.UnquotedString} (");
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.PrimaryKey.Columns.Where(c => !c.ReadOnly))
{
ObjectNameParser columnName = new ObjectNameParser(mutableColumn.ColumnName);
stringBuilderArguments.AppendLine($"\t{argComma} {trackingName.UnquotedString}.{columnName.UnquotedString}");
stringBuilderArguments2.AppendLine($"\t{argComma} :new.{columnName.UnquotedString}");
stringPkAreNull.Append($"{argAnd} :new.{columnName.UnquotedString} IS NULL");
argComma = ",";
argAnd = " AND ";
}
stringBuilder.Append(stringBuilderArguments.ToString());
stringBuilder.AppendLine("\t,create_scope_id");
stringBuilder.AppendLine("\t,create_timestamp");
stringBuilder.AppendLine("\t,update_scope_id");
stringBuilder.AppendLine("\t,update_timestamp");
stringBuilder.AppendLine("\t,timestamp");
stringBuilder.AppendLine("\t,sync_row_is_tombstone");
stringBuilder.AppendLine("\t,last_change_datetime");
StringBuilder filterColumnsString = new StringBuilder();
// Filter columns
if (this.Filters != null && Filters.Count > 0)
{
foreach (var filter in 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;
}
ObjectNameParser columnName = new ObjectNameParser(columnFilter.ColumnName);
filterColumnsString.AppendLine($"\t, :new.{columnName.UnquotedString}");
}
stringBuilder.AppendLine(filterColumnsString.ToString());
}
stringBuilder.AppendLine(") ");
stringBuilder.AppendLine("VALUES (");
stringBuilder.Append(stringBuilderArguments2.ToString());
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine($"\t,{OracleObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,NULL");
stringBuilder.AppendLine("\t,0");
stringBuilder.AppendLine($"\t,{OracleObjectNames.TimestampValue}");
stringBuilder.AppendLine("\t,0");
stringBuilder.AppendLine("\t,sysdate");
if (Filters != null)
{
stringBuilder.AppendLine(filterColumnsString.ToString());
}
stringBuilder.AppendLine("\t);");
stringBuilder.AppendLine(OracleBuilderTrigger.CreateEndTrigger());
return(stringBuilder.ToString());
}
