[Erp5-report] r13716 - in /erp5/trunk/products/ZMySQLDDA: DA.py DABase.py db.py
nobody at svn.erp5.org
nobody at svn.erp5.org
Tue Mar 27 15:04:16 CEST 2007
Author: vincent
Date: Tue Mar 27 15:04:14 2007
New Revision: 13716
URL: http://svn.erp5.org?rev=13716&view=rev
Log:
Remove dependency over ZMySQLDA by duplicating previously-shared code & files, as asked by Yoshinori.
Remove a useless "pass".
Added:
erp5/trunk/products/ZMySQLDDA/DABase.py
Modified:
erp5/trunk/products/ZMySQLDDA/DA.py
erp5/trunk/products/ZMySQLDDA/db.py
Modified: erp5/trunk/products/ZMySQLDDA/DA.py
URL: http://svn.erp5.org/erp5/trunk/products/ZMySQLDDA/DA.py?rev=13716&r1=13715&r2=13716&view=diff
==============================================================================
--- erp5/trunk/products/ZMySQLDDA/DA.py (original)
+++ erp5/trunk/products/ZMySQLDDA/DA.py Tue Mar 27 15:04:14 2007
@@ -83,9 +83,17 @@
# attributions are listed in the accompanying credits file.
#
##############################################################################
+database_type='MySQL'
-from Products.ZMySQLDA.DA import *
+import os
from db import DeferredDB
+import Shared.DC.ZRDB.Connection, sys, DABase
+from App.Dialogs import MessageDialog
+from Globals import HTMLFile
+from ImageFile import ImageFile
+from ExtensionClass import Base
+from DateTime import DateTime
+from thread import allocate_lock
manage_addZMySQLDeferredConnectionForm=HTMLFile('deferredConnectionAdd',globals())
@@ -96,24 +104,49 @@
self._setObject(id, DeferredConnection(id, title, connection_string, check))
if REQUEST is not None: return self.manage_main(self,REQUEST)
-class DeferredConnection(Connection):
+# Connection Pool for connections to MySQL.
+database_connection_pool_lock = allocate_lock()
+database_connection_pool = {}
+
+class DeferredConnection(DABase.Connection):
"""
Experimental MySQL DA which implements
deferred SQL code execution to reduce locking issues
"""
+ database_type=database_type
+ id='%s_database_connection' % database_type
meta_type=title='Z %s Deferred Database Connection' % database_type
+ icon='misc_/Z%sDDA/conn' % database_type
+
+ manage_properties=HTMLFile('connectionEdit', globals())
def factory(self): return DeferredDB
- def connect(self,s):
- try: self._v_database_connection.close()
- except: pass
- self._v_connected=''
- DB=self.factory()
- ## No try. DO.
- self._v_database_connection=DeferredDB(s)
- self._v_connected=DateTime()
- return self
+ def connect(self, s):
+ try:
+ database_connection_pool_lock.acquire()
+ self._v_connected = ''
+ pool_key = self.getPhysicalPath()
+ connection = database_connection_pool.get(pool_key)
+ if connection is not None and connection.connection == s:
+ self._v_database_connection = connection
+ else:
+ if connection is not None:
+ connection.closeConnection()
+ DB = self.factory()
+ database_connection_pool[pool_key] = DeferredDB(s)
+ self._v_database_connection = database_connection_pool[pool_key]
+ # XXX If date is used as such, it can be wrong because an existing
+ # connection may be reused. But this is suposedly only used as a
+ # marker to know if connection was successfull.
+ self._v_connected = DateTime()
+ finally:
+ database_connection_pool_lock.release()
+ return self
+
+ def sql_quote__(self, v, escapes={}):
+ return self._v_database_connection.string_literal(v)
+
classes=('DA.DeferredConnection')
@@ -132,8 +165,8 @@
__ac_permissions__=(
('Add Z MySQL Database Connections',
- ('manage_addZMySQLConnectionForm',
- 'manage_addZMySQLConnection')),
+ ('manage_addZMySQLDeferredConnectionForm',
+ 'manage_addZMySQLDeferredConnection')),
)
misc_={'conn': ImageFile(
Added: erp5/trunk/products/ZMySQLDDA/DABase.py
URL: http://svn.erp5.org/erp5/trunk/products/ZMySQLDDA/DABase.py?rev=13716&view=auto
==============================================================================
--- erp5/trunk/products/ZMySQLDDA/DABase.py (added)
+++ erp5/trunk/products/ZMySQLDDA/DABase.py Tue Mar 27 15:04:14 2007
@@ -1,0 +1,250 @@
+##############################################################################
+#
+# Zope Public License (ZPL) Version 1.0
+# -------------------------------------
+#
+# Copyright (c) Digital Creations. All rights reserved.
+#
+# This license has been certified as Open Source(tm).
+#
+# Redistribution and use in source and binary forms, with or without
+# modification, are permitted provided that the following conditions are
+# met:
+#
+# 1. Redistributions in source code must retain the above copyright
+# notice, this list of conditions, and the following disclaimer.
+#
+# 2. Redistributions in binary form must reproduce the above copyright
+# notice, this list of conditions, and the following disclaimer in
+# the documentation and/or other materials provided with the
+# distribution.
+#
+# 3. Digital Creations requests that attribution be given to Zope
+# in any manner possible. Zope includes a "Powered by Zope"
+# button that is installed by default. While it is not a license
+# violation to remove this button, it is requested that the
+# attribution remain. A significant investment has been put
+# into Zope, and this effort will continue if the Zope community
+# continues to grow. This is one way to assure that growth.
+#
+# 4. All advertising materials and documentation mentioning
+# features derived from or use of this software must display
+# the following acknowledgement:
+#
+# "This product includes software developed by Digital Creations
+# for use in the Z Object Publishing Environment
+# (http://www.zope.org/)."
+#
+# In the event that the product being advertised includes an
+# intact Zope distribution (with copyright and license included)
+# then this clause is waived.
+#
+# 5. Names associated with Zope or Digital Creations must not be used to
+# endorse or promote products derived from this software without
+# prior written permission from Digital Creations.
+#
+# 6. Modified redistributions of any form whatsoever must retain
+# the following acknowledgment:
+#
+# "This product includes software developed by Digital Creations
+# for use in the Z Object Publishing Environment
+# (http://www.zope.org/)."
+#
+# Intact (re-)distributions of any official Zope release do not
+# require an external acknowledgement.
+#
+# 7. Modifications are encouraged but must be packaged separately as
+# patches to official Zope releases. Distributions that do not
+# clearly separate the patches from the original work must be clearly
+# labeled as unofficial distributions. Modifications which do not
+# carry the name Zope may be packaged in any form, as long as they
+# conform to all of the clauses above.
+#
+#
+# Disclaimer
+#
+# THIS SOFTWARE IS PROVIDED BY DIGITAL CREATIONS ``AS IS'' AND ANY
+# EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+# IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR
+# PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL DIGITAL CREATIONS OR ITS
+# CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
+# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
+# LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF
+# USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
+# ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
+# OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT
+# OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
+# SUCH DAMAGE.
+#
+#
+# This software consists of contributions made by Digital Creations and
+# many individuals on behalf of Digital Creations. Specific
+# attributions are listed in the accompanying credits file.
+#
+##############################################################################
+__doc__='''Database Connection
+
+$Id: DABase.py,v 1.5 2001/08/17 02:17:38 adustman Exp $'''
+__version__='$Revision: 1.5 $'[11:-2]
+
+import Shared.DC.ZRDB.Connection, sys
+from Globals import HTMLFile
+from ImageFile import ImageFile
+from ExtensionClass import Base
+import Acquisition
+
+class Connection(Shared.DC.ZRDB.Connection.Connection):
+ _isAnSQLConnection=1
+
+ manage_options=Shared.DC.ZRDB.Connection.Connection.manage_options+(
+ {'label': 'Browse', 'action':'manage_browse'},
+ # {'label': 'Design', 'action':'manage_tables'},
+ )
+
+ manage_tables=HTMLFile('tables',globals())
+ manage_browse=HTMLFile('browse',globals())
+
+ info=None
+
+ def tpValues(self):
+ #if hasattr(self, '_v_tpValues'): return self._v_tpValues
+ r=[]
+ # self._v_tables=tables=TableBrowserCollection()
+ #tables=tables.__dict__
+ c=self._v_database_connection
+ try:
+ for d in c.tables(rdb=0):
+ try:
+ name=d['TABLE_NAME']
+ b=TableBrowser()
+ b.__name__=name
+ b._d=d
+ b._c=c
+ #b._columns=c.columns(name)
+ b.icon=table_icons.get(d['TABLE_TYPE'],'text')
+ r.append(b)
+ # tables[name]=b
+ except:
+ # print d['TABLE_NAME'], sys.exc_type, sys.exc_value
+ pass
+
+ finally: pass #print sys.exc_type, sys.exc_value
+ #self._v_tpValues=r
+ return r
+
+ def __getitem__(self, name):
+ if name=='tableNamed':
+ if not hasattr(self, '_v_tables'): self.tpValues()
+ return self._v_tables.__of__(self)
+ raise KeyError, name
+
+ def manage_wizard(self, tables):
+ " "
+
+ def manage_join(self, tables, select_cols, join_cols, REQUEST=None):
+ """Create an SQL join"""
+
+ def manage_insert(self, table, cols, REQUEST=None):
+ """Create an SQL insert"""
+
+ def manage_update(self, table, keys, cols, REQUEST=None):
+ """Create an SQL update"""
+
+class TableBrowserCollection(Acquisition.Implicit):
+ "Helper class for accessing tables via URLs"
+
+class Browser(Base):
+ def __getattr__(self, name):
+ try: return self._d[name]
+ except KeyError: raise AttributeError, name
+
+class values:
+
+ def len(self): return 1
+
+ def __getitem__(self, i):
+ try: return self._d[i]
+ except AttributeError: pass
+ self._d=self._f()
+ return self._d[i]
+
+class TableBrowser(Browser, Acquisition.Implicit):
+ icon='what'
+ Description=check=''
+ info=HTMLFile('table_info',globals())
+ menu=HTMLFile('table_menu',globals())
+
+ def tpValues(self):
+ v=values()
+ v._f=self.tpValues_
+ return v
+
+ def tpValues_(self):
+ r=[]
+ tname=self.__name__
+ for d in self._c.columns(tname):
+ b=ColumnBrowser()
+ b._d=d
+ b.icon=d['Icon']
+ b.TABLE_NAME=tname
+ r.append(b)
+ return r
+
+ def tpId(self): return self._d['TABLE_NAME']
+ def tpURL(self): return "Table/%s" % self._d['TABLE_NAME']
+ def Name(self): return self._d['TABLE_NAME']
+ def Type(self): return self._d['TABLE_TYPE']
+
+ manage_designInput=HTMLFile('designInput',globals())
+ def manage_buildInput(self, id, source, default, REQUEST=None):
+ "Create a database method for an input form"
+ args=[]
+ values=[]
+ names=[]
+ columns=self._columns
+ for i in range(len(source)):
+ s=source[i]
+ if s=='Null': continue
+ c=columns[i]
+ d=default[i]
+ t=c['Type']
+ n=c['Name']
+ names.append(n)
+ if s=='Argument':
+ values.append("<!--#sql-value %s type=%s-->'" %
+ (n, vartype(t)))
+ a='%s%s' % (n, boboType(t))
+ if d: a="%s=%s" % (a,d)
+ args.append(a)
+ elif s=='Property':
+ values.append("<!--#sql-value %s type=%s-->'" %
+ (n, vartype(t)))
+ else:
+ if isStringType(t):
+ if find(d,"\'") >= 0: d=join(split(d,"\'"),"''")
+ values.append("'%s'" % d)
+ elif d:
+ values.append(str(d))
+ else:
+ raise ValueError, (
+ 'no default was given for <em>%s</em>' % n)
+
+
+
+
+class ColumnBrowser(Browser):
+ icon='field'
+
+ def check(self):
+ return ('\t<input type=checkbox name="%s.%s">' %
+ (self.TABLE_NAME, self._d['Name']))
+ def tpId(self): return self._d['Name']
+ def tpURL(self): return "Column/%s" % self._d['Name']
+ def Description(self): return " %s" % self._d['Description']
+
+table_icons={
+ 'TABLE': 'table',
+ 'VIEW':'view',
+ 'SYSTEM_TABLE': 'stable',
+ }
+
Modified: erp5/trunk/products/ZMySQLDDA/db.py
URL: http://svn.erp5.org/erp5/trunk/products/ZMySQLDDA/db.py?rev=13716&r1=13715&r2=13716&view=diff
==============================================================================
--- erp5/trunk/products/ZMySQLDDA/db.py (original)
+++ erp5/trunk/products/ZMySQLDDA/db.py Tue Mar 27 15:04:14 2007
@@ -84,37 +84,291 @@
#
##############################################################################
-from Products.ZMySQLDA.db import *
-
-class DeferredDB(DB):
+import _mysql
+import MySQLdb
+from _mysql_exceptions import OperationalError, NotSupportedError
+MySQLdb_version_required = (0,9,2)
+
+_v = getattr(_mysql, 'version_info', (0,0,0))
+if _v < MySQLdb_version_required:
+ raise NotSupportedError, \
+ "ZMySQLDDA requires at least MySQLdb %s, %s found" % \
+ (MySQLdb_version_required, _v)
+
+from MySQLdb.converters import conversions
+from MySQLdb.constants import FIELD_TYPE, CR, CLIENT
+from Shared.DC.ZRDB.TM import TM
+from DateTime import DateTime
+from zLOG import LOG, ERROR, INFO
+
+import string, sys
+from string import strip, split, find, upper, rfind
+from time import time
+from thread import get_ident
+
+hosed_connection = (
+ CR.SERVER_GONE_ERROR,
+ CR.SERVER_LOST
+ )
+
+key_types = {
+ "PRI": "PRIMARY KEY",
+ "MUL": "INDEX",
+ "UNI": "UNIQUE",
+ }
+
+field_icons = "bin", "date", "datetime", "float", "int", "text", "time"
+
+icon_xlate = {
+ "varchar": "text", "char": "text",
+ "enum": "what", "set": "what",
+ "double": "float", "numeric": "float",
+ "blob": "bin", "mediumblob": "bin", "longblob": "bin",
+ "tinytext": "text", "mediumtext": "text",
+ "longtext": "text", "timestamp": "datetime",
+ "decimal": "float", "smallint": "int",
+ "mediumint": "int", "bigint": "int",
+ }
+
+type_xlate = {
+ "double": "float", "numeric": "float",
+ "decimal": "float", "smallint": "int",
+ "mediumint": "int", "bigint": "int",
+ "int": "int", "float": "float",
+ "timestamp": "datetime", "datetime": "datetime",
+ "time": "datetime",
+ }
+
+def _mysql_timestamp_converter(s):
+ if len(s) < 14:
+ s = s + "0"*(14-len(s))
+ parts = map(int, (s[:4],s[4:6],s[6:8],
+ s[8:10],s[10:12],s[12:14]))
+ return DateTime("%04d-%02d-%02d %02d:%02d:%02d" % tuple(parts))
+
+def DateTime_or_None(s):
+ try: return DateTime(s)
+ except: return None
+
+def int_or_long(s):
+ try: return int(s)
+ except: return long(s)
+
+class DeferredDB(TM):
"""
An experimental MySQL DA which implements deferred execution
of SQL code in order to reduce locks and provide better behaviour
with MyISAM non transactional tables
"""
+ Database_Connection=_mysql.connect
+ Database_Error=_mysql.Error
+
+ def Database_Connection(self, *args, **kwargs):
+ return MySQLdb.connect(*args, **kwargs)
+
+ defs={
+ FIELD_TYPE.CHAR: "i", FIELD_TYPE.DATE: "d",
+ FIELD_TYPE.DATETIME: "d", FIELD_TYPE.DECIMAL: "n",
+ FIELD_TYPE.DOUBLE: "n", FIELD_TYPE.FLOAT: "n", FIELD_TYPE.INT24: "i",
+ FIELD_TYPE.LONG: "i", FIELD_TYPE.LONGLONG: "l",
+ FIELD_TYPE.SHORT: "i", FIELD_TYPE.TIMESTAMP: "d",
+ FIELD_TYPE.TINY: "i", FIELD_TYPE.YEAR: "i",
+ }
+
+ conv=conversions.copy()
+ conv[FIELD_TYPE.LONG] = int_or_long
+ conv[FIELD_TYPE.DATETIME] = DateTime_or_None
+ conv[FIELD_TYPE.DATE] = DateTime_or_None
+ conv[FIELD_TYPE.DECIMAL] = float
+ del conv[FIELD_TYPE.TIME]
+
+ _p_oid=_p_changed=_registered=None
+
def __init__(self,connection):
- DB.__init__(self, connection)
+ self.connection=connection
+ self.kwargs = self._parse_connection_string(connection)
+ self.db = {}
+ self._finished_or_aborted = {}
+ db = self._getConnection()
+ transactional = db.server_capabilities & CLIENT.TRANSACTIONS
+ if self._try_transactions == '-':
+ transactional = 0
+ elif not transactional and self._try_transactions == '+':
+ raise NotSupportedError, "transactions not supported by this server"
+ self._use_TM = self._transactions = transactional
+ if self._mysql_lock:
+ self._use_TM = 1
self._sql_string_list_dict = {}
+
+ def __del__(self):
+ self._cleanupConnections()
+
+ def _getFinishedOrAborted(self):
+ return self._finished_or_aborted[get_ident()]
+
+ def _setFinishedOrAborted(self, value):
+ self._finished_or_aborted[get_ident()] = value
+
+ def _cleanupConnections(self):
+ for db in self.db.itervalues():
+ db.close()
+
+ def _forceReconnection(self):
+ db = apply(self.Database_Connection, (), self.kwargs)
+ self.db[get_ident()] = db
+ return db
+
+ def _getConnection(self):
+ ident = get_ident()
+ db = self.db.get(ident)
+ if db is None:
+ db = self._forceReconnection()
+ return db
+
+ def _closeConnection(self):
+ ident = get_ident()
+ db = self.db.get(ident)
+ if db is not None:
+ db.close()
+ del self.db[ident]
+
+ def _emptySQLStringList(self):
+ self._sql_string_list_dict[get_ident()] = []
+
+ def _appendToSQLStringList(self, value):
+ self._sql_string_list_dict[get_ident()].append(value)
+
+ def _getSQLStringList(self):
+ return self._sql_string_list_dict[get_ident()]
+
+ def _parse_connection_string(self, connection):
+ kwargs = {'conv': self.conv}
+ items = split(connection)
+ self._use_TM = None
+ if not items: return kwargs
+ lockreq, items = items[0], items[1:]
+ if lockreq[0] == "*":
+ self._mysql_lock = lockreq[1:]
+ db_host, items = items[0], items[1:]
+ self._use_TM = 1
+ else:
+ self._mysql_lock = None
+ db_host = lockreq
+ if '@' in db_host:
+ db, host = split(db_host,'@',1)
+ kwargs['db'] = db
+ if ':' in host:
+ host, port = split(host,':',1)
+ kwargs['port'] = int(port)
+ kwargs['host'] = host
+ else:
+ kwargs['db'] = db_host
+ if kwargs['db'] and kwargs['db'][0] in ('+', '-'):
+ self._try_transactions = kwargs['db'][0]
+ kwargs['db'] = kwargs['db'][1:]
+ else:
+ self._try_transactions = None
+ if not kwargs['db']:
+ del kwargs['db']
+ if not items: return kwargs
+ kwargs['user'], items = items[0], items[1:]
+ if not items: return kwargs
+ kwargs['passwd'], items = items[0], items[1:]
+ if not items: return kwargs
+ kwargs['unix_socket'], items = items[0], items[1:]
+ return kwargs
+
+ def tables(self, rdb=0,
+ _care=('TABLE', 'VIEW')):
+ r=[]
+ a=r.append
+ result = self._query("SHOW TABLES")
+ row = result.fetch_row(1)
+ while row:
+ a({'TABLE_NAME': row[0][0], 'TABLE_TYPE': 'TABLE'})
+ row = result.fetch_row(1)
+ return r
+
+ def columns(self, table_name):
+ from string import join
+ try:
+ c = self._query('SHOW COLUMNS FROM %s' % table_name)
+ except:
+ return ()
+ r=[]
+ for Field, Type, Null, Key, Default, Extra in c.fetch_row(0):
+ info = {}
+ field_default = Default and "DEFAULT %s"%Default or ''
+ if Default: info['Default'] = Default
+ if '(' in Type:
+ end = rfind(Type,')')
+ short_type, size = split(Type[:end],'(',1)
+ if short_type not in ('set','enum'):
+ if ',' in size:
+ info['Scale'], info['Precision'] = \
+ map(int, split(size,',',1))
+ else:
+ info['Scale'] = int(size)
+ else:
+ short_type = Type
+ if short_type in field_icons:
+ info['Icon'] = short_type
+ else:
+ info['Icon'] = icon_xlate.get(short_type, "what")
+ info['Name'] = Field
+ info['Type'] = type_xlate.get(short_type,'string')
+ info['Extra'] = Extra,
+ info['Description'] = join([Type, field_default, Extra or '',
+ key_types.get(Key, Key or ''),
+ Null != 'YES' and 'NOT NULL' or '']),
+ info['Nullable'] = (Null == 'YES') and 1 or 0
+ if Key:
+ info['Index'] = 1
+ if Key == 'PRI':
+ info['PrimaryKey'] = 1
+ info['Unique'] = 1
+ elif Key == 'UNI':
+ info['Unique'] = 1
+ r.append(info)
+ return r
+
+ def _query(self, query, force_reconnect=False):
+ """
+ Send a to MySQL server.
+ It reconnects automaticaly if needed and the following conditions are
+ met:
+ - It has not just tried to reconnect (ie, this function will not
+ attemp to connect twice per call).
+ - This conection is not transactionnal and has set not MySQL locks,
+ because they are bound to the connection. This check can be
+ overridden by passing force_reconnect with True value.
+ """
+ db = self._getConnection()
+ try:
+ db.query(query)
+ except OperationalError, m:
+ if ((not force_reconnect) and \
+ (self._mysql_lock or self._transactions)) or \
+ m[0] not in hosed_connection:
+ raise
+ # Hm. maybe the db is hosed. Let's restart it.
+ self._forceReconnection()
+ db.query(query)
+ return db.store_result()
def query(self,query_string, max_rows=1000):
self._use_TM and self._register()
for qs in filter(None, map(strip,split(query_string, '\0'))):
qtype = upper(split(qs, None, 1)[0])
if qtype == "SELECT":
- raise NotSupportedError, "can not SELECT in deferred connections"
+ raise NotSupportedError, "can not SELECT in deferred connections"
self._appendToSQLStringList(qs)
return (),()
- def _emptySQLStringList(self):
- self._sql_string_list_dict[get_ident()] = []
-
- def _appendToSQLStringList(self, value):
- self._sql_string_list_dict[get_ident()].append(value)
-
- def _getSQLStringList(self):
- return self._sql_string_list_dict[get_ident()]
+ def string_literal(self, s):
+ return self._getConnection().string_literal(s)
def _begin(self, *ignored):
# The Deferred DB instance is sometimes used for several
@@ -125,7 +379,7 @@
def _finish(self, *ignored):
if self._getFinishedOrAborted():
- return
+ return
self._setFinishedOrAborted(True)
# Ping the database to reconnect if connection was lost.
self._query("SELECT 1", force_reconnect=True)
@@ -142,5 +396,4 @@
def _abort(self, *ignored):
self._setFinishedOrAborted(True)
- pass
-
+
More information about the Erp5-report
mailing list