Package logilab-common-0 ::
Package 36 ::
Package 1 ::
Module 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
23
24
29
30
65
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):
77
78 -class UPPER(FunctionDescr):
80 -class LOWER(FunctionDescr):
82 -class IN(FunctionDescr):
83 """this is actually a 'keyword' function..."""
84 maxargs = None
87
88 -class DATE(FunctionDescr):
90
98
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
106
107 backend_name = None
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
116 'MIN': MIN, 'MAX': MAX,
117 'SUM': SUM,
118 'COUNT': COUNT,
119 'AVG': AVG,
120
121 'UPPER': UPPER, 'LOWER': LOWER,
122 'LENGTH': LENGTH,
123 'DATE': DATE,
124 'RANDOM': RANDOM,
125
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
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
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
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
172 """return True if the DBMS support groups"""
173 return self.groups_support
174 support_user = obsolete('use groups_support attribute')(support_groups)
175
179
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
195
197 return 'CURRENT_DATE'
198
200 return 'CURRENT_TIME'
201
203 return 'CURRENT_TIMESTAMP'
204
206 return '''CREATE TABLE %s (last INTEGER);
207 INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
208
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
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
224 return 'DROP TABLE %s;' % seq_name
225
227 return ('UPDATE %s SET last=last+1;' % seq_name,
228 'SELECT last FROM %s;' % seq_name)
229
232 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
233
235 if value:
236 return 'TRUE'
237 else:
238 return 'FALSE'
239
244
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
253 if unique:
254
255
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):
263
264 - def drop_index(self, cursor, table, column, unique=False):
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
273 """return True if a user with the given username exists"""
274 return username in self.list_users(cursor)
275
277 """return the list of existing database users"""
278 raise NotImplementedError('not supported by this DBMS')
279
281 """create a new database"""
282 raise NotImplementedError('not supported by this DBMS')
283
285 """return the list of existing databases"""
286 raise NotImplementedError('not supported by this DBMS')
287
289 """return the list of tables of a database"""
290 raise NotImplementedError('not supported by this DBMS')
291
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
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
308 """Postgres helper, taking advantage of postgres SEQUENCE support
309 """
310 backend_name = 'postgres'
311
312 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy()
313
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
352 return 'CREATE SEQUENCE %s;' % seq_name
353
355 return 'DROP SEQUENCE %s;' % seq_name
356
358 return ("SELECT nextval('%s');" % seq_name,)
359
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
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
378 """postgres specific method to install a procedural language on a database"""
379
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
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
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
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
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
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
423 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy()
424 FUNCTIONS.pop('RANDOM')
425
426 users_support = groups_support = False
427 ilike_support = False
428 union_parentheses_support = False
429
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
438 return 'DROP INDEX %s' % self._index_name(table, column, unique)
439
441 """return the list of tables of a database"""
442
443 cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
444 return [r[0] for r in cursor.fetchall()]
445
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
456 """MySQL helper, taking advantage of postgres SEQUENCE support
457 """
458 backend_name = 'mysql'
459 needs_from_clause = True
460 ilike_support = False
461
462
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
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
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
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
503 sql = "CREATE DATABASE %(dbname)s"
504 if encoding:
505 sql += " CHARACTER SET %(encoding)s"
506 return sql % locals()
507
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
515 if value:
516 return True
517 else:
518 return False
519
521 """return the list of existing database users"""
522
523 cursor.execute("SELECT User FROM mysql.user")
524 return [r[0] for r in cursor.fetchall()]
525
530
535
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
558
561
562
563
568