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)
def create_token_from_passauth(
itgs: LazyIntegrations, passauth_id: int) -> models.TokenResponse:
"""Creates a fresh authentication token from the given password auth, and
returns the token. This updates the last seen at for the password auth"""
pauths = Table('password_authentications')
pauth_perms = Table('password_auth_permissions')
authtokens = Table('authtokens')
authtoken_perms = Table('authtoken_permissions')
token = secrets.token_urlsafe(95) # gives 127 characters
expires_at = datetime.utcnow() + timedelta(days=1)
itgs.write_cursor.execute(
Query
.into(authtokens)
.columns(
authtokens.user_id, authtokens.token, authtokens.expires_at,
authtokens.source_type, authtokens.source_id
)
.from_(pauths)
.select(pauths.user_id, *[Parameter('%s') for _ in range(4)])
.where(pauths.deleted.eq(False))
.where(pauths.id == Parameter('%s'))
.returning(authtokens.id, authtokens.user_id)
.get_sql(),
(token, expires_at, 'password_authentication', passauth_id, passauth_id)
)
(authtoken_id, user_id) = itgs.write_cursor.fetchone()
itgs.write_cursor.execute(
Query
.update(pauths)
.set(pauths.last_seen, ppfns.Now())
.where(pauths.id == Parameter('%s'))
.get_sql(),
(passauth_id,)
)
itgs.write_cursor.execute(
Query
.into(authtoken_perms)
.columns(authtoken_perms.authtoken_id, authtoken_perms.permission_id)
.from_(pauth_perms)
.select(Parameter('%s'), pauth_perms.permission_id)
.where(pauth_perms.password_authentication_id == passauth_id)
.get_sql(),
(authtoken_id,)
)
itgs.write_conn.commit()
return models.TokenResponse(
user_id=user_id,
token=token,
expires_at_utc=expires_at.timestamp()
)
def create_insert_query(self,
row,
columns_to_not_insert=[],
tables_name_to_not_insert=["mail_content"]):
table = pypika.Table(self.name)
arg_list = tuple(sorted(row.keys()))
insert_query = pypika.Query() \
.into(table) \
.columns(*arg_list) \
.insert( \
tuple( \
[ \
fn.Now() if isinstance(row[key], datetime)
else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert)
else Table.format_exit_string(row[key]) if isinstance(row[key], str)
else row[key] for key in sorted(row.keys()) \
] \
) \
)
return Table.get_sql(insert_query)
def create_insert_query(self,
row,
columns_to_not_insert=[],
tables_name_to_not_insert=["mail_content"]):
table = pypika.Table(self.name)
arg_list = tuple(sorted(row.keys()))
insert_query = pypika.Query() \
.into(table) \
.columns(*arg_list) \
.insert( \
tuple( \
[ \
fn.Now() if isinstance(row[key], datetime)
else "" if (key in columns_to_not_insert and self.name in tables_name_to_not_insert)
else Table.format_exit_string(row[key]) if isinstance(row[key], str)
else row[key] for key in sorted(row.keys()) \
] \
) \
)
# WORKAROUND: H2 database does not support quoting for table names or column names.
# Table.get_sql do not let us disabling quoting, we must use query.get_sql to do it (for now).
return insert_query.get_sql(quote_char="") + ";"
def delete_outdated(self, days_interval: int = 7):
news_expiry_date = fn.Now() - fn.Date(Interval(days=days_interval))
bare_query = Query.from_(self._table).delete().where(
news_expiry_date > fn.Date(self._fields['publish_date']))
query = query_to_str(bare_query)
rows_count = 0
try:
self._cursor.execute(query)
except DBError as e:
logging.error(
f"Repository [{self._table}]: delete_outdated -> [{e.errno}]{e.msg}"
)
self._connection.rollback()
else:
self._connection.commit()
rows_count = self._cursor.rowcount
logging.info(
f"Repository [{self._table}]: delete_outdated -> Deleted {rows_count} record/s"
)
return rows_count
def test_now(self):
query = Query.select(fn.Now())
self.assertEqual("SELECT NOW()", str(query))
