This tool processes a SQL query and generates a guess at an appropriate Psalm object-like array definition

psalm-query.php

<?php

const PSALM_STRING = ': string';
const SELECT_ALL = '*';
// These must be upper case to avoid accidental positives
const SQL_SELECT = 'SELECT';
const SQL_FROM = 'FROM';
const ARG_DEBUG = '--debug';
// This is lower case as the field names are forced to lower case
const SQL_AS = ' as ';
const NUMERIC_FIELDS = ['_id', 'count', 'amount', 'price', 'quantity'];

function _query_from_args(array $args): string{
	foreach ($args as $arg){
		if (strpos($arg, SQL_SELECT)!==false){
			return _clean_query($arg);
		}
	}
	throw new RuntimeException('At least one parameter must be a SELECT query');
}

function _clean_query(string $query): string{
	$query = str_replace(["\n", "\t"], ' ', $query);
	$query = explode(' ', $query);
	$query = array_map('trim', $query);
	$query = array_filter($query);

	return implode(' ', $query);
}

/**
 * @return string[]
 */
function _extract_selects(string $query): array{
	$start = strpos($query, SQL_SELECT);
	$end = strpos($query, SQL_FROM);
	if ($end<=$start){
		throw new RuntimeException('Could not find a FROM section in the query');
	}

	$start += strlen(SQL_SELECT);
	$selects = explode(',', substr($query, $start, $end - $start));
	$selects = array_map('trim', $selects);

	return array_filter($selects);
}

/**
 * @param string[] $selects
 *
 * @return string[]
 */
function _fields_from_selects(array $selects): array{
	$fields = [];
	foreach ($selects as $val){
		$val = strtolower($val);
		[$field, $alias] = explode(SQL_AS, $val);
		if ($alias){
			$fields[] = $alias;
			continue;
		}
		[$table, $field] = explode('.', $field);
		$fields[] = $field ?: $table;
	}

	return array_filter($fields);
}

function _maybe_numeric(string $field): bool{
  foreach (NUMERIC_FIELDS as $numeric_field){
    if (strpos($field, $numeric_field)!==false){
      return true;
    }
  }
  
  return false;
}

function _output_psalm(array $fields): void{
	sort($fields);
	if (in_array(SELECT_ALL, $fields, true)){
		echo " WARNING: The select list includes * so it cannot be determined which fields are present\n";
	}
	echo 'array{';
	foreach ($fields as $n => $field){
		if ($field===SELECT_ALL){
			continue;
		}
		echo $field;
		echo _maybe_numeric($field) ? ': numeric' : ': string';
		if ($n!==count($fields) - 1){
			echo ', ';
		}
	}
	echo '}';
}

$is_debug = in_array(ARG_DEBUG, $argv, true);
$query = _query_from_args($argv);

if ($is_debug){
	echo $query.PHP_EOL;
}

$selects = _extract_selects($query);
if ($is_debug){
	print_r($selects);
}

$fields = _fields_from_selects($selects);
if ($is_debug){
	print_r($fields);
}

_output_psalm($fields);
echo PHP_EOL;