1
#This file is part of Tryton. The COPYRIGHT file at the top level of this repository contains the full copyright notices and license terms.
2
from psycopg2.pool import ThreadedConnectionPool
3
from psycopg2.extensions import ISOLATION_LEVEL_SERIALIZABLE, cursor
4
from psycopg2 import IntegrityError
8
from mx import DateTime as mdt
11
from config import CONFIG
14
RE_FROM = re.compile('.* from "?([a-zA-Z_0-9]+)"?.*$')
15
RE_INTO = re.compile('.* into "?([a-zA-Z_0-9]+)"?.*$')
17
class tryton_cursor(cursor):
19
def __build_dict(self, row):
21
for i in range(len(self.description)):
22
res[self.description[i][0]] = row[i]
25
def dictfetchone(self):
28
return self.__build_dict(row)
32
def dictfetchmany(self, size):
34
rows = self.fetchmany(size)
36
res.append(self.__build_dict(row))
39
def dictfetchall(self):
41
rows = self.fetchall()
43
res.append(self.__build_dict(row))
47
class FakeCursor(object):
53
def __init__(self, connpool, conn, dbname, cursor_factory):
54
self._connpool = connpool
56
self.cursor_factory = cursor_factory
57
self.cursor = conn.cursor(cursor_factory=self.cursor_factory)
59
self.sql_from_log = {}
60
self.sql_into_log = {}
63
def execute(self, sql, params=None):
71
res = self.cursor.execute(sql, params)
73
res = self.cursor.execute(sql)
75
logger = logging.getLogger('sql')
76
logger.error('Wrong SQL: ' + self.cursor.query)
80
res_from = RE_FROM.match(sql.lower())
82
self.sql_from_log.setdefault(res_from.group(1), [0, 0])
83
self.sql_from_log[res_from.group(1)][0] += 1
84
self.sql_from_log[res_from.group(1)][1] += mdt.now() - now
85
res_into = RE_INTO.match(sql.lower())
87
self.sql_into_log.setdefault(res_into.group(1), [0, 0])
88
self.sql_into_log[res_into.group(1)][0] += 1
89
self.sql_into_log[res_into.group(1)][1] += mdt.now() - now
92
def print_log(self, sql_type='from'):
93
print "SQL LOG %s:" % (sql_type,)
94
if sql_type == 'from':
95
logs = self.sql_from_log.items()
97
logs = self.sql_into_log.items()
98
logs.sort(lambda x, y: cmp(x[1][1], y[1][1]))
101
print "table:", log[0], ":", str(log[1][1]), "/", log[1][0]
103
print "SUM:%s/%d"% (amount, self.count)
107
self.print_log('from')
108
self.print_log('into')
111
# This force the cursor to be freed, and thus, available again. It is
112
# important because otherwise we can overload the server very easily
113
# because of a cursor shortage (because cursors are not garbage
114
# collected as fast as they should). The problem is probably due in
115
# part because browse records keep a reference to the cursor.
117
self._connpool.putconn(self.conn)
125
def __getattr__(self, name):
126
return getattr(self.cursor, name)
130
Test if it is a Tryton database.
132
self.cursor.execute("SELECT relname " \
134
"WHERE relkind = 'r' AND relname in (" \
142
"'res_group_user_rel', "
149
"'ir_module_module', "
150
"'ir_module_module_dependency, '"
154
return len(self.cursor.fetchall()) != 0
159
def __init__(self, connpool, dbname):
160
self._connpool = connpool
163
def cursor(self, cursor_factory=tryton_cursor):
164
conn = self._connpool.getconn()
165
conn.set_isolation_level(ISOLATION_LEVEL_SERIALIZABLE)
166
return FakeCursor(self._connpool, conn, self.dbname,
167
cursor_factory=cursor_factory)
170
self._connpool.closeall()
172
def db_connect(db_name):
173
host = CONFIG['db_host'] and "host=%s" % CONFIG['db_host'] or ''
174
port = CONFIG['db_port'] and "port=%s" % CONFIG['db_port'] or ''
175
name = "dbname=%s" % db_name
176
user = CONFIG['db_user'] and "user=%s" % CONFIG['db_user'] or ''
177
password = CONFIG['db_password'] \
178
and "password=%s" % CONFIG['db_password'] or ''
179
maxconn = int(CONFIG['db_maxconn']) or 64
180
dsn = '%s %s %s %s %s' % (host, port, name, user, password)
181
connpool = ThreadedConnectionPool(0, maxconn, dsn)
182
return FakeDB(connpool, db_name)
185
sql_file = os.path.join(os.path.dirname(__file__), 'init.sql')
186
for line in file(sql_file).read().split(';'):
187
if (len(line)>0) and (not line.isspace()):
190
for i in ('ir', 'workflow', 'res', 'webdav'):
191
root_path = os.path.dirname(__file__)
192
tryton_file = os.path.join(root_path, i, '__tryton__.py')
193
mod_path = os.path.join(root_path, i)
194
info = eval(file(tryton_file).read())
195
active = info.get('active', False)
199
state = 'uninstalled'
200
cursor.execute('SELECT NEXTVAL(\'ir_module_module_id_seq\')')
201
module_id = cursor.fetchone()[0]
202
cursor.execute('INSERT INTO ir_module_module ' \
203
'(id, author, website, name, shortdesc, ' \
204
'description, state) ' \
205
'VALUES (%s, %s, %s, %s, %s, %s, %s)',
206
(module_id, info.get('author', ''),
207
info.get('website', ''), i, info.get('name', False),
208
info.get('description', ''), state))
209
dependencies = info.get('depends', [])
210
for dependency in dependencies:
211
cursor.execute('INSERT INTO ir_module_module_dependency ' \
212
'(module, name) VALUES (%s, %s)',
213
(module_id, dependency))
215
psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
219
def table_exist(cursor, table_name):
220
cursor.execute("SELECT relname FROM pg_class " \
221
"WHERE relkind = 'r' AND relname = %s",
223
return bool(cursor.rowcount)
228
def __init__(self, cursor, table_name, object_name=None, module_name=None):
229
self.table_name = table_name
234
self.field2module = {}
235
self.module_name = module_name
237
self.object_name = object_name
239
# Create new table if necessary
240
if not table_exist(self.cursor, self.table_name):
241
self.cursor.execute('CREATE TABLE "%s" ' \
242
"(id SERIAL NOT NULL, " \
243
"PRIMARY KEY(id))"% self.table_name)
244
self.update_definitions()
246
def update_definitions(self):
247
# Fetch columns definitions from the table
248
self.cursor.execute("SELECT at.attname, at.attlen, "\
249
"at.atttypmod, at.attnotnull, at.atthasdef, "\
251
"CASE WHEN at.attlen = -1 "\
252
"THEN at.atttypmod-4 "\
253
"ELSE at.attlen END as size "\
255
"JOIN pg_attribute at on (cl.oid = at.attrelid) "\
256
"JOIN pg_type ty on (at.atttypid = ty.oid) "\
257
"WHERE cl.relname = %s AND at.attnum > 0",
260
for line in self.cursor.fetchall():
261
column, length, typmod, notnull, hasdef, typname, size = line
262
self.table[column] = {
270
# fetch constrains for the table
271
self.cursor.execute("SELECT co.contype, co.confdeltype, at.attname, "\
272
"cl2.relname, co.conname "\
273
"FROM pg_constraint co "\
274
"LEFT JOIN pg_class cl on (co.conrelid = cl.oid) "\
275
"LEFT JOIN pg_class cl2 on (co.confrelid = cl2.oid) "\
276
"LEFT JOIN pg_attribute at on (co.conkey[1] = at.attnum) "\
277
"WHERE cl.relname = %s AND at.attrelid = cl.oid",
281
for line in self.cursor.fetchall():
282
contype, confdeltype, column, ref, conname = line
284
self.fk_deltype[column] = confdeltype
286
if conname not in self.constraint:
287
self.constraint.append(conname)
289
# Fetch indexes defined for the table
290
self.cursor.execute("SELECT cl2.relname "\
291
"FROM pg_index ind "\
292
"JOIN pg_class cl on (cl.oid = ind.indrelid) "\
293
"JOIN pg_class cl2 on (cl2.oid = ind.indexrelid) "\
294
"WHERE cl.relname = %s",
296
self.index = [l[0] for l in self.cursor.fetchall()]
298
# Keep track of which module created each field
299
self.field2module = {}
300
if self.object_name is not None:
301
self.cursor.execute('SELECT f.name, f.module '\
302
'FROM ir_model_field f '\
303
'JOIN ir_model m on (f.model=m.id) '\
304
'WHERE m.model = %s',
307
for line in self.cursor.fetchall():
308
self.field2module[line[0]] = line[1]
310
def alter_size(self, column_name, column_type):
312
self.cursor.execute("ALTER TABLE \"%s\" " \
313
"RENAME COLUMN \"%s\" " \
314
"TO temp_change_size" % \
315
(self.table_name, column_name))
316
self.cursor.execute("ALTER TABLE \"%s\" " \
317
"ADD COLUMN \"%s\" %s" % \
318
(self.table_name, column_name, column_type))
319
self.cursor.execute("UPDATE \"%s\" " \
320
"SET \"%s\" = temp_change_size::%s" % \
321
(self.table_name, column_name, column_type))
322
self.cursor.execute("ALTER TABLE \"%s\" " \
323
"DROP COLUMN temp_change_size" % \
325
self.update_definitions()
327
def alter_type(self, column_name, column_type):
328
self.cursor.execute('ALTER TABLE "' + self.table_name + '" ' \
329
'ALTER "' + column_name + '" TYPE ' + column_type)
330
self.update_definitions()
332
def db_default(self, column_name, value):
333
self.cursor.execute('ALTER TABLE "' + self.table_name + '" ' \
334
'ALTER COLUMN "' + column_name + '" SET DEFAULT %s',
337
def add_raw_column(self, column_name, column_type, symbol_set,
338
default_fun=None, field_size=None, migrate=True):
339
if column_name in self.table:
343
base_type = column_type[0].lower()
344
if base_type != self.table[column_name]['typname']:
345
if (self.table[column_name]['typname'], base_type) in [
348
('date', 'timestamp'),
351
self.alter_type(column_name, base_type)
353
logging.getLogger('init').warning(
354
'Unable to migrate column %s on table %s ' \
356
(column_name, self.table_name,
357
self.table[column_name]['typname'], base_type))
359
if base_type == 'varchar' \
360
and self.table[column_name]['typname'] == 'varchar':
362
if field_size == None:
363
if self.table[column_name]['size'] > 0:
364
self.alter_size(column_name, base_type)
366
elif self.table[column_name]['size'] == field_size:
368
elif self.table[column_name]['size'] > 0 and \
369
self.table[column_name]['size'] < field_size:
370
self.alter_size(column_name, column_type[1])
372
logging.getLogger('init').warning(
373
'Unable to migrate column %s on table %s ' \
374
'from varchar(%s) to varchar(%s).' % \
375
(column_name, self.table_name,
376
self.table[column_name]['size'] > 0 and \
377
self.table[column_name]['size'] or "",
381
column_type = column_type[1]
382
self.cursor.execute('ALTER TABLE "%s" ADD COLUMN "%s" %s' %
383
(self.table_name, column_name, column_type))
385
# check if table is non-empty:
386
self.cursor.execute('SELECT 1 FROM "%s" limit 1' % self.table_name)
387
if self.cursor.rowcount:
388
# Populate column with default values:
390
if default_fun is not None:
391
default = default_fun(self.cursor, 0, {})
392
self.cursor.execute('UPDATE "' + self.table_name + '" '\
393
'SET "' + column_name + '" = ' + symbol_set[0],
394
(symbol_set[1](default),))
396
self.update_definitions()
398
def add_m2m(self, column_name, other_table, relation_table, rtable_from, rtable_to,
399
on_delete_from, on_delete_to):
400
if not table_exist(self.cursor, other_table):
401
raise Exception("table %s not found"%other_table)
402
rtable = table_handler(
403
self.cursor, relation_table, object_name=None,
404
module_name= self.module_name)
405
from osv.fields import Integer
406
rtable.add_raw_column(rtable_from, ('int4', 'int4'), Integer._symbol_set)
407
rtable.add_raw_column(rtable_to, ('int4', 'int4'), Integer._symbol_set)
408
rtable.add_fk(rtable_from, self.table_name, on_delete=on_delete_from)
409
rtable.add_fk(rtable_to, other_table, on_delete=on_delete_to)
410
rtable.not_null_action(rtable_from)
411
rtable.not_null_action(rtable_to)
412
rtable.index_action(rtable_from, 'add')
413
rtable.index_action(rtable_to, 'add')
415
def add_fk(self, column_name, reference, on_delete=None):
423
if on_delete is not None :
424
on_delete = on_delete.upper()
425
if on_delete not in on_delete_code:
426
raise Exception('On delete action not supported !')
428
on_delete = 'SET NULL'
429
code = on_delete_code[on_delete]
431
self.cursor.execute('SELECT conname FROM pg_constraint ' \
432
'WHERE conname = %s',
433
(self.table_name + '_' + column_name + '_fkey',))
435
if not self.cursor.rowcount:
437
elif self.fk_deltype.get(column_name) != code:
438
self.cursor.execute('ALTER TABLE "' + self.table_name + '" ' \
439
'DROP CONSTRAINT "' + self.table_name + '_' + \
440
column_name + '_fkey"')
443
self.cursor.execute('ALTER TABLE "' + self.table_name + '" ' \
444
'ADD FOREIGN KEY ("' + column_name + '") ' \
445
'REFERENCES "' + reference + '" ' \
446
'ON DELETE ' + on_delete)
447
self.update_definitions()
449
def index_action(self, column_name, action='add'):
450
index_name = "%s_%s_index" % (self.table_name, column_name)
453
if index_name in self.index:
455
self.cursor.execute('CREATE INDEX "' + index_name + '" ' \
456
'ON "' + self.table_name + '" ("' + column_name + '")')
457
self.update_definitions()
458
elif action == 'remove':
459
if self.field2module.get(column_name) != self.module_name:
462
self.cursor.execute("SELECT * FROM pg_indexes " \
463
"WHERE indexname = '%s'" %
465
if self.cursor.rowcount:
466
self.cursor.execute('DROP INDEX "%s" ' % (index_name,))
467
self.update_definitions()
469
def not_null_action(self, column_name, action='add'):
470
if column_name not in self.table:
474
if self.table[column_name]['notnull']:
476
self.cursor.execute('SELECT id FROM "%s" ' \
477
'WHERE "%s" IS NULL' % \
478
(self.table_name, column_name))
479
if not self.cursor.rowcount:
480
self.cursor.execute('ALTER TABLE "' + self.table_name + '" ' \
481
'ALTER COLUMN "' + column_name + '" ' \
483
self.update_definitions()
485
logging.getLogger('init').warning(
486
'Unable to set column %s ' \
487
'of table %s not null !\n'\
489
'trytond.py --update=module\n' \
490
'If it doesn\'t work, update records and execute manually:\n' \
491
'ALTER TABLE "%s" ALTER COLUMN "%s" SET NOT NULL' % \
492
(column_name, self.table_name, self.table_name, column_name))
493
elif action == 'remove':
494
if not self.table[column_name]['notnull']:
496
if self.field2module.get(column_name) != self.module_name:
498
self.cursor.execute('ALTER TABLE "%s" ' \
499
'ALTER COLUMN "%s" DROP NOT NULL' %
500
(self.table_name, column_name))
501
self.update_definitions()
503
def add_constraint(self, ident, constraint):
504
ident = self.table_name + "_" + ident
505
if ident in self.constraint:
506
# This constrain already exist
509
self.cursor.execute('ALTER TABLE "%s" ' \
510
'ADD CONSTRAINT "%s" %s' % \
511
(self.table_name, ident, constraint,))
513
logging.getLogger('init').warning(
514
'unable to add \'%s\' constraint on table %s !\n' \
515
'If you want to have it, you should update the records ' \
516
'and execute manually:\n'\
517
'ALTER table "%s" ADD CONSTRAINT "%s" %s' % \
518
(constraint, self.table_name, self.table_name,
520
self.update_definitions()
added
removed
trytond/sql_db.py
