Exemplo n.º 1
0
class ApiMethodURLParamEntity(Model):
    """Сущность параметра url (для rest)"""

    __tablename__ = 'api_method_url_param_entity'

    api_method_id = reference_col('api_method', unique=False, nullable=False)
    api_method = relationship('ApiMethod', backref='set_url_param_entity')
    param_name = Column(db.String(80), unique=False, nullable=False)
    param_position = Column(db.Integer, unique=False, nullable=False)
    entity_id = reference_col('entity', unique=False, nullable=False)
    entity = relationship('Entity', backref='set_url_param_entity')

    __table_args__ = (
        UniqueConstraint('api_method_id',
                         'param_position',
                         name='_api_method_position_uc'),
        UniqueConstraint('api_method_id',
                         'entity_id',
                         name='_api_method_entity_id_uc'),
    )

    @classmethod
    def get(cls, api_method_id):
        params = cls.query.join(Entity, Entity.id == cls.entity_id).filter(
            cls.api_method_id == api_method_id, ).order_by(
                cls.param_position).all()
        res = [rec.entity.code for rec in params]
        return res
Exemplo n.º 2
0
class ApiMethod(Model):
    """API метод"""

    __tablename__ = 'api_method'

    entity_id = reference_col('entity', unique=False, nullable=False)
    entity = relationship('Entity', backref='set_api_method')
    protocol_id = reference_col('protocol', unique=False, nullable=False)
    protocol = relationship('Protocol', backref='set_api_method')
    method = Column(db.String(80), unique=False,
                    nullable=False)  # service method/rest method
    template_url = Column(db.Text(), unique=False,
                          nullable=False)  # wsdl uri/rest url template
    version = Column(db.Integer,
                     unique=False,
                     nullable=False,
                     server_default='0')
    host_id = reference_col('host', unique=False, nullable=False)
    host = relationship('Host', backref='set_api_method')

    __table_args__ = (
        # CheckConstraint("protocol_code = '%s' OR wsdl_uri > ''" % ProtocolCode.REST, name='_wsdl_uri_chk'),
    )

    @classmethod
    def get(cls, system_code, entity_code, operation_code, version):
        method = cls.query.join(Entity, Entity.id == cls.entity_id).join(
            System, System.id == Entity.system_id).join(
                Protocol, Protocol.id == cls.protocol_id).join(
                    ApiMethodOperation,
                    ApiMethodOperation.api_method_id == cls.id).join(
                        Operation,
                        Operation.id == ApiMethodOperation.operation_id).join(
                            Host, Host.id == cls.host_id).filter(
                                Entity.code == entity_code,
                                Operation.code == operation_code,
                                System.code == system_code,
                                ApiMethod.version == version,
                            ).first()
        if not method:
            params = {
                'system_code': system_code,
                'entity_code': entity_code,
                'operation_code': operation_code,
                'version': version,
            }
            raise InternalError(
                'Method not registered in %s (%s)' % (cls.__name__, ', '.join(
                    (k + '=%s' % v for k, v in params.items()))))
        params_entities = ApiMethodURLParamEntity.get(method.id)
        sys_url = method.host.url.rstrip('/')
        res = {
            'protocol': method.protocol.code,
            'method': method.method,
            'template_url': sys_url + method.template_url,
            'params_entities': params_entities,
        }
        return res
Exemplo n.º 3
0
class ApiMethodOperation(Model):
    """Операция метода"""

    __tablename__ = 'api_method_operation'

    api_method_id = reference_col('api_method', unique=False, nullable=False)
    api_method = relationship('ApiMethod', backref='set_method_operation')
    operation_id = reference_col('operation', unique=False, nullable=False)
    operation = relationship('Operation', backref='set_api_method')

    __table_args__ = (UniqueConstraint('api_method_id',
                                       'operation_id',
                                       name='_api_method_operation_uc'), )
