mORMot Open Source

I understand you can scale mORmot by having multiple services each on a different server.

Is it possible to have the same service on more than one server (with the database eg MS SQL on a single separate server) to help spread the load of the service ?

Also, if you can have the same service on more than one server is there any way to provide service resilience so that if a service was to stop functioning (eg server hardware failure) so the client could cleanly switch over to another functioning server providing the same service ? I suspect this one may not be easily possible and it may be better to let the client restart fresh.

I'm now building a large (automated) schema for my ctree server with about 170 tables. I'm having some problems with field names that are reserved words in SQL. Eg "order"

I have editoed the enumerations that specify the server types and have added my ctree definition and filled in all the entries that had compiler errors. In SynDB.pas theres the class function TSQLDBConnectionProperties.IsSQLKeyword and I have added a few of the reserved words into it (including order) and have made sure they are listed in alphabetical order. Assuming it locates a reserved word in there, what should it do with the reserved word ?

In ctree, you use double quotes to escape reserved words but I'm not seeing them get escaped.

Also in SynDB is TSQLDBConnectionProperties.SQLTableName which has a BeginQuoteChar and EndQuoteChar which for me is using a back tick character (`) to eascape. This appears to work for ctree (possibly as I'm using the ODBC driver and perhaps it's doing a translate of the back tick).

Is there anywhere else I should look to see how it's handling reserved word escaping ?

I'm having some success with getting the ctree odbc drivers to work with mormot. I'm sure I have a way to go yet, but I can declare TSQLRecord structures that seem to be working for creating, updating and retrieving records including joins.

For reference, faircom uses double quotes to escape reserved words and the field names/reserved words are not case sensitive.

My current problem is that in one of our tables we have a field named "desc" which is a reserved word in SQL and the name needs to be mapped (we also have a field named "key" that I map to "ID")

The code looks like this

  lOdbc := TODBCConnectionProperties.Create('ctree','MATER','ADMIN','ADMIN');
  lModel := TSqlModel.Create([TSQL_Bank]);
  VirtualTableExternalRegisterAll(lModel, lOdbc);
  RegisterCtreeTables(lModel, lOdbc);
  VirtualTableExternalMap(aModel,TSQL_Bank ,aODBC,'_Bank' ).MapField('id','"key"');
  VirtualTableExternalMap(aModel, TSQL_Bank, aODBC, '_Bank').MapField('description','"desc"');
  lRest := TSQLRestServerDB.Create(lModel);
  lRest.CreateMissingTables;

and the record looks like this

TSQL_Bank = Class(TSQLRecord)
  private
    FCode: String;
    FWhoCreated: Integer;
    FDateCreated: TDateTime;
    FDescription: String;
  Published
    Property Code: String Read FCode Write FCode;
    Property Description: String Read FDescription Write FDescription;
    Property WhoCreated: Integer Read FWhoCreated Write FWhoCreated;
    Property DateCreated: TDateTime Read FDateCreated Write FDateCreated;
  End;

I seem to always get an ODBC exception saying "Column id not found" but only when Desc is mapped as well as Key.

The SQL prepared in the TODBCStatement.Prepare 1st call is

select col,coltype,width,width,1,0 from syscolumns where tbl = '_bank'

which returns the following in the ctree sql explorer

col          | coltype    | width| width|  1|  0| 

status       | integer    |     4|     4|  1|  0| 
key          | integer    |     4|     4|  1|  0| 
code         | character  |    10|    10|  1|  0| 
desc         | character  |    30|    30|  1|  0| 
whocreated   | integer    |     4|     4|  1|  0| 
datecreated  | timestamp  |     8|     8|  1|  0| 
description  | varchar    |    50|    50|  1|  0| 

8 record(s) selected

Key is there and gets mapped ok (I know this as if I remove the definition for desc it works correctly). desc is there too and as its mapped in the same way as key, I would have thought it would be ok.

The TSQLStatementPrepare gets called multiple times. The result above is for the 1st call. In the 2nd call it has SQL that is as follows

select top 1 ID from _Bank

If I remove the definition for Description, the 2nd call results in SQL that looks like this

select top 1 "key" as ID from _Bank

It looks as if without Description the field is correctly mapped, but somehow loses it's mapping when Description is added.

If I reverse the order of the VirtualTableExternalMap then the 3 calls to Prepare produce the following 2 lines of SQL

select col,coltype,width,width,1,0 from syscolumns where tbl = ''_bank''
select top 1 "key" as ID from _Bank

if I then execute

  lBank := TSQL_Bank.Create(lRest, 10);
  lBank.Free;

I get the following line of SQL in the prepare function

select "key" as ID,Code,Description,WhoCreated,DateCreated from _Bank where "key"=?

It's correctly mapped key, but has not mapped desc properly which should have been "desc as Description"

Are you able to offer suggestions on where I should look to resolve this.

I now have an https connection and I'd like to be able to do callbacks on it. From the documentation, the callbacks need websockets, but websockets are not available on http.sys which is how I'm doing https.

Is there a way to do callbacks over an https connection or is this not possible at this time ?

This is all except the uses of a test file I'm trying to make work with https

Type
  ICalculator = Interface(IInvokable) ['{EE3E7EDE-3FFA-4E63-B9DC-CE157074FCD4}']
    Function Add(n1,n2: Integer): Integer;
  End;

  TServiceCalculator = Class(TInterfacedObject, ICalculator)
  Public
    Function Add(n1,n2: Integer): Integer;
  End;

var
  aDatabaseFile: TFileName;
  aModel: TSQLModel;
  aServer: TSQLRestServerFullMemory;
  aHTTPServer: TSQLHttpServer;
{ TServiceCalculator }

function TServiceCalculator.Add(n1, n2: Integer): Integer;
begin
  Result := n1 + n2;
end;

begin
  TInterfaceFactory.RegisterInterfaces([TypeInfo(ICalculator)]);

  aModel := TSQLModel.Create([], 'rpc');
  aServer := TSQLRestServerFullMemory.Create(aModel);
  aServer.ServiceDefine(TServiceCalculator, [ICalculator], sicShared);
  aHTTPServer := TSQLHttpServer.Create('888',[aServer]);

  writeln(#13#10'Background server is running at http://localhost:888'#13#10+
          #13#10'Press [Enter] to close the server.');
  ConsoleWaitForEnterKey;
end.

I tried using makecert as per the documentation and I tried creating the certificates with openssl, and finally I tried using powershell with the following line

New-SelfSignedCertificate -DnsName "localhost" -CertStoreLocation "cert:\LocalMachine\My"

Each time, the certificates looked good and registered ok as per this

>netsh http show sslcert

SSL Certificate bindings:
-------------------------

    IP:port                      : 0.0.0.0:888
    Certificate Hash             : 51f0382a9ad387dc686ea41533434bedd72729dc
    Application ID               : {b9865d74-2679-4b58-96dd-8837e6021d54}
    Certificate Store Name       : (null)
    Verify Client Certificate Revocation : Enabled
    Verify Revocation Using Cached Client Certificate Only : Disabled
    Usage Check                  : Enabled
    Revocation Freshness Time    : 0
    URL Retrieval Timeout        : 0
    Ctl Identifier               : (null)
    Ctl Store Name               : (null)
    DS Mapper Usage              : Disabled
    Negotiate Client Certificate : Disabled
    Reject Connections           : Disabled
    Disable HTTP2                : Not Set
    Disable QUIC                 : Not Set
    Disable TLS1.3               : Not Set
    Disable OCSP Stapling        : Not Set

The documentation doesnt really say what's meant to be done with the appid (under windows 10 this seems to be a mandatory parameter). So what I did was this

I started up regedit and created and entry of "Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Classes\AppID\HttpsTest.exe" where HttpsTest.exe is the name of the executable from above. I created a string key for it named "AppID" and gave it a GUID of {B9865D74-2679-4B58-96DD-8837E6021D54} which I generated from delphi (just to create a clean guid).

Then from a command prompt, I ran

netsh http add sslcert ipport=0.0.0.0:888 certhash=51F0382A9AD387DC686EA41533434BEDD72729DC appid={B9865D74-2679-4B58-96DD-8837E6021D54}

which completed ok.

The thumbprint was obtained using mmc and double clicking on the certificate as per the mormot documentation.

I then ran up the server and pointer my browser to it at "https://localhost:888/rpc/calculator/add?n1=1&n2=2" to which chrome said "This site can’t provide a secure connection" however under the developer tools security option, it says "Certificate - valid and trusted" and "Resources - all served securely"

Using "telnet 127.0.0.1 888" and just pressing "/" I see the returned content in plain text, not encypted. Pointing telnet at "google.com 443" it returns encrypted data and not plain text.

Pointing a browser at "http://localhost:888/rpc/calculator/add?n1=1&n2=2" (ie not https) provides the correct result of

{"result":[3]}

I'm suspecting my setting up of the appid may be in error or perhaps my setting up of the server in the source above.

Does anyone have any suggestions ?

I'm trying to get some simple ORM to work. My TSQLRecord structures look like this

 TWorkstation = Class(TSQLRecord)
  private
    FName: String;
  Published
    Property Name: String Read FName Write FName;
  End;

  TService = Class(TSQLRecord)
  private
    FName: String;
    FWorkstation: TWorkstation;
  Published
    Property Name: String Read FName Write FName;
    Property Workstation: TWorkstation Read FWorkstation Write FWorkstation;
  End;

Where TService has a pointer to a TWorkstation.

With the following code

Var
  x: TWorkstation;
  y: TService;
Begin
  x := TWorkstation.Create;
  x.Name := 'WS1';
  FServer.Add(x,True);

  y := TService.Create;
  y.Name := 'Svc-1';

  y.Workstation := x;
  FServer.Add(y, True);
End

Ignoring the leaks, the end result is 2 sqlite tables with the correct fields defined, but the Workstation table has a huge number for the service entry. If instead, before the 2nd add I perform

  y.Workstation := Pointer(x.ID);

It gets the correct value of 1 in it. Is this the wrong way to go about adds like this or do I need to perform this odd operation before any writes of tables with pointers to other tables ?

I'm tyring to connect to a Faircom CTree SQL Server using ODBC.

Using the sample SynDBExplorer to test it out, I keep getting the following exception

---------------------------
Debugger Exception Notification
---------------------------
Project SynDBExplorer.exe raised exception class EODBCException with message 'TODBCConnection.Connect: unrecognized provider DBMSName=ctreeSQL DriverName=CTODBC9.DLL DBMSVersion=11.2.2.24260(Build-170219)'.
---------------------------
Break   Continue   Help   
---------------------------

Is this a message from mormot to say there is no support for this driver or should any/most ODBC drivers work and I'm providing incorrect information for the "New Connection" dialog ?

Ive just started with mormot and after a lot of reading, it feels like a great piece of software.

What I'm trying is a variation of some of the samples in the documentation. If I let the program run without delay (ie no breakpoints) it works as expected, but if I go to the debugger on code like the following on the client

procedure TForm12.Test;
Var
  ans: Integer;
begin
  ans := 0;
  GModel := TSqlModel.Create([], ROOT_NAME);
//  GClient := TSQLHttpClientWinSock.Create('127.0.0.1','888',GModel);
  GClient := TSQLHttpClientWebSockets.Create('127.0.0.1','888',GModel,100000,100000,100000);
  GClient.WebSocketsUpgrade('super secret');
  GClient.ServiceDefine([ICalculator], sicShared);
  if GClient.Services['Calculator'].Get(I) then
  Begin
    ans := I.Add(3,4);
  End;
  Memo1.Lines.Add(IntToStr(ans));

  I := Nil;
  GClient.Free;
  GModel.Free;
end;

if I step up to the line that reads "ans := I.Add(3,4);" if theres more than about a 5 seconds delay I get an exception "ECrtSocket with message 'SndLow 10053 WSAECONNABORTED [An established connection was aborted by the software in your host machine]"

As you can see from the example above I have a large value in the timeouts, but I suspect its the server side dropping the connection based on the exception. I can put the line in a loop of 10000 and it runs fine outside of the debugger.

Is there some type of lifetime for the connection on the server that I havent found yet ?

The relevent part of the server code is

  SqLite3 := TSQLite3LibraryDynamic.Create;
  GModel := TSQLModel.Create([], ROOT_NAME);
  GServer := TSQLRestServerDb.Create(GModel);
  GServer.ServiceDefine(TServiceCalculator, [ICalculator], sicShared);
  GHTTPServer := TSQLHttpServer.Create(PORT_NAME, [GServer], '+', useHttpApiRegisteringURI);

  GHTTPServer.AccessControlAllowOrigin := '*';

but I couldnt find anything obvious where a connection or idle timeout could be set.

Is there a limit and is it possible to set this limit ?