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

Source Code for Module logilab-common-0.39.0.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 intersect_all_support = True 111 users_support = True 112 groups_support = True 113 ilike_support = True 114 115 FUNCTIONS = { 116 # aggregat functions 117 'MIN': MIN, 'MAX': MAX, 118 'SUM': SUM, 119 'COUNT': COUNT, 120 'AVG': AVG, 121 # transformation functions 122 'UPPER': UPPER, 'LOWER': LOWER, 123 'LENGTH': LENGTH, 124 'DATE': DATE, 125 'RANDOM': RANDOM, 126 # keyword function 127 'IN': IN 128 } 129 130 TYPE_MAPPING = { 131 'String' : 'text', 132 'Int' : 'integer', 133 'Float' : 'float', 134 'Decimal' : 'decimal', 135 'Boolean' : 'boolean', 136 'Date' : 'date', 137 'Time' : 'time', 138 'Datetime' : 'timestamp', 139 'Interval' : 'interval', 140 'Password' : 'bytea', 141 'Bytes' : 'bytea', 142 } 143 144 145 #@classmethod
146 - def register_function(cls, funcdef):
147 if isinstance(funcdef, basestring) : 148 funcdef = FunctionDescr(funcdef.upper()) 149 assert not funcdef.name in cls.FUNCTIONS, \ 150 '%s is already registered' % funcdef.name 151 cls.FUNCTIONS[funcdef.name] = funcdef
152 register_function = classmethod(register_function) 153 154 #@classmethod
155 - def function_description(cls, funcname):
156 """return the description (`FunctionDescription`) for a RQL function""" 157 try: 158 return cls.FUNCTIONS[funcname.upper()] 159 except KeyError: 160 raise UnsupportedFunction(funcname)
161 function_description = classmethod(function_description) 162 163 #@obsolete('use users_support attribute')
164 - def support_users(self):
165 """return True if the DBMS support users (this is usually 166 not true for in memory DBMS) 167 """ 168 return self.users_support
169 support_user = obsolete('use users_support attribute')(support_users) 170 171 #@obsolete('use groups_support attribute')
172 - def support_groups(self):
173 """return True if the DBMS support groups""" 174 return self.groups_support
175 support_user = obsolete('use groups_support attribute')(support_groups) 176
177 - def func_sqlname(self, funcname):
178 funcdef = self.function_description(funcname) 179 return funcdef.backend_name(self.backend_name)
180
181 - def system_database(self):
182 """return the system database for the given driver""" 183 raise NotImplementedError('not supported by this DBMS')
184
185 - def backup_command(self, dbname, dbhost, dbuser, dbpassword, backupfile, 186 keepownership=True):
187 """return a command to backup the given database""" 188 raise NotImplementedError('not supported by this DBMS')
189
190 - def restore_commands(self, dbname, dbhost, dbuser, backupfile, 191 encoding='utf-8', keepownership=True, drop=True):
192 """return a list of commands to restore a backup the given database""" 193 raise NotImplementedError('not supported by this DBMS')
194 195 # helpers to standardize SQL according to the database 196
197 - def sql_current_date(self):
198 return 'CURRENT_DATE'
199
200 - def sql_current_time(self):
201 return 'CURRENT_TIME'
202
203 - def sql_current_timestamp(self):
204 return 'CURRENT_TIMESTAMP'
205
206 - def sql_create_sequence(self, seq_name):
207 return '''CREATE TABLE %s (last INTEGER); 208 INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
209
210 - def sql_create_index(self, table, column, unique=False):
211 idx = self._index_name(table, column, unique) 212 if unique: 213 return 'ALTER TABLE %s ADD UNIQUE(%s)' % (table, column) 214 else: 215 return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column)
216
217 - def sql_drop_index(self, table, column, unique=False):
218 idx = self._index_name(table, column, unique) 219 if unique: 220 return 'ALTER TABLE %s DROP CONSTRAINT %s' % (table, idx) 221 else: 222 return 'DROP INDEX %s' % idx
223
224 - def sql_drop_sequence(self, seq_name):
225 return 'DROP TABLE %s;' % seq_name
226
227 - def sqls_increment_sequence(self, seq_name):
228 return ('UPDATE %s SET last=last+1;' % seq_name, 229 'SELECT last FROM %s;' % seq_name)
230
231 - def sql_temporary_table(self, table_name, table_schema, 232 drop_on_commit=True):
233 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
234
235 - def boolean_value(self, value):
236 if value: 237 return 'TRUE' 238 else: 239 return 'FALSE'
240
241 - def increment_sequence(self, cursor, seq_name):
242 for sql in self.sqls_increment_sequence(seq_name): 243 cursor.execute(sql) 244 return cursor.fetchone()[0]
245
246 - def create_user(self, cursor, user, password):
247 """create a new database user""" 248 if not self.users_support: 249 raise NotImplementedError('not supported by this DBMS') 250 cursor.execute("CREATE USER %(user)s " 251 "WITH PASSWORD '%(password)s'" % locals())
252
253 - def _index_name(self, table, column, unique=False):
254 if unique: 255 # note: this naming is consistent with indices automatically 256 # created by postgres when UNIQUE appears in a table schema 257 return '%s_%s_key' % (table.lower(), column.lower()) 258 else: 259 return '%s_%s_idx' % (table.lower(), column.lower())
260
261 - def create_index(self, cursor, table, column, unique=False):
262 if not self.index_exists(cursor, table, column, unique): 263 cursor.execute(self.sql_create_index(table, column, unique))
264
265 - def drop_index(self, cursor, table, column, unique=False):
266 if self.index_exists(cursor, table, column, unique): 267 cursor.execute(self.sql_drop_index(table, column, unique))
268
269 - def index_exists(self, cursor, table, column, unique=False):
270 idx = self._index_name(table, column, unique) 271 return idx in self.list_indices(cursor, table)
272
273 - def user_exists(self, cursor, username):
274 """return True if a user with the given username exists""" 275 return username in self.list_users(cursor)
276
277 - def list_users(self, cursor):
278 """return the list of existing database users""" 279 raise NotImplementedError('not supported by this DBMS')
280
281 - def create_database(self, cursor, dbname, owner=None, encoding='utf-8'):
282 """create a new database""" 283 raise NotImplementedError('not supported by this DBMS')
284
285 - def list_databases(self):
286 """return the list of existing databases""" 287 raise NotImplementedError('not supported by this DBMS')
288
289 - def list_tables(self, cursor):
290 """return the list of tables of a database""" 291 raise NotImplementedError('not supported by this DBMS')
292
293 - def list_indices(self, cursor, table=None):
294 """return the list of indices of a database, only for the given table if specified""" 295 raise NotImplementedError('not supported by this DBMS')
296 297 298
299 -def pgdbcmd(cmd, dbhost, dbuser):
300 cmd = [cmd] 301 if dbhost: 302 cmd.append('--host=%s' % dbhost) 303 if dbuser: 304 cmd.append('--username=%s' % dbuser) 305 return cmd
306 307
308 -class _PGAdvFuncHelper(_GenericAdvFuncHelper):
309 """Postgres helper, taking advantage of postgres SEQUENCE support 310 """ 311 backend_name = 'postgres' 312 # modifiable but should not be shared 313 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() 314
315 - def system_database(self):
316 """return the system database for the given driver""" 317 return 'template1'
318
319 - def backup_command(self, dbname, dbhost, dbuser, backupfile, 320 keepownership=True):
321 """return a command to backup the given database""" 322 cmd = ['pg_dump -Fc'] 323 if dbhost: 324 cmd.append('--host=%s' % dbhost) 325 if dbuser: 326 cmd.append('--username=%s' % dbuser) 327 if not keepownership: 328 cmd.append('--no-owner') 329 cmd.append('--file=%s' % backupfile) 330 cmd.append(dbname) 331 return ' '.join(cmd)
332
333 - def restore_commands(self, dbname, dbhost, dbuser, backupfile, 334 encoding='utf-8', keepownership=True, drop=True):
335 """return a list of commands to restore a backup the given database""" 336 cmds = [] 337 if drop: 338 cmd = pgdbcmd('dropdb', dbhost, dbuser) 339 cmd.append(dbname) 340 cmds.append(' '.join(cmd)) 341 cmd = pgdbcmd('createdb -T template0 -E %s' % encoding, dbhost, dbuser) 342 cmd.append(dbname) 343 cmds.append(' '.join(cmd)) 344 cmd = pgdbcmd('pg_restore -Fc', dbhost, dbuser) 345 cmd.append('--dbname %s' % dbname) 346 if not keepownership: 347 cmd.append('--no-owner') 348 cmd.append(backupfile) 349 cmds.append(' '.join(cmd)) 350 return cmds
351
352 - def sql_create_sequence(self, seq_name):
353 return 'CREATE SEQUENCE %s;' % seq_name
354
355 - def sql_drop_sequence(self, seq_name):
356 return 'DROP SEQUENCE %s;' % seq_name
357
358 - def sqls_increment_sequence(self, seq_name):
359 return ("SELECT nextval('%s');" % seq_name,)
360
361 - def sql_temporary_table(self, table_name, table_schema, 362 drop_on_commit=True):
363 if not drop_on_commit: 364 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, 365 table_schema) 366 return "CREATE TEMPORARY TABLE %s (%s) ON COMMIT DROP;" % (table_name, 367 table_schema)
368
369 - def create_database(self, cursor, dbname, owner=None, encoding='utf-8'):
370 """create a new database""" 371 sql = "CREATE DATABASE %(dbname)s" 372 if owner: 373 sql += " WITH OWNER=%(owner)s" 374 if encoding: 375 sql += " ENCODING='%(encoding)s'" 376 cursor.execute(sql % locals())
377
378 - def create_language(self, cursor, extlang):
379 """postgres specific method to install a procedural language on a database""" 380 # make sure plpythonu is not directly in template1 381 cursor.execute("SELECT * FROM pg_language WHERE lanname='%s';" % extlang) 382 if cursor.fetchall(): 383 print '%s language already installed' % extlang 384 else: 385 cursor.execute('CREATE LANGUAGE %s' % extlang) 386 print '%s language installed' % extlang
387
388 - def list_users(self, cursor):
389 """return the list of existing database users""" 390 cursor.execute("SELECT usename FROM pg_user") 391 return [r[0] for r in cursor.fetchall()]
392
393 - def list_databases(self, cursor):
394 """return the list of existing databases""" 395 cursor.execute('SELECT datname FROM pg_database') 396 return [r[0] for r in cursor.fetchall()]
397
398 - def list_tables(self, cursor):
399 """return the list of tables of a database""" 400 cursor.execute("SELECT tablename FROM pg_tables") 401 return [r[0] for r in cursor.fetchall()]
402
403 - def list_indices(self, cursor, table=None):
404 """return the list of indices of a database, only for the given table if specified""" 405 sql = "SELECT indexname FROM pg_indexes" 406 if table: 407 sql += " WHERE LOWER(tablename)='%s'" % table.lower() 408 cursor.execute(sql) 409 return [r[0] for r in cursor.fetchall()]
410 411
412 -class _SqliteAdvFuncHelper(_GenericAdvFuncHelper):
413 """Generic helper, trying to provide generic way to implement 414 specific functionnalities from others DBMS 415 416 An exception is raised when the functionality is not emulatable 417 """ 418 backend_name = 'sqlite' 419 # modifiable but should not be shared 420 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() 421 FUNCTIONS.pop('RANDOM') # not defined in sqlite 422 423 users_support = groups_support = False 424 ilike_support = False 425 union_parentheses_support = False 426 intersect_all_support = False 427
428 - def sql_create_index(self, table, column, unique=False):
429 idx = self._index_name(table, column, unique) 430 if unique: 431 return 'CREATE UNIQUE INDEX %s ON %s(%s);' % (idx, table, column) 432 else: 433 return 'CREATE INDEX %s ON %s(%s);' % (idx, table, column)
434
435 - def sql_drop_index(self, table, column, unique=False):
436 return 'DROP INDEX %s' % self._index_name(table, column, unique)
437
438 - def list_tables(self, cursor):
439 """return the list of tables of a database""" 440 # filter type='table' else we get indices as well 441 cursor.execute("SELECT name FROM sqlite_master WHERE type='table'") 442 return [r[0] for r in cursor.fetchall()]
443
444 - def list_indices(self, cursor, table=None):
445 """return the list of indices of a database, only for the given table if specified""" 446 sql = "SELECT name FROM sqlite_master WHERE type='index'" 447 if table: 448 sql += " AND LOWER(tbl_name)='%s'" % table.lower() 449 cursor.execute(sql) 450 return [r[0] for r in cursor.fetchall()]
451 452
453 -class _MyAdvFuncHelper(_GenericAdvFuncHelper):
454 """MySQL helper, taking advantage of postgres SEQUENCE support 455 """ 456 backend_name = 'mysql' 457 needs_from_clause = True 458 ilike_support = False # insensitive search by default 459 460 # modifiable but should not be shared 461 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy() 462 TYPE_MAPPING = _GenericAdvFuncHelper.TYPE_MAPPING.copy() 463 TYPE_MAPPING['Password'] = 'tinyblob' 464 TYPE_MAPPING['String'] = 'mediumtext' 465 TYPE_MAPPING['Bytes'] = 'longblob' 466 # don't use timestamp which is automatically updated on row update 467 TYPE_MAPPING['Datetime'] = 'datetime' 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