/// <summary>
/// Returns the field as it should be named inside a query. Most of the time, this is the name from the view and nothing else
/// </summary>
/// <param name="field"></param>
/// <param name="request"></param>
/// <returns></returns>
public string ParseField(string field, SearchRequestPlus request, string parseFor = "query")
{
//These special 'extraQueryFields' can be used regardless of any rules
if (request.typeInfo.extraQueryFields.ContainsKey(field))
{
return(request.typeInfo.extraQueryFields[field]);
}
if (!request.typeInfo.fields.ContainsKey(field))
{
throw new ArgumentException($"Field '{field}' not found in type '{request.type}'({request.name})! (in: {parseFor})");
}
if (!request.typeInfo.fields[field].queryable)
{
throw new ArgumentException($"Field '{field}' not queryable in type '{request.type}'({request.name})! (in: {parseFor})");
}
//For now, we outright reject querying against fields you don't explicitly pull. This CAN be made better in the
//future, but for now, I think this is a reasonable limitation to reduce potential bugs
if (!request.requestFields.Contains(field))
{
throw new ArgumentException($"Can't query against field '{field}' without selecting it (in: {parseFor}): Current query system requires fields to be selected in order to be used anywhere else");
}
return(field);
}
public string ReceiveUserLimit(SearchRequestPlus request, string requester)
{
var typeInfo = typeService.GetTypeInfo <Message>();
//It is OK if requester is 0, because it'st he same check as the previous...
return($@"receiveUserId = 0 or receiveUserId = {requester}");
}
/// <summary>
/// Compute the VIEW fields (named output, not db columns) which were requested in the given SearchRequestPlus
/// </summary>
/// <param name="r"></param>
/// <returns></returns>
public List <string> ComputeRealFields(SearchRequestPlus r)
{
bool inverted = false;
if (r.fields.StartsWith("~"))
{
inverted = true;
r.fields = r.fields.TrimStart('~');
}
var fields = r.fields.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
//Redo fieldlist if they asked for special formats
if (r.fields == "*")
{
fields = new List <string>(r.typeInfo.fields.Keys);
}
if (inverted)
{
fields = r.typeInfo.fields.Keys.Except(fields).ToList();
}
//Check for bad fields. NOTE: this means we can guarantee that future checks against the typeinfo are safe... or can we?
//What about parsing fields from the query string?
foreach (var field in fields)
{
if (!r.typeInfo.fields.ContainsKey(field) && field != CountField)
{
throw new ArgumentException($"Unknown field {field} in request {r.name}");
}
}
return(fields);
}
/// <summary>
/// Assuming a query that can be limited simply, this adds the necessary LIMIT, OFFSET,
/// and ORDER BY clauses. Most queries can use this function.
/// </summary>
/// <param name="queryStr"></param>
/// <param name="r"></param>
/// <param name="parameters"></param>
public void AddStandardFinalLimit(StringBuilder queryStr, SearchRequestPlus r, Dictionary <string, object> parameters)
{
if (!string.IsNullOrEmpty(r.order))
{
var orders = r.order.Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
//Can't parameterize the column... inserting directly; scary. Also, immediately adding the order by even
//though we don't know what we're doing yet... eehhh should be fine?
queryStr.Append("ORDER BY ");
for (int i = 0; i < orders.Length; i++)
{
var order = orders[i];
var descending = false;
if (order.EndsWith(DescendingAppend))
{
descending = true;
order = order.Substring(0, order.Length - DescendingAppend.Length);
}
//We don't need the result, just the checking
var parsedOrder = ParseField(order, r);
queryStr.Append(order);
if (r.typeInfo.fields[order].fieldType == typeof(string))
{
queryStr.Append(" COLLATE NOCASE ");
}
if (descending)
{
queryStr.Append(" DESC ");
}
if (i < orders.Length - 1)
{
queryStr.Append(", ");
}
else
{
queryStr.Append(" ");
}
}
}
//ALWAYS need limit if you're doing offset, just easier to include it. -1 is magic
var limitKey = r.UniqueRequestKey("limit");
queryStr.Append($"LIMIT @{limitKey} ");
parameters.Add(limitKey, r.limit); //WARN: this modifies the parameters!
if (r.skip > 0)
{
var skipKey = r.UniqueRequestKey("skip");
queryStr.Append($"OFFSET @{skipKey} ");
parameters.Add(skipKey, r.skip);
}
}
public void FullParseRequest_LiteralSugar(string query, object value, bool allowed)
{
var request = new SearchRequest()
{
name = "contentTest",
type = "content",
fields = "*",
query = query
};
var values = new Dictionary <string, object>();
var result = new SearchRequestPlus();
var work = new Action(() => result = service.FullParseRequest(request, values));
if (allowed)
{
work();
Assert.NotEmpty(result.computedSql);
Assert.NotEmpty(values);
Assert.Contains(value, values.Values);
}
else
{
Assert.ThrowsAny <ParseException>(work);
}
}
public string ParseMacro(string m, string a, SearchRequestPlus request, Dictionary <string, object> parameters)
{
if (!StandardMacros.ContainsKey(m))
{
throw new ArgumentException($"Macro {m} not found for request '{request.name}'");
}
var macDef = StandardMacros[m];
var args = a.Split(",", StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
if (args.Count != macDef.argumentTypes.Count)
{
throw new ArgumentException($"Expected {macDef.argumentTypes.Count} arguments for macro {m} in '{request.name}', found {args.Count}");
}
//Parameters start with the request, always
var argVals = new List <object?>()
{
request
};
for (var i = 0; i < args.Count; i++)
{
var expArgType = macDef.argumentTypes[i];
var ex = new ArgumentException($"Argument #{i + 1} in macro {m} for request '{request.name}': expected {macDef.argumentTypes[i]} type");
if (expArgType == MacroArgumentType.value)
{
if (!args[i].StartsWith("@"))
{
throw ex;
}
argVals.Add(ParseValue(args[i], request, parameters));
}
else if (expArgType == MacroArgumentType.field)
{
if (args[i].StartsWith("@"))
{
throw ex;
}
argVals.Add(ParseField(args[i], request));
}
else if (expArgType == MacroArgumentType.fieldImmediate)
{
if (args[i].StartsWith("@"))
{
throw ex;
}
argVals.Add(args[i]); //Use the IMMEDIATE value!
}
else
{
throw new InvalidOperationException($"Unknown macro argument type {macDef.argumentTypes[i]} in request {request.name}");
}
}
//At this point, we have the macro function info, so we can just call it
return((string)(macDef.macroMethod.Invoke(this, argVals.ToArray()) ??
throw new InvalidOperationException($"Macro method for macro {m} returned null in request {request.name}!")));
}
// ------------
// -- MACROS --
// ------------
public string KeywordSearchGeneric(SearchRequestPlus request, string value, string op, string contentop)
{
var typeInfo = typeService.GetTypeInfo <ContentKeyword>();
return($@"id {contentop}
(select {nameof(ContentKeyword.contentId)}
from {typeInfo.selfDbInfo?.modelTable}
where {nameof(ContentKeyword.value)} {op} {value}
)");
}
public string InGroupMacro(SearchRequestPlus request, string group)
{
var typeInfo = typeService.GetTypeInfo <UserRelation>();
return($@"id in
(select {nameof(Db.UserRelation.userId)}
from {typeInfo.selfDbInfo?.modelTable}
where {nameof(Db.UserRelation.relatedId)} = {group}
)");
}
public string ParentQueryGenericMacro(SearchRequestPlus request, bool parents)
{
var typeInfo = typeService.GetTypeInfo <Content>();
return($@"id {(parents ? "" : "not")} in
(select {nameof(Content.parentId)}
from {typeInfo.selfDbInfo?.modelTable}
group by {nameof(Content.parentId)}
)");
}
public string ValueKeySearchGeneric(SearchRequestPlus request, string key, string op, string contentop)
{
var typeInfo = typeService.GetTypeInfo <ContentValue>();
return($@"id {contentop}
(select {nameof(ContentValue.contentId)}
from {typeInfo.selfDbInfo?.modelTable}
where {nameof(ContentValue.key)} {op} {key}
)");
}
public string BasicHistory(SearchRequestPlus request)
{
var typeInfo = typeService.GetTypeInfo <Content>();
return($@"contentId in
(select {nameof(Content.id)}
from {typeInfo.selfDbInfo?.modelTable}
where contentType = {(int)InternalContentType.page}
and deleted = 0
)");
}
public string OnlyUserpage(SearchRequestPlus request, string userIdValue)
{
var typeInfo = typeService.GetTypeInfo <Content>();
return($@"id =
(select min({nameof(Content.id)})
from {typeInfo.selfDbInfo?.modelTable}
where {nameof(Content.contentType)} = {(long)InternalContentType.userpage}
and {nameof(Content.createUserId)} = {userIdValue}
and deleted = 0
)");
}
public void ComputeRealFields_Star()
{
var req = new SearchRequestPlus()
{
fields = "*",
typeInfo = typeInfoService.GetTypeInfo <UserView>()
};
var fields = service.ComputeRealFields(req);
Assert.True(new HashSet <string>(req.typeInfo.fields.Keys).SetEquals(fields), "Star didn't generate all queryable fields in ComputeRealFields!");
}
public void ComputeRealFields_NoChange()
{
var req = new SearchRequestPlus()
{
fields = "id, username",
typeInfo = typeInfoService.GetTypeInfo <UserView>()
};
var fields = service.ComputeRealFields(req);
Assert.True(fields.SequenceEqual(new [] { "id", "username" }), "Simple fields were not preserved in ComputeRealFields!");
}
public void ComputeRealFields_Inverted()
{
var req = new SearchRequestPlus()
{
fields = "~ id, username", //This also makes sure spaces are trimmed
typeInfo = typeInfoService.GetTypeInfo <UserView>()
};
var fields = service.ComputeRealFields(req);
var realSet = req.typeInfo.fields.Keys.Except(new[] { "id", "username" });
Assert.True(new HashSet <string>(realSet).SetEquals(fields), "Inverted didn't generate correct set in ComputeRealFields!");
}
public string ParseValue(string value, SearchRequestPlus request, Dictionary <string, object> parameters)
{
var realValName = value.TrimStart('@');
var newName = value.Replace("@", "").Replace(".", "_");
if (!parameters.ContainsKey(newName))
{
var valueObject = FindValueObject(value, parameters);
parameters.Add(newName, valueObject);
}
return($"@{newName}");
}
//NOTE: Even though these might say "0" references, they're all used by the macro system!
//For now, this is JUST read limit!!
public string PermissionLimit(SearchRequestPlus request, string requesters, string idField, string type)
{
var typeInfo = typeService.GetTypeInfo <ContentPermission>();
var checkCol = permissionService.ActionToColumn(permissionService.StringToAction(type));
//Note: we're checking createUserId against ALL requester values they gave us! This is OK, because the
//additional values are things like 0 or their groups, and groups can't create content
return($@"({idField} in
(select {nameof(ContentPermission.contentId)}
from {typeInfo.selfDbInfo?.modelTable}
where {nameof(ContentPermission.userId)} in {requesters}
and `{checkCol}` = 1
))"); //NOTE: DO NOT CHECK CREATE USER! ALL PERMISSIONS ARE NOW IN THE TABLE! NOTHING IMPLIED!
}
public string ActiveBansMacro(SearchRequestPlus request)
{
var typeInfo = typeService.GetTypeInfo <Ban>();
var now = DateTime.UtcNow.ToString(Constants.DateFormat);
//Active bans are such that the expire date is in the future, but bans don't stack!
//Only the VERY LAST ban is the active one (hence the max). This could be a "none" type,
//so we filter that out in the outside
return($@"{nameof(Ban.type)} <> {(int)BanType.none} and id in
(select max({nameof(Ban.id)})
from {typeInfo.selfDbInfo?.modelTable}
where {nameof(Ban.expireDate)} > '{now}'
group by {nameof(Ban.bannedUserId)}
)");
}
/// <summary>
/// This method adds a 'standard' select for regular searches against simple
/// single table queries. For instance, it might be "SELECT id,username FROM users "
/// </summary>
/// <param name="queryStr"></param>
/// <param name="r"></param>
public void AddStandardSelect(StringBuilder queryStr, SearchRequestPlus r)
{
//This enforces the "count is the only field" thing
var fieldSelect =
r.requestFields.Where(x => x == CountField || r.typeInfo.fields[x].queryBuildable).Select(x => StandardFieldSelect(x, r)).ToList();
// r.requestFields.Contains(CountField) ?
// new List<string> { CountSelect } :
var selectFrom = r.typeInfo.selectFromSql;
if (string.IsNullOrWhiteSpace(selectFrom))
{
throw new InvalidOperationException($"Standard select {r.type} doesn't define a 'select from' statement in request {r.name}, this is a program error!");
}
queryStr.Append("SELECT ");
queryStr.Append(string.Join(",", fieldSelect));
queryStr.Append(" FROM ");
queryStr.Append(selectFrom);
queryStr.Append(" "); //To be nice, always end in space?
}
/// <summary>
/// Return the field selector for the given field. For instance, it might be a
/// simple "username", or it might be "(registered IS NULL) AS registered" etc.
/// </summary>
/// <remarks> MOST fields should work with this function, either it's the same name,
/// or it's a simple remap from an attribute, or it's slightly complex but stored
/// in our dictionary of remaps </remarks>
/// <param name="fieldName"></param>
/// <param name="r"></param>
/// <returns></returns>
public string StandardFieldSelect(string fieldName, SearchRequestPlus r)
{
//Just a simple count bypass; counts are a special thing that can be added to any query, so this is safe.
if (fieldName == CountField)
{
return(CountSelect);
}
var c = r.typeInfo.fields[fieldName].fieldSelect;
if (string.IsNullOrWhiteSpace(c))
{
throw new InvalidOperationException($"Can't select field '{fieldName}' in base query: no 'select' sql defined for that field!");
}
else if (c == fieldName)
{
return(c);
}
else
{
return($"({c}) AS {fieldName}");
}
}
/// <summary>
/// This method performs a "standard" user-query parse and adds the generated sql to the
/// given queryStr, along with any missing parameters that were able to be computed.
/// </summary>
/// <param name="queryStr"></param>
/// <param name="request"></param>
/// <param name="parameters"></param>
public string CreateStandardQuery(StringBuilder queryStr, SearchRequestPlus request, Dictionary <string, object> parameters)
{
//Not sure if the "query" is generic enough to be placed outside of standard... mmm maybe
try
{
var parseResult = parser.ParseQuery(request.query,
f => ParseField(f, request),
v => ParseValue(v, request, parameters),
(m, a) => ParseMacro(m, a, request, parameters)
);
return(parseResult);
}
//We know how to handle these exceptions
catch (ParseException) { throw; }
catch (ArgumentException) { throw; }
catch (Exception ex)
{
//Convert to argument exception so the user knows what's up. Nothing that happens here
//is due to a database or other "internal" server error (other than stupid messups on my part)
logger.LogWarning($"Unknown exception during query parse: {ex}");
throw new ParseException($"Parse error: {ex.Message}");
}
}
public string UserTypeMacro(SearchRequestPlus request, string type)
{
return(EnumMacroSearch <UserType>(type));
}
public string KeywordIn(SearchRequestPlus request, string value) => KeywordSearchGeneric(request, value, "in", "in");
public string ValueIn(SearchRequestPlus request, string key, string value) => ValueSearchGeneric(request, key, value, "in", "in");
public string ValueKeyNotLike(SearchRequestPlus request, string key)
{
return(ValueKeySearchGeneric(request, key, "LIKE", "not in"));
}
public string OnlyParents(SearchRequestPlus request) => ParentQueryGenericMacro(request, true);
public string OnlyNotParents(SearchRequestPlus request) => ParentQueryGenericMacro(request, false);
public string NullMacro(SearchRequestPlus request, string field)
{
return($"{field} IS NULL");
}
public string NotDeletedMacro(SearchRequestPlus request)
{
return("deleted = 0");
}
//WARN: should this be part of query builder?? who knows... it kinda doesn't need to be, it's not a big deal.
public async Task AddExtraFields(SearchRequestPlus r, QueryResultSet result)
{
if (!r.requestFields.Contains(nameof(IIdView.id)))
{
logger.LogDebug($"Skipping extra field addition for request '{r.name}'({r.requestId}), it is missing the {nameof(IIdView.id)} field");
return;
}
//We know that at this point, it's safe to index
var index = IndexResults(result);
//This adds groups to users (if requested)
if (r.requestType == RequestType.user)
{
const string groupskey = nameof(UserView.groups);
const string usersInGroupkey = nameof(UserView.usersInGroup);
const string ridkey = nameof(Db.UserRelation.relatedId);
const string uidkey = nameof(Db.UserRelation.userId);
const string typekey = nameof(Db.UserRelation.type);
if (r.requestFields.Contains(groupskey))
{
var relinfo = typeService.GetTypeInfo <Db.UserRelation>();
var groups = await dbcon.QueryAsync <Db.UserRelation>($"select {ridkey},{uidkey} from {relinfo.selfDbInfo?.modelTable} where {typekey} = @type and {uidkey} in @ids",
new { ids = index.Keys, type = (int)UserRelationType.in_group });
var lookup = groups.ToLookup(x => x.userId);
foreach (var u in index)
{
u.Value[groupskey] = lookup.Contains(u.Key) ? lookup[u.Key].Select(x => x.relatedId).ToList() : new List <long>();
}
}
if (r.requestFields.Contains(usersInGroupkey))
{
var relinfo = typeService.GetTypeInfo <Db.UserRelation>();
var groups = await dbcon.QueryAsync <Db.UserRelation>($"select {ridkey},{uidkey} from {relinfo.selfDbInfo?.modelTable} where {typekey} = @type and {ridkey} in @ids",
new { ids = index.Keys, type = (int)UserRelationType.in_group });
var lookup = groups.ToLookup(x => x.relatedId);
foreach (var u in index)
{
u.Value[usersInGroupkey] = lookup.Contains(u.Key) ? lookup[u.Key].Select(x => x.userId).ToList() : new List <long>();
}
}
}
if (r.requestType == RequestType.message)
{
const string cidkey = nameof(Db.MessageValue.messageId);
const string valkey = nameof(MessageView.values);
const string uidskey = nameof(MessageView.uidsInText);
const string textkey = nameof(MessageView.text);
if (r.requestFields.Contains(valkey))
{
var valinfo = typeService.GetTypeInfo <Db.MessageValue>();
var values = await dbcon.QueryAsync <Db.MessageValue>($"select {cidkey},key,value from {valinfo.selfDbInfo?.modelTable} where {cidkey} in @ids",
new { ids = index.Keys });
var lookup = values.ToLookup(x => x.messageId);
foreach (var c in index)
{
c.Value[valkey] = lookup.Contains(c.Key) ? lookup[c.Key].ToDictionary(x => x.key, y => JsonConvert.DeserializeObject(y.value)) : new Dictionary <string, object?>();
}
}
if (r.requestFields.Contains(uidskey) && r.requestFields.Contains(textkey))
{
foreach (var c in index)
{
c.Value[uidskey] = Regex.Matches((string)c.Value[textkey], @"%(\d+)%").Select(x => long.Parse(x.Groups[1].Value)).ToList();
}
}
}
if (r.requestType == RequestType.content)
{
const string keykey = nameof(ContentView.keywords);
const string valkey = nameof(ContentView.values);
const string permkey = nameof(ContentView.permissions);
const string votekey = nameof(ContentView.votes);
const string cidkey = nameof(Db.ContentKeyword.contentId); //WARN: assuming it's the same for all!
var ids = index.Keys.ToList();
var voteinfo = typeService.GetTypeInfo <Db.ContentVote>();
if (r.requestFields.Contains(keykey))
{
var keyinfo = typeService.GetTypeInfo <Db.ContentKeyword>();
var keywords = await dbcon.QueryAsync <Db.ContentKeyword>($"select {cidkey},value from {keyinfo.selfDbInfo?.modelTable} where {cidkey} in @ids",
new { ids = ids });
var lookup = keywords.ToLookup(x => x.contentId);
foreach (var c in index)
{
c.Value[keykey] = lookup.Contains(c.Key) ? lookup[c.Key].Select(x => x.value).ToList() : new List <string>();
}
}
if (r.requestFields.Contains(valkey))
{
var valinfo = typeService.GetTypeInfo <Db.ContentValue>();
var values = await dbcon.QueryAsync <Db.ContentValue>($"select {cidkey},key,value from {valinfo.selfDbInfo?.modelTable} where {cidkey} in @ids",
new { ids = ids });
var lookup = values.ToLookup(x => x.contentId);
foreach (var c in index)
{
c.Value[valkey] = lookup.Contains(c.Key) ? lookup[c.Key].ToDictionary(x => x.key, y => JsonConvert.DeserializeObject(y.value)) : new Dictionary <string, object?>();
}
}
if (r.requestFields.Contains(permkey))
{
var perminfo = typeService.GetTypeInfo <Db.ContentPermission>();
var permissions = await dbcon.QueryAsync($"select * from {perminfo.selfDbInfo?.modelTable} where {cidkey} in @ids",
new { ids = ids });
var lookup = permissions.ToLookup(x => x.contentId);
foreach (var c in index)
{
c.Value[permkey] = permissionService.ResultToPermissions(lookup.Contains(c.Key) ? lookup[c.Key] : new List <dynamic>());
}
}
if (r.requestFields.Contains(votekey))
{
var votes = await dbcon.QueryAsync($"select {cidkey}, vote, count(*) as count from {voteinfo.selfDbInfo?.modelTable} where {cidkey} in @ids group by {cidkey}, vote",
new { ids = ids });
var displayVotes = Enum.GetValues <VoteType>().Where(x => x != VoteType.none);
var lookup = votes.ToLookup(x => x.contentId);
foreach (var c in index)
{
var cvotes = lookup.Contains(c.Key) ? lookup[c.Key].ToDictionary(x => (VoteType)x.vote, y => y.count) : new Dictionary <VoteType, dynamic>();
foreach (var v in displayVotes)
{
if (!cvotes.ContainsKey(v))
{
cvotes.Add(v, 0);
}
}
c.Value[votekey] = cvotes;
}
}
}
}
