Python sqlalchemy.exc 模块，IntegrityError() 实例源码

def user_generate_new_token(callback, max_tries=32):
    failed = 0
    while failed < max_tries:
        try:
            token = random_string(User.TOKEN_LENGTH)
            token_owners = User.query.filter_by(token=token).count()
            if 0 != token_owners:
                failed += 1
                continue
            user = callback(token)
            db.session.commit()
            return user
        except IntegrityError:
            db.session.rollback()
            failed += 1
    raise Exception('Failed to obtain unique token within {} tries'.format(max_tries))

def users(count=100):
    fake = Faker()
    i = 0
    while i < count:
        u = User(email=fake.email(),
                 username=fake.user_name(),
                 password='password',
                 confirmed=True,
                 name=fake.name(),
                 location=fake.city(),
                 about_me=fake.text(),
                 member_since=fake.past_date())
        db.session.add(u)
        try:
            db.session.commit()
            i += 1
        except IntegrityError:
            db.session.rollback()

def build_app(configure_logging=True, with_keystone=True):
    app = flask.Flask(__name__)
    app.url_map.converters.update(converters.ALL)
    api.init_app(app)  # init_app spoils Api object if app is a blueprint
    app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False  # silence warning
    # TUNINGBOX_SETTINGS is the path to the file with tuning_box configuration
    app.config.from_envvar('TUNINGBOX_SETTINGS', silent=True)
    # These handlers work if PROPAGATE_EXCEPTIONS is on (Nailgun case)
    app.register_error_handler(sa_exc.IntegrityError, handle_integrity_error)
    app.register_error_handler(errors.TuningboxIntegrityError,
                               handle_integrity_error)
    app.register_error_handler(errors.TuningboxNotFound,
                               handle_object_not_found)
    app.register_error_handler(errors.RequestValidationError,
                               handle_request_validation_error)
    app.register_error_handler(errors.KeysOperationError,
                               handle_keys_operation_error)
    db.db.init_app(app)
    if configure_logging:
        log_level = app.config.get('LOG_LEVEL', 'DEBUG')
        logger.init_logger(app, log_level)
    if with_keystone:
        app.wsgi_app = keystone.KeystoneMiddleware(app)
    return app

def generate_fake(count=100):
        from sqlalchemy.exc import IntegrityError
        from random import seed
        import forgery_py

        seed()
        for i in range(count):
            u = User(email=forgery_py.internet.email_address(),
                     username=forgery_py.internet.user_name(True),
                     password=forgery_py.lorem_ipsum.word(),
                     confirmed=True,
                     name=forgery_py.name.full_name(),
                     location=forgery_py.address.city(),
                     about_me=forgery_py.lorem_ipsum.sentence(),
                     member_since=forgery_py.date.date(True))
            db.session.add(u)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()

def on_post(self, req, resp, *args, **kwargs):
        data = self.deserialize(req.context['doc'] if 'doc' in req.context else None)
        data, errors = self.clean(data)
        if errors:
            result = {'errors': errors}
            status_code = falcon.HTTP_BAD_REQUEST
            self.render_response(result, req, resp, status_code)
            return

        try:
            with self.session_scope(self.db_engine) as db_session:
                result = self.create(req, resp, data, db_session=db_session)
        except IntegrityError:
            raise HTTPConflict('Conflict', 'Unique constraint violated')
        except ProgrammingError as err:
            # Cases such as unallowed NULL value should have been checked before we got here (e.g. validate against
            # schema using falconjsonio) - therefore assume this is a UNIQUE constraint violation
            if len(err.orig.args) > 1 and err.orig.args[1] == self.VIOLATION_UNIQUE:
                raise HTTPConflict('Conflict', 'Unique constraint violated')
            raise
        status_code = falcon.HTTP_CREATED

        self.render_response(result, req, resp, status_code)

