MemberJunction / MemberJunction/MJ

CodeGen deletes EntityField rows for newly added columns on entities with a custom base view — spDeleteUnneededEntityFields runs before the outer view's sp_refreshview

Open
#4,336 0 comments 0 reactions 0 assignees View on GitHub
bug codegen-repair
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary

On an entity whose base view is application-owned (`BaseViewGenerated = 0`), CodeGen deletes the `EntityField` rows for columns added in the same run — and emits that delete into the migration *ahead of* the `sp_refreshview` that would have made it correct. Every replay of that migration on a fresh database reproduces the loss.

Found while diagnosing a clean install of bizapps-orders 5.9.0, which finishes with three `Order Header` columns (`Origin`, `FulfillmentStatus`, `SourceCheckoutWidgetID`) present in both the table and the view but with **no** `EntityField` rows — so all sync validation against that entity fails.

## Mechanism

`spDeleteUnneededEntityFields` decides a field is orphaned by comparing `EntityField` against `vwSQLColumnsAndEntityFields`, which resolves columns from the entity's **base view**:

```sql
INNER JOIN [__mj].vwSQLTablesAndEntities e
ON c.object_id = COALESCE(e.view_object_id, e.object_id)
```

For a layered entity that base view is the application-owned *outer* view (e.g. `vwOrderHeaders`, defined as `SELECT * FROM vwOrderHeadersGenerated`). SQL Server freezes a non-schemabound view's column list at CREATE time: dropping and recreating the **inner** view does not update the **outer** view's `sys.columns`. Only `sp_refreshview` on the outer view does.

CodeGen runs these in the wrong order — `packages/CodeGenLib/src/Database/sql_codegen.ts`:

- **STEP 2(e)** drops/recreates the inner generated view
- **STEP 3** (L331) `manageEntityFields` → `spDeleteUnneededEntityFields`, issued via `LogSQLAndExecute` (`manage-metadata.ts` L4263-4267), so it both executes against the live DB *and* is written into the migration
- **STEP 4.5** (L417) `emitCustomBaseViewRefreshes()` appends the outer `sp_refreshview` to the migration

The docstring on `buildCustomBaseViewRefreshSQL` already reasons about the *inner* view's cached column list being reset by its drop/recreate. The gap is that the **outer** view's cache is not reset by that, and its refresh is emitted thousands of lines after the delete that depends on it.

## Reproduction (bizapps-orders 5.9.0, clean DB)

Order Headers = `FC529BC8-FF09-44A9-B454-26EAFDAC791B`.

| Migration | EntityField INSERT | spDeleteUnneededEntityFields | sp_refreshview vwOrderHeaders | Outcome |
|---|---|---|---|---|
| V202608131542 | — | L920 | L1422 | outer-view snapshot taken here |
| V202608210130 | `Origin` L1362, `SourceCheckoutWidgetID` L1425 | **L4622** (scoped, incl. FC529BC8) | L6070 | both rows deleted |
| V202608241300 | `FulfillmentStatus` L263 | — | **absent** — rebuilds inner view, never refreshes | snapshot left stale |
| V202608252200 | — | **L6951** (scoped, incl. FC529BC8) | L10307 | `FulfillmentStatus` deleted |

This predicts exactly those three columns and no others, which is what the clean install produces.

Confirming query on an affected database:

```sql
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('__mj_BizAppsOrders.vwOrderHeaders');
```

The three columns are absent there while present on the base table.

## Blast radius

Any app with a `BaseViewGenerated = 0` entity — not specific to Orders.

Separately worth noting: in the `R__RefreshMetadata` replay position these emits call the SP with only `sys,staging` excluded and **no** `@EntityIDs` scope (e.g. bizapps-orders `V202609061900` L98). In a multi-app database that lets one app's migration prune another app's `EntityField` rows whenever the other app's outer view happens to be stale.

## Suggested fix

Refresh application-owned base views immediately after the inner views are regenerated (end of STEP 2(e) / STEP 3.5) and before STEP 3's `manageEntityFields` — in execution order *and* in emit order. STEP 4.5 can remain as a trailing safety net.

## Not a duplicate of #4050

\#4050 is the inverse: CodeGen failing to prune genuinely orphaned rows. This is over-pruning of live columns.

## Downstream status

Repaired symptomatically in bizapps-orders by `V202609061900` (shipped in v5.10.0), which re-inserts the three rows guarded on `EntityID + Name`. That migration still emits the delete (L11099) before the refresh (L11434), so the next column added to a layered entity will reproduce this.

Contributor guide

Open the contributing guide

Research direction

Start in packages/CodeGenLib/src/Database/sql_codegen.ts around STEP 2(e), STEP 3, and STEP 4.5, then trace manageEntityFields and LogSQLAndExecute in manage-metadata.ts (L4263-4267). Verify that application-owned outer views are refreshed before spDeleteUnneededEntityFields executes and that both execution and migration emission preserve EntityField rows for newly added columns.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql, typescript
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.