Foreign Key Constraint Violation when attempting to use SaveAll to save an Entity and all of it's children

[Todd Flora]

Looking at the SaveAll method it looks like there may be a couple of problems with a database that has Referential integrity

1) It seems to save entities in the wrong order, going to the lowest entity first and then saving back up to the parent. This won't work since the Parent must be inserted first in order for referential integrity constraints not to be violated.

2) Foreign Key Value for Children is not set from Parents Primary Key. Using a Sequence to generate the Primary key in the parent, this value should cascade down to any OneToMany associations and fill in the association field of the children.

Maybe I am holding it incorrectly, if so then please let me know what I am doing wrong.

Here is my Parent Class and my Child Class for Reference so you can see how I have setup the Entity Model.

Parent Class:

unit Prism.Model.PubSubscriber;

interface

uses
  Spring.Persistence.Mapping.Attributes
  ,Spring.Persistence.Core.Types
  ,Spring.Collections
  ,Spring
  ,Graphics
  ,Classes
  ,Prism.Model.PubSubscription
  ,Sysutils
  ;

type
  [Entity]
  [Table('PUB_SUBSCRIBER', '')]
  [Sequence('Select GetSid as Sid from dual')]
  TPubSubscriber = class
  private
    [Column('SID',[cpRequired,cpPrimaryKey,cpDontInsert,cpDontUpdate,cpNotNull ],19,0)]
    FSid: Int64;
    FCreatedBy: string;
    FCreatedDatetime: TDateTime;
    FModifiedBy: string;
    FModifiedDatetime: TDateTime;
    FControllerSid: Int64;
    FOriginApplication: string;
    FPostDate: TDateTime;
    FRowVersion: Int64;
    FTenantSid: Int64;
    FSubscriberId: string;
    FSubscriberName: string;
    FAddress: string;
    FUserName: string;
    FPassword: string;
    FInstallationId: Int64;
    FInstallationName: string;
    FTopdownnotifications: Int64;
    FSourceAddress: string;
    [OneToMany(False, [ckCascadeAll])]
    FSubscriptions: Lazy<IList<TPubSubscription>>;
    function GetSubscriptions: IList<TPubSubscription>;
    function GetSid: String;
    procedure SetSid(const Value: String);
  public
    constructor Create;
    property Sid: String read GetSid write SetSid;
    [Column('CREATED_BY',[cpRequired,cpNotNull],20)]
    property CreatedBy: string read FCreatedBy write FCreatedBy;
    [Column('CREATED_DATETIME',[cpRequired,cpNotNull])]
    property CreatedDatetime: TDateTime read FCreatedDatetime write FCreatedDatetime;
    [Column('MODIFIED_BY',[],20)]
    property ModifiedBy: string read FModifiedBy write FModifiedBy;
    [Column('MODIFIED_DATETIME',[])]
    property ModifiedDatetime: TDateTime read FModifiedDatetime write FModifiedDatetime;
    [Column('CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    property ControllerSid: Int64 read FControllerSid write FControllerSid;
    [Column('ORIGIN_APPLICATION',[cpRequired,cpNotNull],20)]
    property OriginApplication: string read FOriginApplication write FOriginApplication;
    [Column('POST_DATE',[])]
    property PostDate: TDateTime read FPostDate write FPostDate;
    [Version('ROW_VERSION',[cpRequired,cpNotNull],10,0,'',1)]
    property RowVersion: Int64 read FRowVersion write FRowVersion;
    [Column('TENANT_SID',[],19,0)]
    property TenantSid: Int64 read FTenantSid write FTenantSid;
    [Column('SUBSCRIBER_ID',[cpRequired,cpNotNull],38)]
    property SubscriberId: string read FSubscriberId write FSubscriberId;
    [Column('SUBSCRIBER_NAME',[cpRequired,cpNotNull],100)]
    property SubscriberName: string read FSubscriberName write FSubscriberName;
    [Column('ADDRESS',[cpRequired,cpNotNull],100)]
    property Address: string read FAddress write FAddress;
    [Column('USER_NAME',[],30)]
    property UserName: string read FUserName write FUserName;
    [Column('PASSWORD',[],30)]
    property Password: string read FPassword write FPassword;
    [Column('INSTALLATION_ID',[],5,0)]
    property InstallationId: Int64 read FInstallationId write FInstallationId;
    [Column('INSTALLATION_NAME',[],15)]
    property InstallationName: string read FInstallationName write FInstallationName;
    [Column('TOPDOWNNOTIFICATIONS',[cpRequired,cpNotNull],1,0)]
    property Topdownnotifications: Int64 read FTopdownnotifications write FTopdownnotifications;
    [Column('SOURCE_ADDRESS',[],100)]
    property SourceAddress: string read FSourceAddress write FSourceAddress;

    property Subscriptions: IList<TPubSubscription> read GetSubscriptions;
  end;

implementation


{ TPubSubscriber }

constructor TPubSubscriber.Create;
begin
  FSubscriptions:= TCollections.CreateObjectList<TPubSubscription>(True);
end;

function TPubSubscriber.GetSid: String;
begin
  Result := FSid.ToString;
end;

function TPubSubscriber.GetSubscriptions: IList<TPubSubscription>;
begin
  Result := FSubscriptions.Value;
end;

procedure TPubSubscriber.SetSid(const Value: String);
begin
  FSid := StrToInt64(Value);
end;

end.

Child Class

unit Prism.Model.PubSubscription;

interface

uses
  Spring.Persistence.Mapping.Attributes
  ,Spring.Persistence.Core.Types
  ,Spring.Collections
  ,Spring
  ,Graphics
  ,Classes
  ,Prism.Model.PubSubscriptionEvent
  ,System.SysUtils
  ,System.StrUtils
  ;

type
  [Entity]
  [Table('PUB_SUBSCRIPTION', '')]
  [Sequence('Select GetSid as Sid from dual')]
  TPubSubscription = class
  private
    [Column('SID',[cpRequired,cpPrimaryKey,cpDontUpdate,cpDontInsert,cpNotNull],19,0)]
    FSid: Int64;
    FCreatedBy: string;
    FCreatedDatetime: TDateTime;
    FModifiedBy: string;
    FModifiedDatetime: TDateTime;
    FControllerSid: Int64;
    FOriginApplication: string;
    FPostDate: TDateTime;
    FRowVersion: Int64;
    FTenantSid: Int64;
    FSubscriberSid: Int64;
    FHeartbeatService: string;
    FDataeventService: string;
    FActive: Int64;
    FSubscriptionName: string;

    [OneToMany(False, [ckCascadeAll])]
    FSubscriptionEvents : Lazy<IList<TPubSubscriptionEvent>>;
    function GetPubSubscriptionEvents: IList<TPubSubscriptionEvent>;
    function GetSid: String;
    procedure SetSid(const Value: String);
  public
    constructor Create;

    property Sid: String read GetSid write SetSid;
    [Column('CREATED_BY',[cpRequired,cpNotNull],20)]
    property CreatedBy: string read FCreatedBy write FCreatedBy;
    [Column('CREATED_DATETIME',[cpRequired,cpNotNull])]
    property CreatedDatetime: TDateTime read FCreatedDatetime write FCreatedDatetime;
    [Column('MODIFIED_BY',[],20)]
    property ModifiedBy: string read FModifiedBy write FModifiedBy;
    [Column('MODIFIED_DATETIME',[])]
    property ModifiedDatetime: TDateTime read FModifiedDatetime write FModifiedDatetime;
    [Column('CONTROLLER_SID',[cpRequired,cpNotNull],19,0)]
    property ControllerSid: Int64 read FControllerSid write FControllerSid;
    [Column('ORIGIN_APPLICATION',[cpRequired,cpNotNull],20)]
    property OriginApplication: string read FOriginApplication write FOriginApplication;
    [Column('POST_DATE',[])]
    property PostDate: TDateTime read FPostDate write FPostDate;
    [Version('ROW_VERSION',[cpRequired,cpNotNull],10,0,1)]
    property RowVersion: Int64 read FRowVersion write FRowVersion;
    [Column('TENANT_SID',[],19,0)]
    property TenantSid: Int64 read FTenantSid write FTenantSid;
    [ForeignJoinColumn('SUBSCRIBER_SID', 'PUB_SUBSCRIBER', 'SID', [fsOnDeleteCascade, fsOnUpdateCascade])]
    [Column('SUBSCRIBER_SID',[cpRequired,cpNotNull],19,0)]
    property SubscriberSid: Int64 read FSubscriberSid write FSubscriberSid;
    [Column('HEARTBEAT_SERVICE',[],100)]
    property HeartbeatService: string read FHeartbeatService write FHeartbeatService;
    [Column('DATAEVENT_SERVICE',[],100)]
    property DataeventService: string read FDataeventService write FDataeventService;
    [Column('ACTIVE',[cpRequired,cpNotNull],1,0)]
    property Active: Int64 read FActive write FActive;
    [Column('SUBSCRIPTION_NAME',[cpRequired,cpNotNull],40)]
    property SubscriptionName: string read FSubscriptionName write FSubscriptionName;

    property SubscriptionEvents : IList<TPubSubscriptionEvent> read GetPubSubscriptionEvents;
  end;

implementation

{ TPubSubscription }

constructor TPubSubscription.Create;
begin
  FSubscriptionEvents := TCollections.CreateObjectList<TPubSubscriptionEvent>;
end;

function TPubSubscription.GetPubSubscriptionEvents: IList<TPubSubscriptionEvent>;
begin
  Result := FSubscriptionEvents.Value;
end;


function TPubSubscription.GetSid: String;
begin
  Result := FSid.ToString;
end;

procedure TPubSubscription.SetSid(const Value: String);
begin
  FSid := StrToInt64(Value);
end;

end.

[Linas Naginionis]

@Toddflora Can you test if this issue still exists with the latest commit?

[Todd Flora]

Sure thing I will check it out tomorrow morning. Thanks so much for such a quick response.

[Todd Flora]

Sure thing I will check it out first thing tomorrow. Thanks so much for your quick response on this. We really like Marshmallow and hope to use it in our upcoming project.

Thanks,

Todd Flora.

--

[Todd Flora]

Linas,

I used your code out of the box and it did not work with Firedac because of Issue #70. I know you resolved this by using the OpenOrExecute method of the Firedac Query, but this still it not working. I have put notes on the above issue for this.

Once I changed the code in the Spring.Persistence.Adapters.FireDAC.ExecuteQuery method back to the following it started working:

    if StartsText('SELECT', Statement.SQL.Text) then
      query.Open
    else
      query.Execute;

3) One more thing on this. When I got the exception I noticed that the Parent record was inserted but the child record failed. When the firedac exception occurred the parent record therefore did not get rolledback. This is also not good IMO.

[Todd Flora]

Linas,

I have commented on #70 to your email. So maybe I was mixing exceptions but I did see #3 at one point. Where the record for one of the children failed but the parent was inserted successfully and not rolled back.

Otherwise this is working now. Thanks.