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