Package logilab :: Package common :: Module adbh
[frames] | no frames]

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