Skip to content

Instantly share code, notes, and snippets.

@M1ke
Created April 27, 2022 15:06
Show Gist options
  • Save M1ke/e8af5cb394d045a04a28b640b021c3bf to your computer and use it in GitHub Desktop.
Save M1ke/e8af5cb394d045a04a28b640b021c3bf to your computer and use it in GitHub Desktop.
This tool processes a SQL query and generates a guess at an appropriate Psalm object-like array definition
<?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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment