In a multitenant project, XData seems to execute SQL on all databases

BIZ TMS XData
1

I discover by accident while debugging that the client request is executed by all databases in a multitenant configuration.

I've adapted my situation to the demo project MultiTenantByURL, where I added this code:

TMappingExplorer.Get(TMappingExplorer.DefaultModelName).Events.OnSqlExecuting.Subscribe(DbSqlLogProc);

procedure TServerByUrlModule.DbSqlLogProc(Args: TSQLExecutingArgs);
begin
  var LDbName: string;
  LDbName := AureliusConnection1.Params.Values['Database'];
  Form1.Memo1.Lines.Add(
    '{' + LDbName + '}' + Args.SQL);
  end;
end;

The client request :

http://localhost:2001/tms/multitenant/Blabla/Artist

The result is as expected. But here is the server log

{Database1.db}SELECT A.ID AS A_ID, A.NAME AS A_NAME
FROM ARTIST A
{Database2.db}SELECT A.ID AS A_ID, A.NAME AS A_NAME
FROM ARTIST A
{Blabla.db}SELECT A.ID AS A_ID, A.NAME AS A_NAME
FROM ARTIST A

So my question is: does the XData server send each request to all the databases, and why? And if so, how can I limit/filter this? In rea project, we're talking about several hundred DBs.

Thank you

2

No.

Is it possible that you share such test project so we can reproduce and debug the behavior at our side?

3

MultiTenantByUrl_Test.zip (13.9 KB)
Capture d'écran 2025-03-15 162900

Capture d'écran 2025-03-15 162943
Capture d'écran 2025-03-15 162943642×704 24 KB

4

The way you configure the OnSqlExecuting event is causing this. You are registering a new listener for each database, thus each request will fire multiple ones.

You should just set the event once, in the main form OnCreate event, for example, this way:

procedure TForm1.FormCreate(Sender: TObject);
begin
  FServers := TObjectDictionary<string, TServerByUrlModule>.Create([doOwnsValues]);
  TMappingExplorer.Get(TMappingExplorer.DefaultModelName).Events.OnSqlExecuting.Subscribe(DbSqlLogProc);
end;

The event could be something like this:

procedure TForm1.DbSqlLogProc(Args: TSQLExecutingArgs);
begin
  var Conn := TObjectManager(Args.Manager).Connection;
  if Conn is TDBConnectionWrapper then
    Conn := (Conn as TDBConnectionWrapper).Intf;
  var SqliteConn := Conn as TSQLiteNativeConnectionAdapter;
  var LDbName := TRttiContext.Create.GetType(TSQLiteNativeConnectionAdapter)
    .GetField('FFileName').GetValue(SqliteCOnn).AsString;
  Form1.Memo1.Lines.Add(
    '{' + LDbName + '}' + Args.SQL);
  if Args.Params <> nil then
  begin
    for var MyParam in Args.Params do
      Form1.Memo1.Lines.Add('>>' + MyParam.ParamName
          + '[' + GetEnumName(TypeInfo(TFieldType), Ord(MyParam.ParamType)) + ']='
          + VarToStr(MyParam.ParamValue)
          );
  end;
end;

Note that it's using RTTI to find the file name because TSQLiteNativeConnectionAdapter doesn't make it public. We changed it here so in next version you can simply use:

 var LDbName := SqliteConn.FileName;
1 Like
5

Thank you @wlandgraf for these explanations.

So, if I have Service1 and Service2 (for each database) with the following methods:

function TService1.Function11: string;
begin
   var LManager := TXDataOperationContext.Current.GetManager;
   Result := LManager.Find...
end;

function TService2.Function21.string;
begin
   var LManager := TXDataOperationContext.Current.GetManager;
   Result := LManager.Find...
end;

function TService2.Function22.string;
begin
   var LManager := TXDataOperationContext.Current.GetManager;
   Result := LManager.Find...
end;

Am I sure I can construct TService.Function12 as follows, and make the requests to the same database, and only to this database? :

function TService1.Function12: string;
begin
...
  Result := Function11 +  XDataClient.Service<IService2>.Function21;

Thanks in advance for your help.

6

What do you mean by that? You don't separate Service1 and Service2 per database. In a multi tenant application, both services could be used to access any database.

The way to choose which database is being accessed depends on your application, in the example, each server will have a different URL for each database.

If you access the URL of database1, the operations will be performed in database1. If you access the URL of database2, the operations will be performed in database2.

If I understand your question, yes. All database operations in a request will be targeting a single database - if you get the connection from the XData context, of course.

7

Yes, that's what I meant. All the services could be used to access any database, and execute request to target database according to URL.

Thanks for the clarification and for your well-done code :slight_smile:

1 Like
8

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.