# Access → SQL Express Schema Mirroring

How Access column and constraint semantics are mirrored into SQL Express. For sync flow and orchestration, see [sync-architecture.md](sync-architecture.md).

Types are inferred from OLE DB CLR types (`bool`, `int`, `string`, …), not Access type enums (`dbMemo`, `dbCurrency`).

| Component | Role |
|-----------|------|
| [`AccessSchemaReader`](../SyncEngine/Access/AccessSchemaReader.cs) | Tables, columns, AutoNumber, MSys metadata |
| [`AccessApplicationSysReader`](../SyncEngine/Access/AccessApplicationSysReader.cs) | COM/DAO fallback, MSys GRANT, field metadata |
| [`AccessIndexGrouper`](../SyncEngine/Access/AccessIndexGrouper.cs) | Groups `MSysIndexes` rows into composite indexes |
| [`AccessTypeMapper`](../SyncEngine/Access/AccessTypeMapper.cs) | CLR type → SQL Server column type |
| [`AccessDefaultTranslator`](../SyncEngine/Access/AccessDefaultTranslator.cs) | `DefaultValue` → `DEFAULT` literal |
| [`AccessValidationTranslator`](../SyncEngine/Access/AccessValidationTranslator.cs) | `ValidationRule` → `CHECK` predicate |
| [`SqlSchemaKeyBuilder`](../SyncEngine/SqlServer/SqlSchemaKeyBuilder.cs) | PK/index DDL and naming |
| [`SqlIdentityHelper`](../SyncEngine/SqlServer/SqlIdentityHelper.cs) | AutoNumber detection, rebuild, `IDENTITY_INSERT` |
| [`SqlSchemaProvisioner`](../SyncEngine/SqlServer/SqlSchemaProvisioner.cs) | All DDL orchestration |

---

## Provisioning order

Leg 1 runs these steps in order (see [`SyncOrchestrator`](../SyncEngine/Sync/SyncOrchestrator.cs)):

