WordPress Custom Table Manager

A PHP library using PSR-3 logging to manage the lifecycle (installation, updates, deletion) of custom WordPress database tables via configuration.

This library focuses on schema management (CREATE, ALTER, DROP) and does not handle data manipulation (INSERT, SELECT, UPDATE, DELETE).

Motivation

I wanted to have a simple schema-manager, where all table configuration (with updates) is visible in a single file. The only dependency is PSR3 logger and WordPress.

Installation

composer require dol-lab/custom-table-manager psr/log

(Requires a PSR-3 logger implementation like monolog/monolog if logging is desired)

Usage

1. Define Table Configurations

Create a configuration file (e.g., config/database-tables.php) that returns an array defining your tables.

Important: Your plugin code is responsible for determining the correct, fully prefixed table names (using $wpdb->prefix) and replacing any placeholders like {name} or {columns_create} in the updates SQL strings before passing the configuration to the SchemaManager.

<?php
/**
 * Database table definitions configuration.
 *
 * @package YourPlugin
 */

// Example of preparing names and update SQL *before* returning the array:
// global $wpdb;
// $logs_table_name = $wpdb->prefix . 'myplugin_logs';
// $items_table_name = $wpdb->prefix . 'myplugin_items';
//
// $log_update_sql_1_1_0 = "ALTER TABLE `{$logs_table_name}` ADD COLUMN `log_context` varchar(255) NULL AFTER `log_message`";
// $item_update_sql_1_3_0 = "ALTER TABLE `{$items_table_name}` ADD COLUMN `item_status` VARCHAR(20) NOT NULL DEFAULT 'active' AFTER `item_name`";

return array(
    // Example Log Table
    array(
        'name'    => 'my_logs',
        'columns' => array(
            'log_id'       => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'log_type'     => 'varchar(50) NOT NULL',
            'log_message'  => 'text',
            'log_time'     => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP',
        ),
        // The library replaces {name} and {columns_create} in this template
        'create'  => "CREATE TABLE {name} (
            {columns_create}, /* placeholder {columns_create} automatically generated from columns above. */
            PRIMARY KEY (log_id),
            KEY log_type (log_type)
        )",
        // Updates: Use actual SQL or Closures.
        'updates' => array(
            // Version where 'log_context' column was added
            '1.1.0' => 'create', // create is a special keyword. 
            // Version with multiple changes (array of SQL strings)
            '1.2.0' => array(
                "ALTER TABLE {name} MODIFY COLUMN `log_message` LONGTEXT",
                "ALTER TABLE {name} ADD INDEX `log_time_idx` (`log_time`)"
             ),
            // Example using a Closure (receives $wpdb, $table_name)
            '1.4.0' => function(\wpdb $wpdb, string $table_name) {
                // Perform complex update logic here
                // $wpdb->query(...);
            }
        ),
    ),

    // Example Items Table (Introduced in version 1.1.0)
     array(
        'name'    => 'my_items',
        'columns' => array(
            'item_id'      => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'item_name'    => 'varchar(255) NOT NULL',
            'item_status'    => 'varchar(255) NOT NULL',
            'date_added'   => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP',
        ),
        'create'  => "CREATE TABLE {name} (
            {columns_create},
            PRIMARY KEY (item_id),
            KEY item_name (item_name) --add index for better search performance.
        )",
        'updates' => array(
            // Special keyword: table was added in this version.
            '1.1.0' => 'create',
            // Version where 'item_status' column was added
            '1.3.0' => 'ALTER TABLE {name} ADD item_status varchar(255) NOT NULL',
        ),
    ),
    // ... add more table definitions
);

Configuration Array Keys:

• name: (string) The full WordPress table name, including the database prefix (e.g., $wpdb->prefix . 'my_table'). Required.
• columns: (array) Associative array ['column_name' => 'SQL Definition']. Required.
• create: (string|false) Template for the CREATE TABLE statement. Use {name} for the table name and {columns_create} for the column definitions. Set to false to prevent automatic creation. Required.
• updates: (array) Optional. Associative array ['plugin_version' => mixed].
  • Key: Plugin version string where the change was introduced.
  • Value:
    • 'create': Special string if the table was introduced in this version.
    • (string) A single SQL ALTER TABLE statement. You must replace any table name placeholders manually.
    • (array) An array of SQL ALTER TABLE strings. You must replace any table name placeholders manually.
    • (Closure) A function receiving (\wpdb $wpdb, string $table_name) for complex updates.

2. Configure Logging (Optional)

Provide a PSR-3 compatible logger instance (like Monolog or a custom one) to the SchemaManager. If omitted, a NullLogger is used (no logs).

<?php
// Example: Using NullLogger (no logging)
use Psr\Log\NullLogger;
$logger = new NullLogger();

// Example: Using Monolog (assuming installed and configured)
// use Monolog\Logger;
// use Monolog\Handler\StreamHandler;
// $logger = new Logger('myplugin_db');
// $logger->pushHandler(new StreamHandler(WP_CONTENT_DIR . '/logs/myplugin_db.log', Logger::WARNING));

// Pass $logger when creating the SchemaManager instance.
// $db_manager = new SchemaManager( $table_configs, $wpdb, $logger );

3. Integrate with Your Plugin

Instantiate the SchemaManager and use its methods within your plugin's activation, update, and deactivation/uninstall hooks, using try...catch blocks for error handling.

Example Integration:

<?php
/**
 * Plugin Name: My Custom Plugin
 * Description: Uses Custom Table Manager.
 * Version: 1.2.3
 */

