private static void AppendLimits(SqlExpression <From> q, IQueryDb dto, IAutoQueryOptions options)
{
var maxLimit = options?.MaxLimit;
var take = dto.Take ?? maxLimit;
if (take > maxLimit)
{
take = maxLimit;
}
q.Limit(dto.Skip, take);
if (dto.OrderBy != null)
{
var fieldNames = dto.OrderBy.Split(FieldSeperators, StringSplitOptions.RemoveEmptyEntries);
q.OrderByFields(fieldNames);
}
else if (dto.OrderByDesc != null)
{
var fieldNames = dto.OrderByDesc.Split(FieldSeperators, StringSplitOptions.RemoveEmptyEntries);
q.OrderByFieldsDescending(fieldNames);
}
else if ((dto.Skip != null || dto.Take != null) &&
(options != null && options.OrderByPrimaryKeyOnLimitQuery))
{
q.OrderByFields(typeof(From).GetModelMetadata().PrimaryKey);
}
}
public ISqlExpression CreateQuery(
IDbConnection db,
IQuery model,
Dictionary <string, string> dynamicParams,
IAutoQueryOptions options = null)
{
dynamicParams = new Dictionary <string, string>(dynamicParams, StringComparer.OrdinalIgnoreCase);
var q = db.From <From>();
if (options != null && options.EnableSqlFilters)
{
AppendSqlFilters(q, model, dynamicParams, options);
}
AppendJoins(q, model);
AppendLimits(q, model, options);
var dtoAttr = model.GetType().FirstAttribute <QueryAttribute>();
var defaultTerm = dtoAttr != null && dtoAttr.DefaultTerm == QueryTerm.Or ? "OR" : "AND";
AppendTypedQueries(q, model, dynamicParams, defaultTerm, options);
if (options != null && options.EnableUntypedQueries)
{
AppendUntypedQueries(q, dynamicParams, defaultTerm, options);
}
if (defaultTerm == "OR" && q.WhereExpression == null)
{
q.Where("1=0"); //Empty OR queries should be empty
}
return(q);
}
public ISqlExpression AddToQuery(
ISqlExpression query,
IQueryDb dto,
Dictionary <string, string> dynamicParams,
IAutoQueryOptions options = null)
{
dynamicParams = new Dictionary <string, string>(dynamicParams, StringComparer.OrdinalIgnoreCase);
var q = (SqlExpression <From>)query;
if (options != null && options.EnableSqlFilters)
{
AppendSqlFilters(q, dto, dynamicParams, options);
}
AppendJoins(q, dto);
AppendLimits(q, dto, options);
var dtoAttr = dto.GetType().FirstAttribute <QueryDbAttribute>();
var defaultTerm = dtoAttr != null && dtoAttr.DefaultTerm == QueryTerm.Or ? "OR" : "AND";
var aliases = new Dictionary <string, string>(StringComparer.OrdinalIgnoreCase);
var props = typeof(From).GetProperties();
foreach (var pi in props)
{
var attr = pi.FirstAttribute <DataMemberAttribute>();
if (attr?.Name == null)
{
continue;
}
aliases[attr.Name] = pi.Name;
}
AppendTypedQueries(q, dto, dynamicParams, defaultTerm, options, aliases);
if (options?.EnableUntypedQueries == true && dynamicParams.Count > 0)
{
AppendUntypedQueries(q, dynamicParams, defaultTerm, options, aliases);
}
if (defaultTerm == "OR" && q.WhereExpression == null)
{
q.Where("1=0"); //Empty OR queries should be empty
}
if (!string.IsNullOrEmpty(dto.Fields))
{
var fields = dto.Fields.Split(',')
.Where(x => x.Trim().Length > 0)
.Map(x => x.Trim());
q.Select(fields.ToArray());
}
return(q);
}
private static MatchQuery GetQueryMatch(SqlExpression <From> q, string name, IAutoQueryOptions options)
{
if (options == null)
{
return(null);
}
var match = options.IgnoreProperties == null || !options.IgnoreProperties.Contains(name)
? q.FirstMatchingField(name) ?? (name.EndsWith(Pluralized) ? q.FirstMatchingField(name.Substring(0, name.Length - 1)) : null)
: null;
if (match == null)
{
foreach (var startsWith in options.StartsWithConventions)
{
if (name.Length <= startsWith.Key.Length || !name.StartsWith(startsWith.Key))
{
continue;
}
var field = name.Substring(startsWith.Key.Length);
match = q.FirstMatchingField(field) ?? (field.EndsWith(Pluralized) ? q.FirstMatchingField(field.Substring(0, field.Length - 1)) : null);
if (match != null)
{
return(new MatchQuery(match, startsWith.Value));
}
}
}
if (match == null)
{
foreach (var endsWith in options.EndsWithConventions)
{
if (name.Length <= endsWith.Key.Length || !name.EndsWith(endsWith.Key))
{
continue;
}
var field = name.Substring(0, name.Length - endsWith.Key.Length);
match = q.FirstMatchingField(field) ?? (field.EndsWith(Pluralized) ? q.FirstMatchingField(field.Substring(0, field.Length - 1)) : null);
if (match != null)
{
return(new MatchQuery(match, endsWith.Value));
}
}
}
return(match != null
? new MatchQuery(match, null)
: null);
}
public ISqlExpression CreateQuery(
IDbConnection db,
IQuery model,
Dictionary <string, string> dynamicParams,
IAutoQueryOptions options = null)
{
dynamicParams = new Dictionary <string, string>(dynamicParams, StringComparer.OrdinalIgnoreCase);
var q = db.From <From>();
if (options != null && options.EnableSqlFilters)
{
AppendSqlFilters(q, model, dynamicParams, options);
}
AppendJoins(q, model);
AppendLimits(q, model, options);
var dtoAttr = model.GetType().FirstAttribute <QueryAttribute>();
var defaultTerm = dtoAttr != null && dtoAttr.DefaultTerm == QueryTerm.Or ? "OR" : "AND";
var aliases = new Dictionary <string, string>(StringComparer.InvariantCultureIgnoreCase);
var props = typeof(From).GetProperties();
foreach (var pi in props)
{
var attr = pi.FirstAttribute <DataMemberAttribute>();
if (attr == null || attr.Name == null)
{
continue;
}
aliases[attr.Name] = pi.Name;
}
AppendTypedQueries(q, model, dynamicParams, defaultTerm, options, aliases);
if (options != null && options.EnableUntypedQueries)
{
AppendUntypedQueries(q, dynamicParams, defaultTerm, options, aliases);
}
if (defaultTerm == "OR" && q.WhereExpression == null)
{
q.Where("1=0"); //Empty OR queries should be empty
}
return(q);
}
private static MatchQuery GetQueryMatch(SqlExpression<From> q, string name, IAutoQueryOptions options, Dictionary<string,string> aliases)
{
var match = GetQueryMatch(q, name, options);
if (match == null)
{
string alias;
if (aliases.TryGetValue(name, out alias))
match = GetQueryMatch(q, alias, options);
if (match == null && JsConfig.EmitLowercaseUnderscoreNames && name.Contains("_"))
match = GetQueryMatch(q, name.Replace("_", ""), options);
}
return match;
}
private static MatchQuery GetQueryMatch(SqlExpression <From> q, string name, IAutoQueryOptions options, Dictionary <string, string> aliases)
{
var match = GetQueryMatch(q, name, options);
if (match == null)
{
if (aliases.TryGetValue(name, out var alias))
{
match = GetQueryMatch(q, alias, options);
}
if (match == null && JsConfig.TextCase == TextCase.SnakeCase && name.Contains("_"))
{
match = GetQueryMatch(q, name.Replace("_", ""), options);
}
}
return(match);
}
private static void AppendUntypedQueries(SqlExpression<From> q, Dictionary<string, string> dynamicParams, string defaultTerm, IAutoQueryOptions options, Dictionary<string, string> aliases)
{
foreach (var entry in dynamicParams)
{
var name = entry.Key;
var match = GetQueryMatch(q, name, options, aliases);
if (match == null)
continue;
var implicitQuery = match.ImplicitQuery;
var quotedColumn = q.DialectProvider.GetQuotedColumnName(match.ModelDef, match.FieldDef);
var strValue = entry.Value;
var fieldType = match.FieldDef.FieldType;
var isMultiple = (implicitQuery != null && (implicitQuery.ValueStyle > ValueStyle.Single))
|| string.Compare(name, match.FieldDef.Name + Pluralized, StringComparison.OrdinalIgnoreCase) == 0;
var value = isMultiple ?
TypeSerializer.DeserializeFromString(strValue, Array.CreateInstance(fieldType, 0).GetType())
: fieldType == typeof(string) ?
strValue
: fieldType.IsValueType && !fieldType.IsEnum ?
Convert.ChangeType(strValue, fieldType) :
TypeSerializer.DeserializeFromString(strValue, fieldType);
AddCondition(q, defaultTerm, quotedColumn, value, implicitQuery);
}
}
private static void AppendTypedQueries(SqlExpression<From> q, IQuery model, Dictionary<string, string> dynamicParams, string defaultTerm, IAutoQueryOptions options, Dictionary<string, string> aliases)
{
foreach (var entry in PropertyGetters)
{
var name = entry.Key;
QueryFieldAttribute implicitQuery;
QueryFieldMap.TryGetValue(name, out implicitQuery);
if (implicitQuery != null && implicitQuery.Field != null)
name = implicitQuery.Field;
var match = GetQueryMatch(q, name, options, aliases);
if (match == null)
continue;
if (implicitQuery == null)
implicitQuery = match.ImplicitQuery;
var quotedColumn = q.DialectProvider.GetQuotedColumnName(match.ModelDef, match.FieldDef);
var value = entry.Value(model);
if (value == null)
continue;
dynamicParams.Remove(entry.Key);
AddCondition(q, defaultTerm, quotedColumn, value, implicitQuery);
}
}
private void AppendSqlFilters(SqlExpression<From> q, IQuery model, Dictionary<string, string> dynamicParams, IAutoQueryOptions options)
{
string select, from, where;
dynamicParams.TryGetValue("_select", out select);
if (select != null)
{
dynamicParams.Remove("_select");
select.SqlVerifyFragment(options.IllegalSqlFragmentTokens);
q.Select(select);
}
dynamicParams.TryGetValue("_from", out from);
if (from != null)
{
dynamicParams.Remove("_from");
from.SqlVerifyFragment(options.IllegalSqlFragmentTokens);
q.From(from);
}
dynamicParams.TryGetValue("_where", out where);
if (where != null)
{
dynamicParams.Remove("_where");
where.SqlVerifyFragment(options.IllegalSqlFragmentTokens);
q.Where(where);
}
}
private static void AppendTypedQueries(SqlExpression <From> q, IQueryDb dto, Dictionary <string, string> dynamicParams, string defaultTerm, IAutoQueryOptions options, Dictionary <string, string> aliases)
{
foreach (var entry in PropertyGetters)
{
var name = entry.Key.LeftPart('#');
dynamicParams.Remove(name);
QueryFieldMap.TryGetValue(name, out var implicitQuery);
if (implicitQuery?.Field != null)
{
name = implicitQuery.Field;
}
var match = GetQueryMatch(q, name, options, aliases);
if (match == null)
{
continue;
}
implicitQuery = implicitQuery == null
? match.ImplicitQuery
: implicitQuery.Combine(match.ImplicitQuery);
var quotedColumn = q.DialectProvider.GetQuotedColumnName(match.ModelDef, match.FieldDef);
var value = entry.Value(dto);
if (value == null)
{
continue;
}
AddCondition(q, defaultTerm, quotedColumn, value, implicitQuery);
}
}