1. **Discover tables** — OleDb `GetSchema("Tables")`, column metadata via `SchemaOnly | KeyInfo`
2. **Enrich from DAO** — `DefaultValue`, `ValidationRule`, table-level rules via `Access.Application`
3. **Read MSys metadata** — queries, indexes, FKs (OleDb → COM GRANT → retry → DAO fallback)
4. **Sort tables** — topological order by FK parent→child
5. **Provision tables** — see [Table phase](#table-phase) below
6. **Provision keys and indexes** — PKs first, then secondary indexes
7. **Provision foreign keys** — with cascade when defined in Access
8. **Provision CHECK constraints** — field/table validation rules where translatable
9. **Provision views** — translated Access QueryDefs
10. **Data sync** — skipped in `schema-only` mode

### Table phase

Within `SqlSchemaProvisioner.ProvisionTables`, per table:

1. **Identity rebuild** — if Access reports AutoNumber but SQL Express column is plain `INT`, rebuild table via staging (`{table}__identity_src`)
2. **CREATE or ALTER** — new tables get inline `DEFAULT` constraints where translatable; brownfield tables get missing columns only
3. **Column defaults** — `ALTER TABLE … ADD CONSTRAINT … DEFAULT` for defaults not already inline
4. **NOT NULL tightening** — `ALTER COLUMN` when Access `Required` and column has no NULL rows

Four **sync columns** are appended to every mirrored table (`_sync_row_hash`, `_sync_is_deleted`, `_sync_last_seen_at`, `_sync_updated_at`). Indexes on `_sync_*` columns are never provisioned.

---

## Schema discovery

### Tables and columns

- User tables via OleDb `GetSchema("Tables")`
- System tables excluded: `MSys*`, type `p`, `~TMPCLP*`
- `ACCESS_SQL_EXCLUDE_TABLES` removes tables from sync **and** from FK/index provisioning
- Column nullability from OleDb `AllowDBNull`
- **AutoNumber** from OleDb `IsAutoIncrement` (`CommandBehavior.KeyInfo`)

### Indexes and primary keys

Discovery order:

1. OleDb `MSysIndexes` joined to `MSysObjects` (composite keys grouped by [`AccessIndexGrouper`](../SyncEngine/Access/AccessIndexGrouper.cs))
2. If empty after GRANT retry → DAO `TableDefs.Indexes` via `Access.Application`
3. Internal index names starting with `~` are ignored
4. Primary key columns are copied onto `AccessTableSchema.PrimaryKeyColumns` for data sync

### Foreign keys

1. OleDb `MSysRelationships` (`grbit` for cascade flags)
2. DAO `CurrentDb.Relations` via COM fallback
3. Filtered by `ACCESS_SQL_EXCLUDE_TABLES`

Cascade mapping: `grbit & 256` → `ON UPDATE CASCADE`; `grbit & 4096` → `ON DELETE CASCADE`.

### Field metadata (DAO)

After OleDb table read, `EnrichColumnMetadataFromDao` opens `Access.Application` once per run to read:

- `TableDefs.Fields.DefaultValue`
- `TableDefs.Fields.ValidationRule`
- `TableDefs.ValidationRule` (table-level)

This requires full Access or Access Runtime (ACE/DAO) and a readable `.mdb`. Full Access uses CreateInstance; Runtime uses headless `DAO.DBEngine.OpenDatabase` with the database password (see [getting-started.md](getting-started.md#access-runtime-vs-full-access-com--dao)). Failures are logged; provisioning continues with OleDb-only metadata.

### Queries / views

1. OleDb `MSysObjects` (`Type=5`)
2. COM `CurrentDb.QueryDefs`
3. Manual [`config/access-views.json`](../config/access-views.json)

---

## Constraints and indexes

| Access artifact | SQL Express | Discovery |
|-----------------|-------------|-----------|
| Primary key | `PRIMARY KEY CLUSTERED` | `MSysIndexes` / DAO `TableDefs.Indexes` |
| Unique index | `CREATE UNIQUE NONCLUSTERED INDEX` | same |
| Unique on nullable column | **Filtered** unique index `WHERE [col] IS NOT NULL` | Access allows multiple NULLs; SQL Server unique indexes treat NULL as equal |
| Non-unique index | `CREATE NONCLUSTERED INDEX` | same |
| Foreign key | `ALTER TABLE ADD FOREIGN KEY` | `MSysRelationships` / DAO `Relations` |
| ON UPDATE CASCADE | `ON UPDATE CASCADE` on FK | `MSysRelationships.grbit` / DAO `Relation.Attributes` |
| ON DELETE CASCADE | `ON DELETE CASCADE` on FK | same |
| Field DefaultValue | `DEFAULT` constraint | DAO `TableDefs.Fields.DefaultValue` |
| Field/table ValidationRule | `CHECK` constraint (subset) | DAO `ValidationRule` |
| Required field | `NOT NULL` on column | OleDb `AllowDBNull`; tightened on existing columns when safe |

### Constraint naming

| SQL object | Pattern |
|------------|---------|
| Primary key | `PK_{table}` |
| Unique / non-unique index | `UQ_{table}_{indexOrColumns}` / `IX_{table}_…` (max 128 chars) |
| DEFAULT | `DF_{table}_{column}` |
| CHECK | `CK_{table}_{column}` or `CK_{table}_table` |
| FOREIGN KEY | `FK_{child}_{parent}_{column}` (max 120 chars) |

---

## Best-effort provisioning and statistics

Constraint DDL is **best-effort**: failures log a `WRN` and increment `*Skipped`; the run continues (exit **0** unless an unhandled exception occurs).

On **re-runs**, objects that already match Access semantics increment `*Unchanged`, not `*Skipped`:

| Counter | Incremented when |
|---------|------------------|
| `*Created` | New object added this run |
| `*Unchanged` | Already exists / duplicate index signature / default already on column |
| `*Skipped` | **Failure only** — SQL error, untranslatable default, untranslatable validation rule, NULL data blocking NOT NULL |

Example successful re-run summary line:

```text
Schema: discovered=41 … pksCreated=0 pksUnchanged=35 pksSkipped=0 indexesCreated=0 indexesUnchanged=77 indexesSkipped=0 defaultsCreated=0 defaultsUnchanged=134 defaultsSkipped=0 …
```

Non-zero `*Skipped` always warrants checking the log for `WRN` lines.

---

## Data types

| Access | SQL Express | Notes |
|--------|-------------|-------|
| Yes/No | `BIT` | |
| Byte | `TINYINT` | |
| Integer | `SMALLINT` | |
| Long Integer | `INT` | |
| Long Integer (large) | `BIGINT` | |
| AutoNumber | `INT IDENTITY(1,1)` / `BIGINT IDENTITY(1,1)` | OleDb `IsAutoIncrement`; brownfield rebuild when mismatch |
| Single | `REAL` | |
| Double | `FLOAT` | |
| Currency | `FLOAT` | Not `MONEY` / `DECIMAL` — precision may differ |
| Date/Time | `DATETIME2` | Not legacy `DATETIME` |
| Replication ID | `UNIQUEIDENTIFIER` | |
| OLE Object / Attachment | `VARBINARY(MAX)` | |
| Text (≤ 4000 chars) | `NVARCHAR(n)` | |
| Memo / Long Text | `NVARCHAR(MAX)` | When OLE DB reports size 0 or > 4000 |
| Unknown CLR type | `NVARCHAR(MAX)` | Fallback |

### AutoNumber and identity rebuild

- New mirrors create `IDENTITY(1,1)` on the AutoNumber column.
- Data sync uses `SET IDENTITY_INSERT ON`, inserts Access-assigned values, then `DBCC CHECKIDENT (N'dbo.table', RESEED, n)` after each table ([`SqlIdentityHelper`](../SyncEngine/SqlServer/SqlIdentityHelper.cs)).
- Existing plain-`INT` mirrors are **rebuilt automatically** when Access reports AutoNumber:
  1. Copy data to `{table}__identity_src` staging table
  2. Drop constraints/indexes on staging (avoid name collisions)
  3. Drop original table; recreate with `IDENTITY`
  4. Copy data back with `IDENTITY_INSERT`
  5. Reseed and drop staging
- Interrupted rebuilds resume when the live table is missing but staging still exists.

### Default values

Translated by [`AccessDefaultTranslator`](../SyncEngine/Access/AccessDefaultTranslator.cs):

| Access expression | T-SQL `DEFAULT` |
|-----------------|-----------------|
| `Now()` / `Date()` / `Time()` | `GETDATE()` |
| `True` / `False` / `Yes` / `No` | `1` / `0` |
| `#1/1/2000#` (date literal) | `'yyyy-MM-dd HH:mm:ss.fff'` |
| Numeric literal | unquoted number |
| `""` / `''` | `N''` |
| `"text"` / `'text'` | `N'…'` (escaped) |
| Leading `=` | Stripped before translation |
| `Null` | No DEFAULT provisioned (implicit NULL) |

Unrecognized expressions increment `defaultsSkipped` with a `WRN` log.

Defaults are applied **inline on CREATE TABLE** where possible, then again via `ALTER TABLE` for brownfield columns missing a default.

### NOT NULL tightening

For columns where Access marks `Required` (`AllowDBNull = false`):

1. Skip if column already `NOT NULL` in SQL Express
2. Skip if column contains any NULL rows (`nullSkipped` + warning)
3. Otherwise `ALTER COLUMN … NOT NULL` using full type from `AccessTypeMapper` (not string replace on `NULL`)

AutoNumber columns are never altered for nullability.

### Validation rules (CHECK constraints)

Translated subset via [`AccessValidationTranslator`](../SyncEngine/Access/AccessValidationTranslator.cs):

| Access pattern | T-SQL |
|----------------|-------|
| `>=0`, `<=100`, etc. | Column name prefixed automatically |
| `Is Not Null` / `Is Null` | `IS NOT NULL` / `IS NULL` |
| `And` / `Or` / `Not` / `Like` / `Between` | `AND` / `OR` / `NOT` / `LIKE` / `BETWEEN` |
| `True` / `False` | `1` / `0` |

Rejected: multi-statement rules, SQL keywords (`SELECT`, `EXEC`, …), untranslatable expressions.

`checksCreated=0` on a database with no persisted `ValidationRule` properties is expected.

---

## Value literals

Used when emitting SQL literals (e.g. `WHERE [pk] > …`):

| Access value | SQL literal |
|--------------|-------------|
| `True` / `False` | `1` / `0` |
| `DateTime` | `'yyyy-MM-dd HH:mm:ss.fff'` |
| `byte[]` | `0x` + hex |
| Numbers | unquoted |
| Strings | `N'…'` with `'` doubled |
| `null` | `NULL` |

---

## Value comparison (change detection)

When hashing row values for change detection:

| Access / CLR value | Normalized form |
|--------------------|-----------------|
| `DateTime` | UTC ISO 8601 |
| `bool` | `1` / `0` |
| `byte[]` | Base64 |
| `null` | empty string |

---

## Names and identifiers

| Item | Access | SQL Express |
|------|--------|-------------|
| Table / column names | e.g. `Switchboard Items`, `[Col A]` | **Same names** — always `[bracketed]` |
| Schema | — | `[dbo].[TableName]` (configurable via `SQL_EXPRESS_SCHEMA`) |
| Saved queries | QueryDefs | Same-name `VIEW` objects |

No table or column renames. Bracket quoting only for reserved words.

---

## View SQL (Access → T-SQL)

| Access | T-SQL |
|--------|-------|
| `PARAMETERS …;` header | stripped |
| `And` / `Or` / `Not` | `AND` / `OR` / `NOT` |
| `IIf(a, b, c)` | `CASE WHEN a THEN b ELSE c END` |
| `Nz(x)` | `ISNULL(x, '')` |
| `#2025/01/01#` | `'2025-01-01'` |
| `Is Null` / `Is Not Null` | `IS NULL` / `IS NOT NULL` |
| `&` (concat) | `+` |
| `True` / `False` | `1` / `0` |
| `table.*` in SELECT | explicit column list (from Access schema catalog) |
| trailing `ORDER BY` | stripped |
| `TRANSFORM` / `CROSSTAB` | **not translated** — view skipped |

Untranslatable view SQL can be supplied manually in [`config/access-views.json`](../config/access-views.json).

---

## Known limitations

| Access feature | Behaviour |
|----------------|-----------|
| Currency | Stored as `FLOAT`, not fixed decimal |
| Multiple NULLs in unique fields | Matched via filtered unique index on nullable columns |
| `MSysIndexes` OleDb read | Often denied; DAO fallback used (warning logged) |
| No relationships in `.mdb` | `fksCreated=0`; no FK constraints on SQL Express |
| `DateAdd`, `Format`, `DCount`, `Val`, … | Not mapped in views or defaults |
| `TRANSFORM` / `CROSSTAB` | View skipped |
| Form/UI-only validation | Only persisted `ValidationRule` properties |
| Complex validation expressions | Skipped when untranslatable |
| `config/table-overrides.json` | Documented but not implemented |

---

## Related

- [sync-architecture.md](sync-architecture.md) — Leg 1 pipeline, data sync, failure modes
- [configuration.md](configuration.md) — `ACCESS_SQL_EXCLUDE_TABLES`, connection settings
- [getting-started.md](getting-started.md) — first-run sequence including `schema-only`