You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Daniel Doppmeier <dd...@cebitec.uni-bielefeld.de> on 2008/05/06 10:17:40 UTC
many-to-many mapping resulting in incomplete SQL queries
Hello everyone!
I am new to cayenne and experiencing some problems, when modelling a
many-to-many relationship. After I could not solve the problem for an
application I am writing at the moment, I set up the following example
application, which is producing the same problems:
I modelled a small bookstore application, with two tables "Book" and
"Person". After that I created a join table "Person_is_author_of_book",
to map from "Person" entries to "Book" entries. On the object side of
the application, this would mean, that every book may have several
authors, whereas one author may have written more than one book.
Inserting and fetching data from the DB seems to work just fine, but
when it comes to deletion there occures my problem. This is what happens
in my code:
DataContext context = DataContext.createDataContext();
Book book1 = (Book) context.newObject(Book.class);
book1.setTitle("The secret life of Donald D");
Person author1 = (Person) context.newObject(Person.class);
author1.setName("Huey");
Person author2 = (Person) context.newObject(Person.class);
author2.setName("Dewey");
context.commitChanges();
author1.removeFromBooks(book1);
context.commitChanges();
Everything is fine until the second commitChanges(), when I try to
delete book1 from author1's list.
This is what the logger says about it:
INFO QueryLogger: --- will run 1 query.
INFO QueryLogger: --- transaction started.
INFO QueryLogger: DELETE FROM Person_is_author_of_book WHERE
INFO QueryLogger: *** error.
As you can see, there is missing some SQL syntax after the WHERE clause.
Does anybody have an idea, what may cause this malformed SQL?
I am using the latest stable cayenne version 2.0.4, MySQL 5.0.51a-3ubuntu5 and java "1.6.0_06"
Thanks a lot for any help!
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Daniel Doppmeier <dd...@cebitec.uni-bielefeld.de>.
You have a good point there. Thanks for the advice. I guess I would just
have run into that problem later. Maybe it would be a good idea to add
this advice to the user guide, where flattened relationships are
discussed. It took me, as a cayenne beginner, some time to figure out
how it is working. One emerging problem was solved in this conversation.
Maybe others will experience the same problems.
Michael Gentry schrieb:
> Daniel,
>
> I just re-ran your app after unchecking personID in your join table
> (only bookID was set as the primary key). That causes SQL like this
> to be produced:
>
> INFO: DELETE FROM Person_is_author_of_book WHERE bookID = ?
> May 12, 2008 2:45:31 PM org.apache.cayenne.access.QueryLogger logQueryParameters
> INFO: [bind: 1->bookID:3]
>
> That is incorrect for what you want. You need to make both a primary
> key. If not, it will delete too many records (potentially) since it
> is not qualifying the personID.
>
> /dev/mrg
>
>
>
>> Hi
>>
>> Thanks again. Setting at least one of the attributes as primary key solved
>> the problem. As far as I tested, it is not important which of the attributes
>> is assigned the primary key, but there has to be at least one PK. The PK
>> generation strategy is set to default by the modeller. Any changes to that
>> setting do not seem to affect the generated code, so everything seems to
>> work just as it should by now.
>>
>>
>> Michael Gentry schrieb:
>>
>>
>>
>>
>>> [followup back to the user list]
>>>
>>> Hi Daniel,
>>>
>>> The first time I ran your test application it did indeed crash with
>>> the missing WHERE clause (this is on Cayenne 3.x, too). I went into
>>> your Cayenne Model, though, and on your Person_is_author_of_book table
>>> (you didn't map it as a class, which is cool since you are flattening
>>> it), I checked the PK/Mandatory checkboxes under the Attributes tab
>>> for the DbEntity. Then I re-ran the application and it worked:
>>>
>>> INFO: DELETE FROM Person_is_author_of_book WHERE bookID = ? AND personID =
>>>
>> ?
>>
>>> May 12, 2008 12:17:06 PM org.apache.cayenne.access.QueryLogger
>>> logQueryParameters
>>> INFO: [bind: 1->bookID:2, 2->personID:2]
>>>
>>> Try updating your model and see if it fixes your issue.
>>>
>>> Thanks,
>>>
>>> /dev/mrg
>>>
>>>
>>>
>>>
>
>
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Michael Gentry <bl...@gmail.com>.
Daniel,
I just re-ran your app after unchecking personID in your join table
(only bookID was set as the primary key). That causes SQL like this
to be produced:
INFO: DELETE FROM Person_is_author_of_book WHERE bookID = ?
May 12, 2008 2:45:31 PM org.apache.cayenne.access.QueryLogger logQueryParameters
INFO: [bind: 1->bookID:3]
That is incorrect for what you want. You need to make both a primary
key. If not, it will delete too many records (potentially) since it
is not qualifying the personID.
/dev/mrg
On Mon, May 12, 2008 at 12:52 PM, Daniel Doppmeier
<dd...@cebitec.uni-bielefeld.de> wrote:
> Hi
>
> Thanks again. Setting at least one of the attributes as primary key solved
> the problem. As far as I tested, it is not important which of the attributes
> is assigned the primary key, but there has to be at least one PK. The PK
> generation strategy is set to default by the modeller. Any changes to that
> setting do not seem to affect the generated code, so everything seems to
> work just as it should by now.
>
>
> Michael Gentry schrieb:
>
>
>
> > [followup back to the user list]
> >
> > Hi Daniel,
> >
> > The first time I ran your test application it did indeed crash with
> > the missing WHERE clause (this is on Cayenne 3.x, too). I went into
> > your Cayenne Model, though, and on your Person_is_author_of_book table
> > (you didn't map it as a class, which is cool since you are flattening
> > it), I checked the PK/Mandatory checkboxes under the Attributes tab
> > for the DbEntity. Then I re-ran the application and it worked:
> >
> > INFO: DELETE FROM Person_is_author_of_book WHERE bookID = ? AND personID =
> ?
> > May 12, 2008 12:17:06 PM org.apache.cayenne.access.QueryLogger
> > logQueryParameters
> > INFO: [bind: 1->bookID:2, 2->personID:2]
> >
> > Try updating your model and see if it fixes your issue.
> >
> > Thanks,
> >
> > /dev/mrg
> >
> >
> >
>
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Michael Gentry <bl...@gmail.com>.
Setting both as PK is more correct, though, from a DB-perspective.
Your join table essentially has a compound primary key. I'd define it
that way in the DB, too. Setting both columns to be PK (it'll help
for the index, too).
Glad to hear it is working for you now.
/dev/mrg
On Mon, May 12, 2008 at 12:52 PM, Daniel Doppmeier
<dd...@cebitec.uni-bielefeld.de> wrote:
> Hi
>
> Thanks again. Setting at least one of the attributes as primary key solved
> the problem. As far as I tested, it is not important which of the attributes
> is assigned the primary key, but there has to be at least one PK. The PK
> generation strategy is set to default by the modeller. Any changes to that
> setting do not seem to affect the generated code, so everything seems to
> work just as it should by now.
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Daniel Doppmeier <dd...@cebitec.uni-bielefeld.de>.
Hi
Thanks again. Setting at least one of the attributes as primary key
solved the problem. As far as I tested, it is not important which of the
attributes is assigned the primary key, but there has to be at least one
PK. The PK generation strategy is set to default by the modeller. Any
changes to that setting do not seem to affect the generated code, so
everything seems to work just as it should by now.
Michael Gentry schrieb:
> [followup back to the user list]
>
> Hi Daniel,
>
> The first time I ran your test application it did indeed crash with
> the missing WHERE clause (this is on Cayenne 3.x, too). I went into
> your Cayenne Model, though, and on your Person_is_author_of_book table
> (you didn't map it as a class, which is cool since you are flattening
> it), I checked the PK/Mandatory checkboxes under the Attributes tab
> for the DbEntity. Then I re-ran the application and it worked:
>
> INFO: DELETE FROM Person_is_author_of_book WHERE bookID = ? AND personID = ?
> May 12, 2008 12:17:06 PM org.apache.cayenne.access.QueryLogger
> logQueryParameters
> INFO: [bind: 1->bookID:2, 2->personID:2]
>
> Try updating your model and see if it fixes your issue.
>
> Thanks,
>
> /dev/mrg
>
>
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Michael Gentry <bl...@gmail.com>.
[followup back to the user list]
Hi Daniel,
The first time I ran your test application it did indeed crash with
the missing WHERE clause (this is on Cayenne 3.x, too). I went into
your Cayenne Model, though, and on your Person_is_author_of_book table
(you didn't map it as a class, which is cool since you are flattening
it), I checked the PK/Mandatory checkboxes under the Attributes tab
for the DbEntity. Then I re-ran the application and it worked:
INFO: DELETE FROM Person_is_author_of_book WHERE bookID = ? AND personID = ?
May 12, 2008 12:17:06 PM org.apache.cayenne.access.QueryLogger
logQueryParameters
INFO: [bind: 1->bookID:2, 2->personID:2]
Try updating your model and see if it fixes your issue.
Thanks,
/dev/mrg
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Michael Gentry <bl...@gmail.com>.
Hi Dan,
I don't see anything offhand. Since you said you created a simple
test application, could you send it to me and I'll take a look at it?
Just tar it up and send directly to me (the mailing list doesn't
support attachments). Be sure to sanitize the DB connection
information first, though.
Thanks,
/dev/mrg
On Fri, May 9, 2008 at 6:00 PM, Daniel Doppmeier
<dd...@cebitec.uni-bielefeld.de> wrote:
> Hi!
> I'm sorry I made a mistake there. When I was posting the code I was leaving
> out some lines of comment and I seem to have lost one line of code
> meanwhile.
>
> So here is the (slightly changed) code again. This time free of typing
> errors, I hope:
>
> DataContext context = DataContext.createDataContext();
> // create a book
>
> Book book1 = (Book) context.newObject(Book.class);
> book1.setTitle("The secret life of Donald D");
>
> // create a person
>
> Person author1 = (Person) context.newObject(Person.class);
> author1.setName("Huey");
> // asign person as author to the book
> author1.addToBooks(book1);
> // save objects to DB
> context.commitChanges();
> // remove the book from the author's list of book and commit
> changes
>
> author1.removeFromBooks(book1);
> context.commitChanges();
>
> The problem is still the same as described earlier.
>
>
> Michael Gentry schrieb:
>
>
>
> > Hi Daniel, I'm not sure if you've solved your problem yet, but from
> > the code you posted, I don't see where you established any
> > relationship between book1 and author1/author2. You had objects with
> > no relationship defined, so you can't actually remove book1 from
> > author1.
> >
> > /dev/mrg
> >
> >
> > On Tue, May 6, 2008 at 4:17 AM, Daniel Doppmeier
> > <dd...@cebitec.uni-bielefeld.de> wrote:
> >
> >
> > > Hello everyone!
> > >
> > > I am new to cayenne and experiencing some problems, when modelling a
> > > many-to-many relationship. After I could not solve the problem for an
> > > application I am writing at the moment, I set up the following example
> > > application, which is producing the same problems:
> > >
> > > I modelled a small bookstore application, with two tables "Book" and
> > > "Person". After that I created a join table "Person_is_author_of_book",
> to
> > > map from "Person" entries to "Book" entries. On the object side of the
> > > application, this would mean, that every book may have several authors,
> > > whereas one author may have written more than one book.
> > >
> > > Inserting and fetching data from the DB seems to work just fine, but
> when it
> > > comes to deletion there occures my problem. This is what happens in my
> code:
> > >
> > > DataContext context = DataContext.createDataContext();
> > > Book book1 = (Book) context.newObject(Book.class);
> > > book1.setTitle("The secret life of Donald D");
> > >
> > > Person author1 = (Person) context.newObject(Person.class);
> > > author1.setName("Huey");
> > > Person author2 = (Person) context.newObject(Person.class);
> > > author2.setName("Dewey");
> > >
> > > context.commitChanges();
> > >
> > > author1.removeFromBooks(book1);
> > > context.commitChanges();
> > >
> > > Everything is fine until the second commitChanges(), when I try to
> delete
> > > book1 from author1's list.
> > > This is what the logger says about it:
> > >
> > > INFO QueryLogger: --- will run 1 query.
> > > INFO QueryLogger: --- transaction started.
> > > INFO QueryLogger: DELETE FROM Person_is_author_of_book WHERE
> > > INFO QueryLogger: *** error.
> > >
> > > As you can see, there is missing some SQL syntax after the WHERE clause.
> > > Does anybody have an idea, what may cause this malformed SQL?
> > >
> > > I am using the latest stable cayenne version 2.0.4, MySQL
> 5.0.51a-3ubuntu5
> > > and java "1.6.0_06"
> > >
> > >
> > > Thanks a lot for any help!
> > >
> > >
> > >
> > >
> >
> >
> >
>
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Daniel Doppmeier <dd...@cebitec.uni-bielefeld.de>.
Hi!
I'm sorry I made a mistake there. When I was posting the code I was
leaving out some lines of comment and I seem to have lost one line of
code meanwhile.
So here is the (slightly changed) code again. This time free of typing
errors, I hope:
DataContext context = DataContext.createDataContext();
// create a book
Book book1 = (Book) context.newObject(Book.class);
book1.setTitle("The secret life of Donald D");
// create a person
Person author1 = (Person) context.newObject(Person.class);
author1.setName("Huey");
// asign person as author to the book
author1.addToBooks(book1);
// save objects to DB
context.commitChanges();
// remove the book from the author's list of book and commit changes
author1.removeFromBooks(book1);
context.commitChanges();
The problem is still the same as described earlier.
Michael Gentry schrieb:
> Hi Daniel, I'm not sure if you've solved your problem yet, but from
> the code you posted, I don't see where you established any
> relationship between book1 and author1/author2. You had objects with
> no relationship defined, so you can't actually remove book1 from
> author1.
>
> /dev/mrg
>
>
> On Tue, May 6, 2008 at 4:17 AM, Daniel Doppmeier
> <dd...@cebitec.uni-bielefeld.de> wrote:
>
>> Hello everyone!
>>
>> I am new to cayenne and experiencing some problems, when modelling a
>> many-to-many relationship. After I could not solve the problem for an
>> application I am writing at the moment, I set up the following example
>> application, which is producing the same problems:
>>
>> I modelled a small bookstore application, with two tables "Book" and
>> "Person". After that I created a join table "Person_is_author_of_book", to
>> map from "Person" entries to "Book" entries. On the object side of the
>> application, this would mean, that every book may have several authors,
>> whereas one author may have written more than one book.
>>
>> Inserting and fetching data from the DB seems to work just fine, but when it
>> comes to deletion there occures my problem. This is what happens in my code:
>>
>> DataContext context = DataContext.createDataContext();
>> Book book1 = (Book) context.newObject(Book.class);
>> book1.setTitle("The secret life of Donald D");
>>
>> Person author1 = (Person) context.newObject(Person.class);
>> author1.setName("Huey");
>> Person author2 = (Person) context.newObject(Person.class);
>> author2.setName("Dewey");
>>
>> context.commitChanges();
>>
>> author1.removeFromBooks(book1);
>> context.commitChanges();
>>
>> Everything is fine until the second commitChanges(), when I try to delete
>> book1 from author1's list.
>> This is what the logger says about it:
>>
>> INFO QueryLogger: --- will run 1 query.
>> INFO QueryLogger: --- transaction started.
>> INFO QueryLogger: DELETE FROM Person_is_author_of_book WHERE
>> INFO QueryLogger: *** error.
>>
>> As you can see, there is missing some SQL syntax after the WHERE clause.
>> Does anybody have an idea, what may cause this malformed SQL?
>>
>> I am using the latest stable cayenne version 2.0.4, MySQL 5.0.51a-3ubuntu5
>> and java "1.6.0_06"
>>
>>
>> Thanks a lot for any help!
>>
>>
>>
>
>
Re: many-to-many mapping resulting in incomplete SQL queries
Posted by Michael Gentry <bl...@gmail.com>.
Hi Daniel, I'm not sure if you've solved your problem yet, but from
the code you posted, I don't see where you established any
relationship between book1 and author1/author2. You had objects with
no relationship defined, so you can't actually remove book1 from
author1.
/dev/mrg
On Tue, May 6, 2008 at 4:17 AM, Daniel Doppmeier
<dd...@cebitec.uni-bielefeld.de> wrote:
> Hello everyone!
>
> I am new to cayenne and experiencing some problems, when modelling a
> many-to-many relationship. After I could not solve the problem for an
> application I am writing at the moment, I set up the following example
> application, which is producing the same problems:
>
> I modelled a small bookstore application, with two tables "Book" and
> "Person". After that I created a join table "Person_is_author_of_book", to
> map from "Person" entries to "Book" entries. On the object side of the
> application, this would mean, that every book may have several authors,
> whereas one author may have written more than one book.
>
> Inserting and fetching data from the DB seems to work just fine, but when it
> comes to deletion there occures my problem. This is what happens in my code:
>
> DataContext context = DataContext.createDataContext();
> Book book1 = (Book) context.newObject(Book.class);
> book1.setTitle("The secret life of Donald D");
>
> Person author1 = (Person) context.newObject(Person.class);
> author1.setName("Huey");
> Person author2 = (Person) context.newObject(Person.class);
> author2.setName("Dewey");
>
> context.commitChanges();
>
> author1.removeFromBooks(book1);
> context.commitChanges();
>
> Everything is fine until the second commitChanges(), when I try to delete
> book1 from author1's list.
> This is what the logger says about it:
>
> INFO QueryLogger: --- will run 1 query.
> INFO QueryLogger: --- transaction started.
> INFO QueryLogger: DELETE FROM Person_is_author_of_book WHERE
> INFO QueryLogger: *** error.
>
> As you can see, there is missing some SQL syntax after the WHERE clause.
> Does anybody have an idea, what may cause this malformed SQL?
>
> I am using the latest stable cayenne version 2.0.4, MySQL 5.0.51a-3ubuntu5
> and java "1.6.0_06"
>
>
> Thanks a lot for any help!
>
>