unit SQLParser; interface uses System.Classes, System.SysUtils,…
- 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
- Assistant
To fix the issue with the
ASkeyword being incorrectly assigned as the table alias in theParseFromClausemethod, you need to adjust how the parser handles aliases. Specifically, you should integrate the handling of theASkeyword into the alias parsing logic, similar to how it's done in theParseSelectClausemethod.Here's how you can modify the
ParseFromClausemethod:-
Remove the separate
AShandling block: The currentelse if SameText(Token, 'AS') thenblock is causing the issue because it treatsASas the alias rather than recognizing it as a keyword indicating that an alias follows. By removing this block, you can avoid incorrectly assigningASas the alias. -
Adjust the alias parsing logic: Within the
elseblock where you process the table name, update the code that checks for aliases to handle both cases—whenASis used and when it's not. This involves checking if the next token isAS, and if so, assigning the following token as the alias. IfASis not present, you can check if the next token is not a keyword, in which case it's the alias.
Here's the modified
ParseFromClausemethod 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') thenblock, we prevent the parser from incorrectly treatingASas the alias. This block was causingASto be assigned as the alias when it should be recognized as a keyword. -
Adjusted alias parsing logic: In the alias checking code within the
elseblock, we added a condition to check if the next token isAS. If it is, we expect the following token to be the alias. IfASis 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 tokennxt. TableInfo.Aliasis correctly set tonxt.
- Query:
-
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.Aliasis correctly set tomy_table_alias.
- Query:
Conclusion:
By integrating the handling of the
ASkeyword into the alias parsing within theelseblock and removing the separateAShandling block, the parser now correctly identifies table aliases in both scenarios. This adjustment aligns theParseFromClausemethod's behavior with that of theParseSelectClausemethod, ensuring consistent and accurate parsing of SQL queries regardless of whetherASis used before the table alias. -