2meet-data-optimizer/tests/integration/GroupCompositeIndexTest.php

<?php
declare(strict_types=1);

use PHPUnit\Framework\TestCase;

/**
 * Composite indexes declared on a group reach its existing flat table.
 *
 * The table already exists with data (its schema hash is unchanged), so the
 * index is added in place rather than through a table rebuild.
 */
class GroupCompositeIndexTest extends TestCase {

	private const TABLE = 'wp_itest_wpdo_post_ci_core';

	public static function setUpBeforeClass(): void {
		$base = WPDO_PLUGIN_DIR;
		foreach ( array(
			'includes/adapters/interface-entity-adapter.php',
			'includes/engine/class-tmdo-schema-manager.php',
			'includes/engine/class-tmdo-entity-registry.php',
			'includes/adapters/class-tmdo-adapter-post.php',
		) as $file ) {
			require_once $base . $file;
		}
	}

	protected function setUp(): void {
		global $wpdb;
		$wpdb->query( 'DROP TABLE IF EXISTS `' . self::TABLE . '`' );
		$wpdb->query( 'CREATE TABLE `' . self::TABLE . '` ( id BIGINT NOT NULL AUTO_INCREMENT, post_id BIGINT NOT NULL, ci_flag BIGINT(20) DEFAULT NULL, ci_price DECIMAL(18,6) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_entity (post_id) ) ENGINE=InnoDB' );
		$wpdb->query( 'INSERT INTO `' . self::TABLE . '` (post_id, ci_flag, ci_price) VALUES (1, 1, 10), (2, 0, 20)' );
		$GLOBALS['_wp_options'] = array();
		// Index DDL runs from WP-CLI / WP-Cron only (2.3 PR-090a); these tests act as one.
		$GLOBALS['_wp_filter_callbacks']['tmdo_schema_ddl_allowed'] = array();
		add_filter( 'tmdo_schema_ddl_allowed', static fn() => true );
		$GLOBALS['_wp_scheduled_single'] = array();

		$ref = new ReflectionProperty( WPDO_Schema_Manager::class, 'table_exists_cache' );
		$ref->setAccessible( true );
		$ref->setValue( null, array() );

		WPDO_Entity_Registry::init();
		WPDO_Entity_Registry::register_adapter( 'post', new WPDO_Adapter_Post() );
		WPDO_Entity_Registry::register_group(
			'post',
			'ci_core',
			array(
				array( 'key' => 'ci_flag', 'type' => 'integer' ),
				array( 'key' => 'ci_price', 'type' => 'decimal' ),
			),
			array( 'indexes' => array( 'flag_price' => array( 'ci_flag', 'ci_price' ) ) )
		);
	}

	protected function tearDown(): void {
		global $wpdb;
		$wpdb->query( 'DROP TABLE IF EXISTS `' . self::TABLE . '`' );
		WPDO_Entity_Registry::init();
		$GLOBALS['_wp_filter_callbacks']['tmdo_schema_ddl_allowed'] = array();
		unset( $GLOBALS['_wp_is_admin'], $GLOBALS['_wp_scheduled_single'] );
	}

	public function test_a_web_request_adds_no_index_and_leaves_it_to_wp_cron(): void {
		$GLOBALS['_wp_filter_callbacks']['tmdo_schema_ddl_allowed'] = array();
		foreach ( array( false, true ) as $is_admin ) { // /wp-admin/ runs init before auth_redirect().
			$GLOBALS['_wp_is_admin'] = $is_admin;
			$this->assertFalse( TMDO_Schema_Manager::ddl_allowed() );

			TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

			$this->assertArrayNotHasKey( 'cidx_flag_price', $this->indexes(), 'no ALTER in a web request' );
		}
		$this->assertSame( array( 'post/ci_core' ), TMDO_Schema_Manager::pending_indexes() );
		$this->assertContains( TMDO_Schema_Manager::INDEXES_HOOK, $GLOBALS['_wp_scheduled_single'] );

		add_filter( 'tmdo_schema_ddl_allowed', static fn() => true ); // The cron run.
		TMDO_Schema_Manager::ensure_pending_indexes();

		$this->assertArrayHasKey( 'cidx_flag_price', $this->indexes() );
		$this->assertSame( array(), TMDO_Schema_Manager::pending_indexes() );
	}

	public function test_the_ddl_decision_can_be_overridden(): void {
		add_filter( 'tmdo_schema_ddl_allowed', static fn() => false );

		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

		$this->assertArrayNotHasKey( 'cidx_flag_price', $this->indexes() );
	}

	public function test_a_failed_alter_is_not_recorded_as_applied(): void {
		global $wpdb;
		$wpdb->query( 'ALTER TABLE `' . self::TABLE . '` ADD COLUMN ci_note TEXT DEFAULT NULL' );
		WPDO_Entity_Registry::init();
		WPDO_Entity_Registry::register_adapter( 'post', new WPDO_Adapter_Post() );
		WPDO_Entity_Registry::register_group(
			'post',
			'ci_core',
			array(
				array( 'key' => 'ci_flag', 'type' => 'integer' ),
				array( 'key' => 'ci_note', 'type' => 'textarea' ),
			),
			array( 'indexes' => array( 'flag_note' => array( 'ci_flag', 'ci_note' ) ) ) // TEXT without a prefix length: MySQL 1170.
		);

		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

		$this->assertArrayNotHasKey( 'cidx_flag_note', $this->indexes() );
		$this->assertFalse( get_option( TMDO_Schema_Manager::INDEXES_OPTION ), 'not recorded, so it is retried' );
		$this->assertSame( array( 'post/ci_core' ), TMDO_Schema_Manager::pending_indexes() );
	}

