def insert_new_user(db_conn, username: str, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO users (name) VALUES (?); """ try: db_conn.cursor().execute(table_query, (username, )) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: log(INFO, f"Inserted new user into the database: {username}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def insert_metadata(db_conn, version, checksum, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO metadata(version, checksum) VALUES ( ?, ? ); """ try: db_conn.cursor().execute(table_query, (version, checksum)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: log(INFO, f"Inserted new metadata into the database: {version}-{checksum}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: if 'UNIQUE' not in str(err): log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def insert_new_command(db_conn, plugin_name, command_name, permission_level, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO commands(plugin_id, name, level) VALUES ( (SELECT plugins.plugin_id FROM plugins WHERE plugins.name = ? LIMIT 1), ?, ? ); """ try: db_conn.cursor().execute( table_query, (plugin_name, command_name, permission_level)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint( f"Inserted new command into the database: {plugin_name}-{command_name}-{permission_level}", origin=L_DATABASE) log(INFO, f"Inserted new command into the database: {plugin_name}-{command_name}-{permission_level}", origin=L_DATABASE) return True return False except Error as err: if 'UNIQUE' not in str(err): dprint(err) return False
def update_alias(db_conn, alias_name, commands, ignore_file_save=False) -> bool: update_alias_query = f""" UPDATE aliases SET alias = ? WHERE name = ?; """ try: db_conn.cursor().execute(update_alias_query, (commands, alias_name)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint(f"Updated alias in the database: {alias_name}", origin=L_DATABASE) log(INFO, f"Updated alias in the database: {alias_name}", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def update_plugin_help(db_conn, plugin_name, plugin_help_text, ignore_file_save=False) -> bool: update_help_query = f""" UPDATE plugins_help SET help_text = ? WHERE plugin_id = (SELECT plugins.plugin_id from plugins WHERE plugins.name = ? ); """ try: db_conn.cursor().execute(update_help_query, (plugin_help_text, plugin_name)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint( f"Updated plugin help text in the database: {plugin_name}", origin=L_DATABASE) log(INFO, f"Updated plugin help text in the database: {plugin_name}", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def insert_new_permission(db_conn, username: str, permission_level: int, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO permissions(user_id, level) VALUES ( (SELECT users.user_id FROM users WHERE users.name = ? LIMIT 1), (SELECT permission_levels.level_id FROM permission_levels WHERE permission_levels.level_id = ? LIMIT 1) ); """ try: db_conn.cursor().execute(table_query, (username, permission_level)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint( f"Inserted new permission into the database: {username}-{permission_level}", origin=L_DATABASE) log(INFO, f"Inserted new permission into the database: {username}-{permission_level}", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def update_user_privileges(db_conn, user_name, level, ignore_file_save=False) -> bool: update_privileges_query = f""" UPDATE permissions SET level = ? WHERE user_id = (SELECT users.user_id FROM users WHERE users.name = ? LIMIT 1) AND ? = (SELECT permission_levels.level_id FROM permission_levels WHERE permission_levels.level_id = ?); """ try: db_conn.cursor().execute(update_privileges_query, (level, user_name, level, level)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint( f"Updated user permission in the database: {user_name}-{level}", origin=L_DATABASE) log(INFO, f"Updated user permission in the database: {user_name}-{level}", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def insert_new_alias(db_conn, alias_name, commands, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO aliases(name, alias) VALUES ( ?, ? ); """ try: db_conn.cursor().execute(table_query, (alias_name, commands)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint(f"Inserted new alias into the database: {alias_name}", origin=L_DATABASE) log(INFO, f"Inserted new alias into the database: {alias_name}", origin=L_DATABASE) return True return False except Error as err: if 'UNIQUE' not in str(err): dprint(err) return False
def insert_new_permission_level(db_conn, level_id: int, level_type: str, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO permission_levels(level_id, level_type) VALUES ( ?, ? ); """ try: db_conn.cursor().execute(table_query, (level_id, level_type)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint( f"Inserted new permission level into the database: {level_id}-{level_type}", origin=L_DATABASE) log(INFO, f"Inserted new permission level into the database: {level_id}-{level_type}", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def update_command_privileges(db_conn, command_name, permission_level, ignore_file_save=False) -> bool: update_cmd_query = f""" UPDATE commands SET level = ? WHERE name = ?; """ try: db_conn.cursor().execute(update_cmd_query, (permission_level, command_name)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: log(INFO, f"Updated command permission in the database: {command_name}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def insert_new_plugins_help(db_conn, plugin_name, help_text, ignore_file_save=False) -> bool: table_query = f""" INSERT INTO plugins_help(plugin_id, help_text) VALUES ( (SELECT plugins.plugin_id FROM plugins WHERE plugins.name = ? LIMIT 1), ? ); """ try: db_conn.cursor().execute(table_query, (plugin_name, help_text)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint( f"Inserted new plugin help data into the database: {plugin_name}", origin=L_DATABASE) log(INFO, f"Inserted new plugin help data into the database: {plugin_name}", origin=L_DATABASE) return True return False except Error as err: if 'UNIQUE' not in str(err): dprint(err) return False
def update_metadata(db_conn, version, checksum, ignore_file_save=False) -> bool: update_metadata_query = f""" UPDATE metadata SET version = ?, checksum = ? WHERE id = 1; """ try: db_conn.cursor().execute(update_metadata_query, (version, checksum)) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: log(INFO, f"Updated metadata in the database: {version}-{checksum}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def delete_user(db_conn, user_id: int = None, user_name: str = None, ignore_file_save=False) -> bool: if user_id is None and user_name is None: return False delete_permission_query = "" delete_user_query = "" if user_id: delete_permission_query = f""" DELETE FROM permissions WHERE permissions.user_id = ? """ delete_user_query = f""" DELETE FROM users WHERE users.user_id = ? """ elif user_name: delete_permission_query = f""" DELETE FROM permissions WHERE permissions.user_id = (SELECT users.user_id FROM users WHERE users.name = ? LIMIT 1); """ delete_user_query = f""" DELETE FROM users WHERE users.name = ?; """ try: db_conn.cursor().execute( delete_permission_query, (user_id if user_id is not None else user_name, )) db_conn.cursor().execute( delete_user_query, (user_id if user_id is not None else user_name, )) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount > 0: log(INFO, f"Deleted alias in the database: {user_id if user_id is not None else user_name}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def delete_alias(db_conn, alias_id: int = None, alias_name: str = None, ignore_file_save=False) -> bool: if alias_id is None and alias_name is None: return False delete_alias_query = "" if alias_id: delete_alias_query = f""" DELETE FROM aliases WHERE aliases.alias_id = ? """ elif alias_name: delete_alias_query = f""" DELETE FROM aliases WHERE aliases.name = ?; """ try: db_conn.cursor().execute( delete_alias_query, (alias_id if alias_id is not None else alias_name, )) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if alias_id is not None: if GetDB.get_alias(db_cursor=db_conn.cursor(), alias_id=alias_id) is None: log(INFO, f"Deleted alias in the database: {alias_id}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True else: if GetDB.get_alias(db_cursor=db_conn.cursor(), alias_name=alias_name) is None: log(INFO, f"Deleted alias in the database: {alias_name}", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def delete_all_commands(db_conn, ignore_file_save=False): delete_commands_query = f""" DELETE FROM commands; """ try: db_conn.cursor().execute(delete_commands_query) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint(f"Deleted all commands in the database", origin=L_DATABASE) log(INFO, f"Deleted all commands in the database", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def delete_all_aliases(db_conn, ignore_file_save=False): # Output Format: [(name, alias), (name, alias), ...] delete_aliases_query = f""" DELETE FROM aliases; """ try: db_conn.cursor().execute(delete_aliases_query) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: dprint(f"Deleted all alias in the database", origin=L_DATABASE) log(INFO, f"Deleted all alias in the database", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def delete_all_commands(db_conn, ignore_file_save=False): delete_commands_query = f""" DELETE FROM commands; """ try: db_conn.cursor().execute(delete_commands_query) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount == -1: log(INFO, f"Deleted all commands in the database", origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return True return False except Error as err: log(ERROR, str(err), origin=L_DATABASE, print_mode=PrintMode.VERBOSE_PRINT.value) return False
def delete_alias(db_conn, alias_id: int = None, alias_name: str = None, ignore_file_save=False) -> bool: if alias_id is None and alias_name is None: return False delete_alias_query = "" if alias_id: delete_alias_query = f""" DELETE FROM aliases WHERE aliases.alias_id = ? """ elif alias_name: delete_alias_query = f""" DELETE FROM aliases WHERE aliases.name = ?; """ try: db_conn.cursor().execute( delete_alias_query, (alias_id if alias_id is not None else alias_name, )) save_memory_db(db_conn) if not ignore_file_save: save_memory_db_to_file() if db_conn.cursor().rowcount > 0: dprint( f"Deleted alias in the database: {alias_id if alias_id is not None else alias_name}", origin=L_DATABASE) log(INFO, f"Deleted alias in the database: {alias_id if alias_id is not None else alias_name}", origin=L_DATABASE) return True return False except Error as err: dprint(err) return False
def init_database(): if not path.exists(f"{dir_utils.get_main_dir()}/cfg/jjmumblebot.db"): dprint("JJMumbleBot database is missing, creating a new database.", origin=L_DATABASE) log(WARNING, "JJMumbleBot database is missing, creating a new database.", origin=L_DATABASE) with sqlite3.connect( f"{dir_utils.get_main_dir()}/cfg/jjmumblebot.db") as conn: cursor = conn.cursor() # Create the users table. if CreateDB.create_table_users(db_cursor=cursor): dprint(f"Created the users table.", origin=L_DATABASE) log(INFO, f"Created the users table.", origin=L_DATABASE) # Create the permission levels table. if CreateDB.create_table_permission_levels(db_cursor=cursor): dprint(f"Created the permission levels table.", origin=L_DATABASE) log(INFO, f"Created the permission levels table.", origin=L_DATABASE) # Create the permissions table. if CreateDB.create_table_permissions(db_cursor=cursor): dprint(f"Created the user permissions table.", origin=L_DATABASE) log(INFO, f"Created the user permissions table.", origin=L_DATABASE) # Create the plugins table. if CreateDB.create_table_plugins(db_cursor=cursor): dprint(f"Created the plugins table.", origin=L_DATABASE) log(INFO, f"Created the plugins table.", origin=L_DATABASE) # Create the commands table. if CreateDB.create_table_commands(db_cursor=cursor): dprint(f"Created the commands table.", origin=L_DATABASE) log(INFO, f"Created the commands table.", origin=L_DATABASE) # Create the aliases table. if CreateDB.create_table_aliases(db_cursor=cursor): dprint(f"Created the aliases table.", origin=L_DATABASE) log(INFO, f"Created the aliases table.", origin=L_DATABASE) # Create the plugins_help table. if CreateDB.create_table_plugins_help(db_cursor=cursor): dprint(f"Created the plugins_help table.", origin=L_DATABASE) log(INFO, f"Created the plugins_help table.", origin=L_DATABASE) # Create all the standard permission levels. if InsertDB.insert_new_permission_level( db_conn=conn, level_id=Privileges.BLACKLIST.value, level_type=Privileges.BLACKLIST.name, ignore_file_save=True): if InsertDB.insert_new_permission_level( db_conn=conn, level_id=Privileges.DEFAULT.value, level_type=Privileges.DEFAULT.name, ignore_file_save=True): if InsertDB.insert_new_permission_level( db_conn=conn, level_id=Privileges.ELEVATED.value, level_type=Privileges.ELEVATED.name, ignore_file_save=True): if InsertDB.insert_new_permission_level( db_conn=conn, level_id=Privileges.MODERATOR.value, level_type=Privileges.MODERATOR.name, ignore_file_save=True): if InsertDB.insert_new_permission_level( db_conn=conn, level_id=Privileges.ADMINISTRATOR.value, level_type=Privileges.ADMINISTRATOR.name, ignore_file_save=True): if InsertDB.insert_new_permission_level( db_conn=conn, level_id=Privileges.SUPERUSER.value, level_type=Privileges.SUPERUSER.name, ignore_file_save=True): dprint( f"Inserted all permission level entries.", origin=L_DATABASE) log(INFO, f"Inserted all permission level entries.", origin=L_DATABASE) # Create a default super user based on the one provided in the config.ini file. if InsertDB.insert_new_user( db_conn=conn, username=global_settings.cfg[C_CONNECTION_SETTINGS] [P_DEFAULT_SU], ignore_file_save=True): if InsertDB.insert_new_permission( db_conn=conn, username=global_settings.cfg[C_CONNECTION_SETTINGS] [P_DEFAULT_SU], permission_level=Privileges.SUPERUSER.value, ignore_file_save=True): dprint( f"Inserted default super user entry from config.ini file.", origin=L_DATABASE) log(INFO, f"Inserted default super user entry from config.ini file.", origin=L_DATABASE) # Finished initializing the database. dprint( f"A new database has been created, and the default user: '******' has been added as a super user.", origin=L_DATABASE) log(INFO, f"A new database has been created, and the default user: '******' has been added as a super user.", origin=L_DATABASE) save_memory_db(mem_db_conn=conn) return with sqlite3.connect( f"{dir_utils.get_main_dir()}/cfg/jjmumblebot.db") as conn: save_memory_db(conn)