Pure.RelationalSchema.Storage.PureQL.Projection 0.1.0-preview.4.1.0

Pure.RelationalSchema.Storage.PureQL.Projection

Compiles PureQL queries over relational schema datasets into one LINQ expression tree: a PureQLQuery from PureQL.CSharp.Model becomes an IStoredTableDataSet whose IQueryable<IRow> tree a LINQ provider can translate to SQL, and which runs in memory when enumerated.

Overview

Pure.RelationalSchema.Storage.PureQL.Projection is an interpreter for the PureQL specification 0.1.0-preview.2.0.1 over the Pure relational storage model. Given a collection of IStoredSchemaDataSet objects and a PureQLQuery, it produces an IStoredTableDataSet whose rows are the query's result under the specification's semantics.

The query is compiled when the projection is built to a single IQueryable<IRow> expression tree made of standard query operators over one root per stored table (see Expression tree). Enumerating the projection runs that tree in memory with LINQ to Objects; a provider that translates the tree (to PostgreSQL, across several databases) reads projection.Expression instead.

Public API

PureQLProjection

The only public type. Implements IStoredTableDataSet, IAsyncEnumerable<IRow> and IQueryable<IRow>.

What is supported

Every clause and operator of the specification: from and joins (inner, left, right, full, aliases and self-joins), where, groupBy over any row expression with keys referenced by alias, having, select, aggregate (total queries), orderBy over any expression, distinctBy, pagination, and subqueries read through from, join and in. All operators in the row, projection, group and total contexts are supported, including aggregates with predicate and over, and comparisons, min and max over every type.

A plain query returns one row per row left by where, a total query exactly one row (even over no rows) and a grouped query one row per group. distinctBy runs after orderBy and keeps, of the rows equal on the listed columns, the first in order.

The semantics are the specification's: null equals null and sorts first, ordering comparisons against null are false, lifted operators are null on a null operand, integer is a checked 64-bit integer, decimal is exact (System.Decimal), strings compare by code point, a datetime is an instant written in UTC, and seconds and time / datetime averages round half away from zero to the microsecond. Overflow, division by zero and dates outside the calendar fail the query with the corresponding .NET exception.

The checks the specification leaves to the interpreter run when the projection is built, and a failing query raises ArgumentException: entities, subqueries, sources and fields exist; each field declares its column's type and, after outer joins, its nullability; group key references name a key and repeat its type; source names, group key aliases, column aliases and subquery names are unique; distinctBy lists only column aliases; a subquery reads only subqueries declared before it.

Expression tree

projection.Expression is built only from constructs a SQL provider translates: Queryable operators (Enumerable ones only over a group or a list), members and methods of the BCL's value types, strings and Math, value tuples, constants and captured parameters. Nothing in it is compiled, invoked or evaluated on the client except the result rows, built by the last Select. Its shape is fixed:

scan(from) [.Where] (.SelectMany(join))* [.Where(rest of where)]
  plain:   [.OrderBy/ThenBy] .Select(columns)
  grouped: .GroupBy(keys) [.Where(having)] [.OrderBy/ThenBy] .Select(columns)
  total:   one-row constant .Select(aggregates over the rows)
[.DistinctBy(columns)] [.Skip] [.Take]

• Sources. Every stored table is its own root, Constant(IStoredTableDataSet) typed IQueryable<IRow>, so a provider can tell tables (and their databases) apart and split the tree there. A table is always scanned as root.Select(row => (field, ...)), reading only the fields the query reads; a field read is IsNullOrEmpty(row.Cells[column].Value.TextValue) ? null : Parse(row.Cells[column].Value.TextValue, ...), the IColumn a constant. A subquery is inlined where it is read.
• Rows are value tuples: a source row holds its fields, a joined row one source row per source, a group key one value per key, a query result one value per column. Every value is nullable: long? (integer), decimal?, string, bool?, DateOnly?, TimeOnly?, DateTime? (UTC), Guid?.
• Joins are a correlated SelectMany (DefaultIfEmpty for an outer side); full is the left join Concat the unmatched joined rows (!Any(...)). An unmatched side is the default tuple: all nulls.
• Aggregates are LINQ's over the group or, over all rows, over the query's rows, which are one shared subtree. A total query reads a one-row constant source, as SQL's SELECT without FROM.
• Semantics are C#'s, which match the specification: null == null, an ordering comparison with null is false, lifted operators. Strings compare with string.CompareOrdinal and order with StringComparer.Ordinal (SQL's "C" collation, code point order); ascending order puts null first. Parameters are captured variables, the Value of a constant holding the parameter, as C# closures are; reading one fails while parameters cannot be bound.

