WC Orders – more FSM analysis

wp erd
WP 4.4.2 schema

If you take a look at the entity-relationship diagram for WP’s original core tables (see above), which comprised a baker’s dozen or so tables, you might get the impression that RI constraints are being enforced via an underlying database engine.

In reality, most of WP/WC’s RI constraints are maintained through aplication code.

This can easily be verified by querying the MariaDB information schema catalog, as shown here.

Why was native RDBMS RI not implemented by WP?

According to Mike Little (one of the original developers of WordPress), it was an accident of history.

The initial DB schema for WP was created by Michel Valdrighi in the early 2000s. Valdrighi was the author of B2/Cafelog, from which WP was forked.

Per Little, either MySQL’s support for referential integrity was lacking back then, or, the database drivers for PHP at the time did not support RI.

The net result is that Woocommerce — an OLTP plugin that extends some core WordPress tables, as well as adds another half dozen or so tables to WP’s original schema — ended up being relational in name only. This despite being a domain that’s ideally suited for the kind of normalized schema implementation that you will find in any database 101 textbook.

In reality, WC product and transactional persistence is achieved via a hard-to-understand, key-value scheme that scales poorly and contains serialized columns that make porting or migrating this database less straightforward than might otherwise be desired.

We will now write a query (Q1) that materializes temporal order-related information.

In the example below, Q1 results in data pertaining to Order 117.

p.ID AS "Trans ID",
p.post_date AS "Trans Date",
p.post_modified AS "Trans Mod Date",
c.comment_content AS "Trans Status", 
c.comment_date AS "Status Date",
x1.meta_value AS "Order Key",
x2.meta_value AS "Currency",
o.meta_value AS "Order Total",
x3.meta_value AS "Completed Date",
x4.meta_value AS "Paid Date"

FROM wp_posts p

LEFT JOIN wp_comments c ON
(p.post_type = "shop_order"
AND p.ID = comment_post_ID 
AND c.comment_type = "order_note")

INNER JOIN wp_postmeta o ON 
(o.`meta_key`= "_order_total"
AND p.ID = o.post_id)

INNER JOIN wp_postmeta x1 ON 
(x1.`meta_key`= "_order_key"
AND p.ID = x1.post_id)

INNER JOIN wp_postmeta x2 ON 
(x2.`meta_key`= "_order_currency"
AND p.ID = x2.post_id)

INNER JOIN wp_postmeta x3 ON 
(x3.`meta_key`= "_completed_date"
AND p.ID = x3.post_id)

INNER JOIN wp_postmeta x4 ON 
(x4.`meta_key`= "_paid_date"
AND p.ID = x4.post_id)

WHERE p.ID = 117
ORDER BY p.post_date;


Q1 produces the following result.

wc data

We now have a starting point for analyzing temporal data with respect to state changes that a WC order can go through.

The FSM state transitions for the WC Orders domain  can be listed as follows:

OS0 unknown (custom state extension)
OS1 pending payment
OS2 processing
OS3 on hold
OS3 completed
OS4 canceled
OS5 refunded
OS6 failed
OS7 manager approved (custom state extension)

In our next few posts, we will further analyze the results produced by a variant of Q1, using a more comprehensive example.

We’ll also inspect a few additional tables — such as the notorious wp_options tables (home of ballooning transient data sets) as well as the quasi indecipherable woocommerce_order_items and woocommerce_order_itemmeta tables.

To anyone who follows Woo or is a Woo customer, it will not come as a revelation that Woo can quickly become cumbersome, complex, and slow.  As many respected observers have already noted, the WP/Woo schema is not particularly well-suited for e-commerce.

With this in mind, products such as Glew have been developed and are available for major e-commerce retailers. But they are expensive.  Still, it does not cost anything to learn from their YouTube tutorials.

Speaking of expensive, Liquid Web offers a pricey WooCommerce managed solution; we will eventually be taking a look at their custom orders plug-in, which is available for free on Github.

Our goal is to try to devise an open-source (ie, affordable!), best-practices way of producing sophisticated order analytics on a highly secure platform that does not consume costly hosting CPU cycles — namely, the desktop.

But first, we will have some fun in serverland, and take a look at the temporal semantics of WC orders, and how MariadDB 10.3+ might be used to extend these such that the average non-technical store owner can better, as the StoreBoss™ tagline goes, mind his or her business.