Пример #1
0
def get_leaderboard():
    claims = get_jwt_claims()

    subquery = (
        db.session.query(
            ValidatedAudio.created_by,
            func.count(ValidatedAudio.file_name).label('cnt')
        )
        .group_by(ValidatedAudio.created_by)
    ).subquery()

    user_ranks = (
        db.session.query(
            subquery,
            func.rank().over(order_by=desc('cnt')).label('user_rank')
        )
    ).all()

    user_ranks = [r._asdict() for r in user_ranks]

    for r in user_ranks:
        if r['created_by'] != claims['user']:
            r['created_by'] = abs(hash(r['created_by'])) % (10 ** 8)

    return jsonify(user_ranks)
Пример #2
0
    def __get_result(model, flight_field, **kw):
        subq = DBSession \
            .query(getattr(Flight, flight_field),
                   func.count('*').label('count'),
                   func.sum(Flight.index_score).label('total')) \
            .group_by(getattr(Flight, flight_field)) \
            .outerjoin(Flight.model)

        if 'year' in kw:
            try:
                year = int(kw['year'])
            except:
                raise HTTPBadRequest

            year_start = date(year, 1, 1)
            year_end = date(year, 12, 31)
            subq = subq.filter(Flight.date_local >= year_start) \
                       .filter(Flight.date_local <= year_end)

        subq = subq.subquery()

        result = DBSession \
            .query(model, subq.c.count, subq.c.total,
                   over(func.rank(), order_by=desc('total')).label('rank')) \
            .join((subq, getattr(subq.c, flight_field) == model.id))

        result = result.order_by(desc('total'))
        return result
Пример #3
0
    def get_comments(cls, ref, offset=0, limit=20, max_replies=3):
        comments = OrderedDict()

        q = Session.query(cls).filter(comments_table.c.ref == ref).filter(
            comments_table.c.thread_id.is_(None)).order_by(
                comments_table.c.created_at.desc()).offset(offset).limit(limit)

        for comment in q.all():
            comments[comment.id] = {
                'comment': comment,
                'replies': [],
            }

        if comments and max_replies:
            sub = Session.query(
                cls,
                func.rank().over(
                    order_by=comments_table.c.created_at.asc(),
                    partition_by=comments_table.c.thread_id,
                ).label('rank')).filter(
                    comments_table.c.thread_id.in_(comments.keys())).filter(
                        comments_table.c.thread_id == comments_table.c.reply_to
                    ).filter(comments_table.c.ref == ref).subquery()

            q = Session.query(cls).select_entity_from(sub).filter(
                sub.c.rank <= max_replies)
            q = q.offset(offset).limit(limit)

            for reply in q.all():
                replies = comments[reply.thread_id]['replies']
                replies.append(reply)

        return [comment for _, comment in comments.iteritems()]
Пример #4
0
    def get(self, project_uuid):
        """Get the most recent build for each environment of a project.

        Only environments for which builds have already been requested
        are considered.  Meaning that environments that are part of a
        project but have never been built won't be part of results.

        """

        # Filter by project uuid. Use a window function to get the most
        # recently requested build for each environment return.
        rank = (func.rank().over(
            partition_by="environment_uuid",
            order_by=desc("requested_time")).label("rank"))
        query = db.session.query(models.EnvironmentBuild)
        query = query.filter_by(project_uuid=project_uuid)
        query = query.add_column(rank)
        # Note: this works because rank is of type Label and rank == 1
        # will evaluate to sqlalchemy.sql.elements.BinaryExpression
        # since the equality operator is overloaded.
        query = query.from_self().filter(rank == 1)
        query = query.with_entities(models.EnvironmentBuild)
        env_builds = query.all()

        return {
            "environment_builds": [build.as_dict() for build in env_builds]
        }
Пример #5
0
def get_standings(session, cup, page, page_size):
    query = session.query(Jumper,
            func.count(FinalStanding.id).label('participations'),
            func.min(FinalStanding.rank).label('top_rank'),
            func.max(FinalStanding.points).label('top_points'),
            func.sum(FinalStanding.i).label('i'),
            func.sum(FinalStanding.ii).label('ii'),
            func.sum(FinalStanding.iii).label('iii'),
            func.sum(FinalStanding.n).label('hill_participations'),
            func.sum(FinalStanding.points).label('total_jump_points'),
            func.sum(FinalStanding.cup_points).label('total_cup_points'),
            func.rank().over(order_by=desc(func.sum(FinalStanding.points)) if cup.rank_method == 'JumpPoints' else (desc(func.sum(FinalStanding.cup_points)), desc(func.sum(FinalStanding.points)))).label('rank')) \
                    .join(FinalStanding) \
                    .join(Tournament) \
                    .join(CupDate) \
                    .filter(CupDate.cup_id == cup.id) \
                    .group_by(Jumper) \
                    .order_by('rank')

    total_count = query.count()
    data = query \
            .offset((page - 1) * page_size) \
            .limit(page_size) \
            .all()

    return data, Pagination(page, page_size, total_count)
Пример #6
0
def _get_result(model, flight_field, year=None):
    subq = db.session \
        .query(getattr(Flight, flight_field),
               func.count('*').label('count'),
               func.sum(Flight.index_score).label('total')) \
        .group_by(getattr(Flight, flight_field)) \
        .outerjoin(Flight.model)

    if isinstance(year, int):
        year_start = date(year, 1, 1)
        year_end = date(year, 12, 31)
        subq = subq.filter(Flight.date_local >= year_start) \
                   .filter(Flight.date_local <= year_end)

    subq = subq.subquery()

    result = db.session \
        .query(model, subq.c.count, subq.c.total,
               over(func.rank(), order_by=desc('total')).label('rank')) \
        .join((subq, getattr(subq.c, flight_field) == model.id))

    if model == User:
        result = result.options(subqueryload(model.club))

    result = result.order_by(desc('total'))
    return result
