MemCP - Recent changes [en]

T-SQL mode

Carli — Fri, 09 Oct 2026 06:32:13 GMT

Created page with "  = T-SQL mode = <blockquote>Development preview from [https://github.com/launix-de/memcp/pull/1057 PR #1057], inspected at commit <code>fed54d227ca73e7878be1186712bbfd0dc2c9038</code>. This describes source capabilities, not a released or certified application installation. Use the eventual tested candidate and its matching libraries.</blockquote> MemCP's t-sql mode adds a..."

New page

= T-SQL mode =

<blockquote>Development preview from [https://github.com/launix-de/memcp/pull/1057 PR #1057], inspected at commit <code>fed54d227ca73e7878be1186712bbfd0dc2c9038</code>. This describes source capabilities, not a released or certified application installation. Use the eventual tested candidate and its matching libraries.</blockquote>

MemCP's t-sql mode adds a SQL syntax frontend, an HTTP endpoint and an optional TDS 7.4 listener. It uses the same planner, columnar storage, transactions, user catalog and access policies as the existing interfaces. It is a compatibility subset for evaluating applications; SQL and client metadata must be checked against the application's actual driver and workflows.

== Start an isolated evaluation ==

Keep the executable and <code>lib/</code> from the same commit. Start in the candidate directory with a fresh data directory:

<pre>
./memcp --no-repl -data ./evaluation-data --api-port=4321 --disable-mysql --tsql-port=1433 --tsql-database=evaluation --root-password='replace-with-a-strong-password' lib/main.scm > evaluation-server.log 2>&1
</pre>

The TDS listener is off by default. <code>--tsql-port</code> enables it; <code>--tsql-database</code> selects its default database, which is created at startup if needed. <code>TSQL_PORT</code> and <code>TSQL_DATABASE</code> provide environment settings. <code>--disable-tsql</code> disables the listener even when a port is configured.

Connect directly to the host and TCP port, select <code>evaluation</code>, and use an existing MemCP username and password. Password storage and database access policies are shared; no separate TDS account migration is needed. The root password flag initializes a fresh directory only. Test with an application account that has the intended permissions.

For TLS 1.2, supply both <code>--tsql-tls-cert=cert.pem</code> and <code>--tsql-tls-key=key.pem</code>, and configure the client to trust the certificate. Without a certificate, select an unencrypted connection explicitly and restrict the network. Integrated authentication and instance discovery are unsupported.

== Syntax and types ==

* Bracket and double-quoted identifiers are supported. A closing bracket inside a bracketed identifier is doubled: <code>[a]]b]</code>. Table, column and view lookup is case-independent while declared spelling is preserved. This does not imply complete text-collation equivalence.
* Unicode strings such as <code>N'Grüße'</code>, raw hexadecimal bytes such as <code>0x0102</code>, <code>TOP</code>, <code>OFFSET … FETCH</code>, DISTINCT, joins, supported subqueries, grouping and basic windows are available.
* INSERT/UPDATE/DELETE, CREATE TABLE/VIEW, alias types and <code>IDENTITY(1,1)</code> use the common engine. Each database exposes <code>dbo</code>; <code>database.dbo.table</code> can address another MemCP database outside the import loader's confined retargeting rules.
* DECIMAL/NUMERIC retain up to 38 digits. MONEY/SMALLMONEY retain fixed currency scale and range. Unary minus preserves precision, scale and currency type; negating TINYINT produces SMALLINT. Integer division and integer AVG follow declared t-sql types.
* DATETIME retains 1/300-second ticks; TIME and DATETIME2 retain fractional precision through results, storage and TDS. Supported casts, CONVERT styles, DATEADD/DATEDIFF/DATEPART and NCHAR cover common expressions. Unsupported types and conversion styles fail explicitly.

== IDENTITY and ROWVERSION ==

<code>@@IDENTITY</code> returns the most recent generated value in the session. <code>SCOPE_IDENTITY()</code> refers to the current batch or prepared RPC scope. Allocation checks the declared type and uses a signed 64-bit counter, including zero-scale decimal identities. Identity columns are non-nullable and cannot be updated. Rollback consumes allocated values; gaps are expected.

