sql.php

<?php
// Updates the linked statements, functions, keywords, system variables and status variables
// shared by MySQL and MariaDB in modules/jush-sql.js
// from the MySQL online manual (index pages are cached in the given directory)
// and a checkout of https://github.com/mariadb-corporation/mariadb-docs
// Entry keys are 'mysql-key maria-key' (see jush.keywords_links); '-' means the vendor doesn't know
// the name at all, an empty part keeps it highlighted as a keyword without a link

require __DIR__ . '/functions.inc.php';

$mysql_version = '9.7';

if (!isset($argv[2])) {
	fwrite(STDERR, "Usage: php update/sql.php path/to/mysql-cache-dir path/to/mariadb-docs\n");
	exit(1);
}
$mysql_dir = rtrim($argv[1], '/\\');
$maria_ref = rtrim($argv[2], '/\\') . '/server/reference';
$jush_file = __DIR__ . '/../modules/jush-sql.js';
$jush = read_file($jush_file);

// Variables of plugins are not highlighted, like jush-php.js doesn't highlight PECL extensions
$skip_pages = [
	// MySQL
	'audit-log-reference.html', 'clone-plugin-options-variables.html', 'connection-control-plugin-variables.html',
	'firewall-plugin.html', 'group-replication-system-variables.html', 'keyring-system-variables.html',
	'lock-order-tool.html', 'mysql-cluster-options-variables.html', 'pluggable-authentication-system-variables.html',
	'rewriter-query-rewrite-plugin-reference.html', 'validate-password-options-variables.html', 'x-plugin-options-system-variables.html',
	// MariaDB
	'cassandra-status-variables', 'cassandra-system-variables', 'connect-system-variables',
	'mariadb-audit-plugin-options-and-system-variables', 'mroonga-status-variables', 'mroonga-system-variables',
	'myrocks-status-variables', 'myrocks-system-variables', 'oqgraph-system-and-status-variables',
	's3-storage-engine-system-variables', 'sphinx-status-variables', 'spider-status-variables', 'spider-system-variables',
	'sql-error-log-system-variables-and-options', 'tokudb-status-variables', 'tokudb-system-variables',
];

// Get a MySQL manual page from the cache directory, fetching it from dev.mysql.com when missing
function mysql_page($name) {
	global $mysql_dir, $mysql_version;
	$file = "$mysql_dir/$name.html";
	if (!file_exists($file)) {
		$url = "https://dev.mysql.com/doc/refman/$mysql_version/en/$name.html";
		fwrite(STDERR, "Fetching $url\n");
		$html = file_get_contents($url);
		if ($html === false) {
			fwrite(STDERR, "Can't fetch $url\n");
			exit(1);
		}
		file_put_contents($file, $html);
	}
	return read_file($file);
}

// Replace the lines between two generated-region markers, returning the new subject and the old lines
function set_region($subject, $marker, $content) {
	$start_marker = "\t// $marker (generated by update/sql.php)\n";
	$start = strpos($subject, $start_marker);
	$end = ($start === false ? false : strpos($subject, "\t// end $marker\n", $start));
	if ($end === false) {
		fwrite(STDERR, "Can't find the $marker region\n");
		exit(1);
	}
	$start += strlen($start_marker);
	return [substr_replace($subject, $content, $start, $end - $start), substr($subject, $start, $end - $start)];
}

// Get ['key' => 'regexp source'] of the entries in a build_links2 block
function block_entries($block) {
	preg_match_all("~^\t'([^']*)': /(.*)/,~m", $block, $matches, PREG_SET_ORDER);
	$return = [];
	foreach ($matches as $match) {
		$return[$match[1]] = $match[2];
	}
	return $return;
}

// Get the body of a build_links2 block with its start and end offsets
function find_block($jush, $key) {
	$start = strpos($jush, "jush.build_links2('$key'");
	$start = ($start === false ? false : strpos($jush, "{\n", $start));
	$end = ($start === false ? false : strpos($jush, "\n});", $start));
	if ($end === false) {
		fwrite(STDERR, "Can't find the build_links2('$key') block\n");
		exit(1);
	}
	$start += 2;
	return [substr($jush, $start, $end - $start), $start, $end];
}

