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)
