# SQL — Deep dive: No Annotation Slot: How the SQL DDL Coder Carries Meaning, and Where It Stops

Every other format on the CoreModels transform surface has somewhere to put meaning. JSON Schema has `x-` keywords. Avro has custom attributes. LinkML has `slot_uri` and `meaning`. OWL *is* meaning. SQL DDL has a name, a type, and a handful of constraints - and no annotation slot at all.

# No Annotation Slot: How the SQL DDL Coder Carries Meaning, and Where It Stops

Every other format on the CoreModels transform surface has somewhere to put meaning. JSON Schema has `x-` keywords. Avro has custom attributes. LinkML has `slot_uri` and `meaning`. OWL *is* meaning. SQL DDL has a name, a type, and a handful of constraints - and no annotation slot at all.

That austerity shapes the whole coder. Its answer comes in three parts: **structure maps to structure**, **exact spellings ride in a dotted extras namespace**, and **semantics ride in comments** - the one free-text channel every dialect offers. What follows is the tour of both directions: what maps, what rides along, every condition that writes a lossiness record, what round-trips, and the edges we know about.

The format key is `sql`. Decode is dialect-tolerant: one parser reads PostgreSQL, MySQL, and SQL Server spellings without a dialect declaration. Encode is dialect-specific: `postgres` (default), `mysql`, or `sqlserver` selects quoting, type defaults, enum handling, and comment placement.

## The structural map

| SQL construct | Model construct | Notes |
|---|---|---|
| `CREATE TABLE t (...)` | Type | id sanitized to camelCase alphanumerics; the original name kept verbatim as the label; provenance stamped `SQL` |
| column | Element | label is the original column name; id is table + column, camelCased |
| column type | primitive kind | four buckets, plus the verbatim spelling in extras |
| `NOT NULL` | required | round-trips in both directions |
| inline `REFERENCES t(c)`, or table-level `FOREIGN KEY (c) REFERENCES t (c)` | type reference | the element's value type points at the referenced table's Type |
| `ENUM('a','b')` or `SET(...)` column | Taxonomy + reference | taxonomy id is the element id plus `Enum`; one term per value |
| column comment, inline or `COMMENT ON COLUMN` | semantic mappings, role, or description | the carrier - below |

Ids are sanitized because node ids must be alphanumeric; separators fold away and camel-case the next word. `sensor_reading` becomes `sensorReading`, and its `taken_at` column becomes `sensorReadingTakenAt`. Sanitization does not lower-case what you shouted: `FULL_NAME` in `Customer` becomes `CustomerFULLNAME`. Labels always keep the original text, and encode writes from labels, so your naming survives the trip - but mapping guides and plans address constructs by id, so copy ids from a returned plan rather than deriving them by eye.

Schema-qualified table names reduce to their local part: `analytics.web_event` imports as `web_event`.

One structural reduction to know: a foreign key records *which table* is referenced, not which column. `REFERENCES invoice(invoice_id)` becomes a reference to the `invoice` type, and re-encoding emits `REFERENCES "invoice"` with no column list.

## Types: four buckets in, verbatim spelling back out

Decode strips any `(args)`, lower-cases the base name, and sorts it into one of four recognized kinds, with String as the everything-else fallback:

| Kind | Recognized spellings |
|---|---|
| Integer | `int`, `integer`, `bigint`, `smallint`, `tinyint`, `serial`, `bigserial`, `smallserial`, `int4`, `int8` |
| Double | `decimal`, `numeric`, `float`, `double`, `real`, `money`, `number`, `dec` |
| Boolean | `bool`, `boolean`, `bit` |
| DateTime | `date`, `datetime`, `datetime2`, `timestamp`, `timestamptz`, `smalldatetime`, `time` |
| String | everything else - `varchar`, `char`, `text`, `uuid`, `json`, `blob`, … |

The bucket is only half the story. The **original spelling, arguments included, is preserved verbatim** under the extras key `sql.type`. `DECIMAL(12,2)` buckets as Double for the benefit of every other format and stays `DECIMAL(12,2)` in the bag. Precision and scale never become model structure; they survive as the verbatim spelling or not at all.

