[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):