Uniqueness of Relation Names
Here’s an interesting scenario that we recently ran into when developing our SchemaEditor application for Clarify.
In Clarify, the rule is that relation names must be unique within a table, which makes sense. For example, I cannot have two relations named case2site on the case table. The schema editing tools (ddcomp, DD Editor, Dovetail SchemaEditor) will disallow this. But there is no problem having case2site on the case table and case2site on the subcase table (although it’s a bad naming convention, it’s not illegal).
However, in Microsoft SQL Server databases, Clarify creates relate stored procedures for relations. For example, the following relation:
case2subcase OTM subcase
INV_REL=subcase2case COMMENT=”Subcases originated from the case”
will produce the following stored proc:
create procedure rl_case2subcase( @case1 int, @subcase2 int) as update table_subcase set subcase2case = @case1 where objid=@subcase2
How and where do these stored procs get used? I’m not exactly sure. Probably deep in the bowels of POMS somewhere, where my mind dares not go.
Notice the name of the stored proc is the same as the name of the relation. No problems yet. But, what if I have two relations with the same name on two different tables? Let’s try it out.
On the phone table, we’ll add a new relation:
x_phone2person OTM person
USER_DEFINED
INV_REL=x_person2phone COMMENT=””
On the modem table, we’ll add a new relation with the same name:
x_phone2person OTM person
USER_DEFINED
INV_REL=x_person2modem COMMENT=””
Using ddcomp, we’ll apply these schema changes, which applies the changes without an issue. Looking at the sql log file, we see:
select name from sysobjects where name=’rl_x_phone2person’
create procedure rl_x_phone2person( @phone1 int, @person2 int) as update table_person set x_person2phone = @phone1 where objid=@person2
grant EXEC on rl_x_phone2person to PUBLIC
select name from sysobjects where name=’rl_x_phone2person’
drop proc rl_x_phone2person
create procedure rl_x_phone2person( @modem1 int, @person2 int) as update table_person set x_person2modem = @modem1 where objid=@person2
grant EXEC on rl_x_phone2person to PUBLIC
Notice what happened. The second stored proc overwrote the first, so we only end up with one stored proc.
What does this mean? To me, it means that relations names should be unique across the entire database, not just within a single table.
To be honest, I’m not even sure if these relate stored procs get used by POMS. I’m sure at some point in time they were, but I’m not positive if they are still used or not. Anyone care to turn on SQL logging for clarify.exe for a day and see if any of these rl_ stored procs are called?
Hopefully everyone is using good, descriptive relation names, which would prevent this situation from happening in the first place.
And there you have it, one more piece of deep Clarify knowledge that’s probably more than you ever wished to know.
