SQL templates (xlyEntry/templesql/)

The framework keeps a small library of SQL scaffolds under xlyEntry/src/main/resources/templates/templesql/. They are not used at runtime to generate SQL on the fly — they're templates an engineer copies and fills in when authoring a new stored procedure that the framework will invoke through generic procedure dispatch.

The scaffolds shipped

Eight files ship in the live tree (xlyEntry/src/main/resources/templates/templesql/):

File	What it scaffolds
sSaveProName.sql	A standard save procedure with the conventional parameter list and OUT codes
sSaveProNameBefore.sql	A pre-save validation procedure
sDeleteProName.sql	A standard delete procedure
sProcName.sql	A generic logic procedure (audit/calc/check)
sButtonParam.sql	A procedure invoked by a button press, parameterised through gdsconfigformslave.sButtonParam
sp_btn_calc.sql	A button-driven calculation procedure
sProTempleByDb.sql	A procedure that switches behaviour by database / tenant
sSqlStr.sql	A SELECT-shape scaffold for gdsconfigformmaster.sSqlStr

Placeholder convention

Every scaffold uses {placeholderName} syntax (single braces, no escaping). A real-world example from sSaveProName.sql:

DROP PROCEDURE IF EXISTS {sProName};
CREATE PROCEDURE {sProName}(
    IN sGuid VARCHAR(100),
    IN sFormGuid VARCHAR(100),
    IN sLoginId VARCHAR(100),
    IN sBrId VARCHAR(100),
    IN sSuId VARCHAR(100),
    OUT sCode INT(1),
    OUT sReturn LONGTEXT
)
BEGIN
    -- … placeholder logic …
    Update {sMasterTableName} AS A
        JOIN {sSlaveTableName} AS B
        ON A.sId = B.sProductId
    SET A.dProductQty = B.dProductQty
    WHERE B.sId = sGuid
      AND B.sBrandsId = sBrId
      AND B.sSubsidiaryId = sSuId;
END

The engineer copies this file, fills in {sProName}, {sMasterTableName}, {sSlaveTableName}, replaces the placeholder logic with the real business rules, and applies the resulting SQL to the target schema.

What the placeholders are for

• {sProName} — the procedure's name (e.g., Sp_Sales_Save).
• {sMasterTableName}, {sSlaveTableName}, {sAllTableName} — the document family the proc operates on.
• Other placeholders depending on the scaffold.

"Template" library, not a code generator — and what that costs

The framework does not auto-generate procs from these templates based on metadata. The scaffolds are convention-enforcing copy-paste starters, nothing more. They exist to nudge a new proc into the shape that generic dispatch can call:

• Standard parameter list (sGuid, sFormGuid, sLoginId, sBrId, sSuId).
• Returns success/error via OUT sCode INT, OUT sReturn LONGTEXT.
• Honours the multi-tenant filter sBrandsId = sBrId AND sSubsidiaryId = sSuId.

Costs of staying at "template" instead of "generator":

• No enforcement. A proc that drifts from the convention compiles fine. The framework discovers the mismatch at runtime as a BadSqlGrammarException or wrong-shaped result. There is no pre-merge check.
• No regeneration. When the convention itself changes (e.g., a new standard OUT param), the existing procs do not update. Engineers have to grep + rewrite, with no automation.
• No knowledge of which proc came from which template. A proc in the live DB doesn't record its origin scaffold; understanding what was customised away requires diffing against the scaffold by hand.
• Customer overrides under script/客户/ can — and do — diverge from the scaffold shape. This is reasonable per customer but means the conventions are observed by social contract, not by any mechanical check.

A real code-generation pipeline (template + metadata → emitted SQL, checked in or applied at deploy time) would catch these. The trade xly made: less tooling to maintain, but discipline-rather- than-enforcement on proc shapes — visible in the 1,687 procs the schema currently carries, not all of which follow the conventions.

Two loaders

There are two classes named FileSqlUtil in the codebase, with very different reliability:

Loader	What it points at	State
xlyFlow/src/main/java/com/xly/sqltemplate/util/FileSqlUtil.java	The 8 scaffolds above	All 8 files exist — this is the loader BACK's script-authoring screen actually uses.
xlyApi/src/main/java/com/xly/api/util/FileSqlUtil.java	7 different template constants: sInSqlStrTemple.sql, sOutSqlStrTemple.sql, sDataSqlTmpDef.sql, sDataSqlTmp.sql, sJsonSqlTmp.sql, sDbPro.sql, sJsonSqlTmpOut.sql	None of these files exist in the live tree. The constants are dead source — likely from an older xlyApi codepath that no longer ships templates. Calls through them will throw FileNotFoundException at runtime.

A maintainer touching the xlyApi FileSqlUtil should treat it as broken-by-default and either restore the missing templates or remove the loader.

See also

• Generic procedure dispatch — how the framework invokes the procs once they're authored.
• Slice 5 (per-customer SQL override) — when a customer needs a replacement of a standard proc, which reuses the same template structure but DROPs and CREATEs an override on the target schema.