SQLGenerator

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 .

輸出

 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}