On encode, a preserved spelling **wins**. Only an element with no `sql.type` - a model that arrived as JSON Schema, Avro, LinkML, or came out of a CoreModels project - gets the vendor default:

| Kind | postgres | mysql | sqlserver |
|---|---|---|---|
| Integer | `INTEGER` | `INTEGER` | `INTEGER` |
| Double | `NUMERIC` | `NUMERIC` | `DECIMAL(18,2)` |
| Boolean | `BOOLEAN` | `TINYINT(1)` | `BIT` |
| DateTime | `TIMESTAMP` | `DATETIME` | `DATETIME2` |
| String and the rest | `VARCHAR(255)` | `VARCHAR(255)` | `NVARCHAR(255)` |

That rule defines what a stateless SQL-to-SQL "dialect conversion" actually is: for a model that entered as SQL, re-encoding to another vendor changes quoting, enum treatment, and comment placement - but the column types come back in their original spellings. A T-SQL `NVARCHAR(200)` exported as PostgreSQL stays `NVARCHAR(200)`. It is a deliberate trade of speculative rewriting for round-trip fidelity. Route the model through a project when you want the vendor table applied, since a project stores the modeled type rather than the source text.

## The carrier: comments that are secretly JSON

Semantics travel in column comments, and the rules are worth knowing exactly.

**Decoding.** MySQL's inline `COMMENT '...'` clause is read during column parsing; standalone `COMMENT ON COLUMN table.column IS '...'` statements are applied in a second pass after all tables exist. Either way the text is inspected: if it parses as a JSON object, `x-maps-to` entries become formal semantic mappings (standard → URI) and `x-sia-role` becomes the element's role. If it is not JSON - plain prose, or JSON that fails to parse - it is kept as a description under the extras key `sql.comment`.

**Encoding.** An element carrying mappings or a role gets a JSON-object comment rebuilt from them; otherwise a stored description is written back as-is. MySQL output carries comments inline on the column; `postgres` and `sqlserver` output collects them into `COMMENT ON COLUMN` statements appended after the tables. One table in, two dialects out:

```sql
CREATE TABLE patient (
  mrn VARCHAR(32) COMMENT '{"x-sia-role":"Identifier","x-maps-to":{"schema.org":"https://schema.org/identifier"}}',
  enrolled_on TIMESTAMP
);
```

```sql
CREATE TABLE `patient` (
  `mrn` VARCHAR(32) COMMENT '{"x-sia-role":"Identifier","x-maps-to":{"schema.org":"https://schema.org/identifier"}}',
  `enrolled_on` TIMESTAMP
);
```

```sql
CREATE TABLE "patient" (
  "mrn" VARCHAR(32),
  "enrolled_on" TIMESTAMP
);

COMMENT ON COLUMN patient.mrn IS '{"x-sia-role":"Identifier","x-maps-to":{"schema.org":"https://schema.org/identifier"}}';
```

Between them, `mrn` is a String element with role `Identifier`, one mapping to `https://schema.org/identifier`, and `sql.type` of `VARCHAR(32)`. The dotted extras namespace for this format is exactly two keys - `sql.type` for the verbatim column type and `sql.comment` for a non-semantic comment - with lifted mappings and roles living in the annotation bag proper.

## The lossiness inventory

The coder itself writes exactly two patterns of record:

1. **Decode.** A table-level `PRIMARY KEY`, `UNIQUE`, or `CHECK` clause produces a `ConstraintRelaxation` at `Type[<table>]`: `SQL PRIMARY constraint not modelled by the IR.` (or `UNIQUE`, or `CHECK`). The model has types, elements, requiredness, references, and vocabularies - not keys or check expressions.
2. **Encode.** A taxonomy-backed element targeted at `postgres` or `sqlserver` produces a `ConstraintRelaxation` at `Element[<id>]`: *"has no inline enum; emitted as VARCHAR (the allowed-value constraint is not enforced)."* MySQL expresses the vocabulary as a real `ENUM(...)` and writes no record.

Everything else you see in a SQL conversion's ledger comes from the engine, not the coder - most often a `StructuralDrop` at `Type[<type>].<element>` reading *"Element is a member of the mapped type but no operation maps or drops it."*, which means the mapping plan had nothing to say about that column.

