One more layer of DB abstraction for 4.0?

[KT Walrus]

I suggest that you add a class that sits between DB class and application code that applies update transactions to the DB. I have defined a dbUpdates class that has various functions for each update transaction that the user can do to the DB (I don't need this abstraction for admin/moderator updates, in my case).

So, when creating a new topic, for example, the post code gathers up the various row data for the tables that need updates and passes them to dbUpdates to do the update transaction (the function in dbUpdates knows how to update the various tables in a single SQL transaction including adding the topic_id into the posts row and which columns need to be incremented implicitly like the member's post count).

In my case, this extra layer of DB abstraction lets me do proper DB SQL transactions for the InnoDB tables. I've also gone so far as to queue the updates instead of applying them immediately. In my case, I gueue the transaction data in the dbUpdates function and have a sister function that dequeues the transaction and applies it to the database. The transaction data is queued in a writable table in the DB and I have a task that runs fairly often that checks the queue and applies the transaction to my Master DB (I'm using MySQL replication). The function that applies the transaction keeps a log of the "before" and "after" images of the affected rows (again in a DB table) so I can "undo" transactions created by abusers or those that violate the forum terms.

This set up also allows me to access the DB using a DB user that has only read-only permissions to the forum tables (except for the transaction queuing table which requires simply insert permission).

Anyway, I haven't finished implementing all this yet, but I'm well on my way. My point of this post is not to suggest that IPB 4.0 do queuing or support Master/Slaves DB replication or transaction UNDOs, but that there really should be an extra layer of abstraction between issuing SQL queries and the DB class to apply those queries. In my case, I'm only concerned right now about the updates that members do to the DB tables, but I would think it would be a good idea to abstract the SELECT queries as well. Embedding the actual SQL inline in the application code reduces a lot of flexibility (like doing proper DB transactions, altering some queries without have to parse the queries in the low level DB class, etc.).

So, my suggestion is that IPB 4.0 consider adding some kind of abstraction between the application code generating queries (especially update transactions) and the low level DB class that simply builds and applies queries to the current DB instance.

[KT Walrus]

BTW, a couple other things that this middle layer of abstraction would allow is to use more advanced features of MySQL. This might be applying some of the transactions using stored procedures (as an optimization) or taking advantage of the new Memcached interface in MySQL 5.6 or even using Memcached to maintain a coherent cache for the underlying DB tables.

I realize that all these features could be added without this middle layer of abstraction, but it is a mess to do so. Having a single middle layer class allows these functions to be easily modified and controlled. I think this could also improve security (like in my case, the transactions are queued so the user has only read-only access to the DB data and therefore hacks like SQL Injection can be mostly eliminated by having a separate update process that can "undo" bad transactions or sanitize all user inputs going into making up a transaction).

[Matt]

Couldn't you do this by adding a parent class to the relevant DB engine class? It would inherit the default methods of classDbMysqliClient.php and you could then overload the various methods and pass it off to parent::query(), for example?

[KT Walrus]

Couldn't you do this by adding a parent class to the relevant DB engine class?

The problem with this approach, as I see it, is that the current application code uses lower level functions to execute the queries in a transaction. I'm not sure how I could add parent class inherited functions that would know enough about when a transaction starts, the data that is used in the queries in the transaction and how they relate to each other. Consider the post topic transaction. It creates several rows in different tables, updates some data in other tables, and has data from one insert (the topic_id) used to update data in other tables.

I guess I'm really suggesting that there be a layer where the entire transaction is managed/executed. Right now, you only abstract the SQL statements and the application code is embedding SQL statements directly in its execution flow. If the entire transaction is abstracted so that you pass all data needed to execute the transaction into a function (this sometimes only turns into one SQL statement, other times a more complex sequence of SQL statements) and the function is specific to a transaction type (like "post topic" or "post reply"), then I have a much greater ability to manage these transactions (like diverting them to a Master DB, or queuing them for serial execution later, or logging them for transaction "undo", or even doing some transactions using other storage methods like using MongoDB or Memcached) without having to touch "application code". You could argue that these specific functions that turn transaction data into a committed transaction are part of "application code", but I think the number of transactions in IPB is pretty small and abstracting them to a single dbUpdates (or dbTransactions) is doable. This would give me a single place to modify to insert my desired features (or for you to provide them to like an "undo" log that admins could use to identify and undo "bad" transactions (or all transactions made by a specific member over a specific period of time).