You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Jason A. Lunn" <ja...@fetchfood.com> on 2005/10/27 00:47:23 UTC
M:N storage removes duplicate elements
I have an indirection table foo_bar that happily implements an
m:n relationship between foo and bar. the columns of foo_bar include
foo_id, bar_id, and a unique id for the association itself (mysql
backend, the id column is inserted automatically). This is because it
is relevant to my application to materialize references to the same
object at different indices of the collection upon retrieval.
I started out doing all the insertion into this table by hand. When I
retrieve foo or bar, I successfully get a collection containing all
the expected elements at all the right indices. But now I'm trying to
get more sophisticated, and thus writing an online editor for this
relationship instead of populating the foo_bar table through the
mysql command line. What I'm finding is that OJB is deduping my
collection by ignoring successive references to elements it has
already seen in the current transaction. The result is that I don't
get as many rows inserted into my indirection table as I want.
For instance:
Foo foo1 = new Foo();
Bar bar1 = new Bar();
broker.beginTransaction();
broker.store( bar1 );
broker.store( foo1 );
bar1.getFoos().add( foo1 );
bar1.getFoos().add( foo1 );
broker.store( bar1 );
broker.commitTransaction();
I would expect the above to result in an insert into the foo table,
an insert into the bar table, and 2 insertions into the foo_bar
table. I get the expected behavior in the foo and bar tables, but I
only get one new record into the foo_bar table.
Someone I know suggested that I manually iterate over the collection
using the link() and unlink() methods of BrokerHelper, but I was
convinced that there was a more elegant way to do this, possibly a
configuration file tweak to tell OJB to create multiple links in the
indirection table rather than assuming that I only want one.
Your thoughts appreciated.
- Jason
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: M:N storage removes duplicate elements
Posted by "Jason A. Lunn" <Ja...@fetchfood.com>.
On Oct 26, 2005, at 19:00, Thomas Dudziak wrote:
> On 10/27/05, Jason A. Lunn <ja...@fetchfood.com> wrote:
>
>> I have an indirection table foo_bar that happily implements an
>> m:n relationship between foo and bar. the columns of foo_bar include
>> foo_id, bar_id, and a unique id for the association itself (mysql
>> backend, the id column is inserted automatically). This is because it
>> is relevant to my application to materialize references to the same
>> object at different indices of the collection upon retrieval.
>>
>> I started out doing all the insertion into this table by hand. When I
>> retrieve foo or bar, I successfully get a collection containing all
>> the expected elements at all the right indices. But now I'm trying to
>> get more sophisticated, and thus writing an online editor for this
>> relationship instead of populating the foo_bar table through the
>> mysql command line. What I'm finding is that OJB is deduping my
>> collection by ignoring successive references to elements it has
>> already seen in the current transaction. The result is that I don't
>> get as many rows inserted into my indirection table as I want.
>>
>> For instance:
>>
>> Foo foo1 = new Foo();
>> Bar bar1 = new Bar();
>>
>> broker.beginTransaction();
>> broker.store( bar1 );
>> broker.store( foo1 );
>>
>> bar1.getFoos().add( foo1 );
>> bar1.getFoos().add( foo1 );
>>
>> broker.store( bar1 );
>> broker.commitTransaction();
>>
>> I would expect the above to result in an insert into the foo table,
>> an insert into the bar table, and 2 insertions into the foo_bar
>> table. I get the expected behavior in the foo and bar tables, but I
>> only get one new record into the foo_bar table.
>>
>> Someone I know suggested that I manually iterate over the collection
>> using the link() and unlink() methods of BrokerHelper, but I was
>> convinced that there was a more elegant way to do this, possibly a
>> configuration file tweak to tell OJB to create multiple links in the
>> indirection table rather than assuming that I only want one.
>>
>> Your thoughts appreciated.
>>
>
> How is the indirection table defined in the database ? Is there a
> compound primary key defined over the columns used to refer to the two
> classes ?
> Also, you could configure P6Spy to trace the generated SQL in order to
> see whether OJB or the database ignores the second insert. Details on
> how to configure P6Spy can be found here:
>
> http://db.apache.org/ojb/docu/faq.html#traceProfileSQL
>
> Tom
CREATE TABLE `foo_bar` (
`id` int(11) NOT NULL auto_increment,
`foo_id` int(11) NOT NULL default '0',
`bar_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Here's the output from Pspy:
1130371602114|34|0|statement||select count(*) from foo
1130371602229|3|0|statement||INSERT INTO bar (name) VALUES ('bar1')
1130371602247|4|0|statement||SELECT LAST_INSERT_ID() FROM bar LIMIT 1
1130371602288|2|0|statement||INSERT INTO foo (name) VALUES ('foo1')
1130371602290|1|0|statement||SELECT LAST_INSERT_ID() FROM foo LIMIT 1
1130371602301|2|0|statement||UPDATE bar SET name='bar1' WHERE id = '5'
1130371602323|2|0|statement||SELECT foo_id FROM foo_bar WHERE bar_id='5'
1130371602357|2|0|statement||INSERT INTO foo_bar (bar_id,foo_id)
VALUES ('5','7')
1130371602378|2|0|commit||
- Jason
Re: M:N storage removes duplicate elements
Posted by Thomas Dudziak <to...@gmail.com>.
On 10/27/05, Jason A. Lunn <ja...@fetchfood.com> wrote:
> I have an indirection table foo_bar that happily implements an
> m:n relationship between foo and bar. the columns of foo_bar include
> foo_id, bar_id, and a unique id for the association itself (mysql
> backend, the id column is inserted automatically). This is because it
> is relevant to my application to materialize references to the same
> object at different indices of the collection upon retrieval.
>
> I started out doing all the insertion into this table by hand. When I
> retrieve foo or bar, I successfully get a collection containing all
> the expected elements at all the right indices. But now I'm trying to
> get more sophisticated, and thus writing an online editor for this
> relationship instead of populating the foo_bar table through the
> mysql command line. What I'm finding is that OJB is deduping my
> collection by ignoring successive references to elements it has
> already seen in the current transaction. The result is that I don't
> get as many rows inserted into my indirection table as I want.
>
> For instance:
>
> Foo foo1 = new Foo();
> Bar bar1 = new Bar();
>
> broker.beginTransaction();
> broker.store( bar1 );
> broker.store( foo1 );
>
> bar1.getFoos().add( foo1 );
> bar1.getFoos().add( foo1 );
>
> broker.store( bar1 );
> broker.commitTransaction();
>
> I would expect the above to result in an insert into the foo table,
> an insert into the bar table, and 2 insertions into the foo_bar
> table. I get the expected behavior in the foo and bar tables, but I
> only get one new record into the foo_bar table.
>
> Someone I know suggested that I manually iterate over the collection
> using the link() and unlink() methods of BrokerHelper, but I was
> convinced that there was a more elegant way to do this, possibly a
> configuration file tweak to tell OJB to create multiple links in the
> indirection table rather than assuming that I only want one.
>
> Your thoughts appreciated.
How is the indirection table defined in the database ? Is there a
compound primary key defined over the columns used to refer to the two
classes ?
Also, you could configure P6Spy to trace the generated SQL in order to
see whether OJB or the database ignores the second insert. Details on
how to configure P6Spy can be found here:
http://db.apache.org/ojb/docu/faq.html#traceProfileSQL
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: M:N storage removes duplicate elements
Posted by "Jason A. Lunn" <Ja...@fetchfood.com>.
On Oct 27, 2005, at 05:53, Armin Waibel wrote:
> Hi Jason,
>
> > For instance:
> >
> > Foo foo1 = new Foo();
> > Bar bar1 = new Bar();
> >
> > broker.beginTransaction();
> > broker.store( bar1 );
> > broker.store( foo1 );
> >
> > bar1.getFoos().add( foo1 );
> > bar1.getFoos().add( foo1 );
> >
> > broker.store( bar1 );
> > broker.commitTransaction();
> >
> > I would expect the above to result in an insert into the foo
> table, an
> > insert into the bar table, and 2 insertions into the foo_bar
> table. I
> > get the expected behavior in the foo and bar tables, but I only
> get one
> > new record into the foo_bar table.
>
> I think this is the intended behavior. You add the same foo object
> twice in bar - why and how should OJB differ? You expect 2 entries
> in indirection table for the same bar/foo combination?
>
> How did OJB notice the autoincrement 'id' column in foo_bar?
Armin,
I expect two entries in indirection table for the same bar/foo
combination _at different indices_ of the collection being preserved.
When I turn SQL generation debugging on via log4j like this:
log4j.logger.org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultIm
pl=DEBUG
I get output like the following:
DEBUG [TP-Processor7] (?:?) - SQL:INSERT INTO bar (name) VALUES (?)
DEBUG [TP-Processor7] (?:?) - SQL:INSERT INTO foo (name) VALUES (?)
DEBUG [TP-Processor7] (?:?) - SQL:INSERT INTO foo_bar (bar_id,foo_id)
VALUES (?,?)
DEBUG [TP-Processor7] (?:?) - SQL:INSERT INTO foo_bar (bar_id,foo_id)
VALUES (?,?)
versus the PSpy output for the very same execution:
1130385321625|33|0|statement||select count(*) from foo
1130385321712|2|0|statement||INSERT INTO bar (name) VALUES ('bar1')
1130385321729|4|0|statement||SELECT LAST_INSERT_ID() FROM bar LIMIT 1
1130385321768|2|0|statement||INSERT INTO foo (name) VALUES ('foo1')
1130385321770|2|0|statement||SELECT LAST_INSERT_ID() FROM foo LIMIT 1
1130385321774|2|0|statement||INSERT INTO foo (name) VALUES ('foo2')
1130385321776|2|0|statement||SELECT LAST_INSERT_ID() FROM foo LIMIT 1
1130385321808|2|0|statement||INSERT INTO foo_bar (bar_id,foo_id)
VALUES ('14','17')
1130385321836|1|0|commit||
So it seems to me that OJB is suppressing an insertion into the
indirection table. My educated guess was that perhaps within the
transaction it was checking to see if it had already inserted a value
in the indirection table for that bar/foo combination, and not
performing the insertion the 2nd - Nth times it encounters that pairing.
- Jason
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: M:N storage removes duplicate elements
Posted by Armin Waibel <ar...@apache.org>.
Hi Jason,
> For instance:
>
> Foo foo1 = new Foo();
> Bar bar1 = new Bar();
>
> broker.beginTransaction();
> broker.store( bar1 );
> broker.store( foo1 );
>
> bar1.getFoos().add( foo1 );
> bar1.getFoos().add( foo1 );
>
> broker.store( bar1 );
> broker.commitTransaction();
>
> I would expect the above to result in an insert into the foo table, an
> insert into the bar table, and 2 insertions into the foo_bar table. I
> get the expected behavior in the foo and bar tables, but I only get one
> new record into the foo_bar table.
I think this is the intended behavior. You add the same foo object twice
in bar - why and how should OJB differ? You expect 2 entries in
indirection table for the same bar/foo combination?
How did OJB notice the autoincrement 'id' column in foo_bar?
regards,
Armin
Jason A. Lunn wrote:
> I have an indirection table foo_bar that happily implements an m:n
> relationship between foo and bar. the columns of foo_bar include
> foo_id, bar_id, and a unique id for the association itself (mysql
> backend, the id column is inserted automatically). This is because it
> is relevant to my application to materialize references to the same
> object at different indices of the collection upon retrieval.
>
> I started out doing all the insertion into this table by hand. When I
> retrieve foo or bar, I successfully get a collection containing all the
> expected elements at all the right indices. But now I'm trying to get
> more sophisticated, and thus writing an online editor for this
> relationship instead of populating the foo_bar table through the mysql
> command line. What I'm finding is that OJB is deduping my collection by
> ignoring successive references to elements it has already seen in the
> current transaction. The result is that I don't get as many rows
> inserted into my indirection table as I want.
>
> For instance:
>
> Foo foo1 = new Foo();
> Bar bar1 = new Bar();
>
> broker.beginTransaction();
> broker.store( bar1 );
> broker.store( foo1 );
>
> bar1.getFoos().add( foo1 );
> bar1.getFoos().add( foo1 );
>
> broker.store( bar1 );
> broker.commitTransaction();
>
> I would expect the above to result in an insert into the foo table, an
> insert into the bar table, and 2 insertions into the foo_bar table. I
> get the expected behavior in the foo and bar tables, but I only get one
> new record into the foo_bar table.
>
> Someone I know suggested that I manually iterate over the collection
> using the link() and unlink() methods of BrokerHelper, but I was
> convinced that there was a more elegant way to do this, possibly a
> configuration file tweak to tell OJB to create multiple links in the
> indirection table rather than assuming that I only want one.
>
> Your thoughts appreciated.
>
> - Jason
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org