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) 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:

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) 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:

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): 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.