HtmlForgeX.DbaClientX 2.0.0

HtmlForgeX.DbaClientX

Answers HtmlForgeX report queries (ReportQuery) from a database table through DbaClientX, for hosted reports and for report bundles whose data is read from a database.

• ReportSqlTable maps a report dataset onto a table: the table column of each dataset column, the key, which columns are indexed, never empty or cheap to count, and how times are stored. The dataset's columns are the allowlist.
• ReportSqlQuerySource is an IReportQuerySource: a query becomes parameterized SQL (every value is a parameter), paged by key when the sort allows it and by offset otherwise, with the matching rows counted and value counts grouped in the database. It returns the rows the in-memory engine returns for the same query on the same data.
• StreamRowsAsync, StreamChunksAsync and ToSidecarSource read a table in key order into sidecar chunks without holding it in memory.

SQLite is implemented and tested (ReportSqliteExecutor); it needs DbaClientX with the reporting-platform work (EvotecIT/DbaClientX#250: interrupted statements, read-only streams, WhereNot, WhereContains, Unicode folding), which is not published yet, so build it from a DbaClientX checkout (-p:UseLocalDbaClientX=true -p:DbaClientXRoot=...).

What the table must hold, and what is cheap:

• The key is unique (within the scope) and is the last sort key of every page. Mark it NotNull().
• Times are UTC: Unix milliseconds (default), Unix seconds, or ISO text of one fixed form. Date columns hold days (midnight UTC, or yyyy-MM-dd). Booleans are numbers: 0 is "No", anything else "Yes".
• Hosted queries carry the viewer's IANA or Windows time-zone ID in ReportQuery.ViewerTimeZone (timeZone in JSON). Local date-time filters and display-text searches use that zone; calendar-day filters use the viewer's current day. DisplayUtc (displayUtc) applies the shell's UTC display choice to date-time parsing and search. CSV, facets and ReportQueryResult.FormatCell keep each column's declared export zone. With no viewer ID, queries use the service's local zone. ISO date-time storage uses the fixed UTC form yyyy-MM-ddTHH:mm:ss.fffZ so indexed comparisons remain valid.
• Text compares, sorts and is searched without case as the other engines fold it (ToLowerInvariant), through the dbx_lower() function and DBX_NOCASE collation of DbaClientX's SQLiteUnicodeText, which ReportSqliteExecutor registers on every connection (after a ConfigureConnection callback of a client you pass). A custom SQLite executor calls ReportSqliteFunctions.Register(connection) on every connection; this also installs the report token and viewer-zone date-time search functions. External query sources attach the query's export context with new ReportQueryResult(...).WithTimeContext(query). SQLite's own lower() and NOCASE fold ASCII letters only.
• Sorting is cheap by the key and by indexed columns that are never empty and hold numbers or dates; those are the sort columns Capabilities advertises, and a hosted endpoint refuses other sorts on a large table. Text sorts without case and with empty values last, which a plain index cannot give.
• Pages are read by key when every sort column is NotNull() and holds numbers, Unix-time dates or text, so a later page does not pass the earlier ones: text keys compare under DBX_NOCASE (DbaClientX KeysetColumn.WithCollation on the folded column, or the column as text through KeysetColumn.Expression), and the key breaks ties. A page whose last text key is too long for a cursor (about 12 KB) continues by position. Other sorts (booleans, ISO-text dates, columns that can be empty) are read by offset.
• Facets group by the stored column (byte for byte, whatever its collation), so an index on it counts when there is no filter; mark cheap ones Facetable(). With a filter or search, one read of the matching rows grouped by every facet column gives the value counts and the count of the rows together.
• Search and "contains" match the text a reader sees (instr() over the folded, formatted values, DbaClientX WhereContainsRaw: the stored folded column with TextFolding.None, other text with TextFolding.Invariant), which reads every row; "does not contain" is the negation of the same test in the same scan. Number and date columns are taken to hold numbers and dates: one is left out of a search for a text that no number or date shows (a word), so text stored in such a column is not found by words.
• The count of a filter is reused for 30 seconds (CountCacheDuration), so the next pages of a result (without value counts) cost only their rows. The unfiltered count keeps RowCount current. The count also says how to read a page: in the page's order (the key or the sort index), stopping at the page, when that passes fewer rows than an index of the filters would find; otherwise through that index.
• Every statement runs on a read-only connection, streams included. A cancelled token interrupts the statement that is running, so a hosted endpoint's time limit frees the connection too. A hosted endpoint should still bound concurrent queries.
• Run ANALYZE after loading a large table, so SQLite knows how selective its indexes are.

Two opt-ins for large tables make the slow shapes cheap:

• Folded("NameFolded", indexed: true) says the table also stores a text column folded, in a TEXT column: value.ToLowerInvariant(), null where the value is empty, kept by the writer (or a STORED generated column dbx_lower(NULLIF(Name, '')) when every writer registers SQLiteUnicodeText), and whether an index starts with it. Equality and "in" then compare the stored folded column, so its index serves them, and order filters, sorts, contains and search read it instead of folding every row in .NET.
• TrigramIndex("ProbesSearch") says an FTS5 trigram index holds a column's folded text, so contains and search of three characters or more find the candidate rows through the index; the exact test still decides each row. For read-mostly tables: DbaClientX measured 128 MB more on disk for three short text columns of 1,000,000 rows and inserts about 20 times slower; here the index of three folded columns took 140 MB and 5 s to build. Create it with table.TrigramIndexes and DbaClientX: await client.CreateTrigramIndexAsync(database, index, table.Table, table.Key.SqlName, columns). The key must be the table's INTEGER PRIMARY KEY.

On 1,000,000 rows every measured shape answers within the 400 ms a hosted page allows with both opt-ins (the numbers are in Docs/REPORT_SHELL_DESIGN.md); without them, search and contains over text fold every row in .NET and take 0.4 to 1.2 s.

var schema = ReportDataset.DefineSchema("probes", d => d
    .Number("id", "Id", c => c.Key()).Text("target", "Target").Category("state", "State").DateTime("seen", "Seen"));
var table = ReportSqlTable.Create(schema, "ProbeResults", t => t
    .Column("id", c => c.Name("Id").Indexed().NotNull())
    .Column("target", c => c.Name("Target").Folded("TargetFolded").TrigramIndex("ProbeResultsSearch"))
    .Column("state", c => c.Name("Status").Facetable().Folded("StatusFolded", indexed: true))
    .Column("seen", c => c.Name("SeenUnixMs").Indexed().NotNull()));
var client = new SQLite();
foreach (var (index, columns) in table.TrigramIndexes) {
    await client.CreateTrigramIndexAsync(@"C:\ProgramData\App\history.db", index, table.Table, table.Key.SqlName, columns);
}
var source = await ReportSqlQuerySource.CreateAsync(table, new ReportSqliteExecutor(@"C:\ProgramData\App\history.db", client));
var page = await source.QueryAsync(new ReportQuery().Where("state", ReportFilterOperator.EqualTo, "down").OrderBy("seen", true).Page(0, 200), cancellationToken);