Live DB Schema Drift Detector

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.
 */

/**
 * Live DB Schema Drift Detector.
 *
 * Purpose:
 * Compares an expected column map against the current table definition.
 *
 * @param mysqli $database Active procedural mysqli connection resource object.
 * @param string $table_name Table to inspect.
 * @param array $expected_columns Expected columns keyed by column name.
 * @return array Drift report listing missing, extra, and changed columns.
 */
function ogSnippetLiveDbSchemaDriftDetector(mysqli $database, string $table_name, array $expected_columns): array {
	$table_name = trim($table_name);

	if (preg_match('/^[a-zA-Z0-9_]+$/', $table_name) !== 1) {
		return array(
			'ok' => false,
			'message' => 'Table name is not an approved identifier.',
			'missing' => array(),
			'extra' => array(),
			'changed' => array()
		);
	}

	$live_columns = array();
	$describe_result = mysqli_query($database, 'DESCRIBE `'.$table_name.'`');

	if ($describe_result === false) {
		return array(
			'ok' => false,
			'message' => 'Table could not be described.',
			'missing' => array(),
			'extra' => array(),
			'changed' => array()
		);
	}

	while ($column = mysqli_fetch_assoc($describe_result)) {
		$column_name = (string) $column['Field'];
		$live_columns[$column_name] = array(
			'type' => strtolower((string) $column['Type']),
			'nullable' => strtoupper((string) $column['Null']),
			'key' => strtoupper((string) $column['Key'])
		);
	}

	mysqli_free_result($describe_result);

	$missing = array();
	$extra = array();
	$changed = array();

	foreach ($expected_columns as $column_name => $expected) {
		if (isset($live_columns[$column_name]) === false) {
			$missing[] = $column_name;
			continue;
		}

		$expected_type = '';
		$expected_nullable = '';
		$expected_key = '';

		if (isset($expected['type']) === true) {
			$expected_type = strtolower((string) $expected['type']);
		}

		if (isset($expected['nullable']) === true) {
			$expected_nullable = strtoupper((string) $expected['nullable']);
		}

		if (isset($expected['key']) === true) {
			$expected_key = strtoupper((string) $expected['key']);
		}

		$column_changes = array();

		if ($expected_type !== '' && $expected_type !== $live_columns[$column_name]['type']) {
			$column_changes['type'] = array(
				'expected' => $expected_type,
				'live' => $live_columns[$column_name]['type']
			);
		}

		if ($expected_nullable !== '' && $expected_nullable !== $live_columns[$column_name]['nullable']) {
			$column_changes['nullable'] = array(
				'expected' => $expected_nullable,
				'live' => $live_columns[$column_name]['nullable']
			);
		}

		if ($expected_key !== '' && $expected_key !== $live_columns[$column_name]['key']) {
			$column_changes['key'] = array(
				'expected' => $expected_key,
				'live' => $live_columns[$column_name]['key']
			);
		}

		if (count($column_changes) > 0) {
			$changed[$column_name] = $column_changes;
		}
	}

	foreach ($live_columns as $column_name => $live_column) {
		if (isset($expected_columns[$column_name]) === false) {
			$extra[] = $column_name;
		}
	}

	$is_clean = false;

	if (count($missing) === 0 && count($extra) === 0 && count($changed) === 0) {
		$is_clean = true;
	}

	return array(
		'ok' => $is_clean,
		'message' => 'Schema drift check completed.',
		'missing' => $missing,
		'extra' => $extra,
		'changed' => $changed
	);
}

if (isset($database_link) === true && $database_link instanceof mysqli) {
	$expected_items = array(
		'id' => array('type' => 'int', 'nullable' => 'NO', 'key' => 'PRI'),
		'slug' => array('type' => 'varchar(190)', 'nullable' => 'NO', 'key' => 'UNI')
	);

	$drift_report = ogSnippetLiveDbSchemaDriftDetector($database_link, 'items', $expected_items);
	echo 'Rocinante schema drift clean: '.(int) $drift_report['ok'];
}