def get_grade_info(self, all_snapshots):
grade = all_snapshots.c.grade
graduation_date = all_snapshots.c.graduation_date
school_year = all_snapshots.c.school_year
status = all_snapshots.c.status
student_lookup = all_snapshots.c.student_lookup
withdraw_reason = all_snapshots.c.withdraw_reason
end_grade = sql.case([(db_func.max(grade) > 12, 12)],
else_=db_func.max(grade))
start_grade = sql.case([(db_func.min(grade) > 12, 12)],
else_=db_func.min(grade))
return \
sql.select([
student_lookup,
(end_grade - start_grade).label('num_grades'),
(db_func.max(school_year) - db_func.min(school_year)).label('num_hs_years'),
start_grade.label('start_grade'),
end_grade.label('end_grade'),
db_func.min(school_year).label('start_year'),
db_func.max(school_year).label('end_year'),
db_func.min(graduation_date).label('graduation_date'),
db_func.array_agg(sql.distinct(status)).label('statuses'),
db_func.array_agg(sql.distinct(withdraw_reason)).label('withdraw_reasons'),
db_func.array_agg(sql.distinct(sql.func.substr(withdraw_reason, 1, 7))).label('withdraw_reasons_short'),
]).\
where(
grade >= 9
).\
group_by(
student_lookup
)
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 testing_function_3(query_module):
models_module = sqlalchemy_models
query = query_module.get_query(min(models_module.Author.id))
rows = query.all()
result = map(extract_row, rows)
return str(result)
def powa_getstatdata_db():
base_query = powa_base_statdata_db()
diffs = get_diffs_forstatdata()
return (select([column("dbid")] + diffs)
.select_from(base_query)
.group_by(column("dbid"))
.having(max(column("calls")) - min(column("calls")) > 0))
def report_root_ids(connection, root_ids):
rep = []
subq = sa.alias(find_children(root_ids))
q = (sa.select([
model.paths.c.uid.label('uid'),
model.paths.c.gid.label('gid'),
safunc.count().label('inodes'),
safunc.coalesce(safunc.sum(model.paths.c.size), 0).label('size'),
safunc.min(model.paths.c.last_seen).label('last seen'),
]).select_from(
model.paths.join(subq, subq.c.id == model.paths.c.id)).group_by(
model.paths.c.uid, model.paths.c.gid).order_by(sa.desc('size')))
for u in connection.execute(q):
u = dict(u)
u['user'] = pwd.getpwuid(u['uid']).pw_name
u['cn'] = pwd.getpwuid(u['uid']).pw_gecos
u['group'] = grp.getgrgid(u['gid']).gr_name
if u['last seen'] is not None:
u['last seen'] = datetime.fromtimestamp(u['last seen'])
rep.append(u)
return rep
def _get_active_pending_versions(self, mappedClass, uid):
"""
Returns the current active version and the pending version to review
"""
# TODO: Is this still needed?
def _check_mandatory_keys():
mandatory_keys = get_mandatory_keys(self.request, 'a')
log.debug(mandatory_keys)
# Get the current active version number
av = Session.query(
mappedClass.version
).\
filter(mappedClass.identifier == uid).\
filter(mappedClass.fk_status == 2).\
first()
active_version = av.version if av is not None else None
# Get the lowest pending version
pv = Session.query(min(mappedClass.version)).\
filter(mappedClass.identifier == uid).\
filter(mappedClass.fk_status == 1).\
first()
pending_version = pv.version if pv is not None else None
# Some logging
log.debug("active version: %s" % active_version)
log.debug("pending version: %s" % pending_version)
return active_version, pending_version
def caprate(self, attacker=None):
maxcap = PA.getfloat("roids", "maxcap")
mincap = PA.getfloat("roids", "mincap")
if not attacker or not self.value:
return maxcap
modifier = (float(self.value) / float(attacker.value)) ** 0.5
return max(mincap, min(maxcap * modifier, maxcap))
async def service_list(request):
company = request['company']
pagination, offset = get_pagination(request)
where = ser_c.company == company.id, apt_c.start > _today()
q1 = (
select([ser_c.id, ser_c.name, ser_c.colour, ser_c.extra_attributes, sql_f.min(apt_c.start).label('min_start')])
.select_from(sa_appointments.join(sa_services))
.where(and_(*where))
.group_by(ser_c.id)
.alias('q1')
)
conn = await request['conn_manager'].get_connection()
results = [
dict(row)
async for row in conn.execute(
select([q1.c.id, q1.c.name, q1.c.colour, q1.c.extra_attributes])
.select_from(q1)
.order_by(q1.c.min_start)
.offset(offset)
.limit(pagination)
)
]
cur_count = await conn.execute(
select([sql_f.count(distinct(ser_c.id))]).select_from(sa_appointments.join(sa_services)).where(and_(*where))
)
return json_response(
request,
results=results,
count=(await cur_count.first())[0],
)
def fetch_transactions(self, start_time, end_time):
session = self.__session()
try:
transactions = session.query(
Transaction.tx_site,
Transaction.tx_code,
functions.count(Transaction.tx_time).label('tx_size'),
functions.min(Transaction.tx_time).label('tx_time_min'),
functions.max(Transaction.tx_time).label('tx_time_max'),
functions.sum(Transaction.tx_inst).label('tx_net_inst'),
functions.sum(Transaction.tx_fund).label('tx_net_fund'),
functions.sum(func.abs(
Transaction.tx_inst)).label('tx_grs_inst'),
functions.sum(func.abs(
Transaction.tx_fund)).label('tx_grs_fund'),
).filter(Transaction.tx_time >= start_time,
Transaction.tx_time < end_time).group_by(
Transaction.tx_site,
Transaction.tx_code,
).subquery()
inst = aliased(Evaluation, name='ev_inst')
fund = aliased(Evaluation, name='ev_fund')
results = session.query(transactions, Product, inst, fund).join(
Product,
and_(
Product.pr_site == transactions.c.tx_site,
Product.pr_code == transactions.c.tx_code,
)).outerjoin(
inst,
and_(
inst.ev_site == Product.pr_site,
inst.ev_unit == Product.pr_inst,
)).outerjoin(
fund,
and_(
fund.ev_site == Product.pr_site,
fund.ev_unit == Product.pr_fund,
)).all()
finally:
session.close()
dto = namedtuple(
'TransactionDto',
('tx_site', 'tx_code', 'tx_size', 'tx_time_min', 'tx_time_max',
'tx_net_inst', 'tx_net_fund', 'tx_grs_inst', 'tx_grs_fund',
'product', 'ev_inst', 'ev_fund'))
return [dto(*r) for r in results]
def stats(self):
"""
select host, count(pk), min(created), max(created) from responses group by host;
"""
q = select([
responses.c.host.label('host'),
functions.count(responses.c.pk).label('amount'),
functions.min(responses.c.created),
functions.max(responses.c.created),
]).group_by('host').order_by(desc('amount'))
return self.db.execute(q).fetchall()
def on_get(self, req, resp):
dates = (self.db_session.query(
functions.min(ActivityLog.local_time),
functions.max(ActivityLog.local_time),
).all())
resp.body = WorkdayPublicSchema().dumps({
'min_date': dates[0][0],
'max_date': dates[0][1],
})
def powa_getwaitdata_db():
base_query = powa_base_waitdata_db()
return (select([
column("dbid"),
column("event_type"),
column("event"),
diff("count")
])
.select_from(base_query)
.group_by(column("dbid"), column("event_type"), column("event"))
.having(max(column("count")) - min(column("count")) > 0))
def handle(self, *args, **options):
# set up
config = get_config()
if config is None:
raise CommandError(
'Unable to process configuration file p_to_p.yml')
connection = get_connection(config)
pedsnet_session = init_pedsnet(connection)
init_pcornet(connection)
init_vocab(connection)
pedsnet_pcornet_valueset_map = aliased(ValueSetMap)
# extract the data from the death table
death_cause = pedsnet_session.query(DeathPedsnet.person_id,
func.left(DeathPedsnet.cause_source_value, 8),
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'),
bindparam("death_cause_type", "NI"),
bindparam("death_cause_source", "L"),
bindparam("death_cause_confidence", None),
min(DeathPedsnet.site)
) \
.join(Demographic, Demographic.patid == cast(DeathPedsnet.person_id, String(256)), ) \
.join(VocabularyConcept, VocabularyConcept.concept_id == DeathPedsnet.cause_concept_id) \
.outerjoin(pedsnet_pcornet_valueset_map,
and_(pedsnet_pcornet_valueset_map.source_concept_class == 'death cause code',
cast(VocabularyConcept.vocabulary_id, String(200)) ==
pedsnet_pcornet_valueset_map.source_concept_id)) \
.filter(and_(DeathPedsnet.cause_source_value != None,
DeathPedsnet.cause_source_concept_id != 44814650)) \
.group_by(DeathPedsnet.person_id, func.left(DeathPedsnet.cause_source_value, 8),
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \
.all()
# transform data to pcornet names and types
# load to demographic table
odo(death_cause,
DeathCause.__table__,
dshape=
'var * {patid: string, death_cause: string, death_cause_code: string,'
'death_cause_type: string, death_cause_source:string, '
'death_cause_confidence: string, site: string}')
# close session
pedsnet_session.close()
# output result
self.stdout.ending = ''
print('Death Cause ETL completed successfully',
end='',
file=self.stdout)
def __init__(self):
"""Find the date range and instantiate the data dictionary."""
connect_to_db(app)
self.min_year = db.session.query(
cast(func.min(extract('year', MedicalCall.received_dttm)), Integer)
).scalar()
self.max_year = db.session.query(
cast(func.max(extract('year', MedicalCall.received_dttm)), Integer)
).scalar()
self.data = {}
def powa_getstatdata_detailed_db(srvid):
base_query = powa_base_statdata_detailed_db()
diffs = get_diffs_forstatdata()
return (select([
column("srvid"),
column("queryid"),
column("dbid"),
column("userid"),
column("datname"),
] + diffs).select_from(base_query).where(
column("srvid") == srvid).group_by(
column("srvid"), column("queryid"), column("dbid"),
column("userid"), column("datname")).having(
max(column("calls")) - min(column("calls")) > 0))
def handle(self, *args, **options):
# set up
config = get_config()
if config is None:
raise CommandError('Unable to process configuration file p_to_p.yml')
connection = get_connection(config)
pedsnet_session = init_pedsnet(connection)
init_pcornet(connection)
init_vocab(connection)
pedsnet_pcornet_valueset_map = aliased(ValueSetMap)
# extract the data from the death table
death_cause = pedsnet_session.query(DeathPedsnet.person_id,
func.left(DeathPedsnet.cause_source_value, 8),
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'),
bindparam("death_cause_type", "NI"),
bindparam("death_cause_source", "L"),
bindparam("death_cause_confidence", None),
min(DeathPedsnet.site)
) \
.join(Demographic, Demographic.patid == cast(DeathPedsnet.person_id, String(256)), ) \
.join(VocabularyConcept, VocabularyConcept.concept_id == DeathPedsnet.cause_concept_id) \
.outerjoin(pedsnet_pcornet_valueset_map,
and_(pedsnet_pcornet_valueset_map.source_concept_class == 'death cause code',
cast(VocabularyConcept.vocabulary_id, String(200)) ==
pedsnet_pcornet_valueset_map.source_concept_id)) \
.filter(and_(DeathPedsnet.cause_source_value != None,
DeathPedsnet.cause_source_concept_id != 44814650)) \
.group_by(DeathPedsnet.person_id, func.left(DeathPedsnet.cause_source_value, 8),
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \
.all()
# transform data to pcornet names and types
# load to demographic table
odo(death_cause, DeathCause.__table__,
dshape='var * {patid: string, death_cause: string, death_cause_code: string,'
'death_cause_type: string, death_cause_source:string, '
'death_cause_confidence: string, site: string}'
)
# close session
pedsnet_session.close()
# output result
self.stdout.ending = ''
print('Death Cause ETL completed successfully', end='', file=self.stdout)
def nearby_earlier(s, ajournal, constraint=None, threshold=0.3):
ajlo = aliased(ActivityJournal)
ajhi = aliased(ActivityJournal)
q = s.query(ActivitySimilarity). \
join(ajhi, ActivitySimilarity.activity_journal_hi_id == ajhi.id). \
join(ajlo, ActivitySimilarity.activity_journal_lo_id == ajlo.id). \
filter(or_(ActivitySimilarity.activity_journal_hi_id == ajournal.id,
ActivitySimilarity.activity_journal_lo_id == ajournal.id),
or_(ajhi.id == ajournal.id, ajhi.start < ajournal.start),
or_(ajhi.id == ajournal.id, ajlo.start < ajournal.start),
ActivitySimilarity.similarity > threshold). \
order_by(desc(min(ajlo.start, ajhi.start)))
if constraint:
q = q.filter(ActivitySimilarity.constraint == constraint)
return [(asm.activity_journal_lo if asm.activity_journal_lo != ajournal else asm.activity_journal_hi, asm)
for asm in q.all()]
def handle(self, *args, **options):
# set up
config = get_config()
if config is None:
raise CommandError(
'Unable to process configuration file p_to_p.yml')
connection = get_connection(config)
pedsnet_session = init_pedsnet(connection)
init_pcornet(connection)
pedsnet_pcornet_valueset_map = aliased(ValueSetMap)
# extract the data from the death table
death_pedsnet = pedsnet_session.query(DeathPedsnet.death_date,
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'),
bindparam("death_match_confidence", None),
bindparam("death_source", "L"),
DeathPedsnet.person_id,
min(DeathPedsnet.site)
). \
outerjoin(pedsnet_pcornet_valueset_map,
and_(pedsnet_pcornet_valueset_map.source_concept_class == 'Death date impute',
cast(DeathPedsnet.death_impute_concept_id, String(200)) ==
pedsnet_pcornet_valueset_map.source_concept_id)) \
.filter(and_(exists().where(DeathPedsnet.person_id == PersonVisit.person_id),
DeathPedsnet.death_type_concept_id == 38003569)) \
.group_by(DeathPedsnet.person_id, DeathPedsnet.death_date,
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \
.all()
# transform data to pcornet names and types
# load to demographic table
odo(death_pedsnet,
DeathPcornet.__table__,
dshape=
'var * {death_date: date, death_date_impute: string, death_match_confidence: string,'
'death_source: string, patid:string, site: string}')
# close session
pedsnet_session.close()
# output result
self.stdout.ending = ''
print('Death ETL completed successfully', end='', file=self.stdout)
def handle(self, *args, **options):
# set up
config = get_config()
if config is None:
raise CommandError('Unable to process configuration file p_to_p.yml')
connection = get_connection(config)
pedsnet_session = init_pedsnet(connection)
init_pcornet(connection)
pedsnet_pcornet_valueset_map = aliased(ValueSetMap)
# extract the data from the death table
death_pedsnet = pedsnet_session.query(DeathPedsnet.death_date,
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT'),
bindparam("death_match_confidence", None),
bindparam("death_source", "L"),
DeathPedsnet.person_id,
min(DeathPedsnet.site)
). \
outerjoin(pedsnet_pcornet_valueset_map,
and_(pedsnet_pcornet_valueset_map.source_concept_class == 'Death date impute',
cast(DeathPedsnet.death_impute_concept_id, String(200)) ==
pedsnet_pcornet_valueset_map.source_concept_id)) \
.filter(and_(exists().where(DeathPedsnet.person_id == PersonVisit.person_id),
DeathPedsnet.death_type_concept_id == 38003569)) \
.group_by(DeathPedsnet.person_id, DeathPedsnet.death_date,
coalesce(pedsnet_pcornet_valueset_map.target_concept, 'OT')) \
.all()
# transform data to pcornet names and types
# load to demographic table
odo(death_pedsnet, DeathPcornet.__table__,
dshape='var * {death_date: date, death_date_impute: string, death_match_confidence: string,'
'death_source: string, patid:string, site: string}'
)
# close session
pedsnet_session.close()
# output result
self.stdout.ending = ''
print('Death ETL completed successfully', end='', file=self.stdout)
def summarise_tag(conn, tag, config):
"""
Provide a summary of a single tag
"""
path_ids = [get_path_id(p, conn) for p in config.get('paths', [])]
q = find_children(path_ids)
q = q.with_only_columns([
safunc.count().label('inodes'),
safunc.coalesce(safunc.sum(model.paths.c.size), 0).label('size'),
safunc.min(model.paths.c.last_seen).label('last seen'),
])
r = conn.execute(q).first()
r = dict(r)
if r['last seen'] is not None:
r['last seen'] = datetime.fromtimestamp(r['last seen'])
return tag, r
def heappop(self):
"""Pop the smallest item off the heap, maintaining the heap invariant."""
row = self.conn.execute(
select([
self.heapq.c.id,
min(self.heapq.c.cost).label("min_cost"), self.heapq.c.count,
self.heapq.c.data
]).where(self.heapq.c.instance == self.instance).group_by(
self.heapq.c.id, self.heapq.c.count,
self.heapq.c.data)).fetchone()
if row is not None:
self.conn.execute(
delete(self.heapq).where(self.heapq.c.id == row.id))
logger_memory.info(
f"HeapSQL: heappop min_cost: {row.min_cost} count: {row.count} "
)
return row.min_cost, row.count, pickle.loads(row.data)
else:
return None, None, None
def __init__(self,
tract_id=None,
min_date=None,
max_date=None):
connect_to_db(app)
if not min_date:
min_date = db.session.query(
func.min(MedicalCall.received_dttm)
).first()[0]
if not max_date:
max_date = db.session.query(
func.max(MedicalCall.received_dttm)
).first()[0]
self.min_date = min_date
self.max_date = max_date
if tract_id:
self.tract = tract_id
def get_states(
self,
*,
size: Optional[int] = None,
offset: int = 0,
selected_actors: Optional[List[str]] = None,
selected_statuses: Optional[List[str]] = None,
selected_message_ids: Optional[List[str]] = None,
selected_composition_ids: Optional[List[str]] = None,
start_datetime: Optional[datetime.datetime] = None,
end_datetime: Optional[datetime.datetime] = None,
sort_column: Optional[str] = None,
sort_direction: Optional[str] = None,
):
sort_column = sort_column or "enqueued_datetime"
sort_direction = sort_direction or "desc"
with self.client.begin() as session:
query = session.query(StoredState)
query = filter_query(
query=query,
selected_actors=selected_actors,
selected_statuses=selected_statuses,
selected_message_ids=selected_message_ids,
selected_composition_ids=selected_composition_ids,
start_datetime=start_datetime,
end_datetime=end_datetime,
)
if size is not None:
query = query.subquery()
query_group = (session.query(
max(query.c.composition_id).label(
"grouped_composition_id"),
max(query.c.message_id).label("grouped_message_id"),
max(query.c.status).label("grouped_status"),
max(query.c.actor_name).label("grouped_actor_name"),
max(query.c.priority).label("grouped_priority"),
func.avg(query.c.progress).label("grouped_progress"),
min(query.c.enqueued_datetime).label(
"grouped_enqueued_datetime"),
min(query.c.started_datetime).label(
"grouped_started_datetime"),
max(query.c.end_datetime).label("grouped_end_datetime"),
max(query.c.queue_name).label("grouped_queue_name"),
).group_by(coalesce(
query.c.composition_id, query.c.message_id)).order_by(
text(f"grouped_{sort_column} {sort_direction}")))
query_group = query_group.offset(offset).limit(size).subquery()
query = (
session.query(StoredState).select_from(StoredState).join(
query_group,
or_(
StoredState.message_id ==
query_group.c.grouped_message_id,
StoredState.composition_id ==
query_group.c.grouped_composition_id,
),
))
query = query.order_by(text(f"{sort_column} {sort_direction}"))
return [
state_model.as_state(self.encoder) for state_model in query
]
def diff(var):
return (max(column(var)) - min(column(var))).label(var)
def total_measure_interval(column):
return extract(
"epoch",
case([(min(column) == '0 second', '1 second')], else_=min(column)))
def bravery(self, target):
bravery = (
max(0, (min(2, float(target.value) / self.value) - 0.1) * (min(2, float(target.score) / self.score) - 0.2))
* 10
)
return bravery
def get_first_time(session):
res = session.query(cast(min(QueueLog.time), TIMESTAMP)).first()[0]
if res is None:
raise LookupError('Table is empty')
return res
def set_score():
if not is_request_valid(request):
abort(400)
commands = [
"help",
"score",
"past_scores",
"compare_scores",
"today",
"my_best",
"top10",
"leaderboard",
]
user_id = request.form["user_id"]
user_name = request.form["user_name"]
text_input = request.form["text"]
if text_input:
params = text_input.split()
else:
return jsonify(
response_type="ephermal",
type="section",
text=
"I didn't catch that! Type 'help' for a list of appropriate commands",
)
if params[0] not in commands:
return jsonify(
response_type="ephemeral",
type="section",
text=
"I didn't catch that! Type 'help' for a list of appropriate commands",
)
if params[0] == "help":
return jsonify(
response_type="ephemeral",
text=
"Here are some commands you can try:\n *score* [set score format including backticks and hours]\n *past_scores*\n *compare_scores* [slack username]\n *today*",
)
if not User.query.filter_by(slack_userid=user_id).first():
user = User(slack_userid=user_id, slack_username=user_name)
db.session.add(user)
db.session.commit()
else:
user = User.query.filter_by(slack_userid=user_id).first()
if params[0] == "score":
input_value = text_input.split(" `")
try:
value = arrow.Arrow.strptime(
input_value[1],
"%H hours %M minutes and %S.%f seconds`").time()
except Exception as e:
return jsonify(
response_type="ephemeral",
text=
"*Uh oh*, I didn't catch that! Please input your score in a code block using backticks, in set score form ex: `0 hours 00 minutes and 0.00 seconds`",
)
score = Score(orig_input=f"`{input_value[1]}", user=user, value=value)
db.session.add(score)
db.session.commit()
congrats = [
"Good job!",
"Well done!",
"Very impressive.",
"Something something blind dog sunshine something.",
]
return jsonify(
response_type="in_channel",
text=f"Thanks *{user.slack_username}*! {random.choice(congrats)}",
)
if params[0] == "past_scores":
return_text = ""
try:
scores = user.set_scores.all()
print(scores)
except:
return jsonify(
response_type="ephemeral",
text=
"You don't have any scores yet! Add one by using `*/set_score*`!",
)
for val in scores:
print(val.value)
return_text += f'{val.value.strftime("`%H hours %M minutes and %S.%f seconds`")} on *{val.timestamp.strftime("%c")}*\n'
print(
f'{val.value.strftime("`%H hours %M minutes and %S.%f seconds`")} on *{val.timestamp.strftime("%c")}*\n'
)
return jsonify(
type="section",
response_type="ephemeral",
text=f"Here are your past scores!\n{return_text}",
)
if params[0] == "compare_scores":
if not params[1]:
return jsonify(
type="section",
response_type="ephemeral",
text=
"*Oops!* Looks like you didn't tell me who you'd like to compare yourself to. Try `compare_scores` followed by `<slack username>`",
)
compare_username = params[1]
compare_user = User.query.filter_by(
slack_username=compare_username).first()
if not compare_user:
return jsonify(
type="section",
response_type="ephemeral",
text=
"*Oh no!* Either that's not a valid username, or that user hasn't played yet! Try again.",
)
compare_user_scores = compare_user.set_scores.all()
# user_df = pd.read_sql('SELECT * FROM User', db.session.bind)
# scores_df = pd.read_sql('SELECT * FROM Score', db.session.bind)
# merge_df = pd.merge(df, df1, left_on='id', right_on='user_id')
# print(compare_user_scores)
return jsonify(
type="section",
response_type="in_channel",
text=f"{compare_user.slack_userid, compare_user.slack_username}",
)
if params[0] == "my_best":
scores = user.set_scores.order_by(Score.value).all()
if scores:
best_time = scores.pop(0)
return_text = f"*{best_time.timestamp.strftime('%c')}* - {best_time.orig_input} \U0001F451 \n"
for s in scores[1:5]:
return_text += f"*{s.timestamp.strftime('%c')}* - {s.orig_input}\n"
return jsonify(
type="section",
response_type="ephemeral",
text=f"Your best scores at set are ~ \n\n {return_text}",
)
else:
return jsonify(
type="section",
response_type="in_channel",
text=
"No scores found for you. Input your score using `/set score`.",
)
if params[0] == "top10":
scores = Score.query.order_by(Score.value).all()
if scores:
best_time = scores.pop(0)
return_text = f"*{best_time.timestamp.strftime('%c')}* - {best_time.user.slack_username} - {best_time.orig_input} \U0001F451 \n"
for s in scores[0:9]:
return_text += f"*{s.timestamp.strftime('%c')}* - {s.user.slack_username} - {s.orig_input}\n"
return jsonify(
type="section",
response_type="ephemeral",
text=f"Top 10 overall scores are ~ \n\n {return_text}",
)
else:
return jsonify(
type="section",
response_type="ephemeral",
text=
"No scores found yet. Input your score using `/set score`.",
)
if params[0] == "leaderboard":
scores = (Score.query.group_by(Score.user_id).having(
min(Score.value) > 0).order_by(Score.value).all())
if scores:
best_time = scores.pop(0)
return_text = f"*{best_time.timestamp.strftime('%c')}* - {best_time.user.slack_username} - {best_time.orig_input} \U0001F451 \n"
for s in scores[0:9]:
return_text += f"*{s.timestamp.strftime('%c')}* - {s.user.slack_username} - {s.orig_input}\n"
return jsonify(
type="section",
response_type="ephemeral",
text=f"Top 10 Personal Bests are ~ \n\n {return_text}",
)
else:
return jsonify(
type="section",
response_type="ephemeral",
text=
"No scores found yet. Input your score using `/set score`.",
)
if params[0] == "today":
todays_datetime = (arrow.now("US/Pacific").replace(
hour=0, minute=0, second=0, microsecond=0).datetime)
scores = (Score.query.filter(
Score.timestamp >= todays_datetime).order_by(Score.value).all())
if scores:
winner = scores.pop(0)
return_text = (
f"*{winner.user.slack_username}:* {winner.orig_input} \U0001F451 \n"
)
print(return_text)
for s in scores:
return_text += f"*{s.user.slack_username}:* {s.orig_input}\n"
return jsonify(
type="section",
response_type="in_channel",
text=f"So far today the scores are ~ \n\n {return_text}",
)
else:
return jsonify(
type="ephemeral",
response_type="in_channel",
text=
"No scores have been recorded yet today! Input your score using `/set score`.",
)
def get_hint(self, flag_name: str, user_id: int):
now = datetime.datetime.utcnow()
if flag_name is None:
logging.error("FLASK : get_hint provided with None flag_name.")
return None
session = Session()
awake, mins = self.is_awake()
if not awake:
return f"The hintbot will be activated in {mins} minutes."
# get the desired flag
flag = session.query(Solution).filter_by(flag_name=flag_name).first()
if flag is None:
return f"{flag_name} is not a flag."
# how long has it been since they last asked for a hint with this flag?
query = session.query(sqlfunc.min(hintreqs.columns.time))
query = query.filter(hintreqs.columns.solu_id == flag.solu_id,
hintreqs.columns.user_id == user_id)
first_req_time = query.first()[0]
# getting all our hints for the flag
query = session.query(Hint).filter_by(solu_id=flag.solu_id)
flag_hints = query.order_by(Hint.strength.asc()).all()
# hint calculation
hint = None
time_to_next = None
# skip if there are no hints for the flag
if len(flag_hints) > 0:
# if they haven't asked before, or asked before activation,
# give them the lowest strength
if (first_req_time is None or
(first_req_time - self.start).seconds < self.sec_til_live):
hint = flag_hints[0]
else:
# calculate where the user is in the hint wait cycle
i = 0
time = 0
while hint is None and i < len(flag_hints):
time = time + flag_hints[1].minutes_live
mins_since_first = ((now - first_req_time).seconds // 60)
if mins_since_first < time:
hint = flag_hints[i]
if i != len(flag_hints) - 1:
time_to_next = time - mins_since_first
i = i + 1
# if hint is still none, the user is past the end of the cycle,
# so give them the final hint
if hint is None:
hint = flag_hints[-1]
time_to_next = None
# stripping the hint down to text so we can close the session
if hint is not None:
hint = hint.hint
session.close()
# no hints available for the flag
if hint is None:
return f"No hints available for this flag."
# add the time to next hint, if there is one
if time_to_next is not None:
suffix = ""
if time_to_next > 1:
suffix = "s"
return (hint + " The next hint will be available " +
f"in {time_to_next} minute{suffix}.")
else:
return hint
def hints():
awake, mins = hintbot.is_awake()
if not awake:
session = Session()
query = session.query(Chattext).filter_by(usage="c_u_ltr")
text = query.order_by(sqlfunc.random()).first()
session.close()
if text is None:
return Response("Sorry, I have no response.",
status=503,
mimetype="text/plain")
else:
return text.ctext.format(minutes=mins)
if request.content_length < 1:
app.logger.info("-- recieved empty request or non-text mime type")
return Response("nope.", status=400, mimetype="text/plain")
# get user uuid from query string
try:
user_uuid = str(uuid.UUID(request.args.get("uuid")))
except ValueError:
return Response("Not a user.", status=400, mimetype="text/plain")
# get user's "chat"
try:
usrmsg = request.data.decode("utf-8")
except Exception:
return Response("I only speak ascii.",
status=400,
mimetype="text/plain")
# get current time
now = datetime.datetime.utcnow()
session = Session()
# check that user exists
user = session.query(User).filter_by(uuid=user_uuid).first()
if user is None:
session.close()
return Response("That user does not exist.",
status=400,
mimetype="text/plain")
# scan user chat for the name of a flag
flag = None
sols = session.query(Solution).all()
for sol in sols:
if usrmsg.find(sol.flag_name) >= 0:
if flag is None or len(sol.flag_name) > len(flag.flag_name):
flag = sol
if flag is None:
chatmsg = None
info = None
prev_reqs = session.query(Hintreq).filter_by(
user_id=user.user_id).all()
if len(prev_reqs) == 0:
query = session.query(Chattext).filter_by(usage="hello_start")
text1 = query.order_by(sqlfunc.random()).first()
query = session.query(Chattext).filter_by(usage="hello_end")
text2 = query.order_by(sqlfunc.random()).first()
if text1 is None or text2 is None:
chatmsg = None
else:
chatmsg = text1.ctext + text2.ctext
info = [user.uname]
else:
query = session.query(Chattext).filter_by(usage="convo")
text = query.order_by(sqlfunc.random()).first()
if text is None or random.random() >= 0.5:
fortune = subprocess.run(["/usr/games/fortune", "-s"],
capture_output=True)
chatmsg = fortune.stdout.decode("utf-8").strip()
else:
chatmsg = text.ctext
# get total points
query = session.query(sqlfunc.sum(solutions.columns.point_val))
query = query.filter(
correct.columns.user_id == user.user_id,
correct.columns.solu_id == solutions.columns.solu_id)
total_points = query.first()[0]
if total_points is None:
total_points = 0
# get first hint time
query = session.query(sqlfunc.min(hintreqs.columns.time))
first_hint = query.filter(
hintreqs.columns.user_id == user.user_id).first()[0]
# provide info for conversation
info = [user.uname, total_points, (now - first_hint).seconds // 60]
ureq = Hintreq(user_id=user.user_id, time=now, solu_id=None)
session.add(ureq)
session.commit()
session.close()
if chatmsg is None:
return Response("Sorry, I have no response.",
status=503,
mimetype="text/plain")
return chatmsg.format(info=info)
# if asked about a flag, link the hint request entry with it
ureq = Hintreq(user_id=user.user_id, time=now, solu_id=flag.solu_id)
session.add(ureq)
session.commit()
hint = hintbot.get_hint(flag.flag_name, user.user_id)
# record request on the mqtt server
if mclient is not None:
mclient.publish(
"hints",
payload=
f"{user.uname} has asked for a hint on flag {flag.flag_name}.",
qos=0,
retain=False)
session.close()
if hint is None:
return "Sorry, no hints are available for this flag."
return hint
def diff(var):
return (max(column(var)) - min(column(var))).label(var)
def sql(self):
return sql(compile_expr([
saf.min(self.expr)
]))
def resources_per_agent(self, target):
return min(10000, (target.value * 2000) / self.value)
def reconstruct_idea_history(db):
from assembl.models import (
Idea, IdeaLink, Discussion, SynthesisPost, Synthesis,
SubGraphIdeaLinkAssociation)
start_by_discussion = dict(db.query(Discussion.id, sqlfunc.min(Idea.creation_date)).join(Idea).filter(Idea.sqla_type != 'assembl:RootIdea').all())
ideas=db.query(Idea).all()
idea_links=db.query(IdeaLink).all()
synthesis_dates = dict(db.query(IdeaLink.id, SynthesisPost.creation_date).join(SubGraphIdeaLinkAssociation).join(Synthesis).join(SynthesisPost).all())
link_by_id = {l.id: l for l in idea_links}
link_ids = link_by_id.keys()
link_ids.sort()
end = datetime.datetime.now()
min_creation = {
l.id: max(l.source_ts.creation_date, l.target_ts.creation_date) for l in idea_links
}
max_end = {id:end for id in link_ids}
for id, date in synthesis_dates.iteritems():
min_creation[id] = date
max_end[id] = date
link_by_id[id].tombstone_date = date
# monotonicity of start
last_date = min_creation[link_ids[0]]
for id in link_ids:
last_date = max(last_date, min_creation[id])
min_creation[id] = last_date
# arbitrary increment
if last_date < max_end[id]:
last_date = min(last_date+datetime.timedelta(seconds=10),
max_end[id])
# child is target. So no two links with same target can coexist.
by_target = defaultdict(list)
ends_before = {}
for l in idea_links:
by_target[l.target_id].append(l.id)
live_ids = {}
# if same target, one replaces the other.
for l in by_target.itervalues():
l.sort()
live_id=[id for id in l if not link_by_id[id].is_tombstone]
assert len(live_id) < 2
if len(live_id):
live_id = live_id[0] if live_id else l[-1]
non_synth = [id for id in l if id not in synthesis_dates]
if not non_synth[-1] == live_id:
import pdb; pdb.set_trace()
else:
live_id = l[-1]
live_ids.update({id:live_id for id in l})
last_id = None
for id in l:
link = link_by_id[id]
link.base_id = live_id
if last_id:
ends_before[last_id] = id
if id in synthesis_dates:
continue
last_id = id
max_end[last_id] = min(min_creation[id], max_end[last_id])
assert last_id == live_id
for link in idea_links:
if link.is_tombstone:
link.tombstone_date = max_end[link.id]
# for id in link_ids:
# print id, min_creation[id] != max_end[id]
# for id,date in max_end.iteritems():
# print id, id in synthesis_dates, date if link_by_id[id].is_tombstone else None
dead_ideas=[idea for idea in ideas if idea.is_tombstone]
for idea in dead_ideas:
links = db.query(IdeaLink.id).filter((IdeaLink.source_id == idea.id) | (IdeaLink.target_id == idea.id)).all()
idea.tombstone_date = max((max_end[id] for (id,) in links))
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 total_measure_interval(column):
return extract(
"epoch",
case([(min(column) == '0 second', '1 second')],
else_=min(column)))
def fromCollections(cls,
registry,
datasetType,
collections,
addResultColumns=True):
"""Construct a builder that searches a multiple collections for
datasets single dataset type.
Parameters
----------
registry : `SqlRegistry`
Registry instance the query is being run against.
datasetType : `DatasetType`
`DatasetType` of the datasets this query searches for.
collections : `list` of `str`
List of collections to search, ordered from highest-priority to
lowest.
addResultColumns : `bool`
If `True` (default), add result columns to ``self.resultColumns``
for the dataset ID and dimension links used to identify this
`DatasetType.
Returns
-------
builder : `SingleDatasetQueryBuilder`
New query builder instance initialized with a
`~QueryBuilder.fromClause` that either directly includes the
dataset table or includes a subquery equivalent.
Notes
-----
If ``len(collections)==1``, this method simply calls
`fromSingleCollection`.
If there are multiple collections, then there can be multiple matching
Datasets for the same DataId. In that case we need only one Dataset
record, which comes from earliest collection (in the user-provided
order). Here things become complicated; we have to:
- replace collection names with their order in input list
- select all combinations of rows from dataset and dataset_collection
which match collection names and dataset type name
- from those only select rows with lowest collection position if
there are multiple collections for the same DataId
Replacing collection names with positions is easy:
SELECT dataset_id,
CASE collection
WHEN 'collection1' THEN 0
WHEN 'collection2' THEN 1
...
END AS collorder
FROM dataset_collection
Combined query will look like (CASE ... END is as above):
SELECT dataset.dataset_id AS dataset_id,
CASE dataset_collection.collection ... END AS collorder,
dataset.link1,
...
dataset.linkN
FROM dataset JOIN dataset_collection
ON dataset.dataset_id = dataset_collection.dataset_id
WHERE dataset.dataset_type_name = <dsType.name>
AND dataset_collection.collection IN (<collections>)
Filtering is complicated; it would be simpler to use Common Table
Expressions (WITH clause) but not all databases support CTEs, so we
will have to do with the repeating sub-queries. We use GROUP BY for
the data ID (link columns) and MIN(collorder) to find ``collorder``
for a particular DataId, then join it with previous combined selection:
SELECT
DS.dataset_id AS dataset_id,
DS.link1 AS link1,
...
DS.linkN AS linkN
FROM (
SELECT dataset.dataset_id AS dataset_id,
CASE ... END AS collorder,
dataset.link1,
...
dataset.linkN
FROM dataset JOIN dataset_collection
ON dataset.dataset_id = dataset_collection.dataset_id
WHERE dataset.dataset_type_name = <dsType.name>
AND dataset_collection.collection IN (<collections>)
) DS
INNER JOIN (
SELECT
MIN(CASE ... END AS) collorder,
dataset.link1,
...
dataset.linkN
FROM dataset JOIN dataset_collection
ON dataset.dataset_id = dataset_collection.dataset_id
WHERE dataset.dataset_type_name = <dsType.name>
AND dataset_collection.collection IN (<collections>)
GROUP BY (
dataset.link1,
...
dataset.linkN
)
) DSG
ON (DS.colpos = DSG.colpos
AND
DS.link1 = DSG.link1
AND
...
AND
DS.linkN = DSG.linkN)
"""
if len(collections) == 1:
return cls.fromSingleCollection(registry,
datasetType,
collections[0],
addResultColumns=addResultColumns)
# helper method
def _columns(selectable, names):
"""Return list of columns for given column names"""
return [selectable.columns[name].label(name) for name in names]
datasetTable = registry._schema.tables["dataset"]
datasetCollectionTable = registry._schema.tables["dataset_collection"]
# full set of link names for this DatasetType
links = list(datasetType.dimensions.links())
# Starting point for both subqueries below: a join of dataset to
# dataset_collection
subJoin = datasetTable.join(
datasetCollectionTable, datasetTable.columns.dataset_id ==
datasetCollectionTable.columns.dataset_id)
subWhere = and_(
datasetTable.columns.dataset_type_name == datasetType.name,
datasetCollectionTable.columns.collection.in_(collections))
# CASE clause that transforms collection name to position in the given
# list of collections
collorder = case([(datasetCollectionTable.columns.collection == coll,
pos) for pos, coll in enumerate(collections)])
# first GROUP BY sub-query, find minimum `collorder` for each DataId
columns = [functions.min(collorder).label("collorder")] + _columns(
datasetTable, links)
groupSubq = select(columns).select_from(subJoin).where(subWhere)
groupSubq = groupSubq.group_by(*links)
groupSubq = groupSubq.alias("sub1" + datasetType.name)
# next combined sub-query
columns = [collorder.label("collorder")] + _columns(
datasetTable, ["dataset_id"] + links)
combined = select(columns).select_from(subJoin).where(subWhere)
combined = combined.alias("sub2" + datasetType.name)
# now join these two
joinsOn = [groupSubq.columns.collorder == combined.columns.collorder] + \
[groupSubq.columns[colName] == combined.columns[colName] for colName in links]
return cls(registry,
fromClause=combined.join(groupSubq, and_(*joinsOn)),
datasetType=datasetType,
selectableForDataset=combined,
addResultColumns=addResultColumns)
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
def get_first_time(session):
res = session.query(cast(min(QueueLog.time), TIMESTAMP)).first()[0]
if res is None:
raise LookupError('Table is empty')
return res
