[Zope] MySQL and Zope struggles

[Monty Taylor]

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 )