Пример #7
0
def _get_result(model, flight_field, year=None):
    subq = (
        db.session.query(
            getattr(Flight, flight_field),
            func.count("*").label("count"),
            func.sum(Flight.index_score).label("total"),
        )
        .group_by(getattr(Flight, flight_field))
        .outerjoin(Flight.model)
        .filter(Flight.is_rankable())
    )

    if isinstance(year, int):
        year_start = date(year, 1, 1)
        year_end = date(year, 12, 31)
        subq = subq.filter(Flight.date_local >= year_start).filter(
            Flight.date_local <= year_end
        )

    subq = subq.subquery()

    result = db.session.query(
        model,
        subq.c.count,
        subq.c.total,
        over(func.rank(), order_by=desc("total")).label("rank"),
    ).join((subq, getattr(subq.c, flight_field) == model.id))

    if model == User:
        result = result.outerjoin(model.club)
        result = result.options(eagerload(model.club))

    return result
Пример #8
0
def _get_result_Flight(table, table_column, year=None):
    subq = (
        db.session.query(
            getattr(Flight, table_column),
            func.count("*").label("count"),
            func.sum(Flight.index_score).label("total"),
        ).group_by(getattr(Flight, table_column)).outerjoin(
            Flight.model)  #glider model
        .filter(Flight.is_rankable()))

    if isinstance(year, int):
        year_start = date(year, 1, 1)
        year_end = date(year, 12, 31)
        subq = subq.filter(Flight.date_local >= year_start).filter(
            Flight.date_local <= year_end)

    subq = subq.subquery()

    result = db.session.query(
        table,
        subq.c.count,
        subq.c.total,
        over(func.rank(), order_by=desc("total")).label("rank"),
    ).join((subq, getattr(subq.c, table_column) == table.id))

    if table == User:
        result = result.outerjoin(table.club)
        result = result.options(eagerload(table.club))

    return result
Пример #9
0
def _get_result_Flight_User_byClub(year=None):
    '''return the number of users in flights that are recorded under the club id.
    Rankable flight means public flight.
    Sort by number of flights this year'''

    query = db.session.query( \
        Flight.club_id, \
        func.count((Flight.pilot_id.distinct())).label('users_count') \
        , func.count(Flight.id).label('flights_count')) \
        .group_by(Flight.club_id)

    if isinstance(year,
                  int):  # if year is None, then get flights for all seasons
        year_start = date(year, 1, 1)
        year_end = date(year, 12, 31)
        query = query.filter(Flight.date_local >= year_start)\
            .filter(Flight.date_local <= year_end)

    subq = query.subquery()

    result = db.session.query(
        Club,
        subq.c.flights_count,
        subq.c.users_count,
        over(func.rank(), order_by=desc("flights_count")).label("rank"),
    ).join((subq, getattr(subq.c, "club_id") == Club.id))

    return result
Пример #10
0
    def __get_result(model, flight_field, **kw):
        subq = (
            DBSession.query(
                getattr(Flight, flight_field),
                func.count("*").label("count"),
                func.sum(Flight.index_score).label("total"),
            )
            .group_by(getattr(Flight, flight_field))
            .outerjoin(Flight.model)
        )

        if "year" in kw:
            try:
                year = int(kw["year"])
            except:
                raise HTTPBadRequest

            year_start = date(year, 1, 1)
            year_end = date(year, 12, 31)
            subq = subq.filter(Flight.date_local >= year_start).filter(Flight.date_local <= year_end)

        subq = subq.subquery()

        result = DBSession.query(
            model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank")
        ).join((subq, getattr(subq.c, flight_field) == model.id))

        result = result.order_by(desc("total"))
        return result
Пример #11
0
 def test_funcfilter_windowing_orderby(self):
     # test filtered windowing:
     self.assert_compile(
         select([
             func.rank().filter(table1.c.name > 'foo').over(
                 order_by=table1.c.name)
         ]), "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable")
Пример #12
0
 def test_funcfilter_windowing_orderby_partitionby(self):
     self.assert_compile(
         select([
             func.rank().filter(table1.c.name > 'foo').over(
                 order_by=table1.c.name, partition_by=['description'])
         ]), "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (PARTITION BY mytable.description ORDER BY mytable.name) "
         "AS anon_1 FROM mytable")
Пример #13
0
    def get_rank(cls, id):
        subquery = db_session.query(
            Squads.id,
            func.rank().over(
                order_by=Squads.total_points.desc()).label('rank')).subquery()

        return db_session.query(subquery).filter(
            subquery.c.id == id).first().rank
Пример #14
0
 def test_funcfilter_windowing_rows(self):
     self.assert_compile(
         select([
             func.rank().filter(table1.c.name > 'foo').over(
                 rows=(1, 5), partition_by=['description'])
         ]), "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (PARTITION BY mytable.description ROWS BETWEEN :param_1 "
         "FOLLOWING AND :param_2 FOLLOWING) "
         "AS anon_1 FROM mytable")
Пример #15
0
 def historical_rank(cls, session, student_id: int):
     rank_func = func.rank().\
                 over(order_by=cls.gpa.desc()).\
                 label("rank")
     students_ranked = session.query(cls, rank_func).\
                       subquery()
     rank = session.query(students_ranked.c.rank).\
            filter(students_ranked.c.id == student_id)
     return rank.scalar()
Пример #16
0
 def test_funcfilter_windowing_range(self):
     self.assert_compile(
         select(func.rank().filter(table1.c.name > "foo").over(
             range_=(1, 5), partition_by=["description"])),
         "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (PARTITION BY mytable.description RANGE BETWEEN :param_1 "
         "FOLLOWING AND :param_2 FOLLOWING) "
         "AS anon_1 FROM mytable",
     )
Пример #17
0
 def class_rank(cls, session, grad_year: int, student_id: int):
     rank_func = func.rank().\
                 over(order_by=cls.gpa.desc()).\
                 label("rank")
     grad_class_ranked = session.query(cls, rank_func).\
                         filter_by(grad_year=grad_year).\
                         subquery()
     rank = session.query(grad_class_ranked.c.rank).\
            filter(grad_class_ranked.c.id == student_id)
     return rank.scalar()
