Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 Engine1 - 2 Block 1 1 3 Cam Soft2 1 4 Piston8 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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 [EMAIL PROTECTED] 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 [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 Engine1 - 2 Block 1 1 3 Cam Soft2 1 4 Piston8 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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 Engine1 - 2 Block 1 1 3 Cam Soft2 1 4 Piston8 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:- AIn 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 DIn 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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 Engine1 - 2 Block 1 1 3 Cam Soft2 1 4 Piston8 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:- AIn 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 DIn 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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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:- AIn 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 DIn 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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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:- AIn 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 DIn 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 ra2 There are total 8 cases that I can see. viz. No relationship in config -- parent PK in SEL child PK in SELResult
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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:- AIn 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 DIn 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.
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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:- AIn 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 DIn 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 ra2 There are total 8 cases that I can see. viz. No relationship in config -- parent PK in SEL child PK in SELResult -- [1] present presentcorrect [2] present missingwrong [3] missing presentwrong [4] missing missing wrong Relationship in config [5] presentpresent correct [6] presentmissing wrong [7] missingpresent wrong [8] missingmissingwrong - When relationship is not defined in DAS Config DAS Client code: DAS das = DAS.FACTORY.createDAS(getConfig(cfg.xml), getConnection());
[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 ra2
There are total 8 cases that I can see. viz.
No relationship in config
--
parent PK in SEL child PK in SELResult
--
[1] present presentcorrect
[2] present missingwrong
[3] missing presentwrong
[4] missing missing wrong
Relationship in config
[5] presentpresent correct
[6] presentmissing wrong
[7] missingpresent wrong
[8] missingmissingwrong
-
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; isingers.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; iisongs.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; isingers.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; iisongs.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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 ra2
There are total 8 cases that I can see. viz.
No relationship in config
--
parent PK in SEL child PK in SELResult
--
[1] present presentcorrect
[2] present missingwrong
[3] missing presentwrong
[4] missing missing wrong
Relationship in config
[5] presentpresent correct
[6] presentmissing wrong
[7] missingpresent wrong
[8] missingmissingwrong
-
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; isingers.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; iisongs.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; isingers.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; iisongs.size(); ii++){
System.out.println(SONG TITLE:+
((DataObject)songs.get(ii)).getString(TITLE));
}
}
}
}
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 ra2
There are total 8 cases that I can see. viz.
No relationship in config
--
parent PK in SEL child PK in SELResult
--
[1] present presentcorrect
[2] present missingwrong
[3] missing presentwrong
[4] missing missing wrong
Relationship in config
[5] presentpresent correct
[6] presentmissing wrong
[7] missingpresent wrong
[8] missingmissingwrong
-
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; isingers.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; iisongs.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
Re: [RDB DAS] Wrong Query Result when SELECT misses PKs
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 ra2
There are total 8 cases that I can see. viz.
No relationship in config
--
parent PK in SEL child PK in SELResult
--
[1] present presentcorrect
[2] present missingwrong
[3] missing presentwrong
[4] missing missing wrong
Relationship in config
[5] presentpresent correct
[6] presentmissing wrong
[7] missingpresent wrong
[8] missingmissingwrong
-
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; isingers.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; iisongs.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
