async def delete_user(username):
"""Add user. Actually is Disable user
Args:
username: username.
Returns:
int. return affected row count.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = ("DELETE gu "
"FROM `group_user` AS gu "
"INNER JOIN `user` AS u ON u.uid = gu.uid "
"WHERE u.username = %s")
para_input = username
affected = await cur.execute(sql, para_input)
# release user's used ip
sql = ("UPDATE `iptable` AS i "
"INNER JOIN `user` AS u "
"ON i.`uid` = u.`uid` "
"SET i.`uid` = 0 "
"WHERE u.`username` = %s ")
affected = await cur.execute(sql, para_input)
await conn.commit()
return affected
async def unlock(ip: str, date=None):
"""set user lock by username
Args:
ip: ip address, str
date: set unlock date,str ,optional
Returns:
bool. If lock data is success set return True,
if catch error return False.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
if date is None:
sql = (
"UPDATE `iptable` AS i "
"INNER JOIN `lock` AS lo ON i.lock_id = lo.lock_id "
"SET i.is_updated = 0,i.lock_id = null, lo.unlock_date = CURRENT_TIMESTAMP "
"WHERE i.ip = %s ")
para_input = ip
else:
sql = ("UPDATE `iptable` AS i "
"INNER JOIN `lock` AS lo ON i.lock_id = lo.lock_id "
"SET lo.unlock_date = %s "
"WHERE i.ip = %s ")
# datetime.strptime(date,"")
para_input = date, ip
await cur.execute(sql, para_input)
await conn.commit()
return True
async def get_user_group(username):
"""
Args:
username: username
Returns:
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = (
"SELECT g.gid, g.name, g.description "
"FROM `user` AS u "
"INNER JOIN `group_user` AS gu ON u.uid = gu.uid "
"INNER JOIN `group` as g ON g.gid = gu.gid "
"WHERE u.username = %s "
)
para_input = username
await cur.execute(sql, para_input)
data = await cur.fetchall()
if data is None:
return None
return data
async def set_password(username: str, password: str) -> bool:
"""Set user password
Args:
username:username.
password:password hash.
Returns:
bool. If set success return True, if catch error return False.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = "UPDATE `user` SET `password_hash` = %s WHERE `username` = %s"
para_input = (password, username)
try:
await cur.execute(sql, para_input)
await conn.commit()
return True
except MySQLError as e:
await conn.rollback()
logger.error("got error {}, {}".format(e, e.args[0]))
logger.error("fail to set user SQL:{}".format(
await cur.mogrify(sql, para_input)))
return False
async def get_lock_by_id(id: int) -> dict:
"""Get user lock status by lock ID
Args:
id: Lock ID
Returns:
Dict is formatted as this:
{
lock_id: int,
lock_type_id: int,
ip: str,
uid: int,
gid: int,
lock_date: datetime,
unlock_date: datetime,
title: str,
description: str,
lock_by_user_id: int
}
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "SELECT * FROM `lock` WHERE `lock_id` = %s"
await cur.execute(sql, id)
data = await cur.fetchone()
return data
async def get_announcement():
"""get announcement list
get announcement list only id and title
Returns:list with dict
[
{
'announcement_id': int,
'title': str,
`uid`: user id
},
]
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = (
"SELECT `announcement_id`, `title`, `content`, `create_datetime`, `uid`, `top` "
"FROM announcement "
"WHERE `is_deleted` = 0 AND `top` = 1 "
"UNION "
"SELECT `announcement_id`, `title`, `content`, `create_datetime`, `uid`, `top` "
"FROM `announcement` "
"WHERE `is_deleted` = 0 "
"ORDER BY `create_datetime` DESC "
"LIMIT 10"
)
await cur.execute(sql)
data = await cur.fetchall()
return data
async def get_ip_by_mac(mac):
"""
Args:
id:ip address
Returns:dict
{
"ip": str,
"switch_id": int,
"ip_type_id": int,
"mac": str,
"port": int,
"port_type": str,
"is_updated": bool,
"uid": int,
"gid": int,
"description": str,
"lock_id": int,
}
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "SELECT * FROM `iptable` WHERE `mac` like %s "
para_input = mac
await cur.execute(sql, para_input)
data = await cur.fetchone()
if data is None:
return None
return data
async def get_userinfo(username):
"""Get userinfo by username
Args:
username: username
Returns:
dict
dict is formatted as this:
(
uid: int,
username: string,
password_hash: string,
nick: string,
bed: string,
department: string,
back_mail: string
)
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql: str = "SELECT * FROM `user` WHERE `username` = %s"
para_input = username
await cur.execute(sql, para_input)
data = await cur.fetchone()
if data is None:
return None
return data
async def set_ip_type(ip, ip_type):
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "UPDATE `iptable` SET `ip_type_id` = %s WHERE `ip` = %s "
para_input = (ip_type, ip)
await cur.execute(sql, para_input)
await conn.commit()
return True
async def assign_mac(ip, mac):
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "UPDATE `iptable` SET `mac` = %s WHERE `ip` = %s "
para_input = (mac, ip)
await cur.execute(sql, para_input)
await conn.commit()
return True
async def get_fullinfo(query, mode):
"""Get full userinfo by username, bed, ip
Args:
query: username or bed or ip
mode: str, "username","bed","ip" to select query mode
Returns:
dict
dict is formatted as this:
(
uid: int,
username: string,
nick: string,
department: string,
back_mail: string
note: string
)
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
if mode == "username":
sql = "SELECT * FROM `user` WHERE `username` = %s"
elif mode == "ip":
sql = (
"SELECT u.* FROM `user` AS u "
"INNER JOIN `iptable` AS i "
"ON u.uid = i.uid WHERE `ip` = %s"
)
elif mode == "mac":
sql = (
"SELECT u.* FROM `user` AS u "
"INNER JOIN `iptable` AS i "
"ON u.uid = i.uid WHERE `mac` LIKE %s"
)
elif mode == "bed":
sql = (
"SELECT u.* FROM `user` AS u "
"INNER JOIN `iptable` AS i "
"ON u.uid = i.uid WHERE `description` LIKE CONCAT('%%',%s)"
)
else:
return None
para_input = query
await cur.execute(sql, para_input)
data = await cur.fetchone()
if data is None:
return None
return data
async def set_lock(
ip: str,
lock_type: LockTypes,
lock_date: datetime,
unlock_date: datetime = None,
title: str = None,
description: str = None,
uid: int = None,
gid: int = None,
lock_by_user_id=None,
) -> bool:
"""set user lock by username
Args:
ip: ip address, str
lock_type: locktypes, int or LockTypes
lock_date: lock date, datetime
unlock_date: unlock date, datetime
title: title, str
description: description, str
uid:Lock current user, int
gid:Lock current group, int
lock_by_user_id: lock by user, int
Returns:
bool. If lock data is success set return True,
if catch error return False.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = "INSERT INTO `lock` VALUES (null, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
para_input = (
lock_type,
ip,
uid,
gid,
lock_date,
unlock_date,
title,
description,
lock_by_user_id,
)
await cur.execute(sql, para_input)
lock_id_query = "SELECT `lock_id` FROM `lock` WHERE `ip` = %s ORDER BY `lock_date` DESC"
await cur.execute(lock_id_query, ip)
lock_id = await cur.fetchone()
ip_set_lock_id_query = "UPDATE `iptable` SET `lock_id` = %s, `is_updated` = 0 WHERE `ip` = %s"
set_lock_tuple_input = (lock_id, ip)
await cur.execute(ip_set_lock_id_query, set_lock_tuple_input)
await conn.commit()
return True
async def get_all_permission(username) -> tuple:
sql = ("SELECT p.str FROM `user` AS u "
"INNER JOIN `group_user` AS gu ON u.uid = gu.uid "
"INNER JOIN `group` AS g on gu.gid = g.gid "
"INNER JOIN `group_permission` AS gp ON gp.gid = g.gid "
"INNER JOIN `permission` AS p ON p.pid = gp.pid "
"WHERE u.username = %s")
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(sql, username)
permission_list_list = await cur.fetchall()
permission_list = []
for p in permission_list_list:
permission_list.append(p[0])
return permission_list
async def add_user_group(username, gid):
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = (
"INSERT INTO `group_user` "
"SELECT %s, u.uid "
"FROM `user` AS u "
"WHERE u.username = %s "
)
para_input = (gid, username)
affect_row = await cur.execute(sql, para_input)
if affect_row:
return True
else:
return False
async def delete_token(token):
"""delete token
Args:
token:
Returns:
affected row.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = "DELETE FROM `token` WHERE `token` = %s"
para_input = token
affect_row = await cur.execute(sql, para_input)
return affect_row
async def remove_user_group(username, gid):
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = (
"DELETE gu "
"FROM `group_user` AS gu "
"INNER JOIN `user` AS u ON gu.uid = u.uid "
"WHERE u.username = %s "
"AND gu.gid = %s "
)
para_input = (username, gid)
affect_row = await cur.execute(sql, para_input)
if affect_row:
return True
else:
return False
async def get_lock(ip: str) -> list:
"""Get user lock status by username
Args:
ip: ip address
Returns:
list dict.
List is formatted as this:
[
{
lock_id: int,
lock_type_id: int,
ip: str,
uid: int,
gid: int,
lock_date: datetime,
unlock_date: datetime,
title: str,
description: str,
lock_by_user_id: int
},
]
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "SELECT * FROM `lock` WHERE `ip` = %s ORDER BY `lock_date` DESC"
para_input = ip
await cur.execute(sql, para_input)
data = await cur.fetchall()
# key = [
# "lock_id",
# "lock_type_id",
# "ip",
# "lock_date",
# "unlock_date",
# "description",
# "lock_by_user_id",
# ]
#
# dicts = [dict(zip(key, d)) for d in data]
return data
async def get_user_bed_info(username):
"""
Args:
username: username
Returns:
dict
{
bed: string
portal: string
ip: string
}
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = ("SELECT i.description, ip_type_id "
"FROM `user` AS u "
"INNER JOIN `iptable` AS i ON i.uid = u.uid "
"WHERE u.username = %s "
"AND (i.ip_type_id = 2 OR i.ip_type_id = 1)")
para_input = username
await cur.execute(sql, para_input)
data = await cur.fetchone()
if data is None:
return None
if data["description"] == "":
error_logger.error(
"Dorm Ip found but description missing: {}".format(
username))
return messages.INTERNAL_SERVER_ERROR
bed = data["description"].split(".")
dicts = {
"portal": bed[0],
"bed": bed[1],
"ip_type": data["ip_type_id"]
}
return dicts
async def get_user_id(username: str):
"""Get actual uid by username
Args:
username
Returns:int, user id, if not found return None.
"""
sql = "SELECT uid FROM `user` WHERE `username` = %s "
para_input = username
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
await cur.execute(sql, para_input)
data = await cur.fetchone()
await conn.commit()
if data is None or len(data) == 0:
return None
return data["uid"]
async def get_username(uid: int):
"""Get actual username by uid
Args:
uid
Returns:str, username, if not found return None.
"""
sql = "SELECT username FROM `user` WHERE `uid` = %s "
para_input = uid
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
await cur.execute(sql, para_input)
data = await cur.fetchone()
await conn.commit()
if len(data) == 0:
return None
return data["username"]
async def get_ip_by_bed(bed):
"""
Args:
bed:username
Returns:dict
{
"ip": str,
"switch_id": int,
"ip_type_id": int,
"mac": str,
"port": int,
"port_type": str,
"is_updated": bool,
"uid": int,
"gid": int,
"description": str,
"lock_id": int,
}
"""
bed_regex = "^[A-Za-z][0-9]{3,4}-[0-9]$"
portal_regex = "^[A-Za-z][0-9]{3,4}$"
building_regex = "^[A-Za-z]$"
if re.search(bed_regex, bed) is not None:
sql = "SELECT * FROM `iptable` WHERE `description` LIKE CONCAT('%%',%s)"
elif re.search(portal_regex, bed) is not None:
sql = "SELECT * FROM `iptable` WHERE `description` LIKE CONCAT(%s,'%%')"
elif re.search(building_regex, bed) is not None:
sql = "SELECT * FROM `iptable` WHERE `description` LIKE CONCAT(%s,'%%')"
else:
return None
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
para_input = bed
await cur.execute(sql, para_input)
data = await cur.fetchall()
if data is None:
return None
return data
async def get_password(username: str) -> str:
"""Get user's password hash
Args:
username: username
Returns:
str. User's password hash,if user not found return empty string
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "SELECT `password_hash` FROM `user` WHERE `username` = %s"
para_input = username
await cur.execute(sql, para_input)
data = await cur.fetchone()
if data is None or len(data) == 0:
return ""
return data["password_hash"]
async def set_mac(ip: str, mac: str) -> bool:
"""Set mac by account_id
Args:
ip: ip
mac:mac address
Returns:
bool, If set success return True, if catch error return False.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = (
"UPDATE `iptable` SET `mac` = %s,`is_updated` = 0 WHERE `ip` = %s "
)
para_input = (mac, ip)
await cur.execute(sql, para_input)
await conn.commit()
return True
async def add_token(uid, token):
"""Add token to user
Args:
uid:
token:
Returns:
affected row.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = (
"INSERT INTO `token` VALUES (%s, %s, current_timestamp()) "
"ON DUPLICATE KEY UPDATE `token` = %s , `timestamp` = current_timestamp()"
)
para_input = (uid, token, token)
affect_row = await cur.execute(sql, para_input)
return affect_row
async def get_token(token):
"""Get token
Args:
token:
Returns:dict, if not found return None
uid: int
token: str
timestamp: datetime
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "SELECT * FROM `token` WHERE `token` = %s "
para_input = token
await cur.execute(sql, para_input)
data = await cur.fetchone()
if data is None or len(data) == 0:
return None
return data
async def check_permission(username, pstr) -> bool:
"""Check if user have the required permission
"""
sql = ("SELECT EXISTS "
"(SELECT * FROM `user` AS u "
"INNER JOIN `group_user` AS gu ON u.uid = gu.uid "
"INNER JOIN `group` AS g on gu.gid = g.gid "
"INNER JOIN `group_permission` AS gp ON gp.gid = g.gid "
"INNER JOIN `permission` AS p ON p.pid = gp.pid "
"WHERE u.username = %s "
"AND p.str = %s )")
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
para_input = (username, pstr)
await cur.execute(sql, para_input)
out = await cur.fetchall()
if out[0][0] >= 1:
return True
else:
return False
async def get_user_own_ip(username):
"""
Args:
username:username
Returns:list with dict
{
"ip": str,
"switch_id": int,
"ip_type_id": int,
"mac": str,
"port": int,
"port_type": str,
"is_updated": bool,
"uid": int,
"gid": int,
"description": str,
"lock_id": int,
}
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = (
"SELECT i.* "
"FROM `iptable` AS i "
"INNER JOIN `user` AS u ON u.`uid`= i.`uid` "
"WHERE u.`username` = %s "
)
para_input = username
await cur.execute(sql, para_input)
data = await cur.fetchall()
if data is None:
return None
return data
async def get_announcement_post(id):
"""get announcement post
get announcement post using id
Returns:list with dict
[
{
'announcement_id: int,
'title': int,
'content': str,
},
]
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "SELECT * " "FROM `announcement` " "WHERE announcement_id = %s"
await cur.execute(sql, id)
await conn.commit()
data = await cur.fetchone()
return data
async def add_user(username,
nick,
department="",
back_mail=None,
note=None):
"""Add new user.
Args:
username: username. str
nick: user's name. str
department: user's department. str
back_mail: user's email. str
note: note. str
Returns:
int. return affected row count.
"""
async with SQLPool.acquire() as conn:
async with conn.cursor(DictCursor) as cur:
sql = "INSERT INTO `user` VALUES (null, %s, null, %s, %s, %s, %s)"
para_input = (username, nick, department, back_mail, note)
affected = await cur.execute(sql, para_input)
await conn.commit()
return cur.lastrowid
async def remove_expired_lock():
async with SQLPool.acquire() as conn:
async with conn.cursor() as cur:
sql = "UPDATE iptable INNER JOIN `lock` ON iptable.lock_id = `lock`.lock_id SET `iptable`.`lock_id` = NULL,`iptable`.is_updated = 0 WHERE `unlock_date` < CURRENT_TIMESTAMP;"
await cur.execute(sql)
await conn.commit()
