private static KeyValuePair <OracleObjectIdentifier, OracleColumn> MapTableColumn(IDataRecord reader, Version version)
{
var dataTypeIdentifier = OracleObjectIdentifier.Create(QualifyStringObject(reader["DATA_TYPE_OWNER"]), QualifyStringObject(reader["DATA_TYPE"]));
var dataType =
new OracleDataType
{
FullyQualifiedName = dataTypeIdentifier,
Length = Convert.ToInt32(reader["DATA_LENGTH"]),
Precision = OracleReaderValueConvert.ToInt32(reader["DATA_PRECISION"]),
Scale = OracleReaderValueConvert.ToInt32(reader["DATA_SCALE"])
};
ResolveDataUnit(dataType, reader["CHAR_USED"]);
var column =
new OracleColumn
{
Name = QualifyStringObject(reader["COLUMN_NAME"]),
DataType = dataType,
Nullable = String.Equals((string)reader["NULLABLE"], "Y"),
Virtual = String.Equals((string)reader["VIRTUAL_COLUMN"], "YES"),
DefaultValue = OracleReaderValueConvert.ToString(reader["DATA_DEFAULT"]),
CharacterSize = Convert.ToInt32(reader["CHAR_LENGTH"]),
UserGenerated = true
};
if (version.Major >= 12)
{
column.Hidden = String.Equals((string)reader["HIDDEN_COLUMN"], "YES");
column.UserGenerated = String.Equals((string)reader["USER_GENERATED"], "YES");
}
return(new KeyValuePair <OracleObjectIdentifier, OracleColumn>(
OracleObjectIdentifier.Create(QualifyStringObject(reader["OWNER"]), QualifyStringObject(reader["TABLE_NAME"])), column));
}
private static OracleTypeCollection MapCollectionTypeAttributes(IDataRecord reader, IDictionary <OracleObjectIdentifier, OracleSchemaObject> allObjects)
{
var typeFullyQualifiedName = OracleObjectIdentifier.Create(QualifyStringObject(reader["OWNER"]), QualifyStringObject(reader["TYPE_NAME"]));
if (!allObjects.TryGetValue(typeFullyQualifiedName, out OracleSchemaObject typeObject))
{
return(null);
}
var collectionType = (OracleTypeCollection)typeObject;
var elementTypeIdentifier = OracleObjectIdentifier.Create(QualifyStringObject(reader["ELEM_TYPE_OWNER"]), QualifyStringObject(reader["ELEM_TYPE_NAME"]));
var dataType =
new OracleDataType
{
FullyQualifiedName = elementTypeIdentifier,
Length = OracleReaderValueConvert.ToInt32(reader["LENGTH"]),
Precision = OracleReaderValueConvert.ToInt32(reader["PRECISION"]),
Scale = OracleReaderValueConvert.ToInt32(reader["SCALE"])
};
ResolveDataUnit(dataType, reader["CHARACTER_SET_NAME"]);
collectionType.ElementDataType = dataType;
collectionType.CollectionType = (string)reader["COLL_TYPE"] == OracleTypeCollection.OracleCollectionTypeNestedTable ? OracleCollectionType.Table : OracleCollectionType.VarryingArray;
collectionType.UpperBound = OracleReaderValueConvert.ToInt32(reader["UPPER_BOUND"]);
return(collectionType);
}
private static OracleTypeObject MapTypeAttributes(IDataRecord reader, IDictionary <OracleObjectIdentifier, OracleSchemaObject> allObjects)
{
var typeFullyQualifiedName = OracleObjectIdentifier.Create(QualifyStringObject(reader["OWNER"]), QualifyStringObject(reader["TYPE_NAME"]));
if (!allObjects.TryGetValue(typeFullyQualifiedName, out OracleSchemaObject typeObject))
{
return(null);
}
var type = (OracleTypeObject)typeObject;
var attributeTypeIdentifier = OracleObjectIdentifier.Create(QualifyStringObject(reader["ATTR_TYPE_OWNER"]), QualifyStringObject(reader["ATTR_TYPE_NAME"]));
var dataType =
new OracleDataType
{
FullyQualifiedName = attributeTypeIdentifier,
Length = OracleReaderValueConvert.ToInt32(reader["LENGTH"]),
Precision = OracleReaderValueConvert.ToInt32(reader["PRECISION"]),
Scale = OracleReaderValueConvert.ToInt32(reader["SCALE"])
};
ResolveDataUnit(dataType, reader["CHAR_USED"]);
var attribute =
new OracleTypeAttribute
{
Name = QualifyStringObject(reader["ATTR_NAME"]),
DataType = dataType,
IsInherited = String.Equals((string)reader["INHERITED"], "YES")
};
type.Attributes.Add(attribute);
return(type);
}
public static void ValidateCondition(OracleValidationModel validationModel, OracleColumnReference columnReference)
{
var columnDataTypeName = columnReference.ColumnDescription?.DataType.FullyQualifiedName.NormalizedName.Trim('"');
if (String.IsNullOrEmpty(columnDataTypeName) || !columnReference.ColumnDescription.DataType.IsPrimitive)
{
return;
}
var condition = FindCondition(columnReference.RootNode);
if (condition == null)
{
return;
}
var expression2 = condition[2];
if (String.Equals(condition[1]?.Id, NonTerminals.RelationalOperator) &&
String.Equals(expression2?.Id, NonTerminals.Expression))
{
var expression1 = condition[0];
var referenceExpression = columnReference.RootNode.GetAncestor(NonTerminals.Expression);
var expressionToCheck = expression1 == referenceExpression
? expression2
: expression1;
if (expressionToCheck[NonTerminals.ExpressionMathOperatorChainedList] != null)
{
return;
}
var dummyColumn = new OracleColumn();
if (!OracleDataType.TryResolveDataTypeFromExpression(expressionToCheck, dummyColumn))
{
return;
}
var literalDataTypeName = dummyColumn.DataType.FullyQualifiedName.NormalizedName.Trim('"');
var isColumnTimeOrNumber = IsTime(columnDataTypeName) || IsNumeric(columnDataTypeName);
var isLiteralTimeOrNumber = IsTime(literalDataTypeName) || IsNumeric(literalDataTypeName);
if (isColumnTimeOrNumber && IsText(literalDataTypeName) || isLiteralTimeOrNumber && IsText(columnDataTypeName))
{
validationModel.AddNonTerminalSuggestion(expressionToCheck, String.Format(OracleSuggestionType.ImplicitConversionWarning, columnDataTypeName, literalDataTypeName));
}
}
}
public static DbParameter GetDbParameter(string name, OracleDataType dataType, int length)
{
switch (dataType)
{
case OracleDataType.Varchar2:
return new OracleParameter(name, OracleDbType.Varchar2, length);
case OracleDataType.Nvarchar2:
return new OracleParameter(name, OracleDbType.NVarchar2, length);
case OracleDataType.Int32:
return new OracleParameter(name, OracleDbType.Int32);
case OracleDataType.Char:
return new OracleParameter(name, OracleDbType.Char, length);
case OracleDataType.Date:
return new OracleParameter(name, OracleDbType.Date);
}
return null;
}
public OracleColumnDataTypeDescriptor(OracleDataType typeSpecifier, int quantity)
{
switch (typeSpecifier)
{
case OracleDataType.BLOB:
throw new ArgumentException("ERROR: quantity specificied for a data type that does not require it.");
//break;
case OracleDataType.CHAR:
throw new NotImplementedException();
//break;
case OracleDataType.CLOB:
throw new ArgumentException("ERROR: quantity specificied for a data type that does not require it.");
//break;
case OracleDataType.DATE:
throw new ArgumentException("ERROR: quantity specificied for a data type that does not require it.");
//break;
case OracleDataType.NCHAR:
throw new NotImplementedException();
//break;
case OracleDataType.NCLOB:
throw new NotImplementedException();
//break;
case OracleDataType.NUMBER:
throw new ArgumentException("ERROR: insufficient quantity specification for this data type.");
//break;
case OracleDataType.NVARCHAR2:
throw new NotImplementedException();
//break;
case OracleDataType.TIMESTAMP:
throw new ArgumentException("ERROR: quantity specificied for a data type that does not require it.");
//break;
case OracleDataType.VARCHAR2:
throw new NotImplementedException();
//break;
}
}
/// <summary>
/// Преобразует строку в тип OracleDbType
/// </summary>
/// <param name="value">Значение</param>
/// <param name="type">Тип</param>
/// <returns></returns>
public static Object StrAsOraValue(String value, OracleDataType type) {
return StrAsOraValue(value, (OracleDbType)type);
}
protected override IReadOnlyList <OracleColumn> BuildPseudocolumns()
{
var pseudocolumns = new List <OracleColumn>();
var table = SchemaObject.GetTargetSchemaObject() as OracleTable;
if (Type != ReferenceType.SchemaObject || table == null)
{
return(pseudocolumns.AsReadOnly());
}
if (table.Organization == OrganizationType.Heap || table.Organization == OrganizationType.Index)
{
var rowIdPseudocolumn =
new OracleColumn(true)
{
Name = RowIdNormalizedName,
DataType =
new OracleDataType
{
FullyQualifiedName = OracleObjectIdentifier.Create(null, table.Organization == OrganizationType.Index ? TerminalValues.UniversalRowId : TerminalValues.RowIdDataType)
}
};
pseudocolumns.Add(rowIdPseudocolumn);
}
if (FlashbackOption == FlashbackOption.None || FlashbackOption == FlashbackOption.AsOf)
{
var rowSystemChangeNumberPseudocolumn =
new OracleColumn(true)
{
Name = "\"ORA_ROWSCN\"",
DataType = OracleDataType.NumberType
};
pseudocolumns.Add(rowSystemChangeNumberPseudocolumn);
}
else if ((FlashbackOption & FlashbackOption.Versions) == FlashbackOption.Versions)
{
var flashbackVersionColumns =
new[]
{
new OracleColumn(true)
{
Name = "\"VERSIONS_STARTTIME\"",
DataType = OracleDataType.CreateTimestampDataType(0)
},
new OracleColumn(true)
{
Name = "\"VERSIONS_ENDTIME\"",
DataType = OracleDataType.CreateTimestampDataType(0)
},
new OracleColumn(true)
{
Name = "\"VERSIONS_STARTSCN\"",
DataType = OracleDataType.NumberType
},
new OracleColumn(true)
{
Name = "\"VERSIONS_ENDSCN\"",
DataType = OracleDataType.NumberType
},
new OracleColumn(true)
{
Name = "\"VERSIONS_OPERATION\"",
DataType = new OracleDataType {
FullyQualifiedName = OracleObjectIdentifier.Create(null, TerminalValues.Varchar2), Unit = DataUnit.Byte, Length = 1
}
},
new OracleColumn(true)
{
Name = "\"VERSIONS_XID\"",
DataType = new OracleDataType {
FullyQualifiedName = OracleObjectIdentifier.Create(null, TerminalValues.Raw), Length = 8
}
}
};
pseudocolumns.AddRange(flashbackVersionColumns);
}
return(pseudocolumns.AsReadOnly());
}
private OracleColumn BuildColumnDescription()
{
var columnReference = IsDirectReference && ColumnReferences.Count == 1
? ColumnReferences[0]
: null;
var columnDescription = columnReference?.ColumnDescription;
_columnDescription =
new OracleColumn
{
Name = ColumnName,
Nullable = columnDescription == null,
DataType = OracleDataType.Empty
};
if (columnDescription != null)
{
_columnDescription.Nullable = columnDescription.Nullable;
_columnDescription.DataType = columnDescription.DataType;
_columnDescription.CharacterSize = columnDescription.CharacterSize;
if (!_columnDescription.Nullable)
{
if (columnReference.ValidObjectReference is OracleDataObjectReference objectReference)
{
_columnDescription.Nullable = objectReference.IsOuterJoined;
}
}
}
if (IsAsterisk || RootNode.TerminalCount == 0)
{
return(_columnDescription);
}
var expressionNode = RootNode[0];
if (String.Equals(expressionNode.Id, NonTerminals.AliasedExpression))
{
expressionNode = expressionNode[0];
}
if (OracleDataType.TryResolveDataTypeFromExpression(expressionNode, _columnDescription) && !_columnDescription.DataType.IsDynamicCollection)
{
if (_columnDescription.DataType.FullyQualifiedName.Name.EndsWith("CHAR"))
{
_columnDescription.CharacterSize = _columnDescription.DataType.Length;
}
var isBuiltInDataType = _columnDescription.DataType.IsPrimitive && OracleDatabaseModelBase.BuiltInDataTypes.Any(t => String.Equals(t, _columnDescription.DataType.FullyQualifiedName.Name));
if (!isBuiltInDataType && SemanticModel.HasDatabaseModel)
{
var oracleType = SemanticModel.DatabaseModel.GetFirstSchemaObject <OracleTypeBase>(_columnDescription.DataType.FullyQualifiedName);
if (oracleType == null)
{
_columnDescription.DataType = OracleDataType.Empty;
}
}
}
else if (columnDescription == null)
{
expressionNode = expressionNode.UnwrapIfNonChainedExpressionWithinParentheses(out bool isChainedExpression);
if (!isChainedExpression)
{
var programReference = ProgramReferences.SingleOrDefault(r => r.RootNode == expressionNode);
if (programReference == null)
{
var typeReference = TypeReferences.SingleOrDefault(r => r.RootNode == expressionNode);
if (typeReference?.Metadata != null)
{
var x = typeReference.Metadata.ReturnParameter.CustomDataType;
}
}
else if (programReference.Metadata != null)
{
if (programReference.Metadata.ReturnParameter == null)
{
if (programReference.Metadata.Identifier == OracleProgramIdentifier.IdentifierBuiltInProgramCoalesce)
{
}
else if (programReference.Metadata.Identifier == OracleProgramIdentifier.IdentifierBuiltInProgramBinaryToNumber)
{
_columnDescription.DataType = OracleDataType.NumberType;
}
}
else if (!String.IsNullOrEmpty(programReference.Metadata.ReturnParameter.DataType))
{
if (programReference.Metadata.Identifier != OracleProgramIdentifier.IdentifierBuiltInProgramNvl)
{
_columnDescription.DataType = new OracleDataType {
FullyQualifiedName = OracleObjectIdentifier.Create(null, programReference.Metadata.ReturnParameter.DataType)
};
switch (programReference.Metadata.ReturnParameter.DataType)
{
case TerminalValues.Varchar:
case TerminalValues.Varchar2:
_columnDescription.CharacterSize = _columnDescription.DataType.Length = SemanticModel.DatabaseModel.MaximumVarcharLength;
break;
case TerminalValues.Raw:
_columnDescription.DataType.Length = SemanticModel.DatabaseModel.MaximumRawLength;
break;
case TerminalValues.NVarchar:
case TerminalValues.NVarchar2:
_columnDescription.CharacterSize = _columnDescription.DataType.Length = SemanticModel.DatabaseModel.MaximumNVarcharLength;
break;
case TerminalValues.Timestamp:
_columnDescription.DataType = OracleDataType.CreateTimestampDataType(9);
break;
case OracleDatabaseModelBase.BuiltInDataTypeTimestampWithTimeZone:
_columnDescription.DataType = OracleDataType.CreateTimestampWithTimeZoneDataType(9);
break;
}
}
}
}
}
}
return(_columnDescription);
}
protected override IReadOnlyList<OracleColumn> BuildColumns()
{
var columns = new List<OracleColumn>();
var pivotColumns = new List<OracleSelectListColumn>();
var pivotForColumnList = PivotClause[NonTerminals.PivotForClause, NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList];
if (pivotForColumnList != null)
{
var aggregatedColumnNames =
SourceReferenceContainer.ColumnReferences
.Where(c => AggregateFunctions.Any(f => f.SourcePosition.Contains(c.RootNode.SourcePosition)))
.Select(c => c.NormalizedName);
var groupingColumns = pivotForColumnList
.GetDescendants(Terminals.Identifier)
.Select(i => i.Token.Value.ToQuotedIdentifier())
.Concat(aggregatedColumnNames)
.ToHashSet();
switch (PivotClause.Id)
{
case NonTerminals.PivotClause:
var sourceColumns = SourceReference.Columns
.Where(c => !groupingColumns.Contains(c.Name))
.Select(c => c.Clone());
columns.AddRange(sourceColumns);
var withXmlTransformation = PivotClause[Terminals.Xml] != null;
if (withXmlTransformation)
{
var xmlColumnName = String.Join("_", groupingColumns.Select(c => c.Trim('"')));
if (xmlColumnName.Length > 26)
{
xmlColumnName = xmlColumnName.Substring(0, 26);
}
var xmlColumn =
new OracleColumn
{
Name = $"\"{xmlColumnName}_XML\"",
DataType = OracleDataType.XmlType,
Nullable = true
};
columns.Add(xmlColumn);
}
else
{
pivotColumns.AddRange(ResolvePivotColumns());
columns.AddRange(pivotColumns.Select(c => c.ColumnDescription));
}
break;
case NonTerminals.UnpivotClause:
var unpivotColumnSources = new List<StatementGrammarNode>();
var unpivotedColumns = new HashSet<string>();
var unpivotColumnSelectorValues = new List<StatementGrammarNode>();
var columnTransformations = PivotClause[NonTerminals.UnpivotInClause].GetDescendants(NonTerminals.UnpivotValueToColumnTransformationList);
var groupingColumnsNullable = false;
foreach (var columnTransformation in columnTransformations)
{
unpivotedColumns.UnionWith(columnTransformation.GetDescendants(Terminals.Identifier).Select(t => t.Token.Value.ToQuotedIdentifier()));
var columnSelectorValue = columnTransformation[NonTerminals.UnpivotValueSelector, NonTerminals.NullOrStringOrNumberLiteralOrParenthesisEnclosedStringOrIntegerLiteralList];
if (columnSelectorValue != null)
{
unpivotColumnSelectorValues.Add(columnSelectorValue);
groupingColumnsNullable |= columnSelectorValue.TerminalCount == 1 || String.Equals(columnSelectorValue.FirstTerminalNode.Id, Terminals.Null);
}
unpivotColumnSources.AddIfNotNull(columnTransformation[NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList]);
}
var unpivotColumnDataTypes = OracleDataType.FromUnpivotColumnSelectorValues(unpivotColumnSelectorValues);
AreUnpivotColumnSelectorValuesValid = unpivotColumnDataTypes != null;
UnpivotColumnSelectorValues = unpivotColumnSelectorValues.AsReadOnly();
UnpivotColumnSources = unpivotColumnSources.AsReadOnly();
columns.AddRange(SourceReference.Columns
.Where(c => !unpivotedColumns.Contains(c.Name))
.Select(c => c.Clone()));
columns.AddRange(groupingColumns.Select(
(c, i) =>
new OracleColumn
{
Name = c,
Nullable = groupingColumnsNullable,
DataType = groupingColumns.Count == unpivotColumnDataTypes?.Count
? unpivotColumnDataTypes[i]
: OracleDataType.Empty
}));
var unpivotColumnsNullable = PivotClause[NonTerminals.UnpivotNullsClause, NonTerminals.IncludeOrExclude, Terminals.Include] != null;
var unpivotColumns = PivotClause[NonTerminals.IdentifierOrParenthesisEnclosedIdentifierList].GetDescendants(Terminals.Identifier);
columns.AddRange(
unpivotColumns.Select(
i => new OracleColumn
{
Name = i.Token.Value.ToQuotedIdentifier(),
DataType = OracleDataType.Empty,
Nullable = unpivotColumnsNullable
}));
break;
}
}
PivotColumns = pivotColumns.AsReadOnly();
return columns.AsReadOnly();
}
private static void ResolveDataUnit(OracleDataType dataType, object characterUsedValue)
{
dataType.Unit = !dataType.FullyQualifiedName.HasOwner && dataType.FullyQualifiedName.NormalizedName.In("\"VARCHAR\"", "\"VARCHAR2\"")
? String.Equals((string)characterUsedValue, "C") ? DataUnit.Character : DataUnit.Byte
: DataUnit.NotApplicable;
}
public void VisitPlSqlVariableReference(OraclePlSqlVariableReference variableReference)
{
if (variableReference.Variables.Count != 1)
{
return;
}
var element = variableReference.Variables.First();
var labelBuilder = new ToolTipLabelBuilder();
var elementTypeName = element.GetType().Name;
var elementName = $"{element.Name.ToSimpleIdentifier()}:";
switch (elementTypeName)
{
case nameof(OraclePlSqlParameter):
var parameter = (OraclePlSqlParameter)element;
labelBuilder.AddElement("Parameter");
labelBuilder.AddElement(elementName);
labelBuilder.AddElement(GetDataTypeFromNode(parameter));
labelBuilder.AddElement("NULL");
labelBuilder.AddElement(GetDefaultExpression(variableReference.PlSqlProgram, parameter), "= ");
break;
case nameof(OraclePlSqlVariable):
var variable = (OraclePlSqlVariable)element;
labelBuilder.AddElement(variable.IsConstant ? "Constant" : "Variable");
labelBuilder.AddElement(elementName);
var dataTypeName = variable.DataType == null?GetDataTypeFromNode(variable) : OracleDataType.ResolveFullTypeName(variable.DataType);
labelBuilder.AddElement(dataTypeName);
labelBuilder.AddElement(variable.Nullable ? "NULL" : "NOT NULL");
labelBuilder.AddElement(GetDefaultExpression(variableReference.PlSqlProgram, variable), "= ");
break;
case nameof(OraclePlSqlType):
labelBuilder.AddElement("Type");
labelBuilder.AddElement(elementName);
break;
case nameof(OraclePlSqlCursorVariable):
var cursor = (OraclePlSqlCursorVariable)element;
if (variableReference.ObjectNode != null && variableReference.IdentifierNode == _terminal)
{
labelBuilder.AddElement("Cursor column");
var columns = cursor.SemanticModel?.MainQueryBlock?.NamedColumns[variableReference.NormalizedName].ToArray();
var columnName = variableReference.NormalizedName.ToSimpleIdentifier();
var columnNameAndType = columns != null && columns.Length == 1 && !String.IsNullOrEmpty(columns[0].ColumnDescription.FullTypeName)
? $"{columnName}: {columns[0].ColumnDescription.FullTypeName}"
: columnName;
labelBuilder.AddElement($"{cursor.Name.ToSimpleIdentifier()}.{columnNameAndType}");
}
else
{
labelBuilder.AddElement(cursor.IsImplicit ? "Implicit cursor" : "Cursor");
labelBuilder.AddElement(elementName);
if (cursor.SemanticModel != null)
{
var queryText = cursor.SemanticModel.Statement.RootNode.GetText(variableReference.Container.SemanticModel.StatementText);
labelBuilder.AddElement(queryText);
}
}
break;
}
ToolTip = new ToolTipObject {
DataContext = labelBuilder.ToString()
};
}
