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 load_cases(court=None): with connection().cursor() as cursor: cases = Table('vw_case_for_advocates') annulment = Table('vw_computed_case_annulment') latest_advocates = Table('vw_latest_tagging_advocate') latest_results = Table('vw_latest_tagging_case_result') select = cases.join( latest_advocates, type_='LEFT', condition=(latest_advocates.case_id == cases.id_case) & (latest_advocates.status == 'processed') ).join( latest_results, type_='LEFT', condition=(latest_results.case_id == cases.id_case) & (latest_results.status == 'processed') ).join( annulment, type_='LEFT', condition=(annulment.annuled_case == cases.id_case) ).select( Column(cases, '*'), latest_advocates.advocate_id, latest_results.case_result, annulment.annuled_case, annulment.annuling_case ) if court is not None: select.where = cases.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 Case(row)
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 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 load_advocates(): with connection().cursor() as cursor: advocates = Table('advocate') info = Table('advocate_info') select = advocates.join( info, condition=(advocates.id_advocate == info.advocate_id) & (info.valid_to == None) ).select( info.degree_before, info.degree_after, info.name, info.surname, advocates.id_advocate.as_('id'), advocates.identification_number.as_('registration_number'), advocates.registration_number.as_('identification_number') ) 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)) yield Advocate(row)
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 __register__(cls, module): connection = Transaction().connection cursor = connection.cursor() super().__register__(module) table_h = cls.__table_handler__(module) template_lot_type_table_name = config.get( 'table', 'product.template-stock.lot.type', default='product.template-stock.lot.type'.replace('.', '_')) lot_type_table_name = config.get('table', 'stock.lot.type', default='stock.lot.type'.replace( '.', '_')) # Migration from 5.2: fill lot_required if (table_h.table_exist(template_lot_type_table_name) and table_h.table_exist(lot_type_table_name)): table = cls.__table__() template_lot_type = Table(template_lot_type_table_name) lot_type = Table(lot_type_table_name) cursor_select = connection.cursor() cursor_select.execute(*template_lot_type.select( template_lot_type.template, distinct_on=template_lot_type.template)) for template_id, in cursor_select: cursor.execute(*template_lot_type.join( lot_type, condition=template_lot_type.type == lot_type.id ).select(lot_type.code, where=template_lot_type.template == template_id)) value = cls.lot_required.sql_format([t for t, in cursor]) cursor.execute(*table.update([table.lot_required], [value], where=table.id == template_id)) table_h.drop_table('product.template-stock.lot.type', template_lot_type_table_name) table_h.drop_table('stock.lot.type', lot_type_table_name)
def startElement(self, name, attributes): cursor = Transaction().cursor values = {} self.xml_id = attributes['id'] for attr in ('name', 'icon', 'sequence', 'parent', 'action', 'groups'): if attributes.get(attr): values[attr] = attributes.get(attr) if attributes.get('active'): values['active'] = bool(eval(attributes['active'])) if values.get('parent'): values['parent'] = self.mh.get_id(values['parent']) action_name = None if values.get('action'): action_id = self.mh.get_id(values['action']) # TODO maybe use a prefetch for this: action = Table('ir_action') report = Table('ir_action_report') act_window = Table('ir_action_act_window') wizard = Table('ir_action_wizard') url = Table('ir_action_url') act_window_view = Table('ir_action_act_window_view') view = Table('ir_ui_view') icon = Table('ir_ui_icon') cursor.execute(*action.join( report, 'LEFT', condition=action.id == report.action ).join( act_window, 'LEFT', condition=action.id == act_window.action ).join(wizard, 'LEFT', condition=action.id == wizard.action).join( url, 'LEFT', condition=action.id == url.action ).join(act_window_view, 'LEFT', condition=act_window.id == act_window_view.act_window).join( view, 'LEFT', condition=view.id == act_window_view.view ).join(icon, 'LEFT', condition=action.icon == icon.id).select(action.name, action.type, view.type, view.field_childs, icon.name, where=(report.id == action_id) | (act_window.id == action_id) | (wizard.id == action_id) | (url.id == action_id), order_by=act_window_view.sequence, limit=1)) action_name, action_type, view_type, field_childs, icon_name = \ cursor.fetchone() values['action'] = '%s,%s' % (action_type, action_id) icon = attributes.get('icon', '') if icon: values['icon'] = icon elif icon_name: values['icon'] = icon_name elif action_type == 'ir.action.wizard': values['icon'] = 'tryton-executable' elif action_type == 'ir.action.report': values['icon'] = 'tryton-print' elif action_type == 'ir.action.act_window': if view_type == 'tree': if field_childs: values['icon'] = 'tryton-tree' else: values['icon'] = 'tryton-list' elif view_type == 'form': values['icon'] = 'tryton-new' elif view_type == 'graph': values['icon'] = 'tryton-graph' elif view_type == 'calendar': values['icon'] = 'tryton-calendar' elif action_type == 'ir.action.url': values['icon'] = 'tryton-web-browser' else: values['icon'] = 'tryton-new' if values.get('groups'): raise Exception("Please use separate records for groups") if not values.get('name'): if not action_name: raise Exception( "Please provide at least a 'name' attributes " "or a 'action' attributes on the menuitem tags.") else: values['name'] = action_name if values.get('sequence'): values['sequence'] = int(values['sequence']) self.values = values
def migrate_property(model_name, field_names, ValueModel, value_names, parent=None, fields=None): "Migrate property from model_name.field_name to ValueModel.value_name" pool = Pool() Field = pool.get('ir.model.field') Model = pool.get('ir.model') TableHandler = backend.get('TableHandler') if not TableHandler.table_exist('ir_property'): return cursor = Transaction().connection.cursor() field = Field.__table__() model = Model.__table__() table = ValueModel.__table__() if fields is None: fields = [] if isinstance(field_names, basestring): field_names = [field_names] if isinstance(value_names, basestring): value_names = [value_names] def split_value(value): return value.split(',')[1] cast_funcs = { 'numeric': lambda v: Decimal(split_value(v)) if v else None, 'integer': lambda v: int(split_value(v)) if v else None, 'float': lambda v: float(split_value(v)) if v else None, 'char': lambda v: split_value(v) if v else None, 'selection': lambda v: split_value(v) if v else None, 'many2one': lambda v: int(split_value(v)) if v else None, 'reference': lambda v: v, } casts = [] queries = [] for field_name, value_name in zip(field_names, value_names): value_field = getattr(ValueModel, value_name) casts.append(cast_funcs[value_field._type]) property_ = Table('ir_property') columns = [ Literal(None).as_(f) if f != value_name else property_.value.as_(value_name) for f in value_names ] if parent: columns.append(property_.res.as_(parent)) where = property_.res.like(model_name + ',%') else: where = property_.res == Null columns.extend([Column(property_, f).as_(f) for f in fields]) query = property_.join(field, condition=property_.field == field.id).join( model, condition=field.model == model.id).select( *columns, where=where & (field.name == field_name) & (model.model == model_name)) queries.append(query) union = Union(*queries) columns = [Max(Column(union, f)).as_(f) for f in value_names] if parent: columns.append(Column(union, parent).as_(parent)) pcolumns = [Column(union, parent)] else: pcolumns = [] vcolumns = [Column(union, f).as_(f) for f in fields] cursor.execute( *union.select(*(columns + vcolumns), group_by=pcolumns + vcolumns)) columns = [Column(table, f) for f in value_names] if parent: pcolumns = [Column(table, parent)] else: pcolumns = [] vcolumns = [Column(table, f) for f in fields] values = [] l = len(value_names) for row in cursor.fetchall(): value = [c(v) for v, c in zip(row, casts)] if parent: value.append(int(row[l].split(',')[1]) if row[l] else None) i = 1 else: i = 0 value.extend(row[l + i:]) values.append(value) if (values and not ( # No property defined len(values) == 1 and all(x is None for x in values[0][:len(columns)]))): # Delete previous migrated values cursor.execute(*table.delete()) cursor.execute( *table.insert(columns + pcolumns + vcolumns, values=values))
'constraint_name': 'fk_contraint_name', 'table_name': 'table', 'column_name': 'table_2', 'foreign_table_name': 'table2', 'foreign_column_name': 'id' } } return fks[field] q = OOQuery('table', dummy_fk) sql = q.select(['field1', 'field2', 'table_2.name']).where([ ('field1', '=', Field('table_2.name')) ]) t = Table('table') t2 = Table('table2') join = t.join(t2) join.condition = join.left.table_2 == join.right.id sel = join.select(t.field1.as_('field1'), t.field2.as_('field2'), t2.name.as_('table_2.name')) sel.where = And((join.left.field1 == join.right.name,)) expect(tuple(sql)).to(equal(tuple(sel))) with it('must support joins'): def dummy_fk(table, field): fks = { 'table_2': { 'constraint_name': 'fk_contraint_name', 'table_name': 'table', 'column_name': 'table_2', 'foreign_table_name': 'table2', 'foreign_column_name': 'id' }
return { 'table_2': { 'constraint_name': 'fk_contraint_name', 'table_name': 'table', 'column_name': 'table_2', 'foreign_table_name': 'table2', 'foreign_column_name': 'id' } } q = OOQuery('table', dummy_fk) sql = q.select(['field1', 'field2']).where([ ('table_2.code', '=', 'XXX') ]) t = Table('table') join = t.join(Table('table2')) join.condition = join.left.table_2 == join.right.id sel = join.select(t.field1, t.field2) sel.where = And((join.right.code == 'XXX',)) expect(tuple(sql)).to(equal(tuple(sel))) with it('must support deep joins'): def dummy_fk(table): if table == 'table': return { 'table_2_id': { 'constraint_name': 'fk_contraint_name', 'table_name': 'table', 'column_name': 'table_2_id', 'foreign_table_name': 'table2', 'foreign_column_name': 'id'