On Tue, 28 Sep 1999, Christopher Petrilli wrote:
3. It seemed to me that Zope doesn't support mySQL very well, being Oracle mentioned all the time. As mySQL is still our main SQL platform, i ask if mySQL DA is still being supported and/or developed?
Well, we have a bit of an architectural issue with MySQL... we're VERY VERY VERY focused on transaction processing, everything is a transaction in Zope, and the object database implements some very advanced ideas. For application sanity, we expect all databases external to Zope to do the same, and MySQL obviously does not. This has caused us to not develop for it, regardless of its popularity in certain areas. It's simply incompatible with the architecture.
Here's the current line on transactions in MySQL from the 3.22.26a docs: """5.3.3 Transactions Transactions are not supported. MySQL shortly will support atomic operations, which are like transactions without rollback. With atomic operations, you can execute a group of INSERT/SELECT/whatever commands and be guaranteed that no other thread will interfere. In this context, you won't usually need rollback. Currently, you can prevent interference from other threads by using the LOCK TABLES and UNLOCK TABLES commands. See section 7.23 LOCK TABLES/UNLOCK TABLES syntax. """ I didn't know how the transactioning fit into the Database Adapator I just pulled down ZPyGreSQLDA for an example. It appears the DB class which the DA defines can override three internal methods: def _begin(self): self.db.query('begin transaction') def _finish(self): self.db.query('commit transaction') def _abort(self): self.db.query('rollback transaction') (For a Python DB API-style interface, _begin would pass, _finish would do self.db.commit(), and _abort would do self.db.rollback().) There are a couple possibilities for implementing transactions in ZMySQLDA: 1) Automatic use of LOCK TABLES. In this case, the interface parses the SQL statements slightly to determine what tables are being used, and issues an appropriate LOCK TABLES command. SELECT statements would cause a READ lock to be established and the statement to be executed. INSERT/REPLACE/UPDATE statements would not be executed immediately but pushed into a queue, and a WRITE lock would be established. _finish() would cause these statements to be executed and UNLOCK TABLES. _abort() would flush the queue. (REPLACE is a MySQL-specific statement, same as INSERT but can overwrite existing data.) 2) Automatic use of GET_LOCK(). MySQL also has the GET_LOCK(lockname)/RELEASE_LOCK(lockname) pair. Only thread can have a lock on lockname at once. The lockname could simply be "ZOPE" which would allow one process to have an open transaction at once (safe but slow), or there could be some user-refinement. I think #1 is feasible, and has the best possibility for decent performance. #2 seems certain to work, though the performance may not be so good. Even #1 will tend to LOCK TABLES when it doesn't have to: If you are executing a single SQL statement, it's probably unnecessary. Some more MySQL info at http://www.zope.org/Members/adustman/MySQLdb -- andy dustman | programmer/analyst | comstar.net, inc. telephone: 770.485.6025 / 706.549.7689 | icq: 32922760 | pgp: 0xc72f3f1d