ROWVERSION is an automatically generated eight-byte binary token, not a timestamp or a row address. Declare it explicitly in CREATE TABLE. Only that declared table column activates persisted synchronous insert/update hooks; ordinary tables gain no generated column or hooks. Every matched update, including a no-op update, changes the token. An update matching no rows consumes no token. Rollback restores the row while consuming the allocated token. The sequence's high-water reservation is durable across restart.

Applications can implement optimistic edits with a bound original token:

<pre>
UPDATE dbo.records
SET label = @new_label
WHERE id = @id AND rv = @original_rv
</pre>

Check the affected row count: zero means that the original version no longer matches or the row is absent. Bind the token as eight raw bytes. Do not infer a token from a physical shard ID and record ID: updates and rebuilds have different identity semantics.

Adding ROWVERSION to an existing table is currently unsupported and fails explicitly. Existing rows and write hooks need atomic initialization/backfill before that operation can be offered.

== Keys and constraints ==

Primary and unique keys and named/composite foreign keys enforce real constraints. Primary-key constraint names normalize to <code>PRIMARY</code>; DROP CONSTRAINT currently supports foreign keys only. CREATE requires an existing parent with a matching ordered candidate key. Adding a foreign key through ALTER requires a physically empty child table. NO ACTION, CASCADE and SET NULL are supported. Character foreign keys use byte-exact matching. Self/forward/cross-database references, unchecked constraints and SET DEFAULT are unsupported.

== Client protocol and metadata ==

The TDS adapter supports SQL logins, semicolon-separated batches, multiple results, transaction-manager requests, cancellation, connection reset, <code>sp_executesql</code>, prepared execute/describe/unprepare RPCs and bounded read-only STATIC cursors. Input bindings cover supported numeric, exact decimal/currency, Unicode/text, binary and temporal types; values remain separate from SQL text.

Read-only <code>sys.types</code>, <code>sys.tables</code>, <code>sys.objects</code>, <code>sys.columns</code> and <code>sys.schemas</code>, plus OBJECT_ID/TYPE_ID/COL_LENGTH and ODBC discovery, expose actual declaration metadata. Declared result types survive empty and all-NULL results. Direct base columns retain nullability and editability. FOR BROWSE adds source/key metadata for a single base table when all primary-key columns are projected; derived results and static cursors remain read-only. A complete system catalog and view object identities remain outside this subset.

== SQL over HTTP ==

<pre>
curl --fail-with-body -u app_user:strong-password -H 'Content-Type: text/plain; charset=utf-8' --data-binary 'SELECT TOP (10) [id], [label] FROM [dbo].[records] ORDER BY [id]' http://localhost:4321/tsql/evaluation
</pre>

Submit one statement per request. Row results are newline-delimited JSON. HTTP uses the existing Basic authentication and access policies. Keep tests of RPCs, cursor behavior, driver metadata and binary bindings on the actual TDS connection. See [[SQL over REST]].

== Import an export script ==

Create a schema-and-data SQL export containing supported DDL and INSERT statements. Invoke the loader in the Scheme console or the admin-only <code>/scm</code> endpoint:

<pre>
(load_tsql "evaluation" "/path/to/export.sql" (sql_policy "root"))
(load_tsql "evaluation" "/path/to/export.sql.gz" (sql_policy "root"))
</pre>

The loader accepts UTF-8, BOM-marked UTF-16 and gzip, recognizes GO separators outside quoted values, and supports line-start INSERT/SET/DDL boundaries in exports without semicolons. Statement buffers are limited to 16 MiB. GO belongs to the script loader, not the query endpoint.

CREATE DATABASE/USE headers select a source alias; supported references are confined to the chosen destination without rewriting string values. References to another source database fail. Explicit identity values advance the counter; IDENTITY_INSERT export switches are accepted by the loader. Clustered/nonclustered key declarations enforce uniqueness without selecting a physical layout.

Statements execute in order. An error stops further statements; prior commits remain. An unfinished explicit transaction is rolled back and reported. Existing tables are replaced only by an explicit DROP. Repeat a failed import into a fresh destination. Create parents first and declare supported foreign keys before inserting child rows; adding them to populated child tables is unsupported.

