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 the unit and fix it, so it can handle both cases, like it is handled in the ParseSelectClause procedure
- Assistant
The issue arises because, in your
ParseFromClausemethod, 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
ParseFromClausemethod: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
iand 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
TableInfoto yourFTableslist.
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_idThe
my_table_aliaswill correctly be identified as the alias ofcentauro024.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_idThe
nxtwill now correctly be identified as the alias ofcentauro024.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.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.