Exemplo n.º 4
0
class SchGrReqExecute(Model):
    """История выполнения расписания запросов сущностей"""

    __tablename__ = 'sch_gr_req_execute'

    sch_group_request_id = reference_col('sch_group_request', unique=False, nullable=False)
    sch_group_request = relationship('ScheduleGroupRequest', backref='set_sch_gr_req_execute')
    begin_datetime = Column(db.DateTime, unique=False, nullable=False)
    end_datetime = Column(db.DateTime, unique=False, nullable=True)

    @classmethod
    def begin(cls, sch_group_request):
        res = cls.create(
            sch_group_request_id=sch_group_request.id,
            begin_datetime=datetime.today()
        )
        return res

    def end(self):
        self.update(
            end_datetime=datetime.today()
        )

    @classmethod
    def last_datetime(cls, local_entity_code):
        res = cls.query.join(
            ScheduleGroupRequest
        ).join(
            Entity
        ).filter(
            Entity.code == local_entity_code
        ).order_by(
            cls.id.desc()
        ).limit(1).first()
        return res and res.begin_datetime
Exemplo n.º 5
0
class ServiceMethod(Model):
    """Service метод"""

    __tablename__ = 'service_method'

    method_code = Column(db.String(80), unique=True,
                         nullable=False)  # service method/rest method
    entity_id = reference_col('entity', unique=False, nullable=False)
    entity = relationship('Entity', backref='set_service_method')

    __table_args__ = (UniqueConstraint('entity_id',
                                       'method_code',
                                       name='_entity_method_name_uc'), )

    @classmethod
    def get_entity(cls, method_code):
        method = cls.query.join(Entity, Entity.id == cls.entity_id).join(
            System, System.id == Entity.system_id).filter(
                cls.method_code == method_code, ).first()
        if not method:
            raise InternalError('Service method (%s) not registered in %s' %
                                (method_code, cls.__name__))
        res = {
            'entity_code': method.entity.code,
            'system_code': method.entity.system.code,
        }
        return res
Exemplo n.º 6
0
class EntityImageDiff(Model):
    __tablename__ = 'entity_image_diff'

    root_entity_id = reference_col('entity', unique=False, nullable=False)
    root_entity = relationship('Entity', backref='set_entity_image_diff_by_root',
                               foreign_keys=root_entity_id)
    entity_id = reference_col('entity', unique=False, nullable=False)
    entity = relationship('Entity', backref='set_entity_image_diff',
                          foreign_keys=entity_id)
    root_external_id = Column(db.String(80), unique=False, nullable=False)
    external_id = Column(db.String(80), unique=False, nullable=False, index=True)
    key = Column(db.String(80), unique=False, nullable=False, index=True, server_default='')
    content = Column(JSONB, unique=False, nullable=True)
    operation_code = Column(db.String(80), unique=False, nullable=False)
    level = Column(db.Integer, unique=False, nullable=False)

    __table_args__ = {'prefixes': ['TEMPORARY']}
Exemplo n.º 7
0
class ScheduleGroupRequest(Model):
    """Данные запросов в группе"""

    __tablename__ = 'sch_group_request'

    schedule_group_id = reference_col('schedule_group', unique=False, nullable=False)
    schedule_group = relationship('ScheduleGroup', backref='set_sch_group_request')
    entity_id = reference_col('entity', unique=False, nullable=False)  # что
    entity = relationship('Entity', backref='set_sch_group_request')
    system_id = reference_col('system', unique=False, nullable=False)  # откуда
    system = relationship('System', backref='set_sch_group_request')
    sampling_method = Column(db.String(80), unique=False, nullable=True)
    order = Column(db.Integer, unique=False, nullable=False)
    enabled = Column(db.Boolean, unique=False, nullable=False, server_default='false')

    __table_args__ = (
        UniqueConstraint('schedule_group_id', 'entity_id', 'system_id', name='_sch_group_entity_system_uc'),
        UniqueConstraint('schedule_group_id', 'order', name='_sch_group_order_uc'),
    )
