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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 <[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.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:
> > [EMAIL PROTECTED]
> > > > > > > > > For additional commands, e-mail:
> > > [EMAIL PROTECTED]
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
