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 the unit and fix it, so it can handle both cases, like it is handled in the ParseSelectClause procedure

  2. Assistant

    The issue arises because, in your ParseFromClause method, the parsing logic for aliases when "AS" is used isn't correctly handling the tokens in the FROM clause. Specifically, when "AS" is present, it's mistakenly being set as the alias instead of the actual alias following it.

    To fix this, you need to adjust your parsing logic to correctly handle both cases—when "AS" is used and when it's not. Here's how you can modify your ParseFromClause method:

    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
        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;
    
          Inc(i);
    
          // Now, check if there is an alias
          if i < Length(Tokens) then
          begin
            NextToken := Trim(Tokens[i]);
    
            if SameText(NextToken, 'AS') then
            begin
              Inc(i);
              if i < Length(Tokens) then
              begin
                // The next token is the alias
                TableInfo.Alias := Trim(Tokens[i]);
                Inc(i);
              end;
            end
            else if not (IsJoinType(NextToken) or SameText(NextToken, 'JOIN') or SameText(NextToken, 'ON') or SameText(NextToken, 'USING') or IsClauseKeyword(NextToken) or NextToken = ',') then
            begin
              // The next token is potentially the alias
              // Let's assign it as alias
              TableInfo.Alias := NextToken;
              Inc(i);
            end;
            // else, there is no alias, proceed
          end;
    
          FTables.Add(TableInfo);
        end;
      end;
    end;
    

    Explanation:

    • Processing the Table Name: After identifying the table name and incrementing i, you check for an alias.

    • Handling "AS": If the next token is "AS", increment i and assign the following token as the alias (provided it exists).

    • Handling Alias Without "AS": If the next token isn't a join type, "JOIN", "ON", "USING", a clause keyword, or a comma, it's considered an alias.

    • Adding the TableInfo: After processing, you add the TableInfo to your FTables list.

    With this change, both cases—using "AS" and omitting "AS" before the table alias—are handled correctly.

    Testing the Fix:

    • For the query without "AS":

      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
      

      The my_table_alias will correctly be identified as the alias of centauro024.subloc_empresa.

    • For the query with "AS":

      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
      

      The nxt will now correctly be identified as the alias of centauro024.subloc_empresa.

    Note:

    • Ensure that your token splitting and manipulation correctly handle all possible whitespaces and special characters in SQL syntax.

    • It's advisable to also refactor your code to handle complex SQL statements more robustly, possibly by using a full-fledged SQL parsing library if you're dealing with varied and complex SQL queries regularly.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.