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
22
23
28
29
60
64
65 -class MAX(AggrFunctionDescr): pass
66 -class MIN(AggrFunctionDescr): pass
67 -class SUM(AggrFunctionDescr): pass
68 -class COUNT(AggrFunctionDescr):
70 -class AVG(AggrFunctionDescr):
72
73 -class UPPER(FunctionDescr):
75 -class LOWER(FunctionDescr):
77 -class IN(FunctionDescr):
78 """this is actually a 'keyword' function..."""
79 maxargs = None
82
83 -class DATE(FunctionDescr):
85
93
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
101
102 backend_name = None
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
114 'MIN': MIN, 'MAX': MAX,
115 'SUM': SUM,
116 'COUNT': COUNT,
117 'AVG': AVG,
118
119 'UPPER': UPPER, 'LOWER': LOWER,
120 'LENGTH': LENGTH,
121 'DATE': DATE,
122 'RANDOM': RANDOM,
123
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
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
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
163
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
179
181 return 'CURRENT_DATE'
182
184 return 'CURRENT_TIME'
185
187 return 'CURRENT_TIMESTAMP'
188
190 return '''CREATE TABLE %s (last INTEGER);
191 INSERT INTO %s VALUES (0);''' % (seq_name, seq_name)
192
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
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
208 return 'DROP TABLE %s;' % seq_name
209
211 return ('UPDATE %s SET last=last+1;' % seq_name,
212 'SELECT last FROM %s;' % seq_name)
213
215 return 'ALTER TABLE %s ALTER COLUMN %s TYPE %s' % (
216 table, column, coltype)
217
219 cmd = null_allowed and 'DROP' or 'SET'
220 return 'ALTER TABLE %s ALTER COLUMN %s %s NOT NULL' % (
221 table, column, cmd)
222
225 return "CREATE TEMPORARY TABLE %s (%s);" % (table_name, table_schema)
226
228 if value:
229 return 'TRUE'
230 else:
231 return 'FALSE'
232
237
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
246 if unique:
247
248
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):
256
257 - def drop_index(self, cursor, table, column, unique=False):
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
266 """return True if a user with the given username exists"""
267 return username in self.list_users(cursor)
268
270 """return the list of existing database users"""
271 raise NotImplementedError('not supported by this DBMS')
272
274 """create a new database"""
275 raise NotImplementedError('not supported by this DBMS')
276
278 """return the list of existing databases"""
279 raise NotImplementedError('not supported by this DBMS')
280
282 """return the list of tables of a database"""
283 raise NotImplementedError('not supported by this DBMS')
284
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
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
301 """Postgres helper, taking advantage of postgres SEQUENCE support
302 """
303 backend_name = 'postgres'
304
305 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy()
306
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
345 return 'CREATE SEQUENCE %s;' % seq_name
346
348 return 'DROP SEQUENCE %s;' % seq_name
349
351 return ("SELECT nextval('%s');" % seq_name,)
352
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
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
371 """postgres specific method to install a procedural language on a database"""
372
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
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
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
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
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
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
412 FUNCTIONS = _GenericAdvFuncHelper.FUNCTIONS.copy()
413 FUNCTIONS.pop('RANDOM')
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
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
439 return 'DROP INDEX %s' % self._index_name(table, column, unique)
440
442 """return the list of tables of a database"""
443
444 cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
445 return [r[0] for r in cursor.fetchall()]
446
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
457 """MySQL helper, taking advantage of postgres SEQUENCE support
458 """
459 backend_name = 'mysql'
460 needs_from_clause = True
461 ilike_support = False
462 case_sensitive = True
463
464
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
471 TYPE_MAPPING['Datetime'] = 'datetime'
472
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
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
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
515 sql = "CREATE DATABASE %(dbname)s"
516 if encoding:
517 sql += " CHARACTER SET %(encoding)s"
518 return sql % locals()
519
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
529 return self.sql_change_col_type(table, column, coltype, null_allowed)
530
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
538 if value:
539 return 1
540 else:
541 return 0
542
544 """return the list of existing database users"""
545
546 cursor.execute("SELECT User FROM mysql.user")
547 return [r[0] for r in cursor.fetchall()]
548
553
558
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
581
584
585
586
591