def on_put(self, req, resp, *args, **kwargs):
        status_code = falcon.HTTP_OK
        try:
            with self.session_scope(self.db_engine) as db_session:
                obj = self.get_object(req, resp, kwargs, for_update=True, db_session=db_session)

                data = self.deserialize(req.context['doc'] if 'doc' in req.context else None)
                data, errors = self.clean(data)
                if errors:
                    result = {'errors': errors}
                    status_code = falcon.HTTP_BAD_REQUEST
                else:
                    result = self.update(req, resp, data, obj, db_session)
        except (IntegrityError, ProgrammingError) as err:
            # Cases such as unallowed NULL value should have been checked before we got here (e.g. validate against
            # schema using falconjsonio) - therefore assume this is a UNIQUE constraint violation
            if isinstance(err, IntegrityError) or err.orig.args[1] == self.VIOLATION_FOREIGN_KEY:
                raise HTTPConflict('Conflict', 'Unique constraint violated')
            else:
                raise

        self.render_response(result, req, resp, status_code)

def load_data(p_session, p_data):
    """
    Insert the list of models instances in the current session then commit.

    :param p_session: Session object.
    :param p_data: List of models instances.
    :return: None
    """
    try:
        db_log.debug("Commiting")
        p_session.bulk_save_objects(p_data)
        p_session.commit()

    except IntegrityError as err:
        db_log.error(err)
        p_session.rollback()

def add_user():
    content = request.get_json(silent=True)
    user = User(**content.get('user'))
    try:
        db.query("INSERT INTO users " +
                 "(email, password) " +
                 "VALUES ('{0}', '{1}')".format(user.email,
                                               user.password))
    except IntegrityError as e:
        logger.info("User exists for {0}".format(user.email))
        return jsonify({"error": "Email {0} already has an user".format(user.email)})

    rows = db.query("SELECT seq FROM sqlite_sequence WHERE NAME = 'users'")
    user_id = rows.all()[0].get('seq', 0)

    created = db.query("SELECT * FROM users WHERE id = {0}".format(user_id))
    return jsonify(**created.all()[0])

def to_database(graph, connection=None, store_parts=True):
    """Stores a graph in a database.

    :param BELGraph graph: A BEL graph
    :param connection: An RFC-1738 database connection string, a pre-built :class:`Manager`, or `None`` for
                        default connection
    :type connection: None or str or pybel.manager.Manager
    :param bool store_parts: Should the graph be stored in the edge store?
    """
    manager = Manager.ensure(connection=connection)

    try:
        manager.insert_graph(graph, store_parts=store_parts)
    except IntegrityError:
        manager.session.rollback()
        log.warning('Error storing graph - other graph with same metadata'
                    ' already present. Consider incrementing the version')
    except OperationalError:
        manager.session.rollback()
        log.exception('Error storing graph - operational exception')
    except Exception as e:
        manager.session.rollback()
        raise e

def generate_fake(count=100):
        from sqlalchemy.exc import IntegrityError
        from random import seed
        import forgery_py

        seed()
        for i in range(count):
            u = User(email=forgery_py.internet.email_address(),
                     username=forgery_py.internet.user_name(True),
                     password=forgery_py.lorem_ipsum.word(),
                     confirmed=True,
                     name=forgery_py.name.full_name(),
                     location=forgery_py.address.city(),
                     about_me=forgery_py.lorem_ipsum.sentence(),
                     member_since=forgery_py.date.date(True))
            db.session.add(u)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()

def event_add():
    form = EventForm()
    form.confidence.choices = [(i, '%s' % i) for i in xrange(0, 100, 5)]
    if form.validate_on_submit():
        ev = Event(form.name.data,
                   form.details.data,
                   form.source.data,
                   form.tlp.data,
                   form.impact.data,
                   form.likelihood.data,
                   form.confidence.data)
        db.session.add(ev)
        try:
            db.session.commit()
            flash('Event added')
        except IntegrityError:
            db.session.rollback()
            flash('Commit Error')
        return redirect('/index')
    print(form.errors)
    return render_template('event_add.html',
                           title='Add Event',
                           form=form)

