unit SQLParser;

interface

uses
  System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions, Uni;

type
  TColumnInfo = class
  public
    FullName: string;
    RealSchemaName: string;
    RealTableName: string;
    ReferencedTableColumn: string;
    QueryColumnAlias: string;
    IsPKColumn: Boolean; // Indicates if it's a primary key column
  end;

  TTableInfo = class
  public
    SchemaName: string;
    TableName: string;
    Alias: string;
  end;

  TSQLParser = class
  private
    FSQLText: string;
    FTables: TObjectList<TTableInfo>;
    FColumns: TObjectList<TColumnInfo>;
    FConnection: TUniConnection;
    FPrimaryKeys: TDictionary<string, TList<string>>;
    procedure Parse;
    procedure ParseFromClause;
    procedure ParseSelectClause;
    function IsExpression(const S: string): Boolean;
    function IsClauseKeyword(const Token: string): Boolean;
    function GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
    function IsJoinType(const Token: string): Boolean;
    procedure CheckPrimaryKeys; // Checks if columns are primary keys
    procedure FillPrimaryKeyDictionary; // Fills the primary key dictionary
    function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
  public
    constructor Create(const SQLText: string; AConnection: TUniConnection);
    destructor Destroy; override;
    property Tables: TObjectList<TTableInfo> read FTables;
    property Columns: TObjectList<TColumnInfo> read FColumns;
  end;

implementation

constructor TSQLParser.Create(const SQLText: string; AConnection: TUniConnection);
begin
  inherited Create;
  FSQLText := SQLText;
  FConnection := AConnection; // Store the provided database connection
  FTables := TObjectList<TTableInfo>.Create(True); // Owns objects
  FColumns := TObjectList<TColumnInfo>.Create(True); // Owns objects
  FPrimaryKeys := TDictionary<string, TList<string>>.Create;
  Parse;
  FillPrimaryKeyDictionary; // After parsing, fill the primary key dictionary
  CheckPrimaryKeys; // Then check for primary key columns using the cache
end;

destructor TSQLParser.Destroy;
var
  ColumnsList: TList<string>;
begin
  // Free the lists inside the dictionary
  for ColumnsList in FPrimaryKeys.Values do
    ColumnsList.Free;
  FPrimaryKeys.Free;
  FTables.Free;
  FColumns.Free;
  inherited;
end;

procedure TSQLParser.Parse;
begin
  ParseFromClause;
  ParseSelectClause;
end;

function TSQLParser.IsClauseKeyword(const Token: string): Boolean;
const
  ClauseKeywords: array[0..10] of string = ('WHERE', 'GROUP BY', 'HAVING', 'ORDER BY', 'LIMIT', 'OFFSET', 'UNION', 'INTERSECT', 'EXCEPT', 'EXISTS', ';');
var
  Keyword: string;
begin
  Result := False;
  for Keyword in ClauseKeywords do
  begin
    if SameText(Token, Keyword) then
      Exit(True);
  end;
end;

procedure TSQLParser.ParseFromClause;
var
  FromMatch, NextClauseMatch: TMatch;
  FromPos, NextClausePos: Integer;
  FromClause: string;
  Tokens: TArray<string>;
  i: Integer;
  Token: string;
  TableInfo: TTableInfo;
  NextToken: string;
  RemainingText: string;
