How to change the database to mySQL in UMMORPG

Post Reply
Coffinmace
Posts: 4
Joined: Mon Jun 12, 2017 11:26 am

How to change the database to mySQL in UMMORPG

Post by Coffinmace » Mon Nov 13, 2017 4:36 am

Changing the default database setup for UMMORPG is a relatively straight forward task. You must have a decent working knowledge of mySQL and it's security options to get this done otherwise you will most likely end up leaving a big gaping hole ready for anyone to walk right into your database.

Original credit for this code goes to Tobias Strunz
https://bitbucket.org/Fhizban/bb_fhizmo ... ew-default

Let us begin...
  1. Download MySQL Connector/Net 6.10.4 or higher

    We need to get our hands on a working MySql.Data.dll file. Best bet is to grab it from the official source by using the URL below. When you get there make sure to set the operating system dropdown to .NET & Mono.

    URL: https://dev.mysql.com/downloads/connector/net/
     
    Once you have downloaded the zip file, open it and copy the MySql.Data.dll file to your plugins folder inside your ummorpg project
     
  2. Create Constants.cs

    Code: Select all

    	// =======================================================================================
    	// CLASS CONSTANTS
    	//
    	// This is just to keep all constants in one place
    	// =======================================================================================
    
    	public static class Constants {
    		
    		public const string MYSQL_SERVER 	= "localhost";
    		public const string MYSQL_USER 		= "yourMySqlUserName";
    		public const string MYSQL_CHARSET 	= "utf8";
    		public const string MYSQL_DATABASE 	= "yourMySqlDatabaseName";
    		public const string MYSQL_PASSWORD 	= "yourMySqlDatabasePassword";
    		public const int MYSQL_PORT 		= 3306; 
    
    	}
     
  3. Create Database_MySql.cs

    Code: Select all

    using UnityEngine;
    using UnityEngine.Networking;
    using System;
    using System.IO;
    using System.Linq;
    using System.Collections.Generic;
    using System.Data;
    using MySql.Data;								// From MySql.Data.dll in Plugins folder
    using MySql.Data.MySqlClient; 					// From MySql.Data.dll in Plugins folder
    
    	public class Database {
    		
    		static MySqlConnection conn;
    	
    		// =======================================================================================
    		// CLASS DATABASE
    		//
    		// MySQL conversion of uMMORPG SQLite Database.cs
    		// Requires MySQL Connector/Net 6.7.9
    		// Compatible with MySQL 5.0 - 5.7
    		//
    		// About item mall coins:
    		//  The payment provider's callback should add new orders to the
    		//  character_orders table. The server will then process them while the player
    		//  is ingame. Don't try to modify 'coins' in the character table directly.
    		// =======================================================================================
    	
    		static Database() {
    
    				string connStr = "";
    
    				connStr += "server=" 	+ Constants.MYSQL_SERVER + ";";			// From Constants.cs
    				connStr += "user=" 		+ Constants.MYSQL_USER + ";";			// From Constants.cs
    				connStr += "charset=" 	+ Constants.MYSQL_CHARSET + ";";		// From Constants.cs
    				connStr += "database=" 	+ Constants.MYSQL_DATABASE + ";";		// From Constants.cs
    				connStr += "port=" 		+ Constants.MYSQL_PORT + ";";			// From Constants.cs
    				connStr += "password=" 	+ Constants.MYSQL_PASSWORD + ";";		// From Constants.cs
    
    				// open connection        
    				conn = new MySqlConnection(connStr);
    				try
    				{
    					Debug.Log("Connecting to MySQL");			
    					conn.Open();
    				}
    				catch (Exception ex)
    				{
    					Debug.Log(ex.ToString());
    				}
    			
    				// Use the UTF-8 character set for queries and results for this client 
    				// NOTE: The Latin-1 character set is not compatible 
    				ExecuteNonQuery("SET NAMES @charset_name COLLATE @collation_name", 
    				new MySqlParameter("@charset_name", "utf8"), 
    				new MySqlParameter("@collation_name", "utf8_general_ci"));
    			
    		}
    	
    		// -----------------------------------------------------------------------------------
    		// Helper Functions
    		// -----------------------------------------------------------------------------------
    
    		// run a query that doesn't return anything
    		public static void ExecuteNonQuery(string sql, params MySqlParameter[] args) {
    			
    			using (var cmd = new MySqlCommand(sql, conn)) {
    				foreach (var param in args) cmd.Parameters.Add(param);
    				
    				//PrintCmd(cmd);														//debug
    				cmd.ExecuteNonQuery();
    			}
    		}
    
    		// run a query that returns a single value
    		public static object ExecuteScalar(string sql, params MySqlParameter[] args) {
    			
    			using (var cmd = new MySqlCommand(sql, conn)) {
    				foreach (var param in args) cmd.Parameters.Add(param);
    				
    				//PrintCmd(cmd);														//debug
    				return cmd.ExecuteScalar();
    			}
    		}
    
    		// run a query that returns several values
    		public static List< List<object> > ExecuteReader(string sql, params MySqlParameter[] args) {
    			
    			var result = new List< List<object> >();        
    
    			using (var cmd = new MySqlCommand(sql, conn)) {
    		   
    				foreach (var param in args) cmd.Parameters.Add(param);
    				
    				using (var reader = cmd.ExecuteReader()) { 
    					while (reader.Read()) {
    						var buf = new object[reader.FieldCount];
    						reader.GetValues(buf);
    						result.Add(buf.ToList());
    					}
    				}
    
    				//PrintCmd(cmd);														//debug
    			}
    			
    																
    			return result;
    		}
    		
    		
    		// debug: echo command text as string
    		public static void PrintCmd(MySqlCommand cmd) {
    			string cmdtext = cmd.CommandText;
    			foreach (MySqlParameter p in cmd.Parameters)
    			cmdtext = cmdtext.Replace(p.ParameterName, p.Value.ToString());
    			Debug.Log(cmdtext);
    		}
    
    		// -----------------------------------------------------------------------------------
    		// Account Data
    		// -----------------------------------------------------------------------------------
    
    		public static bool IsValidAccount(string account, string password) {
    			// this function can be used to verify account credentials in a database
    			// or a content management system. 
    
    			// not empty?
    			if (!Utils.IsNullOrWhiteSpace(account) && !Utils.IsNullOrWhiteSpace(password)) {
    				var table = ExecuteReader("SELECT password, banned FROM accounts WHERE name=@name LIMIT 1", new MySqlParameter("@name", account));
    				if (table.Count == 1) {
    					// account exists. check password and ban status.
    					var row = table[0];
    					return (string)row[0] == password && (byte)row[1] == 0;
    				} else {
    					// account doesn't exist. create it.
    					ExecuteNonQuery("INSERT INTO accounts VALUES (@name, @password, 0, 0)", new MySqlParameter("@name", account), new MySqlParameter("@password", password));
    					return true;
    				}
    			}
    			return false;
    		}
    
    		// -----------------------------------------------------------------------------------
    		// Character Data
    		// -----------------------------------------------------------------------------------
    
    		public static bool CharacterExists(string charName) {
    			// checks deleted ones too so we don't end up with duplicates if we un-
    			// delete one
    			return ((long)ExecuteScalar("SELECT Count(*) FROM characters WHERE `name`=@name LIMIT 1", new MySqlParameter("@name", charName))) == 1;
    		}
    
    		public static void CharacterDelete(string charName) {
    			// soft delete the character so it can always be restored later
    			ExecuteNonQuery("UPDATE characters SET deleted=1 WHERE `name`=@character LIMIT 1", new MySqlParameter("@character", charName));
    		}
    
    		// returns a dict of<character name, character class=prefab name>
    		// we really need the prefab name too, so that client character selection
    		// can read all kinds of properties like icons, stats, 3D models and not
    		// just the character name
    		public static Dictionary<string, string> CharactersForAccount(string account) {
    			var result = new Dictionary<string, string>();
    
    			var table = ExecuteReader("SELECT `name`, class FROM characters WHERE `account`=@account AND deleted=0 LIMIT 4", new MySqlParameter("@account", account));
    			foreach (var row in table)
    				result[(string)row[0]] = (string)row[1];
    
    			return result;
    		}
    	
    		// -----------------------------------------------------------------------------------
    		// CharacterLoad
    		// -----------------------------------------------------------------------------------
    		public static GameObject CharacterLoad(string charName, List<Player> prefabs) {
    			var table = ExecuteReader("SELECT * FROM characters WHERE `name`=@name AND deleted=0 LIMIT 1", new MySqlParameter("@name", charName));
    			if (table.Count == 1) {
    				var mainrow = table[0];
    
    				// instantiate based on the class name
    				string className = (string)mainrow[2];
    				var prefab = prefabs.Find(p => p.name == className);
    				if (prefab != null) {
    					var go = (GameObject)GameObject.Instantiate(prefab.gameObject);
    					var player = go.GetComponent<Player>();
    				
    					player.name               = (string)mainrow[0];
    					player.account            = (string)mainrow[1];
    					player.className          = (string)mainrow[2];
    					var x                     = (float)mainrow[3];
    					var y                     = (float)mainrow[4];
    					var z                     = (float)mainrow[5];
    					// NEVER use player.transform.position = ...; because it
    					// places the player at weird positions. for example,
    					// (200, 0, -200) becomes (76, 0, -76)
    					// using agent.Warp is also recommended in the Unity docs.
    					player.agent.Warp(new Vector3(x, y, z));
    					player.level              = (int)mainrow[6];
    					player.health             = (int)mainrow[7];
    					player.mana               = (int)mainrow[8];
    					player.strength			  = (int)mainrow[9];
    					player.intelligence		  = (int)mainrow[10];
    					player.experience         = (long)mainrow[11];
    					player.skillExperience    = (long)mainrow[12];
    					player.gold               = (long)mainrow[13];
    					player.coins              = (long)mainrow[14]; 
    				
    					// ---------------------------------------------------- load inventory based on inventorySize (creates slots if none)
    					for (int i = 0; i < player.inventorySize; ++i) {
    						// any saved data for that slot?
    						table = ExecuteReader("SELECT `name`, valid, amount FROM character_inventory WHERE `character`=@character AND slot=@slot LIMIT 1", new MySqlParameter("@character", player.name), new MySqlParameter("@slot", i));
    						if (table.Count == 1) {
    							var row = table[0];
    							var item = new Item();
    							item.name = (string)row[0];
    							item.valid = (byte)row[1] != 0; 
    							item.amount = (int)row[2];
    
    							// add item if template still exists, otherwise empty
    							player.inventory.Add(item.valid && item.TemplateExists() ? item : new Item());
    						} else {
    							// add empty slot or default item if any
    							player.inventory.Add(i < player.defaultItems.Length ? new Item(player.defaultItems[i]) : new Item());
    						}
    					}
    
    					// ---------------------------------------------------- load equipment based on equipmentTypes (creates slots if none)
    					for (int i = 0; i < player.equipmentTypes.Length; ++i) {
    						// any saved data for that slot?
    						table = ExecuteReader("SELECT `name`, valid, amount FROM character_equipment WHERE `character`=@character AND slot=@slot LIMIT 1", new MySqlParameter("@character", player.name), new MySqlParameter("@slot", i));
    						if (table.Count == 1) {
    							var row = table[0];
    							var item = new Item();
    							item.name = (string)row[0];
    							item.valid = (byte)row[1] != 0; 
    							item.amount = (int)row[2];
    
    							// add item if template still exists, otherwise empty
    							player.equipment.Add(item.valid && item.TemplateExists() ? item : new Item());
    						} else {
    							// add empty slot or default item if any
    							string equipType = player.equipmentTypes[i];
    							int idx = player.defaultEquipment.FindIndex(equip => player.CanEquip(equipType, new Item(equip)));
    							player.equipment.Add(idx != -1 ? new Item(player.defaultEquipment[idx]) : new Item());
    						}
    					}
    
    					// ---------------------------------------------------- load skills based on skill templates (the others don't matter)
    					foreach (var t in player.skillTemplates) {
    						// create skill based on template
    						var skill = new Skill(t);
    
    						// load saved data if any
    						table = ExecuteReader("SELECT learned, level, castTimeEnd, cooldownEnd, buffTimeEnd FROM character_skills WHERE `character`=@character AND `name`='@name'", new MySqlParameter("@character", charName), new MySqlParameter("@name", t.name));
    						foreach (var row in table) {
    							skill.learned = (byte)row[0] != 0;
    							// make sure that 1 <= level <= maxlevel (in case we removed a skill
    							// level etc)
    							skill.level = Mathf.Clamp((int)row[1], 1, skill.maxLevel);
    							// castTimeEnd and cooldownEnd are based on Time.time, which
    							// will be different when restarting a server, hence why we
    							// saved them as just the remaining times. so let's convert them
    							// back again.
    							skill.castTimeEnd = (float)row[2] + Time.time;
    							skill.cooldownEnd = (float)row[3] + Time.time;
    							skill.buffTimeEnd = (float)row[4] + Time.time;
    						}
    
    						player.skills.Add(skill);
    					}
    				
    					// ---------------------------------------------------- load quests
    					table = ExecuteReader("SELECT `name`, killed, completed FROM character_quests WHERE `character`=@character", new MySqlParameter("@character", player.name));
    					foreach (var row in table) {
    						var quest = new Quest();
    						quest.name = (string)row[0];
    						quest.killed = (int)row[1];
    						quest.completed = (byte)row[2] != 0; 
    						player.quests.Add(quest.TemplateExists() ? quest : new Quest());
    					}
    				
    					// ---------------------------------------------------- in a guild?
    					string guild = (string)ExecuteScalar("SELECT `guild` FROM guild_members WHERE `character`=@character", new MySqlParameter("@character", player.name));
    					if (guild != null) {
    						// load guild info
    						player.guild = guild;
    						table = ExecuteReader("SELECT `notice` FROM guild_info WHERE `name`=@guild", new MySqlParameter("@guild", guild));
    						if (table.Count == 1) {
    							var row = table[0];
    							//player.guildNotice = (string)row[0];
    						}
    
    						// load members list
    						table = ExecuteReader("SELECT `character`, rank FROM guild_members WHERE guild=@guild", new MySqlParameter("@guild", player.guild));
    						foreach (var row in table) {
    							var member = new GuildMember();
    							member.name = (string)row[0];
    							int rank = Convert.ToInt32((long)row[1]);
    							member.rankIndex = 0 <= rank && rank < GuildMember.ranks.Length ? rank : 0;
    							member.online = Player.onlinePlayers.ContainsKey(member.name);
    							if (member.name == player.name) {
    								member.level = player.level;
    							} else {
    								object scalar = ExecuteScalar("SELECT level FROM characters WHERE `name`=@character", new MySqlParameter("@character", member.name));
    								member.level = scalar != null ? Convert.ToInt32((long)scalar) : 1;
    							}
    							player.guildMembers.Add(member);
    						}
    					}
    
    					return go;
    				} else Debug.LogError("no prefab found for class: " + className);
    			}
    			return null;
    		}
    	
    		// -----------------------------------------------------------------------------------
    		// CharacterSave
    		// adds or overwrites character data in the database
    		// -----------------------------------------------------------------------------------
    
    		public static void CharacterSave(string name, string account, string className, Vector3 position, int level, int hp, int mp, int strength, int intelligence, long exp, long skillExp, long gold, long coins, List<Item> inventory, List<Item> equipment, List<Skill> skills, List<Quest> quests, bool useTransaction = true, bool isNewChar = false) {
    
    			// only use a transaction if not called within SaveMany transaction
    			if (useTransaction) ExecuteNonQuery("START TRANSACTION");
    
    			// Uses INSERT INTO ... ON DUPLICATE KEY UPDATE. Faster than REPLACE INTO.
    			ExecuteNonQuery("INSERT INTO characters VALUES (@name, @account, @class, @x, @y, @z, @level, @hp, @mp, @strength, @intelligence, @exp, @skillExp, @gold, @coins, 0) ON DUPLICATE KEY UPDATE x=@x, y=@y, z=@z, level=@level, hp=@hp, mp=@mp, strength=@strength, intelligence=@intelligence, exp=@exp, skillExp=@skillExp, gold=@gold, coins=@coins, deleted=0",
    							new MySqlParameter("@name", name),
    							new MySqlParameter("@account", account),
    							new MySqlParameter("@class", className),
    							new MySqlParameter("@x", position.x),
    							new MySqlParameter("@y", position.y),
    							new MySqlParameter("@z", position.z),
    							new MySqlParameter("@level", level),
    							new MySqlParameter("@hp", hp),
    							new MySqlParameter("@mp", mp),
    							new MySqlParameter("@strength", strength),
    							new MySqlParameter("@intelligence", intelligence),
    							new MySqlParameter("@exp", exp),
    							new MySqlParameter("@skillExp", skillExp),
    							new MySqlParameter("@gold", gold),
    							new MySqlParameter("@coins", coins));
    
    			// ---------------------------------------------------- Inventory
    			// inventory: remove old entries first, then add all new ones
    			// (we could use UPDATE where slot=... but deleting everything makes
    			//  sure that there are never any ghosts)
    			ExecuteNonQuery("DELETE FROM character_inventory WHERE `character`=@character", new MySqlParameter("@character", name));
    			for (int i = 0; i < inventory.Count; ++i) {
    				var item = inventory[i];
    				ExecuteNonQuery("INSERT INTO character_inventory VALUES (@character, @slot, '@name', @valid, @amount, NULL)",
    								new MySqlParameter("@character", name),
    								new MySqlParameter("@slot", i),
    								new MySqlParameter("@name", item.valid ? item.name : ""),
    								new MySqlParameter("@valid", item.valid),
    								new MySqlParameter("@amount", item.valid ? item.amount : 0));
    			}
    	   
    		   // ---------------------------------------------------- Equipment
    			// equipment: remove old entries first, then add all new ones
    			// (we could use UPDATE where slot=... but deleting everything makes
    			//  sure that there are never any ghosts)
    			ExecuteNonQuery("DELETE FROM character_equipment WHERE `character`=@character", new MySqlParameter("@character", name));
    			for (int i = 0; i < equipment.Count; ++i) {
    				var item = equipment[i];
    				ExecuteNonQuery("INSERT INTO character_equipment VALUES (@character, @slot, @name, @valid, @amount, NULL)",
    								new MySqlParameter("@character", name),
    								new MySqlParameter("@slot", i),
    								new MySqlParameter("@name", item.valid ? item.name : ""),
    								new MySqlParameter("@valid", item.valid),
    								new MySqlParameter("@amount", item.valid ? item.amount : 0));
    			}
    		
    			// ---------------------------------------------------- Skills
    			// skills: remove old entries first, then add all new ones
    			ExecuteNonQuery("DELETE FROM character_skills WHERE `character`=@character", new MySqlParameter("@character", name));
    			foreach (var skill in skills)
    				if (skill.learned)
    					// castTimeEnd and cooldownEnd are based on Time.time, which
    					// will be different when restarting the server, so let's
    					// convert them to the remaining time for easier save & load
    					// note: this does NOT work when trying to save character data shortly
    					//       before closing the editor or game because Time.time is 0 then.
    					ExecuteNonQuery("INSERT INTO character_skills VALUES (@character, @name, @learned, @level, @castTimeEnd, @cooldownEnd, @buffTimeEnd, NULL)",
    									new MySqlParameter("@character", name),
    									new MySqlParameter("@name", skill.name),
    									new MySqlParameter("@learned", skill.learned),
    									new MySqlParameter("@level", skill.level),
    									new MySqlParameter("@castTimeEnd", skill.CastTimeRemaining()),
    									new MySqlParameter("@cooldownEnd", skill.CooldownRemaining()),
    									new MySqlParameter("@buffTimeEnd", skill.BuffTimeRemaining()));
    		
    			// ---------------------------------------------------- Quests
    			// quests: remove old entries first, then add all new ones
    			ExecuteNonQuery("DELETE FROM character_quests WHERE `character`=@character", new MySqlParameter("@character", name));
    			foreach (var quest in quests)
    				ExecuteNonQuery("INSERT INTO character_quests VALUES (@character, @name, @killed, @completed, NULL)",
    								new MySqlParameter("@character", name),
    								new MySqlParameter("@name", quest.name),
    								new MySqlParameter("@killed", quest.killed),
    								new MySqlParameter("@completed", quest.completed));
    
    			if (useTransaction) ExecuteNonQuery("COMMIT");
    		}
    
    		// -----------------------------------------------------------------------------------
    		// CharacterSave
    		// -----------------------------------------------------------------------------------
    		public static void CharacterSave(Player player, bool useTransaction = true, bool isNewChar = false) {
    			CharacterSave(player.name, player.account, player.className, player.transform.position, player.level, player.health, player.mana, player.strength, player.intelligence, player.experience, player.skillExperience, player.gold, player.coins, player.inventory.ToList(), player.equipment.ToList(), player.skills.ToList(), player.quests.ToList(), useTransaction, isNewChar);
    		}
     
    		// -----------------------------------------------------------------------------------
    		// CharacterSaveMany
    		// save multiple characters at once (useful for ultra fast transactions)
    		// -----------------------------------------------------------------------------------
    		public static void CharacterSaveMany(List<Player> players) {
    			if (players.Count > 0) {
    				ExecuteNonQuery("START TRANSACTION"); // transaction for performance
    				foreach (var player in players) CharacterSave(player, false);
    				ExecuteNonQuery("COMMIT");
    			}
    		}
    	
    		// -----------------------------------------------------------------------------------
    		// Guilds
    		// -----------------------------------------------------------------------------------
    		public static void SaveGuild(string guild, List<GuildMember> members) {
    			ExecuteNonQuery("START TRANSACTION"); // transaction for performance
    
    			// guild info
    			ExecuteNonQuery("INSERT OR REPLACE INTO guild_info VALUES (@guild, @notice)",
    							new MySqlParameter("@guild", guild),
    							new MySqlParameter("@notice", ""));
    
    			// members list
    			ExecuteNonQuery("DELETE FROM guild_members WHERE `guild`=@guild", new MySqlParameter("@guild", guild));
    			foreach (var member in members) {
    				ExecuteNonQuery("INSERT INTO guild_members VALUES(@guild, @character, @rank)",
    								new MySqlParameter("@guild", guild),
    								new MySqlParameter("@character", member.name),
    								new MySqlParameter("@rank", member.rankIndex));
    			}        
    		
    			ExecuteNonQuery("COMMIT");
    		}
    	
    		public static bool GuildExists(string guild) {
    			return ((long)ExecuteScalar("SELECT Count(*) FROM guild_info WHERE `name`=@name LIMIT 1", new MySqlParameter("@name", guild))) == 1;
    		}
    	
    		public static void RemoveGuild(string guild) {
    			ExecuteNonQuery("START TRANSACTION"); // transaction for performance
    			ExecuteNonQuery("DELETE FROM guild_info WHERE `name`=@name", new MySqlParameter("@name", guild));
    			ExecuteNonQuery("DELETE FROM guild_members WHERE `guild`=@guild", new MySqlParameter("@guild", guild));
    			ExecuteNonQuery("COMMIT");
    		}
    	
    		// -----------------------------------------------------------------------------------
    		// GrabCharacterOrders
    		// grab new orders from the database and delete them immediately
    		//
    		// note: this requires an orderid if we want someone else to write to
    		// the database too. otherwise deleting would delete all the new ones or
    		// updating would update all the new ones. especially in sqlite.
    		//
    		// note: we could just delete processed orders, but keeping them in the
    		// database is easier for debugging / support.
    		// -----------------------------------------------------------------------------------
    		public static List<long> GrabCharacterOrders(string charName) {
    			var result = new List<long>();
    			var table = ExecuteReader("SELECT orderid, coins FROM character_orders WHERE `character`=@character AND processed=0", new MySqlParameter("@character", charName));
    			foreach (var row in table) {
    				result.Add((long)row[1]);
    				ExecuteNonQuery("UPDATE character_orders SET processed=1 WHERE orderid=@orderid", new MySqlParameter("@orderid", (long)row[0]));
    			}
    			return result;
    		}
    	}
    
     
  4. Import SQL

    Code: Select all

    -- For use in a tool like MySQL Workbench to create the database and tables
    
    -- The UTF-8 character set is recommended (Latin-1 is not compatible) 
    CREATE DATABASE IF NOT EXISTS ummorpg
    	DEFAULT CHARACTER SET utf8
    	DEFAULT COLLATE utf8_general_ci;
    
    -- Uncomment DROP TABLE to delete the table if it exists and needs to be replaced
    
    -- DROP TABLE `characters`;    
    
    CREATE TABLE IF NOT EXISTS `characters` (
    	`name` VARCHAR(16) NOT NULL,
    	`account` VARCHAR(16) NOT NULL,
    	`class` VARCHAR(16) NOT NULL,
    	`x` FLOAT NOT NULL,
    	`y` FLOAT NOT NULL,
    	`z` FLOAT NOT NULL,
    	`level` INT NOT NULL,
    	`hp` INT NOT NULL,
    	`mp` INT NOT NULL,
    	`strength` INT NOT NULL,
    	`intelligence` INT NOT NULL,
    	`exp` BIGINT NOT NULL,
    	`skillExp` BIGINT NOT NULL,
    	`gold` BIGINT NOT NULL,
    	`coins` BIGINT NOT NULL,
    	`deleted` TINYINT UNSIGNED NOT NULL,
        PRIMARY KEY(`name`),
        INDEX(`account`(16))
    ) ENGINE = InnoDB;
        
    -- DROP TABLE `character_inventory`;
    
    CREATE TABLE IF NOT EXISTS `character_inventory` (
    	`character` VARCHAR(16) NOT NULL,
    	`slot` INT NOT NULL,
    	`name` VARCHAR(50) NOT NULL,
    	`valid` TINYINT UNSIGNED NOT NULL,
    	`amount` INT NOT NULL,
        `character_inventoryID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(`character_inventoryID`),
        INDEX(`character`(16))
    ) ENGINE = InnoDB;
        
    -- DROP TABLE character_equipment;
    
    CREATE TABLE IF NOT EXISTS `character_equipment` (
    	`character` VARCHAR(16) NOT NULL,
    	`slot` INT NOT NULL,
    	`name` VARCHAR(50) NOT NULL,
    	`valid` TINYINT UNSIGNED NOT NULL,
    	`amount` INT NOT NULL,
        `character_equipmentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(`character_equipmentID`),
        INDEX(`character`(16))
    ) ENGINE = InnoDB;
    
    -- DROP TABLE character_skills;
    
    CREATE TABLE IF NOT EXISTS `character_skills` (
    	`character` VARCHAR(16) NOT NULL,
    	`name` VARCHAR(50) NOT NULL,
    	`learned` TINYINT UNSIGNED NOT NULL,
    	`level` INT NOT NULL,
    	`castTimeEnd` FLOAT NOT NULL,
    	`cooldownEnd` FLOAT NOT NULL,
    	`buffTimeEnd` FLOAT NOT NULL,
        `character_skillsID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(`character_skillsID`),
        INDEX(`character`(16))
    ) ENGINE = InnoDB;
        
    -- DROP TABLE character_quests;
    
    CREATE TABLE IF NOT EXISTS `character_quests` (
    	`character` VARCHAR(16) NOT NULL,
    	`name` VARCHAR(50) NOT NULL,
    	`killed` INT NOT NULL,
    	`completed` TINYINT UNSIGNED NOT NULL,
        `character_questsID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        PRIMARY KEY(`character_questsID`),
        INDEX(`character`(16))
    ) ENGINE = InnoDB;
    
    -- DROP TABLE character_orders;
    
    CREATE TABLE IF NOT EXISTS `character_orders` (
    	`orderid` BIGINT NOT NULL AUTO_INCREMENT,
        `character` VARCHAR(16) NOT NULL,
        `coins` BIGINT NOT NULL,
        `processed` BIGINT NOT NULL,
        PRIMARY KEY(`orderid`),
        INDEX(`character`(16))
    ) ENGINE = InnoDB;	
    	
    -- DROP TABLE accounts;
    
    CREATE TABLE IF NOT EXISTS `accounts` (
    	`name` VARCHAR(16) NOT NULL,
    	`password` CHAR(40) NOT NULL,
    	`banned` TINYINT UNSIGNED NOT NULL,
    	`warehouse_gold` BIGINT NOT NULL DEFAULT '0',
        PRIMARY KEY(`name`)
    ) ENGINE = InnoDB;
          
    -- DROP TABLE `warehouse_player`;
    
    CREATE TABLE IF NOT EXISTS `warehouse_player` (
    	`account` VARCHAR(16) NOT NULL,
    	`slot` INT NOT NULL,
    	`name` VARCHAR(16) NOT NULL,
    	`valid` TINYINT UNSIGNED NOT NULL,
    	`amount` INT NOT NULL,
        PRIMARY KEY(`account`)
    ) ENGINE = InnoDB;
        
    -- DROP TABLE `guild_info`;
    
    CREATE TABLE IF NOT EXISTS `guild_info` (
    	`name` VARCHAR(16) NOT NULL,
    	`notice` TEXT NOT NULL,
    	PRIMARY KEY(`name`)
    ) ENGINE = InnoDB;
    
    -- DROP TABLE `guild_members`;
    
    CREATE TABLE IF NOT EXISTS `guild_members` (
    	`guild` VARCHAR(16) NOT NULL,
    	`character` VARCHAR(16) NOT NULL,
    	`rank` INT NOT NULL,  
      	PRIMARY KEY(`guild`)
    ) ENGINE = InnoDB;
    
    -- DROP TABLE `warehouse_guild`;
    
    CREATE TABLE IF NOT EXISTS `warehouse_guild` (
    	`guild` VARCHAR(16) NOT NULL,
    	`slot` INT NOT NULL,
    	`name` VARCHAR(16) NOT NULL,
    	`valid` TINYINT UNSIGNED NOT NULL,
    	`amount` INT NOT NULL,
        PRIMARY KEY(`guild`)
    ) ENGINE = InnoDB;  
        
    -- ---------------------------------------------------------------------------------------
    
     
    ** NOTES **
    mySql Modification for uMMORPG 1.68
    Original Code by camta005
    Setup a mySql database wherever you host your server. Setup access rights correctly, so that your server can connect to it. Some server setups also require further configuration, otherwise the script generates errors. Modify your .cnf file if that is the case:
    /etc/my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf
    I used WinSCP to find the file (do this however you want) and edited it with notepad, adding the following:
    [mysqld] character-set-server=utf8 collation-server=utf8_general_ci
    (Look to see if the file already has the [mysqld] heading)
    Note: Here are a few other things that could be noteworthy: Tunnel port 7777 on your local machine. Provide port tunnels to your server for the mySQL database 3306 (TCP) as well as 7777 (both UDP and TCP) for the server.
    Log into mySql and create a new database with character set utf8 and collation utf8_general_ci.

    Import the provided file uMMORPG_mySql.sql into your database.

    From the provided files: Move the MySql.Data.dll into the /Assets/uMMORPG/Plugins folder in your project.

    From the provided files: Copy Constants.cs and edit your database credentials, put the file into your projects /Assets/uMMORPG/Scripts folder.

    From the provided files: Copy Database_mySql.cs and add it to your projects script folder. /Assets/uMMORPG/Scripts

    Locate your original database.cs and change the following two lines:

    public class database {
    static database {
    into

    public class database_mysqlite {
    static database_mysqlite {
    The engine now uses the new database_mysql.cs file instead of the original one. This way you can revert back to the original system if you ever want/need to.

    Note: Any changes applied to your original database.cs file are not represented in the new database_mysql.cs file. You have to apply these changes manually, that is beyond the responsibility of this modification.

    Save your project, re-compile both server and client (this is recommended, especially on Linux). Upload and run.

    Note: You should always re-compile both the server and the client before starting a new test run. Otherwise this can result in unexpected errors that are hard to track down, for example the ReadString/ReadBytes too long bug might relate to this.
    Final Notes:
    The mySql.sql file contains a new row in accounts, as well as two new tables (warehouse_player and warehouse_guild). These are required for my next modification, the updated warehouse script. The code is fully functional without a warehouse!
    The only thing that happens is that your accounts table has one more row and that there are two tables sitting in your database that are not used right now.
    Thats all!

Post Reply

Return to “uMMORPG”

Who is online

Users browsing this forum: No registered users and 1 guest