Or you could renormalize your data to have: --------- ----------- | Artist | | Note | |---------|-------|-----------| | id | | id | --------- | artist_id | | note_str | ----------- you now have a list of notes by artist_id. You typically won't have a screen that doesn't have an Artist context to be adding a note to. (or to remove all notes from). Of course this means more work to migrate :-( And I don't know all possible scenarios for which you would need the structure you gave, but it is another way around this. It probably doesn't solve real complex scenarios either. JAT Dale -----Original Message----- From: administrator [mailto:administrator@consotec.de] Sent: Monday, October 16, 2000 6:11 AM To: zope Cc: administrator Subject: Re: [Zope] MySQL and Zope struggles I don't know if the following link can solve your problem, but maybe it gives you an idea: http://www.zope.org/Members/Roug/new_record_with_subrecords (How-To: Creating a new record with subrecords in MySQL) Arno
I'm struggling to migrate an application from Zope/PostgreSQL to Zope MySQL (Why ? - because I've got a kind offer of free hosting if I use MySQL).
MySQL offers a limited set of features and is missing, among other things, the ability to use subqueries - so for example in PostgreSQL you can say
update note set notes =<dtml-sqlvar notes type=string> where note_id = (select note_id from artist where <dtml-sqltest artist_id type=int>)
and in MySQL you can't.
If you were working in a traditional programming environment you could overcome this by splitting the above into two parts - a select to retrieve the value of note_id from the artist table followed by an update of the note table using the returned value of note-id.
For example
select note-id into note-id-var from artist where artist-id = 23; update note set notes = 'asdasda' where note-id = note-id-var;
The problem is that in Zope I believe you can't use a returned value within an SQL Method, so the above code would fail. The only way I can see to do the above is to have two separate SQL Methods, one for the select, returning the note-id-var and another for the update. This is very clumsy.
I was wondering if anyone could tell me if there was a better way.
Many thanks
Richard
Richard Moon richard@dcs.co.uk
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
To: zope@zope.org
Nice idea. Trouble is the note table is used to hold notes for many different reasons, so it looks like this - ----------- ------------ -------- | Artist | | Recording | | Label | etc. ----------- ------------ -------- | artist_id | |recording_id| |label_id| | note_id | |note_id | |note_id | ----------- ------------ -------- | | | | | --------------------------------------------------------- | ---------- | Note | ---------- | note_id | | notes | ---------- It looks as if there wasn't anything I was missing - I'm just going to have to break the code up into separate SQL Methods. The example I gave was quite simple compared to some of the updates I have to deal with. Thanks anyway. At 17:40 16/10/00, you wrote:
Or you could renormalize your data to have:
--------- ----------- | Artist | | Note | |---------|-------|-----------| | id | | id | --------- | artist_id | | note_str | -----------
you now have a list of notes by artist_id. You typically won't have a screen that doesn't have an Artist context to be adding a note to. (or to remove all notes from). Of course this means more work to migrate :-( And I don't know all possible scenarios for which you would need the structure you gave, but it is another way around this. It probably doesn't solve real complex scenarios either.
JAT
Dale
Richard Moon richard@dcs.co.uk
You can do it with temporary tables. Like this- create temporary table foo ( note_id int, notes varchar ); insert into foo select Note.note_id, Note.notes from Note, Artist where Note.note_id=Artist.note_id and Artist.artist_id=23; update foo set notes="asdlfna"; replace into Note select note_id, notes from foo; This does assume that the note_id is unique. You may also need to explicitly drop foo, because it goes away when you disconnect, but of course Zope holds connections open. !!! However -- now that I think of it... you can just do: replace into Note select note_id, "New Note" from Artist where artist_id=23; Of couse, this will change all notes for an artist, so it assumes the artist_id is unique as well. (Which I would assume it is from your model.) If you need more complex things, look into temporary tables, but the MySQL extension (like replace) do allow you to do fun things. Hope this helps, Monty Richard Moon wrote:
Nice idea. Trouble is the note table is used to hold notes for many different reasons, so it looks like this -
----------- ------------ -------- | Artist | | Recording | | Label | etc. ----------- ------------ -------- | artist_id | |recording_id| |label_id| | note_id | |note_id | |note_id | ----------- ------------ -------- | | | | | --------------------------------------------------------- | ---------- | Note | ---------- | note_id | | notes | ----------
It looks as if there wasn't anything I was missing - I'm just going to have to break the code up into separate SQL Methods.
The example I gave was quite simple compared to some of the updates I have to deal with.
Thanks anyway.
At 17:40 16/10/00, you wrote:
Or you could renormalize your data to have:
--------- ----------- | Artist | | Note | |---------|-------|-----------| | id | | id | --------- | artist_id | | note_str | -----------
you now have a list of notes by artist_id. You typically won't have a screen that doesn't have an Artist context to be adding a note to. (or to remove all notes from). Of course this means more work to migrate :-( And I don't know all possible scenarios for which you would need the structure you gave, but it is another way around this. It probably doesn't solve real complex scenarios either.
JAT
Dale
Richard Moon richard@dcs.co.uk
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Thanks Monty, I did try the temporary table route - problem is that Zope keeps the connection open so the temporary table stays there. Of course you can explicitly drop the temporary table after you've used it. However if the update fails for any reason the temporary table will still exist so next time in the SQL method fails because its trying to create a temporary table that is already there. I know this shouldn't happen etc but I don't like code that has that kind of potential problem in it - it isn't 'fail-safe'. I decided better to use a permanent table. The SQL method locks it, deletes everything in it then writes the data it needs to store. That way its fail safe and I would expect more efficient than creating and dropping tables. However I hadn't appreciate the REPLACE command. That's a real hidden gem. I'm off to see if I can use it - thanks. Richard At 11:37 17/10/00, you wrote:
You can do it with temporary tables.
Like this-
create temporary table foo ( note_id int, notes varchar ); insert into foo select Note.note_id, Note.notes from Note, Artist where Note.note_id=Artist.note_id and Artist.artist_id=23; update foo set notes="asdlfna"; replace into Note select note_id, notes from foo;
This does assume that the note_id is unique. You may also need to explicitly drop foo, because it goes away when you disconnect, but of course Zope holds connections open.
!!! However -- now that I think of it... you can just do: replace into Note select note_id, "New Note" from Artist where artist_id=23;
Of couse, this will change all notes for an artist, so it assumes the artist_id is unique as well. (Which I would assume it is from your model.)
If you need more complex things, look into temporary tables, but the MySQL extension (like replace) do allow you to do fun things.
Hope this helps, Monty
Richard Moon wrote:
Nice idea. Trouble is the note table is used to hold notes for many different reasons, so it looks like this -
----------- ------------ -------- | Artist | | Recording | | Label | etc. ----------- ------------ -------- | artist_id | |recording_id| |label_id| | note_id | |note_id | |note_id | ----------- ------------ -------- | | | | | --------------------------------------------------------- | ---------- | Note | ---------- | note_id | | notes | ----------
It looks as if there wasn't anything I was missing - I'm just going to have to break the code up into separate SQL Methods.
The example I gave was quite simple compared to some of the updates I have to deal with.
Thanks anyway.
At 17:40 16/10/00, you wrote:
Or you could renormalize your data to have:
--------- ----------- | Artist | | Note | |---------|-------|-----------| | id | | id | --------- | artist_id | | note_str | -----------
you now have a list of notes by artist_id. You typically won't have a screen that doesn't have an Artist context to be adding a note to. (or to remove all notes from). Of course this means more work to migrate :-( And I don't know all possible scenarios for which you would need the structure you gave, but it is another way around this. It probably doesn't solve real complex scenarios either.
JAT
Dale
Richard Moon richard@dcs.co.uk
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
Richard Moon richard@dcs.co.uk
participants (3)
-
dale.w.lance＠mail.sprint.com -
Monty Taylor -
Richard Moon