unit SQLParserEnhanced;

interface

uses
System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions, Uni, System.StrUtils, Data.DB;

type
///

/// Represents information about a column in the SQL query.
///

TColumnInfo = class
private
FFullName: string;
FRealSchemaName: string;
FRealTableName: string;
FReferencedTableColumn: string;
FQueryColumnAlias: string;
FIsPKColumn: Boolean;
FDataType: TFieldType;
public
property FullName: string read FFullName write FFullName;
property RealSchemaName: string read FRealSchemaName write FRealSchemaName;
property RealTableName: string read FRealTableName write FRealTableName;
property ReferencedTableColumn: string read FReferencedTableColumn write FReferencedTableColumn;
property QueryColumnAlias: string read FQueryColumnAlias write FQueryColumnAlias;
property IsPKColumn: Boolean read FIsPKColumn write FIsPKColumn;
property DataType: TFieldType read FDataType write FDataType;
end;

///

/// Represents information about a table used in the SQL query.
///

TTableInfo = class
private
FSchemaName: string;
FTableName: string;
FAlias: string;
public
///

/// Schema name of the table.
///

property SchemaName: string read FSchemaName write FSchemaName;

/// <summary>
/// Name of the table.
/// </summary>
property TableName: string read FTableName write FTableName;

/// <summary>
/// Alias of the table as defined in the query.
/// </summary>
property Alias: string read FAlias write FAlias;

end;

///

/// Parses SQL SELECT statements to extract table and column information.
///

TSQLParser = class
private
FSQLText: string;
FTables: TObjectList;
FColumns: TObjectList;
FConnection: TUniConnection;
FPrimaryKeys: TDictionary<string, TList>;
FTableColumns: TDictionary<string, TList>; // Cache for table columns

procedure Parse;
procedure ParseFromClause;
procedure ParseSelectClause;
function SplitSelectColumns(const SelectClause: string): TArray<string>;
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 GetTableColumnNames(const SchemaName, TableName: string): TList<string>; // Get column names for a table
function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;

public
procedure AssignDataTypesToColumns;
///

/// Initializes a new instance of the TSQLParser class.
///

/// The SQL query text to parse.
/// The database connection used to retrieve schema information.
constructor Create(const SQLText: string; AConnection: TUniConnection);
///

/// Frees all resources used by the TSQLParser instance.
///

destructor Destroy; override;

/// <summary>
/// Gets the list of tables parsed from the SQL query.
/// </summary>
property Tables: TObjectList<TTableInfo> read FTables;

/// <summary>
/// Gets the list of columns parsed from the SQL query.
/// </summary>
property Columns: TObjectList<TColumnInfo> read FColumns;

end;

implementation

{ TSQLParser }

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

destructor TSQLParser.Destroy;
var
ColumnsList: TList;
begin
// Free the lists inside the dictionaries
for ColumnsList in FPrimaryKeys.Values do
ColumnsList.Free;
FPrimaryKeys.Free;

for ColumnsList in FTableColumns.Values do
ColumnsList.Free;
FTableColumns.Free;

FTables.Free;
FColumns.Free;
inherited;
end;

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

procedure TSQLParser.AssignDataTypesToColumns;
var
Query: TUniQuery;
Field: TField;
ColumnInfo: TColumnInfo;
FieldName: string;
begin
Query := TUniQuery.Create(nil);
try
Query.Connection := FConnection;
Query.SQL.Text := FSQLText;
try
Query.Open;
// Iterate over fields to get field types
for Field in Query.Fields do
begin
FieldName := Field.FieldName;
// Try to find matching ColumnInfo by QueryColumnAlias or FullName
for ColumnInfo in FColumns do
begin
if SameText(ColumnInfo.QueryColumnAlias, FieldName) or
SameText(ColumnInfo.FullName, FieldName) or
SameText(ColumnInfo.ReferencedTableColumn, FieldName) then
begin
ColumnInfo.DataType := Field.DataType;
Break;
end;
end;
end;
Query.Close;
except
on E: Exception do
begin
// Handle exception, e.g., log error message
// Optionally set DataType to ftUnknown for all columns
for ColumnInfo in FColumns do
ColumnInfo.DataType := ftUnknown;
end;
end;
finally
Query.Free;
end;
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;
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 + 1, MaxInt).Trim;

