class.custom-tables.php

<?php
/**
 * A class to create custom tables within WordPress.
 *
 * @version 1.0.0
 */

namespace apermo\WPTools;

use Exception;

class Custom_Tables {
	/**
	 * The option key to store the version.
	 *
	 * @since 1.0.0
	 *
	 * @var string
	 */
	private string $version_key;

	/**
	 * The version to update to.
	 *
	 * @since 1.0.0
	 *
	 * @var int
	 */
	private int $version;

	/**
	 * The debug mode flag.
	 *
	 * @since 1.0.0
	 *
	 * @var bool
	 */
	private bool $debug = false;

	/**
	 * The tables to create.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	private array $tables = [];

	/**
	 * The debug messages.
	 *
	 * @since 1.0.0
	 *
	 * @var array
	 */
	private array $debug_messages = [];

	/**
	 * Constructor.
	 *
	 * @since 1.0.0
	 *
	 * @param string $version_key The option key to store the version.
	 * @param int    $version     The version to update to.
	 * @param bool   $debug       The debug mode flag. Default false.
	 */
	public function __construct( string $version_key, int $version, bool $debug = false ) {
		$this->version_key = $version_key;
		$this->version     = $version;
		$this->debug       = $debug;

		add_action( 'apermo_custom_tables_update_' . $version_key, );
	}

	/**
	 * Adds a table.
	 *
	 * The table name will be prefixed with the WordPress table prefix.
	 *
	 * Example:
	 *
	 * CREATE TABLE %1$s (
	 * id mediumint(8) unsigned NOT NULL auto_increment,
	 * post_id bigint(20) unsigned NOT NULL,
	 * some_text varchar(255) NULL,
	 * PRIMARY KEY (id)
	 * )
	 * %2$s
	 *
	 * Mind that new lines are important for the proper use with dbDelta()
	 *
	 * @since 1.0.0
	 *
	 * @param string $table_name The table name without prefix.
	 * @param string $create_sql The SQL to create the table. Use %1$s for the table name and optionally %2$s for the SQL collation.
	 *
	 * @throws Exception
	 */
	public function add( string $table_name, string $create_sql ) {
		if ( $table_name !== sanitize_key( $table_name ) ) {
			throw new Exception( 'Invalid table name' );
		}

		global $wpdb;
		$wpdb->$table_name = $wpdb->prefix . $table_name;
		$wpdb->tables[] = $table_name;

		if ( str_contains( $create_sql, '%s' ) === false ) {
			throw new Exception( 'Invalid SQL, add %1$s for the table name and optionally %2$s for the SQL collation' );
		}

		$create_sql = sprintf( $create_sql, $wpdb->prefix . $table_name, 'COLLATE ' . $wpdb->collate );

		$this->tables[ $table_name ] = $create_sql;
	}

	/**
	 * Creates and updates the tables.
	 *
	 * @since 1.0.0
	 *
	 * @param bool $force Whether to force the update. Default false.
	 */
	public function create_and_update_tables( bool $force = false ): void {
		$current_version = get_option( $this->version_key, 0 );

		$this->debug_messages[] = 'Creating tables...';
		$this->debug_messages[] = 'Current version: ' . $current_version;
		$this->debug_messages[] = 'Target version: ' . $this->version;

		if ( $current_version >= $this->version && $force !== true ) {
			$this->debug_messages[] = 'Tables up to date...';
			$this->store_debug_messages();
			return;
		}

		$this->debug_messages[] = 'Creating tables...';

		if ( ! function_exists( 'dbDelta' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}

		foreach ( $this->tables as $table_name => $create_sql ) {
			$this->debug_messages[] = 'Creating table ' . $table_name . '...';
			array_push( $this->debug_messages, ...dbDelta( $create_sql ) );
			$this->debug_messages[] = 'Table ' . $table_name . ' created.';
		}

		update_option( $this->version_key, $this->version, false );

		$this->store_debug_messages();
	}

	/**
	 * Stores the debug messages.
	 *
	 * @since 1.0.0
	 */
	private function store_debug_messages(): void {
		if ( $this->debug === true ) {
			update_option( $this->version_key . '_debug_' . date( 'Ymd-His' ), $this->debug_messages, false );
		}
	}

	/**
	 * Clears all previously stored debug messages.
	 *
	 * @since 1.0.0
	 *
	 * @throws Exception If something went wrong while deleting debug messages.
	 * @return int The number of deleted rows.
	 */
	public function clear_debug_messages(): int {
		global $wpdb;
		$num_rows = $wpdb->delete( $wpdb->options, [ 'option_name' => $this->version_key . '_debug_%' ] );
		if ( $num_rows === false ) {
			throw new Exception( 'Something went wrong while deleting debug messages.' );
		}

		return $num_rows;
	}

	/**
	 * Drops a table.
	 *
	 * @since 1.0.0
	 *
	 * @param string $table_name The table name without prefix.
	 *
	 * @throws Exception If something went wrong while dropping the table.
	 * @return bool If the table was dropped successfully.
	 */
	public function drop_table( string $table_name ): bool {
		global $wpdb;
		$result = $wpdb->query( 'DROP TABLE IF EXISTS ' . $wpdb->prefix . $table_name );

		if ( $result === false ) {
			throw new Exception( 'Something went wrong while dropping table ' . $table_name . '.' );
		}

		return (bool) $wpdb->rows_affected;
	}
}