	public function test_a_lock_held_by_another_process_skips_the_ddl_until_it_is_released(): void {
		$other = new mysqli(
			getenv( 'TMDO_TEST_DB_HOST' ) ?: '127.0.0.1',
			getenv( 'TMDO_TEST_DB_USER' ) ?: 'dbo',
			(string) getenv( 'TMDO_TEST_DB_PASS' ),
			getenv( 'TMDO_TEST_DB_NAME' ) ?: 'wp_wpdo_test'
		);
		$name = $other->real_escape_string( TMDO_Schema_Manager::index_lock_name() );
		$this->assertSame( '1', (string) $other->query( "SELECT GET_LOCK(MD5(CONCAT(DATABASE(), '{$name}')), 0)" )->fetch_row()[0] );

		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );
		$this->assertArrayNotHasKey( 'cidx_flag_price', $this->indexes(), 'another process holds the lock' );

		$other->query( "SELECT RELEASE_LOCK(MD5(CONCAT(DATABASE(), '{$name}')))" );
		$other->close();
		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

		$this->assertArrayHasKey( 'cidx_flag_price', $this->indexes() );
		$this->assertSame( array(), TMDO_Schema_Manager::pending_indexes() );
		global $wpdb;
		$this->assertSame( '1', (string) $wpdb->get_var( $wpdb->prepare( 'SELECT IS_FREE_LOCK(MD5(CONCAT(DATABASE(), %s)))', TMDO_Schema_Manager::index_lock_name() ) ), 'lock released' );
	}

	/**
	 * Under read/write splitting (HyperDB, LudicrousDB) a SELECT may go to a
	 * replica, where GET_LOCK would lock nothing: reads go to the primary first.
	 */
	public function test_the_lock_is_taken_on_the_primary_under_read_write_splitting(): void {
		global $wpdb;
		$real = $wpdb;
		$wpdb = new class( $real ) {
			/** @var string[] */
			public array $calls = array();

			public function __construct( private object $db ) {}

			public function send_reads_to_masters(): void {
				$this->calls[] = 'send_reads_to_masters';
			}

			public function __call( string $name, array $args ) {
				if ( 'get_var' === $name && str_contains( (string) $args[0], 'GET_LOCK' ) ) {
					$this->calls[] = 'GET_LOCK';
				}
				return $this->db->$name( ...$args );
			}

			public function __get( string $name ) {
				return $this->db->$name;
			}
		};
		try {
			TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );
			$calls = $wpdb->calls;
		} finally {
			$wpdb = $real;
		}

		$this->assertSame( array( 'send_reads_to_masters', 'GET_LOCK' ), $calls );
		$this->assertArrayHasKey( 'cidx_flag_price', $this->indexes() );
	}

	/** @return array<string, string[]> index name → ordered columns */
	private function indexes(): array {
		global $wpdb;
		$out = array();
		foreach ( (array) $wpdb->get_results( 'SHOW INDEX FROM `' . self::TABLE . '`', ARRAY_A ) as $row ) {
			$out[ $row['Key_name'] ][ (int) $row['Seq_in_index'] - 1 ] = $row['Column_name'];
		}
		return $out;
	}

	public function test_missing_composite_index_is_added_to_the_existing_table(): void {
		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

		$this->assertSame( array( 'ci_flag', 'ci_price' ), $this->indexes()['cidx_flag_price'] ?? null );
		global $wpdb;
		$this->assertSame( '2', $wpdb->get_var( 'SELECT COUNT(*) FROM `' . self::TABLE . '`' ), 'data untouched' );
	}

	public function test_applied_definition_is_remembered_so_later_requests_skip_the_check(): void {
		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );
		global $wpdb;
		$wpdb->query( 'ALTER TABLE `' . self::TABLE . '` DROP INDEX `cidx_flag_price`' );

		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

		$this->assertArrayNotHasKey( 'cidx_flag_price', $this->indexes(), 'same definition → no SHOW INDEX / ALTER' );
		$this->assertArrayHasKey( 'post/ci_core', get_option( TMDO_Schema_Manager::INDEXES_OPTION ) );
	}

	public function test_group_without_indexes_is_left_alone(): void {
		WPDO_Entity_Registry::init();
		WPDO_Entity_Registry::register_adapter( 'post', new WPDO_Adapter_Post() );
		WPDO_Entity_Registry::register_group( 'post', 'ci_core', array( array( 'key' => 'ci_flag', 'type' => 'integer' ) ) );

		TMDO_Schema_Manager::ensure_group_indexes( 'post', 'ci_core' );

		$this->assertSame( array( 'PRIMARY', 'uk_entity' ), array_keys( $this->indexes() ) );
		$this->assertFalse( get_option( TMDO_Schema_Manager::INDEXES_OPTION ) );
	}
}