tspence / csharp-searchlight Goto Github PK

Should the Equals filter on collections be case insensitive?

The other operations ex: Starts with, ends with, etc are case insensitive.

https://github.com/tspence/csharp-searchlight/blob/main/src/Searchlight/LinqExecutor.cs

Use case: Check out the States API with the filter name eq CALIFORNIA vs name eq California

It would be nice to define a Searchlight extension for Swashbuckle that could automatically fill out help text in Swagger for fields like Include, Filter, and Pagination.

If you specify [SearchlightFlag(Name = "name")]

The application crashes with a null reference exception.

Workaround is to specify
[SearchlightFlag(Name = "name", Aliases = new string[] { })]

Also if the flag name is required it may be useful to have a constructor on the attribute so we can use
[SearchlightFlag("name")]

I was attempting to set up an enum to query on and it doesn't seem like setting the EnumType does any conversion.

Example

public enum CarType
{
    Sedan = 0,
    SUV = 1,
    // etc.
}

[SearchlightModel(OriginalName = "Car", DefaultSort = nameof(Type))]
public class Car
{
    [SearchlightField(FieldType = typeof(int), EnumType = typeof(CarType))]
    public CarType Type { get; set; }
}

If I attempt a filter like type eq Sedan, the filter fails with Searchlight.FieldTypeMismatch. There should be an automatic conversion from the enum name to the underlying value so that using type eq 0 and type eq Sedan are both valid filters.

Searchlight supports child collections, but it doesn't yet support 1-1 mappings (e.g. Parent Company as an optional include fetch for Company).

This doesn't work because the SearchlightCollections attribute doesn't work unless the object is an array.

We should have the ability to support optional 1-1 join fetches.

When a default sort value is not specified in the SearchlightModel attribute, Searchlight creates invalid SQL.

SELECT COUNT(1) AS TotalRecords FROM {table} WHERE {property} = @p1;
SELECT * FROM {table} WHERE {property} = @p1 OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY;

The second SQL statement is missing an order by.

We saw this error during testing:

ProblemId: System.InvalidOperationException at Searchlight.LinqExecutor.BuildOneExpression
OuterMessage: The binary operator LessThanOrEqual is not defined for the types 'System.String' and 'System.String'.

I suspect this means that we need to modify LTE and GTE for strings.

Version 0.9.19 generates an exception when ordering by a DateTime value

Currently this code must be written by all users of Searchlight. Instead, let's offer a Searchlight plugin that accomplishes this task.

It looks like the NuGet package contains Debug assemblies, moving to Release might give a small performance boost.

All Searchlight exceptions should implement a message that contains a human-friendly explanation of what went wrong and what the user needs to do to fix their query.

In the most recent changeset, we added XMLDOC text explaining what each error is. Let's build upon that and add human friendly messages.

The object SearchlightEngine should allow you to define MaximumPageSize across all objects.

• Any FetchRequest that does not specify a page size should have a page size set to MaximumPageSize
• Any FetchRequest where the PageSize value is > MaximumPageSize should throw an error

The following operations are not yet implemented for LINQ:

• IN (as in, member of a set)
• IsNull
• NotEqual
• Between
  Implement them and add tests.

Right now if you do this it crashes

It is possible to write a query using the following syntax:

a = b AND c = d OR e = f

In this construction, it's not clear what order of operations should be followed and the end behavior is undefined and implementation specific. We should consider this an error - all expressions within a single CompoundClause should be required to have identical conjunctions. If they do not, we should throw an exception.

Reasonable people can expect different approaches for case sensitivity. Searchlight should allow you to specify, at the engine level, whether the engine is case sensitive or case insensitive.

We should then throw an exception if the executor does not support the case sensitivity regimen specified at the engine level.

We intend to use Searchlight to define webhook subscriptions. These criteria should permit date math, so you could for example subscribe to notifications for "objects due in the next 30 days" or similar.

The Linq executor is looking for a null page number, but no page number is set to 0, causing the switch statement to improperly handle this scenario.

We should investigate lateral join strategies for implementations on Postgres. This could work similarly to multiple resultsets in SQL Server and allow lower overhead for connected fetches.

The following query should throw a searchlight "bad input" exception, but it does not:

Filter=(Field IN ()) AND OtherField eq 'test'

We should detect when an IN clause has zero items and report an input error.

Seems like the code to construct a searchlight engine doesn't scan the assembly properly if it has child namespaces.

