A couple comments about Randy's excellent how-to about subsites and roles in this thread from nearly two years ago:
- There's now a tcl api for creating acs_rel_roles: rel_types::create_role (Malte added this earlier this year). So the plsql in Step 1 shouldn't be used, since Malte's proc automagically internationalizes the role for you.
- There is a surprising gotcha in rel_types::new due to its use of a couple utility procs that munge out names for the behind-the-scenes helper tables and their constraints (plsql_utility::generate_oracle_name and plsql_utility::generate_constraint_name). The problem is that these procs take the strings for the parameter rel_type or the switch table_name and create "oracle-safe" names from the "initials" from these strings -- as parsed from underscores. What can happen is that two very different rel_types can produce the same primary or foreign key constraint -- causing the rel_types::new proc to barf.
For example, if you have these two roles: foofoofoofoo_admin and fumfumfumfum_admin, these procs will generate the same primary key constraint: FAE_REL_ID_PK from both rel_types. This fails the second time, obviously, since two such constraints cannot have the same name.
Worse, rel_types::new doesn't rollback cleanly for reasons that escape me.
Anyway, the optimal solution would be for these utility procs to generate unique results (which they admit they don't). Another workaround would be for rel_types::new to append the next object_id from acs_object_id_seq before sending the value to the constraint name procs, but that actually wouldn't work reliably since they split along underscores, so foofoofoofoo_admin_1001 and fumfumfumfum_admin_1002 would still both turn into FA1E_REL_ID_PK.
The workaround I used for my purposes (bodily migrating all the roles from 3.2.5 sites at once) is to check for uniqueness in the calling code. This obviously isn't a permanent solution, though, as subsequent collisions may still happen when new roles are added at a later date. And at whatever point a UI is built to allow admin users -- and not just programmers -- to add roles, this will need to be dealt with.
Randy's write-up of this topic (with modifications) still hasn't migrated into the regular docs, but it certainly belongs there, and a good UI for handling roles (like 3.2.5 had) would be a great addition. I'll make a stab at this soon.