Built in, before any provider sees it:

• constants fold (an operation that fails, like 1 / 0, is kept and fails the query), true/false fold out of conditions, lifting is dropped where a value cannot be null and an operation over a null literal is null;
• where is split into its and-ed conditions, each moved to the scan of the source it reads, or into the join that first sees every source it reads, unless an outer join may leave one of them without a row; a condition on a subquery source moves into the subquery (onto its having when it reads an aggregate);
• adjacent Wheres merge, Where(true) and identity Selects are dropped, consecutive Selects are one, and pagination comes last;
• a repeated order key or one that is the same for every row is dropped, a distinctBy over every group key is dropped, and a subquery read only by in (without pagination) is neither ordered nor distinctBy-filtered.

Run in memory, each stored table is read once per enumeration, a query nested in a lambda that reads nothing of it (a joined source, an in list, an aggregate over all rows) is computed once, and strings order by code point.

Storage mapping

Empty cell text is NULL. A stored datetime without an offset is read as UTC. Result columns are typed long for integer and double for decimal; result cells hold invariant text (True/False, yyyy-MM-dd, HH:mm:ss[.fffffff], yyyy-MM-ddTHH:mm:ss[.fffffff] in UTC, lower case uuids).

Not supported

• Parameters. There is no API to bind parameter values, so reading a parameter (in an expression, a list or pagination) raises NotSupportedException when the query runs; the tree is built and holds the parameter as a captured variable.
• Stored times and datetimes are read to the .NET tick (100 ns); the specification's values have microsecond precision, so finer stored digits are kept as read.

Dependencies

• Pure.Primitives — core primitive value types (String, Date, DateTime, Time, Guid, Number)
• Pure.RelationalSchema — relational schema abstractions (ISchema, ITable, IColumn, column type hierarchy)
• Pure.RelationalSchema.HashCodes — structural hash codes for relational schema types
• Pure.RelationalSchema.Storage — in-memory relational data model (IStoredSchemaDataSet, IStoredTableDataSet, IRow, ICell)
• PureQL.CSharp.Model — PureQL query model (PureQLQuery, Query, expressions per context) — tracks PureQL specification 0.1.0-preview.2.0.0
• Pure.Collections.Generic — generic collection utilities used in row projection

Target Frameworks

• .NET 8
• .NET 9
• .NET 10

Installation

dotnet add package Pure.RelationalSchema.Storage.PureQL.Projection

Usage

// datasets: IEnumerable<IStoredSchemaDataSet> populated by your storage layer
// query:    a PureQLQuery, built directly or deserialized with
//           PureQL.CSharp.Model.Serialization

IStoredTableDataSet result = new PureQLProjection(datasets, query);

foreach (IRow row in result)
{
    // ...
}

await foreach (IRow row in result)
{
    // ...
}

A query selecting one field:

PureQLQuery query = new PureQLQuery(
    new MainPlainQuery(
        new From(new FromEntity("mySchema.myTable")),
        [
            new SelectItemProjection(
                new SelectItemProjectionNonNullable(
                    new SelectItemProjectionString(
                        "name",
                        new StringProjection(new FieldString("mySchema.myTable", "name"))
                    )
                )
            ),
        ]
    )
);

PureQL.CSharp.Model.Samples holds a query for every feature of the specification; this package's tests run all of them.