· 9 years ago · Sep 05, 2017, 12:24 PM
1// Saves Character Data in a SQLite database. We use SQLite for serveral reasons
2//
3// - SQLite is file based and works without having to setup a database server
4// - We can 'remove all ...' or 'modify all ...' easily via SQL queries
5// - A lot of people requested a SQL database and weren't comfortable with XML
6// - We can allow all kinds of character names, even chinese ones without
7// breaking the file system.
8// - We will need MYSQL or similar when using multiple server instances later
9// and upgrading is trivial
10// - XML is easier, but:
11// - we can't easily read 'just the class of a character' etc., but we need it
12// for character selection etc. often
13// - if each account is a folder that contains players, then we can't save
14// additional account info like password, banned, etc. unless we use an
15// additional account.xml file, which overcomplicates everything
16// - there will always be forbidden file names like 'COM', which will cause
17// problems when people try to create accounts or characters with that name
18//
19// About item mall coins:
20// The payment provider's callback should add new orders to the
21// character_orders table. The server will then process them while the player
22// is ingame. Don't try to modify 'coins' in the character table directly.
23//
24// Tools to open sqlite database files:
25// Windows/OSX program: http://sqlitebrowser.org/
26// Firefox extension: https://addons.mozilla.org/de/firefox/addon/sqlite-manager/
27// Webhost: Adminer/PhpLiteAdmin
28//
29// About performance:
30// - It's recommended to only keep the SQlite connection open while it's used.
31// MMO Servers use it all the time, so we keep it open all the time. This also
32// allows us to use transactions easily, and it will make the transition to
33// MYSQL easier.
34// - Transactions are definitely necessary:
35// saving 100 players without transactions takes 3.6s
36// saving 100 players with transactions takes 0.38s
37// - Using tr = conn.BeginTransaction() + tr.Commit() and passing it through all
38// the functions is ultra complicated. We use a BEGIN + END queries instead.
39//
40// Some benchmarks:
41// saving 100 players unoptimized: 4s
42// saving 100 players always open connection + transactions: 3.6s
43// saving 100 players always open connection + transactions + WAL: 3.6s
44// saving 100 players in 1 'using tr = ...' transaction: 380ms
45// saving 100 players in 1 BEGIN/END style transactions: 380ms
46// saving 100 players with XML: 369ms
47//
48// Build notes:
49// - requires Player settings to be set to '.NET' instead of '.NET Subset',
50// otherwise System.Data.dll causes ArgumentException.
51// - requires sqlite3.dll x86 and x64 version for standalone (windows/mac/linux)
52// => found on sqlite.org website
53// - requires libsqlite3.so x86 and armeabi-v7a for android
54// => compiled from sqlite.org amalgamation source with android ndk r9b linux
55using UnityEngine;
56using System;
57using System.IO;
58using System.Linq;
59using System.Collections.Generic;
60using MySql.Data.MySqlClient;
61//using Mono.Data.Sqlite; // copied from Unity/Mono/lib/mono/2.0 to Plugins
62
63public partial class Database {
64 // database path: Application.dataPath is always relative to the project,
65 // but we don't want it inside the Assets folder in the Editor (git etc.),
66 // instead we put it above that.
67 // we also use Path.Combine for platform independent paths
68 // and we need persistentDataPath on android
69#if UNITY_EDITOR
70 static string path = Path.Combine(Directory.GetParent(Application.dataPath).FullName, "Database.sqlite");
71#elif UNITY_ANDROID
72 static string path = Path.Combine(Application.persistentDataPath, "Database.sqlite");
73#elif UNITY_IOS
74 static string path = Path.Combine(Application.persistentDataPath, "Database.sqlite");
75#else
76 static string path = Path.Combine(Application.dataPath, "Database.sqlite");
77#endif
78
79 //static SqliteConnection connection;
80 public static string host, database, user, password;
81 public static bool pooling = true;
82
83 static string connectionString;
84
85 static MySqlConnection connection;
86 static MySqlConnection connection2;
87
88 // constructor /////////////////////////////////////////////////////////////
89 static Database() {
90 //// create database file if it doesn't exist yet
91 //if(!File.Exists(path))
92 // SqliteConnection.CreateFile(path);
93
94 host = "1.1.1.1";
95 database = "ummorpg";
96 user = "abc123";
97 password = "abc123";
98
99 connectionString = "Server=" + host + ";Database=" + database + ";User=" + user + ";Password=" + password + ";Pooling=" + pooling + ";";
100
101 //// open connection
102 //connection = new SqliteConnection("URI=file:" + path);
103 //connection.Open();
104 connection = new MySqlConnection(connectionString);
105 connection.Open();
106 connection2 = new MySqlConnection(connectionString);
107 connection2.Open();
108
109 Debug.Log("MYSQL::: " + connection.State);
110
111
112 // create tables if they don't exist yet or were deleted
113 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS characters (
114 name VARCHAR(255) NOT NULL PRIMARY KEY,
115 account TEXT NOT NULL,
116 class TEXT NOT NULL,
117 x FLOAT NOT NULL,
118 y FLOAT NOT NULL,
119 z FLOAT NOT NULL,
120 level INTEGER NOT NULL,
121 health INTEGER NOT NULL,
122 mana INTEGER NOT NULL,
123 strength INTEGER NOT NULL,
124 intelligence INTEGER NOT NULL,
125 experience INTEGER NOT NULL,
126 skillExperience INTEGER NOT NULL,
127 gold INTEGER NOT NULL,
128 coins INTEGER NOT NULL,
129 online TEXT NOT NULL,
130 deleted INTEGER NOT NULL)");
131
132 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_inventory (
133 `character` TEXT NOT NULL,
134 slot INTEGER NOT NULL,
135 name TEXT NOT NULL,
136 valid INTEGER NOT NULL,
137 amount INTEGER NOT NULL)");
138
139 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_equipment (
140 `character` TEXT NOT NULL,
141 slot INTEGER NOT NULL,
142 name TEXT NOT NULL,
143 valid INTEGER NOT NULL,
144 amount INTEGER NOT NULL)");
145
146 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_skills (
147 `character` TEXT NOT NULL,
148 name TEXT NOT NULL,
149 learned INTEGER NOT NULL,
150 level INTEGER NOT NULL,
151 castTimeEnd FLOAT NOT NULL,
152 cooldownEnd FLOAT NOT NULL,
153 buffTimeEnd FLOAT NOT NULL)");
154
155 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_quests (
156 `character` TEXT NOT NULL,
157 name TEXT NOT NULL,
158 killed INTEGER NOT NULL,
159 completed INTEGER NOT NULL)");
160
161 // INTEGER PRIMARY KEY is auto incremented by sqlite if the
162 // insert call passes NULL for it.
163 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_orders (
164 orderid INTEGER PRIMARY KEY,
165 `character` TEXT NOT NULL,
166 coins INTEGER NOT NULL,
167 processed INTEGER NOT NULL)");
168
169 // guild master is not in guild_info in case we need more than one later
170 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS guild_info (
171 name VARCHAR(255) NOT NULL PRIMARY KEY,
172 notice TEXT NOT NULL)");
173
174 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS guild_members (
175 guild TEXT NOT NULL,
176 `character` TEXT NOT NULL,
177 rank INTEGER NOT NULL)");
178
179 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS accounts (
180 name VARCHAR(255) NOT NULL PRIMARY KEY,
181 password VARCHAR(255) NOT NULL,
182 banned INTEGER NOT NULL)");
183
184 // addon system hooks
185 Utils.InvokeMany(typeof(Database), null, "Initialize_");
186
187 Debug.Log("connected to database");
188 }
189
190 // helper functions ////////////////////////////////////////////////////////
191 // run a query that doesn't return anything
192 public static void ExecuteNonQuery(string sql, params MySqlParameter[] args) {
193 using (var command = new MySqlCommand(sql, connection)) {
194 foreach (var param in args) command.Parameters.Add(param);
195 command.ExecuteNonQuery();
196 }
197 }
198 public static void BeginExecuteNonQuery(string sql, params MySqlParameter[] args)
199 {
200 using (var command = new MySqlCommand(sql, connection2))
201 {
202 foreach (var param in args) command.Parameters.Add(param);
203 command.BeginExecuteNonQuery(); // IASync
204 }
205 }
206
207 // run a query that returns a single value
208 public static object ExecuteScalar(string sql, params MySqlParameter[] args) {
209 using (var command = new MySqlCommand(sql, connection)) {
210 foreach (var param in args) command.Parameters.Add(param);
211 return command.ExecuteScalar();
212 }
213 }
214
215 // run a query that returns several values
216 // note: sqlite has long instead of int, so use Convert.ToInt32 etc.
217 public static List< List<object> > ExecuteReader(string sql, params MySqlParameter[] args) {
218 var result = new List< List<object> >();
219
220 using (var command = new MySqlCommand(sql, connection)) {
221 foreach (var param in args) command.Parameters.Add(param);
222 using (var reader = command.ExecuteReader()) {
223 // the following code causes a SQL EntryPointNotFoundException
224 // because sqlite3_column_origin_name isn't found on OSX and
225 // some other platforms. newer mono versions have a workaround,
226 // but as long as Unity doesn't update, we will have to work
227 // around it manually. see also GetSchemaTable function:
228 // https://github.com/mono/mono/blob/master/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs
229 //
230 //result.Load(reader); (DataTable)
231 while (reader.Read()) {
232 var buffer = new object[reader.FieldCount];
233 reader.GetValues(buffer);
234 result.Add(buffer.ToList());
235 }
236 }
237 }
238
239 return result;
240 }
241
242 // account data ////////////////////////////////////////////////////////////
243 public static bool IsValidAccount(string account, string password) {
244 // this function can be used to verify account credentials in a database
245 // or a content management system.
246 //
247 // for example, we could setup a content management system with a forum,
248 // news, shop etc. and then use a simple HTTP-GET to check the account
249 // info, for example:
250 //
251 // var request = new WWW("example.com/verify.php?id="+id+"&pw="+pw);
252 // while (!request.isDone)
253 // print("loading...");
254 // return request.error == null && request.text == "ok";
255 //
256 // where verify.php is a script like this one:
257 // <?php
258 // // id and pw set with HTTP-GET?
259 // if (isset($_GET['id']) && isset($_GET['pw'])) {
260 // // validate id and pw by using the CMS, for example in Drupal:
261 // if (user_authenticate($_GET['id'], $_GET['pw']))
262 // echo "ok";
263 // else
264 // echo "invalid id or pw";
265 // }
266 // ?>
267 //
268 // or we could check in a MYSQL database:
269 // var dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=notas;uid=root;password=" + dbpwd);
270 // var cmd = dbConn.CreateCommand();
271 // cmd.CommandText = "SELECT id FROM accounts WHERE id='" + account + "' AND pw='" + password + "'";
272 // dbConn.Open();
273 // var reader = cmd.ExecuteReader();
274 // if (reader.Read())
275 // return reader.ToString() == account;
276 // return false;
277 //
278 // as usual, we will use the simplest solution possible:
279 // create account if not exists, compare password otherwise.
280 // no CMS communication necessary and good enough for an Indie MMORPG.
281
282 // not empty?
283 if (!Utils.IsNullOrWhiteSpace(account) && !Utils.IsNullOrWhiteSpace(password)) {
284 var table = ExecuteReader("SELECT password, banned FROM accounts WHERE name=@name", new MySqlParameter("@name", account));
285 if (table.Count == 1) {
286 // account exists. check password and ban status.
287 var row = table[0];
288 return (string)row[0] == password && (int)row[1] == 0;
289 } else {
290 // account doesn't exist. create it.
291 ExecuteNonQuery("INSERT INTO accounts VALUES (@name, @password, 0)", new MySqlParameter("@name", account), new MySqlParameter("@password", password));
292 return true;
293 }
294 }
295 return false;
296 }
297
298 // character data //////////////////////////////////////////////////////////
299 public static bool CharacterExists(string characterName) {
300 // checks deleted ones too so we don't end up with duplicates if we un-
301 // delete one
302 return ((long)ExecuteScalar("SELECT Count(*) FROM characters WHERE name=@name", new MySqlParameter("@name", characterName))) == 1;
303 }
304
305 public static void CharacterDelete(string characterName) {
306 // soft delete the character so it can always be restored later
307 ExecuteNonQuery("UPDATE characters SET deleted=1 WHERE name=@character", new MySqlParameter("@character", characterName));
308 }
309
310 // returns a dict of<character name, character class=prefab name>
311 // we really need the prefab name too, so that client character selection
312 // can read all kinds of properties like icons, stats, 3D models and not
313 // just the character name
314 public static Dictionary<string, string> CharactersForAccount(string account) {
315 var result = new Dictionary<string, string>();
316
317 var table = ExecuteReader("SELECT name, class FROM characters WHERE account=@account AND deleted=0", new MySqlParameter("@account", account));
318 foreach (var row in table)
319 result[(string)row[0]] = (string)row[1];
320
321 return result;
322 }
323
324 public static GameObject CharacterLoad(string characterName, List<Player> prefabs) {
325 var table = ExecuteReader("SELECT * FROM characters WHERE name=@name AND deleted=0", new MySqlParameter("@name", characterName));
326 if (table.Count == 1) {
327 var mainrow = table[0];
328
329 // instantiate based on the class name
330 string className = (string)mainrow[2];
331 var prefab = prefabs.Find(p => p.name == className);
332 if (prefab != null) {
333 var go = GameObject.Instantiate(prefab.gameObject);
334 var player = go.GetComponent<Player>();
335
336 player.name = (string)mainrow[0];
337 player.account = (string)mainrow[1];
338 player.className = (string)mainrow[2];
339 float x = (float)mainrow[3];
340 float y = (float)mainrow[4];
341 float z = (float)mainrow[5];
342 // NEVER use player.transform.position = ...; because it
343 // places the player at weird positions. for example,
344 // (200, 0, -200) becomes (76, 0, -76)
345 // using agent.Warp is also recommended in the Unity docs.
346 player.agent.Warp(new Vector3(x, y, z));
347 player.level = Convert.ToInt32((int)mainrow[6]);
348 player.health = Convert.ToInt32((int)mainrow[7]);
349 player.mana = Convert.ToInt32((int)mainrow[8]);
350 player.strength = Convert.ToInt32((int)mainrow[9]);
351 player.intelligence = Convert.ToInt32((int)mainrow[10]);
352 player.experience = (int)mainrow[11];
353 player.skillExperience = (int)mainrow[12];
354 player.gold = (int)mainrow[13];
355 player.coins = (int)mainrow[14];
356
357 // load inventory based on inventorySize (creates slots if none)
358 for (int i = 0; i < player.inventorySize; ++i) {
359 // any saved data for that slot?
360 table = ExecuteReader("SELECT name, valid, amount FROM character_inventory WHERE `character`=@character AND slot=@slot;", new MySqlParameter("@character", player.name), new MySqlParameter("@slot", i));
361 if (table.Count == 1) {
362 var row = table[0];
363 var item = new Item();
364 item.name = (string)row[0];
365 item.valid = ((int)row[1]) != 0; // sqlite has no bool
366 item.amount = Convert.ToInt32((int)row[2]);
367
368 // add item if template still exists, otherwise empty
369 player.inventory.Add(item.valid && item.TemplateExists() ? item : new Item());
370 } else {
371 // add empty slot or default item if any
372 player.inventory.Add(i < player.defaultItems.Length ? new Item(player.defaultItems[i]) : new Item());
373 }
374 }
375
376 // load equipment based on equipmentTypes (creates slots if none)
377 for (int i = 0; i < player.equipmentTypes.Length; ++i) {
378 // any saved data for that slot?
379 table = ExecuteReader("SELECT name, valid, amount FROM character_equipment WHERE `character`=@character AND slot=@slot", new MySqlParameter("@character", player.name), new MySqlParameter("@slot", i));
380 if (table.Count == 1) {
381 var row = table[0];
382 var item = new Item();
383 item.name = (string)row[0];
384 item.valid = ((int)row[1]) != 0; // sqlite has no bool
385 item.amount = Convert.ToInt32((int)row[2]);
386
387 // add item if template still exists, otherwise empty
388 player.equipment.Add(item.valid && item.TemplateExists() ? item : new Item());
389 } else {
390 // add empty slot or default item if any
391 string equipmentType = player.equipmentTypes[i];
392 int index = player.defaultEquipment.FindIndex(equip => player.CanEquip(equipmentType, new Item(equip)));
393 player.equipment.Add(index != -1 ? new Item(player.defaultEquipment[index]) : new Item());
394 }
395 }
396
397 // load skills based on skill templates (the others don't matter)
398 foreach (var template in player.skillTemplates) {
399 // create skill based on template
400 var skill = new Skill(template);
401
402 // load saved data if any
403 table = ExecuteReader("SELECT learned, level, castTimeEnd, cooldownEnd, buffTimeEnd FROM character_skills WHERE `character`=@character AND name=@name", new MySqlParameter("@character", characterName), new MySqlParameter("@name", template.name));
404 foreach (var row in table) {
405 skill.learned = ((int)row[0]) != 0; // sqlite has no bool
406 // make sure that 1 <= level <= maxlevel (in case we removed a skill
407 // level etc)
408 skill.level = Mathf.Clamp(Convert.ToInt32((int)row[1]), 1, skill.maxLevel);
409 // castTimeEnd and cooldownEnd are based on Time.time, which
410 // will be different when restarting a server, hence why we
411 // saved them as just the remaining times. so let's convert them
412 // back again.
413 skill.castTimeEnd = (float)row[2] + Time.time;
414 skill.cooldownEnd = (float)row[3] + Time.time;
415 skill.buffTimeEnd = (float)row[4] + Time.time;
416 }
417
418 player.skills.Add(skill);
419 }
420
421 // load quests
422 table = ExecuteReader("SELECT name, killed, completed FROM character_quests WHERE `character`=@character", new MySqlParameter("@character", player.name));
423 foreach (var row in table) {
424 var quest = new Quest();
425 quest.name = (string)row[0];
426 quest.killed = Convert.ToInt32((int)row[1]);
427 quest.completed = ((int)row[2]) != 0; // sqlite has no bool
428 player.quests.Add(quest.TemplateExists() ? quest : new Quest());
429 }
430
431 // in a guild?
432 string guild = (string)ExecuteScalar("SELECT guild FROM guild_members WHERE `character`=@character", new MySqlParameter("@character", player.name));
433 if (guild != null) {
434 // load guild info
435 player.guild = guild;
436 table = ExecuteReader("SELECT notice FROM guild_info WHERE name=@guild", new MySqlParameter("@guild", guild));
437 if (table.Count == 1) {
438 var row = table[0];
439 player.guildNotice = (string)row[0];
440 }
441
442 // load members list
443 table = ExecuteReader("SELECT character, rank FROM guild_members WHERE guild=@guild", new MySqlParameter("@guild", player.guild));
444 foreach (var row in table) {
445 var member = new GuildMember();
446 member.name = (string)row[0];
447 int rank = Convert.ToInt32((int)row[1]);
448 member.rankIndex = 0 <= rank && rank < GuildMember.ranks.Length ? rank : 0;
449 member.online = Player.onlinePlayers.ContainsKey(member.name);
450 if (member.name == player.name) {
451 member.level = player.level;
452 } else {
453 object scalar = ExecuteScalar("SELECT level FROM characters WHERE name=@character", new MySqlParameter("@character", member.name));
454 member.level = scalar != null ? Convert.ToInt32((long)scalar) : 1;
455 }
456 player.guildMembers.Add(member);
457 }
458 }
459
460 // addon system hooks
461 Utils.InvokeMany(typeof(Database), null, "CharacterLoad_", player);
462
463 return go;
464 } else Debug.LogError("no prefab found for class: " + className);
465 }
466 return null;
467 }
468
469 // adds or overwrites character data in the database
470 public static void BeginCharacterSave(Player player, bool online, bool useTransaction = true)
471 {
472 DateTime start = DateTime.Now;
473 // only use a transaction if not called within SaveMany transaction
474 //if (useTransaction) ExecuteNonQuery("BEGIN");
475
476 // online status:
477 // '' if offline (if just logging out etc.)
478 // current time otherwise
479 // -> this way it's fault tolerant because external applications can
480 // check if online != '' and if time difference < saveinterval
481 // -> online time is useful for network zones (server<->server online
482 // checks), external websites which render dynamic maps, etc.
483 // -> it uses the ISO 8601 standard format
484 string onlineString = online ? DateTime.UtcNow.ToString("s") : "";
485
486 BeginExecuteNonQuery("REPLACE INTO characters (name, account, class, x, y, z, level, health, mana, strength, intelligence, experience, skillExperience, gold, coins, online, deleted) " +
487 "VALUES (@name, @account, @class, @x, @y, @z, @level, @health, @mana, @strength, @intelligence, @experience, @skillExperience, @gold, @coins, @online, 0)",
488 new MySqlParameter("@name", player.name),
489 new MySqlParameter("@account", player.account),
490 new MySqlParameter("@class", player.className),
491 new MySqlParameter("@x", player.transform.position.x),
492 new MySqlParameter("@y", player.transform.position.y),
493 new MySqlParameter("@z", player.transform.position.z),
494 new MySqlParameter("@level", player.level),
495 new MySqlParameter("@health", player.health),
496 new MySqlParameter("@mana", player.mana),
497 new MySqlParameter("@strength", player.strength),
498 new MySqlParameter("@intelligence", player.intelligence),
499 new MySqlParameter("@experience", player.experience),
500 new MySqlParameter("@skillExperience", player.skillExperience),
501 new MySqlParameter("@gold", player.gold),
502 new MySqlParameter("@coins", player.coins),
503 new MySqlParameter("@online", onlineString));
504
505 // inventory: remove old entries first, then add all new ones
506 // (we could use UPDATE where slot=... but deleting everything makes
507 // sure that there are never any ghosts)
508 BeginExecuteNonQuery("DELETE FROM character_inventory WHERE `character`=@character", new MySqlParameter("@character", player.name));
509 for (int i = 0; i < player.inventory.Count; ++i)
510 {
511 var item = player.inventory[i];
512 BeginExecuteNonQuery("INSERT INTO character_inventory VALUES (@character, @slot, @name, @valid, @amount)",
513 new MySqlParameter("@character", player.name),
514 new MySqlParameter("@slot", i),
515 new MySqlParameter("@name", item.valid ? item.name : ""),
516 new MySqlParameter("@valid", Convert.ToInt32(item.valid)),
517 new MySqlParameter("@amount", item.valid ? item.amount : 0));
518 }
519
520 // equipment: remove old entries first, then add all new ones
521 // (we could use UPDATE where slot=... but deleting everything makes
522 // sure that there are never any ghosts)
523 BeginExecuteNonQuery("DELETE FROM character_equipment WHERE `character`=@character", new MySqlParameter("@character", player.name));
524 for (int i = 0; i < player.equipment.Count; ++i)
525 {
526 var item = player.equipment[i];
527 BeginExecuteNonQuery("INSERT INTO character_equipment VALUES (@character, @slot, @name, @valid, @amount)",
528 new MySqlParameter("@character", player.name),
529 new MySqlParameter("@slot", i),
530 new MySqlParameter("@name", item.valid ? item.name : ""),
531 new MySqlParameter("@valid", Convert.ToInt32(item.valid)),
532 new MySqlParameter("@amount", item.valid ? item.amount : 0));
533 }
534
535 // skills: remove old entries first, then add all new ones
536 BeginExecuteNonQuery("DELETE FROM character_skills WHERE `character`=@character", new MySqlParameter("@character", player.name));
537 foreach (var skill in player.skills)
538 if (skill.learned)
539 // castTimeEnd and cooldownEnd are based on Time.time, which
540 // will be different when restarting the server, so let's
541 // convert them to the remaining time for easier save & load
542 // note: this does NOT work when trying to save character data shortly
543 // before closing the editor or game because Time.time is 0 then.
544 BeginExecuteNonQuery("INSERT INTO character_skills VALUES (@character, @name, @learned, @level, @castTimeEnd, @cooldownEnd, @buffTimeEnd)",
545 new MySqlParameter("@character", player.name),
546 new MySqlParameter("@name", skill.name),
547 new MySqlParameter("@learned", Convert.ToInt32(skill.learned)),
548 new MySqlParameter("@level", skill.level),
549 new MySqlParameter("@castTimeEnd", skill.CastTimeRemaining()),
550 new MySqlParameter("@cooldownEnd", skill.CooldownRemaining()),
551 new MySqlParameter("@buffTimeEnd", skill.BuffTimeRemaining()));
552
553 // quests: remove old entries first, then add all new ones
554 BeginExecuteNonQuery("DELETE FROM character_quests WHERE `character`=@character", new MySqlParameter("@character", player.name));
555 foreach (var quest in player.quests)
556 BeginExecuteNonQuery("INSERT INTO character_quests VALUES (@character, @name, @killed, @completed)",
557 new MySqlParameter("@character", player.name),
558 new MySqlParameter("@name", quest.name),
559 new MySqlParameter("@killed", quest.killed),
560 new MySqlParameter("@completed", Convert.ToInt32(quest.completed)));
561
562 // addon system hooks
563 Utils.InvokeMany(typeof(Database), null, "CharacterSave_", player);
564
565 //if (useTransaction) ExecuteNonQuery("END");
566 DateTime end = DateTime.Now;
567 TimeSpan span = start - end;
568 Debug.Log("CharacterSave " + player.name + " : " + span.TotalSeconds);
569 }
570
571 // adds or overwrites character data in the database
572 public static void CharacterSave(Player player, bool online, bool useTransaction = true) {
573 DateTime start = DateTime.Now;
574 // only use a transaction if not called within SaveMany transaction
575 //if (useTransaction) ExecuteNonQuery("BEGIN");
576
577 // online status:
578 // '' if offline (if just logging out etc.)
579 // current time otherwise
580 // -> this way it's fault tolerant because external applications can
581 // check if online != '' and if time difference < saveinterval
582 // -> online time is useful for network zones (server<->server online
583 // checks), external websites which render dynamic maps, etc.
584 // -> it uses the ISO 8601 standard format
585 string onlineString = online ? DateTime.UtcNow.ToString("s") : "";
586
587 ExecuteNonQuery("REPLACE INTO characters (name, account, class, x, y, z, level, health, mana, strength, intelligence, experience, skillExperience, gold, coins, online, deleted) " +
588 "VALUES (@name, @account, @class, @x, @y, @z, @level, @health, @mana, @strength, @intelligence, @experience, @skillExperience, @gold, @coins, @online, 0)",
589 new MySqlParameter("@name", player.name),
590 new MySqlParameter("@account", player.account),
591 new MySqlParameter("@class", player.className),
592 new MySqlParameter("@x", player.transform.position.x),
593 new MySqlParameter("@y", player.transform.position.y),
594 new MySqlParameter("@z", player.transform.position.z),
595 new MySqlParameter("@level", player.level),
596 new MySqlParameter("@health", player.health),
597 new MySqlParameter("@mana", player.mana),
598 new MySqlParameter("@strength", player.strength),
599 new MySqlParameter("@intelligence", player.intelligence),
600 new MySqlParameter("@experience", player.experience),
601 new MySqlParameter("@skillExperience", player.skillExperience),
602 new MySqlParameter("@gold", player.gold),
603 new MySqlParameter("@coins", player.coins),
604 new MySqlParameter("@online", onlineString));
605
606 // inventory: remove old entries first, then add all new ones
607 // (we could use UPDATE where slot=... but deleting everything makes
608 // sure that there are never any ghosts)
609 ExecuteNonQuery("DELETE FROM character_inventory WHERE `character`=@character", new MySqlParameter("@character", player.name));
610 for (int i = 0; i < player.inventory.Count; ++i) {
611 var item = player.inventory[i];
612 ExecuteNonQuery("INSERT INTO character_inventory VALUES (@character, @slot, @name, @valid, @amount)",
613 new MySqlParameter("@character", player.name),
614 new MySqlParameter("@slot", i),
615 new MySqlParameter("@name", item.valid ? item.name : ""),
616 new MySqlParameter("@valid", Convert.ToInt32(item.valid)),
617 new MySqlParameter("@amount", item.valid ? item.amount : 0));
618 }
619
620 // equipment: remove old entries first, then add all new ones
621 // (we could use UPDATE where slot=... but deleting everything makes
622 // sure that there are never any ghosts)
623 ExecuteNonQuery("DELETE FROM character_equipment WHERE `character`=@character", new MySqlParameter("@character", player.name));
624 for (int i = 0; i < player.equipment.Count; ++i) {
625 var item = player.equipment[i];
626 ExecuteNonQuery("INSERT INTO character_equipment VALUES (@character, @slot, @name, @valid, @amount)",
627 new MySqlParameter("@character", player.name),
628 new MySqlParameter("@slot", i),
629 new MySqlParameter("@name", item.valid ? item.name : ""),
630 new MySqlParameter("@valid", Convert.ToInt32(item.valid)),
631 new MySqlParameter("@amount", item.valid ? item.amount : 0));
632 }
633
634 // skills: remove old entries first, then add all new ones
635 ExecuteNonQuery("DELETE FROM character_skills WHERE `character`=@character", new MySqlParameter("@character", player.name));
636 foreach (var skill in player.skills)
637 if (skill.learned)
638 // castTimeEnd and cooldownEnd are based on Time.time, which
639 // will be different when restarting the server, so let's
640 // convert them to the remaining time for easier save & load
641 // note: this does NOT work when trying to save character data shortly
642 // before closing the editor or game because Time.time is 0 then.
643 ExecuteNonQuery("INSERT INTO character_skills VALUES (@character, @name, @learned, @level, @castTimeEnd, @cooldownEnd, @buffTimeEnd)",
644 new MySqlParameter("@character", player.name),
645 new MySqlParameter("@name", skill.name),
646 new MySqlParameter("@learned", Convert.ToInt32(skill.learned)),
647 new MySqlParameter("@level", skill.level),
648 new MySqlParameter("@castTimeEnd", skill.CastTimeRemaining()),
649 new MySqlParameter("@cooldownEnd", skill.CooldownRemaining()),
650 new MySqlParameter("@buffTimeEnd", skill.BuffTimeRemaining()));
651
652 // quests: remove old entries first, then add all new ones
653 ExecuteNonQuery("DELETE FROM character_quests WHERE `character`=@character", new MySqlParameter("@character", player.name));
654 foreach (var quest in player.quests)
655 ExecuteNonQuery("INSERT INTO character_quests VALUES (@character, @name, @killed, @completed)",
656 new MySqlParameter("@character", player.name),
657 new MySqlParameter("@name", quest.name),
658 new MySqlParameter("@killed", quest.killed),
659 new MySqlParameter("@completed", Convert.ToInt32(quest.completed)));
660
661 // addon system hooks
662 Utils.InvokeMany(typeof(Database), null, "CharacterSave_", player);
663
664 //if (useTransaction) ExecuteNonQuery("END");
665 DateTime end = DateTime.Now;
666 TimeSpan span = start - end;
667 Debug.Log("CharacterSave "+ player.name + " : " + span.TotalSeconds);
668 }
669
670 // save multiple characters at once (useful for ultra fast transactions)
671 public static void CharacterSaveMany(List<Player> players, bool online = true) {
672 //ExecuteNonQuery("BEGIN"); // transaction for performance
673 foreach (var player in players) BeginCharacterSave(player, online, false);
674 //ExecuteNonQuery("END");
675 }
676
677 // guilds //////////////////////////////////////////////////////////////////
678 public static void SaveGuild(string guild, string notice, List<GuildMember> members) {
679 //ExecuteNonQuery("BEGIN"); // transaction for performance
680
681 // guild info
682 ExecuteNonQuery("INSERT OR REPLACE INTO guild_info VALUES (@guild, @notice)",
683 new MySqlParameter("@guild", guild),
684 new MySqlParameter("@notice", notice));
685
686 // members list
687 ExecuteNonQuery("DELETE FROM guild_members WHERE guild=@guild", new MySqlParameter("@guild", guild));
688 foreach (var member in members) {
689 ExecuteNonQuery("INSERT INTO guild_members VALUES(@guild, @character, @rank)",
690 new MySqlParameter("@guild", guild),
691 new MySqlParameter("character", member.name),
692 new MySqlParameter("rank", member.rankIndex));
693 }
694
695 //ExecuteNonQuery("END");
696 }
697
698 public static bool GuildExists(string guild) {
699 return ((long)ExecuteScalar("SELECT Count(*) FROM guild_info WHERE name=@name", new MySqlParameter("@name", guild))) == 1;
700 }
701
702 public static void RemoveGuild(string guild) {
703 //ExecuteNonQuery("BEGIN"); // transaction for performance
704 ExecuteNonQuery("DELETE FROM guild_info WHERE name=@name", new MySqlParameter("@name", guild));
705 ExecuteNonQuery("DELETE FROM guild_members WHERE guild=@guild", new MySqlParameter("@guild", guild));
706 //ExecuteNonQuery("END");
707 }
708
709 // item mall ///////////////////////////////////////////////////////////////
710 public static List<long> GrabCharacterOrders(string characterName) {
711 // grab new orders from the database and delete them immediately
712 //
713 // note: this requires an orderid if we want someone else to write to
714 // the database too. otherwise deleting would delete all the new ones or
715 // updating would update all the new ones. especially in sqlite.
716 //
717 // note: we could just delete processed orders, but keeping them in the
718 // database is easier for debugging / support.
719 var result = new List<long>();
720 var table = ExecuteReader("SELECT orderid, coins FROM character_orders WHERE `character`=@character AND processed=0", new MySqlParameter("@character", characterName));
721 foreach (var row in table) {
722 result.Add((long)row[1]);
723 ExecuteNonQuery("UPDATE character_orders SET processed=1 WHERE orderid=@orderid", new MySqlParameter("@orderid", (long)row[0]));
724 }
725 return result;
726 }
727}