|
|
|
the ordeal of an overly-generic database
Monday, October 23 2017
Sarah the Vegan showed up this morning with bagels, which Gretchen had been craving since at least Saturday. Later Nancy arrived with a cup of Stewart's Cold Brew iced coffee, which I keep forgetting to get when I go there (even though it is delicious). Gretchen can't stand for long at a counter, so I'd done some prep work on the various fixin's (capers, red onions, tomatoes, vegan cheeses, sundried tomatoes, and even jalapeños. My workday began shortly thereafter, though it had actually begun well before my usual start-time of noon when I was alerted to a data snafu resulting from an export I'd run. A datecode had been screwed up, and I'd fixed it with a search and replace, but this hadn't updated a check digit, which was now throwing an error when the overall code was validated. These are the sorts of crises I deal with when servers aren't crashing and algorithms aren't acting as expected.
After solving that problem, it still took a good five hours to re-run all the scripts to produce a valid export. As that was going on, I turned my attention to writing a report designed to present data from the database undergirding The Organization's WooCommerce-based store. WooCommerce sits atop the WordPress infrastructure and so, to understand WooCommerce, one must understand WordPress. Having spent a good bit of time migrating data into WordPress databases, I can say definitively that WordPress is an attempt to be a generic database system, one that is largely abstracted-away from developers. Most entities in WordPress are posts, which live in the wp_posts table. Posts can be parents of other posts. Any information about an entity for which there is no column in wp_posts goes in wp_postmeta. That wp_postmeta table can also be used for creating complex many-to-many relationships between posts. All of this works okay, and the framework is flexible enough to accommodate practically any database design. But when dealing with the database without the help of the PHP code in WordPress, one is forced to write really unpleasant SQL queries, ones where getting data that would typically be found in an entity's table (in a purpose-built database) must instead be retrieved by an arduous subquery of the wp_postmeta table. These subqueries can't possibly be as efficient as queries in a simple purpose-built database, so it's hard to imagine a complex webapp in WordPress being able to scale up to handle large amounts of data. This is probably why WordPress is more typically used as a web publishing platform, one that requires massive caching to prevent constantly executing all those complicated queries required by its generic database design.
Despite the challenge of writing SQL-only queries to retrieve sales data from the store's unhelpful WordPress database, I was able to do it. Not only that, I was actually able to do it from the contact database's reporting system (which is on the same server and usually only looks at a database of donor records). Doing it from there gave me the full suite of reporting features that I've been developing for the last year and a half.
For linking purposes this article's URL is:
http://asecular.com/blog.php?171023 feedback
previous | next |
