def get(self): obj_count = \ sa.sql.select([ Tag.ns, functions.count(entity_tag_tbl.c.entity_id).label('obj_count'), ])\ .select_from(Tag.__table__.join(entity_tag_tbl))\ .group_by(Tag.ns)\ .alias() ns_query = sa.sql.select( [Tag.ns, functions.count(Tag.id).label('tag_count'), obj_count.c.obj_count], from_obj=[Tag.__table__.outerjoin(obj_count, Tag.ns == obj_count.c.ns)] )\ .group_by(Tag.ns, obj_count.c.obj_count)\ .order_by(Tag.ns) session = current_app.db.session() namespaces = session.execute(ns_query) return render_template( 'admin/tags.html', namespaces=namespaces, )
def top_maps_by_times_played_q(cutoff_days, region = None, game_type_cd = None): """ Query to retrieve the top maps by the amount of times it was played during a date range. Games older than cutoff_days days old are ignored. """ # only games played during this range are considered right_now = datetime.utcnow() cutoff_dt = right_now - timedelta(days=cutoff_days) top_maps_q = DBSession.query(Game.map_id, Map.name, func.count()).\ filter(Map.map_id==Game.map_id).\ filter(expr.between(Game.create_dt, cutoff_dt, right_now)).\ order_by(expr.desc(func.count())).\ group_by(Game.map_id).\ group_by(Map.name) if region and region != "" and region != "0": top_maps_q = top_maps_q.filter(Server.region==region).filter(Server.server_id==Game.server_id) if game_type_cd and game_type_cd != "": top_maps_q = top_maps_q.filter(Game.game_type_cd == game_type_cd) return top_maps_q
def top_maps(self): """Returns the raw data shared by all renderers.""" try: top_maps_q = DBSession.query( fg.row_number().over(order_by=expr.desc(func.count())).label("rank"), Game.map_id, Map.name, func.count().label("times_played"))\ .filter(Map.map_id == Game.map_id)\ .filter(Game.server_id == self.server_id)\ .filter(Game.create_dt > (self.now - timedelta(days=self.lifetime)))\ .group_by(Game.map_id)\ .group_by(Map.name) \ .order_by(expr.desc(func.count())) if self.last: top_maps_q = top_maps_q.offset(self.last) if self.limit: top_maps_q = top_maps_q.limit(self.limit) top_maps = top_maps_q.all() except Exception as e: log.debug(e) raise HTTPNotFound return top_maps
def execute(self, message, user, params): tick = Updates.current_tick() + (params.group(1) or 1) replies = [] Q = session.query(Galaxy.x, Galaxy.y, count()) Q = Q.join(Target.planet) Q = Q.join(Planet.galaxy) Q = Q.filter(Planet.active == True) Q = Q.filter(Target.tick >= tick) Q = Q.group_by(Galaxy.x, Galaxy.y) result = Q.all() prev = [] for x, y, bitches in result: prev.append("%s:%s(%s)"%(x,y,bitches)) replies.append("Active bookings: " + ", ".join(prev)) Q = session.query(Alliance.name, count()) Q = Q.join(Target.planet) Q = Q.outerjoin(Planet.intel) Q = Q.outerjoin(Intel.alliance) Q = Q.filter(Planet.active == True) Q = Q.filter(Target.tick >= tick) Q = Q.group_by(Alliance.name) result = Q.all() prev = [] for name, bitches in result: prev.append("%s (%s)"%(name or "Unknown", bitches)) replies.append("Active bitches: " + ", ".join(prev)) if len(replies) < 1: replies.append("No active bookings. This makes %s sad. Please don't make %s sad." %((Config.get("Connection","nick"),)*2)) message.reply("\n".join(replies))
def fleet_overview(self): if self.scantype not in ("J",): return from sqlalchemy.sql.functions import min, sum f = aliased(FleetScan) a = aliased(FleetScan) d = aliased(FleetScan) Q = session.query( f.landing_tick, f.landing_tick - min(Scan.tick), count(a.id), coalesce(sum(a.fleet_size), 0), count(d.id), coalesce(sum(d.fleet_size), 0), ) Q = Q.join(f.scan) Q = Q.filter(f.scan == self) Q = Q.outerjoin((a, and_(a.id == f.id, a.mission.ilike("Attack")))) Q = Q.outerjoin((d, and_(d.id == f.id, d.mission.ilike("Defend")))) Q = Q.group_by(f.landing_tick) Q = Q.order_by(asc(f.landing_tick)) return Q.all()
def get_counts_for_query(self, q): # HACKITY HACK entities = [ x.entity_zero.entity for x in q._entities] entities = {e.__mapper__.tables[0].name: e for e in entities} content_entity = entities['content'] post = with_polymorphic( Post, [], Post.__table__, aliased=False, flat=True) q = q.join( post, (content_entity.id == post.id) & (post.publication_state.in_(countable_publication_states))) if self.user_id: action_entity = entities['action'] return q.with_entities( count(content_entity.id), count(post.creator_id.distinct()), count(action_entity.id)).first() else: (post_count, contributor_count) = q.with_entities( count(content_entity.id), count(post.creator_id.distinct())).first() return (post_count, contributor_count, 0)
def execute(self, request, user, x, y, z, h=False, hs=False, ticks=None): planet = Planet.load(x,y,z) if planet is None: return HttpResponseRedirect(reverse("planet_ranks")) ticks = int(ticks or 0) if (h or hs) else 12 if not hs: sizediffvalue = PlanetHistory.rdiff * PA.getint("numbers", "roid_value") valuediffwsizevalue = PlanetHistory.vdiff - sizediffvalue resvalue = valuediffwsizevalue * PA.getint("numbers", "res_value") shipvalue = valuediffwsizevalue * PA.getint("numbers", "ship_value") xpvalue = PlanetHistory.xdiff * PA.getint("numbers", "xp_value") Q = session.query(PlanetHistory, sizediffvalue, valuediffwsizevalue, resvalue, shipvalue, xpvalue, ) Q = Q.filter(PlanetHistory.current == planet) Q = Q.order_by(desc(PlanetHistory.tick)) history = Q[:ticks] if ticks else Q.all() else: history = None if not (h or hs): landings = session.query(PlanetLandings.hour, count()).filter(PlanetLandings.planet==planet).group_by(PlanetLandings.hour).all() landed = session.query(PlanetLandedOn.hour, count()).filter(PlanetLandedOn.planet==planet).group_by(PlanetLandedOn.hour).all() vdrops = session.query(PlanetValueDrops.hour, count()).filter(PlanetValueDrops.planet==planet).group_by(PlanetValueDrops.hour).all() idles = session.query(PlanetIdles.hour, count()).filter(PlanetIdles.planet==planet).group_by(PlanetIdles.hour).all() hourstats = { 'landings' : dict(landings), 'landingsT' : sum([c for hour,c in landings]), 'landed' : dict(landed), 'landedT' : sum([c for hour,c in landed]), 'vdrops' : dict(vdrops), 'vdropsT' : sum([c for hour,c in vdrops]), 'idles' : dict(idles), 'idlesT' : sum([c for hour,c in idles]), } else: hourstats = None if not h: Q = session.query(PlanetHistory) Q = Q.filter(or_(PlanetHistory.hour == 23, PlanetHistory.tick == Updates.current_tick())) Q = Q.filter(PlanetHistory.current == planet) Q = Q.order_by(desc(PlanetHistory.tick)) hsummary = Q.all() if hs else Q[:14] else: hsummary = None return render(["planet.tpl",["hplanet.tpl","hsplanet.tpl"][hs]][h or hs], request, planet = planet, history = history, hour = datetime.utcnow().hour, hourstats = hourstats, hsummary = hsummary, ticks = ticks, )
def main_index(request): leaderboard_count = 10 recent_games_count = 32 # top players by score top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Player.player_id > 2).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:10] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] for i in range(leaderboard_count-len(top_players)): top_players.append(('-', '-', '-')) # top servers by number of total players played top_servers = DBSession.query(Server.server_id, Server.name, func.count()).\ filter(Game.server_id==Server.server_id).\ order_by(expr.desc(func.count(Game.game_id))).\ group_by(Server.server_id).\ group_by(Server.name).all()[0:10] for i in range(leaderboard_count-len(top_servers)): top_servers.append(('-', '-', '-')) # top maps by total times played top_maps = DBSession.query(Map.map_id, Map.name, func.count(Game.game_id)).\ filter(Map.map_id==Game.game_id).\ order_by(expr.desc(func.count(Game.game_id))).\ group_by(Map.map_id).\ group_by(Map.name).all()[0:10] for i in range(leaderboard_count-len(top_maps)): top_maps.append(('-', '-', '-')) recent_games = DBSession.query(Game, Server, Map).\ filter(Game.server_id==Server.server_id).\ filter(Game.map_id==Map.map_id).\ order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count] for i in range(recent_games_count-len(recent_games)): recent_games.append(('-', '-', '-')) return {'top_players':top_players, 'top_servers':top_servers, 'top_maps':top_maps, 'recent_games':recent_games, }
def getAlarmCount(days=0): """ Get number of alarms, grouped by state :param optional days: 0 for all alarms, since days else :return: list grouped by state """ if days != 0: return db.get(Alarm.state, count(Alarm.id)).filter(Alarm.timestamp > (datetime.datetime.now() - datetime.timedelta(days=days))).order_by(Alarm.timestamp.desc()).group_by(Alarm.state).all() else: return db.get(Alarm.state, count(Alarm.id)).group_by(Alarm.state).all()
def execute(self, request, user, name): alliance = Alliance.load(name) if alliance is None: return HttpResponseRedirect(reverse("alliance_ranks")) ph = aliased(PlanetHistory) members = count().label("members") size = sum(ph.size).label("size") value = sum(ph.value).label("value") score = sum(ph.score).label("score") avg_size = size.op("/")(members).label("avg_size") avg_value = value.op("/")(members).label("avg_value") t10v = count(case(whens=((ph.value_rank <= 10 ,1),), else_=None)).label("t10v") t100v = count(case(whens=((ph.value_rank <= 100 ,1),), else_=None)).label("t100v") pho = aliased(PlanetHistory) sizeo = sum(pho.size).label("sizeo") valueo = sum(pho.value).label("valueo") scoreo = sum(pho.score).label("scoreo") Q = session.query(PlanetHistory.tick.label("tick"), Alliance.id.label("id"), literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.size) DESC)").label("size_rank"), literal_column("rank() OVER (PARTITION BY planet_history.tick ORDER BY sum(planet_history.value) DESC)").label("value_rank"), ) Q = Q.filter(PlanetHistory.active == True) Q = Q.join(PlanetHistory.current) Q = Q.join(Planet.intel) Q = Q.join(Intel.alliance) Q = Q.group_by(PlanetHistory.tick, Alliance.id) ranks = Q.subquery() Q = session.query(ph.tick, members, size, value, avg_size, avg_value, size-sizeo, value-valueo, score-scoreo, t10v, t100v, ) Q = Q.filter(ph.active == True) Q = Q.join(ph.current) Q = Q.join(Planet.intel) Q = Q.join(Intel.alliance) Q = Q.outerjoin((pho, and_(ph.id==pho.id, ph.tick-1==pho.tick),)) Q = Q.filter(Intel.alliance == alliance) Q = Q.group_by(ph.tick) Q = Q.from_self().add_columns(ranks.c.size_rank, ranks.c.value_rank) Q = Q.outerjoin((ranks, and_(ph.tick == ranks.c.tick, alliance.id == ranks.c.id),)) Q = Q.order_by(desc(ph.tick)) history = Q.all() return render("ialliancehistory.tpl", request, alliance=alliance, members=alliance.intel_members, history=history)
def upgrade(pyramid_env): from assembl.models import IdeaLink, get_session_maker db = get_session_maker()() # First, reorder live links. with transaction.manager: ids = db.query(IdeaLink.source_id)\ .filter(IdeaLink.tombstone_date == None)\ .group_by(IdeaLink.source_id, IdeaLink.order)\ .having((count(IdeaLink.id) > 1)).all() for (source_id,) in ids: links = db.query(IdeaLink).filter_by( source_id=source_id, tombstone_date=None).order_by( IdeaLink.order, IdeaLink.id).all() for n, link in enumerate(links): link.order = n + 1 # Then dead links q = db.query( IdeaLink.source_id, IdeaLink.tombstone_date).\ group_by(IdeaLink.source_id, IdeaLink.order, IdeaLink.tombstone_date).\ having((count(IdeaLink.id) > 1)).all() for (source_id, date) in q: if not date: continue dest_links = db.query(IdeaLink).filter_by( source_id=source_id, tombstone_date=date).all() # Try to find the order of the ordered link the closest # in date to each current link. all_links = db.query(IdeaLink).filter_by(source_id=source_id).all() by_base_id = defaultdict(list) for link in all_links: by_base_id[link.base_id].append(link) signatures = {} for dest in dest_links: base_id = dest.base_id links = by_base_id[base_id] # source_id should be the same. links = [l for l in links if l.order] def distance(l): if l == dest: return -1 if not l.tombstone_date: return 0 return abs((l.tombstone_date-dest.tombstone_date).seconds) links.sort(key=distance) signatures[base_id] = tuple((l.order for l in links)) dest_links.sort(key=lambda l: signatures[l.base_id]) for n, link in enumerate(dest_links): link.order = n
def calculateDomainAffinities(): DeityDomain2 = aliased(DeityDomain) Domain2 = aliased(Domain) q_affinity = session.query(Domain.name, Domain2.name, count()).\ join(DeityDomain).\ join(Deity).\ join(DeityDomain2).\ join(Domain2).\ filter(Domain.id != Domain2.id).\ order_by(Domain.name, count().desc(), Domain2.name).\ group_by(Domain.name, Domain2.name) return q_affinity.all()
def execute(self, message, user, params): tag_count = PA.getint("numbers", "tag_count") alliance = Alliance.load(params.group(1)) if alliance is None: message.reply("No alliance matching '%s' found"%(params.group(1),)) return Q = session.query(sum(Planet.value), sum(Planet.score), sum(Planet.size), sum(Planet.xp), count()) Q = Q.join(Planet.intel) Q = Q.filter(Planet.active == True) Q = Q.filter(Intel.alliance==alliance) Q = Q.group_by(Intel.alliance_id) result = Q.first() if result is None: message.reply("No planets in intel match alliance %s"%(alliance.name,)) return value, score, size, xp, members = result if members <= tag_count: reply="%s Members: %s/%s, Value: %s, Avg: %s," % (alliance.name,members,alliance.members,value,value//members) reply+=" Score: %s, Avg: %s," % (score,score//members) reply+=" Size: %s, Avg: %s, XP: %s, Avg: %s" % (size,size//members,xp,xp//members) message.reply(reply) return Q = session.query(Planet.value, Planet.score, Planet.size, Planet.xp, Intel.alliance_id) Q = Q.join(Planet.intel) Q = Q.filter(Planet.active == True) Q = Q.filter(Intel.alliance==alliance) Q = Q.order_by(desc(Planet.score)) Q = Q.limit(tag_count) Q = Q.from_self(sum(Planet.value), sum(Planet.score), sum(Planet.size), sum(Planet.xp), count()) Q = Q.group_by(Intel.alliance_id) ts_result = Q.first() ts_value, ts_score, ts_size, ts_xp, ts_members = ts_result reply="%s Members: %s/%s (%s)" % (alliance.name,members,alliance.members,ts_members) reply+=", Value: %s (%s), Avg: %s (%s)" % (value,ts_value,value//members,ts_value//ts_members) reply+=", Score: %s (%s), Avg: %s (%s)" % (score,ts_score,score//members,ts_score//ts_members) reply+=", Size: %s (%s), Avg: %s (%s)" % (size,ts_size,size//members,ts_size//ts_members) reply+=", XP: %s (%s), Avg: %s (%s)" % (xp,ts_xp,xp//members,ts_xp//ts_members) message.reply(reply)
def calculateDomainAffinitiesForSetting(setting): DeityDomain2 = aliased(DeityDomain) Domain2 = aliased(Domain) q_settingaffinity = session.query(Domain.name, Domain2.name, count()).\ join(DeityDomain).\ join(Deity).\ join(DeityDomain2).\ join(Domain2).\ join(DeitySetting).\ filter(Domain.id != Domain2.id, DeitySetting.setting == setting).\ order_by(Domain.name, count().desc(), Domain2.name).\ group_by(Domain.name, Domain2.name) return q_settingaffinity.all()
def get_counts_for_query(self, q): if self.user_id: # HACKITY HACK (content_entity, action_entity) = [ x.entity_zero.entity for x in q._entities] return q.with_entities( count(content_entity.id), count(action_entity.id)).first() return (post_count, viewed_count) else: (content_entity,) = [ x.entity_zero.entity for x in q._entities] (post_count,) = q.with_entities( count(content_entity.id)).first() return (post_count, 0)
def execute(self, message, user, params): alliance = Alliance.load(params.group(1)) if alliance is None: message.reply("No alliance matching '%s' found" % (params.group(1),)) return if params.group(2): alliance2 = Alliance.load(params.group(2)) if alliance2 is None: message.reply("No alliance matching '%s' found" % (params.group(2),)) return bums = int(params.group(3) or 2) Q = session.query(Galaxy.x, Galaxy.y, count()) Q = Q.join(Galaxy.planets) Q = Q.join(Planet.intel) Q = Q.filter(Galaxy.active == True) Q = Q.filter(Planet.active == True) if params.group(2): R = Q.filter(Intel.alliance == alliance2) R = R.group_by(Galaxy.x, Galaxy.y) R = R.having(count() >= bums) Q = Q.filter(Intel.alliance == alliance) Q = Q.group_by(Galaxy.x, Galaxy.y) Q = Q.having(count() >= bums) prev = [] if params.group(2): for x1, y1, c1 in Q.all(): for x2, y2, c2 in R.all(): if x1 == x2 and y1 == y2: prev.append("%s:%s (%s,%s)" % (x1, y1, c1, c2)) if len(prev) < 1: message.reply( "No galaxies with at least %s bumchums from %s and %s" % (bums, alliance.name, alliance2.name) ) return reply = "Galaxies with at least %s bums from %s and %s: " % ( bums, alliance.name, alliance2.name, ) + " | ".join(prev) else: result = Q.all() if len(result) < 1: message.reply("No galaxies with at least %s bumchums from %s" % (bums, alliance.name)) return prev = [] for x, y, chums in result: prev.append("%s:%s (%s)" % (x, y, chums)) reply = "Galaxies with at least %s bums from %s: " % (bums, alliance.name) + " | ".join(prev) message.reply(reply)
def list_planets(self, message, user, params): oIntel = aliased(Intel) tIntel = aliased(Intel) # Find all planets with unknown alliance, who have been defended by planets (outside of their galaxy) with known alliance TQ = session.query(Planet.x, Planet.y, Planet.z, Alliance.name, count()).select_from(FleetScan).filter(FleetScan.in_galaxy==False, FleetScan.mission=="Defend") TQ = TQ.join(oIntel, FleetScan.owner_id == oIntel.planet_id).join(tIntel, FleetScan.target_id == tIntel.planet_id) TQ = TQ.filter(tIntel.alliance_id == None).filter(oIntel.alliance_id != None) TQ = TQ.join(Alliance, oIntel.alliance_id == Alliance.id).join(Planet, FleetScan.target_id == Planet.id) TQ = TQ.group_by(Planet.x, Planet.y, Planet.z, Alliance.name) # Find all planets with unknown alliance, who have defended planets (outside of their galaxy) with known alliance OQ = session.query(Planet.x, Planet.y, Planet.z, Alliance.name, count()).select_from(FleetScan).filter(FleetScan.in_galaxy==False, FleetScan.mission=="Defend") OQ = OQ.join(oIntel, FleetScan.owner_id == oIntel.planet_id).join(tIntel, FleetScan.target_id == tIntel.planet_id) OQ = OQ.filter(tIntel.alliance_id != None).filter(oIntel.alliance_id == None) OQ = OQ.join(Alliance, tIntel.alliance_id == Alliance.id).join(Planet, FleetScan.owner_id == Planet.id) OQ = OQ.group_by(Planet.x, Planet.y, Planet.z, Alliance.name) # A FULL OUTER JOIN would fit nicely here, but SQLAlchemy doesn't support it and I'm trying to stick with ORM, so we'll use Python # Combine the results into one sorted list results = sorted(TQ.all()+OQ.all()) # Quit now if there are no results if len(results) == 0: message.reply("No suggestions found") return i = 0 while i < (len(results)-1): # Check for planet/alliance combinations that appeared in both lists if results[i][:4] == results[i+1][:4]: r = list(results.pop(i)) # Add the fleet counts (r[i+1] has moved to r[i]) r[4] += results.pop(i)[4] results.insert(i, r) i+=1 # Sort by number of fleets using a helper function from operator import itemgetter results.sort(key=itemgetter(4), reverse=True) # Reply to the user message.reply("Coords Suggestion Fleets") limit = int(params.group(1) or 5) for r in results[:limit]: message.reply("%-9s %-14s %s" % ("%s:%s:%s" % (r[0], r[1], r[2]), r[3], r[4])) if len(results) > limit: message.reply("%s results not shown (%s total)" % (len(results)-limit, len(results)))
def dashboard(request): dbsession = DBSession() jobs = Job.get_last() running_jobs = Job.get_running() upcoming_jobs = Job.get_upcoming() # statistics num_clients = dbsession.query(count(Client.clientid)).scalar() num_jobs = dbsession.query(count(Job.jobid)).scalar() num_volumes = dbsession.query(count(Media.mediaid)).scalar() sum_volumes = Media.format_byte_size(dbsession.query(sum(Media.volbytes)).scalar() or 0) database_size = get_database_size(DBSession.bind) return locals()
def __init__(self, *args, **kwargs): super(EmailAlertForm, self).__init__(*args, **kwargs) committee_list = Committee\ .query\ .order_by(Committee.house_id.desc())\ .order_by(Committee.name)\ .filter_by(monitored=True)\ .all() # count of daily schedule subscribers subs = User.query.filter(User.subscribe_daily_schedule == True, User.confirmed_at != None).count() # noqa self.daily_schedule_subscribers.label.text += " (%d)" % subs # count subscribers for committees subscriber_counts = {t[0]: t[1] for t in db.session\ .query(user_committee_alerts.c.committee_id, count(1))\ .join(User, User.id == user_committee_alerts.c.user_id)\ .filter(User.confirmed_at != None)\ .group_by(user_committee_alerts.c.committee_id)\ .all()} self.committee_ids.choices = [(c.id, "%s - %s (%d)" % (c.house.name, c.name, subscriber_counts.get(c.id, 0))) for c in committee_list] self.message = None self.ad_hoc_mapper = [] for committee in committee_list: if committee.ad_hoc: self.ad_hoc_mapper.append(committee.id)
def get(self): email = self.get_email() # Shorter variable names auth_user = auth_user_table survey = survey_table submission = submission_table submission_id = submission_table.c.submission_id submission_time = submission_table.c.submission_time result = self._get_records( table=auth_user.join(survey).outerjoin(submission), email=email, selected=[ survey_table.c.survey_title, count(submission_id).label('num_submissions'), # survey_table.c.created_on, sqlmax(submission_time).label('latest_submission'), survey_table.c.survey_id, ], text_filter_column=survey_table.c.survey_title, default_sort_column_name='latest_submission', total_records=get_number_of_surveys(self.db, email) ) self.write(result)
def _base_query(table: Table, email: str, selected: list, where: BinaryExpression=None) -> Select: """ Return a query for a DataTable without any text filtering, ordering, or limiting applied. :param table: the SQLAlchemy table. Should be one or more tables joined with the auth_user table :param email: the user's e-mail address :param selected: the columns to select from the table :param where: an optional WHERE clause to apply to the query :return: the query object """ # Selected columns (but not aggregate functions) must also appear in the # GROUP BY clause grouped = (column for column in selected if type(column) is Column) query = select( # The extra column is for the DataTable recordsFiltered attribute. # It represents the number of records found before applying a sql LIMIT selected + [count().over().label('filtered')] ).select_from( table ).group_by( *grouped ).where( auth_user_table.c.email == email ) if where is not None: query = query.where(where) return query
def set_jcmt_options(self, proposal_id, target_of_opp, daytime, time_specific, polarimetry): """ Set the JCMT proposal options for a given proposal. """ values = { jcmt_options.c.target_of_opp: target_of_opp, jcmt_options.c.daytime: daytime, jcmt_options.c.time_specific: time_specific, jcmt_options.c.polarimetry: polarimetry, } with self._transaction() as conn: if 0 < conn.execute(select( [count(jcmt_options.c.proposal_id)]).where( jcmt_options.c.proposal_id == proposal_id)).scalar(): # Update existing options. result = conn.execute(jcmt_options.update().where( jcmt_options.c.proposal_id == proposal_id ).values(values)) if result.rowcount != 1: raise ConsistencyError( 'no rows matched updating JCMT options') else: # Add new options record. values.update({ jcmt_options.c.proposal_id: proposal_id, }) conn.execute(jcmt_options.insert().values(values))
def execute(self, message, user, params): alliance = Alliance.load(params.group(1)) if alliance is None: message.reply("No alliance matching '%s' found"%(params.group(1),)) return Q = session.query(sum(Planet.value), sum(Planet.score), sum(Planet.size), sum(Planet.xp), count(), Planet.race) Q = Q.join(Planet.intel) Q = Q.filter(Planet.active == True) Q = Q.filter(Intel.alliance==alliance) Q = Q.group_by(Intel.alliance_id, Planet.race) Q = Q.order_by(asc(Planet.race)) result = Q.all() if len(result) < 1: message.reply("No planets in intel match alliance %s"%(alliance.name,)) return prev=[] for value, score, size, xp, members, race in result: reply="%s %s Val(%s)" % (members,race,self.num2short(value/members),) reply+=" Score(%s)" % (self.num2short(score/members),) reply+=" Size(%s) XP(%s)" % (size/members,self.num2short(xp/members),) prev.append(reply) reply="Demographics for %s: "%(alliance.name,)+ ' | '.join(prev) message.reply(reply)
def get_synthesis_contributors(self, id_only=True): # author of important extracts from .idea_content_link import Extract from .auth import AgentProfile from .post import Post from sqlalchemy.sql.functions import count local_uri = AssemblQuadStorageManager.local_uri() discussion_storage = \ AssemblQuadStorageManager.discussion_storage_name() idea_uri = URIRef(self.uri(local_uri)) clause = '''select distinct ?annotation where { %s idea:includes* ?ideaP . ?annotation assembl:resourceExpressesIdea ?ideaP }''' extract_ids = [x for (x,) in self.db.execute( SparqlClause(clause % ( idea_uri.n3(),), quad_storage=discussion_storage.n3()))] r = list(self.db.query(AgentProfile.id, count(Extract.id)).join( Post, Post.creator_id==AgentProfile.id).join(Extract).filter( Extract.important == True, Extract.id.in_(extract_ids))) r.sort(key=lambda x: x[1], reverse=True) if id_only: return [AgentProfile.uri_generic(a) for (a, ce) in r] else: ids = [a for (a, ce) in r] order = {id: order for (order, id) in enumerate(ids)} agents = self.db.query(AgentProfile).filter(AgentProfile.id.in_(ids)).all() agents.sort(key=lambda a: order[a.id]) return agents
def get_recent_weapons(player_id): """ Returns the weapons that have been used in the past 90 days and also used in 5 games or more. """ cutoff = datetime.datetime.utcnow() - datetime.timedelta(days=90) recent_weapons = [] for weapon in DBSession.query(PlayerWeaponStat.weapon_cd, func.count()).\ filter(PlayerWeaponStat.player_id == player_id).\ filter(PlayerWeaponStat.create_dt > cutoff).\ group_by(PlayerWeaponStat.weapon_cd).\ having(func.count() > 4).\ all(): recent_weapons.append(weapon[0]) return recent_weapons
def mostFrequentVariableAndValue(self, variableNameList): """ :type variableNameList: list(str) """ subQueryList = [] if len(variableNameList) == 0: raise EmptyVariableNameListError() with closing(self._sessionMaker()) as session: # For each variable, retrieve all possible values and their occurrence count. for variableName in variableNameList: variableNameColumn = literal(variableName).label(self._VARIABLE_NAME_KEY) variableValueColumn = getattr(SQLModsecurityAuditEntryMessage, variableName).label(self._VARIABLE_VALUE_KEY) variableValueCountColumn = count().label(self._VARIABLE_VALUE_COUNT_KEY) # Subquery of each variable. subQuery = self._makeQuery(session, [variableNameColumn, variableValueColumn, variableValueCountColumn]) subQuery = subQuery.group_by(self._VARIABLE_NAME_KEY, self._VARIABLE_VALUE_KEY) subQueryList.append(subQuery) # Merging all subqueries and sorting by reverse count... query = union(*subQueryList).order_by(desc(self._VARIABLE_VALUE_COUNT_KEY)).limit(1) query = query.order_by(desc(self._VARIABLE_VALUE_COUNT_KEY)).limit(1) # ... then picking the first one. item = session.execute(query).fetchone() if item is not None: return {str(item.variableName): item.variableValue} else: return None
def _samp_compute_up(t, s, **kwargs): if t.n is not None: limit = t.n else: limit = sa.select([safuncs.count() * t.frac], from_obj=s.alias()).as_scalar() return s.order_by(safuncs.random()).limit(limit)
def query_count(cls, phone=None, **kwargs): q = session.query(count(cls.reply_id)) if phone is not None: q = q.filter(cls.phone_number == phone) return q.scalar()
def get_stats(connection: Connection, survey_id: str, email: str) -> dict: """ Get statistics about the specified survey: creation time, number of submissions, time of the earliest submission, and time of the latest submission. :param connection: a SQLAlchemy Connection :param survey_id: the UUID of the survey :param email: the e-mail address of the user :return: a JSON representation of the statistics. """ result = connection.execute( select([ survey_table.c.created_on, count(submission_table.c.submission_id), sqlmin(submission_table.c.submission_time), sqlmax(submission_table.c.submission_time) ]).select_from( auth_user_table.join(survey_table).outerjoin(submission_table) ).where( survey_table.c.survey_id == survey_id ).where( auth_user_table.c.email == email ).group_by( survey_table.c.survey_id ) ).first() return json_response({ 'created_on': maybe_isoformat(result[0]), 'num_submissions': result[1], 'earliest_submission_time': maybe_isoformat(result[2]), 'latest_submission_time': maybe_isoformat(result[3]) })
def get_taxa_photo_count(session, metadata): """Return the photo count for each (genus, section, species) combination. Taxa are returned as 4-tuples ``(genus, section, species, photo_count)``. """ Base = automap_base(metadata=metadata) Base.prepare() configure_mappers() Photo = Base.classes.photos Taxon = Base.classes.taxa Rank = Base.classes.ranks stmt_genus = session.query(Photo.id, Taxon.name.label('genus')).\ join(Photo.taxa_collection, Taxon.ranks).\ filter(Rank.name == 'genus').subquery() stmt_section = session.query(Photo.id, Taxon.name.label('section')).\ join(Photo.taxa_collection, Taxon.ranks).\ filter(Rank.name == 'section').subquery() stmt_species = session.query(Photo.id, Taxon.name.label('species')).\ join(Photo.taxa_collection, Taxon.ranks).\ filter(Rank.name == 'species').subquery() q = session.query('genus', 'section', 'species', functions.count(Photo.id).label('photos')).\ select_from(Photo).\ join(stmt_genus, stmt_genus.c.id == Photo.id).\ outerjoin(stmt_section, stmt_section.c.id == Photo.id).\ join(stmt_species, stmt_species.c.id == Photo.id).\ group_by('genus', 'section', 'species') return q
def main(): logging.basicConfig( level=logging.INFO, format="%(asctime)s %(levelname)s %(name)s: %(message)s", ) try: if len(sys.argv) == 2: from_time = datetime.fromisoformat(sys.argv[2]) elif len(sys.argv) == 1: from_time = None else: raise ValueError except ValueError: print( "Usage:\n guess_imports.py\n" + " guess_imports.py <since-isodate>", file=sys.stderr, ) sys.exit(2) # TODO: Only go over projects that have a download more recent than given date with database.connect() as db: # Count projects total_projects, = db.execute( sqlalchemy.select([functions.count() ]).select_from(database.projects)).one() # List versions done_projects = 0 for project_name, versions in iter_project_versions(db): process_versions(project_name, versions) done_projects += 1 if done_projects % 100 == 0: logger.info("%d / %d", done_projects, total_projects)
def _delete(self, s): start, finish = self._start_finish() # we delete the intervals that the statistics depend on and they will cascade for repeat in range(2): if repeat: q = s.query(Interval) else: q = s.query(count(Interval.id)) q = q.filter(Interval.owner == self.owner_out) if start: q = q.filter(Interval.finish > start) if finish: q = q.filter(Interval.start < finish) if repeat: for interval in q.all(): log.debug('Deleting %s' % interval) s.delete(interval) else: n = q.scalar() if n: log.warning('Deleting %d intervals' % n) else: log.warning('No intervals to delete') s.commit()
def _process_next(self, wid): conn = self.thread.conn with conn.begin() as trans: # check if way already has a virtual_id vid = conn.scalar( select([sqlf.count()]).where(self.data.c.child == wid)) # if it has an id, it is already done. Otherwise do search if vid > 0: return row = conn.execute(self.master.data.select().where( self.master.data.c.id == wid)).first() properties = [row[name] for name in self.rows] merge_list = self._get_all_adjacent_way_ids(wid, properties, conn) # only insert ways that have neighbours if len(merge_list) > 1: conn.execute(self.data.insert(), [{ 'virtual_id': wid, 'child': x } for x in merge_list])
def update(self, engine): with engine.begin() as conn: # XXX do we need this first delete? t = self.data conn.execute(t.delete().where( t.c.child.in_(self.way_table.select_modify_delete()))) tin = self.data.alias() lonely = select([tin.c.virtual_id])\ .group_by(tin.c.virtual_id)\ .having(sqlf.count(text('1')) < 2) conn.execute(t.delete().where(t.c.virtual_id.in_(lonely))) # the worker threads workers = self.create_worker_queue(engine, self._process_next) idcol = self.master.data.c.id cur = engine.execute( select([idcol ]).where(idcol.in_(self.way_table.select_add_modify()))) for obj in cur: workers.add_task(obj[0]) workers.finish()
def count_json(uid): """Count the number of JSONs a user has access to :param uid: :return: int """ try: # get json through user mapping path query_user_json = db.session.query( Json.id).join(JsonAccessMap).filter(JsonAccessMap.user == uid) # get json through team mapping path teams = db.session.query( Team.id).join(TeamMemberMap).filter(TeamMemberMap.user == uid) query_team_json = db.session.query( Json.id).join(TeamJsonMap).filter(TeamJsonMap.team.in_(teams)) # count distinct json in team and user path json_count = db.session.query(count(Json.id.distinct()))\ .filter(or_(Json.id.in_(query_user_json), Json.id.in_(query_team_json))).scalar() return json_count except Exception as e: logging.error(e) raise
def _delete_from(self, s, start=None, inclusive=True): composite_ids = s.query(Composite.id). \ join(StatisticJournal, Composite.id == StatisticJournal.source_id). \ join(StatisticName, StatisticJournal.statistic_name_id == StatisticName.id). \ filter(StatisticName.owner == self.owner_out) if start: if inclusive: composite_ids = composite_ids.filter( StatisticJournal.time >= start) else: composite_ids = composite_ids.filter( StatisticJournal.time > start) log.debug(f'Delete query: {composite_ids}') n = s.query(count(Source.id)). \ filter(Source.id.in_(composite_ids)). \ scalar() if n: log.warning( f'Deleting {n} Composite sources ({start} onwards{" inclusive" if inclusive else ""})' ) s.query(Source). \ filter(Source.id.in_(composite_ids)). \ delete(synchronize_session=False) s.commit()
def get_flight_by_id(idFlight): airport_1 = aliased(Airport) airport_2 = aliased(Airport) airport_3 = aliased(Airport) flight = Schedule.query.join(airport_1, Schedule.departure == airport_1.idAirport) \ .join(airport_2, Schedule.arrival == airport_2.idAirport) \ .join(Plane, Schedule.idPlane == Plane.idPlane) \ .join(Ticket, Schedule.idFlight == Ticket.idFlight) \ .filter(Schedule.idFlight == idFlight)\ .add_columns(Schedule.idFlight, airport_1.name.label("departure_airport"), airport_2.name.label("arrival_airport"), airport_1.locate.label("departure_locate"), airport_2.locate.label("arrival_locate"), Schedule.departureDate.label("departure_date"), Schedule.departureTime.label("departure_time"), Plane.idPlane, Ticket.is_empty, count(Ticket.idTicket).label("empty_seats")) \ .group_by(Schedule.idFlight) \ .order_by(desc(Schedule.departureDate)).first() return flight
def get_synthesis_contributors(self, id_only=True): # author of important extracts from .idea_content_link import Extract from .auth import AgentProfile from .post import Post from .generic import Content from sqlalchemy.sql.functions import count subquery = self.get_descendants_query(self.id) query = self.db.query( Post.creator_id ).join(Extract ).join(subquery, Extract.idea_id == subquery.c.id ).filter(Extract.important == True ).group_by(Post.creator_id ).order_by(count(Extract.id).desc()) if id_only: return [AgentProfile.uri_generic(a) for (a,) in query] else: ids = [x for (x,) in query] if not ids: return [] agents = {a.id: a for a in self.db.query(AgentProfile).filter( AgentProfile.id.in_(ids))} return [agents[id] for id in ids]
def test_bug(self): ''' so why does this work, without 'recursive'? EDIT: the 'recursive' is optional in sqlite! see the very last line at https://www.sqlite.org/lang_with.html damn. and all that trouble to make a nice bug report. ''' q_counts = self.session.query(Node.id.label('id'), count(Connect.input_id).label('count')). \ outerjoin(Connect, Node.id == Connect.output_id). \ group_by(Node.id).order_by(Node.id).subquery() q_missing = self.session.query(Node.id.label('id')). \ join(q_counts, q_counts.c.id == Node.id). \ filter(Node.n_input != q_counts.c.count).cte() q_missing = q_missing.union_all( self.session.query(Node.id).join( Connect, Node.id == Connect.output_id).join( q_missing, Connect.input_id == q_missing.c.id)) print('\nbug\n%s\n' % q_missing.select()) self.assertEqual([(5, ), (7, ), (8, )], self.session.query(Node.id).filter( Node.id.in_(q_missing.select())).order_by( Node.id).all())
async def test_loader_with_aggregation(user): count_col = count().label('count') user_count = select( [User.team_id, count_col] ).group_by( User.team_id ).alias() query = Team.outerjoin(user_count).select() result = await query.gino.load( (Team.id, Team.name, user_count.columns.team_id, count_col) ).all() assert len(result) == 2 # team 1 doesn't have users, team 2 has 1 user # third and forth columns are None for team 1 for team_id, team_name, user_team_id, user_count in result: if team_id == user.team_id: assert team_name == user.team.name assert user_team_id == user.team_id assert user_count == 1 else: assert team_id is not None assert team_name is not None assert user_team_id is None assert user_count is None
'pages_count': 240 } engine.connect().execute(insert_stmt, data) session = Session(bind=engine) q = session.query(Book).filter(Book.title == 'Essential SQLAlchemy') print q book = q.one() print(book.id, book.title) author = Author(name='Rick Copeland') author.books.append(book) session.add(book) session.flush() #### # select CASE WHEN (BOOK.pages_count > 200) THEN 1 ELSE 0 END is_novel, count(*) # from BOOK # group by CASE WHEN (BOOK.pages_count > 200) THEN 1 ELSE 0 END # order by CASE WHEN (BOOK.pages_count > 200) THEN 1 ELSE 0 END # is_novel_column = case([(Book.pages_count > 200, 1)], else_=0) novel_query = session.query(is_novel_column.label('is_alias'), count()).\ group_by(is_novel_column).\ order_by(is_novel_column) print novel_query print novel_query.all() session.close()
def get_posts(request): """ Query interface on posts Filters have two forms: only_*, is for filters that cannot be reversed (ex: only_synthesis, only_orphan) is_*, is for filters that can be reversed (ex:is_unread=true returns only unread message, is_unread=false returns only read messages) order: can be chronological, reverse_chronological, popularity root_post_id: all posts below the one specified. family_post_id: all posts below the one specified, and all its ancestors. post_reply_to: replies to a given post root_idea_id: all posts associated with the given idea ids: explicit message ids. posted_after_date, posted_before_date: date selection (ISO format) post_author: filter by author classifier: filter on message_classifier, or absence thereof (classifier=null). Can be negated with "!" """ localizer = request.localizer discussion_id = int(request.matchdict['discussion_id']) discussion = Discussion.get(int(discussion_id)) if not discussion: raise HTTPNotFound( localizer.translate(_("No discussion found with id=%s")) % discussion_id) discussion.import_from_sources() user_id = authenticated_userid(request) or Everyone permissions = get_permissions(user_id, discussion_id) DEFAULT_PAGE_SIZE = 25 page_size = DEFAULT_PAGE_SIZE filter_names = [ filter_name for filter_name in request.GET.getone('filters').split(',') if filter_name ] if request.GET.get('filters') else [] try: page = int(request.GET.getone('page')) except (ValueError, KeyError): page = 1 text_search = request.GET.get('text_search', None) order = request.GET.get('order') if order is None: order = 'chronological' assert order in ('chronological', 'reverse_chronological', 'score', 'popularity') if order == 'score': assert text_search is not None if page < 1: page = 1 root_post_id = request.GET.getall('root_post_id') if root_post_id: root_post_id = get_database_id("Post", root_post_id[0]) family_post_id = request.GET.getall('family_post_id') if family_post_id: family_post_id = get_database_id("Post", family_post_id[0]) root_idea_id = request.GET.getall('root_idea_id') if root_idea_id: root_idea_id = get_database_id("Idea", root_idea_id[0]) ids = request.GET.getall('ids[]') if ids: ids = [get_database_id("Post", id) for id in ids] view_def = request.GET.get('view') or 'default' only_synthesis = request.GET.get('only_synthesis') post_author_id = request.GET.get('post_author') if post_author_id: post_author_id = get_database_id("AgentProfile", post_author_id) assert AgentProfile.get( post_author_id ), "Unable to find agent profile with id " + post_author_id post_replies_to = request.GET.get('post_replies_to') if post_replies_to: post_replies_to = get_database_id("AgentProfile", post_replies_to) assert AgentProfile.get( post_replies_to ), "Unable to find agent profile with id " + post_replies_to posted_after_date = request.GET.get('posted_after_date') posted_before_date = request.GET.get('posted_before_date') message_classifiers = request.GET.getall('classifier') PostClass = SynthesisPost if only_synthesis == "true" else Post if order == 'score': posts = discussion.db.query(PostClass, Content.body_text_index.score_name) else: posts = discussion.db.query(PostClass) posts = posts.filter(PostClass.discussion_id == discussion_id, ) ##no_of_posts_to_discussion = posts.count() post_data = [] # True means deleted only, False (default) means non-deleted only. None means both. # v0 # deleted = request.GET.get('deleted', None) # end v0 # v1: we would like something like that # deleted = request.GET.get('deleted', None) # if deleted is None: # if view_def == 'id_only': # deleted = None # else: # deleted = False # end v1 # v2 # deleted = request.GET.get('deleted', None) # if deleted is None: # if not ids: # deleted = False # else: # deleted = None # # if deleted == 'false': # deleted = False # posts = posts.filter(PostClass.tombstone_condition()) # elif deleted == 'true': # deleted = True # posts = posts.filter(PostClass.not_tombstone_condition()) # elif deleted == 'any': # deleted = None # # result will contain deleted and non-deleted posts # pass # end v2 # v3 # deleted = request.GET.get('deleted', None) # if deleted is None: # if not ids: # deleted = False # else: # deleted = None # if deleted == 'true': # deleted = True # posts = posts.filter(PostClass.not_tombstone_condition()) # end v3 # v4 deleted = request.GET.get('deleted', None) if deleted is None: if not ids: deleted = False else: deleted = None elif deleted.lower() == "any": deleted = None else: deleted = asbool(deleted) # if deleted is not in (False, True, None): # deleted = False # end v4 only_orphan = asbool(request.GET.get('only_orphan', False)) if only_orphan: if root_idea_id: raise HTTPBadRequest( localizer.translate( _("Getting orphan posts of a specific idea isn't supported." ))) orphans = Idea._get_orphan_posts_statement( discussion_id, True, include_deleted=deleted).subquery("orphans") posts = posts.join(orphans, PostClass.id == orphans.c.post_id) if root_idea_id: related = Idea.get_related_posts_query_c(discussion_id, root_idea_id, True, include_deleted=deleted) posts = posts.join(related, PostClass.id == related.c.post_id) elif not only_orphan: if deleted is not None: if deleted: posts = posts.filter( PostClass.publication_state.in_( deleted_publication_states)) else: posts = posts.filter(PostClass.tombstone_date == None) if root_post_id: root_post = Post.get(root_post_id) posts = posts.filter((Post.ancestry.like(root_post.ancestry + cast(root_post.id, String) + ',%')) | (PostClass.id == root_post.id)) elif family_post_id: root_post = Post.get(family_post_id) ancestor_ids = root_post.ancestor_ids() posts = posts.filter((Post.ancestry.like(root_post.ancestry + cast(root_post.id, String) + ',%')) | (PostClass.id == root_post.id) | (PostClass.id.in_(ancestor_ids))) else: root_post = None if ids: posts = posts.filter(Post.id.in_(ids)) if posted_after_date: posted_after_date = parse_datetime(posted_after_date) if posted_after_date: posts = posts.filter(PostClass.creation_date >= posted_after_date) #Maybe we should do something if the date is invalid. benoitg if posted_before_date: posted_before_date = parse_datetime(posted_before_date) if posted_before_date: posts = posts.filter(PostClass.creation_date <= posted_before_date) #Maybe we should do something if the date is invalid. benoitg if post_author_id: posts = posts.filter(PostClass.creator_id == post_author_id) if message_classifiers: if any([len(classifier) == 0 for classifier in message_classifiers]): return {'total': 0, 'posts': []} polarities = [ classifier[0] != "!" for classifier in message_classifiers ] polarity = all(polarities) if not polarity: message_classifiers = [c.strip("!") for c in message_classifiers] if polarity != any(polarities): raise HTTPBadRequest( _("Do not combine negative and positive classifiers")) # Treat null as no classifier includes_null = 'null' in message_classifiers if includes_null: message_classifiers_nonull = filter(lambda c: c != "null", message_classifiers) if polarity: if len(message_classifiers) == 1: term = PostClass.message_classifier == ( None if includes_null else message_classifiers[0]) else: term = PostClass.message_classifier.in_( message_classifiers_nonull) if includes_null: term = term | (PostClass.message_classifier == None) else: if len(message_classifiers) == 1: term = PostClass.message_classifier != ( None if includes_null else message_classifiers[0]) else: term = PostClass.message_classifier.notin_( message_classifiers_nonull) if not includes_null: term = term | (PostClass.message_classifier == None) posts = posts.filter(term) if post_replies_to: parent_alias = aliased(PostClass) posts = posts.join(parent_alias, PostClass.parent) posts = posts.filter(parent_alias.creator_id == post_replies_to) # Post read/unread management is_unread = request.GET.get('is_unread') translations = None if user_id != Everyone: # This is horrible, but the join creates complex subqueries that # virtuoso cannot decode properly. read_posts = { v.post_id for v in discussion.db.query(ViewPost).filter( ViewPost.tombstone_condition(), ViewPost.actor_id == user_id, *ViewPost.get_discussion_conditions(discussion_id)) } my_sentiments = { l.post_id: l for l in discussion.db.query(SentimentOfPost).filter( SentimentOfPost.tombstone_condition(), SentimentOfPost.actor_id == user_id, *SentimentOfPost.get_discussion_conditions(discussion_id)) } if is_unread != None: posts = posts.outerjoin( ViewPost, and_(ViewPost.actor_id == user_id, ViewPost.post_id == PostClass.id, ViewPost.tombstone_date == None)) if is_unread == "true": posts = posts.filter(ViewPost.id == None) elif is_unread == "false": posts = posts.filter(ViewPost.id != None) user = AgentProfile.get(user_id) service = discussion.translation_service() if service: translations = PrefCollectionTranslationTable( service, LanguagePreferenceCollection.getCurrent(request)) else: #If there is no user_id, all posts are always unread my_sentiments = {} if is_unread == "false": raise HTTPBadRequest( localizer.translate( _("You must be logged in to view which posts are read"))) if text_search is not None: # another Virtuoso bug: offband kills score. but it helps speed. offband = () if (order == 'score') else None posts = posts.filter( Post.body_text_index.contains(text_search.encode('utf-8'), offband=offband)) # posts = posts.options(contains_eager(Post.source)) # Horrible hack... But useful for structure load if view_def == 'id_only': pass # posts = posts.options(defer(Post.body)) else: ideaContentLinkQuery = posts.with_entities( PostClass.id, PostClass.idea_content_links_above_post) ideaContentLinkCache = dict(ideaContentLinkQuery.all()) # Note: we could count the like the same way and kill the subquery. # But it interferes with the popularity order, # and the benefit is not that high. sentiment_counts = discussion.db.query( PostClass.id, SentimentOfPost.type, count(SentimentOfPost.id)).join(SentimentOfPost).filter( PostClass.id.in_(posts.with_entities(PostClass.id).subquery()), SentimentOfPost.tombstone_condition()).group_by( PostClass.id, SentimentOfPost.type) sentiment_counts_by_post_id = defaultdict(dict) for (post_id, sentiment_type, sentiment_count) in sentiment_counts: sentiment_counts_by_post_id[post_id][sentiment_type[ SentimentOfPost.TYPE_PREFIX_LEN:]] = sentiment_count posts = posts.options( # undefer(Post.idea_content_links_above_post), joinedload_all(Post.creator), joinedload_all(Post.extracts), joinedload_all(Post.widget_idea_links), joinedload_all(SynthesisPost.publishes_synthesis), subqueryload_all(Post.attachments)) if len(discussion.discussion_locales) > 1: posts = posts.options(*Content.subqueryload_options()) else: posts = posts.options(*Content.joinedload_options()) if order == 'chronological': posts = posts.order_by(Content.creation_date) elif order == 'reverse_chronological': posts = posts.order_by(Content.creation_date.desc()) elif order == 'score': posts = posts.order_by(Content.body_text_index.score_name.desc()) elif order == 'popularity': # assume reverse chronological otherwise posts = posts.order_by(Content.disagree_count - Content.like_count, Content.creation_date.desc()) else: posts = posts.order_by(Content.id) # print str(posts) no_of_posts = 0 no_of_posts_viewed_by_user = 0 if deleted is True: # We just got deleted posts, now we want their ancestors for context post_ids = set() ancestor_ids = set() def add_ancestors(post): post_ids.add(post.id) ancestor_ids.update( [int(x) for x in post.ancestry.strip(",").split(",") if x]) posts = list(posts) for post in posts: add_ancestors(post) ancestor_ids -= post_ids if ancestor_ids: ancestors = discussion.db.query(PostClass).filter( PostClass.id.in_(ancestor_ids)) if view_def == 'id_only': pass # ancestors = ancestors.options(defer(Post.body)) else: ancestors = ancestors.options( # undefer(Post.idea_content_links_above_post), joinedload_all(Post.creator), joinedload_all(Post.extracts), joinedload_all(Post.widget_idea_links), joinedload_all(SynthesisPost.publishes_synthesis), subqueryload_all(Post.attachments)) if len(discussion.discussion_locales) > 1: ancestors = ancestors.options( *Content.subqueryload_options()) else: ancestors = ancestors.options( *Content.joinedload_options()) posts.extend(ancestors.all()) for query_result in posts: score, viewpost = None, None if not isinstance(query_result, (list, tuple)): query_result = [query_result] post = query_result[0] if deleted is True: add_ancestors(post) if user_id != Everyone: viewpost = post.id in read_posts if view_def != "id_only": translate_content(post, translation_table=translations, service=service) no_of_posts += 1 serializable_post = post.generic_json(view_def, user_id, permissions) or {} if order == 'score': score = query_result[1] serializable_post['score'] = score if viewpost: serializable_post['read'] = True no_of_posts_viewed_by_user += 1 elif user_id != Everyone and root_post is not None and root_post.id == post.id: # Mark post read, we requested it explicitely viewed_post = ViewPost(actor_id=user_id, post=root_post) discussion.db.add(viewed_post) serializable_post['read'] = True else: serializable_post['read'] = False my_sentiment = my_sentiments.get(post.id, None) if my_sentiment is not None: my_sentiment = my_sentiment.generic_json('default', user_id, permissions) serializable_post['my_sentiment'] = my_sentiment if view_def != "id_only": serializable_post['indirect_idea_content_links'] = ( post.indirect_idea_content_links_with_cache( ideaContentLinkCache.get(post.id, None))) serializable_post[ 'sentiment_counts'] = sentiment_counts_by_post_id[post.id] post_data.append(serializable_post) # Benoitg: For now, this completely garbles threading without intelligent #handling of pagination. Disabling #posts = posts.limit(page_size).offset(data['startIndex']-1) # This code isn't up to date. If limiting the query by page, we need to # calculate the counts with a separate query to have the right number of # results #no_of_messages_viewed_by_user = discussion.db.query(ViewPost).join( # Post #).filter( # Post.discussion_id == discussion_id, # ViewPost.actor_id == user_id, #).count() if user_id else 0 data = {} data["page"] = page data["unread"] = no_of_posts - no_of_posts_viewed_by_user data["total"] = no_of_posts data["maxPage"] = max(1, ceil(float(data["total"]) / page_size)) #TODO: Check if we want 1 based index in the api data["startIndex"] = (page_size * page) - (page_size - 1) if data["page"] == data["maxPage"]: data["endIndex"] = data["total"] else: data["endIndex"] = data["startIndex"] + (page_size - 1) data["posts"] = post_data return data
def list(self, where=None): """Return a list of instances of this model. Given a model class, build up the ORM query based on query params and return the query result. """ self.session.flush() model_cls = self.resource_type query = self.session.query(model_cls, count().over()) limit = self._query_arg('limit', int) offset = self._query_arg('offset', int) deleted = self._query_arg('show_deleted', bool, False) search_term = self._query_arg('search') regex = self._query_arg('regex', bool, False) search_fields = self._query_arg('search_fields', list, default=['title']) search_lang = self._query_arg('lang') default_sort = ['{}:ASC'.format(self.default_sort_column_name)] order_by_text = (element.split(':') for element in self._query_arg( 'order_by', list, default=default_sort)) type_constraint = self._query_arg('type') user_id = self._query_arg('user_id') num_total = self.session.query(func.count(self.resource_type.id)) if user_id is not None: if model_cls is Submission: num_total = num_total.join(Survey.submissions) num_total = (num_total.outerjoin(_administrator_table).filter( administrator_filter(user_id))) num_total = num_total.scalar() if search_term is not None: for search_field in search_fields: query = column_search( query, model_cls=model_cls, column_name=search_field, search_term=search_term, language=search_lang, regex=regex, ) if user_id is not None: if model_cls is Submission: query = query.join(Survey.submissions) query = (query.outerjoin(_administrator_table).filter( administrator_filter(user_id))) if not deleted: query = query.filter(~model_cls.deleted) if type_constraint is not None: query = query.filter(model_cls.type_constraint == type_constraint) if where is not None: query = query.filter(where) for attribute_name, direction in order_by_text: try: order = getattr(model_cls, attribute_name) except AttributeError: order = text('{} {} NULLS LAST'.format(attribute_name, direction)) else: directions = {'asc': order.asc, 'desc': order.desc} order = directions[direction.lower()]().nullslast() query = query.order_by(order) if limit is not None: query = query.limit(limit) if offset is not None: query = query.offset(offset) result = query.all() if result: num_filtered = result[0][1] models = [res[0] for res in result] result = self._specific_fields(models, is_detail=False) return num_filtered, num_total, result return 0, num_total, []
def _map_info_data(request): map_id = request.matchdict['id'] try: leaderboard_lifetime = int( request.registry.settings['xonstat.leaderboard_lifetime']) except: leaderboard_lifetime = 30 leaderboard_count = 10 recent_games_count = 20 try: gmap = DBSession.query(Map).filter_by(map_id=map_id).one() # recent games on this map recent_games = DBSession.query(Game, Server, Map, PlayerGameStat).\ filter(Game.server_id==Server.server_id).\ filter(Game.map_id==Map.map_id).\ filter(Game.map_id==map_id).\ filter(PlayerGameStat.game_id==Game.game_id).\ filter(PlayerGameStat.rank==1).\ order_by(expr.desc(Game.start_dt)).all()[0:recent_games_count] # top players by score top_scorers = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.score)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.score))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_scorers = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_scorers] # top players by playing time top_players = DBSession.query(Player.player_id, Player.nick, func.sum(PlayerGameStat.alivetime)).\ filter(Player.player_id == PlayerGameStat.player_id).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(Game.map_id == map_id).\ filter(Player.player_id > 2).\ filter(PlayerGameStat.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.sum(PlayerGameStat.alivetime))).\ group_by(Player.nick).\ group_by(Player.player_id).all()[0:leaderboard_count] top_players = [(player_id, html_colors(nick), score) \ for (player_id, nick, score) in top_players] # top servers using/playing this map top_servers = DBSession.query(Server.server_id, Server.name, func.count(Game.game_id)).\ filter(Game.server_id == Server.server_id).\ filter(Game.map_id == map_id).\ filter(Game.create_dt > (datetime.utcnow() - timedelta(days=leaderboard_lifetime))).\ order_by(expr.desc(func.count(Game.game_id))).\ group_by(Server.name).\ group_by(Server.server_id).all()[0:leaderboard_count] except Exception as e: gmap = None return { 'gmap': gmap, 'recent_games': recent_games, 'top_scorers': top_scorers, 'top_players': top_players, 'top_servers': top_servers, }
def progress_reporter(test_id, token): """ Handle the progress of a certain test after validating the token. If necessary, update the status on GitHub. :param test_id: The id of the test to update. :type test_id: int :param token: The token to check the validity of the request. :type token: str :return: Nothing. :rtype: None """ from run import config, log # Verify token test = Test.query.filter(Test.id == test_id).first() if test is not None and test.token == token: repo_folder = config.get('SAMPLE_REPOSITORY', '') if 'type' in request.form: if request.form['type'] == 'progress': # Progress, log status = TestStatus.from_string(request.form['status']) # Check whether test is not running previous status again istatus = TestStatus.progress_step(status) message = request.form['message'] if len(test.progress) != 0: laststatus = TestStatus.progress_step( test.progress[-1].status) if laststatus in [ TestStatus.completed, TestStatus.canceled ]: return "FAIL" if laststatus > istatus: status = TestStatus.canceled message = "Duplicate Entries" progress = TestProgress(test.id, status, message) g.db.add(progress) g.db.commit() gh = GitHub(access_token=g.github['bot_token']) repository = gh.repos(g.github['repository_owner'])( g.github['repository']) # Store the test commit for testing in case of commit if status == TestStatus.completed: commit_name = 'fetch_commit_' + test.platform.value commit = GeneralData.query.filter( GeneralData.key == commit_name).first() fetch_commit = Test.query.filter( and_(Test.commit == commit.value, Test.platform == test.platform)).first() if test.test_type == TestType.commit and test.id > fetch_commit.id: commit.value = test.commit g.db.commit() # If status is complete, remove the Kvm entry if status in [TestStatus.completed, TestStatus.canceled]: log.debug("Test {id} has been {status}".format( id=test_id, status=status)) var_average = 'average_time_' + test.platform.value current_average = GeneralData.query.filter( GeneralData.key == var_average).first() average_time = 0 total_time = 0 if current_average is None: platform_tests = g.db.query(Test.id).filter( Test.platform == test.platform).subquery() finished_tests = g.db.query( TestProgress.test_id).filter( and_( TestProgress.status.in_([ TestStatus.canceled, TestStatus.completed ]), TestProgress.test_id.in_( platform_tests))).subquery() in_progress_statuses = [ TestStatus.preparation, TestStatus.completed, TestStatus.canceled ] finished_tests_progress = g.db.query( TestProgress).filter( and_( TestProgress.test_id.in_(finished_tests), TestProgress.status.in_( in_progress_statuses))).subquery() times = g.db.query( finished_tests_progress.c.test_id, label( 'time', func.group_concat( finished_tests_progress.c.timestamp)) ).group_by(finished_tests_progress.c.test_id).all() for p in times: parts = p.time.split(',') start = datetime.datetime.strptime( parts[0], '%Y-%m-%d %H:%M:%S') end = datetime.datetime.strptime( parts[-1], '%Y-%m-%d %H:%M:%S') total_time += (end - start).total_seconds() if len(times) != 0: average_time = total_time // len(times) new_avg = GeneralData(var_average, average_time) g.db.add(new_avg) g.db.commit() else: all_results = TestResult.query.count() regression_test_count = RegressionTest.query.count() number_test = all_results / regression_test_count updated_average = float( current_average.value) * (number_test - 1) pr = test.progress_data() end_time = pr['end'] start_time = pr['start'] if end_time.tzinfo is not None: end_time = end_time.replace(tzinfo=None) if start_time.tzinfo is not None: start_time = start_time.replace(tzinfo=None) last_running_test = end_time - start_time updated_average = updated_average + last_running_test.total_seconds( ) current_average.value = updated_average // number_test g.db.commit() kvm = Kvm.query.filter(Kvm.test_id == test_id).first() if kvm is not None: log.debug("Removing KVM entry") g.db.delete(kvm) g.db.commit() # Post status update state = Status.PENDING target_url = url_for('test.by_id', test_id=test.id, _external=True) context = "CI - {name}".format(name=test.platform.value) if status == TestStatus.canceled: state = Status.ERROR message = 'Tests aborted due to an error; please check' elif status == TestStatus.completed: # Determine if success or failure # It fails if any of these happen: # - A crash (unexpected exit code) # - A not None value on the "got" of a TestResultFile ( # meaning the hashes do not match) crashes = g.db.query(count(TestResult.exit_code)).filter( and_(TestResult.test_id == test.id, TestResult.exit_code != TestResult.expected_rc)).scalar() results_zero_rc = g.db.query(RegressionTest.id).filter( RegressionTest.expected_rc == 0).subquery() results = g.db.query(count(TestResultFile.got)).filter( and_( TestResultFile.test_id == test.id, TestResultFile.regression_test_id.in_( results_zero_rc), TestResultFile.got.isnot(None))).scalar() log.debug( 'Test {id} completed: {crashes} crashes, {results} results' .format(id=test.id, crashes=crashes, results=results)) if crashes > 0 or results > 0: state = Status.FAILURE message = 'Not all tests completed successfully, please check' else: state = Status.SUCCESS message = 'Tests completed' update_build_badge(state, test) else: message = progress.message gh_commit = repository.statuses(test.commit) try: gh_commit.post(state=state, description=message, context=context, target_url=target_url) except ApiError as a: log.error( 'Got an exception while posting to GitHub! Message: {message}' .format(message=a.message)) if status in [TestStatus.completed, TestStatus.canceled]: # Start next test if necessary, on the same platform process = Process(target=start_platform, args=(g.db, repository, 60)) process.start() elif request.form['type'] == 'equality': log.debug('Equality for {t}/{rt}/{rto}'.format( t=test_id, rt=request.form['test_id'], rto=request.form['test_file_id'])) rto = RegressionTestOutput.query.filter( RegressionTestOutput.id == request.form['test_file_id']).first() if rto is None: # Equality posted on a file that's ignored presumably log.info('No rto for {test_id}: {test}'.format( test_id=test_id, test=request.form['test_id'])) else: result_file = TestResultFile(test.id, request.form['test_id'], rto.id, rto.correct) g.db.add(result_file) g.db.commit() elif request.form['type'] == 'logupload': log.debug("Received log file for test {id}".format(id=test_id)) # File upload, process if 'file' in request.files: uploaded_file = request.files['file'] filename = secure_filename(uploaded_file.filename) if filename is '': return 'EMPTY' temp_path = os.path.join(repo_folder, 'TempFiles', filename) # Save to temporary location uploaded_file.save(temp_path) final_path = os.path.join( repo_folder, 'LogFiles', '{id}{ext}'.format(id=test.id, ext='.txt')) os.rename(temp_path, final_path) log.debug("Stored log file") elif request.form['type'] == 'upload': log.debug('Upload for {t}/{rt}/{rto}'.format( t=test_id, rt=request.form['test_id'], rto=request.form['test_file_id'])) # File upload, process if 'file' in request.files: uploaded_file = request.files['file'] filename = secure_filename(uploaded_file.filename) if filename is '': return 'EMPTY' temp_path = os.path.join(repo_folder, 'TempFiles', filename) # Save to temporary location uploaded_file.save(temp_path) # Get hash and check if it's already been submitted hash_sha256 = hashlib.sha256() with open(temp_path, "rb") as f: for chunk in iter(lambda: f.read(4096), b""): hash_sha256.update(chunk) file_hash = hash_sha256.hexdigest() filename, file_extension = os.path.splitext(filename) final_path = os.path.join( repo_folder, 'TestResults', '{hash}{ext}'.format(hash=file_hash, ext=file_extension)) os.rename(temp_path, final_path) rto = RegressionTestOutput.query.filter( RegressionTestOutput.id == request.form['test_file_id']).first() result_file = TestResultFile(test.id, request.form['test_id'], rto.id, rto.correct, file_hash) g.db.add(result_file) g.db.commit() elif request.form['type'] == 'finish': log.debug('Finish for {t}/{rt}'.format( t=test_id, rt=request.form['test_id'])) regression_test = RegressionTest.query.filter( RegressionTest.id == request.form['test_id']).first() result = TestResult(test.id, regression_test.id, request.form['runTime'], request.form['exitCode'], regression_test.expected_rc) g.db.add(result) try: g.db.commit() except IntegrityError as e: log.error('Could not save the results: {msg}'.format( msg=e.message)) return "OK" return "FAIL"
def test_sources(self): with TemporaryDirectory() as f: args, data = bootstrap_dir(f, m(V), '5', configurator=acooke) with data.db.session_context() as s: # add a diary entry journal = add(s, DiaryTopicJournal(date='2018-09-29')) cache = journal.cache(s) diary = s.query(DiaryTopic).filter(DiaryTopic.title == 'Status').one() fields = diary.fields self.assertEqual(len(fields), 6, list(enumerate(map(str, fields)))) self.assertEqual(fields[0].statistic_name.name, 'notes') self.assertEqual(fields[1].statistic_name.name, 'weight', str(fields[1])) statistics = [cache[field] for field in fields] for statistic in statistics: self.assertTrue(statistic.value is None, statistics) statistics[0].value = 'hello world' statistics[1].value = 64.5 with data.db.session_context() as s: # check the diary entry was persisted journal = DiaryTopicJournal.get_or_add(s, '2018-09-29') cache = journal.cache(s) diary = s.query(DiaryTopic).filter(DiaryTopic.title == 'Status').one() fields = diary.fields self.assertEqual(len(fields), 6, list(enumerate(map(str, fields)))) self.assertEqual(fields[0].statistic_name.name, 'notes') self.assertEqual(fields[1].statistic_name.name, 'weight', str(fields[1])) statistics = [cache[field] for field in fields] self.assertEqual(statistics[1].value, 64.5) self.assertEqual(statistics[1].type, StatisticJournalType.FLOAT) # generate summary stats SummaryCalculator(data, schedule='m').run() SummaryCalculator(data, schedule='y').run() with data.db.session_context() as s: # check the summary stats diary = s.query(DiaryTopic).filter(DiaryTopic.title == 'Status').one() weights = s.query(StatisticJournal).join(StatisticName). \ filter(StatisticName.owner == diary, StatisticName.name == 'weight'). \ order_by(StatisticJournal.time).all() self.assertEqual(len(weights), 2) self.assertEqual(weights[1].value, 64.5) self.assertEqual(len(weights[1].measures), 2, weights[1].measures) self.assertEqual(weights[1].measures[0].rank, 1) self.assertEqual(weights[1].measures[0].percentile, 100, weights[1].measures[0].percentile) n = s.query(count(StatisticJournalFloat.id)).scalar() self.assertEqual(n, 4, n) n = s.query(count(StatisticJournalInteger.id)).scalar() self.assertEqual(n, 6, n) m_avg = s.query(StatisticJournalFloat).join(StatisticName). \ filter(StatisticName.name == 'avg-month-weight').one() self.assertEqual(m_avg.value, 64.5) y_avg = s.query(StatisticJournalFloat).join(StatisticName). \ filter(StatisticName.name == 'avg-year-weight').one() self.assertEqual(y_avg.value, 64.5) month = s.query(Interval).filter(Interval.schedule == 'm').first() self.assertEqual(month.start, to_date('2018-09-01'), month.start) self.assertEqual(month.finish, to_date('2018-10-01'), month.finish) with data.db.session_context() as s: # delete the diary entry journal = DiaryTopicJournal.get_or_add(s, '2018-09-29') s.delete(journal) with data.db.session_context() as s: # check the delete cascade self.assertEqual(s.query(count(DiaryTopicJournal.id)).scalar(), 1) # this should be zero because the Intervals were automatically deleted # (well, now +1 because there's an original default weight) for source in s.query(Source).all(): print(source) for journal in s.query(StatisticJournal).all(): print(journal) self.assertEqual(s.query(count(Source.id)).scalar(), 37, list(map(str, s.query(Source).all()))) # constants self.assertEqual(s.query(count(StatisticJournalText.id)).scalar(), 13, s.query(count(StatisticJournalText.id)).scalar()) self.assertEqual(s.query(count(StatisticJournal.id)).scalar(), 22, s.query(count(StatisticJournal.id)).scalar())
from abilian.core.entities import Entity from abilian.core.extensions import db from abilian.core.models.tag import Tag, entity_tag_tbl from abilian.i18n import _, _l, _n from abilian.services import get_service from abilian.services.indexing.service import index_update from abilian.web import url_for from abilian.web.admin import AdminPanel from abilian.web.views import ObjectEdit from abilian.web.views.base import View from .forms import TagForm logger = logging.getLogger(__name__) _OBJ_COUNT = func.count(entity_tag_tbl.c.entity_id).label("obj_count") def get_entities_for_reindex(tags): """Collect entities for theses tags.""" if isinstance(tags, Tag): tags = (tags, ) session = db.session() indexing = get_service("indexing") tbl = Entity.__table__ tag_ids = [t.id for t in tags] query = (sa.sql.select([tbl.c.entity_type, tbl.c.id]).select_from( tbl.join(entity_tag_tbl, entity_tag_tbl.c.entity_id == tbl.c.id)).where( entity_tag_tbl.c.tag_id.in_(tag_ids)))
def union_select(select_components, distinct=False, select_type=TRIPLE_SELECT): """ Helper function for building union all select statement. Args: select_components (iterable of tuples): Indicates the table and table type (table_name, where_clause_string, table_type) distinct (bool): Whether to eliminate duplicate results select_type (int): From `rdflib_sqlalchemy.constants`. Either `COUNT_SELECT`, `CONTEXT_SELECT`, `TRIPLE_SELECT` """ selects = [] for table, whereClause, tableType in select_components: if select_type == COUNT_SELECT: c = table.c if tableType == ASSERTED_TYPE_PARTITION: cols = [c.member, c.klass] elif tableType in (ASSERTED_LITERAL_PARTITION, ASSERTED_NON_TYPE_PARTITION, QUOTED_PARTITION): cols = [c.subject, c.predicate, c.object] else: raise ValueError( 'Unrecognized table type {}'.format(tableType)) select_clause = expression.select([ functions.count().label('aCount') ]).select_from( expression.select(cols, whereClause).distinct().select_from(table)) elif select_type == CONTEXT_SELECT: select_clause = expression.select([table.c.context], whereClause) elif tableType in FULL_TRIPLE_PARTITIONS: select_clause = table.select(whereClause) elif tableType == ASSERTED_TYPE_PARTITION: select_clause = expression.select([ table.c.id.label("id"), table.c.member.label("subject"), expression.literal(text_type(RDF.type)).label("predicate"), table.c.klass.label("object"), table.c.context.label("context"), table.c.termComb.label("termcomb"), expression.literal_column("NULL").label("objlanguage"), expression.literal_column("NULL").label("objdatatype") ], whereClause) elif tableType == ASSERTED_NON_TYPE_PARTITION: select_clause = expression.select([c for c in table.columns] + [ expression.literal_column("NULL").label("objlanguage"), expression.literal_column("NULL").label("objdatatype") ], whereClause, from_obj=[table]) selects.append(select_clause) order_statement = [] if select_type == TRIPLE_SELECT: order_statement = [ expression.literal_column("subject"), expression.literal_column("predicate"), expression.literal_column("object"), ] if distinct and select_type != COUNT_SELECT: return expression.union(*selects, **{"order_by": order_statement}) else: return expression.union_all(*selects, **{"order_by": order_statement})
def get_data(self, player_id): """Return player data as dict. This function is similar to the function in player.py but more optimized for this purpose. """ # total games # wins/losses # kills/deaths # duel/dm/tdm/ctf elo + rank player = DBSession.query(Player).filter( Player.player_id == player_id).one() games_played = DBSession.query( Game.game_type_cd, func.count(), func.sum(PlayerGameStat.alivetime)).\ filter(Game.game_id == PlayerGameStat.game_id).\ filter(PlayerGameStat.player_id == player_id).\ group_by(Game.game_type_cd).\ order_by(func.count().desc()).\ all() total_stats = {} total_stats['games'] = 0 total_stats['games_breakdown'] = { } # this is a dictionary inside a dictionary .. dictception? total_stats['games_alivetime'] = {} total_stats['gametypes'] = [] for (game_type_cd, games, alivetime) in games_played: total_stats['games'] += games total_stats['gametypes'].append(game_type_cd) total_stats['games_breakdown'][game_type_cd] = games total_stats['games_alivetime'][game_type_cd] = alivetime (total_stats['kills'], total_stats['deaths'], total_stats['alivetime'],) = DBSession.query( func.sum(PlayerGameStat.kills), func.sum(PlayerGameStat.deaths), func.sum(PlayerGameStat.alivetime)).\ filter(PlayerGameStat.player_id == player_id).\ one() (total_stats['wins'], total_stats['losses']) = DBSession.\ query("wins", "losses").\ from_statement( "SELECT SUM(win) wins, SUM(loss) losses " "FROM (SELECT g.game_id, " " CASE " " WHEN g.winner = pgs.team THEN 1 " " WHEN pgs.rank = 1 THEN 1 " " ELSE 0 " " END win, " " CASE " " WHEN g.winner = pgs.team THEN 0 " " WHEN pgs.rank = 1 THEN 0 " " ELSE 1 " " END loss " " FROM games g, " " player_game_stats pgs " " WHERE g.game_id = pgs.game_id " " AND pgs.player_id = :player_id) win_loss").\ params(player_id=player_id).one() ranks = DBSession.query("game_type_cd", "rank", "max_rank").\ from_statement( "SELECT pr.game_type_cd, pr.rank, overall.max_rank " "FROM player_ranks pr, " " (SELECT game_type_cd, max(rank) max_rank " " FROM player_ranks " " GROUP BY game_type_cd) overall " "WHERE pr.game_type_cd = overall.game_type_cd " " AND player_id = :player_id " "ORDER BY rank").\ params(player_id=player_id).all() ranks_dict = {} for gtc, rank, max_rank in ranks: ranks_dict[gtc] = (rank, max_rank) elos = DBSession.query(PlayerElo).\ filter_by(player_id=player_id).\ order_by(PlayerElo.elo.desc()).\ all() elos_dict = {} for elo in elos: if elo.games >= 32: elos_dict[elo.game_type_cd] = elo.elo self.data = { 'player': player, 'total_stats': total_stats, 'ranks': ranks_dict, 'elos': elos_dict, }
from flask import current_app, render_template, request, flash, redirect from abilian.i18n import _, _l, _n from abilian.core.models.tag import Tag, entity_tag_tbl from abilian.core.entities import Entity from abilian.web import url_for from abilian.web.admin import AdminPanel from abilian.web.views.base import View from abilian.web.views import ObjectEdit from abilian.services.indexing.service import index_update from .forms import TagForm logger = logging.getLogger(__name__) _OBJ_COUNT = functions.count(entity_tag_tbl.c.entity_id).label('obj_count') def get_entities_for_reindex(tags): """ Collect entities for theses tags. """ if isinstance(tags, Tag): tags = (tags, ) session = current_app.db.session() indexing = current_app.services['indexing'] tbl = Entity.__table__ tag_ids = [t.id for t in tags] q = sa.sql.select([tbl.c.entity_type, tbl.c.id])\ .select_from(
def test_sources(self): with NamedTemporaryFile() as f: args, db = bootstrap_file(f, m(V), '5', configurator=acooke) with db.session_context() as s: # add a diary entry diary = s.query(Topic).filter(Topic.name == 'Diary').one() d = add(s, TopicJournal(topic=diary, date='2018-09-29')) d.populate(log, s) self.assertEqual(len(d.topic.fields), 9, list(enumerate(map(str, d.topic.fields)))) self.assertEqual(d.topic.fields[0].statistic_name.name, 'Notes') self.assertEqual(d.topic.fields[1].statistic_name.name, 'Weight', str(d.topic.fields[1])) for field in d.topic.fields: if field in d.statistics: self.assertTrue(d.statistics[field].value is None, field) d.statistics[d.topic.fields[0]].value = 'hello world' d.statistics[d.topic.fields[1]].value = 64.5 with db.session_context() as s: # check the diary entry was persisted diary = s.query(Topic).filter(Topic.name == 'Diary').one() d = s.query(TopicJournal).filter( TopicJournal.topic == diary, TopicJournal.date == '2018-09-29').one() s.flush() d.populate(log, s) self.assertEqual(len(d.topic.fields), 9, list(enumerate(map(str, d.topic.fields)))) self.assertEqual(d.topic.fields[0].statistic_name.name, 'Notes') self.assertEqual(d.statistics[d.topic.fields[0]].value, 'hello world') self.assertEqual(d.topic.fields[1].statistic_name.name, 'Weight') self.assertEqual(d.statistics[d.topic.fields[1]].value, 64.5) self.assertEqual(d.statistics[d.topic.fields[1]].type, StatisticJournalType.FLOAT) # generate summary stats SummaryCalculator(db, schedule='m').run() SummaryCalculator(db, schedule='y').run() with db.session_context() as s: # check the summary stats diary = s.query(Topic).filter(Topic.name == 'Diary').one() weight = s.query(StatisticJournal).join(StatisticName). \ filter(StatisticName.owner == diary, StatisticName.name == 'Weight').one() self.assertEqual(weight.value, 64.5) self.assertEqual(len(weight.measures), 2, weight.measures) self.assertEqual(weight.measures[0].rank, 1) self.assertEqual(weight.measures[0].percentile, 100, weight.measures[0].percentile) n = s.query(count(StatisticJournalFloat.id)).scalar() self.assertEqual(n, 4, n) n = s.query(count(StatisticJournalInteger.id)).scalar() self.assertEqual(n, 11, n) m_avg = s.query(StatisticJournalFloat).join(StatisticName). \ filter(StatisticName.name == 'Avg/Month Weight').one() self.assertEqual(m_avg.value, 64.5) y_avg = s.query(StatisticJournalFloat).join(StatisticName). \ filter(StatisticName.name == 'Avg/Year Weight').one() self.assertEqual(y_avg.value, 64.5) month = s.query(Interval).filter( Interval.schedule == 'm').one() self.assertEqual(month.start, to_date('2018-09-01'), month.start) self.assertEqual(month.finish, to_date('2018-10-01'), month.finish) with db.session_context() as s: # delete the diary entry diary = s.query(Topic).filter(Topic.name == 'Diary').one() d = s.query(TopicJournal).filter( TopicJournal.topic == diary, TopicJournal.date == '2018-09-29').one() s.delete(d) run('sqlite3 %s ".dump"' % f.name, shell=True) with db.session_context() as s: # check the delete cascade self.assertEqual(s.query(count(TopicJournal.id)).scalar(), 0) # this should be zero because the Intervals were automatically deleted for source in s.query(Source).all(): print(source) for journal in s.query(StatisticJournal).all(): print(journal) self.assertEqual( s.query(count(Source.id)).scalar(), 11, list(map(str, s.query(Source).all()))) # constants self.assertEqual( s.query(count(StatisticJournalText.id)).scalar(), 7, s.query(count(StatisticJournalText.id)).scalar()) self.assertEqual( s.query(count(StatisticJournal.id)).scalar(), 7, s.query(count(StatisticJournal.id)).scalar())
async def contractor_list(request): # noqa: C901 (ignore complexity) sort_val = request.query.get('sort') sort_col = SORT_OPTIONS.get(sort_val, SORT_OPTIONS['last_updated']) pagination, offset = get_pagination(request, 100, 100) company = request['company'] options = company.options or {} fields = ( c.id, c.first_name, c.last_name, c.tag_line, c.primary_description, c.town, c.country, c.photo_hash, ) show_labels = options.get('show_labels') if show_labels: fields += (c.labels, ) show_stars = options.get('show_stars') if show_stars: fields += (c.review_rating, ) show_hours_reviewed = options.get('show_hours_reviewed') if show_hours_reviewed: fields += (c.review_duration, ) where = (c.company == company.id, ) subject_filter = get_arg(request, 'subject') qual_level_filter = get_arg(request, 'qual_level') select_from = None if subject_filter or qual_level_filter: select_from = sa_contractors.join(sa_con_skills) if subject_filter: select_from = select_from.join(sa_subjects) where += (sa_subjects.c.id == subject_filter, ) if qual_level_filter: select_from = select_from.join(sa_qual_levels) where += (sa_qual_levels.c.id == qual_level_filter, ) labels_filter = request.query.getall('label', []) labels_exclude_filter = request.query.getall('label_exclude', []) if labels_filter: where += (c.labels.contains(cast(labels_filter, ARRAY(String(255)))), ) if labels_exclude_filter: where += (or_( ~c.labels.overlap(cast(labels_exclude_filter, ARRAY(String(255)))), c.labels.is_(None)), ) location = await geocode(request) inc_distance = None if location: if location.get('error'): return json_response( request, location=location, results=[], count=0, ) max_distance = get_arg(request, 'max_distance', default=80_000) inc_distance = True request_loc = func.ll_to_earth(location['lat'], location['lng']) con_loc = func.ll_to_earth(c.latitude, c.longitude) distance_func = func.earth_distance(request_loc, con_loc) where += (distance_func < max_distance, ) fields += (distance_func.label('distance'), ) sort_col = distance_func distinct_cols = sort_col, c.id if sort_col == c.review_rating: sort_on = c.review_rating.desc().nullslast(), c.review_duration.desc( ).nullslast(), c.id distinct_cols = c.review_rating, c.review_duration, c.id elif sort_col == c.last_updated: sort_on = sort_col.desc(), c.id else: sort_on = sort_col.asc(), c.id q_iter = (select(fields).where(and_(*where)).order_by(*sort_on).distinct( *distinct_cols).offset(offset).limit(pagination)) q_count = select([sql_f.count(distinct(c.id))]).where(and_(*where)) if select_from is not None: q_iter = q_iter.select_from(select_from) q_count = q_count.select_from(select_from) results = [] name_display = company.name_display conn = await request['conn_manager'].get_connection() async for row in conn.execute(q_iter): name = _get_name(name_display, row) con = dict( id=row.id, url=route_url(request, 'contractor-get', company=company.public_key, id=row.id), link=f'{row.id}-{slugify(name)}', name=name, tag_line=row.tag_line, primary_description=row.primary_description, town=row.town, country=row.country, photo=_photo_url(request, row, True), distance=inc_distance and int(row.distance), ) if show_labels: con['labels'] = row.labels or [] if show_stars: con['review_rating'] = row.review_rating if show_hours_reviewed: con['review_duration'] = row.review_duration results.append(con) cur_count = await conn.execute(q_count) return json_response( request, location=location, results=results, count=(await cur_count.first())[0], )
def getMediaCounts(): return db.session.query(CollectionMedia.collection_id, func.count(CollectionMedia.media_id)).group_by( CollectionMedia.collection_id).all()
def get_all_study_summaries(self) -> List[StudySummary]: with _create_scoped_session(self.scoped_session) as session: summarized_trial = ( session.query( models.TrialModel.study_id, functions.min(models.TrialModel.datetime_start).label("datetime_start"), functions.count(models.TrialModel.trial_id).label("n_trial"), ) .group_by(models.TrialModel.study_id) .with_labels() .subquery() ) study_summary_stmt = session.query( models.StudyModel.study_id, models.StudyModel.study_name, summarized_trial.c.datetime_start, functions.coalesce(summarized_trial.c.n_trial, 0).label("n_trial"), ).select_from(orm.outerjoin(models.StudyModel, summarized_trial)) study_summary = study_summary_stmt.all() _directions = defaultdict(list) for d in session.query(models.StudyDirectionModel).all(): _directions[d.study_id].append(d.direction) _user_attrs = defaultdict(list) for a in session.query(models.StudyUserAttributeModel).all(): _user_attrs[d.study_id].append(a) _system_attrs = defaultdict(list) for a in session.query(models.StudySystemAttributeModel).all(): _system_attrs[d.study_id].append(a) study_summaries = [] for study in study_summary: directions = _directions[study.study_id] best_trial: Optional[models.TrialModel] = None try: if len(directions) > 1: raise ValueError elif directions[0] == StudyDirection.MAXIMIZE: best_trial = models.TrialModel.find_max_value_trial( study.study_id, 0, session ) else: best_trial = models.TrialModel.find_min_value_trial( study.study_id, 0, session ) except ValueError: best_trial_frozen: Optional[FrozenTrial] = None if best_trial: value = models.TrialValueModel.find_by_trial_and_objective( best_trial, 0, session ) assert value params = ( session.query( models.TrialParamModel.param_name, models.TrialParamModel.param_value, models.TrialParamModel.distribution_json, ) .filter(models.TrialParamModel.trial_id == best_trial.trial_id) .all() ) param_dict = {} param_distributions = {} for param in params: distribution = distributions.json_to_distribution(param.distribution_json) param_dict[param.param_name] = distribution.to_external_repr( param.param_value ) param_distributions[param.param_name] = distribution user_attrs = models.TrialUserAttributeModel.where_trial_id( best_trial.trial_id, session ) system_attrs = models.TrialSystemAttributeModel.where_trial_id( best_trial.trial_id, session ) intermediate = models.TrialIntermediateValueModel.where_trial_id( best_trial.trial_id, session ) best_trial_frozen = FrozenTrial( best_trial.number, TrialState.COMPLETE, value.value, best_trial.datetime_start, best_trial.datetime_complete, param_dict, param_distributions, {i.key: json.loads(i.value_json) for i in user_attrs}, {i.key: json.loads(i.value_json) for i in system_attrs}, {value.step: value.intermediate_value for value in intermediate}, best_trial.trial_id, ) user_attrs = _user_attrs.get(study.study_id, []) system_attrs = _system_attrs.get(study.study_id, []) study_summaries.append( StudySummary( study_name=study.study_name, direction=None, directions=directions, best_trial=best_trial_frozen, user_attrs={i.key: json.loads(i.value_json) for i in user_attrs}, system_attrs={i.key: json.loads(i.value_json) for i in system_attrs}, n_trials=study.n_trial, datetime_start=study.datetime_start, study_id=study.study_id, ) ) return study_summaries
def _search_with_args(sess, ret_type, parameters): u''' 返り値型と引数の型による検索 ''' u''' -- 大体こんなことがしたい SELECT method.id, method.name, method.fqname FROM methods JOIN types rtype on methods.return_type = rtype.id JOIN methodargs marg on methods.id = marg.method_id JOIN types atype on marg.type = atype.id WHERE method.argcount = ${len(parameters)} AND rtype.name = ${rtype} AND methodargs.order = ${paramidx} AND atype.name = ${parameters[paramidx]} ... GROUP BY method.id HAVING COUNT(*) = ${len(parameters)} ''' method = tables.Method.__table__ marg = tables.MethodArg.__table__ rtype = tables.Type.__table__.alias('rtype') atype = tables.Type.__table__.alias('atype') joined = method.join(marg, method.c.id == marg.c.method_id) joined = joined.join(atype, marg.c.type == atype.c.id) joined = joined.join(rtype, method.c.return_type == rtype.c.id) def make_param_query(idx, param): if param == '*': return marg.c.order == idx return sql.and_(marg.c.order == idx, atype.c.name == param) argquery = sql.or_(*[make_param_query(idx, param) for idx, param in enumerate(parameters)]) if ret_type: where = sql.and_(method.c.argcount == len(parameters), rtype.c.name == ret_type, argquery) else: where = sql.and_(method.c.argcount == len(parameters), argquery) # XXX: sqlite 以外に対応できない # func.group_concat(methodargs.order + ':' + atype.c.name) # とやりたいところだけど、 SQL がまともに生成されなかったので諦めた tmpl = '''group_concat(methodargs.`order` || ':' || {0}) as {1}''' args = tmpl.format(atype.c.name, 'name') fqargs = tmpl.format(atype.c.fqname, 'fqname') columns = [method.c.id, method.c.name, method.c.fqname, method.c.return_type, method.c.modifiers, rtype.c.name, rtype.c.fqname, args, fqargs] query = sql.select(columns, where, joined, use_labels=True) query = query.group_by(method.c.id).having(functions.count() == len(parameters)).order_by(method.c.fqname) results = sess.execute(query) def make_args(names, fqnames): names = dict(x.split(':') for x in names.split(',')) fqnames = dict(x.split(':') for x in fqnames.split(',')) return [dict(name=names[idx], fully_qualified=fqnames[idx]) for idx in map(str, range(len(parameters)))] def make_dict(r): return dict(name=r[method.c.name], fully_qualified=r[method.c.fqname], return_type=dict(name=r[rtype.c.name], fully_qualified=r[rtype.c.fqname]), args=make_args(r['name'], r['fqname']), modifiers=r[method.c.modifiers]) return [make_dict(x) for x in results]
def report(task_id): # 获取筛选数据 search_vul_type = request.args.get("search_vul_type", None) search_rule = request.args.get("search_rule", None) search_level = request.args.get("search_level", None) # 当前页码,默认为第一页 page = int(request.args.get("page", 1)) # 检测 task id 是否存在 task_info = CobraTaskInfo.query.filter_by(id=task_id).first() if not task_info: return jsonify(status="4004", msg="report id not found.") # 获取task的信息 repository = task_info.target task_created_at = task_info.created_at time_consume = task_info.time_consume time_start = task_info.time_start time_end = task_info.time_end files = task_info.file_count code_number = task_info.code_number if code_number is None or code_number == 0: code_number = u"统计中..." else: code_number = common.convert_number(code_number) # 把时间戳转换成datetime time_start = time.strftime("%H:%M:%S", time.localtime(time_start)) time_end = time.strftime("%H:%M:%S", time.localtime(time_end)) # 获取project信息 project = CobraProjects.query.filter_by(repository=repository).first() if project is None: project_name = repository author = 'Anonymous' project_description = 'Compress Project' project_framework = 'Unknown Framework' project_url = 'Unknown URL' else: project_name = project.name author = project.author project_description = project.remark project_framework = project.framework project_url = project.url # 获取漏洞总数量 scan_results = CobraResults.query.filter_by(task_id=task_id).all() total_vul_count = len(scan_results) # 获取出现的漏洞类型 res = db.session.query(count().label("vul_number"), CobraVuls.name).filter( and_( CobraResults.task_id == task_id, CobraResults.rule_id == CobraRules.id, CobraVuls.id == CobraRules.vul_id, ) ).group_by(CobraVuls.name).all() # 提供给筛选列表 select_vul_type = list() # 存下每种漏洞数量 chart_vuls_number = list() for r in res: select_vul_type.append(r[1]) chart_vuls_number.append({"vuls_name": r[1], "vuls_number": r[0]}) # 获取触发的规则类型 res = db.session.query(CobraRules.description).filter( and_( CobraResults.task_id == task_id, CobraResults.rule_id == CobraRules.id, CobraVuls.id == CobraRules.vul_id ) ).group_by(CobraRules.description).all() select_rule_type = list() for r in res: select_rule_type.append(r[0]) # 检索不同等级的漏洞数量 res = db.session.query(count().label('vuln_number'), CobraRules.level).filter( and_( CobraResults.task_id == task_id, CobraResults.rule_id == CobraRules.id, CobraVuls.id == CobraRules.vul_id, ) ).group_by(CobraRules.level).all() low_amount = medium_amount = high_amount = unknown_amount = 0 for every_level in res: """ 低危:1 中危:2 高危:3 未定义:其他值 """ if every_level[1] == 1: low_amount = every_level[0] elif every_level[1] == 2: medium_amount = every_level[0] elif every_level[1] == 3: high_amount = every_level[0] else: unknown_amount = every_level[0] # 检索全部的漏洞信息 filter_group = ( CobraResults.task_id == task_id, CobraResults.rule_id == CobraRules.id, CobraVuls.id == CobraRules.vul_id, ) # 根据传入的筛选条件添加SQL的条件 if search_vul_type is not None and search_vul_type != "all": filter_group += (CobraVuls.name == search_vul_type,) if search_rule is not None and search_rule != "all": filter_group += (CobraRules.description == search_rule,) if search_level is not None and search_level != "all": filter_group += (CobraRules.level == search_level,) # 构建SQL语句 all_scan_results = db.session.query( CobraResults.file, CobraResults.line, CobraResults.code, CobraRules.description, CobraRules.level, CobraRules.regex_location, CobraRules.regex_repair, CobraRules.repair, CobraVuls.name ).filter( *filter_group ) page_size = 5 total_number = all_scan_results.all() total_pages = len(total_number) / page_size + 1 all_scan_results = all_scan_results.limit(page_size).offset((page - 1) * page_size).all() # 处理漏洞信息 vulnerabilities = list() map_level = ["未定义", "低危", "中危", "高危"] map_color = ["#555", "black", "orange", "red"] current_url = '' for result in all_scan_results: # 生成data数据 data_dict = dict() data_dict["file"] = result[0] data_dict["line"] = result[1] data_dict["code"] = result[2] data_dict["rule"] = result[3] data_dict["level"] = map_level[result[4]] data_dict["color"] = map_color[result[4]] data_dict["repair"] = result[7] data_dict['verify'] = '' if project_framework != '': for rule in detection.Detection().rules: if rule['name'] == project_framework: if 'public' in rule: if result.file[:len(rule['public'])] == rule['public']: data_dict['verify'] = project_url + result.file.replace(rule['public'], '') # 检索vulnerabilities中是否存在vul_type的类别 # 如果存在就添加到对应的data字典中 # 否则就新建一下 found = False for v in vulnerabilities: if v["vul_type"] == result[-1]: # 直接添加 v["data"].append(data_dict) # 修改标志 found = True break # 没有找到 if not found: temp_dict = dict(vul_type=result[-1], data=list()) temp_dict["data"].append(data_dict) vulnerabilities.append(temp_dict) current_url = request.url.replace("&page={}".format(page), "").replace("page={}".format(page), "") if "?" not in current_url: current_url += "?" data = { 'id': int(task_id), 'project_name': project_name, 'project_repository': repository, 'project_description': project_description, 'project_url': project_url, 'project_framework': project_framework, 'author': author, 'task_created_at': task_created_at, 'time_consume': common.convert_time(time_consume), 'time_start': time_start, 'time_end': time_end, 'files': common.convert_number(files), 'code_number': code_number, 'vul_count': common.convert_number(total_vul_count), 'vulnerabilities': vulnerabilities, "select_vul_type": select_vul_type, "select_rule_type": select_rule_type, "chart_vuls_number": chart_vuls_number, "current_page": page, "total_pages": total_pages, "filter_vul_number": len(total_number), "current_url": current_url, 'amount': { 'h': high_amount, 'm': medium_amount, 'l': low_amount, 'u': unknown_amount }, } return render_template('report.html', data=data)
class HighlightTag(Base): __tablename__ = 'highlight_tags' highlight_id = Column(Integer, ForeignKey('highlights.id', ondelete='CASCADE'), primary_key=True) highlight = relationship('Highlight') tag_id = Column(Integer, ForeignKey('tags.id', ondelete='CASCADE'), primary_key=True) tag = relationship('Tag') Tag.highlights_count = column_property( select( [functions.count(HighlightTag.highlight_id)], ).where( HighlightTag.tag_id == Tag.id, ).correlate_except(HighlightTag) ) def connect(db_url): """Connect to the database using an environment variable. """ logger.info("Connecting to SQL database %r", db_url) kwargs = {} if db_url.startswith('sqlite:'): kwargs['connect_args'] = {'check_same_thread': False} engine = create_engine(db_url, **kwargs) # logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)
def _samp_compute_up(t, s, **kwargs): if t.n is not None: limit = t.n else: limit = select([safuncs.count() * t.frac]).as_scalar() return s.order_by(safuncs.random()).limit(limit)
def get_all_study_summaries(self) -> List[StudySummary]: session = self.scoped_session() summarized_trial = (session.query( models.TrialModel.study_id, functions.min( models.TrialModel.datetime_start).label("datetime_start"), functions.count(models.TrialModel.trial_id).label("n_trial"), ).group_by(models.TrialModel.study_id).with_labels().subquery()) study_summary_stmt = session.query( models.StudyModel.study_id, models.StudyModel.study_name, models.StudyModel.direction, summarized_trial.c.datetime_start, functions.coalesce(summarized_trial.c.n_trial, 0).label("n_trial"), ).select_from(orm.outerjoin(models.StudyModel, summarized_trial)) study_summary = study_summary_stmt.all() study_summaries = [] for study in study_summary: best_trial: Optional[models.TrialModel] = None try: if study.direction == StudyDirection.MAXIMIZE: best_trial = models.TrialModel.find_max_value_trial( study.study_id, session) else: best_trial = models.TrialModel.find_min_value_trial( study.study_id, session) except ValueError: best_trial_frozen: Optional[FrozenTrial] = None if best_trial: params = (session.query( models.TrialParamModel.param_name, models.TrialParamModel.param_value, models.TrialParamModel.distribution_json, ).filter(models.TrialParamModel.trial_id == best_trial.trial_id).all()) param_dict = {} param_distributions = {} for param in params: distribution = distributions.json_to_distribution( param.distribution_json) param_dict[ param.param_name] = distribution.to_external_repr( param.param_value) param_distributions[param.param_name] = distribution user_attrs = session.query( models.TrialUserAttributeModel).filter( models.TrialUserAttributeModel.trial_id == best_trial.trial_id) system_attrs = session.query( models.TrialSystemAttributeModel).filter( models.TrialSystemAttributeModel.trial_id == best_trial.trial_id) intermediate = session.query(models.TrialValueModel).filter( models.TrialValueModel.trial_id == best_trial.trial_id) best_trial_frozen = FrozenTrial( best_trial.number, TrialState.COMPLETE, best_trial.value, best_trial.datetime_start, best_trial.datetime_complete, param_dict, param_distributions, {i.key: json.loads(i.value_json) for i in user_attrs}, {i.key: json.loads(i.value_json) for i in system_attrs}, {value.step: value.value for value in intermediate}, best_trial.trial_id, ) user_attrs = session.query(models.StudyUserAttributeModel).filter( models.StudyUserAttributeModel.study_id == study.study_id) system_attrs = session.query( models.StudySystemAttributeModel).filter( models.StudySystemAttributeModel.study_id == study.study_id) study_summaries.append( StudySummary( study_name=study.study_name, direction=study.direction, best_trial=best_trial_frozen, user_attrs={ i.key: json.loads(i.value_json) for i in user_attrs }, system_attrs={ i.key: json.loads(i.value_json) for i in system_attrs }, n_trials=study.n_trial, datetime_start=study.datetime_start, study_id=study.study_id, )) # Terminate transaction explicitly to avoid connection timeout during transaction. self._commit(session) return study_summaries
async def check_if_user_exist(session, email): user = await session.execute( select(count(User.id)).where(User.email == email and User.is_active)) return bool(user.scalars().first())