XData Server on two different databases - Standard CRUD Endpoints, service doing joins

[Fabrizio_Brenchio]

I'm a little lost in building up an XData Server capable of handling a scenario like this:

-- DATABASE db-a
create table Persons
(
    id           int auto_increment comment 'PK' primary key,
    Name         varchar(31) null comment 'Name[31]',
    fk_Cities_id int         null comment 'Foreign Key to Cities Table',
    
    constraint Persons_Cities_id_fk
        foreign key (fk_Cities_id) references `db-b`.Cities (id)
            on update cascade on delete set null
);

-- DATABASE db-b
create table Cities
(
    id       int auto_increment comment 'PK'
        primary key,
    CityName varchar(31) default 'ACityName' not null
);

it's immediate to connect to db-b, autogenerate entity for table Cities and have CRUD endpoints for it, test it with swagger... a real kind of magic!

it's not possible to auto-generate entity for db-a ==> getting error "EGException: Relationship Persons_Cities_id_fk has no parent table."

Apart from entity generations, what is the correct way to connect to the two databases and how to write a service that does a simple join like this?

select
    P.*,
    C.CityName
from Persons P
left join `db-b`.`Cities` C on P.fk_Cities_id = C.id;

All above of course derives from a bigger scenario with many tables, scattered into several databases and a lot of SQL queries already working... before doing a big re-design I would like to understand if a "direct port" to XData Aurelius is possible.
Thanks in advance!

[Mika_Koistinen]

Aurelius doens't support directly multiple DB's on one query. Objectmanager knows only one connections. I see there few ways, depending on needs.

1. Use views on master DB. View can be declared also as entity.
2. Fetch data to memory on join there. We use this way collect reports from several DB.
3. Do some sql manipulation. At event OnSqlExecuting you can now modify SQL. (I use it to optimize some of our DB processing .. )
4. Not sure if Linq.sql can do the trick, might

[wlandgraf (Wagner Landgraf)]

There are even more suggestions besides all the valid ones @Mika_Koistinen provided:

5. Map the Cities table with the db-b prefix. It's indeed for schemas, but could work because all Aurelius does is to add a prefix in SQL: [Table('Cities', 'db-b')].
6. The "solve it all" solution: use Service Operations instead of Aurelius Crud Entities. Those are the two "operation modes" in XData. The latter is the "super RAD" one. And former is the "normal" and "100% flexible" one. If you are getting stuck in the RAD option, just to to the one you can do whatever you want. Create a service endpoint, you don't even need to use Aurelius if you don't want to, just execute a query in TFDQuery and process the request there as you wish.

[Fabrizio_Brenchio]

Thank you very much both @Mika_Koistinen and @wlandgraf for your suggestions - I will try them all, hoping to work it out.
Wagner, about suggestion 5... I guess I need anyway two separate connections, one to each database and two separate connection pools to optimize the traffic, isn't it.
I've also seen there's a MultiDBconnectionPool component, but I've not found real demos for it.

[wlandgraf (Wagner Landgraf)]

@Fabrizio_Brenchio

Wagner, about suggestion 5... I guess I need anyway two separate connections, one to each database and two separate connection pools to optimize the traffic, isn't it.

If you need separate connections, yes. I was assuming that you would access both databases from a single connection.

@Fabrizio_Brenchio

I've also seen there's a MultiDBconnectionPool component, but I've not found real demos for it.

This is for dynamic pools, in your case you will only two static ones, so I don't think it's the case. But if you want to check, it's used in the demo "Multitenancy".

[Fabrizio_Brenchio]

I tried suggestion 1. with views... simple & straight to the point, perfect for read-only stuff (it's a view); it's only a pity that it's not possible to auto-generate entities from views... since Aurelius is so "super RAD" capable, there might be a reason for that.

Agree about not being the case for dynamic pools - my scenario is way much simpler.

Then I tried suggestion 5 using the schema trick... but so far I'm not able to have it work.

I'm attaching a demo project with DBs and source from D12.1 (fresh update!).
I used the wizard to create the XData server.

Using both Service operations or Aurelius Crud Entities the tables are always searched from db-b - even if I'm using [Table('Persons', 'db-a')] in code - and of course db-b.Persons is not found.

Where I am wrong?
DB_AB_Test.zip (77.2 KB)

[wlandgraf (Wagner Landgraf)]

@Fabrizio_Brenchio

Then I tried suggestion 5 using the schema trick... but so far I'm not able to have it work.

Indeed, MySQL dialect doesn't support schemas (which is the original intention of the second parameter in Table attribute).

You can workaround it this way, create your own MySQL dialect:

uses {...}, Aurelius.Sql.Interfaces, Aurelius.Sql.Register;

  TMySQLCustomSQLGenerator = class(TMySQLSQLGenerator)
  protected
    function GetSupportedFeatures: TDBFeatures; override;
  end;

function TMySQLCustomSQLGenerator.GetSupportedFeatures: TDBFeatures;
begin
  Result := inherited GetSupportedFeatures;
  Result := Result + [TDBFeature.Schemas];
end;

And then replace the existing one with yours:

initialization
  TSQLGeneratorRegister.GetInstance.RegisterGenerator(TMySQLCustomSQLGenerator.Create);

[Fabrizio_Brenchio]

@wlandgraf I added your code in the data module (the one generated by XData Server wizard) and it works great.

I only had to modify db names from db-a and db-b to dba and dbb.
I guess that unless backticks are added, MySQL / MariaDB is not correctly understanding the SQL statement... anyway it's better to choose standard/better db names since the beginning!

In order to understand your solution, I've seen that the current code in Aurelius.Sql.MySQL.pas is

function TMySQLSQLGenerator.GetSupportedFeatures: TDBFeatures;
begin
  Result := AllDBFeatures - [TDBFeature.Sequences, TDBFeature.RetrieveIdOnInsert, TDBFeature.Schemas];
end;

so, the overriding restores back the Schema feature... was that just a design choice not to support MySQL schemas because in MySQL databases and schemas are basically the same thing (no distinction)? Other DBMSs refer schemas as collection of tables; and databases as collection of schemas... correct?

[wlandgraf (Wagner Landgraf)]

@Fabrizio_Brenchio

was that just a design choice not to support MySQL schemas because in MySQL databases and schemas are basically the same thing (no distinction)?

There is no concept of schemas in MySQL, that's why it's not present. It's not exactly the same as a different database. It affects other command like CREATE TABLE, as well the retrieval of existing tables (and schemes) in the database.