Package logilab-common-0 ::
Package 39 ::
Package 0 ::
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 intersect_all_support = True
111 users_support = True
112 groups_support = True
113 ilike_support = True
114
115 FUNCTIONS = {
116
117 'MIN': MIN, 'MAX': MAX,
118 'SUM': SUM,
119 'COUNT': COUNT,
120 'AVG': AVG,
121
122 'UPPER': UPPER, 'LOWER': LOWER,
123 'LENGTH': LENGTH,
124 'DATE': DATE,
125 'RANDOM': RANDOM,
126
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
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
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
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
173 """return True if the DBMS support groups"""
174 return self.groups_support
175 support_user = obsolete('use groups_support attribute')(support_groups)
176
180
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
196
198 return 'CURRENT_DATE'
199
201 return 'CURRENT_TIME'
202
204 return 'CURRENT_TIMESTAMP'
205
207 return '''CREATE TABLE %s (last INTEGER);
208 INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
209
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
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
225 return 'DROP TABLE %s;' % seq_name
226
228 return ('UPDATE %s SET last=last+1;' % seq_name,
229 'SELECT last FROM %s;' % seq_name)
230
233 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
234
236 if value:
237 return 'TRUE'
238 else:
239 return 'FALSE'
240
245
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
254 if unique:
255
256
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):
264
265 - def drop_index(self, cursor, table, column, unique=False):
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
274 """return True if a user with the given username exists"""
275 return username in self.list_users(cursor)
276
278 """return the list of existing database users"""
279 raise NotImplementedError('not supported by this DBMS')
280
282 """create a new database"""
283 raise NotImplementedError('not supported by this DBMS')
284
286 """return the list of existing databases"""
287 raise NotImplementedError('not supported by this DBMS')
288
290 """return the list of tables of a database"""
291 raise NotImplementedError('not supported by this DBMS')
292
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
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
309 """Postgres helper, taking advantage of postgres SEQUENCE support
310 """
311 backend_name = 'postgres'
312
313 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy()
314
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
353 return 'CREATE SEQUENCE %s;' % seq_name
354
356 return 'DROP SEQUENCE %s;' % seq_name
357
359 return ("SELECT nextval('%s');" % seq_name,)
360
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
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
379 """postgres specific method to install a procedural language on a database"""
380
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
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
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
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
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
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
420 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy()
421 FUNCTIONS.pop('RANDOM')
422
423 users_support = groups_support = False
424 ilike_support = False
425 union_parentheses_support = False
426 intersect_all_support = False
427
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
436 return 'DROP INDEX %s' % self._index_name(table, column, unique)
437
439 """return the list of tables of a database"""
440
441 cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
442 return [r[0] for r in cursor.fetchall()]
443
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
454 """MySQL helper, taking advantage of postgres SEQUENCE support
455 """
456 backend_name = 'mysql'
457 needs_from_clause = True
458 ilike_support = False
459
460
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
467 TYPE_MAPPING['Datetime'] = 'datetime'
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