[Zope] MySQL Select Statements

[Andy Dustman]

I should point out that 0.2.2 has not been fully released yet. There's a
bug I have to iron out of it first. Probably next week. Also, for general
MySQLdb questions, send them to andy-python@dustman.net (mailing list). Or
subscribe, it's low-volume and won't fatten your mailbox.

On Fri, 30 Jun 2000, Michael Blewett wrote:

> It's funny that you say 0.2.2 is broken. Apart from this one glitch, it 
> works fine for me (although from memory I think we had to do a little bit 
> of fiddling at install time to get it to work).

It's a matter of perspective. I prefer to think of it as 0.2.2 breaks
ZMySQLDA. This is because ZMySQLDA uses _mysql, and _mysql prior to 0.2.2
implemented fetch_row(), fetch_rows(), fetch_all_rows(), and the
..._as_dict() variants. This was just too much code duplication to put up
with, and the new interface is fetch([n[,how]]) where n is the maximum
number of rows to fetch (default 1), and how is how to return the result
(0: as tuple, 1: as dictionary, other codes may be added at some point).
To fix ZMySQLDA, change fetch_all_rows() to fetch(maxrows) in the query
routine. One other place (to get the tables) uses fetch_all_rows(). If you
insist on using 0.2.2 at this point, change that as well. 0.2.2 is marked
Alpha for a reason... And when I release it, I'll include a very small
patch to make things right with ZMySQLDA again (I'm treating mordred's as
the official one at this point).
 
> At 00:12 30/06/2000 -0400, Ron Bickers wrote:
> >Why do you say you "must" use the AS statement?  Do you get an error
> >otherwise?
> >
> >MySQLdb 0.2.2 is broken when used with ZMySQLDA.  But 0.2.1 works okay for
> >me.  Andy Dustman (author of the MySQLdb) said he will fix it and hinted
> >that one should use the ZMySQLDA that is on Zope.org (v1.2.0 now I believe).
> >Since that one now uses his MySQLdb, he will likely kill off his own DA.

Quite true. Now for the AS statement: If you are joining tables with
identically-named columns in common, you should use AS in your SQL to
rename them. Otherwise it will return two columns with the same name to
Zope, which is bad. You do not, however, need to do this:

> > > Just need someone to put me straight here. When I use Select
> > > statements in
> > > an SQL Method I cannot simply :
> > >
> > > Select Column1, Column2 etc
> > > from TableA.
> > >
> > > Instead I must use the AS statement ie
> > >
> > > Select Column1 AS Column1, Column2 AS Column2
> > > from TableA.
> > >
> > >   - Michael

This is just gratuitious renaming to the original name, which is not
renaming at all. Here's an example of what I'm talking about.

SELECT TableA.Col, TableB.Col from TableA, TableB # bad
SELECT TableA.Col AS ColA, TableB.Col AS ColB from TableA, TableB # good

Note that the first SELECT returned two columns named Col, which will
confuse Zope, and the second returns ColA (TableA.Col) and ColB
(TableB.Col).

Additional compatibility note: One of the 0.2.2 changes has been to
synchronize with MySQL-3.23 evolution (now at 3.23.19-beta, and GPL'd),
and the big feature here is: Transactions! Yes, as of 3.23.15,
transactions are in MySQL, if you use BDB tables. And yes, BDB does mean
Berkeley DB, specificially the Sleepycat DB v3, which supports
transactions (but probably not columns, which I presume MySQL is imposing
on the database).

For ZMySQLDA developers out there; MySQL-3.23 defaults to autocommit.
However, there is a BEGIN SQL statement which starts a transaction and
similarly for COMMIT and ROLLBACK statements. But remember: All this
transactional stuff ONLY works on BDB tables. If you try to BEGIN a
transaction (or turn off autocommit) on another table type, well, I'm not
sure what happens. Maybe you get an error, and maybe you don't. Not sure
what the solution is for this. It may be necessary to have the ZMySQLDA
package include another DA: ZMySQLTDA for transactions. The standard DA
would not be based on TM, and the transactional one could simply subclass
the other one:

# ZMySQLTDA/db.py
from Shared.DC.ZRDB.TM import TM

class DB(ZMySQLDA.DB, TM):

    def _begin(self, *ignored): self.db.query("BEGIN")
    def _finish(self, *ignored): self.db.query("COMMIT")
    def _abort(self, *ignored): self.db.query("ROLLBACK")

Obviously the application designer will have to use the right DA for the
right table type...

-- 
andy dustman       |     programmer/analyst     |      comstar.net, inc.
telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d
"Therefore, sweet knights, if you may doubt your strength or courage, 
come no further, for death awaits you all, with nasty, big, pointy teeth!"