mORMot + ZEOS + PostgreSQL

moctes · 2013-11-10 00:51:42

Hi to all,

Has anyone done any testing with mORMot, ZEOS and PostgreSQL combo? I'm trying to run the sample "15 - External DB performance" without success, first my environment :

Windows 7 64 bits on a VM with 2GB RAM
PostgreSQL 9.3.1 64 bits
Delphi XE3
Zeos 7.1 stable (7.1.2)
mORMot 1.18

I modified the sample project:

1. Enabled USEZEOS directive
2. Commented the tests for SQLLite/Firebird using ZEOS
3. Added the following test :
Test(TSQLDBZEOSConnectionProperties,
TSQLDBZEOSConnectionProperties.URI(dPostgreSQL,'libpq.dll'),'test','postgres','nnnn','',false); //<--- Compiling the tests with 32 bits profile so libpq.dll is 32 bits

4. I came accross this constructor on SynDBZeos :

constructor TSQLDBZEOSConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8);
const
  PCHARS: array[0..6] of PUTF8Char = (
    'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE');
  TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
    dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite);
  // expecting Postgresql + Sybase + ASA support in TSQLDBDefinition
begin
  fServerName :=  aServerName;
  if (fServerName<>'') and (PosEx(':',fServerName)=0) then
    fServerName := fServerName+':';
  if not IdemPChar(Pointer(aServerName),'ZDBC:') then
    fServerName := 'zdbc:'+fServerName;
  fURL := TZURL.Create(UTF8ToString(fServerName));
  if fURL.Database='' then
    fURL.Database := UTF8ToString(aDatabaseName);
  if fURL.UserName='' then
    fURL.UserName := UTF8ToString(aUserID);
  if fURL.Password='' then
    fURL.Password := UTF8ToString(aPassWord);
  fDBMSName := StringToUTF8(fURL.Protocol);
  fDBMS := TYPES[IdemPCharArray(pointer(fDBMSName),PCHARS)];
  inherited Create(aServerName,aDatabaseName,aUserID,aPassWord);
  fURL.Properties.Add('controls_cp=CP_UTF8');
  fUseCache := false; // caching is to be disabled - not found stable enough
  case fDBMS of
  dSQLite: begin // ZEOS support of SQLite3 is just buggy
    fSQLCreateField[ftInt64] := ' BIGINT'; // SQLite3 INTEGER = 32bit for ZDBC!
  end;
  dFirebird: begin
    if not FileExists(fURL.Database) then
      fURL.Properties.Add('createNewDatabase='+UTF8ToString(
        SQLCreateDatabase(StringToUTF8(fURL.Database))));
    fUseCache := true; // caching rocks with Firebird ZDBC provider :) 
  end;
  dOracle:
    fUseCache := true;
  end;
end;

As you can see neither PCHARS nor TYPES contains PostgreSQL driver related references, and this is what is passed to aServerName on constructor TSQLDBConnectionProperties :
postgresql-9:?LibLocation=C:\CM\mORMot\SQLite3\Samples\15 - External DB performance\libpq.dll

and finally on mORMot SQLite3 the line :

        DB.Execute(Model.GetSQLCreate(t)); // don't catch exception in constructor

Breaks the execution with the message :

Requested database driver was not found.

Which seems is an exception of ZEOS library because is not locating libpq.dll but I don't think is the case because the DBC sample included with ZEOS components is connecting just fine with the following URL: 'zdbc:postgresql-9://localhost/test?UID=postgres;PWD=nnnn' which doesn't look like the one constructed by TSQLDBZEOSConnectionProperties so I'm concluding that ZEOS + PostgreSQL support on mORMot is incomplete.

I don't know if others have succeeded with this but I would like to know.

Thank you

delphinium · 2013-11-10 09:14:56

Hi moctes,

Coincidentally I am testing with sample 15 and using Zeos to access PG. Maybe we can share our finding together while waiting for the expert. I am a newbie to Mormot and here's my findings :
1. Test(TSQLDBZEOSConnectionProperties, TSQLDBZEOSConnectionProperties.URI(dPostgreSQL), 'test', 'postgres','nnnn','',false); - the second parameter for TSQLDBZEOSConnectionProperties.URI is the librarypath, if yr pg files are in your OS path or in your application folder you don't need specify the path or you can specify the full path for it.
2. You are right there's no mapping for PG and what i did was to change a little bit of PCHARS/TYPES (will leave it to the expert for the correct implementation)

