Re: Transaction Fails - when saving interdependent entities.

Tue, 05 Jun 2012 10:54:40 -0700 

Thanks a ton Michael.

1. Will try this tomorrow morning asap. Seems like the problem.
2. Making it default in the adapters is a good idea.

Question: is this doable through Modeller?

Thanks
KM

Sent from my iPhone
On 05-Jun-2012, at 9:29 PM, Michael Gentry <[email protected]> wrote: 


Hi there,

I'll admit I didn't read through all your code, but most likely the
issue is your constraints are not deferred.  PostgreSQL and Oracle can
do deferred constraints (MySQL, for example, cannot) and should
probably be used.  By default, constraints are immediate instead of
checked at the end of the transaction.  Cayenne currently does not
know about how your database constraints are structured (even though
when it generates the schema it can add some constraints).

My suggestion is to alter your constraints to be deferrable:

http://www.postgresql.org/docs/9.1/static/sql-altertable.html

We should probably also update the PostgreSQL and Oracle adapters to
do this by default.

mrg
On Tue, Jun 5, 2012 at 9:41 AM, Kanwar Manish <[email protected]> wrote: 
Hi All
In my DB I have *4 entities dependent on each other* [I am leaving out the 
rest of the DB for sake of simplicity].

1. In the code below if I *Un-comment three "dc.commitChanges();"
statements - it works fine*. However if I keep these commented - and just use the last commit statement - an exception is thrown. [I am assigning the 
random UUIDs in the object entity constructors -
"super.setUserMasterId(UUID.randomUUID());"]

*Exception Is*
"Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on 
table "userdomain" violates foreign key constraint
"userdomain_createdby_fkey"
Detail: Key (createdby)=(a6aa8797-9d0a-45ac-83ef-baa3a86cf837) is not 
present in table "usermaster"
It was my *understanding that once all the respective relations are set in the registered objects - I can save all the inter-dependent objects in one 
commit statement*.

"// ------------- Code Start---------------
 DataContext dc = blgetcontext.getDatacontext();
       //Create UserMaster
       UserMaster um = dc.newObject(UserMaster.class);
       um.setUserName("sysadm");
       um.setPassword("sysadm");
       um.setCreationDetails("<CreationDetails/>");
       um.setCanLogin(Boolean.FALSE);
       // 1. dc.commitChanges();

       //Create CreationLog for LogicalFolder
       CreationLog cl = dc.newObject(CreationLog.class);
       //Set CreationLog relation with User master
       cl.setCLUserMaster(um);
       // 2. dc.commitChanges();

       //Create UserDomain for SysAdmin
       UserDomain ud = dc.newObject(UserDomain.class);
       ud.setName("sysadmdom");
       ud.setActive(Boolean.TRUE);
       ud.setIsDefault(Boolean.FALSE);
       //Set Userdomain Relation with UserMaster
       ud.setUDUserMaster(um);
       // 3. dc.commitChanges();


       //Create LogicalFolder
       LogicalFolder lf = dc.newObject(LogicalFolder.class);
       lf.setMaxSize(10000);
       lf.setMaxFiles(100000);
       lf.setIsRoot(true);
       lf.setLFCreationLog(cl);
       lf.setInherits(false);
       lf.setParentFolder(null);
       lf.setInheritFolder(null);
       lf.setMarkedForFullText(false);
       lf.setLevelWeight(0);
       lf.setContainerId(UUID.randomUUID());
       lf.setLFUserDomain(ud);

       //Set UserMaster relation with UserDomain
       um.setUMUserDomain(ud);

       dc.commitChanges();
// ------------- Code End---------------
       "

*---Database Code---*
*Cayenne Database Code is below for the Four Tables [Leaving rest of the DB 
for simplicity]*

"
CREATE TABLE CreationLog (ActionDateTime timestamp with time zone NOT NULL, CreationLogId character(36) NOT NULL, UserMasterId character(36) NOT NULL, 
PRIMARY KEY (CreationLogId));

CREATE TABLE LogicalFolder (ContainerId character(36) NOT NULL,
CreationLogId character(36) NOT NULL, InheritFolder character(36) NULL, Inherits boolean NOT NULL, IsRoot boolean NOT NULL, LevelWeight integer NOT NULL, LogicalFolderId character(36) NOT NULL, MarkedForFullText boolean NOT NULL, MaxFiles integer NOT NULL, MaxSize integer NOT NULL, ParentFolder 
character(36) NULL, UserDomainId varchar(36) NOT NULL, PRIMARY KEY
(LogicalFolderId));
CREATE TABLE UserDomain (Active boolean NOT NULL, CreatedBy varchar (36) NOT 
NULL, CreatedOn date NOT NULL, IsDefault boolean NOT NULL, Name
varchar(100) NOT NULL, UserDomainId varchar(36) NOT NULL, PRIMARY KEY
(UserDomainId));

CREATE TABLE UserMaster (CanLogin boolean NOT NULL, CreationDateTime
timestamp with time zone NOT NULL, CreationDetails varchar(1000) NULL, LogicalHomeDirectoryFolder character(36) NULL, Password varchar(50) NOT NULL, UserDomainId varchar(36) NULL, UserMasterId character(36) NOT NULL, 
UserName varchar(50) NOT NULL, PRIMARY KEY (UserMasterId));

ALTER TABLE CreationLog ADD FOREIGN KEY (UserMasterId) REFERENCES
UserMaster (UserMasterId);

ALTER TABLE LogicalFolder ADD FOREIGN KEY (CreationLogId) REFERENCES
CreationLog (CreationLogId);

ALTER TABLE LogicalFolder ADD FOREIGN KEY (UserDomainId) REFERENCES
UserDomain (UserDomainId);
ALTER TABLE UserDomain ADD FOREIGN KEY (CreatedBy) REFERENCES UserMaster 
(UserMasterId);

ALTER TABLE UserMaster ADD FOREIGN KEY (LogicalHomeDirectoryFolder)
REFERENCES LogicalFolder (LogicalFolderId);
ALTER TABLE UserMaster ADD FOREIGN KEY (UserDomainId) REFERENCES UserDomain 
(UserDomainId);

"

Thanks in Advance Guys
KM

Reply via email to