public static List <TableColumnDesingerInfo> GetTableColumnDesingerInfos(DbInterpreter dbInterpreter, Table table, List <TableColumn> columns, List <TablePrimaryKey> primaryKeys)
{
List <TableColumnDesingerInfo> columnDesingerInfos = new List <TableColumnDesingerInfo>();
IEnumerable <string> dataTypes = DataTypeManager.GetDataTypeSpecifications(dbInterpreter.DatabaseType).Select(item => item.Name);
foreach (TableColumn column in columns)
{
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(column.DataType);
TableColumnDesingerInfo columnDesingerInfo = new TableColumnDesingerInfo()
{
OldName = column.Name,
IsPrimary = primaryKeys.Any(item => item.Columns.Any(t => t.ColumnName == column.Name)),
Length = dbInterpreter.GetColumnDataLength(column)
};
ObjectHelper.CopyProperties(column, columnDesingerInfo);
string dataType = column.IsUserDefined ? column.DataType : dataTypeInfo.DataType.ToLower();
if (!dataTypes.Contains(dataType))
{
dataTypeInfo = DataTypeHelper.GetSpecialDataTypeInfo(dataType);
dataType = dataTypeInfo.DataType;
columnDesingerInfo.Length = dataTypeInfo.Args;
}
columnDesingerInfo.DataType = dataType;
columnDesingerInfo.ExtraPropertyInfo = new TableColumnExtraPropertyInfo();
if (column.IsComputed)
{
columnDesingerInfo.ExtraPropertyInfo.Expression = column.ComputeExp;
}
if (table.IdentitySeed.HasValue)
{
columnDesingerInfo.ExtraPropertyInfo.Seed = table.IdentitySeed.Value;
columnDesingerInfo.ExtraPropertyInfo.Increment = table.IdentityIncrement.Value;
}
columnDesingerInfos.Add(columnDesingerInfo);
}
return(columnDesingerInfos);
}
private TableColumn CreateTableColumn(string dataType)
{
TableColumn column = new TableColumn();
if (dataType.IndexOf("(") > 0)
{
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(this.SourceInterpreter, dataType);
column.DataType = dataTypeInfo.DataType;
bool isChar = DataTypeHelper.IsCharType(dataType);
string dataLength = dataTypeInfo.Args;
string[] lengthItems = dataLength.Split(',');
int length;
if (!int.TryParse(lengthItems[0], out length))
{
length = -1;
}
if (isChar || DataTypeHelper.IsBinaryType(dataType))
{
column.MaxLength = length;
if (isChar && length != -1)
{
if (DataTypeHelper.StartWithN(dataType))
{
column.MaxLength = length * 2;
}
}
}
else if (length > 0)
{
column.Precision = length;
if (lengthItems.Length > 1)
{
column.Scale = int.Parse(lengthItems[1]);
}
}
}
else
{
UserDefinedType userDefinedType = this.UserDefinedTypes.FirstOrDefault(item => item.Name.Trim(this.TrimChars).ToUpper() == dataType.Trim(this.TrimChars).ToUpper());
if (userDefinedType != null)
{
column.DataType = userDefinedType.Type;
column.MaxLength = userDefinedType.MaxLength;
}
else
{
column.DataType = dataType;
}
}
return(column);
}
public string GenerateRoutineScripts(RoutineScript script)
{
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE OR REPLACE {script.Type.ToString()} {script.FullName}");
if (script.Parameters.Count > 0)
{
sb.AppendLine("(");
int i = 0;
foreach (Parameter parameter in script.Parameters)
{
ParameterType parameterType = parameter.ParameterType;
string dataType = parameter.DataType.Symbol;
string strParameterType = "";
int parenthesesIndex = dataType.IndexOf("(");
if (parenthesesIndex > 0)
{
dataType = dataType.Substring(0, parenthesesIndex);
}
if (parameterType.HasFlag(ParameterType.IN) && parameterType.HasFlag(ParameterType.OUT))
{
strParameterType = "IN OUT";
}
else if (parameterType != ParameterType.NONE)
{
strParameterType = parameterType.ToString();
}
sb.AppendLine($"{parameter.Name} {strParameterType} {dataType}{(i == script.Parameters.Count - 1 ? "" : ",")}");
i++;
}
sb.AppendLine(")");
}
if (script.Type == RoutineType.FUNCTION)
{
if (script.ReturnDataType != null)
{
string dataType = script.ReturnDataType.Symbol;
if (DataTypeHelper.IsCharType(dataType))
{
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(dataType);
dataType = dataTypeInfo.DataType;
}
sb.AppendLine($"RETURN {dataType}");
}
else if (script.ReturnTable != null)
{
//sb.AppendLine($"RETURN {script.ReturnTable}");
}
}
sb.AppendLine("AS");
foreach (Statement statement in script.Statements.Where(item => item is DeclareStatement || item is DeclareCursorStatement))
{
sb.Append(this.BuildStatement(statement).Replace("DECLARE ", ""));
}
sb.AppendLine("BEGIN");
if (script.ReturnTable != null)
{
}
FetchCursorStatement fetchCursorStatement = null;
foreach (Statement statement in script.Statements.Where(item => !(item is DeclareStatement || item is DeclareCursorStatement)))
{
if (statement is FetchCursorStatement fetch)
{
fetchCursorStatement = fetch;
continue;
}
else if (statement is WhileStatement @while)
{
FetchCursorStatement fs = @while.Statements.FirstOrDefault(item => item is FetchCursorStatement) as FetchCursorStatement;
if (fetchCursorStatement != null && fs != null)
{
@while.Condition.Symbol = "1=1";
if (fs.Variables.Count == 0)
{
@while.Statements.Insert(0, new LoopExitStatement()
{
Condition = new TokenInfo($"{fs.CursorName}%NOTFOUND")
});
@while.Statements.Insert(0, fetchCursorStatement);
}
}
}
sb.AppendLine(this.BuildStatement(statement));
}
sb.AppendLine($"END {script.FullName};");
return(this.FormatScripts(sb.ToString()));
}
public void ConvertDataType(TableColumn column)
{
string originalDataType = column.DataType;
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(this.sourceDbInterpreter, originalDataType);
string sourceDataType = dataTypeInfo.DataType;
column.DataType = sourceDataType;
DataTypeSpecification sourceDataTypeSpec = this.GetDataTypeSpecification(this.sourceDataTypeSpecs, sourceDataType);
if (!string.IsNullOrEmpty(dataTypeInfo.Args))
{
if (sourceDataTypeSpec.Args == "scale")
{
column.Scale = int.Parse(dataTypeInfo.Args);
}
else if (sourceDataTypeSpec.Args == "length")
{
if (column.MaxLength == null)
{
column.MaxLength = int.Parse(dataTypeInfo.Args);
}
}
}
DataTypeMapping dataTypeMapping = this.dataTypeMappings.FirstOrDefault(item => item.Source.Type?.ToLower() == column.DataType?.ToLower() ||
(item.Source.IsExpression && Regex.IsMatch(column.DataType, item.Source.Type, RegexOptions.IgnoreCase))
);
if (dataTypeMapping != null)
{
DataTypeMappingSource sourceMapping = dataTypeMapping.Source;
DataTypeMappingTarget targetMapping = dataTypeMapping.Target;
string targetDataType = targetMapping.Type;
DataTypeSpecification targetDataTypeSpec = this.GetDataTypeSpecification(this.targetDataTypeSpecs, targetDataType);
column.DataType = targetDataType;
bool isChar = DataTypeHelper.IsCharType(column.DataType);
bool isBinary = DataTypeHelper.IsBinaryType(column.DataType);
if (isChar || isBinary)
{
if (isChar)
{
if (!string.IsNullOrEmpty(targetMapping.Length))
{
column.MaxLength = int.Parse(targetMapping.Length);
if (DataTypeHelper.StartWithN(targetDataType) && !DataTypeHelper.StartWithN(sourceDataType))
{
column.MaxLength *= 2;
}
}
}
if (dataTypeMapping.Specials != null && dataTypeMapping.Specials.Count > 0)
{
DataTypeMappingSpecial special = dataTypeMapping.Specials.FirstOrDefault(item => this.IsSpecialMaxLengthMatched(item, column));
if (special != null)
{
column.DataType = special.Type;
if (!string.IsNullOrEmpty(special.TargetMaxLength))
{
column.MaxLength = int.Parse(special.TargetMaxLength);
}
}
}
if (column.MaxLength == -1)
{
ArgumentRange?sourceLengthRange = DataTypeManager.GetArgumentRange(sourceDataTypeSpec, "length");
if (sourceLengthRange.HasValue)
{
column.MaxLength = sourceLengthRange.Value.Max;
}
}
ArgumentRange?targetLengthRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "length");
if (targetLengthRange.HasValue)
{
int targetMaxLength = targetLengthRange.Value.Max;
if (column.MaxLength > targetMaxLength)
{
if (!string.IsNullOrEmpty(targetMapping.Substitute))
{
string[] substitutes = targetMapping.Substitute.Split(',');
foreach (string substitute in substitutes)
{
DataTypeSpecification dataTypeSpec = this.GetDataTypeSpecification(this.targetDataTypeSpecs, substitute.Trim());
if (dataTypeSpec != null)
{
if (string.IsNullOrEmpty(dataTypeSpec.Args))
{
column.DataType = substitute;
break;
}
else
{
ArgumentRange?range = DataTypeManager.GetArgumentRange(dataTypeSpec, "length");
if (range.HasValue && range.Value.Max >= column.MaxLength)
{
column.DataType = substitute;
break;
}
}
}
}
}
}
}
}
else
{
if (dataTypeMapping.Specials != null && dataTypeMapping.Specials.Count > 0)
{
foreach (DataTypeMappingSpecial special in dataTypeMapping.Specials)
{
string name = special.Name;
bool matched = false;
if (name == "maxLength")
{
matched = this.IsSpecialMaxLengthMatched(special, column);
}
else if (name.Contains("precision") || name.Contains("scale"))
{
matched = this.IsSpecialPrecisionOrScaleMatched(special, column);
}
else if (name == "expression")
{
matched = this.IsSpecialExpressionMatched(special, originalDataType);
}
if (matched)
{
column.DataType = special.Type;
}
if (!string.IsNullOrEmpty(special.TargetMaxLength))
{
column.MaxLength = int.Parse(special.TargetMaxLength);
}
}
}
if (string.IsNullOrEmpty(targetDataTypeSpec.Format))
{
bool useConfigPrecisionScale = false;
if (!string.IsNullOrEmpty(targetMapping.Precision))
{
column.Precision = int.Parse(targetMapping.Precision);
useConfigPrecisionScale = true;
}
if (!string.IsNullOrEmpty(targetMapping.Scale))
{
column.Scale = int.Parse(targetMapping.Scale);
useConfigPrecisionScale = true;
}
if (!useConfigPrecisionScale)
{
if (sourceDataTypeSpec.Args == "scale")
{
column.Precision = default(int?);
}
else if (sourceDataTypeSpec.Args == "precision,scale" && sourceDataTypeSpec.Args == targetDataTypeSpec.Args)
{
ArgumentRange?precisionRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "precision");
ArgumentRange?scaleRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "scale");
if (precisionRange.HasValue && column.Precision > precisionRange.Value.Max)
{
column.Precision = precisionRange.Value.Max;
}
if (scaleRange.HasValue && column.Scale > scaleRange.Value.Max)
{
column.Scale = scaleRange.Value.Max;
}
if (column.Precision.HasValue)
{
if (column.DataType.ToLower() == "int")
{
if (column.Precision.Value > 10)
{
column.DataType = "bigint";
}
}
}
}
}
}
else
{
string format = targetDataTypeSpec.Format;
string dataType = format;
string[] defaultValues = targetDataTypeSpec.Default?.Split(',');
string targetMappingArgs = targetMapping.Args;
int i = 0;
foreach (DataTypeArgument arg in targetDataTypeSpec.Arugments)
{
if (arg.Name.ToLower() == "scale")
{
ArgumentRange?targetScaleRange = DataTypeManager.GetArgumentRange(targetDataTypeSpec, "scale");
int scale = column.Scale == null ? 0 : column.Scale.Value;
if (targetScaleRange.HasValue && scale > targetScaleRange.Value.Max)
{
scale = targetScaleRange.Value.Max;
}
dataType = dataType.Replace("$scale$", scale.ToString());
}
else
{
string defaultValue = defaultValues != null && defaultValues.Length > i ? defaultValues[i] : "";
string value = defaultValue;
if (targetMapping.Arguments.Any(item => item.Name == arg.Name))
{
value = targetMapping.Arguments.FirstOrDefault(item => item.Name == arg.Name).Value;
}
dataType = dataType.Replace($"${arg.Name}$", value);
}
i++;
}
column.DataType = dataType;
}
}
}
}
