def set_default(self):
if self.is_default:
return
(
Location.query.filter(Location.is_default | (Location.id == self.id)).update(
{"is_default": func.not_(Location.is_default)}, synchronize_session="fetch"
)
)
def set_default(self):
if self.is_default:
return
(Location.query.filter(Location.is_default
| (Location.id == self.id)).update(
{
'is_default': func.not_(
Location.is_default)
},
synchronize_session='fetch'))
class Operator:
OPERATORS: Dict[str, Callable] = {
"is_null": lambda f, a=None: f.is_(None),
"is_not_null": lambda f, a=None: f.isnot(None),
"==": lambda f, a: f == a,
"=": lambda f, a: f == a,
"eq": lambda f, a: f == a,
"!=": lambda f, a: f != a,
"<>": lambda f, a: f != a,
"ne": lambda f, a: f != a,
">": lambda f, a: f > a,
"gt": lambda f, a: f > a,
"<": lambda f, a: f < a,
"lt": lambda f, a: f < a,
">=": lambda f, a: f >= a,
"ge": lambda f, a: f >= a,
"<=": lambda f, a: f <= a,
"le": lambda f, a: f <= a,
"like": lambda f, a: f.like(a),
"ilike": lambda f, a: f.ilike(a),
"not_ilike": lambda f, a: ~f.ilike(a),
"in": lambda f, a: f.in_(a),
"not_in": lambda f, a: ~f.in_(a),
"any": lambda f, a: f.any(a),
"not_any": lambda f, a: func.not_(f.any(a)),
"INTERSECTS": lambda f, a: f.ST_Intersects(a),
"DISJOINT": lambda f, a: f.ST_Disjoint(a),
"CONTAINS": lambda f, a: f.ST_Contains(a),
"WITHIN": lambda f, a: f.ST_Within(a),
"TOUCHES": lambda f, a: f.ST_Touches(a),
"CROSSES": lambda f, a: f.ST_Crosses(a),
"OVERLAPS": lambda f, a: f.ST_Overlaps(a),
"EQUALS": lambda f, a: f.ST_Equals(a),
"RELATE": lambda f, a, pattern: f.ST_Relate(a, pattern),
"DWITHIN": lambda f, a, distance: f.ST_Dwithin(a, distance),
"BEYOND": lambda f, a, distance: ~f.ST_Dwithin(a, distance),
"+": lambda f, a: f + a,
"-": lambda f, a: f - a,
"*": lambda f, a: f * a,
"/": lambda f, a: f / a,
}
def __init__(self, operator: str = None):
if not operator:
operator = "=="
if operator not in self.OPERATORS:
raise Exception("Operator `{}` not valid.".format(operator))
self.operator = operator
self.function = self.OPERATORS[operator]
self.arity = len(signature(self.function).parameters)
def _aj_lon_lat(self, s, new=True):
start = to_time(self.nearby.start)
finish = to_time(self.nearby.finish)
agroup = s.query(ActivityGroup).filter(
ActivityGroup.name == self.nearby.activity_group).one()
lat = s.query(StatisticName.id). \
filter(StatisticName.name == LATITUDE, StatisticName.constraint == agroup).scalar()
lon = s.query(StatisticName.id). \
filter(StatisticName.name == LONGITUDE, StatisticName.constraint == agroup).scalar()
if not lat or not lon:
log.warning(
f'No {LATITUDE} or {LONGITUDE} in database for {agroup}')
return
sj_lat = inspect(StatisticJournal).local_table
sj_lon = alias(inspect(StatisticJournal).local_table)
sjf_lat = inspect(StatisticJournalFloat).local_table
sjf_lon = alias(inspect(StatisticJournalFloat).local_table)
aj = inspect(ActivityJournal).local_table
ns = inspect(ActivitySimilarity).local_table
existing_lo = select([ns.c.activity_journal_lo_id]). \
where(ns.c.constraint == self.nearby.constraint)
existing_hi = select([ns.c.activity_journal_hi_id]). \
where(ns.c.constraint == self.nearby.constraint)
existing = existing_lo.union(existing_hi).cte()
stmt = select([sj_lat.c.source_id, sjf_lon.c.value, sjf_lat.c.value]). \
select_from(sj_lat).select_from(sj_lon).select_from(sjf_lat).select_from(sjf_lat).select_from(aj). \
where(and_(sj_lat.c.source_id == sj_lon.c.source_id, # same source
sj_lat.c.time == sj_lon.c.time, # same time
sj_lat.c.source_id == aj.c.id, # and associated with an activity
aj.c.activity_group_id == agroup.id, # of the right group
sj_lat.c.id == sjf_lat.c.id, # lat sub-class
sj_lon.c.id == sjf_lon.c.id, # lon sub-class
sj_lat.c.statistic_name_id == lat, # lat name
sj_lon.c.statistic_name_id == lon, # lon name
sj_lat.c.time >= start.timestamp(), # time limits
sj_lat.c.time < finish.timestamp(),
sjf_lat.c.value > self.nearby.latitude - self.nearby.height / 2,
sjf_lat.c.value < self.nearby.latitude + self.nearby.height / 2,
sjf_lon.c.value > self.nearby.longitude - self.nearby.width / 2,
sjf_lon.c.value < self.nearby.longitude + self.nearby.width / 2))
if new:
stmt = stmt.where(func.not_(sj_lat.c.source_id.in_(existing)))
else:
stmt = stmt.where(sj_lat.c.source_id.in_(existing))
stmt = stmt.order_by(sj_lat.c.source_id) # needed for seen logic
yield from s.connection().execute(stmt)
def list_build_artifacts(
db,
build_id: int = None,
key: str = None,
excluded_artifact_types: List[schema.BuildArtifactType] = None,
):
filters = []
if build_id:
filters.append(orm.BuildArtifact.build_id == build_id)
if key:
filters.append(orm.BuildArtifact.key == key)
if excluded_artifact_types:
filters.append(
func.not_(
orm.BuildArtifact.artifact_type.in_(excluded_artifact_types)))
return db.query(orm.BuildArtifact).filter(*filters)
def _aj_lon_lat(self, s, new=True):
from ..owners import ActivityReader
lat = s.query(StatisticName.id). \
filter(StatisticName.name == N.LATITUDE,
StatisticName.owner == ActivityReader).scalar()
lon = s.query(StatisticName.id). \
filter(StatisticName.name == N.LONGITUDE,
StatisticName.owner == ActivityReader).scalar()
if not lat or not lon:
log.warning(f'No {N.LATITUDE} or {N.LONGITUDE} in database')
return
# todo - use tables() instead
sj_lat = inspect(StatisticJournal).local_table
sj_lon = alias(inspect(StatisticJournal).local_table)
sjf_lat = inspect(StatisticJournalFloat).local_table
sjf_lon = alias(inspect(StatisticJournalFloat).local_table)
aj = inspect(ActivityJournal).local_table
ns = inspect(ActivitySimilarity).local_table
existing_lo = select([ns.c.activity_journal_lo_id])
existing_hi = select([ns.c.activity_journal_hi_id])
existing = existing_lo.union(existing_hi).cte()
# todo - has not been tuned for latest schema
stmt = select([sj_lat.c.source_id, sjf_lon.c.value, sjf_lat.c.value]). \
select_from(sj_lat).select_from(sj_lon).select_from(sjf_lat).select_from(sjf_lat).select_from(aj). \
where(and_(sj_lat.c.source_id == sj_lon.c.source_id, # same source
sj_lat.c.time == sj_lon.c.time, # same time
sj_lat.c.source_id == aj.c.id, # and associated with an activity
sj_lat.c.id == sjf_lat.c.id, # lat sub-class
sj_lon.c.id == sjf_lon.c.id, # lon sub-class
sj_lat.c.statistic_name_id == lat,
sj_lon.c.statistic_name_id == lon))
if new:
stmt = stmt.where(func.not_(sj_lat.c.source_id.in_(existing)))
else:
stmt = stmt.where(sj_lat.c.source_id.in_(existing))
stmt = stmt.order_by(sj_lat.c.source_id) # needed for seen logic
yield from s.connection().execute(stmt)
class Operator(object):
OPERATORS = {
'is_null': lambda f: f.is_(None),
'is_not_null': lambda f: f.isnot(None),
'==': lambda f, a: f == a,
'eq': lambda f, a: f == a,
'!=': lambda f, a: f != a,
'ne': lambda f, a: f != a,
'>': lambda f, a: f > a,
'gt': lambda f, a: f > a,
'<': lambda f, a: f < a,
'lt': lambda f, a: f < a,
'>=': lambda f, a: f >= a,
'ge': lambda f, a: f >= a,
'<=': lambda f, a: f <= a,
'le': lambda f, a: f <= a,
'like': lambda f, a: f.like(a),
'ilike': lambda f, a: f.ilike(a),
'not_ilike': lambda f, a: ~f.ilike(a),
'in': lambda f, a: f.in_(a),
'not_in': lambda f, a: ~f.in_(a),
'any': lambda f, a: f.any(a),
'not_any': lambda f, a: func.not_(f.any(a)),
'json_contains':
lambda f, a: func.json_contains(f, func.json_array(a)),
}
def __init__(self, operator=None):
if not operator:
operator = '=='
if operator not in self.OPERATORS:
raise BadFilterFormat('Operator `{}` not valid.'.format(operator))
self.operator = operator
self.function = self.OPERATORS[operator]
self.arity = len(signature(self.function).parameters)
class Operator(object):
OPERATORS = {
"is_null": lambda f: f.is_(None),
"is_not_null": lambda f: f.isnot(None),
"==": lambda f, a: f == a,
"eq": lambda f, a: f == a,
"!=": lambda f, a: f != a,
"ne": lambda f, a: f != a,
">": lambda f, a: f > a,
"gt": lambda f, a: f > a,
"<": lambda f, a: f < a,
"lt": lambda f, a: f < a,
">=": lambda f, a: f >= a,
"ge": lambda f, a: f >= a,
"<=": lambda f, a: f <= a,
"le": lambda f, a: f <= a,
"like": lambda f, a: f.like(a),
"ilike": lambda f, a: f.ilike(a),
"not_ilike": lambda f, a: ~f.ilike(a),
"in": lambda f, a: f.in_(a),
"not_in": lambda f, a: ~f.in_(a),
"any": lambda f, a: f.any(a),
"not_any": lambda f, a: func.not_(f.any(a)),
}
def __init__(self, operator=None):
if not operator:
operator = "=="
if operator not in self.OPERATORS:
raise BadFilterFormat("Operator `{}` not valid.".format(operator))
self.operator = operator
self.function = self.OPERATORS[operator]
self.arity = len(signature(self.function).parameters)
# borrowed from sqlalchemy-filters
OPERATORS = {
'==': lambda f, a: f == a,
'!=': lambda f, a: f != a,
'>': lambda f, a: f > a,
'<': lambda f, a: f < a,
'>=': lambda f, a: f >= a,
'<=': lambda f, a: f <= a,
'like': lambda f, a: f.like(a),
'ilike': lambda f, a: f.ilike(a),
'not_ilike': lambda f, a: ~f.ilike(a),
'in': lambda f, a: f.in_(a),
'not_in': lambda f, a: ~f.in_(a),
'any': lambda f, a: f.any(a),
'not_any': lambda f, a: func.not_(f.any(a)),
}
@ns.route('/sequences/<string:species>/<string:genomic_datasets>')
@api.response(404, 'Reference sequence not found.')
class SequencesAPI(Resource):
@digby_protected()
@api.expect(filter_arguments, validate=True)
def get(self, species, genomic_datasets):
""" Returns nucleotide sequences from selected reference or multiple references (separate multiple reference names with ',') """
args = filter_arguments.parse_args(request)
required_cols = json.loads(args['cols'])
genomic_datasets = genomic_datasets.split(',')
ret = find_genomic_sequences(required_cols, genomic_datasets, species,
def all_user_accounts() -> List["Account"]:
return Account.query.filter(func.not_(Account.system)).all()
def hwscores(hwid):
"""The admin page to view the student score table of a given homework.
All users except the administrators will be listed on the table, even
if he or she does not upload any submission. Only the highest score
of a user will be displayed.
The view accepts a query string argument `csvfile`, and if `csvfile` is
set to 1, a csv data file will be responded to the visitor instead of
a html table page.
:route: /admin/hwscores/<hwid>/
:method: GET
:template: admin.csvdata.html
"""
def make_record(itm, hdr):
return tuple([getattr(itm, h) for h in hdr])
# Query about given homework
hw = g.homeworks.get_by_uuid(hwid)
if hw is None:
raise NotFound(lazy_gettext('Requested homework not found.'))
# Get max score for each user
q = (db.session.query(
Handin.user_id, Handin.state, User.name,
func.max(Handin.score * Handin.scale).label('score')).filter(
Handin.hwid == hwid).join(User).group_by(
Handin.user_id,
Handin.state).having(Handin.state == 'Accepted'))
# Show the report
raw_headers = ['name', 'score']
display_headers = [lazy_gettext('Username'), lazy_gettext('Score')]
pagetitle = _('Scores for "%(hw)s"', hw=hw.info.name)
filename = hw.info.name
if isinstance(filename, unicode):
filename = filename.encode('utf-8')
# Pre-process the data
# We need to display all users, even he does not submit anything!
user_query = db.session.query(User.id, User.name)
if not app.config['ADMIN_SCORE_IN_REPORT']:
user_query = user_query.filter(func.not_(User.is_admin))
users = sorted((u.name, u.id) for u in user_query)
user_scores = {}
for rec in q:
user_scores.setdefault(rec.user_id, 0.0)
if user_scores[rec.user_id] < rec.score:
user_scores[rec.user_id] = round_score(rec.score)
# Build data in user name ASC
csvdata = [{
'name': u[0],
'score': user_scores.get(u[1], '-')
} for u in users]
# Link users to their submission page
def LinkUser(idx, name):
if idx == 0:
return url_for('.handins_for_user', username=name)
return _make_csv_report(csvdata,
display_headers,
raw_headers,
pagetitle,
filename,
linker=LinkUser)
def hwscores(hwid):
"""The admin page to view the student score table of a given homework.
All users except the administrators will be listed on the table, even
if he or she does not upload any submission. Only the highest score
of a user will be displayed.
The view accepts a query string argument `csvfile`, and if `csvfile` is
set to 1, a csv data file will be responded to the visitor instead of
a html table page.
:route: /admin/hwscores/<hwid>/
:method: GET
:template: admin.csvdata.html
"""
def make_record(itm, hdr):
return tuple([getattr(itm, h) for h in hdr])
# Query about given homework
hw = g.homeworks.get_by_uuid(hwid)
if hw is None:
raise NotFound(lazy_gettext('Requested homework not found.'))
# Get max score for each user
q = (db.session.query(Handin.user_id,
Handin.state,
User.name,
func.max(Handin.score * Handin.scale).label('score')).
filter(Handin.hwid == hwid).
join(User).
group_by(Handin.user_id, Handin.state).
having(Handin.state == 'Accepted'))
# Show the report
raw_headers = ['name', 'score']
display_headers = [lazy_gettext('Username'), lazy_gettext('Score')]
pagetitle = _('Scores for "%(hw)s"', hw=hw.info.name)
filename = hw.info.name
if isinstance(filename, unicode):
filename = filename.encode('utf-8')
# Pre-process the data
# We need to display all users, even he does not submit anything!
user_query = db.session.query(User.id, User.name)
if not app.config['ADMIN_SCORE_IN_REPORT']:
user_query = user_query.filter(func.not_(User.is_admin))
users = sorted(
(u.name, u.id) for u in user_query
)
user_scores = {}
for rec in q:
user_scores.setdefault(rec.user_id, 0.0)
if user_scores[rec.user_id] < rec.score:
user_scores[rec.user_id] = round_score(rec.score)
# Build data in user name ASC
csvdata = [
{
'name': u[0],
'score': user_scores.get(u[1], '-')
}
for u in users
]
# Link users to their submission page
def LinkUser(idx, name):
if idx == 0:
return url_for('.handins_for_user', username=name)
return _make_csv_report(
csvdata,
display_headers,
raw_headers,
pagetitle,
filename,
linker=LinkUser
)