// Test whether some of the given entries already matches $s as a whole phrase,
// as a keyword followed by something else or as a function call
function is_covered(array $regexps, $s, $mode = 'phrase') {
	$subjects = ['phrase' => $s, 'keyword' => "$s x", 'call' => "$s("];
	$suffixes = ['phrase' => '$', 'keyword' => '\s', 'call' => '\($'];
	foreach ($regexps as $regexp) {
		if (preg_match('~^(?:' . $regexp . ')' . $suffixes[$mode] . '~i', $subjects[$mode])) {
			return true;
		}
	}
	return false;
}

// Expand (?:A|B) and (?:A|B)? groups of a phrase alternation into plain phrases (for diff reporting)
function expand_phrases($alternation) {
	$s = str_replace('\\s+', ' ', $alternation);
	if (preg_match('~^(.*?)\(\?:([^()]*)\)(\??)(.*)$~s', $s, $match)) {
		$return = [];
		$options = explode('|', $match[2]);
		if ($match[3]) {
			$options[] = '';
		}
		foreach ($options as $option) {
			foreach (expand_phrases($match[1] . $option . $match[4]) as $phrase) {
				$return[] = preg_replace('~\s+~', ' ', trim($phrase));
			}
		}
		return array_unique($return);
	}
	return explode('|', $s);
}

// Turn DATABASE back into the (?:DATABASE|SCHEMA) alternation the runtime maps to one page
function schema_regexp($phrases_regexp) {
	return preg_replace('~DATABASE(S?)~', '(?:DATABASE|SCHEMA)$1', $phrases_regexp);
}

// Replace the mechanical form embedded in an exceptional key part by $1 so that
// e.g. st_area and st_binary merge into one st_$1 entry
function embed_name($exception, $mechanical) {
	$pos = strrpos($exception, $mechanical);
	return ($pos === false ? $exception : substr_replace($exception, '$1', $pos, strlen($mechanical)));
}

// A phrase the vendor doesn't document may still be made of its keywords (e.g. GROUP BY):
// an empty key part keeps it highlighted there while '-' leaves it as plain text
function unknown_key($phrase, array $keywords) {
	return (array_diff(explode(' ', $phrase), $keywords) ? '-' : '');
}

// Get the top-level alternatives of the leading capturing subpattern and the rest of the source
function entry_alternatives($source) {
	$alternatives = [];
	$depth = 0;
	$last = 0;
	for ($i = 0; $i < strlen($source); $i++) {
		$c = $source[$i];
		if ($c == '\\') {
			$i++;
		} elseif ($c == '(') {
			if (!$depth++) {
				$last = $i + 1;
			}
		} elseif ($c == ')') {
			if (!--$depth) {
				$alternatives[] = substr($source, $last, $i - $last);
				return [$alternatives, substr($source, $i + 1)];
			}
		} elseif ($c == '|' && $depth == 1) {
			$alternatives[] = substr($source, $last, $i - $last);
			$last = $i + 1;
		}
	}
	fwrite(STDERR, "Can't parse the entry /$source/\n");
	exit(1);
}

// Whether a phrase of the first list is a prefix of a phrase of the second one
function prefix_of(array $phrases, array $others) {
	foreach ($phrases as $phrase) {
		foreach ($others as $other) {
			if (strpos($other, "$phrase ") === 0) {
				return true;
			}
		}
	}
	return false;
}

// links2 is a single alternation matching the first alternative, not the longest one, so a phrase must
// not precede a longer phrase starting with it; phrases_regexp() sorts them inside one entry,
// this orders the entries so that it holds across them too
function order_entries(array $entries) { // [line, phrases]
	$return = [];
	while ($entries) {
		foreach ($entries as $i => $entry) {
			foreach ($entries as $j => $other) {
				if ($i != $j && prefix_of($entry[1], $other[1])) {
					continue 2; // a longer phrase is still waiting
				}
			}
			$return[] = $entry;
			unset($entries[$i]);
			continue 2;
		}
		fwrite(STDERR, "Statement entries shadow each other in a cycle: " . implode(', ', array_map(function ($entry) {
			return implode('|', $entry[1]);
		}, $entries)) . "\n");
		return array_merge($return, $entries);
	}
	return $return;
}

