Ejemplo n.º 1
0
def abort_dead_recipes(*args):
    filters = [not_(DistroTree.lab_controller_assocs.any())]
    if _virt_enabled():
        filters.append(and_(not_(Recipe.systems.any()),
                Recipe.virt_status != RecipeVirtStatus.possible))
    else:
        filters.append(not_(Recipe.systems.any()))
    recipes = MachineRecipe.query\
            .join(MachineRecipe.recipeset).join(RecipeSet.job)\
            .filter(Job.dirty_version == Job.clean_version)\
            .outerjoin(Recipe.distro_tree)\
            .filter(Recipe.status == TaskStatus.queued)\
            .filter(or_(*filters))
    if not recipes.count():
        return False
    log.debug("Entering abort_dead_recipes")
    for recipe_id, in recipes.values(MachineRecipe.id):
        session.begin()
        try:
            abort_dead_recipe(recipe_id)
            session.commit()
        except exceptions.Exception, e:
            log.exception('Error in abort_dead_recipe(%s)', recipe_id)
            session.rollback()
        finally:
Ejemplo n.º 2
0
        def _get_query_for_editors():
            """
            Returns a query that selects versions available to editors.
            """
            active_versions = Session.query(
                mappedClass.version,
                mappedClass.fk_status
            ).\
                filter(mappedClass.identifier == uid).\
                filter(or_(
                    mappedClass.fk_status == 2, mappedClass.fk_status == 3))

            own_filters = and_(
                mappedClass.identifier == uid,
                not_(mappedClass.fk_status == 2),
                not_(mappedClass.fk_status == 3),
                User.username == self.request.user.username)
            own_versions = Session.query(
                mappedClass.version,
                mappedClass.fk_status
            ).\
                join(Changeset).\
                join(User).\
                filter(*own_filters)
            return active_versions.union(own_versions)
Ejemplo n.º 3
0
    def deletePostsByTags(self, blacklist, whitelist):
        if not blacklist:
            return 0

        s = self.DBsession()

        subq = s.query(Post.id).distinct().join(Post.tags)
        subq = subq.filter(Tag.name.in_(whitelist))

        q = s.query(Post.id).distinct().join(Post.tags)
        q = q.filter(Tag.name.in_(blacklist)).except_(subq)

        d = s.query(Post).filter(Post.id.in_(q))
        post_count = d.delete(synchronize_session='fetch')

        # delete the image refs without posts from the database
        q = s.query(Post.image_id).distinct()
        d = s.query(Image.id).filter(not_(Image.id.in_(q)))
        img_count = d.delete(synchronize_session='fetch')

        q = s.query(Tag.id).join(Post.tags)
        d = s.query(Tag.id).filter(not_(Tag.id.in_(q)))
        tag_count = d.delete(synchronize_session='fetch')

        s.commit()
        return (post_count, img_count, tag_count)
Ejemplo n.º 4
0
 def constructQuery(self, context):
     session= Session()
     trusted=removeSecurityProxy(context)
     parliament_id = self._get_parliament_id(context)
     item_id = getattr(context, self.value_field, None)
     trusted = removeSecurityProxy(context)
     existing_item_ids = [assn.item_id for assn in trusted.values()]
     if item_id:
         query = session.query(domain.ParliamentaryItem).filter(
             domain.ParliamentaryItem.parliamentary_item_id ==
             item_id)
     else:
         query = session.query(domain.ParliamentaryItem).filter(
                 sql.and_(
                     sql.not_(domain.ParliamentaryItem.status.in_(
                             _assignable_state_ids
                         )
                     ),
                     sql.not_(
                         domain.ParliamentaryItem.parliamentary_item_id.in_(
                             existing_item_ids
                         )
                     ),
                     domain.ParliamentaryItem.parliament_id == parliament_id
                 )
             )
     return query
