wp-tables-schema

6. Advanced Topics

Congratulations on making it this far! You are now equipped with all the core skills for creating, managing, and relating database tables. This final guide covers specialized features for handling unique requirements like multisite networks, conditional logic, and robust error logging.

1. Fine-Tuning Table Creation Options

While the library provides sensible defaults, you can fine-tune the table-level options directly within the schema builder.

Method Description Example SQL
->engine($name) Sets the database storage engine. InnoDB is highly recommended for foreign key support. ENGINE=InnoDB
->charset($name) Sets the default character set for the table. DEFAULT CHARACTER SET=utf8mb4
->collation($name) Sets the default collation for character sorting and comparison. COLLATE=utf8mb4_unicode_ci
->comment($text) Adds a descriptive comment to the table itself in the database schema. COMMENT='Stores all user-generated tasks.'
->if_not_exists() Adds the IF NOT EXISTS clause to the CREATE TABLE statement. This prevents an error if the table already exists, though the library’s versioning system already handles this safely. CREATE TABLE IF NOT EXISTS...

Example Usage:

protected function migrate_to_10001(): bool
{
    return $this->create_table(function (Create_Table_Schema $schema) {
        // Apply table-level options at the top of the closure.
        $schema->engine('InnoDB');
        $schema->comment('Stores all user-generated tasks and their status.');

        // Define columns as usual.
        $schema->id();
        $schema->string('title');
        
        return $schema;
    });
}

2. Multisite Installations

By default, every table you create is site-specific. In a multisite network, this means each site will get its own version of the table (e.g., wp_2_tasks, wp_3_tasks).

However, sometimes you need a single table that is shared across all sites in the network. You can achieve this by setting one property in your Table class.

Example: Let’s create a global_logs table that is shared across the entire network.

<?php
// In Global_Logs_Table.php

class Global_Logs_Table extends Table
{
    // ... other properties
    protected string $table_name = 'global_logs';
    protected string $plugin_prefix = 'my_plugin_';
    
    /**
     * By setting this to true, the table will be created using the base prefix
     * (e.g., `wp_my_plugin_global_logs`) and will be accessible from all sites.
     * @var bool
     */
    protected bool $multisite_shared = true;
    
    // ... migration methods
}

3. Conditional Migrations

Your plugin may be installed on a wide variety of server environments. Sometimes, you might want to run a migration that uses a feature only available in a newer version of MySQL or MariaDB. The library includes helper methods to check the database version safely.

Example: Imagine a new, faster index type becomes available in MariaDB 10.6. You want to upgrade an index for users on that version, but not break sites on older versions.

protected function migrate_to_10005(): bool
{
    // Check if the site is running a compatible MariaDB version.
    if ($this->is_mariadb_at_least('10.6')) {
    
        // Only run this advanced logic for compatible sites.
        $this->drop_index('idx_tasks_title');
        
        // This is a hypothetical example of a version-specific feature.
        $this->wpdb->query(
            "ALTER TABLE {$this->get_table_name()} ADD INDEX idx_tasks_title (title) COMMENT 'Using new algorithm'"
        );
    }
    
    // IMPORTANT: Always return true so the version number is updated for everyone,
    // even if the conditional logic didn't run.
    return true;
}

4. Logging for Effective Debugging

Database migrations can sometimes fail, especially on live servers with unexpected configurations. Debugging these issues can be difficult. To solve this, the library supports the PSR-3 Logger Interface, a popular standard for logging in PHP.

By providing a logger, you can automatically capture any errors that happen during the install() process to a log file, complete with detailed messages and stack traces.

Step 1: Install a Logger

First, add a PSR-3 compliant logger to your project. Monolog is the most popular and recommended choice.

composer require monolog/monolog

Step 2: Create and Inject the Logger

In your main installation function, create an instance of your logger and pass it to your Table object using the setLogger() method.

my-plugin.php

<?php
use Your_Plugin\Database\Tables\Tasks_Table;
use Monolog\Logger;
use Monolog\Handler\StreamHandler;
use Monolog\Level;

function my_plugin_install() {
    global $wpdb;

    // --- Logger Setup ---
    // 1. Create a new logger instance. 'db-migrations' is the channel name.
    $log = new Logger('db-migrations');
    
    // 2. Create a handler to write logs to a file in your plugin's directory.
    //    We'll only log messages that are WARNING level or higher.
    $log_file = __DIR__ . '/migrations.log';
    $log->pushHandler(new StreamHandler($log_file, Level::Warning));
    // --------------------
    
    // Instantiate your table.
    $tasks_table = new Tasks_Table($wpdb);

    // 3. Inject the logger into the table object.
    $tasks_table->setLogger($log);
    
    // Now, run the installation.
    $tasks_table->install(); 
}

register_activation_hook(__FILE__, 'my_plugin_install');

Now, if any Tasks_Table migration fails due to a SQL error or any other exception, a migrations.log file will appear in your plugin’s root directory. It will contain a detailed error message, making it incredibly easy to diagnose and fix the problem.

You have now completed the full documentation for WP Tables Schema. You have the knowledge to build simple tables, evolve them with complex migrations, connect them with relationships, and manage advanced, real-world scenarios with confidence. Happy coding

This site is open source. Improve this page.