# Slice 5 — extending: a per-customer SQL override

There is a limit to what the metadata-driven framework can express.

A customer needs a different aggregation rule for their sales-check report.
Or a unique custom view to feed their cost dashboard. Or a stored procedure
whose behaviour deviates fundamentally from the standard. The framework's
overlay tables ([Slice 4](04-custom-field.md)) can add fields and override
SQL fragments, but they cannot replace the *logic* of a stored procedure —
the proc is code, not data.

When that limit is reached, xly's escape hatch is **to commit a hand-written
SQL file under `script/客户/<customer-name>/` and apply it directly to that
customer's schema**. This bypasses the metadata layer entirely. It is a
real channel, used in production, for several customers — and the wiki must
document it honestly: the scope, the cost, when to reach for it, and the
operational hygiene it requires.

## What we're documenting

A representative override taken from one customer directory under
`script/客户/`:

| | |
|---|---|
| **Customer** | 重庆展印 (Chongqing Zhanyin Printing) |
| **Override file** | `script/客户/重庆展印/Sp_SalSalesCheck.sql` |
| **What it replaces** | The standard `Sp_SalSalesCheck` stored procedure (sales-check listing) |
| **Companion file** | `script/客户/重庆展印/viw_salsaleschecking_pro.sql` (a custom view this proc reads from) |
| **Deployment** | **Manual** — applied by an engineer / DBA against this customer's schema |

The file's first two lines tell you everything about the channel's
shape:

```sql
DROP PROCEDURE IF EXISTS `Sp_SalSalesCheck`;
delimiter ;;
CREATE PROCEDURE `Sp_SalSalesCheck`(IN sLoginId varchar(100), …)
```

The standard proc is dropped, the custom one takes its place. The
metadata layer in `gdsmodule` continues to reference the proc by name
(`sProcName = 'Sp_SalSalesCheck'`); the runtime calls it as it always
would — the difference lives entirely inside the procedure body, which
is now the engineer-authored variant.

## Customers with overrides in the codebase

Each customer with bespoke SQL gets a subdirectory under `script/客户/`,
named for the customer (typically the Chinese trading name). Each
subdirectory holds anywhere from one to a dozen `.sql` files. Most files
are custom procs (`Sp_*`) and custom views (`viw_*`). Reading the
directory listing is itself a form of system documentation: the
customers and features here are the ones that needed bespoke behaviour
the framework couldn't express. The current set of customer directories
will grow and shrink over time — `ls script/客户/` for the live list.

## How an override gets in

There is **no automated channel**. Searching the Java codebase for
phrases like "load script/客户" or a deployment service that walks
those directories returns nothing. The mechanism is operational:

1. An engineer authors the override `.sql` (using the standard proc as a
   starting point, modifying the body).
2. The file is committed under `script/客户/<customer>/` for traceability.
3. The file is run **manually** against the target customer's MySQL
   schema using `mysql --defaults-file=… < the-file.sql` (or equivalent).
4. From that point, the customer's schema has a different proc body
   from every other customer's. The framework code is unchanged.

The repo location matters: keeping these in the codebase repo (not in a
separate "ops" area) means the engineer team can see at a glance which
customers diverge from standard. It does not mean these files ship as
part of any build.

## Why this is a different channel from Slice 4

Slice 4's customization (`gdsconfigformcustomslave`) and Slice 1's
metadata-driven Add/Update path both stay inside the framework.
Customer-specific behaviour expressed there is *layered over* the
shared codebase — every tenant's runtime reads the same Java, the same
MyBatis mappers, the same standard procs.

The Slice 5 channel is *underneath* the framework. The customer's
schema has a literally-different stored procedure with the same name.
The Java/MyBatis code calling `Sp_SalSalesCheck` has no idea whether
the standard or a 重庆展印 variant is on the other end of the call. The
framework doesn't know; the framework can't tell.

This makes overrides:

- **Powerful.** Anything you can write in MySQL stored-procedure SQL,
  you can use to replace standard behaviour.
- **Operationally fragile.** The override must be re-applied (or kept
  alive) whenever the customer's schema is rebuilt, restored, or
  migrated. It does not travel with backups of the codebase, only with
  backups of the database.
- **Hard to reason about.** A maintainer reading the standard
  `Sp_SalSalesCheck` source has to remember that *for some customers*
  the proc on the live DB is a different piece of code with the same
  name. Stack traces and "what does this proc do" depend on which
  schema you're connected to.

The right rule of thumb: prefer Slice-4 metadata customization. Reach
for Slice-5 SQL overrides only when the metadata model genuinely cannot
express what the customer needs.

