/// <summary>
/// Initializes a new instance of the<see cref="Column"/> class with a column name, data type, nullable and with a modifier.
/// </summary>
/// <param name="columnName">Name of the new column.</param>
/// <param name="nullable">True if columns is allowed to be null.</param>
/// <param name="modifier">Column modifiers.</param>
internal Column(string columnName, bool nullable, ColumnModifier modifier)
{
Validate.IsNotNullAndNotEmpty(columnName, nameof(columnName));
ColumnName = columnName;
Nullable = nullable;
Modifier = modifier;
}
private List <Col> GetColumns(string sourceDB, string sourceTableName, string schema, string originalTableName)
{
//get actual field list on the source table
var includeColumns = new List <string>()
{
"SYS_CHANGE_VERSION", "SYS_CHANGE_OPERATION"
};
var columns = sourceDataUtils.GetFieldList(sourceDB, sourceTableName, schema, originalTableName, includeColumns);
//get the table config object
var table = Config.TableByName(originalTableName);
var cols = new List <Col>();
foreach (TColumn col in columns)
{
string typeName = col.dataType.BaseType;
ColumnModifier mod = null;
//see if there are any column modifiers which override our length defaults
ColumnModifier[] modifiers = table.ColumnModifiers;
if (modifiers != null)
{
IEnumerable <ColumnModifier> mods = modifiers.Where(c => ((c.columnName == col.name) && (c.type == "ShortenField")));
mod = mods.FirstOrDefault();
}
string modDataType = DataType.MapDataType(SqlFlavor.MSSQL, SqlFlavor.MySQL, typeName);
if (typeName != modDataType)
{
if (mod != null && Regex.IsMatch(modDataType, @".*\(\d+\)$"))
{
modDataType = Regex.Replace(modDataType, @"\d+", mod.length.ToString());
}
cols.Add(new Col(col.name, modDataType, col.dataType));
continue;
}
if (col.dataType.UsesMaxLength())
{
if (mod != null)
{
typeName += "(" + mod.length + ")";
}
}
else if (col.dataType.UsesPrecisionScale())
{
typeName += "(" + col.dataType.NumericPrecision + "," + col.dataType.NumericScale + ")";
}
cols.Add(new Col(col.name, typeName, col.dataType));
}
return(cols);
}
public void TestSetFieldList()
{
TableConf t = new TableConf();
t.ColumnList = new string[] { "col1", "col2", "col3", "col4" };
var fields = new List<TColumn>{
{new TColumn("col1", true, null, true)},
{new TColumn("col2", true, null, true)},
{new TColumn("col3", true, null, true)},
{new TColumn("col4", true, null, true)}
};
var cm = new ColumnModifier();
cm.type = "ShortenField";
cm.length = 100;
cm.columnName = "col1";
t.ColumnModifiers = new ColumnModifier[] { cm };
SetFieldList(t, fields);
Assert.Equal("LEFT(CAST(P.[col1] AS NVARCHAR(MAX)),100) as 'col1',P.col2,P.col3,CT.col4", t.ModifiedMasterColumnList);
Assert.Equal("col1,col2,col3,col4", t.SlaveColumnList);
Assert.Equal("P.col1 = CT.col1 AND P.col4 = CT.col4", t.PkList);
Assert.Equal("P.col1 IS NOT NULL AND P.col4 IS NOT NULL", t.NotNullPKList);
Assert.Equal("P.col2=CT.col2,P.col3=CT.col3", t.MergeUpdateList);
}
public void TestSetFieldList()
{
TableConf t = new TableConf();
t.ColumnList = new string[] { "col1", "col2", "col3", "col4" };
var fields = new List <TColumn> {
{ new TColumn("col1", true, null, true) },
{ new TColumn("col2", true, null, true) },
{ new TColumn("col3", true, null, true) },
{ new TColumn("col4", true, null, true) }
};
var cm = new ColumnModifier();
cm.type = "ShortenField";
cm.length = 100;
cm.columnName = "col1";
t.ColumnModifiers = new ColumnModifier[] { cm };
SetFieldList(t, fields);
Assert.Equal("LEFT(CAST(P.[col1] AS NVARCHAR(MAX)),100) as 'col1',P.col2,P.col3,CT.col4", t.ModifiedMasterColumnList);
Assert.Equal("col1,col2,col3,col4", t.SlaveColumnList);
Assert.Equal("P.col1 = CT.col1 AND P.col4 = CT.col4", t.PkList);
Assert.Equal("P.col1 IS NOT NULL AND P.col4 IS NOT NULL", t.NotNullPKList);
Assert.Equal("P.col2=CT.col2,P.col3=CT.col3", t.MergeUpdateList);
}
private List <Col> GetColumns(string sourceDB, string sourceTableName, string schema, string originalTableName)
{
//get actual field list on the source table
var includeColumns = new List <string>()
{
"SYS_CHANGE_VERSION", "SYS_CHANGE_OPERATION"
};
var columns = sourceDataUtils.GetFieldList(sourceDB, sourceTableName, schema, originalTableName, includeColumns);
//get the table config object
var table = Config.TableByName(originalTableName);
var cols = new List <Col>();
bool isPrimaryKey;
foreach (TColumn col in columns)
{
isPrimaryKey = false;
if (table.columns.Count > 0)
{
// NOTE: it is important that table.columns is populated. Problem occurs if not.
// Because Vertica needs Primary Key information for the columns
try {
isPrimaryKey = table.columns.Where(c => (c.name != "SYS_CHANGE_VERSION" && c.name != "SYS_CHANGE_OPERATION")).First(c => c.name == col.name).isPk;
} catch (Exception e) {
logger.Log("Cannot determine whether column [" + col.name + "] is PK", LogLevel.Warn);
isPrimaryKey = false;
}
}
ColumnModifier mod = table.getColumnModifier(col.name);
string verticaTypeName = destDataUtils.MapColumnTypeName(SqlFlavor.MSSQL, col.dataType, mod);
cols.Add(new Col(VerticaDataUtils.MapReservedWord(col.name), verticaTypeName, col.dataType, isPrimaryKey));
}
return(cols);
}
public void Modify(ColumnModifier modifier)
{
_factory.AddModifier(new LambdaElementModifier(_matches, def => modifier));
}
/// <summary>
/// Initializes a new instance of the <see cref="Column"/> class as a non-null column with a column name, data type and with a modifier.
/// </summary>
/// <param name="columnName">Name of the new column.</param>
/// <param name="dataType">SQL data type.</param>
/// <param name="modifier">Column modifiers.</param>
public Column(string columnName, string dataType, ColumnModifier modifier) : this(columnName, dataType, false, modifier)
{
}
/// <summary>
/// Map source column type to Vertica column type, to the form of: data_type_name[(octet_length)]
/// NOTE: other DataUtils may consider implementing this method to map data types correctly
/// </summary>
/// <param name="sourceFlavor">SQL type of the data source</param>
/// <param name="sourceDataType">Data type in the source</param>
/// <param name="modifier">Modifier configured for the column</param>
/// <returns>string representing Vertica column type in the form of: data_type_name[(octet_length)]</returns>
public string MapColumnTypeName(SqlFlavor sourceFlavor, DataType sourceDataType, ColumnModifier modifier)
{
string typeName = "";
string modDataType = DataType.MapDataType(sourceFlavor, SqlFlavor.Vertica, sourceDataType.BaseType);
switch (sourceFlavor)
{
case SqlFlavor.MSSQL:
// these MSSQL types will carry over the length specs (CHARACTER_MAXIMUM_LENGTH) from MSSQL to Vertica
// NOTE: for these types, we have to make sure, in the data mapping file, the mapped-to Vertica data type is
// specified without the (LENGTH) suffix. That is:
// correct: char => char
// wrong: char => char(65000)
var typesUsingLength = new string[6] {
"binary", "char", "nchar", "nvarchar", "varbinary", "varchar"
};
// these MSSQL types will carry over the scale and precision specs (NUMERIC_PRECISION, NUMERIC_SCALE) from MSSQL to Vertica
var typesUsingScale = new string[4] {
"decimal", "money", "numeric", "smallmoney"
};
if (modifier != null)
{
// if modifier is specified, and matches regex, apply modifier to get type name
if (Regex.IsMatch(modDataType, @".*\(\d+\)$"))
{
// if (LENGTH) is specified in the mapped data type
typeName = Regex.Replace(modDataType, @"\d+", modifier.length.ToString());
}
else
{
// there is no (LENGTH) in the mapped data type
typeName = modDataType + "(" + modifier.length.ToString() + ")";
}
}
else
{
// if no modifier is specified, or regex does not match
string suffix = "";
if (typesUsingLength.Contains(sourceDataType.BaseType) && sourceDataType.CharacterMaximumLength != null)
{
// if this type uses length, and its CHARACTER_MAXIMUM_LENGTH is set in MSSQL
// if CHARACTER_MAXIMUM_LENGTH is -1 (max) [(n)varchar(max) types stored with a maxlen of -1 in MSSQL]
// or if CHARACTER_MAXIMUM_LENGTH is greater than Vertica string length,
// then change that to Vertica string length
// otherwise keep the CHARACTER_MAXIMUM_LENGTH value from MSSQL
suffix = "(" + ((sourceDataType.CharacterMaximumLength == -1 || sourceDataType.CharacterMaximumLength > Config.VerticaStringLength)
? Convert.ToString(Config.VerticaStringLength) : Convert.ToString(sourceDataType.CharacterMaximumLength)) + ")";
}
else if (typesUsingScale.Contains(sourceDataType.BaseType) && sourceDataType.NumericPrecision != null && sourceDataType.NumericScale != null)
{
// if this type uses scale and precision
// and both information are available from MSSQL
suffix = "(" + sourceDataType.NumericPrecision + ", " + sourceDataType.NumericScale + ")";
}
typeName = modDataType + suffix;
}
break;
default:
throw new NotImplementedException("Vertica column type name mapping is not implementation for this source: " + sourceFlavor.ToString());
}
return(typeName);
}
public void Modify(ColumnModifier modifier)
{
_factory.AddModifier(new LambdaElementModifier(_matches, def => modifier));
}
