mORMot Open Source

imperyal

Member

Registered: 2018-10-11

Posts: 51

Firebird via Zeos no user table and unique fields

Hello, I'm starting with mORMot and now I'm trying to use a firebird database (Zeos). My AuthUser/AuthGroup tables are not being created, what's missing from that code below?

var
  fbDatabase: TSQLDBZEOSConnectionProperties;
begin
  Model  := CreateDataModel;

  fbDatabase := TSQLDBZEOSConnectionProperties.Create(
                TSQLDBZEOSConnectionProperties.URI(dFirebird, '', '.\Firebird\fbclient.dll', true),
                'fbDB.fdb',
                'sysdba',
                'masterkey');

  fbDatabase.ThreadingMode := tmMainConnection;

  VirtualTableExternalRegisterAll(Model, fbDatabase,[regMapAutoKeywordFields]);

  Server := TSQLRestServerDB.Create(Model, SQLITE_MEMORY_DATABASE_NAME, true);

  Server.CreateMissingTables;

  AddToServerWrapperMethod(Server, ['D:\Dev\mormot\CrossPlatform\templates']);

  HTTPServer := TSQLHttpServer.Create('8080', [Server], '+', useHttpApiRegisteringURI);
  HTTPServer.AccessControlAllowOrigin := '*';

One other difficulty I'm having with Firebird is that I can't index/unique my string fields (RawUTF8), I'm getting errors with the TSQLRecord below:

  TSQLClienteRecord = class(TSQLRecord)
  private
    fNome:  RawUTF8;
    fIdade: Int64;
    fNotas: RawUTF8;
  published
    property Nome:  RawUTF8 read fNome  write fNome stored AS_UNIQUE;
    property Idade: Int64   read fIdade write fIdade;
    property Notas: RawUTF8 read fNotas write fNotas;
  end;

These are some newbie questions I'm sure, sorry... And thank you

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: Firebird via Zeos no user table and unique fields

Hi,

have a look @ https://firebirdsql.org/file/documentat … types.html
section Character Indexes.

Most DB's (except SQLite, Postgre) do limit indices to size of the variable character string. As documented the maximum length for an index equals one quarter of the page size.

your field nome is a unlimited blob subtype text. FireBird can't create an index here. The more fetching such data is slower than fetching a varchar(255) f.e. For all known dbs except (except SQLite, Postgre).

limit your fieldsize to a value you need in reality:

  property Nome:  RawUTF8 index 255 read fNome  write fNome stored AS_UNIQUE

to be clear: You need to re-create this table or do the altering manually. Just change the code as suggested would not resolve your problem.

imperyal

Member

Registered: 2018-10-11

Posts: 51

Re: Firebird via Zeos no user table and unique fields

That makes perfect sense, I was just switching from SQLite to Firebird for some performance testing and that never came to mind.

Do you have any idea way the user tables are not being created on when using Firebird? They are created normally when I switch to SQLite.

Thank you!

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: Firebird via Zeos no user table and unique fields

Firebird embedded will always be slower than the internal SQlite3 engine in off/exclusive mode.

About the tables, they are created using the Firebird metadata - perhaps there is a problem here.
Use the debugger to see what happens.

imperyal

Member

Registered: 2018-10-11

Posts: 51

Re: Firebird via Zeos no user table and unique fields

I can´t see the tables on firebird, but the authentication works, I can add users and login with them... I opened all tables in firebird and I can't find the users.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: Firebird via Zeos no user table and unique fields

I can´t see the tables on firebird, but the authentication works, I can add users and login with them... I opened all tables in firebird and I can't find the users.

Most likely this is problems with transactions - it's not committed on the server-side.

imperyal

Member

Registered: 2018-10-11

Posts: 51

Re: Firebird via Zeos no user table and unique fields

On my experiments I added the Auth tables to my model. That fixed the issue. Looks like the behavior is different when using SQLite, the Auth tables are created if you are using authentication even if you don’t add the tables to the model... Hope that helps.