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