Package logilab-common-0 :: Package 36 :: Package 1 :: Module adbh
[frames] | no frames]

Source Code for Module logilab-common-0.36.1.adbh

  1  """Helpers for DBMS specific (advanced or non standard) functionalities. 
  2   
  3  Helpers are provided for postgresql, mysql and sqlite. 
  4   
  5  :copyright: 
  6    2000-2008 `LOGILAB S.A. <http://www.logilab.fr>`_ (Paris, FRANCE), 
  7    all rights reserved. 
  8   
  9  :contact: 
 10    http://www.logilab.org/project/logilab-common -- 
 11    mailto:python-projects@logilab.org 
 12   
 13  :license: 
 14    `General Public License version 2 
 15    <http://www.gnu.org/licenses/old-licenses/gpl-2.0.html>`_ 
 16  """ 
 17  __docformat__ = "restructuredtext en" 
 18   
 19  from logilab.common.deprecation import obsolete 
 20   
21 -class BadQuery(Exception): pass
22 -class UnsupportedFunction(BadQuery): pass
23 24
25 -class metafunc(type):
26 - def __new__(mcs, name, bases, dict):
27 dict['name'] = name.upper() 28 return type.__new__(mcs, name, bases, dict)
29 30
31 -class FunctionDescr(object):
32 __metaclass__ = metafunc 33 34 supported_backends = () 35 rtype = None # None <-> returned type should be the same as the first argument 36 aggregat = False 37 minargs = 1 38 maxargs = 1 39 40 name_mapping = {} 41
42 - def __init__(self, name=None, rtype=rtype, aggregat=aggregat):
43 if name is not None: 44 name = name.upper() 45 self.name = name 46 self.rtype = rtype 47 self.aggregat = aggregat
48
49 - def backend_name(self, backend):
50 try: 51 return self.name_mapping[backend] 52 except KeyError: 53 return self.name
54 backend_name = classmethod(backend_name) 55 56 #@classmethod
57 - def check_nbargs(cls, nbargs):
58 if cls.minargs is not None and \ 59 nbargs < cls.minargs: 60 raise BadQuery('not enough argument for function %s' % cls.name) 61 if cls.maxargs is not None and \ 62 nbargs < cls.maxargs: 63 raise BadQuery('too many arguments for function %s' % cls.name)
64 check_nbargs = classmethod(check_nbargs)
65
66 -class AggrFunctionDescr(FunctionDescr):
67 aggregat = True 68 rtype = None
69
70 -class MAX(AggrFunctionDescr): pass
71 -class MIN(AggrFunctionDescr): pass
72 -class SUM(AggrFunctionDescr): pass
73 -class COUNT(AggrFunctionDescr):
74 rtype = 'Int'
75 -class AVG(AggrFunctionDescr):
76 rtype = 'Float'
77
78 -class UPPER(FunctionDescr):
79 rtype = 'String'
80 -class LOWER(FunctionDescr):
81 rtype = 'String'
82 -class IN(FunctionDescr):
83 """this is actually a 'keyword' function...""" 84 maxargs = None
85 -class LENGTH(FunctionDescr):
86 rtype = 'Int'
87
88 -class DATE(FunctionDescr):
89 rtype = 'Date'
90
91 -class RANDOM(FunctionDescr):
92 supported_backends = ('postgres', 'mysql',) 93 rtype = 'Float' 94 minargs = maxargs = 0 95 name_mapping = {'postgres': 'RANDOM', 96 'mysql': 'RAND', 97 }
98
99 -class _GenericAdvFuncHelper:
100 """Generic helper, trying to provide generic way to implement 101 specific functionnalities from others DBMS 102 103 An exception is raised when the functionality is not emulatable 104 """ 105 # DBMS resources descriptors and accessors 106 107 backend_name = None # overriden in subclasses ('postgres', 'sqlite', etc.) 108 needs_from_clause = False 109 union_parentheses_support = True 110 users_support = True 111 groups_support = True 112 ilike_support = True 113 114 FUNCTIONS = { 115 # aggregat functions 116 'MIN': MIN, 'MAX': MAX, 117 'SUM': SUM, 118 'COUNT': COUNT, 119 'AVG': AVG, 120 # transformation functions 121 'UPPER': UPPER, 'LOWER': LOWER, 122 'LENGTH': LENGTH, 123 'DATE': DATE, 124 'RANDOM': RANDOM, 125 # keyword function 126 'IN': IN 127 } 128 129 TYPE_MAPPING = { 130 'String' : 'text', 131 'Int' : 'integer', 132 'Float' : 'float', 133 'Decimal' : 'decimal', 134 'Boolean' : 'boolean', 135 'Date' : 'date', 136 'Time' : 'time', 137 'Datetime' : 'timestamp', 138 'Interval' : 'interval', 139 'Password' : 'bytea', 140 'Bytes' : 'bytea', 141 } 142 143 144 #@classmethod
145 - def register_function(cls, funcdef):
146 if isinstance(funcdef, basestring) : 147 funcdef = FunctionDescr(funcdef.upper()) 148 assert not funcdef.name in cls.FUNCTIONS, \ 149 '%s is already registered' % funcdef.name 150 cls.FUNCTIONS[funcdef.name] = funcdef
151 register_function = classmethod(register_function) 152 153 #@classmethod
154 - def function_description(cls, funcname):
155 """return the description (`FunctionDescription`) for a RQL function""" 156 try: 157 return cls.FUNCTIONS[funcname.upper()] 158 except KeyError: 159 raise UnsupportedFunction(funcname)
160 function_description = classmethod(function_description) 161 162 #@obsolete('use users_support attribute')
163 - def support_users(self):
164 """return True if the DBMS support users (this is usually 165 not true for in memory DBMS) 166 """ 167 return self.users_support
168 support_user = obsolete('use users_support attribute')(support_users) 169 170 #@obsolete('use groups_support attribute')
171 - def support_groups(self):
172 """return True if the DBMS support groups""" 173 return self.groups_support
174 support_user = obsolete('use groups_support attribute')(support_groups) 175
176 - def func_sqlname(self, funcname):
177 funcdef = self.function_description(funcname) 178 return funcdef.backend_name(self.backend_name)
179
180 - def system_database(self):
181 """return the system database for the given driver""" 182 raise NotImplementedError('not supported by this DBMS')
183
184 - def backup_command(self, dbname, dbhost, dbuser, dbpassword, backupfile, 185 keepownership=True):
186 """return a command to backup the given database""" 187 raise NotImplementedError('not supported by this DBMS')
188
189 - def restore_commands(self, dbname, dbhost, dbuser, backupfile, 190 encoding='utf-8', keepownership=True, drop=True):
191 """return a list of commands to restore a backup the given database""" 192 raise NotImplementedError('not supported by this DBMS')
193 194 # helpers to standardize SQL according to the database 195
196 - def sql_current_date(self):
197 return 'CURRENT_DATE'
198
199 - def sql_current_time(self):
200 return 'CURRENT_TIME'
201
202 - def sql_current_timestamp(self):
203 return 'CURRENT_TIMESTAMP'
204
205 - def sql_create_sequence(self, seq_name):
206 return '''CREATE TABLE %s (last INTEGER); 207 INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
208
209 - def sql_create_index(self, table, column, unique=False):
210 idx = self._index_name(table, column, unique) 211 if unique: 212 return 'ALTER TABLE %s ADD UNIQUE(%s)' % (table, column) 213 else: 214 return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column)
215
216 - def sql_drop_index(self, table, column, unique=False):
217 idx = self._index_name(table, column, unique) 218 if unique: 219 return 'ALTER TABLE %s DROP CONSTRAINT %s' % (table, idx) 220 else: 221 return 'DROP INDEX %s' % idx
222
223 - def sql_drop_sequence(self, seq_name):
224 return 'DROP TABLE %s;' % seq_name
225
226 - def sqls_increment_sequence(self, seq_name):
227 return ('UPDATE %s SET last=last+1;' % seq_name, 228 'SELECT last FROM %s;' % seq_name)
229
230 - def sql_temporary_table(self, table_name, table_schema, 231 drop_on_commit=True):
232 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
233
234 - def boolean_value(self, value):
235 if value: 236 return 'TRUE' 237 else: 238 return 'FALSE'
239
240 - def increment_sequence(self, cursor, seq_name):
241 for sql in self.sqls_increment_sequence(seq_name): 242 cursor.execute(sql) 243 return cursor.fetchone()[0]
244
245 - def create_user(self, cursor, user, password):
246 """create a new database user""" 247 if not self.users_support: 248 raise NotImplementedError('not supported by this DBMS') 249 cursor.execute("CREATE USER %(user)s " 250 "WITH PASSWORD '%(password)s'" % locals())
251
252 - def _index_name(self, table, column, unique=False):
253 if unique: 254 # note: this naming is consistent with indices automatically 255 # created by postgres when UNIQUE appears in a table schema 256 return '%s_%s_key' % (table.lower(), column.lower()) 257 else: 258 return '%s_%s_idx' % (table.lower(), column.lower())
259
260 - def create_index(self, cursor, table, column, unique=False):
261 if not self.index_exists(cursor, table, column, unique): 262 cursor.execute(self.sql_create_index(table, column, unique))
263
264 - def drop_index(self, cursor, table, column, unique=False):
265 if self.index_exists(cursor, table, column, unique): 266 cursor.execute(self.sql_drop_index(table, column, unique))
267
268 - def index_exists(self, cursor, table, column, unique=False):
269 idx = self._index_name(table, column, unique) 270 return idx in self.list_indices(cursor, table)
271
272 - def user_exists(self, cursor, username):
273 """return True if a user with the given username exists""" 274 return username in self.list_users(cursor)
275
276 - def list_users(self, cursor):
277 """return the list of existing database users""" 278 raise NotImplementedError('not supported by this DBMS')
279
280 - def create_database(self, cursor, dbname, owner=None, encoding='utf-8'):
281 """create a new database""" 282 raise NotImplementedError('not supported by this DBMS')
283
284 - def list_databases(self):
285 """return the list of existing databases""" 286 raise NotImplementedError('not supported by this DBMS')
287
288 - def list_tables(self, cursor):
289 """return the list of tables of a database""" 290 raise NotImplementedError('not supported by this DBMS')
291
292 - def list_indices(self, cursor, table=None):
293 """return the list of indices of a database, only for the given table if specified""" 294 raise NotImplementedError('not supported by this DBMS')
295 296 297
298 -def pgdbcmd(cmd, dbhost, dbuser):
299 cmd = [cmd] 300 if dbhost: 301 cmd.append('--host=%s' % dbhost) 302 if dbuser: 303 cmd.append('--username=%s' % dbuser) 304 return cmd
305 306
307 -class _PGAdvFuncHelper(_GenericAdvFuncHelper):
308 """Postgres helper, taking advantage of postgres SEQUENCE support 309 """ 310 backend_name = 'postgres' 311 # modifiable but should not be shared 312 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() 313
314 - def system_database(self):
315 """return the system database for the given driver""" 316 return 'template1'
317
318 - def backup_command(self, dbname, dbhost, dbuser, backupfile, 319 keepownership=True):
320 """return a command to backup the given database""" 321 cmd = ['pg_dump -Fc'] 322 if dbhost: 323 cmd.append('--host=%s' % dbhost) 324 if dbuser: 325 cmd.append('--username=%s' % dbuser) 326 if not keepownership: 327 cmd.append('--no-owner') 328 cmd.append('--file=%s' % backupfile) 329 cmd.append(dbname) 330 return ' '.join(cmd)
331
332 - def restore_commands(self, dbname, dbhost, dbuser, backupfile, 333 encoding='utf-8', keepownership=True, drop=True):
334 """return a list of commands to restore a backup the given database""" 335 cmds = [] 336 if drop: 337 cmd = pgdbcmd('dropdb', dbhost, dbuser) 338 cmd.append(dbname) 339 cmds.append(' '.join(cmd)) 340 cmd = pgdbcmd('createdb -T template0 -E %s' % encoding, dbhost, dbuser) 341 cmd.append(dbname) 342 cmds.append(' '.join(cmd)) 343 cmd = pgdbcmd('pg_restore -Fc', dbhost, dbuser) 344 cmd.append('--dbname %s' % dbname) 345 if not keepownership: 346 cmd.append('--no-owner') 347 cmd.append(backupfile) 348 cmds.append(' '.join(cmd)) 349 return cmds
350
351 - def sql_create_sequence(self, seq_name):
352 return 'CREATE SEQUENCE %s;' % seq_name
353
354 - def sql_drop_sequence(self, seq_name):
355 return 'DROP SEQUENCE %s;' % seq_name
356
357 - def sqls_increment_sequence(self, seq_name):
358 return ("SELECT nextval('%s');" % seq_name,)
359
360 - def sql_temporary_table(self, table_name, table_schema, 361 drop_on_commit=True):
362 if not drop_on_commit: 363 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, 364 table_schema) 365 return "CREATE TEMPORARY TABLE %s (%s) ON COMMIT DROP;" % (table_name, 366 table_schema)
367
368 - def create_database(self, cursor, dbname, owner=None, encoding='utf-8'):
369 """create a new database""" 370 sql = "CREATE DATABASE %(dbname)s" 371 if owner: 372 sql += " WITH OWNER=%(owner)s" 373 if encoding: 374 sql += " ENCODING='%(encoding)s'" 375 cursor.execute(sql % locals())
376
377 - def create_language(self, cursor, extlang):
378 """postgres specific method to install a procedural language on a database""" 379 # make sure plpythonu is not directly in template1 380 cursor.execute("SELECT * FROM pg_language WHERE lanname='%s';" % extlang) 381 if cursor.fetchall(): 382 print '%s language already installed' % extlang 383 else: 384 cursor.execute('CREATE LANGUAGE %s' % extlang) 385 print '%s language installed' % extlang
386
387 - def list_users(self, cursor, username=None):
388 """return the list of existing database users""" 389 if username: 390 warn('username argument is deprecated, use user_exists method', 391 DeprecationWarning, stacklevel=2) 392 return self.user_exists(cursor, username) 393 cursor.execute("SELECT usename FROM pg_user") 394 return [r[0] for r in cursor.fetchall()]
395
396 - def list_databases(self, cursor):
397 """return the list of existing databases""" 398 cursor.execute('SELECT datname FROM pg_database') 399 return [r[0] for r in cursor.fetchall()]
400
401 - def list_tables(self, cursor):
402 """return the list of tables of a database""" 403 cursor.execute("SELECT tablename FROM pg_tables") 404 return [r[0] for r in cursor.fetchall()]
405
406 - def list_indices(self, cursor, table=None):
407 """return the list of indices of a database, only for the given table if specified""" 408 sql = "SELECT indexname FROM pg_indexes" 409 if table: 410 sql += " WHERE LOWER(tablename)='%s'" % table.lower() 411 cursor.execute(sql) 412 return [r[0] for r in cursor.fetchall()]
413 414
415 -class _SqliteAdvFuncHelper(_GenericAdvFuncHelper):
416 """Generic helper, trying to provide generic way to implement 417 specific functionnalities from others DBMS 418 419 An exception is raised when the functionality is not emulatable 420 """ 421 backend_name = 'sqlite' 422 # modifiable but should not be shared 423 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() 424 FUNCTIONS.pop('RANDOM') # not defined in sqlite 425 426 users_support = groups_support = False 427 ilike_support = False 428 union_parentheses_support = False 429
430 - def sql_create_index(self, table, column, unique=False):
431 idx = self._index_name(table, column, unique) 432 if unique: 433 return 'CREATE UNIQUE INDEX %s ON %s(%s);' % (idx, table, column) 434 else: 435 return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column)
436
437 - def sql_drop_index(self, table, column, unique=False):
438 return 'DROP INDEX %s' % self._index_name(table, column, unique)
439
440 - def list_tables(self, cursor):
441 """return the list of tables of a database""" 442 # filter type='table' else we get indices as well 443 cursor.execute("SELECT name FROM sqlite_master WHERE type='table'") 444 return [r[0] for r in cursor.fetchall()]
445
446 - def list_indices(self, cursor, table=None):
447 """return the list of indices of a database, only for the given table if specified""" 448 sql = "SELECT name FROM sqlite_master WHERE type='index'" 449 if table: 450 sql += " AND LOWER(tbl_name)='%s'" % table.lower() 451 cursor.execute(sql) 452 return [r[0] for r in cursor.fetchall()]
453 454
455 -class _MyAdvFuncHelper(_GenericAdvFuncHelper):
456 """MySQL helper, taking advantage of postgres SEQUENCE support 457 """ 458 backend_name = 'mysql' 459 needs_from_clause = True 460 ilike_support = False # insensitive search by default 461 462 # modifiable but should not be shared 463 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() 464 TYPE_MAPPING = _GenericAdvFuncHelper.TYPE_MAPPING.copy() 465 TYPE_MAPPING['Password'] = 'tinyblob' 466 TYPE_MAPPING['String'] = 'mediumtext' 467 TYPE_MAPPING['Bytes'] = 'longblob' 468
469 - def system_database(self):
470 """return the system database for the given driver""" 471 return ''
472
473 - def backup_command(self, dbname, dbhost, dbuser, backupfile, 474 keepownership=True):
475 """return a command to backup the given database""" 476 # XXX compress 477 return 'mysqldump -h %s -u %s -p -r %s %s' % (dbhost, dbuser, backupfile, dbname)
478
479 - def restore_commands(self, dbname, dbhost, dbuser, backupfile, 480 encoding='utf-8', keepownership=True, drop=True):
481 """return a list of commands to restore a backup the given database""" 482 cmds = [] 483 if drop: 484 cmd = 'echo "DROP DATABASE %s;" | mysql -h %s -u %s -p' % ( 485 dbname, dbhost, dbuser) 486 cmds.append(cmd) 487 cmd = 'echo "%s;" | mysql -h %s -u %s -p' % ( 488 self.sql_create_database(dbname, encoding), dbhost, dbuser) 489 cmds.append(cmd) 490 cmd = 'mysql -h %s -u %s -p %s < %s' % (dbhost, dbuser, dbname, backupfile) 491 cmds.append(cmd) 492 return cmds
493
494 - def sql_temporary_table(self, table_name, table_schema, 495 drop_on_commit=True):
496 if not drop_on_commit: 497 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, 498 table_schema) 499 return "CREATE TEMPORARY TABLE %s (%s) ON COMMIT DROP;" % (table_name, 500 table_schema)
501
502 - def sql_create_database(self, dbname, encoding='utf-8'):
503 sql = "CREATE DATABASE %(dbname)s" 504 if encoding: 505 sql += " CHARACTER SET %(encoding)s" 506 return sql % locals()
507
508 - def create_database(self, cursor, dbname, owner=None, encoding='utf-8'):
509 """create a new database""" 510 cursor.execute(self.sql_create_database(dbname, encoding)) 511 if owner: 512 cursor.execute('GRANT ALL ON `%s`.* to %s' % (dbname, owner))
513
514 - def boolean_value(self, value):
515 if value: 516 return True 517 else: 518 return False
519
520 - def list_users(self, cursor):
521 """return the list of existing database users""" 522 # Host, Password 523 cursor.execute("SELECT User FROM mysql.user") 524 return [r[0] for r in cursor.fetchall()]
525
526 - def list_databases(self, cursor):
527 """return the list of existing databases""" 528 cursor.execute('SHOW DATABASES') 529 return [r[0] for r in cursor.fetchall()]
530
531 - def list_tables(self, cursor):
532 """return the list of tables of a database""" 533 cursor.execute("SHOW TABLES") 534 return [r[0] for r in cursor.fetchall()]
535
536 - def list_indices(self, cursor, table=None):
537 """return the list of indices of a database, only for the given table if specified""" 538 if table: 539 cursor.execute("SHOW INDEX FROM %s" % table) 540 return [r[2] for r in cursor.fetchall()] 541 allindices = [] 542 for table in self.list_tables(cursor): 543 allindices += self.list_indices(cursor, table) 544 return allindices
545 546 547 548 ADV_FUNC_HELPER_DIRECTORY = {'postgres': _PGAdvFuncHelper(), 549 'sqlite': _SqliteAdvFuncHelper(), 550 'mysql': _MyAdvFuncHelper(), 551 } 552 553 554
555 -def get_adv_func_helper(driver):
556 """returns an advanced function helper for the given driver""" 557 return ADV_FUNC_HELPER_DIRECTORY[driver]
558
559 -def register_function(driver, funcdef):
560 ADV_FUNC_HELPER_DIRECTORY[driver].register_function(funcdef)
561 562 # this function should be called `register_function` but the other 563 # definition was defined prior to this one
564 -def auto_register_function(funcdef):
565 """register the function `funcdef` on supported backends""" 566 for driver in funcdef.supported_backends: 567 register_function(driver, funcdef)
568