db-jdo-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Bouschen <mbo.t...@spree.de>
Subject Re: TCK Schema : M-N relations
Date Mon, 05 Sep 2005 13:05:47 GMT
Hi,

I checked in the changes (revision 278730). I included three changes wrt 
Andys patch:
- I applied all the  changes to schema[1-4].sql, too.
- I removed the foreign key definition from the CREATE TABLE statement 
for project_member, since we have a separate ALTER TABLE defining the 
foreign key.
- I changed all the REFERENCES clauses to not include an explicit column 
specification such that it always refers the primary key, e.g. I changed
   (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);
to
   (EMP_OF_THE_MONTH) REFERENCES persons;
Most of the FK definitions used the latter form already, but not all of 
them.

Regards Michael

> Hi Michael,
> 
> Michael Bouschen wrote:
> 
>> Hi Craig,
>>
>>> Hi Michael,
>>>
>>> I believe that the patch applies to the other 
>>> datastoreidentityschemafiles as well.
>>
>>
>>
>> Yes, this is what I think, too. I will check in the change for all 
>> datastoreidentity schema files unless I hear any objections.
>>
>>>
>>> Any idea why one join table uses this pattern:
>>>
>>> CREATE TABLE project_member (
>>>     PROJID INTEGER REFERENCES projects NOT NULL,
>>>     MEMBER INTEGER REFERENCES persons NOT NULL);
>>>
>>> and others use this pattern?
>>>
>>> CREATE TABLE project_reviewer (
>>>     PROJID INTEGER NOT NULL,
>>>     REVIEWER INTEGER NOT NULL
>>> );
>>> ALTER TABLE project_reviewer
>>>     ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
>>>         (PROJID) REFERENCES projects(PROJID);
>>>
>>> ALTER TABLE project_reviewer
>>>     ADD CONSTRAINT PR_REV_FK FOREIGN KEY
>>>         (REVIEWER) REFERENCES persons(PERSONID);
>>>
>>> I guess the latter allows us to name the foreign key so it's easier 
>>> to delete them by name.
>>
>>
> I changed the table definitions and added the constraints in an ALTER 
> TABLE statement so that we would have named constraints for a test.  I 
> don't remember which one now.  That one form specifies the column list 
> and the other defaults it is whimsy.
> 
>>
>> No I have no idea.
>>
>> This is a good catch. I guess the first pattern creates a FK to the 
>> column having the same name in the referenced table. 
> 
> 
> When the column list is omitted the referenced column(s) is(are) the 
> primary key of the referenced table.
> 
>> But we want the FK to reference a different column called 
>> DATASTORE_IDENTITY. Andys patch adds ALTER TABLE definitions for table 
>> project_member, so we should skip the REFERENCES clause from its 
>> definition:
>> CREATE TABLE project_member (
>>    PROJID INTEGER NOT NULL,
>>    MEMBER INTEGER NOT NULL
>> );
>>
>> What do you think?
> 
> 
> Yup.
> 
> -- Michelle
> 
>>
>> Regards Michael
>>
>>>
>>> Craig
>>>
>>> On Sep 4, 2005, at 2:55 PM, Michael Bouschen wrote:
>>>
>>>> Hi Andy,
>>>>
>>>> thanks for the patch!
>>>>
>>>> I think the changes also apply to the other datastoreidentity schema 
>>>> files: schema[1-4].sql, since they have exactly the same problem. Am 
>>>> I right? I have patched all 5 schema files in my workspace. I just 
>>>> want to double check before I check in the changes.
>>>>
>>>> Regards Michael
>>>>
>>>>
>>>>> Hi Craig,
>>>>>
>>>>>  
>>>>>
>>>>>
>>>>>> I'd be happy if you could propose a patch fixing the FK's.
>>>>>>
>>>>>>  
>>>>>
>>>>>
>>>>> patch is attached. Not raised a JIRA because in the time taken to 
>>>>> raise the JIRA somebody could just have applied the patch
>>>>>
>>>>>
>>>>>  
>>>>>
>>>>> ------------------------------------------------------------------------

>>>>>
>>>>>
>>>>> Index: test/sql/derby/datastoreidentity/schema.sql
>>>>> ===================================================================
>>>>> --- test/sql/derby/datastoreidentity/schema.sql    (revision 267234)
>>>>> +++ test/sql/derby/datastoreidentity/schema.sql    (working copy)
>>>>> @@ -167,12 +167,20 @@
>>>>> ALTER TABLE project_reviewer     ADD CONSTRAINT PR_PROJ_FK FOREIGN KEY
>>>>> -        (PROJID) REFERENCES projects(PROJID);
>>>>> +        (PROJID) REFERENCES projects(DATASTORE_IDENTITY);
>>>>> ALTER TABLE project_reviewer     ADD CONSTRAINT PR_REV_FK FOREIGN KEY
>>>>> -        (REVIEWER) REFERENCES persons(PERSONID);
>>>>> +        (REVIEWER) REFERENCES persons(DATASTORE_IDENTITY);
>>>>> +ALTER TABLE project_member +    ADD CONSTRAINT PM_PROJ_FK FOREIGN KEY
>>>>> +        (PROJID) REFERENCES projects(DATASTORE_IDENTITY);
>>>>> +
>>>>> +ALTER TABLE project_member +    ADD CONSTRAINT PM_MEMB_FK FOREIGN KEY
>>>>> +        (MEMBER) REFERENCES persons(DATASTORE_IDENTITY);
>>>>> +
>>>>> ALTER TABLE departments     ADD CONSTRAINT EMP_MO_FK FOREIGN KEY
>>>>>         (EMP_OF_THE_MONTH) REFERENCES persons(DATASTORE_IDENTITY);
>>>>>
>>>>>  
>>>>>
>>>>>
>>>>
>>>>
>>>> -- 
>>>> Michael Bouschen        Tech@Spree Engineering GmbH
>>>> mailto:mbo.tech@spree.de    http://www.tech.spree.de/
>>>> Tel.:++49/30/235 520-33        Buelowstr. 66            
>>>> Fax.:++49/30/2175 2012        D-10783 Berlin          
>>>
>>>
>>> Craig Russell
>>>
>>> Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
>>>
>>> 408 276-5638 mailto:Craig.Russell@sun.com
>>>
>>> P.S. A good JDO? O, Gasp!
>>>
>>>
>>
>>


-- 
Michael Bouschen		Tech@Spree Engineering GmbH
mailto:mbo.tech@spree.de	http://www.tech.spree.de/
Tel.:++49/30/235 520-33		Buelowstr. 66			
Fax.:++49/30/2175 2012		D-10783 Berlin			

Mime
View raw message