[Zope] MySQL and Zope struggles

[Richard Moon]

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