Skip to content
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

Custom Action Scheduler database tables #77

Open
thenbrent opened this issue May 15, 2017 · 13 comments

Comments

Projects
None yet
6 participants
@thenbrent
Copy link
Member

commented May 15, 2017

For scalability, we need to move Action Scheduler away from using a Custom Post Type datastore, to a highly scalable new schema in custom tables. To do this at a high level requires:

  • determine a new, highly scalable database schema
  • implement a new data store to interact with that new schema
  • include database migration paths for both WP CLI and standard WordPress
  • create a feature plugin for testing prior to merge/release in Action Scheduler core: https://github.com/Prospress/action-scheduler-custom-tables/ (requires PHP 5.6)
  • merge code into Action Scheduler v3.0 for release

For current progress, see the this repo: https://github.com/Prospress/action-scheduler-custom-tables/

@thenbrent thenbrent added this to the 2.0 milestone May 15, 2017

@thenbrent thenbrent changed the title Custom Action Scheduler Tables in Database Custom Action Scheduler database tables May 15, 2017

@unfulvio

This comment has been minimized.

Copy link
Contributor

commented May 16, 2017

following this!

while we were pondering whether to include the Action Scheduler in Memberships or look for another solution, we had put together a quick PoC about what a similar tool based on custom tables would look like

https://github.com/skyverge/wp-single-events-scheduler

this is very rough and hastily executed -- as a matter of fact we opted for Action Scheduler in the end, considering it has been "battle tested" for a very long time

Perhaps it could be a good idea if the AS follows a similar approach of Data Stores as in WC 3, in case in future we want to plug alternative implementations in how the actions are stored

@thenbrent

This comment has been minimized.

Copy link
Member Author

commented May 16, 2017

it could be a good idea if the AS follows a similar approach of Data Stores as in WC 3

@unfulvio Action Scheduler has always provided an extensible data store. Take a look at the abstract ActionScheduler_Store which is implemented via ActionScheduler_wpPostStore. That can be overridden with the 'action_scheduler_store_class' filter: https://github.com/Prospress/action-scheduler/blob/master/classes/ActionScheduler_Store.php#L122

It doesn't offer any way to create a data store per action or per action type similar to the way WC 3.0 has a data store per object (e.g. different store for different product types). Whether it needs that or not I'm not sure.

@thenbrent

This comment has been minimized.

Copy link
Member Author

commented May 16, 2017

Something new that would be very useful to add while changing the schema would be to introduce a parent ID relationship, similar to post_parent in wp_posts. This will make querying actions specifically related another post, like a subscription, membership or email, substantially faster.

The only tricky part will be making sure that the table the parent ID is in is not bound to the wp_posts. At the moment, all the objects I've seen an action relate to are custom post type, but they may not always be, especially with the flux around custom tables with WooCommerce (I believe the current plan is to continue to use the wp_posts table for IDs however, and only move most of the meta into a custom table specially designed for orders).

@thenbrent

This comment has been minimized.

Copy link
Member Author

commented Jul 4, 2017

Work on this now well underway in https://github.com/Prospress/action-scheduler-custom-tables/

@franticpsyx

This comment has been minimized.

Copy link
Member

commented Sep 12, 2017

The only tricky part will be making sure that the table the parent ID is in is not bound to the wp_posts.

Just a note that WC is eventually bound to face the same challenge if post/product data gets migrated to a new table. There are lots of metadata in core that reference post IDs, e.g. grouped product children and up/cross-sells.

Been thinking about the same as Product Bundles has its own bundled item ID / bundled product ID / bundle ID table that references product IDs from the posts table.

@toddlahman

This comment has been minimized.

Copy link
Member

commented Sep 12, 2017

It may be beneficial to try to coordinate a solution that is consistent with how WC plans to migrate data, or vice versa, to maintain consistency in the approach. If an open discussion on this topic ensues on the WC repo, please reference it here, as I'm not able to monitor every discussion on these critical topics at the moment, but would like to see how it evolves.

@thenbrent

This comment has been minimized.

Copy link
Member Author

commented Sep 12, 2017

It may be beneficial to try to coordinate a solution that is consistent with how WC plans to migrate data, or vice versa, to maintain consistency in the approach

We now have a fully functional plugin in beta that implements custom tables here: https://github.com/Prospress/action-scheduler-custom-tables/

