Mergebot Beta Update: Plugin Integrations and Query Selection

Ever since we opened up the beta of Mergebot to our lovely early adopters before Christmas, we have been working hard to make a dent in the list of limitations of the beta. The limitations were necessary to get Mergebot out the door, but initial feedback from our testers confirmed our thoughts that these limitations need to be lifted sooner rather than later.

Plugin Integration

Brad previously described how Mergebot needs to know certain information about a plugin, to ensure all data is merged correctly. This information ensures that any new table row, which is assigned a new ID when created, is deployed correctly and its new ID is used in all other queries that reference this new ID.

This information is stored in a schema JSON file, and as a quick recap, it can contain the following definitions:

  • The primary key columns for any custom tables
  • Columns in custom tables that are foreign key relationships to other tables
  • Key / value relationship data stored in meta tables (postmeta, options, usermeta) that contain IDs we want to replace on deployment
  • Attributes in shortcodes that contain IDs we want to replace on deployment
  • IDs stored in content columns, like the Posts table’s post_content column
  • Queries that should be ignored by Mergebot, and shouldn’t be deployed

Example

Unsuprisingly WooCommerce has quite a complex schema, as it has fourteen custom tables, quite a few shortcodes with ID attributes and lots of IDs store in meta data. To show you how important plugin schemas are, I’d like to show you an example deployment that contains queries for WooCommerce’s custom tables:

Plugins We Now Support

  • Advanced Custom Fields (4.4.11)
  • Advanced Custom Fields Pro (5.5.4)
  • Akismet (3.2)
  • All in One SEO Pack (2.3.11.3)
  • Contact Form 7 (4.6)
  • Duplicate Post (3.0.3, 3.1.2)
  • Easy Digital Downloads (2.6.17)
  • Enable Media Replace (3.0.5)
  • Google Analytics by MonsterInsights (5.5.4)
  • Google XML Sitemaps (4.0.8)
  • Gravity Forms (2.1.1)
  • Jetpack by WordPress.com (4.4.2)
  • Limit Login Attempts (1.7.1)
  • MailChimp for WordPress (4.0.11)
  • Regenerate Thumbnails (2.2.6)
  • TinyMCE Advanced (4.4.3)
  • WooCommerce (2.6.11, 2.6.12, 2.6.13)
  • Wordfence Security (6.2.10)
  • WordPress Importer (0.6.3)
  • Yoast SEO (4.0.2)
  • WP Super Cache (1.4.8)

Making Mergebot compatible with the large number of third party plugins out there is not a small task, but we’ve made a good start! Initially I looked at the most popular plugins being used on sites by our existing beta customers, and then added a couple more plugins that are in the top 10 of the WordPress.org plugin repositiory popular list.

We’ve set up a new GitHub repository to hold all the schemas that Mergebot now supports out of the box. Along with the schemas for WordPress core versions 4.3 up to 4.7, the plugins (and the specific versions) we support can be seen here.

Write Your Own Schema

We will be adding more all the time of course, but if there is a specific plugin you need for a site you are using Mergebot on, you can write your own schema for it and save it in your Mergebot settings:

Upload a Schema

Schemas seem scary at first, but to try and help in creating one, we have put together an ‘anatomy’ of a schema doc. That together with looking at examples of existing schemas should be enough to get you started. We are available to help out on email or the Mergebot Slack channel if you get stuck.

If you do start to write schemas for plugins we don’t support, feel free to raise a pull request so we can roll it into the application and others can benefit. We will be eternally grateful! 😀

Schema Generator

To assist in writing schemas I’ve developed a tool to do some of the grunt work for me. Mergebot Schema Generator is a WordPress plugin with a custom WP-CLI command that allows you to create a schema for a plugin.

wp mergebot-schema generate --plugin=woocommerce

Running the above command will download the latest version of WooCommerce (unless it already exists) and then define the primary keys and foreign keys. It will detect if the plugin uses key / value relationships and asks you if those values contain ID data. It does the same for any attributes of shortcodes the plugin registers. It’s not a complete solution but it is a good start.

