Mailing-List: contact jdo-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: jdo-dev@db.apache.org
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Sun, 04 Sep 2005 20:26:03 -0700
From: Michelle Caisse <Michelle.Caisse@Sun.COM>
Subject: Re: TCK Schema : M-N relations
In-reply-to: <431B72C8.5050706@spree.de>
To: jdo-dev@db.apache.org
Message-id: <431BBACB.1050507@sun.com>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7BIT
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.2)
 Gecko/20040803
References: <200509032032.46354.andy@jpox.org>
 <065F314D-2F12-4D02-9BDB-746D98DD5225@Sun.COM>
 <200509040836.13401.andy@jpox.org> <431B6D51.5080508@spree.de>
 <C4B5FB96-1D88-4124-B210-423774F9A6A3@Sun.COM> <431B72C8.5050706@spree.de>

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!
>>
>>
>
>