That includes database migration in a few forms:

  1. a WP CLI command to upgrade a database from the command line (this is the fastest method but it comes with the need for a developer to perform it)
  2. a background migration process which uses Action Scheduler to migrate batches of actions from the old tables to the new
  3. a new Hybrid_Store concept which:
    1. is used as the primary data store whenever there is still data in the old tables
    2. will "hot-swap" data from the old tables/schema to the new tables/schema whenever it is accessed (kind of like a JIT upgrader)

We hope to test these concepts thoroughly both in the Custom Tables plugin and then in Action Scheduler itself to prove or disprove their usefulness. We can then share the results of that approach with those working on custom tables for WC core (I've already sent a few different folks links to that plugin and the Hybrid_Store).

@toddlahman

This comment has been minimized.

Copy link
Member

commented Sep 12, 2017

The code looks awesome as usual Brent!

@franticpsyx

This comment has been minimized.

Copy link
Member

commented Sep 13, 2017

@thenbrent some thoughts:

For 3rd parties, the main issue with migrations like this will be how to establish a relationship between old vs new IDs.

If 3rd party plugins store references to old IDs in the DB, then ideally these will need to be migrated at the same time, possibly by including an action in the function that handles the update/migration (for third parties to use), or/and by persisting this old/new information somewhere.

JIT migration is a cool concept but again if third parties rely on your data, they will need to be able to read data from two DB sources. A simple example -- if a third-party table references a Scheduled Action / Product / Order ID somewhere, it will need to know which table contains the referenced ID: the posts table or the new one?

Really interested to see how WC core developers will approach this. Rolling back is also going to be a tough project for everyone.

@thenbrent

This comment has been minimized.

Copy link
Member Author

commented Sep 13, 2017

For 3rd parties, the main issue with migrations like this will be how to establish a relationship between old vs new IDs.

With Action Scheduler, that's not an issue.

Action Scheduler doesn't expose an action's ID via any API. Nor do any APIs allow you to query an action by ID. You'd need to do raw queries on posts, which has never been supported so not something we're going to maintain compatibility for.

If 3rd party plugins store references to old IDs in the DB

If 3rd party plugins are doing that with AS, they're definitely doing it wrong.

JIT migration is a cool concept but again if third parties rely on your data, they will need to be able to read data from two DB sources.

Again, only if they're querying the DB directly, which has never been supported. Action Scheduler since v1.0 has used data stores, and all data access should have always gone through those stores. Those data stores never

if a third-party table references a Scheduled Action / Product / Order ID somewhere

You're conflating issues that are present with WC core's migration with the AS migration. Two entirely different things. IDs aren't an issue with AS. They are a big issue with WC. At least one solution that has been proposed for the issue with WC is to continue to use the posts table for IDs, so the IDs don't ever change, but move all meta into custom table so it can be indexed/queried with joins. But this isn't the time or place to discuss issues with WC migrating order data. 🙂

@franticpsyx

This comment has been minimized.

Copy link
Member

commented Sep 14, 2017

@thenbrent apologies if I was a bit off-topic here, the only reason I shared these thoughts was because you mentioned this:

We can then share the results of that approach with those working on custom tables for WC core.

If there's no use case where AS IDs are stored directly, then my comments are indeed not relevant to AS.

@sandrodz

This comment has been minimized.

Copy link

commented Oct 30, 2018

Is this still on horizon?

@JPry

This comment has been minimized.

Copy link
Collaborator

commented Oct 30, 2018

@sandrodz This is currently scheduled for version 3.0.0. In the meantime, this is currently available as a separate plugin, as mentioned in this comment.

thenbrent added a commit that referenced this issue Jan 23, 2019

Use 'pre_wp_unique_post_slug' for unique post name
WordPress 5.1 is introducing the 'pre_wp_unique_post_slug' filter
for plugins to short-circuit the non-performant approach it takes
to determining unique post slugs/post_name values.

We can take advantage of this filter to fix #44 as an interim perf
improvement until #77 is finished.

thenbrent added a commit that referenced this issue Jan 23, 2019

Use 'pre_wp_unique_post_slug' for unique post name
WordPress 5.1 is introducing the 'pre_wp_unique_post_slug' filter
for plugins to short-circuit the non-performant approach it takes
to determining unique post slugs/post_name values.

We can take advantage of this filter to fix #44 as an interim perf
improvement until #77 is finished.

This was referenced Feb 12, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.