- changed milestone to 1.2
Foreign Key Constraint Violation when attempting to use SaveAll to save an Entity and all of it's children
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.
Comments (10)
-
repo owner -
@Toddflora Can you test if this issue still exists with the latest commit?
-
reporter Sure thing I will check it out tomorrow morning. Thanks so much for such a quick response.
-
reporter 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.
--
-
reporter 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.
-
reporter -
- changed status to resolved
-
repo owner - changed status to closed
-
repo owner - changed milestone to 1.2 (next version)
-
repo owner - changed milestone to 1.2
- Log in to comment