def indicator_pending():
    if request.method == 'POST':
        update_list = [int(i) for i in request.form.getlist('selected')]
        del_list = [int(i) for i in request.form.getlist('not_selected')]

        upd_query = db.session.query(Indicator).filter(Indicator.id.in_(update_list))
        upd_query.update({'pending':False}, synchronize_session=False)
        del_query = db.session.query(Indicator).filter(Indicator.id.in_(del_list))
        del_query.delete(synchronize_session=False)
        try:
            db.session.commit()
            flash('Indicators updated')
        except IntegrityError:
            db.session.rollback()
            flash('Commit Error')
            return redirect('/indicator/pending/view')

        ioc_query = Indicator.query.with_entities(Indicator.id, Indicator.event_id, Indicator.ioc)
        ioc_list = ioc_query.filter(Indicator.id.in_(update_list)).all()
        _correlate(ioc_list)
        return redirect('/indicator/pending/view')


    return render_template('indicator_pending.html', title='Pending Indicators')

def create_policy_pod(hostname, owner, token):
    def _create_pod():
        if db.session.query(Pod).filter_by(
                name=KUBERDOCK_POLICY_POD_NAME, owner=owner).first():
            return True

        try:
            policy_conf = get_policy_agent_config(MASTER_IP, token)
            check_internal_pod_data(policy_conf, owner)
            policy_pod = PodCollection(owner).add(policy_conf, skip_check=True)
            PodCollection(owner).update(policy_pod['id'],
                                        {'command': 'synchronous_start'})
            return True
        except (IntegrityError, APIError):
            # Either pod already exists or an error occurred during it's
            # creation - log and retry
            current_app.logger.exception(
                'During "{}" node creation tried to create a Network Policy '
                'service pod but got an error.'.format(hostname))

    return retry(_create_pod, 1, 5, exc=APIError('Could not create Network '
                                                 'Policy service POD'))

def generate_fake(count=100):
        from sqlalchemy.exc import IntegrityError
        from random import seed
        import forgery_py

        seed()
        for i in range(count):
                u = User(email=forgery_py.internet.email_address(),
                         username=forgery_py.internet.user_name(True),
                         password=forgery_py.lorem_ipsum.word(),
                         confirmed=True,
                         location=forgery_py.address.city(),
                         about_me=forgery_py.lorem_ipsum.sentence(),
                         member_since=forgery_py.date.date(True))
                db.session.add(u)
                try:
                    db.session.commit()
                except IntegrityError:
                    db.session.rollback()

def generate_fake(count=100):
        from sqlalchemy.exc import IntegrityError
        from random import seed
        import forgery_py

        seed()
        for i in range(count):
            u = User(email=forgery_py.internet.email_address(),
                    username=forgery_py.internet.user_name(True),
                    password=forgery_py.lorem_ipsum.word(),
                    confirmed=True,
                    name=forgery_py.name.full_name(),
                    location=forgery_py.address.city(),
                    about_me=forgery_py.lorem_ipsum.sentence(),
                    member_since=forgery_py.date.date(True))
            db.session.add(u)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()

def post(self):
        """
        Register a new domain name on this resolver.

        Returns a serialization which includes a JSON Web Token which can be used to authorize
        updates to this domain mapping in future.
        """
        args = new_domain_parser.parse_args()
        domain_name = "{}.{}".format(args.domain_name["label"], args.domain_name["zone"])
        try:
            domain = Domain.create(domain_name=domain_name,
                                   zone=args.domain_name["zone"],
                                   public=args.public)
        except exc.IntegrityError:
            return abort(422,
                         message={'domain_name': "Domain {} already exists".format(domain_name)})

        return domain

def generate_comments():
    cakeIds = [11, 21, 31, 41]
    stars = [2, 3, 4, 5]
    comments = [u'????', u'????', u'????', u'?????']
    for i in range(len(cakeIds)):
      comm = Comment(id=i+1, userId=1, cakeId=cakeIds[i], stars=stars[i], comment=comments[i])
      db.session.add(comm)
    try:
      db.session.commit()
      print 'generate comments successfully'
    except IntegrityError:
      db.session.rollback()
      print 'fail to generate comments'


####### class Picture --> table pictures

