0

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:

  1. It seems that we can partition this reduced MySQL command set into purely-read commands (SELECT, SHOW) and purely-write commands (UPDATE, DELETE, INSERT, 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.
  2. 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.
  3. It seems plausible too that table-level binding is not the most specific perfect system. That being said, all stronger systems probably consider ON and WHERE conditions. 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 SELECT commands cannot be influenced by INSERT without ON DUPLICATE KEY UPDATE.

  • The existence of tools such as reactive frameworks and MVVM UI's clearly indicate that data binding is a useful technique. What is your specific question? – Robert Harvey Dec 27 '16 at 15:09
  • @RobertHarvey Existing techniques I've worked with let you bind to a single primitive datum, but I'm unsure of a reliable way to identify data on which views are dependent and which write commands modify so specifically in SQL, because of both the language and the separate ecosystem. I'm hopeful for existing work, or reasons I shouldn't attempt it. I hope this is clearer. – concat Dec 27 '16 at 18:04
  • 1
    Can you state clearly, in 10 words or less, what is the specific problem you're trying to solve? – Robert Harvey Dec 27 '16 at 18:09
  • @RobertHarvey Parsing queries to subscribe views to relevant database changes automatically. – concat Dec 27 '16 at 18:42
  • 1
    You're describing Reactive Frameworks. – Robert Harvey Dec 27 '16 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.