Day 6: TableSchema and Database Structure Upgrades

Yesterday's SQL was all about accessing data. Today is about something else: where those tables came from in the first place.

Which columns a table has, how long each one is, and which ones get an index, are authoritative in TableSchema. The physical structure in the database is a product made from it. This is the layer where that is hardest to honor, because the database already contains data, and data does not change shape just because a definition did.

This post covers:

1. Why TableSchema is separate from FormSchema
2. Why an upgrade compares the current state rather than running generated scripts
3. Why compare, plan and execute are three separate steps
4. How to work out how long this upgrade will take before starting it
5. ALTER or rebuild, and who decides
6. Five stages, each in its own transaction, and how a failure is cleaned up
7. The things deliberately not done, and the principle they share

---

1. Why TableSchema is a definition of its own

The two care about different things:

	FormSchema	TableSchema
What it cares about	Business semantics: what a field is called, what type it is, whether it is required, which form it points at	Physical storage: how long a column is, how many decimal places it holds, which columns get indexes and whether those are unique
Who decides	The people who understand the business	The people who understand this database

The same order number looks like this in the case study's ft_order.TableSchema.xml:

<DbField FieldName="sys_id" Caption="Order No" DbType="String" Length="20" />

<DbTableIndex Name="uk_{0}" Unique="true">
  <IndexFields><IndexField FieldName="sys_id" /></IndexFields>
</DbTableIndex>

The {0} in the index name is where the table name goes, producing uk_ft_order. An earlier post, The Northwind Case Study: Eight Forms and a Custom Business Object, said the framework guarantees the uniqueness of sys_id, and this unique index is where that guarantee lives.

The practical return on splitting them: tuning an index, changing a precision, or adding a composite index for one report touches only TableSchema. FormSchema is not involved, and neither is whoever originally wrote that form.

As mentioned yesterday, this file does not have to be written from scratch. The framework can produce a matching TableSchema straight from a FormSchema. After that the two are maintained separately, which is what gives those adjustments somewhere to live.

This definition contains no database's type names

What is written above is DbType="String" Length="20", not nvarchar(20), and the whole file is like that throughout.

The types a developer can write are a set the framework provides. The common ones are string, long text, small integer, integer, big integer, decimal, currency, date, datetime, boolean, Guid, binary and auto number. Which native type each becomes is decided at the moment a statement is generated, and the databases differ more than you would expect:

Framework type	SQL Server	PostgreSQL	MySQL	Oracle	SQLite
Guid	uniqueidentifier	uuid	CHAR(36)	RAW(16)	UUID
Boolean	bit	boolean	TINYINT(1)	NUMBER(1)	BOOLEAN
Currency	decimal(19,4)	numeric(19,4)	DECIMAL(19,4)	NUMBER(19,4)	NUMERIC(19,4)

The same Guid column is a built in type in one, text in another and binary in a third. Folding that decision into the mechanism buys four things:

• Whoever writes the definition does not have to become an expert in this database first. Picking a native type takes experience. Using a floating point type for money, storing dates as text, or choosing too little precision for a timestamp are exactly where senior and junior diverge, and once it lands it grows into the data. The framework collapses the options into a set of types that talk about meaning, and the wrong one is not on the list at all.
• The same kind of column looks identical across the whole system. You will not have a Guid stored as text in one table and as binary in another, and discover it when a cross table query refuses to line up.
• One definition can run on several databases. Which ones a packaged product supports is stated openly, and the customer picks: their existing environment, the people who can maintain it, and the license fee a commercial database keeps charging. A short list is a whole category of customers you do not have.
• Changing which native type a framework type maps to means editing one mapping table, not a thousand definitions. Writing native types into definitions would mean copying that decision a thousand times the moment it is written.

Comparison gets simpler for the same reason. Columns read back from the database are translated into framework types first, then compared against the definition. The definition says Guid, MySQL reports CHAR(36), translating back gives Guid, and identical means no change.

That type set is deliberately narrow. Vendor specific features are not in it, and using one means going outside definitions. Being able to express less buys the same definition holding everywhere.

The cost: the two have to line up

How FormSchema Drives SQL covered CRUD statements being assembled from FormSchema's field set, while table creation and upgrades go through TableSchema. So:

• A field added to FormSchema but forgotten in TableSchema means the column is not in the database, while the save statement insists on writing it, and the error surfaces at run time
• The other direction, an extra column, is harmless. It gets created, and the form path never touches it