Пример #18
0
def get_user_validated_audio_count(lang):
    claims = get_jwt_claims()

    lang_validated_count = (
        db.session.query(
            ValidatedAudio.file_name
        )
        .filter_by(expected_language_code=lang, created_by=claims['user'])
        .distinct()
        .count()
    )

    total_validated_count = (
        db.session.query(
            ValidatedAudio.file_name
        )
        .filter_by(created_by=claims['user'])
        .count()
    )

    user_rank = (
        db.session.query(
            func.count(ValidatedAudio.file_name).label("cnt")
        )
        .group_by(ValidatedAudio.created_by)
        .order_by(desc("cnt"))
    ).first()

    subquery = (
        db.session.query(
            ValidatedAudio.created_by,
            func.count(ValidatedAudio.file_name).label('cnt')
        )
        .group_by(ValidatedAudio.created_by)
    ).subquery()

    user_ranks = (
        db.session.query(
            subquery,
            func.rank().over(order_by=desc('cnt')).label('user_rank')
        )
    ).subquery()
    
    user_rank = db.session.query(user_ranks).filter_by(created_by=claims['user']).first()

    users_count = db.session.query(ValidatedAudio.created_by).distinct().count()

    return jsonify(
        {
            'languageValidatedCount': lang_validated_count, 
            'totalValidatedCount': total_validated_count,
            'rank': user_rank._asdict()['user_rank'] if user_rank else 0,
            'usersCount': users_count
        }
    )
Пример #19
0
def get_last_measurements(use_cache=True):
	
	app.logger.info("---> get_last_measurements()")
	
	measurements_array = []

	# Check if a cache file exists to avoid querying the database
	# TODO: put cache file path in ONE place
	if os.path.isfile("/shared_data/last_measurements.json") and use_cache:
		with open("/shared_data/last_measurements.json") as measurements_file:
			app.logger.debug("Returning cache data")
			return json.load(measurements_file)
		
	app.logger.debug("Executing subquery")
	# Subquery to get all measurements ranked by station based on date. Rank 1 is the most recent	
	subquery = db.session.query(
	    Measurement,
	    func.rank().over(
	        order_by=Measurement.date_created.desc(),
	        partition_by=Measurement.station
	    ).label('rnk')
	).subquery()
	app.logger.debug("End of subquery execution")
	
	# Query to get the first ranked measurment (most recent), and then joined with Station
	# to extract readable name and coordinates
	app.logger.debug("Executing JOIN")
	query = db.session.query(subquery,Station).filter(subquery.c.rnk==1).filter(Station.code==subquery.c.station)
	app.logger.debug("End of JOIN execution")
	
	# Result is a tuple with a Station object in the 11th position. Create a full 
	# measurement object with the station name and coordinates
	for ms in query:
		full_ms = {}
		
		full_ms['name']= ms[11].name
		full_ms['lat'] = float(ms[11].lat)
		full_ms['lon'] = float(ms[11].lon)
		
		# Convert date to Spanish Time
		full_ms['date_created'] = sp_date_str(ms.date_created)
		full_ms['current_temp'] = float(ms.current_temp)
		full_ms['current_hum'] = float(ms.current_hum)
		full_ms['current_pres'] = float(ms.current_pres)
		full_ms['wind_speed'] = float(ms.current_pres)
		full_ms['max_gust'] = float(ms.max_gust)
		full_ms['wind_direction'] = float(ms.wind_direction)
		full_ms['rainfall'] = float(ms.rainfall)
		full_ms['code'] = ms.station
		
		measurements_array.append(full_ms)
	
	app.logger.info("Returning %d measurements" % len(measurements_array))	
	return measurements_array
Пример #20
0
    def query_byTop(self, i):
        subquery = self.sqlite_session.query(
            zhihuTables.authorName,
            func.rank().over(
                order_by=zhihuTables.vote.desc()).label('rnk')).subquery()
        result = self.sqlite_session.query(subquery).filter(
            subquery.c.rnk == 1)

        # result = self.sqlite_session.query(zhihuTables.authorName, zhihuTables.vote, zhihuTables.content).func.rank(i).over(order_by='vote')
        # print(result)
        return result
Пример #21
0
 def test_no_paren_fns(self):
     for fn, expected in [
         (func.uid(), "uid"),
         (func.UID(), "UID"),
         (func.sysdate(), "sysdate"),
         (func.row_number(), "row_number()"),
         (func.rank(), "rank()"),
         (func.now(), "CURRENT_TIMESTAMP"),
         (func.current_timestamp(), "CURRENT_TIMESTAMP"),
         (func.user(), "USER"),
     ]:
         self.assert_compile(fn, expected)
Пример #22
0
 def test_no_paren_fns(self):
     for fn, expected in [
         (func.uid(), "uid"),
         (func.UID(), "UID"),
         (func.sysdate(), "sysdate"),
         (func.row_number(), "row_number()"),
         (func.rank(), "rank()"),
         (func.now(), "CURRENT_TIMESTAMP"),
         (func.current_timestamp(), "CURRENT_TIMESTAMP"),
         (func.user(), "USER"),
     ]:
         self.assert_compile(fn, expected)
Пример #23
0
 def test_funcfilter_windowing_orderby_partitionby(self):
     self.assert_compile(
         select(
             [
                 func.rank()
                 .filter(table1.c.name > "foo")
                 .over(order_by=table1.c.name, partition_by=["description"])
             ]
         ),
         "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (PARTITION BY mytable.description ORDER BY mytable.name) "
         "AS anon_1 FROM mytable",
     )
Пример #24
0
def view_result(embedding_id, testset_id):
    # Calculate the ranking of the embedding on the testset.
    partition = func.rank().over(partition_by=Result.testset_id,
                                 order_by=Result.accuracy.desc()).label('rank')
    sq = db.query(Result, partition).subquery()

    result = db.query(sq).filter(sq.c.embedding_id == embedding_id,
                                 sq.c.testset_id == testset_id).first()

    if not result:
        abort(404)

    return jsonify(data=serialize_result(result, summary=False))
