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, Data.DB;
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: 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;
/// /// 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 Query: TUniQuery; Field: TField; ColumnInfo: TColumnInfo; FieldName: string; begin Query := TUniQuery.Create(nil); try Query.Connection := FConnection; Query.SQL.Text := FSQLText; try Query.Open; // Iterate over fields to get field types for Field in Query.Fields do begin FieldName := Field.FieldName; // Try to find matching ColumnInfo by QueryColumnAlias or FullName for ColumnInfo in FColumns do begin if SameText(ColumnInfo.QueryColumnAlias, FieldName) or SameText(ColumnInfo.FullName, FieldName) or SameText(ColumnInfo.ReferencedTableColumn, FieldName) then begin ColumnInfo.DataType := Field.DataType; Break; end; end; end; Query.Close; except on E: Exception do begin // Handle exception, e.g., log error message // Optionally set DataType to ftUnknown for all columns for ColumnInfo in FColumns do ColumnInfo.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; 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; var Value: string; ReservedFunctions: array of string; begin Value := Trim(S);
// Check for string literal (enclosed in single quotes) if (Value.StartsWith('''') and Value.EndsWith('''')) then Exit(True);
// Check for string literal (enclosed in double quotes) if (Value.StartsWith('"') and Value.EndsWith('"')) then Exit(True);
// Check for numeric literal if TRegEx.IsMatch(Value, '^-?\d+(.\d+)?$') then Exit(True);
// Check for reserved SQL functions ReservedFunctions := ['CURRENT_TIMESTAMP', 'NOW', 'SYSDATE', 'GETDATE', 'CURDATE', 'CURRENT_DATE', 'CURRENT_TIME'];
if IsReservedWord(Value, ReservedFunctions) then Exit(True);
// Existing heuristic for expressions involving operators or parentheses Result := Value.Contains('(') or Value.Contains('+') or Value.Contains('-') or Value.Contains('*') or Value.Contains('/') or (Value.Contains(' ') and not Value.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.
function GenerateDelphiClassFromQuery(const SQLText: string; const ClassName: string; AConnection: TUniConnection): string; var Parser: TSQLParser; ColumnInfo: TColumnInfo; i: Integer; FieldsList: TObjectList; TableAttr, PKAttr, AttrsLine: string; FieldName, PropertyName: string; DelphiFieldType: string; ResultList: TStringList;
function CreateValidIdentifier(const Name: string): string; var i: Integer; c: Char; begin Result := ''; for i := 1 to Length(Name) do begin c := Name[i]; if c.IsLetterOrDigit or (c = '') then Result := Result + c else Result := Result + ''; end;
// Ensure the identifier does not start with a digit or invalid character if (Result = '') or not (Result[1].IsLetter or (Result[1] = '_')) then Result := '_' + Result;end;
begin ResultList := TStringList.Create; try ResultList.Add('type'); ResultList.Add(Format(' T%s = class', [ClassName])); ResultList.Add(' private');
Parser := TSQLParser.Create(SQLText, AConnection); try FieldsList := Parser.Columns; // Get the columns info // Iterate over the columns to generate field declarations for i := 0 to FieldsList.Count - 1 do begin ColumnInfo := FieldsList[i]; // Map TFieldType to Delphi types case ColumnInfo.DataType of ftString, ftWideString: DelphiFieldType := 'string'; ftInteger, ftSmallint, ftShortInt, ftWord, ftByte: DelphiFieldType := 'Integer'; ftLargeint: DelphiFieldType := 'Int64'; ftFloat, ftCurrency, ftBCD, ftFMTBcd: DelphiFieldType := 'Double'; ftDate, ftTime, ftDateTime, ftTimeStamp: DelphiFieldType := 'TDateTime'; ftBoolean: DelphiFieldType := 'Boolean'; else DelphiFieldType := 'Variant'; // Default to Variant for unsupported types end; // Use QueryColumnAlias as the field name or the column name if ColumnInfo.QueryColumnAlias <> '' then FieldName := ColumnInfo.QueryColumnAlias else FieldName := ColumnInfo.ReferencedTableColumn; // Sanitize the FieldName (remove special characters, etc.) FieldName := CreateValidIdentifier(FieldName); // Build custom attributes if not ColumnInfo.RealTableName.IsEmpty and not ((ColumnInfo.RealTableName = '[none]') or (ColumnInfo.RealTableName = '[ambiguous]') or (ColumnInfo.RealTableName = '[unknown table]')) then begin // Insert Table attribute TableAttr := Format('Table(''%s'')', [ColumnInfo.RealTableName]); end else begin TableAttr := ''; end; if ColumnInfo.IsPKColumn then begin PKAttr := 'PrimaryKey'; end else begin PKAttr := ''; end; // Combine attributes if (TableAttr <> '') and (PKAttr <> '') then AttrsLine := Format(' [%s, %s]', [TableAttr, PKAttr]) else if (TableAttr <> '') then AttrsLine := Format(' [%s]', [TableAttr]) else if (PKAttr <> '') then AttrsLine := Format(' [%s]', [PKAttr]) else AttrsLine := ''; // Add field declaration if AttrsLine <> '' then ResultList.Add(Format('%s F%s: %s;', [AttrsLine, FieldName, DelphiFieldType])) else ResultList.Add(Format(' F%s: %s;', [FieldName, DelphiFieldType])); end; // Generate public property declarations ResultList.Add(' public'); for i := 0 to FieldsList.Count - 1 do begin ColumnInfo := FieldsList[i]; // Use QueryColumnAlias as the field name or the column name if ColumnInfo.QueryColumnAlias <> '' then FieldName := ColumnInfo.QueryColumnAlias else FieldName := ColumnInfo.ReferencedTableColumn; // Sanitize FieldName FieldName := CreateValidIdentifier(FieldName); // Create property name (could be same as FieldName) PropertyName := FieldName; ResultList.Add(Format(' property %s: %s read F%s write F%s;', [PropertyName, DelphiFieldType, FieldName, FieldName])); end; ResultList.Add(' end;'); ResultList.Add(''); finally Parser.Free; end; Result := ResultList.Text;finally ResultList.Free; end; end;
Adapt GenerateDelphiClassFromQuery to add a Custom Attribute name "ReferencedColumn" with the ColumnInfo.ReferencedTableColumn when is not [none]
- Assistant
To adapt the
GenerateDelphiClassFromQueryfunction to add a custom attribute namedReferencedColumnwith theColumnInfo.ReferencedTableColumnwhen it is not[none], you can modify the function as follows:- Include a new variable
RefdColAttr: string;to store theReferencedColumnattribute. - Check if
ColumnInfo.ReferencedTableColumnis not[none], and if so, setRefdColAttrto include theReferencedColumnattribute. - Modify the logic for combining attributes to include the new
RefdColAttr. - Use a list to collect all non-empty attributes and then join them together.
Here's the modified
GenerateDelphiClassFromQueryfunction:function GenerateDelphiClassFromQuery(const SQLText: string; const ClassName: string; AConnection: TUniConnection): string; var Parser: TSQLParser; ColumnInfo: TColumnInfo; i: Integer; FieldsList: TObjectList<TColumnInfo>; TableAttr, PKAttr, RefdColAttr, AttrsLine: string; FieldName, PropertyName: string; DelphiFieldType: string; ResultList: TStringList; AttrList: TList<string>; function CreateValidIdentifier(const Name: string): string; var i: Integer; c: Char; begin Result := ''; for i := 1 to Length(Name) do begin c := Name[i]; if c.IsLetterOrDigit or (c = '_') then Result := Result + c else Result := Result + '_'; end; // Ensure the identifier does not start with a digit or invalid character if (Result = '') or not (Result[1].IsLetter or (Result[1] = '_')) then Result := '_' + Result; end; begin ResultList := TStringList.Create; try ResultList.Add('type'); ResultList.Add(Format(' T%s = class', [ClassName])); ResultList.Add(' private'); Parser := TSQLParser.Create(SQLText, AConnection); try FieldsList := Parser.Columns; // Get the columns info // Iterate over the columns to generate field declarations for i := 0 to FieldsList.Count - 1 do begin ColumnInfo := FieldsList[i]; // Map TFieldType to Delphi types case ColumnInfo.DataType of ftString, ftWideString: DelphiFieldType := 'string'; ftInteger, ftSmallint, ftShortInt, ftWord, ftByte: DelphiFieldType := 'Integer'; ftLargeint: DelphiFieldType := 'Int64'; ftFloat, ftCurrency, ftBCD, ftFMTBcd: DelphiFieldType := 'Double'; ftDate, ftTime, ftDateTime, ftTimeStamp: DelphiFieldType := 'TDateTime'; ftBoolean: DelphiFieldType := 'Boolean'; else DelphiFieldType := 'Variant'; // Default to Variant for unsupported types end; // Use QueryColumnAlias as the field name or the column name if ColumnInfo.QueryColumnAlias <> '' then FieldName := ColumnInfo.QueryColumnAlias else FieldName := ColumnInfo.ReferencedTableColumn; // Sanitize the FieldName (remove special characters, etc.) FieldName := CreateValidIdentifier(FieldName); // Build custom attributes if not ColumnInfo.RealTableName.IsEmpty and not ((ColumnInfo.RealTableName = '[none]') or (ColumnInfo.RealTableName = '[ambiguous]') or (ColumnInfo.RealTableName = '[unknown table]')) then begin // Insert Table attribute TableAttr := Format('Table(''%s'')', [ColumnInfo.RealTableName]); end else begin TableAttr := ''; end; if ColumnInfo.IsPKColumn then begin PKAttr := 'PrimaryKey'; end else begin PKAttr := ''; end; // Insert ReferencedColumn attribute if applicable if ColumnInfo.ReferencedTableColumn <> '[none]' then begin RefdColAttr := Format('ReferencedColumn(''%s'')', [ColumnInfo.ReferencedTableColumn]); end else begin RefdColAttr := ''; end; // Combine attributes AttrList := TList<string>.Create; try if TableAttr <> '' then AttrList.Add(TableAttr); if PKAttr <> '' then AttrList.Add(PKAttr); if RefdColAttr <> '' then AttrList.Add(RefdColAttr); if AttrList.Count > 0 then AttrsLine := ' [' + String.Join(', ', AttrList.ToArray) + ']' else AttrsLine := ''; finally AttrList.Free; end; // Add field declaration if AttrsLine <> '' then ResultList.Add(Format('%s F%s: %s;', [AttrsLine, FieldName, DelphiFieldType])) else ResultList.Add(Format(' F%s: %s;', [FieldName, DelphiFieldType])); end; // Generate public property declarations ResultList.Add(' public'); for i := 0 to FieldsList.Count - 1 do begin ColumnInfo := FieldsList[i]; // Use QueryColumnAlias as the field name or the column name if ColumnInfo.QueryColumnAlias <> '' then FieldName := ColumnInfo.QueryColumnAlias else FieldName := ColumnInfo.ReferencedTableColumn; // Sanitize FieldName FieldName := CreateValidIdentifier(FieldName); // Create property name (could be same as FieldName) PropertyName := FieldName; ResultList.Add(Format(' property %s: %s read F%s write F%s;', [PropertyName, DelphiFieldType, FieldName, FieldName])); end; ResultList.Add(' end;'); ResultList.Add(''); finally Parser.Free; end; Result := ResultList.Text; finally ResultList.Free; end; end;With this modification, whenever
ColumnInfo.ReferencedTableColumnis not[none], theReferencedColumnattribute will be added to the field declaration. The attributes are combined using a list to handle any number of attributes dynamically.Explanation:
- Step 1: Declare a new variable
RefdColAttr: string;to hold theReferencedColumnattribute. - Step 2: Check if
ColumnInfo.ReferencedTableColumnis not[none]. If it's not, format theReferencedColumnattribute with the actual column name.if ColumnInfo.ReferencedTableColumn <> '[none]' then begin RefdColAttr := Format('ReferencedColumn(''%s'')', [ColumnInfo.ReferencedTableColumn]); end else begin RefdColAttr := ''; end; - Step 3: Use a
TList<string>(namedAttrList) to collect all non-empty attributes (TableAttr,PKAttr,RefdColAttr).AttrList := TList<string>.Create; try if TableAttr <> '' then AttrList.Add(TableAttr); if PKAttr <> '' then AttrList.Add(PKAttr); if RefdColAttr <> '' then AttrList.Add(RefdColAttr); - Step 4: Join the attributes in
AttrListusing', 'as a separator, and format them intoAttrsLine.if AttrList.Count > 0 then AttrsLine := ' [' + String.Join(', ', AttrList.ToArray) + ']' else AttrsLine := ''; finally AttrList.Free; end; - Step 5: Use
AttrsLinewhen adding the field declaration to include all the attributes.
Note: This approach allows for flexibility in the number of attributes and ensures that only non-empty attributes are included.
Usage Example:
Suppose you have a SQL query like:
SELECT id, name, created_at FROM users;And
idis a primary key, the generated Delphi class might look like:type TUser = class private [Table('users'), PrimaryKey, ReferencedColumn('id')] Fid: Integer; [Table('users'), ReferencedColumn('name')] Fname: string; [Table('users'), ReferencedColumn('created_at')] Fcreated_at: TDateTime; public property id: Integer read Fid write Fid; property name: string read Fname write Fname; property created_at: TDateTime read Fcreated_at write Fcreated_at; end; - Include a new variable