Custom Tables

Context

The custom tables into WordPress are handled into the $wpdb global object.

Two types of tables

When deadling with custom tables, there is two approaches :

• Global multisite table
• Site by site table, created on activation

Site by site table

To make your custom tables compatible with WordPress (switch to blog for example) you need to do this in the main plugin file :

// Plugin tables
global $wpdb;
$wpdb->tables[]     = 'sample_table';
$wpdb->sample_table = $wpdb->prefix . 'sample_table';

Where sample_table is the real name of your table. This is important because WordPress will search/replace this name in the case of switch_to_blog.

Global mulsite table

To make your custom tables compatible with WordPress on mulsite you need to do :

// Plugin tables
global $wpdb;
$wpdb->ms_global_tables[]     = 'sample_table';
$wpdb->sample_table = $wpdb->prefix . 'sample_table';

Where sample_table is the real name of your table. This will not replace the table name on a switch_to_blog call.

On activation

When activating the plugin, the method BEA\PB\Plugin::activation is called, this is the right place to create your tables. This can be achieved like this :

public static function activate() {
	global $wpdb;

	if ( ! empty( $wpdb->charset ) ) {
		$charset_collate = "DEFAULT CHARACTER SET $wpdb->charset";
	}
	if ( ! empty( $wpdb->collate ) ) {
		$charset_collate .= " COLLATE $wpdb->collate";
	}

	// Add one library admin function for next function
	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );

	// Data table
	maybe_create_table( $wpdb->sample_table, "CREATE TABLE IF NOT EXISTS `{$wpdb->sample_table}` (
		`id` bigint(20) NOT NULL AUTO_INCREMENT,
		`post_id` bigint(20) NOT NULL,
		PRIMARY KEY (`id`)
	) $charset_collate AUTO_INCREMENT=1;" );
}

Database schema changes

Database schema changes are rare but can exists, you can handle it like WordPress.

• Store a version id into the options
• Store a version id into one class constant
• Compare the current code and the option one
• If different launch the queries to edit the database

Examples

• https://github.com/stuttter/wp-blog-meta/blob/master/wp-blog-meta/includes/classes/class-wp-blog-meta-db-table.php
• https://github.com/BeAPI/bea-sender/blob/master/inc/class.client.php => use WordPress functions to alter the schema