Nothing reconciles those two definitions at run time. What holds it together is the order of edits: the field goes into FormSchema first, and TableSchema follows with the physical column. When The Purpose and Classification of Definition Files described that order it read like a habit. It is the operational version of this cost. There is a build time gate that watches for exactly this, covered in Data and Definitions Split Apart, So Forms Need No Types of Their Own.

---

2. An upgrade is not a generated script, it is a comparison against the current state

The usual approach is migrations: change the model, have a tool generate a script, commit it, execute in order, and have the database record which version it reached.

This framework has no scripts and no version numbers. Every upgrade asks the database what it looks like right now, compares that against the definition, computes the difference, and executes it.

The difference shows up in three places:

• No need to know which version this database last stopped at
• No need to guarantee every version's script ran, in order
• Someone having edited the database by hand does not throw everything afterwards out of alignment, because the next comparison sees the edited state

What that buys is a very short contract for success:

When an upgrade returns successfully, the database has at least every column in the definition, with matching type, length and nullability.

Being safe to rerun is a consequence of the same thing. On a second run the current state is already the upgraded one, so the computed difference is empty. That property is used again when failure handling comes up.

Three costs:

1. Comparison has to ask the database about its own structure, and every vendor asks differently, so that part is written once per database
2. "Drop a column" cannot be expressed. A column absent from the definition is simply ignored, which the seventh section returns to
3. With no script, there is no file saying what this deployment will do to the database, available for review. On the migration route that script appears in the change list, and an experienced pair of eyes can see what it touches at a glance. Here the change list is a definition diff, where "a column went from 50 to 100" is readable but "does that trigger a rebuild" has to be asked separately

The third cost does have an answer, the compute without executing usage in the fourth section, except that the deployment process has to reach for it. It does not surface on its own.

---

3. Compare, plan, execute, cut into three

Step	Produces	Touches the database?
Compare	A structured difference: which columns to add, which columns changed definition, which one is being renamed, which indexes to create or drop. There is no SQL in it	Reads
Plan	How this will be done: the mode, how many stages, which statements per stage, and any warnings	No
Execute	Actually runs	Writes

The main reason for cutting it into three is not tidy layering. It is that the middle product is meant to be read by a person.

All three are called by the application. In production, the deployment tool runs through every table registered in DbCategorySettings at release time. The entry point is TableSchemaBuilder, and the case study's version is in NorthwindSchemaSeeder.cs:

var builder = new TableSchemaBuilder(category.Id, defineAccess, connectionManager);
foreach (var table in category.Tables)
    builder.Execute(category.Id, table.TableName);

That one Execute is the three steps run end to end. Computing without executing means taking them apart, which is called a dry run: CompareToDiff for the difference and TableUpgradeOrchestrator.Plan for the plan, then decide whether to go further.

---

4. Run the plan first and you know how long this will take

The plan computes one of four modes:

Mode	Impact	What to do
No change	No impact	Nothing to do
Create table	A new table with no data	Just run it
Alter table	Seconds, with at most a brief lock on a single column	It can run during normal hours
Rebuild	The whole table's data is moved and the table is locked throughout	Schedule a maintenance window and estimate the move time first

A rebuild works by creating a temporary table, moving the data in bulk, dropping the old table and renaming the temporary one. On a table with tens of millions of rows that can take anywhere from tens of minutes to hours. So what to look at before deployment is not the SQL, it is the mode. Do a dry run, see whether the mode is rebuild, and only then print the SQL for human review.

Besides the mode, the plan carries a list of warnings, such as which columns are being narrowed. So there are two things to read: the mode decides whether you need downtime, and the warnings decide whether you need to go check the data first. This question used to be answered by experience, as in asking whoever is more senior. Now it is a return value, and it can be written into a deployment script.

---

5. ALTER or rebuild, decided by the framework

The framework sorts column types into coarse families, being string, numeric, boolean, datetime, Guid and binary, with auto number in a class of its own. The mapping rules are short:

This kind of change	Route
Adding a column, adding an index, dropping an index	ALTER
A change within one family, such as lengthening a string or moving from integer to big integer	ALTER
Nullability and default values	ALTER
Crossing families, such as string to numeric, or anything to boolean	Rebuild
Turning auto number on or off	Rebuild

Then it checks each change in turn. If all of them can ALTER, it alters. If a single one needs a rebuild, the whole table is rebuilt. If a single one is something this database cannot do, it aborts and reports, rather than doing half of it.

This is deliberately not offered as a choice, because it has exactly one right answer and that answer is determined by the content of the change. Making it an option only hands something computable over to guesswork, and a wrong guess costs data.

