private TextSegment GetSegmentToReplace()
{
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
var columnNames = _settingsModel.BooleanOptions.Values
.Where(v => v.Value)
.Select(v => OracleStatementFormatter.FormatTerminalValue(v.OptionIdentifier, formatOption))
.ToArray();
if (columnNames.Length == 0)
{
return(TextSegment.Empty);
}
var columnListText = $"({String.Join(", ", columnNames)})";
if (_columnList == null)
{
return(new TextSegment
{
IndextStart = _insertTarget.TargetNode.SourcePosition.IndexEnd + 1,
Length = 0,
Text = $" {columnListText}"
});
}
return
(new TextSegment
{
IndextStart = _columnList.SourcePosition.IndexStart,
Length = _columnList.SourcePosition.Length,
Text = columnListText
});
}
public string ToFormattedString()
{
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
var ownerPrefix = HasOwner ? $"{OracleStatementFormatter.FormatTerminalValue(Owner.ToSimpleIdentifier(), formatOption)}." : null;
return($"{ownerPrefix}{OracleStatementFormatter.FormatTerminalValue(Name.ToSimpleIdentifier(), formatOption)}");
}
private IEnumerable <TextSegment> GetMissingObjectReferenceQualifications()
{
var objectReference = SingleReference.Value as OracleObjectWithColumnsReference;
if (SingleReference.Value != null && objectReference == null)
{
var dataObjectReference = SingleReference.Value as OracleColumnReference;
if (dataObjectReference?.ValidObjectReference == null)
{
return(EmptySegments);
}
}
var sourceObjectReferences = objectReference == null
? SourceReferenceContainers.SelectMany(c => c.ObjectReferences)
: Enumerable.Repeat(objectReference, 1);
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
return(sourceObjectReferences
.Where(o => o.OwnerNode == null && o.Type == ReferenceType.SchemaObject && o.SchemaObject != null && !String.Equals(o.SchemaObject.FullyQualifiedName.Owner, OracleObjectIdentifier.SchemaPublic))
.Select(o =>
new TextSegment
{
IndextStart = o.ObjectNode.SourcePosition.IndexStart,
Length = 0,
Text = $"{OracleStatementFormatter.FormatTerminalValue(o.SchemaObject.Owner.ToSimpleIdentifier(), formatOption)}."
}));
}
private IEnumerable <TextSegment> GetMissingFunctionQualifications()
{
var programReference = SingleReference.Value as OracleProgramReference;
if (SingleReference.Value != null && programReference == null)
{
return(EmptySegments);
}
var sourceProgramReferences = programReference == null
? SourceReferenceContainers.SelectMany(c => c.ProgramReferences)
: Enumerable.Repeat(programReference, 1);
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
return(sourceProgramReferences
.Where(f => f.OwnerNode == null && f.Metadata != null && !f.Metadata.IsBuiltIn)
.Select(f =>
new TextSegment
{
IndextStart = (f.ObjectNode ?? f.ProgramIdentifierNode).SourcePosition.IndexStart,
Length = 0,
Text = $"{OracleStatementFormatter.FormatTerminalValue(f.Metadata.Identifier.Owner.ToSimpleIdentifier(), formatOption)}."
}));
}
private static string BuildQualifiedColumnLabel(OracleObjectIdentifier columnOwner, string columnName, bool applyFormatting)
{
var prefix = String.IsNullOrEmpty(columnOwner.Name)
? null
: $"{(applyFormatting ? columnOwner.ToFormattedString() : columnOwner.ToLabel())}.";
columnName = columnName.ToSimpleIdentifier();
if (applyFormatting)
{
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
columnName = OracleStatementFormatter.FormatTerminalValue(columnName, formatOption);
}
return($"{prefix}{columnName}");
}
private void BuildCreateTableColumnDefinitions(StringBuilder builder, bool includeDataTypes)
{
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions;
builder.Append(OracleStatementFormatter.FormatTerminalValue("CREATE TABLE ", formatOption.ReservedWord));
builder.Append(_settingsModel.Value);
builder.AppendLine(" (");
var columnPosition = 0;
foreach (var column in CurrentQueryBlock.Columns.Where(c => !c.IsAsterisk))
{
if (columnPosition > 0)
{
builder.Append(",");
builder.AppendLine();
}
columnPosition++;
builder.Append("\t");
var columnName = column.NormalizedName.ToSimpleIdentifier();
if (String.IsNullOrEmpty(columnName))
{
columnName = $"COLUMN{columnPosition}";
}
builder.Append(OracleStatementFormatter.FormatTerminalValue(columnName, formatOption.Identifier));
if (!includeDataTypes)
{
continue;
}
builder.Append(" ");
var columnType = String.IsNullOrEmpty(column.ColumnDescription?.FullTypeName)
? "VARCHAR2(255)"
: column.ColumnDescription.FullTypeName;
builder.Append(OracleStatementFormatter.FormatTerminalValue(columnType, formatOption.ReservedWord));
}
builder.AppendLine();
builder.Append(")");
}
private void WrapSubquery()
{
var settingsModel = ConfigureSettings();
if (!ExecutionContext.SettingsProvider.GetSettings())
{
return;
}
var tableAlias = settingsModel.Value;
var formatOptions = OracleConfiguration.Configuration.Formatter.FormatOptions;
var builder = new SqlTextBuilder();
builder.AppendReservedWord("SELECT ");
var objectQualifier = String.IsNullOrEmpty(tableAlias) ? null : $"{tableAlias}.";
var columnList = String.Join(", ", CurrentQueryBlock.Columns
.Where(c => !c.IsAsterisk && !String.IsNullOrEmpty(c.NormalizedName))
.Select(c => $"{objectQualifier}{OracleStatementFormatter.FormatTerminalValue(c.NormalizedName.ToSimpleIdentifier(), formatOptions.Identifier)}"));
builder.AppendText(columnList);
builder.AppendReservedWord(" FROM (");
ExecutionContext.SegmentsToReplace.Add(
new TextSegment
{
IndextStart = CurrentQueryBlock.RootNode.SourcePosition.IndexStart,
Length = 0,
Text = builder.ToString()
});
var subqueryIndexEnd = CurrentQueryBlock.OrderByClause == null
? CurrentQueryBlock.RootNode.SourcePosition.IndexEnd
: CurrentQueryBlock.OrderByClause.SourcePosition.IndexEnd;
ExecutionContext.SegmentsToReplace.Add(
new TextSegment
{
IndextStart = subqueryIndexEnd + 1,
Length = 0,
Text = String.IsNullOrEmpty(tableAlias) ? ")" : $") {tableAlias}"
});
}
private TextSegment AddCreateTableAsPrefix()
{
var builder = new StringBuilder();
BuildCreateTableColumnDefinitions(builder, false);
builder.AppendLine();
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions;
builder.AppendLine(OracleStatementFormatter.FormatTerminalValue(TerminalValues.As, formatOption.Keyword));
return
(new TextSegment
{
IndextStart = CurrentNode.Statement.SourcePosition.IndexStart,
Text = builder.ToString()
});
}
private void WrapSchemaObjectReference()
{
var formatOptions = OracleConfiguration.Configuration.Formatter.FormatOptions;
var builder = new SqlTextBuilder();
builder.AppendReservedWord("(SELECT ");
var columnList = String.Join(", ", _dataObjectReference.Columns
.Where(c => !c.Hidden)
.Select(c => OracleStatementFormatter.FormatTerminalValue(c.Name.ToSimpleIdentifier(), formatOptions.Identifier)));
builder.AppendText(columnList);
builder.AppendReservedWord(" FROM ");
var substringStart = _dataObjectReference.RootNode.SourcePosition.IndexStart;
var tableReferenceTextLength = _dataObjectReference.AliasNode == null
? _dataObjectReference.RootNode.SourcePosition.Length
: _dataObjectReference.AliasNode.SourcePosition.IndexStart - substringStart;
builder.AppendText(ExecutionContext.StatementText.Substring(substringStart, tableReferenceTextLength).Trim());
builder.AppendText(")");
if (!String.IsNullOrEmpty(_dataObjectReference.FullyQualifiedObjectName.Name))
{
builder.AppendText(" ");
builder.AppendIdentifier(_dataObjectReference.FullyQualifiedObjectName.Name.ToSimpleIdentifier());
}
ExecutionContext.SegmentsToReplace.Add(
new TextSegment
{
IndextStart = substringStart,
Length = _dataObjectReference.RootNode.SourcePosition.Length,
Text = builder.ToString()
});
}
protected override void Execute()
{
var settingsModel = ConfigureSettings();
if (!ExecutionContext.SettingsProvider.GetSettings())
{
return;
}
var tableAlias = settingsModel.Value;
var queryBlock = SemanticModel.GetQueryBlock(CurrentNode);
var lastCte = SemanticModel.MainQueryBlock
.AccessibleQueryBlocks
.OrderByDescending(qb => qb.RootNode.SourcePosition.IndexStart)
.FirstOrDefault();
var builder = new SqlTextBuilder();
var startIndex = SemanticModel.Statement.RootNode.SourcePosition.IndexStart;
if (lastCte == null)
{
builder.AppendReservedWord("WITH ");
}
else if (CurrentQueryBlock.Type == QueryBlockType.CommonTableExpression)
{
startIndex = CurrentQueryBlock.RootNode.GetAncestor(NonTerminals.CommonTableExpression).SourcePosition.IndexStart;
}
else
{
builder.AppendText(", ");
startIndex = lastCte.RootNode.GetAncestor(NonTerminals.CommonTableExpression).SourcePosition.IndexEnd + 1;
}
builder.AppendText(tableAlias);
builder.AppendKeyword(" AS (");
var subqueryIndextStart = queryBlock.RootNode.SourcePosition.IndexStart;
var subqueryIndexEnd = CurrentQueryBlock.OrderByClause == null
? CurrentQueryBlock.RootNode.SourcePosition.IndexEnd
: CurrentQueryBlock.OrderByClause.SourcePosition.IndexEnd;
var subqueryLength = subqueryIndexEnd - subqueryIndextStart + 1;
var subquery = ExecutionContext.StatementText.Substring(subqueryIndextStart, subqueryLength);
builder.AppendText(subquery);
builder.AppendText(")");
if (CurrentQueryBlock.Type == QueryBlockType.CommonTableExpression)
{
builder.AppendText(", ");
}
if (lastCte == null)
{
builder.AppendText(" ");
}
ExecutionContext.SegmentsToReplace.Add(
new TextSegment
{
IndextStart = startIndex,
Text = builder.ToString()
});
var formatOptions = OracleConfiguration.Configuration.Formatter.FormatOptions;
builder = new SqlTextBuilder();
builder.AppendReservedWord("SELECT ");
var columnList = String.Join(", ", queryBlock.Columns
.Where(c => !c.IsAsterisk && !String.IsNullOrEmpty(c.NormalizedName))
.Select(c => $"{tableAlias}.{OracleStatementFormatter.FormatTerminalValue(c.NormalizedName.ToSimpleIdentifier(), formatOptions.Identifier)}"));
builder.AppendText(columnList);
builder.AppendReservedWord(" FROM ");
builder.AppendText(tableAlias);
ExecutionContext.SegmentsToReplace.Add(
new TextSegment
{
IndextStart = subqueryIndextStart,
Length = subqueryLength,
Text = builder.ToString()
});
}
private IEnumerable <TextSegment> GetNamePrefixSegments(bool includeOptionalParameters)
{
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
var parameterReferences = _programReference.ParameterReferences;
var parameterNameMetadata =
_programReference.Metadata.NamedParameters.Where((p, i) => includeOptionalParameters || !p.Value.IsOptional || i < parameterReferences.Count)
.ToArray();
var parameterIndexStart = _programReference.ParameterListNode.FirstTerminalNode.SourcePosition.IndexEnd + 1;
var segmentTextBuilder = new StringBuilder();
var addCommaBeforeMissingParameters = false;
for (var i = 0; i < parameterNameMetadata.Length; i++)
{
var parameterReference = i < parameterReferences.Count ? parameterReferences[i] : null;
if (parameterReference?.OptionalIdentifierTerminal != null)
{
break;
}
var parameterMetadata = parameterNameMetadata[i];
var parameterName = OracleStatementFormatter.FormatTerminalValue(parameterMetadata.Key.ToSimpleIdentifier(), formatOption);
var namedParameterPrefix = $"{parameterName} => ";
if (parameterReference != null)
{
var parameterSourcePosition = parameterReference.ParameterNode.SourcePosition;
parameterIndexStart = parameterSourcePosition.IndexStart;
yield return
(new TextSegment
{
IndextStart = parameterIndexStart,
Text = namedParameterPrefix
});
parameterIndexStart = parameterSourcePosition.IndexEnd + 1;
addCommaBeforeMissingParameters = true;
}
else
{
if (addCommaBeforeMissingParameters || i > 0)
{
segmentTextBuilder.Append(", ");
}
segmentTextBuilder.Append(namedParameterPrefix);
segmentTextBuilder.Append("NULL");
}
}
if (segmentTextBuilder.Length > 0)
{
yield return
(new TextSegment
{
IndextStart = parameterIndexStart,
Text = segmentTextBuilder.ToString()
});
}
}
private void AppendFormat(string value, FormatOption formatOption)
{
var formattedValue = OracleStatementFormatter.FormatTerminalValue(value, formatOption);
_builder.Append(formattedValue);
}
private IEnumerable <TextSegment> GetMissingColumnReferenceObjectQualifications()
{
var columnReference = SingleReference.Value as OracleColumnReference;
if (SingleReference.Value != null && columnReference?.ValidObjectReference == null)
{
yield break;
}
var sourceObjectReferences = columnReference == null
? SourceReferenceContainers.SelectMany(c => c.ColumnReferences)
: Enumerable.Repeat(columnReference, 1);
var columnReferences = sourceObjectReferences
.Where(c => c.ValidObjectReference != null && (!c.ReferencesAllColumns || c.ObjectNode != null));
var formatOption = OracleConfiguration.Configuration.Formatter.FormatOptions.Identifier;
var qualificationBuilder = new StringBuilder();
foreach (var column in columnReferences)
{
qualificationBuilder.Clear();
var validObjectReference = column.ValidObjectReference;
var tableReference = validObjectReference as OracleDataObjectReference;
if (column.OwnerNode == null && validObjectReference.Type == ReferenceType.SchemaObject &&
tableReference?.AliasNode == null &&
validObjectReference.SchemaObject != null && !String.Equals(validObjectReference.SchemaObject.FullyQualifiedName.Owner, OracleObjectIdentifier.SchemaPublic))
{
qualificationBuilder.Append(OracleStatementFormatter.FormatTerminalValue(validObjectReference.SchemaObject.Owner.ToSimpleIdentifier(), formatOption));
qualificationBuilder.Append(".");
}
int indexStart;
if (column.ObjectNode == null)
{
if (!String.IsNullOrEmpty(validObjectReference.FullyQualifiedObjectName.Name))
{
qualificationBuilder.Append(validObjectReference.FullyQualifiedObjectName.Name);
qualificationBuilder.Append(".");
}
indexStart = column.ColumnNode.SourcePosition.IndexStart;
}
else
{
indexStart = column.ObjectNode.SourcePosition.IndexStart;
}
if (qualificationBuilder.Length == 0)
{
continue;
}
yield return
(new TextSegment
{
IndextStart = indexStart,
Length = 0,
Text = qualificationBuilder.ToString()
});
}
}
