public string WriteDataType(DatabaseColumn column) { if (column == null) return string.Empty; if (string.IsNullOrEmpty(column.DbDataType)) return string.Empty; var dataType = column.DbDataTypeStandard(); dataType = OtherDatabaseTypesToPostgreSql(dataType, column); if ((dataType.StartsWith("TIMESTAMP", StringComparison.OrdinalIgnoreCase) || dataType == "TIME") && column.DateTimePrecision > 0) dataType = dataType + " (" + column.DateTimePrecision + ")"; //write out datatype definition if ((dataType == "VARCHAR" || dataType == "CHAR") && 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) //Postgresql can have no specified precision dataType = dataType + " (" + precision + writeScale + ")"; } 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) { //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; }
/// <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) { 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 == "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) 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; 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 TIMESTAMP '" + 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 " + AddQuotedDefault(defaultValue); } 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; }