[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - mysql.py:1.1.2.1 classification.py:1.1.2.1 keygen.py:1.1.2.1 properties.py:1.1.2.1 security.py:1.1.2.1 structure.py:1.1.2.1

Shane Hathaway shane@zope.com
Thu, 10 Apr 2003 02:26:57 -0400


Update of /cvs-repository/Products/Ape/lib/apelib/sql
In directory cvs.zope.org:/tmp/cvs-serv27102

Modified Files:
      Tag: shane-mysql-branch
	classification.py keygen.py properties.py security.py 
	structure.py 
Added Files:
      Tag: shane-mysql-branch
	mysql.py 
Log Message:
Quick and dirty attempt at MySQL 4.0 support.  It seems to work!  MySQL-Max required.

=== Added File Products/Ape/lib/apelib/sql/mysql.py ===
##############################################################################
#
# Copyright (c) 2003 Zope Corporation and Contributors.
# All Rights Reserved.
#
# This software is subject to the provisions of the Zope Public License,
# Version 2.0 (ZPL).  A copy of the ZPL should accompany this distribution.
# THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
# WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
# WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
# FOR A PARTICULAR PURPOSE.
#
##############################################################################
"""MySQL connectivity

$Id: mysql.py,v 1.1.2.1 2003/04/10 06:26:56 shane Exp $
"""

import os
from time import time

import MySQLdb

from apelib.core.interfaces import ITPCConnection

DEBUG = os.environ.get('APE_DEBUG_MYSQL')
PROFILE = os.environ.get('APE_PROFILE_MYSQL')


class MySQLConnection:

    __implements__ = ITPCConnection

    _final = 0
    db = None
    cursor = None

    def __init__(self, params='', prefix='zodb'):
        self.error = MySQLdb.DatabaseError
        self.Binary = MySQLdb.Binary
        self.params = params
        self.prefix = prefix
        self.connect_callbacks = []

    def isConnected(self):
        return (self.db is not None)

    def addConnectCallback(self, f):
        self.connect_callbacks.append(f)

    def connect(self):
        import psycopg
        self.db = MySQLdb.connect(db="ape")  # XXX need to use real params
        self.cursor = self.db.cursor()
        for f in self.connect_callbacks:
            f()
        self.connect_callbacks = []

    def sortKey(self):
        return repr(self)

    def getName(self):
        return repr(self)

    def __repr__(self):
        return 'MySQLConnection(%s, %s)' % (
            repr(self.params), repr(self.prefix))

    def execute(self, text, fetch=0, cursor=None, **kw):
        if cursor is None:
            cursor = self.cursor
            if cursor is None:
                raise RuntimeError('Not connected')
        if text.startswith('CREATE TABLE'):
            text += ' TYPE=INNODB'
        if DEBUG:
            print 'MySQL: %s' % text
        if PROFILE:
            start = time()
            cursor.execute(text, kw)
            end = time()
            print 'MySQL: %0.6fs: %s' % (end - start, text)
        else:
            cursor.execute(text, kw)
        if fetch:
            return list(cursor.fetchall())
        return None

    def asBinary(self, data):
        return self.Binary(data)

    def begin(self):
        pass

    def vote(self):
        self._final = 1

    def reset(self):
        self._final = 0

    def abort(self):
        try:
            self.db.rollback()
        finally:
            self.reset()

    def finish(self):
        if self._final:
            try:
                self.db.commit()
            finally:
                self.reset()

    def close(self):
        if self.isConnected():
            self.cursor.close()
            self.cursor = None
            self.db.close()
            self.db = None



=== Products/Ape/lib/apelib/sql/classification.py 1.1 => 1.1.2.1 ===
--- Products/Ape/lib/apelib/sql/classification.py:1.1	Wed Apr  9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/classification.py	Thu Apr 10 02:26:56 2003
@@ -30,23 +30,23 @@
 
     table_base_name = 'classification'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int PRIMARY KEY,
+    nkey int PRIMARY KEY,
     meta_type character varying(255),
     class_name character varying(255)
     )'''
 
     read_sql = '''SELECT meta_type, class_name from %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
     SET meta_type=%(meta_type)s, class_name=%(class_name)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, meta_type, class_name)