## Worked-example: 重庆展印's `Sp_SalSalesCheck` vs the standard

Quantified diff against the live dev DB:

| Aspect | Standard `Sp_SalSalesCheck` (in DB) | 重庆展印 override (`script/客户/重庆展印/Sp_SalSalesCheck.sql`) |
|---|---|---|
| Body length | 1,714 lines | 723 lines (≈42 % the size) |
| Parameter signature | 14 params: `sLoginId, sCustomerId, sBrId, sSuId, bFilter, sUnTaskFormId, pageNum, pageSize, totalCount(OUT), countCloumn, countMapJson(OUT), sFilterOrderBy, sGroupby_select_sql, sGroupby_group_sql` | **Identical** — same 14 params in the same order |
| `SysSystemSettings.CbxSrcNoCheck` lookup | **Not used** | **Used** (drives "未对账印件清单来源" — which billing-type sources feed the report) |
| `Fun_GetLookCustomer(sLoginId, sBrId, sSuId)` permission scoping | Used | Used (same call) |
| Temp-table-based aggregation flow (`B1`, `B2` etc., several `DROP TEMPORARY TABLE` + `INSERT INTO` blocks) | Heavy (the bulk of the 1,714 lines) | Removed / simplified |

So 重庆展印's override:
- Keeps the framework call-site unchanged (identical parameter
  signature so the metadata-driven dispatcher
  ([proc-dispatch.md](../reference/maintainer/proc-dispatch.md)) still
  invokes it correctly).
- Adds a `CbxSrcNoCheck` system-setting branch that the standard
  doesn't expose. Twelve other `Sp_*` procs in the schema also use
  `CbxSrcNoCheck` (`Sp_Manufacture_MftWorkOrderAround`,
  `Sp_OverdueNoCheck`, `Sp_Receivables_*` family, plus sibling
  `Sp_SalSalesCheck1`/`_1227`/`_YanBao`/`_ded_copy1`); the override
  brings that pattern into the customer's main proc.
- Strips the standard's heavy temp-table aggregation flow — a simpler
  query path, not a more complex one. The customer's check semantics
  evidently don't need the full standard aggregation.

A maintainer wanting the *exact* business-rule difference should diff
the two file bodies directly:

```bash
mysql --defaults-file=$HOME/.my.cnf xlyweberp_saas_ai \
  -BNe "SELECT ROUTINE_DEFINITION FROM information_schema.routines \
        WHERE ROUTINE_NAME='Sp_SalSalesCheck'" > /tmp/std.sql
diff /tmp/std.sql script/客户/重庆展印/Sp_SalSalesCheck.sql | head -200
```

The companion view `viw_salsaleschecking_pro.sql` exists for the same
reason — when the override needs a join shape the standard doesn't
provide, the engineer authors a customer-specific view, drops it into
the same directory, and applies it to that customer's schema alongside
the proc.

## Concepts this slice introduces

- *The two customization channels* (sharpens [the existing concept page](../concepts/customization-channels.md)):
  metadata edits via BACK (slices 1, 2, 4) versus raw SQL overrides
  applied directly to a customer's schema (this slice).
- *Schema divergence between customers* — the same proc name can mean
  different procs in different customer DBs. Affects how maintainers
  reason about runtime behaviour.

## Reference entries this slice exercises

- New page (Maintainer): *Per-customer SQL overrides* — the
  `script/客户/<customer>/` convention, the manual application
  process, the operational implications, the auditing patterns that
  let a maintainer see which customers diverge from standard for which
  procs.

## Open verification items

1. **Is the application of these scripts truly entirely manual, or is
   there a Quartz-job / `DbToDbController` mechanism that loads them?**
   The `xlyFlow/dbtodb` package is named suspiciously close to "DB
   migration" but the surface area looked like inter-DB sync, not
   script application. Confirm by reading
   `DbToDbServiceImpl.java`.
2. **Auditing.** Build a small script that connects to a customer's DB
   and diffs every `Sp_*`/`viw_*` body against the standard. Customers
   running unexpectedly-divergent procs are an operational risk.
3. **Side-by-side `Sp_SalSalesCheck` diff** — the wiki currently
   describes the override structurally. A future revision should
   include the actual body diff that shows which business rule changed
   for 重庆展印 and why.
4. **Lifecycle.** When a customer migrates schemas (upgrade, restore,
   rebuild), how is each override re-applied? A documented runbook for
   that operation belongs in the maintainer chapter on deployment.