begin
  // Find position of FROM
  FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);
  if not FromMatch.Success then Exit; // No FROM clause found
  FromPos := FromMatch.Index + FromMatch.Length;

  // Extract the text after FROM
  RemainingText := Copy(FSQLText, FromPos, MaxInt);

  // Find position of the next clause (e.g., WHERE, GROUP BY, etc.) in RemainingText
  NextClauseMatch := TRegEx.Match(RemainingText, '\b(WHERE|GROUP BY|HAVING|ORDER BY|LIMIT|OFFSET|UNION|INTERSECT|EXCEPT|EXISTS|;)\b', [roIgnoreCase]);
  if NextClauseMatch.Success then
    NextClausePos := NextClauseMatch.Index
  else
    NextClausePos := Length(RemainingText) + 1; // No next clause found

  // Extract FROM clause
  FromClause := Copy(RemainingText, 1, NextClausePos - 1).Trim;

  // Split the FROM clause into tokens using spaces and commas
  Tokens := TRegEx.Split(FromClause, '\s+|,'); // Split on spaces or commas

  i := 0;
  while i < Length(Tokens) do
  begin
    Token := Trim(Tokens[i]);
    if Token = '' then
    begin
      Inc(i);
      Continue;
    end;

    if IsJoinType(Token) or SameText(Token, 'JOIN') then
    begin
      // Skip join type and 'JOIN' keyword
      Inc(i);
      Continue;
    end
    else if SameText(Token, 'ON') then
    begin
      // Skip tokens until the next 'JOIN' or clause keyword
      Inc(i);
      while (i < Length(Tokens)) and not (IsJoinType(Tokens[i]) or SameText(Tokens[i], 'JOIN') or IsClauseKeyword(Tokens[i])) do
        Inc(i);
      Continue;
    end
    else if SameText(Token, 'USING') then
    begin
      // Skip tokens within 'USING (...)'
      Inc(i);
      while (i < Length(Tokens)) and not (Tokens[i].Contains(')')) do
        Inc(i);
      Inc(i); // Skip the token with ')'
      Continue;
    end
    else if SameText(Token, 'AS') then
    begin
      // Alias follows 'AS'
      Inc(i);
      if (i < Length(Tokens)) and (FTables.Count > 0) then
      begin
        FTables.Last.Alias := Tokens[i];
        Inc(i);
      end;
      Continue;
    end
    else
    begin
      // Process table name (may include schema)
      TableInfo := TTableInfo.Create;

      // Check for schema.table format
      if Pos('.', Token) > 0 then
      begin
        TableInfo.SchemaName := Copy(Token, 1, Pos('.', Token) - 1);
        TableInfo.TableName := Copy(Token, Pos('.', Token) + 1, Length(Token));
      end
      else
      begin
        TableInfo.TableName := Token;
      end;

      // Check for alias
      if (i + 1) < Length(Tokens) then
      begin
        NextToken := Tokens[i + 1];
        if not (IsJoinType(NextToken) or SameText(NextToken, 'JOIN') or SameText(NextToken, 'ON') or SameText(NextToken, 'USING') or IsClauseKeyword(NextToken)) then
        begin
          Inc(i);
          TableInfo.Alias := Tokens[i];
        end;
      end;

      FTables.Add(TableInfo);

      Inc(i);
    end;
  end;
end;

procedure TSQLParser.ParseSelectClause;
var
  SelectMatch, FromMatch: TMatch;
  SelectClause: string;
  ColumnsStr: string;
  ColumnList: TArray<string>;
  ColumnStr: string;
  ColumnInfo: TColumnInfo;
  Regex: TRegEx;
  Match: TMatch;
  TableAliasOrName: string;
  ColumnName: string;
  TableInfo: TTableInfo;
  ReservedWords: array of string;
  Tokens: TArray<string>;
  i: Integer;
  SelectPos, FromPos: Integer;
  SelectLength: Integer;
