mORMot Open Source

corchi72

Member

Registered: 2010-12-10

Posts: 232

connection to the same database by multiple users simultaneously

I would like to make a program in Delphi where more people use the same database "sqlite" simultaneously. What classes should I use? I'm forced to create a server?
I checked the save and re-read the tables is much slower, I was wrong to declare the type of table ,all tables are declared class (TSQLRecord).To save and share data with other users use  globalClient.Update(XXX), where xxx is a class declared xxxx= class (TSQLRecord).

Thanks

1)first example of a connection without a server

program TestSqlite;
...
procedure InitClient(filename:String);
begin
  Model:= CreateTestModel;
  globalClient:= TSQLRestClientDB.Create(Model, CreateTestModel, filename, TSQLRestServerDB);
  TSQLRestClientDB(globalClient).Server.CreateMissingTables(0);
end;

2)in the second example I created two programs, a client and a server

program TestSQliteServer;

uses
  Forms,
  FileTables in 'SQLite\FileTables.pas',
  Unit2 in 'Unit2.pas' {Form1};

{$R *.res}

begin
  Application.Initialize;
  Application.CreateForm(TForm1, Form1);
  Application.Run;
end.

unit Unit2;

interface

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

type
  TForm1 = class(TForm)
    Label1: TLabel;
    Button1: TButton;
    Label2: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
    procedure FormShow(Sender: TObject);
  private
  public
    Model: TSQLModel;
    Server: TSQLRestServerDB;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
  Close;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
  Model := CreateTestModel;
  Server := TSQLRestServerDB.Create(Model,'Test.db3');
  Server.CreateMissingTables(0);
  if Server.ExportServerNamedPipe('03') then
    Caption := format('%s - %s Connected',[Caption,Server.DB.FileName ]);

end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  Server.Free;
  Model.Free;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
  Label1.Caption := Caption;
end;

////////

program TestSQliteClient;
...
InitClient('\\severTest\pipe\Sqlite3_03');

end;

procedure InitClient(filename:String);
begin
  Model:= CreateTestModel;
  globalClient:= TSQLRestClientURINamedPipe.Create(Model,filename); //for connection to server "severTest"
  TSQLRestClientDB(globalClient).Server.CreateMissingTables(0);
 
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: connection to the same database by multiple users simultaneously

I'm not sure concurrent direct access is a good idea.
The framework design was not prepared nor optimized for this kind of use.
Even the options used during the SQLite3 compilation from c source code was intended not to have direct concurrent access to the same database file, but will rely on a server orientation.

The Client/Server approach will be both safer and faster, even on the local machine:
- For example, the internal caching of records will be shared server-side, so it will be preferred to spare RAM.
- The auto-refreshing mechanism relies on the Server orientation of the framework - with a multiple direct connection, you'd miss some data refresh.
- And the client executable size will be smaller, because it won't contain the SQLite3 engine itself, which will be shared only on the server side.
- And so on...

So the client/server approach could be faster and lighter than a multiple direct connection, IMHO.

If you need speed and local run (no network), uses GDI message communication instead of named pipes. It's fast and light.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: connection to the same database by multiple users simultaneously

ok thanks I think I understand