def update(timestamp,radar,distance,bearing,latitude,longitude):
        return_datasets=[]


        add_datasets = [(
                    VectorDataset(
                        radarcode = radar,
                        timestamp = timestamp,
            distance = distance,
            bearing = bearing,
            latitude = latitude,
            longitude = longitude
                    )
                )]
        session.add_all(add_datasets)
    try: 
            session.commit()
    except exc.IntegrityError:
        print  " Can not insert duplicate vector" 
    except: 
        print "Unexpected error:", sys.exc_info()[0]
        raise

    session.close()

def use_jti_claim(jti_claim):
    """
    Use a jti claim
    :param jti_claim: jti claim to mark as used.
    :raises ValueError: when jti_claim is None.
    :raises TypeError: when jti_claim is not a valid uuid4.
    :raises JtiTokenUsed: when jti_claim has already been used.
    """
    if jti_claim is None:
        raise ValueError
    if not _is_valid(jti_claim):
        logger.info('jti claim is invalid', jti_claim=jti_claim)
        raise TypeError

    try:
        jti = UsedJtiClaim(jti_claim)
        # pylint: disable=maybe-no-member
        db.session.add(jti)
        db.session.commit()
    except IntegrityError as e:
        logger.error('jti claim has already been used', jti_claim=jti_claim)
        raise JtiTokenUsed(jti_claim) from e

def patch_resource(self, obj):
        if self.has_change_permission(obj) and obj:
            obj, errors = self.schema().load(request.json, instance=obj, partial=True)
            if errors:
                db.session.rollback()
                return {'error': True, 'message': str(errors)}, 400

            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()
                raise SQLIntegrityError(data={}, message='Integrity Error', operation='Adding Resource',
                                        status=400)
            except OperationalError:
                db.session.rollback()
                raise SQlOperationalError(data={}, message='Operational Error', operation='Adding Resource',
                                          status=400)
            return {'success': True, 'message': 'obj updated successfully',
                    'data': self.schema(exclude=tuple(self.obj_exclude), only=tuple(self.obj_only))
                            .dump(obj).data}, 200

        return {'error': True, 'message': 'Forbidden Permission Denied To Change Resource'}, 403

def save_resource(self):
        data = request.json if isinstance(request.json, list) else [request.json]
        objects, errors = self.schema().load(data, session=db.session, many=True)
        if errors:
            db.session.rollback()
            return {'error': True, 'message': str(errors)}, 400

        if self.has_add_permission(objects):
            db.session.add_all(objects)
        else:
            db.session.rollback()
            return {'error': True, 'message': 'Forbidden Permission Denied To Add Resource'}, 403
        try:
            db.session.commit()
        except IntegrityError as e:
            db.session.rollback()
            print(e)
            raise SQLIntegrityError(data=data, message='Integrity Error', operation='Adding Resource', status=400)
        except OperationalError:
            db.session.rollback()
            raise SQlOperationalError(data=data, message='Operational Error', operation='Adding Resource', status=400)
        return {'success': True, 'message': 'Resource added successfully',
                'data': self.schema(exclude=tuple(self.obj_exclude), only=tuple(self.obj_only))
                    .dump(objects, many=True).data}, 201

def add_relation(self, data):
        obj, errors = self.schema().load(data, session=db.session)
        if errors:
            raise CustomException(data=data, message=str(errors), operation='adding relation')

        if self.has_add_permission(obj, data):
            db.session.add(obj)
            try:
                db.session.commit()
            except IntegrityError as e:
                raise SQLIntegrityError(data=data, message=str(e), operation='adding relation', status=400)
            except OperationalError as e:
                raise SQLIntegrityError(data=data, message=str(e), operation='adding relation', status=400)
        else:
            raise RequestNotAllowed(data=data, message='Object not Found', operation='adding relation',
                                    status=401)

