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

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

  1. User

    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; FColumns: TObjectList; FConnection: TUniConnection; FPrimaryKeys: TDictionary<string, TList>; 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 read FTables; property Columns: TObjectList 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.Create(True); // Owns objects FColumns := TObjectList.Create(True); // Owns objects FPrimaryKeys := TDictionary<string, TList>.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; 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; 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; 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); 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; 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 (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 carefully the unit and fix it, so it can handle both cases, like it is handled in the ParseSelectClause procedure

  2. Assistant

    To fix the issue with the AS keyword being incorrectly assigned as the table alias in the ParseFromClause method, you need to adjust how the parser handles aliases. Specifically, you should integrate the handling of the AS keyword into the alias parsing logic, similar to how it's done in the ParseSelectClause method.

    Here's how you can modify the ParseFromClause method:

    1. Remove the separate AS handling block: The current else if SameText(Token, 'AS') then block is causing the issue because it treats AS as the alias rather than recognizing it as a keyword indicating that an alias follows. By removing this block, you can avoid incorrectly assigning AS as the alias.

    2. Adjust the alias parsing logic: Within the else block where you process the table name, update the code that checks for aliases to handle both cases—when AS is used and when it's not. This involves checking if the next token is AS, and if so, assigning the following token as the alias. If AS is not present, you can check if the next token is not a keyword, in which case it's the alias.

    Here's the modified ParseFromClause method with the necessary changes:

    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
        // Remove this block to prevent incorrectly assigning 'AS' as alias
        // 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 SameText(NextToken, 'AS') then
            begin
              // Alias after 'AS' keyword
              if (i + 2) < Length(Tokens) then
              begin
                TableInfo.Alias := Tokens[i + 2];
                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;
              Inc(i); // move to the alias
            end;
          end;
    
          FTables.Add(TableInfo);
    
          Inc(i); // move to the next token after processing
        end;
      end;
    end;
    

    Explanation of Changes:

    • Removed the separate 'AS' handling block: By removing the else if SameText(Token, 'AS') then block, we prevent the parser from incorrectly treating AS as the alias. This block was causing AS to be assigned as the alias when it should be recognized as a keyword.

    • Adjusted alias parsing logic: In the alias checking code within the else block, we added a condition to check if the next token is AS. If it is, we expect the following token to be the alias. If AS is not present, we check if the next token is not a keyword, and if so, treat it as the alias.

      if SameText(NextToken, 'AS') then
      begin
        // Alias after 'AS' keyword
        if (i + 2) < Length(Tokens) then
        begin
          TableInfo.Alias := Tokens[i + 2];
          i := i + 2; // skip 'AS' and the alias
        end
        else
        begin
          // 'AS' without alias - skip 'AS'
          i := i + 1;
        end;
      end
      

    By making these changes, the parser will correctly handle both cases:

    • When the alias is specified with AS (e.g., FROM centauro024.subloc_empresa AS nxt)
    • When the alias is specified without AS (e.g., FROM centauro024.subloc_empresa my_table_alias)

    Testing the Changes:

    • Case 1 (With AS):

      • Query: FROM centauro024.subloc_empresa AS nxt
      • Tokens: ['centauro024.subloc_empresa', 'AS', 'nxt']
      • Parsing logic:
        • Token = 'centauro024.subloc_empresa'
        • Next token is AS, so the alias is the following token nxt.
        • TableInfo.Alias is correctly set to nxt.
    • Case 2 (Without AS):

      • Query: FROM centauro024.subloc_empresa my_table_alias
      • Tokens: ['centauro024.subloc_empresa', 'my_table_alias']
      • Parsing logic:
        • Token = 'centauro024.subloc_empresa'
        • Next token is not a keyword, so it's treated as the alias.
        • TableInfo.Alias is correctly set to my_table_alias.

    Conclusion:

    By integrating the handling of the AS keyword into the alias parsing within the else block and removing the separate AS handling block, the parser now correctly identifies table aliases in both scenarios. This adjustment aligns the ParseFromClause method's behavior with that of the ParseSelectClause method, ensuring consistent and accurate parsing of SQL queries regardless of whether AS is used before the table alias.