Splitting tables between several SQLite3 databases

sgavrilov · 2019-05-24 07:58:25

Hello,

I am starting a new project and going to use mORMot in it. I plan to start with a SQLite3 data base and see how it goes. If it is necessary, I will switch to an external database later.

Since I have some mostly static data (reference tables, users, patients) and potentially significant amounts of various documents to store, I would like to split the tables between several db-files.

Of course, I can use the REST redirection but in this case I will not be able to use redirected tables in JOINs. I can live without it, if this is absolutely necessary, but sometimes JOINs might be helpful/convenient. So, I am thinking about VirtualTableExternalRegister and TSQLDBSQLite3ConnectionProperties.

Something like a sample below (see the constructor TMyRest.Create). However, I was not able to find a definitive answer on thread-safety of this approach. Will this work correctly? Will data access requests be serialized to each db separately or to the whole SQLIte3 engine?

Thank you in advance.

Best regards,
Sergey

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  mORMot,
  mORMotHTTPServer,
  System.SysUtils,
  SynSQLite3Static,
  Unit2 in 'Unit2.pas';

var
  MyRest: TMyRest;

begin
  MyRest := TMyRest.Create(CreateModel);
  try
    with TSQLHttpServer.Create('80', [MyRest], '+', useHttpApiRegisteringURI) do
      try
        Writeln('Server started.');
        Write('Press Enter to terminate...');
        Readln;
        Shutdown;
      finally
        Free;
      end;
  finally
    FreeAndNil(MyRest);
  end;
end.

unit Unit2;

interface

uses
  mORMot, mORMotSQLite3, SynCommons, SynDBSQLite3;

type

  TMyRest = class(TSQLRestServerDB)
  private

    FDocDBProps: TSQLDBSQLite3ConnectionProperties;
    FRefDBProps: TSQLDBSQLite3ConnectionProperties;

  public

    constructor Create(AModel: TSQLModel);

    destructor Destroy; override;

  end;

  TSQLBranch = class(TSQLRecord)
  private

    FName: RawUTF8;

  published

    property Name: RawUTF8  read FName  write FName;

  end;

  TSQLDocument = class(TSQLRecord)
  private

    FBranch: TSQLBranch;
    FName:   RawUTF8;

  published

    property Branch: TSQLBranch  Read FBranch  write FBranch;

    property Name: RawUTF8  read FName  write FName;

  end;

function CreateModel: TSQLModel;

implementation

uses
  System.SysUtils,
  mORMotDB, SynSQLite3;

function CreateModel: TSQLModel;
begin
  Result := TSQLModel.Create([
    TSQLAuthGroup,  // Main
    TSQLAuthUser,   // Main
    TSQLBranch,     // References
    TSQLDocument    // Documents
  ], 'master')
end;

function DBFileName(const aFileName: String): TFileName;
begin
  Result := IncludeTrailingPathDelimiter(ExeVersion.ProgramFilePath) + aFileName + '.db';
end;

constructor TMyRest.Create(AModel: TSQLModel);

  function createProps(const aFileName: String): TSQLDBSQLite3ConnectionProperties;
  begin
    Result := TSQLDBSQLite3ConnectionProperties.Create(StringToUTF8(DBFileName(aFileName)), '', '', '');
    with Result.MainSQLite3DB do
      begin
        Synchronous := smOff;
        LockingMode := lmExclusive;
      end;
  end;

begin
  FDocDBProps := createProps('Documents');
  VirtualTableExternalRegister(AModel, TSQLDocument, FDocDBProps);

  FRefDBProps := createProps('References');
  VirtualTableExternalRegister(AModel, TSQLBranch, FRefDBProps);

  inherited Create(AModel, DBFileName('Main'));
  AModel.Owner := Self;
  DB.LockingMode := lmExclusive;
  DB.Synchronous := smOff;

  CreateMissingTables;
end;

destructor TMyRest.Destroy;
begin
  inherited Destroy;
  FreeAndNil(FRefDBProps);
  FreeAndNil(FDocDBProps);
end;

end.

edwinsn · 2019-05-25 06:54:13

I don't think it's possible with SQLite?

sgavrilov · 2019-05-25 12:01:34

As far as I can understand, if the default amLocked mode does not work for SQLite3 connections, I can at least try to put all ORM writes into a single thread:

aServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

Therefore, all write operations will be queued and it will not matter whether the connections are really threadsafe or not. Am I right?

edwinsn · 2019-05-25 15:05:39

if the db is sqlite, I think all write operations to the db are coordinated with a lock, which means it's thread-safe but the writes are not parallel.

sgavrilov · 2019-05-26 17:07:18

Well, it does not work. But for a different reason.

When I try to run a SELECT query on an "external" SQLite3 table, it is not possible to reference the ID property with a table name.

This works:

tbl := Rest.List([TSQLDocument], 'ID,Name');

but this does not:

tbl := Rest.List([TSQLDocument], 'Document.ID,Document.Name');

The code raises an exception: 'Error SQLITE_ERROR (1) [SELECT Document.ID,Document.Name FROM Document] using 3.27.2 - no such column: Document.ID, extended_errcode=1'.

This code does not work either:

tbl := Rest.List([TSQLDocument], 'ID,Document.Name');

So the JOINs with IDs are not possible.

I tried to inherit the storage objects from TSQLRecordVirtualTableAutoID (this is not really necessary since the VirtualTableExternalRegister sets the Props.Kind := rCustomAutoID) but it did not help.

So, it appears that there is a bug somewhere...

Last edited by sgavrilov (2019-05-26 17:08:35)

ab · 2019-05-26 21:28:48

The workaround is to attach the SQLite3 databases, not to define them as external.

OR define one TSQLRestServerDB per SQLite3 database instance.

mORMot Open Source

#1 2019-05-24 07:58:25

Splitting tables between several SQLite3 databases

#2 2019-05-25 06:54:13

Re: Splitting tables between several SQLite3 databases

#3 2019-05-25 12:01:34

Re: Splitting tables between several SQLite3 databases

#4 2019-05-25 15:05:39

Re: Splitting tables between several SQLite3 databases

#5 2019-05-26 17:07:18

Re: Splitting tables between several SQLite3 databases

#6 2019-05-26 21:28:48

Re: Splitting tables between several SQLite3 databases

Board footer