SQLite local TSQLRestClientDB demo

gclaxton · 2011-05-27 22:44:22

Administrator wrote:

And IMHO you should not use "SQL by hand" on the server side, but rely on ORM methods of the framework, if possible without using Server.DB but the Client instance, i.e. TSQLRestClientDB in your case.

Fixed! If you could be so kind and look at what I have now, and let me know if there is something else I need to look at before I actually use the code below. I have include the complete source code (link below) just in case you need to see it in action.

Source Code can be downloaded here

Administrator wrote:

I've updated the framework documentation to explain better how to use high-level ORM methods instead of SQL. Please take a look at the TSQLRest methods: you'll find here almost all needed ORM-oriented methods to change the TSQLRecord content.

Is this available online now? If so the link would be?

Drives.Data.pas

unit Drives.Data;

interface

uses
  SQLite3, SynCommons, SQLite3Commons, Classes;

type
  TSQLDriveRecord = class(TSQLRecord)
  private
    { Private declarations }
    FDrive: RawUTF8;
    FActive: Integer;
    FChecked: Integer;
  protected
    { Protected declarations }
  public
    { Public declarations }
  published
    { Published declarations }
    property Drive: RawUTF8 read FDrive write FDrive stored false;
    property Active: Integer read FActive write FActive default 0;
    property Checked: Integer read FChecked write FChecked default 0;
  end;

  TDrivesClientDB = class(TSQLRestClientDB)
  public
    { Public declarations }
    class function GetRowCount(const theColumn: string = ''): Integer;
    class procedure GetDriveList(theItems: TStrings; const theColumn: string = '');
    class function GetCheckedDrive(const theID: Integer): Boolean;
    class procedure SetCheckedDrive(const theID: Integer; const theCheck: Boolean);
  end;

var
  DrivesClientDB: TDrivesClientDB;

implementation

uses
  Windows, SysUtils;

var
  DrivesModel: TSQLModel;

function CreateDrivesModel(): TSQLModel;
begin
  Result := TSQLModel.Create([TSQLDriveRecord], 'root');
end;

procedure InitializeClientDB();
var
  X: AnsiChar;
  DriveRecord: TSQLDriveRecord;
  Active: Integer;
