def test_cube_operators(self):
t = table('t', column('value'),
column('x'), column('y'), column('z'), column('q'))
stmt = select([func.sum(t.c.value)])
self.assert_compile(
stmt.group_by(func.cube(t.c.x, t.c.y)),
"SELECT sum(t.value) AS sum_1 FROM t GROUP BY CUBE(t.x, t.y)"
)
self.assert_compile(
stmt.group_by(func.rollup(t.c.x, t.c.y)),
"SELECT sum(t.value) AS sum_1 FROM t GROUP BY ROLLUP(t.x, t.y)"
)
self.assert_compile(
stmt.group_by(
func.grouping_sets(t.c.x, t.c.y)
),
"SELECT sum(t.value) AS sum_1 FROM t "
"GROUP BY GROUPING SETS(t.x, t.y)"
)
self.assert_compile(
stmt.group_by(
func.grouping_sets(
sql.tuple_(t.c.x, t.c.y),
sql.tuple_(t.c.z, t.c.q),
)
),
"SELECT sum(t.value) AS sum_1 FROM t GROUP BY "
"GROUPING SETS((t.x, t.y), (t.z, t.q))"
)
def process_audits(connection, user_id, caches, audits):
"""Process audits"""
snapshot_quads = create_snapshots(connection, user_id, caches, audits)
relationships_payload = []
if snapshot_quads:
snapshots = connection.execute(
select([snapshots_table]).where(
tuple_(
Snapshot.parent_type,
Snapshot.parent_id,
Snapshot.child_type,
Snapshot.child_id,
).in_(snapshot_quads))).fetchall()
snapshot_cache = {(obj_.parent_type, obj_.parent_id, obj_.child_type,
obj_.child_id): (obj_.id, obj_.context_id)
for obj_ in snapshots}
for snapshot in snapshot_quads:
relationships_payload += [{
"source_type":
snapshot[2],
"source_id":
snapshot[3],
"destination_type":
"Snapshot",
"destination_id":
snapshot_cache[snapshot][0],
"modified_by_id":
user_id,
"context_id":
snapshot_cache[snapshot][1],
}]
insert_payloads(connection, relationships=relationships_payload)
def process_audits(connection, user_id, caches, audits):
"""Process audits"""
snapshot_quads = create_snapshots(connection, user_id, caches, audits)
relationships_payload = []
if snapshot_quads:
snapshots = connection.execute(select([snapshots_table]).where(
tuple_(
Snapshot.parent_type,
Snapshot.parent_id,
Snapshot.child_type,
Snapshot.child_id,
).in_(snapshot_quads)
)).fetchall()
snapshot_cache = {
(obj_.parent_type, obj_.parent_id,
obj_.child_type, obj_.child_id): (obj_.id, obj_.context_id)
for obj_ in snapshots
}
for snapshot in snapshot_quads:
relationships_payload += [{
"source_type": snapshot[2],
"source_id": snapshot[3],
"destination_type": "Snapshot",
"destination_id": snapshot_cache[snapshot][0],
"modified_by_id": user_id,
"context_id": snapshot_cache[snapshot][1],
}]
insert_payloads(connection, relationships=relationships_payload)
def load_photos(user: User, limit: Optional[str] = None, offset: Optional[str] = None):
alias_ass = Assessment.alias('ass')
subq = db.select([Assessment.user_id]).where(Assessment.id == alias_ass.id).as_scalar()
query = db.select([Photo]).select_from(Photo.outerjoin(alias_ass, or_(
and_(alias_ass.user_id == user.id, alias_ass.photo_id == Photo.id), alias_ass.photo_id == None))). \
where(not_(tuple_(user.id).in_(subq)))
photos = limit_query(query, limit, offset)
return photos.gino
def test_cube_operators(self):
t = table(
"t",
column("value"),
column("x"),
column("y"),
column("z"),
column("q"),
)
stmt = select(func.sum(t.c.value))
self.assert_compile(
stmt.group_by(func.cube(t.c.x, t.c.y)),
"SELECT sum(t.value) AS sum_1 FROM t GROUP BY CUBE(t.x, t.y)",
)
self.assert_compile(
stmt.group_by(func.rollup(t.c.x, t.c.y)),
"SELECT sum(t.value) AS sum_1 FROM t GROUP BY ROLLUP(t.x, t.y)",
)
self.assert_compile(
stmt.group_by(func.grouping_sets(t.c.x, t.c.y)),
"SELECT sum(t.value) AS sum_1 FROM t "
"GROUP BY GROUPING SETS(t.x, t.y)",
)
self.assert_compile(
stmt.group_by(
func.grouping_sets(
sql.tuple_(t.c.x, t.c.y), sql.tuple_(t.c.z, t.c.q)
)
),
"SELECT sum(t.value) AS sum_1 FROM t GROUP BY "
"GROUPING SETS((t.x, t.y), (t.z, t.q))",
)
def get_revisions(connection, objects):
"""Get latest revisions of provided objects."""
revisions = select([
func.max(revisions_table.c.id),
revisions_table.c.resource_type,
revisions_table.c.resource_id,
]).where(
tuple_(
revisions_table.c.resource_type,
revisions_table.c.resource_id).in_(objects)
).group_by(revisions_table.c.resource_type, revisions_table.c.resource_id)
revisions_ = {
Stub(rtype, rid): id_ for id_, rtype, rid in
connection.execute(revisions).fetchall()
}
return revisions_
def get_revisions(connection, objects):
"""Get latest revisions of provided objects."""
revisions = select([
func.max(revisions_table.c.id),
revisions_table.c.resource_type,
revisions_table.c.resource_id,
]).where(
tuple_(
revisions_table.c.resource_type,
revisions_table.c.resource_id).in_(objects)
).group_by(revisions_table.c.resource_type, revisions_table.c.resource_id)
revisions_ = {
Stub(rtype, rid): id_ for id_, rtype, rid in
connection.execute(revisions).fetchall()
}
return revisions_
def _build_where(table, where_rpn):
''' selectのwhere句のクエリを作成
where_rpn (list): [column, value, operator, ...]
=> Reverse Polish Notation
(el.) WHERE id <= 5 AND user LIKE "%admin%"
=> ["id", 5, "<=", "user", "%admin%", "like", "and"]
'''
# 演算子定義
op = {
'<': lambda x, y: x < y,
'<=': lambda x, y: x <= y,
'>': lambda x, y: x > y,
'>=': lambda x, y: x >= y,
'=': lambda x, y: x == y,
'!=': lambda x, y: x != y,
'and': lambda x, y: and_(x, y),
'or': lambda x, y: or_(x, y),
'like': lambda x, y: x.like(y),
'in': lambda x, y: tuple_(x).in_([(e, ) for e in y]),
}
# 変数定義
op.update(table.columns)
# 逆ポーランド記法でクエリ生成
return RPN(where_rpn, op)[0]
def overlaps(a_pair, b_pair):
return tuple_(*a_pair).op('OVERLAPS')(tuple_(*b_pair))
def link_snapshots_to_objects(connection, user_id, caches, object_settings,
snapshot_quads):
"""Create relationships between snapshots and objects"""
# pylint: disable=too-many-locals
relationships_payload = []
audit_contexts = caches["audit_contexts"]
object_klass = object_settings["type"]
object_relationships = object_settings["object_relationships"]
object_select = object_settings["select_all"]
all_objects = connection.execute(object_select).fetchall()
if snapshot_quads:
snapshots = connection.execute(
select([snapshots_table]).where(
tuple_(
Snapshot.parent_type,
Snapshot.parent_id,
Snapshot.child_type,
Snapshot.child_id,
).in_(snapshot_quads))).fetchall()
snapshot_cache = {(obj_.parent_type, obj_.parent_id, obj_.child_type,
obj_.child_id): obj_.id
for obj_ in snapshots}
for object_ in all_objects:
key = Stub(object_klass, object_.id)
objects = object_relationships[key]
audit = [x for x in objects if x.type == "Audit"]
others = [x for x in objects if x.type in Types.all]
if len(audit) != 1:
continue
if audit:
audit = audit[0]
audit_context_id = audit_contexts[audit.id]
others = set(others)
for obj_ in others:
quad = ("Audit", audit.id, obj_.type, obj_.id)
if quad in snapshot_cache:
relationships_payload += [{
"source_type":
object_klass,
"source_id":
object_.id,
"destination_type":
"Snapshot",
"destination_id":
snapshot_cache[quad],
"modified_by_id":
user_id,
"context_id":
audit_context_id,
}, {
"source_type":
obj_.type,
"source_id":
obj_.id,
"destination_type":
"Snapshot",
"destination_id":
snapshot_cache[quad],
"modified_by_id":
user_id,
"context_id":
audit_context_id,
}]
else:
logger.warning(
"Couldn't map %s-%s to Snapshot of object %s-%s because it "
"doesn't exist due to missing revision.",
object_klass, object_.id, obj_.type, obj_.id)
insert_payloads(connection, relationships=relationships_payload)
def trigger_alerts():
from api.api import build_filtered_lead_selection
if request.remote_addr not in current_app.config['ALERT_TRIGGER_WHITELIST']:
return abort_json(401, 'Unauthorized')
freq = request.args.get('frequency', None)
if freq is not None and freq not in FREQS:
return abort_json(400, 'Invalid frequency specified.')
with engine().connect() as con:
# select all alerts where:
# 1. the recipient email is confirmed
# 2. the alert hasn't been sent in the current time period
confirmed = select(
[confirmed_emails.c.user_id, confirmed_emails.c.email]).cte()
where = tuple_(alerts_.c.user_id, alerts_.c.recipient).in_(confirmed)
if freq is not None:
where = and_(where, alerts_.c.frequency == FREQS[freq])
query = select([alerts_, func.max(sent_alerts.c.send_date).label('last_sent')])\
.select_from(alerts_.outerjoin(sent_alerts, sent_alerts.c.alert_id == alerts_.c.id))\
.where(where)\
.group_by(alerts_.c.id)
results = con.execute(query)
# these results satisfy #1, but not #2 yet
# however, we need to go row by row anyway because MySQL cannot match
# on column values (only plaintext)
for result in results:
row = dict(result)
if row['last_sent'] is not None and row['last_sent'] >= min_date_threshold(row['frequency']):
# has been sent more recently than we allow
print(
f"Last trigger for {row['id']} is too recent ({row['last_sent']}, {min_date_threshold(row['frequency'])})")
continue
with con.begin():
query = build_filtered_lead_selection(
filter_=row['filter'],
from_=None,
to=None,
sources={
key: row[f"{key}_source"]
for key in ['federal', 'regional', 'local']
},
page=None,
fields=[leads.c.id, annotated_leads.c.name],
where=[
annotated_leads.c.published_dt >= min_date_threshold(
row['frequency'], fudge=timedelta(0))
]
)
lead_results = con.execute(query)
lead_results = list(dict(row) for row in lead_results)
if len(lead_results) == 0:
print(f"Skipping alert {row['id']}. No new results.")
continue
# record alert sending
sent_alert = {
k: v
for k, v in row.items()
if k not in ['id', 'last_sent']
}
sent_alert['alert_id'] = row['id']
sent_alert['send_date'] = datetime.now()
sent_alert['db_link'] = build_db_url(sent_alert)
query = sent_alerts.insert().values( # pylint: disable=no-value-for-parameter
**sent_alert)
res = con.execute(query)
send_id = res.inserted_primary_key[0]
sent_alert['send_id'] = send_id
templates = render_alert(sent_alert, [{
'name': lead['name'],
'link': f'{BASE_URL}/lead/{lead["id"]}'
} for lead in islice(lead_results, None)])
sent_contents = [
{'send_id': send_id,
'lead_id': lead['id']}
for lead in lead_results
]
con.execute(sent_alert_contents.insert( # pylint: disable=no-value-for-parameter
), *sent_contents)
send_alert(sent_alert, *templates)
return {'status': 'ok'}
def link_snapshots_to_objects(connection, user_id,
caches, object_settings, snapshot_quads):
"""Create relationships between snapshots and objects"""
# pylint: disable=too-many-locals
relationships_payload = []
audit_contexts = caches["audit_contexts"]
object_klass = object_settings["type"]
object_relationships = object_settings["object_relationships"]
object_select = object_settings["select_all"]
all_objects = connection.execute(object_select).fetchall()
if snapshot_quads:
snapshots = connection.execute(select([snapshots_table]).where(
tuple_(
Snapshot.parent_type,
Snapshot.parent_id,
Snapshot.child_type,
Snapshot.child_id,
).in_(snapshot_quads)
)).fetchall()
snapshot_cache = {
(obj_.parent_type, obj_.parent_id,
obj_.child_type, obj_.child_id): obj_.id
for obj_ in snapshots
}
for object_ in all_objects:
key = Stub(object_klass, object_.id)
objects = object_relationships[key]
audit = [x for x in objects if x.type == "Audit"]
others = [x for x in objects if x.type in Types.all]
if len(audit) != 1:
continue
if audit:
audit = audit[0]
audit_context_id = audit_contexts[audit.id]
others = set(others)
for obj_ in others:
quad = ("Audit", audit.id, obj_.type, obj_.id)
if quad in snapshot_cache:
relationships_payload += [{
"source_type": object_klass,
"source_id": object_.id,
"destination_type": "Snapshot",
"destination_id": snapshot_cache[quad],
"modified_by_id": user_id,
"context_id": audit_context_id,
}, {
"source_type": obj_.type,
"source_id": obj_.id,
"destination_type": "Snapshot",
"destination_id": snapshot_cache[quad],
"modified_by_id": user_id,
"context_id": audit_context_id,
}]
else:
logger.warning(
"Couldn't map %s-%s to Snapshot of object %s-%s because it "
"doesn't exist due to missing revision.",
object_klass, object_.id, obj_.type, obj_.id
)
insert_payloads(connection, relationships=relationships_payload)