def update_relation(self, data):
        obj = self.model.query.get(data['id'])
        if obj:
            obj, errors = self.schema().load(data, instance=obj)
            if errors:
                raise CustomException(data=data, message=str(errors), operation='updating relation')
            if self.has_change_permission(obj, data):
                raise CustomException(data=data, message='Permission Denied', operation='adding relation')
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()
                raise SQLIntegrityError(data=data, message='Integrity Error', operation='Adding Resource', status=400)
            except OperationalError:
                db.session.rollback()
                raise SQlOperationalError(data=data, message='Operational Error', operation='Adding Resource',
                                          status=400)
            else:
                raise RequestNotAllowed(data=data, message='Object not Found', operation='deleting relation',
                                        status=401)
        else:
            raise ResourceNotFound(data=data, message='Object not Found', operation='Updating relation', status=404)

def issue_key():
    # issue api token
    run = True
    while run:
        # issue key
        key = "".join(
            random.choices(
                string.ascii_letters +
                string.digits,
                k=32))
        g.user.api_key = key
        try:
            db.session.add(g.user)
            db.session.commit()
            run = False
        except IntegrityError:  # check for uniqueness
            continue
    return g.user.api_key

def post(self):
        """
        Create a task with data given in the request body. JSON format is
        expected. The model performs the validation automatically when
        instantiated.
        """
        try:
            data = request.json
            assignees = data['assignees']
            del data['assignees']
            persons = Person.query.filter(Person.id.in_(assignees)).all()

            instance = self.model(**data)
            instance.assignees = persons
            instance.save()

            return instance.serialize(), 201

        except TypeError as exception:
            current_app.logger.error(exception.message)
            return {"error": exception.message}, 400

        except IntegrityError as exception:
            current_app.logger.error(exception.message)
            return {"error": exception.message}, 400

def delete(self, instance_id):
        """
        Delete a model corresponding at given ID and return it as a JSON
        object.
        """
        instance = self.get_model_or_404(instance_id)

        try:
            self.check_delete_permissions(instance.serialize())
            instance.delete()

        except IntegrityError as exception:
            current_app.logger.error(str(exception))
            return {"error": str(exception)}, 400

        except permissions.PermissionDenied:
            abort(403)

        return {"deletion_success": True}, 204

def import_entry(self, data):
        entity = None
        parent_shotgun_ids = data["parent_shotgun_ids"]
        del data["parent_shotgun_ids"]

        try:
            entity = self.save_entity(data)
        except IntegrityError:
            current_app.logger.error(
                "Similar asset already exists "
                "or project is missing: %s" % data
            )

        if entity is not None:
            for parent_shotgun_id in parent_shotgun_ids:
                self.parent_map.setdefault(parent_shotgun_id, [])
                self.parent_map[parent_shotgun_id].append(Entity.get(entity.id))

        return entity

def import_row(self, row):
        first_name = row["First Name"]
        last_name = row["Last Name"]
        email = row["Email"]
        phone = row["Phone"]

        try:
            password = auth.encrypt_password("default")
            person = Person.create(
                email=email,
                password=password,
                first_name=first_name,
                last_name=last_name,
                phone=phone
            )
        except IntegrityError:
            person = Person.get_by(email=email)

        return person

def import_row(self, row):
        name = row["Name"]
        project_status_name = row["Status"]

        self.add_to_cache_if_absent(
            self.project_statuses,
            projects_service.get_or_create_status,
            project_status_name
        )
        project_status_id = self.get_id_from_cache(
            self.project_statuses,
            project_status_name
        )

        try:
            project = Project.create(
                name=name,
                project_status_id=project_status_id
            )
        except IntegrityError:
            project = Project.get_by(name=name)

        return project

def error_handler(func):
    from sqlalchemy.exc import IntegrityError
    import traceback,sys
    @functools.wraps(func)
    def inner(*args, **kwargs):
        try:
            return func(*args, **kwargs)
        except ValueError as exc:
            traceback.print_exc(file=sys.stdout)
            return jsonify({'error': 'Invalid input'}), 422, {'ContentType': 'application/json'}

        except IntegrityError as exc:
            traceback.print_exc(file=sys.stdout)
            return jsonify({'error': 'Already exists'}), 409, {'ContentType': 'application/json'}

    return inner

