# 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: what 重庆展印's `Sp_SalSalesCheck` does differently

Skim of the file's top:

- It reads a `'CbxSrcNoCheck'` row from `SysSystemSettings` to determine
  which billing types feed the sales-check report. This is a customer-
  specific switch the standard proc may not expose.
- It calls the global `Fun_GetLookCustomer(sLoginId, sBrId, sSuId)`
  helper for permission scoping — same as the standard proc would.
- It accepts the same parameter list as the standard
  (`sLoginId`/`sCustomerId`/`sBrId`/`sSuId`/`bFilter`/`pageNum`/`pageSize`/…)
  so the framework's call-site is unchanged.

A future revision of this slice can do a side-by-side diff with the
standard `Sp_SalSalesCheck` and explain *exactly* the business rule
that diverges. For now, the structural fact is what matters: the proc
shape and parameter list are identical to the standard; the body
diverges.

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.