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