// 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, considering possible quoted identifiers
Tokens := TRegEx.Split(FromClause, '\s+|,(?![^()]*))'); // Split on spaces or commas not within parentheses

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
begin
  // Process table name (may include schema)
  TableInfo := TTableInfo.Create;

  // Check for schema.table format, considering possible quoted identifiers
  if Pos('.', Token) > 0 then
  begin
    var DotPos := LastDelimiter('.', Token);
    TableInfo.SchemaName := Copy(Token, 1, DotPos - 1).Trim(['"', '`']);
    TableInfo.TableName := Copy(Token, DotPos + 1, Length(Token)).Trim(['"', '`']);
  end
  else
  begin
    TableInfo.SchemaName := FConnection.Database; // Assume default schema
    TableInfo.TableName := Token.Trim(['"', '`']);
  end;

  // Check for alias
  if (i + 1) < Length(Tokens) then
  begin
    NextToken := Tokens[i + 1];
    if SameText(NextToken, 'AS') then
    begin
      // Alias after 'AS' keyword
      if (i + 2) < Length(Tokens) then
      begin
        TableInfo.Alias := Tokens[i + 2].Trim(['"', '`']);
        i := i + 2; // skip 'AS' and the alias
      end
      else
      begin
        // 'AS' without alias - skip 'AS'
        i := i + 1;
      end;
    end
    else if not (IsJoinType(NextToken) or SameText(NextToken, 'JOIN') or SameText(NextToken, 'ON')
      or SameText(NextToken, 'USING') or IsClauseKeyword(NextToken)) then
    begin
      // Alias without 'AS'
      TableInfo.Alias := NextToken.Trim(['"', '`']);
      Inc(i); // move to the alias
    end;
  end;

  FTables.Add(TableInfo);

  Inc(i); // move to the next token after processing
end;

end;
end;

