Description of problem: The root category object map is a tuple of the form (object, category, use context) With the constraint that (object,use context) is unique. The setRootCategory method enforces an additional constraint of (object, category) being unique. This prevents us using the same category tree for several different purposes. For example the 'Local Government Services List' for APLAWS is primarily used as the root for the 'subject' use context of an object. There are, however, circumstances when it is used in the 'navigation' use context at the same time. Version-Release number of selected component (if applicable): How reproducible: Steps to Reproduce: 1. ACSObject obj = SiteNode.getRootSiteNode() Category root = ...create the root cat... 2. Category.setROotForObject(obj, root, 'navigation'); 3. Category.setRootForObject(obj, root, 'subject'); Actual results: Only the (obj, root, 'subject') triple is persisted. Expected results: Both the (obj, root, 'subject') and (obj, root, 'navigation') tuples are persisted. Additional info:
I recall spending a fair amount of time thinking through the constraints that I ended up putting in, but for the life me I can't remember the rationale for requiring that (obj, category) be unique. It seems like it would be sufficient to require that (obj, useContext) be unique. So, yes, it does look like an unnecessary constraint to me now. If I come to remember why I put it in there in the first place, I'll post.
Ok, I remember now. Your description is a little backwards. The (object, category) uniqueness is the primary constraint (imposed by the data model), while the (object,useContext) uniqueness constraint is the additional one. Not the other way around. I agree, in principle, that the only constraint we should impose on (object, category, useContext) tripes is that (object,useContext) be unique. The troublesome (object,category) uniqueness constraint is the result of the chosen implementation. The cat_root_cat_object_map table is the result of the following PDL: association { Category[0..n] rootCategory = ... ACSObject[0..n] categoryOwner = ... String[0..1] useContext = cat_root_cat_object_map.use_context; } The logical model looks correct to me. I am not sure how I would go about modeling this differently than the above. The physical model is auto-generated by DDLWriter: com.redhat.persistence.metadata.UniqueKey.getSQL(UniqueKey.java) com.redhat.persistence.metadata.Table.getSQL(Table.java:173) com.redhat.persistence.metadata.Table.getSQL(Table.java:118) com.redhat.persistence.pdl.DDLWriter.write(DDLWriter.java:107) com.arsdigita.persistence.pdl.PDL.main(PDL.java:263) thusly: create table cat_root_cat_object_map ( category_id INTEGER not null, -- referential constraint for category_id deferred due to circular dependencies object_id INTEGER not null, -- referential constraint for object_id deferred due to circular dependencies use_context VARCHAR(4000), constraint cat_roo_cat_obj_map_ca_p_11spa primary key(object_id, category_id) ); I'm not sure if persistence would get confused, if I were to remove the primary key constraint manually. Cc'ing Archit.
Created attachment 97107 [details] take one Dan, What do you think about turning the above association block into a full-blown object type with a surrogate primary key? Note to self: ====================================================================== Attached is my current minimal patch. It doesn't seem to work -- probably because I'm doing something stupid. I'm getting the following exception: 2004-01-19 16:01:28,790 [ main] INFO rdbms.RDBMSEngine - {:useContext10=null, :categoryOwner9.id=8016} 2004-01-19 16:01:28,792 [ main] ERROR rdbms.RDBMSEngine - select crcom.id as c_1, crcom.use_context as c_2 from cat_root_cat_object_map crcom where (crcom.object_id = :categoryOwner9) and (crcom.use_context is null) java.sql.SQLException: ERROR: parser: parse error at or near ":" at character 117 at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:142) at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505) at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48) at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:486) at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:430) at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:261) at com.redhat.persistence.engine.rdbms.RDBMSEngine.execute(RDBMSEngine.java:252) at com.redhat.persistence.Cursor.execute(Cursor.java:124) at com.redhat.persistence.Cursor.next(Cursor.java:108) at com.arsdigita.persistence.DataQueryImpl.next(DataQueryImpl.java:465) at com.arsdigita.categorization.Category.setRootForObject(Category.java:1712) at com.arsdigita.categorization.Category.setRootForObject(Category.java:1683) at com.arsdigita.categorization.UseContextTest.testRootCategoryObjectMap(UseContextTest.java:111) Character 117 is the ":" in :categoryOwner9 in the "where" clause. I think this should be a question mark instead. Archit is in a meeting right now that he isn't likely go get out of soon. So, I'm putting this thing on hold for now, and am going to take a look at 113447 (totally unrelated).
Yeah, that idea looks reasonable to me, particularly since we've not got any legacy code to worry about.
Change 39552 seems to fix this. (I still need to do some cleanup.)
Dan's change 39556 and my change 39567 seem to have tied up most of the loose ends. Marking QA_READY.
Dennis added an upgrade script for Oracle in change 39581.
Dennis added an upgrade script for PostgreSQL in change 39582.