class OOQuery(object):
def __init__(self, table, foreign_key=None):
self.fields = None
self.table = Table(table)
self._select = self.table.select()
self.parser = Parser(self.table, foreign_key)
@property
def select_on(self):
if self.parser.joins:
return self.parser.joins[-1]
else:
return self.table
def select(self, fields=None):
if fields:
self.fields = [getattr(self.table, arg) for arg in fields]
self._select = self.table.select(*self.fields)
return self
def where(self, domain):
where = self.parser.parse(domain)
self._select = self.select_on.select(*self.fields)
self._select.where = where
return self._select
def __register__(cls, module_name):
TableHandler = backend.get('TableHandler')
cursor = Transaction().cursor
super(ProductMedia, cls).__register__(module_name)
media_table = cls.__table__()
if TableHandler.table_exist(cursor, 'product_product_imageset'):
# Migrate data from ProductImageSet table to ProductMedia table
imageset_table = Table('product_product_imageset')
cursor.execute(*media_table.insert(
columns=[
media_table.sequence,
media_table.product, media_table.template,
media_table.static_file,
],
values=imageset_table.select(
Literal(10),
imageset_table.product, imageset_table.template,
imageset_table.image
)
))
TableHandler.drop_table(
cursor, 'product.product.imageset', 'product_product_imageset',
cascade=True
)
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 test_schema(self):
t1 = Table('t1', 'default')
query = t1.insert([t1.c1], [['foo']])
self.assertEqual(str(query),
'INSERT INTO "default"."t1" ("c1") VALUES (%s)')
self.assertEqual(query.params, ('foo',))
def test_0050_query_pagination(self):
"""
Test pagination via `nereid.contrib.QueryPagination.`
"""
self.setup_defaults()
# Create a 100 addresses
for id in xrange(0, 100):
self.address_obj.create([{
'party': self.guest_party,
'name': 'User %s' % id,
}])
table = Table('party_address')
select_query = table.select()
pagination = QueryPagination(self.address_obj,
select_query,
table,
page=1,
per_page=10)
self.assertEqual(pagination.count, 100)
self.assertEqual(pagination.pages, 10)
self.assertEqual(pagination.begin_count, 1)
self.assertEqual(pagination.end_count, 10)
def create_db(name=DB_NAME, lang='en'):
Database = backend.get('Database')
if not db_exist(name):
with Transaction().start(
None, 0, close=True, autocommit=True, _nocache=True) \
as transaction:
transaction.database.create(transaction.connection, name)
with Transaction().start(name, 0, _nocache=True) as transaction,\
transaction.connection.cursor() as cursor:
Database(name).init()
ir_configuration = Table('ir_configuration')
cursor.execute(*ir_configuration.insert(
[ir_configuration.language], [[lang]]))
pool = Pool(name)
pool.init(update=['res', 'ir'], lang=[lang])
with Transaction().start(name, 0) as transaction:
User = pool.get('res.user')
Lang = pool.get('ir.lang')
language, = Lang.search([('code', '=', lang)])
language.translatable = True
language.save()
users = User.search([('login', '!=', 'root')])
User.write(users, {
'language': language.id,
})
Module = pool.get('ir.module')
Module.update_list()
def sync(cls, transaction):
database = transaction.database
dbname = database.name
if not _clear_timeout and database.has_channel():
pid = os.getpid()
with cls._listener_lock[pid]:
if (pid, dbname) not in cls._listener:
cls._listener[pid, dbname] = listener = threading.Thread(
target=cls._listen, args=(dbname,), daemon=True)
listener.start()
return
if (datetime.now() - cls._clean_last).total_seconds() < _clear_timeout:
return
connection = database.get_connection(readonly=True, autocommit=True)
try:
with connection.cursor() as cursor:
table = Table(cls._table)
cursor.execute(*table.select(
_cast(table.timestamp), table.name))
timestamps = {}
for timestamp, name in cursor.fetchall():
timestamps[name] = timestamp
finally:
database.put_connection(connection)
for name, timestamp in timestamps.items():
try:
inst = cls._instances[name]
except KeyError:
continue
inst_timestamp = inst._timestamp.get(dbname)
if not inst_timestamp or timestamp > inst_timestamp:
inst._clear(dbname, timestamp)
cls._clean_last = datetime.now()
def document_db_table_columns(self):
with connection().cursor() as cursor:
documents = Table(self.document_db_table)
select = documents.select(Column(documents, '*'), limit=1)
query, args = tuple(select)
cursor.execute(query, args)
return [col[0] for col in cursor.description]
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 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):
TableHandler = backend.get('TableHandler')
cursor = Transaction().cursor
super(ProductMedia, cls).__register__(module_name)
media_table = cls.__table__()
if TableHandler.table_exist(cursor, 'product_product_imageset'):
# Migrate data from ProductImageSet table to ProductMedia table
imageset_table = Table('product_product_imageset')
cursor.execute(*media_table.insert(
columns=[
media_table.sequence,
media_table.product,
media_table.template,
media_table.static_file,
],
values=imageset_table.select(Literal(
10), imageset_table.product, imageset_table.template,
imageset_table.image)))
TableHandler.drop_table(cursor,
'product.product.imageset',
'product_product_imageset',
cascade=True)
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 init(self):
from trytond.modules import get_module_info
with self.get_connection() as conn:
cursor = conn.cursor()
sql_file = os.path.join(os.path.dirname(__file__), 'init.sql')
with open(sql_file) as fp:
for line in fp.read().split(';'):
if (len(line) > 0) and (not line.isspace()):
cursor.execute(line)
ir_module = Table('ir_module')
ir_module_dependency = Table('ir_module_dependency')
for module in ('ir', 'res'):
state = 'uninstalled'
if module in ('ir', 'res'):
state = 'to install'
info = get_module_info(module)
insert = ir_module.insert(
[ir_module.create_uid, ir_module.create_date,
ir_module.name, ir_module.state],
[[0, CurrentTimestamp(), module, state]])
cursor.execute(*insert)
cursor.execute('SELECT last_insert_rowid()')
module_id, = cursor.fetchone()
for dependency in info.get('depends', []):
insert = ir_module_dependency.insert(
[ir_module_dependency.create_uid,
ir_module_dependency.create_date,
ir_module_dependency.module,
ir_module_dependency.name,
],
[[0, CurrentTimestamp(), module_id, dependency]])
cursor.execute(*insert)
conn.commit()
def restore_default_party_lang_from_4_2(cls):
from trytond.transaction import Transaction
from sql import Null, Table, Cast
from sql.operators import Concat
from trytond.pool import Pool
TableHandler = backend.get('TableHandler')
if not TableHandler.table_exist('ir_property'):
return
pool = Pool()
property = Table('ir_property')
Lang = pool.get('ir.lang')
field = pool.get('ir.model.field').__table__()
lang = Lang.__table__()
cursor = Transaction().connection.cursor()
query_table = property.join(lang, condition=(
property.value == Concat('ir.lang,', Cast(lang.id, 'VARCHAR'))
)).join(field, condition=((property.field == field.id) &
(field.name == 'lang')))
cursor.execute(
*query_table.select(lang.id, where=property.res == Null))
result = cursor.fetchone()
if result:
result = list(result)
default_lang = Lang(result[0])
print('Default Language restored [%s]' % default_lang.rec_name)
pool.get('party.configuration.party_lang'
).create([{'party_lang': default_lang}])
else:
print('No default language on party configuration found')
def login_from_id(user_id):
with Transaction().new_transaction(readonly=True) as t:
cursor = t.connection.cursor()
table = Table('res_user')
cursor.execute(*table.select(table.login,
where=table.id == user_id))
return cursor.fetchone()[0]
def test_select_from_list(self):
t2 = Table('t2')
t3 = Table('t3')
query = (self.table + t2 + t3).select(self.table.c, getattr(t2, '*'))
self.assertEqual(str(query),
'SELECT "a"."c", "b".* FROM "t" AS "a", "t2" AS "b", "t3" AS "c"')
self.assertEqual(query.params, ())
def test_0050_query_pagination(self):
"""
Test pagination via `nereid.contrib.QueryPagination.`
"""
with Transaction().start(DB_NAME, USER, CONTEXT):
self.setup_defaults()
# Create a 100 addresses
for id in xrange(0, 100):
self.address_obj.create([{
'party': self.guest_party,
'name': 'User %s' % id,
}])
table = Table('party_address')
select_query = table.select()
pagination = QueryPagination(
self.address_obj, select_query, table, page=1, per_page=10
)
self.assertEqual(pagination.count, 100)
self.assertEqual(pagination.pages, 10)
self.assertEqual(pagination.begin_count, 1)
self.assertEqual(pagination.end_count, 10)
def test_in(self):
for in_ in [
In(self.table.c1, [self.table.c2, 1, Null]),
~NotIn(self.table.c1, [self.table.c2, 1, Null]),
~~In(self.table.c1, [self.table.c2, 1, Null])
]:
self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))')
self.assertEqual(in_.params, (1, None))
t2 = Table('t2')
in_ = In(self.table.c1, t2.select(t2.c2))
self.assertEqual(str(in_),
'("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))')
self.assertEqual(in_.params, ())
in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3))
self.assertEqual(
str(in_), '("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" '
'UNION SELECT "a"."c3" FROM "t2" AS "a"))')
self.assertEqual(in_.params, ())
in_ = In(self.table.c1, array('l', range(10)))
self.assertEqual(str(in_),
'("c1" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s))')
self.assertEqual(in_.params, tuple(range(10)))
def PackageInfo(jmgr, os_target, sql, args):
sql.connect_table(tables['package_info'])
sql.connect_table(tables['package_dependency'])
pkgname = args[0]
pkg_id = get_package_id(sql, pkgname)
pkg_info = os_target.get_package_info(pkgname)
pkg_deps = os_target.get_package_dependency(pkgname)
# Clean up records
sql.delete_record(tables['package_info'],
'pkg_id=\'' + Table.stringify(pkg_id) + '\'')
sql.delete_record(tables['package_dependency'],
'pkg_id=\'' + Table.stringify(pkg_id) + '\'')
pkg_info['pkg_id'] = pkg_id
sql.append_record(tables['package_info'], pkg_info)
for dep in pkg_deps:
dep_id = get_package_id(sql, dep)
values = dict()
values['pkg_id'] = pkg_id
values['dependency'] = dep_id
sql.append_record(tables['package_dependency'], values)
sql.commit()
def test_insert_subselect(self):
t1 = Table("t1")
t2 = Table("t2")
subquery = t2.select(t2.c1, t2.c2)
query = t1.insert([t1.c1, t1.c2], subquery)
self.assertEqual(str(query), 'INSERT INTO "t1" ("c1", "c2") ' 'SELECT "a"."c1", "a"."c2" FROM "t2" AS "a"')
self.assertEqual(query.params, ())
def _get_modules(cursor):
ir_module = Table('ir_module')
cursor.execute(*ir_module.select(
ir_module.name,
where=ir_module.state.in_(
['activated', 'to upgrade', 'to remove'])))
return {m for m, in cursor}
def load_documents(court=None):
with connection().cursor() as cursor:
documents = Table('document')
cases = Table('vw_case_for_advocates')
pre_select = documents.join(
cases, type_='INNER',
condition=(cases.id_case==documents.case_id)
)
specific_court_columns = []
for specific_court in Courts.ALL:
court_documents = Table(specific_court.document_db_table)
for col in specific_court.document_db_table_columns:
specific_court_columns.append(Column(court_documents, col).as_('{}__{}'.format(specific_court.name, col)))
pre_select = pre_select.join(
court_documents, type_='LEFT',
condition=(documents.id_document==court_documents.document_id)
)
select = pre_select.select(Column(documents, '*'), *specific_court_columns)
if court is not None:
select.where = documents.court_id == court.id
query, args = tuple(select)
cursor.execute(query, args)
cols = [col[0] for col in cursor.description]
for fetched in cursor:
row = dict(zip(cols, fetched))
current_court = court if court is not None else Courts.BY_ID[row['court_id']]
row['court_id'] = current_court.name
yield current_court.create_document(row)
def _check_update_needed(db_name, options):
# Get current main module version
main_module = config.get('version', 'module', default='coog_core')
current_main_module_version = get_module_info(main_module)['version']
# Do the upgrade anyway if -u is activated
if options.update:
return True, current_main_module_version
# Get main module version which stocked in the database
version_control_table = Table('upgrade_version_control')
cursor = Transaction().connection.cursor()
cursor.execute(
*version_control_table.select(version_control_table.current_version))
db_main_module_version = cursor.fetchone()[0]
if (options.check_update
and current_main_module_version != db_main_module_version):
logger.warning(
f'Current code version ({current_main_module_version}) is '
'different from the last update version '
'({db_main_module_version}), updating')
return True, current_main_module_version
logger.warning(f'Current code version ({current_main_module_version}) '
'matches last update version, nothing to do')
return False, current_main_module_version
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_order_query(self):
table = Table('t')
column = Column(table, 'c')
query = table.select(column)
self.assertEqual(str(Asc(query)),
'(SELECT "a"."c" FROM "t" AS "a") ASC')
self.assertEqual(str(Desc(query)),
'(SELECT "a"."c" FROM "t" AS "a") DESC')
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 registry_sign(self):
cases = Table('vw_case_for_advocates')
select = cases.select(cases.registry_sign)
select.where = cases.id_case == self.info['case_id']
query, args = tuple(select)
with connection().cursor() as cursor:
cursor.execute(query, args)
return cursor.fetchone()[0]
def test_order_query(self):
table = Table('t')
column = Column(table, 'c')
query = table.select(column)
self.assertEqual(str(Asc(query)),
'(SELECT "a"."c" FROM "t" AS "a") ASC')
self.assertEqual(str(Desc(query)),
'(SELECT "a"."c" FROM "t" AS "a") DESC')
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_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 BinarySymbol(jmgr, os_target, sql, args):
sql.connect_table(tables['binary_list'])
sql.connect_table(tables['binary_symbol'])
pkgname = args[0]
bin = args[1]
dir = args[2]
if len(args) > 3:
ref = args[3]
if not package.reference_exists(dir, ref):
dir = None
ref = None
else:
ref = None
unpacked = False
if not dir:
(dir, pkgname, _) = package.unpack_package(os_target, args[0])
if not dir:
return
unpacked = True
exc = None
try:
path = dir + '/' + bin
if not os.path.exists(path):
raise Exception('path ' + path + ' does not exist')
symbols = os_target.get_binary_symbols(dir, bin)
pkg_id = get_package_id(sql, pkgname)
bin_id = get_binary_id(sql, bin)
condition = 'pkg_id=' + Table.stringify(
pkg_id) + ' and bin_id=' + Table.stringify(bin_id)
sql.delete_record(tables['binary_symbol'], condition)
for sym in symbols:
values = dict()
values['pkg_id'] = pkg_id
values['bin_id'] = bin_id
values['symbol_name'] = sym.name
values['version'] = sym.version
values['func_addr'] = sym.addr
sql.append_record(tables['binary_symbol'], values)
sql.update_record(tables['binary_list'], {'callgraph': False},
condition)
sql.commit()
except Exception as err:
exc = sys.exc_info()
if (ref and package.dereference_dir(dir, ref)) or unpacked:
package.remove_dir(dir)
if exc:
raise exc[1], None, exc[2]
def setUp(self):
self.synchro = threading.Event()
self.succeed1 = threading.Event()
self.succeed2 = threading.Event()
self.finish1 = threading.Event()
self.finish2 = threading.Event()
self.t1 = Table('t1')
self.t2 = Table('t2')
def test_select_join(self):
t1 = Table('t1')
t2 = Table('t2')
join = Join(t1, t2)
self.assertEqual(str(join.select()),
'SELECT * FROM "t1" AS "a" INNER JOIN "t2" AS "b"')
self.assertEqual(str(join.select(getattr(t1, '*'))),
'SELECT "a".* FROM "t1" AS "a" INNER JOIN "t2" AS "b"')
def test_lateral_select(self):
t1 = Table('t1')
t2 = Table('t2')
lateral = Lateral(t2.select(where=t2.id == t1.t2))
query = From([t1, lateral]).select()
self.assertEqual(str(query),
'SELECT * FROM "t1" AS "a", LATERAL '
'(SELECT * FROM "t2" AS "c" WHERE ("c"."id" = "a"."t2")) AS "b"')
self.assertEqual(query.params, ())
def clean(dbname):
with Transaction().new_transaction(_nocache=True) as transaction,\
transaction.connection.cursor() as cursor:
table = Table('ir_cache')
cursor.execute(*table.select(table.timestamp, table.name))
timestamps = {}
for timestamp, name in cursor.fetchall():
timestamps[name] = timestamp
for inst in BaseCache._cache_instance:
inst.clean_inst(dbname, timestamps)
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 analysis_binary_instr_linear(sql, binary, pkg_id, bin_id):
condition = 'pkg_id=' + Table.stringify(
pkg_id) + ' and bin_id=' + Table.stringify(bin_id)
condition_unknown = condition + ' and known=False'
sql.delete_record(tables['binary_call'], condition)
sql.delete_record(tables['binary_call_unknown'], condition_unknown)
sql.delete_record(tables['binary_opcode_usage'], condition)
sql.delete_record(tables['binary_call_missrate'], condition)
get_callgraph(binary, False, True, False, sql, pkg_id, bin_id)
def test_lateral_select(self):
t1 = Table('t1')
t2 = Table('t2')
lateral = Lateral(t2.select(where=t2.id == t1.t2))
query = From([t1, lateral]).select()
self.assertEqual(
str(query), 'SELECT * FROM "t1" AS "a", LATERAL '
'(SELECT * FROM "t2" AS "c" WHERE ("c"."id" = "a"."t2")) AS "b"')
self.assertEqual(query.params, ())
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select(t1.c1))
query = self.table.delete(with_=[w],
where=self.table.c2.in_(w.select(w.c3)))
self.assertEqual(str(query),
'WITH a AS (SELECT "b"."c1" FROM "t1" AS "b") '
'DELETE FROM "t" WHERE ("c2" IN (SELECT "a"."c3" FROM a AS "a"))')
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 migrate(self):
obj = objectify.fromstring(self.content)
t = Table('ir_model_data')
for xml_record in obj.iter(tag='record'):
record = self._record(xml_record)
self.records.append(record)
sp = []
for field in self.search_params.get(record.model, record.vals.keys()):
sp.append((field, '=', record.vals[field]))
logger.info('Trying to find existing record with query: {}'.format(
sp
))
table = record.model.replace('.', '_')
q = OOQuery(table)
sql = q.select(['id']).where(sp)
logger.debug(tuple(sql))
self.cursor.execute(*sql)
res_id = self.cursor.fetchone()
if res_id:
res_id = res_id[0]
logger.info('Record {}.{} found! ({} id:{})'.format(
self.module, record.id, record.model, res_id
))
else:
logger.info('Record {}.{} not found!'.format(
self.module, record.id
))
# We have to create the model
table_model = Table(record.model.replace('.', '_'))
columns = []
values = []
for col, value in record.vals.items():
columns.append(getattr(table_model, col))
values.append(value)
sql = table_model.insert(
columns=columns, values=[values], returning=[table_model.id]
)
logger.debug(tuple(sql))
self.cursor.execute(*sql)
res_id = self.cursor.fetchone()[0]
logger.info('Creating record {}.{} ({} id:{})'.format(
self.module, record.id, record.model, res_id
))
sql = t.insert(
columns=[t.name, t.model, t.noupdate, t.res_id, t.module],
values=[(record.id, record.model, record.noupdate, res_id,
self.module)]
)
logger.debug(tuple(sql))
logger.info('Linking model data {}.{} -> record {} id:{}'.format(
self.module, record.id, record.model, res_id
))
self.cursor.execute(*sql)
def get_packages_by_ranks(os_target, sql, min, max):
sql.connect_table(tables['package_popularity'])
packages = os_target.get_packages()
packages_by_ranks = sql.search_record(
tables['package_popularity'], 'rank >= ' + Table.stringify(min) +
' AND rank <= ' + Table.stringify(max), ['package_name'])
result = []
for (name, ) in packages_by_ranks:
if name in packages:
result.append(name)
return result
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select())
query = self.table.insert([self.table.c1],
with_=[w],
values=w.select())
self.assertEqual(
str(query), 'WITH "a" AS (SELECT * FROM "t1" AS "b") '
'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
def show_table(self, text, filt=None):
"""show_table will update the GUI with a new or updated table. This function will first change the table,
then run a select operation on the table with a given filter. Then the table will be regenerated. The drop down
menus will only contain unique values to select from. Finally, the function will delete any rows that aren't needed.
In general, the function tries to modify existing widgets instead of remaking the whole thing. If the table is empty,
then options in the dropdown will be filled with Null. However, if the filter provides nothing, show_tables will quit
Arguments:
text {str} -- text is the name of the table that is to be searched. This is automatically
filled in by the Table comboBox on selection. Otherwise, itshould be the same
Keyword Arguments:
filt {str} -- filt is a string in SQL syntax(TODO:Change this to take only a list) that
describes that all SQL rows must match to be returned (default: {None})
"""
# updates all of the columns for a given table
if self.table.name != text:
self.table = Table(self.db, text)
# Regen table if it's the wrong one
results = self.table.selectTable(filt=filt)
# add a getTable or something that returns all of this stuff and takes advantage of the column class
if results:
# TODO: Replace is select distinct
# transposes results, then selects only unique values
t_results = self.transpose_results(results)
f_results = self.filter_results(t_results)
else:
# print an error message stating that nothing could be found
if filt:
self.display_msg('Table {} has no rows that match filter {}'.format(
self.table.name, filt))
return # quit here to not Null out the results
else:
self.display_msg(
'Table {} has no rows'.format(self.table.name))
# show the new table as long as there wasn't a filter that returned nothing
for i in range(self.table.numColumns()):
# iterate through all of the columns
self.add_label(self.table.colList[i].getName(), i+1, 0)
self.add_line_edit(i+1, 2)
if results:
# if column is foreign key, show selectable options as names of the foreign table values
# instead of indexes
if self.table.colList[i].isForeign():
self.add_combo(
self.table.colList[i].getFKeyVals('Name', f_results[i]), i+1, 1)
else:
self.add_combo(f_results[i], i+1, 1)
else:
# if there aren't any options available here, then just add a Null comboBox
self.add_combo(['Null'], i+1, 1)
self.remove_rows_below(self.table.numColumns())
def test_join_subselect(self):
t1 = Table('t1')
t2 = Table('t2')
select = t2.select()
join = Join(t1, select)
join.condition = t1.c == select.c
with AliasManager():
self.assertEqual(str(join),
'"t1" AS "a" INNER JOIN (SELECT * FROM "t2" AS "c") AS "b" '
'ON ("a"."c" = "b"."c")')
self.assertEqual(join.params, ())
def analysis_binary_instr(sql, binary, pkg_id, bin_id):
codes = get_callgraph(binary)
condition = 'pkg_id=' + Table.stringify(
pkg_id) + ' and bin_id=' + Table.stringify(bin_id)
condition_unknown = condition + ' and known=False'
sql.delete_record(tables['binary_call'], condition)
sql.delete_record(tables['binary_call_unknown'], condition_unknown)
sql.delete_record(tables['binary_instr_usage'], condition)
for func in codes.funcs:
instrs = dict()
calls = []
for bb in func.bblocks:
for instr in bb.instrs:
if isinstance(instr, InstrCall):
if isinstance(instr.target, int) or isinstance(
instr.target, long):
if not instr.target in calls:
calls.append(instr.target)
elif isinstance(instr.target, Op) and instr.target.val:
if not instr.target.val in calls:
calls.append(instr.target.val)
instr_name = instr.get_instr()
if instr_name in instrs:
instrs[instr_name] = instrs[instr_name] + 1
else:
instrs[instr_name] = 1
for call in calls:
values = dict()
values['pkg_id'] = pkg_id
values['bin_id'] = bin_id
values['func_addr'] = func.entry
if isinstance(call, int) or isinstance(call, long):
values['call_addr'] = call
else:
for addr, sym in codes.dynsyms.items():
if sym.name == call:
values['call_addr'] = sym.value
break
values['call_name'] = call
sql.append_record(tables['binary_call'], values)
for instr, count in instrs.items():
values = dict()
values['pkg_id'] = pkg_id
values['bin_id'] = bin_id
values['func_addr'] = func.entry
values['instr'] = instr
values['count'] = count
sql.append_record(tables['binary_instr_usage'], values)
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select())
query = self.table.insert(
[self.table.c1],
with_=[w],
values=w.select())
self.assertEqual(str(query),
'WITH "a" AS (SELECT * FROM "t1" AS "b") '
'INSERT INTO "t" ("c1") SELECT * FROM "a" AS "a"')
self.assertEqual(query.params, ())
def test_with(self):
t1 = Table('t1')
w = With(query=t1.select(t1.c1))
query = self.table.update(
[self.table.c2],
with_=[w],
values=[w.select(w.c3, where=w.c4 == 2)])
self.assertEqual(str(query),
'WITH b AS (SELECT "c"."c1" FROM "t1" AS "c") '
'UPDATE "t" SET "c2" = (SELECT "b"."c3" FROM b AS "b" '
'WHERE ("b"."c4" = %s))')
self.assertEqual(query.params, (2,))
def __register__(cls, module_name):
cursor = Transaction().cursor
model_data = Table('ir_model_data')
# 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)))
super(Purchase, cls).__register__(module_name)
def clean(cls, dbname):
with Transaction().new_transaction() as transaction,\
transaction.connection.cursor() as cursor:
table = Table('ir_cache')
cursor.execute(*table.select(table.timestamp, table.name))
timestamps = {}
for timestamp, name in cursor.fetchall():
timestamps[name] = timestamp
for inst in cls._cache_instance:
if inst._name in timestamps:
with inst._lock:
if (not inst._timestamp
or timestamps[inst._name] > inst._timestamp):
inst._timestamp = timestamps[inst._name]
inst._empty(dbname)
def test_in(self):
in_ = In(self.table.c1, [self.table.c2, 1, None])
self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))')
self.assertEqual(in_.params, (1, None))
t2 = Table('t2')
in_ = In(self.table.c1, t2.select(t2.c2))
self.assertEqual(str(in_),
'("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))')
self.assertEqual(in_.params, ())
in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3))
self.assertEqual(str(in_),
'("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" '
'UNION SELECT "a"."c3" FROM "t2" AS "a"))')
self.assertEqual(in_.params, ())
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 _ref(self, ref):
if '.' in ref:
module, xml_id = ref.split('.')
else:
xml_id = ref
module = self.module
t = Table('ir_model_data')
select = t.select(t.res_id)
select.where = (t.module == module) & (t.name == xml_id)
self.cursor.execute(*select)
res = self.cursor.fetchone()
if not res:
raise KeyError('Reference: {}.{} not found'.format(
module, xml_id
))
return res[0]
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 __register__(cls, module_name):
TableHandler = backend.get('TableHandler')
cursor = Transaction().connection.cursor()
table = TableHandler(cls, module_name)
sql_table = cls.__table__()
journal_account = Table('account_journal_account')
created_account = not table.column_exist('account')
super(Journal, cls).__register__(module_name)
# Migration from 4.8: new account field
if created_account and table.table_exist('account_journal_account'):
value = journal_account.select(journal_account.credit_account,
where=((journal_account.journal == sql_table.journal) &
(journal_account.credit_account ==
journal_account.debit_account)))
# Don't use UPDATE FROM because SQLite does not support it.
cursor.execute(*sql_table.update([sql_table.account], [value]))
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 __register__(cls, module_name):
TableHandler = backend.get('TableHandler')
cursor = Transaction().cursor
sql_table = cls.__table__()
super(InvoiceLine, cls).__register__(module_name)
# Migration from 2.6: remove purchase_lines
rel_table_name = 'purchase_line_invoice_lines_rel'
if TableHandler.table_exist(cursor, rel_table_name):
rel_table = Table(rel_table_name)
cursor.execute(*rel_table.select(
rel_table.purchase_line, rel_table.invoice_line))
for purchase_line, invoice_line in cursor.fetchall():
cursor.execute(*sql_table.update(
columns=[sql_table.origin],
values=['purchase.line,%s' % purchase_line],
where=sql_table.id == invoice_line))
TableHandler.drop_table(cursor,
'purchase.line-account.invoice.line', rel_table_name)
def test(self):
sqlite_master = Table('sqlite_master')
select = sqlite_master.select(sqlite_master.name)
select.where = sqlite_master.type == 'table'
select.where &= sqlite_master.name.in_([
'ir_model',
'ir_model_field',
'ir_ui_view',
'ir_ui_menu',
'res_user',
'res_group',
'ir_module',
'ir_module_dependency',
'ir_translation',
'ir_lang',
])
try:
self.cursor.execute(*select)
except Exception:
return False
return len(self.cursor.fetchall()) != 0
def test_in(self):
for in_ in [In(self.table.c1, [self.table.c2, 1, Null]),
~NotIn(self.table.c1, [self.table.c2, 1, Null]),
~~In(self.table.c1, [self.table.c2, 1, Null])]:
self.assertEqual(str(in_), '("c1" IN ("c2", %s, %s))')
self.assertEqual(in_.params, (1, None))
t2 = Table('t2')
in_ = In(self.table.c1, t2.select(t2.c2))
self.assertEqual(str(in_),
'("c1" IN (SELECT "a"."c2" FROM "t2" AS "a"))')
self.assertEqual(in_.params, ())
in_ = In(self.table.c1, t2.select(t2.c2) | t2.select(t2.c3))
self.assertEqual(str(in_),
'("c1" IN (SELECT "a"."c2" FROM "t2" AS "a" '
'UNION SELECT "a"."c3" FROM "t2" AS "a"))')
self.assertEqual(in_.params, ())
in_ = In(self.table.c1, array('l', range(10)))
self.assertEqual(str(in_),
'("c1" IN (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s))')
self.assertEqual(in_.params, tuple(range(10)))
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, ())