PCHARS: array[0..7] of PUTF8Char = (
    'ORACLE','FREETDS_MSSQL','MSSQL','INTERBASE','FIREBIRD','MYSQL','SQLITE', 'POSTGRESQL-9');
TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
    dDefault,dOracle,dMSSQL,dMSSQL,dFirebird,dFirebird,dMySQL,dSQLite, dPostgreSQL);

After the changes above the example would be able to proceed with an internal exception when it tried to create unique index on ID column, the method was TSQLRestServer.CreateSQLMultiIndex (mormot.pas) and the SQL statement was 'CREATE UNIQUE INDEX postgres.IndexSampleRecordID ON SampleRecord(ID)' which I think should be public. or schemaname. Since today is Sunday at my place (family day), I will continue later and wishfully by then the experts can point us to the right direction or maybe you can share your finding with us if you were still finding..

Last edited by delphinium (2013-11-10 09:22:23)

ab · 2013-11-10 13:16:15

We did not test nor use PostGresql here yet.
Some users reported it to work, but not via ZEOS/ZDBC AFAIK.

Thanks to your report, I've enhanced PostgreSQL support via ZDBC, and fix error when using schema at index creation.
See http://synopse.info/fossil/info/bbe911d7d2

moctes · 2013-11-10 15:44:32

Thank you both and thanks Arnaud for answering in sunday :-D I wasn't expecting an answer until monday, I don't know if I can work on this today but I'll post results when I get the time.

Best regards.

delphinium · 2013-11-12 13:04:24

moctes,
I don't mean to sabatoge your post, just that we are in the same sample and I guess the same issues as well. Sorry yeah.

ab,
First of all thanks for your changes and perftest using ZEOS/ZDBC/PG runs fine. I tried with FireDac n Unidac as well because I want to know which one is the best to use with Mormot/PG combo. Here's the information - Unidac/PG with little changes was able to connect and table samplerecord was created however there were errors when the test was trying to add records : ExecuteFromJSON Invalid types[0] = 0 (mormotDB.pas line 1525). I stopped the debugging on Unidac/PG as of now because I think I am still far away to be able to correct the errors and guess I better goes back to the mormot manual . FireDAC/PG is working fine with refactoring of the unit names due to unit and class name changes from Anydac to FireDAC. From the test at my laptop it seems FireDAC batch performance far superior than ZDBC (is it because you are using FireDAC array DML), however on others ZDBC is better, hmpph any comment?

(synDBUnidac.pas)
constructor TSQLDBUniDACConnection.Create(aProperties: TSQLDBConnectionProperties);
begin
  inherited Create(aProperties);
  fDatabase := TUniConnection.Create(nil);
  fDatabase.ProviderName := UTF8ToString(fProperties.ServerName);
  case aProperties.DBMS of
  dSQLite, dFirebird, dPostgreSQL:  // ----> added dpostgresql otherwise the server property of UnidacConnection will be set to the databasename (Line 377)
    fDatabase.Database := UTF8ToString(fProperties.DatabaseName);
  else
    fDatabase.Server := UTF8ToString(fProperties.DatabaseName);
  end;
  fDatabase.Username := UTF8ToString(fProperties.UserID);
  fDatabase.Password := UTF8ToString(fProperties.PassWord);
  fDatabase.SpecificOptions.AddStrings(
    (fProperties as TSQLDBUniDACConnectionProperties).fSpecificOptions);
end;

Firedac unit name and class name changes

SynDBFireDac.pas 

FireDAC.Comp.Client, FireDAC.Stan.Param; // line 70
//uFDCompClient, uFDStanParam;

implementation

uses
  //uADPhysIntf, uADStanDef, uADDAptManager, uADStanAsync;
  FireDAC.Phys.Intf, FireDAC.Stan.Def, FireDAC.DApt, FireDAC.Stan.Async;  // line 203

Global replace TADConnection -> TFDConnection, TADQuery -> TFDQuery, TADMetaInfoQuery -> TFDMetaInfoQuery, TADPAram -> TFDParam

My Test results (3rd Gen Intel Core™ i7-3740QM Processor (6MB Cache, up to 3.7GHz w/ Turbo Boost 2.0), Windows 8 Single Language, English (64bit), 8GB 1600MHz DDR3 SDRAM)

FireDAC

