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.RealTableName := '';
        ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName;
      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.

    Given the Delphi unit above, enhance it so it can handle SELECT queries like this: SELECT subloc_empresa_id, as emp_id,nome_empresa,endereco,tel_contato,pessoa_responsavel FROM subloc_empresa;

    if there is only one table in the query, it should assume that the TableName of all columns is the mentioned table on the SQL query.

  2. Assistant

    To enhance the TSQLParser so it can handle SELECT queries like the one you provided and assume the table name when there's only one table in the query, you need to modify the ParseSelectClause procedure.

    Here's how you can do it:

    1. Modify the Parsing Logic for Columns without Table Names:

      In the ParseSelectClause procedure, when you're processing columns that don't have an explicit table name, you should check if there's only one table in the FTables list. If so, assign that table's name to ColumnInfo.RealTableName.

      Here's the modified code snippet:

      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;
      
    2. Improve Column Alias Parsing:

      The regular expression used to parse column aliases may not handle all cases correctly. You can improve it to handle aliases more reliably. Here's an improved version:

      // 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;
      

      This regex captures both column AS alias and column alias formats.

    3. Handle Commas Inside Parentheses or Quotes:

      When splitting the SELECT clause into individual columns, ensure that commas inside parentheses or quotes are not treated as separators. You can adjust the splitting logic accordingly.

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

      Here's the full modified ParseSelectClause procedure incorporating the enhancements:

      procedure TSQLParser.ParseSelectClause;
      var
        // ... [other variables]
      begin
        // ... [existing code up to splitting columns]
        
        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
              // ... [existing code]
            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;
      

    After making these changes, your TSQLParser should correctly handle queries like:

    SELECT subloc_empresa_id AS emp_id, nome_empresa, endereco, tel_contato, pessoa_responsavel FROM subloc_empresa;
    

    Now, when there's only one table in the query, the parser will assume that all columns belong to that table if no table is specified.