Given the Delphi units below, that receives a SQL SELECT query and create a class:…
- User
Given the Delphi units below, that receives a SQL SELECT query and create a class:
showmessage(GenerateDelphiClassFromQuery('SELECT caixa_id,caixa_nome as nomedocaixa,(CURRENT_TIMESTAMP) as cx_date FROM caixa','CaixaORM',DM.LocalServer));
Given the class generated by GenerateDelphiClassFromQuery, create Delphi code (using Rtti and other resources available on the latest Delphi verions) that gets the corresponding SELECT that generated the corresponding class and populate it
unit ORM_CustomAttributes;
{$TYPEINFO ON}
interface
uses System.SysUtils, System.Classes, System.Rtti;
type TFormatType = (tiString, tiInteger, tiDate, tiDateTime, tiMoney, tiFloat, tiBoolean, tiByteArray, tiNone);
/// /// Attribute to specify the database table associated with a class or field. /// TableAttribute = class(TCustomAttribute) private FTableName: string; public constructor Create(const ATableName: string); property TableName: string read FTableName; end;
/// /// Attribute to mark a field as a primary key. /// PrimaryKeyAttribute = class(TCustomAttribute) public constructor Create; end;
/// /// Attribute to specify the database column associated with a field. /// RefColumnAttribute = class(TCustomAttribute) private FColumnName: string; public constructor Create(const AColumnName: string); property ColumnName: string read FColumnName; end;
/// /// Attribute to specify the formatting of a field. /// FmtPropAttribute = class(TCustomAttribute) private FFormatType: TFormatType; public constructor Create(AFormatType: TFormatType); property FormatType: TFormatType read FFormatType; end;
implementation
{ TableAttribute }
constructor TableAttribute.Create(const ATableName: string); begin inherited Create; FTableName := ATableName; end;
{ RefColumnAttribute }
constructor RefColumnAttribute.Create(const AColumnName: string); begin inherited Create; FColumnName := AColumnName; end;
{ PrimaryKeyAttribute }
constructor PrimaryKeyAttribute.Create; begin inherited Create; end;
{ FmtPropAttribute }
constructor FmtPropAttribute.Create(AFormatType: TFormatType); begin inherited Create; FFormatType := AFormatType; end;
end.
unit formgerenc_Classes;
{$TYPEINFO ON}
interface
uses orm_utils, pixel_classes, ORM_CustomAttributes;
type TEmployeeProject = class(TQryRecInfo) private [Table('employees'), PrimaryKey, RefColumn('emp_id')] _EmployeeID: TNullable; [Table('employees'), PrimaryKey, RefColumn('dept_id')] _DepartmentID: TNullable; [Table('employees'), RefColumn('emp_name')] _EmployeeName: TNullable; [Table('projects'), PrimaryKey, RefColumn('proj_id')] _ProjectID: TNullable; [Table('projects'), RefColumn('proj_name')] _ProjectName: TNullable; [Table('projects'), RefColumn('proj_budget'), FmtProp(tiMoney)] _ProjectBudget: TNullable; [Table('projects'), RefColumn('proj_date'), FmtProp(tiDate)] _ProjectDate: TNullable; [Table('projects'), RefColumn('proj_starttime'), FmtProp(tiDateTime)] _ProjectStartMoment: TNullable;
published property EmployeeID: TNullable index 0 read _EmployeeID write Set_Info_NullableInteger; property DepartmentID: TNullable index 1 read _DepartmentID write Set_Info_NullableInteger; property EmployeeName: TNullable index 2 read _EmployeeName write Set_Info_NullableString; property ProjectID: TNullable index 3 read _ProjectID write Set_Info_NullableInteger; property ProjectName: TNullable index 4 read _ProjectName write Set_Info_NullableString; property ProjectBudget: TNullable index 5 read _ProjectBudget write Set_Info_NullableCurrency; property ProjectDate: TNullable index 6 read _ProjectDate write Set_Info_NullableDateTime; property ProjectStartMoment: TNullable index 7 read _ProjectStartMoment write Set_Info_NullableDateTime; end;
implementation
end.
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, PropIndex: Integer; FieldsList: TObjectList; AttrsLine: string; FieldName, PropertyName: string; NullableDelphiType, WriteMethod: string; ResultList: TStringList; AttributesList: TList;
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 + ''; // Replace invalid chars with '' 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;
function GetDelphiTypeMapping(FieldType: TFieldType; out NullableDelphiType, WriteMethod: string): Boolean; begin Result := True; case FieldType of TFieldType.ftString, TFieldType.ftWideString, TFieldType.ftMemo, TFieldType.ftWideMemo: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableString'; end; TFieldType.ftInteger, TFieldType.ftByte, TFieldType.ftSmallint, TFieldType.ftWord, TFieldType.ftLongWord, TFieldType.ftShortint: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableInteger'; end; TFieldType.ftLargeint: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableInt64'; end; TFieldType.ftSingle, TFieldType.ftFloat, TFieldType.ftExtended: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableExtended'; end; TFieldType.ftDate, TFieldType.ftTime, TFieldType.ftDateTime, TFieldType.ftTimeStamp: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableDateTime'; end; TFieldType.ftCurrency: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableCurrency'; end; TFieldType.ftFMTBcd, TFieldType.ftBCD: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableCurrency'; end; TFieldType.ftBoolean: begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableBoolean'; end; else begin NullableDelphiType := 'TNullable'; WriteMethod := 'Set_Info_NullableVariant'; Result := False; end; end; end;
begin ResultList := TStringList.Create; try ResultList.Add('type'); ResultList.Add(Format(' T%s = class(TQryRecInfo)', [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]; if not GetDelphiTypeMapping(ColumnInfo.DataType, NullableDelphiType, WriteMethod) then begin // Handle unsupported type if necessary 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); FieldName := '_' + FieldName; // Prefix with '_' // Initialize the list of attributes AttributesList := TList<string>.Create; try // Build custom attributes if not ColumnInfo.RealTableName.IsEmpty and not ((ColumnInfo.RealTableName = '[none]') or (ColumnInfo.RealTableName = '[ambiguous]') or (ColumnInfo.RealTableName = '[unknown]')) then begin // Insert Table attribute AttributesList.Add(Format('Table(''%s'')', [ColumnInfo.RealTableName])); end; if ColumnInfo.IsPKColumn then begin AttributesList.Add('PrimaryKey'); end; if not ColumnInfo.ReferencedTableColumn.IsEmpty and (ColumnInfo.ReferencedTableColumn <> '[none]') then begin AttributesList.Add(Format('RefColumn(''%s'')', [ColumnInfo.ReferencedTableColumn])); end; // Combine attributes if AttributesList.Count > 0 then AttrsLine := Format(' [%s]', [String.Join(', ', AttributesList.ToArray)]) else AttrsLine := ''; // Add field declaration if AttrsLine <> '' then ResultList.Add(Format('%s %s: %s;', [AttrsLine, FieldName, NullableDelphiType])) else ResultList.Add(Format(' %s: %s;', [FieldName, NullableDelphiType])); finally AttributesList.Free; end; end; // Generate published property declarations ResultList.Add(' published'); PropIndex := 0; for i := 0 to FieldsList.Count - 1 do begin ColumnInfo := FieldsList[i]; if not GetDelphiTypeMapping(ColumnInfo.DataType, NullableDelphiType, WriteMethod) then begin // Handle unsupported type if necessary end; // Use the sanitized FieldName (without '_') if ColumnInfo.QueryColumnAlias <> '' then FieldName := ColumnInfo.QueryColumnAlias else FieldName := ColumnInfo.ReferencedTableColumn; FieldName := CreateValidIdentifier(FieldName); // PropertyName is same as FieldName PropertyName := FieldName; // FieldName with '_' FieldName := '_' + FieldName; // Add property declaration ResultList.Add(Format(' property %s: %s index %d read %s write %s;', [PropertyName, NullableDelphiType, PropIndex, FieldName, WriteMethod])); Inc(PropIndex); end; ResultList.Add(' end;'); ResultList.Add(''); finally Parser.Free; end; Result := ResultList.Text;finally ResultList.Free; end; end;
- Assistant
Certainly! Below, I'll provide Delphi code that uses RTTI and features available in the latest Delphi versions to:
- Reconstruct the SELECT statement that generated the class, based on the attributes applied to the class and its properties.
- Execute the SELECT statement using a
TUniConnection. - Populate instances of the class with the results from the query.
We'll proceed step by step, explaining each part of the code.
Step 1: Define a Function to Populate Instances from Class Metadata
First, we'll create a function
CreateInstancesFromClass<T>that:- Receives the class type as a generic parameter.
- Uses RTTI to inspect the class and its properties.
- Builds the SELECT statement.
- Executes the statement.
- Populates and returns a list of instances.
Here's the code:
uses System.Rtti, System.TypInfo, System.Generics.Collections, Data.DB, Uni; function CreateInstancesFromClass<T: class, constructor>(AConnection: TUniConnection): TList<T>; var Context: TRttiContext; RttiType: TRttiType; RttiProperty: TRttiProperty; Attr: TCustomAttribute; TableAttrs: TDictionary<string, string>; // Maps table aliases to table names ColumnList: TList<string>; TableList: TList<string>; SQLSelect: TStringList; Query: TUniQuery; Instance: T; FieldName: string; PropertyMap: TDictionary<string, TRttiProperty>; ColumnAlias: string; Value: TValue; TableName: string; ColumnName: string; RefColumnAttr: RefColumnAttribute; TableAttr: TableAttribute; FmtPropAttr: FmtPropAttribute; DataType: TFieldType; begin Result := TList<T>.Create; Context := TRttiContext.Create; try RttiType := Context.GetType(TypeInfo(T)); TableAttrs := TDictionary<string, string>.Create; ColumnList := TList<string>.Create; TableList := TList<string>.Create; PropertyMap := TDictionary<string, TRttiProperty>.Create; try // First, collect information about tables and columns for RttiProperty in RttiType.GetProperties do begin for Attr in RttiProperty.GetAttributes do begin if Attr is TableAttribute then begin TableName := TableAttribute(Attr).TableName; if not TableList.Contains(TableName) then TableList.Add(TableName); Break; end; end; end; // Build the SELECT clause for RttiProperty in RttiType.GetProperties do begin ColumnAlias := RttiProperty.Name; TableName := ''; ColumnName := ''; // Get Table and Column attributes for Attr in RttiProperty.GetAttributes do begin if Attr is TableAttribute then begin TableName := TableAttribute(Attr).TableName; end else if Attr is RefColumnAttribute then begin ColumnName := RefColumnAttribute(Attr).ColumnName; end; end; if (TableName <> '') and (ColumnName <> '') then begin FieldName := Format('%s.%s AS %s', [TableName, ColumnName, ColumnAlias]); ColumnList.Add(FieldName); // Map the column alias to the property for later assignment PropertyMap.Add(ColumnAlias.ToLower, RttiProperty); end; end; // Build the FROM clause SQLSelect := TStringList.Create; try SQLSelect.Add('SELECT'); SQLSelect.Add(String.Join(', ', ColumnList.ToArray)); SQLSelect.Add('FROM'); SQLSelect.Add(String.Join(', ', TableList.ToArray)); // Now execute the query Query := TUniQuery.Create(nil); try Query.Connection := AConnection; Query.SQL.Text := SQLSelect.Text; Query.Open; while not Query.Eof do begin Instance := T.Create; for FieldName in PropertyMap.Keys do begin RttiProperty := PropertyMap[FieldName]; // Get the value from the dataset field if Query.FieldDefs.IndexOf(FieldName) >= 0 then begin DataType := Query.FieldByName(FieldName).DataType; case DataType of ftString, ftWideString, ftMemo, ftWideMemo: Value := Query.FieldByName(FieldName).AsString; ftInteger, ftSmallint, ftWord, ftLongWord, ftShortint: Value := Query.FieldByName(FieldName).AsInteger; ftLargeint: Value := Query.FieldByName(FieldName).AsLargeInt; ftFloat, ftBCD, ftFMTBcd: Value := Query.FieldByName(FieldName).AsFloat; ftCurrency: Value := Query.FieldByName(FieldName).AsCurrency; ftDate, ftTime, ftDateTime, ftTimeStamp: Value := Query.FieldByName(FieldName).AsDateTime; ftBoolean: Value := Query.FieldByName(FieldName).AsBoolean; else Value := Query.FieldByName(FieldName).Value; end; // Set the property value using RTTI RttiProperty.SetValue(TObject(Instance), Value); end; end; Result.Add(Instance); Query.Next; end; finally Query.Free; end; finally SQLSelect.Free; end; finally TableAttrs.Free; ColumnList.Free; TableList.Free; PropertyMap.Free; end; finally Context.Free; end; end;
Step 2: Explanation of the Code
Imports:
We use several units:
System.Rtti: For RTTI access.System.TypInfo: For type information.System.Generics.Collections: For generic lists and dictionaries.Data.DB: For database-related types.Uni: ForTUniConnectionandTUniQuery.
Function Signature:
function CreateInstancesFromClass<T: class, constructor>(AConnection: TUniConnection): TList<T>;This function takes a generic class type
T(our ORM class) and a database connection, and returns aTList<T>containing instances ofTpopulated from the database.Steps Inside the Function:
-
Initialize RTTI Context and Collections:
Context := TRttiContext.Create; RttiType := Context.GetType(TypeInfo(T));We get the RTTI type of
Tto inspect its properties and attributes. -
Collect Table and Column Information:
for RttiProperty in RttiType.GetProperties do begin // Inspect attributes to find Table and Column information end;For each property, we check for
TableAttributeandRefColumnAttributeto get the table and column names. -
Build the SELECT Clause:
FieldName := Format('%s.%s AS %s', [TableName, ColumnName, ColumnAlias]); ColumnList.Add(FieldName);We create field expressions like
table.column AS PropertyNameand add them to the list of columns. -
Build the FROM Clause:
SQLSelect.Add('FROM'); SQLSelect.Add(String.Join(', ', TableList.ToArray));We assume a simple
FROMclause listing the tables. For more complex queries involving joins, this part would need enhancement. -
Execute the Query:
Query := TUniQuery.Create(nil); Query.Connection := AConnection; Query.SQL.Text := SQLSelect.Text; Query.Open; -
Populate Instances:
-
Iterate Over the Result Set:
while not Query.Eof do -
Create an Instance of T:
Instance := T.Create; -
Set Property Values Using RTTI:
For each property, we get the value from the dataset and set it:
RttiProperty.SetValue(TObject(Instance), Value);
-
Step 3: Using the Function
Now, we can use this function to create instances of
TCaixaORM:var List: TList<TCaixaORM>; Item: TCaixaORM; begin List := CreateInstancesFromClass<TCaixaORM>(DM.LocalServer); try for Item in List do begin // Use Item properties ShowMessage(Format('ID: %d, Name: %s, Date: %s', [Item.caixa_id.Value, Item.nomedocaixa.Value, DateTimeToStr(Item.cx_date.Value)])); end; finally List.Free; end; end;
Complete Example With Context
Here's the complete code integrated together:
uses System.Rtti, System.TypInfo, System.Generics.Collections, Data.DB, Uni, System.SysUtils; // Assume TCaixaORM is the generated class from your example function CreateInstancesFromClass<T: class, constructor>(AConnection: TUniConnection): TList<T>; var Context: TRttiContext; RttiType: TRttiType; RttiProperty: TRttiProperty; Attr: TCustomAttribute; TableAttrs: TDictionary<string, string>; ColumnList: TList<string>; TableList: TList<string>; SQLSelect: TStringList; Query: TUniQuery; Instance: T; FieldName: string; PropertyMap: TDictionary<string, TRttiProperty>; ColumnAlias: string; Value: TValue; TableName: string; ColumnName: string; ColumnExpr: string; begin Result := TList<T>.Create; Context := TRttiContext.Create; try RttiType := Context.GetType(TypeInfo(T)); TableAttrs := TDictionary<string, string>.Create; ColumnList := TList<string>.Create; TableList := TList<string>.Create; PropertyMap := TDictionary<string, TRttiProperty>.Create; try // First, collect information about tables and columns for RttiProperty in RttiType.GetProperties do begin TableName := ''; ColumnName := ''; ColumnAlias := RttiProperty.Name; ColumnExpr := ''; for Attr in RttiProperty.GetAttributes do begin if Attr is TableAttribute then begin TableName := TableAttribute(Attr).TableName; if not TableList.Contains(TableName) then TableList.Add(TableName); end else if Attr is RefColumnAttribute then begin ColumnName := RefColumnAttribute(Attr).ColumnName; end; end; if (TableName <> '') and (ColumnName <> '') then begin ColumnExpr := Format('%s.%s AS %s', [TableName, ColumnName, ColumnAlias]); end else begin // If no table or column info, use the property name as is ColumnExpr := ColumnAlias; end; ColumnList.Add(ColumnExpr); PropertyMap.Add(ColumnAlias.ToLower, RttiProperty); end; // Build the SQL SQLSelect := TStringList.Create; try SQLSelect.Add('SELECT'); SQLSelect.Add(String.Join(', ', ColumnList.ToArray)); SQLSelect.Add('FROM'); SQLSelect.Add(String.Join(', ', TableList.ToArray)); // Execute the query Query := TUniQuery.Create(nil); try Query.Connection := AConnection; Query.SQL.Text := SQLSelect.Text; Query.Open; while not Query.Eof do begin Instance := T.Create; for FieldName in PropertyMap.Keys do begin if Query.FindField(FieldName) <> nil then begin Value := Query.FieldByName(FieldName).Value; PropertyMap[FieldName].SetValue(Instance, Value); end; end; Result.Add(Instance); Query.Next; end; finally Query.Free; end; finally SQLSelect.Free; end; finally TableAttrs.Free; ColumnList.Free; TableList.Free; PropertyMap.Free; end; finally Context.Free; end; end; // Usage example var List: TList<TCaixaORM>; Item: TCaixaORM; begin List := CreateInstancesFromClass<TCaixaORM>(DM.LocalServer); try for Item in List do begin // Use Item properties ShowMessage(Format('ID: %d, Name: %s, Date: %s', [Item.caixa_id.Value, Item.nomedocaixa.Value, DateTimeToStr(Item.cx_date.Value)])); end; finally List.Free; end; end;
Notes and Considerations
-
Assumptions:
- The class properties have
TableandRefColumnattributes. - The database connection is valid and connected.
- The database fields match the properties' expected types.
- The class properties have
-
Error Handling:
- The code provided lacks detailed error handling for brevity. In production code, you should add try-except blocks where appropriate.
-
Complex Queries:
- The code handles simple SELECT statements with direct table references.
- If your original query includes joins or subqueries, you might need to enhance the logic to handle aliases and more complex FROM clauses.
-
Nullable Types:
- The properties are using
TNullable<T>. When setting the property value, you might need to handle nulls appropriately. - In the example, we assume that
Value := Query.FieldByName(FieldName).Value;works correctly withTNullable<T>. If not, you may need to convert it properly.
- The properties are using
-
Field and Property Name Matching:
- The code assumes that the field aliases in the SQL match the property names.
- Be cautious with case sensitivity and ensure consistent naming.
Conclusion
By using RTTI and the class attributes, we've dynamically reconstructed the SELECT statement and populated instances of the class without hardcoding the SQL or the mapping logic. This approach provides flexibility and reduces the need to manually maintain the data access code when the class structure changes.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.