path: root/sql.py

########################################################################################
#     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 <http://www.gnu.org/licenses/>.
########################################################################################
# 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()