Example:

• A library with the namespace BusinessLayer with class ParentClass
• A sub-namespace called BusinessLayer.Models with models in it
• Construct the searchlight engine using the assembly from ParentClass:

            var engine = new SearchlightEngine().AddAssembly(typeof(ParentClass).Assembly);

• In this case, it does not detect the models inside BusinessLayer.Models.

For some people, specifying PageSize and PageNumber may be friendly and useful. That way they can simply add one or subtract one from the page number and move from page to page.

For other people, specifying Skip and Take may be more convenient. They might want to write long running processes to download records gradually, and they might not want to calculate page size and page numbers. Let's make it possible for Searchlight to support both techniques, and that way API designers can allow either PageSize/PageNumber or Skip/Take (of course, not at the same time!)

We have implementations for SQL-based databases and LINQ.

Let's next add execution options for MongoDB.

Some tables may be larger than others. Let's support defaults and maximums on the table level.

Currently, there is only support for ASC and DESC but there is none for the full word.

Right now Searchlight uses exceptions to indicate that parsing failed. It might be better instead to produce a list of validation results, so that if more than one error could be determined at a time we could use that rather than the exception system.

Today, the code looks like this:

try {
    var request = new FetchRequest() { ... };
    var syntax = engine.Parse(request);
    var sql = syntax.ToSqlServerCommand(true);
} catch (e) { ... }

However, some programmers may not want to use exceptions. Why don't we also support a Validate() or TryParse() method? Then, the code could look like this:

var request = new FetchRequest() { ... };
if (!engine.Validate(request, out errors)) {
  Console.WriteLine(errors.ToString());
}
if (engine.TryParse(request, out syntax)) { 
  // Execute the syntax
}

The QueryCollection method does not make use of the order by clauses in the FetchRequest.

So we should add support for sorting in an in-memory collection.

This can be accomplished by using the LINQ methods
OrderBy: https://docs.microsoft.com/en-us/dotnet/api/system.linq.queryable.orderby?view=net-5.0
OrderByDescending: https://docs.microsoft.com/en-us/dotnet/api/system.linq.queryable.orderbydescending?view=net-5.0

and for additional columns
ThenBy: https://docs.microsoft.com/en-us/dotnet/api/system.linq.queryable.thenby?view=net-5.0
ThenByDescending: https://docs.microsoft.com/en-us/dotnet/api/system.linq.queryable.thenbydescending?view=net-5.0

@AlexRussak mentions yesterday that it would be useful to have a "Now" value, separate from Today or Yesterday. Let's do it!

It looks like the LINQ executor doesn't yet support take and skip. Let's implement that.

For simplicity of queries, support shorthand for dates such as simple english words like TODAY.

Implement date-relative queries so that users have a simple shorthand for defining queries that refer to known points in the past, such as the following:

• Last six months
• Year to date
• This week

In some cases, people may want to use the "$include" parameter to specify options that are not part of searchlight.

Let's add support for a "SearchlightFlag" system that allows users to extend the usage of the "$include" parameter.

We have assertions in our code that the SQL executor produces syntactically valid SQL logic. However, we should really use a toolkit like Postgres2Go and SQLite to add tests for execution against different databases.

We should have an option to enforce NOLOCK or READ UNCOMMITTED for SQL queries via Searchlight.

NOLOCK should be set up on a table-by-table basis, and READ UNCOMMITTED should be at an engine level.

It looks like the Searchlight engine doesn't allow a page size of one.
If I write a Searchlight query and only want the first result back, what's the best way to do that?

The LINQ executor returns an ienumerable. This isn't enough: we need to return the same sort of FetchResult object that the SQL executor returns.

If I submit a filter like CompanyId NOT IN ('9cdc47ce-a02d-4f7a-8d72-e5e9732a3b06')

It gets converted to the SQL

CompanyId IN (@p1)

producing the opposite of the intended results

When fetching records, it would be nice to support filtering for child tables and entities.

For example, if I'm fetching news articles, it would be helpful to be able to fetch comments at the same time. However, a news article can have thousands of comments, so we need some way to filter and paginate them.

Ideally, we should support full Searchlight syntax for this. So the end result should be something like the following:

{
  "table": "articles",
  "filter": "date gt 2021-01-01 and headline eq true",
  "include": "top 10 comments where isDeleted eq false and qualityRating > 4.0,links where isActive eq true"
}