Class SqlServerProjectionSchema
- Namespace
- Excalibur.EventSourcing.SqlServer
- Assembly
- Excalibur.EventSourcing.SqlServer.dll
The table a SQL Server projection store reads and writes, as a script you can run.
public static class SqlServerProjectionSchema
- Inheritance
-
SqlServerProjectionSchema
- Inherited Members
Examples
// at deployment time, once per projection table
var ddl = SqlServerProjectionSchema.CreateTableScript("OrderSummary");
await using var command = connection.CreateCommand();
command.CommandText = ddl;
await command.ExecuteNonQueryAsync(cancellationToken);
Remarks
A projection table's columns are dictated by the store, not by the projection type. The
projection body is stored as JSON in a single Data column, so a table whose columns are
named after the projection's own properties compiles, deploys, and then fails at the first apply
with Invalid column name 'Data' — on the consumer's machine, against their database, where
nothing in this framework can see it.
The table name is a per-projection option, so this cannot be a fixed script the way the event store's schema is. It is emitted instead, from the same constants the store itself uses, which is what keeps the two from drifting: change the store's columns without changing this and the round-trip conformance arm fails.
The key widths are a BUDGET, not a preference. SQL Server caps a clustered index key at 900
bytes, and a primary key is clustered unless told otherwise. NVARCHAR costs two bytes per
character, so the tenant and identifier columns together may span at most 450 characters. The tenant
width derives from TenantId.MaxLength; the identifier allows 350 characters. Earlier widths of
200 and 450 summed to 1,300 bytes — the table still CREATEd, with
a warning, and then refused any insert whose actual key exceeded the limit. A schema that deploys
cleanly and rejects rows later is worse than one that refuses up front, because the failure arrives
on the consumer's first long identifier rather than at deployment time.
Those widths are therefore the portable bound on a projection identifier, not a SQL Server quirk to route around. Postgres permits more and its emitted schema is wider, but an identifier that fits only there fails when the same projection is deployed on SQL Server.
It does not create the table for you, and that is deliberate. A store that silently created a missing table would also silently create it in the wrong database when a connection string is wrong, and a projection reading an empty table it just made is indistinguishable from one that is merely behind. Run this where you run your other migrations.
Methods
CreateTableScript(string, string)
Builds the CREATE TABLE script for a projection table, idempotently.
public static string CreateTableScript(string tableName, string schema = "dbo")
Parameters
tableNamestringThe table name, matching the store's configured table name.
schemastringThe schema to create it in.
Returns
- string
A script that creates the table if it does not already exist.
Exceptions
- ArgumentException
A name is empty, whitespace, or contains a character that is not a letter, a digit or an underscore. Letters and digits must be ASCII, matching the store's accepted names. Identifiers are interpolated into the script, so they are restricted rather than quoted-and-hoped: a name is a deployment-time constant, never user input, and refusing the unusual cases costs nothing while leaving no room for an injected one.
CreateTransactionalTableScript(string, string)
Creates a dedicated aggregate-local transactional projection table.
public static string CreateTransactionalTableScript(string tableName, string schema = "dbo")
Parameters
tableNamestringPhysical projection table, including its generation suffix if configured.
schemastringSQL schema.
Returns
- string
Idempotent creation script. Existing ordinary tables are not converted.
Remarks
The position constraint prevents an Inline or Async processor from adopting this destination.