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 <[EMAIL PROTECTED]> wrote:
>
> It seems ok Amita ; )
>
> Adriano Crestani
>
> On 7/27/07, Amita Vadhavkar <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> > > > > > > For additional commands, e-mail:
> [EMAIL PROTECTED]
> > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
