Woo Orders – Part 2

sales receipt

WooCommerce relies on a 2 table schema — one fat, one skinny — as the primary method for constructing Order data.  A variety of other tables provide auxiliary information inflight.

It is difficult for a non-database professional to extract data from this sink.

Woo does not expect its customers to know how to write PhP code.  In Woo’s view, this is the job of specialists, sort of like old school IT departments —  ie, highly trained developers who are versed in the “technology stack” that is the hallmark of modern Web development.

Woo expects these specialists to use an interface called a “REST API” to extract or manipulate data that persists in the Woo/WP datastore.

Absent is the alluring promise of relational databases — with their declarative, somewhat verbose, set-based programming sublanguages, that in theory should allow a programmer to largely be unconcerned with how structured data is saved to disk or retrieved.

Thus, a non-technical Woo store owner cannot with ease directly query the Woo database in an ad hoc fashion; for example, to independently verify and/or audit the validity of Woo’s assertion as to what it holds to be relational facts.

Specialized, rather arcane knowledge, is required.

In my previous post, we saw how to extract a subset of Order data, particularly if we are interested in performing a form of applied ELH analysis to a WooCommerce Order — in other words, how does a Woo order get born, live, then die?

A simple query with only 1 inner join was needed to populate (insert) an ISAM DSS table with Order transaction rows.

We also saw how an Order passes through multiple states.

Woo maintains a log of these states in a table called wp_comments.  Rows with the field comment_type equal ‘order_note’ trace each step along the way.

That is a valuable table, a crucial one, in fact, for the purpose of creating a temporal ELH table.

A common way to capture temporal info is to declare a point-based data model, which is what we shall do with a table called trans_1.

The role of trans_1 is to:

  1.  Provide information relating to Order-level state transitions
  2. Create an efficient means to download or extract Order data from Woo without having to deal with its REST API
  3. Serve as an independent accounting-level verification for the data that is provided by Woo’s reports

Let’s assume for a moment, as a learning exercise, that there is no wp_comments table.  This will help us better understand the Woo Schema and how it uses WP’s underlying tables to track Orders data.

In such a case, how could we populate trans_1?

Leaving aside performance considerations, a trigger might be an attractive option to automatically populate our custom Trans (ie, Order) table. This, in fact, is one of the traditional solutions in data processing to this classic problem; another is having an app write twice, once to the TRANS table in question, the other to an associated historical, timestamped “versioning” table.  These normally feature rolling time-period views (such as yearly, semi-annually, quarterly, monthly, weekly, and daily).

The trigger approach would eliminate the need to write a cron job that would periodically run the INSERT query that we saw in the previous post. This begs the question, how frequently should it poll the wp_posts table? How do we ensure that state transitions are not missed?

To sidestep this problem, triggers may offer a low-code solution.

I will try to demonstrate how this might work, with an Insert trigger, MariaDB-style.  Essentially the trigger populates Table B, every time a row is inserted in Table A.

(In our real-world Order example, Table B would have to have, also, an Update trigger, to retrieve Transaction Amt order-level data from the auxiliary EVA table that the wp_posts table uses to construct virtual Order tables on the fly. This sucks, but there you have it.)

Here goes.

First, I will create a table in PHPMYADMIN that replicates or mirrors the structure of the sb_trans_1 table that we saw in the previous post.  I will call this table sb_trans_2.

CREATE TABLE IF NOT EXISTS `sb_trans_2`
 (
`Trans_ID` bigint(20) NOT NULL, 
`Trans_status` varchar(20) NOT NULL, 
`Trans_date` datetime NOT NULL,
`Trans_amt` decimal(13,2) NOT NULL, 
CONSTRAINT `Trans_pk`
PRIMARY KEY (`Trans_ID`, `Trans_status`) ) 
ENGINE = 'MyISAM';

Here it is, in my local Sandbox MariaDB database.

Next, I will attach a trigger to sb_trans_1.  For demo purposes, I will use an artificial firing condition — that the Order Number exceeds an Order I manually created in the database.  In a real-world implementation, the test condition would, of course, be to perform a `post_type` = “shop_order” column eval on the wp_posts table.

Here’s the trigger:

DELIMITER //
CREATE TRIGGER addrow_sb_trans_2
AFTER INSERT ON sb_trans_1
FOR EACH ROW
BEGIN
IF NEW.Trans_ID > "117" THEN
INSERT INTO sb_trans_2 (Trans_ID, Trans_status, Trans_date, Trans_amt)
VALUES (NEW.Trans_ID,
NEW.Trans_status,
NEW.Trans_date,
NEW.Trans_amt);
END IF;
END; //
DELIMITER ;

Now let’s add an order to the trans_1 table, #118. We can use the Insert Panel on PHP admin to accomplish this task.  As you can see below, trans_2 is automatically updated.

Conclusion:  the trigger idea shows promise — maybe.

Let’s try it on wp_posts, just for laughs.  This is the Big Kahuna table in the WP/WOO database universe.  We add the following trigger.

DELIMITER //
CREATE TRIGGER addrow_sb_trans_1 
AFTER INSERT ON wp_posts 
FOR EACH ROW 
BEGIN 
IF NEW.post_type = "shop_order" 
THEN INSERT INTO sb_trans_1 
(Trans_ID, Trans_status, Trans_date) 
VALUES (NEW.ID, NEW.post_status, NEW.post_date);
END IF; 
END; //
DELIMITER ;

Now let’s try to add a row using WooCommerce -> Orders in the WP admin panel.

Disaster.

And here is some further unexpected behavior.

The naive moral of this exercise is, don’t use triggers on WP/WC tables,

Out of curiosity, I investigated what caused the Fatal Error.  It turns out that our seemingly innocuous trigger resulted in the invocation of code that fires off a call at Line 160 that crashes the plugin. Why this happened shall forever remain one of the mysteries of life.

brackets code

Btw, I also tried, just for kicks, attaching a trigger to the wp_wc_order_stats, which is also populated with Orders data.

woo order stats table

This, too, didn’t work, but we might use this table down the road, as it provides a useful breakdown of the dollar amt descriptors that comprise a total Order amount.

In conclusion, it’s a good job that wp_comments exists.

But we’ve learned quite a bit about how Orders are handled internally in the WP database.  This will help us construct the SELECT queries that we will be needing.

In my next post, I will create a parameterized stored procedure that extracts and transforms Order data from the Woo tables mentioned above and loads the result into trans_1. Most likely I will write the result set to disk, then load the file into the table, as this is far faster than doing inserts, and I won’t have the transaction overhead to deal with. Besides, trans_1 is an ISAM table:  runs like greased lightning for DSS queries, not so fast OLTP.

From a data architecture standpoint, trans_1 will provide an ad hoc means to populate StoreBoss™ with dynamic Order data on a temporal basis.

All I would then have to do is put together a simple PHP GUI WP plug-in that supports CSV exporting to a local machine, and we can go on from there.

Most likely the trans_1 SP would look something like this:

SELECT 
p.ID AS "Trans ID",
p.post_date AS "Trans Date",
c.comment_content AS "Trans Status", 
c.comment_date AS "Status Date",
o.meta_value AS "Trans Amt"
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)
ORDER BY p.post_date;

which results in

sql result

Bingo.

Advertisements