+    (nkey, meta_type, class_name)
     VALUES (%(key)s, %(meta_type)s, %(class_name)s)'''
 
 


=== Products/Ape/lib/apelib/sql/keygen.py 1.1 => 1.1.2.1 ===
--- Products/Ape/lib/apelib/sql/keygen.py:1.1	Wed Apr  9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/keygen.py	Thu Apr 10 02:26:56 2003
@@ -27,13 +27,28 @@
 
     table_base_name = 'key_seq'
 
-    checkexist_sql = "SELECT last_value FROM %(table)s"
+    checkexist_sql = "SELECT nkey FROM %(table)s"
 
-    create_sql = "CREATE SEQUENCE %(table)s"
+    create_sql = "CREATE TABLE %(table)s (nkey int)"
 
-    read_sql = "SELECT nextval('%(table)s')"
+    insert_sql = "INSERT INTO %(table)s VALUES (0)"
 
-    clear_sql = "SELECT setval('%(table)s', 1)"
+    update_sql = "UPDATE %(table)s SET nkey=LAST_INSERT_ID(nkey+1)"
+
+    read_sql = "SELECT LAST_INSERT_ID()"
+
+    clear_sql = "UPDATE %(table)s SET nkey=0"
+
+
+    def setupTables(self):
+        conn = self.conn
+        try:
+            self.execute(self.checkexist_sql)
+        except conn.error:
+            conn.db.rollback()
+            self.execute(self.create_sql)
+            self.execute(self.insert_sql)
+            conn.db.commit()
 
 
     def makeKeychain(self, event, name, stored):
@@ -44,6 +59,7 @@
             # Request that the other side do the work (for ZEO)
             n = event.getKeyedObjectSystem().newKey()
         else:
+            self.execute(self.update_sql)
             n = self.execute(self.read_sql, 1)[0][0]
         return event.getKeychain()[:-1] + (long(n),)
 


=== Products/Ape/lib/apelib/sql/properties.py 1.1 => 1.1.2.1 ===
--- Products/Ape/lib/apelib/sql/properties.py:1.1	Wed Apr  9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/properties.py	Thu Apr 10 02:26:56 2003
@@ -34,28 +34,28 @@
 
     table_base_name = 'properties'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int,
+    nkey int,
     id character varying(255),
     type character varying(255),
-    data bytea
+    data blob
     )'''
 
     read_sql = '''SELECT id, type, data from %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
     SET type = %(type)s, data = %(data)s
-    WHERE key = %(key)s and id = %(id)s'''
+    WHERE nkey = %(key)s and id = %(id)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, id, type, data)
+    (nkey, id, type, data)
     VALUES (%(key)s, %(id)s, %(type)s, %(data)s)'''
 
     delete_sql = '''DELETE FROM %(table)s
-    WHERE key = %(key)s and id = %(id)s'''
+    WHERE nkey = %(key)s and id = %(id)s'''
 
 
     def getSchema(self):


=== Products/Ape/lib/apelib/sql/security.py 1.1 => 1.1.2.1 ===
--- Products/Ape/lib/apelib/sql/security.py:1.1	Wed Apr  9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/security.py	Thu Apr 10 02:26:56 2003
@@ -34,10 +34,10 @@
 
     table_base_name = 'security'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int,
+    nkey int,
     declaration_type character varying(255),
     role character varying(255),
     permission character varying(255),
@@ -45,15 +45,15 @@
     )'''
 
     read_sql = '''SELECT declaration_type, role, permission, username
-    from %(table)s WHERE key = %(key)s'''
+    from %(table)s WHERE nkey = %(key)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, declaration_type, role, permission, username)
+    (nkey, declaration_type, role, permission, username)
     VALUES (%(key)s, %(declaration_type)s, %(role)s,
     %(permission)s, %(username)s)'''
 
     delete_sql = '''DELETE FROM %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
 
     def getSchema(self):
@@ -94,58 +94,58 @@
 
     table_base_name = 'users'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int,
+    nkey int,
     id character varying(255),
     password character varying(255)
     )'''
 
     create_roles_sql = '''CREATE TABLE %(roles_table)s (
-    key int,
+    nkey int,
     id character varying(255),
     role character varying(255)
     )'''
 
     create_domains_sql = '''CREATE TABLE %(domains_table)s (
-    key int,
+    nkey int,
     id character varying(255),
     domain character varying(255)
     )'''
 
     read_sql = '''SELECT id, password FROM %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     read_roles_sql = '''SELECT id, role FROM %(roles_table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     read_domains_sql = '''SELECT id, domain FROM %(domains_table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
     SET password = %(password)s
-    WHERE key = %(key)s AND id = %(id)s'''
+    WHERE nkey = %(key)s AND id = %(id)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, id, password)
+    (nkey, id, password)
     VALUES (%(key)s, %(id)s, %(password)s)'''
 
     delete_sql = '''DELETE FROM %(table)s
