You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tuscany.apache.org by Adriano Crestani <ad...@apache.org> on 2007/04/27 07:24:14 UTC
DAS - relationship with composite keys
I wonder what happens if a relationship has composite keys and the query
does not returns all the relationship keys. Is the relationship ignored and
the keys returned are treated only as containment attributes on the sdo
graph?
Adriano Crestani
Re: DAS - relationship with composite keys
Posted by Amita Vadhavkar <am...@gmail.com>.
I think with JIRA-1464 resolved, this issue is fixed.
Regards,
Amita
On 4/28/07, Adriano Crestani <ad...@apache.org> wrote:
>
> Sorry, the query above should be "SELECT A.id <http://a.id/>, A.b_id1,
> B_id1,
> B_id2 from A, B;" instead of "SELECT A.id <http://a.id/>, A.b_id1 from A,
> B;"
>
> Adriano Crestani
>
> On 4/27/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > I guess the relationship is considered for the key part present in
> select.
> > e.g. if you look at the current CompoundKeyRelationshipTests in java rdb
> > das,
> >
> > If the relationship is considered only for the keypart present in
> select,
> > the populated graph will possible not correctly represent the data on
> the
> > database.
> >
> > For example:
> >
> > Table A:
> > Metadata = id, b_id1, b_id2 (composed foreign key)
> > Row1 = 1, 1, 1
> > Row2 = 2, 2, 1
> > Row3 = 3, 1, 2
> > Row4 = 4, 2, 2
> >
> > Table B:
> > Metadata = id1, id2 (composed primary key)
> > Row1 = 1, 1
> > Row2 = 2, 1
> > Row3 = 1, 2
> > Row4 = 2, 2
> >
> > To clarify the references between tables mapped to ERD(Entity
> Relationship
> > Diagram):
> >
> > B(Row1) -> A(Row1)
> > B(Row2) -> A(Row2)
> > B (Row3) -> A (Row3)
> > B(Row4) -> A(Row4)
> >
> > Relationship definition:
> > <Relationship name="*A->B*" primaryKeyTable="B"
> foreignKeyTable="A"many
> > ="*true*">
> > <KeyPair primaryKeyColumn="id1" foreignKeyColumn ="*b_id1*" />
> > <KeyPair primaryKeyColumn ="id2" foreignKeyColumn="*b_id2*" />
> > </Relationship>
> >
> > The query "SELECT A.id, A.b_id1 from A, B;"
> >
> > Supposing that the relationship is created even if not all the keypairs
> > are present on the result, the sdo graph references created, if I'm not
> > missing anything, as described bellow:
> >
> > B(1, 1) -> A(1, 1)
> >
> > B(3, 1) -> A(1, 1)
> >
> > B(2, 1) -> A(2, 2)
> >
> > B(2, 2) -> A(2, 2)
> >
> > B(1, 1) -> A(3, 1)
> >
> > B(3, 1) -> A(3, 1)
> >
> > B(2, 1) -> A(4, 2)
> >
> > B(2, 2) -> A(4, 2)
> >
> > Concluding, the data related on sdo graph are not correctly described,
> > cause it is different from how they are related on the database. Though,
> if
> > the sdo user does not know how the data are on the database, the user
> would
> > get a wrong idea of how data are linked. On the example above the sdo
> user
> > may think that a row of table A is referenced by two different rows of
> table
> > B, but on the database it does not exist.
> >
> > Adriano Crestani
> >
> >
> > On 4/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > >
> > > I am not sure if I am interpreting the question correct, but please
> see
> > > below findings.
> > >
> > > I guess the relationship is considered for the key part present in
> > > select.
> > > e.g. if you look at the current CompoundKeyRelationshipTests in java
> rdb
> > > das,
> > > Data:
> > > -----
> > > protected static Object[][] orderDetailsData = {{ Integer.valueOf
> > > (1),
> > > Integer.valueOf(1), new Float(1.1)},
> > > {Integer.valueOf(1), Integer.valueOf(2), new Float(1.2)},
> > > {Integer.valueOf(2), Integer.valueOf(1), new Float(2.1)},
> > > {Integer.valueOf (2), Integer.valueOf(2), new Float(2.2)}};
> > >
> > >
> > > protected static Object[][] orderDetailsDescriptionData =
> > > { {Integer.valueOf(1), Integer.valueOf(1), Integer.valueOf(1),
> > > "Descr
> > > 1,1,1"},
> > > { Integer.valueOf(2), Integer.valueOf(1), Integer.valueOf(1),
> > > "Descr
> > > 2,1,1"},
> > > {Integer.valueOf(3), Integer.valueOf(1), Integer.valueOf(2),
> > > "Descr
> > > 3,1,2"},
> > > {Integer.valueOf(4), Integer.valueOf(1), Integer.valueOf(2),
> > > "Descr
> > > 4,1,2"},
> > > {Integer.valueOf(5), Integer.valueOf(2), Integer.valueOf(1),
> > > "Descr
> > > 6,2,1"},
> > > {Integer.valueOf(6), Integer.valueOf(2), Integer.valueOf(2),
> > > "Descr
> > > 6,2,2"}};
> > >
> > > SQL:
> > > -----
> > > the below statement's graph does consider relationship
> > > String statement = "SELECT ORDERDETAILS.ORDERID,
> > > ORDERDETAILSDESC.ORDERID
> > > FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
> > > + "ON ORDERDETAILS.ORDERID =
> ORDERDETAILSDESC.ORDERIDAND "
> > > +
> > > " ORDERDETAILS.PRODUCTID =
> ORDERDETAILSDESC.PRODUCTID" +
> > > " WHERE ORDERDETAILS.ORDERID = 1 AND
> > > ORDERDETAILS.PRODUCTID = 1";
> > >
> > > where the compound key is {ORDERID, PRODUCTID}.
> > >
> > > Similar is case when we have
> > > SELECT ORDERDETAILS.ORDERID, ORDERDETAILSDESC.PRODUCTID....
> > >
> > > OR
> > > basically at lease any one column from ORDERDETAILS.ORDERID/PRODUCTID
> > > and at
> > > least any one
> > > column from ORDERDETAILSDESC.ORDERID/PRODUCTID - makes DAS to look at
> > > relationship.
> > >
> > > But I see there one more confusion:-
> > > even if the relationship is getting considered, in above cases or even
> > > case
> > > like below which covers
> > > the "complete" compound key:-
> > > "SELECT ORDERDETAILS.ORDERID, ORDERDETAILS.PRODUCTID,
> > > ORDERDETAILSDESC.ORDERID,ORDERDETAILSDESC.PRODUCTID"+
> > > " FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
> > > + "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERID AND " +
> > > " ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID " +
> > > " WHERE ORDERDETAILS.ORDERID = 1 AND ORDERDETAILS.PRODUCTID = 1";
> > >
> > > As, TableData.columnData is a java Map, the returned count of
> > > orderdetailsdesc for the orderdetail(1,1) is
> > > shown "1" in the data graph, where as in the database table rows it is
> > > "2".
> > >
> > > This is merely happening as Map does not allow duplicates. Is this a
> > > code
> > > bug or there is
> > > some more consideration behind using Map?
> > > Regards,
> > > Amita
> > >
> > >
> > > On 4/27/07, Adriano Crestani < adrianocrestani@apache.org> wrote:
> > > >
> > > > I wonder what happens if a relationship has composite keys and the
> > > query
> > > > does not returns all the relationship keys. Is the relationship
> > > ignored
> > > > and
> > > > the keys returned are treated only as containment attributes on the
> > > sdo
> > > > graph?
> > > >
> > > > Adriano Crestani
> > > >
> > >
> >
> >
>
Re: DAS - relationship with composite keys
Posted by Adriano Crestani <ad...@apache.org>.
Sorry, the query above should be "SELECT A.id <http://a.id/>, A.b_id1, B_id1,
B_id2 from A, B;" instead of "SELECT A.id <http://a.id/>, A.b_id1 from A,
B;"
Adriano Crestani
On 4/27/07, Adriano Crestani <ad...@apache.org> wrote:
>
> I guess the relationship is considered for the key part present in select.
> e.g. if you look at the current CompoundKeyRelationshipTests in java rdb
> das,
>
> If the relationship is considered only for the keypart present in select,
> the populated graph will possible not correctly represent the data on the
> database.
>
> For example:
>
> Table A:
> Metadata = id, b_id1, b_id2 (composed foreign key)
> Row1 = 1, 1, 1
> Row2 = 2, 2, 1
> Row3 = 3, 1, 2
> Row4 = 4, 2, 2
>
> Table B:
> Metadata = id1, id2 (composed primary key)
> Row1 = 1, 1
> Row2 = 2, 1
> Row3 = 1, 2
> Row4 = 2, 2
>
> To clarify the references between tables mapped to ERD(Entity Relationship
> Diagram):
>
> B(Row1) -> A(Row1)
> B(Row2) -> A(Row2)
> B (Row3) -> A (Row3)
> B(Row4) -> A(Row4)
>
> Relationship definition:
> <Relationship name="*A->B*" primaryKeyTable="B" foreignKeyTable="A"many
> ="*true*">
> <KeyPair primaryKeyColumn="id1" foreignKeyColumn ="*b_id1*" />
> <KeyPair primaryKeyColumn ="id2" foreignKeyColumn="*b_id2*" />
> </Relationship>
>
> The query "SELECT A.id, A.b_id1 from A, B;"
>
> Supposing that the relationship is created even if not all the keypairs
> are present on the result, the sdo graph references created, if I'm not
> missing anything, as described bellow:
>
> B(1, 1) -> A(1, 1)
>
> B(3, 1) -> A(1, 1)
>
> B(2, 1) -> A(2, 2)
>
> B(2, 2) -> A(2, 2)
>
> B(1, 1) -> A(3, 1)
>
> B(3, 1) -> A(3, 1)
>
> B(2, 1) -> A(4, 2)
>
> B(2, 2) -> A(4, 2)
>
> Concluding, the data related on sdo graph are not correctly described,
> cause it is different from how they are related on the database. Though, if
> the sdo user does not know how the data are on the database, the user would
> get a wrong idea of how data are linked. On the example above the sdo user
> may think that a row of table A is referenced by two different rows of table
> B, but on the database it does not exist.
>
> Adriano Crestani
>
>
> On 4/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> >
> > I am not sure if I am interpreting the question correct, but please see
> > below findings.
> >
> > I guess the relationship is considered for the key part present in
> > select.
> > e.g. if you look at the current CompoundKeyRelationshipTests in java rdb
> > das,
> > Data:
> > -----
> > protected static Object[][] orderDetailsData = {{ Integer.valueOf
> > (1),
> > Integer.valueOf(1), new Float(1.1)},
> > {Integer.valueOf(1), Integer.valueOf(2), new Float(1.2)},
> > {Integer.valueOf(2), Integer.valueOf(1), new Float(2.1)},
> > {Integer.valueOf (2), Integer.valueOf(2), new Float(2.2)}};
> >
> >
> > protected static Object[][] orderDetailsDescriptionData =
> > { {Integer.valueOf(1), Integer.valueOf(1), Integer.valueOf(1),
> > "Descr
> > 1,1,1"},
> > { Integer.valueOf(2), Integer.valueOf(1), Integer.valueOf(1),
> > "Descr
> > 2,1,1"},
> > {Integer.valueOf(3), Integer.valueOf(1), Integer.valueOf(2),
> > "Descr
> > 3,1,2"},
> > {Integer.valueOf(4), Integer.valueOf(1), Integer.valueOf(2),
> > "Descr
> > 4,1,2"},
> > {Integer.valueOf(5), Integer.valueOf(2), Integer.valueOf(1),
> > "Descr
> > 6,2,1"},
> > {Integer.valueOf(6), Integer.valueOf(2), Integer.valueOf(2),
> > "Descr
> > 6,2,2"}};
> >
> > SQL:
> > -----
> > the below statement's graph does consider relationship
> > String statement = "SELECT ORDERDETAILS.ORDERID,
> > ORDERDETAILSDESC.ORDERID
> > FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
> > + "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERIDAND "
> > +
> > " ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID" +
> > " WHERE ORDERDETAILS.ORDERID = 1 AND
> > ORDERDETAILS.PRODUCTID = 1";
> >
> > where the compound key is {ORDERID, PRODUCTID}.
> >
> > Similar is case when we have
> > SELECT ORDERDETAILS.ORDERID, ORDERDETAILSDESC.PRODUCTID....
> >
> > OR
> > basically at lease any one column from ORDERDETAILS.ORDERID/PRODUCTID
> > and at
> > least any one
> > column from ORDERDETAILSDESC.ORDERID/PRODUCTID - makes DAS to look at
> > relationship.
> >
> > But I see there one more confusion:-
> > even if the relationship is getting considered, in above cases or even
> > case
> > like below which covers
> > the "complete" compound key:-
> > "SELECT ORDERDETAILS.ORDERID, ORDERDETAILS.PRODUCTID,
> > ORDERDETAILSDESC.ORDERID,ORDERDETAILSDESC.PRODUCTID"+
> > " FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
> > + "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERID AND " +
> > " ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID " +
> > " WHERE ORDERDETAILS.ORDERID = 1 AND ORDERDETAILS.PRODUCTID = 1";
> >
> > As, TableData.columnData is a java Map, the returned count of
> > orderdetailsdesc for the orderdetail(1,1) is
> > shown "1" in the data graph, where as in the database table rows it is
> > "2".
> >
> > This is merely happening as Map does not allow duplicates. Is this a
> > code
> > bug or there is
> > some more consideration behind using Map?
> > Regards,
> > Amita
> >
> >
> > On 4/27/07, Adriano Crestani < adrianocrestani@apache.org> wrote:
> > >
> > > I wonder what happens if a relationship has composite keys and the
> > query
> > > does not returns all the relationship keys. Is the relationship
> > ignored
> > > and
> > > the keys returned are treated only as containment attributes on the
> > sdo
> > > graph?
> > >
> > > Adriano Crestani
> > >
> >
>
>
Re: DAS - relationship with composite keys
Posted by Adriano Crestani <ad...@apache.org>.
I guess the relationship is considered for the key part present in select.
e.g. if you look at the current CompoundKeyRelationshipTests in java rdb
das,
If the relationship is considered only for the keypart present in select,
the populated graph will possible not correctly represent the data on the
database.
For example:
Table A:
Metadata = id, b_id1, b_id2 (composed foreign key)
Row1 = 1, 1, 1
Row2 = 2, 2, 1
Row3 = 3, 1, 2
Row4 = 4, 2, 2
Table B:
Metadata = id1, id2 (composed primary key)
Row1 = 1, 1
Row2 = 2, 1
Row3 = 1, 2
Row4 = 2, 2
To clarify the references between tables mapped to ERD(Entity Relationship
Diagram):
B(Row1) -> A(Row1)
B(Row2) -> A(Row2)
B(Row3) -> A(Row3)
B(Row4) -> A(Row4)
Relationship definition:
<Relationship name="*A->B*" primaryKeyTable="B" foreignKeyTable="A" many
="*true*">
<KeyPair primaryKeyColumn="id1" foreignKeyColumn="*b_id1*" />
<KeyPair primaryKeyColumn="id2" foreignKeyColumn="*b_id2*" />
</Relationship>
The query "SELECT A.id, A.b_id1 from A, B;"
Supposing that the relationship is created even if not all the keypairs are
present on the result, the sdo graph references created, if I'm not missing
anything, as described bellow:
B(1, 1) -> A(1, 1)
B(3, 1) -> A(1, 1)
B(2, 1) -> A(2, 2)
B(2, 2) -> A(2, 2)
B(1, 1) -> A(3, 1)
B(3, 1) -> A(3, 1)
B(2, 1) -> A(4, 2)
B(2, 2) -> A(4, 2)
Concluding, the data related on sdo graph are not correctly described, cause
it is different from how they are related on the database. Though, if the
sdo user does not know how the data are on the database, the user would get
a wrong idea of how data are linked. On the example above the sdo user may
think that a row of table A is referenced by two different rows of table B,
but on the database it does not exist.
Adriano Crestani
On 4/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
>
> I am not sure if I am interpreting the question correct, but please see
> below findings.
>
> I guess the relationship is considered for the key part present in select.
> e.g. if you look at the current CompoundKeyRelationshipTests in java rdb
> das,
> Data:
> -----
> protected static Object[][] orderDetailsData = {{Integer.valueOf(1),
> Integer.valueOf(1), new Float(1.1)},
> {Integer.valueOf(1), Integer.valueOf(2), new Float(1.2)},
> {Integer.valueOf(2), Integer.valueOf(1), new Float(2.1)},
> {Integer.valueOf(2), Integer.valueOf(2), new Float(2.2)}};
>
>
> protected static Object[][] orderDetailsDescriptionData =
> { {Integer.valueOf(1), Integer.valueOf(1), Integer.valueOf(1),
> "Descr
> 1,1,1"},
> {Integer.valueOf(2), Integer.valueOf(1), Integer.valueOf(1),
> "Descr
> 2,1,1"},
> {Integer.valueOf(3), Integer.valueOf(1), Integer.valueOf(2),
> "Descr
> 3,1,2"},
> {Integer.valueOf(4), Integer.valueOf(1), Integer.valueOf(2),
> "Descr
> 4,1,2"},
> {Integer.valueOf(5), Integer.valueOf(2), Integer.valueOf(1),
> "Descr
> 6,2,1"},
> {Integer.valueOf(6), Integer.valueOf(2), Integer.valueOf(2),
> "Descr
> 6,2,2"}};
>
> SQL:
> -----
> the below statement's graph does consider relationship
> String statement = "SELECT ORDERDETAILS.ORDERID, ORDERDETAILSDESC.ORDERID
> FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
> + "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERID AND
> "
> +
> " ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID "
> +
> " WHERE ORDERDETAILS.ORDERID = 1 AND
> ORDERDETAILS.PRODUCTID = 1";
>
> where the compound key is {ORDERID, PRODUCTID}.
>
> Similar is case when we have
> SELECT ORDERDETAILS.ORDERID, ORDERDETAILSDESC.PRODUCTID....
>
> OR
> basically at lease any one column from ORDERDETAILS.ORDERID/PRODUCTID and
> at
> least any one
> column from ORDERDETAILSDESC.ORDERID/PRODUCTID - makes DAS to look at
> relationship.
>
> But I see there one more confusion:-
> even if the relationship is getting considered, in above cases or even
> case
> like below which covers
> the "complete" compound key:-
> "SELECT ORDERDETAILS.ORDERID, ORDERDETAILS.PRODUCTID,
> ORDERDETAILSDESC.ORDERID,ORDERDETAILSDESC.PRODUCTID"+
> " FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
> + "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERID AND " +
> " ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID " +
> " WHERE ORDERDETAILS.ORDERID = 1 AND ORDERDETAILS.PRODUCTID = 1";
>
> As, TableData.columnData is a java Map, the returned count of
> orderdetailsdesc for the orderdetail(1,1) is
> shown "1" in the data graph, where as in the database table rows it is
> "2".
>
> This is merely happening as Map does not allow duplicates. Is this a code
> bug or there is
> some more consideration behind using Map?
> Regards,
> Amita
>
>
> On 4/27/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > I wonder what happens if a relationship has composite keys and the query
> > does not returns all the relationship keys. Is the relationship ignored
> > and
> > the keys returned are treated only as containment attributes on the sdo
> > graph?
> >
> > Adriano Crestani
> >
>
Re: DAS - relationship with composite keys
Posted by Amita Vadhavkar <am...@gmail.com>.
I am not sure if I am interpreting the question correct, but please see
below findings.
I guess the relationship is considered for the key part present in select.
e.g. if you look at the current CompoundKeyRelationshipTests in java rdb
das,
Data:
-----
protected static Object[][] orderDetailsData = {{Integer.valueOf(1),
Integer.valueOf(1), new Float(1.1)},
{Integer.valueOf(1), Integer.valueOf(2), new Float(1.2)},
{Integer.valueOf(2), Integer.valueOf(1), new Float(2.1)},
{Integer.valueOf(2), Integer.valueOf(2), new Float(2.2)}};
protected static Object[][] orderDetailsDescriptionData =
{ {Integer.valueOf(1), Integer.valueOf(1), Integer.valueOf(1), "Descr
1,1,1"},
{Integer.valueOf(2), Integer.valueOf(1), Integer.valueOf(1), "Descr
2,1,1"},
{Integer.valueOf(3), Integer.valueOf(1), Integer.valueOf(2), "Descr
3,1,2"},
{Integer.valueOf(4), Integer.valueOf(1), Integer.valueOf(2), "Descr
4,1,2"},
{Integer.valueOf(5), Integer.valueOf(2), Integer.valueOf(1), "Descr
6,2,1"},
{Integer.valueOf(6), Integer.valueOf(2), Integer.valueOf(2), "Descr
6,2,2"}};
SQL:
-----
the below statement's graph does consider relationship
String statement = "SELECT ORDERDETAILS.ORDERID, ORDERDETAILSDESC.ORDERID
FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
+ "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERID AND "
+
" ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID " +
" WHERE ORDERDETAILS.ORDERID = 1 AND
ORDERDETAILS.PRODUCTID = 1";
where the compound key is {ORDERID, PRODUCTID}.
Similar is case when we have
SELECT ORDERDETAILS.ORDERID, ORDERDETAILSDESC.PRODUCTID....
OR
basically at lease any one column from ORDERDETAILS.ORDERID/PRODUCTID and at
least any one
column from ORDERDETAILSDESC.ORDERID/PRODUCTID - makes DAS to look at
relationship.
But I see there one more confusion:-
even if the relationship is getting considered, in above cases or even case
like below which covers
the "complete" compound key:-
"SELECT ORDERDETAILS.ORDERID, ORDERDETAILS.PRODUCTID,
ORDERDETAILSDESC.ORDERID,ORDERDETAILSDESC.PRODUCTID"+
" FROM ORDERDETAILS LEFT JOIN ORDERDETAILSDESC "
+ "ON ORDERDETAILS.ORDERID = ORDERDETAILSDESC.ORDERID AND " +
" ORDERDETAILS.PRODUCTID = ORDERDETAILSDESC.PRODUCTID " +
" WHERE ORDERDETAILS.ORDERID = 1 AND ORDERDETAILS.PRODUCTID = 1";
As, TableData.columnData is a java Map, the returned count of
orderdetailsdesc for the orderdetail(1,1) is
shown "1" in the data graph, where as in the database table rows it is "2".
This is merely happening as Map does not allow duplicates. Is this a code
bug or there is
some more consideration behind using Map?
Regards,
Amita
On 4/27/07, Adriano Crestani <ad...@apache.org> wrote:
>
> I wonder what happens if a relationship has composite keys and the query
> does not returns all the relationship keys. Is the relationship ignored
> and
> the keys returned are treated only as containment attributes on the sdo
> graph?
>
> Adriano Crestani
>