Thread: RE: [OJB-developers] Field descriptor order requirement and primary keys
Brought to you by:
thma
From: John F. <jfr...@so...> - 2002-04-02 21:49:27
|
I guess I'm confused. In my existing database schema, the primary key column(s) are not always the first ones in the table. Also, I sometimes have "extra" columns in a table that I don't want OJB to do anything with so they aren't even in the repository.xml as it would just slow things down more. Is this allowed as long as those columns have default values to handle any sql inserts OJB would perform? With that background, what should I be setting my id=3D"" attributes of the FieldDescriptor's to? They can't possibly be the order of the columns in the table because then the one with <PrimaryKey>true</PrimaryKey> will not have id=3D"1" as it is the 4th column in the table. From the RsIterator code snippet below, there is an assumption that the primary keys are always first in the m_row member. And the ordering of the m_row member seems to correspond to the id=3D"" numbers from the = field descriptor. So, my assertion is that if you have a field descriptor with <PrimaryKey>true</PrimaryKey> and it is not id=3D"1" then OJB doesn't work. And by extension if you had a compound primary key with multiple field descriptors having <PrimaryKey> elements, they would need to be 1, 2, 3, etc. Is that just the way it is designed? I don't see this documented anywhere, but I may have missed it of course. If this is true, I'll volunteer to add some checking of this during the parsing of repository.xml as I think this is a pretty error prone part of mapping. Thanks, - John -----Original Message----- From: Thomas Mahler [mailto:tho...@ho...] Sent: Tuesday, April 02, 2002 2:58 PM To: John Freeborg Cc: Obj...@li... Subject: Re: [OJB-developers] Field descriptor order requirement and primary keys Hi John, FieldDescriptor need a numeric ID. The Ids must ordered ascending=20 according to the corresponding columns in the database table. The physical position in the xml repository is not important. HTH, Thomas There is some code that John Freeborg wrote: > Does OJB have a requirement that in the repository.xml file, all primary > key field descriptors must be first inside a class descriptor? >=20 > I don't have it that way (my primary key field descriptor happened to be > number 4, not 1). After scratching my head a lot on why my query for a > collection looked so strange, I hauled out BugSeeker and found the > following code marked with ---> below that seems to assume a certain > primary key field descriptor order. Interestingly the line above that > is commented out doesn't appear to have this problem. >=20 > Is this an unintended side effect of the optimization? Is this broken? >=20 > Thanks, > - John >=20 >=20 >>From RsIterator.java in 0.8.361: >=20 > protected Identity getIdentityFromResultSet() throws > PersistenceBrokerException > { > try > { > // fill primary key values from Resultset > FieldDescriptor fld; > FieldDescriptor[] pkFields =3D m_mif.getPkFields(); > Object[] pkValues =3D new Object[pkFields.length]; >=20 > for (int i =3D 0; i < pkFields.length; i++) > { > fld =3D pkFields[i]; > //pkValues[i] =3D JdbcAccess.getObjectFromColumn(m_rs, > fld); > ---> pkValues[i] =3D m_row[i]; > } > // return identity object build up from primary keys > return new Identity(getExtentClass(), pkValues); > } > catch (Throwable t) > { > logger.error(t); > throw new PersistenceBrokerException(t); > } > } >=20 > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers >=20 |
From: John F. <jfr...@so...> - 2002-04-03 16:45:02
Attachments:
testcase.zip
|
Thank you for your detailed reply Thomas! However, there is I believe definitely a bug here, but I'm not communicating it effectively so I created a simple test case based on your example showing the problem. All the necessary files are attached in the zip. When you select all the products that have a value of 1 you should get these 3: Cars BMW 34000 1 BMW 330ci 1 Cars Volvo 23000 2 Volvo 990 1 Boats Swan 2500000 4 55 foot 1 But when you use OJB you don't. You get: Product[id=3D1,name=3DBMW,price=3D34000,stock=3D1category=3DCars] Product[id=3D1,name=3DBMW,price=3D34000,stock=3D1category=3DCars] Product[id=3D4,name=3DSwan,price=3D2500000,stock=3D1category=3DBoats] When I dug into this using my debugger, I found that the snippet of code in RsIterator.java was the problem (or appeared to be - perhaps the root problem lies elsewhere). Hope this helps illustrate the problem with the code that I'm seeing. Just trying to help make OJB even better! I'm using 0.8.361, but I uncommented some of the logging stuff in the JdbcAccess just to get more info on what was going on. SQL Server 2000 w/ SP3 and NetDirect JSQLConnect JDBC drivers. Regards, - John -----Original Message----- From: Thomas Mahler [mailto:tho...@ho...] Sent: Wednesday, April 03, 2002 5:13 AM To: John Freeborg Cc: Obj...@li... Subject: Re: [OJB-developers] Field descriptor order requirement and primary keys Hi John, John Freeborg wrote: > I guess I'm confused. >=20 > In my existing database schema, the primary key column(s) are not always > the first ones in the table. No problem. >=20 > Also, I sometimes have "extra" columns in a table that I don't want OJB > to do anything with so they aren't even in the repository.xml as it > would just slow things down more.=20 No problem. > Is this allowed as long as those > columns have default values to handle any sql inserts OJB would perform? >=20 OJB won't touch these columns, as it does not know anything about them! > With that background, what should I be setting my id=3D"" attributes = of > the FieldDescriptor's to? They can't possibly be the order of the > columns in the table because then the one with > <PrimaryKey>true</PrimaryKey> will not have id=3D"1" as it is the 4th > column in the table. >=20 Say we have this table: CREATE TABLE PRODUCT ( NAME CHAR(100), PRICE DOUBLE, ID INT PRIMARY KEY, HIDDEN VARCHAR(1000), STOCK INT ) This is the persistent class: public class Product { private int id; protected String name; protected double price; // column HIDDEN is not mapped to any attribute protected int stock; } This is the ClassDescriptor: <ClassDescriptor id=3D"product"> <class.name>tutorial.Product</class.name> <table.name>PRODUCT</table.name> <FieldDescriptor id=3D"1"> <field.name>name</field.name> <column.name>NAME</column.name> <jdbc_type>CHAR</jdbc_type> </FieldDescriptor> <FieldDescriptor id=3D"2"> <field.name>price</field.name> <column.name>PRICE</column.name> <jdbc_type>DOUBLE</jdbc_type> </FieldDescriptor> <FieldDescriptor id=3D"3"> <field.name>id</field.name> <column.name>ID</column.name> <jdbc_type>INT</jdbc_type> <PrimaryKey>true</PrimaryKey> <autoincrement>true</autoincrement> </FieldDescriptor> <FieldDescriptor id=3D"4"> <field.name>stock</field.name> <column.name>STOCK</column.name> <jdbc_type>INT</jdbc_type> </FieldDescriptor> </ClassDescriptor> The ids of the FieldDescriptors correspond directly to the *order* of=20 the mapped table columns. E.g. the stock column is *not* the fourth=20 column of the table. but it is the fourth *mapped* column. >>From the RsIterator code snippet below, there is an assumption that the > primary keys are always first in the m_row member.=20 this would be an uneccessary restriction! > And the ordering of > the m_row member seems to correspond to the id=3D"" numbers from the field > descriptor. That's right >=20 > So, my assertion is that if you have a field descriptor with > <PrimaryKey>true</PrimaryKey> and it is not id=3D"1" then OJB doesn't > work. =20 As long as you maintain the order of the mapped columns in the FK ids=20 OJB does work! > And by extension if you had a compound primary key with multiple > field descriptors having <PrimaryKey> elements, they would need to be 1, > 2, 3, etc. they might as well be 4, 17, 23 ! > Is that just the way it is designed? I don't see this documented > anywhere, but I may have missed it of course. >=20 I thought it was documented somewhere, but did not find it :-( > If this is true, I'll volunteer to add some checking of this during the > parsing of repository.xml as I think this is a pretty error prone part > of mapping. >=20 Maybe it would be the best thing to eliminate the id based lookup of=20 columns as there are several things like freeform SQL queries that don't work well with this approach. cu, Thomas > Thanks, > - John >=20 >=20 > -----Original Message----- > From: Thomas Mahler [mailto:tho...@ho...] > Sent: Tuesday, April 02, 2002 2:58 PM > To: John Freeborg > Cc: Obj...@li... > Subject: Re: [OJB-developers] Field descriptor order requirement and > primary keys >=20 > Hi John, >=20 > FieldDescriptor need a numeric ID. The Ids must ordered ascending=20 > according to the corresponding columns in the database table. >=20 > The physical position in the xml repository is not important. >=20 > HTH, >=20 > Thomas >=20 > There is some code that >=20 > John Freeborg wrote: >=20 >=20 >>Does OJB have a requirement that in the repository.xml file, all >> > primary >=20 >>key field descriptors must be first inside a class descriptor? >> >>I don't have it that way (my primary key field descriptor happened to >> > be >=20 >>number 4, not 1). After scratching my head a lot on why my query for >> > a >=20 >>collection looked so strange, I hauled out BugSeeker and found the >>following code marked with ---> below that seems to assume a certain >>primary key field descriptor order. Interestingly the line above that >>is commented out doesn't appear to have this problem. >> >>Is this an unintended side effect of the optimization? Is this >> > broken? >=20 >>Thanks, >> - John >> >> >>>From RsIterator.java in 0.8.361: >> >> protected Identity getIdentityFromResultSet() throws >>PersistenceBrokerException >> { >> try >> { >> // fill primary key values from Resultset >> FieldDescriptor fld; >> FieldDescriptor[] pkFields =3D m_mif.getPkFields(); >> Object[] pkValues =3D new Object[pkFields.length]; >> >> for (int i =3D 0; i < pkFields.length; i++) >> { >> fld =3D pkFields[i]; >> //pkValues[i] =3D JdbcAccess.getObjectFromColumn(m_rs, >>fld); >>---> pkValues[i] =3D m_row[i]; >> } >> // return identity object build up from primary keys >> return new Identity(getExtentClass(), pkValues); >> } >> catch (Throwable t) >> { >> logger.error(t); >> throw new PersistenceBrokerException(t); >> } >> } |
From: Jakob B. <jbr...@ho...> - 2002-04-04 06:46:41
|
hi, i can reproduce this behaviour. it's caused by an error in RsIterator method getIdentityFromResultSet(): the pkValues are taken from the wron position in the row: ... for (int i = 0; i < pkFields.length; i++) { fld = pkFields[i]; pkValues[i] = m_row[fld.getColNo() - 1]; //pkValues[i] = m_row[i]; DOES NOT WORK } // return identity object build up from primary keys return new Identity(getExtentClass(), pkValues); ... hth jakob ----- Original Message ----- From: "John Freeborg" <jfr...@so...> To: "Thomas Mahler" <tho...@ho...> Cc: <Obj...@li...> Sent: Wednesday, April 03, 2002 6:44 PM Subject: RE: [OJB-developers] Field descriptor order requirement and primary keys Thank you for your detailed reply Thomas! However, there is I believe definitely a bug here, but I'm not communicating it effectively so I created a simple test case based on your example showing the problem. All the necessary files are attached in the zip. When you select all the products that have a value of 1 you should get these 3: Cars BMW 34000 1 BMW 330ci 1 Cars Volvo 23000 2 Volvo 990 1 Boats Swan 2500000 4 55 foot 1 But when you use OJB you don't. You get: Product[id=1,name=BMW,price=34000,stock=1category=Cars] Product[id=1,name=BMW,price=34000,stock=1category=Cars] Product[id=4,name=Swan,price=2500000,stock=1category=Boats] When I dug into this using my debugger, I found that the snippet of code in RsIterator.java was the problem (or appeared to be - perhaps the root problem lies elsewhere). Hope this helps illustrate the problem with the code that I'm seeing. Just trying to help make OJB even better! I'm using 0.8.361, but I uncommented some of the logging stuff in the JdbcAccess just to get more info on what was going on. SQL Server 2000 w/ SP3 and NetDirect JSQLConnect JDBC drivers. Regards, - John -----Original Message----- From: Thomas Mahler [mailto:tho...@ho...] Sent: Wednesday, April 03, 2002 5:13 AM To: John Freeborg Cc: Obj...@li... Subject: Re: [OJB-developers] Field descriptor order requirement and primary keys Hi John, John Freeborg wrote: > I guess I'm confused. > > In my existing database schema, the primary key column(s) are not always > the first ones in the table. No problem. > > Also, I sometimes have "extra" columns in a table that I don't want OJB > to do anything with so they aren't even in the repository.xml as it > would just slow things down more. No problem. > Is this allowed as long as those > columns have default values to handle any sql inserts OJB would perform? > OJB won't touch these columns, as it does not know anything about them! > With that background, what should I be setting my id="" attributes of > the FieldDescriptor's to? They can't possibly be the order of the > columns in the table because then the one with > <PrimaryKey>true</PrimaryKey> will not have id="1" as it is the 4th > column in the table. > Say we have this table: CREATE TABLE PRODUCT ( NAME CHAR(100), PRICE DOUBLE, ID INT PRIMARY KEY, HIDDEN VARCHAR(1000), STOCK INT ) This is the persistent class: public class Product { private int id; protected String name; protected double price; // column HIDDEN is not mapped to any attribute protected int stock; } This is the ClassDescriptor: <ClassDescriptor id="product"> <class.name>tutorial.Product</class.name> <table.name>PRODUCT</table.name> <FieldDescriptor id="1"> <field.name>name</field.name> <column.name>NAME</column.name> <jdbc_type>CHAR</jdbc_type> </FieldDescriptor> <FieldDescriptor id="2"> <field.name>price</field.name> <column.name>PRICE</column.name> <jdbc_type>DOUBLE</jdbc_type> </FieldDescriptor> <FieldDescriptor id="3"> <field.name>id</field.name> <column.name>ID</column.name> <jdbc_type>INT</jdbc_type> <PrimaryKey>true</PrimaryKey> <autoincrement>true</autoincrement> </FieldDescriptor> <FieldDescriptor id="4"> <field.name>stock</field.name> <column.name>STOCK</column.name> <jdbc_type>INT</jdbc_type> </FieldDescriptor> </ClassDescriptor> The ids of the FieldDescriptors correspond directly to the *order* of the mapped table columns. E.g. the stock column is *not* the fourth column of the table. but it is the fourth *mapped* column. >>From the RsIterator code snippet below, there is an assumption that the > primary keys are always first in the m_row member. this would be an uneccessary restriction! > And the ordering of > the m_row member seems to correspond to the id="" numbers from the field > descriptor. That's right > > So, my assertion is that if you have a field descriptor with > <PrimaryKey>true</PrimaryKey> and it is not id="1" then OJB doesn't > work. As long as you maintain the order of the mapped columns in the FK ids OJB does work! > And by extension if you had a compound primary key with multiple > field descriptors having <PrimaryKey> elements, they would need to be 1, > 2, 3, etc. they might as well be 4, 17, 23 ! > Is that just the way it is designed? I don't see this documented > anywhere, but I may have missed it of course. > I thought it was documented somewhere, but did not find it :-( > If this is true, I'll volunteer to add some checking of this during the > parsing of repository.xml as I think this is a pretty error prone part > of mapping. > Maybe it would be the best thing to eliminate the id based lookup of columns as there are several things like freeform SQL queries that don't work well with this approach. cu, Thomas > Thanks, > - John > > > -----Original Message----- > From: Thomas Mahler [mailto:tho...@ho...] > Sent: Tuesday, April 02, 2002 2:58 PM > To: John Freeborg > Cc: Obj...@li... > Subject: Re: [OJB-developers] Field descriptor order requirement and > primary keys > > Hi John, > > FieldDescriptor need a numeric ID. The Ids must ordered ascending > according to the corresponding columns in the database table. > > The physical position in the xml repository is not important. > > HTH, > > Thomas > > There is some code that > > John Freeborg wrote: > > >>Does OJB have a requirement that in the repository.xml file, all >> > primary > >>key field descriptors must be first inside a class descriptor? >> >>I don't have it that way (my primary key field descriptor happened to >> > be > >>number 4, not 1). After scratching my head a lot on why my query for >> > a > >>collection looked so strange, I hauled out BugSeeker and found the >>following code marked with ---> below that seems to assume a certain >>primary key field descriptor order. Interestingly the line above that >>is commented out doesn't appear to have this problem. >> >>Is this an unintended side effect of the optimization? Is this >> > broken? > >>Thanks, >> - John >> >> >>>From RsIterator.java in 0.8.361: >> >> protected Identity getIdentityFromResultSet() throws >>PersistenceBrokerException >> { >> try >> { >> // fill primary key values from Resultset >> FieldDescriptor fld; >> FieldDescriptor[] pkFields = m_mif.getPkFields(); >> Object[] pkValues = new Object[pkFields.length]; >> >> for (int i = 0; i < pkFields.length; i++) >> { >> fld = pkFields[i]; >> //pkValues[i] = JdbcAccess.getObjectFromColumn(m_rs, >>fld); >>---> pkValues[i] = m_row[i]; >> } >> // return identity object build up from primary keys >> return new Identity(getExtentClass(), pkValues); >> } >> catch (Throwable t) >> { >> logger.error(t); >> throw new PersistenceBrokerException(t); >> } >> } |
From: <ll...@li...> - 2002-04-03 06:33:13
|
Time for an extra mailing list ? I propose that a objectbridge-users mailing-list is made. And that all users from this list initially is subscribed to it. /Lasse |
From: Thomas M. <tho...@ho...> - 2002-04-04 09:57:15
|
Hi All, OJB is gathering more and more attention. That is very good! Until now we managed to have all communication in one mailing list. I agree to Lasse that it's time to start a new mailing list for all OJB user related topics (questions, help requests, bug reports, feature requests, etc.) I just setup this objectbridge-users list. All OJB users that do not contribute actively to the code base are requested to subscribe to this list and to post their messages to this list only. To subcribe follow=20 this link: http://lists.sourceforge.net/lists/listinfo/objectbridge-users OJB developers are requested to also subcribe to this new list to answer questions and help users. Thanks, Thomas Lasse Lindg=E5rd wrote: > Time for an extra mailing list ? > > I propose that a objectbridge-users mailing-list is made. And that all > users from this list initially is subscribed to it. > > /Lasse > > > > > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers > > > > |
From: Thomas M. <tho...@ho...> - 2002-04-03 10:13:12
|
Hi John, John Freeborg wrote: > I guess I'm confused. > > In my existing database schema, the primary key column(s) are not always > the first ones in the table. No problem. > > Also, I sometimes have "extra" columns in a table that I don't want OJB > to do anything with so they aren't even in the repository.xml as it > would just slow things down more. No problem. > Is this allowed as long as those > columns have default values to handle any sql inserts OJB would perform? > OJB won't touch these columns, as it does not know anything about them! > With that background, what should I be setting my id="" attributes of > the FieldDescriptor's to? They can't possibly be the order of the > columns in the table because then the one with > <PrimaryKey>true</PrimaryKey> will not have id="1" as it is the 4th > column in the table. > Say we have this table: CREATE TABLE PRODUCT ( NAME CHAR(100), PRICE DOUBLE, ID INT PRIMARY KEY, HIDDEN VARCHAR(1000), STOCK INT ) This is the persistent class: public class Product { private int id; protected String name; protected double price; // column HIDDEN is not mapped to any attribute protected int stock; } This is the ClassDescriptor: <ClassDescriptor id="product"> <class.name>tutorial.Product</class.name> <table.name>PRODUCT</table.name> <FieldDescriptor id="1"> <field.name>name</field.name> <column.name>NAME</column.name> <jdbc_type>CHAR</jdbc_type> </FieldDescriptor> <FieldDescriptor id="2"> <field.name>price</field.name> <column.name>PRICE</column.name> <jdbc_type>DOUBLE</jdbc_type> </FieldDescriptor> <FieldDescriptor id="3"> <field.name>id</field.name> <column.name>ID</column.name> <jdbc_type>INT</jdbc_type> <PrimaryKey>true</PrimaryKey> <autoincrement>true</autoincrement> </FieldDescriptor> <FieldDescriptor id="4"> <field.name>stock</field.name> <column.name>STOCK</column.name> <jdbc_type>INT</jdbc_type> </FieldDescriptor> </ClassDescriptor> The ids of the FieldDescriptors correspond directly to the *order* of the mapped table columns. E.g. the stock column is *not* the fourth column of the table. but it is the fourth *mapped* column. >>From the RsIterator code snippet below, there is an assumption that the > primary keys are always first in the m_row member. this would be an uneccessary restriction! > And the ordering of > the m_row member seems to correspond to the id="" numbers from the field > descriptor. That's right > > So, my assertion is that if you have a field descriptor with > <PrimaryKey>true</PrimaryKey> and it is not id="1" then OJB doesn't > work. As long as you maintain the order of the mapped columns in the FK ids OJB does work! > And by extension if you had a compound primary key with multiple > field descriptors having <PrimaryKey> elements, they would need to be 1, > 2, 3, etc. they might as well be 4, 17, 23 ! > Is that just the way it is designed? I don't see this documented > anywhere, but I may have missed it of course. > I thought it was documented somewhere, but did not find it :-( > If this is true, I'll volunteer to add some checking of this during the > parsing of repository.xml as I think this is a pretty error prone part > of mapping. > Maybe it would be the best thing to eliminate the id based lookup of columns as there are several things like freeform SQL queries that don't work well with this approach. cu, Thomas > Thanks, > - John > > > -----Original Message----- > From: Thomas Mahler [mailto:tho...@ho...] > Sent: Tuesday, April 02, 2002 2:58 PM > To: John Freeborg > Cc: Obj...@li... > Subject: Re: [OJB-developers] Field descriptor order requirement and > primary keys > > Hi John, > > FieldDescriptor need a numeric ID. The Ids must ordered ascending > according to the corresponding columns in the database table. > > The physical position in the xml repository is not important. > > HTH, > > Thomas > > There is some code that > > John Freeborg wrote: > > >>Does OJB have a requirement that in the repository.xml file, all >> > primary > >>key field descriptors must be first inside a class descriptor? >> >>I don't have it that way (my primary key field descriptor happened to >> > be > >>number 4, not 1). After scratching my head a lot on why my query for >> > a > >>collection looked so strange, I hauled out BugSeeker and found the >>following code marked with ---> below that seems to assume a certain >>primary key field descriptor order. Interestingly the line above that >>is commented out doesn't appear to have this problem. >> >>Is this an unintended side effect of the optimization? Is this >> > broken? > >>Thanks, >> - John >> >> >>>From RsIterator.java in 0.8.361: >> >> protected Identity getIdentityFromResultSet() throws >>PersistenceBrokerException >> { >> try >> { >> // fill primary key values from Resultset >> FieldDescriptor fld; >> FieldDescriptor[] pkFields = m_mif.getPkFields(); >> Object[] pkValues = new Object[pkFields.length]; >> >> for (int i = 0; i < pkFields.length; i++) >> { >> fld = pkFields[i]; >> //pkValues[i] = JdbcAccess.getObjectFromColumn(m_rs, >>fld); >>---> pkValues[i] = m_row[i]; >> } >> // return identity object build up from primary keys >> return new Identity(getExtentClass(), pkValues); >> } >> catch (Throwable t) >> { >> logger.error(t); >> throw new PersistenceBrokerException(t); >> } >> } >> >>_______________________________________________ >>Objectbridge-developers mailing list >>Obj...@li... >>https://lists.sourceforge.net/lists/listinfo/objectbridge-developers >> >> > > _______________________________________________ > Objectbridge-developers mailing list > Obj...@li... > https://lists.sourceforge.net/lists/listinfo/objectbridge-developers > > > > |