Architecture Question for a Sphinx Server/XData Server set up

[Weetch_Russell (Russell Weetch)]

We have an XData/Webcore application that takes care of it's own logins at present. At present we have a server per client (there's only 2 at the moment), but want to convert it to a Multi-Tenant server with each tenant having their own database.

I want to use Sphinx to manage the logins and trying to think of the best way to do this:

1. A separate Sphinx server: This has advantages that if anyone has access to more than one system, they can select which one

   • do I make this multi-tenant and issue login paths https://mysphinx.com/client1?
   • not have it multi-tenant but issue a tenant id as the result of a successful login?

2. Keep the user tables within the client database and use the XData app to also manage the Sphinx elements - in which case it is all multi-tenanted.

Any thoughts? Or better approaches?

[wlandgraf (Wagner Landgraf)]

@Weetch_Russell

This has advantages that if anyone has access to more than one system, they can select which one

This is key information. If this is something you consider implementing in future, then it completely discards option 2 and a multi-tenant Sphinx server.

But if the clients will be completely separated, meaning a user "a" will access "client1", and if there exists a "client2" you will want that "a' makes a new registration, use a different password, etc, to access "client2", then the setup is different.

There is no right answer and no best scenario, you really have to decide if your setup should have different users for different clients, or if the users will be shared.

Once this is decided, then we can proceed to suggest some implementations, and indeed a few options can be provided for each scenario.

[Weetch_Russell (Russell Weetch)]

Thanks for the quick reply.

Thinking it through I don't think that anyone will need access to more than one system. The only occasion would be for support, but a different login for each system isn't a bad idea even then. So that would keep clients completely separated.

[wlandgraf (Wagner Landgraf)]

In this case you have both options: a completely separate Sphinx server for each of your clients, or a single Sphinx multi-tenant server that authenticates users from each client, but still having the users separated.

Both options are possible.

[Weetch_Russell (Russell Weetch)]

Is there a demo app that uses the TSparkleTenantMiddleware and TXDataMultiDBConnectionPool components or any documentation? Thanks.

[wlandgraf (Wagner Landgraf)]

Unfortunately there is no current demo, but use is relatively simple: instead of using a TXDataConnectionPool component, you will use a TXDataMultiDBConnectionPool. Then you create a handler for the event OnCreateDatabasePoolEvent:

  TCreateDatabasePoolEvent = procedure(Sender: TObject; const DatabaseId: string; 
    var Pool: IDBConnectionPool) of object;

Such event will pass the DatabaseId (just another name for the tenant id).
With the tenant id provided, you should set the Pool variable with the respective connection pool for that tenant.

Thus, if tenant is "A", create and return a IDBConnectionPool that connects to the database "A" and do the same for B, C, etc..

The tenant id is automatically set by the tenant middleware, so that part is transparent.

[Weetch_Russell (Russell Weetch)]

Just double checking here.

If I'm using FireDac (with Firebird 3) I only need to register the Generator once not for each pool I create?

[Weetch_Russell (Russell Weetch)]

One other question. How can I get a pool to retrieve an IDBConnection sort of

Result := MultiDBConnectionPool.GetPool(TenantId).GetConnection;

Thanks

[wlandgraf (Wagner Landgraf)]

@Weetch_Russell

If I'm using FireDac (with Firebird 3) I only need to register the Generator once not for each pool I create?

What do you mean by "register the Generator", exactly? But I would say in advance that the answer is probably "correct".

@Weetch_Russell

One other question. How can I get a pool to retrieve an IDBConnection sort of

Result := MultiDBConnectionPool.GetPool(TenantId).GetConnection;

Thanks

That's how you would do it.

[Weetch_Russell (Russell Weetch)]

Just to complete the info: Generator register:

Generator := (TSQLGeneratorRegister.GetInstance.GetGenerator('Firebird3') as TFirebird3SQLGenerator);
Generator.UseBoolean := False;
Generator.UseIdentity := False;

On the second point. I am using a TXDataMultiDBConnectionPool which doesn't have the GetPool Method as it doesn't implement ITenantDBPoolProvider interface either itself or it's ancestors. So I'm wondering if

Result := (MultiDBConnectionPool.GetPoolInterface as ITenantDBPoolProvider).GetPool(TenantId).GetConnection;

might be the way to do it?

[wlandgraf (Wagner Landgraf)]

Yes, all your points are correct.