Easy-digital-downloads: Migrate payments to a custom table

[pippinsplugins]

In order to ensure Easy Digital Downloads is a platform that developers continue to want to build on top of and that it is one that _we_ wish to continue to build, payment records need to migrated to a custom table.

The current data schema, which relies on the wp_posts and wp_postmeta tables, is highly inefficient and has caused us, and many others, difficulties for quite some time.

There is no question about the performance benefits that can be gained by migrating our payment records to a custom database table. It will allow us to control the database schema precisely and exactly define how our data relationships are structured.

One of the primary weaknesses of Easy Digital Downloads over the last four years has been the underlying structure of the payment database, which all stems from my own ignorance when the first foundations were laid.

As the project has grown so has the demands put upon it. It is abundantly clear now that the original hap-hazard schema created 4 years will not suffice far into the future.

It is time to begin investigating what the process of migrating to a custom database schema will look like. At minimum, it will include the following:

1. A well thought out payments table and a corresponding payment meta table
2. A backwards compatibility layer that ensures all existing calls to get_post_meta() and get_post() for the edd_payment post type continue to function
3. A migration script (UI and CLI) that handles the transfer from data from wp_posts/wp_postmeta to the new tables
4. The update of _all_ official extensions to utilize and comply with the new database structure.

[pippinsplugins]

This will also provide us with the ability to remove several very poor and painful aspects of our current database schemas, namely the downloads, cart_details and item_number arrays currently stored in payment meta.

[sunnyratilal]

I believe we should remove most, if not all serialised data from the database when performing the migration.

Albeit serialised data is good for storing a lot of data, it breaks the relational model.

Unserialised data also alows for searching and advanced queries to take place on the database layer. If we take an example that requires a search involing the _edd_payment_meta meta key, we have to query that data using a SELECT statement first, unserialise it in PHP and then iterate over the data to conduct the search. Whereas with unserialised data, we can do a MySQL LIKE statement and perform the searches on the database level making the data accessible much quicker and the query more efficient too.

[sunnyratilal]

For backwards compatibility with get_post_meta() we can hook on to the get_post_metadata filter and hijack the query.

[pippinsplugins]

Yes, all serialized data will go away.

We will actually need three tables (at least):

• payments
• payment_meta
• payment_items

[pippinsplugins]

If we choose to use background processing for the upgrade routine, @bradt reminded me of this: https://github.com/A5hleyRich/wp-background-processing

[daigo75]

From perspective (as a DBA as well), serialised data is all good as long as one doesn't have to query it. I use it myself for elements such as settings, that always go together and should be queried. :smile:

Regarding the actual big change, the new class should no longer be declared as "final". I would have used it earlier, but I can't inherit it at the moment, and that prevents me from extending it with all the multi-currency goodies it would need. :smile:

[pippinsplugins]

@daigo75 final has been removed for 2.6: https://github.com/easydigitaldownloads/easy-digital-downloads/issues/4046 :)

[daigo75]

Perfect. :+1:
Now I will just have to find the time to use the new class and maintain backward compatibility. :smile:

[davekiss]

For inspiration on the DB structure, I might suggest having a look at the resulting schema from a Rails ecommerce platform called Solidus - I've had a positive experience with it on other projects, but certainly miss the "digital" aspect of EDD

[pippinsplugins]

@daigo75 Note: our intention is that _every_ current method of accessing the data will continue to work, including those such as get_post_meta().

[pippinsplugins]

Thanks @davekiss!

[cklosowski]

We can look at the backwards compatible method we used for payment tax meta for backwards compatibility using get_post_meta.

[pippinsplugins]

We'll also need to account for updates, inserts, and deletions of post meta.

[jcomack]

I'm actually in the process of migrating some of our EDD data into custom, normalized tables via a custom migration tool. I also started looking at extracting most (not all) data into separate tables so (database) maintenance becomes a lot easier.

I'll report back when it's finished, but it might come in handy when trying to tackle this issue.

P.S. Was there already a plan / database design made? I just made one based on the formal training I had. I can continue splitting up the data even more and ensuring everything has their own, neatly designed table.

[pippinsplugins]

@jcomack we do not have a plan set in stone yet.

[jordymeow]

Are you still on it guys?

[pippinsplugins]

Yes but we're doing the process in a couple of extensions first to nail down the process. The first we're attacking is the Commissions extension. After that we'll do Software Licensing, then we'll do core here.

[hiddenpearls]

@pippinsplugins Have you guys released this fix in EDD?

[cklosowski]

@hiddenpearls no, this issue is still open but is slated for our next major release. We've begun our process of releasing a few extensions using custom table migrations, and once those are done payments is next.

We have some initial thoughts on the table schemas that we believe will work best for us.

[hiddenpearls]

@cklosowski That is great. Looking forward.

[JJJ]

Added the In Progress label because this is _literally_ in progress.

• The tables themselves were merged into the release/3.0 branch, and I'll have a PR for the query class imminently
• I'm going to extend EDD_DB so it's familiar to everyone (vs. a WordPress-like _Query_ class)
• Back compat will be filled in incrementally, based on previous/similar work from @sunnyratilal
• @cklosowski is working on the migration, others will be helping with CLI/API/etc, I'll be assisting anywhere & everywhere
• Next step will be creating a "WIP" pull-request that is merged into release/3.0 and unblocks the rest of the above work

[sunnyratilal]

Phase 1 merged.

[sunnyratilal]

@easydigitaldownloads/core-team CLI migration routine is ready for first round of testing. PR opened at #6641.

[pippinsplugins]

Noticed one small item that needs addressed.

Also, could we move the migration logic into a migrate() method like we did with discounts so that CLI and UI can share the exact same code without having lots of duplication?

[sunnyratilal]

Phase 2 merged. 🎉

[sunnyratilal]

Phase 3 involves backwards compatibility for WordPress Core methods and improved performance of existing EDD core methods by making use of the new query methods and new schema.

E.g. edd_has_user_purchased() can be completely refactored and be made more efficient.

[sunnyratilal]

Closing as this is now complete. The UI migration routine will be introduced #6275.