# 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](proc-dispatch.md).

## 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`:

```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](proc-dispatch.md) 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](proc-dispatch.md) — how the framework
  invokes the procs once they're authored.
- [Slice 5 (per-customer SQL override)](../../slices/05-customer-sql-override.md)
  — 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.