Auto Increment Auditor

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

/**
 * Audits whether AUTO_INCREMENT values are aligned after an import or restore.
 *
 * Each table row should include name, max_id, and auto_increment values.
 *
 * @param array $tables Auto-increment metadata rows.
 * @return array Auto-increment audit report.
 */
function ogAuditAutoIncrementState($tables = array()) {
	$result = array(
		'success' => false,
		'message' => '',
		'data' => array()
	);

	if (!is_array($tables)) {
		$result['message'] = 'Tables must be supplied as an array.';
		return $result;
	}

	$issues = array();
	$checked = array();
	foreach ($tables as $table) {
		if (!is_array($table)) {
			continue;
		}

		$name = '';
		if (!empty($table['name'])) {
			$name = trim((string)$table['name']);
		}
		if (empty($name) || !preg_match('/^[a-zA-Z0-9_]+$/', $name)) {
			continue;
		}

		$max_id = 0;
		if (isset($table['max_id'])) {
			$max_id = (int)$table['max_id'];
		}
		$auto_increment = 0;
		if (isset($table['auto_increment'])) {
			$auto_increment = (int)$table['auto_increment'];
		}

		$expected_next = $max_id + 1;
		$status = 'ok';
		if ($auto_increment > 0 && $auto_increment < $expected_next) {
			$status = 'needs_adjustment';
			$issues[] = array(
				'table' => $name,
				'max_id' => $max_id,
				'auto_increment' => $auto_increment,
				'expected_minimum' => $expected_next
			);
		}

		$checked[] = array(
			'table' => $name,
			'max_id' => $max_id,
			'auto_increment' => $auto_increment,
			'expected_next' => $expected_next,
			'status' => $status
		);
	}

	$result['success'] = true;
	if (empty($issues)) {
		$result['message'] = 'Auto-increment state passed.';
	} else {
		$result['message'] = 'Auto-increment issues detected.';
	}
	$result['data'] = array(
		'checked' => $checked,
		'issues' => $issues
	);

	return $result;
}