Adjusting the code to use the view-based approach

One of the issues to solve when implementing the view-based approach described in my previous post on the topic is how to make the current codebase be aware of when to use a view and when to keep operating on a table.

The problem is twofold. First, the set of tables which hold localisable data will change from release to release (we might want to localise more data in the future, or schema changes might move a localisable column to some other table), and we don’t want have to track what table is localisable by hand; at the same time, localising all tables wouldn’t be practical (as we really want to do that to just a couple of them). Second, it would be most useful if there was a way that would somehow automagically handle this without other CiviCRM coders having to remember to glue $dbLocale variable to the end of any hand-crafted SQL.

Most of CiviCRM’s database operations are done through an ORM mapper (DB::DataObject), and are handled by auto-generated DAO classes; it was enough to switch the template for generating these classes to make the getTableName() method return the proper view’s name.

Unfortunately, quite a bit of custom data functionality is done using hand-crafter SQL queries. There are often referenes in the same query to both localisable and non-localisable tables; the references are in all of SELECT, FROM and WHERE parts of the queries; also, quite often the resulting column names incorporate table names as prefixes, under which they are subsequently visible as object properties. Also, quite often the table names used in the queries are not literal strings, but keys in hashes which are also used elsewhere.

All of the above made me scratch my head quite a bit; even if I could track down all the references to table_x (and turn them to references to table_x_$dbLocale), I still would wonder whether I didn’t miss anything, it would be hard to grasp by any other developer (‘why does this table’s name have something appended to it, while that doesn’t?’), hard to maintain, and all the people who wrote third-party extensions would have to update their code as well.

All of the custom SQL queries pass through one central place, namely CRM_Core_DAO::executeQuery(); unfortunately, this function gets the query as a single string, not as a set of parameters that could be operated upon (the assumption being that it’s used for those of the queries that DataObject can’t handle, most often more sophisticated ones). Fortunately, after looking a bit at the queries passed through it, I managed to come up with a set of regular expressions that match the localisable table names and can replace them with view references, while at the same time skipping any of those references used in output labeling (and, thus, maintainting the PHP-side property references).

This approach ends up in very small changes (basically, a couple of lines in one method call), while at the same time being ‘invisible’ to other developers, who don’t have to remember (or even know) they’re now using localised views; unless they debug the actual SQL, it still looks (and feels) like direct table access to any code outside executeQuery() and the DAOs.