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 new(cls):
with pg_conn_context() as conn, conn.cursor() as cursor:
get_sequence_query = """SELECT nextval('user_id_seq')"""
cursor.execute(get_sequence_query)
num = cursor.fetchone()[0]
return num
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 SEQUENCE IF NOT EXISTS user_id_seq START WITH 10000000;
"""
cursor.execute(create_table_query)
conn.commit()
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 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 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 get_visitors(cls, sid_orig: str) -> List[Dict]:
"""获得访客列表"""
from everyclass.rpc.entity import Entity
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-server
search_result = Entity.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 get_request_by_id(cls, req_id: str) -> Optional[Dict]:
"""由 request_id 获得请求，如果找不到则返回 None"""
with pg_conn_context() as conn, conn.cursor() as cursor:
register_hstore(conn_or_curs=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(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 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 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 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 update_track(cls, host: str, visitor: StudentSession) -> 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.sid_orig, datetime.datetime.now()))
conn.commit()
def migrate(cls) -> None:
"""migrate data from mongodb"""
mongo = get_mongodb()
with pg_conn_context() as pg_conn, pg_conn.cursor() as cursor:
results = mongo.get_collection("privacy_settings").find()
for each in results:
insert_query = "INSERT INTO privacy_settings (student_id, level, create_time) VALUES (%s,%s,%s)"
cursor.execute(
insert_query,
(each['sid_orig'], each['level'], each['create_time']))
pg_conn.commit()
print("Migration finished.")
def migrate(cls) -> None:
"""migrate data from mongodb"""
mongo = get_mongodb()
with pg_conn_context() as pg_conn, pg_conn.cursor() as cursor:
results = mongo.get_collection("visitor_track").find()
for each in results:
insert_query = "INSERT INTO visit_tracks (host_id, visitor_id, last_visit_time) VALUES (%s,%s,%s)"
cursor.execute(
insert_query,
(each['host'], each['visitor'], each['last_time']))
pg_conn.commit()
print("Migration finished.")
def migrate(cls):
"""migrate data from mongodb"""
mongo = get_mongodb()
with pg_conn_context() as pg_conn, pg_conn.cursor() as cursor:
results = mongo.get_collection("simple_passwords").find()
for each in results:
insert_query = "INSERT INTO simple_passwords (student_id, time, password) VALUES (%s,%s,%s)"
cursor.execute(
insert_query,
(each['sid_orig'], each['time'], each['password']))
pg_conn.commit()
print("Migration finished.")
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 migrate(cls) -> None:
"""migrate data from mongodb"""
mongo = get_mongodb()
with pg_conn_context() as pg_conn, pg_conn.cursor() as cursor:
results = mongo.get_collection("calendar_token").find()
for each in results:
insert_query = """
INSERT INTO calendar_tokens (type, identifier, semester, token, create_time, last_used_time)
VALUES (%s,%s,%s,%s,%s,%s)
"""
cursor.execute(
insert_query,
(each['type'], each['identifier'], each['semester'],
each['token'], each['create_time'],
each['last_used'] if 'last_used' in each else None))
pg_conn.commit()
print("Migration finished.")
def migrate(cls):
"""migrate data from mongodb"""
mongo = get_mongodb()
with pg_conn_context() as pg_conn, pg_conn.cursor() as cursor:
results = mongo.get_collection("verification_requests").find()
for each in results:
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,
(each['request_id'], each['sid_orig'],
each['verification_method'], each['status'],
each['create_time'], {
'password': each['password']
} if 'password' in each else None))
pg_conn.commit()
print("Migration finished.")
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 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:
register_hstore(conn_or_curs=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)