Ejemplo n.º 5
0
 def resume_work(self,context):
     """
     on resume setting value from task context
     """
     execution_context=context["execution_context"]
     WRK_LOGGER.debug("RESUMING WORKER for :"+self.worker )
     if execution_context:
         self.start_time=execution_context.get("start_time",datetime.utcnow())
         self.worker_ids=execution_context.get("worker_ids",[])
         self.sp_list=execution_context.get("sp_list",[])
         ##validate all the worker ids are taken care of
         ets = DBSession.query(EntityTasks).filter(EntityTasks.worker==self.worker).\
                                 filter(not_(EntityTasks.worker_id.in_(self.worker_ids))).all()
         if len(ets) > 0:
             xtra_work_ids = [et.worker_id for et in ets]
             WRK_LOGGER.error("GOT ENT Tasks different from execution_context :"+self.worker+\
             ": CONTEXT WORKERS : "+str(self.worker_ids) +": XTRA WORKERS :"+str(xtra_work_ids))
             r = DBSession.query(EntityTasks.entity_id).\
                     filter(EntityTasks.worker_id.in_(xtra_work_ids)).\
                     filter(EntityTasks.worker==self.worker).\
                     update(values=dict(worker_id=None,finished=True,end_time=datetime.utcnow()))
             transaction.commit()
             WRK_LOGGER.debug("Cleaned Up entity_tasks . worker:rows : "+self.worker+":"+str(r))
             
     WRK_LOGGER.debug("RESUMING WORKER for :"+self.worker+":"+str(self.start_time)+":"+str(self.worker_ids) )
     self.do_work()
 def _Not(self, node, fieldname):
     expressions = []
     self.in_not = True
     for subnode in node:
         expressions.append(self._eval(subnode, fieldname))
     self.in_not = False
     return not_(*expressions)
Ejemplo n.º 7
0
def user_view(user_id):
  user = User.query.get(user_id)
  tab = request.args.get("tab", "activity")

  e = Env(label=user.name, user=user)
  e.bread_crumbs = make_bread_crumbs()
  e.active_tab_id = tab
  e.tabs = make_tabs(user)

  if tab == "conversations":
    pass

  elif tab == "activity":
    # XXX quick & dirty
    e.activity_entries =\
      ActivityEntry.query.filter(ActivityEntry.actor_id == user.id).limit(30).all()

  elif tab == "audit":
    # XXX quick & dirty
    e.audit_entries =\
      AuditEntry.query.filter(AuditEntry.user_id == user.id).limit(30).all()

  elif tab in ("documents", "images"):
    files = File.query.filter(File.owner_id == user_id)
    if tab == "documents":
      files = files.filter(not_(File.mime_type.like("image/%")))
      e.documents = files.all()
    elif tab == "images":
      files = files.filter(File.mime_type.like("image/%"))
      e.images = files.all()

  return render_template("social/user.html", **e)
Ejemplo n.º 8
0
    def index(self):
        self.template = 'index.phtml'

        # top rated
        top_rated = Rating.top_rated(self.session, limit=6)
        self.view['top_rated'] = top_rated

        # last rated
        last_rated = self.view['last_rated']
        shuffle(last_rated)
        last_rated[5:] = []
        ids = map(itemgetter1, self.view['last_rated'])
        movies = self.session.query(Movie).filter(Movie.id.in_(ids)).all()
        movies = dict((m.id, m)
                      for m in movies)
        self.view['movies'] = movies

        # recent users
        self.view['recent_users'] = User.recent(self.session, limit=8)

        # recent ratings
        already_shown = set(ids)
        already_shown.update((m.id for m, _ in top_rated))
        recent = self.session.query(Rating)\
                     .options(joinedload(Rating.movie))\
                     .options(joinedload(Rating.user))\
                     .filter(not_(Rating.movie_id.in_(already_shown)))\
                     .order_by(Rating.rated.desc()).limit(15).all()
        shuffle(recent)
        recent[10:] = []
        self.view['recent_ratings'] = recent