// The entries which can't be ordered - a hand-maintained phrase after the generated region - get
// a lookahead rejecting the rest of the longer phrases instead
function set_shadow_lookaheads($jush, $block_key) {
	list($block, $start, $end) = find_block($jush, $block_key);
	$lines = explode("\n", $block);
	$alternatives = []; // [line, position in the entry, phrase or '' when it can't take a lookahead]
	$last_phrase = []; // phrase => index of its last alternative
	foreach ($lines as $line => $source) {
		if (!preg_match("~^\t'[^']*': /(.*)/,$~", $source, $match)) {
			continue;
		}
		foreach (entry_alternatives($match[1])[0] as $position => $alternative) {
			$alternative = preg_replace('~\(\?!\\\\s\+\(\?:[^)]*\)\)~', '', $alternative); // drop the lookahead of the previous run
			// only a plain phrase optionally followed by lookaheads can take one more
			$phrase = (preg_match('~^([A-Z][A-Z0-9_]*(?:\\\\s\+[A-Z][A-Z0-9_]*)*)(|\(\?[=!].*)$~s', $alternative, $match2)
				? str_replace('\\s+', ' ', $match2[1])
				: '');
			foreach (($phrase != '' ? [$phrase] : expand_phrases(preg_replace('~\(\?[=!].*~s', '', $alternative))) as $name) {
				if (preg_match('~^[A-Z][A-Z0-9_ ]*$~', $name)) {
					$last_phrase[$name] = count($alternatives);
				}
			}
			$alternatives[] = [$line, $position, $phrase];
		}
	}

	$lookaheads = [];
	foreach ($alternatives as $index => list($line, $position, $phrase)) {
		$rests = [];
		foreach ($last_phrase as $name => $last) {
			if ($phrase != '' && $last > $index && strpos($name, "$phrase ") === 0) {
				// the whole rest, not just the next word - PARTITION must stay linked in PARTITION BY x
				$rests[str_replace(' ', '\\s+', substr($name, strlen($phrase) + 1))] = true;
			}
		}
		if ($rests) {
			$rests = array_keys($rests);
			sort($rests);
			$lookaheads[$line][$position] = '(?!\\s+(?:' . implode('|', $rests) . '))';
			fwrite(STDERR, "Shadowed: $phrase before " . implode(', ', str_replace('\\s+', ' ', $rests)) . "\n");
		}
	}

	foreach ($lines as $line => $source) {
		if (!preg_match("~^(\t'[^']*': /)(.*)(/,)$~", $source, $match)) {
			continue;
		}
		list($entry, $rest) = entry_alternatives($match[2]);
		foreach ($entry as $position => $alternative) {
			$alternative = preg_replace('~\(\?!\\\\s\+\(\?:[^)]*\)\)~', '', $alternative);
			$lookahead = ($lookaheads[$line][$position] ?? '');
			$entry[$position] = ($lookahead == '' ? $alternative : preg_replace(
				'~^([A-Z][A-Z0-9_]*(?:\\\\s\+[A-Z][A-Z0-9_]*)*)~',
				'$1' . str_replace('\\', '\\\\', $lookahead),
				$alternative
			));
		}
		$lines[$line] = $match[1] . '(' . implode('|', $entry) . ')' . $rest . $match[3];
	}
	return substr_replace($jush, implode("\n", $lines), $start, $end - $start);
}


// MariaDB inventory

// functions: the reference table links every function to its page (the file name is the KB slug)
$maria_functions = [];
preg_match_all('~^\| \[([^\]]+)\]\(([^)#]+)\.md[^)]*\)~m', read_file("$maria_ref/sql-functions/function-and-operator-reference.md"), $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
	$name = str_replace(['\\', '()'], '', trim($match[1]));
	// Spider UDFs are documented under storage-engines
	if (preg_match('~^\w+$~', $name) && strpos($match[2], 'storage-engines/') === false) {
		$maria_functions[strtolower($name)] = strtolower(basename($match[2]));
	}
}
if (!$maria_functions) {
	fwrite(STDERR, "No functions found in function-and-operator-reference.md\n");
	exit(1);
}

// statements: one file per statement, the all-caps H1 is the phrase;
// some statement docs live outside the sql-statements reference
$maria_statements = [];
$maria_usage = dirname($maria_ref) . '/server-usage';
foreach (
	["$maria_ref/sql-statements", "$maria_ref/sql-structure/sequences", "$maria_usage/stored-routines", "$maria_usage/triggers-events", "$maria_usage/views"] as $dir
) {
	foreach (new RecursiveIteratorIterator(new RecursiveDirectoryIterator($dir)) as $file) {
		// geometry-constructors holds geometry function docs, not statements
		if (preg_match('~(?<!README)\.md$~', $file) && strpos($file, 'geometry-constructors') === false) {
			preg_match('~^# (.+)~m', read_file($file), $match);
			$title = trim($match[1] ?? '');
			if (preg_match('~^[A-Z][A-Z0-9_ ]*$~', $title)) {
				$maria_statements[preg_replace('~\s+~', ' ', $title)] = basename($file, '.md');
			}
		}
	}
}
if (!$maria_statements) {
	fwrite(STDERR, "No statements found in sql-statements\n");
	exit(1);
}

