######################################################################################## # This file is part of Spheres. # Copyright (C) 2019 Jesusalva # This program is free software: you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation, either version 3 of the License, or # (at your option) any later version. # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program. If not, see . ######################################################################################## # SQL Module import mysql.connector import uuid, threading, json from utils import dlist, Player, stdout, now, create_password from consts import (SQL_NONE, SQL_DELAY, ERR_OK, ERR_ERR, ERR_BAD, MAX_PARTIES, SQL_PINGTIME) ############################################################### # Configuration f=open("pass.json", "r") p=json.load(f) f.close() SQLHOST=p["HOST"] SQLUSER=p["SQLUSER"] SQLPASS=p["SQLPASS"] SQLDBID=p["SQLDB"] clients = [] ############################################################### # Connect to database db = mysql.connector.connect( host=SQLHOST, user=SQLUSER, passwd=SQLPASS, database=SQLDBID ) ####################################### Private methods def save_player(token, mask=SQL_NONE): global Player w = db.cursor() m="" # Do "update delayed" even exist? #if mask & SQL_DELAY: # m+=" DELAYED" # Update player APTime before saving Player[token]["aptime"]=now() #stdout(str(Player[token])) sqlx="""UPDATE%s `player` SET status = %d, quest = %d, gp = %d, crystals = %d, exp = %d, level = %d, ap = %d, max_ap = %d, aptime = %d, max_sum = %d WHERE `userid`=%d""" % (m, Player[token]["status"], Player[token]["quest"], Player[token]["gp"], Player[token]["crystals"], Player[token]["exp"], Player[token]["level"], Player[token]["ap"], Player[token]["max_ap"], Player[token]["aptime"], Player[token]["max_sum"], Player[token]["userid"]) stdout(sqlx, 2) try: w.execute(sqlx) except: stdout("SQL.ERR: Error happened (save.player), commiting anyway due MyISAM...", 0) stdout("Faulty: %s" % sqlx, 0) #w.execute("INSERT INTO ....") # <- Not in save_player #w.execute("REPLACE DELAYED....") # <- Not where you can't delete, but for inv #w.execute("""INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19""") #w.execute("""INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name=name, age=19;""") db.commit() w.close() return ERR_OK def load_inv(token): uid=Player[token]["userid"] inv=dlist() w = db.cursor(dictionary=True) w.execute("SELECT * FROM `inventory` WHERE `userid`=%d" % (uid)) r = w.fetchall() for it in r: inv[it["index"]]={"unit_id": it["unit_id"], "level": it["unit_lv"], "exp": it["unit_xp"]} w.close() return inv def save_inv(token, mask=SQL_NONE, force=False): uid=Player[token]["userid"] inv=Player[token]["inv"] fatalmsg="This error is fatal, we abort inventory saving!" m="" # FIXME: This is not a transactional database if mask & SQL_DELAY: m+=" DELAYED" w = db.cursor() i=0 revert=False # If force, we will commit whatever we have if (force): fatalmsg="" while i < len(inv): it=inv[i] # Blank items doesn't need saving, but may need deleting!! if (it is None): # Do we need to perhaps delete the ID? w.execute("""DELETE FROM `inventory` WHERE `userid` = %d AND `index` = %d""" % (uid, i)) stdout("SQL.INV: Delete where uid %d and index %d" % (uid, i), 2) i+=1 continue # Non-blank items, however, do need try: w.execute("""REPLACE%s INTO `inventory` (`userid`, `index`, `unit_id`, `unit_lv`, `unit_xp`) VALUES (%d, %d, %d, %d, %d)""" % (m, uid, i, it["unit_id"], it["level"], it["exp"])) except: # Something went wrong! stdout("ERROR Saving item index %d for player %d: %s\n%s" % (i, uid, str(it), fatalmsg), 0) if not force: revert=True finally: i+=1 # If something went wrong, don't save inventory if revert: #db.rollback() stdout("SQL.ERR: Error happened, commiting anyway due MyISAM...", 0) # We should now remove every item with an index higher than length # In case the inventory shrink without None's taking the place... w.execute("""DELETE FROM `inventory` WHERE `userid` = %d AND `index` >= %d""" % (uid, len(inv))) stdout("SQL.INV: delete indexes >= %d, committing changes" % len(inv), 2) db.commit() w.close() return inv def save_party(token, mask=SQL_NONE): global Player w = db.cursor() m="" # Do "update delayed" even exist? #if mask & SQL_DELAY: # m+=" DELAYED" i=1 while i <= MAX_PARTIES: try: sqlx="""UPDATE%s `party` SET member1_id = %d, member1_ix = %d, member2_id = %d, member2_ix = %d, member3_id = %d, member3_ix = %d, member4_id = %d, member4_ix = %d WHERE `userid`=%d AND `party_id`=%d""" % (m, Player[token]["party_%d" % i][0]["unit_id"], Player[token]["party_%d" % i][0]["inv_id"], Player[token]["party_%d" % i][1]["unit_id"], Player[token]["party_%d" % i][1]["inv_id"], Player[token]["party_%d" % i][2]["unit_id"], Player[token]["party_%d" % i][2]["inv_id"], Player[token]["party_%d" % i][3]["unit_id"], Player[token]["party_%d" % i][3]["inv_id"], Player[token]["userid"], i) stdout(sqlx, 2) w.execute(sqlx) except: stdout("[SQL ERROR] Impossible to save party %d" % i, 0) db.rollback() i+=1 db.commit() w.close() return ERR_OK def load_party(token, pid): uid=Player[token]["userid"] pid=int(pid) inv=[] w = db.cursor(dictionary=True) w.execute("SELECT * FROM `party` WHERE `userid`=%d AND `party_id`=%d" % (uid, pid)) r = w.fetchone() j=0 while j < 4: j+=1 try: inv.append({"unit_id": r["member%d_id" % j], "inv_id": r["member%d_ix" % j]}) except TypeError: # Maybe we should append an empty field instead? inv.append({"unit_id": 0, "inv_id": -1}) except: print("Error loading party (token: %s, ID: %d) (j: %d k: %d)" % (token, uid, j, pid)) print("r is: %s" % str(r)) return ERR_ERR w.close() return inv def query_email(xmail): w = db.cursor(dictionary=True) w.execute("SELECT `userid` FROM `login` WHERE `email`='%s'" % (xmail)) r = w.fetchall() c = "" for it in r: c+=str(it) w.close() return c def clobber_email(userid, oldmail, newmail): w = db.cursor(dictionary=True) w.execute("SELECT `userpw` FROM `login` WHERE `userid`='%s' AND `email`='%s'" % (userid, oldmail)) r = w.fetchall() c = "" for it in r: c+=str(it) w.close() w = db.cursor() w.execute("UPDATE `login` SET `email` = '%s' WHERE `userid`='%s' AND `email`='%s'" % (newmail, userid, oldmail)) w.close() return c def add_player(xmail): # TODO: Handle possible collisions # The original string have 32 letters and we're using only 12 # Since 28-07-2022 we're using 16 letters from all alphabet passwd=create_password(16) w = db.cursor() # FIXME: Escaping email would be a good idea, but is not needed here. # Why is it not needed? Because this is a private function called by # player.register(), which already applies a regex filter and hard-fails # in case the email is invalid. SQL Injections would result in an invalid # email. # BECAUSE THIS IS A PRIVATE FUNCTION, I've been negligent to check twice. # Or to escape the string, just to be safe. w.execute("""INSERT INTO `login` (userpw, email) VALUES ("%s", "%s")""" % (passwd, xmail)) db.commit() # Retrieve the new userid userid=w.lastrowid # Less relevant # Prepare tutorial data w.execute("""INSERT INTO `player` (userid) VALUES (%d)""" % (userid)) w.execute("""INSERT INTO `inventory` (`userid`, `index`, `unit_id`) VALUES (%d, 0, 10000000)""" % (userid)) w.execute("""INSERT INTO `party` (userid, party_id, member1_id, member1_ix) VALUES (%d, 1, 10000000, 0)""" % (userid)) w.execute("""INSERT INTO `party` (userid, party_id) VALUES (%d, 2)""" % (userid)) w.execute("""INSERT INTO `party` (userid, party_id) VALUES (%d, 3)""" % (userid)) db.commit() w.close() bf={"userid": userid, "password": passwd} return bf ####################################### Public methods (Player/Client) def load_player(token, password): w = db.cursor(dictionary=True) # This is impossible try: if not password.isalnum(): raise Exception("Idiotic password") except: w.close() return ERR_ERR # FIXME: Escaping password would be a good idea, but is not needed here. # Why is it not needed? Because this is a private function called by # player.get_data(), which already applies an isalpnum filter and hard-fails # in case the password is invalid. SQL Injections would result in an invalid # password, which must be alphanumeric. # BECAUSE THIS IS A PRIVATE FUNCTION, I've been negligent to check twice. # Or to escape the string, just to be safe. # Validade userpw (the recovery password), return ERR_BAD if not found w.execute("SELECT `userid` FROM `login` WHERE `userpw`='%s'" % (password)) r = w.fetchone() if r is None: w.close() return ERR_BAD # Select the user ID uid=r["userid"] # Retrieve player data w.execute("SELECT * FROM `player` WHERE `userid`=%d" % (uid)) r = w.fetchone() try: tmp=r["status"] del tmp except: # User account does not exists, or something went wrong!! w.close() return ERR_ERR # Mark the player login AFTER we got the data w.execute("UPDATE `player` SET lastlogin = CURRENT_TIMESTAMP WHERE `userid`=%d" % uid) db.commit() # Send Player Structure (last login data is deleted on daily login handler) w.close() return r # sql.keep_alive() -> Pinger routine def keep_alive(): try: db.ping(reconnect=True, attempts=10, delay=1) except: # SQL error stdout("keep_alive: INTERNAL ERROR", 0) db.reconnect(attempts=12, delay=10) sql_keep_alive=threading.Timer(SQL_PINGTIME, keep_alive) sql_keep_alive.daemon=True sql_keep_alive.start() return # Begin sql.keep_alive() routine keep_alive()