Ejemplo n.º 9
0
 def roles(self, new_roles):
     db().query(Role).filter(not_(Role.name.in_(new_roles))).delete(synchronize_session="fetch")
     added_roles = self.roles
     for role in new_roles:
         if role not in added_roles:
             self.role_list.append(Role(name=role, release=self))
             added_roles.append(role)
Ejemplo n.º 10
0
 def constructQuery(self, context):
     session= Session()
     trusted=removeSecurityProxy(context)
     parliament_id = self._get_parliament_id(context)
     trusted = removeSecurityProxy(context)
     assigned_committee_ids = []
     if IGroupGroupItemAssignment.providedBy(trusted):
         committee_id = getattr(trusted, "group_id", None)
         if committee_id:
             query = session.query(domain.Committee).filter(
                 sql.and_(
                     domain.Committee.parent_group_id == parliament_id,
                     domain.Committee.group_id == committee_id
                 )
             )
             return query
         else:
             assigned_committee_ids = \
                 [ comm.group_id for comm in trusted.__parent__.values() ]
     else:
         assigned_committee_ids = \
             [ comm.group_id for comm in trusted.values() ]
     query = session.query(domain.Committee).filter(
         sql.and_(
             domain.Committee.status == 'active',
             domain.Committee.parent_group_id == parliament_id,
             sql.not_(
                 domain.Committee.group_id.in_(assigned_committee_ids)
             )
         )
     )
     return query
Ejemplo n.º 11
0
def get_available_venues( start, end, sitting=None ):
    """get all venues that are not booked for a sitting
    (but sitting if given)
    in the given time period 
    SQL:
    SELECT * 
    FROM venues 
    WHERE venues.venue_id NOT IN (SELECT sitting.venue_id 
        FROM sitting 
        WHERE (sitting.start_date BETWEEN '2000-01-01' AND '2000-01-02' 
            OR sitting.end_date BETWEEN '2000-01-01'  AND '2000-01-02'
            OR '2000-01-01'  BETWEEN sitting.start_date AND sitting.end_date 
            OR '2000-01-02'  BETWEEN sitting.start_date AND sitting.end_date) 
        AND sitting.venue_id IS NOT NULL)
    """
    session = Session()
    query = session.query(domain.Venue)
    b_filter = sql.and_(
        sql.or_( 
            sql.between(schema.sitting.c.start_date, start, end), 
            sql.between(schema.sitting.c.end_date, start, end),
            sql.between(start, schema.sitting.c.start_date, 
                schema.sitting.c.end_date),
            sql.between(end, schema.sitting.c.start_date, 
                schema.sitting.c.end_date)
        ),
        schema.sitting.c.venue_id != None)
    if sitting:
        if sitting.sitting_id:
            b_filter = sql.and_(b_filter,
                schema.sitting.c.sitting_id != sitting.sitting_id)
    query = query.filter(sql.not_(schema.venue.c.venue_id.in_(
                sql.select( [schema.sitting.c.venue_id] ).where(b_filter) )))
    venues = query.all()
    return venues
Ejemplo n.º 12
0
    def find(self, reservation_id=None, resource_id=None, slice_id=None, user_id=None, start_time=None, end_time=None):
        """
        Returns a list of reservation value objects (see class description).
        If all parameters a None, all reservations for this schedule_subject will be returned.
        If given parameters are not-None the result will be filtered by the respective field.
        If multiple params are given the result will be reduced (conditions will be AND-ed).
        If the times are given, all records which touch the given period will be returned.
        If {start_time} is given, but {end_time} is omitted, all records which span start_time will be returned.
        
        Limitations:
        - This method can not be used to filter records with NULL fields. E.g. it is not possible to filter all records to the ones which have set user_id to NULL.
        """
        q = db_session.query(ReservationRecord)
        q = q.filter_by(schedule_subject=self.schedule_subject)
        if not reservation_id is None:
            q = q.filter_by(reservation_id=reservation_id)
        if not resource_id is None:
            q = q.filter_by(resource_id=resource_id)
        if not slice_id is None:
            q = q.filter_by(slice_id=slice_id)
        if not user_id is None:
            q = q.filter_by(user_id=user_id)

        if (not start_time is None) and (end_time is None):
            end_time = start_time
        if (not start_time is None) and (not end_time is None):
            q = q.filter(not_(or_(ReservationRecord.end_time < start_time, ReservationRecord.start_time > end_time)))

        records = q.all()
        result = [self._convert_record_to_value_object(r) for r in records]
        db_session.expunge_all()
        return result
