Schema Drift Checker

Code

<?php

/*
 * Copyright (c) 2026 Jeffery L. Paris <jparis@phpog.com>.
 * Free for personal and internal use. Paid project use requires visible credit
 * to Jeffery L. Paris. Corporate use requires a paid license fee unless a
 * separate written license states otherwise.
 */

/**
 * Compares expected table/column/index definitions against actual schema data.
 *
 * The caller supplies expected and actual schema arrays. This function only reports
 * drift and does not execute database changes.
 *
 * @param array $expected_schema Expected schema map.
 * @param array $actual_schema Actual schema map.
 * @return array Schema drift report.
 */
function ogCheckSchemaDrift($expected_schema = array(), $actual_schema = array()) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

	if (!is_array($expected_schema) || empty($expected_schema)) {
		$result['message'] = 'Expected schema is required.';
		return $result;
	}

	if (!is_array($actual_schema)) {
		$result['message'] = 'Actual schema must be an array.';
		return $result;
	}

	$missing_tables = array();
	$extra_tables = array();
	$column_drift = array();
	$index_drift = array();

	foreach ($expected_schema as $table => $expected_table) {
		$table = (string)$table;
		if (!array_key_exists($table, $actual_schema)) {
			$missing_tables[] = $table;
			continue;
		}

		$expected_columns = array();
		if (!empty($expected_table['columns']) && is_array($expected_table['columns'])) {
			$expected_columns = $expected_table['columns'];
		}
		$actual_columns = array();
		if (!empty($actual_schema[$table]['columns']) && is_array($actual_schema[$table]['columns'])) {
			$actual_columns = $actual_schema[$table]['columns'];
		}
		foreach ($expected_columns as $column => $definition) {
			if (!array_key_exists($column, $actual_columns)) {
				$column_drift[] = array('table' => $table, 'column' => $column, 'issue' => 'missing');
			} elseif ((string)$definition != (string)$actual_columns[$column]) {
				$column_drift[] = array('table' => $table, 'column' => $column, 'issue' => 'definition_mismatch');
			}
		}

		$expected_indexes = array();
		if (!empty($expected_table['indexes']) && is_array($expected_table['indexes'])) {
			$expected_indexes = $expected_table['indexes'];
		}
		$actual_indexes = array();
		if (!empty($actual_schema[$table]['indexes']) && is_array($actual_schema[$table]['indexes'])) {
			$actual_indexes = $actual_schema[$table]['indexes'];
		}
		foreach ($expected_indexes as $index_name => $definition) {
			if (!array_key_exists($index_name, $actual_indexes)) {
				$index_drift[] = array('table' => $table, 'index' => $index_name, 'issue' => 'missing');
			}
		}
	}

	foreach ($actual_schema as $table => $actual_table) {
		if (!array_key_exists($table, $expected_schema)) {
			$extra_tables[] = (string)$table;
		}
	}

	$result['success'] = true;
	$result['message'] = 'Schema drift check completed.';
	$result['data'] = array(
		'missing_tables' => $missing_tables,
		'extra_tables' => $extra_tables,
		'column_drift' => $column_drift,
		'index_drift' => $index_drift,
		'has_drift' => !empty($missing_tables) || !empty($extra_tables) || !empty($column_drift) || !empty($index_drift)
	);

	return $result;
}