def test_update_returning_str(self):
q = PostgreSQLQuery.update(self.table_abc).where(
self.table_abc.foo == 0).set('foo', 'bar').returning('id')
self.assertEqual(
'UPDATE "abc" SET "foo"=\'bar\' WHERE "foo"=0 RETURNING id',
str(q))
def update_game(self,
chat_id: int,
cur_player: int,
cur_color: str,
player_passed: List[bool],
board: List[List[GridPosition]],
last_placed_stone: Tuple[int, int],
last_capt_stone: Optional[Tuple[int, int]]) -> None:
if not self.use_db:
return
clean_state: Dict[str, str] = {}
for x in range(len(board)):
for y in range(len(board[x])):
if not board[x][y].free:
clean_state[f"{x},{y}"] = board[x][y].color
last_capt_stone_str = ""
if last_capt_stone is not None:
last_capt_stone_str = f"{last_capt_stone[0]},{last_capt_stone[1]}"
games = Table('games')
update_game_query = PostgreSQLQuery.update(games) \
.set(games.state, json.dumps(clean_state)) \
.set(games.turn_color, cur_color) \
.set(games.turn_player, cur_player) \
.set(games.last_stone, f'{last_placed_stone[0]},{last_placed_stone[1]}') \
.set(games.last_capt_stone, last_capt_stone_str) \
.set(games.player_passed, f'{player_passed[0]},{player_passed[1]}') \
.where(games.chat_id == chat_id)
self._cur.execute(update_game_query.get_sql())
self._conn.commit()
def test_update_returning_str(self):
q = (PostgreSQLQuery.update(self.table_abc).where(
self.table_abc.foo == 0).set("foo", "bar").returning("id"))
self.assertEqual(
'UPDATE "abc" SET "foo"=\'bar\' WHERE "foo"=0 RETURNING "abc"."id"',
str(q))
def test_non_insert_on_conflict_do_update(self):
with self.assertRaises(QueryException):
query = (
PostgreSQLQuery.update(self.table_abc)
.set("foo", "bar")
.on_conflict("id")
.do_update(["name"], ["m"])
)
def test_update_returning_from_different_tables(self):
table_bcd = Table('bcd')
q = (PostgreSQLQuery.update(self.table_abc).from_(table_bcd).set(
self.table_abc.lname,
table_bcd.long_name).returning(self.table_abc.id, table_bcd.fname))
self.assertEqual(
'UPDATE "abc" SET "lname"="bcd"."long_name" FROM "bcd" RETURNING "abc"."id","bcd"."fname"',
str(q))
def update_response(name: str,
change: models.ResponseEditArgs,
authorization: str = Header(None)):
if len(change.edit_reason) < 5:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['body', 'edit_reason']
},
'msg': 'minimum 5 characters',
'type': 'too_short'
})
with LazyItgs(no_read_only=True) as itgs:
authed, user_id = users_helper.check_permissions_from_header(
itgs, authorization, 'responses')
if not authed:
return Response(status_code=403)
users = Table('users')
itgs.write_cursor.execute(
Query.from_(users).select(
users.id).where(users.id == Parameter('%s')).get_sql() +
' FOR SHARE', (user_id, ))
row = itgs.write_cursor.fetchone()
if row is None:
itgs.write_conn.rollback()
return Response(status_code=403)
responses = Table('responses')
itgs.write_cursor.execute(
Query.from_(responses).select(
responses.id, responses.response_body,
responses.description).where(
responses.name == Parameter('%s')).get_sql() +
' FOR UPDATE', (name, ))
row = itgs.write_cursor.fetchone()
if row is None:
itgs.write_conn.rollback()
return Response(status_code=404)
(resp_id, old_body, old_desc) = row
resp_hists = Table('response_histories')
itgs.write_cursor.execute(
Query.into(resp_hists).columns(
resp_hists.response_id, resp_hists.user_id, resp_hists.old_raw,
resp_hists.new_raw, resp_hists.reason, resp_hists.old_desc,
resp_hists.new_desc).insert(
*[Parameter('%s') for _ in range(7)]).get_sql(),
(resp_id, user_id, old_body, change.body, change.edit_reason,
old_desc, change.desc))
itgs.write_cursor.execute(
Query.update(responses).set(
responses.response_body, Parameter('%s')).set(
responses.description, Parameter('%s')).set(
responses.updated_at, ppfns.Now()).where(
responses.id == Parameter('%s')).get_sql(),
(change.body, change.desc, resp_id))
itgs.write_conn.commit()
return Response(status_code=200)
async def update_chat(self, request, context, metadata):
query = PostgreSQLQuery.update(self.chats_table)
for field in request.DESCRIPTOR.fields:
if field.containing_oneof and request.HasField(field.name):
field_value = getattr(request, field.name)
query = query.set(field.name, field_value)
query = query.where(self.chats_table.chat_id ==
request.chat_id).returning('*').get_sql()
async with self.pool_holder.pool.acquire() as session:
result = await session.execute(query)
chat = await result.fetchone()
return self.enrich_chat(ChatPb(**chat))
async def refreshSession(self, session: Session) -> Optional[Session]:
sql = Query.update(sessions).set(sessions.updated_at, datetime.now(timezone.utc)) \
.where(Criterion.all([
sessions.session_id == session.sessionId,
sessions.user_id == session.userId,
sessions.updated_at == session.updatedAt
])) \
.returning(sessions.session_id, sessions.user_id, sessions.updated_at)
result = await databaseClient.query(sql.get_sql())
if len(result) == 0: return None
row = result[0]
return Session(sessionId=row[0], userId=row[1], updatedAt=row[2])
def generate_update_sql(
self,
scimag_pb: ScimagPb,
fields: Optional[Set[str]] = None,
) -> str:
query = (PostgreSQLQuery.update(self.scimag_table))
fields = fields or self.db_fields
for field_name in fields:
if self.is_field_set(scimag_pb, field_name):
field_value = getattr(scimag_pb, field_name)
field_name, field_value = self.cast_field_value(
field_name, field_value)
query = query.set(field_name, field_value)
return query.where(self.scimag_table.id == scimag_pb.id).get_sql()
def _get_query_update_sql(self, updatequery):
values = []
table = self.pika_table
query = PostgreSQLQuery.update(table)
i = 0
criterion, where_values = self._expressions_to_criterion(
updatequery.expressions, i)
query = query.where(criterion)
values.extend(where_values)
i += len(where_values)
for key, value in updatequery.update_kwargs.items():
query = query.set(table[key], self.parameter(i))
values.append(value)
i += 1
sql = str(query.get_sql())
return sql, values
def _get_update_sql(self,
instance,
update_fields,
condition_fields={}) -> str:
"""
Generates the SQL for updating a model depending on provided update_fields.
Result is cached for performance.
"""
key, sql, values = self._get_update_cached(instance, update_fields,
condition_fields)
if sql:
return sql, values
values = []
table = self.pika_table
query = PostgreSQLQuery.update(table)
count = 0
for field_name in update_fields or self.meta.fields_db_projection.keys(
):
db_field = self.meta.fields_db_projection[field_name]
field_object = self.meta.fields_map[field_name]
if not field_object.pk:
query = query.set(table[db_field], self.parameter(count))
values.append(
field_object.to_db_value(getattr(instance, field_name)))
count += 1
query = query.where(
table[self.meta.db_pk_field] == self.parameter(count))
values.append(self.meta.pk.to_db_value(instance.pk))
count += 1
for k, v in condition_fields:
query = query.where(table[k] == self.parameter(count))
values.append(v)
count += 1
sql = self.update_cache[key] = str(query.get_sql())
return sql, values
async def update_user(**data):
userid = data['id']
if data.get('password'):
data['password'] = hash_password(data['password'], algorithm='argon2')
if data.get('email'):
data['email'] = data['email'].lower()
db = await get_db()
query = Query.update(users_table).where(users_table.id == userid)
for key, value in data.items():
if key in ('id', '@id'):
continue
if key in ('data', 'allowed_scopes'):
value = json.dumps(value)
try:
query = query.set(getattr(users_table, key), value)
except AttributeError:
pass
async with db.acquire() as conn:
await conn.execute(str(query))
await notify(UserModifiedEvent(userid, data))
return data
def delete(id: int, reason_wrapped: models.Reason, authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
request_cost = 5
with LazyItgs(no_read_only=True) as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM,
helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429,
headers={'x-request-cost': str(request_cost)})
if user_id is None:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
can_modify_others_auth_methods = (
helper.CAN_MODIFY_OTHERS_AUTHENTICATION_METHODS_PERM in perms)
auth_methods = Table('password_authentications')
itgs.read_cursor.execute(
Query.from_(auth_methods).select(
auth_methods.human, auth_methods.deleted,
auth_methods.user_id).where(
auth_methods.id == Parameter('%s')).get_sql(), (id, ))
row = itgs.read_cursor.fetchone()
if row is None:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
(human, deleted, auth_method_user_id) = row
if deleted and not can_view_deleted_auth_methods:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
if auth_method_user_id != user_id and not can_view_others_auth_methods:
return Response(status_code=404,
headers={'x-request-cost': str(request_cost)})
if human:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
if auth_method_user_id != user_id and not can_modify_others_auth_methods:
return Response(status_code=403,
headers={'x-request-cost': str(request_cost)})
if deleted:
return Response(status_code=409,
headers={'x-request-cost': str(request_cost)})
itgs.write_cursor.execute(
Query.update(auth_methods).set(
auth_methods.deleted,
True).where(auth_methods.id == Parameter('%s')).get_sql(),
(id, ))
auth_events = Table('password_authentication_events')
itgs.write_cursor.execute(
Query.into(auth_events).columns(
auth_events.password_authentication_id, auth_events.type,
auth_events.reason, auth_events.user_id,
auth_events.permission_id).insert(
*[Parameter('%s') for _ in range(5)]).get_sql(),
(id, 'deleted', reason_wrapped.reason, user_id, None))
authtokens = Table('authtokens')
itgs.write_cursor.execute(
Query.from_(authtokens).delete().where(
authtokens.source_type == Parameter('%s')).where(
authtokens.source_id == Parameter('%s')).get_sql(),
('password_authentication', id))
itgs.write_conn.commit()
return Response(status_code=200,
headers={'x-request-cost': str(request_cost)})
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
def test_non_insert_on_conflict_do_update(self):
with self.assertRaises(QueryException):
query = PostgreSQLQuery.update(self.table_abc).set(
'foo', 'bar').on_conflict('id').do_update(['name'], ["m"])
def test_non_insert_on_conflict_do_nothing(self):
with self.assertRaises(QueryException):
(PostgreSQLQuery.update(self.table_abc).set(
"foo", "bar").on_conflict("id").do_nothing())
def test_update_returning(self):
q = PostgreSQLQuery.update(self.table_abc).where(
self.table_abc.foo == 0
).set('foo', 'bar').returning(self.table_abc.id)
self.assertEqual('UPDATE "abc" SET "foo"=\'bar\' WHERE "foo"=0 RETURNING id', str(q))
def update(loan_id: int,
loan: edit_models.LoanBasicFields,
dry_run: bool = False,
dry_run_text: bool = False,
if_match: str = Header(None),
authorization: str = Header(None)):
"""Allows modifying the standard fields on a loan. Must provide an If-Match
header which is the etag of the loan being modified.
"""
if if_match is None:
return Response(status_code=428)
with LazyItgs(no_read_only=True) as itgs:
has_perm, user_id = users.helper.check_permissions_from_header(
itgs, authorization, (helper.EDIT_LOANS_PERMISSION, ))
if not has_perm:
return Response(status_code=403)
etag = helper.calculate_etag(itgs, loan_id)
if etag is None:
return Response(status_code=410)
if etag != if_match:
return Response(status_code=412)
loans = Table('loans')
moneys = Table('moneys')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
currencies = Table('currencies')
itgs.read_cursor.execute(
Query.from_(loans).select(
currencies.id, currencies.code).join(principals).on(
principals.id == loans.principal_id).join(currencies).on(
currencies.id == principals.currency_id).where(
loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
(currency_id, currency_code) = itgs.read_cursor.fetchone()
is_repaid = None
if (loan.principal_minor is None) != (loan.principal_repayment_minor is
None):
itgs.read_cursor.execute(
Query.from_(loans).select(
principals.amount,
principal_repayments.amount).join(principals).on(
principals.id == loans.principal_id).join(
principal_repayments).on(
principal_repayments.id ==
loans.principal_repayment_id).where(
loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
princ_amt, princ_repay_amt = itgs.read_cursor.fetchone()
new_princ_amt = loan.principal_minor or princ_amt
new_princ_repay_amt = loan.principal_repayment_minor or princ_repay_amt
if new_princ_amt < new_princ_repay_amt:
return JSONResponse(
status_code=422,
content={
'detail': {
'loc': [
'loan',
(loan.principal_minor is None
and 'principal_minor'
or 'principal_repayment_minor')
],
'msg':
'Cannot have principal repayment higher than principal',
'type':
'value_error'
}
})
is_repaid = new_princ_amt == new_princ_repay_amt
elif loan.principal_minor is not None:
is_repaid = loan.principal_minor == loan.principal_repayment_minor
admin_events = Table('loan_admin_events')
query = (Query.into(admin_events).columns(
admin_events.loan_id, admin_events.admin_id, admin_events.reason,
admin_events.old_principal_id, admin_events.new_principal_id,
admin_events.old_principal_repayment_id,
admin_events.new_principal_repayment_id,
admin_events.old_created_at, admin_events.new_created_at,
admin_events.old_repaid_at, admin_events.new_repaid_at,
admin_events.old_unpaid_at, admin_events.new_unpaid_at,
admin_events.old_deleted_at, admin_events.new_deleted_at))
query = (query.from_(loans).select(
Parameter('$1'), Parameter('$2'),
Parameter('$3')).where(loans.id == Parameter('$1')))
query_params = [loan_id, user_id, loan.reason]
update_query = (Query.update(loans).where(loans.id == Parameter('$1')))
update_params = [loan_id]
# Principal
query = query.select(loans.principal_id)
if loan.principal_minor is None:
query = query.select(loans.principal_id)
else:
usd_amount = (
loan.principal_minor *
(1 / lbshared.convert.convert(itgs, 'USD', currency_code)))
itgs.write_cursor.execute(
Query.into(moneys).columns(
moneys.currency_id, moneys.amount,
moneys.amount_usd_cents).insert(
*[Parameter('%s')
for _ in range(3)]).returning(moneys.id).get_sql(),
(currency_id, loan.principal_minor, usd_amount))
(new_principal_id, ) = itgs.write_cursor.fetchone()
query = query.select(Parameter(f'${len(query_params) + 1}'))
query_params.append(new_principal_id)
update_query = update_query.set(
loans.principal_id, Parameter(f'${len(update_params) + 1}'))
update_params.append(new_principal_id)
# Principal Repayment
query = query.select(loans.principal_repayment_id)
if loan.principal_repayment_minor is None:
query = query.select(loans.principal_repayment_id)
else:
usd_amount = (
loan.principal_repayment_minor *
(1 / lbshared.convert.convert(itgs, 'USD', currency_code)))
itgs.write_cursor.execute(
Query.into(moneys).columns(
moneys.currency_id, moneys.amount,
moneys.amount_usd_cents).insert(
*[Parameter('%s')
for _ in range(3)]).returning(moneys.id).get_sql(),
(currency_id, loan.principal_repayment_minor, usd_amount))
(new_principal_repayment_id, ) = itgs.write_cursor.fetchone()
query = query.select(Parameter(f'${len(query_params) + 1}'))
query_params.append(new_principal_repayment_id)
update_query = update_query.set(
loans.principal_repayment_id,
Parameter(f'${len(update_params) + 1}'))
update_params.append(new_principal_repayment_id)
# Created At
query = query.select(loans.created_at)
if loan.created_at is None:
query = query.select(loans.created_at)
else:
new_created_at = datetime.fromtimestamp(loan.created_at)
query = query.select(Parameter(f'${len(query_params) + 1}'))
query_params.append(new_created_at)
update_query = update_query.set(
loans.created_at, Parameter(f'${len(update_params) + 1}'))
update_params.append(new_created_at)
# Repaid
query = query.select(loans.repaid_at)
if is_repaid is None:
query = query.select(loans.repaid_at)
else:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.repaid_at, Parameter(f'${len(update_params) + 1}'))
if is_repaid:
val = datetime.now()
query_params.append(val)
update_params.append(val)
else:
query_params.append(None)
update_params.append(None)
# Unpaid
query = query.select(loans.unpaid_at)
if is_repaid:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.unpaid_at, Parameter(f'${len(update_params) + 1}'))
query_params.append(None)
update_params.append(None)
elif loan.unpaid is None:
query = query.select(loans.unpaid_at)
else:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.unpaid_at, Parameter(f'${len(update_params) + 1}'))
if loan.unpaid:
val = datetime.now()
query_params.append(val)
update_params.append(val)
else:
query_params.append(None)
update_params.append(None)
# Deleted
query = query.select(loans.deleted_at)
if loan.deleted is None:
query = query.select(loans.deleted_at)
else:
query = query.select(Parameter(f'${len(query_params) + 1}'))
update_query = update_query.set(
loans.deleted_at, Parameter(f'${len(update_params) + 1}'))
if loan.deleted:
val = datetime.now()
query_params.append(val)
update_params.append(val)
else:
query_params.append(None)
update_params.append(None)
admin_event_insert_sql, admin_event_insert_params = (
lbshared.queries.convert_numbered_args(query.get_sql(),
query_params))
update_loan_sql, update_loan_params = (
lbshared.queries.convert_numbered_args(update_query.get_sql(),
update_params))
itgs.write_cursor.execute(admin_event_insert_sql,
admin_event_insert_params)
if update_loan_sql.strip():
itgs.write_cursor.execute(update_loan_sql, update_loan_params)
if not dry_run:
itgs.write_conn.commit()
itgs.logger.print(Level.INFO,
'Admin user {} just modified loan {}', user_id,
loan_id)
return Response(status_code=200)
else:
itgs.write_conn.rollback()
fmtted_admin_event_insert_sql = sqlparse.format(
admin_event_insert_sql, keyword_case='upper', reindent=True)
fmtted_update_loan_sql = sqlparse.format(update_loan_sql,
keyword_case='upper',
reindent=True)
if not dry_run_text:
return JSONResponse(
status_code=200,
content={
'loan_id':
loan_id,
'loan':
loan.dict(),
'dry_run':
dry_run,
'dry_run_text':
dry_run_text,
'admin_event_insert_sql':
fmtted_admin_event_insert_sql,
'admin_event_insert_params':
jsonable_encoder(admin_event_insert_params),
'update_loan_sql':
fmtted_update_loan_sql,
'update_loan_params':
jsonable_encoder(update_loan_params)
})
spaces = ' ' * 20
return Response(status_code=200,
headers={'Content-Type': 'plain/text'},
content=("\n".join(
(line[20:] if line[:20] == spaces else line)
for line in f"""
loan_id: {loan_id},
loan:
principal_minor: {loan.principal_minor},
principal_repayment_minor: {loan.principal_repayment_minor},
unpaid: {loan.unpaid},
created_at: {loan.created_at}
deleted: {loan.deleted}
reason: {repr(loan.reason)}
admin_event_insert_sql:
{fmtted_admin_event_insert_sql}
admin_event_insert_params: {admin_event_insert_params}
update_loan_sql:
{fmtted_update_loan_sql}
update_loan_params:
{update_loan_params}
""".splitlines())))
def update_users(loan_id: int,
new_users: edit_models.ChangeLoanUsers,
if_match: str = Header(None),
authorization: str = Header(None)):
"""Allows modifying the users on a loan. Must provide an If-Match header
which is the etag of the loan being modified.
"""
if if_match is None:
return Response(status_code=428)
with LazyItgs(no_read_only=True) as itgs:
has_perm, user_id = users.helper.check_permissions_from_header(
itgs, authorization, (helper.EDIT_LOANS_PERMISSION, ))
if not has_perm:
return Response(status_code=403)
etag = helper.calculate_etag(itgs, loan_id)
if etag is None:
return Response(status_code=410)
if etag != if_match:
return Response(status_code=412)
usrs = Table('users')
created_lender = False
try:
itgs.write_cursor.execute(
Query.into(usrs).columns(usrs.username).insert(
Parameter('%s')).returning(usrs.id).get_sql(),
(new_users.lender_name.lower(), ))
created_lender = True
except UniqueViolation:
itgs.write_conn.rollback()
itgs.write_cursor.execute(
Query.from_(usrs).select(
usrs.id).where(usrs.username == Parameter('%s')).get_sql(),
(new_users.lender_name.lower(), ))
(lender_id, ) = itgs.write_cursor.fetchone()
try:
itgs.write_cursor.execute(
Query.into(usrs).columns(usrs.username).insert(
Parameter('%s')).returning(usrs.id).get_sql(),
(new_users.borrower_name.lower(), ))
except UniqueViolation:
itgs.write_conn.rollback()
if created_lender:
itgs.write_cursor.execute(
Query.into(usrs).columns(usrs.username).insert(
Parameter('%s')).returning(usrs.id).get_sql(),
(new_users.lender_name.lower(), ))
else:
# Slight race condition
itgs.write_cursor.execute(
Query.from_(usrs).select(usrs.id).where(
usrs.username == Parameter('%s')).get_sql(),
(new_users.lender_name.lower(), ))
(lender_id, ) = itgs.write_cursor.fetchone()
itgs.write_cursor.execute(
Query.from_(usrs).select(
usrs.id).where(usrs.username == Parameter('%s')).get_sql(),
(new_users.borrower_name.lower(), ))
(borrower_id, ) = itgs.write_cursor.fetchone()
loans = Table('loans')
itgs.write_cursor.execute(
Query.into(loans).columns(
loans.lender_id, loans.borrower_id, loans.principal_id,
loans.principal_repayment_id, loans.created_at,
loans.repaid_at, loans.unpaid_at,
loans.deleted_at).from_(loans).select(
Parameter('%s'), Parameter('%s'), loans.principal_id,
loans.principal_repayment_id, loans.created_at,
loans.repaid_at, loans.unpaid_at, loans.deleted_at).where(
loans.id == Parameter('%s')).returning(
loans.id).get_sql(),
(lender_id, borrower_id, loan_id))
(new_loan_id, ) = itgs.write_cursor.fetchone()
admin_events = Table('loan_admin_events')
base_query = (Query.into(admin_events).columns(
admin_events.loan_id, admin_events.admin_id, admin_events.reason,
admin_events.old_principal_id, admin_events.new_principal_id,
admin_events.old_principal_repayment_id,
admin_events.new_principal_repayment_id,
admin_events.old_created_at, admin_events.new_created_at,
admin_events.old_repaid_at, admin_events.new_repaid_at,
admin_events.old_unpaid_at, admin_events.new_unpaid_at,
admin_events.old_deleted_at,
admin_events.new_deleted_at).from_(loans).where(
loans.id == Parameter('$1')))
base_select_params = [
loans.id,
Parameter('$2'),
Parameter('$3'), loans.principal_id, loans.principal_id,
loans.principal_repayment_id, loans.principal_repayment_id,
loans.created_at, loans.created_at, loans.repaid_at,
loans.repaid_at, loans.unpaid_at, loans.unpaid_at,
loans.deleted_at, loans.deleted_at
]
base_args = [None, user_id, None]
new_deleted_at = datetime.now()
update_old_select_params = base_select_params.copy()
update_old_select_params[-1] = new_deleted_at
update_old_args = base_args.copy()
update_old_args[0] = loan_id
update_old_args[2] = ('This loan had the users changed. ' +
f'The new loan id is {new_loan_id}. ' +
'Do not modify this loan further.')
itgs.write_cursor.execute(*lbshared.queries.convert_numbered_args(
base_query.select(
*update_old_select_params).get_sql(), update_old_args))
itgs.write_cursor.execute(
Query.update(loans).set(
loans.deleted_at,
new_deleted_at).where(loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
creation_infos = Table('loan_creation_infos')
itgs.write_cursor.execute(
Query.into(creation_infos).columns(
creation_infos.loan_id, creation_infos.type,
creation_infos.mod_user_id).insert(Parameter('%s'),
Parameter('%s'),
Parameter('%s')).get_sql(),
(new_loan_id, 1, user_id))
base_args[0] = new_loan_id
base_args[2] = (
f'This loan was copied from loan {loan_id}. ' +
'The users were changed during this operation. Reason: ' +
new_users.reason)
itgs.write_cursor.execute(*lbshared.queries.convert_numbered_args(
base_query.select(*base_select_params).get_sql(), base_args))
itgs.write_conn.commit()
return JSONResponse(
status_code=200,
content=edit_models.SingleLoanResponse(loan_id=new_loan_id).dict())
def handle_comment(self, itgs, comment, rpiden, rpversion):
token_vals = PARSER.parse(comment['body'])
lender_username = comment['author']
borrower_username = token_vals[0]
comment_permalink = 'https://www.reddit.com/comments/{}/redditloans/{}'.format(
comment['link_fullname'][3:], comment['fullname'][3:])
loans = Table('loans')
lenders = Table('lenders')
borrowers = Table('borrowers')
itgs.write_cursor.execute(
loan_format_helper.create_loans_query().where(
lenders.username == Parameter('%s')).where(
borrowers.username == Parameter('%s')).where(
loans.unpaid_at.isnull()).where(
loans.repaid_at.isnull()).get_sql(),
(lender_username.lower(), borrower_username.lower()))
row = itgs.write_cursor.fetchone()
affected_pre = []
while row is not None:
affected_pre.append(loan_format_helper.fetch_loan(row))
row = itgs.write_cursor.fetchone()
if affected_pre:
itgs.write_cursor.execute(
Query.update(loans).set(loans.unpaid_at, Now()).where(
loans.id.isin([Parameter('%s')
for _ in affected_pre])).get_sql(),
tuple(loan.id for loan in affected_pre))
loan_unpaid_events = Table('loan_unpaid_events')
itgs.write_cursor.execute(
Query.into(loan_unpaid_events).columns(
loan_unpaid_events.loan_id,
loan_unpaid_events.unpaid).insert(
*[(Parameter('%s'), True)
for _ in affected_pre]).returning(
loan_unpaid_events.id).get_sql(),
tuple(loan.id for loan in affected_pre))
itgs.channel.exchange_declare('events', 'topic')
row = itgs.write_cursor.fetchone()
while row is not None:
itgs.channel.basic_publish(
'events', 'loans.unpaid',
json.dumps({"loan_unpaid_event_id": row[0]}))
row = itgs.write_cursor.fetchone()
itgs.write_cursor.execute(
loan_format_helper.create_loans_query().where(
loans.id.isin([Parameter('%s')
for _ in affected_pre])).get_sql(),
tuple(loan.id for loan in affected_pre))
row = itgs.write_cursor.fetchone()
affected_post = []
while row is not None:
affected_post.append(loan_format_helper.fetch_loan(row))
row = itgs.write_cursor.fetchone()
else:
affected_post = []
itgs.logger.print(Level.INFO,
'/u/{} marked {} loan{} sent to /u/{} unpaid at {}',
lender_username, len(affected_pre),
's' if len(affected_pre) != 1 else '',
borrower_username, comment_permalink)
borrower_summary = loan_format_helper.get_and_format_all_or_summary(
itgs, borrower_username)
formatted_response = get_response(
itgs,
'unpaid',
lender_username=lender_username,
borrower_username=borrower_username,
loans_before=loan_format_helper.format_loan_table(affected_pre),
loans_after=loan_format_helper.format_loan_table(affected_post),
borrower_summary=borrower_summary)
utils.reddit_proxy.send_request(itgs, rpiden, rpversion,
'post_comment', {
'parent': comment['fullname'],
'text': formatted_response
})
def update_currency(loan_id: int,
new_currency: edit_models.ChangeLoanCurrency,
if_match: str = Header(None),
authorization: str = Header(None)):
"""Allows modifying the currency on a loan. Must provide an If-Match header
which is the etag of the loan being modified.
"""
if if_match is None:
return Response(status_code=428)
with LazyItgs(no_read_only=True) as itgs:
has_perm, user_id = users.helper.check_permissions_from_header(
itgs, authorization, (helper.EDIT_LOANS_PERMISSION, ))
if not has_perm:
return Response(status_code=403)
etag = helper.calculate_etag(itgs, loan_id)
if etag is None:
return Response(status_code=410)
if etag != if_match:
return Response(status_code=412)
loans = Table('loans')
moneys = Table('moneys')
currencies = Table('currencies')
itgs.write_cursor.execute(
Query.from_(currencies).select(currencies.id).where(
currencies.code == Parameter('%s')).get_sql(),
(new_currency.currency_code.upper(), ))
row = itgs.write_cursor.fetchone()
if row is None:
return JSONResponse(status_code=422,
content={
'detail': [{
'loc': ['currency_code'],
'msg':
'Must be a recognized currency code',
'type': 'value_error'
}]
})
(currency_id, ) = row
rate_usd_to_currency = lbshared.convert.convert(
itgs, 'USD', new_currency.currency_code.upper())
principal_usd = round(new_currency.principal_minor /
rate_usd_to_currency)
principal_repayment_usd = round(
new_currency.principal_repayment_minor / rate_usd_to_currency)
itgs.write_cursor.execute(
Query.into(moneys).columns(
moneys.currency_id, moneys.amount,
moneys.amount_usd_cents).insert(
*[Parameter('%s')
for _ in range(3)]).returning(moneys.id).get_sql(),
(currency_id, new_currency.principal_minor, principal_usd))
(new_principal_id, ) = itgs.write_cursor.fetchone()
itgs.write_cursor.execute(
Query.into(moneys).columns(
moneys.currency_id, moneys.amount,
moneys.amount_usd_cents).insert(
*[Parameter('%s')
for _ in range(3)]).returning(moneys.id).get_sql(),
(currency_id, new_currency.principal_repayment_minor,
principal_repayment_usd))
(new_principal_repayment_id, ) = itgs.write_cursor.fetchone()
itgs.write_cursor.execute(
Query.into(loans).columns(
loans.lender_id, loans.borrower_id, loans.principal_id,
loans.principal_repayment_id, loans.created_at,
loans.repaid_at,
loans.unpaid_at, loans.deleted_at).from_(loans).select(
loans.lender_id, loans.borrower_id, Parameter('%s'),
Parameter('%s'), loans.created_at, loans.repaid_at,
loans.unpaid_at, loans.deleted_at).where(
loans.id == Parameter('%s')).returning(
loans.id).get_sql(),
(new_principal_id, new_principal_repayment_id, loan_id))
(new_loan_id, ) = itgs.write_cursor.fetchone()
admin_events = Table('loan_admin_events')
query = (Query.into(admin_events).columns(
admin_events.loan_id, admin_events.admin_id, admin_events.reason,
admin_events.old_principal_id, admin_events.new_principal_id,
admin_events.old_principal_repayment_id,
admin_events.new_principal_repayment_id,
admin_events.old_created_at, admin_events.new_created_at,
admin_events.old_repaid_at, admin_events.new_repaid_at,
admin_events.old_unpaid_at, admin_events.new_unpaid_at,
admin_events.old_deleted_at,
admin_events.new_deleted_at).from_(loans).select(
loans.id, user_id, Parameter('%s'), loans.principal_id,
loans.principal_id, loans.principal_repayment_id,
loans.principal_repayment_id, loans.created_at,
loans.created_at, loans.repaid_at, loans.repaid_at,
loans.unpaid_at, loans.unpaid_at,
loans.deleted_at).where(loans.id == Parameter('%s')))
new_deleted_at = datetime.now()
itgs.write_cursor.execute(
query.select(Parameter('%s')).get_sql(),
(f'This loan was copied to loan {new_loan_id} then deleted in ' +
f'order to change the currency to {new_currency.currency_code.upper()}. '
+ 'Do not modify this loan further.', new_deleted_at, loan_id))
itgs.write_cursor.execute(
Query.update(loans).set(
loans.deleted_at,
Parameter('%s')).where(loans.id == Parameter('%s')).get_sql(),
(new_deleted_at, loan_id))
creation_infos = Table('loan_creation_infos')
itgs.write_cursor.execute(
Query.into(creation_infos).columns(
creation_infos.loan_id, creation_infos.type,
creation_infos.mod_user_id).insert(
*[Parameter('%s') for _ in range(3)]).get_sql(),
(new_loan_id, 1, user_id))
itgs.write_cursor.execute(
query.select(loans.deleted_at).get_sql(),
(f'This loan was copied from {loan_id} with the currency changed to '
+
f'{new_currency.currency_code.upper()}. Reason: {new_currency.reason}',
new_loan_id))
itgs.write_conn.commit()
return JSONResponse(
status_code=200,
content=edit_models.SingleLoanResponse(loan_id=new_loan_id).dict())
async def do(
self,
document_operation_pb: DocumentOperationPb) -> DocumentOperationPb:
update_document_pb = document_operation_pb.update_document
scitech_pb = update_document_pb.typed_document.scitech
fields = update_document_pb.fields or self.db_fields
conditions = []
if scitech_pb.id:
conditions.append(self.scitech_table.id == scitech_pb.id)
if scitech_pb.libgen_id:
conditions.append(
self.scitech_table.libgen_id == scitech_pb.libgen_id)
if scitech_pb.fiction_id:
conditions.append(
self.scitech_table.fiction_id == scitech_pb.fiction_id)
if scitech_pb.doi:
conditions.append(self.scitech_table.doi == scitech_pb.doi)
# if scitech_pb.md5:
# conditions.append(self.scitech_table.md5 == UuidFunction(scitech_pb.md5))
if conditions:
casted_conditions = conditions[0]
for condition in conditions[1:]:
casted_conditions = casted_conditions | condition
sql = (PostgreSQLQuery.from_(self.scitech_table).select(
functions.Count('*')).where(casted_conditions).get_sql())
result = await self.pool_holder.execute(sql, fetch=True)
count = result[0][0]
if count > 1:
raise ConflictError(scitech_pb, duplicates=[])
if count == 1:
query = PostgreSQLQuery.update(self.scitech_table)
for field_name in fields:
if self.is_field_set(scitech_pb, field_name):
field_value = getattr(scitech_pb, field_name)
field_name, field_value = self.cast_field_value(
field_name, field_value)
query = query.set(field_name, field_value)
sql = query.where(casted_conditions).returning(
'id', 'original_id').get_sql()
else:
columns = []
inserts = []
for field_name in fields:
if self.is_field_set(scitech_pb, field_name):
field_value = getattr(scitech_pb, field_name)
field_name, field_value = self.cast_field_value(
field_name, field_value)
columns.append(field_name)
inserts.append(field_value)
query = (PostgreSQLQuery.into(self.scitech_table).columns(
*columns).insert(*inserts).on_conflict('libgen_id', 'doi'))
for col, val in zip(columns, inserts):
query = query.do_update(col, val)
sql = query.returning('id', 'original_id').get_sql()
result = await self.pool_holder.execute(sql, fetch=True)
scitech_pb.id, scitech_pb.original_id = result[0][
0], result[0][1] or 0
return document_operation_pb
def test_non_insert_on_conflict_do_update(self):
with self.assertRaises(QueryException):
query = PostgreSQLQuery.update(self.table_abc).set('foo', 'bar').on_conflict(
'id').do_update(['name'], ["m"])
def apply_repayment(itgs: LazyItgs, loan_id: int, amount: Money):
"""Applies up to the given amount of money to the given loan. This will
convert the amount to the loans currency if necessary. This will return the
primary key of the loan_repayment_events row that was created, the amount
of money that was applied to the loan (which will be in the loan currency),
and the amount of money which exceeded the remaining principal to be repaid
on this loan (which will be in the provided currency).
This does not commit anything and expects to be running with explicit
commits, i.e., where this is running in a transaction which it does not
itself commit.
For consistency this will use the same conversion rate to USD for the loan
as when the loan was initially created.
Example:
(repayment_event_id, amount_applied, amount_remaining) = apply_repayment(
itgs, loan_id, amount
)
Raises:
ValueError: If the loan does not exist, is already repaid or, the
amount is 0.
Arguments:
itgs (LazyIntegrations): The lazy loaded networked services connector
loan_id (int): The primary key of the loan to apply the repayment to
amount (Money): The amount of money to apply toward this loan. This may
be in any currency, although it will be converted to the loans
currency.
Returns:
repayment_event_id (int): The primary key of of the loan repayment
event that this created.
amount_applied (Money): The amount of money that was applied toward
the loan, in the loans currency.
amount_remaining (Money): The amount of money that is remaining, in
the same currency that the amount was given in.
"""
tus.check(itgs=(itgs, LazyItgs),
loan_id=(loan_id, int),
amount=(amount, Money))
if amount.minor <= 0:
raise ValueError(
f'Cannot apply {amount} toward a loan (only positive amounts can be applied)'
)
loans = Table('loans')
moneys = Table('moneys')
usrs = Table('users')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
currencies = Table('currencies')
principal_currencies = currencies.as_('principal_currencies')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
itgs.write_cursor.execute(
Query.from_(loans).select(
lenders.id, lenders.username, borrowers.id, borrowers.username,
principal_currencies.id, principal_currencies.code,
principal_currencies.exponent, principal_currencies.symbol,
principal_currencies.symbol_on_left, principals.amount,
principals.amount_usd_cents, principal_repayments.id,
principal_repayments.amount, loans.unpaid_at).join(principals).
on(principals.id == loans.principal_id).join(principal_currencies).on(
principal_currencies.id == principals.currency_id).
join(principal_repayments).on(
principal_repayments.id == loans.principal_repayment_id).join(
lenders).on(lenders.id == loans.lender_id).join(borrowers).on(
borrowers.id == loans.borrower_id).where(
loans.id == Parameter('%s')).get_sql(), (loan_id, ))
row = itgs.write_cursor.fetchone()
if row is None:
raise ValueError(f'Loan {loan_id} does not exist')
(lender_user_id, lender_username, borrower_user_id, borrower_username,
loan_currency_id, loan_currency, loan_currency_exp, loan_currency_symbol,
loan_currency_symbol_on_left, principal_amount, principal_usd_cents,
principal_repayment_id, principal_repayment_amount, unpaid_at) = row
rate_loan_to_usd = (principal_amount / float(principal_usd_cents))
if principal_amount == principal_repayment_amount:
raise ValueError(f'Loan {loan_id} is already repaid')
if loan_currency == amount.currency:
loan_currency_amount = amount
else:
rate_given_to_loan = convert(itgs, amount.currency, loan_currency)
loan_currency_amount = Money(
int(math.ceil(amount.minor * rate_given_to_loan)),
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left)
applied = Money(min(principal_amount - principal_repayment_amount,
loan_currency_amount.minor),
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left)
applied_usd_cents = int(math.ceil(applied.minor / rate_loan_to_usd))
if loan_currency == amount.currency:
remaining = Money(amount.minor - applied.minor,
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left)
else:
applied_in_given_currency = int(
math.ceil(applied.minor / rate_given_to_loan))
remaining = Money(max(0, amount.minor - applied_in_given_currency),
amount.currency,
exp=amount.exp,
symbol=amount.symbol,
symbol_on_left=amount.symbol_on_left)
repayment_event_money_id = utils.money_utils.find_or_create_money(
itgs, applied, applied_usd_cents)
loan_repayment_events = Table('loan_repayment_events')
itgs.write_cursor.execute(
Query.into(loan_repayment_events).columns(
loan_repayment_events.loan_id,
loan_repayment_events.repayment_id).insert(
*[Parameter('%s') for _ in range(2)]).returning(
loan_repayment_events.id).get_sql(),
(loan_id, repayment_event_money_id))
(repayment_event_id, ) = itgs.write_cursor.fetchone()
new_princ_repayment_amount = principal_repayment_amount + applied.minor
new_princ_repayment_usd_cents = int(
math.ceil(new_princ_repayment_amount / rate_loan_to_usd))
new_princ_repayment_id = utils.money_utils.find_or_create_money(
itgs,
Money(new_princ_repayment_amount,
loan_currency,
exp=loan_currency_exp,
symbol=loan_currency_symbol,
symbol_on_left=loan_currency_symbol_on_left),
new_princ_repayment_usd_cents)
itgs.write_cursor.execute(
Query.update(loans).set(
loans.principal_repayment_id,
Parameter('%s')).where(loans.id == Parameter('%s')).get_sql(),
(new_princ_repayment_id, loan_id))
if new_princ_repayment_amount == principal_amount:
itgs.write_cursor.execute(
Query.update(loans).set(loans.repaid_at, Now()).set(
loans.unpaid_at,
None).where(loans.id == Parameter('%s')).get_sql(),
(loan_id, ))
if unpaid_at is not None:
loan_unpaid_events = Table('loan_unpaid_events')
itgs.write_cursor.execute(
Query.into(loan_unpaid_events).columns(
loan_unpaid_events.loan_id,
loan_unpaid_events.unpaid).insert(
*[Parameter('%s') for _ in range(2)]).get_sql(),
(loan_id, False))
itgs.channel.exchange_declare('events', 'topic')
itgs.channel.basic_publish(
'events', 'loans.paid',
json.dumps({
'loan_id': loan_id,
'lender': {
'id': lender_user_id,
'username': lender_username
},
'borrower': {
'id': borrower_user_id,
'username': borrower_username
},
'amount': {
'minor': amount.minor,
'currency': amount.currency,
'exp': amount.exp,
'symbol': amount.symbol,
'symbol_on_left': amount.symbol_on_left
},
'was_unpaid': unpaid_at is not None
}))
return (repayment_event_id, applied, remaining)
def send_messages(version):
with LazyIntegrations(logger_iden=LOGGER_IDEN) as itgs:
itgs.logger.print(Level.TRACE,
'Sending moderator onboarding messages...')
mod_onboarding_messages = Table('mod_onboarding_messages')
itgs.read_cursor.execute(
Query.from_(mod_onboarding_messages).select(
Max(mod_onboarding_messages.msg_order)).get_sql())
(max_msg_order, ) = itgs.read_cursor.fetchone()
if max_msg_order is None:
itgs.logger.print(Level.DEBUG,
'There are no moderator onboarding messages.')
return
mod_onboarding_progress = Table('mod_onboarding_progress')
moderators = Table('moderators')
users = Table('users')
itgs.read_cursor.execute(
Query.from_(moderators).join(users).on(
users.id == moderators.user_id).
left_join(mod_onboarding_progress).on(
mod_onboarding_progress.moderator_id == moderators.id).select(
users.id, moderators.id, users.username,
mod_onboarding_progress.msg_order).where(
mod_onboarding_progress.msg_order.isnull()
| (mod_onboarding_progress.msg_order < Parameter('%s'))
).get_sql(), (max_msg_order, ))
rows = itgs.read_cursor.fetchall()
responses = Table('responses')
titles = responses.as_('titles')
bodies = responses.as_('bodies')
for (user_id, mod_id, username, cur_msg_order) in rows:
itgs.read_cursor.execute(
Query.from_(mod_onboarding_messages).join(titles).on(
titles.id == mod_onboarding_messages.title_id).join(bodies)
.on(bodies.id == mod_onboarding_messages.body_id).select(
mod_onboarding_messages.msg_order, titles.id, titles.name,
bodies.id, bodies.name).where(
Parameter('%s').isnull()
| (mod_onboarding_messages.msg_order > Parameter('%s'))
).orderby(mod_onboarding_messages.msg_order,
order=Order.asc).limit(1).get_sql(), (
cur_msg_order,
cur_msg_order,
))
(new_msg_order, title_id, title_name, body_id,
body_name) = itgs.read_cursor.fetchone()
title_formatted = get_response(itgs, title_name, username=username)
body_formatted = get_response(itgs, body_name, username=username)
utils.reddit_proxy.send_request(
itgs, 'mod_onboarding_messages', version, 'compose', {
'recipient': username,
'subject': title_formatted,
'body': body_formatted
})
utils.mod_onboarding_utils.store_letter_message_with_id_and_names(
itgs, user_id, title_id, title_name, body_id, body_name)
if cur_msg_order is None:
itgs.write_cursor.execute(
Query.into(mod_onboarding_progress).columns(
mod_onboarding_progress.moderator_id,
mod_onboarding_progress.msg_order).insert(
*(Parameter('%s') for _ in range(2))).get_sql(),
(mod_id, new_msg_order))
else:
itgs.write_cursor.execute(
Query.update(mod_onboarding_progress).set(
mod_onboarding_progress.msg_order,
Parameter('%s')).set(
mod_onboarding_progress.updated_at,
CurTimestamp()).where(
mod_onboarding_progress.moderator_id ==
Parameter('%s')).get_sql(),
(new_msg_order, mod_id))
itgs.write_conn.commit()
itgs.logger.print(
Level.INFO,
'Successfully sent moderator onboarding message (msg_order={}) to /u/{}',
new_msg_order, username)
def destroy(req_user_id: int, captcha: str, authorization=Header(None)):
"""Purges our accessible stores of the given users demographics. This
operation is not reversible and will destroy our history of who knew what
about this user. We will not allow the user to submit further information
once they do this.
This should only be done in extreme circumstances, or by the users request.
We would much prefer users update their demographic information to all
blanks, which will _also_ prevent anyone from using the website to access
the information, but preserves the history in the database.
If the users information is already purged this returns 451. If the
user does not have demographic information, we create them an all blank
record and mark it as purged to ensure they cannot submit new information,
for consistency.
"""
if authorization is None:
return Response(status_code=401)
attempt_request_cost = 5
success_request_cost = 95
with LazyItgs(no_read_only=True) as itgs:
auth = demographics_helper.get_failure_response_or_user_id_and_perms_for_authorization(
itgs, authorization, attempt_request_cost, req_user_id,
demographics_helper.PURGE_SELF_DEMOGRAPHICS_PERMISSION,
demographics_helper.PURGE_OTHERS_DEMOGRAPHICS_PERMISSION, [])
if isinstance(auth, Response):
return auth
(user_id, perms) = auth
headers = {'x-request-cost': str(attempt_request_cost)}
if not security.verify_captcha(itgs, captcha):
return Response(status_code=403, headers=headers)
headers['x-request-cost'] = str(attempt_request_cost +
success_request_cost)
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
success_request_cost):
return Response(status_code=429, headers=headers)
demos = Table('user_demographics')
itgs.read_cursor.execute(
Query.from_(demos).select(demos.id, demos.deleted).where(
demos.user_id == Parameter('%s')).get_sql(), (req_user_id, ))
row = itgs.read_cursor.fetchone()
if row is not None and row[1]:
return Response(status_code=451, headers=headers)
if row is None:
users = Table('users')
itgs.read_cursor.execute(
Query.from_(users).select(1).where(
users.id == Parameter('%s')).limit(1).get_sql(),
(req_user_id, ))
if itgs.read_cursor.fetchone() is None:
return Response(status_code=404, headers=headers)
itgs.write_cursor.execute(
Query.into(demos).columns(
demos.user_id,
demos.deleted).insert(*[Parameter('%s')
for _ in range(2)]).get_sql(),
(req_user_id, True))
itgs.write_conn.commit()
return Response(status_code=200, headers=headers)
demo_id = row[0]
demo_history = Table('user_demographic_history')
itgs.write_cursor.execute(
Query.update(demo_history).set(demo_history.old_email, None).set(
demo_history.new_email,
None).set(demo_history.old_name, None).set(
demo_history.new_name,
None).set(demo_history.old_street_address, None).set(
demo_history.new_street_address,
None).set(demo_history.old_city, None).set(
demo_history.new_city,
None).set(demo_history.old_state, None).set(
demo_history.new_state,
None).set(demo_history.old_zip, None).set(
demo_history.new_zip, None).set(
demo_history.old_country, None).set(
demo_history.new_country,
None).set(demo_history.purged_at,
Now()).
where(
demo_history.user_demographic_id == Parameter('%s')).get_sql(),
(demo_id, ))
itgs.write_cursor.execute(
Query.into(demo_history).columns(
demo_history.user_demographic_id,
demo_history.changed_by_user_id, demo_history.old_deleted,
demo_history.new_deleted, demo_history.purged_at).insert(
*[Parameter('%s') for _ in range(4)], Now()).get_sql(),
(demo_id, user_id, False, True))
itgs.write_cursor.execute(
Query.update(demos).set(demos.email, None).set(
demos.name, None).set(demos.street_address, None).set(
demos.city, None).set(demos.state, None).set(
demos.zip, None).set(demos.country, None).set(
demos.deleted,
True).where(demos.id == Parameter('%s')).get_sql(),
(demo_id, ))
itgs.write_conn.commit()
return Response(status_code=200, headers=headers)
def change_password(id: int,
args: models.ChangePasswordParams,
authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
# In reality this is probably closer to ~10000, but we want to make sure
# everyone could actually save enough tokens to do this. To avoid user error,
# we will split the ratelimit cost into a pre- and post- part
check_request_cost = 5
perform_request_cost = 295
with LazyItgs(no_read_only=True) as itgs:
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, authorization,
(helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM,
helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
check_request_cost):
return Response(
status_code=429,
headers={'x-request-cost': str(check_request_cost)})
if user_id is None:
return Response(
status_code=403,
headers={'x-request-cost': str(check_request_cost)})
can_view_others_auth_methods = helper.VIEW_OTHERS_AUTHENTICATION_METHODS_PERM in perms
can_view_deleted_auth_methods = helper.CAN_VIEW_DELETED_AUTHENTICATION_METHODS_PERM in perms
auth_methods = Table('password_authentications')
itgs.read_cursor.execute(
Query.from_(auth_methods).select(
auth_methods.user_id, auth_methods.deleted).where(
auth_methods.id == Parameter('%s')).get_sql(), (id, ))
row = itgs.read_cursor.fetchone()
if row is None:
return Response(
status_code=404,
headers={'x-request-cost': str(check_request_cost)})
(auth_method_user_id, deleted) = row
if auth_method_user_id != user_id:
if not can_view_others_auth_methods:
return Response(
status_code=404,
headers={'x-request-cost': str(check_request_cost)})
return Response(
status_code=403,
headers={'x-request-cost': str(check_request_cost)})
if deleted:
if not can_view_deleted_auth_methods:
return Response(
status_code=404,
headers={'x-request-cost': str(check_request_cost)})
return Response(
status_code=403,
headers={'x-request-cost': str(check_request_cost)})
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
perform_request_cost):
return Response(status_code=429,
headers={
'x-request-cost':
str(check_request_cost + perform_request_cost)
})
salt = secrets.token_urlsafe(23) # 31 chars
block_size = int(os.environ.get('NONHUMAN_PASSWORD_BLOCK_SIZE', '8'))
dklen = int(os.environ.get('NONHUMAN_PASSWORD_DKLEN', '64'))
# final number is MiB of RAM for the default
iterations = int(
os.environ.get('NONHUMAN_PASSWORD_ITERATIONS', str(
(1024 * 8) * 64)))
hash_name = f'scrypt-{block_size}-{dklen}'
password_digest = b64encode(
scrypt(
args.password.encode('utf-8'),
salt=salt.encode('utf-8'),
n=iterations,
r=block_size,
p=1,
maxmem=128 * iterations * block_size +
1024 * 64, # padding not necessary?
dklen=dklen)).decode('ascii')
itgs.write_cursor.execute(
Query.update(auth_methods).set(
auth_methods.hash_name,
Parameter('%s')).set(auth_methods.hash, Parameter('%s')).set(
auth_methods.salt, Parameter('%s')).set(
auth_methods.iterations, Parameter('%s')).where(
auth_methods.id == Parameter('%s')).get_sql(),
(hash_name, password_digest, salt, iterations, id))
auth_events = Table('password_authentication_events')
itgs.write_cursor.execute(
Query.into(auth_events).columns(
auth_events.password_authentication_id, auth_events.type,
auth_events.reason, auth_events.permission_id,
auth_events.user_id).insert(
*[Parameter('%s') for _ in range(5)]).get_sql(),
(id, 'password-changed', args.reason, None, user_id))
itgs.write_conn.commit()
return Response(status_code=200,
headers={
'x-request-cost':
str(check_request_cost + perform_request_cost)
})
def update(req_user_id: int,
captcha: str,
args: demographics_models.UserDemographics,
authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
if args.user_id != req_user_id:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['body', 'user_id'],
'msg':
'must match path parameter req_user_id',
'type': 'mismatch_error'
}
})
attempt_request_cost = 5
success_request_cost = 95
with LazyItgs(no_read_only=True) as itgs:
auth = demographics_helper.get_failure_response_or_user_id_and_perms_for_authorization(
itgs, authorization, attempt_request_cost, req_user_id,
demographics_helper.EDIT_SELF_DEMOGRAPHICS_PERMISSION,
demographics_helper.EDIT_OTHERS_DEMOGRAPHICS_PERMISSION, [])
if isinstance(auth, Response):
return auth
(user_id, perms) = auth
headers = {'x-request-cost': str(attempt_request_cost)}
if not security.verify_captcha(itgs, captcha):
return Response(status_code=403, headers=headers)
headers['x-request-cost'] = str(attempt_request_cost +
success_request_cost)
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
success_request_cost):
return Response(status_code=429, headers=headers)
demos = Table('user_demographics')
itgs.read_cursor.execute(
Query.from_(demos).select(demos.id, demos.deleted).where(
demos.user_id == Parameter('%s')).limit(1).get_sql(),
(req_user_id, ))
row = itgs.read_cursor.fetchone()
if row is None:
users = Table('users')
itgs.read_cursor.execute(
Query.from_(users).select(1).where(
users.id == Parameter('%s')).limit(1).get_sql(),
(req_user_id, ))
if itgs.read_cursor.fetchone() is None:
return Response(status_code=404, headers=headers)
itgs.write_cursor.execute(
Query.into(demos).columns(demos.user_id).insert(
Parameter('%s')).returning(demos.id).get_sql(),
(req_user_id, ))
row = itgs.write_cursor.fetchone()
demo_id = row[0]
demo_history = Table('user_demographic_history')
itgs.write_cursor.execute(
Query.into(demo_history).columns(
demo_history.user_demographic_id,
demo_history.changed_by_user_id,
demo_history.old_email,
demo_history.new_email,
demo_history.old_name,
demo_history.new_name,
demo_history.old_street_address,
demo_history.new_street_address,
demo_history.old_city,
demo_history.new_city,
demo_history.old_state,
demo_history.new_state,
demo_history.old_zip,
demo_history.new_zip,
demo_history.old_country,
demo_history.new_country,
demo_history.old_deleted,
demo_history.new_deleted,
).from_(demos).select(
demos.id,
Parameter('%s'),
demos.email,
Parameter('%s'),
demos.name,
Parameter('%s'),
demos.street_address,
Parameter('%s'),
demos.city,
Parameter('%s'),
demos.state,
Parameter('%s'),
demos.zip,
Parameter('%s'),
demos.country,
Parameter('%s'),
demos.deleted, # This should be false (we just checked), but
demos.deleted # we store the value here just in case
).where(demos.id == Parameter('%s')).get_sql(),
(user_id, args.email, args.name, args.street_address, args.city,
args.state, args.zip, args.country, demo_id))
itgs.write_cursor.execute(
Query.update(demos).set(demos.email, Parameter('%s')).set(
demos.name, Parameter('%s')).set(
demos.street_address,
Parameter('%s')).set(demos.city, Parameter('%s')).set(
demos.state,
Parameter('%s')).set(demos.zip, Parameter('%s')).set(
demos.country, Parameter('%s')).where(
demos.id == Parameter('%s')).get_sql(),
(args.email, args.name, args.street_address, args.city, args.state,
args.zip, args.country, demo_id))
itgs.write_conn.commit()
return Response(status_code=200, headers=headers)
