def create_class_trait(event, data): sql = """ INSERT INTO class_traits ( class_id, trait_name, trait_desc, char_level, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("class_id", ""), data.get("trait_name", ""), data.get("trait_desc", ""), data.get("char_level", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute_no_return(sql, params)
def get_campaign(camp_id=None): q = """ select c.camp_id, c.camp_name, c.created_by, if (c.created_by = %s, 'true', 'false') is_owner from campaigns c where 1=1 """ if camp_id != None: q = q + " and c.camp_id = %s" params = (mysql_connector.get_username(ev), camp_id) camp = mysql_connector.single_query_json(q, params) return camp else: q = q + """ and c.created_by = %s or exists (select 1 from campaign_invites ci where c.camp_id = ci.camp_id and ci.user = %s and ci.accepted = 1)""" params = (mysql_connector.get_username(ev), mysql_connector.get_username(ev), mysql_connector.get_username(ev)) camps = mysql_connector.query_json(q, params) return camps
def create_invite(data): sql = """ INSERT IGNORE INTO campaign_invites ( camp_id, user, accepted, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, 1, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("camp_id", ""), data.get("user", ""), mysql_connector.get_username(ev), mysql_connector.get_username(ev)) mysql_connector.execute_no_return(sql, params)
def create_class(event, data): sql = """ INSERT INTO classes ( class_name, hit_die, str, dex, con, intel, wis, cha, caster, parent_id, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("class_name", ""), data.get("hit_die", ""), (1 if str(data.get("str", "")).lower() == "true" else 0), (1 if str(data.get("dex", "")).lower() == "true" else 0), (1 if str(data.get("con", "")).lower() == "true" else 0), (1 if str(data.get("intel", "")).lower() == "true" else 0), (1 if str(data.get("wis", "")).lower() == "true" else 0), (1 if str(data.get("cha", "")).lower() == "true" else 0), (1 if str(data.get("caster", "")).lower() == "true" else 0), data.get("parent_id", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute_no_return(sql, params) class_id = mysql_connector.get_last_insert_id() for sub in data.get("subclasses", list()): sub["parent_id"] = class_id create_class(event, sub) for trait in data.get("class_traits", list()): trait["class_id"] = class_id create_class_trait(event, trait) return class_id
def create_char(event, data): sql = """ INSERT INTO characters ( class_id, race_id, background_id, char_name, lvl, str, dex, con, intel, wis, cha, armor_class, caster, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("class_id", ""), data.get("race_id", ""), data.get("background_id", ""), data.get("char_name", ""), data.get("lvl", ""), data.get("str", ""), data.get("dex", ""), data.get("con", ""), data.get("intel", ""), data.get("wis", ""), data.get("cha", ""), data.get("armor_class", ""), (1 if str(data.get("caster", "")).lower() == "true" else 0), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute_no_return(sql, params) char_id = mysql_connector.get_last_insert_id() return char_id
def update_invite(data): sql = """ UPDATE campaign_invites SET accepted = 1, updated_by = %s, updated_date = SYSDATE() WHERE camp_id = %s and user = %s """ params = (mysql_connector.get_username(ev), data.get("camp_id", ""), mysql_connector.get_username(ev)) mysql_connector.execute_no_return(sql, params)
def create_race(event, data): sql = """ INSERT INTO races ( race_name, str, dex, con, intel, wis, cha, speed, parent_id, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("race_name", ""), data.get("str", ""), data.get("dex", ""), data.get("con", ""), data.get("intel", ""), data.get("wis", ""), data.get("cha", ""), data.get("speed", ""), data.get("parent_id", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute_no_return(sql, params) race_id = mysql_connector.get_last_insert_id() for sub in data.get("subraces", list()): sub["parent_id"] = race_id create_race(event, sub) for trait in data.get("race_traits", list()): trait["race_id"] = race_id create_race_trait(event, trait) return race_id
def update_char(event, data): sql = """ UPDATE characters SET char_name = %s, lvl = %s, str = %s, dex = %s, con = %s, intel = %s, wis = %s, cha = %s, armor_class = %s, caster = %s, updated_by = %s, updated_date = SYSDATE() WHERE char_id = %s """ params = (data.get("char_name", ""), data.get("lvl", ""), data.get("str", ""), data.get("dex", ""), data.get("con", ""), data.get("intel", ""), data.get("wis", ""), data.get("cha", ""), data.get("armor_class", ""), (1 if str(data.get("caster", "")).lower() == "true" else 0), mysql_connector.get_username(event), data.get("char_id", "")) mysql_connector.execute_no_return(sql, params)
def delete_camp_chars(camp_id): sql = """ DELETE FROM campaign_chars WHERE camp_id = %s and created_by = %s """ params = (camp_id, mysql_connector.get_username(ev)) mysql_connector.execute_no_return(sql, params)
def create_campaign(data): sql = """ INSERT INTO campaigns ( camp_name, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("camp_name", ""), mysql_connector.get_username(ev), mysql_connector.get_username(ev)) mysql_connector.execute_no_return(sql, params) camp_id = mysql_connector.get_last_insert_id() return camp_id
def update_campaign(data): sql = """ UPDATE campaigns SET camp_name = %s, updated_by = %s, updated_date = SYSDATE() WHERE camp_id = %s """ params = (data.get("camp_name", ""), mysql_connector.get_username(ev), data.get("camp_id", "")) mysql_connector.execute_no_return(sql, params)
def add_campaign(event, data): sql = """ INSERT IGNORE INTO campaign_chars ( camp_id, char_id, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("camp_id", ""), data.get("char_id", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) return mysql_connector.execute(sql, params)
def add_asset(event, data): sql = """ INSERT INTO char_assets ( char_id, asset_id, asset_type, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ params = (data.get("char_id", ""), data.get("asset_id", ""), data.get("asset_type", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) return mysql_connector.execute(sql, params)
def get_invites(): q = """ select camp_id, user, IF(accepted, 'true', 'false') accepted, created_by from campaign_invites where 1=1 """ q = q + " and user = %s" params = mysql_connector.get_username(ev) invites = mysql_connector.query_json(q, params) return invites
def get_users(camp_id): q = """ select distinct c.created_by user from characters c where 1=1 and c.created_by <> %s and not exists (select 1 from campaign_invites ci where ci.user = c.created_by and ci.accepted = 1 and ci.camp_id = %s)""" params = (mysql_connector.get_username(ev), camp_id) users = mysql_connector.query_json(q, params) users = [user["user"] for user in users] return users
def update_class_trait(event, data): sql = """ UPDATE class_traits SET trait_name = %s, trait_desc = %s, char_level = %s, updated_by = %s, updated_date = SYSDATE() WHERE trait_id = %s """ params = (data.get("trait_name", ""), data.get("trait_desc", ""), data.get("char_level", ""), mysql_connector.get_username(event), data.get("trait_id", "")) mysql_connector.execute_no_return(sql, params)
def update_class(event, data): sql = """ UPDATE classes SET class_name = %s, hit_die = %s, str = %s, dex = %s, con = %s, intel = %s, wis = %s, cha = %s, caster = %s, updated_by = %s, updated_date = SYSDATE() WHERE class_id = %s """ params = (data.get("class_name", ""), data.get("hit_die", ""), (1 if str(data.get("str", "")).lower() == "true" else 0), (1 if str(data.get("dex", "")).lower() == "true" else 0), (1 if str(data.get("con", "")).lower() == "true" else 0), (1 if str(data.get("intel", "")).lower() == "true" else 0), (1 if str(data.get("wis", "")).lower() == "true" else 0), (1 if str(data.get("cha", "")).lower() == "true" else 0), (1 if str(data.get("caster", "")).lower() == "true" else 0), mysql_connector.get_username(event), data.get("class_id", "")) mysql_connector.execute_no_return(sql, params) for sub in data.get("subclasses", list()): if sub.get("delete", False) == True: delete_class(sub["class_id"]) elif sub.get("class_id", None) != None: sub["parent_id"] = data["class_id"] update_class(event, sub) else: sub["parent_id"] = data["class_id"] create_class(event, sub) for trait in data.get("class_traits", list()): if trait.get("delete", False) == True: delete_class_trait(trait["trait_id"]) elif trait.get("trait_id", None) != None: trait["class_id"] = data["class_id"] update_class_trait(event, trait) else: trait["class_id"] = data["class_id"] create_class_trait(event, trait)
def update_race(event, data): sql = """ UPDATE races SET race_name = %s, str = %s, dex = %s, con = %s, intel = %s, wis = %s, cha = %s, speed = %s, updated_by = %s, updated_date = SYSDATE() WHERE race_id = %s """ params = (data.get("race_name", ""), data.get("str", ""), data.get("dex", ""), data.get("con", ""), data.get("intel", ""), data.get("wis", ""), data.get("cha", ""), data.get("speed", ""), mysql_connector.get_username(event), data.get("race_id", "")) mysql_connector.execute_no_return(sql, params) for sub in data.get("subraces", list()): if sub.get("delete", False) == True: delete_race(sub["race_id"]) elif sub.get("race_id", None) != None: sub["parent_id"] = data["race_id"] update_race(event, sub) else: sub["parent_id"] = data["race_id"] create_race(event, sub) for trait in data.get("race_traits", list()): if trait.get("delete", False) == True: delete_race_trait(trait["trait_id"]) elif trait.get("trait_id", None) != None: trait["race_id"] = data["race_id"] update_race_trait(event, trait) else: trait["race_id"] = data["race_id"] create_race_trait(event, trait)
def get_char(char_id=None): q = """ select ch.char_id, ch.char_name, ch.lvl, ch.str, ch.dex, ch.con, ch.intel, ch.wis, ch.cha, ch.armor_class, IFNULL(ra2.race_id, ra.race_id) race_id, IFNULL(ra2.race_name, ra.race_name) race_name, IFNULL(ra2.speed, ra.speed) speed, ra.race_id subrace_id, ra.race_name subrace_name, IFNULL(cl2.class_id, cl.class_id) class_id, IFNULL(cl2.class_name, cl.class_name) class_name, IFNULL(cl2.hit_die, cl.hit_die) hit_die, cl.class_id subclass_id, cl.class_name subclass_name, ba.value_id background_id, ba.set_value background_name, ba.value_desc background_desc, IF(ch.caster, 'true', 'false') caster, ch.created_by from characters ch, classes cl LEFT OUTER JOIN classes cl2 ON cl.parent_id = cl2.class_id, races ra LEFT OUTER JOIN races ra2 ON ra.parent_id = ra2.race_id, set_values ba {} where ch.class_id = cl.class_id and ch.race_id = ra.race_id and ch.background_id = ba.value_id """ params = ev["queryStringParameters"] if char_id != None: q = q.format("") q = q + " and ch.char_id = %s" params = char_id char = mysql_connector.single_query_json(q, params) char["race_traits"] = get_race_traits(char["race_id"]) char["class_traits"] = get_class_traits(char["class_id"]) char["armor"] = get_char_armor(char_id) char["spells"] = get_char_spells(char_id) char["weapons"] = get_char_weapons(char_id) return char elif params != None and "camp_id" in params: q = q.format(", campaign_chars cc ") q = q + "and ch.char_id = cc.char_id and cc.camp_id = %s" params = params["camp_id"] chars = [ get_char(c["char_id"]) for c in mysql_connector.query_json(q, params) ] return chars else: q = q.format("") q = q + " and ch.created_by = %s" params = mysql_connector.get_username(ev) chars = [ get_char(c["char_id"]) for c in mysql_connector.query_json(q, params) ] return chars
def handler(event, context): q = """ select sv.value_id, sv.set_value, sv.value_desc, sv.created_by, vs.set_id, vs.set_name from set_values sv, value_sets vs where sv.set_id = vs.set_id """ try: httpMethod = event["httpMethod"] if httpMethod == "GET": params = event["queryStringParameters"] if params != None and "set_name" in params: q += " and vs.set_name = %s" params = (params["set_name"]) return mysql_connector.query(q, params) elif httpMethod == "POST": try: sql = """ INSERT INTO set_values ( set_value, set_id, value_desc, created_by, created_date, updated_by, updated_date ) VALUES ( %s, (SELECT vs.set_id FROM value_sets vs WHERE vs.set_name = %s), %s, %s, SYSDATE(), %s, SYSDATE()); """ data = json.loads(event["body"]) params = (data.get("set_value", ""), data.get("set_name", ""), data.get("value_desc", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute(sql, params) q += """ and sv.value_id = LAST_INSERT_ID()""" return mysql_connector.single_query(q) except Exception as e: return mysql_connector.client_error("Invalid POST data" + str(e)) elif httpMethod == "PUT": try: sql = """ UPDATE set_values SET set_value = %s, value_desc = %s, updated_by = %s, updated_date = SYSDATE() WHERE value_id = %s """ data = json.loads(event["body"]) params = (data.get("set_value", ""), data.get("value_desc", ""), mysql_connector.get_username(event), data.get("value_id", "")) mysql_connector.execute(sql, params) params = (data.get("value_id", "")) q += """ and sv.value_id = %s""" return mysql_connector.single_query(q, params) except: return mysql_connector.client_error("Invalid PUT data") elif httpMethod == "DELETE": try: sql = """ DELETE FROM set_values WHERE value_id = %s """ params = event["queryStringParameters"] params = (params["value_id"]) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid DELETE data") else: return mysql_connector.client_error("Invalid HTTP Method") except: return mysql_connector.server_error("Unknown server error")
def handler(event, context): q = """ select weapon_id, weapon_name, weapon_desc, weapon_type, cost, damage, weight, weapon_props, created_by from weapons where 1=1 """ try: httpMethod = event["httpMethod"] if httpMethod == "GET": params = event["queryStringParameters"] if params != None and "weapon_id" in params: q += " and weapon_id = %s" params = (params["weapon_id"]) return mysql_connector.single_query(q, params) else: return mysql_connector.query(q, params) elif httpMethod == "POST": try: sql = """ INSERT INTO weapons ( weapon_name, weapon_desc, weapon_type, cost, damage, weight, weapon_props, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ data = json.loads(event["body"]) params = (data.get("weapon_name", ""), data.get("weapon_desc", ""), data.get("weapon_type", ""), data.get("cost", ""), data.get("damage", ""), data.get("weight", ""), data.get("weapon_props", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute(sql, params) q += """ and weapon_id = LAST_INSERT_ID()""" return mysql_connector.single_query(q) except: return mysql_connector.client_error("Invalid POST data") elif httpMethod == "PUT": try: sql = """ UPDATE weapons SET weapon_name = %s, weapon_desc = %s, weapon_type = %s, cost = %s, damage = %s, weight = %s, weapon_props = %s, updated_by = %s, updated_date = SYSDATE() WHERE weapon_id = %s """ data = json.loads(event["body"]) params = (data.get("weapon_name", ""), data.get("weapon_desc", ""), data.get("weapon_type", ""), data.get("cost", ""), data.get("damage", ""), data.get("weight", ""), data.get("weapon_props", ""), mysql_connector.get_username(event), data.get("weapon_id", "")) mysql_connector.execute(sql, params) params = (data.get("weapon_id", "")) q += """ and weapon_id = %s""" return mysql_connector.single_query(q, params) except: return mysql_connector.client_error("Invalid PUT data") elif httpMethod == "DELETE": try: sql = """ DELETE FROM weapons WHERE weapon_id = %s """ params = event["queryStringParameters"] params = (params["weapon_id"]) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid DELETE data") else: return mysql_connector.client_error("Invalid HTTP Method") except: return mysql_connector.server_error("Unknown server error")
def handler(event, context): q = """ select armor_id, armor_name, armor_desc, armor_type, cost, armor_class, strength, IF(stealth_dis, 'true', 'false') stealth_dis, weight, created_by from armor where 1=1 """ try: httpMethod = event["httpMethod"] if httpMethod == "GET": params = event["queryStringParameters"] if params != None and "armor_id" in params: q += " and armor_id = %s" params = (params["armor_id"]) return mysql_connector.single_query(q, params) else: return mysql_connector.query(q, params) elif httpMethod == "POST": try: sql = """ INSERT INTO armor ( armor_name, armor_desc, armor_type, cost, armor_class, strength, stealth_dis, weight, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ data = json.loads(event["body"]) params = (data.get("armor_name", ""), data.get("armor_desc", ""), data.get("armor_type", ""), data.get("cost", ""), data.get("armor_class", ""), data.get("strength", ""), (1 if str(data.get("stealth_dis", "")).lower() == "true" else 0), data.get("weight", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute(sql, params) q += """ and armor_id = LAST_INSERT_ID()""" return mysql_connector.single_query(q) except: return mysql_connector.client_error("Invalid POST data") elif httpMethod == "PUT": try: sql = """ UPDATE armor SET armor_name = %s, armor_desc = %s, armor_type = %s, cost = %s, armor_class = %s, strength = %s, stealth_dis = %s, weight = %s, updated_by = %s, updated_date = SYSDATE() WHERE armor_id = %s """ data = json.loads(event["body"]) params = (data.get("armor_name", ""), data.get("armor_desc", ""), data.get("armor_type", ""), data.get("cost", ""), data.get("armor_class", ""), data.get("strength", ""), (1 if str(data.get("stealth_dis", "")).lower() == "true" else 0), data.get("weight", ""), mysql_connector.get_username(event), data.get("armor_id", "")) mysql_connector.execute(sql, params) params = (data.get("armor_id", "")) q += """ and armor_id = %s""" return mysql_connector.single_query(q, params) except: return mysql_connector.client_error("Invalid PUT data") elif httpMethod == "DELETE": try: sql = """ DELETE FROM armor WHERE armor_id = %s """ params = event["queryStringParameters"] params = (params["armor_id"]) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid DELETE data") else: return mysql_connector.client_error("Invalid HTTP Method") except: return mysql_connector.server_error("Unknown server error")
def handler(event, context): try: httpMethod = event["httpMethod"] if httpMethod == "GET": q = """ select set_id, set_name, set_desc, created_by from value_sets """ params = event["queryStringParameters"] if params != None and "set_name" in params: q += " where set_name = %s" params = (params["set_name"]) return mysql_connector.single_query(q, params) return mysql_connector.query(q, params) elif httpMethod == "POST": try: sql = """ INSERT INTO value_sets ( set_name, set_desc, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ data = json.loads(event["body"]) params = (data.get("set_name", ""), data.get("set_desc", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid POST data") elif httpMethod == "PUT": try: sql = """ UPDATE value_sets SET set_name = %s, set_desc = %s, updated_by = %s, updated_date = SYSDATE() WHERE set_id = %s """ data = json.loads(event["body"]) params = (data.get("set_name", ""), data.get("set_desc", ""), mysql_connector.get_username(event), data.get("set_id", "")) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid PUT data") elif httpMethod == "DELETE": try: sql = """ DELETE FROM value_sets WHERE set_id = %s """ data = json.loads(event["body"]) params = (data.get("set_id", "")) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid DELETE data") else: return mysql_connector.client_error("Invalid HTTP Method") except: return mysql_connector.server_error("Unknown server error")
def handler(event, context): q = """ select spell_id, spell_name, spell_desc, spell_level, higher_level, spell_range, components, IF(ritual, 'true', 'false') ritual, duration, casting_time, spell_school, created_by from spells where 1=1 """ try: httpMethod = event["httpMethod"] if httpMethod == "GET": params = event["queryStringParameters"] if params != None and "spell_id" in params: q += " and spell_id = %s" params = (params["spell_id"]) return mysql_connector.single_query(q, params) else: return mysql_connector.query(q, params) elif httpMethod == "POST": try: sql = """ INSERT INTO spells ( spell_name, spell_desc, spell_level, higher_level, spell_range, components, ritual, duration, casting_time, spell_school, created_by, created_date, updated_by, updated_date ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, SYSDATE(), %s, SYSDATE()); """ data = json.loads(event["body"]) params = (data.get("spell_name", ""), data.get("spell_desc", ""), data.get("spell_level", ""), data.get("higher_level", ""), data.get("spell_range", ""), data.get("components", ""), (1 if str(data.get("ritual", "")).lower() == "true" else 0), data.get("duration", ""), data.get("casting_time", ""), data.get("spell_school", ""), mysql_connector.get_username(event), mysql_connector.get_username(event)) mysql_connector.execute(sql, params) q += """ and spell_id = LAST_INSERT_ID()""" return mysql_connector.single_query(q) except: return mysql_connector.client_error("Invalid POST data") elif httpMethod == "PUT": try: sql = """ UPDATE spells SET spell_name = %s, spell_desc = %s, spell_level = %s, higher_level = %s, spell_range = %s, components = %s, ritual = %s, duration = %s, casting_time = %s, spell_school = %s, updated_by = %s, updated_date = SYSDATE() WHERE spell_id = %s """ data = json.loads(event["body"]) params = (data.get("spell_name", ""), data.get("spell_desc", ""), data.get("spell_level", ""), data.get("higher_level", ""), data.get("spell_range", ""), data.get("components", ""), (1 if str(data.get("ritual", "")).lower() == "true" else 0), data.get("duration", ""), data.get("casting_time", ""), data.get("spell_school", ""), mysql_connector.get_username(event), data.get("spell_id", "")) mysql_connector.execute(sql, params) params = (data.get("spell_id", "")) q += """ and spell_id = %s""" return mysql_connector.single_query(q, params) except: return mysql_connector.client_error("Invalid PUT data") elif httpMethod == "DELETE": try: sql = """ DELETE FROM spells WHERE spell_id = %s """ params = event["queryStringParameters"] params = (params["spell_id"]) return mysql_connector.execute(sql, params) except: return mysql_connector.client_error("Invalid DELETE data") else: return mysql_connector.client_error("Invalid HTTP Method") except: return mysql_connector.server_error("Unknown server error")