Пример #25
0
 def test_funcfilter_windowing_orderby(self):
     # test filtered windowing:
     self.assert_compile(
         select([
             func.rank().filter(
                 table1.c.name > 'foo'
             ).over(
                 order_by=table1.c.name
             )
         ]),
         "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable"
     )
Пример #26
0
    def _get_last_classif_history(self, from_user_id: Optional[int], but_not_from_user_id: Optional[int]) \
            -> List[HistoricalLastClassif]:
        """
            Query for last classification history on all objects of self, mixed with present state in order
            to have restore-able lines.
        """
        # Get the histo entries
        subqry = self.session.query(
            ObjectsClassifHisto,
            func.rank().over(
                partition_by=ObjectsClassifHisto.objid,
                order_by=ObjectsClassifHisto.classif_date.desc()).label("rnk"))
        if from_user_id:
            subqry = subqry.filter(
                ObjectsClassifHisto.classif_who == from_user_id)
        if but_not_from_user_id:
            subqry = subqry.filter(
                ObjectsClassifHisto.classif_who != but_not_from_user_id)
        subqry = subqry.filter(ObjectsClassifHisto.classif_type == "M")
        subqry = subqry.filter(
            ObjectsClassifHisto.objid == any_(self.object_ids)).subquery()

        # Also get some fields from ObjectHeader for referencing, info, and fallback
        qry = self.session.query(
            ObjectHeader.objid, ObjectHeader.classif_id,
            func.coalesce(subqry.c.classif_date,
                          ObjectHeader.classif_auto_when),
            subqry.c.classif_type,
            func.coalesce(subqry.c.classif_id,
                          ObjectHeader.classif_auto_id).label("h_classif_id"),
            func.coalesce(
                subqry.c.classif_qual,
                case([(ObjectHeader.classif_auto_id.isnot(None), 'P')])),
            subqry.c.classif_who)
        qry = qry.join(subqry,
                       ObjectHeader.objid == subqry.c.objid,
                       isouter=(from_user_id is None))
        if from_user_id is not None:
            # If taking history from a user, don't apply to the objects he/she classsified
            # in last already.
            qry = qry.filter(ObjectHeader.classif_who != from_user_id)
            qry = qry.filter(subqry.c.rnk == 1)
        else:
            # Taking any history, including nothing, so emit blank history (see isouter above)
            qry = qry.filter(ObjectHeader.objid == any_(self.object_ids))
            qry = qry.filter(or_(subqry.c.rnk == 1, subqry.c.rnk.is_(None)))
        logger.info("_get_last_classif_history qry:%s", str(qry))
        with CodeTimer("HISTORY for %d objs: " % len(self.object_ids), logger):
            ret = [HistoricalLastClassif(rec) for rec in qry.all()]
        logger.info("_get_last_classif_history qry: %d rows", len(ret))
        return ret
Пример #27
0
 def get_failed_build_priority_query(self):
     rank = func.rank().over(partition_by=Package.id,
                             order_by=Build.id.desc()).label('rank')
     sub = self.db.query(Package.id.label('pkg_id'), Build.state, rank)\
                  .outerjoin(Build,
                             Package.id == Build.package_id)\
                  .subquery()
     return self.db.query(sub.c.pkg_id,
                          literal_column(str(self.failed_priority))
                          .label('priority'))\
                   .filter(((sub.c.rank == 1) & (sub.c.state == 5)) |
                           ((sub.c.rank == 2) & (sub.c.state != 5)))\
                   .group_by(sub.c.pkg_id)\
                   .having(func.count(sub.c.pkg_id) == 2)
Пример #28
0
 def test_funcfilter_windowing_rows(self):
     self.assert_compile(
         select(
             [
                 func.rank()
                 .filter(table1.c.name > "foo")
                 .over(rows=(1, 5), partition_by=["description"])
             ]
         ),
         "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
         "OVER (PARTITION BY mytable.description ROWS BETWEEN :param_1 "
         "FOLLOWING AND :param_2 FOLLOWING) "
         "AS anon_1 FROM mytable",
     )
Пример #29
0
 def get_failed_build_priority_query(self):
     rank = func.rank().over(partition_by=Package.id,
                             order_by=Build.id.desc()).label('rank')
     sub = self.db.query(Package.id.label('pkg_id'), Build.state,
                         Build.deps_resolved, rank)\
                  .outerjoin(Build,
                             Package.id == Build.package_id)\
                  .subquery()
     failed_prio = get_config('priorities.failed_build_priority')
     return self.db.query(
         sub.c.pkg_id,
         literal_column(str(failed_prio)).label('priority')
     ).filter(
         ((sub.c.rank == 1) & ((sub.c.state == 5) | (sub.c.deps_resolved == False))) |
         ((sub.c.rank == 2) & (sub.c.state != 5))
     ).group_by(sub.c.pkg_id).having(func.count(sub.c.pkg_id) == 2)