begin
  // Find position of SELECT and FROM
  SelectMatch := TRegEx.Match(FSQLText, '\bSELECT\b', [roIgnoreCase]);
  FromMatch := TRegEx.Match(FSQLText, '\bFROM\b', [roIgnoreCase]);

  if (not SelectMatch.Success) or (not FromMatch.Success) then Exit;

  SelectPos := SelectMatch.Index + SelectMatch.Length;
  FromPos := FromMatch.Index;

  // Extract the SELECT clause content
  SelectLength := FromPos - SelectPos;
  if SelectLength <= 0 then Exit;
  SelectClause := Copy(FSQLText, SelectPos + 1, SelectLength - 1);
  SelectClause := Trim(SelectClause);

  // List of reserved words that may appear after SELECT
  ReservedWords := ['ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN',
                    'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE',
                    'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS'];

  // Split the SelectClause into tokens to check for reserved words at the beginning
  Tokens := SelectClause.Split([' '], TStringSplitOptions.ExcludeEmpty);
  i := 0;
  // Skip reserved words at the beginning
  while (i < Length(Tokens)) and IsReservedWord(Tokens[i], ReservedWords) do
  begin
    Inc(i);
  end;

  // Reconstruct ColumnsStr without leading reserved words
  ColumnsStr := String.Join(' ', Tokens, i, Length(Tokens) - i);

  // Split columns by comma, taking care not to split inside parentheses or quotes
  ColumnList := TRegEx.Split(ColumnsStr, ',(?![^()]*\))');

  for i := 0 to Length(ColumnList) - 1 do
  begin
    ColumnStr := Trim(ColumnList[i]);

    // Regular expression to extract column expressions and aliases
    Regex := TRegEx.Create('^(.*?)(?:\s+AS\s+|\s+)(\w+)$', [roIgnoreCase]);
    Match := Regex.Match(ColumnStr);

    ColumnInfo := TColumnInfo.Create; // Create the ColumnInfo object

    if Match.Success then
    begin
      ColumnInfo.FullName := Match.Groups[1].Value.Trim;
      ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim;
    end
    else
    begin
      ColumnInfo.FullName := ColumnStr;
      ColumnInfo.QueryColumnAlias := '';
    end;

    // Determine if the column is an expression
    if IsExpression(ColumnInfo.FullName) then
    begin
      // It's an expression
      ColumnInfo.RealSchemaName := '[none]';
      ColumnInfo.RealTableName := '[none]';
      ColumnInfo.ReferencedTableColumn := '[none]';
    end
    else
    begin
      // It's a simple column reference
      if Pos('.', ColumnInfo.FullName) > 0 then
      begin
        // Parse table alias/name and column name
        TableAliasOrName := Copy(ColumnInfo.FullName, 1, LastDelimiter('.', ColumnInfo.FullName) - 1);
        ColumnName := Copy(ColumnInfo.FullName, LastDelimiter('.', ColumnInfo.FullName) + 1, Length(ColumnInfo.FullName));

        // Get the table info for this alias or name
        TableInfo := GetTableInfoByAliasOrName(TableAliasOrName);

        if Assigned(TableInfo) then
        begin
          ColumnInfo.RealSchemaName := TableInfo.SchemaName;
          ColumnInfo.RealTableName := TableInfo.TableName;
        end
        else
        begin
          ColumnInfo.RealSchemaName := '';
          ColumnInfo.RealTableName := '';
        end;
        ColumnInfo.ReferencedTableColumn := ColumnName;
      end
      else
      begin
        // Column without table alias/name prefix
        ColumnInfo.RealSchemaName := '';
        ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName;
        if FTables.Count = 1 then
        begin
          // Assign the only table's name
          ColumnInfo.RealTableName := FTables[0].TableName;
          ColumnInfo.RealSchemaName := FTables[0].SchemaName;
        end
        else
        begin
          // No table information available
          ColumnInfo.RealTableName := '';
        end;
      end;
    end;

    FColumns.Add(ColumnInfo);
  end;
end;

function TSQLParser.IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;
var
  Word: string;
begin
  for Word in ReservedWords do
  begin
    if SameText(Token, Word) then
      Exit(True);
  end;
  Result := False;
end;

function TSQLParser.IsExpression(const S: string): Boolean;
begin
  // Simple heuristic to determine if the string is an expression
  Result := S.Contains('(') or
            S.Contains('+') or
            S.Contains('-') or
            S.Contains('*') or
            S.Contains('/') or
            (S.Contains(' ') and not S.Contains('.'));
end;

function TSQLParser.GetTableInfoByAliasOrName(const AliasOrName: string): TTableInfo;
var
  TableInfo: TTableInfo;