// system and status variables: the full list links every variable to its page and anchor
$maria_sysvars = [];
$maria_status = [];
// some variables link across GitBook spaces (https://app.gitbook.com/...#anchor without .md)
preg_match_all('~\[([^\]]+)\]\(([^)#]+?)(?:\.md)?#([\w.-]+)\)~', read_file("$maria_ref/full-list-of-mariadb-options-system-and-status-variables.md"), $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
	$name = str_replace('\\', '', $match[1]);
	$page = basename($match[2]);
	if (preg_match('~^\w+$~', $name) && !in_array($page, $skip_pages)) {
		if (strpos($page, 'status-variables') !== false) {
			$maria_status[$name] = [$page, $match[3]];
		} elseif (strpos($page, 'system-variables') !== false) {
			$maria_sysvars[$name] = [$page, $match[3]];
		}
		// command-line only options are not settable in SET
	}
}
if (!$maria_sysvars || !$maria_status) {
	fwrite(STDERR, "No variables found in the full list of options and variables\n");
	exit(1);
}

// reserved words
preg_match_all('~^\| ([A-Z][A-Z_0-9]*)\s*\|~m', mdn_section(read_file("$maria_ref/sql-structure/sql-language-structure/reserved-words.md"), 'Reserved Words'), $matches);
$maria_keywords = $matches[1];
if (!$maria_keywords) {
	fwrite(STDERR, "No reserved words found in reserved-words.md\n");
	exit(1);
}


// MySQL inventory

// functions: NAME() with a #function_ anchor (operators and keywords have #operator_ and stay hand-maintained)
$mysql_functions = [];
preg_match_all('~href="([\w-]+\.html)#function_([\w-]+)"><code class="literal">(\w+)(?:\(\))?</code>~', mysql_page('built-in-function-reference'), $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
	if (strpos($match[1], 'group-replication-functions') !== 0) {
		$mysql_functions[strtolower($match[3])] = [$match[1], $match[2]];
	}
}
if (!$mysql_functions) {
	fwrite(STDERR, "No functions found in built-in-function-reference.html\n");
	exit(1);
}

// statements: chapter TOC titles like "15.1.2 ALTER DATABASE Statement"
$mysql_statements = [];
preg_match_all('~href="([\w-]+\.html)">(?:[\d.]+ )?([A-Z][A-Z0-9_ ]*?) (?:Statements?|Clause)<~', mysql_page('sql-statements'), $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
	$phrase = preg_replace('~\s+~', ' ', $match[2]);
	if (!isset($mysql_statements[$phrase])) {
		$mysql_statements[$phrase] = $match[1];
	}
}
if (!$mysql_statements) {
	fwrite(STDERR, "No statements found in sql-statements.html\n");
	exit(1);
}

// reserved keywords are marked with (R)
preg_match_all('~<code class="literal">([A-Z_0-9]+)</code> \(R\)~', mysql_page('keywords'), $matches);
$mysql_keywords = array_unique($matches[1]);
if (!$mysql_keywords) {
	fwrite(STDERR, "No keywords found in keywords.html\n");
	exit(1);
}

// system variables (the option reference adds nothing settable in SET);
// some variables are documented only as options with dashed #option_mysqld_ anchors -
// their names get a [-_] alternation and jush.js finds the right form by probing links2
$mysql_sysvars = [];
preg_match_all('~href="([\w-]+\.html)#(sysvar|option_mysqld)_([\w-]+)">(\w+)<~', mysql_page('server-system-variable-reference'), $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
	if ((!isset($mysql_sysvars[$match[4]]) || $match[2] == 'sysvar') && !in_array($match[1], $skip_pages)) {
		$mysql_sysvars[$match[4]] = [$match[1], $match[3], $match[2]];
	}
}
if (!$mysql_sysvars) {
	fwrite(STDERR, "No variables found in server-system-variable-reference.html\n");
	exit(1);
}

