mORMot Open Source

squirrel

Member

Registered: 2015-08-13

Posts: 155

Connection pools and thread safety

Hi

I've decided to not go the orm route so I can reuse some of our legacy queries.  For now, I decided to have a simple scenario where a function call will execute the TSQLDBStatement and return the results as json using FetchAllToJSON. 

How does the connection pools work then?  Do I need to create a new connection in every function, or should I declare the TSQLDBConnectionProperties.NewConnection globally so that NewConnection.NewStatement can be used in every function?  This does not sound thread safe, is there an alternative?

Given the complexities of the existing legacy code, I'll also have to do custom authentication, which will mean that the login query will run before a session is created and the normal queries after a session is created.  Will this affect the connection pool, or will this work the same as the normal queries?  The TSQLHttpServer is created in the Main thread.

Sorry if I don't express my questions well.  If anything is unclear I'll try to restate.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,664

Website

Re: Connection pools and thread safety

Hello bro (marmots and squirels are both rodents, right?)!

In fact thread safety on external databases would depend on the SynDB unit and the associated driver used.
Most of the time, there is a connection per thread, and a thread pool within the HTTP server.
See http://synopse.info/files/html/Synopse% … ml#TITL_25 for additional information.
Do not create a connection by hand: just use the NewThreadSafeStatementPrepared method.

Custom authentication could be easily implemented using a sicClientDriven kind of service.
A first method would authenticate, and allow execution of the other methods.
During all the process, one instance would stay alive on the server side, with session private parameters and info.
See http://synopse.info/files/html/Synopse% … ml#TITL_92

squirrel

Member

Registered: 2015-08-13

Posts: 155

Re: Connection pools and thread safety

Yes, squirrels and marmots often share food

To pass on the connection, I followed something done by a user in one of the forum posts and created this little test:

  aConnection := TODBCConnectionProperties.Create('',ConnectionStr,'','');
  aRestServer := TSQLRestServerFullMemory.Create(aModel, false);
  aRestServer.AuthenticationRegister([TMyAuthentication]);
  aRestServer.ServiceDefine(TSales.Create(aConnection),[ISales]);
  aRestServer.ServiceDefine(TStaff,[IStaff], sicShared);

My auth for this test is also currently as simple as possible:

  TMyAuthentication= class(TSQLRestServerAuthenticationDefault)
  protected
    function CheckPassword(Ctxt: TSQLRestServerURIContext; User: TSQLAuthUser; const aClientNonce, aPassWord: RawUTF8): boolean; override;
  end;

  function TMyAuthentication.CheckPassword(Ctxt: TSQLRestServerURIContext; User: TSQLAuthUser; const aClientNonce, aPassWord: RawUTF8): boolean;
  begin
    result := aPassword = 'abc';
  end;

I then defined a WhoAmI function in Both TSales and TStaff.  When calling the Sales.WhoAmI function, it always returns 403 Forbidden, but Staff.WhoAmI always works as expected, depending on whether the authentication passed or failed. The WhoAmI function is also basic and doesn't perform any logic:

function TStaff.WhoAmI: string;
begin
  result := 'I am Somebody';
end;

Not sure if this is relevant, but I'm testing using Ajax, but will use the exact same function calls from a Delphi client.

Any advice would be super helpful.  I'm still finding my feet, but the more I see, the more I like it.

Last edited by squirrel (2015-09-23 10:26:21)

squirrel

Member

Registered: 2015-08-13

Posts: 155

Re: Connection pools and thread safety

Is there a different way of passing the connection on to the TInterfacedObjects?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,664

Website

Re: Connection pools and thread safety

You can inherit from TSQLRestServerFullMemory and set your own properties.
Then you set the the TSQLOracleConnectionProperties instance.

Or you add a "SQL" service, which returns a connection.

squirrel

Member

Registered: 2015-08-13

Posts: 155

Re: Connection pools and thread safety

Thanks.  As per my test code, I already use TSQLRestServerFullMemory.  How does that bring the connection to the interfaces?  Is there a property that I need to access in the interface's functions?

I tried creating a class from which all my instances can inherit, but it doesn't look as if the connection is available when used in the TStaff class.  Would this be the correct way to access a specific created instance?  The Get call currently generates an access violation.

TMyInterface = class(TInterfacedObject)
private
public
  DBConnection: TSQLDBConnectionProperties;
end;

TStaff = class(TMyInterface, IStaff)
private
public
  function GetNickname(const aUserID: string):string;
  function WhoAmI: string;
end;

function TStaff.GetNickname(const aUserID: string): string;
var
  Res : ISQLDBRows;
begin
  Result := '';
  Res := DbConnection.Execute('select nickname from tblstaff where UserID=? ', [aUserID] );
  if Res.Step then
    Result := Res['nickname'];
end;

//This looks like it creates a copy of the interface and does not return the instance in the server,
//so setting properties here leave them nil in the server instance
if aRestServer.Services['Staff'].Get(obj) then
begin
  TMyInterface(obj).DBConnection := aConnection;
  showmessage(TStaff(obj).GetNickname('0')); //this works, but not when calling from ajax, since dbconnection then is nil
end;

//this does not bring the server instance back either, just give an AV
for I := 0 to aRestServer.Services.Count -1 do
  if aRestServer.Services.Index(I).Get(obj) then  //Access Violation here
    TMyInterface(obj).DBConnection := aConnection

//this works, but requires me to do for every service, no for loop
TMyInterface(aRestServer.Service<IStaff>).DBConnection := aConnection;

Now a more basic question remains: how to end a session from ajax?  I currently do the auth? call to authenticate, but what do I call to log out and ensure that a session can't be re-used? This always returns 400 Bad Request but does seem to kill the session (all calls after that returns 403 Forbidden):

http://localhost:8080/root/auth?session=184464650&UserName=admin&session_signature=0afeb50a0003f9f9d88a6677

Last edited by squirrel (2015-09-25 10:14:15)