def select_notifications():
sql_str = """SELECT Note_ID, Admin_Notifications.User_ID, Type, Username, Has_Been_Read, Notification_ID
FROM Admin_Notifications
INNER JOIN Notification_Types ON Admin_Notifications.Notification_Type=Notification_Types.Notification_ID
INNER JOIN Users ON Admin_Notifications.User_ID=Users.User_ID;
"""
return Query(sql_str, (), True, True).run_query()
def update(table_name, data, where_clause="", where_clause_data=()):
if len(data) < 1:
# TODO: handle in a better way such as throwing an execption
return
sql_str = "UPDATE " + table_name + " SET "
num_of_fields = len(data)
count = 0
args = []
for key in data:
args.append(data[key])
sql_str += key + "=?"
if count < num_of_fields - 1:
sql_str += ", "
count += 1
sql_str += " " + where_clause + ";"
for d in where_clause_data:
args.append(d)
## TODO: see the delete query todo
Query(sql_str, tuple(args), False).run_query()
def insert(table_name, item_data):
if len(item_data) < 1:
# TODO: handle in a better way such as throwing an execption
return
sql_str = "INSERT INTO " + table_name + " ("
values_str = "VALUES ("
num_of_fields = len(item_data)
count = 0
args = []
for key in item_data:
sql_str += key
values_str += "?"
args.append(item_data[key])
if count < num_of_fields - 1:
sql_str += ", "
values_str += ","
else:
sql_str += ") "
values_str += ")"
count += 1
sql_str += values_str + ";"
## TODO: see the delete query todo
Query(sql_str, tuple(args), False).run_query()
def get_class_names(class_id):
sql_str = """SELECT Class_Name
FROM Class
WHERE Class_ID = ?;
"""
result = Query(sql_str, (class_id, )).run_query()
if result is not None:
return result['Class_Name']
return result
def get_username(user_id):
sql_str = """SELECT Username
FROM Users
WHERE User_ID=?;
"""
result = Query(sql_str, (user_id, )).run_query()
if result is not None:
return result['Username']
return result
def get_notification_id(notification_type):
sql_str = """SELECT Notification_ID
FROM Notification_Types
WHERE Type = ?;
"""
result = Query(sql_str, (notification_type, )).run_query()
if result is not None:
return result['Notification_ID']
return result
def get_rarity_id_from_name(rarity_name):
sql_str = """SELECT Rarities_ID
FROM Rarities
WHERE Rarities_Name = ?;
"""
result = Query(sql_str, (rarity_name, )).run_query()
if result is not None:
return result['Rarities_ID']
return result
def get_user_id(username):
sql_str = """SELECT User_ID
FROM Users
WHERE Username = ?;
"""
result = Query(sql_str, (username, ), True, False).run_query()
if result is not None:
return result['User_ID']
return result
def get_slot_id_from_name(slot_name):
sql_str = """SELECT Slots_ID
FROM Slots
WHERE Slots_Name = ?;
"""
result = Query(sql_str, (slot_name, )).run_query()
if result is not None:
return result['Slots_ID']
return result
def get_item_id_from_name(item_name):
sql_str = """SELECT Item_ID
FROM Items
WHERE Item_Name = ?;
"""
result = Query(sql_str, (item_name, )).run_query()
if result is not None:
return result['Item_ID']
return result
def get_alignment_name(alignment_id):
sql_str = """SELECT Alignment_Name
FROM Alignments
WHERE Alignment_ID = ?;
"""
result = Query(sql_str, (alignment_id, )).run_query()
if result is not None:
return result['Alignment_Name']
return result
def select_character_data(char_id, user_id=-1):
sql_str = """SELECT *
FROM Character
WHERE Character_ID=?
"""
args = [char_id]
if user_id > 0:
sql_str += " AND User_ID=?"
args.append(user_id)
sql_str += ";"
return Query(sql_str, tuple(args)).run_query()
def select_slot_names(slot_id=-1):
where_command = ""
args = ()
multi = True
if slot_id > -1:
where_command = "WHERE Slots_ID=?"
args = (slot_id, )
multi = False
sql_str = """SELECT Slots_Name
FROM Slots """ + where_command + ";"
return Query(sql_str, args, True, multi).run_query()
def select_effect_names(effect_id=-1):
where_command = ""
args = ()
multi = True
if effect_id > -1:
where_command = "WHERE Effect_ID=?"
args = (effect_id, )
multi = False
sql_str = """SELECT Effect_Name
FROM Effects """ + where_command + ";"
return Query(sql_str, args, True, multi).run_query()
def select_items(item_id=-1):
where_command = ""
args = ()
multi = True
if item_id > -1:
where_command = "WHERE Items.Item_ID=?"
args = (item_id, )
multi = False
sql_str = """SELECT *
FROM Items
LEFT JOIN Rarities ON Items.Rarity_ID=Rarities.Rarities_ID
INNER JOIN Slots ON Items.Item_Slot=Slots.Slots_ID """ + where_command + ";"
return Query(sql_str, args, True, multi).run_query()
def select(select_fields,
from_table,
multiple=False,
where_clause="",
args=(),
joins=()):
sql_str = "SELECT "
num_of_fields = len(select_fields)
count = 0
for field in select_fields:
sql_str += field
if count < num_of_fields - 1:
sql_str += ", "
count += 1
sql_str += " FROM " + from_table + " "
for join in joins:
sql_str += join + " "
sql_str += where_clause + ";"
return Query(sql_str, args, True, multiple).run_query()
def select_user_data_except_user(user_id):
sql_str = """SELECT User_ID, Username, Is_Verified, Is_Admin
FROM Users
WHERE NOT User_ID = ?;
"""
return Query(sql_str, (user_id, ), True, True).run_query()
def delete(table_name, where_clause="", where_clause_data=()):
sql_str = """DELETE FROM """ + table_name + " "
sql_str += where_clause + ";"
## TODO: not sure if this will return anything useful... need to look into it
Query(sql_str, where_clause_data, False).run_query()
def select_effect_id_from_name(effect_name):
sql_str = """SELECT Effect_ID
FROM Effects
WHERE Effect_Name = ?;
"""
return Query(sql_str, (effect_name, )).run_query()
def get_char_id(user_id):
sql_str = """SELECT Character_ID
FROM Character
WHERE User_ID= ?;
"""
return Query(sql_str, (user_id, ), True, True).run_query()
def select_char_name_and_id(user_id):
sql_str = """SELECT Character_Name, Character_ID
FROM Character
WHERE User_ID = ?;
"""
return Query(sql_str, (user_id, ), True, True).run_query()
def select_rarity_names():
sql_str = """SELECT Rarities_Name
FROM Rarities;
"""
return Query(sql_str, multiple=True).run_query()
def get_has_agreed_to_tos(user_id):
sql_str = """SELECT Has_Agreed_TOS
FROM Users
WHERE User_ID=?;
"""
return Query(sql_str, (user_id, )).run_query()['Has_Agreed_TOS']
def select_user_data_from_id(user_id):
sql_str = """SELECT *
FROM Users
WHERE User_ID=?;
"""
return Query(sql_str, (user_id, )).run_query()
def select_user_data(username):
sql_str = """SELECT *
FROM Users
WHERE Username = ?;
"""
return Query(sql_str, (username, ), True, False).run_query()
def select_slots():
sql_str = """SELECT *
FROM SLOTS;
"""
return Query(sql_str, multiple=True).run_query()
def select_items_name_and_id():
sql_str = """SELECT Item_Name, Item_ID
FROM Items;
"""
return Query(sql_str, multiple=True).run_query()
