public IQueryBuilder QueryIds <T>(List <T> rs) where T : IDataObject
{
var id = ReflectionTool.FieldsAndPropertiesOf(typeof(T)).FirstOrDefault(f => f.GetCustomAttribute <PrimaryKeyAttribute>() != null);
var rid = ReflectionTool.FieldsAndPropertiesOf(typeof(T)).FirstOrDefault(f => f.GetCustomAttribute <ReliableIdAttribute>() != null);
return(Qb.Fmt($"SELECT {id.Name}, {rid.Name} FROM {typeof(T).Name} WHERE") + Qb.In(rid.Name, rs, i => i.RID));
}
public IQueryBuilder OnInsertSubQuery(Type t, MemberInfo mi)
{
return(Qb.Fmt($@"(
SELECT a.{mi.Name} + 1 as ICount From {t.Name} a
ORDER BY a.{mi.Name} DESC
LIMIT 1
)"));
}
public IQueryBuilder QueryIds <T>(List <T> rs) where T : IDataObject
{
var type = rs.FirstOrDefault()?.GetType() ?? typeof(T);
var id = FiTechBDadosExtensions.IdColumnOf[type];
var rid = FiTechBDadosExtensions.RidColumnOf[type];
var ridType = ReflectionTool.GetTypeOf(ReflectionTool.FieldsAndPropertiesOf(type).FirstOrDefault(x => x.GetCustomAttribute <ReliableIdAttribute>() != null));
return(Qb.Fmt($"SELECT {id} AS Id, {rid} AS RID FROM {type.Name} WHERE") + Qb.In(rid, rs, i => Convert.ChangeType(i.RID, ridType)));
}
public IQueryBuilder OnInsertSubQuery(Type t, MemberInfo mi)
{
return(Qb.Fmt($@"(
SELECT a.{mi.Name} + 1 as PCount From {t.Name} a
LEFT JOIN {t.Name} b
ON b.{mi.Name} = a.{mi.Name} + 1
WHERE b.{mi.Name} IS NULL
LIMIT 1
)"));
}
public IQueryBuilder GenerateMultiInsert <T>(List <T> inputRecordset, bool OmmitPk = true) where T : IDataObject
{
var t = inputRecordset.FirstOrDefault()?.GetType();
if (t == null)
{
return(Qb.Fmt("SELECT 1"));
}
List <T> workingSet = new List <T>();
workingSet.AddRange(inputRecordset.Where((r) => !r.IsPersisted));
if (workingSet.Count < 1)
{
return(null);
}
// --
QueryBuilder Query = new QueryBuilder();
Query.Append($"INSERT INTO {t.Name} (");
Query.Append(GenerateFieldsString(t, OmmitPk));
Query.Append(") VALUES");
// --
for (int i = 0; i < workingSet.Count; i++)
{
Query.Append("(");
Query.Append(GenerateValuesString(workingSet[i], OmmitPk));
Query.Append(")");
if (i < workingSet.Count - 1)
{
Query.Append(",");
}
}
// --
//Query.Append("ON DUPLICATE KEY UPDATE ");
//var Fields = GetMembers(typeof(T)).Where(
// field=>
// (OmmitPk || field.GetCustomAttribute<PrimaryKeyAttribute>() == null) &&
// field.GetCustomAttribute<ReliableIdAttribute>() == null
//).ToList();
//for (int i = 0; i < Fields.Count; ++i) {
// Query.Append($"{Fields[i].Name} = VALUES({Fields[i].Name})");
// if (i < Fields.Count - 1) {
// Query.Append(",");
// }
//}
// --
return(Query);
}
public IQueryBuilder QueryIds <T>(List <T> rs) where T : IDataObject
{
if (!rs.Any())
{
return(Qb.Fmt("SELECT 1 as Id, 'no-rid' as RID WHERE FALSE"));
}
var t = rs.First().GetType();
var id = FiTechBDadosExtensions.IdColumnOf[t];
var rid = FiTechBDadosExtensions.RidColumnOf[t];
var retv = Qb.Fmt($"SELECT {id}, {rid} FROM {t.Name} WHERE") + Qb.In(rid, rs, i => i.RID);
//if(rs.Any(i=> i.Id>0)) {
// var existingIds = rs.Where(i => i.Id > 0).ToList();
// if (existingIds.Count > 0) {
// retv += Qb.Or() + Qb.In(id, existingIds, i => i.Id);
// }
//}
return(retv);
}
private QueryBuilder ParseExpression(Expression foofun, Type typeOfT, String ForceAlias = null, QueryBuilder strBuilder = null, bool fullConditions = true)
{
//if(strBuilder == null)
strBuilder = new QueryBuilder();
if (foofun is BinaryExpression)
{
var expr = foofun as BinaryExpression;
if (expr.NodeType == ExpressionType.Equal &&
expr.Right is ConstantExpression && (expr.Right as ConstantExpression).Value == null)
{
strBuilder.Append("(");
strBuilder.Append(ParseExpression(expr.Left, typeOfT, ForceAlias, strBuilder, fullConditions));
strBuilder.Append("IS NULL");
strBuilder.Append(")");
}
else
if (expr.NodeType == ExpressionType.NotEqual &&
expr.Right is ConstantExpression && (expr.Right as ConstantExpression).Value == null)
{
strBuilder.Append("(");
strBuilder.Append(ParseExpression(expr.Left, typeOfT, ForceAlias, strBuilder, fullConditions));
strBuilder.Append("IS NOT NULL");
strBuilder.Append(")");
}
else
if (expr.NodeType == ExpressionType.Equal &&
CanGetValue(expr.Right) &&
GetValue(expr.Right)?.GetType() == typeof(string) &&
(expr.Left is MemberExpression)
)
{
var member = (expr.Left as MemberExpression).Member;
var comparisonType = member.GetCustomAttribute <QueryComparisonAttribute>()?.Type;
//if (Debugger.IsAttached) {
// Debugger.Break();
//}
if (GetValue(expr.Right)?.GetType() == typeof(string))
{
strBuilder.Append("(");
strBuilder.Append(ParseExpression(expr.Left, typeOfT, ForceAlias, strBuilder, fullConditions));
var appendFragment = String.Empty;
switch (comparisonType)
{
case DataStringComparisonType.Containing:
appendFragment = $"LIKE CONCAT('%', @{GenerateParameterId}, '%')";
break;
case DataStringComparisonType.EndingWith:
appendFragment = $"LIKE CONCAT('%', @{GenerateParameterId})";
break;
case DataStringComparisonType.StartingWith:
appendFragment = $"LIKE CONCAT(@{GenerateParameterId}, '%')";
break;
case DataStringComparisonType.ExactValue:
appendFragment = $"=@{GenerateParameterId}";
break;
default:
appendFragment = $"=@{GenerateParameterId}";
break;
}
strBuilder.Append(appendFragment, GetValue(expr.Right));
strBuilder.Append(")");
}
}
else
{
strBuilder.Append("(");
strBuilder.Append(ParseExpression(expr.Left, typeOfT, ForceAlias, strBuilder, fullConditions));
strBuilder.Append(
expr.NodeType == ExpressionType.AndAlso ? "AND" :
expr.NodeType == ExpressionType.OrElse ? "OR" :
expr.NodeType == ExpressionType.Equal ? "=" :
expr.NodeType == ExpressionType.NotEqual ? "!=" :
expr.NodeType == ExpressionType.Not ? "!" :
expr.NodeType == ExpressionType.GreaterThan ? ">" :
expr.NodeType == ExpressionType.GreaterThanOrEqual ? ">=" :
expr.NodeType == ExpressionType.LessThan ? "<" :
expr.NodeType == ExpressionType.LessThanOrEqual ? "<=" :
"");
strBuilder.Append(ParseExpression(expr.Right, typeOfT, ForceAlias, strBuilder, fullConditions));
strBuilder.Append(")");
}
}
else
if (foofun is MemberExpression)
{
var expr = foofun as MemberExpression;
Expression subexp = expr;
while (subexp is MemberExpression && (subexp as MemberExpression).Expression != null)
{
subexp = (subexp as MemberExpression).Expression;
}
if (subexp is UnaryExpression)
{
subexp = (subexp as UnaryExpression).Operand;
}
if (subexp is ParameterExpression)
{
// If this member belongs to an AggregateField, then problems problems...
var aList = new List <MemberInfo>();
aList.AddRange(ReflectionTool.FieldsWithAttribute <FieldAttribute>(subexp.Type).Where((t) => t.Name == expr.Member.Name.Replace("_", "")));
if (!fullConditions && ((subexp as ParameterExpression).Type != typeOfT || !aList.Any()))
{
return(new QbFmt("1"));
}
if (aList.Any())
{
strBuilder.Append($"{ForceAlias ?? GetPrefixOfExpression(expr.Expression)}.{expr.Member.Name.Replace("_", "")}");
}
else
{
// oh hell.
MemberInfo member;
if (expr.Expression is ParameterExpression)
{
member = (expr.Expression as ParameterExpression).Type.GetMembers().FirstOrDefault(m => m.Name == expr.Member.Name);
}
else
{
member = (expr.Expression as MemberExpression).Type.GetMembers().FirstOrDefault(m => m.Name == expr.Member.Name);
}
var info = ReflectionTool.GetAttributeFrom <AggregateFieldAttribute>(member);
if (info != null)
{
var prefix = ForceAlias ?? GetPrefixOfExpression(expr); // prefixer.GetAliasFor("root", subexp.Type.Name);
//var alias = prefixer.GetAliasFor(prefix, expr.Member.Name);
strBuilder.Append($"{prefix}.{info.RemoteField}");
}
else
{
var info2 = ReflectionTool.GetAttributeFrom <AggregateFarFieldAttribute>(expr.Member);
if (info2 != null)
{
var prefix = ForceAlias ?? GetPrefixOfExpression(expr); // prefixer.GetAliasFor("root", subexp.Type.Name);
//var alias = prefixer.GetAliasFor(prefix, expr.Member.Name);
strBuilder.Append($"{prefix}.{info2.FarField}");
}
else
{
var mem = (expr.Expression).Type.GetMembers().FirstOrDefault(m => m.Name == expr.Member.Name);
if (mem == null)
{
throw new BDadosException($"Fatal runtime inconsistency error: Cannot find member {expr.Member.Name} in type {(expr.Expression).Type}!");
}
var info3 = ReflectionTool.GetAttributeFrom <AggregateObjectAttribute>(mem);
var altName = ReflectionTool.GetAttributeFrom <OverrideColumnNameOnWhere>(mem);
var memberName = altName?.Name ?? member.Name;
if (info3 != null)
{
var prefix = ForceAlias ?? GetPrefixOfExpression(expr.Expression); // prefixer.GetAliasFor("root", subexp.Type.Name);
//var alias = prefixer.GetAliasFor(prefix, expr.Member.Name);
strBuilder.Append($"{prefix}.{memberName}");
}
else
{
var prefix = GetPrefixOfExpression(expr);
strBuilder.Append($"{prefix}.{memberName}");
}
}
}
}
}
else if (subexp is MethodCallExpression)
{
strBuilder.Append($"{ParseExpression(subexp, typeOfT, ForceAlias, strBuilder, fullConditions).GetCommandText()}.{expr.Member.Name}");
}
else
{
strBuilder.Append($"@{GenerateParameterId}", GetValue(expr));
}
}
else
if (foofun is ConstantExpression)
{
var expr = foofun as ConstantExpression;
strBuilder.Append($"@{GenerateParameterId}", expr.Value);
}
else
if (foofun is UnaryExpression)
{
var expr = foofun as UnaryExpression;
if (expr.NodeType == ExpressionType.Not)
{
strBuilder.Append("!(");
strBuilder.Append(ParseExpression(expr.Operand, typeOfT, ForceAlias, strBuilder, fullConditions));
strBuilder.Append(")");
}
if (expr.NodeType == ExpressionType.Convert)
{
//strBuilder.Append($"@{GenerateParameterId}", GetValue(expr));
strBuilder.Append(ParseExpression(expr.Operand, typeOfT, ForceAlias, strBuilder, fullConditions));
}
}
else
if (!fullConditions)
{
return(new QbFmt(""));
}
else
if (foofun is MethodCallExpression)
{
var expr = foofun as MethodCallExpression;
if (expr.Method.DeclaringType == typeof(Qh))
{
var tq = typeof(Qb);
var equivalent = tq.GetMethods().FirstOrDefault(m => m.Name == expr.Method.Name && m.GetParameters().Length == expr.Method.GetParameters().Length - 1);
if (equivalent != null)
{
if (equivalent.ContainsGenericParameters)
{
var gmdefTypeArgs = expr.Method.GetGenericArguments();
//var gmdefTypeArgs = gmdef;
equivalent = equivalent.MakeGenericMethod(gmdefTypeArgs);
}
return((QueryBuilder)equivalent.Invoke(null, expr.Arguments.Skip(1).Select(a => GetValue(a)).ToArray()));
}
}
if (expr.Method.Name == "Any")
{
if (expr.Arguments.Count > 0)
{
if (expr.Arguments[0] is MemberExpression)
{
strBuilder.Append($"{GetPrefixOfExpression(expr.Arguments[0])}.RID IS NOT NULL");
}
else
{
strBuilder.Append(ParseExpression(expr.Arguments[0], typeOfT, ForceAlias, strBuilder, fullConditions));
}
}
}
if (expr.Method.Name == "ToLower")
{
strBuilder.Append($"LOWER(").Append(ParseExpression(expr.Object, typeOfT, ForceAlias, strBuilder, fullConditions)).Append(")");
}
if (expr.Method.Name == "Equals")
{
var memberEx = expr.Object as MemberExpression;
var pre = GetPrefixOfExpression(memberEx);
var column = memberEx.Member.Name;
strBuilder.Append($"{pre}.{column}=(");
strBuilder.Append(ParseExpression(expr.Arguments[0], typeOfT, ForceAlias, strBuilder, fullConditions));
strBuilder.Append(")");
}
if (expr.Method.Name == "ToUpper")
{
strBuilder.Append($"UPPER(").Append(ParseExpression(expr.Object, typeOfT, ForceAlias, strBuilder, fullConditions)).Append(")");
}
if (expr.Method.Name == "Where")
{
if (expr.Arguments.Count > 1)
{
var prevV = ForceAlias;
ForceAlias = GetPrefixOfExpression(expr.Arguments[0] as MemberExpression);
strBuilder.Append(ParseExpression((expr.Arguments[1] as LambdaExpression).Body, typeOfT, ForceAlias, strBuilder, fullConditions));
ForceAlias = prevV;
}
}
if (expr.Method.Name == "First")
{
if (expr.Arguments.Count > 0)
{
strBuilder.Append(GetPrefixOfExpression(expr.Arguments[0]));
}
}
}
if (foofun is NewExpression newex)
{
var marshalledValue = newex.Constructor.Invoke(newex.Arguments.Select(arg => GetValue(arg)).ToArray());
return(Qb.Fmt($"@{GenerateParameterId}", marshalledValue));
}
if (fullConditions)
{
return(strBuilder);
}
else
{
return((QueryBuilder) new QueryBuilder().Append(strBuilder.GetCommandText().Replace("tba.", ""), strBuilder.GetParameters().Select((pm) => pm.Value).ToArray()));
}
}
public IQueryBuilder EnableForeignKeys()
{
return(Qb.Fmt("SET FOREIGN_KEY_CHECKS=0;"));
}
public IQueryBuilder GenerateMultiUpdate <T>(List <T> inputRecordset) where T : IDataObject
{
// --
var t = inputRecordset?.FirstOrDefault()?.GetType();
if (t == null)
{
return(Qb.Fmt("SELECT 1"));
}
List <T> workingSet = new List <T>();
var rid = FiTechBDadosExtensions.RidColumnOf[t];
var upd = FiTechBDadosExtensions.UpdateColumnOf[t];
workingSet.AddRange(inputRecordset.Where((record) => record.IsPersisted));
if (workingSet.Count < 1)
{
return(null);
}
QueryBuilder Query = new QueryBuilder();
Query.Append($"UPDATE {t.Name} ");
Query.Append("SET \r\n");
// --
var members = GetMembers(t);
members.RemoveAll(
m =>
m.GetCustomAttribute <PrimaryKeyAttribute>() != null ||
m.GetCustomAttribute <ReliableIdAttribute>() != null
);
int x = 0;
int ggid = ++gid;
Query.PrepareForQueryLength(inputRecordset.Count * 512);
for (var i = 0; i < members.Count; i++)
{
var memberType = ReflectionTool.GetTypeOf(members[i]);
Query.Append($"\t{members[i].Name}=(CASE ");
for (int ridx = 0; ridx < inputRecordset.Count; ridx++)
{
Query.Append($"WHEN {rid}=@r_{ridx}", inputRecordset[ridx].RID);
if (inputRecordset[ridx].IsReceivedFromSync)
{
Query.Append($"AND {upd}<@u_{ridx}", inputRecordset[ridx].UpdatedTime);
}
Query.Append($"THEN @{ggid}_{++x}", ReflectionTool.GetMemberValue(members[i], inputRecordset[ridx]));
}
Query.Append($"ELSE {members[i].Name} END)");
if (i < members.Count - 1)
{
Query.Append(",\r\n");
}
}
Query.Append($"WHERE {rid} IN (")
.Append(Fi.Tech.ListRids(workingSet))
.Append(");");
// --
return(Query);
}
public IQueryBuilder EnableForeignKeys()
{
return(Qb.Fmt("SET session_replication_role = 'origin';"));
}
public IQueryBuilder DisableForeignKeys()
{
return(Qb.Fmt("SET session_replication_role = 'replica';"));
}
public static int Execute(this IRdbmsDataAccessor self, BDadosTransaction transaction, string query, params object[] args)
{
return(self.Execute(transaction, (IQueryBuilder)Qb.Fmt(query, args)));
}
public static DataTable Query(this IRdbmsDataAccessor self, BDadosTransaction transaction, string query, params object[] args)
{
return(self.Query(transaction, Qb.Fmt(query, args)));
}
public static DataTable Query(this IRdbmsDataAccessor self, string query, params object[] args)
{
return(self.Query(Qb.Fmt(query, args)));
}
public static List <T> Query <T>(this IRdbmsDataAccessor self, BDadosTransaction transaction, string query, params object[] args) where T : IDataObject, new()
{
return(self.Query <T>(transaction, (IQueryBuilder)Qb.Fmt(query, args)));
}
public static List <T> LoadAll <T>(this IRdbmsDataAccessor self, BDadosTransaction transaction, string query, params object[] args) where T : IDataObject, new()
{
return(self.LoadAll(transaction, (IQueryBuilder)Qb.Fmt(query, args), (int?)null, (int?)null, (Expression <Func <T, object> >)null, OrderingType.Asc, (object)null));
}
