protected override DbTypeInfo CreateDbTypeInfo(Type clrType, DbTypeDef typeDef, long size = 0, byte prec = 0, byte scale = 0)
{
var typeInfo = base.CreateDbTypeInfo(clrType, typeDef, size, prec, scale);
// Big trouble - Oracle provider returns different value types in numeric columns with scale ==0 (integers)
// depending on precision value
if (typeDef == NumericTypeDef)
{
if (typeInfo.Scale == 0)
{
if (typeInfo.Precision <= 4)
{
typeInfo.ColumnOutType = typeof(Int16);
typeInfo.ColumnReader = (rec, i) => rec.GetInt16(i);
}
else if (typeInfo.Precision <= 9)
{
typeInfo.ColumnOutType = typeof(Int32);
typeInfo.ColumnReader = (rec, i) => rec.GetInt32(i);
}
else if (typeInfo.Precision <= 18)
{
typeInfo.ColumnOutType = typeof(Int64);
typeInfo.ColumnReader = (rec, i) => rec.GetInt64(i);
}
else
{
typeInfo.ColumnOutType = typeof(decimal);
typeInfo.ColumnReader = (rec, i) => rec.GetDecimal(i);
}
} // if mapping.Scale == 0
} // if storageType ==
return(typeInfo);
}
public SqlListParamPlaceHolder(Type elementType, DbTypeDef elemTypeDef, Func <object[], object> valueReader,
Func <object, string> formatLiteral, Func <IDbDataParameter, string> formatParameter = null)
{
ElementType = elementType;
ElementTypeDef = elemTypeDef;
ListValueReader = valueReader;
base.FormatLiteral = formatLiteral;
base.FormatParameter = formatParameter ?? (p => p.ParameterName);
}
public override string FormatTypeSpec(DbTypeDef typeDef, long size = 0, byte prec = 0, byte scale = 0)
{
if (typeDef.Name.StartsWith(PrefixIntervalDay))
{
return($"interval day({prec}) to second({scale})");
}
if (typeDef.Name.StartsWith(PrefixIntervalDay))
{
return($"interval year({prec}) to month({scale})");
}
return(base.FormatTypeSpec(typeDef, size, prec, scale));
}
public SQLiteTypeRegistry(SQLiteDbDriver driver) : base(driver)
{
// For all int types SQLite provider returns Int64, not sure about actual storage; so we set ColOutType to Int64 here for Int32 type
var tdInt = AddDbTypeDef("int", typeof(Int32), mapColumnType: false, aliases: "integer");
MapMany(new[] { typeof(byte), typeof(sbyte), typeof(Int16), typeof(UInt16), typeof(Int32), typeof(UInt32) }, tdInt);
IntTypeDef = tdInt;
var tdInt64 = AddDbTypeDef("int64", typeof(Int64));
MapMany(new[] { typeof(Int64), typeof(UInt64) }, tdInt64);
Int64TypeDef = tdInt64;
var tdReal = AddDbTypeDef("real", typeof(double));
MapMany(new[] { typeof(Single), typeof(double), typeof(decimal) }, tdReal);
var tdText = AddDbTypeDef("text", typeof(string));
MapMany(new[] { typeof(string), typeof(char) }, tdText);
SpecialTypeDefs[DbSpecialType.String] = tdText;
SpecialTypeDefs[DbSpecialType.StringAnsi] = tdText;
SpecialTypeDefs[DbSpecialType.StringUnlimited] = tdText;
SpecialTypeDefs[DbSpecialType.StringAnsiUnlimited] = tdText;
var tdBlob = AddDbTypeDef("blob", typeof(byte[]), toLiteral: BytesToLiteral);
MapMany(new Type[] { typeof(Guid), typeof(Vita.Entities.Binary), typeof(byte[]) }, tdBlob);
SpecialTypeDefs[DbSpecialType.Binary] = tdBlob;
SpecialTypeDefs[DbSpecialType.BinaryUnlimited] = tdBlob;
// SQLite data provider can automatically handle 'date' values, if there's 'date' column association
// it returns DateTime values from these columns. But in this case it cuts off milliseconds.
// So we store dates/time as strings and handle conversion in code; DbReader still sometimes returns value as DateTime -
// we handle it in code.
// Important - we need to to set type name (affinity) to smth special, not just 'date' - otherwise provider recognizes
// it and starts converting dates in DbReader and this blows up. Did not find any reliable way to disable this behavior
var tdDate = AddDbTypeDef("date", typeof(string), mapColumnType: false, toLiteral: DateTimeToLiteral);
Map(typeof(DateTime), tdDate);
var tdTime = AddDbTypeDef("time", typeof(string), mapColumnType: false, toLiteral: TimeSpanToLiteral);
Map(typeof(TimeSpan), tdTime);
var tdBool = AddDbTypeDef("bool", typeof(Int32), mapColumnType: false, toLiteral: BoolToBitLiteral);
Map(typeof(bool), tdBool);
Converters.AddConverter <string, TimeSpan>(ParseTimeSpan, TimeSpanToString);
Converters.AddConverter <Int32, bool>(IntToBool, x => (bool)x ? 1 : 0);
Converters.AddConverter <string, DateTime>(ParseDateTime, DateTimeToString);
}
public virtual string ListToLiteral(object value, DbTypeDef elemTypeDef)
{
var list = value as IList;
if (list.Count == 0)
{
return(GetEmptyListLiteral(elemTypeDef));
}
var strList = new List <string>(list.Count);
foreach (var item in list)
{
strList.Add(elemTypeDef.ToLiteral(item));
}
return(string.Join(", ", strList));
}
/*
* We are using Any() function for in-array operator (instead of standard SQL's '<value> IN <list>)
* Any() works both for parameter (as list value) or list literal. But list literal must be formatted using
* ARRAY[..] method. Example of correct query with literal:
*
* Select *
* from "books"."Book"
* WHERE "Title" = Any(ARRAY['IronMan', 'Windows Programming'])
* or "Category" = Any(ARRAY[0,1])
* OR "Category" = Any('{}') -- empty array
*
*/
public override string ListToLiteral(object value, DbTypeDef elemTypeDef)
{
var list = value as IList;
if (list.Count == 0)
{
return("'{}'"); // GetEmptyListLiteral(elemTypeDef);
}
var strList = new List <string>(list.Count);
foreach (var item in list)
{
strList.Add(elemTypeDef.ToLiteral(item));
}
return("ARRAY[" + string.Join(", ", strList) + "]");
}
// Used in 't.Col IN (<list>)' expressions when list is empty; does not work for all providers
public override string GetEmptyListLiteral(DbTypeDef elemTypeDef)
{
return("SELECT NULL FROM dual WHERE 1=0");
}
// Used in 't.Col IN (<list>)' expressions when list is empty; does not work for all providers
public virtual string GetEmptyListLiteral(DbTypeDef elemTypeDef)
{
return("SELECT NULL WHERE 1=0"); //works OK for MS SQL, SQLite
}
