/// <summary>
/// Gets the Oracle datatype definition as string
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
public static string OracleDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var dataType = column.DbDataTypeStandard();
int providerType = GetProviderType(column);
var precision = column.Precision;
var scale = column.Scale;
var length = column.Length;
dataType = PostgreSqlToSqlServerConversion(dataType);
//oracle to sql server translation
dataType = SqlServerToOracleConversion(dataType, providerType, length);
if (dataType == "NUMERIC" || dataType == "DECIMAL")
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
dataType = dataType + " (" + precision + writeScale + ")";
}
return(dataType);
}
public string WriteDataType(DatabaseColumn column)
{
//sqlite is not strongly typed, and the type affinities (http://www.sqlite.org/datatype3.html) are very limited
// (text, integer, real, blob)
//the ado provider uses the column types for richer support
//ado mapping http://sqlite.phxsoftware.com/forums/t/31.aspx
if (column == null)
{
return(string.Empty);
}
var dt = column.DataType;
if (dt != null)
{
if (dt.IsString)
{
return("TEXT");
}
if (dt.IsInt)
{
return("INTEGER");
}
if (dt.IsNumeric)
{
return("NUMERIC"); //Integer or Real
}
if (dt.IsFloat)
{
return("REAL");
}
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var dataType = column.DbDataTypeStandard();
if (dataType == "IMAGE" || dataType.IndexOf("BINARY", StringComparison.OrdinalIgnoreCase) != -1)
{
return("BLOB");
}
if (dataType == "BIT")
{
return("INTEGER"); // boolean = 0 or 1
}
if (dataType == "DATE" || dataType == "DATETIME")
{
//a hint to the ado provider
return("DATETIME");
}
return(dataType);
}
public static string OracleDataTypeForParameter(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var dataType = column.DbDataTypeStandard();
int providerType = GetProviderType(column);
var length = column.Length;
//oracle to sql server translation
dataType = SqlServerToOracleConversion(dataType, providerType, length);
return(dataType);
}
public string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var dataType = column.DbDataTypeStandard();
dataType = OtherDatabaseTypesToDb2(dataType, column);
if ((dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) || dataType == "TIME") &&
column.DateTimePrecision > 0)
{
dataType = dataType + " (" + column.DateTimePrecision + ")";
}
//write out datatype definition
if (IsString(column, dataType) && column.Length > 0)
{
dataType = dataType + " (" + column.Length + ")";
}
if (dataType == "NUMERIC" || dataType == "DECIMAL")
{
var scale = column.Scale;
var precision = column.Precision;
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
if (precision > 0)
{
dataType = dataType + " (" + precision + writeScale + ")";
}
}
return(dataType);
}
public string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var sql = string.Empty;
var dataType = column.DbDataTypeStandard();
var precision = column.Precision;
var scale = column.Scale;
var length = column.Length;
if (dataType == "BOOLEAN")
{
dataType = "NUMBER";
precision = 1;
scale = 0;
}
//sql server to oracle translation
dataType = SqlServerToOracleConversion(dataType, GetProviderType(column), length);
if (dataType == "UNIQUEIDENTIFIER")
{
dataType = "RAW";
length = 16;
}
if (dataType == "NUMERIC")
{
dataType = "NUMBER";
}
if (dataType == "INT")
{
dataType = "NUMBER";
precision = 9;
scale = 0;
}
if (dataType == "BIGINT")
{
dataType = "NUMBER";
precision = 19;
scale = 0;
}
if (dataType == "SMALLINT")
{
dataType = "NUMBER";
precision = 5;
scale = 0;
}
if (dataType == "BIT")
{
dataType = "NUMBER";
precision = 1;
scale = 0;
}
if (dataType == "DECIMAL")
{
dataType = "NUMBER";
precision = 18;
scale = 0;
}
if (dataType == "MONEY")
{
dataType = "NUMBER";
precision = 15;
scale = 4;
}
string defaultValue = FixDefaultValue(column);
//write out Oracle datatype definition
if (dataType == "NVARCHAR2")
{
if (length == -1)
{
dataType = "CLOB";
}
else
{
//don't specify "CHAR" for NVARCHAR2
sql = dataType + " (" + length + ")";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
}
if (dataType == "VARCHAR2")
{
//assume it's CHAR rather than bytes
sql = dataType + " (" + length + " CHAR)";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (dataType == "CHAR" || dataType == "NCHAR")
{
sql = dataType + " (" + length + ")";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (dataType == "NUMBER")
{
if (!precision.HasValue)
{
sql = "NUMBER";
}
else
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
sql = "NUMBER (" + precision + writeScale + ")";
}
if (!column.Nullable && (string.IsNullOrEmpty(defaultValue) || column.IdentityDefinition != null))
{
sql += " NOT NULL";
}
if (column.IdentityDefinition != null)
{
if (column.DatabaseSchema == null ||
column.DatabaseSchema.Provider.IndexOf("Oracle", StringComparison.OrdinalIgnoreCase) == -1)
{
//this doesn't look like oracle, so we're converting.
return(sql);
}
//Oracle 12c- this can be set.
//For Oracle 11, IsAutoNumber can be set (by recognizing a sequence), but the definition won't be set.
//these must be NUMBER of some sort or it's invalid
sql += " GENERATED ";
if (column.IdentityDefinition.IdentityByDefault)
{
sql += "BY DEFAULT ";
}
sql += "AS IDENTITY";
if (column.IdentityDefinition.IsNonTrivialIdentity())
{
sql += string.Format(CultureInfo.InvariantCulture, " (START WITH {0} INCREMENT BY {1})",
column.IdentityDefinition.IdentitySeed,
column.IdentityDefinition.IdentityIncrement);
}
//no other options should be done
return(sql);
}
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue;
if (!column.Nullable)
{
sql += " NOT NULL";
}
}
return(sql);
}
if (dataType == "REAL")
{
sql = "REAL";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue;
}
}
if (dataType == "RAW")
{
sql = "RAW(" + length + ")";
}
if (dataType == "XMLTYPE")
{
sql = dataType;
}
if (dataType == "DATE")
{
sql = "DATE";
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT DATE '" + defaultValue + "'";
}
}
if (dataType == "TIMESTAMP")
{
sql = "TIMESTAMP" + (precision.HasValue ? " (" + precision + ")" : " (6)");
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue;
}
}
if (dataType == "TIMESTAMP WITH TIME ZONE")
{
sql = string.Format("TIMESTAMP ({0}) WITH TIME ZONE", precision.HasValue ? precision : 6);
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue;
}
}
if (dataType == "CLOB" || dataType == "NCLOB")
{
sql = dataType;
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
if (dataType == "BLOB")
{
sql = dataType;
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + defaultValue; //not quoted
}
}
if (column.IsComputed)
{
sql = "GENERATED ALWAYS AS (" + column.ComputedDefinition + ") VIRTUAL";
}
if (string.IsNullOrEmpty(sql))
{
sql = column.DbDataType;
if (!string.IsNullOrEmpty(defaultValue))
{
sql += " DEFAULT " + AddQuotedDefault(defaultValue);
}
}
return(sql.TrimEnd() + (!column.Nullable ? " NOT NULL" : string.Empty));
}
/// <summary>
/// Gets the SQLServer datatype definition as string
/// </summary>
/// <param name="column">The column.</param>
/// <returns></returns>
public virtual string WriteDataType(DatabaseColumn column)
{
if (column == null)
{
return(string.Empty);
}
if (string.IsNullOrEmpty(column.DbDataType))
{
return(string.Empty);
}
var dataType = column.DbDataTypeStandard();
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 sql server translation
if (dataType == "BLOB")
{
dataType = "VARBINARY";
length = -1;
}
if (dataType == "CLOB")
{
dataType = "NVARCHAR";
length = -1;
}
//In SqlServer, the maximum length allowed for any data type is 8000.
//Ergo, TEXTs and NTEXTs that are larger (int.MaxValue or int.MaxValue/2) must be SqlServer types
if (dataType == "NTEXT" && length > 8000)
{
return("NTEXT");
}
if (dataType == "TEXT" && length > 8000)
{
return("TEXT");
}
if (_originSqlType == SqlType.SqlServer || _originSqlType == SqlType.SqlServerCe)
{
if (dataType == "BINARY")
{
//should not be varbinary
return(WriteDataTypeWithLength(dataType, length));
}
}
else
{
dataType = ConvertOtherPlatformTypes(dataType, providerType, length, precision, scale);
}
if ((dataType == "DATETIME2" || dataType == "TIME") && column.DateTimePrecision.HasValue)
{
dataType = dataType + "(" + column.DateTimePrecision + ")";
}
//write out SqlServer datatype definition
if (dataType == "NVARCHAR" ||
dataType == "VARCHAR" ||
dataType == "CHAR" ||
dataType == "NCHAR" ||
dataType == "BINARY" ||
dataType == "VARBINARY")
{
dataType = WriteDataTypeWithLength(dataType, length);
}
if (dataType == "NUMERIC" ||
dataType == "DECIMAL")
{
if (precision != null)
{
var writeScale = ((scale != null) && (scale > 0) ? "," + scale : "");
dataType = dataType + " (" + precision + writeScale + ")";
}
}
return(dataType);
}
