# How to define a virtual table

A *virtual table* in xly is a "table" declared as metadata, not as DDL.
The framework knows about it because rows in `gdsconfigtbmaster` and
`gdsconfigtbslave` describe its shape; the actual data is stored in a
real physical table, but the *abstraction* the framework operates over
is the metadata declaration.

This is distinct from a database **view**, which is a `CREATE VIEW`
SQL object. Both can back a form via `gdsconfigformmaster.sType =
'table'` or `'view'`.

## What virtual tables are for

- Letting a PM declare "I need a thing of this shape" without an
  engineer running `CREATE TABLE`.
- Defining the shape that downstream forms can layer on top of.
- Centralising tenant-aware column definitions so multiple forms
  reading the same shape can share defaults.

Virtual tables cover lookup tables, classification trees, and
configurable parameter sets. The catalog grows freely as PMs add new
shapes.

## Recipe

### 1. The virtual-table master — `gdsconfigtbmaster`

One row per virtual table:

| Column | Value |
|---|---|
| `sId` | unique virtual-table ID |
| `sChinese` / `sEnglish` / `sBig5` | display name |
| `sBrandsId` / `sSubsidiaryId` | tenant scope |
| `sTbName` | the underlying physical name. **In practice this points at a table, view, *or* stored procedure** — the column is unique-keyed but otherwise unconstrained. The runtime resolves it as a generic SQL identifier. |
| `sParentId` | parent virtual table for tree-style classifications (empty for flat tables) |
| `iOrder` | sort order in the BACK list |

### 2. The columns — `gdsconfigtbslave`

One row per column. Each row carries the column's name, type, default,
display label, validation, and whether it's part of the primary key.

## What `sTbName` actually points at — and the drift

Every `gdsconfigtbmaster` row carries a non-empty `sTbName`, but the
column is just a unique-keyed string — the framework doesn't enforce
that it resolves to a base table. Verified against the live dev DB:

- 307 `gdsconfigtbmaster` rows total.
- **296 (96.4 %) resolve to a real `BASE TABLE`** in
  `information_schema.tables`.
- **11 (3.6 %) do not resolve** — and the breakdown is itself
  informative:

  | Where the unresolved `sTbName` actually points | Count | Examples |
  |---|---:|---|
  | A view (`viw_*`) instead of a table | 4 | `viw_mftproductionreport`, `viw_mftproductionreportEmployee1` |
  | A stored procedure (`Sp_*`) | 3 | `Sp_Cashier_BankJournal`, `Sp_Cashier_SumJournal`, `Sp_Sales_NotDeliverGoodNotifyList` |
  | A real table that exists under a different case-folded name, or a renamed/dropped object | 4 | `QlyProcessTestResult` (case drift), … |

So `sTbName` is **not** strictly "the physical table name" — it is the
generic SQL identifier the runtime will substitute into the read query,
which can equally point at a view or a callable proc. The wiki's
earlier framing ("the underlying physical table name") was too narrow.

Audit pattern that surfaces drift:

```sql
SELECT sId, sChinese, sTbName
FROM gdsconfigtbmaster
WHERE sTbName NOT IN (
  SELECT TABLE_NAME FROM information_schema.tables
  WHERE TABLE_SCHEMA = DATABASE()
);
```

## When to choose virtual table vs. view vs. table

| Need | Choose |
|---|---|
| A new shape, declared by a PM, backed by a real (possibly pre-existing) table | virtual table |
| A read-only join across existing tables | a database view |
| A new shape that genuinely needs storage no existing table provides | a real `CREATE TABLE` (engineer task) |

The virtual-table channel is the framework's "type system" for
data-driven shapes; the physical schema is what actually stores rows.
The two are deliberately decoupled.

## Worked example — `包装方式` (Packing-method lookup)

A representative real row from the dev DB:

**Master** (`gdsconfigtbmaster`):

```
sId       = 192116810113315231587698560
sChinese  = 包装方式 (Packing method)
sTbName   = SisPacking
sParentId = 192116810113315231564967560 (parent classification row)
```

**Slave columns** (`gdsconfigtbslave`, 10 rows under that `sParentId`)
declare the *logical* shape — names, display labels, validation. The
*physical* shape lives in the real `SisPacking` table:

| Slave row | Backing physical column on `SisPacking` |
|---|---|
| `iIncrement` (自增列, auto-increment) | `iIncrement int auto_increment PK` |
| `sId` (标准ID) | `sId varchar(100) UNIQUE` |
| `sBrandsId` (加工商Id) | `sBrandsId varchar(100)` |
| `sSubsidiaryId` (子公司Id) | `sSubsidiaryId varchar(100)` |
| `tCreateDate` (制单日期) | `tCreateDate datetime DEFAULT CURRENT_TIMESTAMP` |
| `sMakePerson` (制单人) | `sMakePerson varchar(255)` |
| `iOrder` (排序号) | `iOrder int DEFAULT 0` |
| `sName` (名称) | `sName varchar(255)` |
| `sNo` (编号) | `sNo varchar(255)` |
| `bInvalid` (作废) | `bInvalid bit(1) DEFAULT b'0'` |

The 10 slave rows in `gdsconfigtbslave` map exactly to the 10 columns
on the physical `SisPacking` table. A PM can then point a
`gdsconfigformmaster` row at `sTbName='SisPacking'`, and the
form-slave rows reference the same columns by name. The framework
glues the two layers together at runtime — same metadata-driven path
as Slice 1.

This page used to flag a worked example as a TODO; this is it.