Jeff Tsai

Posted on Nov 4 Originally published at hackmd.io

Day 11: Connections, Transactions and Query Conditions in the Data Access Layer

#architecture #dotnet #erp #softwareengineering

How FormSchema Drives SQL drew the boundary of the definition-driven route: relationships across tables are always single column equality, and what they join to is always a physical table. Reports, statistics and batch imports go beyond that range, where SQL gets written by hand, and every framework has to leave that route open. The trouble is at the junction. "I do not do this part" and "you do this part entirely yourself" are two different things, and opening an entry point that takes an arbitrary SQL string writes them as one. The cost does not show up there and then. It shows up at the second database, at the first hand assembled WHERE, at the first query inserted halfway through a save.

What the framework puts at that junction is DbAccess, the one entry point for an application issuing its own statements: one SQL statement, a few values to pass, no connection object, nothing to remember to close, not one piece of string concatenation. What it saves you is the things you do not need to know. Three things it cannot save you, and this post takes them one at a time:

Which database to connect to
A query condition is not a string, it is a tree
Who opens a transaction, and who closes it

What those three have in common: they cut across every SQL statement, while the answer is not inside the statement.

1. Which database to connect to

The pain: you write a query, and the first line of code has to answer where it connects to. A single database does not have this problem, but the moment one system serves several companies at once, the data is spread across several databases, and one of those changes with whichever company is in play. Writing the answer next to the query means writing the shape of the deployment into the business code.

The answer the framework wants is a database id. On the deployment side there is a list, one id mapping to one set of connection details, which in the case study is DatabaseSettings.xml:

<DatabaseItem Id="company"
              DatabaseType="SQLite" ConnectionString="Data Source=northwind.db;Cache=Shared" />

What that list looks like is a fact about the deployment environment and has nothing to do with how the application is written. Not one database ever appears in FormSchema, and not one id ever appears in the business code.

Who owns a table and where it lives are two different things

Tables in this framework carry one of two prefixes. The framework's own tables are st_, and the application's business tables are ft_.

The prefix answers who owns a table, and the database category answers where it lives. Neither answer can be derived from the other.

The decisive example is in the framework's own definitions. The built in department, employee, role, role grant and user role binding tables all carry the framework prefix, and all land in the company database, because a company's employees are that company's data. The prefix has not stopped working, since what it answers is which half the framework touches at upgrade time. It just cannot answer location. Bind "who owns it" and "where it goes" into one thing and those tables no longer fit.

What goes in each of the three categories

The framework sorts databases into three categories:

Category	What goes in it
`common`	Users, sessions, the company list, definition storage, and the cache notifications that cross nodes
`company`	One company's own data: the business tables plus the department, employee and role tables
`log`	Records of operations, logins and changes

How many categories there are and how many databases there actually are is a separate question. The three can share one database or each stand alone. In a multi-company deployment company is one per company, and log can be split further by year, with the current year writable and earlier years read only. All of that is written in that one DatabaseItem list, and not one FormSchema has to change along with it.

Where the routing happens

The framework declares it on the type. A repository states at construction which category it reads and writes. The framework's system classes write common or log in directly, and the form bound one has FormSchema declare it, and both resolve at that moment into one physical database id.

The common and log categories do not have to ask the session, since their ids are fixed. Only company has to know which company has been entered first. That asymmetry is deliberate: logging in happens at a point where there is no session yet, and both a successful and a failed login have to leave a record.

Serving two companies means the company category is two entries:

<DatabaseItem Id="company001" CategoryId="company"
              DatabaseType="SQLServer" ConnectionString="Server=db01;Database=northwind001" />
<DatabaseItem Id="company002" CategoryId="company"
              DatabaseType="SQLServer" ConnectionString="Server=db01;Database=northwind002" />

Enter the first company and the Order FormSchema declares its category as company, so the id the repository resolves is company001. Enter the second and the same code with the same definition resolves company002. The hop in the middle is carried by the company's own data, with a column on st_company recording which id that company uses.

Two decisions each block one kind of trouble:

Declared on the type, so looking at the type tells you which database it touches, with no need to page through every one of its methods.
Resolved at construction, so a routing mistake fails before a single row is touched. An expired session, no company entered yet, or company data that cannot be found, all three throw at the moment the object is built.

