public PgSqlDialect(PgDbDriver driver) : base(driver)
{
base.MaxParamCount = 32000; //reported also 65K
base.OffsetTemplate = new SqlTemplate(" OFFSET {0} ");
base.OffsetLimitTemplate = new SqlTemplate(" LIMIT {1} OFFSET {0} ");
base.DynamicSqlParameterPrefix = "@P";
base.BatchBeginTransaction = new TextSqlFragment("START TRANSACTION;");
base.BatchCommitTransaction = new TextSqlFragment("COMMIT;");
// we are using ANY instead of IN, works for parameters and list literals
base.SqlCrudTemplateDeleteMany = new SqlTemplate(
@"DELETE FROM {0}
WHERE {1} = ANY({2})");
}
public PgTypeRegistry(PgDbDriver driver) : base(driver)
{
AddType("real", DbType.Single, typeof(Single), NpgsqlDbType.Real);
AddType("double precision", DbType.Double, typeof(Double), NpgsqlDbType.Double);
AddType("numeric", DbType.Decimal, typeof(Decimal), NpgsqlDbType.Numeric, args: "({precision},{scale})");
//Just to handle props marked with DbType=DbType.Currency
AddType("money", DbType.Currency, typeof(Decimal), NpgsqlDbType.Money, isDefault: false, valueToLiteral: NumToLiteralWithCast);
//integers
// Note: We have to provide explicit cast for certain int and currency types; otherwise, function overload resolution does not match the intended function
// (it works by function name and arg types match). Quite annoying.
AddType("int", DbType.Int32, typeof(int), NpgsqlDbType.Integer, aliases: "integer,oid", clrTypes: new Type[] { typeof(UInt16) });
AddType("smallint", DbType.Int16, typeof(Int16), NpgsqlDbType.Smallint, clrTypes: new Type[] { typeof(byte), typeof(sbyte) }, valueToLiteral: NumToLiteralWithCast);
AddType("bigint", DbType.Int64, typeof(Int64), NpgsqlDbType.Bigint, clrTypes: new Type[] { typeof(UInt32), typeof(UInt64) }, valueToLiteral: NumToLiteralWithCast);
//AddType("serial", DbType.Int32, typeof(Int32), NpgsqlDbType ??);
// Bool
AddType("boolean", DbType.Boolean, typeof(bool), NpgsqlDbType.Boolean);
//Bit
//TODO: finish Bit data types : http://www.postgresql.org/docs/8.4/static/datatype-bit.html
// Strings
AddType("character varying", DbType.String, typeof(string), NpgsqlDbType.Varchar, args: "({size})");
AddType("character", DbType.StringFixedLength, typeof(string), NpgsqlDbType.Char, args: "({size})",
isDefault: false, clrTypes: new Type[] { typeof(char) }, aliases: "char");
AddType("text", DbType.String, typeof(string), NpgsqlDbType.Text, supportsMemo: true, isDefault: false);
// Datetime
AddType("timestamp without time zone", DbType.DateTime, typeof(DateTime), NpgsqlDbType.Timestamp);
AddType("timestamp with time zone", DbType.Date, typeof(DateTimeOffset), NpgsqlDbType.TimestampTZ);
AddType("date", DbType.Date, typeof(DateTime), NpgsqlDbType.Date, isDefault: false, aliases: "date without time zone");
AddType("time", DbType.Time, typeof(TimeSpan), NpgsqlDbType.Time, aliases: "time without time zone");
AddType("interval", DbType.Object, typeof(TimeSpan), NpgsqlDbType.Interval, isDefault: false);
// Binaries
// Note: Postgres has special way of presenting binary blobs, so we provide special BytesToLiteral converter
var binTypes = new Type[] { typeof(Vita.Common.Binary) };
AddType("bytea", DbType.Binary, typeof(byte[]), NpgsqlDbType.Bytea, supportsMemo: true, clrTypes: binTypes, valueToLiteral: BytesToLiteral);
AddType("array", DbType.Binary, typeof(byte[]), NpgsqlDbType.Array, isDefault: false, clrTypes: binTypes, valueToLiteral: BytesToLiteral);
// Guid
AddType("uuid", DbType.Guid, typeof(Guid), NpgsqlDbType.Uuid);
}
public PgTypeRegistry(PgDbDriver driver)
: base(driver)
{
AddType("real", DbType.Single, typeof(Single), NpgsqlDbType.Real);
AddType("double precision", DbType.Double, typeof(Double), NpgsqlDbType.Double);
AddType("numeric", DbType.Decimal, typeof(Decimal), NpgsqlDbType.Numeric, args: "({precision},{scale})");
//Just to handle props marked with DbType=DbType.Currency
AddType("money", DbType.Currency, typeof(Decimal), NpgsqlDbType.Money, isDefault: false, valueToLiteral: NumToLiteralWithCast);
//integers
// Note: We have to provide explicit cast for certain int and currency types; otherwise, function overload resolution does not match the intended function
// (it works by function name and arg types match). Quite annoying.
AddType("int", DbType.Int32, typeof(int), NpgsqlDbType.Integer, aliases: "integer", clrTypes: new Type[] { typeof(UInt16) });
AddType("smallint", DbType.Int16, typeof(Int16), NpgsqlDbType.Smallint, clrTypes: new Type[] { typeof(byte), typeof(sbyte) }, valueToLiteral: NumToLiteralWithCast);
AddType("bigint", DbType.Int64, typeof(Int64), NpgsqlDbType.Bigint, clrTypes: new Type[] { typeof(UInt32), typeof(UInt64) }, valueToLiteral: NumToLiteralWithCast);
//AddType("serial", DbType.Int32, typeof(Int32), NpgsqlDbType ??);
// Bool
AddType("boolean", DbType.Boolean, typeof(bool), NpgsqlDbType.Boolean);
//Bit
//TODO: finish Bit data types : http://www.postgresql.org/docs/8.4/static/datatype-bit.html
// Strings
AddType("character varying", DbType.String, typeof(string), NpgsqlDbType.Varchar, args: "({size})");
AddType("character", DbType.StringFixedLength, typeof(string), NpgsqlDbType.Char, args: "({size})",
isDefault: false, clrTypes: new Type[] { typeof(char) }, aliases: "char");
AddType("text", DbType.String, typeof(string), NpgsqlDbType.Text, supportsMemo: true, isDefault: false);
// Datetime
AddType("timestamp without time zone", DbType.DateTime, typeof(DateTime), NpgsqlDbType.Timestamp);
AddType("timestamp with time zone", DbType.Date, typeof(DateTimeOffset), NpgsqlDbType.TimestampTZ);
AddType("date", DbType.Date, typeof(DateTime), NpgsqlDbType.Date, isDefault: false, aliases: "date without time zone");
AddType("time", DbType.Time, typeof(TimeSpan), NpgsqlDbType.Time, aliases: "time without time zone");
AddType("interval", DbType.Object, typeof(TimeSpan), NpgsqlDbType.Interval, isDefault: false);
// Binaries
// Note: Postgres has special way of presenting binary blobs, so we provide special BytesToLiteral converter
var binTypes = new Type[] { typeof(Vita.Common.Binary) };
AddType("bytea", DbType.Binary, typeof(byte[]), NpgsqlDbType.Bytea, supportsMemo: true, clrTypes: binTypes, valueToLiteral: BytesToLiteral);
AddType("array", DbType.Binary, typeof(byte[]), NpgsqlDbType.Array, isDefault: false, clrTypes: binTypes, valueToLiteral: BytesToLiteral);
// Guid
AddType("uuid", DbType.Guid, typeof(Guid), NpgsqlDbType.Uuid);
}
public PgTypeRegistry(PgDbDriver driver) : base(driver)
{
var none = new Type[] { };
AddDbTypeDef("real", typeof(Single), aliases: "float4", providerDbType: NpgsqlDbType.Real);
AddDbTypeDef("double precision", typeof(Double), aliases: "float8", providerDbType: NpgsqlDbType.Double);
AddDbTypeDef("numeric", typeof(Decimal), DbTypeFlags.PrecisionScale, providerDbType: NpgsqlDbType.Numeric);
//Just to handle props marked with DbType=DbType.Currency
AddDbTypeDef("money", typeof(Decimal), mapColumnType: false, providerDbType: NpgsqlDbType.Money,
toLiteral: CreateFuncNumToLiteralWithCast("money"));
//integers
var tdInt = AddDbTypeDef("int", typeof(Int32), aliases: "integer,oid,int4", providerDbType: NpgsqlDbType.Integer);
Map(typeof(UInt16), tdInt);
var tdSmallInt = AddDbTypeDef("smallint", typeof(Int16), providerDbType: NpgsqlDbType.Smallint,
toLiteral: CreateFuncNumToLiteralWithCast("smallint"));
Map(typeof(byte), tdSmallInt);
Map(typeof(sbyte), tdSmallInt);
var tdBigInt = AddDbTypeDef("bigint", typeof(Int64), aliases: "int8", providerDbType: NpgsqlDbType.Bigint,
toLiteral: CreateFuncNumToLiteralWithCast("bigint"));
Map(typeof(UInt32), tdBigInt);
Map(typeof(UInt64), tdBigInt);
// Bool
AddDbTypeDef("boolean", typeof(bool), providerDbType: NpgsqlDbType.Boolean);
//Bit
//TODO: finish Bit data types : http://www.postgresql.org/docs/8.4/static/datatype-bit.html
// Strings
var tdVarChar = AddDbTypeDef("character varying", typeof(string), DbTypeFlags.Size,
aliases: "varchar", providerDbType: NpgsqlDbType.Varchar);
SpecialTypeDefs[DbSpecialType.String] = tdVarChar;
SpecialTypeDefs[DbSpecialType.StringAnsi] = tdVarChar;
var tdChar = AddDbTypeDef("character", typeof(string), DbTypeFlags.Size, providerDbType: NpgsqlDbType.Char);
Map(typeof(char), tdChar, size: 1);
var tdText = AddDbTypeDef("text", typeof(string), flags: DbTypeFlags.Unlimited, providerDbType: NpgsqlDbType.Text);
SpecialTypeDefs[DbSpecialType.StringUnlimited] = tdText;
SpecialTypeDefs[DbSpecialType.StringAnsiUnlimited] = tdText;
// Datetime
AddDbTypeDef("timestamp without time zone", typeof(DateTime), providerDbType: NpgsqlDbType.Timestamp);
AddDbTypeDef("timestamp with time zone", typeof(DateTimeOffset), providerDbType: NpgsqlDbType.TimestampTz);
AddDbTypeDef("date without time zone", typeof(DateTime), mapColumnType: false, providerDbType: NpgsqlDbType.Date);
AddDbTypeDef("time without time zone", typeof(TimeSpan), providerDbType: NpgsqlDbType.TimeTz);
AddDbTypeDef("interval", typeof(TimeSpan), mapColumnType: false, providerDbType: NpgsqlDbType.Interval);
// Binaries
// Note: Postgres has special way of presenting binary blobs, so we provide special BytesToLiteral converter
var tdByteA = AddDbTypeDef("bytea", typeof(byte[]), mapColumnType: false, providerDbType: NpgsqlDbType.Bytea,
toLiteral: BytesToLiteral);
Map(typeof(Binary), tdByteA);
// Guid
AddDbTypeDef("uuid", typeof(Guid), toLiteral: GuidToLiteral, providerDbType: NpgsqlDbType.Uuid);
}
