ORM: Self Reference fails to work properly

[Todd Flora]

Environment

• Delphi: Seattle
• Database: Oracle 11g, 12c
• OS: MS Windows 10

Description

Persistence layer fails to load the children properly when a child reference points to itself as the parent class. The parent is loaded and then the same parent is again loaded into child list and this continues until a stack overflow occurs.

Table Definition

create table PRISM_RESOURCE
(
  sid                NUMBER(19) not null, 
  resource_name      NVARCHAR2(50) not null,
  parent_sid         NUMBER(19), 
)

alter table PRISM_RESOURCE
  add constraint XPKREM_RESOURCE primary key (SID);

alter table PRISM_RESOURCE
  add constraint PRISM_RESOURCE_RESOURCE foreign key (PARENT_SID)
  references PRISM_RESOURCE (SID) on delete cascade;

Class Definition

  [Table('PRISM_RESOURCE')]
  [Entity]
  [Sequence('Select GetSid() as Sid from dual')]
  TPrismResource = class(TModelBase)
  private

    [Column('SID',[cpRequired,cpPrimaryKey,cpNotNull],19,0)]
    FSid: Int64;

    [Column('RESOURCE_NAME',[cpRequired,cpNotNull],50)]
    FResourceName: string;

    [ForeignJoinColumn('PARENT_SID', 'PRISM_RESOURCE', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('PARENT_SID',[],19,0)]
    FParentSid: Nullable<int64>;

    [OneToMany(True, [ckCascadeAll])]
    FChildResource: Lazy<IList<TPrismResource>>;
  protected
    procedure SetSid(const Value : Int64);
    procedure SetResourceName(const Value : string);
    procedure SetParentSid(const Value : Nullable<int64>);
    function GetChildResource: IList<TPrismResource>;
    procedure SetChildResource(const Value: IList<TPrismResource>);
  public
    constructor Create; override;
    property Sid: Int64 read FSid write SetSid;
    property ResourceName: string read FResourceName write SetResourceName;
    property ParentSid: Nullable<int64> read FParentSid write SetParentSid;
    property ChildResource: IList<TPrismResource> read GetChildResource write SetChildResource;
  end;

Possible Resolution

After some evaluation of the issue the following code was found to be at fault. Please note the commented out lines in this method below.

• Unit: Spring.Persistence.Core.AbstractSession
• Class: TAbstractSession
• Method:: TAbstractSession.DoGetLazy

function TAbstractSession.DoGetLazy(const id: TValue; const entity: TObject;
  const column: ColumnAttribute; entityClass: TClass): IDBResultSet;
var
  baseEntityClass,
  entityToLoadClass: TClass;
begin
  baseEntityClass := entity.ClassType;
  entityToLoadClass := entityClass;

  if not TEntityCache.IsValidEntity(entityToLoadClass) then
    entityToLoadClass := baseEntityClass;

//  if entityToLoadClass = baseEntityClass then
//    baseEntityClass := nil;

  Result := GetResultSetById(entityToLoadClass, id, baseEntityClass, column);
end;

When BaseEntityClass is set to nil the call to GetResultSetByID will use the primary key of the parent to get the children causing the same record to be gotten over and over again by the recursive call.

We are unsure of the reason why these two lines were added to the method when the coder first wrote it. (Possibly to resolve some other issue) if removing these two lines will have an adverse affect we have not seen it so far. We will continue to test our code without these lines to see if there are any repercussions.

[Todd Flora]

Directly related to this issue in the latest code base is the following:

function TCriteria<T>.Add(const criterion: ICriterion): ICriteria<T>;
begin
//  TFlo - Commented out: FindTable method will blow with wrong table if there is a self reference
//         to a table. This is because a nil EntityClass means use the primary table, where as a non
//         null entity class means look for the table from the bottom up in the list of tables that
//         make up a models query.
//  if criterion.EntityClass = nil then
//    criterion.EntityClass := fEntityClass;
  fCriterions.Add(criterion);
  Result := Self;
end;

This must be commented out in order for the FindTable method to pick the right table see below. Also the find table method actually has an issue in that if a reference to self is seen twice in a given Model file there is not way to know which one to choose of the referenced tables with the same name. One will always be chosen but it may or may not be the table referenced.

function TSelectCommand.FindTable(entityClass: TClass): TSQLTable;
var
  tableName: string;
  currentTable: TSQLTable;
begin
  if entityClass = nil then  <-- If the ENTITY CLASS is Null then pick the root table for the Sql Select, In all cases where entity class is not defined this will be correct
    Exit(fTable);

  tableName := TEntityCache.Get(entityClass).EntityTable.TableName;

  for currentTable in fTables do  <-- If a table is self referenced twice then this logic will just pick the first one in the list and may or may not be correct.
    if SameText(currentTable.NameWithoutSchema, tableName) then
      Exit(currentTable);
  Result := fTable;
end;

[Stefan Glienke]

Removing milestone: Future version (automated comment)

[Todd Flora]

BTW just to update you on this issue and the proposed fix. We have seen no adverse issues from commenting out the above lines in the TCriteria.Add and DoGetLazy methods. This has been in production for us for a year and a half now.

[Todd Flora]

Just another FYI on this one. The issue has just been moved to another spot in the latest release

function TAbstractSession.LoadOneToManyAssociation(const id: TValue;
  const entity: TObject; const column: ColumnAttribute;
  entityClass: TClass): IDBResultSet;
var
  baseEntityClass,
  entityToLoadClass: TClass;
begin
  baseEntityClass := entity.ClassType;
  entityToLoadClass := entityClass;

  if not TEntityCache.IsValidEntity(entityToLoadClass) then
    entityToLoadClass := baseEntityClass;

//  if entityToLoadClass = baseEntityClass then  <-- Take this out is hoses Self Joined entities.
//    baseEntityClass := nil;

  Result := GetResultSetById(entityToLoadClass, id, baseEntityClass, column);
end;

[Stefan Glienke]

As already announced further development regarding new features and fixing non trivial bugs or design issues of the ORM will be put on hold.