public MySqlDialect(MySqlDbDriver driver) : base(driver)
{
base.MaxParamCount = 32000; // no doc ref, just posting on forum: https://stackoverflow.com/questions/6581573
// MySql does not have syntax for offset-only, so we set limit to 10 million recs
base.OffsetTemplate = new SqlTemplate(" LIMIT 10000000 OFFSET {0} ");
base.OffsetLimitTemplate = new SqlTemplate(" LIMIT {1} OFFSET {0} ");
}
public MySqlTypeRegistry(MySqlDbDriver driver)
: base(driver)
{
//numerics
AddType("float", DbType.Single, typeof(Single), MySqlDbType.Float);
AddType("double", DbType.Double, typeof(Double), MySqlDbType.Double, aliases: "real");
AddType("decimal", DbType.Decimal, typeof(Decimal), MySqlDbType.Decimal, args: "({precision},{scale})", aliases: "numeric,fixed,dec");
//Just to handle props marked with DbType=DbType.Currency
AddType("decimal", DbType.Currency, typeof(Decimal), MySqlDbType.Decimal, args: "({precision},{scale})", isDefault: false, isSubType: true);
//integers
AddType("int", DbType.Int32, typeof(int), MySqlDbType.Int32, aliases: "integer");
AddType("int unsigned", DbType.UInt32, typeof(uint), MySqlDbType.UInt32);
AddType("tinyint", DbType.SByte, typeof(sbyte), MySqlDbType.Byte); //tinyint in MySql is signed byte - unlike in MSSQL
AddType("tinyint unsigned", DbType.Byte, typeof(byte), MySqlDbType.UByte);
AddType("smallint", DbType.Int16, typeof(Int16), MySqlDbType.Int16);
AddType("smallint unsigned", DbType.UInt16, typeof(UInt16), MySqlDbType.UInt16);
AddType("mediumint", DbType.Int16, typeof(Int32), MySqlDbType.Int24, isDefault: false);
AddType("bigint", DbType.Int64, typeof(Int64), MySqlDbType.Int64);
AddType("bigint unsigned", DbType.UInt64, typeof(UInt64), MySqlDbType.UInt64);
AddType("enum", DbType.Int16, typeof(Int16), MySqlDbType.Enum, isDefault: false);
AddType("set", DbType.UInt64, typeof(UInt64), MySqlDbType.Set, isDefault: false);
// Bool
AddType("bit", DbType.Boolean, typeof(ulong), MySqlDbType.Bit, isDefault: false, clrTypes: new Type[] { typeof(bool) });
// Strings
AddType("varchar", DbType.String, typeof(string), MySqlDbType.VarChar, args: "({size})", valueToLiteral: MySqlStringToLiteral);
AddType("char", DbType.StringFixedLength, typeof(string), MySqlDbType.String, args: "({size})",
isDefault: false, clrTypes: new Type[] { typeof(char) }, valueToLiteral: MySqlStringToLiteral);
AddType("tinytext", DbType.String, typeof(string), MySqlDbType.TinyText, isDefault: false, valueToLiteral: MySqlStringToLiteral);
AddType("mediumtext", DbType.String, typeof(string), MySqlDbType.MediumText, supportsMemo: true, isDefault: false, valueToLiteral: MySqlStringToLiteral);
AddType("text", DbType.String, typeof(string), MySqlDbType.Text, supportsMemo: true, isDefault: false, valueToLiteral: MySqlStringToLiteral);
AddType("longtext", DbType.String, typeof(string), MySqlDbType.LongText, supportsMemo: true, isDefault: false, valueToLiteral: MySqlStringToLiteral);
// Datetime
AddType("datetime", DbType.DateTime, typeof(DateTime), MySqlDbType.DateTime, valueToLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
AddType("date", DbType.Date, typeof(DateTime), MySqlDbType.Date, isDefault: false, valueToLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
AddType("time", DbType.Time, typeof(TimeSpan), MySqlDbType.Time, valueToLiteral: DbValueToLiteralConverters.TimeSpanToLiteralNoMs);
AddType("timestamp", DbType.Object, typeof(DateTime), MySqlDbType.Timestamp, isDefault: false);
AddType("year", DbType.Int16, typeof(Int16), MySqlDbType.Year, isDefault: false);
// Binaries
var binTypes = new Type[] { typeof(Vita.Common.Binary) };
AddType("varbinary", DbType.Binary, typeof(byte[]), MySqlDbType.VarBinary, args: "({size})", clrTypes: binTypes);
AddType("binary", DbType.Binary, typeof(byte[]), MySqlDbType.Binary, isDefault: false, args: "({size})", clrTypes: binTypes);
AddType("tinyblob", DbType.Binary, typeof(byte[]), MySqlDbType.TinyBlob, isDefault: false, clrTypes: binTypes);
AddType("blob", DbType.Binary, typeof(byte[]), MySqlDbType.Blob, supportsMemo: true, isDefault: false, clrTypes: binTypes);
AddType("mediumblob", DbType.Binary, typeof(byte[]), MySqlDbType.MediumBlob, supportsMemo: true, isDefault: false, clrTypes: binTypes);
AddType("longblob", DbType.Binary, typeof(byte[]), MySqlDbType.LongBlob, supportsMemo: true, isDefault: false, clrTypes: binTypes);
// Guid - specialized subtype binary(16)
AddType("binary", DbType.Binary, typeof(byte[]), MySqlDbType.Binary, args: "(16)", isDefault: true, isSubType: true,
clrTypes: new Type[] { typeof(Guid) }, dbFirstClrType: typeof(Guid), columnInit: "0");
// the following types should be treated as auto-memo - whenevery property specifies this type explicitly through [Column(TypeName=?)] spec,
// the column is set to Memo (unlimited size) - unless size is specified explicitly.
RegisterAutoMemoTypes("mediumtext", "text", "longtext", "blob", "longblob");
// bool is stored as UInt64
Converters.AddConverter<ulong, bool>(LongToBool, x => x); //this is for MySql bit type
}
public MySqlTypeRegistry(MySqlDbDriver driver) : base(driver)
{
//numerics
AddType("float", DbType.Single, typeof(Single), MySqlDbType.Float, aliases: "float unsigned");
AddType("double", DbType.Double, typeof(Double), MySqlDbType.Double, aliases: "real;double unsigned");
AddType("decimal", DbType.Decimal, typeof(Decimal), MySqlDbType.Decimal, args: "({precision},{scale})", aliases: "numeric,fixed,dec,decimal unsigned");
//Just to handle props marked with DbType=DbType.Currency
AddType("decimal", DbType.Currency, typeof(Decimal), MySqlDbType.Decimal, args: "({precision},{scale})", isDefault: false, isSubType: true);
//integers
AddType("int", DbType.Int32, typeof(int), MySqlDbType.Int32, aliases: "integer");
AddType("int unsigned", DbType.UInt32, typeof(uint), MySqlDbType.UInt32);
AddType("tinyint", DbType.SByte, typeof(sbyte), MySqlDbType.Byte); //tinyint in MySql is signed byte - unlike in MSSQL
AddType("tinyint unsigned", DbType.Byte, typeof(byte), MySqlDbType.UByte);
AddType("smallint", DbType.Int16, typeof(Int16), MySqlDbType.Int16);
AddType("smallint unsigned", DbType.UInt16, typeof(UInt16), MySqlDbType.UInt16);
AddType("mediumint", DbType.Int16, typeof(Int32), MySqlDbType.Int24, isDefault: false);
AddType("bigint", DbType.Int64, typeof(Int64), MySqlDbType.Int64);
AddType("bigint unsigned", DbType.UInt64, typeof(UInt64), MySqlDbType.UInt64);
AddType("enum", DbType.Int16, typeof(Int16), MySqlDbType.Enum, isDefault: false);
AddType("set", DbType.UInt64, typeof(UInt64), MySqlDbType.Set, isDefault: false);
// Bool
AddType("bit", DbType.Boolean, typeof(ulong), MySqlDbType.Bit, isDefault: false, clrTypes: new Type[] { typeof(bool) });
// Strings
AddType("varchar", DbType.String, typeof(string), MySqlDbType.VarChar, args: "({size})", valueToLiteral: MySqlStringToLiteral);
AddType("char", DbType.StringFixedLength, typeof(string), MySqlDbType.String, args: "({size})",
isDefault: false, clrTypes: new Type[] { typeof(char) }, valueToLiteral: MySqlStringToLiteral);
AddType("tinytext", DbType.String, typeof(string), MySqlDbType.TinyText, isDefault: false, valueToLiteral: MySqlStringToLiteral);
AddType("mediumtext", DbType.String, typeof(string), MySqlDbType.MediumText, supportsMemo: true, isDefault: false, valueToLiteral: MySqlStringToLiteral);
AddType("text", DbType.String, typeof(string), MySqlDbType.Text, supportsMemo: true, isDefault: false, valueToLiteral: MySqlStringToLiteral);
AddType("longtext", DbType.String, typeof(string), MySqlDbType.LongText, supportsMemo: true, isDefault: false, valueToLiteral: MySqlStringToLiteral);
// Datetime
AddType("datetime", DbType.DateTime, typeof(DateTime), MySqlDbType.DateTime, valueToLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
AddType("date", DbType.Date, typeof(DateTime), MySqlDbType.Date, isDefault: false, valueToLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
AddType("time", DbType.Time, typeof(TimeSpan), MySqlDbType.Time, valueToLiteral: DbValueToLiteralConverters.TimeSpanToLiteralNoMs);
AddType("timestamp", DbType.Object, typeof(DateTime), MySqlDbType.Timestamp, isDefault: false);
AddType("year", DbType.Int16, typeof(Int16), MySqlDbType.Year, isDefault: false);
// Binaries
var binTypes = new Type[] { typeof(Vita.Common.Binary) };
AddType("varbinary", DbType.Binary, typeof(byte[]), MySqlDbType.VarBinary, args: "({size})", clrTypes: binTypes);
AddType("binary", DbType.Binary, typeof(byte[]), MySqlDbType.Binary, isDefault: false, args: "({size})", clrTypes: binTypes);
AddType("tinyblob", DbType.Binary, typeof(byte[]), MySqlDbType.TinyBlob, isDefault: false, clrTypes: binTypes);
AddType("blob", DbType.Binary, typeof(byte[]), MySqlDbType.Blob, supportsMemo: true, isDefault: false, clrTypes: binTypes);
AddType("mediumblob", DbType.Binary, typeof(byte[]), MySqlDbType.MediumBlob, supportsMemo: true, isDefault: false, clrTypes: binTypes);
AddType("longblob", DbType.Binary, typeof(byte[]), MySqlDbType.LongBlob, supportsMemo: true, isDefault: false, clrTypes: binTypes);
// Guid - specialized subtype binary(16)
AddType("binary", DbType.Binary, typeof(byte[]), MySqlDbType.Binary, args: "(16)", isDefault: true, isSubType: true,
clrTypes: new Type[] { typeof(Guid) }, dbFirstClrType: typeof(Guid), columnInit: "0");
// the following types should be treated as auto-memo - whenevery property specifies this type explicitly through [Column(TypeName=?)] spec,
// the column is set to Memo (unlimited size) - unless size is specified explicitly.
RegisterAutoMemoTypes("mediumtext", "text", "longtext", "blob", "longblob");
// bool is stored as UInt64
Converters.AddConverter <ulong, bool>(LongToBool, x => x); //this is for MySql bit type
}
public MySqlTypeRegistry(MySqlDbDriver driver) : base(driver)
{
AddDbTypeDef("float", typeof(Single), aliases: "float unsigned");
AddDbTypeDef("double", typeof(Double), aliases: "real;double unsigned");
AddDbTypeDef("decimal", typeof(Decimal), DbTypeFlags.PrecisionScale, aliases: "numeric,fixed,dec,decimal unsigned");
//integers
AddDbTypeDef("int", typeof(int), aliases: "integer");
AddDbTypeDef("int unsigned", typeof(uint));
AddDbTypeDef("tinyint", typeof(sbyte)); //tinyint in MySql is signed byte - unlike in MSSQL
AddDbTypeDef("tinyint unsigned", typeof(byte));
AddDbTypeDef("smallint", typeof(Int16));
AddDbTypeDef("smallint unsigned", typeof(UInt16));
AddDbTypeDef("mediumint", typeof(Int32), mapColumnType: false);
AddDbTypeDef("bigint", typeof(Int64));
AddDbTypeDef("bigint unsigned", typeof(UInt64));
AddDbTypeDef("enum", typeof(Int16), mapColumnType: false);
AddDbTypeDef("set", typeof(UInt64), mapColumnType: false);
// Bool
var bitTd = AddDbTypeDef("bit", typeof(ulong), mapColumnType: false);
Map(typeof(bool), bitTd);
// Strings
var tdVarChar = AddDbTypeDef("varchar", typeof(string), DbTypeFlags.Size, toLiteral: MySqlStringToLiteral);
base.SpecialTypeDefs[DbSpecialType.String] = base.SpecialTypeDefs[DbSpecialType.StringAnsi] = tdVarChar;
var tdChar = AddDbTypeDef("char", typeof(string), DbTypeFlags.Size, mapColumnType: false, toLiteral: MySqlStringToLiteral);
Map(typeof(char), tdChar, size: 1);
AddDbTypeDef("tinytext", typeof(string), mapColumnType: false, toLiteral: MySqlStringToLiteral);
AddDbTypeDef("mediumtext", typeof(string), flags: DbTypeFlags.Unlimited, mapColumnType: false,
toLiteral: MySqlStringToLiteral);
AddDbTypeDef("text", typeof(string), flags: DbTypeFlags.Unlimited, mapColumnType: false, toLiteral: MySqlStringToLiteral);
// this maps to unlimited string
var tdLText = AddDbTypeDef("longtext", typeof(string), flags: DbTypeFlags.Unlimited, toLiteral: MySqlStringToLiteral);
base.SpecialTypeDefs[DbSpecialType.StringUnlimited] = base.SpecialTypeDefs[DbSpecialType.StringAnsiUnlimited] = tdLText;
// Datetime
AddDbTypeDef("datetime", typeof(DateTime), toLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
AddDbTypeDef("date", typeof(DateTime), mapColumnType: false, toLiteral: DbValueToLiteralConverters.DateTimeToLiteralNoMs);
AddDbTypeDef("time", typeof(TimeSpan), toLiteral: DbValueToLiteralConverters.TimeSpanToLiteralNoMs);
AddDbTypeDef("timestamp", typeof(DateTime), mapColumnType: false);
AddDbTypeDef("year", typeof(Int16), mapColumnType: false);
// Binaries
AddDbTypeDef("varbinary", typeof(byte[]), DbTypeFlags.Size, specialType: DbSpecialType.Binary);
var tdBin = AddDbTypeDef("binary", typeof(byte[]), DbTypeFlags.Size, mapColumnType: false);
AddDbTypeDef("tinyblob", typeof(byte[]), mapColumnType: false);
AddDbTypeDef("blob", typeof(byte[]), flags: DbTypeFlags.Unlimited, mapColumnType: false);
AddDbTypeDef("mediumblob", typeof(byte[]), flags: DbTypeFlags.Unlimited, mapColumnType: false);
// serves as unlimited binary
AddDbTypeDef("longblob", typeof(byte[]), flags: DbTypeFlags.Unlimited, columnInit: "0",
specialType: DbSpecialType.BinaryUnlimited);
// Guid - specialized subtype binary(16)
Map(typeof(Guid), tdBin, size: 16);
// bool is stored as UInt64
Converters.AddConverter <ulong, bool>(LongToBool, x => x); //this is for MySql bit type
}