Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

Sign up
Appearance settings

HemulGM/SQLGenerator

Repository files navigation

SQLGenerator

uses
 System.SysUtils,
 System.TypInfo,
 System.Rtti,
 SQLG.Table in 'SQLG.Table.pas',
 SQLG.Field in 'SQLG.Field.pas',
 SQLG.Condition in 'SQLG.Condition.pas',
 SQLG.Params in 'SQLG.Params.pas',
 SQLG.Select in 'SQLG.Select.pas',
 SQLG.CreateTable in 'SQLG.CreateTable.pas',
 SQLG.Types in 'SQLG.Types.pas';
type
 [TableName('user')]
 TUser = class(TSQLTable)
 [FieldName('id'),
 UNIQUE,
 PRIMARYKEY]
 Id: TFGUID;
 [FieldName('role_id')]
 RoleId: TFGUID;
 [FieldName('status')]
 Status: TFInteger;
 [FieldName('name')]
 Name: TFString;
 end;
 [TableName('user_role')]
 TUserRole = class(TSQLTable)
 [FieldName('id')]
 Id: TFGUID;
 [FieldName('type')]
 RoleType: TFInteger;
 [FieldName('desc')]
 Desc: TFString;
 [FieldName('name'),
 LENGTH(20)]
 Name: TFVARCHAR;
 end;
procedure Test;
begin
 var User := TUser.Create;
 var UserRole := TUserRole.Create;
 var Params: TSQLParams;
 var Sel :=
 Select([User, UserRole.Desc.Table('ur').&As('description')]).
 From(User).
 LeftJoin(
 Select('*').
 From(UserRole).Where(UserRole.RoleType = 1), 'ur').
 on(User.RoleId = UserRole.Id.Table('ur')).
 Where(not (User.Id = TGUID.NewGuid) or (User.Status in [1, 2, 3])).
 Where(User.Status and 1 = 0).
 Where(User.Name = 'Dan').
 Where(User.Status in
 Select(User.Status).From(User).Where(User.RoleId in [TGUID.NewGuid, TGUID.NewGuid])).
 OrderBy([User.Name, DESC(User.Status)]).
 GroupBy([User.Id]);
 writeln(Sel.Build(Params));
 writeln;
 for var Param in Params do
 writeln(Param.Key, ': ', Param.Value.TypeInfo.Name, ' = ', Param.ToString);
 User.Free;
 UserRole.Free;
end;
begin
 try
 Test;
 except
 on E: Exception do
 Writeln(E.ClassName, ': ', E.Message);
 end;
 Readln;
end.

output

SELECT user.*, ur.desc description
 FROM user
 LEFT JOIN (
 SELECT *
 FROM user_role
 WHERE user_role.type = :p0) ur ON user.role_id = ur.id
 WHERE (NOT (user.id = :p1)) OR (user.status in (:p2, :p3, :p4)) AND user.status & 1 = :p5 AND user.name = :p6 AND user.status in (
 SELECT user.status
 FROM user
 WHERE user.role_id in (:p7, :p8))
 ORDER BY user.name, user.status DESC
 GROUP BY user.id
p0: Integer = 1
p1: TGUID = {4D8FD3C0-9972-4269-BBB6-34E3925EABE2}
p2: Integer = 1
p3: Integer = 2
p4: Integer = 3
p5: Integer = 0
p6: string = Dan
p7: TGUID = {2A37B58A-3B2F-4320-9850-A70AA70C3971}
p8: TGUID = {3FBC11F6-381F-4443-8592-1E5FC5CDF479}

About

SQL generator for Delphi

Topics

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Languages

AltStyle によって変換されたページ (->オリジナル) /