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))