def get_or_create(db, model, create_method: str='',
                  create_method_kwargs=None, **kwargs):
    try:
        return db.query(model).filter_by(**kwargs).one()
    except NoResultFound:
        pass

    kwargs.update(create_method_kwargs or {})
    created = getattr(model, create_method, model)(**kwargs)
    try:
        db.add(created)
        db.flush()
        return created
    except IntegrityError:
        pass

    db.rollback()
    return db.query(model).filter_by(**kwargs).one()

def insert_element(self, element):
        s = db.session

        if isinstance(element, DBFeedItem):
            try:
                s.add(element)
                s.commit()
            except exc.IntegrityError, e:
                # we expect integrity errors. The reason is that we may try to regularly insert the same element into the database
                # those elements should not be added to the database and we just rollback the transaction and don't log anything
                s.rollback()
            except Exception, e:
                self._logger.error(str(e))
                s.rollback()
        elif isinstance(element, Feeditem):
            self.insert_element(DBFeedItem(element.content, element.type, element.source, element.time))
        else:
            self._logger.warning('An element could not be inserted into the database, because it is non of the accepted types. (' + type(element) + ')')

def generate_fake(count=100, **kwargs):
        """Generate a number of fake users for testing."""
        from sqlalchemy.exc import IntegrityError
        from random import seed, choice
        from faker import Faker

        fake = Faker()
        roles = Role.query.all()

        seed()
        for i in range(count):
            u = User(
                first_name=fake.first_name(),
                last_name=fake.last_name(),
                email=fake.email(),
                password=fake.password(),
                confirmed=True,
                role=choice(roles),
                **kwargs)
            db.session.add(u)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()

def grant_access_to_existing_repos(user):
    provider = GitHubRepositoryProvider(cache=True)
    owner_list = [o['name'] for o in provider.get_owners(user)]
    if owner_list:
        matching_repos = Repository.query.unrestricted_unsafe().filter(
            Repository.provider == RepositoryProvider.github,
            Repository.owner_name.in_(owner_list),
            ~Repository.id.in_(db.session.query(
                RepositoryAccess.repository_id,
            ).filter(
                RepositoryAccess.user_id == user.id,
            ))
        )
        for repo in matching_repos:
            if provider.has_access(auth.get_current_user(), repo):
                try:
                    with db.session.begin_nested():
                        db.session.add(RepositoryAccess(
                            repository_id=repo.id,
                            user_id=user.id,
                        ))
                        db.session.flush()
                except IntegrityError:
                    pass
            db.session.commit()

def post(self, build: Build):
        """
        Create a new job.
        """
        result = self.schema_from_request(job_schema, partial=True)
        if result.errors:
            return self.respond(result.errors, 403)
        data = result.data
        job = Job(build=build, repository_id=build.repository_id, **data)
        if job.status != Status.queued and not job.date_started:
            job.date_started = timezone.now()

        db.session.add(job)

        try:
            db.session.commit()
        except IntegrityError:
            db.session.rollback()
            return self.respond(status=422)

        aggregate_build_stats_for_job.delay(job_id=job.id)

        return self.respond_with_schema(job_schema, job)

def process(self, fp):
        results = self.get_coverage(fp)

        for result in results:
            try:
                with db.session.begin_nested():
                    db.session.add(result)
            except IntegrityError:
                lock_key = 'coverage:{build_id}:{file_hash}'.format(
                    build_id=result.build_id.hex,
                    file_hash=sha1(result.filename.encode(
                        'utf-8')).hexdigest(),
                )
                with redis.lock(lock_key):
                    result = self.merge_coverage(result)
                    db.session.add(result)
            db.session.flush()

        return results

def generate_fake(count=100) :
        from sqlalchemy.exc import IntegrityError 
        from random import seed
        import forgery_py

        seed()
        for i in range(count) :
            u = User(email=forgery_py.internet.email_address() ,
                      username=forgery_py.internet.user_name() ,
                      password=forgery_py.lorem_ipsum.word(),
                      confirmed=True,
                      name=forgery_py.name.full_name(),
                      location=forgery_py.address.city(),
                      about_me=forgery_py.lorem_lpsum.sentence(),
                      member_since=forgery_py.date.date(True))
            db.session.add(u)
            try : 
                db.session.commit()
            except IntegrityError :
                db.session.rollback()
    # ?????????

