Custom Action Scheduler database tables
thenbrent opened this issue · 15 comments
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/
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
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.
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).
Work on this now well underway in https://github.com/Prospress/action-scheduler-custom-tables/
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.
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.
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:
- 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)
- a background migration process which uses Action Scheduler to migrate batches of actions from the old tables to the new
- a new
Hybrid_Storeconcept which:- is used as the primary data store whenever there is still data in the old tables
- 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).
The code looks awesome as usual Brent!
@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.
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. 🙂
@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.
Is this still on horizon?
@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.
👍