Import the good ol' SQL module.

1 files changed, 365 insertions, 0 deletions

diff --git a/sql.py b/sql.py
new file mode 100644
index 0000000..40ee446
--- /dev/null
+++ b/sql.py
@@ -0,0 +1,365 @@
+########################################################################################
+#     This file is part of Spheres.
+#     Copyright (C) 2019  Jesusalva
+
+#     This library is free software; you can redistribute it and/or
+#     modify it under the terms of the GNU Lesser General Public
+#     License as published by the Free Software Foundation; either
+#     version 2.1 of the License, or (at your option) any later version.
+
+#     This library 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
+#     Lesser General Public License for more details.
+
+#     You should have received a copy of the GNU Lesser General Public
+#     License along with this library; if not, write to the Free Software
+#     Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
+########################################################################################
+# SQL Module
+import mysql.connector
+import uuid, threading, json
+from utils import dlist, Player, stdout, now
+from consts import *
+
+###############################################################
+# 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
+                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]["userid"])
+
+    stdout(sqlx)
+    try:
+        w.execute(sqlx)
+    except:
+        stdout("SQL.ERR: Error happened (save.player), commiting anyway due MyISAM...")
+
+    #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))
+            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!
+            print("ERROR Saving item index %d for player %d: %s\n%s" % (i, uid, str(it), fatalmsg))
+            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...")
+
+    # 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))
+    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)
+            w.execute(sqlx)
+        except:
+            print("[SQL ERROR] Impossible to save party %d" % i)
+            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 add_player(xmail):
+    # TODO: Generate password using the whole alphabet.
+    # The original string have 32 letters and we're using only 12
+    passwd=uuid.uuid4().hex[:12].upper()
+
+
+    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"]
+    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")
+        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()
+