New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Migrate payments to a custom table #4576

Closed
pippinsplugins opened this Issue May 14, 2016 · 27 comments

Comments

@pippinsplugins
Member

pippinsplugins commented May 14, 2016

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 pippinsplugins added this to the 2.9 milestone May 14, 2016

@pippinsplugins

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 14, 2016

Member

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.

Member

pippinsplugins commented May 14, 2016

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

This comment has been minimized.

Show comment
Hide comment
@sunnyratilal

sunnyratilal May 14, 2016

Member

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.

Member

sunnyratilal commented May 14, 2016

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

This comment has been minimized.

Show comment
Hide comment
@sunnyratilal

sunnyratilal May 14, 2016

Member

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

Member

sunnyratilal commented May 14, 2016

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

@pippinsplugins

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 14, 2016

Member

Yes, all serialized data will go away.

We will actually need three tables (at least):

  • payments
  • payment_meta
  • payment_items
Member

pippinsplugins commented May 14, 2016

Yes, all serialized data will go away.

We will actually need three tables (at least):

  • payments
  • payment_meta
  • payment_items
@pippinsplugins

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 14, 2016

Member

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

Member

pippinsplugins commented May 14, 2016

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

@daigo75

This comment has been minimized.

Show comment
Hide comment
@daigo75

daigo75 May 14, 2016

Collaborator

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. 😄

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. 😄

Collaborator

daigo75 commented May 14, 2016

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. 😄

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. 😄

@pippinsplugins

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 14, 2016

Member

@daigo75 final has been removed for 2.6: #4046 :)

Member

pippinsplugins commented May 14, 2016

@daigo75 final has been removed for 2.6: #4046 :)

@daigo75

This comment has been minimized.

Show comment
Hide comment
@daigo75

daigo75 May 14, 2016

Collaborator

Perfect. 👍
Now I will just have to find the time to use the new class and maintain backward compatibility. 😄

Collaborator

daigo75 commented May 14, 2016

Perfect. 👍
Now I will just have to find the time to use the new class and maintain backward compatibility. 😄

@davekiss

This comment has been minimized.

Show comment
Hide comment
@davekiss

davekiss May 14, 2016

Collaborator

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

Collaborator

davekiss commented May 14, 2016

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

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 16, 2016

Member

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

Member

pippinsplugins commented May 16, 2016

@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

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins
Member

pippinsplugins commented May 16, 2016

Thanks @davekiss!

@cklosowski

This comment has been minimized.

Show comment
Hide comment
@cklosowski

cklosowski May 16, 2016

Member

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

Member

cklosowski commented May 16, 2016

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

@pippinsplugins

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 16, 2016

Member

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

Member

pippinsplugins commented May 16, 2016

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

@jcomack

This comment has been minimized.

Show comment
Hide comment
@jcomack

jcomack Dec 12, 2016

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.

jcomack commented Dec 12, 2016

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

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins Dec 12, 2016

Member

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

Member

pippinsplugins commented Dec 12, 2016

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

@jordymeow

This comment has been minimized.

Show comment
Hide comment
@jordymeow

jordymeow May 10, 2017

Are you still on it guys?

jordymeow commented May 10, 2017

Are you still on it guys?

@pippinsplugins

This comment has been minimized.

Show comment
Hide comment
@pippinsplugins

pippinsplugins May 10, 2017

Member

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.

Member

pippinsplugins commented May 10, 2017

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

This comment has been minimized.

Show comment
Hide comment
@hiddenpearls

hiddenpearls Oct 1, 2017

@pippinsplugins Have you guys released this fix in EDD?

hiddenpearls commented Oct 1, 2017

@pippinsplugins Have you guys released this fix in EDD?

@cklosowski

This comment has been minimized.

Show comment
Hide comment
@cklosowski

cklosowski Oct 1, 2017

Member

@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.

Member

cklosowski commented Oct 1, 2017

@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

This comment has been minimized.

Show comment
Hide comment
@hiddenpearls

hiddenpearls Oct 1, 2017

@cklosowski That is great. Looking forward.

hiddenpearls commented Oct 1, 2017

@cklosowski That is great. Looking forward.

@pippinsplugins pippinsplugins removed this from the 2.9 milestone Dec 4, 2017

sunnyratilal added a commit that referenced this issue Jun 15, 2018

sunnyratilal added a commit that referenced this issue Jun 15, 2018

sunnyratilal added a commit that referenced this issue Jun 15, 2018

sunnyratilal added a commit that referenced this issue Jun 15, 2018

@sunnyratilal

This comment has been minimized.

Show comment
Hide comment
@sunnyratilal

sunnyratilal Jun 30, 2018

Member

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

Member

sunnyratilal commented Jun 30, 2018

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

3DD™: Custom Tables automation moved this from Todo to Done Jun 30, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment