Back to index

plone3  3.1.7
SQLStorage.py
Go to the documentation of this file.
00001 from Products.Archetypes.SQLMethod import SQLMethod
00002 from Products.Archetypes.interfaces.storage import ISQLStorage
00003 from Products.Archetypes.interfaces.field import IObjectField
00004 from Products.Archetypes.interfaces.layer import ILayer
00005 from Products.Archetypes.debug import log
00006 from Products.Archetypes.config import TOOL_NAME, MYSQL_SQLSTORAGE_TABLE_TYPE
00007 from Products.Archetypes.Storage import StorageLayer, type_map
00008 from Acquisition import aq_base, aq_inner, aq_parent
00009 from Products.CMFCore.utils import getToolByName
00010 from ZODB.POSException import ConflictError
00011 from OFS.ObjectManager import BeforeDeleteException
00012 
00013 class BaseSQLStorage(StorageLayer):
00014     """ SQLStorage Base, more or less ISO SQL """
00015 
00016     __implements__ = ISQLStorage, ILayer
00017 
00018     query_create = ('create table <dtml-var table> '
00019                     '(UID char(50) primary key not null, '
00020                     'PARENTUID char(50), <dtml-var columns>)')
00021     query_drop   = ('drop table <dtml-var table>')
00022     query_select = ('select <dtml-var field> from <dtml-var table> '
00023                     'where <dtml-sqltest UID op="eq" type="string">')
00024     query_insert = ('insert into <dtml-var table> '
00025                     'set UID=<dtml-sqlvar UID type="string">, '
00026                     'PARENTUID=<dtml-sqlvar PARENTUID type="string">')
00027     query_update = ('update <dtml-var table> set '
00028                     '<dtml-var field>=<dtml-sqlvar value '
00029                     'type="%s" optional> where '
00030                     '<dtml-sqltest UID op="eq" type="string">')
00031     query_delete = ('delete from <dtml-var table> '
00032                     'where <dtml-sqltest UID op="eq" type="string">')
00033 
00034     sqlm_type_map = {'integer':'int'}
00035 
00036     db_type_map = {'fixedpoint' : 'integer'}
00037 
00038     def map_object(self, field, value):
00039         if value is None:
00040             return 'None'
00041         else:
00042             return value
00043 
00044     def unmap_object(self, field, value):
00045         if value == 'None':
00046             return None
00047         else:
00048             return value
00049 
00050     def map_datetime(self, field, value):
00051         # we don't want to lose even 0.001 second
00052         try:
00053             return value.ISO()[:-2] + str(value.second())
00054         except:
00055             return None
00056 
00057     def map_fixedpoint(self, field, value):
00058         __traceback_info__ = repr(value)
00059         template = '%%d%%0%dd' % field.precision
00060         return template % value
00061 
00062     def unmap_fixedpoint(self, field, value):
00063         __traceback_info__ = repr(value)
00064         if value is None or value == '':
00065             return (0, 0)
00066         if type(value) == type(''):   # Gadfly return integers as strings
00067             value = int(value)
00068         split = 10 ** field.precision
00069         return (value / split), (value % split)
00070 
00071     def map_lines(self, field, value):
00072         __traceback_info__ = repr(value)
00073         return '\n'.join(value)
00074 
00075     def unmap_lines(self, field, value):
00076         __traceback_info__ = repr(value)
00077         return value.split('\n')
00078 
00079     def map_boolean(self, field, value):
00080         __traceback_info__ = repr(value)
00081         if not value:
00082             return 0
00083         else:
00084             return 1
00085         
00086     def map_reference(self, field, value):
00087         __traceback_info__ = repr(value)
00088 
00089         return ','.join(value)
00090     
00091     def unmap_boolean(self, field, value):
00092         __traceback_info__ = repr(value)
00093         if not value or value == '0':   # Gadfly return integers as strings
00094             return 0
00095         else:
00096             return 1
00097 
00098     def table_exists(self, instance):
00099         raise NotImplemented
00100 
00101     def is_initialized(self, instance):
00102         try:
00103             return self.getName() in instance.__initialized
00104         except AttributeError:
00105             return None
00106 
00107     def initializeField(self, instance, field):
00108         pass
00109 
00110     def is_cleaned(self, instance):
00111         try:
00112             return self.getName() in instance.__cleaned
00113         except AttributeError:
00114             return None
00115 
00116     def cleanupField(self, instance, field):
00117         pass
00118 
00119     def _query(self, instance, query, args):
00120         c_tool = getToolByName(instance, TOOL_NAME)
00121         connection_id = c_tool.getConnFor(instance)
00122         method = SQLMethod(instance)
00123         method.edit(connection_id, ' '.join(args.keys()), query)
00124         query, result = method(test__=1, **args)
00125         return result
00126 
00127     def initializeInstance(self, instance, item=None, container=None):
00128         if (self.is_initialized(instance) or
00129             getattr(instance, '_at_is_fake_instance', None)):
00130             # duh, we don't need to be initialized twice
00131             return
00132         factory = getToolByName(instance,'portal_factory')
00133         if factory.isTemporary(instance):
00134           return
00135               
00136         fields = instance.Schema().fields()
00137         fields = [f for f in fields if IObjectField.isImplementedBy(f) \
00138                   and f.getStorage().__class__ is self.__class__]
00139         columns = []
00140         args = {}
00141         for field in fields:
00142             type = self.db_type_map.get(field.type, field.type)
00143             name = field.getName()
00144             # MySQL supports escape for columns names!
00145             if self.__class__.__name__ == 'MySQLSQLStorage':
00146                 columns.append('`%s` %s' % (name, type))
00147             else:
00148                 columns.append('%s %s' % (name, type))
00149         parent = container or aq_parent(aq_inner(instance))
00150         args['PARENTUID'] = getattr(aq_base(parent), 'UID', lambda: None)()
00151         args['table'] = instance.portal_type
00152         args['UID'] = instance.UID()
00153         #args['db_encoding']=kwargs.get('db_encoding',None)
00154         args['columns'] = ', ' + ', '.join(columns)
00155         if not self.table_exists(instance):
00156             self._query(instance, self.query_create, args)
00157             log('created table %s\n' % args['table'])
00158         try:
00159             self._query(instance, self.query_insert, args)
00160         except ConflictError:
00161             raise
00162         except:
00163             # usually, duplicate key
00164             # raise SQLInitException(msg)
00165             raise
00166         try:
00167             instance.__initialized += (self.getName(),)
00168         except AttributeError:
00169             instance.__initialized = (self.getName(),)
00170         # now, if we find an attribute called _v_$classname_temps, it
00171         # means the object was moved and we can initialize the fields
00172         # with those values
00173         temps_var = '_v_%s_temps' % self.getName()
00174         if hasattr(aq_base(instance), temps_var):
00175             temps = getattr(instance, temps_var)
00176             for key, value in temps.items():
00177                 instance.Schema()[key].set(instance, value)
00178             delattr(instance, temps_var)
00179         try:
00180             del instance.__cleaned
00181         except (AttributeError, KeyError):
00182             pass
00183 
00184     def get(self, name, instance, **kwargs):
00185         if not self.is_initialized(instance):
00186             # ignore all calls before we're initialized - some
00187             # manage_afterAdd() methods try to get and set fields and
00188             # we can't allow that to break
00189             return None
00190         field = kwargs.get('field', instance.getField(name))
00191         args = {}
00192         args['table'] = instance.portal_type
00193         args['UID'] = instance.UID()
00194         args['db_encoding']=kwargs.get('db_encoding',None)
00195         args['field'] = name
00196         result = self._query(instance, self.query_select, args)
00197         result = result[0][0]
00198         mapper = getattr(self, 'unmap_' + field.type, None)
00199         if mapper is not None:
00200             result = mapper(field, result)
00201         return result
00202 
00203     def set(self, name, instance, value, **kwargs):
00204         if not self.is_initialized(instance):
00205             # ignore all calls before we're initialized - some
00206             # manage_afterAdd() methods try to get and set fields and
00207             # we can't allow that to break
00208             return None
00209         field = kwargs.get('field', instance.getField(name))
00210         mapper = getattr(self, 'map_' + field.type, None)
00211         if mapper is not None:
00212             value = mapper(field, value)
00213         type = type_map.get(field.type, 'string')
00214         sql_type = self.sqlm_type_map.get(field.type, 'string')
00215         default = field.default
00216         args = {}
00217         args['table'] = instance.portal_type
00218         args['UID'] = instance.UID()
00219         #args['db_encoding']=kwargs.get('db_encoding',None)
00220         field_name = '%s:%s' % (name, type)
00221         if default:
00222             if type == 'string':
00223                 default = "'%s'" % default
00224             field_name =  "%s=%s" % (name, default)
00225         args[field_name] = name
00226         args['field'] = name
00227         if value is not None:
00228             # omiting it causes dtml-sqlvar to insert NULL
00229             args['value'] = value
00230         self._query(instance, self.query_update % sql_type, args)
00231 
00232     def cleanupInstance(self, instance, item=None, container=None):
00233         if (self.is_cleaned(instance) or
00234             getattr(instance, '_at_is_fake_instance', None)):
00235             # duh, we don't need to be cleaned twice
00236             return
00237         # the object is being deleted. remove data from sql.  but
00238         # first, made a temporary copy of the field values in case we
00239         # are being moved
00240         fields = instance.Schema().fields()
00241         fields = [f for f in fields if IObjectField.isImplementedBy(f) \
00242                   and f.getStorage().__class__ is self.__class__]
00243         temps = {}
00244         for f in fields:
00245             temps[f.getName()] = f.get(instance)
00246         setattr(instance, '_v_%s_temps' % self.getName(), temps)
00247         # now, remove data from sql
00248         c_tool = getToolByName(instance, TOOL_NAME)
00249         connection_id = c_tool.getConnFor(instance)
00250         args = {}
00251         args['table'] = instance.portal_type
00252         args['UID'] = instance.UID()
00253         #args['db_encoding']=kwargs.get('db_encoding',None)
00254         method = SQLMethod(instance)
00255         method.edit(connection_id, ' '.join(args.keys()), self.query_delete)
00256         try:
00257             query, result = method(test__=1, **args)
00258         except ConflictError:
00259             raise
00260         except:
00261             # dunno what could happen here raise
00262             # SQLCleanupException(msg)
00263             raise BeforeDeleteException
00264         try:
00265             instance.__cleaned += (self.getName(),)
00266         except AttributeError:
00267             instance.__cleaned = (self.getName(),)
00268         try:
00269             del instance.__initialized
00270         except (AttributeError, KeyError):
00271             pass
00272 
00273 class GadflySQLStorage(BaseSQLStorage):
00274 
00275     __implements__ = BaseSQLStorage.__implements__
00276 
00277     query_create = ('create table <dtml-var table> '
00278                     '(UID varchar, PARENTUID varchar <dtml-var columns>)')
00279     query_select = ('select <dtml-var field> from <dtml-var table> '
00280                     'where <dtml-sqltest UID op="eq" type="string">')
00281     query_insert = ('insert into <dtml-var table> '
00282                     'values (<dtml-sqlvar UID type="string">, '
00283                     '<dtml-sqlvar PARENTUID type="string">, '
00284                     '<dtml-in expr="_.string.split(columns,\',\')[1:]"> '
00285                     '<dtml-if sequence-end>\'\'<dtml-else>\'\', </dtml-if> '
00286                     '</dtml-in>) ')
00287     query_update = ('update <dtml-var table> set '
00288                     '<dtml-var field>=<dtml-sqlvar value '
00289                     'type="%s" optional> where '
00290                     '<dtml-sqltest UID op="eq" type="string">')
00291     query_delete = ('delete from <dtml-var table> '
00292                     'where <dtml-sqltest UID op="eq" type="string">')
00293 
00294     sqlm_type_map = {'integer':'string',
00295                      'float':'string'}
00296 
00297     db_type_map = {'object'     : 'varchar',
00298                    'string'     : 'varchar',
00299                    'text'       : 'varchar',
00300                    'datetime'   : 'varchar',
00301                    'integer'    : 'varchar',
00302                    'float'      : 'varchar',
00303                    'fixedpoint' : 'integer',
00304                    'lines'      : 'varchar',
00305                    'reference'  : 'varchar',
00306                    'boolean'    : 'integer',
00307                    }
00308 
00309     def map_datetime(self, field, value):
00310         try:
00311             return value.ISO()[:-2] + str(value.second())
00312         except:
00313             return ''
00314 
00315     def unmap_datetime(self, field, value):
00316         from DateTime import DateTime
00317         try:
00318             return DateTime(value)
00319         except:
00320             return None
00321 
00322     def map_integer(self, field, value):
00323         __traceback_info__ = repr(value)
00324         if value is None:   # Gadfly represents None as an empty string
00325             return ''
00326         else:
00327             return str(value)
00328 
00329     def unmap_integer(self, field, value):
00330         __traceback_info__ = repr(value)
00331         if value == '':   # Gadfly represents None as an empty string
00332             return None
00333         else:
00334             return int(value)
00335 
00336     def map_float(self, field, value):
00337         __traceback_info__ = repr(value)
00338         if value is None:   # Gadfly represents None as an empty string
00339             return ''
00340         else:
00341             return str(value)
00342 
00343     def unmap_float(self, field, value):
00344         __traceback_info__ = repr(value)
00345         if value == '':   # Gadfly represents None as an empty string
00346             return None
00347         else:
00348             return float(value)
00349 
00350     def unmap_lines(self, field, value):
00351         __traceback_info__ = repr(value)
00352         if value == '':   # Gadfly represents None as an empty String
00353             return None
00354         else:
00355             return value.split('\n')
00356 
00357     def table_exists(self, instance):
00358         try:
00359             self._query(instance,
00360                         'select * from <dtml-var table>',
00361                         {'table': instance.portal_type.lower()})
00362         except ConflictError:
00363             raise
00364         except:
00365             return 0
00366         else:
00367             return 1
00368 
00369 class MySQLSQLStorage(BaseSQLStorage):
00370 
00371     __implements__ = BaseSQLStorage.__implements__
00372 
00373     query_create = ('create table `<dtml-var table>` '
00374                     '(UID char(50) primary key not null, '
00375                     'PARENTUID char(50) <dtml-var columns>) TYPE = %s' % MYSQL_SQLSTORAGE_TABLE_TYPE)
00376     query_select = ('select `<dtml-var field>` '
00377                     'from `<dtml-var table>` where '
00378                     '<dtml-sqltest UID op="eq" type="string">')
00379     query_insert = ('insert into `<dtml-var table>` '
00380                     'set UID=<dtml-sqlvar UID type="string">, '
00381                     'PARENTUID=<dtml-sqlvar PARENTUID type="string">')
00382     query_update = ('update `<dtml-var table>` set '
00383                     '`<dtml-var field>`=<dtml-sqlvar value '
00384                     'type="%s" optional> where '
00385                     '<dtml-sqltest UID op="eq" type="string">')
00386     query_delete = ('delete from `<dtml-var table>` '
00387                     'where <dtml-sqltest UID op="eq" type="string">')
00388 
00389     db_type_map = {'object'     : 'text',
00390                    'string'     : 'text',
00391                    'fixedpoint' : 'integer',
00392                    'lines'      : 'text',
00393                    'reference'  : 'text',
00394                    'boolean'    : 'tinyint',
00395                    }
00396 
00397     def table_exists(self, instance):
00398         result =  [r[0].lower() for r in
00399                    self._query(instance, '''show tables''', {})]
00400         return instance.portal_type.lower() in result
00401 
00402 class PostgreSQLStorage(BaseSQLStorage):
00403 
00404     __implements__ = BaseSQLStorage.__implements__
00405 
00406     query_create = ('create table <dtml-var table> '
00407                     '(UID text primary key not null, '
00408                     'PARENTUID text <dtml-var columns>)')
00409     query_select = ('select <dtml-var field> from <dtml-var table> '
00410                     'where <dtml-sqltest UID op="eq" type="string">')
00411     query_insert = ('insert into <dtml-var table> '
00412                     '(UID, PARENTUID) values '
00413                     '(<dtml-sqlvar UID type="string">, '
00414                     '<dtml-sqlvar PARENTUID type="string">)')
00415     query_update = ('update <dtml-var table> set '
00416                     '<dtml-var field>=<dtml-sqlvar value '
00417                     'type="%s" optional> where '
00418                     '<dtml-sqltest UID op="eq" type="string">')
00419     query_delete = ('delete from <dtml-var table> '
00420                     'where <dtml-sqltest UID op="eq" type="string">')
00421 
00422     db_type_map = {'object'     : 'text',
00423                    'string'     : 'text',
00424                    'datetime'   : 'timestamp',
00425                    'fixedpoint' : 'integer',
00426                    'lines'      : 'text',
00427                    'reference'  : 'text',
00428                    }
00429 
00430     def table_exists(self, instance):
00431         return self._query(instance,
00432                            ('select relname from pg_class where '
00433                             '<dtml-sqltest relname op="eq" type="string">'),
00434                            {'relname': instance.portal_type.lower()})
00435 
00436 class SQLServerStorage(BaseSQLStorage):
00437 
00438     __implements__ = BaseSQLStorage.__implements__
00439 
00440     query_create = ('create table <dtml-var table> '
00441                     '(UID varchar(50) CONSTRAINT pk_uid '
00442                     'PRIMARY KEY CLUSTERED, '
00443                     'PARENTUID varchar(50) '
00444                     '<dtml-var columns>)')
00445     query_select = ('select <dtml-var field> from '
00446                     '<dtml-var table> '
00447                     'where <dtml-sqltest UID op="eq" type="string">')
00448     query_insert = ('insert into <dtml-var table> '
00449                     '(UID, PARENTUID) values '
00450                     '(<dtml-sqlvar UID type="string">, '
00451                     '<dtml-sqlvar PARENTUID type="string">)')
00452     query_update = ('update <dtml-var table> set '
00453                     '<dtml-var field>=<dtml-sqlvar value '
00454                     'type="%s" optional> where '
00455                     '<dtml-sqltest UID op="eq" type="string">')
00456     query_delete = ('delete from <dtml-var table> '
00457                     'where <dtml-sqltest UID op="eq" type="string">')
00458 
00459     db_type_map = {'object'     : 'varchar',
00460                    'string'     : 'varchar',
00461                    'text'       : 'varchar',
00462                    'datetime'   : 'timestamp',
00463                    'fixedpoint' : 'integer',
00464                    'lines'      : 'varchar',
00465                    'reference'  : 'varchar',
00466                    'boolean'    : 'integer',
00467                    }
00468 
00469     def table_exists(self, instance):
00470         return self._query(instance,
00471                            ('select name from '
00472                             'sysobjects where '
00473                             'xtype=char(85) and uid=1 and '
00474                             '<dtml-sqltest name op="eq" type="string">'),
00475                            {'name':instance.portal_type.lower()})