def test_tuple_is_aggregate(self):
with self.subTest(
'None if single argument returns None for is_aggregate'):
self.assertEqual(None, Tuple(0).is_aggregate)
self.assertEqual(None, Tuple(Coalesce('col')).is_aggregate)
with self.subTest(
'None if multiple arguments all return None for is_aggregate'):
self.assertEqual(None, Tuple(0, 'a').is_aggregate)
self.assertEqual(
None,
Tuple(Coalesce('col'), NullIf('col2', 0)).is_aggregate)
with self.subTest(
'True if single argument returns True for is_aggregate'):
self.assertEqual(True, Tuple(Sum('col')).is_aggregate)
with self.subTest(
'True if multiple arguments return True for is_aggregate'):
self.assertEqual(True, Tuple(Sum('col'), Sum('col2')).is_aggregate)
with self.subTest(
'True when mix of arguments returning None and True for is_aggregate'
):
self.assertEqual(
True,
Tuple(Coalesce('col'), Coalesce('col2', 0),
Sum('col3')).is_aggregate)
with self.subTest(
'False when one of the arguments returns False for is_aggregate'
):
self.assertEqual(False,
Tuple(Field('col1'), Sum('col2')).is_aggregate)
def test_tuple_is_aggregate(self):
with self.subTest(
"None if single argument returns None for is_aggregate"):
self.assertEqual(None, Tuple(0).is_aggregate)
self.assertEqual(None, Tuple(Coalesce("col")).is_aggregate)
with self.subTest(
"None if multiple arguments all return None for is_aggregate"):
self.assertEqual(None, Tuple(0, "a").is_aggregate)
self.assertEqual(
None,
Tuple(Coalesce("col"), NullIf("col2", 0)).is_aggregate)
with self.subTest(
"True if single argument returns True for is_aggregate"):
self.assertEqual(True, Tuple(Sum("col")).is_aggregate)
with self.subTest(
"True if multiple arguments return True for is_aggregate"):
self.assertEqual(True, Tuple(Sum("col"), Sum("col2")).is_aggregate)
with self.subTest(
"True when mix of arguments returning None and True for is_aggregate"
):
self.assertEqual(
True,
Tuple(Coalesce("col"), Coalesce("col2", 0),
Sum("col3")).is_aggregate)
with self.subTest(
"False when one of the arguments returns False for is_aggregate"
):
self.assertEqual(False,
Tuple(Field("col1"), Sum("col2")).is_aggregate)
async def test_filter_by_aggregation_field_coalesce(self):
await Tournament.create(name="1", desc="demo")
await Tournament.create(name="2")
tournaments = await Tournament.annotate(
clean_desc=Coalesce("desc", "demo")).filter(clean_desc="demo")
self.assertEqual(len(tournaments), 2)
self.assertSetEqual({(t.name, t.clean_desc)
for t in tournaments}, {("1", "demo"),
("2", "demo")})
async def test_filter_by_aggregation_field_comparison_coalesce_numeric(
self):
await IntFields.create(intnum=3, intnum_null=10)
await IntFields.create(intnum=1, intnum_null=4)
await IntFields.create(intnum=2)
ints = await IntFields.annotate(
clean_intnum_null=Coalesce("intnum_null", 0)
).filter(clean_intnum_null__gt=0)
self.assertEqual(len(ints), 2)
self.assertSetEqual({i.clean_intnum_null for i in ints}, {10, 4})
async def test_filter_by_aggregation_field_coalesce_numeric(self):
await IntFields.create(intnum=1, intnum_null=10)
await IntFields.create(intnum=4)
ints = await IntFields.annotate(
clean_intnum_null=Coalesce("intnum_null", 0)
).filter(clean_intnum_null__in=(0, 10))
self.assertEqual(len(ints), 2)
self.assertSetEqual(
{(i.intnum_null, i.clean_intnum_null)
for i in ints}, {(None, 0), (10, 10)})
async def run():
Tortoise.init(db_url="sqlite://:memory:", modules={"models": ["__main__"]})
await Tortoise.open_connections()
await Tortoise.generate_schemas()
tournament = await Tournament.create(name="New Tournament", desc="great")
await tournament.save()
await Tournament.create(name="Second tournament")
await Tournament.create(name=" final tournament ")
await Event(name="Without participants",
tournament_id=tournament.id).save()
event = Event(name="Test", tournament_id=tournament.id)
await event.save()
participants = []
for i in range(2):
team = Team(name=f"Team {(i + 1)}")
await team.save()
participants.append(team)
await event.participants.add(participants[0], participants[1])
await event.participants.add(participants[0], participants[1])
print(await Tournament.all().annotate(events_count=Count("events")
).filter(events_count__gte=1))
print(await Event.filter(id=event.id).first().annotate(
lowest_team_id=Min("participants__id")))
print(await Tournament.all().annotate(events_count=Count("events")
).order_by("events_count"))
print(await Event.all().annotate(tournament_test_id=Sum("tournament__id")
).first())
print(await Tournament.annotate(clean_desciption=Coalesce("desc")
).filter(clean_desciption=""))
print(await Tournament.annotate(trimmed_name=Trim("name")
).filter(trimmed_name="final tournament"))
print(await
Tournament.annotate(name_len=Length("name")
).filter(name_len__gt=len("New Tournament")))
print(await Tournament.annotate(name_lo=Lower("name")
).filter(name_lo="new tournament"))
print(await Tournament.annotate(name_lo=Upper("name")
).filter(name_lo="NEW TOURNAMENT"))
def try_handle_deprecated_call(
itgs: LazyItgs,
request: Request,
endpoint_slug: str,
user_id: int = None) -> Response:
"""Attempts to fully handle the deprecated call. If the underlying
functionality on this call should not be provided then this will
return the Response that should be given instead.
Arguments:
- `itgs (LazyItgs)`: The lazy integrations to use for connecting to
networked components.
- `request (Request)`: The underlying starlette request, which we will
use for checking for shared query parameters like `deprecated`.
- `endpoint_slug (str)`: The internal name we have for this
endpoint, which will let us find the description, reason for deprecation,
and list of alternatives in the database (main table: `endpoints`).
- `user_id (int, None)`: If the request was authenticated in any way that
could be recognized with `find_bearer_token`, and the token was valid,
this should be the id of the authenticated user.
Returns:
- `resp (Response, None)`: If the response for the endpoint should be
overriden, this is the response that should be used. Otherwise this
is None.
"""
endpoints = Table('endpoints')
itgs.read_cursor.execute(
Query.from_(endpoints).select(
endpoints.id,
endpoints.deprecated_on,
endpoints.sunsets_on
).where(endpoints.slug == Parameter('%s'))
.get_sql(),
(endpoint_slug,)
)
row = itgs.read_cursor.fetchone()
if row is None:
return None
host = URL(
scope={
'scheme': 'https',
'server': (request.headers['x-real-host'], 443),
'root_path': '/api',
'path': request.url.path,
'query_string': request.url.query.encode('utf-8'),
'headers': {}
}
)
ip_address = request.headers.get('x-real-ip', '')
user_agent = request.headers.get('user-agent', '')
(
endpoint_id,
deprecated_on,
sunsets_on
) = row
if deprecated_on is None:
return None
if sunsets_on is None:
itgs.logger.print(
Level.WARN,
'The endpoint slug {} is deprecated but does not have a sunset '
'date set! This should not happen; the maximum sunsetting time '
'of 36 months will be assigned',
endpoint_slug
)
itgs.write_cursor.execute(
Query.update(endpoints)
.set(
endpoints.sunsets_on,
Coalesce(endpoints.sunsets_on, Now() + Interval(months=36))
)
.where(endpoints.slug == Parameter('%s'))
.returning(endpoints.sunsets_on)
.get_sql(),
(endpoint_slug,)
)
(sunsets_on,) = itgs.write_cursor.fetchone()
itgs.write_conn.commit()
curtime = datetime.utcnow()
if curtime.date() < deprecated_on:
return None
# 2pm UTC = 10am est = 7am pst
sunset_time = datetime(
sunsets_on.year, sunsets_on.month, sunsets_on.day,
14, tzinfo=curtime.tzinfo
)
if curtime >= sunset_time + timedelta(days=31):
# No logging, can't be suppressed, provides no info
if request.method not in ('GET', 'HEAD'):
return Response(
status_code=405,
headers={
'Allow': 'GET, HEAD'
}
)
return Response(
status_code=404,
headers=SUNSETTED_HEADERS
)
if curtime >= sunset_time:
# No logging, can't be suppressed, provides info
if request.method == 'HEAD':
return Response(status_code=400)
return JSONResponse(
status_code=400,
content={
'deprecated': True,
'sunsetted': True,
'retryable': False,
'error': (
'This endpoint has been deprecated since {} and was sunsetted on {}, '
'meaning that it can no longer be used. For the reason for deprecation '
'and how to migrate off, visit {}://{}/endpoints.html?slug={}'
).format(
deprecated_on.strftime('%B %d, %Y'),
sunsets_on.strftime('%B %d, %Y'),
host.scheme,
host.netloc,
endpoint_slug
)
},
headers=SUNSETTED_HEADERS if request.method == 'GET' else None
)
if request.query_params.get('deprecated') == 'true':
# This flag suppresses all behavior before sunset, including logging
return None
if user_id is not None:
ip_address = None
user_agent = None
if curtime >= sunset_time - timedelta(days=14):
store_response(itgs, user_id, ip_address, user_agent, endpoint_id, 'error')
return JSONResponse(
status_code=400,
content={
'deprecated': True,
'sunsetted': False,
'retryable': False,
'error': (
'This endpoint has been deprecated since {deprecated_on} and will sunset '
'on {sunsets_on}. For the reason for deprecation and how to migrate off, '
'visit {scheme}://{netloc}/endpoints.html?slug={slug}. To continue using '
'this endpoint until {sunsets_on} you must acknowledge this warning by '
'setting the query parameter "deprecated" with the value "true". For '
'example: {scheme}://{netloc}{path}?{query_params}{opt_ambersand}'
'deprecated=true{opt_hashtag}{fragment}'
).format(
deprecated_on=deprecated_on.strftime('%B %d, %Y'),
sunsets_on=sunsets_on.strftime('%B %d, %Y'),
scheme=host.scheme,
netloc=host.netloc,
path=host.path,
query_params=host.query_params,
opt_ambersand='' if host.query_params == '' else '&',
opt_hashtag='' if host.fragment == '' else '#',
fragment=host.fragment,
slug=endpoint_slug
)
},
headers={
'Cache-Control': 'no-store'
}
)
if user_id is None:
# We will error them if they have <5 errors this month or it's
# within 30 days of sunsetting and they have received <5 errors
# this week
endpoint_users = Table('endpoint_users')
std_query = (
Query.from_(endpoint_users)
.select(Count(Star()))
.where(endpoint_users.ip_address == Parameter('%s'))
.where(endpoint_users.user_agent == Parameter('%s'))
.where(endpoint_users.response_type == Parameter('%s'))
# notnull ensure postgres uses matching index
.where(endpoint_users.ip_address.notnull())
.where(endpoint_users.user_agent.notnull())
)
std_args = [
ip_address,
user_agent,
'error'
]
itgs.read_cursor.execute(
std_query
.where(endpoint_users.created_at > DateTrunc('month', Now()))
.get_sql(),
std_args
)
(errors_this_month,) = itgs.read_cursor.fetchone()
should_error = errors_this_month < 5
if not should_error and curtime >= sunset_time - timedelta(days=30):
itgs.read_cursor.execute(
std_query
.where(endpoint_users.created_at > Now() - Interval(days=7))
.get_sql(),
std_args
)
(errors_this_week,) = itgs.read_cursor.fetchone()
should_error = errors_this_week < 5
if should_error:
store_response(itgs, None, ip_address, user_agent, endpoint_id, 'error')
return JSONResponse(
status_code=400,
content={
'deprecated': True,
'sunsetted': False,
'retryable': True,
'error': (
'This endpoint has been deprecated since {deprecated_on} and will '
'sunset on {sunsets_on}. Since your request is not authenticated '
'the only means to alert you of the sunset date is to fail some of '
'your requests. You may pass the query parameter `deprecated=true` '
'to suppress this behavior. We will only fail 5 requests per month '
'until it gets closer to the sunset date.\n\n'
'Check {scheme}://{netloc}/endpoints.html?slug={slug} for information '
'about why this endpoint was deprecated and how to migrate.'
).format(
deprecated_on=deprecated_on.strftime('%B %d, %Y'),
sunsets_on=sunsets_on.strftime('%B %d, %Y'),
scheme=host.scheme,
netloc=host.netloc,
slug=endpoint_slug
)
},
headers={
'Cache-Control': 'no-store'
}
)
store_response(itgs, user_id, ip_address, user_agent, endpoint_id, 'passthrough')
return None
SELECT
0 AS permissions,
allow,
deny
FROM page_permissions
WHERE entity = ANY ($2) OR page_id = $1)
SELECT bit_or(permissions) | bit_or(allow) | (coalesce((SELECT * FROM everyone_perms), $3)) & ~bit_or(deny)
FROM all_permissions
"""
"""pypika attempt:"""
q = (Query.with_(
Query.select(rp.permissions).from_(rp).where(rp.entity == entity_id),
'everyone_perms').with_(
Query.select(rp.permissions,
Term(0).as_('allow'),
Term(0).as_('deny')).from_(rp).where(
rp.entity.isin(role_ids)).union_all(
Query.select(
Term(0).as_('permissions'), pp.allow,
pp.deny).from_(pp).where(
pp.entity.isin(role_ids)
| pp.page_id == entity_id)),
'all_permissions').select(
bit_or('permissions') | bit_or('allow') | bit_or('deny')
| Coalesce(
Query.select(pypika.Table('everyone_perms').star).from_(
pypika.Table('everyone_perms')), default_permissions)
& ~bit_or('deny')).from_('all_permissions'))
print(q)