use DolLab\CustomTableManager\SchemaManager;
use DolLab\CustomTableManager\TableOperationException;
use DolLab\CustomTableManager\TableConfigurationException;
use Psr\Log\LoggerInterface;
use Psr\Log\NullLogger; // Or your chosen logger

// Define constants for versions and options
define('MYPLUGIN_VERSION', '1.2.3'); // Your current plugin version
define('MYPLUGIN_DB_VERSION_OPTION', 'myplugin_db_version');

/**
 * Get the PSR-3 Logger instance.
 */
function myplugin_get_logger(): LoggerInterface {
    // Replace with your actual logger setup or NullLogger
    return new NullLogger();
}

/**
 * Plugin Activation Hook.
 */
function myplugin_activate() {
    global $wpdb;
    $logger = myplugin_get_logger();

    if (!class_exists(SchemaManager::class)) {
        $logger->critical('Custom Table Manager class not found. Run composer install.');
        // Consider wp_die or admin notice
        return;
    }

    try {
        $table_configs = require plugin_dir_path(__FILE__) . 'config/database-tables.php';
        if (empty($table_configs)) {
             $logger->error('Activation: No valid table configurations found after processing.');
             return; // Or handle error appropriately
        }

        $db_manager = new SchemaManager($table_configs, $wpdb, $logger);
        $db_manager->install(); // Returns void on success, throws TableOperationException on failure

        // If install() didn't throw, it succeeded.
        update_option(MYPLUGIN_DB_VERSION_OPTION, MYPLUGIN_VERSION);
        $logger->info('Activation: Database tables installed/verified successfully.');

    } catch (TableConfigurationException $e) {
        $logger->error('Activation Error: Invalid table configuration. ' . $e->getMessage());
        // Add admin notice
    } catch (TableOperationException $e) {
        $logger->error('Activation Error: Failed to install/verify tables. ' . $e->getMessage());
        // Add admin notice
    } catch (\Exception $e) {
        $logger->critical('Activation Error: An unexpected error occurred. ' . $e->getMessage());
        // Add admin notice
    }
}
register_activation_hook(__FILE__, 'myplugin_activate');

/**
 * Check for database updates (e.g., on 'plugins_loaded').
 */
function myplugin_check_for_updates() {
    global $wpdb;
    $logger = myplugin_get_logger();
    $installed_version = get_option(MYPLUGIN_DB_VERSION_OPTION, '0.0.0');

    if (version_compare($installed_version, MYPLUGIN_VERSION, '<')) {
        $logger->info("DB Update Check: Updating from v{$installed_version} to v" . MYPLUGIN_VERSION);

        if (!class_exists(SchemaManager::class)) {
            $logger->error('Update Check: Custom Table Manager class not found.');
            return; // Add admin notice.
        }

        try {
            $table_configs = myplugin_get_processed_table_configs();
             if (empty($table_configs)) {
                 $logger->error('Update Check: No valid table configurations found after processing.');
                 return; // Or handle error
            }

            $db_manager = new SchemaManager($table_configs, $wpdb, $logger);
            // update_table_version returns void on success, throws TableOperationException on failure.
            $db_manager->update_table_version($installed_version, MYPLUGIN_VERSION);

            // If update_table_version() didn't throw, it succeeded.
            update_option(MYPLUGIN_DB_VERSION_OPTION, MYPLUGIN_VERSION);
            $logger->info("Update Check: Database update process completed successfully to v" . MYPLUGIN_VERSION);

        } catch (\Exception $e) {
            $logger->critical('Update Check Error: ' . $e->getMessage());
            // Add admin notice.
        }
    }
}
add_action('plugins_loaded', 'myplugin_check_for_updates');

/**
 * Plugin Uninstall Hook (Optional).
 */
function myplugin_uninstall() {
    global $wpdb;
    // Option to preserve data
    // if (get_option('myplugin_preserve_data_on_uninstall')) { return; }

    $logger = myplugin_get_logger();
    if (!class_exists(SchemaManager::class)) {
        $logger->warning('Uninstall: Custom Table Manager class not found. Tables may not be dropped.');
        // Don't necessarily return, still try to delete options.
    } else {
         try {
             $table_configs = myplugin_get_processed_table_configs();
             // Pass configs even if empty, uninstall should try based on names if possible.
             $db_manager = new SchemaManager($table_configs, $wpdb, $logger);
             $db_manager->uninstall(); // Returns void on success, throws TableOperationException on failure.
             $logger->info('Uninstall: Table drop process completed successfully (or tables did not exist).');

         } catch (\Exception $e) {
             $logger->critical('Uninstall Error: ' . $e->getMessage());
         }
    }

    // Delete options regardless of table drop success/failure
    // delete_option(MYPLUGIN_DB_VERSION_OPTION);
    // Delete other plugin options...
}
// register_uninstall_hook(__FILE__, 'myplugin_uninstall'); // Uncomment if needed

Manager Methods

• install(): void: Creates tables defined in the configuration if they don't exist. Throws TableOperationException on failure.
• update_table_version(string $old_plugin_version, string $new_plugin_version): void: Applies updates from the configuration based on version changes. Throws TableOperationException on failure.
• uninstall(): void: Drops all tables defined in the configuration. Throws TableOperationException on failure.

Use try...catch blocks to handle potential TableConfigurationException (during instantiation) and TableOperationException (during operations).

Important Notes

• Error Handling: Use try...catch blocks. Check logs via the injected PSR-3 logger for details.
• Direct Queries: This library uses direct CREATE TABLE and ALTER TABLE queries, not WordPress's dbDelta. Ensure your SQL syntax is correct for your target MySQL/MariaDB versions.
• Versioning: Use semantic versioning. Store the installed database schema version (usually the plugin version when the schema was last successfully updated) in wp_options to manage updates correctly.