"Supports SQL Server, PostgreSQL, MySQL, Oracle and SQLite" is one line in a specification. Inside a framework it is a question that has to be answered before anything else: what exactly is being abstracted for them.
Neither direction works:
- Wrapping up what every vendor can do has no end to it. Each item wrapped means filling the same gap five times over, and by the end every item is half finished
- Sharing nothing but a connection object is not support for multiple databases, it means every form's statements get written five times
This framework's answer is narrow, narrow enough to be two things only.
This post covers:
- Why there are only two shared behaviors
- CRUD, where the differences are small enough for a lookup table to absorb
- Structure upgrades, where the differences are large enough that each vendor really does get its own
- Which vendor is used is registered by the application, and the framework ships none
1. Why there are only two shared behaviors
How many databases a framework should support has no general answer, because it depends on what is being sold. Supporting one buys depth, since that vendor's native types, bulk interfaces and dialect features are all directly available. TableSchema and Database Structure Upgrades said that for packaged software a short support list means a whole set of customers gone, which is what makes multi-vendor support a condition of entry.
What this layer faces is five commonly used relational databases, not everything that can store data. Those five share one data model: tables, columns and rows, all speaking SQL, all with transactions. Precisely because the layer underneath is shared, "CRUD" and "keeping the structure caught up with the definition" point at the same thing on all five.
Two tests, both of which have to hold:
- Is this something every application does
- Does it keep doing it
Measured with those two, not much is left:
| What got in | Why |
|---|---|
| CRUD against a single table | There is no application that does not do it, and it happens once every time a user presses save |
| Keeping the structure caught up with the definition | No application can dodge it, and it comes round once at every deployment |
What did not get in: partitioned tables, materialized views, full text search, each vendor's own analytic functions, query hints, and the dedicated interfaces for bulk loading. Every one of them is valuable, and they share one property. Not every application needs them, and the ones that do usually use them in only a handful of places.
Take bulk loading as the example. Most databases have a dedicated mechanism far faster than writing row by row, but no two of them are the same shape. One hands you a class of its own, another a special statement, another wants a file written out first for the database to read itself. Designing a common interface over those yields the smallest intersection and nothing more, and the smallest intersection is usually about as fast as writing row by row. Wrapping it gains nothing and leaves five implementations to maintain.
These two tests measure whether this layer should be doing something, not whether it matters. Partitioning may be a life or death question for a document table of a hundred million rows. It simply should not be an interface that has to hold for five vendors at once deciding how it is done.
Once the measuring is over, what is left is all of it. The framework's entire outward abstraction is one interface, and what gets asked of a database falls into two categories: how to produce CRUD statements for a table, and how to keep the structure caught up with the definition. The second splits into four smaller pieces, being read the current state, create a new table, alter in place, and rebuild when altering will not do.
The line is drawn narrowly, and what that buys is that it can be finished. One interface and two categories of question means connecting a new database is something you can read to the end of and write to the end of. If that interface had thirty members, this would never happen in practice.
The two jobs sit inside the same interface, though, and the abstraction over them is nothing like the same thickness.
2. CRUD, where the differences are small enough for a lookup table to absorb
The pain: the same query looks different on all five, and the places where it differs are extremely boring. What symbol wraps table and column names, how a parameter is written, how paging is expressed. Boring, but every statement has to be right, and one mistake is a run time syntax error, of the kind that never appears at all when the test environment runs a different database.
These differences are of a good kind. They are different spellings of the same thing, not different things.
The reason for collecting them into one lookup table is that the cost of not collecting them is scattered. Statements get assembled in more than one place, with separate stretches for listing, reading one row, counting, and each of insert, update and delete. Every stretch judging for itself which vendor is in play means every stretch has its own chance to miss one, and the one missed shows itself only when a customer happens to run that database.
| Database | Identifier quoting | Parameter prefix |
|---|---|---|
| SQL Server | [sys_id] |
@ |
| PostgreSQL | "sys_id" |
@ |
| MySQL | `sys_id` |
@ |
| Oracle | "SYS_ID" |
: |
| SQLite | "sys_id" |
@ |
Oracle's row carries one extra thing. An unquoted identifier gets folded to upper case there, so what the framework sends is always quoted and upper case, and on the way back the framework folds it to lower case at the boundary, so that every layer above sees exactly the same column names on all five. Data and Definitions Split Apart, So Forms Need No Types of Their Own said there is one set of names the whole way through, and this is that rule's closing move at the lowest level.
One thing is left that a lookup table cannot solve, because the shapes really are different:
| Database | Taking rows 21 to 40 | Skipping the first 20 and taking everything after |
|---|---|---|
| SQL Server and Oracle | OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY |
OFFSET 20 ROWS |
| PostgreSQL and SQLite | LIMIT 20 OFFSET 20 |
OFFSET 20 |
| MySQL | LIMIT 20 OFFSET 20 |
LIMIT 18446744073709551615 OFFSET 20 |
MySQL matches the middle row only when a range is being taken. Given a starting point and no count, it will not accept an OFFSET standing on its own, so a ceiling has to be padded in, and that string of digits is the sentinel value its own documentation specifies, not something typed at random.
The conclusion is in the shape of the five implementations. The five classes matching the five vendors are all but identical, each one handing the query and the delete to the same shared code and mentioning on the way past which vendor it is. The three statements used for saving go further and do not even come in five: one class assembles them while holding an enumeration value.
That class is TableSchemaCommandBuilder, which takes which vendor and the table's structure at construction, assembles the three, and wraps them together with that table from the DataSet into one thing handed down:
var insertCmd = BuildInsertCommand();
var updateCmd = BuildUpdateCommand();
var deleteCmd = BuildDeleteCommand();
return new DataTableUpdateSpec()
{
DataTable = dataTable,
InsertCommand = insertCmd,
UpdateCommand = updateCmd,
DeleteCommand = deleteCmd
};
That same post said the state of each row decides which statement gets issued, and here is where it is honored. Inside the whole class, which vendor is in play arrives from exactly two places, being how identifiers are quoted and what prefix parameter names take, which are the two columns of the first table above. How the three statements are assembled has not one branch in it.
The differences all sink to the layer below, where what can be looked up is looked up and the one case left over is taken care of by shared code.
This half of the abstraction is very thin, and thin is good. Connecting one more vendor means almost nothing to write on this half.
3. Structure upgrades, where the differences are large enough that each vendor really does get its own
For "widen this column from 20 characters to 50", what differs between vendors is not the spelling but whether it can be done at all. That is no longer different spellings of one thing, it is a difference in capability itself, and a difference in capability cannot be absorbed.
Difference one: reading the current state
No two of the five are alike:
| Database | Where it is read from |
|---|---|
| SQL Server | The sys.* set of system views |
| PostgreSQL |
information_schema mixed with pg_catalog
|
| MySQL | INFORMATION_SCHEMA |
| Oracle | The USER_* data dictionary |
| SQLite |
sqlite_master plus a few PRAGMAs |
The reason this cannot be pulled apart is not dialect, it is how each vendor regards its own structure. SQL Server treats a column default as an object in its own right, stored separately and named separately, so reading it means joining one more system view. SQLite goes to the other extreme. It does not enforce types, and keeps the VARCHAR(50) originally written down exactly as written, so for the framework to know how long a value can be, that string has to be taken apart and read again.
There is a step after reading, and it is harder than the query.
That post's rule that the definition holds no database's type names has its cost land here. Creating a table means translating framework types into native types, so comparing means translating the native types read back the other way, and that translation is many to one. On MySQL a Guid is stored as a fixed length character column of 36, and a fixed length character column is just one way of spelling a string, while auto number and long integer both land on BIGINT. Translating back, all the database reports is a char with a length, or a bigint. The meaning was already thrown away on the way out.
So the reverse half is not the lookup table read backwards, it is recognizing the meaning again from extra clues:
- A fixed length character column of exactly 36 is a
Guid - A numeric column of precision 19 and scale 4 is a money amount
- A
BIGINTgets a second look for an auto increment flag
These clues hold on the premise that the framework only ever emits those few spellings, so a table the framework built itself is always recognized again. The cost sits on that same premise. Take over a table the framework did not build and these inferences go wrong, with the symptom being a comparison that reports a difference which is not there, every time.
The forward mapping fits in one list, and the reverse half does not. Once an abstraction has thrown information away in the middle, there is no free route back.
Difference two: whether it can be altered at all
That post's rule, altering in place within a family and rebuilding across families, is all but void on SQLite, because its ALTER TABLE cannot touch an existing column. So the same change that takes seconds on another database copies the whole table's data on SQLite.
The framework does not choose to smooth this over. What it does is compute the difference and show it, which is what that post's dry run is for. The same definition against different databases can yield different plans, and whether to schedule a maintenance window was always a question for that plan to answer. Smoothing it over would be worse, because what gets smoothed away is exactly the thing the user most needs to know.
Difference three: the hardest to spot, because it reports no error
From 8.0.13 onward MySQL requires that a default value which is not a literal be written as an expression wrapped in parentheses. So for the framework to have a column generate a Guid automatically, what goes out is DEFAULT (UUID()). Writing it is fine.
But at the next comparison, what INFORMATION_SCHEMA reports back is uuid(). The parentheses are gone, and the function name has turned lower case.
So the literal comparison runs like this. The definition says (UUID()), the database says uuid(), the two do not match, a change is declared, one more ALTER goes out, it runs, the next comparison still gets uuid() back, and there is one more ALTER again.
An upgrade that never converges. It does not fail and it produces no error message. It quietly issues one more statement at every startup, altering a column that never changed.
The framework's answer is to normalize both sides to the same shape before comparing, stripping one layer of outer parentheses and ignoring case. SQLite has a problem of the same kind, since it stores the create statement as written and reads it back as written, and it needs a normalization of its own.
And one more kind: column descriptions
Every column in the definition has a line of description meant for people, and storing that in the database gets four answers out of the five vendors:
| Database | How column descriptions are stored |
|---|---|
| MySQL | Written straight after the column definition |
| PostgreSQL and Oracle | A separate COMMENT ON statement |
| SQL Server | Hung on a set of things called extended properties |
| SQLite | Not kept at all, where writing is a no-op and reading always comes back empty |
What the framework does about the last one is say outright that it cannot be done, and leave the description on the definition side. Pretending it had been stored is what would be the disaster, because it would never match when read back, which is that non converging loop over again.
These several kinds of difference are all branches off the same abstraction on its way down, and not one of a thousand definitions needs to know about any of it. The cost is that someone has to know for them.
That someone is this layer. Reading back and writing out are only the entry fee. The hard part is recognizing that what was written out and what comes back are the same thing.
4. Which vendor is used is registered by the application, and the framework ships none
The pain: your system uses one database, and the framework claims support for five. So five vendors' drivers sit in the output directory and the container image is that much bigger, and the day one of them ships a security update, your vulnerability scan lists it even though you never used a line of it. Versions are harder still. A framework that folds five drivers into its own dependencies has pinned five versions on your behalf.
A dependency that is not itself using something should not be the one making that decision for all of its users.
So the framework builds in none of them. An application registers whichever one it wants to support, at startup, and the case study's line comes from NorthwindBackend.cs:
DbProviderRegistry.Register(DatabaseType.SQLite, new SqliteProviderFactory(SqliteFactory.Instance));
What gets registered is the ADO.NET level factory, out of each vendor's own package. The same file has one more line, registering the set that produces statements and reads structures for it, which is the whole interface of the two sections above. Connecting a database the framework does not ship for means writing exactly that one part.
The framework never registers any vendor itself, and that sentence is written into the source comments.
Registration happens in code, not in configuration. What configuration carries is the database kind, an enumeration value, not a type name. Letting configuration name a type would mean the framework going off to find an assembly by name at run time, which moves the driver dependency out of the dependency list and into a string, where the build can no longer see it.
This discipline is verifiable in the source. Not one project in the whole framework references any of the five vendors' packages.
SQLite took one extra piece of work for this. Its driver does not provide the DataAdapter the framework needs for writing back, so the framework supplies one, wrapping the abstract factory type and handing every other member straight through, with the instance passed in by the application at registration. Even something needed for that one vendor does not reference that vendor's package.
The case study uses SQLite only, and the other four vendors' packages are not even referenced. The reason for choosing it has nothing to do with the framework's design: no database to install, one file and it runs, which is the least trouble for a demonstration.
The thickness of an abstraction
Five databases, two jobs. What got in is what every application has to do and keeps doing. What did not get in is not less important, it is only needed in a few particular places.
The two look nothing alike once abstracted. The CRUD half is thin enough that the five implementations are all but the same, because the differences between vendors are different spellings of one thing. The structure upgrade half is thick enough that each vendor gets its own, because the differences between vendors are differences in capability.
The thickness of an abstraction is not decided by its designer. It is decided by how far apart the things underneath it are.
This layer's return takes time to show. At the first delivery it is pure cost, being one more interface, two more lines of wiring and five more implementations. It pays back when a second customer names a different database and you find that not one word of a thousand definitions has to change.
Supporting one more database does not really cost those few hundred lines of implementation. It costs the gaps that no document tells you about in advance, and how many of those there are is something you only learn by walking into them.
The next post, Connections, Transactions and Query Conditions in the Data Access Layer, is about the stretch the framework decides not to do itself: when SQL is handed back to the application to write, which things the framework does not hand over along with it.
๐ 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-10-two-common-jobs
๐ข You are welcome to republish this, please credit the source.
๐ฌ Follow for more engineering notes:
Facebook ๏ฝ HackMD ๏ฝ GitHub ๏ฝ NuGet
Top comments (2)
CRUD์ ๋ฌธ๋ฒ ์ฐจ์ด์ ๊ตฌ์กฐ ์ ๊ทธ๋ ์ด๋์ ๊ธฐ๋ฅ ์ฐจ์ด๋ฅผ ๊ฐ์ ๋๊ป๋ก ์ถ์ํํ์ง ์๋๋ค๋ ๊ฒฐ๋ก ์ด ๊ธฐ์ต์ ๋จ์ต๋๋ค. SQLite ๋ฐ๋ชจ์ ๋ค์ฏ ๋ฒค๋์ ๊ตฌํ ๋ฒ์๋ฅผ ๊ตฌ๋ถํด ์ค๋ช ํ ์ ๋ ์ข๋ค์. ๋ฒค๋๋ง๋ค ์ฌ๊ตฌ์ถ์ด ํ์ํ ๋ณ๊ฒฝ์ ๊ฒ์ฆํ ๋, ๊ณตํต ๊ณ์ฝ ํ ์คํธ์ ๋ฒค๋๋ณ ์คํจยท๋ณต๊ตฌ ํ ์คํธ๋ฅผ ์ด๋ป๊ฒ ๋๋์๋์ง ๊ถ๊ธํฉ๋๋ค.
Thanks for reading so closely. The split in Polhem today is a little different from contract tests vs. vendor failure tests, so let me describe what is actually there.
Shared, no database: the decision layer is tested once. Which change can be altered in place, which needs a rebuild, which is refused, what counts as narrowing, and how the plan is staged and ordered. SQLite gets its own small set because its rule is different (every type change is a rebuild).
Per vendor, SQL text only: each dialect's rebuild builder has its own tests on the generated script. Temp table, dropping a leftover temp table first, keeping extension columns that are not in the definition, and how indexes come back (PostgreSQL renames them, SQLite has no ALTER INDEX RENAME so it recreates them after the swap).
Against a live database: the full upgrade path (create, alter keeping data, rebuild, rerun is a no-op) runs end to end on SQL Server, plus targeted regression tests on Oracle for errors we actually hit.
For failure and recovery, the design does most of the work, so there is less vendor-specific behavior to test than you might expect. A rebuild copies the data into a temp table first, so if it fails before the swap, the original table is untouched and there is nothing to roll back. Anything short of a rebuild is not rolled back at all: the definition is still there to compare against, so after fixing the cause you rerun, and whatever already finished produces no diff. That is also why I don't lean on DDL transactions, which are not reliable across several statements on most databases.
Note: Polhem is the successor to Bee.NET and replaces it. New work happens in Polhem.