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 Sun, 04 Sep 2005 22:35:19 GMT
Hi Craig,

you are right. I will change the project_member table as we discussed 
and check in the changes tomorrow.

Regards Michael

> Hi Michael,
>
> You won't hear any objections from the U.S. Everyone is on vacation 
> until Tuesday!
>
> So let's go ahead. We can always make minor changes later.
>
> On Sep 4, 2005, at 3:18 PM, 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.
>>>
>>
>> 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.
>
>
> I don't know for sure, but I don't think so. I think the first pattern 
> creates an FK to the primary key column.
>
> Anyone have a SQL reference handy?
>
>> 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?
>
>
> Yes. Let's make the FK definitions explicit.
>
> Regards,
>
> Craig
>
>>
>> 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            
>>
>>
>
> 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