public IQueryBuilder GenerateSelect <T>(IQueryBuilder condicoes = null, int?skip = null, int?limit = null, MemberInfo orderingMember = null, OrderingType ordering = OrderingType.Asc) where T : IDataObject, new()
{
var alias = "tba";
QueryBuilder Query = new QueryBuilder();
lock (LocksAutoSelectCache[$"MYSQL_SELECTS_{typeof(T).Name}"]) {
if (!AutoSelectCache.ContainsKey(typeof(T)))
{
Fi.Tech.WriteLine($"Generating SELECT {condicoes} {skip} {limit} {orderingMember?.Name} {ordering}");
QueryBuilder baseSelect = new QbFmt("SELECT ");
baseSelect.Append(GenerateFieldsString(typeof(T), false));
baseSelect.Append(String.Format($"FROM {typeof(T).Name} AS { alias }"));
AutoSelectCache[typeof(T)] = baseSelect;
}
Query.Append(AutoSelectCache[typeof(T)]);
}
if (condicoes != null && !condicoes.IsEmpty)
{
Query.Append("WHERE");
Query.Append(condicoes);
}
if (orderingMember != null)
{
Query.Append($"ORDER BY {alias}.{orderingMember.Name} {ordering.ToString().ToUpper()}");
}
if (limit != null || skip != null)
{
Query.Append($"LIMIT {(skip != null ? $"{skip}," : "")} {limit ?? Int32.MaxValue}");
}
Query.Append(";");
return(Query);
}
public IQueryBuilder GenerateSelect <T>(IQueryBuilder condicoes = null, int?skip = null, int?limit = null, MemberInfo orderingMember = null, OrderingType ordering = OrderingType.Asc) where T : IDataObject, new()
{
var type = typeof(T);
var alias = new PrefixMaker().GetAliasFor("root", typeof(T).Name, String.Empty);
Fi.Tech.WriteLine($"Generating SELECT {condicoes} {skip} {limit} {orderingMember?.Name} {ordering}");
QueryBuilder Query = new QbFmt("SELECT ");
Query.Append(GenerateFieldsString(type, false));
Query.Append(String.Format($"FROM {type.Name} AS { alias }"));
if (condicoes != null && !condicoes.IsEmpty)
{
Query.Append("WHERE");
Query.Append(condicoes);
}
if (orderingMember != null)
{
Query.Append($"ORDER BY {alias}.{orderingMember.Name} {ordering.ToString().ToUpper()}");
}
if (limit != null || skip != null)
{
Query.Append($"LIMIT {(skip != null ? $"{skip}," : "")} {limit ?? Int32.MaxValue}");
}
Query.Append(";");
return(Query);
}
public IQueryBuilder GetCreationCommand(Type t)
{
String objectName = t.Name;
var members =
ReflectionTool
.FieldsAndPropertiesOf(t).Where((m) => m?.GetCustomAttribute <FieldAttribute>() != null)
.ToArray();
if (objectName == null || objectName.Length == 0)
{
return(null);
}
QueryBuilder CreateTable = new QbFmt($"CREATE TABLE IF NOT EXISTS {objectName} (\n");
for (int i = 0; i < members.Length; i++)
{
var info = members[i].GetCustomAttribute <FieldAttribute>();
CreateTable.Append(GetColumnDefinition(members[i], info));
CreateTable.Append(" ");
CreateTable.Append(info.Options ?? "");
if (i != members.Length - 1)
{
CreateTable.Append(", \n");
}
}
CreateTable.Append(" );");
return(CreateTable);
}
public IQueryBuilder GenerateInsertQuery(IDataObject inputObject)
{
var type = inputObject.GetType();
QueryBuilder query = new QbFmt($"INSERT INTO {inputObject.GetType().Name}");
query.Append("(");
query.Append(GenerateFieldsString(type, false));
query.Append(")");
query.Append("VALUES (");
var members = GetMembers(type);
// members.RemoveAll(m => m.GetCustomAttribute<PrimaryKeyAttribute>() != null);
for (int i = 0; i < members.Count; i++)
{
query.Append($"@{i + 1}", ReflectionTool.GetMemberValue(members[i], inputObject));
if (i < members.Count - 1)
{
query.Append(",");
}
}
query.Append(")");
//query.Append("ON CONFLICT (" + GenerateKeysString(type) + ") DO UPDATE SET ");
//var Fields = GetMembers(inputObject.GetType());
//for (int i = 0; i < Fields.Count; ++i) {
// if (Fields[i].GetCustomAttribute(typeof(PrimaryKeyAttribute)) != null)
// continue;
// query.Append(String.Format("{0} = VALUES({0})", Fields[i].Name));
// if (i < Fields.Count - 1) {
// query.Append(",");
// }
//}
return(query);
}
public IQueryBuilder GenerateInsertQuery(IDataObject inputObject)
{
QueryBuilder query = new QbFmt($"INSERT INTO {inputObject.GetType().Name}");
query.Append("(");
query.Append(GenerateFieldsString(inputObject.GetType(), true));
query.Append(")");
query.Append("VALUES (");
var members = GetMembers(inputObject.GetType());
members.RemoveAll(m => m.GetCustomAttribute <PrimaryKeyAttribute>() != null);
for (int i = 0; i < members.Count; i++)
{
var val = ReflectionTool.GetMemberValue(members[i], inputObject);
if (val == null)
{
var pc = members[i].GetCustomAttribute <PreemptiveCounter>();
var ic = members[i].GetCustomAttribute <IncrementalCounter>();
if (pc != null)
{
query.Append(pc.OnInsertSubQuery(inputObject.GetType(), members[i]));
}
else
if (ic != null)
{
query.Append(ic.OnInsertSubQuery(inputObject.GetType(), members[i]));
}
else
{
query.Append($"@{members[i].Name}_{i + 1}", val);
}
}
else
{
query.Append($"@{members[i].Name}_{i + 1}", val);
}
if (i < members.Count - 1)
{
query.Append(",");
}
}
query.Append(")");
//query.Append("ON DUPLICATE KEY UPDATE ");
//var Fields = GetMembers(inputObject.GetType()).Where(field =>
// field.GetCustomAttribute(typeof(PrimaryKeyAttribute)) == null &&
// field.GetCustomAttribute(typeof(ReliableIdAttribute)) == null
//).ToList();
//for (int i = 0; i < Fields.Count; ++i) {
// query.Append(String.Format("{0} = VALUES({0})", Fields[i].Name));
// if (i < Fields.Count - 1) {
// query.Append(",");
// }
//}
return(query);
}
public IQueryBuilder GenerateSelectAll <T>() where T : IDataObject, new()
{
var type = typeof(T);
QueryBuilder Query = new QbFmt("SELECT ");
Query.Append(GenerateFieldsString(type, false));
Query.Append($"FROM {type.Name} AS {new PrefixMaker().GetAliasFor("root", typeof(T).Name, String.Empty)};");
return(Query);
}
public IQueryBuilder GenerateUpdateQuery(IDataObject tabelaInput)
{
var rid = FiTechBDadosExtensions.RidColumnOf[tabelaInput.GetType()];
QueryBuilder Query = new QbFmt(String.Format("UPDATE {0} ", tabelaInput.GetType().Name));
Query.Append("SET");
Query.Append(GenerateUpdateValueParams(tabelaInput, true));
Query.Append($" WHERE {rid} = @rid;", tabelaInput.RID);
return(Query);
}
public IQueryBuilder GenerateUpdateQuery(IDataObject tabelaInput)
{
var type = tabelaInput.GetType();
var rid = FiTechBDadosExtensions.RidColumnOf[type];
var ridType = ReflectionTool.GetTypeOf(ReflectionTool.FieldsAndPropertiesOf(type).FirstOrDefault(x => x.GetCustomAttribute <ReliableIdAttribute>() != null));
QueryBuilder Query = new QbFmt(String.Format("UPDATE {0} ", tabelaInput.GetType().Name));
Query.Append("SET");
Query.Append(GenerateUpdateValueParams(tabelaInput, false));
Query.Append($" WHERE {rid} = @rid;", Convert.ChangeType(tabelaInput.RID, ridType));
return(Query);
}
public IQueryBuilder GenerateSelectAll <T>() where T : IDataObject, new()
{
var alias = "tba";
QueryBuilder Query = new QueryBuilder();
if (!AutoSelectCache.ContainsKey(typeof(T)))
{
QueryBuilder baseSelect = new QbFmt("SELECT ");
baseSelect.Append(GenerateFieldsString(typeof(T), false));
baseSelect.Append(String.Format($"FROM {typeof(T).Name} AS { alias }"));
AutoSelectCache[typeof(T)] = baseSelect;
}
Query.Append(AutoSelectCache[typeof(T)]);
return(Query);
}
public IQueryBuilder GenerateCallProcedure(string name, object[] args)
{
QueryBuilder query = new QbFmt($"CALL {name}");
for (int i = 0; i < args.Length; i++)
{
if (i == 0)
{
query.Append("(");
}
query.Append($"@{i + 1}", args[i]);
if (i < args.Length - 1)
{
query.Append(",");
}
if (i == args.Length - 1)
{
query.Append(")");
}
}
return(query);
}
public IQueryBuilder GenerateInsertQuery(IDataObject inputObject)
{
QueryBuilder query = new QbFmt($"INSERT INTO {inputObject.GetType().Name}");
query.Append("(");
query.Append(GenerateFieldsString(inputObject.GetType(), true));
query.Append(")");
query.Append("VALUES (");
var members = GetMembers(inputObject.GetType());
members.RemoveAll(m => m.GetCustomAttribute <PrimaryKeyAttribute>() != null);
for (int i = 0; i < members.Count; i++)
{
query.Append($"@{i + 1}", ReflectionTool.GetMemberValue(members[i], inputObject));
if (i < members.Count - 1)
{
query.Append(",");
}
}
query.Append(") ON DUPLICATE KEY UPDATE ");
var Fields = GetMembers(inputObject.GetType());
for (int i = 0; i < Fields.Count; ++i)
{
if (Fields[i].GetCustomAttribute(typeof(PrimaryKeyAttribute)) != null)
{
continue;
}
query.Append(String.Format("{0} = VALUES({0})", Fields[i].Name));
if (i < Fields.Count - 1)
{
query.Append(",");
}
}
return(query);
}
public IQueryBuilder GenerateJoinQuery(JoinDefinition inputJoin, IQueryBuilder conditions, int?skip = null, int?take = null, MemberInfo orderingMember = null, OrderingType otype = OrderingType.Asc, IQueryBuilder rootConditions = null)
{
if (rootConditions == null)
{
rootConditions = new QbFmt("true");
}
if (inputJoin.Joins.Count < 1)
{
throw new BDadosException("This join needs 1 or more tables.");
}
List <Type> tables = (from a in inputJoin.Joins select a.ValueObject).ToList();
List <String> tableNames = (from a in inputJoin.Joins select a.ValueObject.Name).ToList();
List <String> prefixes = (from a in inputJoin.Joins select a.Prefix).ToList();
List <String> aliases = (from a in inputJoin.Joins select a.Alias).ToList();
List <String> onclauses = (from a in inputJoin.Joins select a.Args).ToList();
List <List <String> > columns = (from a in inputJoin.Joins select a.Columns).ToList();
List <JoinType> joinTypes = (from a in inputJoin.Joins select a.Type).ToList();
var isLinedAggregateJoin = false; // && conditions.GetCommandText() == rootConditions.GetCommandText() && conditions.GetParameters().SequenceEqual(rootConditions.GetParameters());
QueryBuilder Query = new QueryBuilder();
if (!AutoJoinCache.ContainsKey(inputJoin))
{
lock (AutoJoinCache) {
if (!AutoJoinCache.ContainsKey(inputJoin))
{
// By caching this heavy process I might gain loads of performance
// When redoing the same queries.
QueryBuilder autoJoinMain = new QbFmt("SELECT sub.*\n");
autoJoinMain.Append($"\t FROM (SELECT\n");
for (int i = 0; i < tables.Count; i++)
{
autoJoinMain.Append($"\t\t-- Table {tableNames[i]}\n");
var ridF = FiTechBDadosExtensions.RidColumnOf[tables[i]];
if (!columns[i].Any(c => c.ToUpper() == ridF.ToUpper()))
{
columns[i].Add(ridF);
}
var nonexcl = columns[i];
for (int j = 0; j < nonexcl.Count; j++)
{
autoJoinMain.Append($"\t\t{prefixes[i]}.{nonexcl[j]} AS {prefixes[i]}_{nonexcl[j]},\n");
}
autoJoinMain.Append("\n");
}
autoJoinMain.Append($"\t\t1 FROM (SELECT * FROM {tableNames[0]}");
if (isLinedAggregateJoin)
{
if (rootConditions != null)
{
autoJoinMain.Append("WHERE ");
autoJoinMain.Append(rootConditions);
}
if (orderingMember != null)
{
autoJoinMain.Append($"ORDER BY {orderingMember.Name} {otype.ToString().ToUpper()}");
}
if (skip != null || take != null)
{
autoJoinMain.Append("LIMIT ");
autoJoinMain.Append(
skip != null ? $"{skip},{take ?? Int32.MaxValue}" : $"{take ?? Int32.MaxValue}"
);
}
autoJoinMain.Append($"");
}
autoJoinMain.Append($") AS {prefixes[0]}\n");
for (int i = 1; i < tables.Count; i++)
{
autoJoinMain.Append($"\t\t{"LEFT"} JOIN {tableNames[i]} AS {prefixes[i]} ON {onclauses[i]}\n");
}
AutoJoinCache[inputJoin] = (autoJoinMain);
}
}
}
Query.Append(AutoJoinCache[inputJoin]);
if (!isLinedAggregateJoin)
{
if (conditions != null && !conditions.IsEmpty)
{
Query.Append("\tWHERE");
Query.Append(conditions);
}
if (orderingMember != null)
{
Query.Append($"ORDER BY {prefixes[0]}.{orderingMember.Name} {otype.ToString().ToUpper()}");
}
if (skip != null || take != null)
{
Query.Append("LIMIT ");
Query.Append(
skip != null ? $"{skip},{take ?? Int32.MaxValue}" : $"{take ?? Int32.MaxValue}"
);
}
}
Query.Append(") AS sub\n");
return(Query);
}
public IQueryBuilder GenerateJoinQuery(JoinDefinition inputJoin, IQueryBuilder conditions, int?skip = null, int?limit = null, MemberInfo orderingMember = null, OrderingType otype = OrderingType.Asc, IQueryBuilder rootConditions = null)
{
if (rootConditions == null)
{
rootConditions = new QbFmt("true");
}
if (inputJoin.Joins.Count < 1)
{
throw new BDadosException("This join needs 1 or more tables.");
}
List <Type> tables = (from a in inputJoin.Joins select a.ValueObject).ToList();
List <String> tableNames = (from a in inputJoin.Joins select a.ValueObject.Name).ToList();
List <String> prefixes = (from a in inputJoin.Joins select a.Prefix).ToList();
List <String> aliases = (from a in inputJoin.Joins select a.Alias).ToList();
List <String> onclauses = (from a in inputJoin.Joins select a.Args).ToList();
List <List <String> > columns = (from a in inputJoin.Joins select a.Columns).ToList();
List <JoinType> joinTypes = (from a in inputJoin.Joins select a.Type).ToList();
QueryBuilder Query = new QbFmt("SELECT sub.*\n");
Query.Append($"\t FROM (SELECT\n");
for (int i = 0; i < tables.Count; i++)
{
Query.Append($"\t\t-- Table {tableNames[i]}\n");
var fields = ReflectionTool.FieldsAndPropertiesOf(
tables[i])
.Where((a) => a.GetCustomAttribute(typeof(FieldAttribute)) != null)
.ToArray();
if (!columns[i].Contains("RID"))
{
columns[i].Add("RID");
}
var nonexcl = columns[i];
for (int j = 0; j < nonexcl.Count; j++)
{
Query.Append($"\t\t{prefixes[i]}.{nonexcl[j]} AS {prefixes[i]}_{nonexcl[j]}");
if (true || j < nonexcl.Count - 1 || i < tables.Count - 1)
{
Query.Append(",");
}
Query.Append("\n");
}
Query.Append("\n");
}
Query.Append($"\t\t1 FROM (SELECT * FROM {tableNames[0]}");
if (rootConditions != null)
{
Query.Append("WHERE ");
Query.Append(rootConditions);
}
if (orderingMember != null)
{
Query.Append($"ORDER BY {orderingMember.Name} {otype.ToString().ToUpper()}");
}
if (limit != null)
{
Query.Append($"LIMIT");
if (skip != null)
{
Query.Append($"{skip},");
}
Query.Append($"{limit}");
}
Query.Append($"");
Query.Append($") AS {prefixes[0]}\n");
for (int i = 1; i < tables.Count; i++)
{
Query.Append($"\t\t{"LEFT"} JOIN {tableNames[i]} AS {prefixes[i]} ON {onclauses[i]}\n");
}
if (conditions != null && !conditions.IsEmpty)
{
Query.Append("\tWHERE");
Query.Append(conditions);
}
if (orderingMember != null)
{
Query.Append($"ORDER BY {prefixes[0]}.{orderingMember.Name} {otype.ToString().ToUpper()}");
}
if (limit != null)
{
Query.Append($"LIMIT");
if ((skip ?? 0) > 0)
{
Query.Append($"{skip}, ");
}
Query.Append($"{limit}");
}
Query.Append(") AS sub\n");
return(Query);
}
