add TYPE_CONVERTERS dict on advanced helpers defining conversions callback from Python SQL types (closes #132587)

Now, the dbhelper stores converters for different base types, that could be overwritten by client code (original implementations backported from cubicweb).

boolean_value method has been reimplemented to use this new structure.

authorVincent Michel <vincent.michel@logilab.fr>
changeset3c58962f468c
branchstable
phasepublic
hiddenno
parent revision#7db3d7c30c53 default is stable
child revision#69bf85e32c2d [sqlgen] Allow arbitrary SQL expression in statements generated by SQLGenerator. Closes #132590, #ecc3ef56e4ba fix dumb name error in mysql introduced by 3c58962f468c
files modified by this revision
ChangeLog
__init__.py
mysql.py
postgres.py
sqlite.py
sqlserver.py
sqlserver2005.py
test/unittest_converters.py
# HG changeset patch
# User Vincent Michel <vincent.michel@logilab.fr>
# Date 1366115264 -7200
# Tue Apr 16 14:27:44 2013 +0200
# Branch stable
# Node ID 3c58962f468c7df96152967702ac2946891c5612
# Parent 7db3d7c30c533bbf09d151a7bd5d88377c2eea24
add TYPE_CONVERTERS dict on advanced helpers defining conversions callback from Python SQL types (closes #132587)

Now, the dbhelper stores converters for different base types, that could be overwritten
by client code (original implementations backported from cubicweb).

`boolean_value` method has been reimplemented to use this new structure.

diff --git a/ChangeLog b/ChangeLog
@@ -1,8 +1,13 @@
1  Changelog for logilab database package
2  ======================================
3 
4 +
5 +--
6 +    * #132587: TYPE_CONVERTERS dictionary on db helper containing type 
7 +      conversion callback
8 +
9  2012-02-03  --  1.8.2
10      * don't use CURRENT_DATETIME for postgresql (#83892)
11 
12      * sqlserver: sql_current_[date|timestamp] shouldn't return date[time] objects (#88279)
13 
diff --git a/__init__.py b/__init__.py
@@ -1,6 +1,6 @@
14 -# copyright 2003-2012 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
15 +# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
16  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
17  #
18  # This file is part of logilab-database.
19  #
20  # logilab-database is free software: you can redistribute it and/or modify it
@@ -39,14 +39,14 @@
21  __docformat__ = "restructuredtext en"
22 
23  import sys
24  import threading
25  import logging
26 -from datetime import datetime, time
27 +from datetime import datetime, time, date
28 
29  from logilab.common.modutils import load_module_from_name
30 -from logilab.common.date import utcdatetime, utctime
31 +from logilab.common.date import todate, todatetime, utcdatetime, utctime
32 
33  _LOGGER = logging.getLogger('logilab.database')
34 
35  USE_MX_DATETIME = False
36 
@@ -124,10 +124,37 @@
37          except ValueError:
38              raise UnknownDriver('Unknown module %s for %s' % (module, driver))
39          modules.insert(0, module)
40 
41 
42 +# types converters #############################################################
43 +
44 +def convert_datetime(value):
45 +    # Note: use is __class__ since issubclass(datetime, date)
46 +    if type(value) is date:
47 +        value = todatetime(value)
48 +    return value
49 +
50 +def convert_date(value):
51 +    if isinstance(value, datetime):
52 +        value = todate(value)
53 +    return value
54 +
55 +def convert_tzdatetime(value):
56 +    # Note: use is __class__ since issubclass(datetime, date)
57 +    if type(value) is date:
58 +        value = todatetime(value)
59 +    elif getattr(value, 'tzinfo', None):
60 +        value = utcdatetime(value)
61 +    return value
62 +
63 +def convert_tztime(value):
64 +    if getattr(value, 'tzinfo', None):
65 +        value = utctime(value)
66 +    return value
67 +
68 +
69  # unified db api ###############################################################
70 
71  class UnknownDriver(Exception):
72      """raised when a unknown driver is given to get connection"""
73 
@@ -650,10 +677,11 @@
74 
75  def register_function(funcdef):
76      """register the function `funcdef` on supported backends"""
77      SQL_FUNCTIONS_REGISTRY.register_function(funcdef)
78 
79 +
80  class _TypeMapping(dict):
81      def __getitem__(self, key):
82          try:
83              return dict.__getitem__(self, key)
84          except KeyError:
@@ -664,10 +692,11 @@
85              raise
86 
87      def copy(self):
88          return _TypeMapping(dict.copy(self))
89 
90 +
91  class _GenericAdvFuncHelper(FTIndexerMixIn):
92      """Generic helper, trying to provide generic way to implement
93      specific functionalities from others DBMS
94 
95      An exception is raised when the functionality is not emulatable
@@ -688,10 +717,19 @@
96          'Time' :     'time',
97          'Datetime' : 'timestamp',
98          'Interval' : 'interval',
99          })
100 
101 +    TYPE_CONVERTERS = {
102 +        'Boolean': bool,
103 +        # XXX needed for sqlite but I don't think it is for other backends
104 +        'Datetime': convert_datetime,
105 +        'Date': convert_date,
106 +        'TZDatetime': convert_tzdatetime,
107 +        'TZTime': convert_tztime,
108 +        }
109 +
110      # DBMS resources descriptors and accessors
111      backend_name = None # overridden in subclasses ('postgres', 'sqlite', etc.)
112      needs_from_clause = False
113      union_parentheses_support = True
114      intersect_all_support = True
@@ -930,15 +968,12 @@
115      def sql_temporary_table(self, table_name, table_schema,
116                              drop_on_commit=True):
117          table_name = self.temporary_table_name(table_name)
118          return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
119 
120 -    def boolean_value(self, value):
121 -        if value:
122 -            return 'TRUE'
123 -        else:
124 -            return 'FALSE'
125 +    def boolean_value(self, value): # XXX deprecate?
126 +        return self.TYPE_CONVERTERS['Boolean'](value)
127 
128      def binary_value(self, value):
129          """convert a value to a python object known by the driver to
130          be mapped to a binary column"""
131          return self.dbapi_module.Binary(value)
diff --git a/mysql.py b/mysql.py
@@ -1,6 +1,6 @@
132 -# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
133 +# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
134  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
135  #
136  # This file is part of logilab-database.
137  #
138  # logilab-database is free software: you can redistribute it and/or modify it
@@ -126,11 +126,10 @@
139 
140  db._PREFERED_DRIVERS['mysql'] = ['MySQLdb']#, 'pyMySQL.MySQL']
141  db._ADAPTER_DIRECTORY['mysql'] = {'MySQLdb': _MySqlDBAdapter,
142                           }
143 
144 -
145  class _MyAdvFuncHelper(db._GenericAdvFuncHelper):
146      """MySQL helper, taking advantage of postgres SEQUENCE support
147      """
148      backend_name = 'mysql'
149      needs_from_clause = True
@@ -142,10 +141,13 @@
150      TYPE_MAPPING['String'] = 'mediumtext'
151      TYPE_MAPPING['Bytes'] = 'longblob'
152      # don't use timestamp which is automatically updated on row update
153      TYPE_MAPPING['Datetime'] = 'datetime'
154 
155 +    TYPE_CONVERTERS = db._GenericAdvFuncHelper.TYPE_CONVERTERS.copy()
156 +    TYPE_CONVERTERS['Boolean'] = mysql_convert_boolean
157 +
158      def mycmd(self, cmd, dbhost, dbport, dbuser):
159          cmd = [cmd]
160          # XXX compress
161          dbhost = dbhost or self.dbhost
162          if dbhost is not None:
@@ -225,16 +227,10 @@
163 
164      def sql_regexp_match_expression(self, pattern):
165          """pattern matching using regexp"""
166          return "REGEXP %s" % pattern
167 
168 -    def boolean_value(self, value):
169 -        if value:
170 -            return 1
171 -        else:
172 -            return 0
173 -
174      def list_users(self, cursor):
175          """return the list of existing database users"""
176          # Host, Password
177          cursor.execute("SELECT User FROM mysql.user")
178          return [r[0] for r in cursor.fetchall()]
diff --git a/postgres.py b/postgres.py
@@ -1,6 +1,6 @@
179 -# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
180 +# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
181  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
182  #
183  # This file is part of logilab-database.
184  #
185  # logilab-database is free software: you can redistribute it and/or modify it
@@ -176,10 +176,15 @@
186      'psycopg2' : _Psycopg2Adapter,
187      'psycopg2ct' : _Psycopg2CtypesAdapter,
188      'pyPgSQL.PgSQL' : _PgsqlAdapter,
189      }
190 
191 +def convert_boolean(value):
192 +    if value:
193 +        return 'TRUE'
194 +    else:
195 +        return 'FALSE'
196 
197 
198  class _PGAdvFuncHelper(db._GenericAdvFuncHelper):
199      """Postgres helper, taking advantage of postgres SEQUENCE support
200      """
@@ -187,10 +192,12 @@
201      backend_name = 'postgres'
202      TYPE_MAPPING = db._GenericAdvFuncHelper.TYPE_MAPPING.copy()
203      TYPE_MAPPING.update({
204          'TZTime' :   'time with time zone',
205          'TZDatetime':'timestamp with time zone'})
206 +    TYPE_CONVERTERS = db._GenericAdvFuncHelper.TYPE_CONVERTERS.copy()
207 +    TYPE_CONVERTERS['Boolean'] = convert_boolean
208 
209      def pgdbcmd(self, cmd, dbhost, dbport, dbuser, *args):
210          cmd = [cmd]
211          cmd += args
212          if dbhost or self.dbhost:
diff --git a/sqlite.py b/sqlite.py
@@ -1,6 +1,6 @@
213 -# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
214 +# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
215  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
216  #
217  # This file is part of logilab-database.
218  #
219  # logilab-database is free software: you can redistribute it and/or modify it
@@ -211,15 +211,11 @@
220      ilike_support = False
221      union_parentheses_support = False
222      intersect_all_support = False
223      alter_column_support = False
224 
225 -    def boolean_value(self, value):
226 -        if value:
227 -            return 1
228 -        else:
229 -            return 0
230 +    TYPE_CONVERTERS = db._GenericAdvFuncHelper.TYPE_CONVERTERS.copy()
231 
232      def backup_commands(self, backupfile, keepownership=True,
233                          dbname=None, dbhost=None, dbport=None, dbuser=None):
234          dbname = dbname or self.dbname
235          return ['gzip -c %s > %s' % (dbname, backupfile)]
diff --git a/sqlserver.py b/sqlserver.py
@@ -1,6 +1,6 @@
236 -# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
237 +# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
238  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
239  #
240  # This file is part of logilab-database.
241  #
242  # logilab-database is free software: you can redistribute it and/or modify it
diff --git a/sqlserver2005.py b/sqlserver2005.py
@@ -1,6 +1,6 @@
243 -# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
244 +# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
245  # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
246  #
247  # This file is part of logilab-database.
248  #
249  # logilab-database is free software: you can redistribute it and/or modify it
@@ -23,14 +23,14 @@
250  """
251 
252  import os
253  import sys
254  import shutil
255 +import StringIO
256 
257  from logilab import database as db
258  from logilab.database.sqlserver import _PyodbcAdapter, _AdodbapiAdapter
259 -import StringIO
260 
261  class _PyodbcSqlServer2005Adapter(_PyodbcAdapter):
262      driver = "SQL Server Native Client 10.0"
263 
264  class _AdodbapiSqlServer2005Adapter(_AdodbapiAdapter):
@@ -54,16 +54,11 @@
265      TYPE_MAPPING['Date'] =     'smalldatetime'
266      TYPE_MAPPING['Datetime'] = 'datetime'
267      TYPE_MAPPING['Password'] = 'varbinary(255)'
268      TYPE_MAPPING['Bytes'] =    'varbinary(max)'
269      TYPE_MAPPING['SizeConstrainedString'] = 'nvarchar(%s)'
270 -
271 -    def boolean_value(self, value):
272 -        if value:
273 -            return 1
274 -        else:
275 -            return 0
276 +    TYPE_CONVERTERS = db._GenericAdvFuncHelper.TYPE_CONVERTERS.copy()
277 
278      def list_tables(self, cursor):
279          """return the list of tables of a database"""
280          cursor.execute('''sys.sp_tables @table_type = "'TABLE'"''')
281          return [row[2] for row in cursor.fetchall()]
diff --git a/test/unittest_converters.py b/test/unittest_converters.py
@@ -0,0 +1,52 @@
282 +# copyright 2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
283 +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
284 +#
285 +# This file is part of logilab-database.
286 +#
287 +# logilab-database is free software: you can redistribute it and/or modify it
288 +# under the terms of the GNU Lesser General Public License as published by the
289 +# Free Software Foundation, either version 2.1 of the License, or (at your
290 +# option) any later version.
291 +#
292 +# logilab-database is distributed in the hope that it will be useful, but
293 +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
294 +# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
295 +# for more details.
296 +#
297 +# You should have received a copy of the GNU Lesser General Public License along
298 +# with logilab-database. If not, see <http://www.gnu.org/licenses/>.
299 +import unittest
300 +from datetime import datetime, time, date
301 +
302 +from logilab.database import get_db_helper
303 +
304 +
305 +class TYPEConvertersTC(unittest.TestCase):
306 +
307 +    def test_existing_converters(self):
308 +        self.helper = get_db_helper('sqlite')
309 +        self.assertEqual(len(self.helper.TYPE_CONVERTERS), 5)
310 +
311 +    def test_convert_boolean_sqlite(self):
312 +        self.helper = get_db_helper('sqlite')
313 +        self.assertEqual(self.helper.TYPE_CONVERTERS['Boolean'](False), 0)
314 +
315 +    def test_convert_boolean_postgres(self):
316 +        self.helper = get_db_helper('postgres')
317 +        self.assertEqual(self.helper.TYPE_CONVERTERS['Boolean'](False), 'FALSE')
318 +
319 +    def test_convert_datetime(self):
320 +        _date = date(1900,10,1)
321 +        _datetime = datetime(1900, 10, 1, 0, 0)
322 +        self.helper = get_db_helper('sqlite')
323 +        self.assertEqual(self.helper.TYPE_CONVERTERS['Datetime'](_date), _datetime)
324 +
325 +    def test_convert_date(self):
326 +        _date = date(1900,10,1)
327 +        _datetime = datetime(1900, 10, 1, 0, 0)
328 +        self.helper = get_db_helper('sqlite')
329 +        self.assertEqual(self.helper.TYPE_CONVERTERS['Date'](_datetime), _date)
330 +
331 +
332 +if __name__ == '__main__':
333 +    unittest.main()