Ejemplo n.º 13
0
    def get_wall_events_query(self):
        user_is_admin_of_groups = [membership.group_id
                                   for membership in self.memberships
                                   if membership.membership_type == 'administrator']
        subjects = self.all_watched_subjects
        if self.is_teacher:
            subjects += self.taught_subjects
        from ututi.lib.wall import generic_events_query
        evts_generic = generic_events_query()

        t_evt = meta.metadata.tables['events']
        t_evt_comments = meta.metadata.tables['event_comments']
        t_wall_posts = meta.metadata.tables['wall_posts']
        t_content_items = meta.metadata.tables['content_items']
        subject_ids = [s.id for s in subjects]
        group_ids = [m.group.id for m in self.memberships]
        user_commented_evts_select = select([t_evt_comments.c.event_id],
                                            from_obj=[t_evt_comments.join(t_content_items,
                                                                          t_content_items.c.id == t_evt_comments.c.id)],)\
            .where(t_content_items.c.created_by == self.id)
        user_commented_evts = map(lambda r: r[0], meta.Session.execute(user_commented_evts_select).fetchall())

        query = evts_generic\
            .where(or_(or_(t_evt.c.object_id.in_(subject_ids),
                           t_wall_posts.c.subject_id.in_(subject_ids)) if subject_ids else False,  # subject wall posts
                       and_(or_(t_evt.c.author_id == self.id,  # location wall posts
                                # XXX User comments may grow to 1k-10k scale, consider a different implementation.
                                t_evt.c.id.in_(user_commented_evts) if user_commented_evts else False),
                            t_evt.c.event_type.in_(('subject_wall_post', 'location_wall_post'))),
                       or_(t_evt.c.object_id.in_(group_ids),) if group_ids else False))\
            .where(or_(t_evt.c.event_type != 'moderated_post_created',
                       t_evt.c.object_id.in_(user_is_admin_of_groups) if user_is_admin_of_groups else False))\
            .where(not_(t_evt.c.event_type.in_(self.ignored_events_list) if self.ignored_events_list else False))
        return query
Ejemplo n.º 14
0
def provision_scheduled_recipesets(*args):
    """
    if All recipes in a recipeSet are in Scheduled state then move them to
     Running.
    """
    recipesets = RecipeSet.query.join(RecipeSet.job)\
            .filter(and_(Job.dirty_version == Job.clean_version, Job.deleted == None))\
            .filter(not_(RecipeSet.recipes.any(
                Recipe.status != TaskStatus.scheduled)))
    if not recipesets.count():
        return False
    log.debug("Entering provision_scheduled_recipesets")
    for rs_id, in recipesets.values(RecipeSet.id):
        log.info("scheduled_recipesets: RS:%s" % rs_id)
        session.begin()
        try:
            provision_scheduled_recipeset(rs_id)
            session.commit()
        except exceptions.Exception:
            log.exception('Error in provision_scheduled_recipeset(%s)', rs_id)
            session.rollback()
        finally:
            session.close()
    log.debug("Exiting provision_scheduled_recipesets")
    return True
