private static string OracleToSqlServerConversion(string dataType, int providerType, int?precision, int?scale)
{
if (dataType == "VARCHAR2")
{
return("VARCHAR");
}
if (dataType == "NVARCHAR2")
{
return("NVARCHAR");
}
//DateTime in SQL Server range from 1753 A.D. to 9999 A.D., whereas dates in Oracle range from 4712 B.C. to 4712 A.D. For 2008, DateTime2 is 0001-9999, plus more accuracy.
if (dataType == "DATE" && providerType != (int)SqlDbType.Date)
{
return("DATETIME");
}
//Oracle timestamp is a date with fractional sections. SqlServer timestamp is a binary type used for optimistic concurrency.
if (dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) && providerType != (int)SqlDbType.Timestamp)
{
return("DATETIME");
}
//Oracle numbers- use precise SqlServer versiom
if (dataType == "NUMBER")
{
return(DataTypeConverter.OracleNumberConversion(precision, scale));
}
//not an exact match
if (dataType == "XMLTYPE")
{
return("XML");
}
return(dataType);
}
private static string OtherDatabaseTypesToDb2(string dataType, DatabaseColumn column)
{
//string types
if (DataTypeConverter.IsFixedLengthString(dataType))
{
return("CHAR");
}
if (DataTypeConverter.IsLongString(dataType))
{
return(dataType == "CLOB" ? "CLOB" : "DBCLOB");
}
if (DataTypeConverter.IsVariableString(dataType))
{
if (column.Length == -1)
{
return("DBCLOB");
}
return("VARCHAR");
}
//numeric types
if (dataType == "SMALLINT")
{
return(dataType);
}
if (dataType == "BIGINT")
{
return(dataType);
}
if (dataType == "INTEGER")
{
return(dataType);
}
if (dataType == "INT")
{
return("INTEGER");
}
if (dataType == "NUM")
{
return("NUMERIC"); //DB2 alias
}
if (dataType == "DEC")
{
return("DECIMAL"); //DB2 alias
}
if (dataType == "MONEY")
{
return("DECIMAL(19,4)");
}
if (dataType == "BIT")
{
return("SMALLINT"); //could be CHAR(1) but nicer with an integer
}
if (dataType == "NUMBER")
{
return(DataTypeConverter.OracleNumberConversion(column.Precision, column.Scale));
}
//date times
//SqlServer Timestamp is a binary
if (DataTypeConverter.IsSqlServerTimestamp(dataType, column))
{
return("GRAPHIC");
}
if (DataTypeConverter.IsDateTime(dataType))
{
return("TIMESTAMP");
}
//bytes
if (DataTypeConverter.IsBlob(dataType, column))
{
return("BLOB");
}
if (DataTypeConverter.IsBinary(dataType))
{
if (dataType == "LONG VARGRAPHIC")
{
return(dataType);
}
if (dataType == "GRAPHIC")
{
return(dataType);
}
return("VARGRAPHIC");
}
//other types
if (dataType == "XMLTYPE")
{
return("XML");
}
if (dataType == "UNIQUEIDENTIFIER")
{
return("CHAR(16) FOR BIT DATA");
}
return(dataType);
}
private static string OtherDatabaseTypesToPostgreSql(string dataType, DatabaseColumn column)
{
//string types
//character(n) (aka char(n)) character varying(n) aka varchar(n) and text
if (DataTypeConverter.IsFixedLengthString(dataType))
{
return("CHAR");
}
if (DataTypeConverter.IsLongString(dataType))
{
return("TEXT");
}
if (DataTypeConverter.IsVariableString(dataType))
{
if (column.Length == -1)
{
return("TEXT");
}
return("VARCHAR");
}
//numeric types
if (dataType == "INT")
{
return("INTEGER");
}
if (dataType == "INT4")
{
return("INTEGER"); //this is a PostgreSql alias, we'll use standard SQL
}
//else if (dataType == "SERIAL") return "INTEGER"; //this is a PostgreSql alias, we'll use standard SQL
//else if (dataType == "BIGSERIAL") return "BIGINT"; //this is a PostgreSql alias, we'll use standard SQL
if (dataType == "INT8")
{
return("BIGINT"); //this is a PostgreSql alias, we'll use standard SQL
}
if (dataType == "INT2")
{
return("SMALLINT"); //this is a PostgreSql alias, we'll use standard SQL
}
if (dataType == "TINYINT")
{
return("SMALLINT"); //this is a MsSql alias, we'll use standard SQL
}
if (dataType == "NUMBER")
{
return(DataTypeConverter.OracleNumberConversion(column.Precision, column.Scale));
}
//float and real
if (dataType == "FLOAT4")
{
return("REAL"); //this is a PostgreSql alias, we'll use standard SQL
}
if (dataType == "FLOAT")
{
return("DOUBLE PRECISION");
}
//date times
//SqlServer Timestamp is a binary
if (DataTypeConverter.IsSqlServerTimestamp(dataType, column))
{
return("BYTEA"); //this is just a byte array- functionally you should redesign the table and perhaps use the system extension columns
}
if (DataTypeConverter.IsDateTime(dataType))
{
return("TIMESTAMP");
}
//bytes
if (DataTypeConverter.IsBlob(dataType, column))
{
return("OID");//blobs become object ids
}
if (DataTypeConverter.IsBinary(dataType))
{
return("BYTEA");
}
//there is a native BIT(n) type in Postgresql, but in conversion we probably mean boolean.
if (dataType == "BIT" && !column.Length.HasValue)
{
return("BOOLEAN");
}
//other types
if (dataType == "XMLTYPE")
{
return("XML");
}
if (dataType == "UNIQUEIDENTIFIER")
{
return("UUID");
}
return(dataType);
}
/// <summary>
/// Gets the MySql datatype definition as string
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
public string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
//we don't do column.DbDataTypeStandard() as native types will have length/precision-scale
//and also ints will have the UNSIGNED marker
//These types will fall through unchanged.
var dataType = column.DbDataType.ToUpperInvariant();
//int providerType = -1;
//if (column.DataType != null)
// providerType = column.DataType.ProviderDbType;
var precision = column.Precision;
var scale = column.Scale;
var length = column.Length;
//oracle to MySql translation
if (dataType == "NUMBER")
{
dataType = DataTypeConverter.OracleNumberConversion(precision, scale);
}
if (dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) &&
DataTypeConverter.IsSqlServerTimestamp(dataType, column))
{
dataType = "TINYBLOB"; //there's no equivalent really
}
if (dataType == "VARCHAR2" || dataType == "NVARCHAR" || dataType == "NVARCHAR2")
{
dataType = ConvertString(length);
}
else if (dataType == "CLOB" || dataType == "NTEXT")
{
dataType = "LONGTEXT";
}
else if (dataType == "NCHAR")
{
dataType = "CHAR";
}
else if (dataType == "DATETIME2" || dataType == "TIME")
{
dataType = "DATETIME";
}
else if (dataType == "MONEY")
{
dataType = "DECIMAL";
precision = 19;
scale = 4;
}
else if (dataType == "BIT")
{
dataType = "TINYINT";
}
else if (dataType == "IMAGE" || dataType == "VARBINARY")
{
dataType = ConvertBlob(length);
}
else if (dataType == "UNIQUEIDENTIFIER")
{
dataType = "VARCHAR";
length = 64;
}
else if (dataType == "XML" || dataType == "XMLTYPE")
{
dataType = "TEXT";
}
//write out MySql datatype definition
if (dataType == "VARCHAR" ||
dataType == "CHAR" ||
dataType == "BINARY" ||
dataType == "VARBINARY")
{
dataType = dataType + " (" + length + ")";
}
if (dataType == "NUMERIC" ||
dataType == "DECIMAL")
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
dataType = dataType + " (" + precision + writeScale + ")";
}
return(dataType);
}