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 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 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
def get_query_column(self, entity):
return func.bool_or(self.resolve_entity_column(entity))
