Add a SQL_CONVERTERS dict to allow possible conversions to SQL for specific types.

Now, the dbhelper stores converters for different base types, that could be overwritten by external processes.

authorVincent Michel <vincent.michel@logilab.fr>
changeset54a7e5ad624f
branchdefault
phasedraft
hiddenyes
parent revision#87f26f15a66e merge with stable
child revision#e8d100e527fc [sqlgen] Allow insertion SQL functions rather than values in SQL INSERT., #6a80c4669354 [sqlgen] Allow insertion SQL functions rather than values in SQL INSERT.
files modified by this revision
__init__.py
mysql.py
postgres.py
sqlite.py
test/unittest_converters.py
# HG changeset patch
# User Vincent Michel <vincent.michel@logilab.fr>
# Date 1365409064 -7200
# Mon Apr 08 10:17:44 2013 +0200
# Node ID 54a7e5ad624f85907c637a84cb429ae564c7797c
# Parent 87f26f15a66eaa6caf9173a315f2b0a05c31b719
Add a SQL_CONVERTERS dict to allow possible conversions to SQL for specific types.

Now, the dbhelper stores converters for different base types, that could be overwritten by external processes.

diff --git a/__init__.py b/__init__.py
@@ -39,14 +39,14 @@
1  __docformat__ = "restructuredtext en"
2 
3  import sys
4  import threading
5  import logging
6 -from datetime import datetime, time
7 +from datetime import datetime, time, date
8 
9  from logilab.common.modutils import load_module_from_name
10 -from logilab.common.date import utcdatetime, utctime
11 +from logilab.common.date import todate, todatetime, utcdatetime, utctime
12 
13  _LOGGER = logging.getLogger('logilab.database')
14 
15  USE_MX_DATETIME = False
16 
@@ -664,10 +664,42 @@
17              raise
18 
19      def copy(self):
20          return _TypeMapping(dict.copy(self))
21 
22 +### CONVERTERS ###
23 +def convert_datetime(value):
24 +    # Note: use is __class__ since issubclass(datetime, date)
25 +    if type(value) is date:
26 +        value = todatetime(value)
27 +    return value
28 +
29 +def convert_date(value):
30 +    if isinstance(value, datetime):
31 +        value = todate(value)
32 +    return value
33 +
34 +def convert_tzdatetime(value):
35 +    # Note: use is __class__ since issubclass(datetime, date)
36 +    if type(value) is date:
37 +        value = todatetime(value)
38 +    elif getattr(value, 'tzinfo', None):
39 +        value = utcdatetime(value)
40 +    return value
41 +
42 +def convert_tztime(value):
43 +    if getattr(value, 'tzinfo', None):
44 +        value = utctime(value)
45 +    return value
46 +
47 +def convert_boolean(value):
48 +    if value:
49 +        return 'TRUE'
50 +    else:
51 +        return 'FALSE'
52 +
53 +
54  class _GenericAdvFuncHelper(FTIndexerMixIn):
55      """Generic helper, trying to provide generic way to implement
56      specific functionalities from others DBMS
57 
58      An exception is raised when the functionality is not emulatable
@@ -688,10 +720,19 @@
59          'Time' :     'time',
60          'Datetime' : 'timestamp',
61          'Interval' : 'interval',
62          })
63 
64 +    SQL_CONVERTERS = {
65 +        'Boolean': convert_boolean,
66 +        # XXX needed for sqlite but I don't think it is for other backends
67 +        'Datetime': convert_datetime,
68 +        'Date': convert_date,
69 +        'TZDatetime': convert_tzdatetime,
70 +        'TZTime': convert_tztime,
71 +        }
72 +
73      # DBMS resources descriptors and accessors
74      backend_name = None # overridden in subclasses ('postgres', 'sqlite', etc.)
75      needs_from_clause = False
76      union_parentheses_support = True
77      intersect_all_support = True
@@ -930,15 +971,12 @@
78      def sql_temporary_table(self, table_name, table_schema,
79                              drop_on_commit=True):
80          table_name = self.temporary_table_name(table_name)
81          return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
82 
83 -    def boolean_value(self, value):
84 -        if value:
85 -            return 'TRUE'
86 -        else:
87 -            return 'FALSE'
88 +    def boolean_value(self, value): # XXX deprecate?
89 +        return self.SQL_CONVERTERS['Boolean'](value)
90 
91      def binary_value(self, value):
92          """convert a value to a python object known by the driver to
93          be mapped to a binary column"""
94          return self.dbapi_module.Binary(value)
diff --git a/mysql.py b/mysql.py
@@ -126,10 +126,15 @@
95 
96  db._PREFERED_DRIVERS['mysql'] = ['MySQLdb']#, 'pyMySQL.MySQL']
97  db._ADAPTER_DIRECTORY['mysql'] = {'MySQLdb': _MySqlDBAdapter,
98                           }
99 
100 +def mysql_convert_boolean(value):
101 +    if value:
102 +        return 1
103 +    else:
104 +        return 0
105 
106  class _MyAdvFuncHelper(db._GenericAdvFuncHelper):
107      """MySQL helper, taking advantage of postgres SEQUENCE support
108      """
109      backend_name = 'mysql'
@@ -142,10 +147,13 @@
110      TYPE_MAPPING['String'] = 'mediumtext'
111      TYPE_MAPPING['Bytes'] = 'longblob'
112      # don't use timestamp which is automatically updated on row update
113      TYPE_MAPPING['Datetime'] = 'datetime'
114 
115 +    SQL_CONVERTERS = db._GenericAdvFuncHelper.SQL_CONVERTERS.copy()
116 +    SQL_CONVERTERS['Boolean'] = mysql_convert_boolean
117 +
118      def mycmd(self, cmd, dbhost, dbport, dbuser):
119          cmd = [cmd]
120          # XXX compress
121          dbhost = dbhost or self.dbhost
122          if dbhost is not None:
@@ -225,16 +233,10 @@
123 
124      def sql_regexp_match_expression(self, pattern):
125          """pattern matching using regexp"""
126          return "REGEXP %s" % pattern
127 
128 -    def boolean_value(self, value):
129 -        if value:
130 -            return 1
131 -        else:
132 -            return 0
133 -
134      def list_users(self, cursor):
135          """return the list of existing database users"""
136          # Host, Password
137          cursor.execute("SELECT User FROM mysql.user")
138          return [r[0] for r in cursor.fetchall()]
diff --git a/postgres.py b/postgres.py
@@ -187,10 +187,11 @@
139      backend_name = 'postgres'
140      TYPE_MAPPING = db._GenericAdvFuncHelper.TYPE_MAPPING.copy()
141      TYPE_MAPPING.update({
142          'TZTime' :   'time with time zone',
143          'TZDatetime':'timestamp with time zone'})
144 +    SQL_CONVERTERS = db._GenericAdvFuncHelper.SQL_CONVERTERS.copy()
145 
146      def pgdbcmd(self, cmd, dbhost, dbport, dbuser, *args):
147          cmd = [cmd]
148          cmd += args
149          if dbhost or self.dbhost:
diff --git a/sqlite.py b/sqlite.py
@@ -196,10 +196,15 @@
150  db._ADAPTER_DIRECTORY['sqlite'] = {
151      'pysqlite2.dbapi2' : _PySqlite2Adapter,
152      'sqlite3' : _PySqlite2Adapter,
153      }
154 
155 +def sqlite_convert_boolean(value):
156 +    if value:
157 +        return 1
158 +    else:
159 +        return 0
160 
161  class _SqliteAdvFuncHelper(db._GenericAdvFuncHelper):
162      """Generic helper, trying to provide generic way to implement
163      specific functionalities from others DBMS
164 
@@ -210,16 +215,12 @@
165      users_support = groups_support = False
166      ilike_support = False
167      union_parentheses_support = False
168      intersect_all_support = False
169      alter_column_support = False
170 -
171 -    def boolean_value(self, value):
172 -        if value:
173 -            return 1
174 -        else:
175 -            return 0
176 +    SQL_CONVERTERS = db._GenericAdvFuncHelper.SQL_CONVERTERS.copy()
177 +    SQL_CONVERTERS['Boolean'] = sqlite_convert_boolean
178 
179      def backup_commands(self, backupfile, keepownership=True,
180                          dbname=None, dbhost=None, dbport=None, dbuser=None):
181          dbname = dbname or self.dbname
182          return ['gzip -c %s > %s' % (dbname, backupfile)]
diff --git a/test/unittest_converters.py b/test/unittest_converters.py
@@ -0,0 +1,52 @@
183 +# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
184 +# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
185 +#
186 +# This file is part of logilab-database.
187 +#
188 +# logilab-database is free software: you can redistribute it and/or modify it
189 +# under the terms of the GNU Lesser General Public License as published by the
190 +# Free Software Foundation, either version 2.1 of the License, or (at your
191 +# option) any later version.
192 +#
193 +# logilab-database is distributed in the hope that it will be useful, but
194 +# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
195 +# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
196 +# for more details.
197 +#
198 +# You should have received a copy of the GNU Lesser General Public License along
199 +# with logilab-database. If not, see <http://www.gnu.org/licenses/>.
200 +import unittest
201 +from datetime import datetime, time, date
202 +
203 +from logilab.database import get_db_helper
204 +
205 +
206 +class SQLConvertersTC(unittest.TestCase):
207 +
208 +    def test_existing_converters(self):
209 +        self.helper = get_db_helper('sqlite')
210 +        self.assertEqual(len(self.helper.SQL_CONVERTERS), 5)
211 +
212 +    def test_convert_boolean_sqlite(self):
213 +        self.helper = get_db_helper('sqlite')
214 +        self.assertEqual(self.helper.SQL_CONVERTERS['Boolean'](False), 0)
215 +
216 +    def test_convert_boolean_postgres(self):
217 +        self.helper = get_db_helper('postgres')
218 +        self.assertEqual(self.helper.SQL_CONVERTERS['Boolean'](False), 'FALSE')
219 +
220 +    def test_convert_datetime(self):
221 +        _date = date(1900,10,1)
222 +        _datetime = datetime(1900, 10, 1, 0, 0)
223 +        self.helper = get_db_helper('sqlite')
224 +        self.assertEqual(self.helper.SQL_CONVERTERS['Datetime'](_date), _datetime)
225 +
226 +    def test_convert_date(self):
227 +        _date = date(1900,10,1)
228 +        _datetime = datetime(1900, 10, 1, 0, 0)
229 +        self.helper = get_db_helper('sqlite')
230 +        self.assertEqual(self.helper.SQL_CONVERTERS['Date'](_datetime), _date)
231 +
232 +
233 +if __name__ == '__main__':
234 +    unittest.main()