What the application side sees is the difference between these two lines:

CreateDbAccess()                          // the id is resolved by the framework
CreateDbAccess("company001")              // you answer "connect where" yourself

The framework provides the second as well, because some applications really do know which one they want. The first has nowhere to fill in wrongly, and the second has.

Why an id and not a connection string

What those two lines have in common is easy to skim past: both take a database id, not a connection string. Not one entry point in the framework's data access takes a connection string. Two consequences follow:

The first is already covered above. An id can be resolved at the moment of construction, while a connection string can only be carried around by the caller.
The second is about the connection pool. ADO.NET keys its pools by connection string, and two strings differing by one character, an extra space, a different parameter order, an option written or left out, are two pools, each counting its own limit. So the price of every caller assembling its own connection string is that the pool splits into several depending on each caller's luck, and the symptom appears on the database side: connection counts inexplicably high, while the application feels it has opened hardly any.

The framework collects that into one place. Connection details, being the database kind, the driver factory and the connection string, are cached by id, so the same id is always the same string and therefore always the same pool. Change the configuration and the whole set is cleared and read again.

The framework does not pool anything itself. The real pool belongs to each vendor's driver, and the framework does two things only: not manufacture surplus connection strings, and give back the connections it opens. The second looks different on the two sides:

// The developer's side: this one line is all that is visible
var status = CreateDbAccess().ExecuteScalar("SELECT status FROM ft_order WHERE sys_rowid = {0}", rowId);

// The framework's side: open one per execution, return it to the pool on leaving this scope
using var scope = CreateScope();

Every execution is open one, use it, give it back, rather than holding one connection and not letting go. On a single developer machine this is impossible to feel. It is when dozens of concurrent requests arrive that the pool gets its turn.

2. A query condition is not a string, it is a tree

The pain: a search screen has eight fields, the user fills in two, and you have to assemble a WHERE containing exactly those two conditions. The usual way to write it is string concatenation plus a run of conditional checks. Concatenation is where injection comes from, and injection's real trouble is not that it is hard to fix, it is that every new search screen is another chance to commit it again.

The framework makes a query condition a model, and that model lives in the definition layer. Its position matters more than its content. The definition layer is the one layer every other layer knows, so the condition the user fills in, the condition the API contract carries, the condition the business object receives, the condition the repository gets, and the condition each of five databases reads while assembling its statement, are one type from end to end, with not one conversion in between.

The model itself is small, holding two kinds of thing only:

var filter = FilterGroup.All(
    FilterCondition.Equal("status", "Confirmed"),
    FilterCondition.Between("order_date", from, to),
    FilterGroup.Any(
        FilterCondition.Equal("ref_customer_id", customerId),
        FilterCondition.In("employee_rowid", myTeam)));

A single condition: a column name, a comparison, and one value, with range comparisons carrying a second. The comparisons are a closed list, being equal, not equal, the four orderings, like, in a set, between two values, starts with, ends with, and contains.
A group: All and Any, and a group can itself be a member of another group.

Making it a tree is not for the looks. The real shape of a condition is a tree already, and flattening it into a string leaves nothing but text, which every downstream has to parse again to get the structure back.

Nodes on one tree do not have to come from one place either. What the user filled in is one batch, the data scope computed by the permission layer is another, and the two hang on the same tree. Crossing the boundary between front end and back end there is one more layer walking node by node and converting time values across. The same model has several producers and consumers unrelated to each other, and none of them needs an adapter in between.

Values go through parameters, column names go through the definition

When this tree turns into SQL, values and column names take two different routes:

Values always become parameters. Each value encountered is handed to a parameter collector, which returns a parameter name to put in the statement, and the value itself never appears in the text of the SQL.
Column names cannot be handled that way, since a database will not accept a parameter as a column name, so they take the other route. First they are looked up against the definition to find which column of which table they are, and then the vendor's identifier quoting is applied, with any quote character already in the name escaped at the same step. The delete path is stricter still: a name not in that table's column list is refused outright, and so is one pointing at another table, or at a display column that is not a physical column.

Parameterization stops values. It does not stop column names.

An implementation that parameterizes and still concatenates column names looks, from the outside, like it has already been taken care of.