// status variables
$mysql_status = [];
preg_match_all('~href="([\w-]+\.html)#statvar_([\w.-]+)">(\w+)<~', mysql_page('server-status-variable-reference'), $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
	if (!in_array($match[1], $skip_pages)) {
		$mysql_status[$match[3]] = [$match[1], $match[2]];
	}
}
if (!$mysql_status) {
	fwrite(STDERR, "No variables found in server-status-variable-reference.html\n");
	exit(1);
}


// Update the function groups of the hand-maintained 'page.html#function_$1' entries in place

list($block) = find_block($jush, 'sql');
list($hand) = set_region($block, 'statements', '');
list($hand) = set_region($hand, 'functions', '');
foreach (block_entries($hand) as $key => $regexp) {
	if (preg_match('~^([\w-]+\.html)#function_\$1( |$)~', $key, $match)) {
		$page = $match[1];
		$names = [];
		foreach ($mysql_functions as $name => $function) {
			if ($function[0] == $page && $function[1] == str_replace('_', '-', $name)) {
				$names[] = $name;
			}
		}
		if (!$names) {
			fwrite(STDERR, "Stale function page $page: not in the MySQL function reference\n");
			continue;
		}
		$keywords = array_map('strtolower', explode('|', ltrim(preg_replace('~\(\?:.*~s', '', $regexp), '(')));
		$names = array_values(array_diff($names, $keywords));
		sort($names);
		$start = strpos($jush, "'$key': /");
		$end = strpos($jush, "\n", $start);
		$entry = set_list(substr($jush, $start, $end - $start), '|(?:', ')(?=', $names, "$page functions");
		$jush = substr_replace($jush, $entry, $start, $end - $start);
	}
}

// Hand-maintained entries decide what the generated regions must not duplicate
list($block) = find_block($jush, 'sql');
list($hand) = set_region($block, 'statements', '');
list($hand) = set_region($hand, 'functions', '');
$hand_regexps = [];
foreach (block_entries($hand) as $key => $regexp) {
	if ($key != '') {
		$hand_regexps[] = $regexp;
	}
}

// Partition functions: shared names link to both manuals, the rest to one
$function_groups = []; // entry key => names
$maria_only_functions = []; // names with a mechanical KB slug
foreach ($mysql_functions as $name => $function) {
	if (is_covered($hand_regexps, $name, 'call')) {
		continue;
	}
	list($page, $anchor) = $function;
	$mysql_key = "$page#function_" . ($anchor == str_replace('_', '-', $name) ? '$1' : embed_name($anchor, str_replace('_', '-', $name)));
	$maria_slug = ($maria_functions[$name] ?? '');
	$maria_key = ($maria_slug == '' ? '-' : ($maria_slug == $name ? '$1' : embed_name($maria_slug, $name)) . '/');
	$function_groups["$mysql_key $maria_key"][] = $name;
}
foreach ($maria_functions as $name => $slug) {
	if (!isset($mysql_functions[$name]) && !is_covered($hand_regexps, $name, 'call')) {
		if ($slug == $name) {
			$maria_only_functions[] = $name;
		} else {
			$function_groups['- ' . embed_name($slug, $name) . '/'][] = $name;
		}
	}
}

// Partition statements; DATABASE pages also match SCHEMA (jush.js maps it back)
$shared_statements = [];
$mysql_only_statements = [];
$maria_only_statements = [];
$statement_exceptions = []; // entry key => phrases
foreach (array_keys($mysql_statements + $maria_statements) as $phrase) {
	if (is_covered($hand_regexps, $phrase)) {
		continue;
	}
	$mechanical = strtolower(str_replace(' ', '-', $phrase));
	$mysql_key = (isset($mysql_statements[$phrase]) ? ($mysql_statements[$phrase] == "$mechanical.html" ? '$1.html' : embed_name($mysql_statements[$phrase], $mechanical)) : unknown_key($phrase, $mysql_keywords));
	$maria_key = (isset($maria_statements[$phrase]) ? ($maria_statements[$phrase] == $mechanical ? '$1' : embed_name($maria_statements[$phrase], $mechanical)) . '/' : unknown_key($phrase, $maria_keywords));
	if ($mysql_key == '$1.html' && $maria_key == '$1/') {
		$shared_statements[] = $phrase;
	} elseif ($mysql_key == '$1.html' && $maria_key == '-') {
		$mysql_only_statements[] = $phrase;
	} elseif ($mysql_key == '-' && $maria_key == '$1/') {
		$maria_only_statements[] = $phrase;
	} else {
		$statement_exceptions["$mysql_key $maria_key"][] = $phrase;
	}
}