def generate_fake(count=100):
        from sqlalchemy.exc import IntegrityError
        from random import seed
        import forgery_py

        seed()
        for i in range(count):
            u = User(email=forgery_py.internet.email_address(),
                     username=forgery_py.internet.user_name(True),
                     password=forgery_py.lorem_ipsum.word(),
                     confirmed=True,
                     name=forgery_py.name.full_name(),
                     location=forgery_py.address.city(),
                     about_me=forgery_py.lorem_ipsum.sentence(),
                     member_since=forgery_py.date.date(True))
            db.session.add(u)
            try:
                db.session.commit()
            except IntegrityError:
                db.session.rollback()

def add_account(account, type, email, session=None):
    """ Add an account with the given account name and type.

    :param account: the name of the new account.
    :param type: the type of the new account.
    :param email: The Email address associated with the account.
    :param session: the database session in use.
    """
    new_account = models.Account(account=account, account_type=type, email=email,
                                 status=AccountStatus.ACTIVE)
    try:
        new_account.save(session=session)
    except IntegrityError:
        raise exception.Duplicate('Account ID \'%s\' already exists!' % account)
    # Create the account counters for this account
    rucio.core.account_counter.create_counters_for_new_account(account=account, session=session)

def add_scope(scope, account, session=None):
    """ add a scope for the given account name.

    :param scope: the name for the new scope.
    :param account: the account to add the scope to.
    :param session: The database session in use.
    """

    result = session.query(models.Account).filter_by(account=account, status=AccountStatus.ACTIVE).first()
    if result is None:
        raise AccountNotFound('Account ID \'%s\' does not exist' % account)

    new_scope = models.Scope(scope=scope, account=account, status=ScopeStatus.OPEN)
    try:
        new_scope.save(session=session)
    except IntegrityError, e:
        if match('.*IntegrityError.*ORA-00001: unique constraint.*SCOPES_PK.*violated.*', e.args[0]) \
           or match('.*IntegrityError.*1062, "Duplicate entry.*for key.*', e.args[0]) \
           or match('.*IntegrityError.*UNIQUE constraint failed: scopes.scope.*', e.args[0]) \
           or match('.*IntegrityError.*duplicate key value violates unique constraint.*', e.args[0])\
           or match('.*sqlite3.IntegrityError.*is not unique.*', e.args[0]):
            raise Duplicate('Scope \'%s\' already exists!' % scope)
    except:
        raise RucioException(str(format_exc()))

def list_subscription_rule_states(name=None, account=None, session=None):
    """Returns a list of with the number of rules per state for a subscription.

    :param name: Name of the subscription
    :param account: Account identifier
    :param session: The database session in use.
    :returns: List with tuple (account, name, state, count)
    """
    subscription = aliased(models.Subscription)
    rule = aliased(models.ReplicationRule)
    query = session.query(subscription.account, subscription.name, rule.state, func.count()).join(rule, subscription.id == rule.subscription_id)

    try:
        if name:
            query = query.filter(subscription.name == name)
        if account:
            query = query.filter(subscription.account == account)
    except IntegrityError as error:
        print(error)
        raise

    query = query.group_by(subscription.account, subscription.name, rule.state)

    for row in query:
        yield row

def add_key(key, key_type, value_type=None, value_regexp=None, session=None):
    """
    Adds a new allowed key.

    :param key: the name for the new key.
    :param key_type: the type of the key: all(container, dataset, file), collection(dataset or container), file, derived(compute from file for collection).
    :param value_type: the type of the value, if defined.
    :param value_regexp: the regular expression that values should match, if defined.
    :param session: The database session in use.
    """

    # Check if value_type is supported
    if value_type and value_type not in [str(t) for t in AUTHORIZED_VALUE_TYPES]:
        raise UnsupportedValueType('The type \'%(value_type)s\' is not supported for values!' % locals())

    new_key = models.DIDKey(key=key, value_type=value_type and str(value_type), value_regexp=value_regexp, key_type=key_type)
    try:
        new_key.save(session=session)
    except IntegrityError as error:
        if error.args[0] == "(IntegrityError) column key is not unique":
            raise Duplicate('key \'%(key)s\' already exists!' % locals())
        raise

