/// <summary> /// Returns a string representation of the column for use in CREATE TABLE statements /// </summary> public string ToExpression(SqlFlavor flavor = SqlFlavor.MSSQL) { switch (flavor) { case SqlFlavor.MSSQL: return(string.Format("[{0}] {1} {2}", name, dataType.ToString(), isNullable ? "NULL" : "NOT NULL")); case SqlFlavor.MySQL: return(string.Format("{0} {1} {2}", name, dataType.ToString(), isNullable ? "NULL" : "NOT NULL")); default: throw new NotImplementedException("No defined ToExpression for sql flavor: " + flavor.ToString()); } }
/// <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); }