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:

• Capable in the technical sense. Anything you can write in MySQL stored-procedure SQL can replace standard behaviour. (This isn't a good thing per se — see drawbacks below.)
• 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.
• No version control on the deployed body. The .sql file in script/客户/ shows what should have been applied. There is no audit trail confirming what was applied (or when, or by whom), and no automated re-apply on schema rebuild.
• No type-safety bridge. When the override changes a result-set shape, every Java caller that reads from Sp_SalSalesCheck may silently break for that one customer with a BadSqlGrammarException or — worse — a wrong-shaped row that propagates as a wrong number.
• Compounds the BI problem. Charts on customers with overridden procs (bi-engine.md) will silently disagree across tenants because the underlying data is computed by different SQL.

The "prefer Slice 4, reach for Slice 5 only as last resort" advice is correct in principle, but the existence of 18 customer directories suggests that in practice this channel has become the standard answer for material business-logic differences. That's a signal the metadata model isn't expressive enough for the actual customer-customisation demand the system encounters — not a celebration of the escape hatch.

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

Worked-example 2: 万昌 builds a multi-level approval workflow

The 重庆展印 example above replaces one proc body. The script/客户/万昌/ directory shows a more ambitious pattern: the customer extends the schema and builds a multi-level approval workflow that the standard framework doesn't ship.

The customisation tree (excerpt):

script/客户/万昌/
├── 计件工资/
│   ├── 日报审核/
│   │   └── 领班驳回.sql               ← this slice's anchor
│   ├── 报表/
│   │   ├── 包装补时.sql
│   │   ├── 员工大废品.sql
│   │   ├── 班组大废品率查询报表.sql
│   │   ├── 手工质检组返工.sql
│   │   └── Sp_Manual_quality_inspection_rework.sql
│   └── 计件工资核算/
│       ├── 计件工资/
│       │   ├── sp_piece_rate_j.sql
│       │   ├── sp_piece_rate_JZ.sql
│       │   ├── sp_piece_rate_other.sql
│       │   └── sp_piece_rate_w.sql
│       ├── 员工工资汇总查询/员工工资汇总查询.sql
│       ├── Sp_BtnEven_CalcJsHs.sql
│       └── sp_btn_WorkOrderAssessmentPassRate.sql
├── Sp_getworkorder_calc_cb.sql
└── …

The Chinese-named subdirectories (计件工资/日报审核 = "piece-rate wages / daily-report approval") encode the customer's organisational flow into the file system itself. A maintainer reading ls knows which business process each script belongs to.

What the rejection script actually does

领班驳回.sql ("Foreman Rejection") is 185 lines defining Sp_mftproductionreportmaster_check1_0. The naming is xly's state-transition convention: Sp_<table>_check<currentState>_<nextState>, so check1_0 means "transition from state 1 (approved) back to state 0 (draft)" — i.e., a rejection.

Trimmed body of the central UPDATE:

SET p_setSql = CONCAT('bManager = 0,
                       bIPQC = 0,
                       bDeputy = 0,
                       bSubmit = 0,
                       bWorkshopManager = 0,
                       bCheck = 0,
                       sRejectMemo = ''', p_sRejectMemo, ''',
                       sMReserve1 = ', p_textareaValue);

Set @sSqlStmt = CONCAT('Update mftproductionreportmaster
                        Set ', p_setSql, '
                        Where sId = ''', p_sTmpId, '''
                          AND sBrandsId = ''', sBrId, '''
                          AND sSubsidiaryId = ''', sSuId, '''');
PREPARE sSqlStmt FROM @sSqlStmt;
EXECUTE sSqlStmt;

CALL sp_add_flow_log(p_sTmpId, p_sTmpId, '驳回', '驳回', '驳回',
                     sMakePerson, p_sRejectMemo, @sReturn, @sCode);

So one button click resets six approval flags simultaneously, appends to a per-row rejection-reason history, and writes to a custom audit log.

What's customer-side and not in standard

Verified against the dev DB recon target (xlyweberp_saas_ai):

Customisation	Standard schema?	万昌 needs to add it
Multi-level approval columns: bManager, bIPQC, bDeputy, bSubmit, bWorkshopManager on mftproductionreportmaster	No — only bCheck, sCheckPerson, tCheckDate exist.	Yes — ALTER TABLE to add 5 boolean columns.
sRejectMemo rejection-reason history column	No	Yes — ALTER TABLE to add a longtext.
sp_add_flow_log audit-log proc	No — does not exist in standard.	Yes — wholly customer-defined.
Naming convention Sp_<table>_check<n>_<m>	No — no procs in DB use this pattern.	Yes — 万昌's convention.
Hook into the framework's button machinery	Yes — gdsconfigformslave.sButtonParam points at the proc name.	(configuration only)

So 万昌's "Foreman Rejection" workflow is a customer-built state-machine atop xly's button primitive: schema extension + custom procs + custom audit log. The framework provides only the button-press dispatch (via /procedureCall/doGenericProcedureCall or the button-param hook on the form-slave). Everything else — what state the document is in, what flags toggle, what audit text gets logged — is customer-side.

This is fundamentally different from how Activiti would solve the same problem (BPMN graph + assignee model + Activiti's task table). xly's framework lets the customer choose either model:

• Activiti style: deploy a BPMN, link via gdsmoduleflow, flip ConstantUtils.bCheckflowCheck = true (see activiti.md).
• 万昌 style: extend the schema, write transition procs, drop them under script/客户/<name>/<flow-name>/, apply manually.

The 万昌 style is what the codebase actually shows in production- adjacent customisations — Activiti is wired but no customer directory under script/客户/ deploys a BPMN, while 万昌-style schema-extending workflows DO show up. That's the empirical answer to "how is workflow customised in this repo?": schema-extending stored procs delivered via per-customer override scripts.

Customer customisation patterns at a glance

Of the 18 customer override directories, most don't customise workflow per se — they customise calculations and reports. The breakdown of what each directory contains:

• 万昌 (14 files): includes the 领班驳回.sql workflow extension, plus piece-rate wage calculation procs.
• 千彩 (50 files): the most heavily customised customer. Mostly per-tenant calculation overrides (Sp_Calc_*, Sp_Inventory_*, Sp_Manufacture_*) and one workflow-list view (viw_NoSalSalesChecking).
• 重庆展印 (2 files): replacement of one sales-check proc + a companion view, as documented above.
• 朝阳 (8), 金宣发 (8), 无锡中江 (8), 亚明威 (6), 福雅 (5), 金九 (5), 快马 (4), and others: smaller calc / report overrides.

So the workflow customisation pattern (schema extension + transition procs + custom audit) is rare — it's worth doing only when the customer's process genuinely doesn't fit a single-step approval and the standard framework's bCheck toggle isn't enough. Most customer divergence is calculation logic, not workflow shape.

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 manual, or is there a Quartz/DbToDb mechanism? CLOSED — manual. xlyFlow/.../dbtodb/service/impl/DbToDbServiceImpl.java is inter-DB sync (its public methods are getData, getDataDetail, getDataCount, addSave, execute, select, testConnect — all working over DruidDataSource + DruidProperties + JdbcUtils against the customer's own remote DB). It is not a script-applier — there is no walk-the-script/客户/ directory step anywhere in the in-scope codebase (grep -rn "script/客户" xly-src/.../*.java returns zero hits). Each script/客户/<customer>/<file>.sql is committed for traceability and applied manually by an engineer / DBA via mysql --defaults-file=… < the-file.sql.
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. (Future-work backlog item — not a verification claim. The audit query against any single tenant DB is one statement, but automating it across the customer fleet is the work.)
3. Side-by-side Sp_SalSalesCheck diff. The structural diff table above (size, params, key SQL features, CbxSrcNoCheck branch) covers the shape of the divergence; a body-level diff showing the exact business rule difference would deepen this. (Future-work backlog item — the copy-pasteable command above produces it on demand; embedding the full diff in the wiki was judged not worth the page weight.)
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.