def add_rse_attribute(rse, key, value, session=None):
    """ Adds a RSE attribute.

    :param rse: the rse name.
    :param key: the key name.
    :param value: the value name.
    :param issuer: The issuer account.
    :param session: The database session in use.

    :returns: True is successful
    """
    rse_id = get_rse_id(rse, session=session)
    try:
        new_rse_attr = models.RSEAttrAssociation(rse_id=rse_id, key=key, value=value)
        new_rse_attr = session.merge(new_rse_attr)
        new_rse_attr.save(session=session)
    except IntegrityError:
        raise exception.Duplicate("RSE attribute '%(key)s-%(value)s\' for RSE '%(rse)s' already exists!" % locals())
    return True

def delete_rse_transfer_limits(rse, activity=None, rse_id=None, session=None):
    """
    Delete RSE transfer limits.

    :param rse: The RSE name.
    :param activity: The activity.
    :param rse_id: The RSE id.
    """
    try:
        if not rse_id:
            rse_id = get_rse_id(rse=rse, session=session)

        query = session.query(models.RSETransferLimit).filter_by(rse_id=rse_id)
        if activity:
            query = query.filter_by(activity=activity)
        rowcount = query.delete()
        return rowcount
    except IntegrityError, e:
        raise exception.RucioException(e.args)

def add_naming_convention(scope, regexp, convention_type, session=None):
    """
    add a naming convention for a given scope

    :param scope: the name for the scope.
    :param regexp: the regular expression to validate the name.
    :param convention_type: the did_type on which the regexp should apply.
    :param session: The database session in use.
    """
    # validate the regular expression
    try:
        compile(regexp)
    except error:
        raise RucioException('Invalid regular expression %s!' % regexp)

    new_convention = models.NamingConvention(scope=scope,
                                             regexp=regexp,
                                             convention_type=convention_type)
    try:
        new_convention.save(session=session)
    except IntegrityError:
        raise Duplicate('Naming convention already exists!')
    except:
        raise RucioException(str(format_exc()))

def touch_transfer(external_host, transfer_id, session=None):
    """
    Update the timestamp of requests in a transfer. Fails silently if the transfer_id does not exist.

    :param request_host:   Name of the external host.
    :param transfer_id:    External transfer job id as a string.
    :param session:        Database session to use.
    """

    record_counter('core.request.touch_transfer')

    try:
        # don't touch it if it's already touched in 30 seconds
        session.query(models.Request).with_hint(models.Request, "INDEX(REQUESTS REQUESTS_EXTERNALID_UQ)", 'oracle')\
                                     .filter_by(external_id=transfer_id)\
                                     .filter(models.Request.state == RequestState.SUBMITTED)\
                                     .filter(models.Request.updated_at < datetime.datetime.utcnow() - datetime.timedelta(seconds=30))\
                                     .update({'updated_at': datetime.datetime.utcnow()}, synchronize_session=False)
    except IntegrityError, e:
        raise RucioException(e.args)

def __set_transfer_state(external_host, transfer_id, new_state, session=None):
    """
    Update the state of a transfer. Fails silently if the transfer_id does not exist.

    :param external_host:  Selected external host as string in format protocol://fqdn:port
    :param transfer_id:    External transfer job id as a string.
    :param new_state:      New state as string.
    :param session:        Database session to use.
    """

    record_counter('core.request.set_transfer_state')

    try:
        rowcount = session.query(models.Request).filter_by(external_id=transfer_id).update({'state': new_state, 'updated_at': datetime.datetime.utcnow()}, synchronize_session=False)
    except IntegrityError, e:
        raise RucioException(e.args)

    if not rowcount:
        raise UnsupportedOperation("Transfer %s on %s state %s cannot be updated." % (transfer_id, external_host, new_state))