Hi Kanwar, We could probably tweak Cayenne Modeler's "Generate Database Schema" option for databases which support deferrable constraints. PostgreSQL (not sure about Oracle) also supports the following at the beginning of the transaction:
SET CONSTRAINTS ALL DEFERRED I think we should add that, too, just to catch other deferrable constraints that might be created outside Cayenne Modeler. mrg On Tue, Jun 5, 2012 at 1:53 PM, Kanwar Manish <[email protected]> wrote: > 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