Exemplo n.º 8
0
class EntityImage(Model):
    """Отпечаток содержимого сущностей удаленных систем"""

    __tablename__ = 'entity_image'

    root_entity_id = reference_col('entity', unique=False, nullable=False)
    root_entity = relationship('Entity', backref='set_entity_image_by_root',
                               foreign_keys=root_entity_id)
    entity_id = reference_col('entity', unique=False, nullable=False)
    entity = relationship('Entity', backref='set_entity_image',
                          foreign_keys=entity_id)
    parent_id = reference_col('entity_image', unique=False, nullable=True)
    parent = relationship('EntityImage')
    root_external_id = Column(db.String(80), unique=False, nullable=False)
    external_id = Column(db.String(80), unique=False, nullable=False, index=True)
    key = Column(db.String(80), unique=False, nullable=False, index=True, server_default='')
    content = Column(JSONB, unique=False, nullable=False)
    level = Column(db.Integer, unique=False, nullable=False)
    created = Column(db.DateTime, unique=False, nullable=False, server_default=text('now()'))
    modified = Column(db.DateTime, unique=False, nullable=False,
                      server_default=text('now()'), server_onupdate=text('now()'))
Exemplo n.º 9
0
class Role(Model):
    """A role for a user."""

    __tablename__ = 'roles'
    name = Column(db.String(80), unique=True, nullable=False)
    user_id = reference_col('users', nullable=True)
    user = relationship('User', backref='roles')

    def __init__(self, name, **kwargs):
        """Create instance."""
        db.Model.__init__(self, name=name, **kwargs)

    def __repr__(self):
        """Represent instance as a unique string."""
        return '<Role({name})>'.format(name=self.name)
Exemplo n.º 10
0
class Host(Model):
    """Хосты системы"""

    __tablename__ = 'host'

    code = Column(db.String(80), unique=False, nullable=False)
    system_id = reference_col('system', unique=False, nullable=False)
    system = relationship('System', backref='set_host')
    url = Column(db.String(80), unique=False, nullable=False)

    __table_args__ = (UniqueConstraint('code', 'system_id', name='_host_uc'), )

    @classmethod
    def get_url(cls, system_code):
        return cls.query.join(
            System,
            System.id == cls.system_id,
        ).filter(System.code == system_code, ).order_by(cls.code).value(
            cls.url)
Exemplo n.º 11
0
class Entity(Model):
    """Сущности удаленной и локальной систем"""

    __tablename__ = 'entity'

    code = Column(db.String(80), unique=False, nullable=False)
    system_id = reference_col('system', unique=False, nullable=False)
    system = relationship('System', backref='set_entity')

    __table_args__ = (UniqueConstraint('system_id',
                                       'code',
                                       name='_sys_entity_uc'), )

    @classmethod
    def get_id(cls, system_code, entity_code):
        entity_id = cls.query.join(System, System.id == cls.system_id).filter(
            cls.code == entity_code,
            System.code == system_code,
        ).value(cls.id)
        if not entity_id:
            raise InternalError('Entity not exist (%s, %s)' %
                                (system_code, entity_code))
        return entity_id
