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.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.
- Assistant
To enhance the
TSQLParserso 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 theParseSelectClauseprocedure.Here's how you can do it:
-
Modify the Parsing Logic for Columns without Table Names:
In the
ParseSelectClauseprocedure, when you're processing columns that don't have an explicit table name, you should check if there's only one table in theFTableslist. If so, assign that table's name toColumnInfo.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; -
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 aliasandcolumn aliasformats. -
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, ',(?![^()]*\))'); -
Put It All Together:
Here's the full modified
ParseSelectClauseprocedure 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
TSQLParsershould 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.
-