Ejemplo n.º 15
0
def managegroups(request):
    params = {'action': 'manage_groups', 'form': None}
    form, session = None, DBSession
    is_a_post = request.method == 'POST'
    adding = 'groupname' in request.POST
    deleting = request.params.get('group_action', '') == 'delete'
    settings = request.registry.settings
    default_groups_names = [a[0] 
                            for a in get_default_groups(settings)] 
    if is_a_post and deleting:
        items = [a[1]
                 for a in request.POST.items()
                 if a[0] == 'delete']

        todelete = session.query( AuthGroup).filter(
            se.and_(
                AuthGroup.id.in_(items),
                se.not_(AuthGroup.name.in_(default_groups_names))
            )).all()
        noecho = [session.delete(i) for i in todelete]
        request.session.flash(_('Groups %s have been deleted') % (
            ', '.join([a.name for a in todelete])), 'info')
    add_form = GroupForm(request.POST)
    if is_a_post and adding:
        if add_form.validate():
            try:
                group = add_form.save()
                add_form = GroupForm()
                flash(_('Added group : %s' % group.name, 'info'))
            except Exception, e:
                flash(_('Problem adding group : %s' % e, 'error'))
Ejemplo n.º 16
0
    def _fetch_db(result):
        LOGGER.debug('Open aggregate for item #%d: %r', item_id, result)
        # Si l'info se trouvait dans le cache,
        # on utilise cette valeur là.
        if result is not None:
            # La valeur 0 est utilisée à la place de None
            # dans le cache. On fait la conversion inverse ici.
            if not result:
                return None
            return result

        # Sinon on récupère l'information
        # depuis la base de données...
        state_ok = StateName.statename_to_value('OK')
        state_up = StateName.statename_to_value('UP')
        aggregate = database.run(
            DBSession.query(
                CorrEvent.idcorrevent
            ).join(
                (Event, CorrEvent.idcause == Event.idevent)
            ).filter(
                # Ici, on ne prend pas en compte l'état d'acquittement :
                # on n'agrège jamais une alerte dans un agrégat OK/UP
                # (voir le ticket #1027 pour plus d'information).
                not_(Event.current_state.in_([state_ok, state_up]))
            ).filter(Event.idsupitem == item_id
            ).scalar)

        # ...et on met à jour le cache avant de retourner l'ID.
        # NB: la valeur 0 est utilisée à la place de None pour que
        # le cache puisse réellement servir à l'appel suivant.
        ctx.setShared('open_aggr:%d' % item_id, aggregate or 0)
        return aggregate
Ejemplo n.º 17
0
 def get_unsolved_challenges(self):
     """
     Return a query that produces a list of all unsolved challenges for a
     given team.
     """
     team_solved_subquery = get_team_solved_subquery(self.id)
     online = get_online_challenges()
     return online.filter(not_(team_solved_subquery))
Ejemplo n.º 18
0
    def search_moc(self, facility_id, public, moc_id=None, state=None,
                   with_description=False, order_by_date=False):
        """
        Search for MOC records for a facility.
        """

        select_cols = [
            moc.c.id,
            moc.c.facility_id,
            moc.c.name,
            moc.c.public,
            moc.c.uploaded,
            moc.c.num_cells,
            moc.c.area,
            moc.c.state,
        ]

        if with_description:
            select_cols.append(moc.c.description)
            select_cols.append(moc.c.description_format)
            default = {}
        else:
            default = {'description': None, 'description_format': None}

        stmt = select(select_cols)

        if facility_id is not None:
            stmt = stmt.where(moc.c.facility_id == facility_id)

        if public is not None:
            if public:
                stmt = stmt.where(moc.c.public)
            else:
                stmt = stmt.where(not_(moc.c.public))

        if moc_id is not None:
            stmt = stmt.where(moc.c.id == moc_id)

        if state is not None:
            if is_list_like(state):
                stmt = stmt.where(moc.c.state.in_(state))
            else:
                stmt = stmt.where(moc.c.state == state)

        if order_by_date:
            stmt = stmt.order_by(moc.c.uploaded.desc())
        else:
            stmt = stmt.order_by(moc.c.id.asc())

        ans = ResultCollection()

        with self._transaction() as conn:
            for row in conn.execute(stmt):
                values = default.copy()
                values.update(**row)
                ans[row['id']] = MOCInfo(**values)

        return ans
