def unified_package_view(template, query_fn=None, **template_args): untracked = request.args.get('untracked') == '1' order_name = request.args.get('order_by', 'running,failing,name') # pylint: disable=E1101 subq = db.query(Package.name, func.array_agg(func.row(Package.collection_id, Package.tracked, Package.resolved, Package.last_complete_build_state)) .label("states"), func.bool_or(Package.resolved).label("resolved"), func.bool_or(Package.last_complete_build_state == Build.FAILED) .label("failing"), func.bool_or(Package.last_complete_build_id != Package.last_build_id) .label("has_running_build"))\ .filter(Package.blocked == False)\ .group_by(Package.name) if not untracked: subq = subq.filter(Package.tracked == True) if query_fn: subq = query_fn(subq) subq = subq.subquery() order_map = { 'name': [subq.c.name], 'failing': [subq.c.resolved, Reversed(subq.c.failing)], 'running': [NullsLastOrder(subq.c.has_running_build)], } order_names, order = get_order(order_map, order_name) package_query = db.query(subq.c.name, subq.c.states, subq.c.has_running_build) page = package_query.order_by(*order).paginate(packages_per_page) page.items = map(UnifiedPackage, page.items) populate_package_groups(page.items) return render_template(template, packages=page.items, page=page, order=order_names, collection=None, **template_args)
def package_detail(name): collection = g.current_collection or g.default_collection packages = { p.collection_id: p for p in db.query(Package).filter_by(name=name) } package = None all_packages = [] for coll in g.collections: p = packages.get(coll.id) if p: all_packages.append((coll, p)) if coll is collection: package = p if not package: abort(404) package.global_groups = db.query(PackageGroup)\ .join(PackageGroupRelation)\ .filter(PackageGroupRelation.package_name == package.name)\ .filter(PackageGroup.namespace == None)\ .all() package.user_groups = [] package.available_groups = [] if g.user: user_groups = \ db.query(PackageGroup, func.bool_or(PackageGroupRelation.package_name == package.name))\ .outerjoin(PackageGroupRelation)\ .join(GroupACL)\ .filter(GroupACL.user_id == g.user.id)\ .order_by(PackageGroup.namespace.nullsfirst(), PackageGroup.name)\ .group_by(PackageGroup.id)\ .distinct().all() package.user_groups = [ group for group, checked in user_groups if checked and group.namespace ] package.available_groups = [ group for group, checked in user_groups if not checked ] page = db.query(Build)\ .filter_by(package_id=package.id)\ .options(subqueryload(Build.dependency_changes), subqueryload(Build.build_arch_tasks))\ .order_by(Build.id.desc())\ .paginate(builds_per_page) return render_template("package-detail.html", package=package, page=page, builds=page.items, all_packages=all_packages)
def get(self, database, query): if not self.has_extension("pg_qualstats"): raise HTTPError(501, "PG qualstats is not installed") base_query = qualstat_getstatdata() c = inner_cc(base_query) base_query.append_from(text("""LATERAL unnest(quals) as qual""")) base_query = (base_query .where(c.queryid == query) .having(func.bool_or(column('eval_type') == 'f')) .having(c.execution_count > 1000) .having(c.occurences > 0) .having(c.filter_ratio > 0.5) .params(**{'from': '-infinity', 'to': 'infinity'})) optimizable = list(self.execute(base_query, params={'query': query})) optimizable = resolve_quals(self.connect(database=database), optimizable, 'quals') hypoplan = None indexes = {} for qual in optimizable: indexes[qual.where_clause] = possible_indexes(qual) hypo_version = self.has_extension("hypopg", database=database) if hypo_version and hypo_version >= "0.0.3": # identify indexes # create them allindexes = [ind for indcollection in indexes.values() for ind in indcollection] for ind in allindexes: ddl = ind.hypo_ddl if ddl is not None: ind.name = self.execute(ddl, database=database).scalar() # Build the query and fetch the plans querystr = get_any_sample_query(self, database, query, self.get_argument("from"), self.get_argument("to")) try: hypoplan = get_hypoplans(self.connect(database=database), querystr, allindexes) except: # TODO: offer the possibility to fill in parameters from the UI self.flash("We couldn't get plans for this query, presumably " "because some parameters are missing ") self.render("database/query/indexes.html", indexes=indexes, hypoplan=hypoplan)
def package_detail(name): collection = g.current_collection or g.default_collection packages = {p.collection_id: p for p in db.query(Package).filter_by(name=name)} package = None all_packages = [] for coll in g.collections: p = packages.get(coll.id) if p: all_packages.append((coll, p)) if coll is collection: package = p if not package: abort(404) package.global_groups = db.query(PackageGroup)\ .join(PackageGroupRelation)\ .filter(PackageGroupRelation.package_name == package.name)\ .filter(PackageGroup.namespace == None)\ .all() package.user_groups = [] package.available_groups = [] if g.user: user_groups = \ db.query(PackageGroup, func.bool_or(PackageGroupRelation.package_name == package.name))\ .outerjoin(PackageGroupRelation)\ .join(GroupACL)\ .filter(GroupACL.user_id == g.user.id)\ .order_by(PackageGroup.namespace.nullsfirst(), PackageGroup.name)\ .group_by(PackageGroup.id)\ .distinct().all() package.user_groups = [group for group, checked in user_groups if checked and group.namespace] package.available_groups = [group for group, checked in user_groups if not checked] page = db.query(Build)\ .filter_by(package_id=package.id)\ .options(subqueryload(Build.dependency_changes), subqueryload(Build.build_arch_tasks))\ .order_by(Build.id.desc())\ .paginate(builds_per_page) return render_template("package-detail.html", package=package, page=page, builds=page.items, all_packages=all_packages)
def get(self, database, query): if not self.has_extension("pg_qualstats"): raise HTTPError(501, "PG qualstats is not installed") base_query = qualstat_getstatdata() c = inner_cc(base_query) base_query.append_from(text("""LATERAL unnest(quals) as qual""")) base_query = (base_query .where(c.queryid == query) .having(func.bool_or(column('eval_type') == 'f')) .having(c.count > 1000) .having(c.filter_ratio > 0.5) .params(**{'from': '-infinity', 'to': 'infinity'})) optimizable = list(self.execute(base_query, params={'query': query})) optimizable = resolve_quals(self.connect(database=database), optimizable, 'quals') qual_indexes = {} for line in optimizable: qual_indexes[line['where_clause']] = possible_indexes( line['quals']) self.render("database/query/indexes.html", indexes=qual_indexes)
def get_taxa_list(id_area): """ :param type: :return: """ try: reproduction_id = ( ( DB.session.query(TNomenclatures.id_nomenclature) .join( BibNomenclaturesTypes, TNomenclatures.id_type == BibNomenclaturesTypes.id_type, ) .filter( and_( BibNomenclaturesTypes.mnemonique.like("STATUT_BIO"), TNomenclatures.cd_nomenclature.like("3"), ) ) ) .first() .id_nomenclature ) print("reproduction_id", reproduction_id) query_territory = ( DB.session.query( Taxref.cd_ref.label("id"), LAreas.id_area, LAreas.area_code, Taxref.cd_ref, func.split_part(Taxref.nom_vern, ",", 1).label("nom_vern"), Taxref.nom_valide, Taxref.group1_inpn, Taxref.group2_inpn, func.count(distinct(Synthese.id_synthese)).label("count_occtax"), func.count(distinct(Synthese.observers)).label("count_observer"), func.count(distinct(Synthese.date_min)).label("count_date"), func.count(distinct(Synthese.id_dataset)).label("count_dataset"), func.max(distinct(func.extract("year", Synthese.date_min))).label( "last_year" ), func.array_agg( aggregate_order_by( distinct(func.extract("year", Synthese.date_min)), func.extract("year", Synthese.date_min).desc(), ) ).label("list_years"), func.array_agg( aggregate_order_by( distinct(func.extract("month", Synthese.date_min)), func.extract("month", Synthese.date_min).asc(), ) ).label("list_months"), func.bool_or( Synthese.id_nomenclature_bio_status == reproduction_id ).label("reproduction"), func.max(distinct(func.extract("year", Synthese.date_min))) .filter(Synthese.id_nomenclature_bio_status == reproduction_id) .label("last_year_reproduction"), func.array_agg(distinct(Synthese.id_nomenclature_bio_status)).label( "bio_status_id" ), case( [(func.count(TaxrefProtectionEspeces.cd_nom) > 0, True)], else_=False, ).label("protection"), ) .select_from(CorAreaSynthese) .join(Synthese, Synthese.id_synthese == CorAreaSynthese.id_synthese) .join(Taxref, Synthese.cd_nom == Taxref.cd_nom) .join(LAreas, LAreas.id_area == CorAreaSynthese.id_area) .outerjoin(TaxrefLR, TaxrefLR.cd_nom == Taxref.cd_ref) .outerjoin( TaxrefProtectionEspeces, TaxrefProtectionEspeces.cd_nom == Taxref.cd_nom ) .filter(LAreas.id_area == id_area) .group_by( LAreas.id_area, LAreas.area_code, Taxref.cd_ref, Taxref.nom_vern, Taxref.nom_valide, Taxref.group1_inpn, Taxref.group2_inpn, ) .order_by( func.count(distinct(Synthese.id_synthese)).desc(), Taxref.group1_inpn, Taxref.group2_inpn, Taxref.nom_valide, ) ) print("query_territory", query_territory) result = query_territory.all() count = len(result) data = [] for r in result: dict = r._asdict() bio_status = [] for s in r.bio_status_id: bio_status.append(get_nomenclature(s)) dict["bio_status"] = bio_status redlist = get_redlist_status(r.cd_ref) dict["redlist"] = redlist data.append(dict) redlistless_data = list(filter(redlist_list_is_null, data)) print("redlistless_data", len(redlistless_data)) redlist_data = list(filter(redlist_is_not_null, data)) print("redlist_data", len(redlist_data)) redlist_sorted_data = sorted( redlist_data, key=lambda k: ( k["redlist"][0]["priority_order"], k["redlist"][0]["threatened"], ), ) sorted_data = redlist_sorted_data + list(redlistless_data) return jsonify({"count": count, "data": sorted_data}), 200 except Exception as e: error = "<get_taxa_list> ERROR: {}".format(e) current_app.logger.error(error) return {"Error": error}, 400
def package_detail(name, form=None, collection=None): if not collection: collection = g.current_collections[0] g.current_collections = [collection] base = db.query(BasePackage).filter_by(name=name).first_or_404() # Get packages for all collections, so that we can display # "State in other collections" table packages = {p.collection_id: p for p in db.query(Package).filter_by(base_id=base.id)} # assign packages to collections in the right order package = None # the current package, may stay None all_packages = [] for coll in g.collections: p = packages.get(coll.id) if p: all_packages.append((coll, p)) if coll is collection: package = p # prepare group checkboxes base.global_groups = db.query(PackageGroup)\ .join(PackageGroupRelation)\ .filter(PackageGroupRelation.base_id == base.id)\ .filter(PackageGroup.namespace == None)\ .all() base.user_groups = [] base.available_groups = [] if g.user: user_groups = \ db.query(PackageGroup, func.bool_or(PackageGroupRelation.base_id == base.id))\ .outerjoin(PackageGroupRelation)\ .join(GroupACL)\ .filter(GroupACL.user_id == g.user.id)\ .order_by(PackageGroup.namespace.nullsfirst(), PackageGroup.name)\ .group_by(PackageGroup.id)\ .distinct().all() base.user_groups = [group for group, checked in user_groups if checked and group.namespace] base.available_groups = [group for group, checked in user_groups if not checked] # History entry pagination pivot timestamp # We only display entries older than this last_seen_ts = request.args.get('last_seen_ts') if last_seen_ts: try: last_seen_ts = int(last_seen_ts) except ValueError: abort(400) def to_ts(col): return cast(func.extract('EPOCH', col), Integer) entries = None if package: # set current priority package.current_priority = db.query( Package.current_priority_expression( collection=package.collection, last_build=package.last_build, ) ).filter(Package.id == package.id).scalar() # prepare history entries - builds and resolution changes builds = db.query(Build)\ .filter_by(package_id=package.id)\ .filter(to_ts(Build.started) < last_seen_ts if last_seen_ts else true())\ .options(subqueryload(Build.dependency_changes), subqueryload(Build.build_arch_tasks))\ .order_by(Build.started.desc())\ .limit(builds_per_page)\ .all() resolutions = db.query(ResolutionChange)\ .filter_by(package_id=package.id)\ .filter(to_ts(ResolutionChange.timestamp) < last_seen_ts if last_seen_ts else true())\ .options(joinedload(ResolutionChange.problems))\ .order_by(ResolutionChange.timestamp.desc())\ .limit(builds_per_page)\ .all() entries = sorted( builds + resolutions, key=lambda x: getattr(x, 'started', None) or getattr(x, 'timestamp'), reverse=True, )[:builds_per_page] if not form: form = forms.EditPackageForm( tracked=package.tracked, collection_id=package.collection_id, manual_priority=package.manual_priority, arch_override=(package.arch_override or '').split(' '), skip_resolution=package.skip_resolution, ) # Note: package might be None return render_template( "package-detail.html", base=base, package=package, collection=collection, form=form, entries=entries, all_packages=all_packages, is_continuation=bool(last_seen_ts), is_last=len(entries) < builds_per_page if package else True, )
def get_query_column(self, entity): return func.bool_or(self.resolve_entity_column(entity))
def package_detail(name, form=None, collection=None): if not collection: collection = g.current_collections[0] g.current_collections = [collection] base = db.query(BasePackage).filter_by(name=name).first_or_404() # Get packages for all collections, so that we can display # "State in other collections" table packages = { p.collection_id: p for p in db.query(Package).filter_by(base_id=base.id) } # assign packages to collections in the right order package = None # the current package, may stay None all_packages = [] for coll in g.collections: p = packages.get(coll.id) if p: all_packages.append((coll, p)) if coll is collection: package = p # prepare group checkboxes base.global_groups = db.query(PackageGroup)\ .join(PackageGroupRelation)\ .filter(PackageGroupRelation.base_id == base.id)\ .filter(PackageGroup.namespace == None)\ .all() base.user_groups = [] base.available_groups = [] if g.user: user_groups = \ db.query(PackageGroup, func.bool_or(PackageGroupRelation.base_id == base.id))\ .outerjoin(PackageGroupRelation)\ .join(GroupACL)\ .filter(GroupACL.user_id == g.user.id)\ .order_by(PackageGroup.namespace.nullsfirst(), PackageGroup.name)\ .group_by(PackageGroup.id)\ .distinct().all() base.user_groups = [ group for group, checked in user_groups if checked and group.namespace ] base.available_groups = [ group for group, checked in user_groups if not checked ] # History entry pagination pivot timestamp # We only display entries older than this last_seen_ts = request.args.get('last_seen_ts') if last_seen_ts: try: last_seen_ts = int(last_seen_ts) except ValueError: abort(400) def to_ts(col): return cast(func.extract('EPOCH', col), Integer) entries = None if package: # set current priority package.current_priority = db.query( Package.current_priority_expression( collection=package.collection, last_build=package.last_build, )).filter(Package.id == package.id).scalar() # prepare history entries - builds and resolution changes builds = db.query(Build)\ .filter_by(package_id=package.id)\ .filter(to_ts(Build.started) < last_seen_ts if last_seen_ts else true())\ .options(subqueryload(Build.dependency_changes), subqueryload(Build.build_arch_tasks))\ .order_by(Build.started.desc())\ .limit(builds_per_page)\ .all() resolutions = db.query(ResolutionChange)\ .filter_by(package_id=package.id)\ .filter(to_ts(ResolutionChange.timestamp) < last_seen_ts if last_seen_ts else true())\ .options(joinedload(ResolutionChange.problems))\ .order_by(ResolutionChange.timestamp.desc())\ .limit(builds_per_page)\ .all() entries = sorted( builds + resolutions, key=lambda x: getattr(x, 'started', None) or getattr( x, 'timestamp'), reverse=True, )[:builds_per_page] if not form: form = forms.EditPackageForm( tracked=package.tracked, collection_id=package.collection_id, manual_priority=package.manual_priority, arch_override=(package.arch_override or '').split(' '), skip_resolution=package.skip_resolution, ) # Note: package might be None return render_template( "package-detail.html", base=base, package=package, collection=collection, form=form, entries=entries, all_packages=all_packages, is_continuation=bool(last_seen_ts), is_last=len(entries) < builds_per_page if package else True, )
def query_work_day_stats( company_id, start_date=None, end_date=None, first=None, after=None, tzname="Europe/Paris", ): tz = gettz(tzname) if after: max_time, user_id_ = parse_datetime_plus_id_cursor(after) max_date = max_time.date() end_date = min(max_date, end_date) if end_date else max_date query = (Activity.query.join(Mission).join( Expenditure, and_( Activity.user_id == Expenditure.user_id, Activity.mission_id == Expenditure.mission_id, ), isouter=True, ).with_entities( Activity.id, Activity.user_id, Activity.mission_id, Mission.name, Activity.start_time, Activity.end_time, Activity.type, Expenditure.id.label("expenditure_id"), Expenditure.type.label("expenditure_type"), func.generate_series( func.date_trunc( "day", func.timezone( tzname, func.timezone("UTC", Activity.start_time), ), ), func.timezone( tzname, func.coalesce( func.timezone("UTC", Activity.end_time), func.now(), ), ), "1 day", ).label("day"), ).filter( Mission.company_id == company_id, ~Activity.is_dismissed, Activity.start_time != Activity.end_time, )) query = _apply_time_range_filters( query, to_datetime(start_date, tz_for_date=tz), to_datetime(end_date, tz_for_date=tz, convert_dates_to_end_of_day_times=True), ) has_next_page = False if first: activity_first = max(first * 5, 200) query = query.order_by(desc("day"), desc( Activity.user_id)).limit(activity_first + 1) has_next_page = query.count() > activity_first query = query.subquery() query = (db.session.query(query).group_by( query.c.user_id, query.c.day, query.c.mission_id, query.c.name).with_entities( query.c.user_id.label("user_id"), query.c.day, func.timezone("UTC", func.timezone(tzname, query.c.day)).label("utc_day_start"), query.c.mission_id.label("mission_id"), query.c.name.label("mission_name"), func.min( func.greatest( query.c.start_time, func.timezone("UTC", func.timezone(tzname, query.c.day)), )).label("start_time"), func.max( func.least( func.timezone( "UTC", func.timezone( tzname, query.c.day + func.cast("1 day", Interval)), ), func.coalesce(query.c.end_time, func.now()), )).label("end_time"), func.bool_or( and_( query.c.end_time.is_(None), query.c.day == func.current_date(), )).label("is_running"), *[ func.sum( case( [( query.c.type == a_type.value, extract( "epoch", func.least( func.timezone( "UTC", func.timezone( tzname, query.c.day + func.cast("1 day", Interval), ), ), func.coalesce(query.c.end_time, func.now()), ) - func.greatest( query.c.start_time, func.timezone( "UTC", func.timezone(tzname, query.c.day), ), ), ), )], else_=0, )).label(f"{a_type.value}_duration") for a_type in ActivityType ], func.greatest(func.count(distinct(query.c.expenditure_id)), 1).label("n_exp_dups"), func.count(distinct(query.c.id)).label("n_act_dups"), *[ func.sum( case( [(query.c.expenditure_type == e_type.value, 1)], else_=0, )).label(f"n_{e_type.value}_expenditures") for e_type in ExpenditureType ], ).subquery()) query = (db.session.query(query).group_by( query.c.user_id, query.c.day).with_entities( query.c.user_id.label("user_id"), query.c.day, func.array_agg(distinct( query.c.mission_name)).label("mission_names"), func.min(query.c.start_time).label("start_time"), func.max(query.c.end_time).label("end_time"), func.bool_or(query.c.is_running).label("is_running"), *[ func.sum( getattr(query.c, f"{a_type.value}_duration") / query.c.n_exp_dups).cast(Integer).label( f"{a_type.value}_duration") for a_type in ActivityType ], *[ func.sum( getattr(query.c, f"n_{e_type.value}_expenditures") / query.c.n_act_dups).cast(Integer).label( f"n_{e_type.value}_expenditures") for e_type in ExpenditureType ], ).order_by(desc("day"), desc("user_id")).subquery()) query = db.session.query(query).with_entities( *query.c, extract("epoch", query.c.end_time - query.c.start_time).label("service_duration"), reduce( lambda a, b: a + b, [ getattr(query.c, f"{a_type.value}_duration") for a_type in ActivityType ], ).label("total_work_duration"), ) results = query.all() if after: results = [ r for r in results if r.day.date() < max_date or ( r.day.date() == max_date and r.user_id < user_id_) ] if first: if has_next_page: # The last work day may be incomplete because we didn't fetch all the activities => remove it results = results[:-1] if len(results) > first: results = results[:first] has_next_page = True return results, has_next_page