It works for any plugin on the WordPress.org repository, as well as any premium plugin already installed on your site.

If you fancy trying it out to define some schemas then keep in mind a couple of things: this is a rough tool, there will be bugs, and you shouldn’t run this on a production site – run it in a new, development WordPress install.

Word Up Plugin Developers

Of course the ultimate goal here is for the developers of each plugin to create and maintain the schema for their own plugin, instead of us and Mergebot users having to write them. This is a lofty aspiration for sure, but it doesn’t stop developers getting involved sooner rather than later. If you are a developer of one of the plugins we already support then please take a look at what we’ve done. Is it correct? Are we missing anything?

Please feel free to open issues for existing schemas and raise pull requests for schemas you have written. We will be reaching out to developers soon to start this dialogue, but if you are reading this, then great!

More Control of Changesets

Mergebot’s recording button in the WordPress admin bar has allowed you to control which queries are part of your deployment. However, if you forget to turn on recording before making a bunch of changes to your site, you had to start over. Far from ideal.

We actually already send all queries to the application, but only queries executed whilst recording is on will make it to deployment. Having all these queries in the app means we can automatically include queries that are needed for the deployment that might not have been recorded. For example, if you aren’t recording and click ‘Add New’ post in the dashboard, WordPress at that point inserts a new record in the Posts table. If you turn on recording, fill out the post and then press ‘Publish’ you would assume all those queries for the new post are in your changeset ready for deployment. The first query that does the INSERT isn’t included as it was executed before you started recording. But Mergebot currently handles this for you, and automatically includes it. But what if you could tell Mergebot to include a whole batch of queries you forgot to turn recording on for? Don’t worry, Gilbert has you covered!

Query Selection

Query Selection is a new feature that radically enhances the changeset page in the app, which displays all of the queries to be deployed. Previously it was a read-only list of SQL statements to give you an idea of what will be executed at deploy time. Well, not anymore! You are now presented with a screen that shows the queries that are to be deployed, as well as groups of queries that have been saved to Mergebot but are not flagged for deployment.

Any query can be deselected so it won’t be deployed. Along with bulk select/deselect tools for groups of queries (executed within 5 seconds of each other), this changeset screen now gives full control over what will be deployed. The obvious benefit is being able to go back and include those changes you made if you forgot to turn on recording, but the other benefit I have seen during testing is to deselect queries that just aren’t relevant and that I don’t want deployed.

Until you start looking at all the queries executed on your site, you don’t realise just how many SQL statements are being run by plugins. A lot of these queries are environment specific: saving cached data here, setting options over and over again, basically stuff that doesn’t need to be taken across to the production site on deployment. We do allow queries to be ignored by defining them in the schema for a plugin, but being able to just quickly deselect a chunk of queries we don’t need is awesome. Just be careful that you don’t leave other queries still selected that rely on a query you have since deselected, which could result in things breaking at deployment.

Up Next

There’s plenty more work to be done but I thought I’d give you a hint of what’s high on our todo list. I’ll be starting work on making Mergebot compatible with multisite installs. This will mean some changes to the plugin and expanding the core schemas to include the network related tables. I’ll also be regularly creating new plugin schemas, usually going down the list of popular plugins in the WordPress.org plugin repository, but if you have any requests hit me up in the comments below.

Gilbert will be working on removing the 1,000 query limit for a changeset, which will involve some benchmarking and performance improvements. He will also be looking at handling duplicate values in columns without a MySQL UNIQUE index.

If you are already a Mergebot beta customer then I hope these features are a welcome improvement. If you haven’t tried Mergebot yet then there are seats still left for the beta. What other features would you like to see? Are there any specific plugins you’d like to see a schema for? Let us know in the comments below.

About the Author

Iain Poulson

Iain is a WordPress and PHP developer from England. He builds free and premium plugins, as well as occasionally blogging about WordPress. Moonlights as a PhpStorm evangelist.