我正在使用 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 论坛上发布这个,这些天它不像其他土拨鼠那样 sleep ......但是在 SO 中看到这样的问题真是太好了!
首先,一些一般性的注意事项:
UTF8ToString
而不是 UTF8ToWideString
函数; try..finally
块:例如如果 TComment.CreateAndPrepare
构造函数失败并引发异常,您将永远无法访问 FreeAndNil(Task)
代码,因此会泄漏内存; FreeAndNil()
是非常危险的——你可能会被诅咒! FSQLModel
应该公开,并且在所有数据库时间都存在; TSQLRestClientDB
3d 参数(服务器模型)应为零; FormDestroy
来释放内存,但不是这里的重点; 关于您的代码,实际上,正如文档所述,
TSQLRecordMany
子类 应 至少有两个已发布的属性,命名为 Source
和 Dest
,按照惯例:然后,它应该按预期工作:
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
方法来检索所需的字段。请参阅有关该方法的文档,或阅读 TestMany
单元的 TTestSQLite3Engine._TSQLRestClientDB
方法中的 SQLite3.pas
过程。您还可以查看“自动 JOIN 查询”新功能(在 1.16 主干中):它将为您进行查询。见 this article 。
关于delphi - Synopse SQLite 为 1 :N relationship 选择行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/8972918/