T-SQL mode

T-SQL mode

Development preview from PR #1057, inspected at commit fed54d227ca73e7878be1186712bbfd0dc2c9038. This describes source capabilities, not a released or certified application installation. Use the eventual tested candidate and its matching libraries.

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 lib/ from the same commit. Start in the candidate directory with a fresh data directory:

./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

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

Connect directly to the host and TCP port, select evaluation, 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 --tsql-tls-cert=cert.pem and --tsql-tls-key=key.pem, 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: [a]]b]. 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 N'Grüße', raw hexadecimal bytes such as 0x0102, TOP, OFFSET … FETCH, DISTINCT, joins, supported subqueries, grouping and basic windows are available.
• INSERT/UPDATE/DELETE, CREATE TABLE/VIEW, alias types and IDENTITY(1,1) use the common engine. Each database exposes dbo; database.dbo.table 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

@@IDENTITY returns the most recent generated value in the session. SCOPE_IDENTITY() 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:

UPDATE dbo.records
SET label = @new_label
WHERE id = @id AND rv = @original_rv

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 PRIMARY; 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, sp_executesql, 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 sys.types, sys.tables, sys.objects, sys.columns and sys.schemas, 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

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

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 /scm endpoint:

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

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

1. Record the exact candidate commit, binary checksum, matching libraries, client version and connection settings.
2. Verify login, SELECT 1, Unicode, parameter binding, empty-result metadata and catalog discovery using the actual client stack.
3. Run installation first; retain the first failing statement, declared parameter types, bindings, session settings, transaction order, SQLSTATE and server log.
4. Exercise read/create/edit/cancel and two concurrent edits of one record; compare decimal/currency and date results with the source.
5. 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 evaluation guide gives the detailed capture procedure.