Day 5：FormSchema 如何驅動 SQL

The previous post, The Purpose and Classification of Definition Files, laid the definition files out one by one, and FormSchema is the hub among them. Today, a concrete question: when a form queries and when it saves, where do those SQL statements come from?

This post covers:

1. The six places a new form touches, with zero lines of code
2. Querying: the join list and paging, both decided on every request
3. Saving: how a set of fields becomes INSERT, UPDATE and DELETE
4. Why this is not an ORM, and why the missing pieces are not omissions
5. The difference between a thousand definitions and a thousand classes

1. One more form, six places to touch

The first post, Why ERP Needs a Definition-Driven Framework, counted adding one field to an existing form. This counts a form that is entirely new:

Three new files, three added rows, zero lines of code, plus one more TableSchema if the master has details. The only one that has to be written from scratch is FormSchema. The other two can be derived from it and then adjusted. Seven of the eight forms in the case study grew exactly this way.

None of those six is a restatement. Each answers a different question: what this form has, what the physical storage looks like, how it is arranged on screen, which database it belongs to, who executes it, and where you click in.

Given that not a line of code was written, what runs when you press query, or press save? The same FormSchema holds up both, using completely different parts of itself:

• Querying uses the relationship declarations, to assemble joins
• Saving uses the field set, to assemble INSERT, UPDATE and DELETE

2. Querying: the join is looked up, not written

An earlier post, The Northwind Case Study: Eight Forms and a Custom Business Object, covered relationships being declared between forms, with no table name in the declaration. What the framework does with it is short:

RelationProgId (the program identifier)
  -> fetch that program's FormSchema
    -> take its master table
      -> take the physical table name
        -> join on that table's sys_rowid

That extra hop returns two things.

One when writing. What you declare is that an order points at a customer, not that some column of ft_order points at the primary key of ft_customer. When relationships live between tables, the author has to translate the business relationship into storage terms first. Between forms, what you write down is the thing you had in mind, with one less translation in between.

One when changing. The physical table name is written only inside that form's own structure, and other forms point at it with a program identifier. Renaming the customer table means editing the customer's definition, and every form pointing at it stays untouched. With table names hard coded everywhere, that becomes a global search that nobody can certify as complete.

The join list is computed fresh every time

The order list shows six columns, and the query the framework assembles is:

SELECT A.[sys_id], A.[order_date],
       B.[sys_name] AS [ref_customer_name], C.[sys_name] AS [ref_employee_name],
       A.[status], A.[total_amount]
FROM [ft_order] A
LEFT JOIN [ft_customer] B ON A.[customer_rowid] = B.[sys_rowid]
LEFT JOIN [st_employee] C ON A.[employee_rowid] = C.[sys_rowid]

The order master declares three relationships, and this statement joins two tables. The list does not show the shipper, so that table never enters.

There is only one rule:

The framework first collects the fields this query will touch into one list, from three sources, being the fields to select, the fields appearing in conditions, and the fields used for sorting. Whichever relationship fields appear in that list decide which tables are joined.

So "used" is not the same as "displayed":

• Filtering the list by customer name joins the customer table for the WHERE clause, even though it is not among the displayed columns
• Opening a single record brings in all three joins, because all three columns are displayed
• Selecting only the master's own columns produces no joins at all

Condition values always go through parameters and never into the SQL text.

Nowhere is the answer to "which tables does an order join" stored. The cost is moving "what does this SQL look like" from build time to every single request, which adds a statement assembly step per query. It is the fixed overhead of this route, not something that can be optimized away.

The same declaration is also at work on the screen side. A field with a relationship becomes a lookup window editor in that cell, picking one record from the source form. The declaration the join reads and the declaration the editor reads are the same one.

Display columns do not exist in the table

The ref_customer_name in that statement is an alias. The physical order table has only customer_rowid.

• When the front end finishes picking, it writes the code and the name into the data it is holding, purely so the screen updates immediately
• Those columns are not written to the database on save, and the next read has the server join them in again
• The authoritative value is always on the query side

So there is never a case of a customer changing its name and old orders still showing the old one. The cost is one join per display.

Which is correct depends on whether the field wants the value as it was or the value as it is. A name for display wants the current one; the unit price agreed at the time wants the historical one, and that one should have been a column on the order itself anyway. This line has to be drawn by the framework first, or each form decides for itself and one system ends up with two behaviors.