begin
  Result := nil;
  for TableInfo in FTables do
  begin
    if SameText(TableInfo.Alias, AliasOrName) then
    begin
      Result := TableInfo;
      Exit;
    end
    else if (TableInfo.Alias = '') and SameText(TableInfo.TableName, AliasOrName) then
    begin
      Result := TableInfo;
      Exit;
    end;
  end;
end;

function TSQLParser.IsJoinType(const Token: string): Boolean;
const
  JoinTypes: array[0..7] of string = ('JOIN', 'INNER', 'LEFT', 'RIGHT', 'FULL', 'CROSS', 'NATURAL', 'OUTER');
var
  JoinType: string;
begin
  Result := False;
  if Token = '' then Exit;
  for JoinType in JoinTypes do
  begin
    if SameText(Token, JoinType) then
      Exit(True);
  end;
end;

procedure TSQLParser.FillPrimaryKeyDictionary;
var
  Query: TUniQuery;
  TableName, ColumnName, SchemaName: string;
  ColumnsList: TList<string>;
begin
  if not Assigned(FConnection) then
    Exit; // Cannot fill without a database connection

  Query := TUniQuery.Create(nil);
  try
    Query.Connection := FConnection;
    // Use the schema name from the connection if not specified
    SchemaName := FConnection.Database;

    Query.SQL.Text := 'SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE ' +
                      'WHERE TABLE_SCHEMA = :SchemaName AND CONSTRAINT_NAME = ''PRIMARY'' ' +
                      'ORDER BY TABLE_NAME, ORDINAL_POSITION';

    Query.ParamByName('SchemaName').AsString := SchemaName;
    Query.Open;

    while not Query.Eof do
    begin
      TableName := LowerCase(Query.FieldByName('TABLE_NAME').AsString);
      ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);

      if not FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
      begin
        ColumnsList := TList<string>.Create;
        FPrimaryKeys.Add(TableName, ColumnsList);
      end;

      ColumnsList.Add(ColumnName);

      Query.Next;
    end;

    Query.Close;
  finally
    Query.Free;
  end;
end;

procedure TSQLParser.CheckPrimaryKeys;
var
  ColumnInfo: TColumnInfo;
  TableName: string;
  ColumnName: string;
  ColumnsList: TList<string>;
begin
  for ColumnInfo in FColumns do
  begin
    if (ColumnInfo.RealTableName <> '') and (ColumnInfo.RealTableName <> '[none]') and
       (ColumnInfo.ReferencedTableColumn <> '') and (ColumnInfo.ReferencedTableColumn <> '[none]') then
    begin
      TableName := LowerCase(ColumnInfo.RealTableName);
      ColumnName := LowerCase(ColumnInfo.ReferencedTableColumn);

      if FPrimaryKeys.TryGetValue(TableName, ColumnsList) then
      begin
        ColumnInfo.IsPKColumn := ColumnsList.Contains(ColumnName);
      end
      else
      begin
        // Table not found in primary keys, so column is not a primary key
        ColumnInfo.IsPKColumn := False;
      end;
    end
    else
    begin
      // For expressions or columns without table reference
      ColumnInfo.IsPKColumn := False;
    end;
  end;
end;

end.


The is a problem in the SQL parser above:
The table alias is incorrectly parsed if whe use "AS" before the table alias.
For example, if the query is:
SELECT subloc_empresa_id as sub_id,nome_empresa nome_emp,endereco,tel_contato,nxt.pessoa_responsavel FROM centauro024.subloc_empresa my_table_alias ORDER BY sub_id
then my_table_alias is correctly identified as the alias of subloc_empresa.

But if th query is:
SELECT subloc_empresa_id as sub_id,nome_empresa nome_emp,endereco,tel_contato,nxt.pessoa_responsavel FROM centauro024.subloc_empresa AS nxt ORDER BY sub_id
then "AS" is wrongly identified as the alias of subloc_empresa.

Please review the unit and fix it, so it can handle both cases, like it is handled in the ParseSelectClause procedure