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:

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 = (root)

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.