Posts by delphinium

delphinium · mORMot 1

Seem there's no more array of TSQLRecordClass 1st parameter in the create constructors, so you can remove the [] from line 278 of SynDBExplorerFrame.pas and it is working again. On the changes reason, guess
ab and other experts involved will share with us . Mormot is moving really fast better than many commercial products

Change TSQLTableJSON.Create([], '',pointer(fJSONBuffer),length(fJSONBuffer)); to
TSQLTableJSON.Create('',pointer(fJSONBuffer),length(fJSONBuffer));

delphinium · mORMot 1

For SynDBExplorer we may need to quote the table names.
Perhaps http://synopse.info/fossil/info/1c830102ba (and http://synopse.info/fossil/info/e93758fc1e BTW) are enough.

Yup changes in both procedure are enough for SynDBExplorer. I have slightly change it and your input is always welcome.

SynDB.pas 
function TSQLDBConnectionProperties.SQLTableName(const aTableName: RawUTF8): RawUTF8;
var
  BeginQuoteChar, EndQuoteChar: RawUTF8;
  bUseQuote: boolean;
begin
  BeginQuoteChar := '"';
  EndQuoteChar := '"';
  bUseQuote := False; // flag to always use quote for tablename - 
  case fDBMS of
    dPostgresql:
      if PosEx('.', aTablename) = 0 then  // if schema.identifier format - then dont quote
        bUseQuote := True;
    dMySQL: begin
      BeginQuoteChar := '`';  // backtick/grave accent
      EndQuoteChar := '`';
    end;
    dJet: begin  // ,dMSSQL begin // MSSQL can SET IDENTIFIER ON to use doublequotes 
      BeginQuotechar := '[';
      EndQuoteChar := ']';
    end;
  end;

  if bUseQuote and (PosEx(BeginQuoteChar, aTableName) = 0) then
    result := BeginQuoteChar + aTableName + EndQuoteChar
  else if (PosEx(' ', aTableName) > 0) and (PosEx(BeginQuoteChar, aTableName) = 0) then  // not quoted tablename with space
    result := BeginQuoteChar + aTableName + EndQuoteChar else
    result := aTableName;
end;

delphinium · mORMot 1

Sorry for my english, let me try

Table/field/function names are identifiers in PG (http://www.postgresql.org/docs/current/ … DENTIFIERS). PG treats identifiers case insensitively when not quoted
(PG folds them to lowercase internally), and case sensitively when quoted; hence it is advisable to always use lowercase during schema creation and you are safe. Comes back to our case, unless Zeos metadata
can return exactly the schema namely with/without quotes, we might get the "ERROR: relation "xx" does not exist", so what I did was just a workaround with the assumption that the identifier letters' retrieved by
Zeos metadata engine were correct and treat all with quotes hence case-sensitive check. It was not the best way as I had stated and because of that i reported the issue in hope that expert like you could comes
in . On your uppercase removed fix, the issue is still there for identifier created with quotes. Hmpph, I think the issue is more to Zeos than mormot, maybe you can just put as a note in the documentation to inform
user. Just to share with you, FireDAC metadata returns exactly the schema with/without quotes and don't have the issue (SynDBExplorer); and err.. SynDBFireDac.GetTablenames does store quotes into the tables var
parameter (if quotes are used in the schema).

delphinium · mORMot 1

greeting everyone,

I am at 12 - SynDBExplorer and added FireDAC and ZeosDBO into the connection selection. It seems Zeos-PG metadata does not return the exact table names (sometime double quotes needed) from postgresql.
Due to postgresql is quite particular in that I have modified SynDBZeos to always use double quotes and normal case. It is just a workaround (the least changes in mormot that my limited brain can think of) and hopefully the gurus can
share their solution though i think the best way is Zeos/ZDBC metadata returns the exact case with/without double quotes.

SynDBZeos.pas
procedure TSQLDBZEOSConnectionProperties.GetTableNames(var Tables: TRawUTF8DynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    TableTypes: Types.TStringDynArray;
    n: integer;
begin
  if GetDatabaseMetadata(meta) then begin
    ...
    ... 
    while res.Next do
      if fDBMS = dPostgreSQL then  // added line: 341
        AddSortedRawUTF8(Tables,n, '"' + SynUnicodeToUtf8(res.GetUnicodeString(3)) + '"')  // line 342 
      else
        AddSortedRawUTF8(Tables,n,UpperCase(SynUnicodeToUtf8(res.GetUnicodeString(3))));  // default
    SetLength(Tables,n);
  end else
    inherited;
end;

delphinium · mORMot 1

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

delphinium · mORMot 1

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 .

delphinium · mORMot 1

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"
}

delphinium · mORMot 1

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..

delphinium · mORMot 1

Yes, I am reading the latest 1.18 and downloaded the latest nightly build. I am doing it in my free time as of now and in the very early stage testing with TDynArray/TSynLog, so far so good and i like the dynamic array wrapper which can be used even in my existing codes to increase performance and to make the codes like handling TList . In Malaysia we have "Tupai" (squirrel in English), I think there are cousin/maybe nephew to Mormot no ?

delphinium · mORMot 1

Thanks for your advise, I should have finished the documentation and search the forum before posting. I will be back after that

delphinium · mORMot 1

I am totally new to Mormot so please bear with me. Currently I am at page 176 of the documentation and by right I should finish reading before I ask questions, however seeing that i am still way to go before i can finish and my not so intelligent brain keep asking questions so i just try my luck if by any chance you have any step-by-step guide to build database application from scratch and how to implement deltas changes/reconcile errors and pagination (like packetrecord in datasnap) the mormot way. In rtc (Http server hence stateless too) i store the pagination information like the last fetched unique id at the client and pass the information to the rtc server and it will issue SQL statement something like SELECT col1, col2.. coln FROM table WHERE (id > :lastfetchid) ORDER BY id ASC LIMIT :pagerows' to the postgresql DB server.

I am happy to listen to any suggestion and am willing to adapt to Mormot ways because I am looking forward to Mormot/FireDac/Postgresql + SMS combinations for my future projects. Oh yeah, This is my 1st post so please accept my gratitude for your great works and best wishes from Malaysia.

mORMot Open Source

#1 Re: mORMot 1 » project SynDBExplorer not compiling, Syntax error. » 2013-12-26 12:35:02

#2 Re: mORMot 1 » SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG » 2013-11-21 04:29:07

#3 Re: mORMot 1 » SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG » 2013-11-20 17:43:07

#4 mORMot 1 » SynDBExplorer: ERROR: relation "tablename" does not exist - ZEOS-PG » 2013-11-20 06:02:13

#5 Re: mORMot 1 » mORMot + ZEOS + PostgreSQL » 2013-11-14 10:59:35

#6 Re: mORMot 1 » mORMot + ZEOS + PostgreSQL » 2013-11-13 10:08:11

#7 Re: mORMot 1 » mORMot + ZEOS + PostgreSQL » 2013-11-12 13:04:24

#8 Re: mORMot 1 » mORMot + ZEOS + PostgreSQL » 2013-11-10 09:14:56

#9 Re: mORMot 1 » Newbie coming from RtcSdk/Datasnap » 2013-11-06 13:11:10

#10 Re: mORMot 1 » Newbie coming from RtcSdk/Datasnap » 2013-11-05 20:32:52

#11 mORMot 1 » Newbie coming from RtcSdk/Datasnap » 2013-11-05 18:09:26

Board footer