{
    "Engine": "FireDAC",
    "CreateTableTime": "430.50ms",
    "NumberOfElements": 5000,
    "InsertTime": "1.78s",
    "InsertRate": 2796,
    "InsertBatchTime": "180.90ms",
    "InsertBatchRate": 27638,
    "InsertTransactionTime": "3.41s",
    "InsertTransactionRate": 1466,
    "InsertBatchTransactionTime": "184.01ms",
    "InsertBatchTransactionRate": 27171,
    "ReadOneByOneTime": "2.24s",
    "ReadOneByOneRate": 2222,
    "ReadOneByNameTime": "2.31s",
    "ReadOneByNameRate": 2156,
    "ReadAllVirtualTime": "81.75ms",
    "ReadAllVirtualRate": 61156,
    "ReadAllDirectTime": "61.51ms",
    "ReadAllDirectRate": 81284,
    "ClientCloseTime": "4.35ms"
}

ZDBC

{
	"Engine": "ZEOS",
	"CreateTableTime": "418.04ms",
	"NumberOfElements": 5000,
	"InsertTime": "1.67s",
	"InsertRate": 2982,
	"InsertBatchTime": "1.66s",
	"InsertBatchRate": 2994,
	"InsertTransactionTime": "803.36ms",
	"InsertTransactionRate": 6223,
	"InsertBatchTransactionTime": "829.35ms",
	"InsertBatchTransactionRate": 6028,
	"ReadOneByOneTime": "624.59ms",
	"ReadOneByOneRate": 8005,
	"ReadOneByNameTime": "692.38ms",
	"ReadOneByNameRate": 7221,
	"ReadAllVirtualTime": "42.77ms",
	"ReadAllVirtualRate": 116896,
	"ReadAllDirectTime": "31.26ms",
	"ReadAllDirectRate": 159933,
	"ClientCloseTime": "4.65ms"
}

Last edited by delphinium (2013-11-12 15:21:52)

moctes · 2013-11-12 18:41:29

Delphinium, nevermind I haven't had the time to test with Unidac but your numbers show that indeed ZEOS will be faster than Unidac, thanks for sharing.

ab · 2013-11-12 19:15:20

Thanks to your comments:

I have added PostgreSQL support with UniDAC Library.
See http://synopse.info/fossil/info/58ced0e1bb

And Delphi XE5 support for new FireDAC namespaces.
See http://synopse.info/fossil/info/45051a417f

Ensure you got the LATEST version of the libraries.
I did enhance MSSQL support for UniDAC provider, and AFAIR we fixed this "ExecuteFromJSON Invalid types[0] = 0" error.
See http://synopse.info/fossil/info/fc46e98a61

About the performance, it is the same as with MSSQL, Oracle and FireBird: FireDAC array DML is much faster than ZDBC, but reading is much slower than our classes.
This is due to the TDataSet bottleneck, especially for individual raw retrieval.
Perhaps we may temper a little bit this by tuning our SynDBDataset unit, but I'm doubtful we may come close to ZDBC (or our SynDBODBC.pas unit).

BTW, did you try PostgreSQL with SynDBODBC.pas ?

moctes · 2013-11-12 22:27:26

I'm not a big fan of ODBC, but in this case Would you say that it is worth?

ab · 2013-11-12 23:42:22

Our unit gave much better results than TDataset or Zeos, for reading data.

delphinium · 2013-11-13 10:08:11

ab,

Unidac need some small changes to make it works because TUniMetadata always failed to get the correct field_type (ftUnknown) and thus it would fall back to get the metadata through SQL default. Here's the little changes:

synDBUnidac.pas 
constructor TSQLDBUniDACConnectionProperties.Create(const aServerName, aDatabaseName, aUserID, aPassWord: RawUTF8); // i think it is better to have an overload constructor to pass the schemaname
....
...
case fDBMS of
  ...
  ...
  dPostgreSQL: begin  // <-- added this to force schemaname to "public" and unicodes  line: 229
    if ForcedSchemaName = '' then
      ForcedSchemaName := 'public';
    fSpecificOptions.Values['CharSet'] := 'UTF8';
    fSpecificOptions.Values['UseUnicode'] := 'true';
    fSpecificOptions.Values['CharLength'] := '2';
  end;

synDB.pas 
function TSQLDBConnectionProperties.SQLGetField(const aTableName: RawUTF8): RawUTF8;
var Owner, Table: RawUTF8;
    FMT: PUTF8Char;
begin
  result := '';
  case DBMS of
  ...
  dMSSQL, dMySQL, dPostgreSQL: FMT :=
    'select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,'+
    ' NUMERIC_SCALE, 0 INDEX_COUNT'+ // INDEX_COUNT=0 here (done via OleDB)
    ' from INFORMATION_SCHEMA.COLUMNS'+
    ' where UPPER(TABLE_SCHEMA) = ''%'' and UPPER(TABLE_NAME) = ''%'''; // added UPPER function to Table_Schema and Table_name line: 3870
  ...
  end;

  SQLSplitTableName(aTableName,Owner,Table);
  result := FormatUTF8(FMT,[UpperCase(Owner),UpperCase(Table)]);
