MemberJunction / MemberJunction/MJ

T-SQL→Postgres migration translation lowercases unquoted mixed-case schema names (breaks BizApps Common + codegen on PG/BI)

Open
#2,977 0 comments 0 reactions 1 assignee Claimed by @bc-izygmunt View on GitHub
Dominant language
TSQL
Stars
29
Forks
6
Avg merge
2d 1h
Merged PRs (30d)
323

Description

## Summary
When a T-SQL migration creates a **mixed-case schema** with an **unquoted** identifier, running it on Postgres (BI / AWS-managed instances) **folds the name to lowercase**, diverging from what all generated metadata expects. This breaks CodeGen and any app that ships its own schema.

Concrete case — BizApps Common (`bizapps/V202602271452__v1.0.x_Schema_and_Tables.sql`):
```sql
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '__mj_BizAppsCommon')
EXEC('CREATE SCHEMA __mj_BizAppsCommon'); -- unquoted
```
- **SQL Server:** identifier is case-preserving → schema is `__mj_BizAppsCommon`.
- **Postgres (translated):** unquoted identifier is folded → schema becomes **`__mj_bizappscommon`**.

## Impact (observed on a live 5.43 PG/BI instance)
- Physical schema is `__mj_bizappscommon`; **all** metadata + 182 repo files reference the canonical `__mj_BizAppsCommon`.
- `__mj.Entity` ends up with **both** casings (10 canonical PascalCase entities pointing at a schema that doesn't exist by that name, + 10 auto-discovered lowercase duplicates suffixed `____mj_bizappscommon`).
- `mj codegen` then fails with **"missing create/update/delete routine"** for every BizApps entity (can't reconcile the casing) → `exit 1`.
- Because the connector/RSU apply *also* runs codegen, this poisons connector table-setup too.

## Second, related defect — schema ownership
The migration runs as the **Aurora master user (`mjadmin`)**, so the schema is **owned by `mjadmin`**. But CodeGen runs as **`MJ_CodeGen`**, which is *not* the owner and isn't granted ownership — so even with correct casing, CodeGen can't create SPs/views in that schema (`must be owner of schema`). App-shipped schemas should be created as / `ALTER SCHEMA ... OWNER TO` the codegen role (it already `GRANT`s to `cdp_UI/cdp_Developer/cdp_Integration` but not ownership to the codegen user).

## Expected
The SQL Server → Postgres migration translation should **quote** identifiers that aren't all-lowercase (schema/table/column), so mixed-case names survive (`CREATE SCHEMA "__mj_BizAppsCommon"`). And app schemas should be owned by / granted to the CodeGen role used for runtime + GHA codegen.

## Repro
1. PG/BI instance (5.43).
2. Install an app whose T-SQL migration does an unquoted `CREATE SCHEMA `.
3. Observe the schema is created lowercase; `mj codegen` fails with "missing routine" for that schema's entities.

## Workaround
Bracket/quote the identifier so the PG translation quotes it (`EXEC('CREATE SCHEMA [__mj_BizAppsCommon]')`), and `ALTER SCHEMA "__mj_BizAppsCommon" OWNER TO "MJ_CodeGen"`. Existing instances need a one-off `ALTER SCHEMA ... RENAME` (as the owner) + dedupe of the auto-discovered duplicate entities + a codegen re-run.

## Version
- `@memberjunction/cli` / codegen-lib **5.43.0**, Postgres (Aurora), BI/AWS-managed.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.