Ejemplo n.º 19
0
def check_field_association(session, tid, field_dict):
    if field_dict.get('fieldgroup_id', '') and session.query(models.Field).filter(models.Field.id == field_dict['fieldgroup_id'],
                                                                                  models.Field.tid != tid).count():
        raise errors.InputValidationError()

    if field_dict.get('template_id', '') and session.query(models.Field).filter(models.Field.id == field_dict['template_id'],
                                                                                not_(models.Field.tid.in_(set([1, tid])))).count():
        raise errors.InputValidationError()

    if field_dict.get('step_id', '') and session.query(models.Field).filter(models.Step.id == field_dict['step_id'],
                                                                            models.Questionnaire.id == models.Step.questionnaire_id,
                                                                            not_(models.Questionnaire.tid.in_(set([1, tid])))).count():
        raise errors.InputValidationError()

    if field_dict.get('fieldgroup_id', ''):
        ancestors = set(fieldtree_ancestors(session, field_dict['fieldgroup_id']))
        if field_dict['id'] == field_dict['fieldgroup_id'] or field_dict['id'] in ancestors:
            raise errors.InputValidationError("Provided field association would cause recursion loop")
Ejemplo n.º 20
0
 def _op1(self, values, op, elem):
     elem = self._walk(values, elem)
     if op == "+":
         return + elem
     elif op == "-":
         return - elem
     elif op == "not":
         return expr.not_(elem)
     assert False, "should not happen"
Ejemplo n.º 21
0
def db_update_fieldoptions(session, field_id, options, language):
    options_ids = [db_update_fieldoption(session, field_id, option['id'], option, language, idx) for idx, option in enumerate(options)]

    if not options_ids:
        return

    to_remove = session.query(models.FieldOption.id).filter(models.FieldOption.field_id == field_id,
                                                            not_(models.FieldOption.id.in_(options_ids)))

    session.query(models.FieldOption).filter(models.FieldOption.id.in_(to_remove.subquery())).delete(synchronize_session='fetch')
Ejemplo n.º 22
0
 def check_vm_name(self, vm_name, vm_id):
     from convirt.model import DBSession
     from convirt.model.VM import VM
     query = DBSession.query(VM)
     if vm_id:
         query = query.filter(not_(VM.id==vm_id))
     vm = query.filter(VM.name==vm_name).first()
     if vm :
         return dict(success=False,msg='VM <b>'+vm_name+'</b> already exists.')
     return dict(success=True,msg='')
Ejemplo n.º 23
0
def db_update_fieldattrs(session, field_id, field_attrs, language):
    attrs_ids = [db_update_fieldattr(session, field_id, attr_name, attr, language) for attr_name, attr in field_attrs.items()]

    if not attrs_ids:
        return

    to_remove = session.query(models.FieldAttr.id).filter(models.FieldAttr.field_id == field_id,
                                                                  not_(models.FieldAttr.id.in_(attrs_ids)))

    session.query(models.FieldAttr).filter(models.FieldAttr.id.in_(to_remove.subquery())).delete(synchronize_session='fetch')
Ejemplo n.º 24
0
Archivo: DAO.py Proyecto: joubu/CDL
 def downloads(cls):
     try:
         from modele import Download, Config
         url_blacklist = [e.url for e in 
                 session.query(Config).first().blacklist]
                     
         return session.query(Download).filter(
                 not_(Download.url.in_(url_blacklist))).all()
     except Exception, e:
         return None
