delphi – Synopse SQLite为1：N关系选择行

我正在使用Synopse的SQLite实现,但我仍然坚持使用以下代码.在表单构造函数中,我创建了一个数据库模型,其中有两个表Task和Comment以及一个表TaskComments,其关系为1：N,用于任务注释.我可以将行添加到TaskComments表中(Button1.OnClick事件为它添加一个任务和两个注释)但我不知道如何获取此任务的注释.

任何人都可以建议我如何获得某一行的N行(在这种情况下如何获得任务的评论)？

unit SynopseSQLiteTestUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, SynCommons, SQLite3, SQLite3Commons, StdCtrls;

type
  TTask = class(TSQLRecord)
  private
    FTaskName: RawUTF8;
    FTaskCreated: TDateTime;
  published
    property TaskName: RawUTF8 read FTaskName write FTaskName;
    property TaskCreated: TDateTime read FTaskCreated write FTaskCreated;
  end;

  TComment = class(TSQLRecord)
  private
    FCommentText: RawUTF8;
    FCommentCreated: TDateTime;
  published
    property CommentText: RawUTF8 read FCommentText write FCommentText;
    property CommentCreated: TDateTime read FCommentCreated write FCommentCreated;
  end;

  TTaskComments = class(TSQLRecordMany)
  private
    FTask: TTask;
    FComment: TComment;
  published
    property Task: TTask read FTask;
    property Comment: TComment read FComment;
  end;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Memo1: TMemo;
    Memo2: TMemo;
    Memo3: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    FDatabase: TSQLRestClientURI;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  SQLModel: TSQLModel;
begin
  SQLModel := TSQLModel.Create([
    TTask,
    TComment,
    TTaskComments
  ]);
  FDatabase := TSQLRestClientDB.Create(SQLModel, SQLModel, ChangeFileExt(Application.ExeName,'.db3'), TSQLRestServerDB);
  TSQLRestClientDB(FDatabase).Server.CreateMissingTables(0);
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  Task: TTask;
  TaskID: Integer;
  Comment: TComment;
  CommentID: Integer;
  TaskComments: TTaskComments;
begin
  Task := TTask.Create;
  Comment := TComment.Create;
  TaskComments := TTaskComments.Create;

  try
    Task.TaskName := StringToUTF8('Task Name');
    Task.TaskCreated := Now;
    TaskID := FDatabase.Add(Task, True);

    Comment.CommentText := StringToUTF8('Comment Text 1');
    Comment.CommentCreated := Now;
    CommentID := FDatabase.Add(Comment, True);

    TaskComments.ManyAdd(FDatabase, TaskID, CommentID);

    Comment.CommentText := StringToUTF8('Comment Text 2');
    Comment.CommentCreated := Now;
    CommentID := FDatabase.Add(Comment, True);

    TaskComments.ManyAdd(FDatabase, TaskID, CommentID, True);

  finally
    FreeAndNil(Task);
    FreeAndNil(Comment);
    FreeAndNil(TaskComments);
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  Task: TTask;
  Comment: TComment;
  TaskComments: TTaskComments;
begin
  Memo1.Clear;
  Memo2.Clear;
  Memo3.Clear;

  // here I want to select task with ID = 1, that's fine
  Task := TTask.CreateAndFillPrepare(FDatabase, 'ID = 1');
  // here I want to select all comments, that's fine
  Comment := TComment.CreateAndFillPrepare(FDatabase, '');
  // here I want to create the task comments, ok
  TaskComments := TTaskComments.Create;

  try
    // here I'm filling the memo boxes with the task and all comments, ok
    while Task.FillOne do
      Memo1.Lines.Add(UTF8ToWideString(Task.TaskName));
    while Comment.FillOne do
      Memo2.Lines.Add(UTF8ToWideString(Comment.CommentText));

    // here I'm trying to get all comments for task with ID = 1
    // but the FillOne function returns always False, what means, that
    // I don't get any row fetched
    TaskComments.FillMany(FDatabase, 1);
    while TaskComments.FillOne do
      Memo3.Lines.Add(UTF8ToWideString(TaskComments.Task.TaskName) + '; ' + UTF8ToWideString(TaskComments.Comment.CommentText));

  finally
    FreeAndNil(Task);
    FreeAndNil(Comment);
    FreeAndNil(TaskComments);
  end;
end;

end.

非常感谢

你应该把它发布在官方的mORMot论坛上,这个日子里没有像其他土拨鼠一样睡觉……但是很高兴在SO中看到这样的问题！

首先,一些一般性说明：

>你应该更好地使用UTF8ToString而不是UTF8ToWideString函数;
>如果您创建对象实例,最好使用嵌套的try..finally块：例如如果TComment.CreateAndPrepare构造函数失败并引发异常,您将永远不会到达FreeAndNil(任务)代码,因此您将泄漏内存;
>小心,使用FreeAndNil()在Delphi社区的那些日子里非常危险 – 你可能会被解剖！
>主要的FSQLModel应该公开,并在所有数据库时间内生效;
> TSQLRestClientDB 3d参数(服务器模型)应为零;
>需要FormDestroy才能释放内存,但这不是主要内容;

关于您的代码,事实上,正如文档所述,TSQLRecordMany子类应至少具有两个已发布的属性,名为Source和Dest,按惯例：

• by default, only two TSQLRecord (i.e. INTEGER) fields must be created,
  named “Source” and “Dest”, the first pointing to the source record (the one
  with a TSQLRecordMany published property) and the second to the destination record
  …
• in all cases, at leat two ‘Source’ and ‘Dest’ published properties must
  be declared as TSQLRecord children in any TSQLRecordMany descendant
  because they will always be needed for the ‘many to many’ relationship

然后,它应该按预期工作：

TTaskComments = class(TSQLRecordMany)
  private
    FSource: TTask;
    FDest: TComment;
  published
    property Source: TTask read FSource;
    property Dest: TComment read FDest;
  end;

请注意,FillMany()方法仅将Source和Dest填充为ID,因此您无法直接获取Source.TaskName或Dest.CommentText.你必须使用,例如DestGetJoined方法来检索所需的字段.请参阅有关该方法的文档,或阅读SQLite3.pas单元的TTestSQLite3Engine._TSQLRestClientDB方法中的TestMany过程.

您还可以查看“自动连接查询”新功能(在1.16主干中)：它将为您提供查询.见this article.