/// <summary>
/// bit type in my sql could have a precision, specifying how much bits I can store
/// for example : bit(4) can store a value from (mysql syntax) b'0000' to b'1111' (so 0 to 15)
/// so if bit precision inf or eq to 1 so we have a sql server bit, otherwise, we have a binary data
/// </summary>
private static DmColumn MySql_Bit_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
var precision = mySqlColumn.Precision;
if (precision <= 1)
{
sqlColumn.DataType = typeof(Boolean);
sqlColumn.Precision = 1;
sqlColumn.MaxLength = 1;
sqlColumn.DbType = DbType.Boolean;
sqlColumn.OriginalDbType = "SqlDbType.Bit";
sqlColumn.OriginalTypeName = "bit";
return(sqlColumn);
}
// getting the ceiling value to get the binary length we need
var binaryLength = Convert.ToInt32(Math.Ceiling(precision / 8d));
sqlColumn.DataType = typeof(Byte[]);
sqlColumn.Precision = 0;
sqlColumn.MaxLength = binaryLength;
sqlColumn.DbType = DbType.Binary;
sqlColumn.OriginalDbType = "SqlDbType.Binary";
sqlColumn.OriginalTypeName = "binary";
return(sqlColumn);
}
/// <summary>
/// Convert a Sql column to MySql column
/// </summary>
private static DmColumn GetMySqlColumnFromSql(DmColumn sqlColumn)
{
var mySqlColumn = sqlColumn.Clone();
mySqlColumn.OriginalTypeName = "mysql";
// get the sql type (varchar, int, bigint and so on ..)
var oType = sqlColumn.OriginalTypeName.ToLowerInvariant();
if (oType == "int")
{
return(mySqlColumn);
}
if (oType == "bigint")
{
return(mySqlColumn);
}
if (oType == "smallint")
{
return(mySqlColumn);
}
if (oType == "tinyint")
{
// tinyint is unsigned in SQL Server
// mySql can store a tinyint to 255 if set as unsigned
mySqlColumn.IsUnsigned = true;
return(mySqlColumn);
}
return(mySqlColumn);
}
/// <summary>
/// Converts to int if [signed] else to bigint if [unsigned] && type is "int"
/// </summary>
private static DmColumn MySql_Int_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.Scale = 0;
sqlColumn.Precision = 10;
// if it's an int (not mediumint) check if unsigned
if (mySqlColumn.IsUnsigned && mySqlColumn.OriginalTypeName.ToLowerInvariant() != "mediumint")
{
sqlColumn.MaxLength = 8;
sqlColumn.DbType = DbType.Int64;
sqlColumn.OriginalDbType = "SqlDbType.BigInt";
sqlColumn.OriginalTypeName = "bigint";
}
else
{
sqlColumn.MaxLength = 4;
sqlColumn.DbType = DbType.Int32;
sqlColumn.OriginalDbType = "SqlDbType.Int";
sqlColumn.OriginalTypeName = "int";
}
return(sqlColumn);
}
/// <summary>
/// Convert to NVarchar (max if len > 4000)
/// </summary>
private static DmColumn MySql_Varchar_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
var maxlen = mySqlColumn.MaxLength;
// check length.
// Exception on 0 and go to NVachar(max) if > 4000
if (maxlen == 0)
{
maxlen = 1;
}
else if (maxlen > 4000)
{
maxlen = -1;
}
sqlColumn.MaxLength = maxlen;
sqlColumn.Precision = 0;
sqlColumn.Scale = 0;
sqlColumn.DbType = DbType.String;
sqlColumn.OriginalDbType = "SqlDbType.NVarchar";
sqlColumn.OriginalTypeName = "nvarchar";
return(sqlColumn);
}
private static DmColumn MySql_Date_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = 1;
sqlColumn.DbType = DbType.Date;
sqlColumn.OriginalDbType = "SqlDbType.Date";
sqlColumn.OriginalTypeName = "date";
return(sqlColumn);
}
/// <summary>
/// Binary is pretty the same. Just be careful a minimum Maxlength (1) is set
/// </summary>
private static DmColumn MySql_Binary_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.DbType = DbType.Binary;
sqlColumn.maxLength = Math.Min(mySqlColumn.MaxLength, 1);
sqlColumn.OriginalDbType = "SqlDbType.Binary";
sqlColumn.OriginalTypeName = "binary";
return(sqlColumn);
}
/// <summary>
/// Converts to Varbinary(Max)
/// </summary>
private static DmColumn MySql_Blob_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.DbType = DbType.Binary;
sqlColumn.maxLength = -1;
sqlColumn.OriginalDbType = "SqlDbType.VarBinary";
sqlColumn.OriginalTypeName = "varbinary";
return(sqlColumn);
}
/// <summary>
/// Bigint is the same. Just the max length (octet length) from Sql Server could be useful
/// Even if the MySql Bigint is unsigned, we get bigint
/// </summary>
private static DmColumn MySql_BigInt_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = 8;
sqlColumn.DbType = DbType.Int64;
sqlColumn.OriginalDbType = "SqlDbType.BigInt";
sqlColumn.OriginalTypeName = "bigint";
sqlColumn.IsUnsigned = false;
return(sqlColumn);
}
private static DmColumn MySql_TimeStamp_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = 8;
sqlColumn.Precision = 0;
sqlColumn.Scale = 0;
sqlColumn.DbType = DbType.UInt64;
sqlColumn.OriginalDbType = "SqlDbType.BigInt";
sqlColumn.OriginalTypeName = "bigint";
return(sqlColumn);
}
/// <summary>
/// Convert datetime to sql datetime2 (https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017)
/// </summary>
private static DmColumn MySql_Datetime_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = 8;
sqlColumn.Precision = 27;
sqlColumn.Scale = 7;
sqlColumn.DbType = DbType.DateTime2;
sqlColumn.OriginalDbType = "SqlDbType.DateTime2";
sqlColumn.OriginalTypeName = "datetime2";
return(sqlColumn);
}
private static DmColumn MySql_Decimal_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = -1;
sqlColumn.Precision = Math.Min((byte)38, mySqlColumn.Precision);
sqlColumn.Scale = Math.Min((byte)30, mySqlColumn.Scale);
sqlColumn.DbType = DbType.Decimal;
sqlColumn.OriginalDbType = "SqlDbType.Decimal";
sqlColumn.OriginalTypeName = "decimal";
return(sqlColumn);
}
private static DmColumn MySql_Text_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = -1;
sqlColumn.Precision = 0;
sqlColumn.Scale = 0;
sqlColumn.DbType = DbType.String;
sqlColumn.OriginalDbType = "SqlDbType.NVarchar";
sqlColumn.OriginalTypeName = "nvarchar";
return(sqlColumn);
}
/// <summary>
/// Converts to smallint
/// </summary>
private static DmColumn MySql_Tinyint_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = -1;
sqlColumn.Precision = 0;
sqlColumn.Scale = 0;
sqlColumn.DbType = DbType.Int16;
sqlColumn.OriginalDbType = "SqlDbType.SmallInt";
sqlColumn.OriginalTypeName = "smallint";
return(sqlColumn);
}
private static DmColumn ToSqliteInt(DmColumn mySqlColumn)
{
var column = mySqlColumn.Clone();
column.MaxLength = -1;
column.Scale = 0;
column.Precision = 0;
column.DbType = DbType.Int64;
column.OriginalDbType = "DbType.Int64";
column.OriginalTypeName = "integer";
return(column);
}
private static DmColumn ToSqliteBlob(DmColumn mySqlColumn)
{
var column = mySqlColumn.Clone();
column.MaxLength = -1;
column.Scale = 0;
column.Precision = 0;
column.DbType = DbType.Binary;
column.OriginalDbType = "DbType.Binary";
column.OriginalTypeName = "binary";
return(column);
}
private static DmColumn ToSqliteDate(DmColumn mySqlColumn)
{
var column = mySqlColumn.Clone();
column.MaxLength = -1;
column.Scale = 0;
column.Precision = 0;
column.DbType = DbType.DateTime;
column.OriginalDbType = "DbType.DateTime";
column.OriginalTypeName = "datetime";
return(column);
}
private static DmColumn ToSqliteText(DmColumn mySqlColumn)
{
var column = mySqlColumn.Clone();
// No need to precise max length for Sqlite
column.MaxLength = -1;
column.Scale = 0;
column.Precision = 0;
column.DbType = DbType.String;
column.OriginalDbType = "DbType.String";
column.OriginalTypeName = "text";
return(column);
}
private static DmColumn ToSqliteNumeric(DmColumn mySqlColumn)
{
var column = mySqlColumn.Clone();
column.MaxLength = -1;
// no need to specify scale and precision for Sqlite
column.Scale = 0;
column.Precision = 0;
column.DbType = DbType.VarNumeric;
column.OriginalDbType = "DbType.VarNumeric";
column.OriginalTypeName = "numeric";
return(column);
}
/// <summary>
/// Converts to unicode Sql server char (nchar)
/// </summary>
private static DmColumn MySql_Char_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
// a char(0) is possible in mysql
if (mySqlColumn.MaxLength == 0)
{
sqlColumn.MaxLength = 1;
}
else
{
sqlColumn.MaxLength = mySqlColumn.MaxLength >= 1 ? mySqlColumn.MaxLength : -1;
}
// set if it's a fixed size string
sqlColumn.DbType = sqlColumn.MaxLength > 0 ? DbType.StringFixedLength : DbType.String;
sqlColumn.OriginalDbType = "SqlDbType.NChar";
sqlColumn.OriginalTypeName = "nchar";
return(sqlColumn);
}
/// <summary>
/// if scale is sup to 0 then return numeric otherwise, return a float
/// </summary>
private static DmColumn MySql_Double_To_SqlServer(DmColumn mySqlColumn)
{
var sqlColumn = mySqlColumn.Clone();
sqlColumn.MaxLength = -1;
if (mySqlColumn.Scale > 0)
{
sqlColumn.DbType = DbType.VarNumeric;
sqlColumn.OriginalDbType = "SqlDbType.Numeric";
sqlColumn.OriginalTypeName = "numeric";
}
else
{
sqlColumn.DbType = DbType.Double;
// if we convert from double, the float will be 53 or if we convert from float it will be 24
sqlColumn.Precision = mySqlColumn.OriginalTypeName.ToLowerInvariant() == "double" ? (byte)53 : (byte)24;
sqlColumn.Scale = 0;
sqlColumn.OriginalDbType = "SqlDbType.Float";
sqlColumn.OriginalTypeName = "float";
}
return(sqlColumn);
}
/// <summary>
/// Converts to Varbinary(N)
/// </summary>
private static DmColumn MySql_VarBinary_To_SqlServer(DmColumn mySqlColumn)
{
var maxlen = mySqlColumn.MaxLength;
// check length.
// Exception on 0 and go to Varbinary(max) if > 8000
if (maxlen == 0)
{
maxlen = 1;
}
else if (maxlen > 8000)
{
maxlen = -1;
}
var sqlColumn = mySqlColumn.Clone();
sqlColumn.DbType = DbType.Binary;
sqlColumn.MaxLength = maxlen;
sqlColumn.OriginalDbType = "SqlDbType.VarBinary";
sqlColumn.OriginalTypeName = "varbinary";
return(sqlColumn);
}