Not letting the caller send in SQL and not letting the caller send in an expression are the same line: both amount to opening an execution entry point on the server. So what the caller can express is a tree, not a string.

The same holds on the side where the application issues its own statements. The position of a value is always marked with a placeholder:

CreateDbAccess().ExecuteScalar(
    "SELECT MAX(sys_id) FROM ft_order WHERE sys_id LIKE {0}", "ORD-202608-%");

At the moment it goes out, {0} is replaced by a parameter name, @p0 on SQL Server and :p0 on Oracle, the difference being the parameter prefix from the list in Multi-Database Support: CRUD and Structure Upgrades. The whole of ORD-202608-% hangs on that parameter.

This interface takes values, not an assembled string. To concatenate a value into the text of the statement you would have to go around this method first. An earlier post's UPDATE writing every column of the row rather than only the changed ones is the same shape as this: using design to make a whole category of code impossible is far more reliable than using a rule asking people not to write it.

3. Who opens a transaction, and who closes it

The pain: saving one document is not one SQL statement. One for the master, and as many for the detail as there are rows, and those statements either all count or none of them do. Outside one transaction, the master gets written and one of the statements fails, and what is left is a document with an incomplete detail. Phantom data like that comes with no warning at all, waiting for the next time someone opens it, and the database will not block it for you either, since The Northwind Case Study: Eight Forms and a Custom Business Object covered the framework not creating foreign key constraints. This is not an edge case, it is every save.

A framework driven save opens a transaction of its own: master first, detail after, committing only when all of it succeeds and rolling the whole batch back if one statement fails. Delete goes the other way, detail first, for the same reason, so that no unmatched half is left behind. The application never sees that transaction and does not have to care.

The only part needing an answer is the hand written stretch, and the framework offers two entry points. Several statements issued together get packed into one batch, with the transaction still opened and closed by the framework:

var batch = new DbBatchSpec { UseTransaction = true };
batch.Commands.Add(new DbCommandSpec(DbCommandKind.NonQuery,
    "UPDATE ft_product SET units_in_stock = units_in_stock - {0} WHERE sys_rowid = {1}", qty, productRowId));
batch.Commands.Add(/* the rest of this batch */);
CreateDbAccess().ExecuteBatch(batch);

When one is already running, such as writing one more row partway through a save, hand that transaction in and the statement runs on it:

CreateDbAccess().Execute(spec, transaction);

What makes the second entry point work is the full rule behind "give back the connections it opens" from the end of the first section:

If the framework opened the connection itself, it closes it when done. If the connection was handed in from outside, it opens it when necessary and does not close it, because it does not own that connection.

The first half governs whether connections get back to the pool, and the second governs whether a hand written statement can join an existing one. One rule answers both questions, and ordinarily the two look entirely unrelated.

Only the mechanism is covered here, not the semantics. Which side of the transaction an extension point belongs on, what putting it on the wrong side costs, and what price is paid for having every extension point inside, is the whole subject of Extension Points on a Business Object and When to Override Them.

Back to Northwind

Of eight forms, only the order needs the application to take over, and on the data access side that is two SQL statements, both in OrderRepository. The two sections above used one each, and the one reading the order status out of the database carries both the first section's id and the second section's placeholder.

In that whole class there is:

No connection string, no database id, no connection object
Not one try or finally
Not one piece of string concatenation

What there is, is one SQL statement, one {0} and one value to pass.

One SQL statement, plus one id

What the application finally writes down is one SQL statement plus a few values to pass. Which database is answered by an id, and the id is resolved at the moment the repository is built. The connection details behind that id are collected into one place by the framework, so one id always means one pool. A query condition is a tree, and values go through parameters. Transactions are opened and closed by the framework, and when something needs to join one, the transaction in hand is passed to it.

All three are still there. They are just not on the line you wrote, and they never belonged there. Which database to connect to is a fact about deployment shape, what a condition looks like is a matter of the contract between front end and back end, and which transaction something runs on is a matter of calling context. Not one of them belongs to that SQL statement. Whoever writes a query answers only the questions about their query, and gets the same answer to the rest every time.

The next post, Cross-Node Invalidation for Definition and Data Caches, turns in another direction. Those thousand definitions themselves, reread on every request, would hand back all the cost saved up to this point.

📚 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-11-three-guarantees

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

DEV Community