Пример #30
0
def hypo():
    team1 = request.form['team1']
    team2 = request.form['team2']
    team3 = request.form['team3']
    team4 = request.form['team4']
    team5 = request.form['team5']
    gen = request.form['gender']
    dist = request.form['event']

    rightschools = db.session.query(
        models.athlete.athlete_name.label('athlete_name'),
        models.athlete.school_name.label('school_name'),
        models.athlete.event.label('event'),
        models.athlete.best_mark.label('best_mark'),
        models.athlete.gender.label('gender')).filter(
            and_(
                or_(models.athlete.school_name == team1,
                    models.athlete.school_name == team2,
                    models.athlete.school_name == team3,
                    models.athlete.school_name == team4,
                    models.athlete.school_name == team5),
                models.athlete.gender == gen,
                models.athlete.event == dist)).order_by(
                    asc(models.athlete.best_mark)).subquery()
    sq1 = db.session.query(
        rightschools.c.athlete_name.label('athlete_name'),
        rightschools.c.school_name.label('school_name'),
        rightschools.c.best_mark.label('mark'),
        func.rank().over(order_by=rightschools.c.best_mark.asc(),
                         partition_by=rightschools.c.school_name).label(
                             'tmsrnk')).subquery()
    sq2 = db.session.query(
        func.row_number().over(order_by=sq1.c.mark).label('points'),
        sq1.c.tmsrnk, sq1.c.athlete_name.label('athlete_name'),
        sq1.c.school_name.label('school_name'),
        sq1.c.mark.label('mark')).filter(
            sq1.c.tmsrnk < 8).order_by('mark').subquery()
    q2 = db.session.query(
        func.row_number().over(order_by=sq1.c.mark).label('points'),
        sq1.c.tmsrnk, sq1.c.athlete_name.label('athlete_name'),
        sq1.c.school_name.label('school_name'),
        sq1.c.mark.label('mark')).filter(sq1.c.tmsrnk < 8).order_by('mark')
    q3 = db.session.query(sq2.c.school_name.label('school_name'),
                          func.sum(sq2.c.points).label('points')).group_by(
                              sq2.c.school_name).order_by('points')
    return render_template('hypo.html', hypo=q2, teams=q3)
Пример #31
0
def get_rt_resource_usage(param=None):
    if param is None:
        param = {'resource_name': '', 'usage_type_code': ''}
    subquery = db.session.query(
        ResourceUsage,
        func.rank().over(order_by=ResourceUsage.imported_at.desc(),
                         partition_by=(ResourceUsage.resource_id,
                                       ResourceUsage.usage_type_code
                                       )).label('imported_rank')).subquery(
                                           name='resource_usage')
    query = db.session.query(subquery).filter(
        subquery.c.imported_rank == 1,
        subquery.c.resource_name.like('%{}%'.format(str(
            param['resource_name']))),
        subquery.c.usage_type_code.like('%{}%'.format(
            str(param['usage_type_code']))))
    return query
Пример #32
0
    def __get_result(model, flight_field):
        subq = (
            DBSession.query(
                getattr(Flight, flight_field),
                func.count("*").label("count"),
                func.sum(Flight.index_score).label("total"),
            )
            .group_by(getattr(Flight, flight_field))
            .outerjoin(Flight.model)
            .subquery()
        )

        result = DBSession.query(
            model, subq.c.count, subq.c.total, over(func.rank(), order_by=desc("total")).label("rank")
        ).join((subq, getattr(subq.c, flight_field) == model.id))

        result = result.order_by(desc("total"))
        return result
Пример #33
0
    def get_k_to_last_subscriptions(
        self,
        # Which set of subscriptions we want, first to last (= the last one), second to last (penultimate),
        # third to last ...
        k: int = 1):
        k_to_Last_subscriptions_query = self.bq_session.query(
            *[column.label(column.name) for column in self.sub_data.columns],
            func.rank().over(partition_by=self.sub_data.c['user_id'],
                             order_by=self.sub_data.c['start_time']).label(
                                 'reverse_order_rank'), self.sub_web_access(),
            func.safe_divide(
                (self.sub_data.c["amount"] -
                 self.sub_data.c["additional_amount"]),
                self.sub_data.c['length']).label('daily_price')).filter(
                    self.sub_data.c['start_time'].cast(
                        DATE) <= self.max_date.date()).subquery()

        k_to_Last_subscriptions_query = self.bq_session.query(*[
            column.label(column.name)
            for column in k_to_Last_subscriptions_query.columns
        ]).filter(k_to_Last_subscriptions_query.c['reverse_order_rank'] ==
                  k).subquery()

        sub_prices_query = self.sub_prices_query()
        k_to_Last_subscriptions_query = self.bq_session.query(
            *[
                column.label(column.name)
                for column in k_to_Last_subscriptions_query.columns
            ],
            case([(sub_prices_query.c['average_price'] == 0, 0.0),
                  (1 - k_to_Last_subscriptions_query.c['daily_price'] /
                   sub_prices_query.c['average_price'] >= 0.2, 1.0)],
                 else_=0.0).label('is_discount')).join(
                     sub_prices_query,
                     and_(
                         k_to_Last_subscriptions_query.c['web_access_level'] ==
                         sub_prices_query.c['web_access_level'], *[
                             k_to_Last_subscriptions_query.c[column] ==
                             sub_prices_query.c[column]
                             for column in self.sub_desc_column_names
                         ])).subquery()

        return k_to_Last_subscriptions_query
Пример #34
0
def list_results():
    embedding_id = request.args.get('embedding', None)
    testset_id = request.args.get('testset', None)

    # Calculate the ranking of the embedding on the testset.
    partition = func.rank().over(partition_by=Result.testset_id,
                                 order_by=Result.accuracy.desc()).label('rank')
    sq = db.query(Result, partition).subquery()
    query = db.query(sq)

    if embedding_id:
        query = query.filter(sq.c.embedding_id == int(embedding_id))
    if testset_id:
        query = query.filter(sq.c.testset_id == int(testset_id))

    results = query.all()

    data = [serialize_result(res) for res in results]
    meta = {'count': len(data)}

    return jsonify(data=data, meta=meta)
Пример #35
0
    def index(self, **kw):
        subq = DBSession.query(TrackingFix,
                               over(func.rank(),
                                    partition_by=TrackingFix.pilot_id,
                                    order_by=desc(TrackingFix.time)).label('rank')) \
                .outerjoin(TrackingFix.pilot) \
                .filter(TrackingFix.time >= datetime.utcnow() - timedelta(hours=6)) \
                .filter(TrackingFix.location_wkt != None) \
                .subquery()

        query = DBSession.query(TrackingFix) \
                .filter(TrackingFix.id == subq.c.id) \
                .filter(subq.c.rank == 1) \
                .order_by(desc(TrackingFix.time))

        tracks = []
        for track in query.all():
            airport = Airport.by_location(track.location, None)
            distance = airport.distance(track.location)
            tracks.append([track, airport, distance])

        return dict(tracks=tracks)