Native backup/package files and bulk-copy formats have no reader. This mode has no live source importer analogous to the existing live importers. See [[Migration from MySQL and PostgreSQL]] for shared validation steps.

== Known limits ==

Integrated authentication, instance discovery, MARS, bulk copy, writable/dynamic/keyset cursors, arbitrary stored procedures, output parameters, named/nested transactions, extra schemas, CTE/APPLY, explicit window frames, ordered aggregate windows, TRUNCATE TABLE and TOP PERCENT/WITH TIES are unsupported. Explicit text-collation declarations and full session-option equivalence are not implemented. Unsupported forms should fail explicitly; accepting a connection or a simple SELECT does not prove an application's full installer is compatible.

== Plan an application test ==

# Record the exact candidate commit, binary checksum, matching libraries, client version and connection settings.
# Verify login, SELECT 1, Unicode, parameter binding, empty-result metadata and catalog discovery using the actual client stack.
# Run installation first; retain the first failing statement, declared parameter types, bindings, session settings, transaction order, SQLSTATE and server log.
# Exercise read/create/edit/cancel and two concurrent edits of one record; compare decimal/currency and date results with the source.
# Commit and roll back, restart, and verify committed rows and generated counters. Compare import counts/checksums and constraints.

Keep original traces private and publish only an anonymized minimal reproduction. The repository's [https://github.com/launix-de/memcp/blob/fed54d227ca73e7878be1186712bbfd0dc2c9038/README-TSQL.md evaluation guide] gives the detailed capture procedure.

User:ErikDuigan846

ErikDuigan846 — Mon, 05 Oct 2026 04:47:57 GMT

User account ErikDuigan846 was created

User:LucianaHowland

LucianaHowland — Sun, 04 Oct 2026 21:12:22 GMT

User account LucianaHowland was created

Group Caches

Carli — Fri, 25 Sep 2026 17:37:18 GMT

Show changes

Main Page

Carli — Fri, 25 Sep 2026 10:41:57 GMT

Internals

← Older revision		Revision as of 12:41, 25 September 2026
Line 271:		Line 271:
	* [[Transactions and Isolation]]		* [[Transactions and Isolation]]
	* [[Query Planner and Physical Lowering]]		* [[Query Planner and Physical Lowering]]
			* [[Group Caches]]
	* [[Full SCM API documentation]]		* [[Full SCM API documentation]]

Group Caches

Carli — Fri, 25 Sep 2026 10:39:33 GMT

Created page with " = Group Cache Dimensions: Equality, Additive Ranges, and Snapshots = Implementation reference: 25 September 2026, commit b22387664, [https://github.com/launix-de/memcp/pull/999 PR #999]. Examples describe query shapes; actual eligibility and plan selection depend on semantic proofs, estimated costs, and cache state. A group cache retains reusable aggregate state across SQL requests. It is separate from the query-plan cach..."

New page

= Group Cache Dimensions: Equality, Additive Ranges, and Snapshots =

