[DAS] Relationship info using DBMS Metadata getCrossReference() - pros and cons?
(Prev mail thread:- http://www.mail-archive.com/[EMAIL PROTECTED]/msg20770.html) More on this... Looks like, single table registry approach was taken to dump all rows fetcted from database without linking DataObjects, as there is no relationship information in DAS Config. So, from the above mail thread, as join between singer and song returns 3 rows, there were 3 singer DOs and 3 song DOs and it is user's responsibility to see that out of 3 singer DOs 2 are actually identical. So question here is...can DAS use DatabaseMetadata.getCrossReference() JDBC API to form relationship information when it is missing in DAS Config. To keep matter simple, DAS can take approach of using DAS Config relationship info as first priority and DatabaseMetadata.getCrossReference() as next priority. In case queries involve tables where for some relationship is defined in DAS Config and for some it is not, DAS can just stick to DAS Config. i.e. avoid mixing DAS Config provided relationship info and DBMS provided relationship info. Whatever is the approach (we can take mixed approach too, for that matter), it just needs to be documented clearly. DAS - from the basic usage so far, promotes less config and thus uses DBMS Metadata info for tables and columns when one is not available in DAS Config. So same can be extended for Relationships too. Are there any known issues around usage of DatabaseMetadata.getCrossReference()? Checked for Derby, MySQL so far and found no issues. Also, if some vendor do not have implementation for this method, we can detect exception/null/empty ResultSet returns and continue the way we are at present using SingleTableRegistry. Comments? Regards, Amita
Re: DAS Relationship
Yes. That looks good except you will also need to define the compound primary key for the join table. On 4/20/07, Paulo Henrique Trecenti [EMAIL PROTECTED] wrote: Like that's Table tableName=RESERVE typeName=Reserve Column columnName=ID propertyName=reserveID primaryKey=true / Column columnName=ROOM_ID propertyName=roomID / Column columnName=EMAIL propertyName=email/ Column columnName=DATEHOUR propertyName=dateHour/ Column columnName=REASON propertyName=reasom/ /Table Table tableName=EQUIPMENT typeName=Equipment Column columnName=ID propertyName=equipmentID primaryKey=true generated=true / Column columnName=NAME propertyName=name / Column columnName=QUANTITY propertyName=quantity/ Column columnName=TYPE propertyName=type/ /Table Table tableName=RESERVE_EQUIPMENTS Column columnName=RESERVE_ID propertyName=reserveID / Column columnName=EQUIPMENT_ID propertyName=equipmentID / /Table Relationship name=reserveEquipments primaryKeyTable=RESERVE foreignKeyTable=RESERVE_EQUIPMENTS many=true KeyPair primaryKeyColumn=ID foreignKeyColumn=RESERVE_ID / /Relationship Relationship name=equipmentReserves primaryKeyTable=EQUIPMENT foreignKeyTable=RESERVE_EQUIPMENTS many=true KeyPair primaryKeyColumn=ID foreignKeyColumn=EQUIPMENT_ID / /Relationship 2007/4/20, Kevin Williams [EMAIL PROTECTED]: Hello Paulo, The RDB DAS does not yet directly support many to many relationships. YOu can work around this by using two 1:m relationships where both Reserve and Equipment has 1:m relationship with Reserve_Equipment. So, you have to directly model the junction table. --Kevin On 4/20/07, Paulo Henrique Trecenti [EMAIL PROTECTED] wrote: Hi, I'm try using the DAS Relationship but I can not undestand, in my case I try make this _ __ | Reserve | *_*| Equipment | || ||__| | ___| | Reserve_Equipments | |__ | Ok, one Reserve have many equipments, one equipment can be used by many Reservers then I have a relatonship many to many. My config file is Table tableName=RESERVE typeName=Reserve Column columnName=ID propertyName=reserveID primaryKey=true / Column columnName=ROOM_ID propertyName=roomID / Column columnName=EMAIL propertyName=email/ Column columnName=DATEHOUR propertyName=dateHour/ Column columnName=REASON propertyName=reasom/ /Table Table tableName=EQUIPMENT typeName=Equipment Column columnName=ID propertyName=equipmentID primaryKey=true generated=true / Column columnName=NAME propertyName=name / Column columnName=QUANTITY propertyName=quantity/ Column columnName=TYPE propertyName=type/ /Table Relationship name=reserveEquipments primaryKeyTable=RESERVE foreignKeyTable=EQUIPMENT many=true KeyPair primaryKeyColumn=ID foreignKeyColumn=EQUIPMENT_ID / /Relationship This is correct ? If yes how I can insert into table RESERVE_EQUIPMENTS using DataObject with ChangeSummary ? -- Paulo Henrique Trecenti -- Paulo Henrique Trecenti
DAS Relationship
Hi, I'm try using the DAS Relationship but I can not undestand, in my case I try make this _ __ | Reserve | *_*| Equipment | || ||__| | ___| | Reserve_Equipments | |__ | Ok, one Reserve have many equipments, one equipment can be used by many Reservers then I have a relatonship many to many. My config file is Table tableName=RESERVE typeName=Reserve Column columnName=ID propertyName=reserveID primaryKey=true / Column columnName=ROOM_ID propertyName=roomID / Column columnName=EMAIL propertyName=email/ Column columnName=DATEHOUR propertyName=dateHour/ Column columnName=REASON propertyName=reasom/ /Table Table tableName=EQUIPMENT typeName=Equipment Column columnName=ID propertyName=equipmentID primaryKey=true generated=true / Column columnName=NAME propertyName=name / Column columnName=QUANTITY propertyName=quantity/ Column columnName=TYPE propertyName=type/ /Table Relationship name=reserveEquipments primaryKeyTable=RESERVE foreignKeyTable=EQUIPMENT many=true KeyPair primaryKeyColumn=ID foreignKeyColumn=EQUIPMENT_ID / /Relationship This is correct ? If yes how I can insert into table RESERVE_EQUIPMENTS using DataObject with ChangeSummary ? -- Paulo Henrique Trecenti
Re: DAS Relationship
Hello Paulo, The RDB DAS does not yet directly support many to many relationships. YOu can work around this by using two 1:m relationships where both Reserve and Equipment has 1:m relationship with Reserve_Equipment. So, you have to directly model the junction table. --Kevin On 4/20/07, Paulo Henrique Trecenti [EMAIL PROTECTED] wrote: Hi, I'm try using the DAS Relationship but I can not undestand, in my case I try make this _ __ | Reserve | *_*| Equipment | || ||__| | ___| | Reserve_Equipments | |__ | Ok, one Reserve have many equipments, one equipment can be used by many Reservers then I have a relatonship many to many. My config file is Table tableName=RESERVE typeName=Reserve Column columnName=ID propertyName=reserveID primaryKey=true / Column columnName=ROOM_ID propertyName=roomID / Column columnName=EMAIL propertyName=email/ Column columnName=DATEHOUR propertyName=dateHour/ Column columnName=REASON propertyName=reasom/ /Table Table tableName=EQUIPMENT typeName=Equipment Column columnName=ID propertyName=equipmentID primaryKey=true generated=true / Column columnName=NAME propertyName=name / Column columnName=QUANTITY propertyName=quantity/ Column columnName=TYPE propertyName=type/ /Table Relationship name=reserveEquipments primaryKeyTable=RESERVE foreignKeyTable=EQUIPMENT many=true KeyPair primaryKeyColumn=ID foreignKeyColumn=EQUIPMENT_ID / /Relationship This is correct ? If yes how I can insert into table RESERVE_EQUIPMENTS using DataObject with ChangeSummary ? -- Paulo Henrique Trecenti