// Rewrite the statements region: the exceptions, MariaDB-only statements and functions,
// MySQL-only statements and the shared list, ordered so that a longer phrase wins over its prefix
$entries = []; // [line, phrases]
ksort($statement_exceptions);
foreach ($statement_exceptions as $key => $phrases) {
	$entries[] = ["\t'$key': /(" . schema_regexp(phrases_regexp($phrases)) . ")(?!\\()/,\n", $phrases];
}
$maria_only = ($maria_only_statements ? schema_regexp(phrases_regexp($maria_only_statements)) . '(?!\\()' : '');
if ($maria_only_functions) {
	sort($maria_only_functions);
	$maria_only .= ($maria_only ? '|' : '') . '(?:' . implode('|', $maria_only_functions) . ')(?=\\s*\\(|$)';
}
if ($maria_only != '') {
	$entries[] = ["\t'- \$1/': /($maria_only)/,\n", $maria_only_statements];
}
if ($mysql_only_statements) {
	$entries[] = ["\t'\$1.html -': /(" . schema_regexp(phrases_regexp($mysql_only_statements)) . ")(?!\\()/,\n", $mysql_only_statements];
}
$entries[] = ["\t'\$1.html': /(" . schema_regexp(phrases_regexp($shared_statements)) . ")(?!\\()/,\n", $shared_statements];
$lines = implode('', array_column(order_entries($entries), 0));
list($jush, $old_region) = set_region($jush, 'statements', $lines);
$old_statements = [];
foreach (block_entries($old_region) as $regexp) {
	$regexp = str_replace('(?!\()', '', $regexp);
	if (preg_match('~^\((.*)\)$~s', $regexp, $match)) {
		$regexp = $match[1];
	}
	$old_statements = array_merge($old_statements, array_filter(expand_phrases($regexp), function ($phrase) {
		// the SCHEMA variants only exist in the regexps, the doc pages use DATABASE
		return preg_match('~^[A-Z0-9_ ]+$~', $phrase) && !preg_match('~\bSCHEMAS?\b~', $phrase);
	}));
}
// the region also holds the MariaDB-only functions
preg_match_all('~\(\?:([\w|]+)\)\(\?=~', $old_region, $matches);
$old_maria_functions = ($matches[1] ? explode('|', implode('|', $matches[1])) : []);
$new_statements = array_merge($shared_statements, $mysql_only_statements, $maria_only_statements, ...array_values($statement_exceptions ?: [[]]));
sort($old_statements);
sort($new_statements);
report_diff('statements', array_unique($old_statements), $new_statements);

// Rewrite the functions region
$lines = '';
ksort($function_groups);
foreach ($function_groups as $key => $names) {
	sort($names);
	$lines .= "\t'$key': /(" . implode('|', $names) . ")(?=\\s*\\(|\$)/,\n";
}
list($jush, $old_region) = set_region($jush, 'functions', $lines);
$old_functions = $old_maria_functions;
foreach (block_entries($old_region) as $regexp) {
	preg_match_all('~\w+~', str_replace(['(?:', '(?=\\s*\\(|$)'], '', $regexp), $matches);
	$old_functions = array_merge($old_functions, $matches[0]);
}
$new_functions = array_merge($maria_only_functions, ...array_values($function_groups ?: [[]]));
sort($old_functions);
sort($new_functions);
report_diff('functions', array_unique($old_functions), $new_functions);

// The '' entry holds keywords with no doc page: subtract words linked by other entries
// but keep words linked only as function calls (their lookahead rejects a bare keyword)
$keywords = array_unique(array_map('strtoupper', array_merge($mysql_keywords, $maria_keywords)));
sort($keywords);
list($block) = find_block($jush, 'sql');
$linked = [];
foreach (block_entries($block) as $key => $regexp) {
	if ($key != '') {
		$linked[] = $regexp;
	}
}
$keywords = array_values(array_filter($keywords, function ($keyword) use ($linked) {
	return !is_covered($linked, $keyword, 'keyword');
}));
$jush = set_list($jush, "'': /(", ")(?!\\()/,", $keywords, 'keywords');