begin
  DrivesModel := CreateDrivesModel();
  DrivesClientDB := TDrivesClientDB.Create(DrivesModel, nil, 'drives.sqlite', TSQLRestServerDB);

  with TSQLRestClientDB(DrivesClientDB) do
  begin
    Server.CreateMissingTables(0);
    (*
    DB.Execute('CREATE TABLE IF NOT EXISTS [driveRecord] (' +
      '[id] INTEGER PRIMARY KEY AUTOINCREMENT, ' +
      '[drive] TEXT NOT NULL UNIQUE COLLATE NOCASE, ' +
      '[active] INTEGER NOT NULL DEFAULT (0), ' +
      '[checked] INTEGER NOT NULL DEFAULT (0));');
    *)

    for X := 'A' to 'Z' do
    begin
      DriveRecord := TSQLDriveRecord.Create(TSQLRestClientDB(DrivesClientDB), Ord(X) - 64);
      try
        Active := DriveRecord.Active;

        if (Windows.GetDriveType(PChar(X + ':\')) in [0, 1]) then
          DriveRecord.Active := 0
        else
          DriveRecord.Active := 1;

        if (Length(DriveRecord.Drive) = 0) then
        begin
          DriveRecord.Drive := StringToUTF8(X) + ':\';
          DriveRecord.Checked := 0;
          Add(DriveRecord, True);
        end
        else begin
          if (Active <> DriveRecord.Active) then Update(DriveRecord);
        end;

      finally
        DriveRecord.Free();
        if (Pointer(DriveRecord) <> nil) then Pointer(DriveRecord) := nil;
      end;
    end;
  end;
end;

procedure FinalizeClientDB();
begin
  TDrivesClientDB(DrivesClientDB).Free();
  TSQLModel(DrivesModel).Free();
end;

{ TDrivesClient }
class function TDrivesClientDB.GetRowCount(const theColumn: string = ''): Integer;
var
  Data: TIntegerDynArray;
begin
  with TSQLRestClientDB(DrivesClientDB) do
    if (Length(theColumn) = 0) then
      Result := TableRowCount(TSQLDriveRecord)
    else begin
      OneFieldValues(TSQLDriveRecord, 'drive', StringToUTF8(theColumn) + '=1', Data);
      Result := high(Data) + 1;
    end;
end;

class procedure TDrivesClientDB.GetDriveList(theItems: TStrings; const theColumn: string = '');
begin
  with TSQLRestClientDB(DrivesClientDB) do
    if (Length(theColumn) = 0) then
      OneFieldValues(TSQLDriveRecord, 'drive', '', theItems)
    else begin
      OneFieldValues(TSQLDriveRecord, 'drive', StringToUTF8(theColumn) + '=1', theItems);
    end;
end;

class function TDrivesClientDB.GetCheckedDrive(const theID: Integer): Boolean;
var
  Data: TIntegerDynArray;
begin
  with TSQLRestClientDB(DrivesClientDB) do
    Result := OneFieldValues(TSQLDriveRecord, 'drive', 'id=' + IntToStr(theID) + ' and active=1 and checked=1', Data);
end;

class procedure TDrivesClientDB.SetCheckedDrive(const theID: Integer; const theCheck: Boolean);
var
  DriveRecord: TSQLDriveRecord;
begin
  DriveRecord := TSQLDriveRecord.Create(TSQLRestClientDB(DrivesClientDB), theID);
  try
    DriveRecord.Checked := Ord(theCheck);
    TSQLRestClientDB(DrivesClientDB).Update(DriveRecord);
  finally
    DriveRecord.Free();
    if (Pointer(DriveRecord) <> nil) then Pointer(DriveRecord) := nil;
  end;
end;

initialization
  InitializeClientDB();

finalization
  FinalizeClientDB();

end.

Unit1.pas

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms,
  StdCtrls, ExtCtrls, ComCtrls;

type
  TMainFrm = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    ListView1: TListView;
    Button1: TButton;
    Label1: TLabel;
    Button2: TButton;
    Button3: TButton;
    Panel3: TPanel;
    Label2: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure ListView1MouseDown(Sender: TObject; Button: TMouseButton;
      Shift: TShiftState; X, Y: Integer);
    procedure ListView1KeyPress(Sender: TObject; var Key: Char);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  MainFrm: TMainFrm;

implementation

uses
  Drives.Data;

{$R *.DFM}
procedure TMainFrm.Button1Click(Sender: TObject);
var
  SL: TStringList;
  X: Integer;
  Freq, StartCount, StopCount: Int64;
begin
  Label1.Caption := ' Count: ' + IntToStr(DrivesClientDB.GetRowCount((Sender as TButton).Hint));

  SL := TStringList.Create();
  try
    QueryPerformanceFrequency(Freq);
    QueryPerformanceCounter(StartCount);
    DrivesClientDB.GetDriveList(SL, (Sender as TButton).Hint);
    QueryPerformanceCounter(StopCount);
    Label2.Caption := Format('(%.1f seconds) ', [(StopCount - StartCount) / Freq]);

    ListView1.Items.BeginUpdate();
    try
      ListView1.Items.Clear();

      for X := 0 to (SL.Count - 1) do
      begin
        with ListView1.Items.Add do
        begin
          Caption := '(' + SL[X] + ')';
          ListView1.Items[X].SubItems.AddObject('', SL.Objects[X]);
          ListView1.Items[X].Checked := DrivesClientDB.GetCheckedDrive(Integer(SL.Objects[X]));
        end;
      end;
    finally
      ListView1.Items.EndUpdate();
    end;

  finally
    SL.Free();
    if (Pointer(SL) <> nil) then Pointer(SL) := nil;
  end;
end;

procedure TMainFrm.ListView1MouseDown(Sender: TObject; Button: TMouseButton; Shift: TShiftState; X, Y: Integer);
var
  Item: TListItem;
  HitTest: THitTests;
begin
  Item := (Sender as TListView).GetItemAt(X, Y);
  if Assigned(Item) then
  begin
    HitTest := (Sender as TListView).GetHitTestInfoAt(X, Y);

    if (HitTest = [htOnStateIcon]) then
    begin
      (Sender as TListView).SetFocus();
      Item.Selected := True;
      Item.Focused := True;

      DrivesClientDB.SetCheckedDrive(Integer((Sender as TListView).Items[Item.Index].SubItems.Objects[0]), Item.Checked);
    end;
  end;
end;

procedure TMainFrm.ListView1KeyPress(Sender: TObject; var Key: Char);
var
  Item: TListItem;
begin
  Item := (Sender as TListView).ItemFocused;
  if Assigned(Item) then
  begin
    (Sender as TListView).SetFocus();
    Item.Selected := True;
    Item.Focused := True;

    if (Key = Chr(32)) then
      DrivesClientDB.SetCheckedDrive(Integer((Sender as TListView).Items[Item.Index].SubItems.Objects[0]), Item.Checked);
  end;
end;

end.

Last edited by gclaxton (2011-05-27 22:45:37)

ab · 2011-05-28 08:31:08

I'll take a look at your code.

About the documentation, just search the forum:
http://synopse.info/forum/viewtopic.php?id=55

Here is an extract of what I wrote in this documentation, about the ORM approach (the latest version of SynProject is able to copy some content as HTML or BBCODE):

ORM is not DB

It's worth emphasizing that you should not think about the ORM like a mapping of an existing DB schema. This is an usual mistake in ORM design.

The database is just one way of your objects persistence:
- Don't think about tables with simple types (text/number...), but objects with high level types;
- Don't think about Master/Detail, but logical units;
- Don't think "SQL", think about classes;
- Don't wonder "How will I store it", but "Which data do I need".

For instance, don't be tempted to always create a pivot table (via a TSQLRecordMany property), but consider using a dynamic array, TPersistent, TStrings or TCollection published property instead.

Or consider that you can use a TRecordReference property pointing to any registered class of the TSQLModel, instead of creating one TSQLRecord property per potential table.

Objects, not tables

With an ORM, you should usually define less tables than in a "regular" relational database, because you can use the high-level type of the TSQLRecord properties to handle some per-row data.

The first point, which may be shocking for a database architect, is that you should better not create Master/Detail tables, but just one "master" object with the details stored within, as JSON, via dynamic array, TPersistent, TStrings or TCollection properties.

Another point is that a table is not to be created for every aspect of your software configuration. Let's confess that some DB architects design one configuration table per module or per data table. In an ORM, you could design a configuration class, then use the unique corresponding table to store all configuration encoded as some JSON data, or some DFM-like data. And don't hesitate to separate the configuration from the data, for all not data-related configuration - see e.g. how the SQLite3Options unit works. With our framework, you can serialize directly any TSQLRecord or TPersistent instance into JSON, without the need of adding this TSQLRecord to the TSQLModel list. Since revision 1.13 of the framework, you can even define a TPersistent published property in your TSQLRecord class, and it will be automatically serialized as TEXT in the database.

Methods, not SQL

At first, you should be tempted to write code as such (this code sample was posted on our forum, and is not bad code, just not using the ORM orientation of the framework):

DrivesModel := CreateDrivesModel();
GlobalClient := TSQLRestClientDB.Create(DrivesModel, CreateDrivesModel(), 'drives.sqlite', TSQLRestServerDB);
TSQLRestClientDB(GlobalClient).Server.DB.Execute(
'CREATE TABLE IF NOT EXISTS drives ' +
'(id INTEGER PRIMARY KEY, drive TEXT NOT NULL UNIQUE COLLATE NOCASE);');
for X := 'A' to 'Z' do
begin
TSQLRestClientDB(GlobalClient).Server.DB.Execute(
'INSERT OR IGNORE INTO drives (drive) VALUES ("' + StringToUTF8(X) + ':")');
end;

Please, don't do that!

The correct ORM-oriented implementation should be the following:

DrivesModel := TSQLModel.Create([TDrives], 'root');
GlobalClient := TMyClient.Create(DrivesModel, nil, 'drives.sqlite', TSQLRestServerDB);
GlobalClient.CreateMissingTables(0);
if GlobalClient.TableRowCount(TDrives)=0 then
begin
D := TDrives.Create;
try
for X := 'A' to 'Z' do
begin
D.Drive := X;
GlobalClient.Add(D,true);
end;
finally
D.Free;
end;
end;

In the above lines, no SQL was written. It's up to the ORM to:
- Create all missing tables, via the CreateMissingTables method - and not compute by hand a "CREATE TABLE IF NOT EXISTS..." SQL statement;
- Check if there is some rows of data, via the TableRowCount method - instead of a "SELECT COUNT(*) FROM DRIVES";
- Append some data using an high-level TDrives Delphi instance and the Add method - and not any "INSERT OR IGNORE INTO DRIVES...".

Then, in order to retrieve some data, you'll be tempted to code something like that (extracted from the same forum article):

procedure TMyClient.FillDrives(aList: TStrings);
var
table: TSQLTableJSON;
X, FieldIndex: Integer;
begin
table := TSQLRestClientDB(GlobalClient).ExecuteList([TSQLDrives], 'SELECT * FROM drives');
if (table <> nil) then
try
FieldIndex := table.FieldIndex('drive');
if (FieldIndex >= 0) then
for X := 1 to table.RowCount do
Items.Add(UTF8ToString(table.GetU(X, FieldIndex)));
finally
table.Free;
end;
end;

Thanks to the TSQLTableJSON class, code is somewhat easy to follow. Using a temporary FieldIndex variable make also it fast inside the loop execution.

But it could also be coded as such, using the CreateAndFillPrepare then FillOne method in a loop:

procedure TMyClient.FillDrives(aList: TStrings);
begin
aList.BeginUpdate;
try
aList.Clear;
with TSQLDrives.CreateAndFillPrepare(GlobalClient,'') do
try
while FillOne do
aList.Add(UTF8ToString(Drive));
finally
Free;
end;
finally
aList.EndUpdate;
end;
end;

We even added the BeginUpdate / EndUpdate VCL methods, to have even cleaner and faster code (if you work with a TListBox e.g.).

Note that in the above code, an hidden TSQLTableJSON class is created in order to retrieve the data from the server. The abstraction introduced by the ORM methods makes the code not slowest, but less error-prone (e.g. Drive is now a RawUTF8 property), and easier to understand.

But ORM is not perfect in all cases.

For instance, if the Drive field is the only column content to retrieve, it could make sense to ask only for this very column. One drawback of the CreateAndFillPrepare method is that, by default, it retrieves all columns content from the server, even if you need only one. This is a common potential issue of an ORM: since the library doesn't know which data is needed, it will retrieve all object data, which is some cases is not worth it.

You can specify the optional aCustomFieldsCSV parameter as such, in order to retrieve only the Drive property content, and potentialy save some bandwidth:

with TSQLDrives.CreateAndFillPrepare(GlobalClient,'','Drive') do

Note that for this particular case, you have an even more high-level method, handling directly a TStrings property as the recipient:

procedure TMyClient.FillDrives(aList: TStrings);
begin
GlobalClients.OneFieldValues(TSQLDrives,'drive','',aList);
end;

The whole query is made in one line, with no SELECT statement to write.

For a particular ID range, you may have written, with a specific WHERE clause using a prepared statement:

GlobalClients.OneFieldValues(TSQLDrives,'drive',
'ID>=:(%): AND ID<=:(%):',[aFirstID,aLastID],aList);

It's certainly worth reading all the (verbose) interface part of the SQLite3Commons.pas unit, e.g. the TSQLRest class, to make your own idea about all the high-level methods available. In the following pages, you'll find all needed documentation about this particular unit. Since our framework is used in real applications, most useful methods should already have been made available. If you need additional high-level features, feel free to ask for them, if possible with source code sample, in our forum, freely available at http://synopse.info

Think multi-tier

And don't forget the framework is able to have several level of objects, thanks to our Client-Server architecture - see 6. Such usage is not only possible, but strongly encouraged.

You should have business-logic level objects at the Client side. Then both business-logic and DB objects at the Server side.

If you have a very specific database schema, business-logic objects can be of very high level, encapsulating some SQL views for reading, and accessed via some RESTful service commands for writing - see 11.

Another possibility to access your high-level type, is to use either custom SQLite3 SQL functions either stored procedures - see 23 - both coded in Delphi.

flydev · 2022-01-10 13:50:26

Sorry to write on a suck ten years old thread, but it will be easier to me to stumb on again, and i think it should pinned

Reading it again made me realized I took a bad direction on a feature of the software I am working on, even if I read ten times the doc since my first mORMot introduction.

tbo · 2022-01-10 14:01:59

Thanks for posting the picture. It made me smile. The only problem is that the road between theory and practice can be long.

With best regards
Thomas

flydev · 2022-01-10 14:25:32

True, and so, with the documentation chapter #13.2, the #6.2.4 sentence and the following blogpost, the road should be shorter:

https://blog.synopse.info/?post/2013/02 … SQL-access

Last edited by flydev (2022-01-10 14:27:06)

mORMot Open Source

#1 2011-05-27 22:44:22