1 files changed, 144 insertions, 0 deletions
diff --git a/code/dbsetup.py b/code/dbsetup.py
new file mode 100644
index 0000000..acd6ce6
--- /dev/null
+++ b/code/dbsetup.py
@@ -0,0 +1,144 @@
+import os, re, config, sys, mysql.connector
+from colorama import Fore, Style, init
+from mysql.connector import errorcode
+from config import SQL_HOST as MYhost
+from config import SQL_PORT as MYport
+from config import SQL_USER as MYuser
+from config import SQL_PASS as MYpass
+from config import SQL_DATABASE as MYbase
+
+TABLES = {}
+TABLES['events'] = (
+    "CREATE TABLE `events` ("
+    "  `id` int NOT NULL AUTO_INCREMENT,"
+    " `type` text NOT NULL,"
+    " `smessage` text NOT NULL,"
+    " `image` text NOT NULL,"
+    " PRIMARY KEY (`id`),"
+    " UNIQUE KEY `type` (`type`(7)) USING BTREE"
+    ") ENGINE=InnoDB")
+
+if config.EXSERVERINFO:
+    TABLES['exstats'] = (
+        "CREATE TABLE `exstats` ("
+        "  `id` int NOT NULL AUTO_INCREMENT,"
+        "  `savezdos` varchar(50) DEFAULT NULL,"
+        "  `savesec` varchar(10) DEFAULT NULL,"
+        "  `worldsize` varchar(10) DEFAULT NULL,"
+        "  `serverversion` varchar(10) DEFAULT NULL,"
+        "  `plusversion` varchar(10) DEFAULT NULL,"
+        "  `gameday` int DEFAULT NULL,"
+        "  `timestamp` bigint DEFAULT NULL,"
+        "  PRIMARY KEY (`id`)"
+        ") ENGINE=InnoDB")
+
+if config.PLOCINFO:
+    TABLES['plocinfo'] = (
+        "CREATE TABLE `plocinfo` ("
+        "  `id` int NOT NULL AUTO_INCREMENT,"
+        "  `locations` varchar(10) DEFAULT NULL,"
+        "  `zone` varchar(10) DEFAULT NULL,"
+        "  `duration` varchar(10) DEFAULT NULL,"
+        "  PRIMARY KEY (`id`)"
+        ") ENGINE=InnoDB")
+
+TABLES['players'] = (
+    "CREATE TABLE `players` ("
+    "  `id` int NOT NULL AUTO_INCREMENT,"
+    "  `user` varchar(100) NOT NULL,"
+    "  `deaths` int NOT NULL DEFAULT '0',"
+    "  `valid` varchar(50) DEFAULT NULL,"
+    "  `startdate` varchar(20) DEFAULT NULL,"
+    "  `playtime` bigint DEFAULT '0',"
+    "  `jointime` bigint DEFAULT NULL,"
+    "  `ingame` int NOT NULL DEFAULT '0',"
+    "  PRIMARY KEY (`id`) USING BTREE,"
+    "  UNIQUE KEY `users` (`user`)"
+    ") ENGINE=InnoDB")
+
+TABLES['serverstats'] = (
+    "CREATE TABLE `serverstats` ("
+    "  `id` int NOT NULL AUTO_INCREMENT,"
+    "  `date` varchar(20) DEFAULT NULL,"
+    "  `timestamp` bigint DEFAULT NULL,"
+    "  `users` int NOT NULL DEFAULT '0',"
+    "  PRIMARY KEY (`id`) USING BTREE,"
+    "  UNIQUE KEY `timestamp` (`timestamp`)"
+    ") ENGINE=InnoDB")
+
+def mydbconnect():
+    global mydb
+    mydb = mysql.connector.connect(
+        host=MYhost,
+        user=MYuser,
+        password=MYpass,
+        database=MYbase,
+        port=MYport,
+        )
+    try:
+        if mydb.is_connected():
+            db_Info = mydb.get_server_info()
+            print(Fore.GREEN + "Connected to MySQL database... MySQL Server version ", db_Info + Style.RESET_ALL)
+    except mysql.connector.Error as err:
+        print(Fore.RED + err + 'From MySQL database' + Style.RESET_ALL)
+
+mydbconnect()
+
+def maketable():
+    mycursor = mydb.cursor()
+    for table_name in TABLES:
+        table_description = TABLES[table_name]
+        try:
+            print(Fore.GREEN + "Creating table {}: ".format(table_name), end='')
+            mycursor.execute(table_description)
+            if table_name == "events":
+                eventinsert()
+            if table_name == "exstats":
+                exstatinsert()
+        except mysql.connector.Error as err:
+            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
+                print(Fore.RED + "already exists." + Style.RESET_ALL)
+            else:
+                print(Fore.RED + err.msg + Style.RESET_ALL)
+        else:
+            print(Fore.GREEN + "OK" + Style.RESET_ALL)
+    mycursor.close()
+    print(Fore.GREEN + "Done" + Style.RESET_ALL)
+
+def eventinsert():
+    print(Fore.GREEN + "Adding events info to table events" + Style.RESET_ALL)
+    mycursor1 = mydb.cursor()
+    sql = """INSERT INTO `events` (`id`, `type`, `smessage`, `image`) VALUES (%s, %s, %s, %s)"""
+    val = [
+        (1, 'Skeletons', 'Skeleton Surprise', 'skeleton.png'),
+        (2, 'Blobs', '..', 'Ooze.png'),
+        (3, 'Foresttrolls', 'The ground is shaking', 'troll.png'),
+        (4, 'Wolves', 'You are being hunted', 'wolf.png'),
+        (5, 'Surtlings', 'There\'s a smell of sulfur in the air', 'surtling.png'),
+        (6, 'Eikthyrnir', 'Meadows', 'Eikthyr.png'),
+        (7, 'GDKing', 'Black Forest', 'The_Elder.png'),
+        (8, 'Bonemass', 'Swamp', 'Bonemass.png'),
+        (9, 'Dragonqueen', 'Mountain', 'Moder.png'),
+        (10, 'GoblinKing', 'Plains', 'Yagluth.png'),
+        (11, 'army_eikthyr', 'Eikthyr rallies the creatures of the forest', 'Boar.png'),
+        (12, 'army_theelder', 'The forest is moving...', 'Greydwarf.png'),
+        (13, 'army_bonemass', 'A foul smell from the swamp', 'Draugr.png'),
+        (14, 'army_moder', 'A cold wind blows from the mountains', 'Drake.png'),
+        (15, 'army_goblin', 'The horde is attacking', 'Fuling.png')
+    ]
+    mycursor1.executemany(sql, val)
+    mydb.commit()
+    mycursor1.close()
+
+def exstatinsert():
+    mycursor2 = mydb.cursor()
+    print(Fore.GREEN + "Adding 1st row to table exstats" + Style.RESET_ALL)
+    sql = """INSERT INTO `exstats` VALUES (1,'NULL','NULL','NULL','NULL',NULL,NULL,1616448381)"""
+    mycursor2.execute(sql)
+    mydb.commit()
+    mycursor2.close()
+
+
+maketable()
+mydb.close()
+exit()