def get_level(cls, student_id: str) -> int: with pg_conn_context() as conn, conn.cursor() as cursor: select_query = "SELECT level FROM privacy_settings WHERE student_id=%s" cursor.execute(select_query, (student_id, )) result = cursor.fetchone() return result[0] if result is not None else get_config( ).DEFAULT_PRIVACY_LEVEL
def find_calendar_token(cls, tid=None, sid=None, semester=None, token=None): """通过 token 或者 sid/tid + 学期获得 token 文档""" with pg_conn_context() as conn, conn.cursor() as cursor: if token: select_query = """ SELECT type, identifier, semester, token, create_time, last_used_time FROM calendar_tokens WHERE token=%s """ cursor.execute(select_query, (uuid.UUID(token), )) result = cursor.fetchall() return cls._parse(result[0]) if result else None elif (tid or sid) and semester: select_query = """ SELECT type, identifier, semester, token, create_time, last_used_time FROM calendar_tokens WHERE type=%s AND identifier=%s AND semester=%s; """ cursor.execute( select_query, ("teacher" if tid else "student", tid, semester)) result = cursor.fetchall() return cls._parse(result[0]) if result else None else: raise ValueError( "tid/sid together with semester or token must be given to search a token document" )
def add_user(cls, sid_orig: str, password: str, password_encrypted: bool = False) -> None: """add a user :param sid_orig: 学号 :param password: 密码 :param password_encrypted: 密码是否已经被加密过了(否则会被二次加密) """ import psycopg2.errors if not password_encrypted: password_hash = generate_password_hash(password) else: password_hash = password with pg_conn_context() as conn, conn.cursor() as cursor: select_query = "INSERT INTO users (student_id, password, create_time) VALUES (%s,%s,%s)" try: cursor.execute( select_query, (sid_orig, password_hash, datetime.datetime.now())) conn.commit() except psycopg2.errors.UniqueViolation as e: raise ValueError("Student already exists in database") from e
def init(cls) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: create_table_query = """ CREATE TABLE IF NOT EXISTS visit_tracks ( host_id character varying(15) NOT NULL, visitor_id character varying(15) NOT NULL, last_visit_time timestamp with time zone NOT NULL ) WITH ( OIDS = FALSE ); """ cursor.execute(create_table_query) create_index_query = """ CREATE UNIQUE INDEX IF NOT EXISTS idx_host_time ON visit_tracks USING btree("host_id", "last_visit_time" DESC); """ cursor.execute(create_index_query) create_constraint_query = """ ALTER TABLE visit_tracks ADD CONSTRAINT unq_host_visitor UNIQUE ("host_id", "visitor_id"); """ cursor.execute(create_constraint_query) conn.commit()
def exist(cls, student_id: str) -> bool: """check if a student has registered""" with pg_conn_context() as conn, conn.cursor() as cursor: select_query = "SELECT create_time FROM users WHERE student_id=%s" cursor.execute(select_query, (student_id, )) result = cursor.fetchone() return result is not None
def get_visitors(cls, sid_orig: str) -> List[Dict]: """获得访客列表""" from everyclass.rpc.api_server import APIServer with pg_conn_context() as conn, conn.cursor() as cursor: select_query = """ SELECT visitor_id, last_visit_time FROM visit_tracks where host_id=%s ORDER BY last_visit_time DESC; """ cursor.execute(select_query, (sid_orig, )) result = cursor.fetchall() conn.commit() visitor_list = [] for record in result: # query api-identity search_result = APIServer.search(record[0]) visitor_list.append({ "name": search_result.students[0].name, "student_id": search_result.students[0].student_id_encoded, "last_semester": search_result.students[0].semesters[-1], "visit_time": record[1] }) return visitor_list
def new(cls, password: str, sid_orig: str) -> None: """新增一条简单密码记录""" with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = "INSERT INTO simple_passwords (student_id, time, password) VALUES (%s,%s,%s)" cursor.execute(insert_query, (sid_orig, datetime.datetime.now(), password)) conn.commit()
def update_track(cls, host: str, visitor: str) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: insert_or_update_query = """ INSERT INTO visit_tracks (host_id, visitor_id, last_visit_time) VALUES (%s,%s,%s) ON CONFLICT ON CONSTRAINT unq_host_visitor DO UPDATE SET last_visit_time=EXCLUDED.last_visit_time; """ cursor.execute(insert_or_update_query, (host, visitor, datetime.datetime.now())) conn.commit()
def set_level(cls, student_id: str, new_level: int) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = """ INSERT INTO privacy_settings (student_id, level, create_time) VALUES (%s,%s,%s) ON CONFLICT (student_id) DO UPDATE SET level=EXCLUDED.level """ cursor.execute(insert_query, (student_id, new_level, datetime.datetime.now())) conn.commit()
def update_last_used_time(cls, token: str): """更新token最后使用时间""" with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = """ UPDATE calendar_tokens SET last_used_time = %s WHERE token = %s; """ cursor.execute(insert_query, (datetime.datetime.now(), uuid.UUID(token))) conn.commit()
def set_request_status(cls, request_id: str, status: str) -> None: """mark a verification request's status as email token passed""" with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = """ UPDATE identity_verify_requests SET status = %s WHERE request_id = %s; """ cursor.execute(insert_query, (status, uuid.UUID(request_id))) conn.commit()
def check_password(cls, sid_orig: str, password: str) -> bool: """verify a user's password. Return True if password is correct, otherwise return False.""" with pg_conn_context() as conn, conn.cursor() as cursor: select_query = "SELECT password FROM users WHERE student_id=%s" cursor.execute(select_query, (sid_orig, )) result = cursor.fetchone() if result is None: raise ValueError("Student not registered") return check_password_hash(result[0], password)
def reset_tokens(cls, student_id: str, typ: Optional[str] = "student") -> None: """删除某用户所有的 token,默认为学生""" with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = """ DELETE FROM calendar_tokens WHERE identifier = %s AND type = %s; """ cursor.execute(insert_query, (student_id, typ)) conn.commit()
def init(cls) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: create_table_query = """ CREATE TABLE IF NOT EXISTS privacy_settings ( student_id character varying(15) NOT NULL PRIMARY KEY, level smallint NOT NULL, create_time timestamp with time zone NOT NULL ) WITH ( OIDS = FALSE ); """ cursor.execute(create_table_query) conn.commit()
def init(cls) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: create_table_query = """ CREATE TABLE IF NOT EXISTS simple_passwords ( student_id character varying(15) NOT NULL, "time" timestamp with time zone NOT NULL, password text NOT NULL ) WITH ( OIDS = FALSE ); """ cursor.execute(create_table_query) create_index_query = """ CREATE INDEX IF NOT EXISTS idx_time ON simple_passwords USING btree("time" DESC); """ cursor.execute(create_index_query) conn.commit()
def init(cls) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: create_type_query = """ DO $$ BEGIN CREATE TYPE people_type AS enum('student', 'teacher'); EXCEPTION WHEN duplicate_object THEN null; END $$; """ cursor.execute(create_type_query) create_table_query = """ CREATE TABLE IF NOT EXISTS calendar_tokens ( "type" people_type NOT NULL, identifier character varying(15) NOT NULL, semester character varying(15) NOT NULL, token uuid NOT NULL, create_time timestamp with time zone NOT NULL, last_used_time timestamp with time zone ) WITH ( OIDS = FALSE ); """ cursor.execute(create_table_query) create_index_query = """ CREATE UNIQUE INDEX IF NOT EXISTS idx_token ON calendar_tokens USING btree(token); """ cursor.execute(create_index_query) create_index_query2 = """ CREATE INDEX IF NOT EXISTS idx_type_idt_sem ON calendar_tokens USING btree("type", identifier, semester); """ cursor.execute(create_index_query2) conn.commit()
def insert_calendar_token(cls, resource_type: str, semester: str, identifier: str) -> str: """ 生成日历令牌,写入数据库并返回字符串类型的令牌。此时的 last_used_time 是 NULL。 :param resource_type: student/teacher :param semester: 学期字符串 :param identifier: 学号或教工号 :return: token 字符串 """ token = uuid.uuid4() with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = """ INSERT INTO calendar_tokens (type, identifier, semester, token, create_time) VALUES (%s,%s,%s,%s,%s); """ cursor.execute(insert_query, (resource_type, identifier, semester, token, datetime.datetime.now())) conn.commit() return str(token)
def init(cls) -> None: with pg_conn_context() as conn, conn.cursor() as cursor: create_verify_methods_type_query = """ DO $$ BEGIN CREATE TYPE identity_verify_methods AS enum('password', 'email'); EXCEPTION WHEN duplicate_object THEN null; END $$; """ cursor.execute(create_verify_methods_type_query) create_status_type_query = f""" DO $$ BEGIN CREATE TYPE identity_verify_statuses AS enum({','.join(["'" + x + "'" for x in ID_STATUSES])}); EXCEPTION WHEN duplicate_object THEN null; END $$; """ cursor.execute(create_status_type_query) create_table_query = """ CREATE TABLE IF NOT EXISTS identity_verify_requests ( request_id uuid PRIMARY KEY, identifier character varying(15) NOT NULL, method identity_verify_methods NOT NULL, status identity_verify_statuses NOT NULL, create_time timestamp with time zone NOT NULL, extra hstore ) WITH ( OIDS = FALSE ); """ cursor.execute(create_table_query) conn.commit()
def get_request_by_id(cls, req_id: str) -> Optional[Dict]: """由 request_id 获得请求,如果找不到则返回 None""" with pg_conn_context() as conn, conn.cursor() as cursor: insert_query = """ SELECT request_id, identifier, method, status, extra FROM identity_verify_requests WHERE request_id = %s; """ cursor.execute(insert_query, (uuid.UUID(req_id), )) result = cursor.fetchone() if not result: return None doc = { "request_id": result[0], "sid_orig": result[1], "verification_method": result[2], "status": result[3] } if result[4]: if "password" in result[4]: doc["password"] = result[4]["password"] return doc
def new_register_request(cls, sid_orig: str, verification_method: str, status: str, password: str = None) -> str: """ 新增一条注册请求 :param sid_orig: original sid :param verification_method: password or email :param status: status of the request :param password: if register by password, fill everyclass password here :return: the `request_id` """ if verification_method not in ("email", "password"): raise ValueError( "verification_method must be one of email, password") request_id = uuid.uuid4() with pg_conn_context() as conn, conn.cursor() as cursor: extra_doc = {} if password: extra_doc.update( {"password": generate_password_hash(password)}) insert_query = """ INSERT INTO identity_verify_requests (request_id, identifier, method, status, create_time, extra) VALUES (%s,%s,%s,%s,%s,%s) """ cursor.execute(insert_query, (request_id, sid_orig, verification_method, status, datetime.datetime.now(), extra_doc)) conn.commit() return str(request_id)