/// <summary>
/// Returns an appropriate class that implements IDataCopy based on the passed in source and dest Sql types
/// </summary>
public static IDataCopy GetInstance(SqlFlavor sourceSqlFlavor, SqlFlavor destSqlFlavor, IDataUtils sourceDataUtils, IDataUtils destDataUtils, Logger logger)
{
if (sourceDataUtils.GetType() == typeof(TestDataUtils) && destDataUtils.GetType() == typeof(TestDataUtils)) {
return new TestDataCopy((TestDataUtils)sourceDataUtils, (TestDataUtils)destDataUtils);
}
switch (sourceSqlFlavor) {
case SqlFlavor.MSSQL:
if (destSqlFlavor == SqlFlavor.MSSQL) {
return new MSSQLToMSSQLDataCopy((MSSQLDataUtils)sourceDataUtils, (MSSQLDataUtils)destDataUtils, logger);
} else if (destSqlFlavor == SqlFlavor.Netezza) {
return new MSSQLToNetezzaDataCopy((MSSQLDataUtils)sourceDataUtils, (NetezzaDataUtils)destDataUtils, logger, Config.Slave, Config.NetezzaUser, Config.NetezzaPrivateKeyPath);
} else if (destSqlFlavor == SqlFlavor.Vertica) {
return new MSSQLToVerticaDataCopy((MSSQLDataUtils)sourceDataUtils, (VerticaDataUtils)destDataUtils, logger);
} else if (destSqlFlavor == SqlFlavor.MySQL) {
return new MSSQLToMySQLDataCopy((MSSQLDataUtils)sourceDataUtils, (MySQLDataUtils)destDataUtils, logger);
}
break;
case SqlFlavor.MySQL:
if (destSqlFlavor == SqlFlavor.MSSQL) {
return new MySQLToMSSQLDataCopy((MySQLDataUtils)sourceDataUtils, (MSSQLDataUtils)destDataUtils, logger);
}
break;
}
//if we get here without returning it means something was passed in that isn't supported
throw new NotImplementedException("Specified SQL types not supported for data copying!");
}
/// <summary>
/// Execute the insert statement, and return the assigned primary key
/// </summary>
/// <param name="databaseAssignsKey">if true, returns newly assigned key</param>
/// <returns>null or a newly assigned key</returns>
public Task <object> Execute(IDbConnection db, string tableName, string pkColName, bool databaseAssignsKey)
{
string nonKeyColNames = string.Join(",", Cols.Select(c => c.Name));
string valuesList = string.Join(",", Cols.Select(c => c.LiteralExpression));
using (var cmd = db.CreateCommand())
{
foreach (var c in Cols)
{
cmd.Parameters.Add(c.AsParameter(cmd));
}
string coreCommand = $"insert into {tableName} ({nonKeyColNames}) values ({valuesList})";
if (databaseAssignsKey)
{
cmd.CommandText = coreCommand + SqlFlavor.BuildGetIdentityClause(pkColName);
cmd.CommandText = SqlCustomizer(cmd.CommandText);
return(Task.FromResult(cmd.ExecuteScalar()));
}
else
{
cmd.CommandText = SqlCustomizer(coreCommand);
cmd.ExecuteNonQuery();
return(Task.FromResult <object>(null));
}
}
}
/// <summary>
/// Returns an appropriate class that implements IDataUtils based on the passed in Sql type
/// </summary>
public static IDataUtils GetInstance(Logger logger, TServer server, SqlFlavor flavor)
{
switch (flavor) {
case SqlFlavor.MSSQL:
return new MSSQLDataUtils(logger, server);
case SqlFlavor.Netezza:
return new NetezzaDataUtils(logger, server);
}
//if we get here without returning it means something was passed in that isn't supported
throw new NotImplementedException("Specified SQL types not supported for data copying!");
}
/// <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>
/// Validates a sql flavor (MSSQL, Netezza, Vertica, etc.) and returns a SqlFlavor enum
/// </summary>
/// <param name="sqltype">String representing the sql flavor</param>
/// <returns>SqlFlavor enum</returns>
public static SqlFlavor ValidateSqlFlavor(string sqltype)
{
SqlFlavor flavor = new SqlFlavor();
if (String.IsNullOrEmpty(sqltype))
{
throw new InvalidDataException("Sql type must be specified!");
}
else
{
try {
flavor = (SqlFlavor)Enum.Parse(typeof(SqlFlavor), sqltype);
} catch {
throw new InvalidDataException("Invalid SQL type: " + sqltype);
}
}
return(flavor);
}
public static string MapDataType(SqlFlavor source, SqlFlavor dest, string datatype)
{
datatype = datatype.ToLower();
if (dataMappings.Count == 0)
{
throw new Exception("Data mappings file not loaded, unable to map data types! Please specify a data mappings file using the -p argument of Tesla.");
}
if (!dataMappings.ContainsKey(source) || !dataMappings.ContainsKey(dest))
{
throw new Exception("The data mappings file seems to be incorrect, please specify a data type mappings file that maps from " + source + " to " + dest);
}
int idx = dataMappings[source].IndexOf(datatype);
if (idx == -1)
{
return(datatype);
}
return(dataMappings[dest][idx]);
}
/// <summary>
/// Returns an appropriate class that implements IDataUtils based on the passed in Sql type
/// </summary>
public static IDataUtils GetInstance(Logger logger, TServer server, SqlFlavor flavor)
{
switch (flavor)
{
case SqlFlavor.MSSQL:
return(new MSSQLDataUtils(logger, server));
case SqlFlavor.Netezza:
return(new NetezzaDataUtils(logger, server));
case SqlFlavor.MySQL:
return(new MySQLDataUtils(logger, server));
case SqlFlavor.Vertica:
return(new VerticaDataUtils(logger, server));
}
//if we get here without returning it means something was passed in that isn't supported
throw new NotImplementedException("Specified SQL types not supported for data copying!");
}
public static void LoadDataMappings(string mappings)
{
using (var reader = new StringReader(mappings)) {
var flavors = new List <SqlFlavor>();
string[] headers = reader.ReadLine().Split(new char[] { '\t' });
foreach (var flavor in headers)
{
SqlFlavor fl = (SqlFlavor)Enum.Parse(typeof(SqlFlavor), flavor);
flavors.Add(fl);
dataMappings[fl] = new List <string>();
}
while (reader.Peek() > 0)
{
string[] map = reader.ReadLine().Split(new char[] { '\t' });
for (int i = 0; i < map.Length; i++)
{
dataMappings[flavors[i]].Add(map[i].ToLower());
}
}
}
}
/// <summary>
/// Returns an appropriate class that implements IDataCopy based on the passed in source and dest Sql types
/// </summary>
public static IDataCopy GetInstance(SqlFlavor sourceSqlFlavor, SqlFlavor destSqlFlavor, IDataUtils sourceDataUtils, IDataUtils destDataUtils, Logger logger)
{
if (sourceDataUtils.GetType() == typeof(TestDataUtils) && destDataUtils.GetType() == typeof(TestDataUtils))
{
return(new TestDataCopy((TestDataUtils)sourceDataUtils, (TestDataUtils)destDataUtils));
}
switch (sourceSqlFlavor)
{
case SqlFlavor.MSSQL:
if (destSqlFlavor == SqlFlavor.MSSQL)
{
return(new MSSQLToMSSQLDataCopy((MSSQLDataUtils)sourceDataUtils, (MSSQLDataUtils)destDataUtils, logger));
}
else if (destSqlFlavor == SqlFlavor.Netezza)
{
return(new MSSQLToNetezzaDataCopy((MSSQLDataUtils)sourceDataUtils, (NetezzaDataUtils)destDataUtils, logger, Config.Slave, Config.NetezzaUser, Config.NetezzaPrivateKeyPath));
}
else if (destSqlFlavor == SqlFlavor.Vertica)
{
return(new MSSQLToVerticaDataCopy((MSSQLDataUtils)sourceDataUtils, (VerticaDataUtils)destDataUtils, logger));
}
else if (destSqlFlavor == SqlFlavor.MySQL)
{
return(new MSSQLToMySQLDataCopy((MSSQLDataUtils)sourceDataUtils, (MySQLDataUtils)destDataUtils, logger));
}
break;
case SqlFlavor.MySQL:
if (destSqlFlavor == SqlFlavor.MSSQL)
{
return(new MySQLToMSSQLDataCopy((MySQLDataUtils)sourceDataUtils, (MSSQLDataUtils)destDataUtils, logger));
}
break;
}
//if we get here without returning it means something was passed in that isn't supported
throw new NotImplementedException("Specified SQL types not supported for data copying!");
}
public static string MapDataType(SqlFlavor source, SqlFlavor dest, string datatype)
{
datatype = datatype.ToLower();
if (dataMappings.Count == 0) {
throw new Exception("Data mappings file not loaded, unable to map data types! Please specify a data mappings file using the -p argument of Tesla.");
}
if (!dataMappings.ContainsKey(source) || !dataMappings.ContainsKey(dest)) {
throw new Exception("The data mappings file seems to be incorrect, please specify a data type mappings file that maps from " + source + " to " + dest);
}
int idx = dataMappings[source].IndexOf(datatype);
if (idx == -1) {
return datatype;
}
return dataMappings[dest][idx];
}
/// <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);
}