Пример #36
0
def _comic_archive_shared(parent_folder, request):
    folders = (session.query(GalleryFolder).filter(
        GalleryFolder.parent == parent_folder).options(
            joinedload(GalleryFolder.children)).order_by(
                GalleryFolder.order).all())
    folder_ids = [folder.id for folder in folders]
    child_folder_ids = [
        child.id for folder in folders for child in folder.children
    ]

    # XXX remove this; currently used by _base.mako and the test below
    comic = session.query(Comic).order_by(Comic.id.asc()).first()

    if request.has_permission('queue', comic):
        queued_clause = True
    else:
        queued_clause = ~GalleryItem.is_queued

    recent_pages_by_folder = defaultdict(list)
    date_range_by_folder = dict()
    page_count_by_folder = dict()
    all_seen_items = set()
    if folder_ids:
        recent_page_subq = (session.query(
            GalleryItem,
            func.rank().over(
                partition_by=GalleryItem.folder_id,
                order_by=GalleryItem.order.asc(),
            ).label('rank_first'),
            func.rank().over(
                partition_by=GalleryItem.folder_id,
                order_by=GalleryItem.order.desc(),
            ).label('rank_last'),
        ).filter(GalleryItem.folder_id.in_(folder_ids)).filter(
            queued_clause).subquery())
        GalleryItem_alias = aliased(GalleryItem, recent_page_subq)
        recent_page_q = (session.query(GalleryItem_alias).filter(
            (recent_page_subq.c.rank_last <= FOLDER_PREVIEW_PAGE_COUNT)
            | (recent_page_subq.c.rank_first == 1)).order_by(
                GalleryItem_alias.order.asc()))
        for item in recent_page_q:
            recent_pages_by_folder[item.folder].append(item)
            all_seen_items.add(item)

        # Snag the start/end dates for each folder and the number of items in
        # each
        group_q = (session.query(
            GalleryFolder, func.min(GalleryItem.date_published),
            func.max(GalleryItem.date_published),
            func.count('*')).join(GalleryFolder.pages).filter(
                GalleryItem.folder_id.in_(folder_ids)).filter(
                    queued_clause).group_by(GalleryFolder.id))
        tz = XXX_HARDCODED_TIMEZONE
        for folder, mindate, maxdate, count in group_q:
            date_range_by_folder[folder] = (
                tz.normalize(mindate.astimezone(tz)),
                tz.normalize(maxdate.astimezone(tz)),
            )
            page_count_by_folder[folder] = count

    first_page_by_folder = {}
    if child_folder_ids:
        GalleryFolder_descendants = aliased(GalleryFolder)
        recent_page_q = (session.query(GalleryFolder, GalleryItem).join(
            (GalleryFolder_descendants,
             and_(
                 GalleryFolder.left <= GalleryFolder_descendants.left,
                 GalleryFolder.right >= GalleryFolder_descendants.right,
             ))).join((GalleryItem, GalleryFolder_descendants.pages)).filter(
                 GalleryFolder.id.in_(child_folder_ids)).order_by(
                     GalleryFolder.id,
                     GalleryItem.order.asc()).distinct(GalleryFolder.id))
        first_page_by_folder = {
            folder: page
            for (folder, page) in recent_page_q
        }
        all_seen_items.update(first_page_by_folder.values())

    # Eagerload media rows for every item we've seen
    (session.query(GalleryItem).options(joinedload(GalleryItem.media)).filter(
        GalleryItem.id.in_([item.id for item in all_seen_items])).all())

    return dict(
        comic=comic,
        parent_folder=parent_folder,
        folders=folders,
        recent_pages_by_folder=recent_pages_by_folder,
        first_page_by_folder=first_page_by_folder,
        date_range_by_folder=date_range_by_folder,
        page_count_by_folder=page_count_by_folder,
    )
Пример #37
0
def _comic_archive_shared(parent_folder, request):
    folders = (
        session.query(GalleryFolder)
        .filter(GalleryFolder.parent == parent_folder)
        .options(
            joinedload(GalleryFolder.children)
        )
        .order_by(GalleryFolder.order)
        .all()
    )
    folder_ids = [folder.id for folder in folders]
    child_folder_ids = [
        child.id for folder in folders for child in folder.children
    ]

    # XXX remove this; currently used by _base.mako and the test below
    comic = session.query(Comic).order_by(Comic.id.asc()).first()

    if request.has_permission('queue', comic):
        queued_clause = True
    else:
        queued_clause = ~GalleryItem.is_queued

    recent_pages_by_folder = defaultdict(list)
    date_range_by_folder = dict()
    page_count_by_folder = dict()
    all_seen_items = set()
    if folder_ids:
        recent_page_subq = (
            session.query(
                GalleryItem,
                func.rank().over(
                    partition_by=GalleryItem.folder_id,
                    order_by=GalleryItem.order.asc(),
                ).label('rank_first'),
                func.rank().over(
                    partition_by=GalleryItem.folder_id,
                    order_by=GalleryItem.order.desc(),
                ).label('rank_last'),
            )
            .filter(GalleryItem.folder_id.in_(folder_ids))
            .filter(queued_clause)
            .subquery()
        )
        GalleryItem_alias = aliased(GalleryItem, recent_page_subq)
        recent_page_q = (
            session.query(GalleryItem_alias)
            .filter(
                (recent_page_subq.c.rank_last <= FOLDER_PREVIEW_PAGE_COUNT) |
                (recent_page_subq.c.rank_first == 1)
            )
            .order_by(GalleryItem_alias.order.asc())
        )
        for item in recent_page_q:
            recent_pages_by_folder[item.folder].append(item)
            all_seen_items.add(item)

        # Snag the start/end dates for each folder and the number of items in
        # each
        group_q = (
            session.query(
                GalleryFolder,
                func.min(GalleryItem.date_published),
                func.max(GalleryItem.date_published),
                func.count('*')
            )
            .join(GalleryFolder.pages)
            .filter(GalleryItem.folder_id.in_(folder_ids))
            .filter(queued_clause)
            .group_by(GalleryFolder.id)
        )
        tz = XXX_HARDCODED_TIMEZONE
        for folder, mindate, maxdate, count in group_q:
            date_range_by_folder[folder] = (
                tz.normalize(mindate.astimezone(tz)),
                tz.normalize(maxdate.astimezone(tz)),
            )
            page_count_by_folder[folder] = count

    first_page_by_folder = {}
    if child_folder_ids:
        GalleryFolder_descendants = aliased(GalleryFolder)
        recent_page_q = (
            session.query(GalleryFolder, GalleryItem)
            .join((GalleryFolder_descendants, and_(
                GalleryFolder.left <= GalleryFolder_descendants.left,
                GalleryFolder.right >= GalleryFolder_descendants.right,
            )))
            .join((GalleryItem, GalleryFolder_descendants.pages))
            .filter(GalleryFolder.id.in_(child_folder_ids))
            .order_by(GalleryFolder.id, GalleryItem.order.asc())
            .distinct(GalleryFolder.id)
        )
        first_page_by_folder = {
            folder: page
            for (folder, page) in recent_page_q
        }
        all_seen_items.update(first_page_by_folder.values())

    # Eagerload media rows for every item we've seen
    (
        session.query(GalleryItem)
        .options(
            joinedload(GalleryItem.media)
        )
        .filter(GalleryItem.id.in_([item.id for item in all_seen_items]))
        .all()
    )

    return dict(
        comic=comic,
        parent_folder=parent_folder,
        folders=folders,
        recent_pages_by_folder=recent_pages_by_folder,
        first_page_by_folder=first_page_by_folder,
        date_range_by_folder=date_range_by_folder,
        page_count_by_folder=page_count_by_folder,
    )