Declared one level deep, executed several levels deep

The employee form brings in the department name, and also the manager's name. Its relationship field points at department, and one of the mappings names ref_manager_name as its source field, which on the department form is itself a relationship field pointing at employee, for the manager. So it has to go one level further down:

FROM [st_employee] A
LEFT JOIN [st_department] B ON A.[dept_rowid] = B.[sys_rowid]
LEFT JOIN [st_employee] C ON B.[manager_rowid] = C.[sys_rowid]

st_employee appears twice. A is the employee, C is their manager.

Every form declares only the level it points at directly, and that level is how the business already talks about it, so whoever writes the definition does not have to be a developer to read it. The field of view is fixed at one level, the framework expands along the chain of forms recursively, and the SQL depth is unbounded. In a system with thousands of forms this is decisive. Relationships weave into a very large graph, and nobody needs to hold it in their head.

If the department side changes its own relationship, the employee form does not change by a single character, and the next query picks up or drops a level on its own. Whoever writes the declaration describes only the business level relationship. How deep it reaches, and how many tables it touches, is the framework's problem.

The circular relationship is not a problem here

The circle left in earlier, from department to employee and back, was already traversed by the statement above.

It causes no problem, because queries are computed fresh every time. The most a circle leaves to decide here is how many levels this particular query joins, and the level count is decided by the requested fields, not by the shape of the model. When nobody asks for the manager's name, that level never appears.

Generate strongly typed classes at build time instead, and the same circle becomes types referencing each other and a question of initialization order. One circle, and in a run time derivation world it is the depth of one query, while in a build time generation world it is a structural problem that has to be solved first.

Paging is computed fresh too

Which page, how many rows, and whether to count the total are all sent by the caller, on the same object:

var result = bo.GetList(new GetListArgs
{
    SelectFields = "sys_id,order_date,ref_customer_name",
    SortFields = [new SortField("order_date", SortDirection.Desc)],
    Paging = new PagingOptions { Page = 2, PageSize = 20 },
});

The framework normalizes those values before using them. The page number and page size are clamped into a sensible range, and the result.Paging that comes back is the set that actually took effect. The total is not counted by default; fetching one extra row is enough to know whether more follow, and an exact number costs one more COUNT statement. Paging always requires a sort, and if none is given the master's sequence column is supplied. Only when even that is absent does the caller have to say what it wants.

3. Saving: the field set is the write statements

The framework builds a table description on the spot from the master's field set, and from each detail's, and all three write statements come out of it. The order's UPDATE:

Update [ft_order] Set
[sys_id]=@sys_id, [order_date]=@order_date, [customer_rowid]=@customer_rowid, ...
Where [sys_rowid]=@sys_rowid

There are four things going on in that statement:

• The sequence column is not in it, because the database assigns it
• Display columns are not in it either, since the previous section covered their not having physical columns
• The condition is always sys_rowid, and DELETE is the same
• The whole row is written, not only the columns that changed

The fourth is clearest on details. A document has ten detail rows, the user edits one and deletes one, and the whole set is submitted. Only two statements reach the database, and the other eight rows are never touched. The UPDATE that does run writes every column of the edited row, not only the one that changed.

When master and details arrive together, all changes are wrapped in one transaction, and the order of saving and deleting is exactly reversed:

• Saving: master first, details after
• Deleting: details first, master after

The framework does not need to know what this document means to the business. It needs to know which table is the master, which are the details, and which column hangs the details back on the master.

One more thing, and the naming makes it easy to get wrong: all three statements are produced from FormSchema, not read out of TableSchema. TableSchema governs how the physical table is created, how lengths and indexes are tuned, and how the database keeps up with the definition, which is the next post's subject.

4. Why this is not an ORM

This section is not saying ORMs are bad, nor comparing their merits. An ORM works well in the setting it was designed for. The problem is quantity, as an earlier post covered.

The difference is not in how much each can do. It is in what sits at the two ends of the mapping:

Different starting points grow different things, and three things are missing here.

No entity classes

Query results are not restored into objects. Data travels as rows, carrying its structure with it. With no classes there is no "one more table means one more class," and the multiply by a thousand problem from the first post simply does not arise at this layer. The cost is that compile time type checking goes with it, and Data and Definitions Split Apart, So Forms Need No Types of Their Own covers how that is repaid.

