Aster Web Data / Connect your data

SQL ServerCommercial

How Aster Web Data uses SQL Server, and how SQL Server's types, collations and results shape what clients receive.

If your data is in SQL Server 2019 or later, Aster Web Data serves it from your existing tables, with filters, sorts, pages and counts written as T-SQL that SQL Server's planner can serve from your indexes. Each comparison uses the column's own collation, and each result is SQL Server's own. Where it differs from OData's, SQL Server's answer stands; Departures from OData lists those places.

Three choices change what your users see: the collation of your text columns, which decides case and accent sensitivity; whether strings in keys and indexes have a declared maximum length, which the start-up check requires; and the rowversion column that gives a set its ETag.

Connecting

Declare the database once under databases:, and name it from each data source stored on it:

databases:
  orders:
    kind: mssql
    url_file: secrets/orders.url
    tls: verify-full
    ca_file: secrets/sql-ca.pem    # optional

The connection comes from an environment variable (url_env), a file (url_file), or its parts (host, port, user, password_env or password_file, and database). SQL Server authentication is supported. Secrets explains the rules, and the configuration reference lists every key with its default. These behave in a way particular to SQL Server:

• schema is dbo when absent, and a data source's own schema wins.
• tls takes disable, prefer, require or verify-full. verify-full checks the certificate chain and the host name, against ca_file when you give one. verify-ca is refused, because the driver cannot check the chain without the host name. Any mode other than verify-full logs a warning, and tls_strict refuses it.
• read_only: true makes the service refuse every write request and write binding. Use a SQL Server login with read permissions only as the enforcement inside the database.

The service checks the server when it connects: SQL Server 2019 or later, with a database compatibility level of 150 or higher. It refuses an older one, naming the version and level it found. The tests run on SQL Server 2022.

Start-up check

When the service opens the database, it reads the sys catalog for each data source's table and checks it against the configuration: the columns, their types and facets, identity and computed columns, defaults, keys and foreign keys. The account sees only what its permissions allow.

Two rules are particular to SQL Server. A string used in a key or an index needs a declared maxLength, because SQL Server limits an index key to 900 bytes for a clustered primary key and 1,700 bytes for a nonclustered index; the check names the field that lacks one. And an existing text column must be NVARCHAR: a VARCHAR column is refused, because binding an NVARCHAR value against it can convert the indexed column and lose the index.

A failed check makes only that database's sets unavailable; A database goes down describes what clients see. To run the check on its own, use the check command.

Types

A decimal is read from the wire's exact value and checked against the 28-digit bound, which 29 digits meet only up to 79,228,162,514,264,337,593,543,950,335; it is never converted through a double. A literal written to a decimal column is checked against the declared facets. A row with an offset written by another client is still returned in UTC, though year, month and the other date parts read the stored offset.

Collation and case

A comparison, a sort, grouping and in use the column's collation, with SQL Server's own operators and no correction. $metadata publishes each text field's collation as AsterData.Collation.

• Trailing spaces. SQL Server pads trailing spaces in comparisons under every collation, BIN2 included, so 'a' = 'a ' is true. LEN ignores trailing spaces too.
• Case and accents come from the collation: a _CI_AS collation makes eq case-insensitive and accent-sensitive.
• BIN2 ordering follows UTF-16 units, so a character outside the Basic Multilingual Plane can sort differently from code point order.
• Case mapping. tolower and toupper are LOWER and UPPER, with SQL Server's own mapping.
• Substring search. contains, startswith and endswith are LIKE with ESCAPE, with [, %, _ and the backslash in the needle escaped.

A named collation whose comparison the service cannot reproduce is refused when a request would need it outside SQL Server.

Where each operation runs

Each OData operator becomes the SQL Server operator with the same meaning:

These run in the service, within the limits on rows it may hold: casts that SQL Server cannot do exactly, Traverse, 128-bit arithmetic, and windows over a table with text columns. On a table with a collection column, the text functions run in the service too.

A statement binds at most 2,098 values, which bounds key batches and multi-row writes. A key of several columns is matched with OR over each tuple. A page with $count=true sends the page and the count in one request, inside one serializable transaction, so the total agrees with the page.

Errors

A timeout or a lost connection discards the connection; an evaluation error or a conflict keeps it.

Nulls and ordering

SQL Server sorts nulls first in ascending order, as OData does. Where a sort needs the other placement, the service writes a CASE key ahead of the sort key. uniqueidentifier values sort in SQL Server's byte order, which differs from the order of their text.

$count=true is exact and agrees with the page.

Departures from OData

Where OData 4.01 defines a result and SQL Server gives another, SQL Server's result stands. These are the places:

• Trailing spaces are ignored in text equality, even under BIN2, and by length: length('a ') is 1.
• trim removes spaces only, so a leading tab stays. concat treats null as the empty string.
• substring with a negative start counts positions before the first character, so substring('abc', -2) is 'abc'.
• GUID ordering follows SQL Server's byte significance, not the text order.
• Division by zero raises an error for every type, divby included, where OData gives INF or NaN. Double overflow raises an error too.
• avg of integers truncates: the average of 1 and 2 is 1.
• Decimal arithmetic uses SQL Server's precision and scale.
• Integer sum can overflow its running total, and SQL Server decides the evaluation order.

Writes and transactions

• Session. Each connection is opened with ANSI_WARNINGS, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS and CONCAT_NULL_YIELDS_NULL on, ARITHIGNORE and NUMERIC_ROUNDABORT off, DATEFORMAT ymd and NOCOUNT ON.
• Isolation. Read committed is the default. A page with its count runs in a serializable transaction. Read committed snapshot and snapshot isolation stay database settings of yours.
• Writes by key. A PATCH, PUT or DELETE by key sends its lock (SELECT ... WITH (UPDLOCK, HOLDLOCK), which compares the ETag's token), the write and the read of the stored row in one round trip. A check that fails changes nothing and gives the same 404, 409 or 412 as it would on its own.
• Triggers. An insert captures its keys with OUTPUT ... INTO and reads the rows back, so AFTER triggers work and the response shows their changes. A trigger that changes or deletes the key of a row being written fails the request and rolls it back. Generated keys use IDENTITY.
• Multi-row inserts take at most 1,000 rows per statement, in batches within the bound-value budget.

• ETags. SQL Server has no row version that etag: native could read, so a set has no ETag until it declares one. The usual token is a rowversion column, which SQL Server changes on every update of the row:

  ALTER TABLE dbo.orders ADD row_version rowversion NOT NULL;
  

  Declare it as a read-only binary field and name it in etag: {columns: [row_version]}. scaffold declares a table's rowversion column as its token. A version column Aster advances, versionField, works as well. ETags and concurrent edits explains the tokens.

Indexes and sort order

Index the columns your screens filter and sort on, as Indexes describes. Equality and a prefix LIKE keep the column bare, so SQL Server can choose a seek. An expression such as LOWER(name) needs an indexed computed column where you want an index to serve it.

Scaffold

The scaffold command reads the sys catalog for schemas, tables and views, columns with their types and facets, identity and computed columns, defaults, comments, keys and foreign keys, and writes a starting data-source configuration. It declares a rowversion column as the set's ETag token. Connect your own database walks through it.