def test_delete3(self):
t1 = Table('t1')
t2 = Table('t2')
query = t1.delete(where=(t1.c.in_(t2.select(t2.c))))
self.assertEqual(str(query),
'DELETE FROM "t1" WHERE ("c" IN ('
'SELECT "a"."c" FROM "t2" AS "a"))')
self.assertEqual(query.params, ())
def test_delete3(self):
t1 = Table('t1')
t2 = Table('t2')
query = t1.delete(where=(t1.c.in_(t2.select(t2.c))))
self.assertEqual(
str(query), 'DELETE FROM "t1" WHERE ("c" IN ('
'SELECT "a"."c" FROM "t2" AS "a"))')
self.assertEqual(query.params, ())
def write_db_data(cls, nb):
bench_table = Table('benchmark_table')
cursor = Transaction().connection.cursor()
cursor.execute(*bench_table.delete(where=bench_table.id >= 0))
for x in range(nb):
cursor.execute(*bench_table.insert(
columns=[
bench_table.id, bench_table.some_string,
bench_table.some_date
],
values=[[x, str(x) *
10, datetime.date(2016, 1, 1)]]))
def clean_properties_from_4_2(cls):
from sql import Null, Table, Cast
from sql.operators import Like, Concat
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
property = Table('ir_property')
cursor = Transaction().connection.cursor()
cursor.execute(
*property.select(property.res, where=property.res != Null))
res_model_names = list(
set([x[0].split(',')[0] for x in cursor.fetchall()]))
to_delete = {}
for res_model_name in res_model_names:
table_name = res_model_name.replace('.', '_')
res_model = Table(table_name)
query_table = property.join(res_model,
'LEFT OUTER',
condition=(property.res == Concat(
res_model_name + ',',
Cast(res_model.id, 'VARCHAR'))))
cursor.execute(
*query_table.select(property.id,
where=Like(property.res, res_model_name +
',%') & (res_model.id == Null)))
property_ids = [x[0] for x in cursor.fetchall()]
if property_ids:
to_delete[res_model_name] = property_ids
if to_delete:
cursor.execute(*property.delete(
where=property.id.in_(sum([p
for p in to_delete.values()], []))))
for res_model_name, property_ids in to_delete.items():
if property_ids:
print '[%s] - %s Inconsistent record(s) removed' % (
res_model_name, len(property_ids))
else:
print 'Nothing to do - Exisiting property records are clean'
def clean_properties_from_4_2(cls):
from sql import Null, Table, Cast
from sql.operators import Like, Concat
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
property = Table('ir_property')
cursor = Transaction().connection.cursor()
cursor.execute(*property.select(property.res,
where=property.res != Null))
res_model_names = list(set([x[0].split(',')[0]
for x in cursor.fetchall()]))
to_delete = {}
for res_model_name in res_model_names:
table_name = res_model_name.replace('.', '_')
res_model = Table(table_name)
query_table = property.join(res_model, 'LEFT OUTER', condition=(
property.res == Concat(res_model_name + ',',
Cast(res_model.id, 'VARCHAR'))
))
cursor.execute(*query_table.select(property.id,
where=Like(property.res, res_model_name + ',%') &
(res_model.id == Null)))
property_ids = [x[0] for x in cursor.fetchall()]
if property_ids:
to_delete[res_model_name] = property_ids
if to_delete:
cursor.execute(
*property.delete(where=property.id.in_(
sum([p for p in list(to_delete.values())], []))))
for res_model_name, property_ids in list(to_delete.items()):
if property_ids:
print('[%s] - %s Inconsistent record(s) removed' % (
res_model_name, len(property_ids)))
else:
print('Nothing to do - Exisiting property records are clean')
def delete(cursor, table_name, old_name, var_name):
table = Table(table_name)
cursor.execute(*table.delete(where=(getattr(table, var_name) == old_name)))
def delete(cursor, table_name, old_name, var_name):
table = Table(table_name)
cursor.execute(*table.delete(
where=(getattr(table, var_name) == old_name)))