Description of problem: On the aplaws-ws3 branch, we added an AuditingObserver to ContentItem by adding these two lines in the "initialize" method. m_audit_trail = BasicAuditTrail.retrieveForACSObject(this); addObserver(new AuditingObserver(m_audit_trail)); Since every ContentItem will have a BasicAuditTrail, I wanted to find a way to force the load in a single query like the old aggressive load (e.g. when I load a ContentItem, it automatically loads the BasicAuditTrail as well). So, I thought that I could do this by adding an association between ContentItem and BasicAuditTrail so in ContentItem.pdl, in the "object type" definitely, I added this: BasicAuditTrail[0..1] auditTrail = join cms_items.item_id to acs_auditing.object_id; Once I compile with this once change in the pdl then various actions cease to work (such as publishing and deleting). For instance, when I delete, I get this: -*-*-*- Section: Stack trace -*-*-*- com.redhat.persistence.engine.rdbms.RDBMSEngine$4: ORA-01407: cannot update ("APLAWSWP3"."CMS_ITEMS"."ITEM_ID") to NULL at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:514) at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:430) at com.redhat.persistence.engine.rdbms.RDBMSEngine.flush(RDBMSEngine.java:395) at com.redhat.persistence.Session.flushInternal(Session.java(Compiled Code)) at com.redhat.persistence.Session.flush(Session.java:562) at com.arsdigita.persistence.DataObjectImpl.delete(DataObjectImpl.java:380) at com.arsdigita.domain.DomainObject.delete(DomainObject.java:324) at com.arsdigita.cms.ui.folder.FolderBrowser$ItemDeleter.cellSelected(FolderBrowser.java:463) at com.arsdigita.bebop.Table.fireCellSelected(Table.java:238) at com.arsdigita.bebop.Table.respond(Table.java:538) at com.arsdigita.cms.ui.folder.FolderBrowser.respond(FolderBrowser.java:204) at com.arsdigita.bebop.PageState.respond(PageState.java:367) The queries leading up to the delete are: -- ID: #280 -- Duration: 1ms update cms_items set type_id = null where cms_items.item_id = '1477'; -- ID: #281 -- Duration: 2ms update cms_items set section_id = null where cms_items.item_id = '1477'; -- ID: #282 -- Duration: 3ms update cms_items set item_id = null where cms_items.item_id = '1477'; My guess is that because it is a 0..1 association it is trying to null out the column. However, since the column is actually the primary key we are getting the error. I would think that persistence should be able to catch this situation and no try to execute the above query. If I change the association to 1..1 and then in the initialize() method I do: m_audit_trail = BasicAuditTrail.retrieveForACSObject(this); addObserver(new AuditingObserver(m_audit_trail)); setAssociation("auditTrail", m_audit_trail); Then when I create an item I get this: com.redhat.persistence.engine.rdbms.RDBMSEngine$4: ORA-01400: cannot insert NULL into ("APLAWSWP3"."CMS_ITEMS"."ITEM_ID") at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:514) at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:430) at com.redhat.persistence.engine.rdbms.RDBMSEngine.flush(RDBMSEngine.java:395) at com.redhat.persistence.Session.flushInternal(Session.java(Compiled Code)) at com.redhat.persistence.Session.flush(Session.java:562) at com.redhat.persistence.Cursor.next(Cursor.java:107) at com.arsdigita.persistence.DataQueryImpl.next(DataQueryImpl.java:465) at com.arsdigita.cms.ContentBundle.getInstance(ContentBundle.java:295) at com.arsdigita.cms.ContentBundle.getPrimaryInstance(ContentBundle.java:255) at com.arsdigita.cms.ContentBundle.beforeSave(ContentBundle.java:507) with the last several queries being: -- ID: #96 -- Duration: 3ms insert into acs_objects (object_type, object_id, display_name, default_domain_class) values ('com.arsdigita.cms.contenttypes.Address', '1507', 'ffff', 'com.arsdigita.cms.contenttypes.Address'); -- ID: #97 -- Duration: 2ms insert into vc_objects (object_id, is_deleted) values ('1507', 'false'); -- ID: #98 -- Duration: 2ms insert into cms_items (ancestors, version, name, language, item_id) values ('1507/', 'draft', 'ffff', 'en', null); Again, my guess is that it has something to do with the column being used for the join being the same as the reference key. Is this in fact a persistence bug? Or is there a better way that we can get the system to load the BasicAuditTrail and the ContentItem all in the same query? If we could eliminate the extra query or two for each BasicAuditTrail there are pages that would have their number of queries cut in half so this is relatively important to figure out from a performance point of view. Version-Release number of selected component (if applicable): How reproducible: Steps to Reproduce: 1. 2. 3. Actual results: Expected results: Additional info:
*** Bug 113752 has been marked as a duplicate of this bug. ***
> BasicAuditTrail[0..1] auditTrail = > join cms_items.item_id to acs_auditing.object_id; You'll probably have better luck doing something like this: BasicAuditTrail[0..1] auditTrail = join cms_items.audit_id to acs_auditing.object_id; This adds a new foreign key column to cms_items rather than trying to reuse an existing column. Since ContentItem extends VersionedACSObject, cms_items.item_id already references vc_objects.object_id. What you're saying is, Oh, and by the way, cms_items.item_id should *also* reference acs_auditing.object_id. This may be a valid modeling technique which needs to be supported in the long run. (Although I'm not sure if you can have a single column reference two different primary columns.) In the short term, I think adding the explicit new column cms_items.audit_id may be the quickest way to get this thing up and running.
I agree that adding the column is the best short term approach but I am hesitant to add that in to the core product. Right now, we have the functionality that we need (but not the performance we would like) without using the association. If you think that adding the duplicate column is the correct solution for now, I can just do that (which should make things work and we should get the benefit of less queries per page) with the downfall of having two identical columns in the same table. Is this an acceptable workaround for us to place in the CMS product until the persistence feature can be supported (or another apporach found) in the long run?
problem solved using oql/qualias functionality