Equally important is what is dropped *without* a record, because the column tokenizer recognizes only `NOT NULL`, `REFERENCES`, and `COMMENT` among column attributes: `DEFAULT` clauses, auto-increment markers, and **inline** column-level `PRIMARY KEY` / `UNIQUE` pass through silently, as do table-level `KEY` / `INDEX` lines. Move keys to table-level clauses if you want them itemized, and diff an export against its source once if your DDL leans on defaults.

## What round-trips

Decode → encode → decode → encode over MySQL is stable: the second pass produces text identical to the first, with structure, requiredness, references, vocabularies, and any `x-maps-to` mapping intact. The test suite pins the same claims - the structural map from a reference table, the `x-maps-to` lift out of a column comment, a MySQL round-trip whose signature is identical across the loop, and the vendor defaults from a fresh model.

Two inputs fail outright rather than reporting lossiness: an empty payload (`The SQL DDL is empty.`) and text with no `CREATE TABLE` at all (`No CREATE TABLE statement was found.`).

## Every edge we know about

- **Bracket-quoted schema-qualified names.** `CREATE TABLE [dbo].[Patient]` binds the table name to `dbo`, because the bracket form matches the first bracketed token. Unquoted `dbo.Patient` reduces correctly to `Patient`. Strip the schema qualifier, or leave it unquoted.
- **`CHARACTER VARYING(50)`** parses as `CHARACTER`: the second word is only re-attached when it stands alone, as in `DOUBLE PRECISION`. Prefer `VARCHAR(50)` in DDL you intend to round-trip.
- **Enum values are sanitized as term ids and re-emitted as ids.** `ENUM('in progress','on-hold','DONE')` comes back as `ENUM('inProgress', 'onHold', 'DONE')`; the originals are kept as term labels. Machine-shaped enum values round-trip exactly.
- **A JSON-object comment with no SIA keys is consumed.** `COMMENT '{"owner":"team-a"}'` parses as JSON, offers no `x-maps-to` or `x-sia-role`, and is not kept as a description. Prose comments and malformed JSON are kept.
- **Apostrophes double on the inline-comment loop.** `COMMENT 'it''s fine'` re-emits as `COMMENT 'it''''s fine'`; the `COMMENT ON COLUMN` path unescapes correctly. Keep inline comments apostrophe-free.
- **`COMMENT ON COLUMN` statements are emitted unquoted**, in PostgreSQL syntax, for both `postgres` and `sqlserver` output. Identifiers containing spaces, and SQL Server targets generally, need a hand edit.
- **`ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY` is not read.** Only `CREATE TABLE` bodies contribute; declare foreign keys inline or as table-level clauses.
- **Other statements are ignored without error** - `CREATE INDEX`, `CREATE VIEW`, `INSERT`, and anything else that is not `CREATE TABLE` or `COMMENT ON COLUMN`.
- **A `COMMENT ON COLUMN` naming a column that does not exist is ignored**, silently.
- **A truncated `CREATE TABLE Broken (` succeeds with nothing in it.** The statement header matched, the body did not parse, and the result is an empty model - check that the type count is what you expected. `CREATE TABLE e1 ();` likewise yields a type with no elements.
- **Generated DDL is a description, not a migration.** No key constraints are emitted, and tables are written in model order rather than dependency order.
- **An `ENUM` column mapped through inference stops execution** with `Referenced taxonomy '<id>' does not resolve.`, because a SQL enum arrives unnamed and inference matches by label. Add a `TaxonomyMapping` to the plan, or move the table through a project's import and export, which do not run the mapping engine.

## The stance

One promise runs through all of it: never guess silently where you can preserve verbatim or report honestly. Types the model cannot hold structurally are carried as spellings; meaning the format cannot hold natively goes through the one channel SQL offers, named so you can find it; constraints that cannot be represented are reported at the exact path. That is what makes a format with no annotation slot a full citizen of a transform surface built on semantic fidelity.

For the routes and tools that drive this coder - import, export, stateless mapping, and replayable plans - see the transform API guide in the CoreModels docs.