You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@tuscany.apache.org by Amita Vadhavkar <am...@gmail.com> on 2007/07/18 14:51:46 UTC
[RDB DAS] Wrong Query Result when SELECT misses PKs
Sorry for the leng thy mail....
Tried to check the case when the database has parent-child tables and DAS
SELECT Command may/may not
contain the PKs of the tables. And found some quite confusing cases/results,
which are effectively giving
user a wrong impression of the data in tables.
Looks like there are places where we are allowing partial results, wrong
association in parent and child rows.
As RDB DAS logic revolves around PKs, can we state clearly that
"When Query SELECT does not include PK for a table, the data graph will be
empty for that table"
? i.e. in the below analysis, instead of giving wrong/partial result, at
least consistently give no result?
And make necessary code corrections to adhere to this statement?
Or any alternative approaches?
What DAS C++ is doing for this case? Just curious.
-------------------------------------------------------------------------------------
Say, take below data -
Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
Data:
SINGER
ID NAME
--------------------
1 Jonh
2 Jane
SONG
ID TITLE SINGERID
-------------------------------------
10 ABCD 1
20 Lamb 1
30 La ra ra 2
There are total 8 cases that I can see. viz.
No relationship in config
--------------------------------------------------
parent PK in SEL child PK in SEL Result
----------------------------------------------------------------------
[1] present present correct
[2] present missing wrong
[3] missing present wrong
[4] missing missing wrong
Relationship in config
[5] present present correct
[6] present missing wrong
[7] missing present wrong
[8] missing missing wrong
-------------------------------------------------------------------------------------
When relationship is not defined in DAS Config
DAS Client code:
----------------
DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"), getConnection());
Command select = das.getCommand("withNoRel-5/6/7/8");
DataObject root = select.executeQuery();
List singers = root.getList("SINGER");
if(singers != null){
System.out.println("Singer size:"+singers.size());
for(int i=0; i<singers.size(); i++){
System.out.println("SINGER NAME:"+
((DataObject)singers.get(i)).getString("NAME"));
}
}
List songs = root.getList("SONG");//as there is no relationship
(explicit/implicit)
if(songs != null){
System.out.println("Songs size "+songs .size());
for(int ii=0; ii<songs.size(); ii++){
System.out.println("SONG TITLE:"+
((DataObject)songs.get(ii)).getString("TITLE"));
}
}
}
-------------------------------------------------------------------------------------
Result:
-------------------------------------------------------------------------------------
[1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
WHERE SINGER.ID = SONG.SINGERID
Singer size:2
SINGER NAME:John
SINGER NAME:Jane
Songs size 3
SONG TITLE:ABCD
SONG TITLE:Lamb
SONG TITLE:La ra ra
[2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
SINGER.ID = SONG.SINGERID
Singer size:2
SINGER NAME:John
SINGER NAME:Jane
Songs size 1
SONG TITLE:ABCD
[3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
SINGER.ID = SONG.SINGERID
Singer size:1
SINGER NAME:John
Songs size 3
SONG TITLE:ABCD
SONG TITLE:Lamb
SONG TITLE:La ra ra
[4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
SONG.SINGERID
Singer size:1
SINGER NAME:John
Songs size 1
SONG TITLE:ABCD
-------------------------------------------------------------------------------------
When relationship one-to-many (sing) is defined in DAS Config
DAS Client code:
----------------
DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"), getConnection());
Command select = das.getCommand("withRel-1/2/3/4");
DataObject root = select.executeQuery();
List singers = root.getList("SINGER");
if(singers != null){
System.out.println("Singer size:"+singers.size());
for(int i=0; i<singers.size(); i++){
System.out.println("SINGER NAME:"+
((DataObject)singers.get(i)).getString("NAME"));
List songs = ((DataObject)singers.get(i)).getList("sing"); //use
relationship
if(songs != null){
System.out.println("Songs size "+songs .size()+" for singer
:"+((DataObject)singers.get(i)).getString("NAME"));
for(int ii=0; ii<songs.size(); ii++){
System.out.println("SONG TITLE:"+
((DataObject)songs.get(ii)).getString("TITLE"));
}
}
}
}
-------------------------------------------------------------------------------------
Result:
-------------------------------------------------------------------------------------
[5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
WHERE SINGER.ID = SONG.SINGERID
Singer size:2
SINGER NAME:John
Songs size 2 for singer :John
SONG TITLE:ABCD
SONG TITLE:Lamb
SINGER NAME:Jane
Songs size 1 for singer :Jane
SONG TITLE:La ra ra
[6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
SINGER.ID = SONG.SINGERID
Singer size:2
SINGER NAME:John
Songs size 0 for singer :John
SINGER NAME:Jane
Songs size 1 for singer :Jane
SONG TITLE:ABCD
[7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
SINGER.ID = SONG.SINGERID
Singer size:1
SINGER NAME:John
Songs size 3 for singer :John
SONG TITLE:ABCD
SONG TITLE:Lamb
SONG TITLE:La ra ra
[8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
SONG.SINGERID
Singer size:1
SINGER NAME:John
Songs size 1 for singer :John
SONG TITLE:ABCD
-------------------------------------------------------------------------------------
Regards,
Amita
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Amita Vadhavkar <am...@gmail.com>.
Hi Adriano,
There are test cases like - testReadOrdersAndDetails2(
org.apache.tuscany.das.rdb.test.CompoundKeyTests)
which were failing when the code was throwing exception for any null data in
PK. With the
OUTER JOIN logic (no exception when all columns are null including PK , just
omit the DO),
these cases are succeeding now. But for the sake of completeness, added new
testOuterJoin() case.
Also, I added a case where in parent-child - child PK is null and the row
as such has some data(testNullDataInPK()). This throws exception. Please
review the latest
patch (Aug 6) for this.
On 8/4/07, Adriano Crestani <ad...@apache.org> wrote:
>
> Hi Amita,
>
> Have you added the case when the row is completely null on last patch
> you've
> uploaded on JIRA-1464?
>
> Regards,
> Adriano Crestani
>
> On 8/3/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > Hi Amita,
> >
> > Great work you are doing : ). You are correct about the outer join case,
> I
> > hadn't remember this use case. Yes, DAS should ignore a row completely
> null,
> > but only when it's completely null, any other data in it, DAS should
> throw
> > an exception.
> >
> > Thanks for the explanation about the static id column. I wasn't aware
> > enough about how static DO works : (
> >
> > Regards,
> > Adriano Crestani
> >
> > On 8/3/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > >
> > > In RDBMS and in DAS OUTER JOINs are used quite frequently. By
> definition
> > > it means, all rows from parent and matching rows from child. So, there
> > > is a
> > > chance for having complete null rows from child and that is the
> purpose
> > > of
> > > OUTER JOIN.
> > >
> > > So there are different situations
> > > 1) complete child row is null (outer join case)- we should allow this,
> > > so
> > > that outer joins work :)
> > > 2) PK(single/compound) in child row is null, but some other columns
> have
> > >
> > > data - we should flag this case and throw exception
> > > 3) PK(single/compound) in parent row is null, but some other columns
> > > have
> > > data - we should flag this case and throw exception
> > > 4) complete parent row is null - allow :) , as anyways relationship
> will
> > > not
> > > be formed
> > >
> > > If we do not allow 1) 20 existing cases which use OUTER JOIN will
> fail,
> > > otherwise
> > > all existing cases succeed.
> > >
> > > So, instead of giving exception for Null data in PK columns, we can
> > > avoid
> > > those DOs in final Graph.
> > >
> > > But, absence of PK column (Type) itself from Result Set is another
> case,
> > > where there is no way to form the correct graph irrespective of values
> > > in
> > > the columns.
> > >
> > > Due to the above reasons, I am not doing the change in logic over
> Jul31
> > > patch, but
> > > I am still uploading a new patch to fix 2 minor issues
> > > 1) name of the new xml file referred in test case was
> > > companyNoIDMappingWithConverters
> > > changing it to
> > > companynoidMappingWithConverters
> > >
> > > 2)if check in ResultSetProcessor.addRowToGraph() is made more complete
> > > to
> > > ensure
> > > no corner cases miss
> > >
> -----------------------------------------------------------------------------------------------------------------------------------
> > >
> > > For the other question in JIRA-1464, please see the below
> explaination:-
> > > Question:
> > > As ID column being considered primary key is a Convention Over
> > > Configuration
> > > issue, I think the user shouldn't need to declare it anywhere, cause
> DAS
> > >
> > > should recognize it anyway. What do you think?
> > >
> > > Ans:
> > > This will be true for Dynamic DO case, typically a query will be
> > > executed
> > > with ID column. COC will determine to treat it as PK. It will be used
> > > when
> > > registering new Type and Properties (SDO) in SDO context. And so when
> > > populating data in DOs, ID property will be found.
> > >
> > > But the change is done in company.xsd to take care of static DO
> > > scenario.
> > > Here, companyMappingWithConverters.xml refers to static model
> > > company.xsdand the generation
> > > of equiv java classes is before runtime. So, if ID is missing in
> > > company.xsd,
> > > ID will
> > > not be created in CompanyType...generated classes. After that in
> > > runtime,
> > > DAS will not be
> > > creating new Types and Properties for company as these are already in
> > > SDO
> > > context.
> > > Thus when populating DO with values from query, ID propery will not be
> > > found
> > > and exception will be thrown. Checked the same and get below
> exception.
> > > Example:-
> > > testSimpleStatic(org.apache.tuscany.das.rdb.test.CompanyTests) Time
> > > elapsed: 0.
> > > 18 sec <<< ERROR!
> > > java.lang.RuntimeException: Type CompanyType does not contain a
> property
> > > named I
> > >
> > > Regards,
> > > Amita
> > >
> > > On 8/2/07, Adriano Crestani <ad...@apache.org> wrote:
> > > >
> > > > Yes, I think it should fail, once DAS shouldn't omit a data from
> the
> > > > user.
> > > > Cause, if the pk has null pk, it means it doesn't have an id and
> > > cannot be
> > > > guaranteed its uniqueness. I think maybe DAS should not throw the
> > > > exception,
> > > > but needs to warn the user when any row is omitted, however I don't
> > > think
> > > > it
> > > > is a good approach at all. My suggestion is that it should fail
> > > whenever
> > > > is
> > > > found a null pk.
> > > >
> > > > Regards,
> > > > Adriano Crestani
> > > >
> > > > On 8/2/07, Amita Vadhavkar < amita.vadhavkar@gmail.com> wrote:
> > > > >
> > > > > There is a bit of confusion around the
> > > > RecursiveTests.testReadEngineParts
> > > > > ()
> > > > > , in the context of this fix.
> > > > >
> > > > > Below is the data for tables, queries etc.
> > > > >
> > > > > sql return:-
> > > > > *1 Engine 1 - 2 Block 1
> 1 -
> > > > > - - -
> > > > > *1 Engine 1 - 3 Cam Soft 2 1 -
> -
> > > > > - -
> > > > > 1 Engine 1 - 4 Piston 8
> 1 5
> > > > Piston
> > > > > Ring 2 4
> > > > >
> > > > > table data:-
> > > > > id name qty parent id
> > > > > 1 Engine 1 -
> > > > > 2 Block 1 1
> > > > > 3 Cam Soft 2 1
> > > > > 4 Piston 8 1
> > > > > 5 Piston Ring 2 4
> > > > >
> > > > > query:-
> > > > > SELECT
> > > > > P1.ID,
> > > > > P1.NAME,
> > > > > P1.QUANTITY ,
> > > > > P1.PARENT_ID,
> > > > > P2.ID,
> > > > > P2.NAME,
> > > > > P2.QUANTITY,
> > > > > P2.PARENT_ID,
> > > > > P3.ID,
> > > > > P3.NAME,
> > > > > P3.QUANTITY,
> > > > > P3.PARENT_ID
> > > > > FROM
> > > > > APP.PART AS P1 LEFT OUTER JOIN APP.PART AS P2
> > > > > ON P1.ID = P2.PARENT_ID
> > > > > LEFT OUTER JOIN APP.PART AS P3
> > > > > ON P2.ID = P3.PARENT_ID
> > > > > WHERE
> > > > > P1.ID = 1
> > > > >
> > > > > See the recursiveTests. here the recursion occurs 3 times on the
> > > same
> > > > > (part)
> > > > > table and total 5 DOs should be formed in mem. (pre-existing
> case).
> > > Now
> > > > > see
> > > > > ResultSetProcessor.addRowToGraph(). if we take null data in pk as
> > > > > exception,
> > > > > the rows from
> > > > > sql return above marked with *, will cause the whole query to fail
> > > and
> > > > so
> > > > > the recursiveTests
> > > > > will fail.
> > > > >
> > > > > But if we do some adjustments to allow this case to succeed, there
> > > can
> > > > be
> > > > > other situations
> > > > > where not throwing exception for null data in PK for any row can
> > > cause
> > > > > problem (incomplete/wrong results). So, is it better to make
> > > > > RecursiveTests
> > > > > fail? Suggestions?
> > > > >
> > > > > Regards,
> > > > > Amita
> > > > >
> > > > > On 7/28/07, Adriano Crestani < adrianocrestani@apache.org> wrote:
> > > > > >
> > > > > > It seems ok Amita ; )
> > > > > >
> > > > > > Adriano Crestani
> > > > > >
> > > > > > On 7/27/07, Amita Vadhavkar < amita.vadhavkar@gmail.com> wrote:
> > > > > > >
> > > > > > > Hi Adriano,
> > > > > > > Yes, so in summary , trying to do following -
> > > > > > >
> > > > > > > 1) select missing complete or partial PK for any of the tables
> > > > > involved-
> > > > > > > exception
> > > > > > > 2) if any table in select has no PK in config and no ID column
> > > in
> > > > > > > config/select - exception
> > > > > > > 3) if any table in select has no PK in config and has ID
> column
> > > in
> > > > > > config
> > > > > > > -
> > > > > > > exception
> > > > > > > 4) if any table in select has no PK in config but has ID
> column
> > > in
> > > > > > SELECT
> > > > > > > -
> > > > > > > success
> > > > > > >
> > > > > > > Regards,
> > > > > > > Amita
> > > > > > >
> > > > > > > On 7/27/07, Adriano Crestani <ad...@apache.org>
> wrote:
> > > > > > > >
> > > > > > > > I had the same problem on DAS C++, now it's throwing an
> > > exception
> > > > > when
> > > > > > > it
> > > > > > > > finds a row that does not contain all the pk columns.
> > > > > > > >
> > > > > > > > I'm not used to the DAS Java, but I will explain how I did
> it
> > > on
> > > > DAS
> > > > > > > C++,
> > > > > > > > maybe this can help you ; )
> > > > > > > >
> > > > > > > > It reads the ResultSet metadata to find the pk columns. If
> the
> > > PK
> > > > is
> > > > > > > > defined
> > > > > > > > on the config, so it look for the columns defined as pk on
> the
> > >
> > > > > config.
> > > > > > > In
> > > > > > > > case it does not find the pk column(or columns if it is a
> > > compound
> > > > > > pk),
> > > > > > > it
> > > > > > > > looks for the ID columns according to DAS Convention Over
> > > > > > Configuration
> > > > > > > > rules. Otherwise it throws the exception.
> > > > > > > >
> > > > > > > > Does it help? : )
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Adriano Crestani
> > > > > > > >
> > > > > > > > On 7/27/07, Amita Vadhavkar <am...@gmail.com>
> wrote:
> > > > > > > > >
> > > > > > > > > Further on this,
> > > > > > > > > Need to consider single and compound PKs case.
> > > > > > > > > When select does not include complete PK (all PK columns
> > > from
> > > > > > compound
> > > > > > > > PK)
> > > > > > > > > ,
> > > > > > > > > DAS needs to throw exception.
> > > > > > > > >
> > > > > > > > > As a fix proposing below changes:-
> > > > > > > > >
> > > > > > > > > A>In ResultMetadata - introduce new HashMap
> > > tableToPrimaryKeys ,
> > > > > > fill
> > > > > > > it
> > > > > > > > > during constuctor
> > > > > > > > > and provide get method - getAllPKsForTable(tableName).
> > > > > > > > >
> > > > > > > > > B> In ResultSetRow - add method
> > > > > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > > > > > > > which will take each table and check if all PKs are there
> in
> > > > > result
> > > > > > > set.
> > > > > > > > > If
> > > > > > > > > not it will mark that TableData with hasValidPKs=FALSE.
> > > > > > > > >
> > > > > > > > > C> There is already another check in TableData.addData(),
> > > which
> > > > > > marks
> > > > > > > > this
> > > > > > > > > flag FALSE, if any PK in result set has NULL data.
> > > > > > > > >
> > > > > > > > > B> and C> together will provide complete check
> > > > > > > > >
> > > > > > > > > D>In ResultSetRow, call,
> > > > > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> from
> > >
> > > > > > > > processRow()
> > > > > > > > > and processRecursiveRow(). With this, all TableData will
> be
> > > set
> > > > > with
> > > > > > > > > proper
> > > > > > > > > hasValidPK, during ResultSetProcessor.processResultSet()
> and
> > > > > > > > consequently,
> > > > > > > > > in ResultSetProcessor.addRowToGraph() will be able to do
> > > > > judgement
> > > > > > if
> > > > > > > > any
> > > > > > > > > table is missing PK, in which case DAS will throw
> > > > RuntimeException
> > > > > > and
> > > > > > > > > will
> > > > > > > > > not form DataGraph.
> > > > > > > > >
> > > > > > > > > Any comments/suggestions? Based on this I will work on
> patch
> > > for
> > > > > > > > > JIRA-1464.
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > Amita
> > > > > > > > >
> > > > > > > > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > > > > > > > >
> > > > > > > > > > It is best to throw an exception for PK not being there,
> > > > > otherwise
> > > > > > > an
> > > > > > > > > > empty
> > > > > > > > > > result set can have two meaning:Empty or something went
> > > wrong
> > > > > > > > > >
> > > > > > > > > > On 7/18/07, Adriano Crestani <adrianocrestani@apache.org
> >
> > > > wrote:
> > > > > > > > > > >
> > > > > > > > > > > Amita,
> > > > > > > > > > >
> > > > > > > > > > > There is now way for DAS to keep
> > > > > > > > the relationship data consistence
> > > > > > > > > > if
> > > > > > > > > > > both, pk and fk, are not completely defined. Without
> > > them
> > > > DAS
> > > > > > > cannot
> > > > > > > > > > > predict
> > > > > > > > > > > the relationship.
> > > > > > > > > > >
> > > > > > > > > > > As Brent said, I think it could throw an exception
> when
> > > the
> > > > PK
> > > > > > is
> > > > > > > > > > missing,
> > > > > > > > > > > no matter if there are relationships or not. Because,
> as
> > > far
> > > > > as
> > > > > > I
> > > > > > > > > know,
> > > > > > > > > > a
> > > > > > > > > > > table that has no complete PK retrieved on the
> > > ResultSet is
> > > > > > being
> > > > > > > > > > omitted
> > > > > > > > > > > from the graph and I don't think this is a good
> > > approach.
> > > > > > > > > > >
> > > > > > > > > > > But when only the fk is missing, I think it is ok to
> > > omit
> > > > the
> > > > > > > > > > relationship
> > > > > > > > > > > between the data objects on the graph. This way the
> user
> > > has
> > > > > the
> > > > > > > > > option
> > > > > > > > > > to
> > > > > > > > > > > decide if the references(relationships) will be
> included
> > > or
> > > > > not
> > > > > > on
> > > > > > > > the
> > > > > > > > > > > graph.
> > > > > > > > > > >
> > > > > > > > > > > Regards,
> > > > > > > > > > > Adriano Crestani
> > > > > > > > > > >
> > > > > > > > > > > On 7/18/07, Brent Daniel <br...@gmail.com>
> wrote:
> > > > > > > > > > > >
> > > > > > > > > > > > Amita,
> > > > > > > > > > > >
> > > > > > > > > > > > Definitely, the DAS should enforce the requirement
> > > that
> > > > the
> > > > > PK
> > > > > > > > > should
> > > > > > > > > > > > be returned for each table in the results. I would
> > > > consider
> > > > > > this
> > > > > > > a
> > > > > > > > > > > > case where the DAS should throw an exception.
> > > > > > > > > > > >
> > > > > > > > > > > > Brent
> > > > > > > > > > > >
> > > > > > > > > > > > On 7/18/07, Amita Vadhavkar <
> > > amita.vadhavkar@gmail.com>
> > > > > wrote:
> > > > > > > > > > > > > Sorry for the leng thy mail....
> > > > > > > > > > > > >
> > > > > > > > > > > > > Tried to check the case when the database has
> > > > parent-child
> > > > > > > > tables
> > > > > > > > > > and
> > > > > > > > > > > > DAS
> > > > > > > > > > > > > SELECT Command may/may not
> > > > > > > > > > > > > contain the PKs of the tables. And found some
> quite
> > > > > > confusing
> > > > > > > > > > > > cases/results,
> > > > > > > > > > > > > which are effectively giving
> > > > > > > > > > > > > user a wrong impression of the data in tables.
> > > > > > > > > > > > >
> > > > > > > > > > > > > Looks like there are places where we are allowing
> > > > partial
> > > > > > > > results,
> > > > > > > > > > > wrong
> > > > > > > > > > > > > association in parent and child rows.
> > > > > > > > > > > > > As RDB DAS logic revolves around PKs, can we state
> > > > clearly
> > > > > > > that
> > > > > > > > > > > > > "When Query SELECT does not include PK for a
> table,
> > > the
> > > > > data
> > > > > > > > graph
> > > > > > > > > > > will
> > > > > > > > > > > > be
> > > > > > > > > > > > > empty for that table"
> > > > > > > > > > > > > ? i.e. in the below analysis, instead of giving
> > > > > > wrong/partial
> > > > > > > > > > result,
> > > > > > > > > > > at
> > > > > > > > > > > > > least consistently give no result?
> > > > > > > > > > > > > And make necessary code corrections to adhere to
> > > this
> > > > > > > statement?
> > > > > > > > > > > > >
> > > > > > > > > > > > > Or any alternative approaches?
> > > > > > > > > > > > >
> > > > > > > > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > Say, take below data -
> > > > > > > > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE,
> > > > SINGERID)
> > > > > > > > > > > > > Data:
> > > > > > > > > > > > > SINGER
> > > > > > > > > > > > > ID NAME
> > > > > > > > > > > > > --------------------
> > > > > > > > > > > > > 1 Jonh
> > > > > > > > > > > > > 2 Jane
> > > > > > > > > > > > >
> > > > > > > > > > > > > SONG
> > > > > > > > > > > > > ID TITLE SINGERID
> > > > > > > > > > > > > -------------------------------------
> > > > > > > > > > > > > 10 ABCD 1
> > > > > > > > > > > > > 20 Lamb 1
> > > > > > > > > > > > > 30 La ra ra 2
> > > > > > > > > > > > >
> > > > > > > > > > > > > There are total 8 cases that I can see. viz.
> > > > > > > > > > > > >
> > > > > > > > > > > > > No relationship in config
> > > > > > > > > > > > > --------------------------------------------------
> > > > > > > > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > > > > > > > >
> > > > > > > > > >
> > > > > > >
> > > >
> ----------------------------------------------------------------------
> > > > > > > > > > > > > [1]
> > > > present present correct
> > > > > > > > > > > > > [2]
> > > present missing wrong
> > > > > > > > > > > > > [3]
> > > missing present wrong
> > > > > > > > > > > > > [4] missing missing
> > > wrong
> > > > > > > > > > > > >
> > > > > > > > > > > > > Relationship in config
> > > > > > > > > > > > > [5] present present
> > > correct
> > > > > > > > > > > > > [6] present missing
> > > wrong
> > > > > > > > > > > > > [7] missing present
> > > wrong
> > > > > > > > > > > > > [8]
> > > missing missing wrong
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > When relationship is not defined in DAS Config
> > > > > > > > > > > > > DAS Client code:
> > > > > > > > > > > > > ----------------
> > > > > > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml
> > > "),
> > > > > > > > > > > getConnection());
> > > > > > > > > > > > > Command select = das.getCommand
> > > ("withNoRel-5/6/7/8");
> > > > > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > > > > if(singers != null){
> > > > > > > > > > > > > System.out.println("Singer
> > > > size:"+singers.size());
> > > > > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > > > }
> > > > > > > > > > > > >
> > > > > > > > > > > > > }
> > > > > > > > > > > > >
> > > > > > > > > > > > > List songs = root.getList("SONG");//as there is no
> > > > > > > relationship
> > > > > > > > > > > > > (explicit/implicit)
> > > > > > > > > > > > >
> > > > > > > > > > > > > if(songs != null){
> > > > > > > > > > > > > System.out.println("Songs size "+songs
> > > .size());
> > > > > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > > > > }
> > > > > > > > > > > > > }
> > > > > > > > > > > > >
> > > > > > > > > > > > > }
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > Result:
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> > > > SONG.TITLEFROM
> > > > > > > > > SINGER,
> > > > > > > > > > > SONG
> > > > > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > > Songs size 3
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > > >
> > > > > > > > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM
> > > > SINGER,
> > > > > > > SONG
> > > > > > > > > > WHERE
> > > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > > Songs size 1
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > >
> > > > > > > > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > > SINGER,
> > > > > > SONG
> > > > > > > > > WHERE
> > > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > Songs size 3
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > > >
> > > > > > > > > > > > > [4] SELECT SINGER.NAME , SONG.TITLE FROM SINGER,
> > > SONG
> > > > WHERE
> > > > > > > > > SINGER.ID=
> > > > > > > > > > > > > SONG.SINGERID
> > > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > Songs size 1
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > When relationship one-to-many (sing) is defined in
> > > DAS
> > > > > > Config
> > > > > > > > > > > > > DAS Client code:
> > > > > > > > > > > > > ----------------
> > > > > > > > > > > > > DAS das = DAS.FACTORY.createDAS (getConfig("
> cfg.xml
> > > "),
> > > > > > > > > > > getConnection());
> > > > > > > > > > > > > Command select = das.getCommand
> ("withRel-1/2/3/4");
> > > > > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > > > > if(singers != null){
> > > > > > > > > > > > > System.out.println("Singer
> > > size:"+singers.size());
> > > > > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > > > > System.out.println ("SINGER NAME:"+
> > > > > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > > >
> > > > > > > > > > > > > List songs =
> > > > > > > > ((DataObject)singers.get(i)).getList("sing");
> > > > > > > > > > > //use
> > > > > > > > > > > > > relationship
> > > > > > > > > > > > > if(songs != null){
> > > > > > > > > > > > > System.out.println("Songs size "+songs
> > > > > .size()+"
> > > > > > > for
> > > > > > > > > > > singer
> > > > > > > > > > > > >
> :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > > > > }
> > > > > > > > > > > > > }
> > > > > > > > > > > > >
> > > > > > > > > > > > > }
> > > > > > > > > > > > > }
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > Result:
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> > > > SONG.TITLEFROM
> > > > > > > > > SINGER,
> > > > > > > > > > > SONG
> > > > > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > Songs size 2 for singer :John
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > > >
> > > > > > > > > > > > > [6] SELECT SINGER.ID , SINGER.NAME, SONG.TITLEFROM
> > > > SINGER,
> > > > > > > SONG
> > > > > > > > > > WHERE
> > > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > Songs size 0 for singer :John
> > > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > >
> > > > > > > > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > > SINGER,
> > > > > > SONG
> > > > > > > > > WHERE
> > > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > Songs size 3 for singer :John
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > > >
> > > > > > > > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER,
> SONG
> > > > WHERE
> > > > > > > > > SINGER.ID=
> > > > > > > > > > > > > SONG.SINGERID
> > > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > > Songs size 1 for singer :John
> > > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > > >
> > > > > > > > > > > > > Regards,
> > > > > > > > > > > > >
> > > > > > > > > > > > > Amita
> > > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > >
> > > > > >
> > > ---------------------------------------------------------------------
> > > > > > > > > > > > To unsubscribe, e-mail:
> > > > > tuscany-dev-unsubscribe@ws.apache.org
> > > > > > > > > > > > For additional commands, e-mail:
> > > > > > tuscany-dev-help@ws.apache.org
> > > > > > > > > > > >
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Adriano Crestani <ad...@apache.org>.
Hi Amita,
Have you added the case when the row is completely null on last patch you've
uploaded on JIRA-1464?
Regards,
Adriano Crestani
On 8/3/07, Adriano Crestani <ad...@apache.org> wrote:
>
> Hi Amita,
>
> Great work you are doing : ). You are correct about the outer join case, I
> hadn't remember this use case. Yes, DAS should ignore a row completely null,
> but only when it's completely null, any other data in it, DAS should throw
> an exception.
>
> Thanks for the explanation about the static id column. I wasn't aware
> enough about how static DO works : (
>
> Regards,
> Adriano Crestani
>
> On 8/3/07, Amita Vadhavkar <am...@gmail.com> wrote:
> >
> > In RDBMS and in DAS OUTER JOINs are used quite frequently. By definition
> > it means, all rows from parent and matching rows from child. So, there
> > is a
> > chance for having complete null rows from child and that is the purpose
> > of
> > OUTER JOIN.
> >
> > So there are different situations
> > 1) complete child row is null (outer join case)- we should allow this,
> > so
> > that outer joins work :)
> > 2) PK(single/compound) in child row is null, but some other columns have
> >
> > data - we should flag this case and throw exception
> > 3) PK(single/compound) in parent row is null, but some other columns
> > have
> > data - we should flag this case and throw exception
> > 4) complete parent row is null - allow :) , as anyways relationship will
> > not
> > be formed
> >
> > If we do not allow 1) 20 existing cases which use OUTER JOIN will fail,
> > otherwise
> > all existing cases succeed.
> >
> > So, instead of giving exception for Null data in PK columns, we can
> > avoid
> > those DOs in final Graph.
> >
> > But, absence of PK column (Type) itself from Result Set is another case,
> > where there is no way to form the correct graph irrespective of values
> > in
> > the columns.
> >
> > Due to the above reasons, I am not doing the change in logic over Jul31
> > patch, but
> > I am still uploading a new patch to fix 2 minor issues
> > 1) name of the new xml file referred in test case was
> > companyNoIDMappingWithConverters
> > changing it to
> > companynoidMappingWithConverters
> >
> > 2)if check in ResultSetProcessor.addRowToGraph() is made more complete
> > to
> > ensure
> > no corner cases miss
> > -----------------------------------------------------------------------------------------------------------------------------------
> >
> > For the other question in JIRA-1464, please see the below explaination:-
> > Question:
> > As ID column being considered primary key is a Convention Over
> > Configuration
> > issue, I think the user shouldn't need to declare it anywhere, cause DAS
> >
> > should recognize it anyway. What do you think?
> >
> > Ans:
> > This will be true for Dynamic DO case, typically a query will be
> > executed
> > with ID column. COC will determine to treat it as PK. It will be used
> > when
> > registering new Type and Properties (SDO) in SDO context. And so when
> > populating data in DOs, ID property will be found.
> >
> > But the change is done in company.xsd to take care of static DO
> > scenario.
> > Here, companyMappingWithConverters.xml refers to static model
> > company.xsdand the generation
> > of equiv java classes is before runtime. So, if ID is missing in
> > company.xsd,
> > ID will
> > not be created in CompanyType...generated classes. After that in
> > runtime,
> > DAS will not be
> > creating new Types and Properties for company as these are already in
> > SDO
> > context.
> > Thus when populating DO with values from query, ID propery will not be
> > found
> > and exception will be thrown. Checked the same and get below exception.
> > Example:-
> > testSimpleStatic(org.apache.tuscany.das.rdb.test.CompanyTests) Time
> > elapsed: 0.
> > 18 sec <<< ERROR!
> > java.lang.RuntimeException: Type CompanyType does not contain a property
> > named I
> >
> > Regards,
> > Amita
> >
> > On 8/2/07, Adriano Crestani <ad...@apache.org> wrote:
> > >
> > > Yes, I think it should fail, once DAS shouldn't omit a data from the
> > > user.
> > > Cause, if the pk has null pk, it means it doesn't have an id and
> > cannot be
> > > guaranteed its uniqueness. I think maybe DAS should not throw the
> > > exception,
> > > but needs to warn the user when any row is omitted, however I don't
> > think
> > > it
> > > is a good approach at all. My suggestion is that it should fail
> > whenever
> > > is
> > > found a null pk.
> > >
> > > Regards,
> > > Adriano Crestani
> > >
> > > On 8/2/07, Amita Vadhavkar < amita.vadhavkar@gmail.com> wrote:
> > > >
> > > > There is a bit of confusion around the
> > > RecursiveTests.testReadEngineParts
> > > > ()
> > > > , in the context of this fix.
> > > >
> > > > Below is the data for tables, queries etc.
> > > >
> > > > sql return:-
> > > > *1 Engine 1 - 2 Block 1 1 -
> > > > - - -
> > > > *1 Engine 1 - 3 Cam Soft 2 1 - -
> > > > - -
> > > > 1 Engine 1 - 4 Piston 8 1 5
> > > Piston
> > > > Ring 2 4
> > > >
> > > > table data:-
> > > > id name qty parent id
> > > > 1 Engine 1 -
> > > > 2 Block 1 1
> > > > 3 Cam Soft 2 1
> > > > 4 Piston 8 1
> > > > 5 Piston Ring 2 4
> > > >
> > > > query:-
> > > > SELECT
> > > > P1.ID,
> > > > P1.NAME,
> > > > P1.QUANTITY ,
> > > > P1.PARENT_ID,
> > > > P2.ID,
> > > > P2.NAME,
> > > > P2.QUANTITY,
> > > > P2.PARENT_ID,
> > > > P3.ID,
> > > > P3.NAME,
> > > > P3.QUANTITY,
> > > > P3.PARENT_ID
> > > > FROM
> > > > APP.PART AS P1 LEFT OUTER JOIN APP.PART AS P2
> > > > ON P1.ID = P2.PARENT_ID
> > > > LEFT OUTER JOIN APP.PART AS P3
> > > > ON P2.ID = P3.PARENT_ID
> > > > WHERE
> > > > P1.ID = 1
> > > >
> > > > See the recursiveTests. here the recursion occurs 3 times on the
> > same
> > > > (part)
> > > > table and total 5 DOs should be formed in mem. (pre-existing case).
> > Now
> > > > see
> > > > ResultSetProcessor.addRowToGraph(). if we take null data in pk as
> > > > exception,
> > > > the rows from
> > > > sql return above marked with *, will cause the whole query to fail
> > and
> > > so
> > > > the recursiveTests
> > > > will fail.
> > > >
> > > > But if we do some adjustments to allow this case to succeed, there
> > can
> > > be
> > > > other situations
> > > > where not throwing exception for null data in PK for any row can
> > cause
> > > > problem (incomplete/wrong results). So, is it better to make
> > > > RecursiveTests
> > > > fail? Suggestions?
> > > >
> > > > Regards,
> > > > Amita
> > > >
> > > > On 7/28/07, Adriano Crestani < adrianocrestani@apache.org> wrote:
> > > > >
> > > > > It seems ok Amita ; )
> > > > >
> > > > > Adriano Crestani
> > > > >
> > > > > On 7/27/07, Amita Vadhavkar < amita.vadhavkar@gmail.com> wrote:
> > > > > >
> > > > > > Hi Adriano,
> > > > > > Yes, so in summary , trying to do following -
> > > > > >
> > > > > > 1) select missing complete or partial PK for any of the tables
> > > > involved-
> > > > > > exception
> > > > > > 2) if any table in select has no PK in config and no ID column
> > in
> > > > > > config/select - exception
> > > > > > 3) if any table in select has no PK in config and has ID column
> > in
> > > > > config
> > > > > > -
> > > > > > exception
> > > > > > 4) if any table in select has no PK in config but has ID column
> > in
> > > > > SELECT
> > > > > > -
> > > > > > success
> > > > > >
> > > > > > Regards,
> > > > > > Amita
> > > > > >
> > > > > > On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
> > > > > > >
> > > > > > > I had the same problem on DAS C++, now it's throwing an
> > exception
> > > > when
> > > > > > it
> > > > > > > finds a row that does not contain all the pk columns.
> > > > > > >
> > > > > > > I'm not used to the DAS Java, but I will explain how I did it
> > on
> > > DAS
> > > > > > C++,
> > > > > > > maybe this can help you ; )
> > > > > > >
> > > > > > > It reads the ResultSet metadata to find the pk columns. If the
> > PK
> > > is
> > > > > > > defined
> > > > > > > on the config, so it look for the columns defined as pk on the
> >
> > > > config.
> > > > > > In
> > > > > > > case it does not find the pk column(or columns if it is a
> > compound
> > > > > pk),
> > > > > > it
> > > > > > > looks for the ID columns according to DAS Convention Over
> > > > > Configuration
> > > > > > > rules. Otherwise it throws the exception.
> > > > > > >
> > > > > > > Does it help? : )
> > > > > > >
> > > > > > > Regards,
> > > > > > > Adriano Crestani
> > > > > > >
> > > > > > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > > > >
> > > > > > > > Further on this,
> > > > > > > > Need to consider single and compound PKs case.
> > > > > > > > When select does not include complete PK (all PK columns
> > from
> > > > > compound
> > > > > > > PK)
> > > > > > > > ,
> > > > > > > > DAS needs to throw exception.
> > > > > > > >
> > > > > > > > As a fix proposing below changes:-
> > > > > > > >
> > > > > > > > A>In ResultMetadata - introduce new HashMap
> > tableToPrimaryKeys ,
> > > > > fill
> > > > > > it
> > > > > > > > during constuctor
> > > > > > > > and provide get method - getAllPKsForTable(tableName).
> > > > > > > >
> > > > > > > > B> In ResultSetRow - add method
> > > > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > > > > > > which will take each table and check if all PKs are there in
> > > > result
> > > > > > set.
> > > > > > > > If
> > > > > > > > not it will mark that TableData with hasValidPKs=FALSE.
> > > > > > > >
> > > > > > > > C> There is already another check in TableData.addData(),
> > which
> > > > > marks
> > > > > > > this
> > > > > > > > flag FALSE, if any PK in result set has NULL data.
> > > > > > > >
> > > > > > > > B> and C> together will provide complete check
> > > > > > > >
> > > > > > > > D>In ResultSetRow, call,
> > > > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> >
> > > > > > > processRow()
> > > > > > > > and processRecursiveRow(). With this, all TableData will be
> > set
> > > > with
> > > > > > > > proper
> > > > > > > > hasValidPK, during ResultSetProcessor.processResultSet() and
> > > > > > > consequently,
> > > > > > > > in ResultSetProcessor.addRowToGraph() will be able to do
> > > > judgement
> > > > > if
> > > > > > > any
> > > > > > > > table is missing PK, in which case DAS will throw
> > > RuntimeException
> > > > > and
> > > > > > > > will
> > > > > > > > not form DataGraph.
> > > > > > > >
> > > > > > > > Any comments/suggestions? Based on this I will work on patch
> > for
> > > > > > > > JIRA-1464.
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Amita
> > > > > > > >
> > > > > > > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > > > > > > >
> > > > > > > > > It is best to throw an exception for PK not being there,
> > > > otherwise
> > > > > > an
> > > > > > > > > empty
> > > > > > > > > result set can have two meaning:Empty or something went
> > wrong
> > > > > > > > >
> > > > > > > > > On 7/18/07, Adriano Crestani <ad...@apache.org>
> > > wrote:
> > > > > > > > > >
> > > > > > > > > > Amita,
> > > > > > > > > >
> > > > > > > > > > There is now way for DAS to keep
> > > > > > > the relationship data consistence
> > > > > > > > > if
> > > > > > > > > > both, pk and fk, are not completely defined. Without
> > them
> > > DAS
> > > > > > cannot
> > > > > > > > > > predict
> > > > > > > > > > the relationship.
> > > > > > > > > >
> > > > > > > > > > As Brent said, I think it could throw an exception when
> > the
> > > PK
> > > > > is
> > > > > > > > > missing,
> > > > > > > > > > no matter if there are relationships or not. Because, as
> > far
> > > > as
> > > > > I
> > > > > > > > know,
> > > > > > > > > a
> > > > > > > > > > table that has no complete PK retrieved on the
> > ResultSet is
> > > > > being
> > > > > > > > > omitted
> > > > > > > > > > from the graph and I don't think this is a good
> > approach.
> > > > > > > > > >
> > > > > > > > > > But when only the fk is missing, I think it is ok to
> > omit
> > > the
> > > > > > > > > relationship
> > > > > > > > > > between the data objects on the graph. This way the user
> > has
> > > > the
> > > > > > > > option
> > > > > > > > > to
> > > > > > > > > > decide if the references(relationships) will be included
> > or
> > > > not
> > > > > on
> > > > > > > the
> > > > > > > > > > graph.
> > > > > > > > > >
> > > > > > > > > > Regards,
> > > > > > > > > > Adriano Crestani
> > > > > > > > > >
> > > > > > > > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > > > > > > > >
> > > > > > > > > > > Amita,
> > > > > > > > > > >
> > > > > > > > > > > Definitely, the DAS should enforce the requirement
> > that
> > > the
> > > > PK
> > > > > > > > should
> > > > > > > > > > > be returned for each table in the results. I would
> > > consider
> > > > > this
> > > > > > a
> > > > > > > > > > > case where the DAS should throw an exception.
> > > > > > > > > > >
> > > > > > > > > > > Brent
> > > > > > > > > > >
> > > > > > > > > > > On 7/18/07, Amita Vadhavkar <
> > amita.vadhavkar@gmail.com>
> > > > wrote:
> > > > > > > > > > > > Sorry for the leng thy mail....
> > > > > > > > > > > >
> > > > > > > > > > > > Tried to check the case when the database has
> > > parent-child
> > > > > > > tables
> > > > > > > > > and
> > > > > > > > > > > DAS
> > > > > > > > > > > > SELECT Command may/may not
> > > > > > > > > > > > contain the PKs of the tables. And found some quite
> > > > > confusing
> > > > > > > > > > > cases/results,
> > > > > > > > > > > > which are effectively giving
> > > > > > > > > > > > user a wrong impression of the data in tables.
> > > > > > > > > > > >
> > > > > > > > > > > > Looks like there are places where we are allowing
> > > partial
> > > > > > > results,
> > > > > > > > > > wrong
> > > > > > > > > > > > association in parent and child rows.
> > > > > > > > > > > > As RDB DAS logic revolves around PKs, can we state
> > > clearly
> > > > > > that
> > > > > > > > > > > > "When Query SELECT does not include PK for a table,
> > the
> > > > data
> > > > > > > graph
> > > > > > > > > > will
> > > > > > > > > > > be
> > > > > > > > > > > > empty for that table"
> > > > > > > > > > > > ? i.e. in the below analysis, instead of giving
> > > > > wrong/partial
> > > > > > > > > result,
> > > > > > > > > > at
> > > > > > > > > > > > least consistently give no result?
> > > > > > > > > > > > And make necessary code corrections to adhere to
> > this
> > > > > > statement?
> > > > > > > > > > > >
> > > > > > > > > > > > Or any alternative approaches?
> > > > > > > > > > > >
> > > > > > > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > Say, take below data -
> > > > > > > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE,
> > > SINGERID)
> > > > > > > > > > > > Data:
> > > > > > > > > > > > SINGER
> > > > > > > > > > > > ID NAME
> > > > > > > > > > > > --------------------
> > > > > > > > > > > > 1 Jonh
> > > > > > > > > > > > 2 Jane
> > > > > > > > > > > >
> > > > > > > > > > > > SONG
> > > > > > > > > > > > ID TITLE SINGERID
> > > > > > > > > > > > -------------------------------------
> > > > > > > > > > > > 10 ABCD 1
> > > > > > > > > > > > 20 Lamb 1
> > > > > > > > > > > > 30 La ra ra 2
> > > > > > > > > > > >
> > > > > > > > > > > > There are total 8 cases that I can see. viz.
> > > > > > > > > > > >
> > > > > > > > > > > > No relationship in config
> > > > > > > > > > > > --------------------------------------------------
> > > > > > > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > > > > > > >
> > > > > > > > >
> > > > > >
> > > ----------------------------------------------------------------------
> > > > > > > > > > > > [1]
> > > present present correct
> > > > > > > > > > > > [2]
> > present missing wrong
> > > > > > > > > > > > [3]
> > missing present wrong
> > > > > > > > > > > > [4] missing missing
> > wrong
> > > > > > > > > > > >
> > > > > > > > > > > > Relationship in config
> > > > > > > > > > > > [5] present present
> > correct
> > > > > > > > > > > > [6] present missing
> > wrong
> > > > > > > > > > > > [7] missing present
> > wrong
> > > > > > > > > > > > [8]
> > missing missing wrong
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > When relationship is not defined in DAS Config
> > > > > > > > > > > > DAS Client code:
> > > > > > > > > > > > ----------------
> > > > > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml
> > "),
> > > > > > > > > > getConnection());
> > > > > > > > > > > > Command select = das.getCommand
> > ("withNoRel-5/6/7/8");
> > > > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > > > if(singers != null){
> > > > > > > > > > > > System.out.println("Singer
> > > size:"+singers.size());
> > > > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > > }
> > > > > > > > > > > >
> > > > > > > > > > > > }
> > > > > > > > > > > >
> > > > > > > > > > > > List songs = root.getList("SONG");//as there is no
> > > > > > relationship
> > > > > > > > > > > > (explicit/implicit)
> > > > > > > > > > > >
> > > > > > > > > > > > if(songs != null){
> > > > > > > > > > > > System.out.println("Songs size "+songs
> > .size());
> > > > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > > > }
> > > > > > > > > > > > }
> > > > > > > > > > > >
> > > > > > > > > > > > }
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > Result:
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> > > SONG.TITLEFROM
> > > > > > > > SINGER,
> > > > > > > > > > SONG
> > > > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > Songs size 3
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > >
> > > > > > > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM
> > > SINGER,
> > > > > > SONG
> > > > > > > > > WHERE
> > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > Songs size 1
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > >
> > > > > > > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > SINGER,
> > > > > SONG
> > > > > > > > WHERE
> > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > Songs size 3
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > >
> > > > > > > > > > > > [4] SELECT SINGER.NAME , SONG.TITLE FROM SINGER,
> > SONG
> > > WHERE
> > > > > > > > SINGER.ID=
> > > > > > > > > > > > SONG.SINGERID
> > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > Songs size 1
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > When relationship one-to-many (sing) is defined in
> > DAS
> > > > > Config
> > > > > > > > > > > > DAS Client code:
> > > > > > > > > > > > ----------------
> > > > > > > > > > > > DAS das = DAS.FACTORY.createDAS (getConfig("cfg.xml
> > "),
> > > > > > > > > > getConnection());
> > > > > > > > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > > > if(singers != null){
> > > > > > > > > > > > System.out.println("Singer
> > size:"+singers.size());
> > > > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > > > System.out.println ("SINGER NAME:"+
> > > > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > >
> > > > > > > > > > > > List songs =
> > > > > > > ((DataObject)singers.get(i)).getList("sing");
> > > > > > > > > > //use
> > > > > > > > > > > > relationship
> > > > > > > > > > > > if(songs != null){
> > > > > > > > > > > > System.out.println("Songs size "+songs
> > > > .size()+"
> > > > > > for
> > > > > > > > > > singer
> > > > > > > > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > > > }
> > > > > > > > > > > > }
> > > > > > > > > > > >
> > > > > > > > > > > > }
> > > > > > > > > > > > }
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > Result:
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> > > SONG.TITLEFROM
> > > > > > > > SINGER,
> > > > > > > > > > SONG
> > > > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > Songs size 2 for singer :John
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > >
> > > > > > > > > > > > [6] SELECT SINGER.ID , SINGER.NAME, SONG.TITLE FROM
> > > SINGER,
> > > > > > SONG
> > > > > > > > > WHERE
> > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > Singer size:2
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > Songs size 0 for singer :John
> > > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > >
> > > > > > > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > SINGER,
> > > > > SONG
> > > > > > > > WHERE
> > > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > Songs size 3 for singer :John
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > > >
> > > > > > > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > > WHERE
> > > > > > > > SINGER.ID=
> > > > > > > > > > > > SONG.SINGERID
> > > > > > > > > > > > Singer size:1
> > > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > > Songs size 1 for singer :John
> > > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> > -------------------------------------------------------------------------------------
> > > > > > > > > > > >
> > > > > > > > > > > > Regards,
> > > > > > > > > > > >
> > > > > > > > > > > > Amita
> > > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > >
> > > > >
> > ---------------------------------------------------------------------
> > > > > > > > > > > To unsubscribe, e-mail:
> > > > tuscany-dev-unsubscribe@ws.apache.org
> > > > > > > > > > > For additional commands, e-mail:
> > > > > tuscany-dev-help@ws.apache.org
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Adriano Crestani <ad...@apache.org>.
Hi Amita,
Great work you are doing : ). You are correct about the outer join case, I
hadn't remember this use case. Yes, DAS should ignore a row completely null,
but only when it's completely null, any other data in it, DAS should throw
an exception.
Thanks for the explanation about the static id column. I wasn't aware enough
about how static DO works : (
Regards,
Adriano Crestani
On 8/3/07, Amita Vadhavkar <am...@gmail.com> wrote:
>
> In RDBMS and in DAS OUTER JOINs are used quite frequently. By definition
> it means, all rows from parent and matching rows from child. So, there is
> a
> chance for having complete null rows from child and that is the purpose of
> OUTER JOIN.
>
> So there are different situations
> 1) complete child row is null (outer join case)- we should allow this, so
> that outer joins work :)
> 2) PK(single/compound) in child row is null, but some other columns have
> data - we should flag this case and throw exception
> 3) PK(single/compound) in parent row is null, but some other columns have
> data - we should flag this case and throw exception
> 4) complete parent row is null - allow :) , as anyways relationship will
> not
> be formed
>
> If we do not allow 1) 20 existing cases which use OUTER JOIN will fail,
> otherwise
> all existing cases succeed.
>
> So, instead of giving exception for Null data in PK columns, we can avoid
> those DOs in final Graph.
>
> But, absence of PK column (Type) itself from Result Set is another case,
> where there is no way to form the correct graph irrespective of values in
> the columns.
>
> Due to the above reasons, I am not doing the change in logic over Jul31
> patch, but
> I am still uploading a new patch to fix 2 minor issues
> 1) name of the new xml file referred in test case was
> companyNoIDMappingWithConverters
> changing it to
> companynoidMappingWithConverters
>
> 2)if check in ResultSetProcessor.addRowToGraph() is made more complete to
> ensure
> no corner cases miss
>
> -----------------------------------------------------------------------------------------------------------------------------------
> For the other question in JIRA-1464, please see the below explaination:-
> Question:
> As ID column being considered primary key is a Convention Over
> Configuration
> issue, I think the user shouldn't need to declare it anywhere, cause DAS
> should recognize it anyway. What do you think?
>
> Ans:
> This will be true for Dynamic DO case, typically a query will be executed
> with ID column. COC will determine to treat it as PK. It will be used when
> registering new Type and Properties (SDO) in SDO context. And so when
> populating data in DOs, ID property will be found.
>
> But the change is done in company.xsd to take care of static DO scenario.
> Here, companyMappingWithConverters.xml refers to static model
> company.xsdand the generation
> of equiv java classes is before runtime. So, if ID is missing in
> company.xsd,
> ID will
> not be created in CompanyType...generated classes. After that in runtime,
> DAS will not be
> creating new Types and Properties for company as these are already in SDO
> context.
> Thus when populating DO with values from query, ID propery will not be
> found
> and exception will be thrown. Checked the same and get below exception.
> Example:-
> testSimpleStatic(org.apache.tuscany.das.rdb.test.CompanyTests) Time
> elapsed: 0.
> 18 sec <<< ERROR!
> java.lang.RuntimeException: Type CompanyType does not contain a property
> named I
>
> Regards,
> Amita
>
> On 8/2/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > Yes, I think it should fail, once DAS shouldn't omit a data from the
> > user.
> > Cause, if the pk has null pk, it means it doesn't have an id and cannot
> be
> > guaranteed its uniqueness. I think maybe DAS should not throw the
> > exception,
> > but needs to warn the user when any row is omitted, however I don't
> think
> > it
> > is a good approach at all. My suggestion is that it should fail whenever
> > is
> > found a null pk.
> >
> > Regards,
> > Adriano Crestani
> >
> > On 8/2/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > >
> > > There is a bit of confusion around the
> > RecursiveTests.testReadEngineParts
> > > ()
> > > , in the context of this fix.
> > >
> > > Below is the data for tables, queries etc.
> > >
> > > sql return:-
> > > *1 Engine 1 - 2 Block 1 1 -
> > > - - -
> > > *1 Engine 1 - 3 Cam Soft 2 1 - -
> > > - -
> > > 1 Engine 1 - 4 Piston 8 1 5
> > Piston
> > > Ring 2 4
> > >
> > > table data:-
> > > id name qty parent id
> > > 1 Engine 1 -
> > > 2 Block 1 1
> > > 3 Cam Soft 2 1
> > > 4 Piston 8 1
> > > 5 Piston Ring 2 4
> > >
> > > query:-
> > > SELECT
> > > P1.ID,
> > > P1.NAME,
> > > P1.QUANTITY,
> > > P1.PARENT_ID,
> > > P2.ID,
> > > P2.NAME,
> > > P2.QUANTITY,
> > > P2.PARENT_ID,
> > > P3.ID,
> > > P3.NAME,
> > > P3.QUANTITY,
> > > P3.PARENT_ID
> > > FROM
> > > APP.PART AS P1 LEFT OUTER JOIN APP.PART AS P2
> > > ON P1.ID = P2.PARENT_ID
> > > LEFT OUTER JOIN APP.PART AS P3
> > > ON P2.ID = P3.PARENT_ID
> > > WHERE
> > > P1.ID = 1
> > >
> > > See the recursiveTests. here the recursion occurs 3 times on the same
> > > (part)
> > > table and total 5 DOs should be formed in mem. (pre-existing case).
> Now
> > > see
> > > ResultSetProcessor.addRowToGraph(). if we take null data in pk as
> > > exception,
> > > the rows from
> > > sql return above marked with *, will cause the whole query to fail and
> > so
> > > the recursiveTests
> > > will fail.
> > >
> > > But if we do some adjustments to allow this case to succeed, there can
> > be
> > > other situations
> > > where not throwing exception for null data in PK for any row can cause
> > > problem (incomplete/wrong results). So, is it better to make
> > > RecursiveTests
> > > fail? Suggestions?
> > >
> > > Regards,
> > > Amita
> > >
> > > On 7/28/07, Adriano Crestani <ad...@apache.org> wrote:
> > > >
> > > > It seems ok Amita ; )
> > > >
> > > > Adriano Crestani
> > > >
> > > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > >
> > > > > Hi Adriano,
> > > > > Yes, so in summary , trying to do following -
> > > > >
> > > > > 1) select missing complete or partial PK for any of the tables
> > > involved-
> > > > > exception
> > > > > 2) if any table in select has no PK in config and no ID column in
> > > > > config/select - exception
> > > > > 3) if any table in select has no PK in config and has ID column in
> > > > config
> > > > > -
> > > > > exception
> > > > > 4) if any table in select has no PK in config but has ID column in
> > > > SELECT
> > > > > -
> > > > > success
> > > > >
> > > > > Regards,
> > > > > Amita
> > > > >
> > > > > On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
> > > > > >
> > > > > > I had the same problem on DAS C++, now it's throwing an
> exception
> > > when
> > > > > it
> > > > > > finds a row that does not contain all the pk columns.
> > > > > >
> > > > > > I'm not used to the DAS Java, but I will explain how I did it on
> > DAS
> > > > > C++,
> > > > > > maybe this can help you ; )
> > > > > >
> > > > > > It reads the ResultSet metadata to find the pk columns. If the
> PK
> > is
> > > > > > defined
> > > > > > on the config, so it look for the columns defined as pk on the
> > > config.
> > > > > In
> > > > > > case it does not find the pk column(or columns if it is a
> compound
> > > > pk),
> > > > > it
> > > > > > looks for the ID columns according to DAS Convention Over
> > > > Configuration
> > > > > > rules. Otherwise it throws the exception.
> > > > > >
> > > > > > Does it help? : )
> > > > > >
> > > > > > Regards,
> > > > > > Adriano Crestani
> > > > > >
> > > > > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > > >
> > > > > > > Further on this,
> > > > > > > Need to consider single and compound PKs case.
> > > > > > > When select does not include complete PK (all PK columns from
> > > > compound
> > > > > > PK)
> > > > > > > ,
> > > > > > > DAS needs to throw exception.
> > > > > > >
> > > > > > > As a fix proposing below changes:-
> > > > > > >
> > > > > > > A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys
> ,
> > > > fill
> > > > > it
> > > > > > > during constuctor
> > > > > > > and provide get method - getAllPKsForTable(tableName).
> > > > > > >
> > > > > > > B> In ResultSetRow - add method
> > > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > > > > > which will take each table and check if all PKs are there in
> > > result
> > > > > set.
> > > > > > > If
> > > > > > > not it will mark that TableData with hasValidPKs=FALSE.
> > > > > > >
> > > > > > > C> There is already another check in TableData.addData(),
> which
> > > > marks
> > > > > > this
> > > > > > > flag FALSE, if any PK in result set has NULL data.
> > > > > > >
> > > > > > > B> and C> together will provide complete check
> > > > > > >
> > > > > > > D>In ResultSetRow, call,
> > > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> > > > > > processRow()
> > > > > > > and processRecursiveRow(). With this, all TableData will be
> set
> > > with
> > > > > > > proper
> > > > > > > hasValidPK, during ResultSetProcessor.processResultSet() and
> > > > > > consequently,
> > > > > > > in ResultSetProcessor.addRowToGraph() will be able to do
> > > judgement
> > > > if
> > > > > > any
> > > > > > > table is missing PK, in which case DAS will throw
> > RuntimeException
> > > > and
> > > > > > > will
> > > > > > > not form DataGraph.
> > > > > > >
> > > > > > > Any comments/suggestions? Based on this I will work on patch
> for
> > > > > > > JIRA-1464.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Amita
> > > > > > >
> > > > > > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > > > > > >
> > > > > > > > It is best to throw an exception for PK not being there,
> > > otherwise
> > > > > an
> > > > > > > > empty
> > > > > > > > result set can have two meaning:Empty or something went
> wrong
> > > > > > > >
> > > > > > > > On 7/18/07, Adriano Crestani <ad...@apache.org>
> > wrote:
> > > > > > > > >
> > > > > > > > > Amita,
> > > > > > > > >
> > > > > > > > > There is now way for DAS to keep
> > > > > > the relationship data consistence
> > > > > > > > if
> > > > > > > > > both, pk and fk, are not completely defined. Without them
> > DAS
> > > > > cannot
> > > > > > > > > predict
> > > > > > > > > the relationship.
> > > > > > > > >
> > > > > > > > > As Brent said, I think it could throw an exception when
> the
> > PK
> > > > is
> > > > > > > > missing,
> > > > > > > > > no matter if there are relationships or not. Because, as
> far
> > > as
> > > > I
> > > > > > > know,
> > > > > > > > a
> > > > > > > > > table that has no complete PK retrieved on the
> ResultSet is
> > > > being
> > > > > > > > omitted
> > > > > > > > > from the graph and I don't think this is a good approach.
> > > > > > > > >
> > > > > > > > > But when only the fk is missing, I think it is ok to omit
> > the
> > > > > > > > relationship
> > > > > > > > > between the data objects on the graph. This way the user
> has
> > > the
> > > > > > > option
> > > > > > > > to
> > > > > > > > > decide if the references(relationships) will be included
> or
> > > not
> > > > on
> > > > > > the
> > > > > > > > > graph.
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > > Adriano Crestani
> > > > > > > > >
> > > > > > > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > > > > > > >
> > > > > > > > > > Amita,
> > > > > > > > > >
> > > > > > > > > > Definitely, the DAS should enforce the requirement that
> > the
> > > PK
> > > > > > > should
> > > > > > > > > > be returned for each table in the results. I would
> > consider
> > > > this
> > > > > a
> > > > > > > > > > case where the DAS should throw an exception.
> > > > > > > > > >
> > > > > > > > > > Brent
> > > > > > > > > >
> > > > > > > > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com>
> > > wrote:
> > > > > > > > > > > Sorry for the leng thy mail....
> > > > > > > > > > >
> > > > > > > > > > > Tried to check the case when the database has
> > parent-child
> > > > > > tables
> > > > > > > > and
> > > > > > > > > > DAS
> > > > > > > > > > > SELECT Command may/may not
> > > > > > > > > > > contain the PKs of the tables. And found some quite
> > > > confusing
> > > > > > > > > > cases/results,
> > > > > > > > > > > which are effectively giving
> > > > > > > > > > > user a wrong impression of the data in tables.
> > > > > > > > > > >
> > > > > > > > > > > Looks like there are places where we are allowing
> > partial
> > > > > > results,
> > > > > > > > > wrong
> > > > > > > > > > > association in parent and child rows.
> > > > > > > > > > > As RDB DAS logic revolves around PKs, can we state
> > clearly
> > > > > that
> > > > > > > > > > > "When Query SELECT does not include PK for a table,
> the
> > > data
> > > > > > graph
> > > > > > > > > will
> > > > > > > > > > be
> > > > > > > > > > > empty for that table"
> > > > > > > > > > > ? i.e. in the below analysis, instead of giving
> > > > wrong/partial
> > > > > > > > result,
> > > > > > > > > at
> > > > > > > > > > > least consistently give no result?
> > > > > > > > > > > And make necessary code corrections to adhere to this
> > > > > statement?
> > > > > > > > > > >
> > > > > > > > > > > Or any alternative approaches?
> > > > > > > > > > >
> > > > > > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > Say, take below data -
> > > > > > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE,
> > SINGERID)
> > > > > > > > > > > Data:
> > > > > > > > > > > SINGER
> > > > > > > > > > > ID NAME
> > > > > > > > > > > --------------------
> > > > > > > > > > > 1 Jonh
> > > > > > > > > > > 2 Jane
> > > > > > > > > > >
> > > > > > > > > > > SONG
> > > > > > > > > > > ID TITLE SINGERID
> > > > > > > > > > > -------------------------------------
> > > > > > > > > > > 10 ABCD 1
> > > > > > > > > > > 20 Lamb 1
> > > > > > > > > > > 30 La ra ra 2
> > > > > > > > > > >
> > > > > > > > > > > There are total 8 cases that I can see. viz.
> > > > > > > > > > >
> > > > > > > > > > > No relationship in config
> > > > > > > > > > > --------------------------------------------------
> > > > > > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > > > > > >
> > > > > > > >
> > > > >
> > ----------------------------------------------------------------------
> > > > > > > > > > > [1]
> > present present correct
> > > > > > > > > > > [2]
> present missing wrong
> > > > > > > > > > > [3]
> missing present wrong
> > > > > > > > > > > [4] missing missing wrong
> > > > > > > > > > >
> > > > > > > > > > > Relationship in config
> > > > > > > > > > > [5] present present
> correct
> > > > > > > > > > > [6] present missing wrong
> > > > > > > > > > > [7] missing present wrong
> > > > > > > > > > > [8] missing missing wrong
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > When relationship is not defined in DAS Config
> > > > > > > > > > > DAS Client code:
> > > > > > > > > > > ----------------
> > > > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > > > > getConnection());
> > > > > > > > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > > if(singers != null){
> > > > > > > > > > > System.out.println("Singer
> > size:"+singers.size());
> > > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > }
> > > > > > > > > > >
> > > > > > > > > > > }
> > > > > > > > > > >
> > > > > > > > > > > List songs = root.getList("SONG");//as there is no
> > > > > relationship
> > > > > > > > > > > (explicit/implicit)
> > > > > > > > > > >
> > > > > > > > > > > if(songs != null){
> > > > > > > > > > > System.out.println("Songs size "+songs
> .size());
> > > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > > }
> > > > > > > > > > > }
> > > > > > > > > > >
> > > > > > > > > > > }
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > Result:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> > SONG.TITLEFROM
> > > > > > > SINGER,
> > > > > > > > > SONG
> > > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > > Singer size:2
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > Songs size 3
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > >
> > > > > > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM
> > SINGER,
> > > > > SONG
> > > > > > > > WHERE
> > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > Singer size:2
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > Songs size 1
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > >
> > > > > > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM
> SINGER,
> > > > SONG
> > > > > > > WHERE
> > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > Singer size:1
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > Songs size 3
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > >
> > > > > > > > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > WHERE
> > > > > > > SINGER.ID=
> > > > > > > > > > > SONG.SINGERID
> > > > > > > > > > > Singer size:1
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > Songs size 1
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > When relationship one-to-many (sing) is defined in DAS
> > > > Config
> > > > > > > > > > > DAS Client code:
> > > > > > > > > > > ----------------
> > > > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > > > > getConnection());
> > > > > > > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > > if(singers != null){
> > > > > > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > >
> > > > > > > > > > > List songs =
> > > > > > ((DataObject)singers.get(i)).getList("sing");
> > > > > > > > > //use
> > > > > > > > > > > relationship
> > > > > > > > > > > if(songs != null){
> > > > > > > > > > > System.out.println("Songs size "+songs
> > > .size()+"
> > > > > for
> > > > > > > > > singer
> > > > > > > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > > }
> > > > > > > > > > > }
> > > > > > > > > > >
> > > > > > > > > > > }
> > > > > > > > > > > }
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > Result:
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> > SONG.TITLEFROM
> > > > > > > SINGER,
> > > > > > > > > SONG
> > > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > > Singer size:2
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > Songs size 2 for singer :John
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > >
> > > > > > > > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM
> > SINGER,
> > > > > SONG
> > > > > > > > WHERE
> > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > Singer size:2
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > Songs size 0 for singer :John
> > > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > >
> > > > > > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM
> SINGER,
> > > > SONG
> > > > > > > WHERE
> > > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > > Singer size:1
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > Songs size 3 for singer :John
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > > >
> > > > > > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > WHERE
> > > > > > > SINGER.ID=
> > > > > > > > > > > SONG.SINGERID
> > > > > > > > > > > Singer size:1
> > > > > > > > > > > SINGER NAME:John
> > > > > > > > > > > Songs size 1 for singer :John
> > > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > >
> > > > > > > > > > > Regards,
> > > > > > > > > > >
> > > > > > > > > > > Amita
> > > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > >
> > > >
> ---------------------------------------------------------------------
> > > > > > > > > > To unsubscribe, e-mail:
> > > tuscany-dev-unsubscribe@ws.apache.org
> > > > > > > > > > For additional commands, e-mail:
> > > > tuscany-dev-help@ws.apache.org
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Amita Vadhavkar <am...@gmail.com>.
In RDBMS and in DAS OUTER JOINs are used quite frequently. By definition
it means, all rows from parent and matching rows from child. So, there is a
chance for having complete null rows from child and that is the purpose of
OUTER JOIN.
So there are different situations
1) complete child row is null (outer join case)- we should allow this, so
that outer joins work :)
2) PK(single/compound) in child row is null, but some other columns have
data - we should flag this case and throw exception
3) PK(single/compound) in parent row is null, but some other columns have
data - we should flag this case and throw exception
4) complete parent row is null - allow :) , as anyways relationship will not
be formed
If we do not allow 1) 20 existing cases which use OUTER JOIN will fail,
otherwise
all existing cases succeed.
So, instead of giving exception for Null data in PK columns, we can avoid
those DOs in final Graph.
But, absence of PK column (Type) itself from Result Set is another case,
where there is no way to form the correct graph irrespective of values in
the columns.
Due to the above reasons, I am not doing the change in logic over Jul31
patch, but
I am still uploading a new patch to fix 2 minor issues
1) name of the new xml file referred in test case was
companyNoIDMappingWithConverters
changing it to
companynoidMappingWithConverters
2)if check in ResultSetProcessor.addRowToGraph() is made more complete to
ensure
no corner cases miss
-----------------------------------------------------------------------------------------------------------------------------------
For the other question in JIRA-1464, please see the below explaination:-
Question:
As ID column being considered primary key is a Convention Over Configuration
issue, I think the user shouldn't need to declare it anywhere, cause DAS
should recognize it anyway. What do you think?
Ans:
This will be true for Dynamic DO case, typically a query will be executed
with ID column. COC will determine to treat it as PK. It will be used when
registering new Type and Properties (SDO) in SDO context. And so when
populating data in DOs, ID property will be found.
But the change is done in company.xsd to take care of static DO scenario.
Here, companyMappingWithConverters.xml refers to static model
company.xsdand the generation
of equiv java classes is before runtime. So, if ID is missing in company.xsd,
ID will
not be created in CompanyType...generated classes. After that in runtime,
DAS will not be
creating new Types and Properties for company as these are already in SDO
context.
Thus when populating DO with values from query, ID propery will not be found
and exception will be thrown. Checked the same and get below exception.
Example:-
testSimpleStatic(org.apache.tuscany.das.rdb.test.CompanyTests) Time
elapsed: 0.
18 sec <<< ERROR!
java.lang.RuntimeException: Type CompanyType does not contain a property
named I
Regards,
Amita
On 8/2/07, Adriano Crestani <ad...@apache.org> wrote:
>
> Yes, I think it should fail, once DAS shouldn't omit a data from the
> user.
> Cause, if the pk has null pk, it means it doesn't have an id and cannot be
> guaranteed its uniqueness. I think maybe DAS should not throw the
> exception,
> but needs to warn the user when any row is omitted, however I don't think
> it
> is a good approach at all. My suggestion is that it should fail whenever
> is
> found a null pk.
>
> Regards,
> Adriano Crestani
>
> On 8/2/07, Amita Vadhavkar <am...@gmail.com> wrote:
> >
> > There is a bit of confusion around the
> RecursiveTests.testReadEngineParts
> > ()
> > , in the context of this fix.
> >
> > Below is the data for tables, queries etc.
> >
> > sql return:-
> > *1 Engine 1 - 2 Block 1 1 -
> > - - -
> > *1 Engine 1 - 3 Cam Soft 2 1 - -
> > - -
> > 1 Engine 1 - 4 Piston 8 1 5
> Piston
> > Ring 2 4
> >
> > table data:-
> > id name qty parent id
> > 1 Engine 1 -
> > 2 Block 1 1
> > 3 Cam Soft 2 1
> > 4 Piston 8 1
> > 5 Piston Ring 2 4
> >
> > query:-
> > SELECT
> > P1.ID,
> > P1.NAME,
> > P1.QUANTITY,
> > P1.PARENT_ID,
> > P2.ID,
> > P2.NAME,
> > P2.QUANTITY,
> > P2.PARENT_ID,
> > P3.ID,
> > P3.NAME,
> > P3.QUANTITY,
> > P3.PARENT_ID
> > FROM
> > APP.PART AS P1 LEFT OUTER JOIN APP.PART AS P2
> > ON P1.ID = P2.PARENT_ID
> > LEFT OUTER JOIN APP.PART AS P3
> > ON P2.ID = P3.PARENT_ID
> > WHERE
> > P1.ID = 1
> >
> > See the recursiveTests. here the recursion occurs 3 times on the same
> > (part)
> > table and total 5 DOs should be formed in mem. (pre-existing case). Now
> > see
> > ResultSetProcessor.addRowToGraph(). if we take null data in pk as
> > exception,
> > the rows from
> > sql return above marked with *, will cause the whole query to fail and
> so
> > the recursiveTests
> > will fail.
> >
> > But if we do some adjustments to allow this case to succeed, there can
> be
> > other situations
> > where not throwing exception for null data in PK for any row can cause
> > problem (incomplete/wrong results). So, is it better to make
> > RecursiveTests
> > fail? Suggestions?
> >
> > Regards,
> > Amita
> >
> > On 7/28/07, Adriano Crestani <ad...@apache.org> wrote:
> > >
> > > It seems ok Amita ; )
> > >
> > > Adriano Crestani
> > >
> > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > >
> > > > Hi Adriano,
> > > > Yes, so in summary , trying to do following -
> > > >
> > > > 1) select missing complete or partial PK for any of the tables
> > involved-
> > > > exception
> > > > 2) if any table in select has no PK in config and no ID column in
> > > > config/select - exception
> > > > 3) if any table in select has no PK in config and has ID column in
> > > config
> > > > -
> > > > exception
> > > > 4) if any table in select has no PK in config but has ID column in
> > > SELECT
> > > > -
> > > > success
> > > >
> > > > Regards,
> > > > Amita
> > > >
> > > > On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
> > > > >
> > > > > I had the same problem on DAS C++, now it's throwing an exception
> > when
> > > > it
> > > > > finds a row that does not contain all the pk columns.
> > > > >
> > > > > I'm not used to the DAS Java, but I will explain how I did it on
> DAS
> > > > C++,
> > > > > maybe this can help you ; )
> > > > >
> > > > > It reads the ResultSet metadata to find the pk columns. If the PK
> is
> > > > > defined
> > > > > on the config, so it look for the columns defined as pk on the
> > config.
> > > > In
> > > > > case it does not find the pk column(or columns if it is a compound
> > > pk),
> > > > it
> > > > > looks for the ID columns according to DAS Convention Over
> > > Configuration
> > > > > rules. Otherwise it throws the exception.
> > > > >
> > > > > Does it help? : )
> > > > >
> > > > > Regards,
> > > > > Adriano Crestani
> > > > >
> > > > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > >
> > > > > > Further on this,
> > > > > > Need to consider single and compound PKs case.
> > > > > > When select does not include complete PK (all PK columns from
> > > compound
> > > > > PK)
> > > > > > ,
> > > > > > DAS needs to throw exception.
> > > > > >
> > > > > > As a fix proposing below changes:-
> > > > > >
> > > > > > A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys ,
> > > fill
> > > > it
> > > > > > during constuctor
> > > > > > and provide get method - getAllPKsForTable(tableName).
> > > > > >
> > > > > > B> In ResultSetRow - add method
> > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > > > > which will take each table and check if all PKs are there in
> > result
> > > > set.
> > > > > > If
> > > > > > not it will mark that TableData with hasValidPKs=FALSE.
> > > > > >
> > > > > > C> There is already another check in TableData.addData(), which
> > > marks
> > > > > this
> > > > > > flag FALSE, if any PK in result set has NULL data.
> > > > > >
> > > > > > B> and C> together will provide complete check
> > > > > >
> > > > > > D>In ResultSetRow, call,
> > > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> > > > > processRow()
> > > > > > and processRecursiveRow(). With this, all TableData will be set
> > with
> > > > > > proper
> > > > > > hasValidPK, during ResultSetProcessor.processResultSet() and
> > > > > consequently,
> > > > > > in ResultSetProcessor.addRowToGraph() will be able to do
> > judgement
> > > if
> > > > > any
> > > > > > table is missing PK, in which case DAS will throw
> RuntimeException
> > > and
> > > > > > will
> > > > > > not form DataGraph.
> > > > > >
> > > > > > Any comments/suggestions? Based on this I will work on patch for
> > > > > > JIRA-1464.
> > > > > >
> > > > > > Regards,
> > > > > > Amita
> > > > > >
> > > > > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > > > > >
> > > > > > > It is best to throw an exception for PK not being there,
> > otherwise
> > > > an
> > > > > > > empty
> > > > > > > result set can have two meaning:Empty or something went wrong
> > > > > > >
> > > > > > > On 7/18/07, Adriano Crestani <ad...@apache.org>
> wrote:
> > > > > > > >
> > > > > > > > Amita,
> > > > > > > >
> > > > > > > > There is now way for DAS to keep
> > > > > the relationship data consistence
> > > > > > > if
> > > > > > > > both, pk and fk, are not completely defined. Without them
> DAS
> > > > cannot
> > > > > > > > predict
> > > > > > > > the relationship.
> > > > > > > >
> > > > > > > > As Brent said, I think it could throw an exception when the
> PK
> > > is
> > > > > > > missing,
> > > > > > > > no matter if there are relationships or not. Because, as far
> > as
> > > I
> > > > > > know,
> > > > > > > a
> > > > > > > > table that has no complete PK retrieved on the ResultSet is
> > > being
> > > > > > > omitted
> > > > > > > > from the graph and I don't think this is a good approach.
> > > > > > > >
> > > > > > > > But when only the fk is missing, I think it is ok to omit
> the
> > > > > > > relationship
> > > > > > > > between the data objects on the graph. This way the user has
> > the
> > > > > > option
> > > > > > > to
> > > > > > > > decide if the references(relationships) will be included or
> > not
> > > on
> > > > > the
> > > > > > > > graph.
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > > Adriano Crestani
> > > > > > > >
> > > > > > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > > > > > >
> > > > > > > > > Amita,
> > > > > > > > >
> > > > > > > > > Definitely, the DAS should enforce the requirement that
> the
> > PK
> > > > > > should
> > > > > > > > > be returned for each table in the results. I would
> consider
> > > this
> > > > a
> > > > > > > > > case where the DAS should throw an exception.
> > > > > > > > >
> > > > > > > > > Brent
> > > > > > > > >
> > > > > > > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com>
> > wrote:
> > > > > > > > > > Sorry for the leng thy mail....
> > > > > > > > > >
> > > > > > > > > > Tried to check the case when the database has
> parent-child
> > > > > tables
> > > > > > > and
> > > > > > > > > DAS
> > > > > > > > > > SELECT Command may/may not
> > > > > > > > > > contain the PKs of the tables. And found some quite
> > > confusing
> > > > > > > > > cases/results,
> > > > > > > > > > which are effectively giving
> > > > > > > > > > user a wrong impression of the data in tables.
> > > > > > > > > >
> > > > > > > > > > Looks like there are places where we are allowing
> partial
> > > > > results,
> > > > > > > > wrong
> > > > > > > > > > association in parent and child rows.
> > > > > > > > > > As RDB DAS logic revolves around PKs, can we state
> clearly
> > > > that
> > > > > > > > > > "When Query SELECT does not include PK for a table, the
> > data
> > > > > graph
> > > > > > > > will
> > > > > > > > > be
> > > > > > > > > > empty for that table"
> > > > > > > > > > ? i.e. in the below analysis, instead of giving
> > > wrong/partial
> > > > > > > result,
> > > > > > > > at
> > > > > > > > > > least consistently give no result?
> > > > > > > > > > And make necessary code corrections to adhere to this
> > > > statement?
> > > > > > > > > >
> > > > > > > > > > Or any alternative approaches?
> > > > > > > > > >
> > > > > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > Say, take below data -
> > > > > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE,
> SINGERID)
> > > > > > > > > > Data:
> > > > > > > > > > SINGER
> > > > > > > > > > ID NAME
> > > > > > > > > > --------------------
> > > > > > > > > > 1 Jonh
> > > > > > > > > > 2 Jane
> > > > > > > > > >
> > > > > > > > > > SONG
> > > > > > > > > > ID TITLE SINGERID
> > > > > > > > > > -------------------------------------
> > > > > > > > > > 10 ABCD 1
> > > > > > > > > > 20 Lamb 1
> > > > > > > > > > 30 La ra ra 2
> > > > > > > > > >
> > > > > > > > > > There are total 8 cases that I can see. viz.
> > > > > > > > > >
> > > > > > > > > > No relationship in config
> > > > > > > > > > --------------------------------------------------
> > > > > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > > > > >
> > > > > > >
> > > >
> ----------------------------------------------------------------------
> > > > > > > > > > [1]
> present present correct
> > > > > > > > > > [2] present missing wrong
> > > > > > > > > > [3] missing present wrong
> > > > > > > > > > [4] missing missing wrong
> > > > > > > > > >
> > > > > > > > > > Relationship in config
> > > > > > > > > > [5] present present correct
> > > > > > > > > > [6] present missing wrong
> > > > > > > > > > [7] missing present wrong
> > > > > > > > > > [8] missing missing wrong
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > When relationship is not defined in DAS Config
> > > > > > > > > > DAS Client code:
> > > > > > > > > > ----------------
> > > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > > > getConnection());
> > > > > > > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > if(singers != null){
> > > > > > > > > > System.out.println("Singer
> size:"+singers.size());
> > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > > > List songs = root.getList("SONG");//as there is no
> > > > relationship
> > > > > > > > > > (explicit/implicit)
> > > > > > > > > >
> > > > > > > > > > if(songs != null){
> > > > > > > > > > System.out.println("Songs size "+songs .size());
> > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > }
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > Result:
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> SONG.TITLEFROM
> > > > > > SINGER,
> > > > > > > > SONG
> > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > Singer size:2
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > Songs size 3
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > >
> > > > > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM
> SINGER,
> > > > SONG
> > > > > > > WHERE
> > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > Singer size:2
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > Songs size 1
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > >
> > > > > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> > > SONG
> > > > > > WHERE
> > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > Singer size:1
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > Songs size 3
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > >
> > > > > > > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> WHERE
> > > > > > SINGER.ID=
> > > > > > > > > > SONG.SINGERID
> > > > > > > > > > Singer size:1
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > Songs size 1
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > When relationship one-to-many (sing) is defined in DAS
> > > Config
> > > > > > > > > > DAS Client code:
> > > > > > > > > > ----------------
> > > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > > > getConnection());
> > > > > > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > > if(singers != null){
> > > > > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > >
> > > > > > > > > > List songs =
> > > > > ((DataObject)singers.get(i)).getList("sing");
> > > > > > > > //use
> > > > > > > > > > relationship
> > > > > > > > > > if(songs != null){
> > > > > > > > > > System.out.println("Songs size "+songs
> > .size()+"
> > > > for
> > > > > > > > singer
> > > > > > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > > }
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > > > }
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > Result:
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID,
> SONG.TITLEFROM
> > > > > > SINGER,
> > > > > > > > SONG
> > > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > > Singer size:2
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > Songs size 2 for singer :John
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > >
> > > > > > > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM
> SINGER,
> > > > SONG
> > > > > > > WHERE
> > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > Singer size:2
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > Songs size 0 for singer :John
> > > > > > > > > > SINGER NAME:Jane
> > > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > >
> > > > > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> > > SONG
> > > > > > WHERE
> > > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > > Singer size:1
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > Songs size 3 for singer :John
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > > >
> > > > > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> WHERE
> > > > > > SINGER.ID=
> > > > > > > > > > SONG.SINGERID
> > > > > > > > > > Singer size:1
> > > > > > > > > > SINGER NAME:John
> > > > > > > > > > Songs size 1 for singer :John
> > > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > >
> > > > > > > > > > Regards,
> > > > > > > > > >
> > > > > > > > > > Amita
> > > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > >
> > > ---------------------------------------------------------------------
> > > > > > > > > To unsubscribe, e-mail:
> > tuscany-dev-unsubscribe@ws.apache.org
> > > > > > > > > For additional commands, e-mail:
> > > tuscany-dev-help@ws.apache.org
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Adriano Crestani <ad...@apache.org>.
Yes, I think it should fail, once DAS shouldn't omit a data from the user.
Cause, if the pk has null pk, it means it doesn't have an id and cannot be
guaranteed its uniqueness. I think maybe DAS should not throw the exception,
but needs to warn the user when any row is omitted, however I don't think it
is a good approach at all. My suggestion is that it should fail whenever is
found a null pk.
Regards,
Adriano Crestani
On 8/2/07, Amita Vadhavkar <am...@gmail.com> wrote:
>
> There is a bit of confusion around the RecursiveTests.testReadEngineParts
> ()
> , in the context of this fix.
>
> Below is the data for tables, queries etc.
>
> sql return:-
> *1 Engine 1 - 2 Block 1 1 -
> - - -
> *1 Engine 1 - 3 Cam Soft 2 1 - -
> - -
> 1 Engine 1 - 4 Piston 8 1 5 Piston
> Ring 2 4
>
> table data:-
> id name qty parent id
> 1 Engine 1 -
> 2 Block 1 1
> 3 Cam Soft 2 1
> 4 Piston 8 1
> 5 Piston Ring 2 4
>
> query:-
> SELECT
> P1.ID,
> P1.NAME,
> P1.QUANTITY,
> P1.PARENT_ID,
> P2.ID,
> P2.NAME,
> P2.QUANTITY,
> P2.PARENT_ID,
> P3.ID,
> P3.NAME,
> P3.QUANTITY,
> P3.PARENT_ID
> FROM
> APP.PART AS P1 LEFT OUTER JOIN APP.PART AS P2
> ON P1.ID = P2.PARENT_ID
> LEFT OUTER JOIN APP.PART AS P3
> ON P2.ID = P3.PARENT_ID
> WHERE
> P1.ID = 1
>
> See the recursiveTests. here the recursion occurs 3 times on the same
> (part)
> table and total 5 DOs should be formed in mem. (pre-existing case). Now
> see
> ResultSetProcessor.addRowToGraph(). if we take null data in pk as
> exception,
> the rows from
> sql return above marked with *, will cause the whole query to fail and so
> the recursiveTests
> will fail.
>
> But if we do some adjustments to allow this case to succeed, there can be
> other situations
> where not throwing exception for null data in PK for any row can cause
> problem (incomplete/wrong results). So, is it better to make
> RecursiveTests
> fail? Suggestions?
>
> Regards,
> Amita
>
> On 7/28/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > It seems ok Amita ; )
> >
> > Adriano Crestani
> >
> > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > >
> > > Hi Adriano,
> > > Yes, so in summary , trying to do following -
> > >
> > > 1) select missing complete or partial PK for any of the tables
> involved-
> > > exception
> > > 2) if any table in select has no PK in config and no ID column in
> > > config/select - exception
> > > 3) if any table in select has no PK in config and has ID column in
> > config
> > > -
> > > exception
> > > 4) if any table in select has no PK in config but has ID column in
> > SELECT
> > > -
> > > success
> > >
> > > Regards,
> > > Amita
> > >
> > > On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
> > > >
> > > > I had the same problem on DAS C++, now it's throwing an exception
> when
> > > it
> > > > finds a row that does not contain all the pk columns.
> > > >
> > > > I'm not used to the DAS Java, but I will explain how I did it on DAS
> > > C++,
> > > > maybe this can help you ; )
> > > >
> > > > It reads the ResultSet metadata to find the pk columns. If the PK is
> > > > defined
> > > > on the config, so it look for the columns defined as pk on the
> config.
> > > In
> > > > case it does not find the pk column(or columns if it is a compound
> > pk),
> > > it
> > > > looks for the ID columns according to DAS Convention Over
> > Configuration
> > > > rules. Otherwise it throws the exception.
> > > >
> > > > Does it help? : )
> > > >
> > > > Regards,
> > > > Adriano Crestani
> > > >
> > > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > >
> > > > > Further on this,
> > > > > Need to consider single and compound PKs case.
> > > > > When select does not include complete PK (all PK columns from
> > compound
> > > > PK)
> > > > > ,
> > > > > DAS needs to throw exception.
> > > > >
> > > > > As a fix proposing below changes:-
> > > > >
> > > > > A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys ,
> > fill
> > > it
> > > > > during constuctor
> > > > > and provide get method - getAllPKsForTable(tableName).
> > > > >
> > > > > B> In ResultSetRow - add method
> > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > > > which will take each table and check if all PKs are there in
> result
> > > set.
> > > > > If
> > > > > not it will mark that TableData with hasValidPKs=FALSE.
> > > > >
> > > > > C> There is already another check in TableData.addData(), which
> > marks
> > > > this
> > > > > flag FALSE, if any PK in result set has NULL data.
> > > > >
> > > > > B> and C> together will provide complete check
> > > > >
> > > > > D>In ResultSetRow, call,
> > > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> > > > processRow()
> > > > > and processRecursiveRow(). With this, all TableData will be set
> with
> > > > > proper
> > > > > hasValidPK, during ResultSetProcessor.processResultSet() and
> > > > consequently,
> > > > > in ResultSetProcessor.addRowToGraph() will be able to do
> judgement
> > if
> > > > any
> > > > > table is missing PK, in which case DAS will throw RuntimeException
> > and
> > > > > will
> > > > > not form DataGraph.
> > > > >
> > > > > Any comments/suggestions? Based on this I will work on patch for
> > > > > JIRA-1464.
> > > > >
> > > > > Regards,
> > > > > Amita
> > > > >
> > > > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > > > >
> > > > > > It is best to throw an exception for PK not being there,
> otherwise
> > > an
> > > > > > empty
> > > > > > result set can have two meaning:Empty or something went wrong
> > > > > >
> > > > > > On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
> > > > > > >
> > > > > > > Amita,
> > > > > > >
> > > > > > > There is now way for DAS to keep
> > > > the relationship data consistence
> > > > > > if
> > > > > > > both, pk and fk, are not completely defined. Without them DAS
> > > cannot
> > > > > > > predict
> > > > > > > the relationship.
> > > > > > >
> > > > > > > As Brent said, I think it could throw an exception when the PK
> > is
> > > > > > missing,
> > > > > > > no matter if there are relationships or not. Because, as far
> as
> > I
> > > > > know,
> > > > > > a
> > > > > > > table that has no complete PK retrieved on the ResultSet is
> > being
> > > > > > omitted
> > > > > > > from the graph and I don't think this is a good approach.
> > > > > > >
> > > > > > > But when only the fk is missing, I think it is ok to omit the
> > > > > > relationship
> > > > > > > between the data objects on the graph. This way the user has
> the
> > > > > option
> > > > > > to
> > > > > > > decide if the references(relationships) will be included or
> not
> > on
> > > > the
> > > > > > > graph.
> > > > > > >
> > > > > > > Regards,
> > > > > > > Adriano Crestani
> > > > > > >
> > > > > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > > > > >
> > > > > > > > Amita,
> > > > > > > >
> > > > > > > > Definitely, the DAS should enforce the requirement that the
> PK
> > > > > should
> > > > > > > > be returned for each table in the results. I would consider
> > this
> > > a
> > > > > > > > case where the DAS should throw an exception.
> > > > > > > >
> > > > > > > > Brent
> > > > > > > >
> > > > > > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com>
> wrote:
> > > > > > > > > Sorry for the leng thy mail....
> > > > > > > > >
> > > > > > > > > Tried to check the case when the database has parent-child
> > > > tables
> > > > > > and
> > > > > > > > DAS
> > > > > > > > > SELECT Command may/may not
> > > > > > > > > contain the PKs of the tables. And found some quite
> > confusing
> > > > > > > > cases/results,
> > > > > > > > > which are effectively giving
> > > > > > > > > user a wrong impression of the data in tables.
> > > > > > > > >
> > > > > > > > > Looks like there are places where we are allowing partial
> > > > results,
> > > > > > > wrong
> > > > > > > > > association in parent and child rows.
> > > > > > > > > As RDB DAS logic revolves around PKs, can we state clearly
> > > that
> > > > > > > > > "When Query SELECT does not include PK for a table, the
> data
> > > > graph
> > > > > > > will
> > > > > > > > be
> > > > > > > > > empty for that table"
> > > > > > > > > ? i.e. in the below analysis, instead of giving
> > wrong/partial
> > > > > > result,
> > > > > > > at
> > > > > > > > > least consistently give no result?
> > > > > > > > > And make necessary code corrections to adhere to this
> > > statement?
> > > > > > > > >
> > > > > > > > > Or any alternative approaches?
> > > > > > > > >
> > > > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > Say, take below data -
> > > > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > > > > > > > Data:
> > > > > > > > > SINGER
> > > > > > > > > ID NAME
> > > > > > > > > --------------------
> > > > > > > > > 1 Jonh
> > > > > > > > > 2 Jane
> > > > > > > > >
> > > > > > > > > SONG
> > > > > > > > > ID TITLE SINGERID
> > > > > > > > > -------------------------------------
> > > > > > > > > 10 ABCD 1
> > > > > > > > > 20 Lamb 1
> > > > > > > > > 30 La ra ra 2
> > > > > > > > >
> > > > > > > > > There are total 8 cases that I can see. viz.
> > > > > > > > >
> > > > > > > > > No relationship in config
> > > > > > > > > --------------------------------------------------
> > > > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > > > >
> > > > > >
> > > ----------------------------------------------------------------------
> > > > > > > > > [1] present present correct
> > > > > > > > > [2] present missing wrong
> > > > > > > > > [3] missing present wrong
> > > > > > > > > [4] missing missing wrong
> > > > > > > > >
> > > > > > > > > Relationship in config
> > > > > > > > > [5] present present correct
> > > > > > > > > [6] present missing wrong
> > > > > > > > > [7] missing present wrong
> > > > > > > > > [8] missing missing wrong
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > When relationship is not defined in DAS Config
> > > > > > > > > DAS Client code:
> > > > > > > > > ----------------
> > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > > getConnection());
> > > > > > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > if(singers != null){
> > > > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > }
> > > > > > > > >
> > > > > > > > > }
> > > > > > > > >
> > > > > > > > > List songs = root.getList("SONG");//as there is no
> > > relationship
> > > > > > > > > (explicit/implicit)
> > > > > > > > >
> > > > > > > > > if(songs != null){
> > > > > > > > > System.out.println("Songs size "+songs .size());
> > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > }
> > > > > > > > > }
> > > > > > > > >
> > > > > > > > > }
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > Result:
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLEFROM
> > > > > SINGER,
> > > > > > > SONG
> > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > Singer size:2
> > > > > > > > > SINGER NAME:John
> > > > > > > > > SINGER NAME:Jane
> > > > > > > > > Songs size 3
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > >
> > > > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER,
> > > SONG
> > > > > > WHERE
> > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > Singer size:2
> > > > > > > > > SINGER NAME:John
> > > > > > > > > SINGER NAME:Jane
> > > > > > > > > Songs size 1
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > >
> > > > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> > SONG
> > > > > WHERE
> > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > Singer size:1
> > > > > > > > > SINGER NAME:John
> > > > > > > > > Songs size 3
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > >
> > > > > > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > > > SINGER.ID=
> > > > > > > > > SONG.SINGERID
> > > > > > > > > Singer size:1
> > > > > > > > > SINGER NAME:John
> > > > > > > > > Songs size 1
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > When relationship one-to-many (sing) is defined in DAS
> > Config
> > > > > > > > > DAS Client code:
> > > > > > > > > ----------------
> > > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > > getConnection());
> > > > > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > > if(singers != null){
> > > > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > >
> > > > > > > > > List songs =
> > > > ((DataObject)singers.get(i)).getList("sing");
> > > > > > > //use
> > > > > > > > > relationship
> > > > > > > > > if(songs != null){
> > > > > > > > > System.out.println("Songs size "+songs
> .size()+"
> > > for
> > > > > > > singer
> > > > > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > > }
> > > > > > > > > }
> > > > > > > > >
> > > > > > > > > }
> > > > > > > > > }
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > Result:
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLEFROM
> > > > > SINGER,
> > > > > > > SONG
> > > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > > Singer size:2
> > > > > > > > > SINGER NAME:John
> > > > > > > > > Songs size 2 for singer :John
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > SINGER NAME:Jane
> > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > >
> > > > > > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER,
> > > SONG
> > > > > > WHERE
> > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > Singer size:2
> > > > > > > > > SINGER NAME:John
> > > > > > > > > Songs size 0 for singer :John
> > > > > > > > > SINGER NAME:Jane
> > > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > >
> > > > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> > SONG
> > > > > WHERE
> > > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > > Singer size:1
> > > > > > > > > SINGER NAME:John
> > > > > > > > > Songs size 3 for singer :John
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > > SONG TITLE:Lamb
> > > > > > > > > SONG TITLE:La ra ra
> > > > > > > > >
> > > > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > > > SINGER.ID=
> > > > > > > > > SONG.SINGERID
> > > > > > > > > Singer size:1
> > > > > > > > > SINGER NAME:John
> > > > > > > > > Songs size 1 for singer :John
> > > > > > > > > SONG TITLE:ABCD
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > >
> > > > > > > > > Regards,
> > > > > > > > >
> > > > > > > > > Amita
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > >
> > ---------------------------------------------------------------------
> > > > > > > > To unsubscribe, e-mail:
> tuscany-dev-unsubscribe@ws.apache.org
> > > > > > > > For additional commands, e-mail:
> > tuscany-dev-help@ws.apache.org
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Amita Vadhavkar <am...@gmail.com>.
There is a bit of confusion around the RecursiveTests.testReadEngineParts()
, in the context of this fix.
Below is the data for tables, queries etc.
sql return:-
*1 Engine 1 - 2 Block 1 1 -
- - -
*1 Engine 1 - 3 Cam Soft 2 1 - -
- -
1 Engine 1 - 4 Piston 8 1 5 Piston
Ring 2 4
table data:-
id name qty parent id
1 Engine 1 -
2 Block 1 1
3 Cam Soft 2 1
4 Piston 8 1
5 Piston Ring 2 4
query:-
SELECT
P1.ID,
P1.NAME,
P1.QUANTITY,
P1.PARENT_ID,
P2.ID,
P2.NAME,
P2.QUANTITY,
P2.PARENT_ID,
P3.ID,
P3.NAME,
P3.QUANTITY,
P3.PARENT_ID
FROM
APP.PART AS P1 LEFT OUTER JOIN APP.PART AS P2
ON P1.ID = P2.PARENT_ID
LEFT OUTER JOIN APP.PART AS P3
ON P2.ID = P3.PARENT_ID
WHERE
P1.ID = 1
See the recursiveTests. here the recursion occurs 3 times on the same (part)
table and total 5 DOs should be formed in mem. (pre-existing case). Now see
ResultSetProcessor.addRowToGraph(). if we take null data in pk as exception,
the rows from
sql return above marked with *, will cause the whole query to fail and so
the recursiveTests
will fail.
But if we do some adjustments to allow this case to succeed, there can be
other situations
where not throwing exception for null data in PK for any row can cause
problem (incomplete/wrong results). So, is it better to make RecursiveTests
fail? Suggestions?
Regards,
Amita
On 7/28/07, Adriano Crestani <ad...@apache.org> wrote:
>
> It seems ok Amita ; )
>
> Adriano Crestani
>
> On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> >
> > Hi Adriano,
> > Yes, so in summary , trying to do following -
> >
> > 1) select missing complete or partial PK for any of the tables involved-
> > exception
> > 2) if any table in select has no PK in config and no ID column in
> > config/select - exception
> > 3) if any table in select has no PK in config and has ID column in
> config
> > -
> > exception
> > 4) if any table in select has no PK in config but has ID column in
> SELECT
> > -
> > success
> >
> > Regards,
> > Amita
> >
> > On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
> > >
> > > I had the same problem on DAS C++, now it's throwing an exception when
> > it
> > > finds a row that does not contain all the pk columns.
> > >
> > > I'm not used to the DAS Java, but I will explain how I did it on DAS
> > C++,
> > > maybe this can help you ; )
> > >
> > > It reads the ResultSet metadata to find the pk columns. If the PK is
> > > defined
> > > on the config, so it look for the columns defined as pk on the config.
> > In
> > > case it does not find the pk column(or columns if it is a compound
> pk),
> > it
> > > looks for the ID columns according to DAS Convention Over
> Configuration
> > > rules. Otherwise it throws the exception.
> > >
> > > Does it help? : )
> > >
> > > Regards,
> > > Adriano Crestani
> > >
> > > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > >
> > > > Further on this,
> > > > Need to consider single and compound PKs case.
> > > > When select does not include complete PK (all PK columns from
> compound
> > > PK)
> > > > ,
> > > > DAS needs to throw exception.
> > > >
> > > > As a fix proposing below changes:-
> > > >
> > > > A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys ,
> fill
> > it
> > > > during constuctor
> > > > and provide get method - getAllPKsForTable(tableName).
> > > >
> > > > B> In ResultSetRow - add method
> > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > > which will take each table and check if all PKs are there in result
> > set.
> > > > If
> > > > not it will mark that TableData with hasValidPKs=FALSE.
> > > >
> > > > C> There is already another check in TableData.addData(), which
> marks
> > > this
> > > > flag FALSE, if any PK in result set has NULL data.
> > > >
> > > > B> and C> together will provide complete check
> > > >
> > > > D>In ResultSetRow, call,
> > > > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> > > processRow()
> > > > and processRecursiveRow(). With this, all TableData will be set with
> > > > proper
> > > > hasValidPK, during ResultSetProcessor.processResultSet() and
> > > consequently,
> > > > in ResultSetProcessor.addRowToGraph() will be able to do judgement
> if
> > > any
> > > > table is missing PK, in which case DAS will throw RuntimeException
> and
> > > > will
> > > > not form DataGraph.
> > > >
> > > > Any comments/suggestions? Based on this I will work on patch for
> > > > JIRA-1464.
> > > >
> > > > Regards,
> > > > Amita
> > > >
> > > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > > >
> > > > > It is best to throw an exception for PK not being there, otherwise
> > an
> > > > > empty
> > > > > result set can have two meaning:Empty or something went wrong
> > > > >
> > > > > On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
> > > > > >
> > > > > > Amita,
> > > > > >
> > > > > > There is now way for DAS to keep
> > > the relationship data consistence
> > > > > if
> > > > > > both, pk and fk, are not completely defined. Without them DAS
> > cannot
> > > > > > predict
> > > > > > the relationship.
> > > > > >
> > > > > > As Brent said, I think it could throw an exception when the PK
> is
> > > > > missing,
> > > > > > no matter if there are relationships or not. Because, as far as
> I
> > > > know,
> > > > > a
> > > > > > table that has no complete PK retrieved on the ResultSet is
> being
> > > > > omitted
> > > > > > from the graph and I don't think this is a good approach.
> > > > > >
> > > > > > But when only the fk is missing, I think it is ok to omit the
> > > > > relationship
> > > > > > between the data objects on the graph. This way the user has the
> > > > option
> > > > > to
> > > > > > decide if the references(relationships) will be included or not
> on
> > > the
> > > > > > graph.
> > > > > >
> > > > > > Regards,
> > > > > > Adriano Crestani
> > > > > >
> > > > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > > > >
> > > > > > > Amita,
> > > > > > >
> > > > > > > Definitely, the DAS should enforce the requirement that the PK
> > > > should
> > > > > > > be returned for each table in the results. I would consider
> this
> > a
> > > > > > > case where the DAS should throw an exception.
> > > > > > >
> > > > > > > Brent
> > > > > > >
> > > > > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > > > > Sorry for the leng thy mail....
> > > > > > > >
> > > > > > > > Tried to check the case when the database has parent-child
> > > tables
> > > > > and
> > > > > > > DAS
> > > > > > > > SELECT Command may/may not
> > > > > > > > contain the PKs of the tables. And found some quite
> confusing
> > > > > > > cases/results,
> > > > > > > > which are effectively giving
> > > > > > > > user a wrong impression of the data in tables.
> > > > > > > >
> > > > > > > > Looks like there are places where we are allowing partial
> > > results,
> > > > > > wrong
> > > > > > > > association in parent and child rows.
> > > > > > > > As RDB DAS logic revolves around PKs, can we state clearly
> > that
> > > > > > > > "When Query SELECT does not include PK for a table, the data
> > > graph
> > > > > > will
> > > > > > > be
> > > > > > > > empty for that table"
> > > > > > > > ? i.e. in the below analysis, instead of giving
> wrong/partial
> > > > > result,
> > > > > > at
> > > > > > > > least consistently give no result?
> > > > > > > > And make necessary code corrections to adhere to this
> > statement?
> > > > > > > >
> > > > > > > > Or any alternative approaches?
> > > > > > > >
> > > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > Say, take below data -
> > > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > > > > > > Data:
> > > > > > > > SINGER
> > > > > > > > ID NAME
> > > > > > > > --------------------
> > > > > > > > 1 Jonh
> > > > > > > > 2 Jane
> > > > > > > >
> > > > > > > > SONG
> > > > > > > > ID TITLE SINGERID
> > > > > > > > -------------------------------------
> > > > > > > > 10 ABCD 1
> > > > > > > > 20 Lamb 1
> > > > > > > > 30 La ra ra 2
> > > > > > > >
> > > > > > > > There are total 8 cases that I can see. viz.
> > > > > > > >
> > > > > > > > No relationship in config
> > > > > > > > --------------------------------------------------
> > > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > > >
> > > > >
> > ----------------------------------------------------------------------
> > > > > > > > [1] present present correct
> > > > > > > > [2] present missing wrong
> > > > > > > > [3] missing present wrong
> > > > > > > > [4] missing missing wrong
> > > > > > > >
> > > > > > > > Relationship in config
> > > > > > > > [5] present present correct
> > > > > > > > [6] present missing wrong
> > > > > > > > [7] missing present wrong
> > > > > > > > [8] missing missing wrong
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > When relationship is not defined in DAS Config
> > > > > > > > DAS Client code:
> > > > > > > > ----------------
> > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > getConnection());
> > > > > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > if(singers != null){
> > > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > }
> > > > > > > >
> > > > > > > > }
> > > > > > > >
> > > > > > > > List songs = root.getList("SONG");//as there is no
> > relationship
> > > > > > > > (explicit/implicit)
> > > > > > > >
> > > > > > > > if(songs != null){
> > > > > > > > System.out.println("Songs size "+songs .size());
> > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > }
> > > > > > > > }
> > > > > > > >
> > > > > > > > }
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > Result:
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > > > SINGER,
> > > > > > SONG
> > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > Singer size:2
> > > > > > > > SINGER NAME:John
> > > > > > > > SINGER NAME:Jane
> > > > > > > > Songs size 3
> > > > > > > > SONG TITLE:ABCD
> > > > > > > > SONG TITLE:Lamb
> > > > > > > > SONG TITLE:La ra ra
> > > > > > > >
> > > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER,
> > SONG
> > > > > WHERE
> > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > Singer size:2
> > > > > > > > SINGER NAME:John
> > > > > > > > SINGER NAME:Jane
> > > > > > > > Songs size 1
> > > > > > > > SONG TITLE:ABCD
> > > > > > > >
> > > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> SONG
> > > > WHERE
> > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > Singer size:1
> > > > > > > > SINGER NAME:John
> > > > > > > > Songs size 3
> > > > > > > > SONG TITLE:ABCD
> > > > > > > > SONG TITLE:Lamb
> > > > > > > > SONG TITLE:La ra ra
> > > > > > > >
> > > > > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > > SINGER.ID=
> > > > > > > > SONG.SINGERID
> > > > > > > > Singer size:1
> > > > > > > > SINGER NAME:John
> > > > > > > > Songs size 1
> > > > > > > > SONG TITLE:ABCD
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > When relationship one-to-many (sing) is defined in DAS
> Config
> > > > > > > > DAS Client code:
> > > > > > > > ----------------
> > > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > > getConnection());
> > > > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > > > DataObject root = select.executeQuery();
> > > > > > > > List singers = root.getList("SINGER");
> > > > > > > > if(singers != null){
> > > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > >
> > > > > > > > List songs =
> > > ((DataObject)singers.get(i)).getList("sing");
> > > > > > //use
> > > > > > > > relationship
> > > > > > > > if(songs != null){
> > > > > > > > System.out.println("Songs size "+songs .size()+"
> > for
> > > > > > singer
> > > > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > > }
> > > > > > > > }
> > > > > > > >
> > > > > > > > }
> > > > > > > > }
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > Result:
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > > > SINGER,
> > > > > > SONG
> > > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > > Singer size:2
> > > > > > > > SINGER NAME:John
> > > > > > > > Songs size 2 for singer :John
> > > > > > > > SONG TITLE:ABCD
> > > > > > > > SONG TITLE:Lamb
> > > > > > > > SINGER NAME:Jane
> > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > SONG TITLE:La ra ra
> > > > > > > >
> > > > > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER,
> > SONG
> > > > > WHERE
> > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > Singer size:2
> > > > > > > > SINGER NAME:John
> > > > > > > > Songs size 0 for singer :John
> > > > > > > > SINGER NAME:Jane
> > > > > > > > Songs size 1 for singer :Jane
> > > > > > > > SONG TITLE:ABCD
> > > > > > > >
> > > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> SONG
> > > > WHERE
> > > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > > Singer size:1
> > > > > > > > SINGER NAME:John
> > > > > > > > Songs size 3 for singer :John
> > > > > > > > SONG TITLE:ABCD
> > > > > > > > SONG TITLE:Lamb
> > > > > > > > SONG TITLE:La ra ra
> > > > > > > >
> > > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > > SINGER.ID=
> > > > > > > > SONG.SINGERID
> > > > > > > > Singer size:1
> > > > > > > > SINGER NAME:John
> > > > > > > > Songs size 1 for singer :John
> > > > > > > > SONG TITLE:ABCD
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > >
> > > > > > > > Regards,
> > > > > > > >
> > > > > > > > Amita
> > > > > > > >
> > > > > > >
> > > > > > >
> > > >
> ---------------------------------------------------------------------
> > > > > > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > > > > > For additional commands, e-mail:
> tuscany-dev-help@ws.apache.org
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Adriano Crestani <ad...@apache.org>.
It seems ok Amita ; )
Adriano Crestani
On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
>
> Hi Adriano,
> Yes, so in summary , trying to do following -
>
> 1) select missing complete or partial PK for any of the tables involved-
> exception
> 2) if any table in select has no PK in config and no ID column in
> config/select - exception
> 3) if any table in select has no PK in config and has ID column in config
> -
> exception
> 4) if any table in select has no PK in config but has ID column in SELECT
> -
> success
>
> Regards,
> Amita
>
> On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > I had the same problem on DAS C++, now it's throwing an exception when
> it
> > finds a row that does not contain all the pk columns.
> >
> > I'm not used to the DAS Java, but I will explain how I did it on DAS
> C++,
> > maybe this can help you ; )
> >
> > It reads the ResultSet metadata to find the pk columns. If the PK is
> > defined
> > on the config, so it look for the columns defined as pk on the config.
> In
> > case it does not find the pk column(or columns if it is a compound pk),
> it
> > looks for the ID columns according to DAS Convention Over Configuration
> > rules. Otherwise it throws the exception.
> >
> > Does it help? : )
> >
> > Regards,
> > Adriano Crestani
> >
> > On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > >
> > > Further on this,
> > > Need to consider single and compound PKs case.
> > > When select does not include complete PK (all PK columns from compound
> > PK)
> > > ,
> > > DAS needs to throw exception.
> > >
> > > As a fix proposing below changes:-
> > >
> > > A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys , fill
> it
> > > during constuctor
> > > and provide get method - getAllPKsForTable(tableName).
> > >
> > > B> In ResultSetRow - add method
> > > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > > which will take each table and check if all PKs are there in result
> set.
> > > If
> > > not it will mark that TableData with hasValidPKs=FALSE.
> > >
> > > C> There is already another check in TableData.addData(), which marks
> > this
> > > flag FALSE, if any PK in result set has NULL data.
> > >
> > > B> and C> together will provide complete check
> > >
> > > D>In ResultSetRow, call,
> > > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> > processRow()
> > > and processRecursiveRow(). With this, all TableData will be set with
> > > proper
> > > hasValidPK, during ResultSetProcessor.processResultSet() and
> > consequently,
> > > in ResultSetProcessor.addRowToGraph() will be able to do judgement if
> > any
> > > table is missing PK, in which case DAS will throw RuntimeException and
> > > will
> > > not form DataGraph.
> > >
> > > Any comments/suggestions? Based on this I will work on patch for
> > > JIRA-1464.
> > >
> > > Regards,
> > > Amita
> > >
> > > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > > >
> > > > It is best to throw an exception for PK not being there, otherwise
> an
> > > > empty
> > > > result set can have two meaning:Empty or something went wrong
> > > >
> > > > On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
> > > > >
> > > > > Amita,
> > > > >
> > > > > There is now way for DAS to keep
> > the relationship data consistence
> > > > if
> > > > > both, pk and fk, are not completely defined. Without them DAS
> cannot
> > > > > predict
> > > > > the relationship.
> > > > >
> > > > > As Brent said, I think it could throw an exception when the PK is
> > > > missing,
> > > > > no matter if there are relationships or not. Because, as far as I
> > > know,
> > > > a
> > > > > table that has no complete PK retrieved on the ResultSet is being
> > > > omitted
> > > > > from the graph and I don't think this is a good approach.
> > > > >
> > > > > But when only the fk is missing, I think it is ok to omit the
> > > > relationship
> > > > > between the data objects on the graph. This way the user has the
> > > option
> > > > to
> > > > > decide if the references(relationships) will be included or not on
> > the
> > > > > graph.
> > > > >
> > > > > Regards,
> > > > > Adriano Crestani
> > > > >
> > > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > > >
> > > > > > Amita,
> > > > > >
> > > > > > Definitely, the DAS should enforce the requirement that the PK
> > > should
> > > > > > be returned for each table in the results. I would consider this
> a
> > > > > > case where the DAS should throw an exception.
> > > > > >
> > > > > > Brent
> > > > > >
> > > > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > > > Sorry for the leng thy mail....
> > > > > > >
> > > > > > > Tried to check the case when the database has parent-child
> > tables
> > > > and
> > > > > > DAS
> > > > > > > SELECT Command may/may not
> > > > > > > contain the PKs of the tables. And found some quite confusing
> > > > > > cases/results,
> > > > > > > which are effectively giving
> > > > > > > user a wrong impression of the data in tables.
> > > > > > >
> > > > > > > Looks like there are places where we are allowing partial
> > results,
> > > > > wrong
> > > > > > > association in parent and child rows.
> > > > > > > As RDB DAS logic revolves around PKs, can we state clearly
> that
> > > > > > > "When Query SELECT does not include PK for a table, the data
> > graph
> > > > > will
> > > > > > be
> > > > > > > empty for that table"
> > > > > > > ? i.e. in the below analysis, instead of giving wrong/partial
> > > > result,
> > > > > at
> > > > > > > least consistently give no result?
> > > > > > > And make necessary code corrections to adhere to this
> statement?
> > > > > > >
> > > > > > > Or any alternative approaches?
> > > > > > >
> > > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > Say, take below data -
> > > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > > > > > Data:
> > > > > > > SINGER
> > > > > > > ID NAME
> > > > > > > --------------------
> > > > > > > 1 Jonh
> > > > > > > 2 Jane
> > > > > > >
> > > > > > > SONG
> > > > > > > ID TITLE SINGERID
> > > > > > > -------------------------------------
> > > > > > > 10 ABCD 1
> > > > > > > 20 Lamb 1
> > > > > > > 30 La ra ra 2
> > > > > > >
> > > > > > > There are total 8 cases that I can see. viz.
> > > > > > >
> > > > > > > No relationship in config
> > > > > > > --------------------------------------------------
> > > > > > > parent PK in SEL child PK in SEL Result
> > > > > > >
> > > >
> ----------------------------------------------------------------------
> > > > > > > [1] present present correct
> > > > > > > [2] present missing wrong
> > > > > > > [3] missing present wrong
> > > > > > > [4] missing missing wrong
> > > > > > >
> > > > > > > Relationship in config
> > > > > > > [5] present present correct
> > > > > > > [6] present missing wrong
> > > > > > > [7] missing present wrong
> > > > > > > [8] missing missing wrong
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > When relationship is not defined in DAS Config
> > > > > > > DAS Client code:
> > > > > > > ----------------
> > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > getConnection());
> > > > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > > > DataObject root = select.executeQuery();
> > > > > > > List singers = root.getList("SINGER");
> > > > > > > if(singers != null){
> > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > }
> > > > > > >
> > > > > > > }
> > > > > > >
> > > > > > > List songs = root.getList("SONG");//as there is no
> relationship
> > > > > > > (explicit/implicit)
> > > > > > >
> > > > > > > if(songs != null){
> > > > > > > System.out.println("Songs size "+songs .size());
> > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > }
> > > > > > > }
> > > > > > >
> > > > > > > }
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > Result:
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > > SINGER,
> > > > > SONG
> > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > Singer size:2
> > > > > > > SINGER NAME:John
> > > > > > > SINGER NAME:Jane
> > > > > > > Songs size 3
> > > > > > > SONG TITLE:ABCD
> > > > > > > SONG TITLE:Lamb
> > > > > > > SONG TITLE:La ra ra
> > > > > > >
> > > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER,
> SONG
> > > > WHERE
> > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > Singer size:2
> > > > > > > SINGER NAME:John
> > > > > > > SINGER NAME:Jane
> > > > > > > Songs size 1
> > > > > > > SONG TITLE:ABCD
> > > > > > >
> > > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> > > WHERE
> > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > Singer size:1
> > > > > > > SINGER NAME:John
> > > > > > > Songs size 3
> > > > > > > SONG TITLE:ABCD
> > > > > > > SONG TITLE:Lamb
> > > > > > > SONG TITLE:La ra ra
> > > > > > >
> > > > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > SINGER.ID=
> > > > > > > SONG.SINGERID
> > > > > > > Singer size:1
> > > > > > > SINGER NAME:John
> > > > > > > Songs size 1
> > > > > > > SONG TITLE:ABCD
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > When relationship one-to-many (sing) is defined in DAS Config
> > > > > > > DAS Client code:
> > > > > > > ----------------
> > > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > > getConnection());
> > > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > > DataObject root = select.executeQuery();
> > > > > > > List singers = root.getList("SINGER");
> > > > > > > if(singers != null){
> > > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > > System.out.println("SINGER NAME:"+
> > > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > >
> > > > > > > List songs =
> > ((DataObject)singers.get(i)).getList("sing");
> > > > > //use
> > > > > > > relationship
> > > > > > > if(songs != null){
> > > > > > > System.out.println("Songs size "+songs .size()+"
> for
> > > > > singer
> > > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > > System.out.println("SONG TITLE:"+
> > > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > > }
> > > > > > > }
> > > > > > >
> > > > > > > }
> > > > > > > }
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > Result:
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > > SINGER,
> > > > > SONG
> > > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > > Singer size:2
> > > > > > > SINGER NAME:John
> > > > > > > Songs size 2 for singer :John
> > > > > > > SONG TITLE:ABCD
> > > > > > > SONG TITLE:Lamb
> > > > > > > SINGER NAME:Jane
> > > > > > > Songs size 1 for singer :Jane
> > > > > > > SONG TITLE:La ra ra
> > > > > > >
> > > > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER,
> SONG
> > > > WHERE
> > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > Singer size:2
> > > > > > > SINGER NAME:John
> > > > > > > Songs size 0 for singer :John
> > > > > > > SINGER NAME:Jane
> > > > > > > Songs size 1 for singer :Jane
> > > > > > > SONG TITLE:ABCD
> > > > > > >
> > > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> > > WHERE
> > > > > > > SINGER.ID = SONG.SINGERID
> > > > > > > Singer size:1
> > > > > > > SINGER NAME:John
> > > > > > > Songs size 3 for singer :John
> > > > > > > SONG TITLE:ABCD
> > > > > > > SONG TITLE:Lamb
> > > > > > > SONG TITLE:La ra ra
> > > > > > >
> > > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > SINGER.ID=
> > > > > > > SONG.SINGERID
> > > > > > > Singer size:1
> > > > > > > SINGER NAME:John
> > > > > > > Songs size 1 for singer :John
> > > > > > > SONG TITLE:ABCD
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > >
> > > > > > > Regards,
> > > > > > >
> > > > > > > Amita
> > > > > > >
> > > > > >
> > > > > >
> > > ---------------------------------------------------------------------
> > > > > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > > > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Amita Vadhavkar <am...@gmail.com>.
Hi Adriano,
Yes, so in summary , trying to do following -
1) select missing complete or partial PK for any of the tables involved-
exception
2) if any table in select has no PK in config and no ID column in
config/select - exception
3) if any table in select has no PK in config and has ID column in config -
exception
4) if any table in select has no PK in config but has ID column in SELECT -
success
Regards,
Amita
On 7/27/07, Adriano Crestani <ad...@apache.org> wrote:
>
> I had the same problem on DAS C++, now it's throwing an exception when it
> finds a row that does not contain all the pk columns.
>
> I'm not used to the DAS Java, but I will explain how I did it on DAS C++,
> maybe this can help you ; )
>
> It reads the ResultSet metadata to find the pk columns. If the PK is
> defined
> on the config, so it look for the columns defined as pk on the config. In
> case it does not find the pk column(or columns if it is a compound pk), it
> looks for the ID columns according to DAS Convention Over Configuration
> rules. Otherwise it throws the exception.
>
> Does it help? : )
>
> Regards,
> Adriano Crestani
>
> On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
> >
> > Further on this,
> > Need to consider single and compound PKs case.
> > When select does not include complete PK (all PK columns from compound
> PK)
> > ,
> > DAS needs to throw exception.
> >
> > As a fix proposing below changes:-
> >
> > A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys , fill it
> > during constuctor
> > and provide get method - getAllPKsForTable(tableName).
> >
> > B> In ResultSetRow - add method
> > checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> > which will take each table and check if all PKs are there in result set.
> > If
> > not it will mark that TableData with hasValidPKs=FALSE.
> >
> > C> There is already another check in TableData.addData(), which marks
> this
> > flag FALSE, if any PK in result set has NULL data.
> >
> > B> and C> together will provide complete check
> >
> > D>In ResultSetRow, call,
> > checkResultSetMissesPK(allTableNamesFromQueryResultSet) from
> processRow()
> > and processRecursiveRow(). With this, all TableData will be set with
> > proper
> > hasValidPK, during ResultSetProcessor.processResultSet() and
> consequently,
> > in ResultSetProcessor.addRowToGraph() will be able to do judgement if
> any
> > table is missing PK, in which case DAS will throw RuntimeException and
> > will
> > not form DataGraph.
> >
> > Any comments/suggestions? Based on this I will work on patch for
> > JIRA-1464.
> >
> > Regards,
> > Amita
> >
> > On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> > >
> > > It is best to throw an exception for PK not being there, otherwise an
> > > empty
> > > result set can have two meaning:Empty or something went wrong
> > >
> > > On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
> > > >
> > > > Amita,
> > > >
> > > > There is now way for DAS to keep
> the relationship data consistence
> > > if
> > > > both, pk and fk, are not completely defined. Without them DAS cannot
> > > > predict
> > > > the relationship.
> > > >
> > > > As Brent said, I think it could throw an exception when the PK is
> > > missing,
> > > > no matter if there are relationships or not. Because, as far as I
> > know,
> > > a
> > > > table that has no complete PK retrieved on the ResultSet is being
> > > omitted
> > > > from the graph and I don't think this is a good approach.
> > > >
> > > > But when only the fk is missing, I think it is ok to omit the
> > > relationship
> > > > between the data objects on the graph. This way the user has the
> > option
> > > to
> > > > decide if the references(relationships) will be included or not on
> the
> > > > graph.
> > > >
> > > > Regards,
> > > > Adriano Crestani
> > > >
> > > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > > >
> > > > > Amita,
> > > > >
> > > > > Definitely, the DAS should enforce the requirement that the PK
> > should
> > > > > be returned for each table in the results. I would consider this a
> > > > > case where the DAS should throw an exception.
> > > > >
> > > > > Brent
> > > > >
> > > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > > Sorry for the leng thy mail....
> > > > > >
> > > > > > Tried to check the case when the database has parent-child
> tables
> > > and
> > > > > DAS
> > > > > > SELECT Command may/may not
> > > > > > contain the PKs of the tables. And found some quite confusing
> > > > > cases/results,
> > > > > > which are effectively giving
> > > > > > user a wrong impression of the data in tables.
> > > > > >
> > > > > > Looks like there are places where we are allowing partial
> results,
> > > > wrong
> > > > > > association in parent and child rows.
> > > > > > As RDB DAS logic revolves around PKs, can we state clearly that
> > > > > > "When Query SELECT does not include PK for a table, the data
> graph
> > > > will
> > > > > be
> > > > > > empty for that table"
> > > > > > ? i.e. in the below analysis, instead of giving wrong/partial
> > > result,
> > > > at
> > > > > > least consistently give no result?
> > > > > > And make necessary code corrections to adhere to this statement?
> > > > > >
> > > > > > Or any alternative approaches?
> > > > > >
> > > > > > What DAS C++ is doing for this case? Just curious.
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > Say, take below data -
> > > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > > > > Data:
> > > > > > SINGER
> > > > > > ID NAME
> > > > > > --------------------
> > > > > > 1 Jonh
> > > > > > 2 Jane
> > > > > >
> > > > > > SONG
> > > > > > ID TITLE SINGERID
> > > > > > -------------------------------------
> > > > > > 10 ABCD 1
> > > > > > 20 Lamb 1
> > > > > > 30 La ra ra 2
> > > > > >
> > > > > > There are total 8 cases that I can see. viz.
> > > > > >
> > > > > > No relationship in config
> > > > > > --------------------------------------------------
> > > > > > parent PK in SEL child PK in SEL Result
> > > > > >
> > > ----------------------------------------------------------------------
> > > > > > [1] present present correct
> > > > > > [2] present missing wrong
> > > > > > [3] missing present wrong
> > > > > > [4] missing missing wrong
> > > > > >
> > > > > > Relationship in config
> > > > > > [5] present present correct
> > > > > > [6] present missing wrong
> > > > > > [7] missing present wrong
> > > > > > [8] missing missing wrong
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > When relationship is not defined in DAS Config
> > > > > > DAS Client code:
> > > > > > ----------------
> > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > getConnection());
> > > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > > DataObject root = select.executeQuery();
> > > > > > List singers = root.getList("SINGER");
> > > > > > if(singers != null){
> > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > System.out.println("SINGER NAME:"+
> > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > > }
> > > > > >
> > > > > > }
> > > > > >
> > > > > > List songs = root.getList("SONG");//as there is no relationship
> > > > > > (explicit/implicit)
> > > > > >
> > > > > > if(songs != null){
> > > > > > System.out.println("Songs size "+songs .size());
> > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > System.out.println("SONG TITLE:"+
> > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > }
> > > > > > }
> > > > > >
> > > > > > }
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > Result:
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > SINGER,
> > > > SONG
> > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > Singer size:2
> > > > > > SINGER NAME:John
> > > > > > SINGER NAME:Jane
> > > > > > Songs size 3
> > > > > > SONG TITLE:ABCD
> > > > > > SONG TITLE:Lamb
> > > > > > SONG TITLE:La ra ra
> > > > > >
> > > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > > WHERE
> > > > > > SINGER.ID = SONG.SINGERID
> > > > > > Singer size:2
> > > > > > SINGER NAME:John
> > > > > > SINGER NAME:Jane
> > > > > > Songs size 1
> > > > > > SONG TITLE:ABCD
> > > > > >
> > > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> > WHERE
> > > > > > SINGER.ID = SONG.SINGERID
> > > > > > Singer size:1
> > > > > > SINGER NAME:John
> > > > > > Songs size 3
> > > > > > SONG TITLE:ABCD
> > > > > > SONG TITLE:Lamb
> > > > > > SONG TITLE:La ra ra
> > > > > >
> > > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > SINGER.ID=
> > > > > > SONG.SINGERID
> > > > > > Singer size:1
> > > > > > SINGER NAME:John
> > > > > > Songs size 1
> > > > > > SONG TITLE:ABCD
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > When relationship one-to-many (sing) is defined in DAS Config
> > > > > > DAS Client code:
> > > > > > ----------------
> > > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > > getConnection());
> > > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > > DataObject root = select.executeQuery();
> > > > > > List singers = root.getList("SINGER");
> > > > > > if(singers != null){
> > > > > > System.out.println("Singer size:"+singers.size());
> > > > > > for(int i=0; i<singers.size(); i++){
> > > > > > System.out.println("SINGER NAME:"+
> > > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > >
> > > > > > List songs =
> ((DataObject)singers.get(i)).getList("sing");
> > > > //use
> > > > > > relationship
> > > > > > if(songs != null){
> > > > > > System.out.println("Songs size "+songs .size()+" for
> > > > singer
> > > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > > System.out.println("SONG TITLE:"+
> > > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > > }
> > > > > > }
> > > > > >
> > > > > > }
> > > > > > }
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > Result:
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> > SINGER,
> > > > SONG
> > > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > > Singer size:2
> > > > > > SINGER NAME:John
> > > > > > Songs size 2 for singer :John
> > > > > > SONG TITLE:ABCD
> > > > > > SONG TITLE:Lamb
> > > > > > SINGER NAME:Jane
> > > > > > Songs size 1 for singer :Jane
> > > > > > SONG TITLE:La ra ra
> > > > > >
> > > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > > WHERE
> > > > > > SINGER.ID = SONG.SINGERID
> > > > > > Singer size:2
> > > > > > SINGER NAME:John
> > > > > > Songs size 0 for singer :John
> > > > > > SINGER NAME:Jane
> > > > > > Songs size 1 for singer :Jane
> > > > > > SONG TITLE:ABCD
> > > > > >
> > > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> > WHERE
> > > > > > SINGER.ID = SONG.SINGERID
> > > > > > Singer size:1
> > > > > > SINGER NAME:John
> > > > > > Songs size 3 for singer :John
> > > > > > SONG TITLE:ABCD
> > > > > > SONG TITLE:Lamb
> > > > > > SONG TITLE:La ra ra
> > > > > >
> > > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > SINGER.ID=
> > > > > > SONG.SINGERID
> > > > > > Singer size:1
> > > > > > SINGER NAME:John
> > > > > > Songs size 1 for singer :John
> > > > > > SONG TITLE:ABCD
> > > > > >
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > >
> > > > > > Regards,
> > > > > >
> > > > > > Amita
> > > > > >
> > > > >
> > > > >
> > ---------------------------------------------------------------------
> > > > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > > > >
> > > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Adriano Crestani <ad...@apache.org>.
I had the same problem on DAS C++, now it's throwing an exception when it
finds a row that does not contain all the pk columns.
I'm not used to the DAS Java, but I will explain how I did it on DAS C++,
maybe this can help you ; )
It reads the ResultSet metadata to find the pk columns. If the PK is defined
on the config, so it look for the columns defined as pk on the config. In
case it does not find the pk column(or columns if it is a compound pk), it
looks for the ID columns according to DAS Convention Over Configuration
rules. Otherwise it throws the exception.
Does it help? : )
Regards,
Adriano Crestani
On 7/27/07, Amita Vadhavkar <am...@gmail.com> wrote:
>
> Further on this,
> Need to consider single and compound PKs case.
> When select does not include complete PK (all PK columns from compound PK)
> ,
> DAS needs to throw exception.
>
> As a fix proposing below changes:-
>
> A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys , fill it
> during constuctor
> and provide get method - getAllPKsForTable(tableName).
>
> B> In ResultSetRow - add method
> checkResultSetMissesPK(allTableNamesFromQueryResultSet)
> which will take each table and check if all PKs are there in result set.
> If
> not it will mark that TableData with hasValidPKs=FALSE.
>
> C> There is already another check in TableData.addData(), which marks this
> flag FALSE, if any PK in result set has NULL data.
>
> B> and C> together will provide complete check
>
> D>In ResultSetRow, call,
> checkResultSetMissesPK(allTableNamesFromQueryResultSet) from processRow()
> and processRecursiveRow(). With this, all TableData will be set with
> proper
> hasValidPK, during ResultSetProcessor.processResultSet() and consequently,
> in ResultSetProcessor.addRowToGraph() will be able to do judgement if any
> table is missing PK, in which case DAS will throw RuntimeException and
> will
> not form DataGraph.
>
> Any comments/suggestions? Based on this I will work on patch for
> JIRA-1464.
>
> Regards,
> Amita
>
> On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
> >
> > It is best to throw an exception for PK not being there, otherwise an
> > empty
> > result set can have two meaning:Empty or something went wrong
> >
> > On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
> > >
> > > Amita,
> > >
> > > There is now way for DAS to keep the relationship data consistence
> > if
> > > both, pk and fk, are not completely defined. Without them DAS cannot
> > > predict
> > > the relationship.
> > >
> > > As Brent said, I think it could throw an exception when the PK is
> > missing,
> > > no matter if there are relationships or not. Because, as far as I
> know,
> > a
> > > table that has no complete PK retrieved on the ResultSet is being
> > omitted
> > > from the graph and I don't think this is a good approach.
> > >
> > > But when only the fk is missing, I think it is ok to omit the
> > relationship
> > > between the data objects on the graph. This way the user has the
> option
> > to
> > > decide if the references(relationships) will be included or not on the
> > > graph.
> > >
> > > Regards,
> > > Adriano Crestani
> > >
> > > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > > >
> > > > Amita,
> > > >
> > > > Definitely, the DAS should enforce the requirement that the PK
> should
> > > > be returned for each table in the results. I would consider this a
> > > > case where the DAS should throw an exception.
> > > >
> > > > Brent
> > > >
> > > > On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > > Sorry for the leng thy mail....
> > > > >
> > > > > Tried to check the case when the database has parent-child tables
> > and
> > > > DAS
> > > > > SELECT Command may/may not
> > > > > contain the PKs of the tables. And found some quite confusing
> > > > cases/results,
> > > > > which are effectively giving
> > > > > user a wrong impression of the data in tables.
> > > > >
> > > > > Looks like there are places where we are allowing partial results,
> > > wrong
> > > > > association in parent and child rows.
> > > > > As RDB DAS logic revolves around PKs, can we state clearly that
> > > > > "When Query SELECT does not include PK for a table, the data graph
> > > will
> > > > be
> > > > > empty for that table"
> > > > > ? i.e. in the below analysis, instead of giving wrong/partial
> > result,
> > > at
> > > > > least consistently give no result?
> > > > > And make necessary code corrections to adhere to this statement?
> > > > >
> > > > > Or any alternative approaches?
> > > > >
> > > > > What DAS C++ is doing for this case? Just curious.
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > Say, take below data -
> > > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > > > Data:
> > > > > SINGER
> > > > > ID NAME
> > > > > --------------------
> > > > > 1 Jonh
> > > > > 2 Jane
> > > > >
> > > > > SONG
> > > > > ID TITLE SINGERID
> > > > > -------------------------------------
> > > > > 10 ABCD 1
> > > > > 20 Lamb 1
> > > > > 30 La ra ra 2
> > > > >
> > > > > There are total 8 cases that I can see. viz.
> > > > >
> > > > > No relationship in config
> > > > > --------------------------------------------------
> > > > > parent PK in SEL child PK in SEL Result
> > > > >
> > ----------------------------------------------------------------------
> > > > > [1] present present correct
> > > > > [2] present missing wrong
> > > > > [3] missing present wrong
> > > > > [4] missing missing wrong
> > > > >
> > > > > Relationship in config
> > > > > [5] present present correct
> > > > > [6] present missing wrong
> > > > > [7] missing present wrong
> > > > > [8] missing missing wrong
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > When relationship is not defined in DAS Config
> > > > > DAS Client code:
> > > > > ----------------
> > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > getConnection());
> > > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > > DataObject root = select.executeQuery();
> > > > > List singers = root.getList("SINGER");
> > > > > if(singers != null){
> > > > > System.out.println("Singer size:"+singers.size());
> > > > > for(int i=0; i<singers.size(); i++){
> > > > > System.out.println("SINGER NAME:"+
> > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > > }
> > > > >
> > > > > }
> > > > >
> > > > > List songs = root.getList("SONG");//as there is no relationship
> > > > > (explicit/implicit)
> > > > >
> > > > > if(songs != null){
> > > > > System.out.println("Songs size "+songs .size());
> > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > System.out.println("SONG TITLE:"+
> > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > }
> > > > > }
> > > > >
> > > > > }
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > Result:
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> SINGER,
> > > SONG
> > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > Singer size:2
> > > > > SINGER NAME:John
> > > > > SINGER NAME:Jane
> > > > > Songs size 3
> > > > > SONG TITLE:ABCD
> > > > > SONG TITLE:Lamb
> > > > > SONG TITLE:La ra ra
> > > > >
> > > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > WHERE
> > > > > SINGER.ID = SONG.SINGERID
> > > > > Singer size:2
> > > > > SINGER NAME:John
> > > > > SINGER NAME:Jane
> > > > > Songs size 1
> > > > > SONG TITLE:ABCD
> > > > >
> > > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> WHERE
> > > > > SINGER.ID = SONG.SINGERID
> > > > > Singer size:1
> > > > > SINGER NAME:John
> > > > > Songs size 3
> > > > > SONG TITLE:ABCD
> > > > > SONG TITLE:Lamb
> > > > > SONG TITLE:La ra ra
> > > > >
> > > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> SINGER.ID=
> > > > > SONG.SINGERID
> > > > > Singer size:1
> > > > > SINGER NAME:John
> > > > > Songs size 1
> > > > > SONG TITLE:ABCD
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > When relationship one-to-many (sing) is defined in DAS Config
> > > > > DAS Client code:
> > > > > ----------------
> > > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > > getConnection());
> > > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > > DataObject root = select.executeQuery();
> > > > > List singers = root.getList("SINGER");
> > > > > if(singers != null){
> > > > > System.out.println("Singer size:"+singers.size());
> > > > > for(int i=0; i<singers.size(); i++){
> > > > > System.out.println("SINGER NAME:"+
> > > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > >
> > > > > List songs = ((DataObject)singers.get(i)).getList("sing");
> > > //use
> > > > > relationship
> > > > > if(songs != null){
> > > > > System.out.println("Songs size "+songs .size()+" for
> > > singer
> > > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > > for(int ii=0; ii<songs.size(); ii++){
> > > > > System.out.println("SONG TITLE:"+
> > > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > > }
> > > > > }
> > > > >
> > > > > }
> > > > > }
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > Result:
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM
> SINGER,
> > > SONG
> > > > > WHERE SINGER.ID = SONG.SINGERID
> > > > > Singer size:2
> > > > > SINGER NAME:John
> > > > > Songs size 2 for singer :John
> > > > > SONG TITLE:ABCD
> > > > > SONG TITLE:Lamb
> > > > > SINGER NAME:Jane
> > > > > Songs size 1 for singer :Jane
> > > > > SONG TITLE:La ra ra
> > > > >
> > > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> > WHERE
> > > > > SINGER.ID = SONG.SINGERID
> > > > > Singer size:2
> > > > > SINGER NAME:John
> > > > > Songs size 0 for singer :John
> > > > > SINGER NAME:Jane
> > > > > Songs size 1 for singer :Jane
> > > > > SONG TITLE:ABCD
> > > > >
> > > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> WHERE
> > > > > SINGER.ID = SONG.SINGERID
> > > > > Singer size:1
> > > > > SINGER NAME:John
> > > > > Songs size 3 for singer :John
> > > > > SONG TITLE:ABCD
> > > > > SONG TITLE:Lamb
> > > > > SONG TITLE:La ra ra
> > > > >
> > > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> SINGER.ID=
> > > > > SONG.SINGERID
> > > > > Singer size:1
> > > > > SINGER NAME:John
> > > > > Songs size 1 for singer :John
> > > > > SONG TITLE:ABCD
> > > > >
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > >
> > > > > Regards,
> > > > >
> > > > > Amita
> > > > >
> > > >
> > > >
> ---------------------------------------------------------------------
> > > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > > >
> > > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Amita Vadhavkar <am...@gmail.com>.
Further on this,
Need to consider single and compound PKs case.
When select does not include complete PK (all PK columns from compound PK) ,
DAS needs to throw exception.
As a fix proposing below changes:-
A>In ResultMetadata - introduce new HashMap tableToPrimaryKeys , fill it
during constuctor
and provide get method - getAllPKsForTable(tableName).
B> In ResultSetRow - add method
checkResultSetMissesPK(allTableNamesFromQueryResultSet)
which will take each table and check if all PKs are there in result set. If
not it will mark that TableData with hasValidPKs=FALSE.
C> There is already another check in TableData.addData(), which marks this
flag FALSE, if any PK in result set has NULL data.
B> and C> together will provide complete check
D>In ResultSetRow, call,
checkResultSetMissesPK(allTableNamesFromQueryResultSet) from processRow()
and processRecursiveRow(). With this, all TableData will be set with proper
hasValidPK, during ResultSetProcessor.processResultSet() and consequently,
in ResultSetProcessor.addRowToGraph() will be able to do judgement if any
table is missing PK, in which case DAS will throw RuntimeException and will
not form DataGraph.
Any comments/suggestions? Based on this I will work on patch for JIRA-1464.
Regards,
Amita
On 7/19/07, haleh mahbod <hm...@gmail.com> wrote:
>
> It is best to throw an exception for PK not being there, otherwise an
> empty
> result set can have two meaning:Empty or something went wrong
>
> On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
> >
> > Amita,
> >
> > There is now way for DAS to keep the relationship data consistence
> if
> > both, pk and fk, are not completely defined. Without them DAS cannot
> > predict
> > the relationship.
> >
> > As Brent said, I think it could throw an exception when the PK is
> missing,
> > no matter if there are relationships or not. Because, as far as I know,
> a
> > table that has no complete PK retrieved on the ResultSet is being
> omitted
> > from the graph and I don't think this is a good approach.
> >
> > But when only the fk is missing, I think it is ok to omit the
> relationship
> > between the data objects on the graph. This way the user has the option
> to
> > decide if the references(relationships) will be included or not on the
> > graph.
> >
> > Regards,
> > Adriano Crestani
> >
> > On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> > >
> > > Amita,
> > >
> > > Definitely, the DAS should enforce the requirement that the PK should
> > > be returned for each table in the results. I would consider this a
> > > case where the DAS should throw an exception.
> > >
> > > Brent
> > >
> > > On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > > Sorry for the leng thy mail....
> > > >
> > > > Tried to check the case when the database has parent-child tables
> and
> > > DAS
> > > > SELECT Command may/may not
> > > > contain the PKs of the tables. And found some quite confusing
> > > cases/results,
> > > > which are effectively giving
> > > > user a wrong impression of the data in tables.
> > > >
> > > > Looks like there are places where we are allowing partial results,
> > wrong
> > > > association in parent and child rows.
> > > > As RDB DAS logic revolves around PKs, can we state clearly that
> > > > "When Query SELECT does not include PK for a table, the data graph
> > will
> > > be
> > > > empty for that table"
> > > > ? i.e. in the below analysis, instead of giving wrong/partial
> result,
> > at
> > > > least consistently give no result?
> > > > And make necessary code corrections to adhere to this statement?
> > > >
> > > > Or any alternative approaches?
> > > >
> > > > What DAS C++ is doing for this case? Just curious.
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > Say, take below data -
> > > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > > Data:
> > > > SINGER
> > > > ID NAME
> > > > --------------------
> > > > 1 Jonh
> > > > 2 Jane
> > > >
> > > > SONG
> > > > ID TITLE SINGERID
> > > > -------------------------------------
> > > > 10 ABCD 1
> > > > 20 Lamb 1
> > > > 30 La ra ra 2
> > > >
> > > > There are total 8 cases that I can see. viz.
> > > >
> > > > No relationship in config
> > > > --------------------------------------------------
> > > > parent PK in SEL child PK in SEL Result
> > > >
> ----------------------------------------------------------------------
> > > > [1] present present correct
> > > > [2] present missing wrong
> > > > [3] missing present wrong
> > > > [4] missing missing wrong
> > > >
> > > > Relationship in config
> > > > [5] present present correct
> > > > [6] present missing wrong
> > > > [7] missing present wrong
> > > > [8] missing missing wrong
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > When relationship is not defined in DAS Config
> > > > DAS Client code:
> > > > ----------------
> > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > getConnection());
> > > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > > DataObject root = select.executeQuery();
> > > > List singers = root.getList("SINGER");
> > > > if(singers != null){
> > > > System.out.println("Singer size:"+singers.size());
> > > > for(int i=0; i<singers.size(); i++){
> > > > System.out.println("SINGER NAME:"+
> > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > > }
> > > >
> > > > }
> > > >
> > > > List songs = root.getList("SONG");//as there is no relationship
> > > > (explicit/implicit)
> > > >
> > > > if(songs != null){
> > > > System.out.println("Songs size "+songs .size());
> > > > for(int ii=0; ii<songs.size(); ii++){
> > > > System.out.println("SONG TITLE:"+
> > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > }
> > > > }
> > > >
> > > > }
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > Result:
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> > SONG
> > > > WHERE SINGER.ID = SONG.SINGERID
> > > > Singer size:2
> > > > SINGER NAME:John
> > > > SINGER NAME:Jane
> > > > Songs size 3
> > > > SONG TITLE:ABCD
> > > > SONG TITLE:Lamb
> > > > SONG TITLE:La ra ra
> > > >
> > > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> WHERE
> > > > SINGER.ID = SONG.SINGERID
> > > > Singer size:2
> > > > SINGER NAME:John
> > > > SINGER NAME:Jane
> > > > Songs size 1
> > > > SONG TITLE:ABCD
> > > >
> > > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> > > > SINGER.ID = SONG.SINGERID
> > > > Singer size:1
> > > > SINGER NAME:John
> > > > Songs size 3
> > > > SONG TITLE:ABCD
> > > > SONG TITLE:Lamb
> > > > SONG TITLE:La ra ra
> > > >
> > > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID=
> > > > SONG.SINGERID
> > > > Singer size:1
> > > > SINGER NAME:John
> > > > Songs size 1
> > > > SONG TITLE:ABCD
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > When relationship one-to-many (sing) is defined in DAS Config
> > > > DAS Client code:
> > > > ----------------
> > > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> > getConnection());
> > > > Command select = das.getCommand("withRel-1/2/3/4");
> > > > DataObject root = select.executeQuery();
> > > > List singers = root.getList("SINGER");
> > > > if(singers != null){
> > > > System.out.println("Singer size:"+singers.size());
> > > > for(int i=0; i<singers.size(); i++){
> > > > System.out.println("SINGER NAME:"+
> > > > ((DataObject)singers.get(i)).getString("NAME"));
> > > >
> > > > List songs = ((DataObject)singers.get(i)).getList("sing");
> > //use
> > > > relationship
> > > > if(songs != null){
> > > > System.out.println("Songs size "+songs .size()+" for
> > singer
> > > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > > for(int ii=0; ii<songs.size(); ii++){
> > > > System.out.println("SONG TITLE:"+
> > > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > > }
> > > > }
> > > >
> > > > }
> > > > }
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > Result:
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> > SONG
> > > > WHERE SINGER.ID = SONG.SINGERID
> > > > Singer size:2
> > > > SINGER NAME:John
> > > > Songs size 2 for singer :John
> > > > SONG TITLE:ABCD
> > > > SONG TITLE:Lamb
> > > > SINGER NAME:Jane
> > > > Songs size 1 for singer :Jane
> > > > SONG TITLE:La ra ra
> > > >
> > > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG
> WHERE
> > > > SINGER.ID = SONG.SINGERID
> > > > Singer size:2
> > > > SINGER NAME:John
> > > > Songs size 0 for singer :John
> > > > SINGER NAME:Jane
> > > > Songs size 1 for singer :Jane
> > > > SONG TITLE:ABCD
> > > >
> > > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> > > > SINGER.ID = SONG.SINGERID
> > > > Singer size:1
> > > > SINGER NAME:John
> > > > Songs size 3 for singer :John
> > > > SONG TITLE:ABCD
> > > > SONG TITLE:Lamb
> > > > SONG TITLE:La ra ra
> > > >
> > > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID=
> > > > SONG.SINGERID
> > > > Singer size:1
> > > > SINGER NAME:John
> > > > Songs size 1 for singer :John
> > > > SONG TITLE:ABCD
> > > >
> > >
> >
> -------------------------------------------------------------------------------------
> > > >
> > > > Regards,
> > > >
> > > > Amita
> > > >
> > >
> > > ---------------------------------------------------------------------
> > > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> > >
> > >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by haleh mahbod <hm...@gmail.com>.
It is best to throw an exception for PK not being there, otherwise an empty
result set can have two meaning:Empty or something went wrong
On 7/18/07, Adriano Crestani <ad...@apache.org> wrote:
>
> Amita,
>
> There is now way for DAS to keep the relationship data consistence if
> both, pk and fk, are not completely defined. Without them DAS cannot
> predict
> the relationship.
>
> As Brent said, I think it could throw an exception when the PK is missing,
> no matter if there are relationships or not. Because, as far as I know, a
> table that has no complete PK retrieved on the ResultSet is being omitted
> from the graph and I don't think this is a good approach.
>
> But when only the fk is missing, I think it is ok to omit the relationship
> between the data objects on the graph. This way the user has the option to
> decide if the references(relationships) will be included or not on the
> graph.
>
> Regards,
> Adriano Crestani
>
> On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
> >
> > Amita,
> >
> > Definitely, the DAS should enforce the requirement that the PK should
> > be returned for each table in the results. I would consider this a
> > case where the DAS should throw an exception.
> >
> > Brent
> >
> > On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > > Sorry for the leng thy mail....
> > >
> > > Tried to check the case when the database has parent-child tables and
> > DAS
> > > SELECT Command may/may not
> > > contain the PKs of the tables. And found some quite confusing
> > cases/results,
> > > which are effectively giving
> > > user a wrong impression of the data in tables.
> > >
> > > Looks like there are places where we are allowing partial results,
> wrong
> > > association in parent and child rows.
> > > As RDB DAS logic revolves around PKs, can we state clearly that
> > > "When Query SELECT does not include PK for a table, the data graph
> will
> > be
> > > empty for that table"
> > > ? i.e. in the below analysis, instead of giving wrong/partial result,
> at
> > > least consistently give no result?
> > > And make necessary code corrections to adhere to this statement?
> > >
> > > Or any alternative approaches?
> > >
> > > What DAS C++ is doing for this case? Just curious.
> > >
> >
> -------------------------------------------------------------------------------------
> > > Say, take below data -
> > > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > > Data:
> > > SINGER
> > > ID NAME
> > > --------------------
> > > 1 Jonh
> > > 2 Jane
> > >
> > > SONG
> > > ID TITLE SINGERID
> > > -------------------------------------
> > > 10 ABCD 1
> > > 20 Lamb 1
> > > 30 La ra ra 2
> > >
> > > There are total 8 cases that I can see. viz.
> > >
> > > No relationship in config
> > > --------------------------------------------------
> > > parent PK in SEL child PK in SEL Result
> > > ----------------------------------------------------------------------
> > > [1] present present correct
> > > [2] present missing wrong
> > > [3] missing present wrong
> > > [4] missing missing wrong
> > >
> > > Relationship in config
> > > [5] present present correct
> > > [6] present missing wrong
> > > [7] missing present wrong
> > > [8] missing missing wrong
> > >
> >
> -------------------------------------------------------------------------------------
> > > When relationship is not defined in DAS Config
> > > DAS Client code:
> > > ----------------
> > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> getConnection());
> > > Command select = das.getCommand("withNoRel-5/6/7/8");
> > > DataObject root = select.executeQuery();
> > > List singers = root.getList("SINGER");
> > > if(singers != null){
> > > System.out.println("Singer size:"+singers.size());
> > > for(int i=0; i<singers.size(); i++){
> > > System.out.println("SINGER NAME:"+
> > > ((DataObject)singers.get(i)).getString("NAME"));
> > > }
> > >
> > > }
> > >
> > > List songs = root.getList("SONG");//as there is no relationship
> > > (explicit/implicit)
> > >
> > > if(songs != null){
> > > System.out.println("Songs size "+songs .size());
> > > for(int ii=0; ii<songs.size(); ii++){
> > > System.out.println("SONG TITLE:"+
> > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > }
> > > }
> > >
> > > }
> > >
> >
> -------------------------------------------------------------------------------------
> > > Result:
> > >
> >
> -------------------------------------------------------------------------------------
> > > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> SONG
> > > WHERE SINGER.ID = SONG.SINGERID
> > > Singer size:2
> > > SINGER NAME:John
> > > SINGER NAME:Jane
> > > Songs size 3
> > > SONG TITLE:ABCD
> > > SONG TITLE:Lamb
> > > SONG TITLE:La ra ra
> > >
> > > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > SINGER.ID = SONG.SINGERID
> > > Singer size:2
> > > SINGER NAME:John
> > > SINGER NAME:Jane
> > > Songs size 1
> > > SONG TITLE:ABCD
> > >
> > > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> > > SINGER.ID = SONG.SINGERID
> > > Singer size:1
> > > SINGER NAME:John
> > > Songs size 3
> > > SONG TITLE:ABCD
> > > SONG TITLE:Lamb
> > > SONG TITLE:La ra ra
> > >
> > > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
> > > SONG.SINGERID
> > > Singer size:1
> > > SINGER NAME:John
> > > Songs size 1
> > > SONG TITLE:ABCD
> > >
> >
> -------------------------------------------------------------------------------------
> > > When relationship one-to-many (sing) is defined in DAS Config
> > > DAS Client code:
> > > ----------------
> > > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"),
> getConnection());
> > > Command select = das.getCommand("withRel-1/2/3/4");
> > > DataObject root = select.executeQuery();
> > > List singers = root.getList("SINGER");
> > > if(singers != null){
> > > System.out.println("Singer size:"+singers.size());
> > > for(int i=0; i<singers.size(); i++){
> > > System.out.println("SINGER NAME:"+
> > > ((DataObject)singers.get(i)).getString("NAME"));
> > >
> > > List songs = ((DataObject)singers.get(i)).getList("sing");
> //use
> > > relationship
> > > if(songs != null){
> > > System.out.println("Songs size "+songs .size()+" for
> singer
> > > :"+((DataObject)singers.get(i)).getString("NAME"));
> > > for(int ii=0; ii<songs.size(); ii++){
> > > System.out.println("SONG TITLE:"+
> > > ((DataObject)songs.get(ii)).getString("TITLE"));
> > > }
> > > }
> > >
> > > }
> > > }
> > >
> >
> -------------------------------------------------------------------------------------
> > > Result:
> > >
> >
> -------------------------------------------------------------------------------------
> > > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER,
> SONG
> > > WHERE SINGER.ID = SONG.SINGERID
> > > Singer size:2
> > > SINGER NAME:John
> > > Songs size 2 for singer :John
> > > SONG TITLE:ABCD
> > > SONG TITLE:Lamb
> > > SINGER NAME:Jane
> > > Songs size 1 for singer :Jane
> > > SONG TITLE:La ra ra
> > >
> > > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > > SINGER.ID = SONG.SINGERID
> > > Singer size:2
> > > SINGER NAME:John
> > > Songs size 0 for singer :John
> > > SINGER NAME:Jane
> > > Songs size 1 for singer :Jane
> > > SONG TITLE:ABCD
> > >
> > > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> > > SINGER.ID = SONG.SINGERID
> > > Singer size:1
> > > SINGER NAME:John
> > > Songs size 3 for singer :John
> > > SONG TITLE:ABCD
> > > SONG TITLE:Lamb
> > > SONG TITLE:La ra ra
> > >
> > > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
> > > SONG.SINGERID
> > > Singer size:1
> > > SINGER NAME:John
> > > Songs size 1 for singer :John
> > > SONG TITLE:ABCD
> > >
> >
> -------------------------------------------------------------------------------------
> > >
> > > Regards,
> > >
> > > Amita
> > >
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> > For additional commands, e-mail: tuscany-dev-help@ws.apache.org
> >
> >
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Adriano Crestani <ad...@apache.org>.
Amita,
There is now way for DAS to keep the relationship data consistence if
both, pk and fk, are not completely defined. Without them DAS cannot predict
the relationship.
As Brent said, I think it could throw an exception when the PK is missing,
no matter if there are relationships or not. Because, as far as I know, a
table that has no complete PK retrieved on the ResultSet is being omitted
from the graph and I don't think this is a good approach.
But when only the fk is missing, I think it is ok to omit the relationship
between the data objects on the graph. This way the user has the option to
decide if the references(relationships) will be included or not on the
graph.
Regards,
Adriano Crestani
On 7/18/07, Brent Daniel <br...@gmail.com> wrote:
>
> Amita,
>
> Definitely, the DAS should enforce the requirement that the PK should
> be returned for each table in the results. I would consider this a
> case where the DAS should throw an exception.
>
> Brent
>
> On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> > Sorry for the leng thy mail....
> >
> > Tried to check the case when the database has parent-child tables and
> DAS
> > SELECT Command may/may not
> > contain the PKs of the tables. And found some quite confusing
> cases/results,
> > which are effectively giving
> > user a wrong impression of the data in tables.
> >
> > Looks like there are places where we are allowing partial results, wrong
> > association in parent and child rows.
> > As RDB DAS logic revolves around PKs, can we state clearly that
> > "When Query SELECT does not include PK for a table, the data graph will
> be
> > empty for that table"
> > ? i.e. in the below analysis, instead of giving wrong/partial result, at
> > least consistently give no result?
> > And make necessary code corrections to adhere to this statement?
> >
> > Or any alternative approaches?
> >
> > What DAS C++ is doing for this case? Just curious.
> >
> -------------------------------------------------------------------------------------
> > Say, take below data -
> > Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> > Data:
> > SINGER
> > ID NAME
> > --------------------
> > 1 Jonh
> > 2 Jane
> >
> > SONG
> > ID TITLE SINGERID
> > -------------------------------------
> > 10 ABCD 1
> > 20 Lamb 1
> > 30 La ra ra 2
> >
> > There are total 8 cases that I can see. viz.
> >
> > No relationship in config
> > --------------------------------------------------
> > parent PK in SEL child PK in SEL Result
> > ----------------------------------------------------------------------
> > [1] present present correct
> > [2] present missing wrong
> > [3] missing present wrong
> > [4] missing missing wrong
> >
> > Relationship in config
> > [5] present present correct
> > [6] present missing wrong
> > [7] missing present wrong
> > [8] missing missing wrong
> >
> -------------------------------------------------------------------------------------
> > When relationship is not defined in DAS Config
> > DAS Client code:
> > ----------------
> > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"), getConnection());
> > Command select = das.getCommand("withNoRel-5/6/7/8");
> > DataObject root = select.executeQuery();
> > List singers = root.getList("SINGER");
> > if(singers != null){
> > System.out.println("Singer size:"+singers.size());
> > for(int i=0; i<singers.size(); i++){
> > System.out.println("SINGER NAME:"+
> > ((DataObject)singers.get(i)).getString("NAME"));
> > }
> >
> > }
> >
> > List songs = root.getList("SONG");//as there is no relationship
> > (explicit/implicit)
> >
> > if(songs != null){
> > System.out.println("Songs size "+songs .size());
> > for(int ii=0; ii<songs.size(); ii++){
> > System.out.println("SONG TITLE:"+
> > ((DataObject)songs.get(ii)).getString("TITLE"));
> > }
> > }
> >
> > }
> >
> -------------------------------------------------------------------------------------
> > Result:
> >
> -------------------------------------------------------------------------------------
> > [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> > WHERE SINGER.ID = SONG.SINGERID
> > Singer size:2
> > SINGER NAME:John
> > SINGER NAME:Jane
> > Songs size 3
> > SONG TITLE:ABCD
> > SONG TITLE:Lamb
> > SONG TITLE:La ra ra
> >
> > [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > SINGER.ID = SONG.SINGERID
> > Singer size:2
> > SINGER NAME:John
> > SINGER NAME:Jane
> > Songs size 1
> > SONG TITLE:ABCD
> >
> > [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> > SINGER.ID = SONG.SINGERID
> > Singer size:1
> > SINGER NAME:John
> > Songs size 3
> > SONG TITLE:ABCD
> > SONG TITLE:Lamb
> > SONG TITLE:La ra ra
> >
> > [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
> > SONG.SINGERID
> > Singer size:1
> > SINGER NAME:John
> > Songs size 1
> > SONG TITLE:ABCD
> >
> -------------------------------------------------------------------------------------
> > When relationship one-to-many (sing) is defined in DAS Config
> > DAS Client code:
> > ----------------
> > DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"), getConnection());
> > Command select = das.getCommand("withRel-1/2/3/4");
> > DataObject root = select.executeQuery();
> > List singers = root.getList("SINGER");
> > if(singers != null){
> > System.out.println("Singer size:"+singers.size());
> > for(int i=0; i<singers.size(); i++){
> > System.out.println("SINGER NAME:"+
> > ((DataObject)singers.get(i)).getString("NAME"));
> >
> > List songs = ((DataObject)singers.get(i)).getList("sing"); //use
> > relationship
> > if(songs != null){
> > System.out.println("Songs size "+songs .size()+" for singer
> > :"+((DataObject)singers.get(i)).getString("NAME"));
> > for(int ii=0; ii<songs.size(); ii++){
> > System.out.println("SONG TITLE:"+
> > ((DataObject)songs.get(ii)).getString("TITLE"));
> > }
> > }
> >
> > }
> > }
> >
> -------------------------------------------------------------------------------------
> > Result:
> >
> -------------------------------------------------------------------------------------
> > [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> > WHERE SINGER.ID = SONG.SINGERID
> > Singer size:2
> > SINGER NAME:John
> > Songs size 2 for singer :John
> > SONG TITLE:ABCD
> > SONG TITLE:Lamb
> > SINGER NAME:Jane
> > Songs size 1 for singer :Jane
> > SONG TITLE:La ra ra
> >
> > [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> > SINGER.ID = SONG.SINGERID
> > Singer size:2
> > SINGER NAME:John
> > Songs size 0 for singer :John
> > SINGER NAME:Jane
> > Songs size 1 for singer :Jane
> > SONG TITLE:ABCD
> >
> > [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> > SINGER.ID = SONG.SINGERID
> > Singer size:1
> > SINGER NAME:John
> > Songs size 3 for singer :John
> > SONG TITLE:ABCD
> > SONG TITLE:Lamb
> > SONG TITLE:La ra ra
> >
> > [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
> > SONG.SINGERID
> > Singer size:1
> > SINGER NAME:John
> > Songs size 1 for singer :John
> > SONG TITLE:ABCD
> >
> -------------------------------------------------------------------------------------
> >
> > Regards,
> >
> > Amita
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
> For additional commands, e-mail: tuscany-dev-help@ws.apache.org
>
>
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
Posted by Brent Daniel <br...@gmail.com>.
Amita,
Definitely, the DAS should enforce the requirement that the PK should
be returned for each table in the results. I would consider this a
case where the DAS should throw an exception.
Brent
On 7/18/07, Amita Vadhavkar <am...@gmail.com> wrote:
> Sorry for the leng thy mail....
>
> Tried to check the case when the database has parent-child tables and DAS
> SELECT Command may/may not
> contain the PKs of the tables. And found some quite confusing cases/results,
> which are effectively giving
> user a wrong impression of the data in tables.
>
> Looks like there are places where we are allowing partial results, wrong
> association in parent and child rows.
> As RDB DAS logic revolves around PKs, can we state clearly that
> "When Query SELECT does not include PK for a table, the data graph will be
> empty for that table"
> ? i.e. in the below analysis, instead of giving wrong/partial result, at
> least consistently give no result?
> And make necessary code corrections to adhere to this statement?
>
> Or any alternative approaches?
>
> What DAS C++ is doing for this case? Just curious.
> -------------------------------------------------------------------------------------
> Say, take below data -
> Parent: SINGER(ID, NAME), Child:SONG (ID, TITLE, SINGERID)
> Data:
> SINGER
> ID NAME
> --------------------
> 1 Jonh
> 2 Jane
>
> SONG
> ID TITLE SINGERID
> -------------------------------------
> 10 ABCD 1
> 20 Lamb 1
> 30 La ra ra 2
>
> There are total 8 cases that I can see. viz.
>
> No relationship in config
> --------------------------------------------------
> parent PK in SEL child PK in SEL Result
> ----------------------------------------------------------------------
> [1] present present correct
> [2] present missing wrong
> [3] missing present wrong
> [4] missing missing wrong
>
> Relationship in config
> [5] present present correct
> [6] present missing wrong
> [7] missing present wrong
> [8] missing missing wrong
> -------------------------------------------------------------------------------------
> When relationship is not defined in DAS Config
> DAS Client code:
> ----------------
> DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"), getConnection());
> Command select = das.getCommand("withNoRel-5/6/7/8");
> DataObject root = select.executeQuery();
> List singers = root.getList("SINGER");
> if(singers != null){
> System.out.println("Singer size:"+singers.size());
> for(int i=0; i<singers.size(); i++){
> System.out.println("SINGER NAME:"+
> ((DataObject)singers.get(i)).getString("NAME"));
> }
>
> }
>
> List songs = root.getList("SONG");//as there is no relationship
> (explicit/implicit)
>
> if(songs != null){
> System.out.println("Songs size "+songs .size());
> for(int ii=0; ii<songs.size(); ii++){
> System.out.println("SONG TITLE:"+
> ((DataObject)songs.get(ii)).getString("TITLE"));
> }
> }
>
> }
> -------------------------------------------------------------------------------------
> Result:
> -------------------------------------------------------------------------------------
> [1] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> WHERE SINGER.ID = SONG.SINGERID
> Singer size:2
> SINGER NAME:John
> SINGER NAME:Jane
> Songs size 3
> SONG TITLE:ABCD
> SONG TITLE:Lamb
> SONG TITLE:La ra ra
>
> [2] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> SINGER.ID = SONG.SINGERID
> Singer size:2
> SINGER NAME:John
> SINGER NAME:Jane
> Songs size 1
> SONG TITLE:ABCD
>
> [3] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> SINGER.ID = SONG.SINGERID
> Singer size:1
> SINGER NAME:John
> Songs size 3
> SONG TITLE:ABCD
> SONG TITLE:Lamb
> SONG TITLE:La ra ra
>
> [4] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
> SONG.SINGERID
> Singer size:1
> SINGER NAME:John
> Songs size 1
> SONG TITLE:ABCD
> -------------------------------------------------------------------------------------
> When relationship one-to-many (sing) is defined in DAS Config
> DAS Client code:
> ----------------
> DAS das = DAS.FACTORY.createDAS(getConfig("cfg.xml"), getConnection());
> Command select = das.getCommand("withRel-1/2/3/4");
> DataObject root = select.executeQuery();
> List singers = root.getList("SINGER");
> if(singers != null){
> System.out.println("Singer size:"+singers.size());
> for(int i=0; i<singers.size(); i++){
> System.out.println("SINGER NAME:"+
> ((DataObject)singers.get(i)).getString("NAME"));
>
> List songs = ((DataObject)singers.get(i)).getList("sing"); //use
> relationship
> if(songs != null){
> System.out.println("Songs size "+songs .size()+" for singer
> :"+((DataObject)singers.get(i)).getString("NAME"));
> for(int ii=0; ii<songs.size(); ii++){
> System.out.println("SONG TITLE:"+
> ((DataObject)songs.get(ii)).getString("TITLE"));
> }
> }
>
> }
> }
> -------------------------------------------------------------------------------------
> Result:
> -------------------------------------------------------------------------------------
> [5] SELECT SINGER.ID, SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG
> WHERE SINGER.ID = SONG.SINGERID
> Singer size:2
> SINGER NAME:John
> Songs size 2 for singer :John
> SONG TITLE:ABCD
> SONG TITLE:Lamb
> SINGER NAME:Jane
> Songs size 1 for singer :Jane
> SONG TITLE:La ra ra
>
> [6] SELECT SINGER.ID, SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE
> SINGER.ID = SONG.SINGERID
> Singer size:2
> SINGER NAME:John
> Songs size 0 for singer :John
> SINGER NAME:Jane
> Songs size 1 for singer :Jane
> SONG TITLE:ABCD
>
> [7] SELECT SINGER.NAME, SONG.ID, SONG.TITLE FROM SINGER, SONG WHERE
> SINGER.ID = SONG.SINGERID
> Singer size:1
> SINGER NAME:John
> Songs size 3 for singer :John
> SONG TITLE:ABCD
> SONG TITLE:Lamb
> SONG TITLE:La ra ra
>
> [8] SELECT SINGER.NAME, SONG.TITLE FROM SINGER, SONG WHERE SINGER.ID =
> SONG.SINGERID
> Singer size:1
> SINGER NAME:John
> Songs size 1 for singer :John
> SONG TITLE:ABCD
> -------------------------------------------------------------------------------------
>
> Regards,
>
> Amita
>
---------------------------------------------------------------------
To unsubscribe, e-mail: tuscany-dev-unsubscribe@ws.apache.org
For additional commands, e-mail: tuscany-dev-help@ws.apache.org