Skip to content
TheChatArchiveThe Chat Archive
o1-preview-2024-09-12· OpenAIAi2 WildChat-4.8M (AI2)

unit SQLParserEnhanced; interface uses System.Classes, System.SysUtils,…

  1. User

    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]

  2. Assistant

    To adapt the GenerateDelphiClassFromQuery function to add a custom attribute named ReferencedColumn with the ColumnInfo.ReferencedTableColumn when it is not [none], you can modify the function as follows:

    1. Include a new variable RefdColAttr: string; to store the ReferencedColumn attribute.
    2. Check if ColumnInfo.ReferencedTableColumn is not [none], and if so, set RefdColAttr to include the ReferencedColumn attribute.
    3. Modify the logic for combining attributes to include the new RefdColAttr.
    4. Use a list to collect all non-empty attributes and then join them together.

    Here's the modified GenerateDelphiClassFromQuery function:

    function GenerateDelphiClassFromQuery(const SQLText: string; const ClassName: string; AConnection: TUniConnection): string;
    var
      Parser: TSQLParser;
      ColumnInfo: TColumnInfo;
      i: Integer;
      FieldsList: TObjectList<TColumnInfo>;
      TableAttr, PKAttr, RefdColAttr, AttrsLine: string;
      FieldName, PropertyName: string;
      DelphiFieldType: string;
      ResultList: TStringList;
      AttrList: TList<string>;
    
      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;
    
            // Insert ReferencedColumn attribute if applicable
            if ColumnInfo.ReferencedTableColumn <> '[none]' then
            begin
              RefdColAttr := Format('ReferencedColumn(''%s'')', [ColumnInfo.ReferencedTableColumn]);
            end
            else
            begin
              RefdColAttr := '';
            end;
    
            // Combine attributes
            AttrList := TList<string>.Create;
            try
              if TableAttr <> '' then
                AttrList.Add(TableAttr);
              if PKAttr <> '' then
                AttrList.Add(PKAttr);
              if RefdColAttr <> '' then
                AttrList.Add(RefdColAttr);
    
              if AttrList.Count > 0 then
                AttrsLine := '    [' + String.Join(', ', AttrList.ToArray) + ']'
              else
                AttrsLine := '';
            finally
              AttrList.Free;
            end;
    
            // 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;
    

    With this modification, whenever ColumnInfo.ReferencedTableColumn is not [none], the ReferencedColumn attribute will be added to the field declaration. The attributes are combined using a list to handle any number of attributes dynamically.

    Explanation:

    • Step 1: Declare a new variable RefdColAttr: string; to hold the ReferencedColumn attribute.
    • Step 2: Check if ColumnInfo.ReferencedTableColumn is not [none]. If it's not, format the ReferencedColumn attribute with the actual column name.
      if ColumnInfo.ReferencedTableColumn <> '[none]' then
      begin
        RefdColAttr := Format('ReferencedColumn(''%s'')', [ColumnInfo.ReferencedTableColumn]);
      end
      else
      begin
        RefdColAttr := '';
      end;
      
    • Step 3: Use a TList<string> (named AttrList) to collect all non-empty attributes (TableAttr, PKAttr, RefdColAttr).
      AttrList := TList<string>.Create;
      try
        if TableAttr <> '' then
          AttrList.Add(TableAttr);
        if PKAttr <> '' then
          AttrList.Add(PKAttr);
        if RefdColAttr <> '' then
          AttrList.Add(RefdColAttr);
      
    • Step 4: Join the attributes in AttrList using ', ' as a separator, and format them into AttrsLine.
        if AttrList.Count > 0 then
          AttrsLine := '    [' + String.Join(', ', AttrList.ToArray) + ']'
        else
          AttrsLine := '';
      finally
        AttrList.Free;
      end;
      
    • Step 5: Use AttrsLine when adding the field declaration to include all the attributes.

    Note: This approach allows for flexibility in the number of attributes and ensures that only non-empty attributes are included.

    Usage Example:

    Suppose you have a SQL query like:

    SELECT id, name, created_at FROM users;
    

    And id is a primary key, the generated Delphi class might look like:

    type
      TUser = class
      private
        [Table('users'), PrimaryKey, ReferencedColumn('id')] Fid: Integer;
        [Table('users'), ReferencedColumn('name')] Fname: string;
        [Table('users'), ReferencedColumn('created_at')] Fcreated_at: TDateTime;
      public
        property id: Integer read Fid write Fid;
        property name: string read Fname write Fname;
        property created_at: TDateTime read Fcreated_at write Fcreated_at;
      end;