Exemplo n.º 12
0
class Schedule(Model):
    """
    Расписание запросов групп сущностей
    """
    # todo: зачем группы отвязаны от времени?

    __tablename__ = 'schedule'

    code = Column(db.String(80), unique=True, nullable=False)
    schedule_time_id = reference_col('schedule_time', unique=False, nullable=False)
    schedule_time = relationship('ScheduleTime', backref='set_schedule')
    schedule_group_id = reference_col('schedule_group', unique=False, nullable=False)
    schedule_group = relationship('ScheduleGroup', backref='set_sch_group_entity')

    __table_args__ = (
        UniqueConstraint('schedule_time_id', 'schedule_group_id', name='_sch_time_sch_group_uc'),
    )

    @classmethod
    def get_schedules_to_execute(cls):
        """Отбор расписаний, для которых есть подходящие по времени группы"""
        cur_datetime = datetime.today()
        cur_date = cur_datetime.date()
        cur_time = cur_datetime.time()
        last_execs_sq = db.session.query(SchGrReqExecute.begin_datetime).join(
            ScheduleGroupRequest, ScheduleGroupRequest.id == SchGrReqExecute.sch_group_request_id
        ).filter(
            ScheduleGroupRequest.schedule_group_id == ScheduleGroup.id,
        ).correlate(ScheduleGroup).order_by(
            SchGrReqExecute.id.desc()
        ).limit(1).subquery('sq')
        old_execs_q = db.session.query('1').filter(
            case([
                (
                    ScheduleTime.type == ScheduleTimeType.DELTA,
                    cur_datetime - last_execs_sq.c.begin_datetime > ScheduleTime.time
                ),
                (
                    ScheduleTime.type == ScheduleTimeType.TIME,
                    and_(
                        cur_time.isoformat() > ScheduleTime.time,
                        cur_date > cast(last_execs_sq.c.begin_datetime, db.Date)
                    )
                ),
            ]),
        ).correlate(ScheduleTime)
        one_exec_q = SchGrReqExecute.query.join(
            ScheduleGroupRequest, ScheduleGroupRequest.id == SchGrReqExecute.sch_group_request_id
        ).filter(
            ScheduleGroupRequest.schedule_group_id == ScheduleGroup.id
        ).correlate(ScheduleGroup).limit(1)
        res = cls.query.join(
            ScheduleTime, ScheduleTime.id == cls.schedule_time_id
        ).join(
            ScheduleGroup, ScheduleGroup.id == cls.schedule_group_id
        ).filter(
            or_(
                old_execs_q.exists() == True,
                one_exec_q.exists() == False,
            )
        ).all()
        return res

    @contextmanager
    def acquire_group_lock(self):
        lock_expire = 60*60*10  # 10h
        group_hexdigest = md5(self.schedule_group.code).hexdigest()
        lock_id = '{0}-lock-{1}'.format(self.__class__.__name__, group_hexdigest)

        def acquire_lock():
            # cache.add fails if the key already exists
            return cache.add(lock_id, 'true', lock_expire)

        def release_lock():
            cache.delete(lock_id)

        res = None
        try:
            res = acquire_lock()
            yield res
        finally:
            if res:
                release_lock()