No relationships expressed as properties, therefore no lazy loading

There is no equivalent here of walking from an order to its customer to that customer's salesperson in one expression. Which fields you want has to be stated at query time, so there is no accident where touching one extra property fires one database round trip per row. In the other direction, wanting a column that was never declared means adding a mapping to the definition first.

Change tracking did not disappear, it moved

An ORM holds loaded entities in a context object and compares properties at save time. Here there is nothing to compare. Each row records for itself whether it is new, edited, deleted or untouched, and an edited row keeps the old values of the changed columns too. The state is recorded on the row rather than per column, which is why the UPDATE in the previous section writes the whole row.

Those three are not half finished work. This mechanism has no place for them to begin with. There are also a few limits of a different nature: joins are always single column equality against a physical table, and subqueries and non equality joins are not in scope. Those are not impossibilities. This route only governs the standardized form segment. Reports, statistics and batch imports run on a different track, writing their own SQL, and Connections, Transactions and Query Conditions in the Data Access Layer takes that apart.

5. A thousand definitions versus a thousand classes

Thousands of business tables under an object mapping model are thousands of classes, plus their DTOs, mapping configuration and repositories. Those have to be compiled, maintained, and kept in step with the database structure, and no tool can fully take that last part over, because it involves what a column means to the business.

The same thousands of tables under Definition-Driven are thousands of structurally identical pieces of data. The difference is not the count. It is who can process them in bulk.

A definition is structured data, and that has three direct consequences:

• A batch script can sweep them. "Are the decimal places set correctly on every amount column" is one global search plus one test.
• Tools can produce them. TableSchema and FormLayout are both derived from FormSchema, and that is a transformation program, not a code generation step followed by a compile.
• AI can work with them. Asked to produce an eleventh definition following the pattern of ten existing ones, what it reads is a document with relationships and rules already declared, not a pile of classes whose intent has to be inferred from code.

Checking can move earlier too. A thousand definitions will still contain mistakes, pointing at a form that does not exist, mapping to a column that does not exist, or misspelling a field name in a rule. The difference is that those are errors a program can find at build time, and the framework does find some of them, as a later post covers.

A thousand wrong definitions are as bad as a thousand wrong classes. Being data does not make a definition automatically correct. What changes is how it gets fixed: edit one piece of data, rather than edit code and go through compilation, packaging and deployment again.

Definitions have not left ordinary engineering practice either. They sit in the same source tree as the code, go into version control the same way, show which lines changed the same way, and go through review before merging the same way. How they are changed is different. How they are governed is not.

One more item is not on the cost sheet at all: a thousand definitions share one set of conventions. Understanding how one form declares a relationship means understanding all of them, and whoever inherits the system does not have to learn each module's original author's habits one at a time. On a system maintained for ten or twenty years, where people have turned over several times and modules have been added in batches, that piece of work is paid again every time. That is how maintenance cost accumulates.

6. Back to the order form

The most complex form in the case study is one file in definition terms, Order.FormSchema.xml: a master with one detail, three relationships on the master, one per detail row, three validation rules and one computed field, with how those last two are evaluated being the subject of Declarative Business Rules and Computed Fields. Multiple relationships, master and detail, and a lookup window on every detail row, all declared, with no UI or CRUD code.

It is also the one form that needed code. Its repository exists for two SQL statements, reading the order's state in the database and finding the highest order number this month. What the declarations cannot express, you write. The standardized segment is definition driven, and those two statements do not have to take a detour because of the framework. Inherit a repository, add them, done.

Whether a form can avoid code does not depend on how clever the framework is. It depends on whether the framework recognized the part that is the same on every form and converged it into one mechanism. Which tables a query joins, and which statements a save issues, both fall inside that part.

The next post, TableSchema and Database Structure Upgrades, is about how a FormSchema becomes one, and how the physical database safely keeps up with the definition.

📚 Part of the thirty-post series ERP Architect's Notes: Designing a Definition-Driven Framework

📘 Original article on HackMD, in Traditional Chinese:
👉 https://hackmd.io/@jeff377/day-05-schema-drives-sql

📢 You are welcome to republish this, please credit the source.
📬 Follow for more engineering notes:
Facebook ｜ HackMD ｜ GitHub ｜ NuGet