public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks, IBlobSerializer serializer,
IDictionary<Type, string> extraTypeMappings)
{
string decl = "\"" + p.Name + "\" " + SqlType(p, storeDateTimeAsTicks, serializer, extraTypeMappings) + " ";
if (p.IsPK)
{
decl += "primary key ";
}
if (p.IsAutoInc)
{
decl += "autoincrement ";
}
if (!p.IsNullable)
{
decl += "not null ";
}
if (!string.IsNullOrEmpty(p.Collation))
{
decl += "collate " + p.Collation + " ";
}
if (p.DefaultValue != null)
{
decl += "default('" + p.DefaultValue + "') ";
}
return decl;
}
public static string SqlType(TableMapping.Column p, bool storeDateTimeAsTicks, IBlobSerializer serializer)
{
Type clrType = p.ColumnType;
var interfaces = clrType.GetTypeInfo().ImplementedInterfaces.ToList();
if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) ||
clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32) ||
interfaces.Contains(typeof(ISerializable<Boolean>)) ||
interfaces.Contains(typeof(ISerializable<Byte>)) ||
interfaces.Contains(typeof(ISerializable<UInt16>)) ||
interfaces.Contains(typeof(ISerializable<SByte>)) ||
interfaces.Contains(typeof(ISerializable<Int16>)) ||
interfaces.Contains(typeof(ISerializable<Int32>)))
{
return "integer";
}
if (clrType == typeof(UInt32) || clrType == typeof(Int64) ||
interfaces.Contains(typeof(ISerializable<UInt32>)) ||
interfaces.Contains(typeof(ISerializable<Int64>)))
{
return "bigint";
}
if (clrType == typeof(Single) || clrType == typeof(Double) || clrType == typeof(Decimal) ||
interfaces.Contains(typeof(ISerializable<Single>)) ||
interfaces.Contains(typeof(ISerializable<Double>)) ||
interfaces.Contains(typeof(ISerializable<Decimal>)))
{
return "float";
}
if (clrType == typeof(String) || interfaces.Contains(typeof(ISerializable<String>)))
{
int len = p.MaxStringLength;
return "varchar(" + len + ")";
}
if (clrType == typeof(TimeSpan) || interfaces.Contains(typeof(ISerializable<TimeSpan>)))
{
return "bigint";
}
if (clrType == typeof(DateTime) || interfaces.Contains(typeof(ISerializable<DateTime>)))
{
return storeDateTimeAsTicks ? "bigint" : "datetime";
}
if (clrType.GetTypeInfo().IsEnum)
{
return "integer";
}
if (clrType == typeof(byte[]) || interfaces.Contains(typeof(ISerializable<byte[]>)))
{
return "blob";
}
if (clrType == typeof(Guid) || interfaces.Contains(typeof(ISerializable<Guid>)))
{
return "varchar(36)";
}
if (serializer != null && serializer.CanDeserialize(clrType))
{
return "blob";
}
throw new NotSupportedException("Don't know about " + clrType);
}
protected NotNullConstraintViolationException(Result r, string message, TableMapping mapping, object obj)
: base(r, message)
{
if (mapping != null && obj != null)
{
Columns = from c in mapping.Columns
where c.IsNullable == false && c.GetValue(obj) == null
select c;
}
}
public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks)
{
//http://www.sqlite.org/lang_createtable.html
return String.Format("\"{0}\" {1} {2} {3} {4} ",
p.Name,
SqlType(p, storeDateTimeAsTicks),
p.IsAutoInc ? "primary key autoincrement" : null, //autoincrement can not be set with a multiple primary key
!p.IsNullable ? "not null" : null,
!String.IsNullOrEmpty(p.Collation) ? "collate " + p.Collation : null
);
}
public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks, IBlobSerializer serializer)
{
var result = String.Format("\"{0}\" {1} {2} {3} {4} ",
p.Name,
SqlType(p, storeDateTimeAsTicks, serializer),
p.IsAutoInc ? "primary key autoincrement" : null,
!p.IsNullable ? "not null" : null,
!String.IsNullOrEmpty(p.Collation) ? "collate " + p.Collation : null
);
return result;
}
public static string SqlType(TableMapping.Column p, bool storeDateTimeAsTicks)
{
var clrType = p.ColumnType;
if (clrType == typeof(Boolean) || clrType == typeof(Byte) || clrType == typeof(UInt16) || clrType == typeof(SByte) || clrType == typeof(Int16) || clrType == typeof(Int32))
{
return "integer";
}
if (clrType == typeof (UInt32) || clrType == typeof (Int64))
{
return "bigint";
}
if (clrType == typeof (Single) || clrType == typeof (Double) || clrType == typeof (Decimal))
{
return "float";
}
if (clrType == typeof(XElement))
{
return "nvarchar"; //SQLite ignores the length //See http://www.sqlite.org/datatype3.html
}
if (clrType == typeof (String))
{
var len = p.MaxStringLength;
if(len.HasValue)
return "nvarchar(" + len + ")";
return "nvarchar";
}
if (clrType == typeof(DateTimeOffset))
{
return "varchar";
}
if (clrType == typeof(TimeSpan))
{
return "bigint";
}
if (clrType == typeof (DateTime))
{
return storeDateTimeAsTicks ? "bigint" : "datetime";
}
if (clrType.GetTypeInfo().IsEnum)
{
return "integer";
}
if (clrType == typeof (byte[]))
{
return "blob";
}
if (clrType == typeof (Guid))
{
return "varchar(36)";
}
throw new NotSupportedException("Don't know about " + clrType);
}
public static string SqlType(TableMapping.Column p, bool storeDateTimeAsTicks, IBlobSerializer serializer)
{
Type clrType = p.ColumnType;
if (clrType == typeof (Boolean) || clrType == typeof (Byte) || clrType == typeof (UInt16) ||
clrType == typeof (SByte) || clrType == typeof (Int16) || clrType == typeof (Int32))
{
return "integer";
}
if (clrType == typeof (UInt32) || clrType == typeof (Int64))
{
return "bigint";
}
if (clrType == typeof (Single) || clrType == typeof (Double) || clrType == typeof (Decimal))
{
return "float";
}
if (clrType == typeof (String))
{
int len = p.MaxStringLength;
return "varchar(" + len + ")";
}
if (clrType == typeof (TimeSpan))
{
return "bigint";
}
if (clrType == typeof (DateTime))
{
return storeDateTimeAsTicks ? "bigint" : "datetime";
}
if (clrType.IsEnum)
{
return "integer";
}
if (clrType == typeof (byte[]))
{
return "blob";
}
if (clrType == typeof (Guid))
{
return "varchar(36)";
}
if (serializer != null && serializer.CanDeserialize(clrType))
{
return "blob";
}
throw new NotSupportedException("Don't know about " + clrType);
}
public IEnumerable <T> ExecuteDeferredQuery <T>(TableMapping map)
{
_conn.TraceListener.WriteLine("Executing Query: {0}", this);
var stmt = Prepare();
try
{
var cols = new TableMapping.Column[_sqlitePlatform.SQLiteApi.ColumnCount(stmt)];
for (var i = 0; i < cols.Length; i++)
{
var name = _sqlitePlatform.SQLiteApi.ColumnName16(stmt, i);
cols[i] = map.FindColumn(name);
}
while (_sqlitePlatform.SQLiteApi.Step(stmt) == Result.Row)
{
var obj = _conn.Resolver.CreateObject(map.MappedType);
for (var i = 0; i < cols.Length; i++)
{
if (cols[i] == null)
{
continue;
}
var colType = _sqlitePlatform.SQLiteApi.ColumnType(stmt, i);
var val = ReadCol(stmt, i, colType, cols[i].ColumnType);
cols[i].SetValue(obj, val);
}
OnInstanceCreated(obj);
yield return((T)obj);
}
}
finally
{
_sqlitePlatform.SQLiteApi.Finalize(stmt);
}
}
public static string SqlDecl(TableMapping.Column p, bool storeDateTimeAsTicks, IBlobSerializer serializer)
{
string decl = "\"" + p.Name + "\" " + SqlType(p, storeDateTimeAsTicks, serializer) + " ";
if (p.IsPK)
{
decl += "primary key ";
}
if (p.IsAutoInc)
{
decl += "autoincrement ";
}
if (!p.IsNullable)
{
decl += "not null ";
}
if (!string.IsNullOrEmpty(p.Collation))
{
decl += "collate " + p.Collation + " ";
}
return decl;
}
internal static NotNullConstraintViolationException New(Result r, string message, TableMapping mapping, object obj)
{
return(new NotNullConstraintViolationException(r, message, mapping, obj));
}
public ActiveInsertCommand(TableMapping tableMapping)
{
_tableMapping = tableMapping;
}
private CompileResult CompileExpr([NotNull] Expression expr, List <object> queryArgs, TableMapping table)
{
if (expr == null)
{
throw new NotSupportedException("Expression is NULL");
}
if (expr is BinaryExpression)
{
var bin = (BinaryExpression)expr;
var leftr = CompileExpr(bin.Left, queryArgs, table);
var rightr = CompileExpr(bin.Right, queryArgs, table);
//If either side is a parameter and is null, then handle the other side specially (for "is null"/"is not null")
string text;
if (leftr.CommandText == "?" && leftr.Value == null)
{
text = CompileNullBinaryExpression(bin, rightr);
}
else if (rightr.CommandText == "?" && rightr.Value == null)
{
text = CompileNullBinaryExpression(bin, leftr);
}
else
{
text = "(" + leftr.CommandText + " " + GetSqlName(bin) + " " + rightr.CommandText + ")";
}
return(new CompileResult
{
CommandText = text
});
}
if (expr.NodeType == ExpressionType.Not)
{
var operandExpr = ((UnaryExpression)expr).Operand;
var opr = CompileExpr(operandExpr, queryArgs, table);
var val = opr.Value;
if (val is bool)
{
val = !((bool)val);
}
return(new CompileResult
{
CommandText = "NOT(" + opr.CommandText + ")",
Value = val
});
}
if (expr.NodeType == ExpressionType.Call)
{
var call = (MethodCallExpression)expr;
var args = new CompileResult[call.Arguments.Count];
var obj = call.Object != null?CompileExpr(call.Object, queryArgs, table) : null;
for (var i = 0; i < args.Length; i++)
{
args[i] = CompileExpr(call.Arguments[i], queryArgs, table);
}
var sqlCall = "";
if (call.Method.Name == "Like" && args.Length == 2)
{
sqlCall = "(" + args[0].CommandText + " like " + args[1].CommandText + ")";
}
else if (call.Method.Name == "Contains" && args.Length == 2)
{
sqlCall = "(" + args[1].CommandText + " in " + args[0].CommandText + ")";
}
else if (call.Method.Name == "Contains" && args.Length == 1)
{
if (call.Object != null && call.Object.Type == typeof(string))
{
sqlCall = "(" + obj.CommandText + " like ('%' || " + args[0].CommandText + " || '%'))";
}
else
{
sqlCall = "(" + args[0].CommandText + " in " + obj.CommandText + ")";
}
}
else if (call.Method.Name == "StartsWith" && args.Length == 1)
{
sqlCall = "(" + obj.CommandText + " like (" + args[0].CommandText + " || '%'))";
}
else if (call.Method.Name == "EndsWith" && args.Length == 1)
{
sqlCall = "(" + obj.CommandText + " like ('%' || " + args[0].CommandText + "))";
}
else if (call.Method.Name == "Equals" && args.Length == 1)
{
sqlCall = "(" + obj.CommandText + " = (" + args[0].CommandText + "))";
}
else if (call.Method.Name == "ToLower")
{
sqlCall = "(lower(" + obj.CommandText + "))";
}
else if (call.Method.Name == "ToUpper")
{
sqlCall = "(upper(" + obj.CommandText + "))";
}
else
{
sqlCall = call.Method.Name.ToLower() + "(" +
string.Join(",", args.Select(a => a.CommandText).ToArray()) + ")";
}
return(new CompileResult
{
CommandText = sqlCall
});
}
if (expr.NodeType == ExpressionType.Constant)
{
var c = (ConstantExpression)expr;
queryArgs.Add(c.Value);
return(new CompileResult
{
CommandText = "?",
Value = c.Value
});
}
if (expr.NodeType == ExpressionType.Convert)
{
var u = (UnaryExpression)expr;
var ty = u.Type;
var valr = CompileExpr(u.Operand, queryArgs, table);
return(new CompileResult
{
CommandText = valr.CommandText,
Value = valr.Value != null?ConvertTo(valr.Value, ty) : null
});
}
if (expr.NodeType == ExpressionType.MemberAccess)
{
var mem = (MemberExpression)expr;
if (mem.Expression != null && mem.Expression.NodeType == ExpressionType.Parameter)
{
//
// This is a column of our table, output just the column name
// Need to translate it if that column name is mapped
//
var column = table.FindColumnWithPropertyName(mem.Member.Name);
var columnName = column != null ? column.Name : mem.Member.Name;
return(new CompileResult
{
RawText = columnName,
CommandText = "\"" + columnName + "\""
});
}
object obj = null;
if (mem.Expression != null)
{
var r = CompileExpr(mem.Expression, queryArgs, table);
if (r.Value == null)
{
if (r.RawText != null)
{
var columnNameWithAlias = r.RawText + "." + mem.Member.Name;
return(new CompileResult
{
RawText = columnNameWithAlias,
CommandText = columnNameWithAlias
});
}
throw new NotSupportedException("Member access failed to compile expression");
}
if (r.CommandText == "?")
{
queryArgs.RemoveAt(queryArgs.Count - 1);
}
obj = r.Value;
}
//
// Get the member value
//
var val = _sqlitePlatform.ReflectionService.GetMemberValue(obj, expr, mem.Member);
//
// Work special magic for enumerables
//
if (val != null && val is IEnumerable && !(val is string) && !(val is IEnumerable <byte>))
{
var sb = new StringBuilder();
sb.Append("(");
var head = "";
foreach (var a in (IEnumerable)val)
{
queryArgs.Add(a);
sb.Append(head);
sb.Append("?");
head = ",";
}
sb.Append(")");
return(new CompileResult
{
CommandText = sb.ToString(),
Value = val
});
}
queryArgs.Add(val);
return(new CompileResult
{
CommandText = "?",
Value = val
});
}
throw new NotSupportedException("Cannot compile: " + expr.NodeType);
}
public IEnumerable <T> ExecuteDeferredQuery <T>(TableMapping map)
{
_conn.TraceListener.WriteLine("Executing Query: {0}", this);
var stmt = Prepare();
try
{
var cols = new TableMapping.Column[_sqlitePlatform.SQLiteApi.ColumnCount(stmt)];
var seenColumns = new HashSet <TableMapping.Column>();
for (var i = 0; i < cols.Length; i++)
{
var name = _sqlitePlatform.SQLiteApi.ColumnName16(stmt, i);
var namedColumns = map.FindColumns(name);
if (namedColumns.Count == 1)
{
cols[i] = namedColumns[0];
}
else if (namedColumns.Count > 1)
{
cols[i] = namedColumns.FirstOrDefault(c => !seenColumns.Contains(c));
}
if (cols[i] != null)
{
seenColumns.Add(cols[i]);
}
}
while (_sqlitePlatform.SQLiteApi.Step(stmt) == Result.Row)
{
var obj = map.IsJoinTable
? _conn.Resolver.CreateObject(map.MappedType, new[] { _conn.Resolver })
: _conn.Resolver.CreateObject(map.MappedType);
for (var i = 0; i < cols.Length; i++)
{
if (cols[i] == null)
{
continue;
}
var colType = _sqlitePlatform.SQLiteApi.ColumnType(stmt, i);
var val = ReadCol(stmt, i, colType, cols[i].ColumnType);
if (!map.IsJoinTable)
{
cols[i].SetValue(obj, val);
}
else
{
cols[i].SetJoinValue(obj, val);
}
}
OnInstanceCreated(obj);
yield return((T)obj);
}
}
finally
{
_sqlitePlatform.SQLiteApi.Finalize(stmt);
}
}
private CompileResult CompileLambdaExpression(Expression expr, List <object> queryArgs, TableMapping table)
{
if (expr == null)
{
throw new ArgumentNullException("expr");
}
if (expr.NodeType != ExpressionType.Lambda)
{
throw new NotSupportedException("Must be a lambda");
}
var lambdaExpr = (LambdaExpression)expr;
return(CompileExpr(lambdaExpr.Body, queryArgs, table));
}
private static string SqlType(TableMapping.Column p, bool storeDateTimeAsTicks,
IBlobSerializer serializer,
IDictionary<Type, string> extraTypeMappings)
{
var clrType = p.ColumnType;
var interfaces = clrType.GetTypeInfo().ImplementedInterfaces.ToList();
string extraMapping;
if (extraTypeMappings.TryGetValue(clrType, out extraMapping))
{
return extraMapping;
}
if (clrType == typeof (bool) || clrType == typeof (byte) || clrType == typeof (ushort) ||
clrType == typeof (sbyte) || clrType == typeof (short) || clrType == typeof (int) ||
clrType == typeof (uint) || clrType == typeof (long) ||
interfaces.Contains(typeof (ISerializable<bool>)) ||
interfaces.Contains(typeof (ISerializable<byte>)) ||
interfaces.Contains(typeof (ISerializable<ushort>)) ||
interfaces.Contains(typeof (ISerializable<sbyte>)) ||
interfaces.Contains(typeof (ISerializable<short>)) ||
interfaces.Contains(typeof (ISerializable<int>)) ||
interfaces.Contains(typeof (ISerializable<uint>)) ||
interfaces.Contains(typeof (ISerializable<long>)) ||
interfaces.Contains(typeof (ISerializable<ulong>)))
{
return "integer";
}
if (clrType == typeof (float) || clrType == typeof (double) || clrType == typeof (decimal) ||
interfaces.Contains(typeof (ISerializable<float>)) ||
interfaces.Contains(typeof (ISerializable<double>)) ||
interfaces.Contains(typeof (ISerializable<decimal>)))
{
return "float";
}
if (clrType == typeof (string) || interfaces.Contains(typeof (ISerializable<string>)))
{
var len = p.MaxStringLength;
if (len.HasValue)
{
return "varchar(" + len.Value + ")";
}
return "varchar";
}
if (clrType == typeof (TimeSpan) || interfaces.Contains(typeof (ISerializable<TimeSpan>)))
{
return "bigint";
}
if (clrType == typeof (DateTime) || interfaces.Contains(typeof (ISerializable<DateTime>)))
{
return storeDateTimeAsTicks ? "bigint" : "datetime";
}
if (clrType == typeof (DateTimeOffset))
{
return "bigint";
}
if (clrType.GetTypeInfo().IsEnum)
{
return "integer";
}
if (clrType == typeof (byte[]) || interfaces.Contains(typeof (ISerializable<byte[]>)))
{
return "blob";
}
if (clrType == typeof (Guid) || interfaces.Contains(typeof (ISerializable<Guid>)))
{
return "varchar(36)";
}
if (serializer != null && serializer.CanDeserialize(clrType))
{
return "blob";
}
throw new NotSupportedException("Don't know about " + clrType);
}
public ActiveInsertCommand(TableMapping tableMapping)
{
_tableMapping = tableMapping;
}
public static NotNullConstraintViolationException New(Result r, string message, TableMapping mapping, object obj)
{
return new NotNullConstraintViolationException(r, message, mapping, obj);
}
private TableQuery(ISQLitePlatform platformImplementation, SQLiteConnection conn, TableMapping table)
{
_sqlitePlatform = platformImplementation;
Connection = conn;
Table = table;
}
public static NotNullConstraintViolationException New(SQLiteException exception, TableMapping mapping, object obj)
{
return new NotNullConstraintViolationException(exception.Result, exception.Message, mapping, obj);
}
public List <T> ExecuteQuery <T>(TableMapping map)
{
return(ExecuteDeferredQuery <T>(map).ToList());
}
internal static NotNullConstraintViolationException New(SQLiteException exception, TableMapping mapping, object obj)
{
return(new NotNullConstraintViolationException(exception.Result, exception.Message, mapping, obj));
}