Exemplo n.º 13
0
class MatchingId(Model):
    """Сопоставление ID частей сущности удаленной и локальной систем"""
    # todo: переработать методы, реиспользуя запросы

    __tablename__ = 'matching_id'

    local_entity_id = reference_col('entity', unique=False, nullable=False)
    local_entity = relationship('Entity',
                                backref='set_local_matching_id',
                                foreign_keys=local_entity_id)
    local_id_prefix = Column(db.String(80),
                             unique=False,
                             nullable=False,
                             index=False,
                             server_default='')
    local_id = Column(db.String(80), unique=False, nullable=False, index=True)
    local_param_name = Column(db.String(80), unique=False, nullable=True)

    remote_entity_id = reference_col('entity', unique=False, nullable=False)
    remote_entity = relationship('Entity',
                                 backref='set_remote_matching_id',
                                 foreign_keys=remote_entity_id)
    remote_id_prefix = Column(db.String(80),
                              unique=False,
                              nullable=False,
                              index=False,
                              server_default='')
    remote_id = Column(db.String(80), unique=False, nullable=False, index=True)
    remote_param_name = Column(db.String(80), unique=False, nullable=True)

    parent_id = reference_col('matching_id',
                              unique=False,
                              nullable=True,
                              ondelete='CASCADE')

    created = Column(db.DateTime,
                     unique=False,
                     nullable=False,
                     server_default=text('now()'))

    __table_args__ = (
        UniqueConstraint(
            'local_entity_id',
            'local_id',
            'local_id_prefix',
            'remote_entity_id',
            name='_local_entity_local_id_loc_id_pref_remote_entity_uc'),
        UniqueConstraint(
            'remote_entity_id',
            'remote_id',
            'remote_id_prefix',
            'local_entity_id',
            name='_remote_entity_remote_id_rem_id_pref_local_entity_uc'),
        CheckConstraint("local_param_name > '' OR remote_param_name > ''",
                        name='_param_name_chk'),
    )

    @classmethod
    def first_local_id(cls,
                       remote_entity_code,
                       remote_id,
                       local_entity_code,
                       remote_sys_code,
                       remote_id_prefix=None):
        dst_id = None
        dst_param_name = None
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                            cls.remote_id == str(remote_id),
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                        )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.first()
        if res:
            dst_id = res.local_id
            dst_param_name = res.local_param_name
        res = {
            'dst_id': dst_id,
            'dst_id_url_param_name': dst_param_name,
        }
        return res

    @classmethod
    def first_remote_id(cls,
                        src_entity_code,
                        src_id,
                        dst_entity_code,
                        remote_sys_code,
                        src_id_prefix=None):
        dst_id = None
        dst_param_name = None
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == src_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.local_id == str(src_id),
                            RemoteEntity.code == dst_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if src_id_prefix:
            res = res.filter(cls.local_id_prefix == (src_id_prefix or ''), )
        res = res.first()
        if res:
            dst_id = res.remote_id
            dst_param_name = res.remote_param_name
        res = {
            'dst_id': dst_id,
            'dst_id_url_param_name': dst_param_name,
        }
        return res

    @classmethod
    def shortest_first_remote_id(cls,
                                 src_entity_code,
                                 src_id,
                                 remote_sys_code,
                                 src_id_prefix=None):
        dst_id = None
        dst_param_name = None
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == src_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.local_id == str(src_id),
                            RemoteSystem.code == remote_sys_code,
                        )
        if src_id_prefix:
            res = res.filter(cls.local_id_prefix == (src_id_prefix or ''), )
        res = res.first()
        if res:
            dst_id = res.remote_id
            dst_param_name = res.remote_param_name
        res = {
            'dst_id': dst_id,
            'dst_id_url_param_name': dst_param_name,
        }
        return res

    @classmethod
    def first_remote_param_name(cls,
                                remote_entity_code,
                                remote_id,
                                remote_sys_code,
                                remote_id_prefix=None):
        dst_param_name = None
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            RemoteEntity, RemoteEntity.id == cls.remote_entity_id).join(
                RemoteSystem,
                RemoteSystem.id == RemoteEntity.system_id).filter(
                    cls.remote_id == str(remote_id),
                    RemoteEntity.code == remote_entity_code,
                    RemoteSystem.code == remote_sys_code,
                )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.first()
        if res:
            dst_param_name = res.remote_param_name
        res = {
            'dst_id_url_param_name': dst_param_name,
        }
        return res

    @classmethod
    def get_local_id(cls,
                     local_entity_code,
                     remote_entity_code,
                     remote_id,
                     remote_sys_code,
                     remote_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.one()
        return res.local_id

    @classmethod
    def find_local_id(cls,
                      local_entity_code,
                      remote_entity_code,
                      remote_id,
                      remote_sys_code,
                      remote_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.first()
        return res and res.local_id

    @classmethod
    def first_match_by_local_id(cls,
                                local_entity_code,
                                local_id,
                                remote_entity_code,
                                remote_sys_code,
                                local_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.local_id == str(local_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if local_id_prefix:
            res = res.filter(cls.local_id_prefix == (local_id_prefix or ''), )
        res = res.first()
        return res

    @classmethod
    def first_match_by_remote_id(cls,
                                 local_entity_code,
                                 remote_entity_code,
                                 remote_id,
                                 remote_sys_code,
                                 remote_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.first()
        return res

    @classmethod
    def find_all_matches_by_remote_id(cls,
                                      local_entity_code,
                                      remote_entity_code,
                                      remote_id,
                                      remote_sys_code,
                                      remote_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.all()
        return res

    @classmethod
    def get_by_remote_id(cls,
                         local_entity_code,
                         remote_entity_code,
                         remote_id,
                         remote_sys_code,
                         remote_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if remote_id_prefix:
            res = res.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        res = res.one()
        return res

    @classmethod
    def get_remote_id(cls,
                      local_entity_code,
                      local_id,
                      remote_entity_code,
                      remote_sys_code,
                      local_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.local_id == str(local_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if local_id_prefix:
            res = res.filter(cls.local_id_prefix == (local_id_prefix or ''), )
        res = res.one()
        return res.remote_id

    @classmethod
    def get_by_local_id(cls,
                        local_entity_code,
                        local_id,
                        remote_entity_code,
                        remote_sys_code,
                        local_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.local_id == str(local_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if local_id_prefix:
            res = res.filter(cls.local_id_prefix == (local_id_prefix or ''), )
        res = res.one()
        return res

    @classmethod
    def find_remote_id(cls,
                       local_entity_code,
                       local_id,
                       remote_entity_code,
                       remote_sys_code,
                       local_id_prefix=None):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.local_id == str(local_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        )
        if local_id_prefix:
            res = res.filter(cls.local_id_prefix == (local_id_prefix or ''), )
        res = res.first()
        return res and res.remote_id

    @classmethod
    def first_by_remote_id_without_prefix(cls, local_entity_code,
                                          remote_entity_code, remote_id,
                                          remote_sys_code):
        # специальный для обновления филиала врача в Туле
        # по хорошему филиал обновлять при синхронизации врачей (но долго)
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        ).first()
        return res

    @classmethod
    def get_remote_prefix_id(cls, local_entity_code, remote_entity_code,
                             remote_id, remote_sys_code):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        res = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            LocalEntity.code == local_entity_code,
                            LocalSystem.code == SystemCode.LOCAL,
                            cls.remote_id == str(remote_id),
                            RemoteEntity.code == remote_entity_code,
                            RemoteSystem.code == remote_sys_code,
                        ).one()
        return res.remote_id_prefix

    @classmethod
    def add(
        cls,
        local_entity_id=None,
        local_id_prefix=None,
        local_id=None,
        local_param_name=None,
        remote_entity_id=None,
        remote_id_prefix=None,
        remote_id=None,
        remote_param_name=None,
        matching_parent_id=None,
    ):
        cls.create(
            local_entity_id=local_entity_id,
            local_id_prefix=local_id_prefix or '',
            local_id=local_id,
            local_param_name=local_param_name,
            remote_entity_id=remote_entity_id,
            remote_id_prefix=remote_id_prefix or '',
            remote_id=remote_id,
            remote_param_name=remote_param_name,
            parent_id=matching_parent_id,
        )

    @classmethod
    def remove(
        cls,
        remote_sys_code=None,
        remote_entity_code=None,
        remote_id_prefix=None,
        remote_id=None,
        local_entity_code=None,
        local_id_prefix=None,
        local_id=None,
    ):
        LocalEntity = aliased(Entity, name='LocalEntity')
        LocalSystem = aliased(System, name='LocalSystem')
        RemoteEntity = aliased(Entity, name='RemoteEntity')
        RemoteSystem = aliased(System, name='RemoteSystem')
        req = cls.query.join(
            LocalEntity, LocalEntity.id == cls.local_entity_id).join(
                LocalSystem, LocalSystem.id == LocalEntity.system_id).join(
                    RemoteEntity,
                    RemoteEntity.id == cls.remote_entity_id).join(
                        RemoteSystem,
                        RemoteSystem.id == RemoteEntity.system_id).filter(
                            RemoteSystem.code == remote_sys_code,
                            RemoteEntity.code == remote_entity_code,
                            cls.remote_id == str(remote_id),
                            LocalSystem.code == SystemCode.LOCAL,
                            LocalEntity.code == local_entity_code,
                            cls.local_id == str(local_id),
                        )
        if remote_id_prefix is not None:
            req = req.filter(cls.remote_id_prefix == (remote_id_prefix
                                                      or ''), )
        if local_id_prefix is not None:
            req = req.filter(cls.local_id_prefix == (local_id_prefix or ''), )
        rec = req.one()
        rec.delete()