I've found that a common task for web applications I've built is to react to a change in the state of the database. That is, any user interaction that affects the persistent state may change views that are dependent on that data, possibly views for other clients. The challenge is to update all affected views immediately, which I'm hoping can be solved by binding views to database data.
Preferably, these view dependencies would be as specific as possible. That is, if we treat the application layer as a stateless transformation of database data to views, the "dependency set" is the domain of that transformation, and we want this to be as close to minimal as possible.
What types of elements (e.g. rows, ranges of rows, individual objects) should constitute the dependency sets? Can they be specific enough without being too costly to check?
Current thinking, MySQL
To address the motivation and some concerns for the project, let's take MySQL without its triggers. (For the sake of simplicity, let's also treat foreign key cascades as triggers.) I am hopeful for the idea on this platform from a few hypotheses:
- It seems that we can partition this reduced MySQL command set into purely-read commands (
SHOW) and purely-write commands (
REPLACE, ...). If this is true, the direction of influence can be determined solely by the type of command.
- Caveat: Functionality in the application layer that relies on the number of rows affected from otherwise purely-write commands break this assumption.
- It seems plausible that table-level binding can be perfect and need only query parsing. That is, purely-write commands can only modify and purely-read commands can only depend on tables mentioned in the command, and these table names can even be identified uniquely and reliably by parsing alone.
It seems plausible too that table-level binding is not the most specific perfect system. That being said, all stronger systems probably consider
WHEREconditions. This is problematic because, unless conditions involve the same column, intersections between conditions depend on data in the table. Then, we might be left with two options: intersect corresponding sets of unique IDs, or regress to table-level binding. I imagine the former could be very costly.
There might be some more power to parsing by itself with some more nuanced rules, like knowing non-aggregate
SELECTcommands cannot be influenced by
ON DUPLICATE KEY UPDATE.