A few months back I announced a new feature called Quicksaves. Each Quicksave contains a fair amount of website data which I’ve been storing in a WordPress custom post type and some custom fields with Advanced Custom Fields. Right from the beginning, I’ve suspected that this might quickly grow beyond what a WordPress custom post type was intended for. However it was the easiest way to get things started.
The alternative would be to create a custom database table. That said, some 20,000 Quicksaves later, I decided to jump in and move my custom post type and custom fields over to a custom table. I’m by no means an expert at this. The following is an overview of the steps I took in order to complete the switchover.
What is a Quicksave and why store it in WordPress?
A Quicksave is something I made up. It’s an automatic git repository storing a copy of each website’s themes, plugins and version numbers. It’s just a collections of files. The only reason I’m storing Quicksave details into WordPress is for management purposes. Here’s a quicklook at what that interface looks like which is powered by the WordPress data.
Quicklook at Quicksaves
Creating the new database structure.
It’s no surprise that creating a custom post type and custom fields (via ACF) is way easier then creating a custom table. For most cases that’s all you need to do however for complex situations a custom table is the way to go. Below are the ACF custom fields which is a good start point for planning out the necessary database fields.
The following php function captaincore_create_tables() takes care of creating the new database table. This was adapted from Delicious Brains’ fantastic blog post: Creating A Custom Table With PHP In WordPress. Running captaincore_create_tables() creates the new custom table wp_captaincore_quicksaves and increments captcorecore_db_version which will prevent this block of code from running multiple times. Also provides a clean way to step through future database changes.
<?php
function captaincore_create_tables() {
global $wpdb;
$charset_collate = $wpdb->get_charset_collate();
$version = (int) get_site_option( 'captcorecore_db_version' );
if ( $version < 1 ) {
$sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_quicksaves` (
quicksave_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
site_id bigint(20) UNSIGNED NOT NULL,
created_at datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
git_status varchar(255),
git_commit varchar(100),
core varchar(10),
themes longtext,
plugins longtext,
PRIMARY KEY (quicksave_id)
) $charset_collate;";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta( $sql );
$success = empty( $wpdb->last_error );
update_site_option( 'captcorecore_db_version', 1 );
}
return $success;
}Building a custom database API to make the custom table useable.
To actually use that custom database table in any meaningful way, a custom database API is needed. Pippin Williamson has a fantastic advanced tutorial, although I admit my object-oriented programming could use some studying up. I ended up creating a database class based on Brad Touesnard’s example which is a bit simpler to work with. If this is outside your comfort zone, then you’ll really enjoy the Object-Oriented Bootcamp PHP course by Laracasts which I’m currently going through.
<?php
namespace CaptainCore;
class DB {
private static function _table() {
global $wpdb;
$tablename = str_replace( '\\', '_', strtolower( get_called_class() ) );
return $wpdb->prefix . $tablename;
}
private static function _fetch_sql( $value ) {
global $wpdb;
$sql = sprintf( 'SELECT * FROM %s WHERE %s = %%s', self::_table(), static::$primary_key );
return $wpdb->prepare( $sql, $value );
}
static function valid_check( $data ) {
global $wpdb;
$sql_where = '';
$sql_where_count = count( $data );
$i = 1;
foreach ( $data as $key => $row ) {
if ( $i < $sql_where_count ) {
$sql_where .= "`$key` = '$row' and ";
} else {
$sql_where .= "`$key` = '$row'";
}
$i++;
}
$sql = 'SELECT * FROM ' . self::_table() . " WHERE $sql_where";
$results = $wpdb->get_results( $sql );
if ( count( $results ) != 0 ) {
return false;
} else {
return true;
}
}
static function get( $value ) {
global $wpdb;
return $wpdb->get_row( self::_fetch_sql( $value ) );
}
static function insert( $data ) {
global $wpdb;
$wpdb->insert( self::_table(), $data );
}
static function update( $data, $where ) {
global $wpdb;
$wpdb->update( self::_table(), $data, $where );
}
static function delete( $value ) {
global $wpdb;
$sql = sprintf( 'DELETE FROM %s WHERE %s = %%s', self::_table(), static::$primary_key );
return $wpdb->query( $wpdb->prepare( $sql, $value ) );
}
static function fetch( $value ) {
global $wpdb;
$value = intval( $value );
$sql = 'SELECT * FROM ' . self::_table() . " WHERE `site_id` = '$value' order by `created_at` DESC";
return $wpdb->get_results( $sql );
}
}
class quicksaves extends DB {
static $primary_key = 'quicksave_id';
}With the PHP namespace in place, below are a few examples of how these classes are used to display and loop through custom table records. Any place in code referring to the custom post type and custom fields will need updated to this new format. Luckily my codebase is fairly straightforward and only required a few small updates. In fact using the database API happens to be a lot cleaner then doing a custom WP_Query.
// Establishes new connection to database class
$db_quicksaves = new CaptainCore\quicksaves;
// Retrieves quicksaves for a site
$quicksaves = $db_quicksaves->fetch( $site_id );
// Loops through each quicksave
foreach ( $quicksaves as $quicksave ) {
// Formats mysql timestamp format from "2018-06-20 09:15:20" to "Jun 20th 2018 9:15am"
$date = new DateTime( $quicksave->created_at );
$timestamp = $date->format('M jS Y g:ia');
$plugins = json_decode( $quicksave->plugins );
$themes = json_decode( $quicksave->themes );
$core = $quicksave->core;
$git_status = $quicksave->git_status;
$git_commit = $quicksave->git_commit;
// do something here
}
// Imports a new quicksave
// Assumes a bunch of JSON data was posted to the variable $data from an external bash script.
foreach ( $data as $row ) {
// Format for mysql timestamp format. Changes "1530817828" to "2018-06-20 09:15:20"
$epoch = $row->date;
$dt = new DateTime( "@$epoch" ); // convert UNIX timestamp to PHP DateTime
$date_formatted = $dt->format( 'Y-m-d H:i:s' ); // output = 2017-01-01 00:00:00
$themes = json_encode( $row->themes );
$plugins = json_encode( $row->plugins );
$new_quicksave = array(
'site_id' => $site_id,
'created_at' => $date_formatted,
'git_status' => $row->git_status,
'git_commit' => $row->git_commit,
'core' => $row->core,
'themes' => $themes,
'plugins' => $plugins,
);
$new_quicksave_check = array(
'site_id' => $site_id,
'created_at' => $date_formatted,
);
$valid_check = $db_quicksaves->valid_check( $new_quicksave_check );
// Add new quicksave if not added.
if ( $valid_check ) {
$db_quicksaves->insert( $new_quicksave );
}
}Testing out the new tables locally before production.
The next step is to load up the table with some real test data. Since all of my Quicksaves are within git, some custom bash scripts were required to run these imports into WordPress. That’s a bit outside the scope of this post. I might dig into that later in a future post. After I was happy with what I was seeing in my local development, I proceeded with creating the new database table on production and ran an overnight bash script to fill up the new database table.
Next purge unnecessary data from wp_posts and wp_postmeta.
After the new tables were fully populated with the same info and everything was confirmed to be working, I proceeded to purge out the old database info. The following two SQL statements ran within PHPMyAdmin purged out the old custom post type and custom fields data from wp_posts and wp_postmeta.
DELETE FROM wp_posts WHERE post_type='captcore_quicksave';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT id FROM wp_posts);Last I removed the custom post type named captcore_quicksave from the project PHP files as well as deleted the Advanced Custom Field group for the Quicksave custom fields. Feels great to have a clean set of data. The removal of 20,000 records from wp_posts and 340,000~ records from wp_postmeta should help keep my regular WordPress database queries running nice and fast.
Database usage before and after comparisons.
Between wp_postmeta and wp_posts tables the database usage dropped a total of 159MB 🏆. That’s fairly significant considering the new table wp_captaincore_quicksaves only added 99MB. There are a couple of factors that account for the difference. For one my custom table is very clean without all of the extra WordPress specific info. Also the purge from postmeta table likely cleared out other orphaned records not related to this specific feature. All in all I’ve very happy with the switchover.
