def test_update_subselect(self):
t1 = Table('t1')
t2 = Table('t2')
query_list = t1.update([t1.c], [t2.select(t2.c, where=t2.i == t1.i)])
query_nolist = t1.update([t1.c], t2.select(t2.c, where=t2.i == t1.i))
for query in [query_list, query_nolist]:
self.assertEqual(
str(query), 'UPDATE "t1" SET "c" = ('
'SELECT "b"."c" FROM "t2" AS "b" WHERE ("b"."i" = "t1"."i"))')
self.assertEqual(query.params, ())
def test_update_subselect(self):
t1 = Table('t1')
t2 = Table('t2')
query_list = t1.update([t1.c], [t2.select(t2.c, where=t2.i == t1.i)])
query_nolist = t1.update([t1.c], t2.select(t2.c, where=t2.i == t1.i))
for query in [query_list, query_nolist]:
self.assertEqual(str(query),
'UPDATE "t1" SET "c" = ('
'SELECT "b"."c" FROM "t2" AS "b" WHERE ("b"."i" = "t1"."i"))')
self.assertEqual(query.params, ())
def rename(cursor, table_name, old_name, new_name, var_name):
table = Table(table_name)
fields = None
# If the view already exists in destination module
if table_name == 'ir_model_data':
fields = ['fs_id', 'model']
if table_name == 'ir_ui_view':
fields = ['model', 'name']
if fields:
query = ('DELETE from %(table)s where '
'(%(fields)s) in ('
'SELECT %(fields)s FROM %(table)s WHERE '
'"module" IN (\'%(old_name)s\', \'%(new_name)s\') '
'GROUP BY %(fields)s '
'HAVING COUNT("module") > 1) '
'and "module" = \'%(old_name)s\';' % {
'table': table_name,
'old_name': old_name,
'new_name': new_name,
'fields':
(', '.join('"' + f + '"' for f in fields))
})
cursor.execute(query)
query = table.update([getattr(table, var_name)], [new_name],
where=(getattr(table, var_name) == old_name))
cursor.execute(*query)
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
# Migration from 5.6: Rename main tax ids
if module_name == 'account_de_skr03':
for old_id, new_id in (
('tax_ust_19', 'tax_ust_standard_rate'),
('tax_ust_7', 'tax_ust_reduced_rate'),
('tax_vst_19', 'tax_vst_standard_rate'),
('tax_vst_7', 'tax_vst_reduced_rate'),
('tax_eu_19_purchase', 'tax_purchase_eu_standard_rate'),
('tax_eu_7_purchase', 'tax_purchase_eu_reduced_rate'),
('tax_import_19', 'tax_import_standard_rate'),
('tax_import_7', 'tax_import_reduced_rate'),
):
cursor.execute(
*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(
*model_data.update(columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super().__register__(module_name)
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
# Migration from 3.0: new tax rates
if module_name == 'account_nl':
for old_id, new_id in (
('tva_vente_19_6', 'tva_vente_taux_normal'),
('tva_vente_7', 'tva_vente_taux_intermediaire'),
('tva_vente_intracommunautaire_19_6',
'tva_vente_intracommunautaire_taux_normal'),
('tva_vente_intracommunautaire_7',
'tva_vente_intracommunautaire_taux_intermediaire'),
('tva_achat_19_6', 'tva_achat_taux_normal'),
('tva_achat_7', 'tva_achat_taux_intermediaire'),
('tva_achat_intracommunautaire_19_6',
'tva_achat_intracommunautaire_taux_normal'),
):
cursor.execute(*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super(TaxTemplate, cls).__register__(module_name)
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
# Migration from 3.0: new tax rates
if module_name == 'account_fr':
for old_id, new_id in (
('tva_vente_19_6', 'tva_vente_taux_normal'),
('tva_vente_7', 'tva_vente_taux_intermediaire'),
('tva_vente_intracommunautaire_19_6',
'tva_vente_intracommunautaire_taux_normal'),
('tva_vente_intracommunautaire_7',
'tva_vente_intracommunautaire_taux_intermediaire'),
('tva_achat_19_6', 'tva_achat_taux_normal'),
('tva_achat_7', 'tva_achat_taux_intermediaire'),
('tva_achat_intracommunautaire_19_6',
'tva_achat_intracommunautaire_taux_normal'),
):
cursor.execute(
*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(
*model_data.update(columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super(TaxTemplate, cls).__register__(module_name)
def update_formulas(cls, records=None):
cursor = Transaction().connection.cursor()
pool = Pool()
Compilation = Pool().get('lims.interface.compilation')
TableField = pool.get('lims.interface.table.field')
Column = pool.get('lims.interface.column')
compilation_id = Transaction().context.get(
'lims_interface_compilation')
if not compilation_id:
return
compilation = Compilation(compilation_id)
table = compilation.table
interface = compilation.interface
sql_table = SqlTable(table.name)
formula_fields = []
fields = TableField.search([
('table', '=', table),
('formula', 'not in', [None, '']),
])
for field in fields:
col = Column.search([
('interface', '=', interface),
('alias', '=', field.name),
])
order = col and col[0].evaluation_order or 0
formula_fields.append({
'order': order,
'field': field,
})
if not formula_fields:
return
formula_fields = sorted(formula_fields, key=lambda x: x['order'])
if not records:
records = cls.search([])
for record in records:
vals = {}
fields = []
values = []
for field in formula_fields:
for x in (field['field'].inputs or '').split():
if x not in vals:
vals[x] = getattr(record, x)
field_name = field['field'].name
value = record.get_formula_value(field['field'], vals)
if value is None:
continue
fields.append(SqlColumn(sql_table, field_name))
values.append(value)
vals[field_name] = value
if not values:
continue
query = sql_table.update(fields, values,
where=(sql_table.id == record.id))
cursor.execute(*query)
def test_update2(self):
t1 = Table('t1')
t2 = Table('t2')
query = t1.update([t1.c], ['foo'], from_=[t2], where=(t1.c == t2.c))
self.assertEqual(str(query),
'UPDATE "t1" AS "b" SET "c" = %s FROM "t2" AS "a" '
'WHERE ("b"."c" = "a"."c")')
self.assertEqual(query.params, ('foo',))
def test_update2(self):
t1 = Table('t1')
t2 = Table('t2')
query = t1.update([t1.c], ['foo'], from_=[t2], where=(t1.c == t2.c))
self.assertEqual(
str(query), 'UPDATE "t1" AS "b" SET "c" = %s FROM "t2" AS "a" '
'WHERE ("b"."c" = "a"."c")')
self.assertEqual(query.params, ('foo', ))
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
# Migration from 1.6: corrected misspelling of ounce (was once)
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=['uom_ounce'],
where=(model_data.fs_id == 'uom_once')
& (model_data.module == 'product')))
super(Uom, cls).__register__(module_name)
def test_schema_subselect(self):
t1 = Table('t1', 'default')
t2 = Table('t2', 'default')
query = t1.update([t1.c1], t2.select(t2.c, where=t2.i == t1.i))
self.assertEqual(
str(query), 'UPDATE "default"."t1" SET "c1" = ('
'SELECT "b"."c" FROM "default"."t2" AS "b" '
'WHERE ("b"."i" = "default"."t1"."i"))')
self.assertEqual(query.params, ())
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
# Migration from 1.6: corrected misspelling of ounce (was once)
cursor.execute(
*model_data.update(columns=[model_data.fs_id],
values=['uom_ounce'],
where=(model_data.fs_id == 'uom_once')
& (model_data.module == 'product')))
super(Uom, cls).__register__(module_name)
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
model = Table('ir_model')
# Migration from 1.2: packing renamed into shipment
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[Overlay(model_data.fs_id, 'shipment',
Position('packing', model_data.fs_id),
len('packing'))],
where=model_data.fs_id.like('%packing%')
& (model_data.module == module_name)))
cursor.execute(*model.update(
columns=[model.model],
values=[Overlay(model.model, 'shipment',
Position('packing', model.model),
len('packing'))],
where=model.model.like('%packing%')
& (model.module == module_name)))
super(ShipmentInternal, cls).__register__(module_name)
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
model = Table('ir_model')
# Migration from 1.2: packing renamed into shipment
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[Overlay(model_data.fs_id, 'shipment',
Position('packing', model_data.fs_id),
len('packing'))],
where=model_data.fs_id.like('%packing%')
& (model_data.module == module_name)))
cursor.execute(*model.update(
columns=[model.model],
values=[Overlay(model.model, 'shipment',
Position('packing', model.model),
len('packing'))],
where=model.model.like('%packing%')
& (model.module == module_name)))
super(ShipmentInternal, cls).__register__(module_name)
def commit(cls, transaction):
table = Table(cls._table)
reset = cls._reset.pop(transaction, None)
if not reset:
return
database = transaction.database
dbname = database.name
if not _clear_timeout and transaction.database.has_channel():
with transaction.connection.cursor() as cursor:
# The count computed as
# 8000 (max notify size) / 64 (max name data len)
for sub_reset in grouped_slice(reset, 125):
cursor.execute(
'NOTIFY "%s", %%s' % cls._channel,
(json.dumps(list(sub_reset), separators=(',', ':')),))
else:
connection = database.get_connection(
readonly=False, autocommit=True)
try:
with connection.cursor() as cursor:
for name in reset:
cursor.execute(*table.select(table.name, table.id,
table.timestamp,
where=table.name == name,
limit=1))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(*table.update([table.timestamp],
[CurrentTimestamp()],
where=table.name == name))
else:
cursor.execute(*table.insert(
[table.timestamp, table.name],
[[CurrentTimestamp(), name]]))
cursor.execute(*table.select(
Max(table.timestamp),
where=table.name == name))
timestamp, = cursor.fetchone()
cursor.execute(*table.select(
_cast(Max(table.timestamp)),
where=table.name == name))
timestamp, = cursor.fetchone()
inst = cls._instances[name]
inst._clear(dbname, timestamp)
connection.commit()
finally:
database.put_connection(connection)
cls._clean_last = datetime.now()
reset.clear()
def commit(cls, transaction):
table = Table(cls._table)
reset = cls._reset.setdefault(transaction, set())
if not reset:
return
database = transaction.database
dbname = database.name
if not _clear_timeout and transaction.database.has_channel():
with transaction.connection.cursor() as cursor:
# JCA: Fix for https://bugs.tryton.org/issue8781
resets = list(reset)
for i in range(0, len(resets), 10):
cursor.execute('NOTIFY "%s", %%s' % cls._channel,
(json.dumps(resets[i:i + 10],
separators=(',', ':')), ))
else:
connection = database.get_connection(readonly=False,
autocommit=True)
try:
with connection.cursor() as cursor:
for name in reset:
cursor.execute(*table.select(table.name,
table.id,
table.timestamp,
where=table.name == name,
limit=1))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(*table.update(
[table.timestamp], [CurrentTimestamp()],
where=table.name == name))
else:
cursor.execute(
*table.insert([table.timestamp, table.name],
[[CurrentTimestamp(), name]]))
cursor.execute(*table.select(Max(table.timestamp),
where=table.name == name))
timestamp, = cursor.fetchone()
cursor.execute(
*table.select(_cast(Max(table.timestamp)),
where=table.name == name))
timestamp, = cursor.fetchone()
inst = cls._instances[name]
inst._clear(dbname, timestamp)
connection.commit()
finally:
database.put_connection(connection)
reset.clear()
def test_update_returning_select(self):
t1 = Table('t1')
t2 = Table('t2')
query = t1.update([t1.c], ['foo'],
returning=[
t2.select(t2.c,
where=(t2.c1 == t1.c) &
(t2.c2 == 'bar'))
])
self.assertEqual(
str(query), 'UPDATE "t1" SET "c" = %s '
'RETURNING (SELECT "b"."c" FROM "t2" AS "b" '
'WHERE (("b"."c1" = "t1"."c") AND ("b"."c2" = %s)))')
self.assertEqual(query.params, ('foo', 'bar'))
def update(Model, allowed_columns, pk_column_value, column_values):
assert pk_column_value, "'pk_column_value' must be a non-empty dictionary with column name as key, and primary key as value."
for column, value in column_values.iteritems():
assert column in allowed_columns, "'{}' is not set as allowed".format(
column)
table = Table(Model._meta.db_table)
cvalues1, cvalues2 = tee(clean_default(Model, column_values).iteritems())
table_columns = [getattr(table, column) for column, value in cvalues1]
with connection.cursor() as cursor:
def where_and(a, b):
bkey, bvalue = b
return a & (getattr(table, bkey) == bvalue)
cursor.execute(*table.update(
columns=table_columns,
values=[value for column, value in cvalues2],
where=reduce(
where_and, pk_column_value.iteritems(),
Literal(1) == Literal(1)
) # Hacky way to generate filter using reduce with initial value
))
assert cursor.rowcount == 1, 'Expect only 1 SQL Update. Got {} instead. pk_column_value={}'.format(
cursor.rowcount, pk_column_value)
# Updating a node's parent should result in
# updating of the node's descendants.
try:
parent_name = Model._nested_intervals_field_names[-1]
except AttributeError:
pass
else:
if parent_name + '_id' in column_values:
parent_id = Model.objects.get(**pk_column_value).pk
id_filter = {
parent_name + '__' + key: value
for key, value in pk_column_value.iteritems()
}
children = Model.objects.filter(**id_filter).iterator()
for child in children:
update(Model, allowed_columns, {Model._meta.pk.name: child.pk},
{
parent_name + '_id': parent_id,
})
return Model.objects.get(**pk_column_value).pk
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
if module_name == 'account_fr':
for old_id, new_id in (
('tax_rule_ventes_intracommunautaires_19_6',
'tax_rule_ventes_intracommunautaires_taux_normal'),
('tax_rule_ventes_intracommunautaires_7',
'tax_rule_ventes_intracommunautaires_taux_intermediaire'),
):
cursor.execute(
*model_data.update(columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super(TaxRuleTemplate, cls).__register__(module_name)
def resets(dbname):
table = Table('ir_cache')
with Transaction().new_transaction(_nocache=True) as transaction,\
transaction.connection.cursor() as cursor,\
Cache._resets_lock:
Cache._resets.setdefault(dbname, set())
for name in Cache._resets[dbname]:
cursor.execute(*table.select(
table.name, where=table.name == name, limit=1))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(
*table.update([table.timestamp], [CurrentTimestamp()],
where=table.name == name))
else:
cursor.execute(*table.insert([table.timestamp, table.name],
[[CurrentTimestamp(), name]]))
Cache._resets[dbname].clear()
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
if module_name == 'account_nl':
for old_id, new_id in (
('tax_rule_ventes_intracommunautaires_19_6',
'tax_rule_ventes_intracommunautaires_taux_normal'),
('tax_rule_ventes_intracommunautaires_7',
'tax_rule_ventes_intracommunautaires_taux_intermediaire'),
):
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super(TaxRuleTemplate, cls).__register__(module_name)
def resets(cls, dbname):
table = Table('ir_cache')
with Transaction().new_transaction() as transaction,\
transaction.connection.cursor() as cursor,\
cls._resets_lock:
cls._resets.setdefault(dbname, set())
for name in cls._resets[dbname]:
cursor.execute(*table.select(table.name,
where=table.name == name))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(*table.update([table.timestamp],
[CurrentTimestamp()],
where=table.name == name))
else:
cursor.execute(*table.insert(
[table.timestamp, table.name],
[[CurrentTimestamp(), name]]))
cls._resets[dbname].clear()
def resets(dbname):
with Transaction().new_cursor():
cursor = Transaction().cursor
table = Table('ir_cache')
with Cache._resets_lock:
Cache._resets.setdefault(dbname, set())
for name in Cache._resets[dbname]:
cursor.execute(
*table.select(table.name, where=table.name == name))
if cursor.fetchone():
# It would be better to insert only
cursor.execute(
*table.update([table.timestamp], [Now()],
where=table.name == name))
else:
cursor.execute(*table.insert(
[table.timestamp, table.name], [[Now(), name]]))
Cache._resets[dbname].clear()
cursor.commit()
def __register__(cls, module_name):
cursor = Transaction().connection.cursor()
model_data = Table('ir_model_data')
# Migration from 6.0: rename ids
if module_name == 'account_fr':
for old_id, new_id in (
('fr_pcg_pay', '4011'),
('fr_pcg_recv', '4111'),
('fr_pcg_cash', '512'),
('fr_pcg_expense', '607'),
):
cursor.execute(*model_data.select(model_data.id,
where=(model_data.fs_id == new_id)
& (model_data.module == module_name)))
if cursor.fetchone():
continue
cursor.execute(*model_data.update(
columns=[model_data.fs_id],
values=[new_id],
where=(model_data.fs_id == old_id)
& (model_data.module == module_name)))
super().__register__(module_name)
def _init_pool(db_name, options, lang):
pool = Pool(db_name)
with Transaction().start(db_name, 0) as transaction:
try:
if options.check_update:
# This lock of table will block others workers /
# processes until the current upgrade is finished
# Attention: lock activated only when -cu is activated
# -u can do the update normally
cursor = transaction.connection.cursor()
cursor.execute(
"LOCK upgrade_version_control IN EXCLUSIVE MODE")
upgrade_needed, new_version = _check_update_needed(
db_name, options)
if not upgrade_needed:
options.update, options.check_update = [], []
updated_needed = options.update or options.check_update
pool.init(update=updated_needed,
lang=list(lang),
activatedeps=options.activatedeps)
if updated_needed:
# If upgrade finishes correctly->update version in database
version_control_table = Table('upgrade_version_control')
cursor = transaction.connection.cursor()
cursor.execute(*version_control_table.update(
columns=[version_control_table.current_version],
values=[new_version]))
transaction.commit()
except Exception:
transaction.rollback()
raise
return pool
def rename(cursor, table_name, old_name, new_name, var_name):
table = Table(table_name)
cursor.execute(
*table.update([getattr(table, var_name)], [new_name],
where=(getattr(table, var_name) == old_name)))
def run(options):
Database = backend.get('Database')
main_lang = config.get('database', 'language')
init = {}
for db_name in options.database_names:
init[db_name] = False
database = Database(db_name)
database.connect()
if options.update:
if not database.test():
logger.info("init db")
database.init()
init[db_name] = True
elif not database.test():
raise Exception('"%s" is not a Tryton database.' % db_name)
for db_name in options.database_names:
if options.update:
with Transaction().start(db_name, 0) as transaction,\
transaction.connection.cursor() as cursor:
database = Database(db_name)
database.connect()
if not database.test():
raise Exception('"%s" is not a Tryton database.' % db_name)
lang = Table('ir_lang')
cursor.execute(
*lang.select(lang.code, where=lang.translatable == True))
lang = set([x[0] for x in cursor.fetchall()])
lang.add(main_lang)
else:
lang = set()
lang |= set(options.languages)
pool = Pool(db_name)
# XUNG
# Create upgrade version control table if it doesn't exist
_init_upgrade_version_control_table(db_name)
with Transaction().start(db_name, 0) as transaction:
# This lock of table will block others workers /
# processes until the current upgrade is finished
# Attention: lock activated only when -cu is activated
# -u can do the update normally
cursor = transaction.connection.cursor()
if options.check_update:
cursor.execute(
"LOCK upgrade_version_control IN EXCLUSIVE MODE;")
is_upgrade_needed, new_version = _check_update_needed(
db_name, options, transaction)
if not is_upgrade_needed:
options.update = []
options.check_update = []
pool.init(update=options.update or options.check_update,
lang=list(lang),
activatedeps=options.activatedeps)
if is_upgrade_needed:
# If upgrade finishes correctly->update version in database
try:
version_control_table = Table('upgrade_version_control')
cursor = transaction.connection.cursor()
cursor.execute(*version_control_table.update(
columns=[version_control_table.current_version],
values=[new_version]))
transaction.commit()
except:
transaction.rollback()
raise
if options.update_modules_list:
with Transaction().start(db_name, 0) as transaction:
Module = pool.get('ir.module')
Module.update_list()
if lang:
with Transaction().start(db_name, 0) as transaction:
pool = Pool()
Lang = pool.get('ir.lang')
languages = Lang.search([
('code', 'in', lang),
])
Lang.write(languages, {
'translatable': True,
})
for db_name in options.database_names:
with Transaction().start(db_name, 0) as transaction:
pool = Pool()
User = pool.get('res.user')
Configuration = pool.get('ir.configuration')
configuration = Configuration(1)
with transaction.set_context(active_test=False):
admin, = User.search([('login', '=', 'admin')])
if options.email is not None:
admin.email = options.email
elif init[db_name]:
admin.email = input('"admin" email for "%s": ' % db_name)
if init[db_name] or options.password:
configuration.language = main_lang
# try to read password from environment variable
# TRYTONPASSFILE, empty TRYTONPASSFILE ignored
passpath = os.getenv('TRYTONPASSFILE')
password = ''
if passpath:
try:
with open(passpath) as passfile:
password, = passfile.read().splitlines()
except Exception as err:
sys.stderr.write('Can not read password '
'from "%s": "%s"\n' % (passpath, err))
if not password and not options.reset_password:
while True:
password = getpass('"admin" password for "%s": ' %
db_name)
password2 = getpass('"admin" password confirmation: ')
if password != password2:
sys.stderr.write(
'"admin" password confirmation '
'doesn\'t match "admin" password.\n')
continue
if not password:
sys.stderr.write('"admin" password is required.\n')
continue
break
if not options.reset_password:
admin.password = password
admin.save()
if options.reset_password:
User.reset_password([admin])
if options.hostname is not None:
configuration.hostname = options.hostname or None
configuration.save()
def rename(cursor, table_name, old_name, new_name, var_name):
table = Table(table_name)
cursor.execute(
*table.update([getattr(table, var_name)], [new_name], where=(getattr(table, var_name) == old_name))
)
def test_schema(self):
t1 = Table('t1', 'default')
query = t1.update([t1.c1], ['foo'])
self.assertEqual(str(query), 'UPDATE "default"."t1" SET "c1" = %s')
self.assertEqual(query.params, ('foo', ))