// Rewrite the sqlset block: system variables grouped by their page pair
$groups = [];
foreach (array_keys($mysql_sysvars + $maria_sysvars) as $name) {
	$mysql_key = '-';
	$regexp_name = $name;
	if (isset($mysql_sysvars[$name])) {
		list($page, $anchor, $kind) = $mysql_sysvars[$name];
		$mechanical = ($kind == 'sysvar' ? $name : str_replace('_', '-', $name));
		$mysql_key = "$page#{$kind}_" . ($anchor == $mechanical ? '$1' : embed_name($anchor, $mechanical));
		if ($kind == 'option_mysqld') {
			$regexp_name = str_replace('_', '[-_]', $name);
		}
	}
	$maria_key = '-';
	if (isset($maria_sysvars[$name])) {
		list($page, $anchor) = $maria_sysvars[$name];
		$maria_key = "$page/#" . ($anchor == strtolower($name) ? '$1' : embed_name($anchor, strtolower($name)));
	}
	$groups["$mysql_key $maria_key"][] = $regexp_name;
}
ksort($groups);
$lines = '';
foreach ($groups as $key => $names) {
	sort($names);
	$lines .= "\t'$key': /(" . implode('|', $names) . ")/,\n";
}
list($block, $start, $end) = find_block($jush, 'sqlset');
$old_sysvars = [];
foreach (block_entries($block) as $regexp) {
	foreach (explode('|', $regexp) as $alternative) {
		$old_sysvars[] = trim(str_replace('[-_]', '_', $alternative), '()');
	}
}
report_diff('system variables', array_unique($old_sysvars), array_keys($mysql_sysvars + $maria_sysvars));
$jush = substr_replace($jush, rtrim($lines, "\n"), $start, $end - $start);

// Rewrite the sqlstatus block: whole pages are matched by a name prefix, the rest links
// to the big server-status-variables pages (MariaDB anchors are lowercased by jush.js)
$maria_pages = [];
foreach ($maria_status as $name => $status) {
	$maria_pages[$status[0]][] = $name;
}
ksort($maria_pages);
$com_page = 'server-status-variables';
$lines = "\t'server-status-variables.html#statvar_Com_xxx $com_page/#\$1': /(Com_.+)/,\n";
foreach ($maria_pages as $page => $names) {
	if ($page == 'server-status-variables') {
		continue;
	}
	$prefix = array_reduce($names, function ($carry, $name) {
		while ($carry !== null && strncasecmp($name, $carry, strlen($carry))) {
			$carry = substr($carry, 0, -1);
		}
		return ($carry === null ? $name : $carry);
	});
	$prefix = preg_replace('~(_[^_]*|[^_]+)$~', '_', $prefix);
	$mysql_key = '-';
	foreach ($mysql_status as $name => $status) {
		if ($prefix != '' ? !strncasecmp($name, $prefix, strlen($prefix)) : isset($maria_status[$name]) && $maria_status[$name][0] == $page) {
			$mysql_key = "$status[0]#statvar_\$1";
			break;
		}
	}
	// a whole page is matched by the common name prefix, mixed pages list all their names
	sort($names);
	$lines .= "\t'$mysql_key $page/#\$1': /(" . (strlen($prefix) < 4 ? implode('|', $names) : "$prefix.+") . ")/,\n";
}
$lines .= "\t'server-status-variables.html#statvar_\$1 server-status-variables/#\$1': /(.+)/,\n";
list($block, $start, $end) = find_block($jush, 'sqlstatus');
$old_pages = [];
foreach (array_keys(block_entries($block)) as $key) {
	if (preg_match('~(?:^|\s)([\w-]+)/#~', $key, $match)) {
		$old_pages[] = $match[1];
	}
}
report_diff('status pages', array_unique($old_pages), array_keys($maria_pages));
$jush = substr_replace($jush, rtrim($lines, "\n"), $start, $end - $start);

$jush = set_shadow_lookaheads($jush, 'sql');

file_put_contents($jush_file, $jush);
fwrite(STDERR, sprintf(
	"MySQL: %d functions, %d statements, %d keywords, %d system variables, %d status variables\n",
	count($mysql_functions), count($mysql_statements), count($mysql_keywords), count($mysql_sysvars), count($mysql_status)
));
fwrite(STDERR, sprintf(
	"MariaDB: %d functions, %d statements, %d keywords, %d system variables, %d status variables\n",
	count($maria_functions), count($maria_statements), count($maria_keywords), count($maria_sysvars), count($maria_status)
));