unit SQLParserEnhanced; interface uses System.Classes, System.SysUtils,…
- User
unit SQLParserEnhanced;
interface
uses System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions, Uni, System.StrUtils;
type /// /// Represents information about a column in the SQL query. /// TColumnInfo = class private FFullName: string; FRealSchemaName: string; FRealTableName: string; FReferencedTableColumn: string; FQueryColumnAlias: string; FIsPKColumn: Boolean; FDataType: string; public property FullName: string read FFullName write FFullName; property RealSchemaName: string read FRealSchemaName write FRealSchemaName; property RealTableName: string read FRealTableName write FRealTableName; property ReferencedTableColumn: string read FReferencedTableColumn write FReferencedTableColumn; property QueryColumnAlias: string read FQueryColumnAlias write FQueryColumnAlias; property IsPKColumn: Boolean read FIsPKColumn write FIsPKColumn; property DataType: string read FDataType write FDataType; end;
/// /// Represents information about a table used in the SQL query. /// TTableInfo = class private FSchemaName: string; FTableName: string; FAlias: string; public /// /// Schema name of the table. /// property SchemaName: string read FSchemaName write FSchemaName;
/// <summary> /// Name of the table. /// </summary> property TableName: string read FTableName write FTableName; /// <summary> /// Alias of the table as defined in the query. /// </summary> property Alias: string read FAlias write FAlias;end;
/// /// Parses SQL SELECT statements to extract table and column information. /// TSQLParser = class private FSQLText: string; FTables: TObjectList; FColumns: TObjectList; FConnection: TUniConnection; FPrimaryKeys: TDictionary<string, TList>; FTableColumns: TDictionary<string, TList>; // Cache for table columns
procedure Parse; procedure ParseFromClause; procedure ParseSelectClause; function SplitSelectColumns(const SelectClause: string): TArray<string>; 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 GetTableColumnNames(const SchemaName, TableName: string): TList<string>; // Get column names for a table function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean;public procedure AssignDataTypesToColumns; /// /// Initializes a new instance of the TSQLParser class. /// /// The SQL query text to parse. /// The database connection used to retrieve schema information. constructor Create(const SQLText: string; AConnection: TUniConnection); /// /// Frees all resources used by the TSQLParser instance. /// destructor Destroy; override;
/// <summary> /// Gets the list of tables parsed from the SQL query. /// </summary> property Tables: TObjectList<TTableInfo> read FTables; /// <summary> /// Gets the list of columns parsed from the SQL query. /// </summary> property Columns: TObjectList<TColumnInfo> read FColumns;end;
implementation
{ TSQLParser }
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; FTableColumns := TDictionary<string, TList>.Create; Parse; FillPrimaryKeyDictionary; // After parsing, fill the primary key dictionary CheckPrimaryKeys; // Then check for primary key columns using the cache AssignDataTypesToColumns; // Assign data types to columns end;
destructor TSQLParser.Destroy; var ColumnsList: TList; begin // Free the lists inside the dictionaries for ColumnsList in FPrimaryKeys.Values do ColumnsList.Free; FPrimaryKeys.Free;
for ColumnsList in FTableColumns.Values do ColumnsList.Free; FTableColumns.Free;
FTables.Free; FColumns.Free; inherited; end;
procedure TSQLParser.Parse; begin ParseFromClause; ParseSelectClause; end;
procedure TSQLParser.AssignDataTypesToColumns; var ColumnInfo: TColumnInfo; Query: TUniQuery; SchemaName, TableName, ColumnName, DataType: string; begin for ColumnInfo in FColumns do begin if (ColumnInfo.RealTableName <> '') and not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and (ColumnInfo.ReferencedTableColumn <> '') and not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) then begin SchemaName := ColumnInfo.RealSchemaName; TableName := ColumnInfo.RealTableName; ColumnName := ColumnInfo.ReferencedTableColumn;
// Query the information_schema.COLUMNS to get DATA_TYPE Query := TUniQuery.Create(nil); try Query.Connection := FConnection; Query.SQL.Text := 'SELECT DATA_TYPE, COLUMN_TYPE FROM information_schema.COLUMNS ' + 'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName AND COLUMN_NAME = :ColumnName'; Query.ParamByName('SchemaName').AsString := SchemaName; Query.ParamByName('TableName').AsString := TableName; Query.ParamByName('ColumnName').AsString := ColumnName; Query.Open; if not Query.Eof then begin // Use COLUMN_TYPE to get full type info (e.g., 'int(11)') DataType := Query.FieldByName('COLUMN_TYPE').AsString; ColumnInfo.DataType := DataType; end else begin ColumnInfo.DataType := 'unknown'; end; Query.Close; finally Query.Free; end; end else begin ColumnInfo.DataType := 'unknown'; end;end; 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 + 1, MaxInt).Trim;
// 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, considering possible quoted identifiers Tokens := TRegEx.Split(FromClause, '\s+|,(?![^()]*))'); // Split on spaces or commas not within parentheses
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, considering possible quoted identifiers if Pos('.', Token) > 0 then begin var DotPos := LastDelimiter('.', Token); TableInfo.SchemaName := Copy(Token, 1, DotPos - 1).Trim(['"', '`']); TableInfo.TableName := Copy(Token, DotPos + 1, Length(Token)).Trim(['"', '`']); end else begin TableInfo.SchemaName := FConnection.Database; // Assume default schema TableInfo.TableName := Token.Trim(['"', '`']); 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].Trim(['"', '`']); 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.Trim(['"', '`']); Inc(i); // move to the alias end; end; FTables.Add(TableInfo); Inc(i); // move to the next token after processing end;end; end;
function TSQLParser.SplitSelectColumns(const SelectClause: string): TArray; var i: Integer; c: Char; InSingleQuote, InDoubleQuote: Boolean; ParenthesisDepth: Integer; CurrentColumn: string; ColumnsList: TList; begin InSingleQuote := False; InDoubleQuote := False; ParenthesisDepth := 0; CurrentColumn := ''; ColumnsList := TList.Create; try i := 1; while i <= Length(SelectClause) do begin c := SelectClause[i]; if (c = '''') and not InDoubleQuote then begin InSingleQuote := not InSingleQuote; CurrentColumn := CurrentColumn + c; end else if (c = '"') and not InSingleQuote then begin InDoubleQuote := not InDoubleQuote; CurrentColumn := CurrentColumn + c; end else if not InSingleQuote and not InDoubleQuote then begin if c = '(' then begin Inc(ParenthesisDepth); CurrentColumn := CurrentColumn + c; end else if c = ')' then begin Dec(ParenthesisDepth); CurrentColumn := CurrentColumn + c; end else if (c = ',') and (ParenthesisDepth = 0) then begin // Column separator ColumnsList.Add(Trim(CurrentColumn)); CurrentColumn := ''; end else begin CurrentColumn := CurrentColumn + c; end end else begin // Inside quotes CurrentColumn := CurrentColumn + c; end; Inc(i); end; // After the loop, add the last column if CurrentColumn.Trim <> '' then ColumnsList.Add(Trim(CurrentColumn)); Result := ColumnsList.ToArray; finally ColumnsList.Free; 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).Trim;
// 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 := TRegEx.Split(SelectClause, '\s+'); 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 if i >= Length(Tokens) then ColumnsStr := '' else ColumnsStr := String.Join(' ', Copy(Tokens, i, Length(Tokens) - i));
if ColumnsStr.IsEmpty then Exit;
// Use the new SplitSelectColumns function to split columns ColumnList := SplitSelectColumns(ColumnsStr);
for i := 0 to Length(ColumnList) - 1 do begin ColumnStr := Trim(ColumnList[i]);
// Regular expression to extract column expressions and aliases // Handles cases like "expression AS alias", "expression alias", or just "expression" Regex := TRegEx.Create('^(.*?)(?:\s+(?:AS\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; if Match.Groups.Count > 2 then ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim else ColumnInfo.QueryColumnAlias := ''; 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 ColumnInfo.FullName.Contains('.') then begin // Parse table alias/name and column name var DotPos := LastDelimiter('.', ColumnInfo.FullName); TableAliasOrName := Copy(ColumnInfo.FullName, 1, DotPos - 1).Trim(['"', '`']); ColumnName := Copy(ColumnInfo.FullName, DotPos + 1, Length(ColumnInfo.FullName)).Trim(['"', '`']); // 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 // Table alias/name not found; mark as unknown ColumnInfo.RealSchemaName := '[unknown schema]'; ColumnInfo.RealTableName := '[unknown table]'; end; ColumnInfo.ReferencedTableColumn := ColumnName; end else begin // Column without table alias/name prefix ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName.Trim(['"', '`']); ColumnInfo.RealTableName := ''; ColumnInfo.RealSchemaName := ''; if FTables.Count = 1 then begin // Assign the only table's name ColumnInfo.RealTableName := FTables[0].TableName; ColumnInfo.RealSchemaName := FTables[0].SchemaName; end else if FTables.Count > 1 then begin // More than one table: try to find the table(s) that contain this column var PossibleTables := TList<TTableInfo>.Create; try for var TInfo in FTables do begin var ColumnsList := GetTableColumnNames(TInfo.SchemaName, TInfo.TableName); if ColumnsList.Contains(LowerCase(ColumnInfo.ReferencedTableColumn)) then PossibleTables.Add(TInfo); end; if PossibleTables.Count = 1 then begin // Only one table contains the column, assign it ColumnInfo.RealTableName := PossibleTables[0].TableName; ColumnInfo.RealSchemaName := PossibleTables[0].SchemaName; end else if PossibleTables.Count > 1 then begin // Column is ambiguous - exists in multiple tables ColumnInfo.RealTableName := '[ambiguous]'; ColumnInfo.RealSchemaName := '[ambiguous schema]'; end else begin // Column doesn't exist in any known table ColumnInfo.RealTableName := '[unknown]'; ColumnInfo.RealSchemaName := '[unknown schema]'; end; finally PossibleTables.Free; end; end else begin // No table information available ColumnInfo.RealTableName := '[no tables parsed]'; ColumnInfo.RealSchemaName := '[no tables parsed]'; 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 Exit(TableInfo) else if (TableInfo.Alias.IsEmpty) and SameText(TableInfo.TableName, AliasOrName) then Exit(TableInfo); end; end;
function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList; var ColumnsList: TList; Query: TUniQuery; ColumnName: string; Key: string; begin Key := LowerCase(SchemaName + '.' + TableName); if FTableColumns.TryGetValue(Key, ColumnsList) then begin Result := ColumnsList; Exit; end;
// Load column names from the database schema ColumnsList := TList.Create; try Query := TUniQuery.Create(nil); try Query.Connection := FConnection; Query.SQL.Text := 'SELECT COLUMN_NAME FROM information_schema.COLUMNS ' + 'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName'; Query.ParamByName('SchemaName').AsString := SchemaName; Query.ParamByName('TableName').AsString := TableName; Query.Open;
while not Query.Eof do begin ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString); ColumnsList.Add(ColumnName); Query.Next; end; Query.Close; finally Query.Free; end; FTableColumns.Add(Key, ColumnsList); Result := ColumnsList;except ColumnsList.Free; raise; 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.IsEmpty 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 not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and (ColumnInfo.ReferencedTableColumn <> '') and not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) 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.
====================
Change the Delphi unit above, so the AssignDataTypesToColumns procedure populate the datatypes from running the own query being parsed, using Query.Fields. Field.DataType is a TFieldType, like the code snippet below for reference, so it can handle CAST for example. FDataType must be a TFieldType
TFieldType = (ftUnknown, ftString, ftSmallint, ftInteger, ftWord, ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate, ftTime, ftDateTime, ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo, ftGraphic, ftFmtMemo, ftParadoxOle, ftDBaseOle, ftTypedBinary, ftCursor, ftFixedChar, ftWideString, ftLargeint, ftADT, ftArray, ftReference, ftDataSet, ftOraBlob, ftOraClob, ftVariant, ftInterface, ftIDispatch, ftGuid, ftTimeStamp, ftFMTBcd, ftFixedWideChar, ftWideMemo, ftOraTimeStamp, ftOraInterval, ftLongWord, ftShortint, ftByte, ftExtended, ftConnection, ftParams, ftStream, ftTimeStampOffset, ftObject, ftSingle);
// Iterate over fields to get field types: for i := 0 to Query.Fields.Count - 1 do begin Field := Query.Fields[i]; FieldName := Field.FieldName;
case Field.DataType of ftString, ftWideString: DelphiFieldType := 'string'; ftInteger, ftSmallint, ftShortInt, ftWord, ftByte: DelphiFieldType := 'Integer'; ftLargeint: DelphiFieldType := 'Int64'; ftFloat, ftCurrency: DelphiFieldType := 'Double'; ftDate, ftTime, ftDateTime, ftTimeStamp: DelphiFieldType := 'TDateTime'; ftBoolean: DelphiFieldType := 'Boolean'; else DelphiFieldType := 'Variant'; // Default to Variant for unsupported types end;end;
- Assistant
Here is the modified Delphi unit with the
AssignDataTypesToColumnsprocedure updated to run the query being parsed and populate the data types usingQuery.Fields. TheFDataTypeproperty inTColumnInfohas been changed toTFieldType.unit SQLParserEnhanced; interface uses System.Classes, System.SysUtils, System.Generics.Collections, System.RegularExpressions, Uni, System.StrUtils, Data.DB; type /// <summary> /// Represents information about a column in the SQL query. /// </summary> TColumnInfo = class private FFullName: string; FRealSchemaName: string; FRealTableName: string; FReferencedTableColumn: string; FQueryColumnAlias: string; FIsPKColumn: Boolean; FDataType: TFieldType; public property FullName: string read FFullName write FFullName; property RealSchemaName: string read FRealSchemaName write FRealSchemaName; property RealTableName: string read FRealTableName write FRealTableName; property ReferencedTableColumn: string read FReferencedTableColumn write FReferencedTableColumn; property QueryColumnAlias: string read FQueryColumnAlias write FQueryColumnAlias; property IsPKColumn: Boolean read FIsPKColumn write FIsPKColumn; property DataType: TFieldType read FDataType write FDataType; end; /// <summary> /// Represents information about a table used in the SQL query. /// </summary> TTableInfo = class private FSchemaName: string; FTableName: string; FAlias: string; public /// <summary> /// Schema name of the table. /// </summary> property SchemaName: string read FSchemaName write FSchemaName; /// <summary> /// Name of the table. /// </summary> property TableName: string read FTableName write FTableName; /// <summary> /// Alias of the table as defined in the query. /// </summary> property Alias: string read FAlias write FAlias; end; /// <summary> /// Parses SQL SELECT statements to extract table and column information. /// </summary> TSQLParser = class private FSQLText: string; FTables: TObjectList<TTableInfo>; FColumns: TObjectList<TColumnInfo>; FConnection: TUniConnection; FPrimaryKeys: TDictionary<string, TList<string>>; FTableColumns: TDictionary<string, TList<string>>; // Cache for table columns procedure Parse; procedure ParseFromClause; procedure ParseSelectClause; function SplitSelectColumns(const SelectClause: string): TArray<string>; 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 GetTableColumnNames(const SchemaName, TableName: string): TList<string>; // Get column names for a table function IsReservedWord(const Token: string; const ReservedWords: array of string): Boolean; public procedure AssignDataTypesToColumns; /// <summary> /// Initializes a new instance of the TSQLParser class. /// </summary> /// <param name="SQLText">The SQL query text to parse.</param> /// <param name="AConnection">The database connection used to retrieve schema information.</param> constructor Create(const SQLText: string; AConnection: TUniConnection); /// <summary> /// Frees all resources used by the TSQLParser instance. /// </summary> destructor Destroy; override; /// <summary> /// Gets the list of tables parsed from the SQL query. /// </summary> property Tables: TObjectList<TTableInfo> read FTables; /// <summary> /// Gets the list of columns parsed from the SQL query. /// </summary> property Columns: TObjectList<TColumnInfo> read FColumns; end; implementation { TSQLParser } constructor TSQLParser.Create(const SQLText: string; AConnection: TUniConnection); begin inherited Create; FSQLText := SQLText; FConnection := AConnection; // Store the provided database connection FTables := TObjectList<TTableInfo>.Create(True); // Owns objects FColumns := TObjectList<TColumnInfo>.Create(True); // Owns objects FPrimaryKeys := TDictionary<string, TList<string>>.Create; FTableColumns := TDictionary<string, TList<string>>.Create; Parse; FillPrimaryKeyDictionary; // After parsing, fill the primary key dictionary CheckPrimaryKeys; // Then check for primary key columns using the cache AssignDataTypesToColumns; // Assign data types to columns end; destructor TSQLParser.Destroy; var ColumnsList: TList<string>; begin // Free the lists inside the dictionaries for ColumnsList in FPrimaryKeys.Values do ColumnsList.Free; FPrimaryKeys.Free; for ColumnsList in FTableColumns.Values do ColumnsList.Free; FTableColumns.Free; FTables.Free; FColumns.Free; inherited; end; procedure TSQLParser.Parse; begin ParseFromClause; ParseSelectClause; end; procedure TSQLParser.AssignDataTypesToColumns; var Query: TUniQuery; Field: TField; i: Integer; begin Query := TUniQuery.Create(nil); try Query.Connection := FConnection; Query.SQL.Text := FSQLText; try Query.Open; // Iterate over fields to get field types for i := 0 to Query.Fields.Count - 1 do begin if i < FColumns.Count then begin Field := Query.Fields[i]; FColumns[i].DataType := Field.DataType; end else begin // Field count greater than column count, handle as needed Break; end; end; Query.Close; except on E: Exception do begin // Handle exception, e.g., log error message // Optionally set FDataType to ftUnknown for all columns for i := 0 to FColumns.Count - 1 do FColumns[i].DataType := ftUnknown; end; end; finally Query.Free; end; 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<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 + 1, MaxInt).Trim; // 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, considering possible quoted identifiers Tokens := TRegEx.Split(FromClause, '\s+|,(?![^()]*\))'); // Split on spaces or commas not within parentheses 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, considering possible quoted identifiers if Pos('.', Token) > 0 then begin var DotPos := LastDelimiter('.', Token); TableInfo.SchemaName := Copy(Token, 1, DotPos - 1).Trim(['"', '`']); TableInfo.TableName := Copy(Token, DotPos + 1, Length(Token)).Trim(['"', '`']); end else begin TableInfo.SchemaName := FConnection.Database; // Assume default schema TableInfo.TableName := Token.Trim(['"', '`']); 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].Trim(['"', '`']); 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.Trim(['"', '`']); Inc(i); // move to the alias end; end; FTables.Add(TableInfo); Inc(i); // move to the next token after processing end; end; end; function TSQLParser.SplitSelectColumns(const SelectClause: string): TArray<string>; var i: Integer; c: Char; InSingleQuote, InDoubleQuote: Boolean; ParenthesisDepth: Integer; CurrentColumn: string; ColumnsList: TList<string>; begin InSingleQuote := False; InDoubleQuote := False; ParenthesisDepth := 0; CurrentColumn := ''; ColumnsList := TList<string>.Create; try i := 1; while i <= Length(SelectClause) do begin c := SelectClause[i]; if (c = '''') and not InDoubleQuote then begin InSingleQuote := not InSingleQuote; CurrentColumn := CurrentColumn + c; end else if (c = '"') and not InSingleQuote then begin InDoubleQuote := not InDoubleQuote; CurrentColumn := CurrentColumn + c; end else if not InSingleQuote and not InDoubleQuote then begin if c = '(' then begin Inc(ParenthesisDepth); CurrentColumn := CurrentColumn + c; end else if c = ')' then begin Dec(ParenthesisDepth); CurrentColumn := CurrentColumn + c; end else if (c = ',') and (ParenthesisDepth = 0) then begin // Column separator ColumnsList.Add(Trim(CurrentColumn)); CurrentColumn := ''; end else begin CurrentColumn := CurrentColumn + c; end end else begin // Inside quotes CurrentColumn := CurrentColumn + c; end; Inc(i); end; // After the loop, add the last column if CurrentColumn.Trim <> '' then ColumnsList.Add(Trim(CurrentColumn)); Result := ColumnsList.ToArray; finally ColumnsList.Free; end; end; procedure TSQLParser.ParseSelectClause; var SelectMatch, FromMatch: TMatch; SelectClause: string; ColumnsStr: string; ColumnList: TArray<string>; ColumnStr: string; ColumnInfo: TColumnInfo; Regex: TRegEx; Match: TMatch; TableAliasOrName: string; ColumnName: string; TableInfo: TTableInfo; ReservedWords: array of string; Tokens: TArray<string>; 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).Trim; // 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 := TRegEx.Split(SelectClause, '\s+'); 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 if i >= Length(Tokens) then ColumnsStr := '' else ColumnsStr := String.Join(' ', Copy(Tokens, i, Length(Tokens) - i)); if ColumnsStr.IsEmpty then Exit; // Use the new SplitSelectColumns function to split columns ColumnList := SplitSelectColumns(ColumnsStr); for i := 0 to Length(ColumnList) - 1 do begin ColumnStr := Trim(ColumnList[i]); // Regular expression to extract column expressions and aliases // Handles cases like "expression AS alias", "expression alias", or just "expression" Regex := TRegEx.Create('^(.*?)(?:\s+(?:AS\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; if Match.Groups.Count > 2 then ColumnInfo.QueryColumnAlias := Match.Groups[2].Value.Trim else ColumnInfo.QueryColumnAlias := ''; 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 ColumnInfo.FullName.Contains('.') then begin // Parse table alias/name and column name var DotPos := LastDelimiter('.', ColumnInfo.FullName); TableAliasOrName := Copy(ColumnInfo.FullName, 1, DotPos - 1).Trim(['"', '`']); ColumnName := Copy(ColumnInfo.FullName, DotPos + 1, Length(ColumnInfo.FullName)).Trim(['"', '`']); // 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 // Table alias/name not found; mark as unknown ColumnInfo.RealSchemaName := '[unknown schema]'; ColumnInfo.RealTableName := '[unknown table]'; end; ColumnInfo.ReferencedTableColumn := ColumnName; end else begin // Column without table alias/name prefix ColumnInfo.ReferencedTableColumn := ColumnInfo.FullName.Trim(['"', '`']); ColumnInfo.RealTableName := ''; ColumnInfo.RealSchemaName := ''; if FTables.Count = 1 then begin // Assign the only table's name ColumnInfo.RealTableName := FTables[0].TableName; ColumnInfo.RealSchemaName := FTables[0].SchemaName; end else if FTables.Count > 1 then begin // More than one table: try to find the table(s) that contain this column var PossibleTables := TList<TTableInfo>.Create; try for var TInfo in FTables do begin var ColumnsList := GetTableColumnNames(TInfo.SchemaName, TInfo.TableName); if ColumnsList.Contains(LowerCase(ColumnInfo.ReferencedTableColumn)) then PossibleTables.Add(TInfo); end; if PossibleTables.Count = 1 then begin // Only one table contains the column, assign it ColumnInfo.RealTableName := PossibleTables[0].TableName; ColumnInfo.RealSchemaName := PossibleTables[0].SchemaName; end else if PossibleTables.Count > 1 then begin // Column is ambiguous - exists in multiple tables ColumnInfo.RealTableName := '[ambiguous]'; ColumnInfo.RealSchemaName := '[ambiguous schema]'; end else begin // Column doesn't exist in any known table ColumnInfo.RealTableName := '[unknown]'; ColumnInfo.RealSchemaName := '[unknown schema]'; end; finally PossibleTables.Free; end; end else begin // No table information available ColumnInfo.RealTableName := '[no tables parsed]'; ColumnInfo.RealSchemaName := '[no tables parsed]'; 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 Exit(TableInfo) else if (TableInfo.Alias.IsEmpty) and SameText(TableInfo.TableName, AliasOrName) then Exit(TableInfo); end; end; function TSQLParser.GetTableColumnNames(const SchemaName, TableName: string): TList<string>; var ColumnsList: TList<string>; Query: TUniQuery; ColumnName: string; Key: string; begin Key := LowerCase(SchemaName + '.' + TableName); if FTableColumns.TryGetValue(Key, ColumnsList) then begin Result := ColumnsList; Exit; end; // Load column names from the database schema ColumnsList := TList<string>.Create; try Query := TUniQuery.Create(nil); try Query.Connection := FConnection; Query.SQL.Text := 'SELECT COLUMN_NAME FROM information_schema.COLUMNS ' + 'WHERE TABLE_SCHEMA = :SchemaName AND TABLE_NAME = :TableName'; Query.ParamByName('SchemaName').AsString := SchemaName; Query.ParamByName('TableName').AsString := TableName; Query.Open; while not Query.Eof do begin ColumnName := LowerCase(Query.FieldByName('COLUMN_NAME').AsString); ColumnsList.Add(ColumnName); Query.Next; end; Query.Close; finally Query.Free; end; FTableColumns.Add(Key, ColumnsList); Result := ColumnsList; except ColumnsList.Free; raise; 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.IsEmpty 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<string>; 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<string>; begin for ColumnInfo in FColumns do begin if (ColumnInfo.RealTableName <> '') and not (ColumnInfo.RealTableName.StartsWith('[') and ColumnInfo.RealTableName.EndsWith(']')) and (ColumnInfo.ReferencedTableColumn <> '') and not (ColumnInfo.ReferencedTableColumn.StartsWith('[') and ColumnInfo.ReferencedTableColumn.EndsWith(']')) 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.Explanation of Changes:
-
Changed the
FDataTypeProperty:- In the
TColumnInfoclass, theFDataTypeproperty type has been changed fromstringtoTFieldTypeto store the data type as aTFieldType, as required.
- In the
-
Updated the
AssignDataTypesToColumnsProcedure:- The procedure now runs the query being parsed (
FSQLText) using aTUniQueryobject. - It opens the query and iterates over the
Fieldsof the query. - For each field, it assigns the
Field.DataTypeto the correspondingColumnInfo.DataType. - It ensures that the number of fields in the query matches the number of parsed columns (
FColumns). If there's a mismatch, it handles it appropriately. - Exception handling is added to catch any errors when running the query.
- The procedure now runs the query being parsed (
-
Removed Previous Data Type Assignment Logic:
- The previous logic that queried the
information_schema.COLUMNStable to get data types has been removed, as it's no longer needed.
- The previous logic that queried the
-
Exception Handling:
- If the query cannot be executed (e.g., due to an error), the code now assigns
ftUnknownto eachColumnInfo.DataTypeto indicate that the data type could not be determined.
- If the query cannot be executed (e.g., due to an error), the code now assigns
Usage Notes:
- Ensure that the SQL query (
FSQLText) can be safely executed. If the query contains parameters or is not safe to run as-is, additional handling will be necessary. - This code assumes that the fields in the query result correspond to the columns in the parsed SELECT clause in the same order.
- Be cautious when running dynamic SQL queries to prevent SQL injection or unintended side effects.
I hope this modification meets your requirements.
-