The families are coarse on purpose, erring on the safe side. Judging something alterable when it actually needs a rebuild, and discovering that halfway through execution, is far worse than spending one more rebuild's worth of time. Differences in vendor capability are absorbed the same way. The extreme case is SQLite, whose ALTER TABLE can only add, rename and drop columns. Change a type, a nullability or a default and the whole table has to be rebuilt. The same definition and the same change is a seconds long ALTER on SQL Server and a rebuild on SQLite, and the calling code is identical.

---

6. Five stages, each in its own transaction

On the ALTER route the SQL is not sent in one go. It is cut into ordered stages, each in its own transaction:

1. Drop the indexes whose definitions are about to change
2. Modify existing columns, being name, type, length and nullability
3. Add new columns
4. Create new indexes, and rebuild the ones dropped in step one
5. Sync the column descriptions

There is no other possible order. A column cannot be modified while an index sits on it, so the index has to get out of the way and go back afterwards.

If a stage fails, that stage rolls back, the rest do not run, and the exception propagates. Stages already committed are not rolled back. That sounds dangerous, so why not wrap the whole thing in one transaction? Because for statements like creating and altering tables, wholesale rollback is not reliable on most databases, and some do not enroll them in a transaction at all. Wrapping them buys a promise that looks safe and does not hold.

So the framework does not make that promise. It allows stopping partway and cleans up by rerunning. The property held over from the second section is used here: fix the cause and run again, and the completed stages compare as no change and are skipped automatically. Being safe to rerun is more practical than being able to roll back. A flow that reruns safely lets you fix the problem at three in the morning and press the button again. One that claims wholesale rollback but partially takes effect only leaves you unsure where you are.

---

7. The things deliberately not done

For an automated upgrade mechanism, the boundary of its capability matters more than the capability, because it is automatic, and nobody shouts stop at the cut that goes too far.

• No dropping columns. A column present in the database and absent from the definition is always kept. The reason is that it is not necessarily garbage. It could have been added by another system, or be an extension column not yet folded into the definition, and a deletion cannot be taken back.
• No foreign keys, and no touching triggers or views. Business rules do not go into the database, which is the same decision carried through.
• Narrowing a column is refused by default. It is honored only if that option is explicitly turned on, and even then it is recorded as a warning. The reason is that silent truncation is the worst failure mode: it reports nothing, it just cuts a piece off the data, and it is usually discovered months later when someone asks why half a line of this address is missing.
• Renaming a column has to be stated. The framework does not guess that something is probably a rename. To rename, mark the old name in the definition. It also guarantees only a single hop, and a chain of renames accumulated across several versions is not supported.

A simpler way to say the no dropping rule: the database may have more than the definition, never less. "Definitions are the single source of truth" has a precise meaning at this layer. It guarantees that everything declared in the definition is in the database. It does not guarantee that there is nothing else there. Demanding the reverse, that the database equal the definition exactly, costs the framework being given the authority to delete columns it does not recognize, and nobody wants to hand it that.

Not creating foreign keys brings an incidental benefit. The circle between department and employee from an earlier post is not even a question on the table creation side. With no foreign keys there is no ordering, and tables are created one at a time. A world with foreign key constraints has to work out which comes first. This one does not.

Together those rules are one principle: automation may add, and subtraction is left to people. Add a column wrongly and you add another one to fix it. Drop a column wrongly and the data is gone.

---

Back to Northwind

The case study creates its tables at startup, running the loop from the third section, comparing and upgrading each registered table in turn.

So adding a table does not change that code. Two of the six places counted in the previous post live here, being the new TableSchema and the row in DbCategorySettings, and table creation follows.

The first run is a fresh creation. Every run after that is a comparison, and with no changes it does nothing. Change a column length or add an index during development and the next startup catches up, with nobody having hand written a create or alter statement in between.

---

The boundary of the truth

Back to the opening sentence: the definition is authoritative, and the structure in the database is the product. That says more than "the framework generates your create statements." Generating statements is the easy part. The real content is these things together. The structure is described once, and the description does not mention which database vendor it is for. To know what it should look like right now, reading the definition is enough. And when the two disagree, it is the database that gets changed.

This layer has one thing the earlier ones did not: there is data here, so the word truth needs a boundary. The database may have more than the definition, never less. At other layers, definitions being the single source of truth is about clearing away duplication. At this layer, it is about knowing what it must not touch.

The next post, FormLayout and Screen Generation for Multiple Front Ends, returns to the screen side: how one definition grows into screens on four front ends.

---

📚 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-06-schema-single-truth

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