Model madness: cache table where IDs can be duplicated on purpose

[Raimondi_Andrea]

Hello!

I am stuck in a hole I didn't dig myself in: we have a flat import table($B) that eventually maps onto different tables and it can have duplicated IDs so no PK can be set. The issue is that this table gets used in an outer join with a crucial table ($A) of the system and thus I need some way to make Aurelius do so automatically.
I'd rather not load $B independently and recombine with $A which so far I am assuming is the only solution.

Because it's in an outer join, I am assuming I can define "fake FKs" and Aurelius should pick them up but I am not sure.

Can anyone please confirm that if I create "fake FKs" Aurelius will merrily make an outer join? (Yes, the matching property is going to be a Nullable<TList<$B>>).

Cheers!

[wlandgraf (Wagner Landgraf)]

Hi Andrea, I got confused with your description? Maybe it's easier if we go directly to what the tables are, the entity classes? I mean, describe the exact table, fields (remove unnecessary), ids, etc.?

Fact is Aurelius simply doesn't support tables with no unique keys. Something needs to uniquely identity a record in the table.

[Raimondi_Andrea]

Fact is Aurelius simply doesn't support tables with no unique keys. Something needs to uniquely identity a record in the table.

I managed to make the ID of the table a proper foreign key to the right table, but I won't know if this works until I have sorted out other problems. I wish the TMS Data Modeler were more flexible because this DB is a bit of a nightmare!

[wlandgraf (Wagner Landgraf)]

It's not clear to me, have you solved the issue?

[Raimondi_Andrea]

No, I haven't. I am now receiving the following message:
Project BFiveServiceVCL.exe raised exception class EMultipleColumnsFoundForAttribute with message 'Mixed Column, JoinColumn and/or ForeignJoinColumn used in member "FID_PETCACHEPERSONALE" of class "TSSTPERSONALE_VISITE".'.

Okay, PETCACHEPERSONALE is a table that does not have a primary key because it can't really have one as per how it is currently used. What I have done, then, to try to make it work is add a foreign key to the ID field pointing to the same field in PETPERSONALE(I mean that the same value is used for the same field in both tables).
Then I have added a reference to PETCACHEPERSONALE in TSSTPERSONALE_VISITE like so:

[Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [ForeignJoinColumn('ID_PETPERSONALE',[TColumnProp.Required],'ID')]
    FID_PETCACHEPERSONALE: TPETCACHEPERSONALE;

Note that the ID is the same for both tables so I am using the same ID field to do the join (but this might the problem itself).
If I press Continue I get the following message:

Project BFiveServiceVCL.exe raised exception class EEntityTypeMappingFailed with message 'Error mapping entity type BFive.Model.WorkingModel.TSSTPERSONALE_VISITE: Mixed Column, JoinColumn and/or ForeignJoinColumn used in member "FID_PETCACHEPERSONALE" of class "TSSTPERSONALE_VISITE". (EMultipleColumnsFoundForAttribute)'.

As though Aurelius would be expecting a list but it should always really be a 1-to-1.
What do I do?

Cheers!

[Raimondi_Andrea]

Okay, by changing the ForeignKeyJoin to a common Join and fixing the ID attribute most problems go away.
There is just one problem left: I get an assert.
The assert is this:
Project BFiveServiceVCL.exe raised exception class EAssertionFailed with message 'Field/property is not a Proxy type (D:__deployer\builds\aurelius_temp\sources\d11\Win32\source\core\Aurelius.Mapping.Explorer.pas, line 3332)'.

I have no idea whether it is complaining that I am using a proxy type when I should not or the opposite: I am not using one when I actually should.

If it's complaining that I am not using one when I actually should, then that's probably still the PETCACHEPERSONALE because there is no proxy given that I am using it straight up in the service call to assign a bunch of things. If instead it's complaining about a proxy type that shouldn't be there are a few in there and there is no indication of which it is that Aurelius has a problem with.

Cheers!

[wlandgraf (Wagner Landgraf)]

That message happens when you declare an association as lazy (flag TAssociationProp.Lazy) but your field type is not a Proxy. This is the case:

[Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll  -[TCascadeType.Remove])]
FID_PETCACHEPERSONALE: TPETCACHEPERSONALE;

Either map the association as lazy-loading (recommended):

[Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll  -[TCascadeType.Remove])]
FID_PETCACHEPERSONALE: Proxy<TPETCACHEPERSONALE>;

Or remove the lazy flag:

[Association([TAssociationProp.Required], CascadeTypeAll  -[TCascadeType.Remove])]
FID_PETCACHEPERSONALE: TPETCACHEPERSONALE;

[Raimondi_Andrea]

Cool!

We're finally getting there :P
Another hurdle: SQLServer complains about an invalid column name (that is perfectly valid!).

[Entity]
  [Table('SSTPERSONALE_VISITE')]
  [Id('FID', TIdGenerator.IdentityOrSequence)]
  TSSTPERSONALE_VISITE = class( TBFiveModelDataLogBase )
  private
    [Column('ID', [TColumnProp.Required, TColumnProp.NoInsert, TColumnProp.NoUpdate])]
    FID: Integer;

    [Column('ID_CMTSTATI', [TColumnProp.Required])]
    FID_CMTSTATI: Integer;

    [Column('ID_SSTVISITE', [])]
    FID_SSTVISITE: Nullable<Integer>;

    [Column('DATA_PIANIFICATA', [TColumnProp.Required])]
    FDATA_PIANIFICATA: TDateTime;

The base class contains the common columns:

[AbstractEntity]
  TBFiveModelDataLogBase = class( TBFiveRoot, IORMDataLog )
  strict private
    [Column('USER_INS', [], 50)]
    FUSER_INS: Nullable<string>;

    [Column('DATA_INS', [])]
    FDATA_INS: Nullable<TDateTime>;

    [Column('USER_UPD', [], 50)]
    FUSER_UPD: Nullable<string>;

    [Column('DATA_UPD', [])]
    FDATA_UPD: Nullable<TDateTime>;

These are all present in the table and conforming to the attributes.

The exact message is:
Project BFiveServiceVCL.exe raised exception class EAureliusOdbcException with message 'Error -1: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name 'USER_INS'.'.

The table obviously has the columns:

What am I missing?

Cheers!

[Raimondi_Andrea]

Sorted. There were mismatches on the mapping.