Пример #38
0
def get_user_medals(user: User) -> Tuple[List[Medal], List[Medal]]:
	unlocked = []
	locked = []

	#
	# REVIEWS
	#

	users_by_reviews = db.session.query(User.username, func.count(PackageReview.id).label("count")) \
		.select_from(User).join(PackageReview) \
		.group_by(User.username).order_by(text("count DESC")).all()
	try:
		review_boundary = users_by_reviews[math.floor(len(users_by_reviews) * 0.25)][1] + 1
	except IndexError:
		review_boundary = None
	users_by_reviews = [username for username, _ in users_by_reviews]

	review_idx = None
	review_percent = None
	try:
		review_idx = users_by_reviews.index(user.username)
		review_percent = round(100 * review_idx / len(users_by_reviews), 1)
	except ValueError:
		pass

	if review_percent is not None and review_percent < 25:
		if review_idx == 0:
			title = gettext(u"Most reviews")
			description = gettext(
					u"%(display_name)s has written the most reviews on ContentDB.",
					display_name=user.display_name)
		elif review_idx <= 2:
			if review_idx == 1:
				title = gettext(u"2nd most reviews")
			else:
				title = gettext(u"3rd most reviews")
			description = gettext(
					u"This puts %(display_name)s in the top %(perc)s%%",
					display_name=user.display_name, perc=review_percent)
		else:
			title = gettext(u"Top %(perc)s%% reviewer", perc=review_percent)
			description = gettext(u"Only %(place)d users have written more reviews.", place=review_idx)

		unlocked.append(Medal.make_unlocked(
				place_to_color(review_idx + 1), "fa-star-half-alt", title, description))
	else:
		description = gettext(u"Consider writing more reviews to get a medal.")
		if review_idx:
			description += " " + gettext(u"You are in place %(place)s.", place=review_idx + 1)
		locked.append(Medal.make_locked(
				description, (len(user.reviews), review_boundary)))

	#
	# TOP PACKAGES
	#
	all_package_ranks = db.session.query(
			Package.type,
			Package.author_id,
			func.rank().over(
					order_by=db.desc(Package.score),
					partition_by=Package.type) \
				.label("rank")).order_by(db.asc(text("rank"))) \
		.filter_by(state=PackageState.APPROVED).subquery()

	user_package_ranks = db.session.query(all_package_ranks) \
		.filter_by(author_id=user.id) \
		.filter(text("rank <= 30")) \
		.all()

	user_package_ranks = next(
			(x for x in user_package_ranks if x[0] == PackageType.MOD or x[2] <= 10),
			None)
	if user_package_ranks:
		top_rank = user_package_ranks[2]
		top_type = PackageType.coerce(user_package_ranks[0])
		if top_rank == 1:
			title = gettext(u"Top %(type)s", type=top_type.value.lower())
		else:
			title = gettext(u"Top %(group)d %(type)s", group=top_rank, type=top_type.value.lower())
		if top_type == PackageType.MOD:
			icon = "fa-box"
		elif top_type == PackageType.GAME:
			icon = "fa-gamepad"
		else:
			icon = "fa-paint-brush"

		description = gettext(u"%(display_name)s has a %(type)s placed at #%(place)d.",
				display_name=user.display_name, type=top_type.value.lower(), place=top_rank)
		unlocked.append(
				Medal.make_unlocked(place_to_color(top_rank), icon, title, description))

	#
	# DOWNLOADS
	#
	total_downloads = db.session.query(func.sum(Package.downloads)) \
		.select_from(User) \
		.join(User.packages) \
		.filter(User.id == user.id,
			Package.state == PackageState.APPROVED).scalar()
	if total_downloads is None:
		pass
	elif total_downloads < 50000:
		description = gettext(u"Your packages have %(downloads)d downloads in total.", downloads=total_downloads)
		description += " " + gettext(u"First medal is at 50k.")
		locked.append(Medal.make_locked(description, (total_downloads, 50000)))
	else:
		if total_downloads >= 300000:
			place = 1
			title = gettext(u">300k downloads")
		elif total_downloads >= 100000:
			place = 2
			title = gettext(u">100k downloads")
		elif total_downloads >= 75000:
			place = 3
			title = gettext(u">75k downloads")
		else:
			place = 10
			title = gettext(u">50k downloads")
		description = gettext(u"Has received %(downloads)d downloads across all packages.",
				display_name=user.display_name, downloads=total_downloads)
		unlocked.append(Medal.make_unlocked(place_to_color(place), "fa-users", title, description))

	return unlocked, locked
