public override string GetColumnDataLength(TableColumn column)
{
string dataType = column.DataType;
string dataLength = string.Empty;
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(this, dataType);
bool isChar = DataTypeHelper.IsCharType(dataType);
bool isBinary = DataTypeHelper.IsBinaryType(dataType);
DataTypeSpecification dataTypeSpec = this.GetDataTypeSpecification(dataTypeInfo.DataType);
if (dataTypeSpec != null)
{
if (!string.IsNullOrEmpty(dataTypeSpec.Args))
{
if (string.IsNullOrEmpty(dataTypeInfo.Args))
{
if (isChar || isBinary)
{
dataLength = column.MaxLength.ToString();
}
else if (!this.IsNoLengthDataType(dataType))
{
dataLength = this.GetDataTypePrecisionScale(column, dataTypeInfo.DataType);
}
}
else
{
dataLength = dataTypeInfo.Args;
}
}
}
return(dataLength);
}
public string GetDataTypePrecisionScale(TableColumn column, string dataType)
{
DataTypeSpecification dataTypeSpecification = this.GetDataTypeSpecification(dataType);
if (dataTypeSpecification != null)
{
long precision = column.Precision.HasValue ? column.Precision.Value : 0;
int scale = column.Scale.HasValue ? column.Scale.Value : 0;
if (dataTypeSpecification.Args.Contains(","))
{
if (precision > 0)
{
return($"{precision},{scale}");
}
}
else if (dataTypeSpecification.Args == "scale")
{
ArgumentRange?range = DataTypeManager.GetArgumentRange(dataTypeSpecification, "scale");
if (range.HasValue)
{
if (scale > range.Value.Max)
{
scale = range.Value.Max;
}
}
return($"{scale}");
}
}
return(string.Empty);
}
public static ArgumentRange?GetArgumentRange(DataTypeSpecification dataTypeSpecification, string argumentName)
{
ArgumentRange?range = default(ArgumentRange?);
if (dataTypeSpecification.Arugments.Any(item => item.Name.ToLower() == argumentName.ToLower()))
{
return(dataTypeSpecification.Arugments.FirstOrDefault(item => item.Name.ToLower() == argumentName.ToLower()).Range);
}
return(range);
}
private void SetColumnCellsReadonly(DataGridViewRow row)
{
DataGridViewCell lengthCell = row.Cells[this.colLength.Name];
DataGridViewCell primaryCell = row.Cells[this.colPrimary.Name];
DataGridViewCell identityCell = row.Cells[this.colIdentity.Name];
string dataType = DataGridViewHelper.GetCellStringValue(row, this.colDataType.Name);
if (!string.IsNullOrEmpty(dataType))
{
UserDefinedType userDefindedType = this.GetUserDefinedType(dataType);
if (userDefindedType != null)
{
dataType = userDefindedType.Type;
}
DataTypeSpecification dataTypeSpec = this.dataTypeSpecifications.FirstOrDefault(item => item.Name == dataType);
if (dataTypeSpec != null)
{
bool isLengthReadOnly = userDefindedType != null || string.IsNullOrEmpty(dataTypeSpec.Args);
bool isPrimaryReadOnly = dataTypeSpec.IndexForbidden;
bool isIdentityReadOnly = !dataTypeSpec.AllowIdentity;
lengthCell.ReadOnly = isLengthReadOnly;
primaryCell.ReadOnly = isPrimaryReadOnly;
identityCell.ReadOnly = isIdentityReadOnly;
if (isLengthReadOnly)
{
lengthCell.Value = null;
}
if (isPrimaryReadOnly)
{
primaryCell.Value = false;
}
if (isIdentityReadOnly)
{
identityCell.Value = false;
}
}
}
else
{
lengthCell.ReadOnly = true;
primaryCell.ReadOnly = true;
identityCell.ReadOnly = true;
}
}
public static DataTypeSpecification ParseArgument(DataTypeSpecification dataTypeSpecification)
{
if (string.IsNullOrEmpty(dataTypeSpecification.Args) || dataTypeSpecification.Arugments.Count > 0)
{
return(dataTypeSpecification);
}
if (!string.IsNullOrEmpty(dataTypeSpecification.Range))
{
string[] argItems = dataTypeSpecification.Args.Split(ArugumentRangeItemDelimiter);
string[] rangeItems = dataTypeSpecification.Range.Split(ArugumentRangeItemDelimiter);
int i = 0;
foreach (string argItem in argItems)
{
DataTypeArgument argument = new DataTypeArgument()
{
Name = argItem
};
if (i < rangeItems.Length)
{
ArgumentRange range = new ArgumentRange();
string[] rangeValues = rangeItems[i].Split(ArugumentRangeValueDelimiter);
range.Min = int.Parse(rangeValues[0]);
if (rangeValues.Length > 1)
{
range.Max = int.Parse(rangeValues[1]);
}
else
{
range.Max = range.Min;
}
argument.Range = range;
}
dataTypeSpecification.Arugments.Add(argument);
i++;
}
}
return(dataTypeSpecification);
}
private void ShowColumnSelector(DatabaseObjectType databaseObjectType, IEnumerable <IndexColumn> values, bool columnIsReadonly)
{
frmColumSelect columnSelect = new frmColumSelect()
{
ColumnIsReadOnly = columnIsReadonly
};
IEnumerable <TableColumnDesingerInfo> columns = this.ucColumns.GetColumns().Where(item => !string.IsNullOrEmpty(item.Name));
List <IndexColumn> columnInfos = new List <IndexColumn>();
foreach (TableColumnDesingerInfo column in columns)
{
if (databaseObjectType == DatabaseObjectType.TableIndex)
{
if (!string.IsNullOrEmpty(column.DataType) && string.IsNullOrEmpty(column.ExtraPropertyInfo?.Expression))
{
DataTypeSpecification dataTypeSpec = DataTypeManager.GetDataTypeSpecification(this.displayInfo.DatabaseType, column.DataType);
if (dataTypeSpec != null && !dataTypeSpec.IndexForbidden)
{
columnInfos.Add(new IndexColumn()
{
ColumnName = column.Name
});
}
}
}
}
columnSelect.InitControls(columnInfos, this.displayInfo.DatabaseType == DatabaseType.SqlServer);
columnSelect.LoadColumns(values);
if (columnSelect.ShowDialog() == DialogResult.OK)
{
if (databaseObjectType == DatabaseObjectType.TableIndex)
{
this.ucIndexes.SetRowColumns(columnSelect.SelectedColumns);
}
}
}
public async Task <DataTable> GetPagedDataTableAsync(DbConnection connection, Table table, List <TableColumn> columns, string orderColumns, long total, int pageSize, long pageNumber, string whereClause = "")
{
string quotedTableName = this.GetQuotedObjectName(table);
List <string> columnNames = new List <string>();
foreach (TableColumn column in columns)
{
string columnName = this.GetQuotedString(column.Name);
#region Convert MySql float to decimal, avoid scientific notation
if (this.DatabaseType == DatabaseType.MySql && column.DataType.ToLower().Contains("float"))
{
DataTypeInfo dataTypeInfo = DataTypeHelper.GetDataTypeInfo(column.DataType);
if (!string.IsNullOrEmpty(dataTypeInfo.Args))
{
string strPrecision = dataTypeInfo.Args.Split(',')[0].Trim();
int precision;
DataTypeSpecification dataTypeSpec = this.GetDataTypeSpecification("decimal");
if (dataTypeSpec != null)
{
ArgumentRange?precisionRange = DataTypeManager.GetArgumentRange(dataTypeSpec, "precision");
if (precisionRange.HasValue)
{
if (int.TryParse(strPrecision, out precision) && precision > 0 && precision <= precisionRange.Value.Max)
{
columnName = $"CONVERT({columnName},DECIMAL({dataTypeInfo.Args})) AS {columnName}";
}
}
}
}
}
#endregion
#region Convert Oracle number to char
else if (this.DatabaseType == DatabaseType.Oracle && column.DataType.ToLower().Contains("number"))
{
columnName = $"TO_CHAR({columnName}) AS {columnName}";
}
#endregion
columnNames.Add(columnName);
}
string strColumnNames = string.Join(",", columnNames);
string pagedSql = this.GetSqlForPagination(quotedTableName, strColumnNames, orderColumns, whereClause, pageNumber, pageSize);
DataTable dt = await this.GetDataTableAsync(connection, pagedSql);
if (dt.Columns.OfType <DataColumn>().Any(item => item.ColumnName == RowNumberColumnName))
{
dt.Columns.Remove(RowNumberColumnName);
}
return(dt);
}
private static bool IsTableColumnDataTypeAndLengthEquals(DatabaseType databaseType, TableColumn column1, TableColumn column2)
{
if (column1.IsUserDefined != column2.IsUserDefined)
{
return(false);
}
else if (column1.IsUserDefined && column2.IsUserDefined)
{
return(column1.DataType == column2.DataType);
}
DataTypeInfo dataTypeInfo1 = DataTypeHelper.GetDataTypeInfo(column1.DataType);
DataTypeInfo dataTypeInfo2 = DataTypeHelper.GetDataTypeInfo(column2.DataType);
var dataTypeSpecs1 = DataTypeManager.GetDataTypeSpecifications(databaseType);
var dataTypeSpecs2 = DataTypeManager.GetDataTypeSpecifications(databaseType);
string dataType1 = dataTypeInfo1.DataType;
string dataType2 = dataTypeInfo2.DataType;
if (!dataTypeSpecs1.Any(item => item.Name == dataType1))
{
dataTypeInfo1 = DataTypeHelper.GetSpecialDataTypeInfo(column1.DataType.ToLower());
dataType1 = dataTypeInfo1.DataType;
}
if (!dataTypeSpecs2.Any(item => item.Name == dataType2))
{
dataTypeInfo2 = DataTypeHelper.GetSpecialDataTypeInfo(column2.DataType.ToLower());
dataType2 = dataTypeInfo2.DataType;
}
if (dataType1.ToLower() != dataType2.ToLower())
{
return(false);
}
DataTypeSpecification dataTypeSpec1 = dataTypeSpecs1.FirstOrDefault(item => item.Name == dataType1);
DataTypeSpecification dataTypeSpec2 = dataTypeSpecs2.FirstOrDefault(item => item.Name == dataType2);
if (dataTypeInfo1.DataType == dataTypeInfo2.DataType && string.IsNullOrEmpty(dataTypeSpec1.Args) && string.IsNullOrEmpty(dataTypeSpec2.Args))
{
return(true);
}
bool isChar1 = DataTypeHelper.IsCharType(dataType1);
bool isChar2 = DataTypeHelper.IsCharType(dataType2);
bool isBytes1 = DataTypeHelper.IsCharType(dataType1);
bool isBytes2 = DataTypeHelper.IsCharType(dataType2);
if (isBytes1 && isBytes2)
{
return(column1.MaxLength == column2.MaxLength);
}
else if (isChar1 && isChar2 && DataTypeHelper.StartWithN(dataType1) && DataTypeHelper.StartWithN(dataType2))
{
return(column1.MaxLength == column2.MaxLength);
}
else if ((column1.Precision == null && column1.Scale == null && column1.MaxLength == column2.Precision) ||
(column2.Precision == null && column2.Scale == null && column2.MaxLength == column1.Precision))
{
return(true);
}
if (dataTypeSpec1.Name == dataTypeSpec2.Name && dataTypeSpec1.Args?.Contains("length") == false)
{
if (dataTypeSpec1.Args == "scale")
{
return(IsPrecisionScaleEquals(column1.Scale, column2.Scale));
}
else if (dataTypeSpec1.Args == "precision")
{
return(IsPrecisionScaleEquals(column1.Precision, column2.Precision));
}
else if (dataTypeSpec1.Args?.Contains("scale") == true || dataTypeSpec1.Args?.Contains("precision") == true)
{
return(IsPrecisionScaleEquals(column1.Precision, column2.Precision) &&
IsPrecisionScaleEquals(column1.Scale, column2.Scale));
}
}
return(column1.MaxLength == column2.MaxLength &&
IsPrecisionScaleEquals(column1.Precision, column2.Precision) &&
IsPrecisionScaleEquals(column1.Scale, column2.Scale));
}
public static bool ValidateDataType(DatabaseType databaseType, TableColumnDesingerInfo columnDesingerInfo, out string message)
{
message = "";
if (columnDesingerInfo.IsUserDefined)
{
return(true);
}
string columName = columnDesingerInfo.Name;
string dataType = columnDesingerInfo.DataType;
DataTypeSpecification dataTypeSpec = DataTypeManager.GetDataTypeSpecification(databaseType, dataType);
if (dataTypeSpec == null)
{
message = $"Invalid data type:{dataType}";
return(false);
}
if (!string.IsNullOrEmpty(dataTypeSpec.Args))
{
string length = columnDesingerInfo?.Length?.Trim();
if (string.IsNullOrEmpty(length) && dataTypeSpec.Optional)
{
return(true);
}
if (dataTypeSpec.AllowMax && !string.IsNullOrEmpty(length) && length.ToLower() == "max")
{
return(true);
}
string args = dataTypeSpec.Args;
string[] argsNames = args.Split(',');
string[] lengthItems = length?.Split(',');
if (argsNames.Length != lengthItems?.Length)
{
if (argsNames.Length == 2 && lengthItems?.Length == 1)
{
lengthItems = new string[] { lengthItems[0], "0" };
}
else
{
message = $"Length is invalid for column \"{columName}\", it's format should be:{args}";
return(false);
}
}
int i = 0;
foreach (string argName in argsNames)
{
string lengthItem = lengthItems[i];
ArgumentRange?range = DataTypeManager.GetArgumentRange(dataTypeSpec, argName);
if (range.HasValue)
{
int lenValue;
if (!int.TryParse(lengthItem, out lenValue))
{
message = $"\"{lengthItem}\" is't a valid integer value";
return(false);
}
if (lenValue < range.Value.Min || lenValue > range.Value.Max)
{
message = $"The \"{argName}\"'s range of column \"{columName}\" should be between {range.Value.Min} and {range.Value.Max}";
return(false);
}
}
i++;
}
}
return(true);
}
public static void SetColumnLength(DatabaseType databaseType, TableColumn column, string length)
{
string dataType = column.DataType;
DataTypeSpecification dataTypeSpec = DataTypeManager.GetDataTypeSpecification(databaseType, dataType);
if (!string.IsNullOrEmpty(dataTypeSpec.MapTo))
{
column.DataType = dataTypeSpec.MapTo;
return;
}
string args = dataTypeSpec.Args;
if (string.IsNullOrEmpty(args))
{
return;
}
string[] argsNames = args.Split(',');
string[] lengthItems = length?.Split(',');
int i = 0;
foreach (string argName in argsNames)
{
if (lengthItems == null || i > lengthItems.Length - 1)
{
continue;
}
string lengthItem = lengthItems[i];
if (argName == "length")
{
bool isChar = DataTypeHelper.IsCharType(dataType);
if (isChar)
{
if (dataTypeSpec.AllowMax && lengthItem.ToLower() == "max")
{
column.MaxLength = -1;
}
else
{
column.MaxLength = long.Parse(lengthItem) * (DataTypeHelper.StartWithN(dataType) ? 2 : 1);
}
}
else
{
column.MaxLength = long.Parse(lengthItem);
}
}
else if (argName == "precision" || argName == "dayScale")
{
column.Precision = int.Parse(lengthItem);
}
else if (argName == "scale")
{
column.Scale = int.Parse(lengthItem);
}
i++;
}
}
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;
}
}
}
}