Implementation reference: 25 September 2026, commit b22387664, [https://github.com/launix-de/memcp/pull/999 PR #999]. Examples describe query shapes; actual eligibility and plan selection depend on semantic proofs, estimated costs, and cache state.

A group cache retains reusable aggregate state across SQL requests. It is separate from the query-plan cache and from caching complete HTTP responses. Cache eviction or changes to source dependencies can require reconstruction.

The three dimension modes describe '''how aggregate results at different parameter values relate to each other'''. They are not specific to time. Coordinates can represent numeric versions, positions, thresholds, or timestamps.

{| class="wikitable"
! Dimension mode !! Meaning !! Reuse strategy !! Typical query shape
|-
| '''Equality / point domain''' || An exact key or key tuple || Look up the aggregate state for that key || Aggregate over an equality group
|-
| '''Additive range domain''' || An interval of an ordered source dimension || Combine states for disjoint input intervals || Aggregate over selected facts
|-
| '''Snapshot domain''' || A parameter point at which contributions were evaluated || Read an exact point, or correct an existing state for a new point || Aggregate over parameter-dependent selected states
|}

The new shape is called '''snapshot aggregation over a parameter domain''', or more precisely, '''a snapshot domain with incremental contribution correction'''. ''As-of state aggregation'' is a useful name for the ordered-state-selection subclass, but the abstraction is not restricted to temporal queries.

== 1. Equality domains: One aggregate per exact key ==

<pre>
SELECT COUNT(*) FROM orders WHERE customer_id = :customer;
SELECT SUM(amount) FROM orders WHERE customer_id = :customer;
SELECT COUNT(*) FROM orders;
</pre>

The first two queries can reuse aggregate states indexed by customer ID. An aggregate without a correlated parameter, such as the final COUNT(*), is the limiting case with one group and an empty key tuple. Additional fixed filters belong to the computation and its cache identity.

This is particularly useful for correlated aggregates: many outer rows may request the same group. Once its aggregate state is available, the group need not be aggregated again for each outer row. Several equality keys can form a partition, such as tenant and product class.

'''Work avoided:''' Repeated processing of M records in a group becomes a lookup of existing aggregate state. Whether to prepare groups together or populate them on demand is a physical planning decision.

'''Potential speedup:''' No isolated A/B measurement of this mode is claimed here. If an expensive group aggregate is requested 100 times, its work can conceptually drop from 100 aggregations to one build plus 100 lookups. The speedup of that part approaches at most 100×, while other query work limits the end-to-end gain. Against a cheap index probe or a tiny group, the benefit may be small.

== 2. Additive range domains: Combine disjoint fact intervals ==

<pre>
SELECT SUM(revenue)
FROM sales
WHERE coordinate >= :lo AND coordinate < :hi;

SELECT SUM(revenue)
FROM sales
WHERE tenant_id = :tenant
AND coordinate >= :lo AND coordinate < :hi;
</pre>

Each fact has a contribution that does not depend on the requested interval within this computation. The bounds determine which facts participate. The second query combines an equality partition with a range dimension.

For a sum over disjoint intervals:

<pre>
R([a,c)) = R([a,b)) + R([b,c))
</pre>

A cache can reuse and combine matching partial states. Missing coverage and boundary fragments still require work. Overlapping cells must not be counted twice. Inclusive/exclusive boundaries and SQL NULL semantics must be preserved.

'''Suitable query families:''' Interval sums, interval counts, cumulative fact aggregates, and repeated overlapping reporting windows. SUM and COUNT are straightforward examples. More generally, each aggregate needs a valid state-combination rule. Conceptually, an average requires a sum and a count, rather than an unweighted average of averages. DISTINCT needs its own state semantics.

'''Work avoided:''' Instead of reading M facts, a query may process K cached partial states and B uncovered facts.

'''Potential speedup:''' No isolated measured factor for this mode is claimed here. If those operations have comparable costs, M/(K+B) approximates the reduction in aggregation work. For example, replacing 1,000,000 facts with 100 cached cells plus 9,900 remaining facts means roughly 100× fewer processing units. This is an illustrative work estimate, not a measured latency speedup. Cold construction, fragmentation, and little overlap between requests can consume much of the benefit.

== 3. Snapshot domains: Aggregate state at a parameter point ==

=== Why an additive range is insufficient ===

<pre>
SELECT SUM((
SELECT h.quantity
FROM entity_history h
WHERE h.entity_id = e.id AND h.coordinate <= :x
ORDER BY h.coordinate DESC
LIMIT 1
))
FROM entity e;
</pre>

This example requires an unambiguous ordered selection, for example UNIQUE(entity_id, coordinate).

It does '''not''' sum all history entries up to x. Each entity contributes only its selected state. If an entity changes from quantity 3 to quantity 5, the aggregate must increase by 2. Simply adding the new quantity 5 would be incorrect.

A snapshot retains aggregate state at an anchor point x₀, together with logical keys and contribution values needed for correction. '''The snapshot itself is a point.''' The interval between x₀ and x helps identify possible changes.

<pre>
C(k,x) = contribution of entity k at parameter point x
S(x) = sum of all C(k,x)
A = a complete superset of potentially changed keys

S(x) = S(x₀) - sum(C(k,x₀), k in A) + sum(C(k,x), k in A)
</pre>

A key is corrected once even if several dependency paths identify it. For every key outside A, its contribution must be proven unchanged. Original residual predicates are still evaluated for candidates: a candidate may turn out not to change anything.

=== Query structures recognized by the current implementation ===

Subject to the corresponding proof conditions, the implementation handles these building blocks:

* '''Ordered state selection per entity:''' Select an unambiguous state using a moving bound. Several scalar values may depend on that selection.
* '''Selected state plus threshold predicates:''' A contribution also depends on a selected property being less than, greater than, or equal to a coordinate. Boundary crossings without a new history row must be included in the change cover.
* '''Conditional SUM payloads:''' CASE/IF chooses a contribution using provable conditions. Change coverage includes selection, predicate, and payload dependencies.
* '''Fixed first/last properties:''' A coordinate-independent first or last value can use the same property group cache in both full construction and correction.
* '''Bounded EXISTS predicates:''' Relevant child rows crossing a bound can identify affected contributions through projected parent keys.
* '''Derived outer coordinates:''' A pure outer expression such as x-5 can define a dimension. The selected states for x and x-5 must remain distinct.
* '''Supported clamps and combinations:''' Certain MIN/MAX clamps on selection bounds, including clamps involving selected properties, COALESCE, and Boolean combinations have complete change-cover proofs.
* '''Repeated aggregate series:''' Multiple parameter points of the same formula can use retained anchors, which later SQL requests can reuse as well.

MIN/MAX in the clamp example means a '''scalar bound expression''', not general support for retractable MIN/MAX aggregates.

Applications include versioned inventory, quantities of qualifying contracts, valid configurations, and other aggregates of “the selected state at coordinate x”. Eligibility follows the relational formula, not the application's business terminology.

=== What happens on a subsequent request ===

# '''Same point, matching fixed inputs, unchanged dependencies:''' Read the retained aggregate state.
# '''New point:''' Choose a valid anchor, identify potential changes, retract the old contributions, and add the new ones.
# '''No relevant change between selection bounds:''' Proven equivalence of ordered selection cuts can permit reuse even when the numeric bound changes. A changed parameter value alone need not imply a fresh cache build.
# '''Many changes:''' Full reconstruction may be cheaper and can produce a new anchor.
# '''Source mutation or eviction:''' Reuse must be re-established. Moving an input parameter is distinct from modifying the underlying data.

For monotone interval covers, the current anchor selection compares affected-key work at the nearest valid anchor on each side. Numeric distance alone is not a work estimate. A farther anchor can require fewer corrections.

=== Current implementation limits ===

* The new retraction path currently targets '''SUM contributions'''. Runtime admission permits NULL and integral numeric values within a conservative exactness budget; the sum of absolute magnitudes must not exceed 2^52. Other values retain the ordinary SQL reducer. Arbitrary floating-point sums are not silently reordered.
* A count can mathematically be represented as a sum of 0/1 contributions. This does not imply that every COUNT spelling currently selects the snapshot path. Nor does the new path provide general support for COUNT DISTINCT, AVG, MIN/MAX, or arbitrary user-defined aggregates.
* The contribution proof currently requires a suitable single base driver with a single-column primary key. Arbitrarily multiplying joins and composite contribution identities are not generally covered.
* Each contribution proof chooses one moving axis; other inputs partition the cache. This is not yet general incremental correction across freely moving multidimensional surfaces.
* The current snapshot runtime restricts coordinates to suitable integral numeric values. The abstraction is broader than this implementation; support for arbitrary ordered types is not implied.
* Not every algebraically related formula has a proof. For example, a scaled inner selection coordinate is not automatically treated as an identity change domain.
* INSERT/UPDATE/DELETE, changes to classification tables, and table replacement must participate in dependency handling. An invalid snapshot may be discarded and rebuilt. Fully incremental maintenance for every source mutation is not implemented.
* Cost selection and memory budgets may favor direct execution even when a formula is eligible. Small or empty inputs should not pay for unnecessary preparation.

=== Potential speedup ===

Let N be the number of contributions in a full build and D the number of potentially changed contributions. A follow-up replaces much of the work on N contributions with change-cover/index work and evaluation of D contributions. With comparable per-contribution costs, N/D is a rough estimate for the reduction in that part of execution, not the complete HTTP latency.

For example, 100,000 entities with 100 change candidates mean roughly 1,000× fewer contributions to re-evaluate. Expensive candidate discovery, remaining scans elsewhere in the query, planning, and response handling reduce the actual speedup.

Across P requested points, a favorable snapshot plan replaces P full evaluations with one build and P-1 corrections. An exact repeat can be cheaper still. Initial construction remains real work.

== Performance expectations and reproducible evaluation ==

There is no universal speedup factor for a dimension mode. The estimates above describe reductions in work under stated assumptions. They are not measured end-to-end latency guarantees.

A reproducible comparison should identify the exact source revisions, hardware, generated fixture, query parameters, and cache preparation. Measure one cold request plus a fixed number of follow-ups, include all requests in the total, and report phase timings separately. Moving-bound tests should cover both empty and nonempty change sets. Repeating an identical query tests exact-point reuse; it does not establish the cost of snapshot correction.

The public test suite provides neutral examples that can be inspected and executed without access to application or customer data:

* ''tests/performance/domain-snapshot.yaml'': generated entities and versioned values; ordered state selection, selected-property boundaries, moving parameters, retained snapshots, anchor selection, and source-change correctness.
* ''tests/performance/group-range-cache.yaml'': generated range-aggregation workloads with performance configuration.
* ''tests/planner/aggregates/group-range-cache.yaml'': correctness and plan regressions for group/range reuse.

Use each test's declared setup and sampling configuration when reproducing it. Small correctness fixtures establish semantics, not production-scale speedups. A comparison intended to isolate one cache mode must control the other planner and runtime changes as well.

''This page does not publish latency factors from private application workloads. It makes no independently measured speedup claim for an individual dimension mode.''

== Combining dimensions and choosing a plan ==

One report can use all three modes: equality partitions for tenants or categories, additive ranges for fact revenue, and snapshot domains for state-dependent quantities. Different metrics need not use the same mode. For example, the current implementation can keep categories as fixed partitions without computing every category together in one contribution vector.

Logical IR exposes parameter dependencies, unique selection, contribution identity, and a complete superset of potentially changed keys. Physical planning then decides whether direct execution or cache reuse is cheaper. The logical IR does not introduce physical scans or cache tables prematurely.

EXPLAIN IR and EXPLAIN REORDER help inspect the structure. EXPLAIN PHYSICAL exposes decisions such as ''contribution_domain'', with ''domain_snapshot'' and ''direct_aggregate'' alternatives. EXPLAIN shows the generated Scheme plan. Actual latency and records visited still require execution measurements.

A snapshot cache is distinct from RecMap. RecMap accelerates physical row mappings within a query. Retained snapshots use logical keys and values; query-local physical row identities must not survive arbitrary shard rebuilds.

== Implementation and test references ==

* [https://github.com/launix-de/memcp/pull/999 PR #999]: implementation changes.
* [https://github.com/launix-de/memcp/blob/b2238766400c2c5b20d155a328d0ed380a7414ff/INVARIANTS.md INVARIANTS.md]: planner and group-cache contracts.
* [https://github.com/launix-de/memcp/blob/b2238766400c2c5b20d155a328d0ed380a7414ff/lib/queryplan-optimize.scm queryplan-optimize.scm]: logical contribution and dependency proofs.
* [https://github.com/launix-de/memcp/blob/b2238766400c2c5b20d155a328d0ed380a7414ff/lib/queryplan-physical-expr.scm queryplan-physical-expr.scm]: physical selection, snapshot admission, and correction.
* [https://github.com/launix-de/memcp/blob/b2238766400c2c5b20d155a328d0ed380a7414ff/tests/performance/domain-snapshot.yaml domain-snapshot.yaml]: neutral correctness and performance cases.
* [https://github.com/launix-de/memcp/blob/b2238766400c2c5b20d155a328d0ed380a7414ff/tests/planner/aggregates/group-range-cache.yaml group-range-cache.yaml]: group/range regression cases.

User:KellieDlh9638

KellieDlh9638 — Tue, 15 Sep 2026 10:18:28 GMT

User account KellieDlh9638 was created