Пример #39
0
def ranked(event_type):
    return func.rank().over(
        order_by=event_type.clock.desc(),
        partition_by=event_type.container_id,
    )
Пример #40
0
def _env_images_that_can_be_deleted(
    project_uuid: Optional[str] = None,
    environment_uuid: Optional[str] = None,
    latest_can_be_removed: bool = False,
) -> List[models.EnvironmentImage]:
    """Gets environment images that are not in use and can be deleted.

    An env image to be considered as such needs to respect the following
    requirements:
    - not in use by a session, job or pipeline run
    - not the "latest" image for a given environment, because that would
        mean that a new session, job or pipeline run could use it
    - not already marked_for_removal
    - doesn't share the digest with an image that does not respect point
        1 or 2. This is because the digest is the real "identity" of the
        image, meaning that a name:tag actually points to digest, and
        a digest might be pointed at by different image:tag combinations
        . The registry doesn't allow deletion by image:tag but by
        digest, hence the edge case. Essentially, we don't want to
        delete a digest that is in use.

    Given this, the following query checks for env images which digest
    is not in the digests that are in use or are considered "latest".
    When it comes to edge cases (assuming the query is correct):
    - we don't run the risk of deleting a digest which is in use or
        latest
    - we don't run the risk of deleting a digest right after a new
        "latest" image that uses the same digest has been pushed
        assuming the task performing the registry deletion runs in the
        env builds queue, which ensures that no build is taking place
    - on the nodes, the node-agent is responsible for issuing a
        deletion to the client, said deletion happens by name:tag, which
        avoids the risk of deletion unintended images.
    """
    if latest_can_be_removed and (project_uuid is None
                                  or environment_uuid is None):
        raise ValueError(
            "'latest_can_be_removed' requires project and env uuid to be passed."
        )

    # Digests in use.
    imgs_in_use_by_int_runs = (db.session.query(
        models.EnvironmentImage.digest).join(
            models.PipelineRunInUseImage,
            models.EnvironmentImage.runs_using_image).join(
                models.InteractivePipelineRun,
                models.InteractivePipelineRun.uuid ==
                models.PipelineRunInUseImage.run_uuid,
            ).filter(
                models.InteractivePipelineRun.status.in_(
                    ["PENDING", "STARTED"])))
    imgs_in_use_by_sessions = (db.session.query(
        models.EnvironmentImage.digest).join(
            models.InteractiveSessionInUseImage,
            models.EnvironmentImage.sessions_using_image,
        ).join(
            models.InteractiveSession,
            models.InteractiveSession.project_uuid
            == models.InteractiveSessionInUseImage.project_uuid
            and models.InteractiveSessionInUseImage.pipeline_uuid
            == models.InteractiveSessionInUseImage.pipeline_uuid,
        ))
    imgs_in_use_by_jobs = (db.session.query(
        models.EnvironmentImage.digest).join(
            models.JobInUseImage,
            models.EnvironmentImage.jobs_using_image).join(
                models.Job,
                models.Job.uuid == models.JobInUseImage.job_uuid).filter(
                    models.Job.status.in_(
                        ["DRAFT", "PENDING", "STARTED", "PAUSED"])))
    imgs_in_use = imgs_in_use_by_int_runs.union(
        imgs_in_use_by_sessions, imgs_in_use_by_jobs).subquery()

    # Latest images digests.
    if not latest_can_be_removed:
        # Will produce a subquery like the following:
        # select project_uuid, environment_uuid, tag,
        # ####
        # rank() OVER (
        #     partition by project_uuid, environment_uuid
        #     order by tag desc
        # ) rank
        # from environment_images
        # order by project_uuid, environment_uuid, tag desc;
        # ####
        # Which will give rank 1 to the "latest" image of every
        # environment, example:
        # project_uuid  | environment_uuid | tag | rank
        # --------------+------------------+-----+------
        # 13006c56-... | 18b59993-...     |   1 |    1
        # 13006c56-... | c56ab762-...     |   2 |    1
        # 13006c56-... | c56ab762-...     |   1 |    2
        # 13006c56-... | e0af758d-...     |   3 |    1
        # 13006c56-... | e0af758d-...     |   2 |    2
        # 13006c56-... | e0af758d-...     |   1 |    3
        tag_rank = (func.rank().over(
            partition_by=[
                models.EnvironmentImage.project_uuid,
                models.EnvironmentImage.environment_uuid,
            ],
            order_by=models.EnvironmentImage.tag.desc(),
        ).label("tag_rank"))
        latest_imgs = db.session.query(
            models.EnvironmentImage).add_column(tag_rank)
        latest_imgs = (latest_imgs.from_self().filter(
            tag_rank == 1).with_entities(
                models.EnvironmentImage.digest).subquery())

    imgs_not_in_use = models.EnvironmentImage.query.with_for_update().filter(
        models.EnvironmentImage.digest.not_in(imgs_in_use),
        # Assume it's already been processed.
        models.EnvironmentImage.marked_for_removal.is_(False),
    )
    if not latest_can_be_removed:
        imgs_not_in_use = imgs_not_in_use.filter(
            models.EnvironmentImage.digest.not_in(latest_imgs), )
    if project_uuid:
        imgs_not_in_use = imgs_not_in_use.filter(
            models.EnvironmentImage.project_uuid == project_uuid)
    if environment_uuid:
        imgs_not_in_use = imgs_not_in_use.filter(
            models.EnvironmentImage.environment_uuid == environment_uuid)
    return imgs_not_in_use.all()