[Zope] MySQL and Zope struggles

[dale.w.lance@mail.sprint.com]

--openmail-part-2b1fc156-00000001
Content-Type: text/plain; charset=US-ASCII
Content-Disposition: inline
	;Creation-Date="Mon, 16 Oct 2000 11:40:29 -0500"
Content-Transfer-Encoding: 7bit

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

--openmail-part-2b1fc156-00000001--