The revenge of the fake primary key
The revenge of the fake primary key
- Subject: The revenge of the fake primary key
- From: "Pierre Bernard" <email@hidden>
- Date: Wed, 16 Apr 2003 16:24:16 +0200
- Thread-topic: The revenge of the fake primary key
Hi!
In order to ease the displaying of data which may hold status, error, etc. codes our application uses a reference table to lookup display labels matching those code values.
In the database this is materialized by a table with a 3-element primary key: the code value, the name of the entity where the code may be used as well as the name of the attribute the code value may appear in. Additionaly this table provides a short and a long display string.
Data tables have 'code' columns that to the database are plain 4-character Strings. No foreign key constraints.
In my Java code I wanted the codes to be represented by to-one relationships to the displayable "code" objects. I thus mapped a set of entities on the single table holding the codes. Each entity has a restricting qualifier specifying a value for both the entityName and attribute columns. I did not create a common parant class to all objects mapping this table.
Considering that the restricting qualifier provides constant values for 2 of the table's 3 primary key elements, I set the entities to have only the code column as simple primary key. It is unique for a given combination of entityName and attribute name.
With the code entities having simple primary keys it becomes possible to create plain to-one relationships from any entity that has a code attribute to the matching code entity.
This worked fine for a very long time. That is until we discovered that single stupid exception that breaks the whole idea. When the code relationship is mentionned in the sort order of a fetch specification EOF generates a join between the main table and the code table. The resulting SQL statement is obviously based on the assumption that the code entities participate in a single-table inheritance where their common primary key uniquely identifies any code object. Now this is not really true: to get unicity in my setup I also need to have the attributes from the restricting qualifier to be specified, yet EOF omits those from the SQL statement. The result is a join that introduces duplicate rows.
The quick workaround is to create database views that apply the restrictions formerly applied by restriicting qualifier.
A longer workaround - close to hacking - would be to subclass EOSQLExpression to amend the SQL statements so that for each join over a relationship to an entity with a restricting qualifier that qualifier is appended to the WHERE clause. I still don't really know how to do this. Hints appreciated.
Now I wonder if this should not be the default behavior when generating a SELECT statement. Granted, had I declared to EOF the exact primary key setup that exists in the database things would have worked. Granted, the restricting qualifier is not meant as a general purpose filter but as discriminant for inheritance.
Yet, why, in certain cases, take the liberty to skip over a restricting qualifier specified explicitely in the EOModel when respecting it would make things more flexible and consistent?
Best regards
Pierre Bernard
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.