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!