Ejemplo n.º 25
0
    def list(self, status = ['active'], limit_offset = None, starts_with = None, search = None):

        db = get_session()

        # Make a list from string
        if not isinstance(status, (list, tuple)):
            status = [status]


        q = db.query(Movie) \
            .join(Movie.library, Library.titles) \
            .options(joinedload_all('releases.status')) \
            .options(joinedload_all('releases.quality')) \
            .options(joinedload_all('releases.files')) \
            .options(joinedload_all('releases.info')) \
            .options(joinedload_all('library.titles')) \
            .options(joinedload_all('library.files')) \
            .options(joinedload_all('status')) \
            .options(joinedload_all('files')) \
            .filter(LibraryTitle.default == True) \
            .filter(or_(*[Movie.status.has(identifier = s) for s in status]))

        filter_or = []
        if starts_with:
            starts_with = toUnicode(starts_with.lower())
            if starts_with in ascii_lowercase:
                filter_or.append(LibraryTitle.simple_title.startswith(starts_with))
            else:
                ignore = []
                for letter in ascii_lowercase:
                    ignore.append(LibraryTitle.simple_title.startswith(toUnicode(letter)))
                filter_or.append(not_(or_(*ignore)))

        if search:
            filter_or.append(LibraryTitle.simple_title.like('%%' + search + '%%'))

        if filter_or:
            q = q.filter(or_(*filter_or))

        q = q.order_by(asc(LibraryTitle.simple_title))

        if limit_offset:
            splt = limit_offset.split(',')
            limit = splt[0]
            offset = 0 if len(splt) is 1 else splt[1]
            q = q.limit(limit).offset(offset)

        results = q.all()

        movies = []
        for movie in results:
            temp = movie.to_dict(self.default_dict)
            movies.append(temp)

        return movies
Ejemplo n.º 26
0
 def constructQuery(self, context):
     session= Session()
     trusted=removeSecurityProxy(context)
     parliament_id = self._get_parliament_id(trusted)
     mp_user_ids = sql.select([schema.user_group_memberships.c.user_id], 
         schema.user_group_memberships.c.group_id == parliament_id)
     query = session.query(domain.User).filter(sql.and_(
         sql.not_(domain.User.user_id.in_(mp_user_ids)),
         domain.User.active_p == 'A')).order_by(
             domain.User.last_name, domain.User.first_name)
     return query
Ejemplo n.º 27
0
    def __init__(self, course_hedu_id, bra_id):
        Major.__init__(self, course_hedu_id, bra_id)
        self.course_hedu_id = course_hedu_id

        self.max_year_query = db.session.query(
            func.max(Ybc_hedu.year)).filter_by(course_hedu_id=course_hedu_id)

        if bra_id == '':
            self.hedu_query = Ybc_hedu.query.filter(
                Ybc_hedu.course_hedu_id == self.course_hedu_id,
                Ybc_hedu.year == self.max_year_query,
                not_(Ybc_hedu.bra_id.like('0xx%')),
                func.length(Ybc_hedu.bra_id) == 9)
        else:
            self.hedu_query = Ybc_hedu.query.filter(
                Ybc_hedu.course_hedu_id == self.course_hedu_id,
                Ybc_hedu.year == self.max_year_query,
                Ybc_hedu.bra_id.like(self.bra_id+'%'),
                not_(Ybc_hedu.bra_id.like('0xx%')),
                func.length(Ybc_hedu.bra_id) == 9)
