v4.2.0

Added

• redb.Templates is a collection of seven templates. Each one runs as is: SQLite with Pro, the PVT
  prefilter and change tracking on, the props cache off with the lines that turn it on in a comment. Every
  project that has a database also ships a Dockerfile and compose files for PostgreSQL and SQL Server.

  • redb: the console app, as before.
  • redb-razor: a Razor Pages site - a product list with search, sorting and paging in the database, and a
    create / edit form.
  • redb-blazor: the same as a Blazor site (Interactive Server), plus a category tree. Components take an
    IRedbService per operation (RedbWork), not one per circuit.
  • redb-worker: an integration worker - a folder inbox, an XSD check, one .Transacted() write to redb, a
    receipt in the outbox; a repeated order is answered as a duplicate through the object's unique key.
  • redb-chat: an LLM chat on redb.Route.Llm with the history in redb, in the console and over HTTP.
    --tools none|shell|mcp adds a read-only system-command tool or the tools of an MCP server; --audit
    stamps the user and audit tags on every message and reads them back with a LINQ query. The API key goes
    into appsettings.json; a comment there shows how to switch to DeepSeek.
  • redb-app: a Blazor WebAssembly client and a REST DSL API with sign-in (JWT through InboundAuth=Bearer),
    CORS for development and nginx on one origin for deployment.
  • redb-bff: a Blazor Server backend-for-frontend with a cookie session, and a backend of redb.Route
    controllers that only the web server may call, with a service key.

  The route-based templates (redb-worker, redb-chat, and the servers of redb-app and redb-bff) are a
  module with InitRoute.main plus a host that calls it. The same module runs in its own host or on a Tsak
  worker: deploy/pack-tpkg.ps1 packs it, deploy/docker-compose.tsak.yml runs it on the Tsak stack image,
  and one set of setting keys and environment variables works in both places.

Changed

• The redb console template turns on the PVT prefilter and change tracking in its Pro variant; they
  were commented out. The props cache stays off, with the lines that turn it on next to it.

• SQL Server Pro: string matching follows the contract of COLLATION.md, as SQL Server Free does. Plain
  Contains / StartsWith / EndsWith were forced case-sensitive (COLLATE Latin1_General_CS_AS) whatever the
  column said; they now keep the column collation - case-insensitive on the default one. The *IgnoreCase forms
  were forced to Latin1_General_CI_AI, which also ignored accents (muller found Müller); they now fold case
  only, with LOWER. On the same database Free and Pro answered the same query differently; they agree now.
  Code that relied on Pro's case-sensitive plain match on a case-insensitive database should say so with an
  ordinal re-check in memory, as COLLATION.md describes. The prefilter renderer changed with it, so it stays a
  superset of the filter.

• Configuration copying has one list of settings. RedbServiceConfiguration.CopyFrom copies every setting,
  CopyBehaviourFrom every setting except the connection identity (ConnectionString, CacheDomain); Clone,
  ApplyTemporary and the validator's auto-fix go through them. The Clone extension method in
  RedbServiceConfigurationExtensions is removed - the instance method always won over it, so only an explicit
  static call reached it.

• The obsolete InitializeAsync / AutoSyncSchemesAsync extensions delegate to IRedbService.InitializeAsync.
  They carried their own copy of the start-up sequence: schemes synchronized in parallel on one service instance,
  no up-front validation of scheme names, and every synchronization error swallowed.

• SQL Server (Free): the filter and aggregate builders refuse what they cannot read. An operator they did
  not know became 1=1 (the condition vanished) or 1=0 (no rows - and every row under $not); an unknown
  comparison became =; a value that was not a number became 0; an aggregate they could not compile became a
  NULL column. They now fail through one helper, dbo.pvt_fail, with a message that starts with "redb:", where
  the PostgreSQL module raises. LINQ does not produce these shapes; JSON filters sent directly do.

• The SQL modules' smoke runners run in the integration suite (PostgresModuleSmokeTests,
  MsSqlModuleSmokeTests). They were run by hand only.

• PostgreSQL Pro: the pivot takes a scalar field with max() instead of (array_agg(...))[1]. The array
  per object and per field kept the planner on a sorted GroupAggregate; max() lets it hash-aggregate. A
  filtered, sorted page over 200 000 objects went from 1.1-1.4 s to 0.84-0.98 s. The FILTER leaves one row per
  object, so the value is the same. PostgreSQL 14 has no max() for every type: bool fields take bool_or,
  Guid and byte[] fields keep array_agg. One helper (PvtPick) now renders this for every Pro PostgreSQL
  query shape (flat, tree, aggregate, grouping, window, grouped window, array grouping) instead of 26 copies.