end;

On ODBC I tried and the error was more or less the same with Unidac where when it falled back to SQL default and the schemaname was empty. I did not test further because
like moctes I am also not that keen with ODBC unless it is the preferred way in Mormot. Little tweak though to make it able to create SampleRecord table and then came the error

synDBODBC.pas
procedure TODBCConnection.Connect;
var Log: ISynLog;
    Len: SqlSmallint;
    Info: array[byte] of WideChar;
const
  PCHARS: array[0..7] of PUTF8Char = (
    'ORACLE','MICROSOFT SQL','ACCESS','MYSQL','SQLITE','FIREBIRD','INTERBASE', 'POSTGRESQL');  // -- added postgresql line 960
  TYPES: array[-1..high(PCHARS)] of TSQLDBDefinition = (
    dDefault,dOracle,dMSSQL,dJet,dMySQL,dSQLite,dFirebird,dFirebird, dPostgreSql);  // -- added dPostgresql
  .....

So far with my limited resources (only my laptop without network etc), Zeos is the overall best performer however when it comes to batch processing FireDAC reign supreme (due to it's unique array DML implementation which ab has incorporated into mormot - thanks). Unidac errr.. is the lowest in all categories so far. I will try on SynODBC after ab fixes the little bugs .

Last edited by delphinium (2013-11-13 10:29:54)

ab · 2013-11-13 16:41:08

I've committed your patches.
See http://synopse.info/fossil/info/785d87bb8e

Thanks for the feedback.

delphinium · 2013-11-14 10:59:35

Due to forcedschemaname to 'public' zeos needs a little tweaks

procedure TSQLDBZEOSConnectionProperties.GetFields(
  const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    n, i: integer;
    //TableName: string;  // remark 
    F: TSQLDBColumnDefine;
    FA: TDynArray;
    tablename, Owner: RawUTF8; // -- added
begin
  if GetDatabaseMetadata(meta) then begin
    //TableName := UTF8ToString(UpperCase(aTableName));  
    SQLSplitTableName(aTablename, owner, tablename); // due to forcedschemaname to public, atablename = 'public.tablename' so has to split it
    tableName := UTF8ToString(UpperCase(tablename));
    ..

Minor typos in SynDBFireDac.pas

TSQLDBFireDACConnection = class(TSQLDBConnectionThreadSafe)
..
public
  property Database: {$ifdef ISDELPHIXE5}TFDConnection{$else}TADConnection{$endif} read fDatabase;  //line 167
end;

implementation
...
type
  ...
  TADParams = TFDParams;
  TADPhysMetaInfoKind = TFDPhysMetaInfoKind;  // line 217

I tested 4 of them together and this should be it, Thanks alot ab, I can continue my study with mormot

ab · 2013-11-29 15:01:34

Thanks for the feedback.

See http://synopse.info/fossil/info/e02f768d51
and http://synopse.info/fossil/info/c2a95794b2

mORMot Open Source

#1 2013-11-10 00:51:42

mORMot + ZEOS + PostgreSQL

#2 2013-11-10 09:14:56

Re: mORMot + ZEOS + PostgreSQL

#3 2013-11-10 13:16:15

Re: mORMot + ZEOS + PostgreSQL

#4 2013-11-10 15:44:32

Re: mORMot + ZEOS + PostgreSQL

#5 2013-11-12 13:04:24

Re: mORMot + ZEOS + PostgreSQL

#6 2013-11-12 18:41:29

Re: mORMot + ZEOS + PostgreSQL

#7 2013-11-12 19:15:20

Re: mORMot + ZEOS + PostgreSQL

#8 2013-11-12 22:27:26

Re: mORMot + ZEOS + PostgreSQL

#9 2013-11-12 23:42:22

Re: mORMot + ZEOS + PostgreSQL

#10 2013-11-13 10:08:11

Re: mORMot + ZEOS + PostgreSQL

#11 2013-11-13 16:41:08

Re: mORMot + ZEOS + PostgreSQL

#12 2013-11-14 10:59:35

Re: mORMot + ZEOS + PostgreSQL

#13 2013-11-29 15:01:34

Re: mORMot + ZEOS + PostgreSQL

Board footer