ORM: Self Reference fails to work properly

Issue #174 open
Todd Flora
created an issue

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.

Comments (7)

  1. Todd Flora reporter

    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;
    
  2. Log in to comment