Fixed

• Pro (all three providers): ToAggregateSqlStringAsync showed the aggregate without the query's Where.
  The preview built the SQL with no filter while AggregateAsync runs with it, so the preview of a filtered sum
  was a sum over the whole scheme. It now passes the same filter. Only the preview was wrong; results were not.
• SQL Server (Free), JSON filters, found by the smoke runner once it ran: $arrayAt read its operand as the
  index and never compared the value - it now takes {"index":N,"value":V} as PostgreSQL does; $arrayCountLte
  was spelled $arraycountle and matched nothing; $ilike on a field was missing; {"Dict.ContainsKey":"key"}
  with a bare string key was never recognised. Each returned no rows without an error. SQL Server module
  version 0.2.26.
• Configuration copies lost five settings. Clone did not copy AutoApplyDatabaseUpgrades, StringCollation,
  EnablePvtPrefilter, PropsSaveStrategy and DefaultCheckPermissionsOnQuery. The auto-fix of an invalid
  configuration (GetValidatedRedbServiceConfiguration without throwing) returned such a copy, so it could switch
  automatic schema upgrades back on for an operator who had turned them off.
• ApplyTemporary left its change behind. The builder form edited the live configuration before taking the
  snapshot meant to undo it, so the scope ended with the temporary values in place. The configuration form
  applied and restored fourteen settings out of thirty-six. Both apply and restore every behaviour setting now,
  and never touch the connection string or the cache domain.
• Pro: a stored collection element that did not convert to the element type was lost without a word. The
  same failure had three outcomes, chosen by the shape of the collection: a dictionary entry vanished, an array
  element became the type's default (0), a list element was skipped. Loading now fails with the value, the target
  type and the original error.
• SQLite: a column value the row mapper could not convert left the property at its default. Two silent
  catch blocks made the row read as if the column were NULL. It fails now, naming the column, the value and the
  property. Conversions use the invariant culture (a decimal text like 1.5 was parsed with the current one).
• Tree projections (TreeQueryableBase.Select): Take / Skip / Distinct after Where / OrderBy.
  Where and OrderBy ran in memory after the projection while Take and Skip went to SQL before it, so the
  page was cut from the unfiltered, unsorted nodes; Distinct went to the source query, where every node is
  distinct. They now run in call order after the projection, as the flat projection has done since S-4.
• Free: DistinctByRedb / DistinctBy on a tree query returned every node. The flat query passed the
  distinct key to pvt_build_query_sql; the three Free tree providers passed none. They pass it now. The SQL
  builders then had to honour it in the tree shape too: SQL Server ignored the key in the tree branch without
  props fields, and the SQLite native extension ignored it in the wide shape (a tree without props fields, or a
  filter with an absence check). SQL Server module version 0.2.24, SQLite extension version 0.6.7 - the SQLite
  native library is rebuilt for Windows x64 and Linux x64/arm64.
• Pro: DistinctByRedb / DistinctBy on a tree query.
  • SQLite Pro ignored the distinct key on a tree and returned every node: the key was computed and never put
    into the SQL. The tree now ranks rows with ROW_NUMBER() as the flat query does.
  • CountAsync of a distinct tree query was wrong on all three Pro providers. PostgreSQL counted every node
    (COUNT(DISTINCT o._id) - the ids are distinct whatever the key is); SQL Server and SQLite looked for a
    PostgreSQL DISTINCT ON, did not find it, and returned the first column of the first row - an object id.
    The count is now the number of distinct keys, and a count that cannot find the distinct query refuses.
• SQL Server (Free): an unknown nested field resolved to its parent. T-SQL keeps a variable's old value when
  SELECT @v = ... finds no row, so dbo.pvt_resolve_field_path returned the parent structure for a child the
  scheme does not have (Contacts[].NoSuchField became the Contacts array), and a filter, sort or grouping on
  that path read the wrong structure. The dictionary child and the list-item accessor had the same trap.
• SQL Server (Free): the array grouping skipped a key or aggregate field the item does not have. The grouping
  ran on the remaining keys, the aggregate vanished from the result, and with no key left the flat list of items
  came back instead of groups. Such a request is refused now, as is an unknown aggregate function.
• Pro: HAVING constants. An array or object in $const was bound as its raw JSON text and compared as a
  string; it is refused now. The unreachable fallback operator = of the translator is a refusal too.
• SQL Server module version 0.2.26: the functions are reinstalled on the first start.