protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
var command = new PSqlCommand();
if (context.EmptyBodiesEnabled)
{
command.Append($"CREATE OR ALTER PACKAGE {PackageName.AsSqlIndentifier()}");
}
else
{
command.Append($"RECREATE PACKAGE BODY {PackageName.AsSqlIndentifier()}");
}
if (SqlSecurity != null)
{
command.AppendLine();
command.Append($"SQL SECURITY {SqlHelper.SqlSecurityString(SqlSecurity)}");
}
command.AppendLine();
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append(PackageHeaderSource);
}
else
{
command.Append(PackageBodySource);
}
yield return(command);
}
protected override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (ProcedureType == ProcedureProcedureType.LEGACY)
{
throw new NotSupportedException("Legacy type stored procedures are not supported.");
}
var command = new PSqlCommand();
command.Append($"CREATE OR ALTER PROCEDURE {ProcedureName.AsSqlIndentifier()}");
if (ProcedureInputs > 0)
{
var inputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.IN)
.OrderBy(o => o.ParameterNumber);
command.Append(" (");
command.AppendLine();
command.Append(CreateParametersDefinitions(inputs, sourceMetadata, targetMetadata, context));
command.Append(")");
}
command.AppendLine();
if (ProcedureOutputs > 0)
{
var outputs = ProcedureParameters
.Where(o => o.ParameterType == ProcedureParameterType.OUT)
.OrderBy(o => o.ParameterNumber);
command.Append("RETURNS (");
command.AppendLine();
command.Append(CreateParametersDefinitions(outputs, sourceMetadata, targetMetadata, context));
command.Append(")");
command.AppendLine();
}
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append("BEGIN");
command.AppendLine();
if (ProcedureType == ProcedureProcedureType.SELECTABLE)
{
command.Append($" SUSPEND{SqlHelper.Terminator}");
command.AppendLine();
}
command.Append("END");
}
else
{
command.Append(ProcedureSource);
}
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 override IEnumerable <Command> OnCreate(IMetadata sourceMetadata, IMetadata targetMetadata, IComparerContext context)
{
if (IsLegacy)
{
var command = new Command();
command.Append($"DECLARE EXTERNAL FUNCTION {FunctionName.AsSqlIndentifier()}");
var inputs =
FunctionArguments
.Where(x => x.ArgumentPosition != ReturnArgument)
.OrderBy(x => x.ArgumentPosition)
.Select(CreateLegacyArgumentDefinition);
command.Append($" {string.Join(", ", inputs)}");
command.AppendLine();
var @return = FunctionArguments.First(x => x.ArgumentPosition == ReturnArgument);
command.Append($"RETURNS{(ReturnArgument != 0 ? " PARAMETER" : string.Empty)} {CreateLegacyArgumentDefinition(@return)}");
command.AppendLine();
command.Append($"ENTRY_POINT '{SqlHelper.DoubleSingleQuotes(EntryPoint)}' MODULE_NAME '{SqlHelper.DoubleSingleQuotes(ModuleName)}'");
yield return(command);
}
else
{
var command = new PSqlCommand();
command.Append("CREATE OR ALTER FUNCTION");
if (PackageName != null)
{
command.Append($" {PackageName.AsSqlIndentifier()}.{FunctionName.AsSqlIndentifier()}");
}
else
{
command.Append($" {FunctionName.AsSqlIndentifier()}");
}
var inputs =
FunctionArguments
.Where(x => x.ArgumentPosition != ReturnArgument)
.OrderBy(x => x.ArgumentPosition)
.Select(x => CreateNewArgumentDefinition(x, sourceMetadata, targetMetadata, context));
var @return = FunctionArguments.First(x => x.ArgumentPosition == ReturnArgument);
var output = CreateNewArgumentDefinition(@return, sourceMetadata, targetMetadata, context);
command.Append($" ({string.Join(", ", inputs)}) RETURNS {output}");
command.AppendLine();
command.Append("AS");
command.AppendLine();
if (context.EmptyBodiesEnabled)
{
command.Append("BEGIN");
command.AppendLine();
command.Append("END");
}
else
{
command.Append(FunctionSource);
}
if (EntryPoint != null)
{
command.AppendLine();
command.Append($"EXTERNAL NAME '{EntryPoint}'");
}
if (EngineName != null)
{
command.AppendLine();
command.Append($"ENGINE {EngineName.AsSqlIndentifier()}");
}
yield return(command);
}
}