protected virtual Command OnCreateView(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = new Command();
command.Append($"CREATE OR ALTER VIEW {RelationName.AsSqlIndentifier()} (");
command.AppendLine();
InsertColumns(command, sourceMetadata, targetMetadata, context, true);
command.Append(")");
command.AppendLine();
command.Append("AS");
if (context.EmptyBodiesEnabled)
{
command.AppendLine();
var fields = string.Join(", ", Fields.Select(x => SqlHelper.GetDataType(x.Field, sourceMetadata.MetadataCharacterSets.CharacterSetsById, sourceMetadata.MetadataDatabase.Database.CharacterSet.CharacterSetId)).Select(RelationField.CreateShimFieldContent));
command.Append($"SELECT {fields} FROM RDB$DATABASE");
}
else
{
if (!char.IsWhiteSpace(ViewSource[0]))
{
command.AppendLine();
}
command.Append(ViewSource);
}
return(command);
}
protected virtual Command OnCreateTable(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = new Command();
var isGtt = MetadataRelationType == MetadataRelationType.GlobalTemporaryPreserve || MetadataRelationType == MetadataRelationType.GlobalTemporaryDelete;
var isExternal = MetadataRelationType == MetadataRelationType.External;
command.Append("CREATE ");
if (isGtt)
{
command.Append("GLOBAL TEMPORARY ");
}
command.Append($"TABLE {RelationName.AsSqlIndentifier()} ");
if (isExternal)
{
command.Append($"EXTERNAL '{SqlHelper.DoubleSingleQuotes(ExternalFile)}' ");
}
command.Append("(");
command.AppendLine();
InsertColumns(command, sourceMetadata, targetMetadata, context, false);
command.AppendLine();
command.Append(")");
if (isGtt)
{
command.AppendLine();
command.Append($"ON COMMIT {MetadataRelationType.ToDescription()} ROWS");
}
if (SqlSecurity != null)
{
command.AppendLine();
command.Append($"SQL SECURITY {SqlHelper.SqlSecurityString(SqlSecurity)}");
}
return(command);
}
protected virtual Command OnCreateTable(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = new Command();
var isGtt = MetadataRelationType == MetadataRelationType.GLOBAL_TEMPORARY_PRESERVE || MetadataRelationType == MetadataRelationType.GLOBAL_TEMPORARY_DELETE;
var isExternal = MetadataRelationType == MetadataRelationType.EXTERNAL;
command.Append("CREATE ");
if (isGtt)
{
command.Append("GLOBAL TEMPORARY ");
}
command.Append($"TABLE {RelationName.AsSqlIndentifier()} ");
if (isExternal)
{
command.Append($"EXTERNAL '{SqlHelper.DoubleSingleQuotes(ExternalFile)}' ");
}
command.Append("(");
command.AppendLine();
InsertColumns(command, sourceMetadata, targetMetadata, context, false);
command.AppendLine();
command.Append(")");
if (isGtt)
{
command.AppendLine();
command.Append($"ON COMMIT {MetadataRelationType.ToDescription()} ROWS");
}
return(command);
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (IsUserCreatedIndex)
{
var command = new Command();
command
.Append($"CREATE {(Unique ? "UNIQUE " : string.Empty)}{(Descending ? "DESCENDING" : "ASCENDING")} INDEX {IndexName.AsSqlIndentifier()}")
.AppendLine()
.Append($" ON {RelationName.AsSqlIndentifier()}");
if (ExpressionSource == null)
{
command.Append($"({string.Join(",", Segments.Select(s => s.FieldName.AsSqlIndentifier()))})");
}
else
{
command.Append($" COMPUTED BY {ExpressionSource}");
}
yield return(command);
if (Inactive)
{
yield return(HandleIndexActiveInactive(context));
}
}
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = SqlHelper.IsValidExternalEngine(this) ? new Command() : new PSqlCommand();
command
.Append($"CREATE OR ALTER TRIGGER {TriggerName.AsSqlIndentifier()}")
.AppendLine()
.Append($"{(Inactive ? "INACTIVE" : "ACTIVE")} ");
switch (TriggerClass)
{
case TriggerClassType.DML:
case TriggerClassType.DDL:
command.Append($"{TriggerBeforeAfter.ToDescription()} {TriggerEvents[0].ToDescription()} ");
foreach (var item in TriggerEvents.Skip(1))
{
command.Append($"OR {item.ToDescription()} ");
}
break;
case TriggerClassType.DB:
command.Append($"ON {TriggerEvents[0].ToDescription()} ");
break;
}
command
.Append($"POSITION {TriggerSequence}")
.AppendLine();
if (TriggerClass == TriggerClassType.DML)
{
command
.Append($"ON {RelationName.AsSqlIndentifier()}")
.AppendLine();
}
if (SqlHelper.IsValidExternalEngine(this))
{
command.Append($"EXTERNAL NAME '{SqlHelper.DoubleSingleQuotes(EntryPoint)}'");
command.AppendLine();
command.Append($"ENGINE {EngineName.AsSqlIndentifier()}");
}
else
{
if (SqlSecurity != null)
{
command.Append($"SQL SECURITY {SqlHelper.SqlSecurityString(SqlSecurity)}");
command.AppendLine();
}
command.Append(TriggerSource);
}
yield return(command);
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = new PSqlCommand();
command
.Append($"CREATE OR ALTER TRIGGER {TriggerName.AsSqlIndentifier()}")
.AppendLine()
.Append($"{(Inactive ? "INACTIVE" : "ACTIVE")} ");
if (TriggerClass == TriggerClassType.DB)
{
command.Append($"ON {TriggerAction1.ToDescription()} ");
}
else if (TriggerClass == TriggerClassType.DML)
{
var beforeOrAfter = TriggerAction1.In(new[] { TriggerActionType.BEFORE_INSERT, TriggerActionType.BEFORE_UPDATE, TriggerActionType.BEFORE_DELETE }) ? "BEFORE" : "AFTER";
command.Append($"{beforeOrAfter} {TriggerAction1.ToDescription()} ");
if (TriggerAction2 != null)
{
command.Append($"OR {TriggerAction2.ToDescription()} ");
}
if (TriggerAction3 != null)
{
command.Append($"OR {TriggerAction3.ToDescription()} ");
}
}
else
{
throw new ArgumentOutOfRangeException($"Unknown trigger class {TriggerClass}.");
}
command
.Append($"POSITION {TriggerSequence}")
.AppendLine();
if (TriggerClass == TriggerClassType.DML)
{
command
.Append($"ON {RelationName.AsSqlIndentifier()}")
.AppendLine();
}
command.Append(TriggerSource);
yield return(command);
}
protected virtual IEnumerable <Command> OnAlterTable(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var otherRelation = FindOtherChecked(targetMetadata.MetadataRelations.Relations, RelationName, "table");
var fieldNames = sourceMetadata
.MetadataRelations
.Relations[RelationName]
.Fields
.Select(f => f.FieldName);
var otherFieldNames = targetMetadata
.MetadataRelations
.Relations[RelationName]
.Fields
.Select(f => f.FieldName);
// 1. new fields
var newFieldNames = new HashSet <Identifier>(fieldNames);
newFieldNames.ExceptWith(otherFieldNames);
var newFields = newFieldNames
.Select(fieldName => sourceMetadata.MetadataRelations.RelationFields[new RelationFieldKey(RelationName, fieldName)]);
foreach (var field in newFields)
{
foreach (var command in field.Create(sourceMetadata, targetMetadata, context))
{
yield return(command);
}
context.CreatedRelationFields.Add(new RelationFieldKey(field.RelationName, field.FieldName));
}
// 2. altered fields
var alteredFieldNames = new HashSet <Identifier>(fieldNames);
alteredFieldNames.IntersectWith(otherFieldNames);
var alteredFields = alteredFieldNames
.Select(fieldName => new
{
Field = sourceMetadata.MetadataRelations.RelationFields[new RelationFieldKey(RelationName, fieldName)],
OtherField = targetMetadata.MetadataRelations.RelationFields[new RelationFieldKey(RelationName, fieldName)]
})
.Where(x => x.Field != x.OtherField)
.Select(x => x.Field);
foreach (var command in alteredFields.SelectMany(newField => newField.Alter(sourceMetadata, targetMetadata, context)))
{
yield return(command);
}
// 3. dropped fields
var droppedFieldNames = new HashSet <Identifier>(otherFieldNames);
droppedFieldNames.ExceptWith(fieldNames);
var droppedFields = droppedFieldNames
.Select(fieldName => targetMetadata.MetadataRelations.RelationFields[new RelationFieldKey(RelationName, fieldName)]);
foreach (var field in droppedFields)
{
context.DeferredColumnsToDrop.Add(new CommandGroup().Append(field.Drop(sourceMetadata, targetMetadata, context)));
}
// 3. SQL SECURITY
if (SqlSecurity == null && otherRelation.SqlSecurity != null)
{
yield return(new Command().Append($"ALTER TABLE {RelationName.AsSqlIndentifier()} DROP SQL SECURITY"));
}
else if (SqlSecurity != null && otherRelation.SqlSecurity != null && SqlSecurity != otherRelation.SqlSecurity)
{
yield return(new Command().Append($"ALTER TABLE {RelationName.AsSqlIndentifier()} ALTER SQL SECURITY {SqlHelper.SqlSecurityString(SqlSecurity)}"));
}
}
protected override IEnumerable <Command> OnDrop(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
yield return(new Command()
.Append($"DROP {(MetadataRelationType == MetadataRelationType.View ? "VIEW" : "TABLE")} {RelationName.AsSqlIndentifier()}"));
}
protected override IEnumerable <Command> OnDrop(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
yield return(new Command()
.Append($"ALTER TABLE {RelationName.AsSqlIndentifier()} DROP {FieldName.AsSqlIndentifier()}"));
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
yield return(new Command()
.Append($"ALTER TABLE {RelationName.AsSqlIndentifier()} ADD {CreateDefinition(sourceMetadata, targetMetadata, context)}"));
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = new Command();
command.Append($"ALTER TABLE {RelationName.AsSqlIndentifier()}");
if (!SqlHelper.IsImplicitIntegrityConstraintName(ConstraintName))
{
command.Append($" ADD CONSTRAINT {ConstraintName.AsSqlIndentifier()}");
}
else
{
command.Append(" ADD");
}
switch (RelationConstraintType)
{
case RelationConstraintType.Check:
command.Append($" {Triggers[0].TriggerSource}");
break;
case RelationConstraintType.NotNull:
break;
case RelationConstraintType.ForeignKey:
case RelationConstraintType.PrimaryKey:
case RelationConstraintType.Unique:
{
var fields =
Index
.Segments
.OrderBy(s => s.FieldPosition)
.Select(s => s.FieldName);
command.Append($" {RelationConstraintType.ToDescription()} ({string.Join(", ", fields)})");
if (RelationConstraintType == RelationConstraintType.ForeignKey)
{
var referenceConstraint = sourceMetadata.MetadataConstraints.ReferenceConstraintsByName[ConstraintName];
var referenceRelationConstraint = referenceConstraint.RelationConstraintUq;
var primaryKeyFields =
referenceRelationConstraint
.Index
.Segments
.OrderBy(s => s.FieldPosition)
.Select(s => s.FieldName);
command
.AppendLine()
.Append($" REFERENCES {referenceRelationConstraint.RelationName} ({string.Join(", ", primaryKeyFields)})");
if (referenceConstraint.UpdateRule != ConstraintRule.Restrict)
{
command
.AppendLine()
.Append($" ON UPDATE {referenceConstraint.UpdateRule.ToDescription()}");
}
if (referenceConstraint.DeleteRule != ConstraintRule.Restrict)
{
command
.AppendLine()
.Append($" ON DELETE {referenceConstraint.DeleteRule.ToDescription()}");
}
if (referenceConstraint.UpdateRule != ConstraintRule.Restrict || referenceConstraint.DeleteRule != ConstraintRule.Restrict)
{
command.AppendLine();
}
}
command.Append(AddConstraintUsingIndex(sourceMetadata, targetMetadata, context));
break;
}
default:
throw new ArgumentOutOfRangeException($"Unknown relation constraint type: {RelationConstraintType}.");
}
yield return(command);
}