Ejemplo n.º 28
0
def test_not_clause():
    raw_clause = {
        'op': 'not',
        'clause': {
                'col': 'project_name',
                'op': 'eq',
                'other': 'quijibo',
        },
    }
    expected_clause = not_(proj.c.project_name == 'quijibo')
    clause_eq(raw_clause, expected_clause)
    def _makeQueryFilter(self):
        # Query filter has already been generated.
        if self._queryFilter is not None:
            return self._queryFilter
        
        # There's no filter to apply.
        if self._sqlFilterConditionListDict is None:
            return None
        
        queryFilterList = []
        
        for key, sqlFilterConditionList in self._sqlFilterConditionListDict.items():
            variableName, negate = key
            variable = getattr(SQLModsecurityAuditEntryMessage, variableName)

            # If it's a negation, we create one 'NOT IN' query filter for all values.
            if negate:
                with closing(self._sessionMaker()) as session:
                    # Now we store the filter and it's variables in the database before using them through sub queries.
                    sqlFilterObject = SQLFilter(conditionList = sqlFilterConditionList)
                    session.add(sqlFilterObject)
                    session.commit()
    
                    # Crawling group items.
                    
                    # @hack: why is this line necessary ?
                    sqlFilterObject = session.query(SQLFilter).filter(SQLFilter.id == sqlFilterObject.id).one()
                    
                    # Making a 'NOT IN' filter with not null values.
                    sqlFilterVariableValueIterable = session.query(SQLFilterCondition.variableValue)\
                                                            .with_parent(sqlFilterObject)\
                                                            .filter(SQLFilterCondition.variableValue != None)
                    queryFilter = not_(variable.in_(sqlFilterVariableValueIterable))
                    
                    # @hack: managing NULL values as they are ignored by 'IN' and 'NOT IN'.
                    # Making a 'IS NOT NULL' filter if there's a null value.
                    if session.query(SQLFilterCondition.variableValue)\
                              .with_parent(sqlFilterObject)\
                              .filter(SQLFilterCondition.variableValue == None)\
                              .count() > 0:
                        queryFilter = and_(queryFilter, variable != None)
                    else:
                        queryFilter = or_(queryFilter, variable == None)
                    queryFilterList.append(queryFilter)
                    

            # We create a filter for each value if it's an equality condition.
            else:
                for sqlFilterCondition in sqlFilterConditionList:
                    queryFilterList.append(variable == sqlFilterCondition.variableValue)

        self._queryFilter = and_(*queryFilterList)
        return self._queryFilter
Ejemplo n.º 30
0
 def index(self,**kw):
     self.col_call = 0
     self.max_cols = 0
     self.job_ids = []
     matrix_options = {}
     matrix_options['whiteboard_options'] = self.get_whiteboard_options(kw.get('filter'), selected=kw.get('whiteboard'))
     if ('job_ids' in kw or 'whiteboard' in kw):
         job_ids = []
         if 'job_ids' in kw:
             job_ids = [int(j) for j in kw['job_ids'].split()]
             # Filter out ids of deleted jobs
             query = model.Job.query.filter(not_(model.Job.is_deleted)).filter(model.Job.id.in_(job_ids))
             job_ids = [job_id for job_id, in query.values(model.Job.id)]
         # Build the result grid
         gen_results = self.generate(whiteboard=kw.get('whiteboard'),
             job_ids=job_ids, toggle_nacks=kw.get('toggle_nacks_on'))
         matrix_options['grid'] = gen_results['grid']
         matrix_options['list'] = gen_results['data']
         if 'whiteboard' in kw: # Getting results by whiteboard
             jobs = model.Job.by_whiteboard(kw.get('whiteboard')).filter(not_(model.Job.is_deleted))
             job_count = jobs.count()
             if job_count > model.Job.max_by_whiteboard:
                 flash(_('Your whiteboard contains %s jobs, only %s will be used' % (job_count, model.Job.max_by_whiteboard)))
             jobs = jobs.limit(model.Job.max_by_whiteboard)
             job_ids = [str(j.id) for j in jobs]
             self.job_ids = job_ids
             matrix_options['job_ids_vals'] = "\n".join([str(j) for j in job_ids])
         elif job_ids: #Getting results by job id
             self.job_ids = job_ids
             matrix_options['job_ids_vals'] = '\n'.join([str(j) for j in job_ids])
         if 'toggle_nacks_on' in kw:
             matrix_options['toggle_nacks_on'] = True
         else:
             matrix_options['toggle_nacks_on'] = False
     else:
         matrix_options['toggle_nacks_on'] = False
         matrix_options['grid'] = None
    
     return dict(widget=self.job_matrix_widget, widget_options=matrix_options,
             title="Job Matrix Report", value=None, widget_attrs={})