-    WHERE key = %(key)s and id = %(id)s'''
+    WHERE nkey = %(key)s and id = %(id)s'''
 
     delete_roles_sql = '''DELETE FROM %(roles_table)s
-    WHERE key = %(key)s and id = %(id)s'''
+    WHERE nkey = %(key)s and id = %(id)s'''
 
     delete_domains_sql = '''DELETE FROM %(domains_table)s
-    WHERE key = %(key)s and id = %(id)s'''
+    WHERE nkey = %(key)s and id = %(id)s'''
 
     insert_role_sql = '''INSERT INTO %(roles_table)s
-    (key, id, role)
+    (nkey, id, role)
     VALUES (%(key)s, %(id)s, %(role)s)'''
 
     insert_domain_sql = '''INSERT INTO %(domains_table)s
-    (key, id, domain)
+    (nkey, id, domain)
     VALUES (%(key)s, %(id)s, %(domain)s)'''
 
     clear_roles_sql = 'DELETE FROM %(roles_table)s'


=== Products/Ape/lib/apelib/sql/structure.py 1.1 => 1.1.2.1 ===
--- Products/Ape/lib/apelib/sql/structure.py:1.1	Wed Apr  9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/structure.py	Thu Apr 10 02:26:56 2003
@@ -30,22 +30,22 @@
 
     table_base_name = 'object_data'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int PRIMARY KEY,
-    data bytea
+    nkey int PRIMARY KEY,
+    data longblob
     )'''
 
     read_sql = '''SELECT data from %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
     SET data = %(data)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, data)
+    (nkey, data)
     VALUES (%(key)s, %(data)s)'''
 
 
@@ -85,27 +85,27 @@
 
     table_base_name = 'folder_items'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int,
+    nkey int,
     name character varying(255),
     child_key int
     )'''
 
     read_sql = '''SELECT name, child_key FROM %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
     SET child_key=%(child_key)s
-    WHERE key = %(key)s and name = %(name)s'''
+    WHERE nkey = %(key)s and name = %(name)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, name, child_key)
+    (nkey, name, child_key)
     VALUES (%(key)s, %(name)s, %(child_key)s)'''
 
     delete_sql = '''DELETE FROM %(table)s
-    WHERE key = %(key)s and name = %(name)s'''
+    WHERE nkey = %(key)s and name = %(name)s'''
 
 
     def getSchema(self):
@@ -197,19 +197,19 @@
     table_base_name = 'remainder'
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int PRIMARY KEY,
-    pickle bytea
+    nkey int PRIMARY KEY,
+    pickle longblob
     )'''
 
     read_sql = '''SELECT pickle from %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
     SET pickle = %(data)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, pickle)
+    (nkey, pickle)
     VALUES (%(key)s, %(data)s)'''
 
 
@@ -222,23 +222,23 @@
 
     table_base_name = 'mtime'
 
-    checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
+    checkexist_sql = '''SELECT nkey FROM %(table)s WHERE nkey = 0'''
 
     create_sql = '''CREATE TABLE %(table)s (
-    key int PRIMARY KEY,
+    nkey int PRIMARY KEY,
     mtime bigint
     )'''
 
     read_sql = '''SELECT mtime from %(table)s
-    WHERE key = %(key)s'''
+    WHERE nkey = %(key)s'''
 
     update_sql = '''UPDATE %(table)s
-    SET mtime = %(mtime)d
-    WHERE key = %(key)s'''
+    SET mtime = %(mtime)s
+    WHERE nkey = %(key)s'''
 
     insert_sql = '''INSERT INTO %(table)s
-    (key, mtime)
-    VALUES (%(key)s, %(mtime)d)'''
+    (nkey, mtime)
+    VALUES (%(key)s, %(mtime)s)'''
 
 
     def getSchema(self):