function TSQLParser.SplitSelectColumns(const SelectClause: string): TArray;
var
i: Integer;
c: Char;
InSingleQuote, InDoubleQuote: Boolean;
ParenthesisDepth: Integer;
CurrentColumn: string;
ColumnsList: TList;
begin
InSingleQuote := False;
InDoubleQuote := False;
ParenthesisDepth := 0;
CurrentColumn := '';
ColumnsList := TList.Create;
try
i := 1;
while i <= Length(SelectClause) do
begin
c := SelectClause[i];
if (c = '''') and not InDoubleQuote then
begin
InSingleQuote := not InSingleQuote;
CurrentColumn := CurrentColumn + c;
end
else if (c = '"') and not InSingleQuote then
begin
InDoubleQuote := not InDoubleQuote;
CurrentColumn := CurrentColumn + c;
end
else if not InSingleQuote and not InDoubleQuote then
begin
if c = '(' then
begin
Inc(ParenthesisDepth);
CurrentColumn := CurrentColumn + c;
end
else if c = ')' then
begin
Dec(ParenthesisDepth);
CurrentColumn := CurrentColumn + c;
end
else if (c = ',') and (ParenthesisDepth = 0) then
begin
// Column separator
ColumnsList.Add(Trim(CurrentColumn));
CurrentColumn := '';
end
else
begin
CurrentColumn := CurrentColumn + c;
end
end
else
begin
// Inside quotes
CurrentColumn := CurrentColumn + c;
end;
Inc(i);
end;
// After the loop, add the last column
if CurrentColumn.Trim <> '' then
ColumnsList.Add(Trim(CurrentColumn));
Result := ColumnsList.ToArray;
finally
ColumnsList.Free;
end;
end;

procedure TSQLParser.ParseSelectClause;
var
SelectMatch, FromMatch: TMatch;
SelectClause: string;
ColumnsStr: string;
ColumnList: TArray;
ColumnStr: string;
ColumnInfo: TColumnInfo;
Regex: TRegEx;
Match: TMatch;
TableAliasOrName: string;
ColumnName: string;
TableInfo: TTableInfo;
ReservedWords: array of string;
Tokens: TArray;
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).Trim;

// 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 := TRegEx.Split(SelectClause, '\s+');
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
if i >= Length(Tokens) then
ColumnsStr := ''
else
ColumnsStr := String.Join(' ', Copy(Tokens, i, Length(Tokens) - i));

if ColumnsStr.IsEmpty then Exit;

// Use the new SplitSelectColumns function to split columns
ColumnList := SplitSelectColumns(ColumnsStr);

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

// Regular expression to extract column expressions and aliases
// Handles cases like "expression AS alias", "expression alias", or just "expression"
Regex := TRegEx.Create('^(.*?)(?:\s+(?:AS\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;
  if Match.Groups.Count > 2 then
    ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim
  else
    ColumnInfo.QueryColumnAlias := '';
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 ColumnInfo.FullName.Contains('.') then
  begin
    // Parse table alias/name and column name
    var DotPos := LastDelimiter('.', ColumnInfo.FullName);
    TableAliasOrName := Copy(ColumnInfo.FullName, 1, DotPos - 1).Trim(['"', '`']);
    ColumnName := Copy(ColumnInfo.FullName, DotPos + 1, Length(ColumnInfo.FullName)).Trim(['"', '`']);

    // 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
      // Table alias/name not found; mark as unknown
      ColumnInfo.RealSchemaName := '[unknown schema]';
      ColumnInfo.RealTableName := '[unknown table]';
    end;
    ColumnInfo.ReferencedTableColumn := ColumnName;
  end
  else
  begin
    // Column without table alias/name prefix
    ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName.Trim(['"', '`']);
    ColumnInfo.RealTableName := '';
    ColumnInfo.RealSchemaName := '';

    if FTables.Count = 1 then
    begin
      // Assign the only table's name
      ColumnInfo.RealTableName := FTables[0].TableName;
      ColumnInfo.RealSchemaName := FTables[0].SchemaName;
    end
    else if FTables.Count > 1 then
    begin
      // More than one table: try to find the table(s) that contain this column
      var PossibleTables := TList<TTableInfo>.Create;
      try
        for var TInfo in FTables do
        begin
          var ColumnsList := GetTableColumnNames(TInfo.SchemaName, TInfo.TableName);
          if ColumnsList.Contains(LowerCase(ColumnInfo.ReferencedTableColumn)) then
            PossibleTables.Add(TInfo);
        end;

        if PossibleTables.Count = 1 then
        begin
          // Only one table contains the column, assign it
          ColumnInfo.RealTableName := PossibleTables[0].TableName;
          ColumnInfo.RealSchemaName := PossibleTables[0].SchemaName;
        end
        else if PossibleTables.Count > 1 then
        begin
          // Column is ambiguous - exists in multiple tables
          ColumnInfo.RealTableName := '[ambiguous]';
          ColumnInfo.RealSchemaName := '[ambiguous schema]';
        end
        else
        begin
          // Column doesn't exist in any known table
          ColumnInfo.RealTableName := '[unknown]';
          ColumnInfo.RealSchemaName := '[unknown schema]';
        end;
      finally
        PossibleTables.Free;
      end;
    end
    else
    begin
      // No table information available
      ColumnInfo.RealTableName := '[no tables parsed]';
      ColumnInfo.RealSchemaName := '[no tables parsed]';
    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;
var
Value: string;
ReservedFunctions: array of string;
begin
Value := Trim(S);

// Check for string literal (enclosed in single quotes)
if (Value.StartsWith('''') and Value.EndsWith('''')) then
Exit(True);

// Check for string literal (enclosed in double quotes)
if (Value.StartsWith('"') and Value.EndsWith('"')) then
Exit(True);

// Check for numeric literal
if TRegEx.IsMatch(Value, '^-?\d+(.\d+)?$') then
Exit(True);

// Check for reserved SQL functions
ReservedFunctions := ['CURRENT_TIMESTAMP', 'NOW', 'SYSDATE', 'GETDATE', 'CURDATE', 'CURRENT_DATE', 'CURRENT_TIME'];

if IsReservedWord(Value, ReservedFunctions) then
Exit(True);

// Existing heuristic for expressions involving operators or parentheses
Result := Value.Contains('(') or
Value.Contains('+') or
Value.Contains('-') or
Value.Contains('*') or
Value.Contains('/') or
(Value.Contains(' ') and not Value.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
Exit(TableInfo)
else if (TableInfo.Alias.IsEmpty) and SameText(TableInfo.TableName, AliasOrName) then
Exit(TableInfo);
end;
end;

function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList;
var
ColumnsList: TList;
Query: TUniQuery;
ColumnName: string;
Key: string;
begin
Key := LowerCase(SchemaName + '.' + TableName);
if FTableColumns.TryGetValue(Key, ColumnsList) then
begin
Result := ColumnsList;
Exit;
end;

// Load column names from the database schema
ColumnsList := TList.Create;
try
Query := TUniQuery.Create(nil);
try
Query.Connection := FConnection;
Query.SQL.Text := 'SELECT COLUMN_NAME FROM information_schema.COLUMNS ' +
'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName';
Query.ParamByName('SchemaName').AsString := SchemaName;
Query.ParamByName('TableName').AsString := TableName;
Query.Open;

  while not Query.Eof do
  begin
    ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString);
    ColumnsList.Add(ColumnName);
    Query.Next;
  end;
  Query.Close;
finally
  Query.Free;
end;
FTableColumns.Add(Key, ColumnsList);
Result := ColumnsList;

except
ColumnsList.Free;
raise;
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.IsEmpty 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;
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;
begin
for ColumnInfo in FColumns do
begin
if (ColumnInfo.RealTableName <> '') and
not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and
(ColumnInfo.ReferencedTableColumn <> '') and
not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) 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.

function GenerateDelphiClassFromQuery(const SQLText: string; const ClassName: string; AConnection: TUniConnection): string;
var
Parser: TSQLParser;
ColumnInfo: TColumnInfo;
i: Integer;
FieldsList: TObjectList;
TableAttr, PKAttr, AttrsLine: string;
FieldName, PropertyName: string;
DelphiFieldType: string;
ResultList: TStringList;

function CreateValidIdentifier(const Name: string): string;
var
i: Integer;
c: Char;
begin
Result := '';
for i := 1 to Length(Name) do
begin
c := Name[i];
if c.IsLetterOrDigit or (c = '') then
Result := Result + c
else
Result := Result + '';
end;

// Ensure the identifier does not start with a digit or invalid character
if (Result = '') or not (Result[1].IsLetter or (Result[1] = '_')) then
  Result := '_' + Result;

end;

begin
ResultList := TStringList.Create;
try
ResultList.Add('type');
ResultList.Add(Format(' T%s = class', [ClassName]));
ResultList.Add(' private');

Parser := TSQLParser.Create(SQLText, AConnection);
try
  FieldsList := Parser.Columns;  // Get the columns info

  // Iterate over the columns to generate field declarations
  for i := 0 to FieldsList.Count - 1 do
  begin
    ColumnInfo := FieldsList[i];

    // Map TFieldType to Delphi types
    case ColumnInfo.DataType of
      ftString, ftWideString:
        DelphiFieldType := 'string';
      ftInteger, ftSmallint, ftShortInt, ftWord, ftByte:
        DelphiFieldType := 'Integer';
      ftLargeint:
        DelphiFieldType := 'Int64';
      ftFloat, ftCurrency, ftBCD, ftFMTBcd:
        DelphiFieldType := 'Double';
      ftDate, ftTime, ftDateTime, ftTimeStamp:
        DelphiFieldType := 'TDateTime';
      ftBoolean:
        DelphiFieldType := 'Boolean';
      else
        DelphiFieldType := 'Variant'; // Default to Variant for unsupported types
    end;

    // Use QueryColumnAlias as the field name or the column name
    if ColumnInfo.QueryColumnAlias <> '' then
      FieldName := ColumnInfo.QueryColumnAlias
    else
      FieldName := ColumnInfo.ReferencedTableColumn;

    // Sanitize the FieldName (remove special characters, etc.)
    FieldName := CreateValidIdentifier(FieldName);

    // Build custom attributes
    if not ColumnInfo.RealTableName.IsEmpty and
       not ((ColumnInfo.RealTableName = '[none]') or (ColumnInfo.RealTableName = '[ambiguous]') or (ColumnInfo.RealTableName = '[unknown table]')) then
    begin
      // Insert Table attribute
      TableAttr := Format('Table(''%s'')', [ColumnInfo.RealTableName]);
    end
    else
    begin
      TableAttr := '';
    end;

    if ColumnInfo.IsPKColumn then
    begin
      PKAttr := 'PrimaryKey';
    end
    else
    begin
      PKAttr := '';
    end;

    // Combine attributes
    if (TableAttr <> '') and (PKAttr <> '') then
      AttrsLine := Format('    [%s, %s]', [TableAttr, PKAttr])
    else if (TableAttr <> '') then
      AttrsLine := Format('    [%s]', [TableAttr])
    else if (PKAttr <> '') then
      AttrsLine := Format('    [%s]', [PKAttr])
    else
      AttrsLine := '';

    // Add field declaration
    if AttrsLine <> '' then
      ResultList.Add(Format('%s F%s: %s;', [AttrsLine, FieldName, DelphiFieldType]))
    else
      ResultList.Add(Format('    F%s: %s;', [FieldName, DelphiFieldType]));
  end;

  // Generate public property declarations
  ResultList.Add('  public');
  for i := 0 to FieldsList.Count - 1 do
  begin
    ColumnInfo := FieldsList[i];

    // Use QueryColumnAlias as the field name or the column name
    if ColumnInfo.QueryColumnAlias <> '' then
      FieldName := ColumnInfo.QueryColumnAlias
    else
      FieldName := ColumnInfo.ReferencedTableColumn;

    // Sanitize FieldName
    FieldName := CreateValidIdentifier(FieldName);

    // Create property name (could be same as FieldName)
    PropertyName := FieldName;

    ResultList.Add(Format('    property %s: %s read F%s write F%s;', [PropertyName, DelphiFieldType, FieldName, FieldName]));
  end;

  ResultList.Add('  end;');
  ResultList.Add('');
finally
  Parser.Free;
end;

Result := ResultList.Text;

finally
ResultList.Free;
end;
end;

Adapt GenerateDelphiClassFromQuery to add a Custom Attribute name "ReferencedColumn" with the ColumnInfo.ReferencedTableColumn when is not [none]