· 8 years ago · Jul 29, 2018, 07:26 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 UnityEngine.Networking;
57using System;
58using System.IO;
59using System.Linq;
60using System.Collections.Generic;
61using Mono.Data.Sqlite; // copied from Unity/Mono/lib/mono/2.0 to Plugins
62
63public partial class Database
64{
65 // database path: Application.dataPath is always relative to the project,
66 // but we don't want it inside the Assets folder in the Editor (git etc.),
67 // instead we put it above that.
68 // we also use Path.Combine for platform independent paths
69 // and we need persistentDataPath on android
70#if UNITY_EDITOR
71 static string path = Path.Combine(Directory.GetParent(Application.dataPath).FullName, "Database.sqlite");
72#elif UNITY_ANDROID
73 static string path = Path.Combine(Application.persistentDataPath, "Database.sqlite");
74#elif UNITY_IOS
75 static string path = Path.Combine(Application.persistentDataPath, "Database.sqlite");
76#else
77 static string path = Path.Combine(Application.dataPath, "Database.sqlite");
78#endif
79
80 static SqliteConnection connection;
81
82 // constructor /////////////////////////////////////////////////////////////
83 static Database()
84 {
85 // create database file if it doesn't exist yet
86 if(!File.Exists(path))
87 SqliteConnection.CreateFile(path);
88
89 // open connection
90 connection = new SqliteConnection("URI=file:" + path);
91 connection.Open();
92
93 // create tables if they don't exist yet or were deleted
94 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
95 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS characters (
96 name TEXT NOT NULL PRIMARY KEY,
97 account TEXT NOT NULL,
98 class TEXT NOT NULL,
99 x REAL NOT NULL,
100 y REAL NOT NULL,
101 z REAL NOT NULL,
102 level INTEGER NOT NULL,
103 health INTEGER NOT NULL,
104 mana INTEGER NOT NULL,
105 strength INTEGER NOT NULL,
106 intelligence INTEGER NOT NULL,
107 experience INTEGER NOT NULL,
108 skillExperience INTEGER NOT NULL,
109 gold INTEGER NOT NULL,
110 coins INTEGER NOT NULL,
111 online TEXT NOT NULL,
112 deleted INTEGER NOT NULL)");
113
114 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
115 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_inventory (
116 character TEXT NOT NULL,
117 slot INTEGER NOT NULL,
118 name TEXT NOT NULL,
119 amount INTEGER NOT NULL,
120 petHealth INTEGER NOT NULL,
121 petLevel INTEGER NOT NULL,
122 petExperience INTEGER NOT NULL,
123 petAttackIV INTEGER NOT NULL,
124 petAttackEV INTEGER NOT NULL,
125 petSpecialAttackIV INTEGER NOT NULL,
126 petSpecialAttackEV INTEGER NOT NULL,
127 petDefenseIV INTEGER NOT NULL,
128 petDefenseEV INTEGER NOT NULL,
129 petSpecialDefenseIV INTEGER NOT NULL,
130 petSpecialDefenseEV INTEGER NOT NULL,
131 petHealthIV INTEGER NOT NULL,
132 petHealthEV INTEGER NOT NULL,
133 petHealthRegenIV INTEGER NOT NULL,
134 petHealthRegenEV INTEGER NOT NULL,
135 petStaminaIV INTEGER NOT NULL,
136 petStaminaEV INTEGER NOT NULL,
137 petStaminaRegenIV INTEGER NOT NULL,
138 petStaminaRegenEV INTEGER NOT NULL,
139 PRIMARY KEY(character, slot))");
140
141 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
142 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_equipment (
143 character TEXT NOT NULL,
144 slot INTEGER NOT NULL,
145 name TEXT NOT NULL,
146 amount INTEGER NOT NULL,
147 PRIMARY KEY(character, slot))");
148
149 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
150 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_skills (
151 character TEXT NOT NULL,
152 name TEXT NOT NULL,
153 level INTEGER NOT NULL,
154 castTimeEnd REAL NOT NULL,
155 cooldownEnd REAL NOT NULL,
156 PRIMARY KEY(character, name))");
157
158 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
159 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_buffs (
160 character TEXT NOT NULL,
161 name TEXT NOT NULL,
162 level INTEGER NOT NULL,
163 buffTimeEnd REAL NOT NULL,
164 PRIMARY KEY(character, name))");
165
166 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
167 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_quests (
168 character TEXT NOT NULL,
169 name TEXT NOT NULL,
170 killed INTEGER NOT NULL,
171 completed INTEGER NOT NULL,
172 PRIMARY KEY(character, name))");
173
174 // INTEGER PRIMARY KEY is auto incremented by sqlite if the
175 // insert call passes NULL for it.
176 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
177 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_orders (
178 orderid INTEGER PRIMARY KEY,
179 character TEXT NOT NULL,
180 coins INTEGER NOT NULL,
181 processed INTEGER NOT NULL)");
182
183 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
184 // guild members are saved in a separate table because instead of in a
185 // characters.guild field because:
186 // * guilds need to be resaved independently, not just in CharacterSave
187 // * kicked members' guilds are cleared automatically because we drop
188 // and then insert all members each time. otherwise we'd have to
189 // update the kicked member's guild field manually each time
190 // * it's easier to remove / modify the guild feature if it's not hard-
191 // coded into the characters table
192 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS character_guild (
193 character TEXT NOT NULL PRIMARY KEY,
194 guild TEXT NOT NULL,
195 rank INTEGER NOT NULL)");
196
197 // add index on guild to avoid full scans when loading guild members
198 ExecuteNonQuery("CREATE INDEX IF NOT EXISTS character_guild_by_guild ON character_guild (guild)");
199
200 // guild master is not in guild_info in case we need more than one later
201 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
202 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS guild_info (
203 name TEXT NOT NULL PRIMARY KEY,
204 notice TEXT NOT NULL)");
205
206 // [PRIMARY KEY is important for performance: O(log n) instead of O(n)]
207 ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS accounts (
208 name TEXT NOT NULL PRIMARY KEY,
209 password TEXT NOT NULL,
210 banned INTEGER NOT NULL)");
211
212 // addon system hooks
213 Utils.InvokeMany(typeof(Database), null, "Initialize_");
214
215 Debug.Log("connected to database");
216 }
217
218 // helper functions ////////////////////////////////////////////////////////
219 // run a query that doesn't return anything
220 public static void ExecuteNonQuery(string sql, params SqliteParameter[] args)
221 {
222 using (SqliteCommand command = new SqliteCommand(sql, connection))
223 {
224 foreach (SqliteParameter param in args)
225 command.Parameters.Add(param);
226 command.ExecuteNonQuery();
227 }
228 }
229
230 // run a query that returns a single value
231 public static object ExecuteScalar(string sql, params SqliteParameter[] args)
232 {
233 using (SqliteCommand command = new SqliteCommand(sql, connection))
234 {
235 foreach (SqliteParameter param in args)
236 command.Parameters.Add(param);
237 return command.ExecuteScalar();
238 }
239 }
240
241 // run a query that returns several values
242 // note: sqlite has long instead of int, so use Convert.ToInt32 etc.
243 public static List< List<object> > ExecuteReader(string sql, params SqliteParameter[] args)
244 {
245 List< List<object> > result = new List< List<object> >();
246
247 using (SqliteCommand command = new SqliteCommand(sql, connection))
248 {
249 foreach (SqliteParameter param in args)
250 command.Parameters.Add(param);
251
252 using (SqliteDataReader reader = command.ExecuteReader())
253 {
254 // the following code causes a SQL EntryPointNotFoundException
255 // because sqlite3_column_origin_name isn't found on OSX and
256 // some other platforms. newer mono versions have a workaround,
257 // but as long as Unity doesn't update, we will have to work
258 // around it manually. see also GetSchemaTable function:
259 // https://github.com/mono/mono/blob/master/mcs/class/Mono.Data.Sqlite/Mono.Data.Sqlite_2.0/SQLiteDataReader.cs
260 //
261 //result.Load(reader); (DataTable)
262 while (reader.Read())
263 {
264 object[] buffer = new object[reader.FieldCount];
265 reader.GetValues(buffer);
266 result.Add(buffer.ToList());
267 }
268 }
269 }
270
271 return result;
272 }
273
274 // account data ////////////////////////////////////////////////////////////
275 public static bool IsValidAccount(string account, string password)
276 {
277 // this function can be used to verify account credentials in a database
278 // or a content management system.
279 //
280 // for example, we could setup a content management system with a forum,
281 // news, shop etc. and then use a simple HTTP-GET to check the account
282 // info, for example:
283 //
284 // var request = new WWW("example.com/verify.php?id="+id+"&pw="+pw);
285 // while (!request.isDone)
286 // print("loading...");
287 // return request.error == null && request.text == "ok";
288 //
289 // where verify.php is a script like this one:
290 // <?php
291 // // id and pw set with HTTP-GET?
292 // if (isset($_GET['id']) && isset($_GET['pw'])) {
293 // // validate id and pw by using the CMS, for example in Drupal:
294 // if (user_authenticate($_GET['id'], $_GET['pw']))
295 // echo "ok";
296 // else
297 // echo "invalid id or pw";
298 // }
299 // ?>
300 //
301 // or we could check in a MYSQL database:
302 // var dbConn = new MySql.Data.MySqlClient.MySqlConnection("Persist Security Info=False;server=localhost;database=notas;uid=root;password=" + dbpwd);
303 // var cmd = dbConn.CreateCommand();
304 // cmd.CommandText = "SELECT id FROM accounts WHERE id='" + account + "' AND pw='" + password + "'";
305 // dbConn.Open();
306 // var reader = cmd.ExecuteReader();
307 // if (reader.Read())
308 // return reader.ToString() == account;
309 // return false;
310 //
311 // as usual, we will use the simplest solution possible:
312 // create account if not exists, compare password otherwise.
313 // no CMS communication necessary and good enough for an Indie MMORPG.
314
315 // not empty?
316 if (!Utils.IsNullOrWhiteSpace(account) && !Utils.IsNullOrWhiteSpace(password))
317 {
318 List< List<object> > table = ExecuteReader("SELECT password, banned FROM accounts WHERE name=@name", new SqliteParameter("@name", account));
319 if (table.Count == 1)
320 {
321 // account exists. check password and ban status.
322 List<object> row = table[0];
323 return (string)row[0] == password && (long)row[1] == 0;
324 }
325 else
326 {
327 // account doesn't exist. create it.
328 ExecuteNonQuery("INSERT INTO accounts VALUES (@name, @password, 0)", new SqliteParameter("@name", account), new SqliteParameter("@password", password));
329 return true;
330 }
331 }
332 return false;
333 }
334
335 // character data //////////////////////////////////////////////////////////
336 public static bool CharacterExists(string characterName)
337 {
338 // checks deleted ones too so we don't end up with duplicates if we un-
339 // delete one
340 return ((long)ExecuteScalar("SELECT Count(*) FROM characters WHERE name=@name", new SqliteParameter("@name", characterName))) == 1;
341 }
342
343 public static void CharacterDelete(string characterName)
344 {
345 // soft delete the character so it can always be restored later
346 ExecuteNonQuery("UPDATE characters SET deleted=1 WHERE name=@character", new SqliteParameter("@character", characterName));
347 }
348
349 // returns the list of character names for that account
350 // => all the other values can be read with CharacterLoad!
351 public static List<string> CharactersForAccount(string account)
352 {
353 List<string> result = new List<string>();
354 List< List<object> > table = ExecuteReader("SELECT name FROM characters WHERE account=@account AND deleted=0", new SqliteParameter("@account", account));
355 foreach (List<object> row in table)
356 result.Add((string)row[0]);
357 return result;
358 }
359
360 static void LoadInventory(Player player)
361 {
362 // fill all slots first
363 for (int i = 0; i < player.inventorySize; ++i)
364 player.inventory.Add(new ItemSlot());
365
366 // then load valid items and put into their slots
367 // (one big query is A LOT faster than querying each slot separately)
368 List< List<object> > table = ExecuteReader("SELECT name, slot, amount, petHealth, petLevel, petExperience, petAttackIV, petAttackEV, petSpecialAttackIV, petSpecialAttackEV, petDefenseIV, petDefenseEV, petSpecialDefenseIV, petSpecialDefenseEV, petHealthIV, petHealthEV, petHealthRegenIV, petHealthRegenEV, petStaminaIV, petStaminaEV, petStaminaRegenIV, petStaminaRegenEV FROM character_inventory WHERE character=@character", new SqliteParameter("@character", player.name));
369 foreach (List<object> row in table)
370 {
371 string itemName = (string)row[0];
372 int slot = Convert.ToInt32((long)row[1]);
373 ScriptableItem itemData;
374 if (slot < player.inventorySize && ScriptableItem.dict.TryGetValue(itemName.GetStableHashCode(), out itemData))
375 {
376 Item item = new Item(itemData);
377 int amount = Convert.ToInt32((long)row[2]);
378 item.petHealth = Convert.ToInt32((long)row[3]);
379 item.petLevel = Convert.ToInt32((long)row[4]);
380 item.petExperience = (long)row[5];
381 item.petAttackIV = Convert.ToInt32((long)row[6]);
382 item.petAttackEV = Convert.ToInt32((long)row[7]);
383 item.petSpecialAttackIV = Convert.ToInt32((long)row[8]);
384 item.petSpecialAttackEV = Convert.ToInt32((long)row[9]);
385 item.petDefenseIV = Convert.ToInt32((long)row[10]);
386 item.petDefenseEV = Convert.ToInt32((long)row[11]);
387 item.petSpecialDefenseIV = Convert.ToInt32((long)row[12]);
388 item.petSpecialDefenseEV = Convert.ToInt32((long)row[13]);
389 item.petHealthIV = Convert.ToInt32((long)row[14]);
390 item.petHealthEV = Convert.ToInt32((long)row[15]);
391 item.petHealthRegenIV = Convert.ToInt32((long)row[16]);
392 item.petHealthRegenEV = Convert.ToInt32((long)row[17]);
393 item.petStaminaIV = Convert.ToInt32((long)row[18]);
394 item.petStaminaEV = Convert.ToInt32((long)row[19]);
395 item.petStaminaRegenIV = Convert.ToInt32((long)row[20]);
396 item.petStaminaRegenEV = Convert.ToInt32((long)row[21]);
397 player.inventory[slot] = new ItemSlot(item, amount);;
398 }
399 }
400 }
401
402 static void LoadEquipment(Player player)
403 {
404 // fill all slots first
405 for (int i = 0; i < player.equipmentInfo.Length; ++i)
406 player.equipment.Add(new ItemSlot());
407
408 // then load valid equipment and put into their slots
409 // (one big query is A LOT faster than querying each slot separately)
410 List< List<object> > table = ExecuteReader("SELECT name, slot, amount FROM character_equipment WHERE character=@character", new SqliteParameter("@character", player.name));
411 foreach (List<object> row in table)
412 {
413 string itemName = (string)row[0];
414 int slot = Convert.ToInt32((long)row[1]);
415 ScriptableItem itemData;
416 if (slot < player.equipmentInfo.Length && ScriptableItem.dict.TryGetValue(itemName.GetStableHashCode(), out itemData))
417 {
418 Item item = new Item(itemData);
419 int amount = Convert.ToInt32((long)row[2]);
420 player.equipment[slot] = new ItemSlot(item, amount);
421 }
422 }
423 }
424
425 static void LoadSkills(Player player)
426 {
427 // load skills based on skill templates (the others don't matter)
428 // -> this way any skill changes in a prefab will be applied
429 // to all existing players every time (unlike item templates
430 // which are only for newly created characters)
431
432 // fill all slots first
433 foreach (ScriptableSkill skillData in player.skillTemplates)
434 player.skills.Add(new Skill(skillData));
435
436 // then load learned skills and put into their slots
437 // (one big query is A LOT faster than querying each slot separately)
438 List< List<object> > table = ExecuteReader("SELECT name, level, castTimeEnd, cooldownEnd FROM character_skills WHERE character=@character", new SqliteParameter("@character", player.name));
439 foreach (List<object> row in table)
440 {
441 string skillName = (string)row[0];
442 int index = player.skills.FindIndex(skill => skill.name == skillName);
443 if (index != -1)
444 {
445 Skill skill = player.skills[index];
446 // make sure that 1 <= level <= maxlevel (in case we removed a skill
447 // level etc)
448 skill.level = Mathf.Clamp(Convert.ToInt32((long)row[1]), 1, skill.maxLevel);
449 // make sure that 1 <= level <= maxlevel (in case we removed a skill
450 // level etc)
451 // castTimeEnd and cooldownEnd are based on Time.time, which
452 // will be different when restarting a server, hence why we
453 // saved them as just the remaining times. so let's convert them
454 // back again.
455 skill.castTimeEnd = (float)row[2] + Time.time;
456 skill.cooldownEnd = (float)row[3] + Time.time;
457
458 player.skills[index] = skill;
459 }
460 }
461 }
462
463 static void LoadBuffs(Player player)
464 {
465 // load buffs
466 // note: no check if we have learned the skill for that buff
467 // since buffs may come from other people too
468 List< List<object> > table = ExecuteReader("SELECT name, level, buffTimeEnd FROM character_buffs WHERE character=@character", new SqliteParameter("@character", player.name));
469 foreach (List<object> row in table)
470 {
471 string buffName = (string)row[0];
472 ScriptableSkill skillData;
473 if (ScriptableSkill.dict.TryGetValue(buffName.GetStableHashCode(), out skillData))
474 {
475 // make sure that 1 <= level <= maxlevel (in case we removed a skill
476 // level etc)
477 int level = Mathf.Clamp(Convert.ToInt32((long)row[1]), 1, skillData.maxLevel);
478 Buff buff = new Buff((BuffSkill)skillData, level);
479 // buffTimeEnd is based on Time.time, which will be
480 // different when restarting a server, hence why we saved
481 // them as just the remaining times. so let's convert them
482 // back again.
483 buff.buffTimeEnd = (float)row[2] + Time.time;
484 player.buffs.Add(buff);
485 }
486 }
487 }
488
489 static void LoadQuests(Player player)
490 {
491 // load quests
492 List< List<object> > table = ExecuteReader("SELECT name, killed, completed FROM character_quests WHERE character=@character", new SqliteParameter("@character", player.name));
493 foreach (List<object> row in table)
494 {
495 string questName = (string)row[0];
496 ScriptableQuest questData;
497 if (ScriptableQuest.dict.TryGetValue(questName.GetStableHashCode(), out questData))
498 {
499 Quest quest = new Quest(questData);
500 quest.killed = Convert.ToInt32((long)row[1]);
501 quest.completed = ((long)row[2]) != 0; // sqlite has no bool
502 player.quests.Add(quest);
503 }
504 }
505 }
506
507 static void LoadGuild(Player player)
508 {
509 // in a guild?
510 string guild = (string)ExecuteScalar("SELECT guild FROM character_guild WHERE character=@character", new SqliteParameter("@character", player.name));
511 if (guild != null)
512 {
513 // load guild info
514 player.guildName = guild;
515 List< List<object> > table = ExecuteReader("SELECT notice FROM guild_info WHERE name=@guild", new SqliteParameter("@guild", guild));
516 if (table.Count == 1) {
517 List<object> row = table[0];
518 player.guild.notice = (string)row[0];
519 }
520
521 // load members list
522 List<GuildMember> members = new List<GuildMember>();
523 table = ExecuteReader("SELECT character, rank FROM character_guild WHERE guild=@guild", new SqliteParameter("@guild", player.guildName));
524 foreach (List<object> row in table) {
525 GuildMember member = new GuildMember();
526 member.name = (string)row[0];
527 member.rank = (GuildRank)Convert.ToInt32((long)row[1]);
528 member.online = Player.onlinePlayers.ContainsKey(member.name);
529 if (member.name == player.name)
530 {
531 member.level = player.level;
532 }
533 else
534 {
535 object scalar = ExecuteScalar("SELECT level FROM characters WHERE name=@character", new SqliteParameter("@character", member.name));
536 member.level = scalar != null ? Convert.ToInt32((long)scalar) : 1;
537 }
538 members.Add(member);
539 }
540 player.guild.members = members.ToArray(); // guild.AddMember each time is too slow because array resizing
541 }
542 }
543
544 public static GameObject CharacterLoad(string characterName, List<Player> prefabs) {
545 List< List<object> > table = ExecuteReader("SELECT * FROM characters WHERE name=@name AND deleted=0", new SqliteParameter("@name", characterName));
546 if (table.Count == 1)
547 {
548 List<object> mainrow = table[0];
549
550 // instantiate based on the class name
551 string className = (string)mainrow[2];
552 Player prefab = prefabs.Find(p => p.name == className);
553 if (prefab != null)
554 {
555 GameObject go = GameObject.Instantiate(prefab.gameObject);
556 Player player = go.GetComponent<Player>();
557
558 player.name = (string)mainrow[0];
559 player.account = (string)mainrow[1];
560 player.className = (string)mainrow[2];
561 float x = (float)mainrow[3];
562 float y = (float)mainrow[4];
563 float z = (float)mainrow[5];
564 Vector3 position = new Vector3(x, y, z);
565 player.level = Convert.ToInt32((long)mainrow[6]);
566 int health = Convert.ToInt32((long)mainrow[7]);
567 int mana = Convert.ToInt32((long)mainrow[8]);
568 player.strength = Convert.ToInt32((long)mainrow[9]);
569 player.intelligence = Convert.ToInt32((long)mainrow[10]);
570 player.experience = (long)mainrow[11];
571 player.skillExperience = (long)mainrow[12];
572 player.gold = (long)mainrow[13];
573 player.coins = (long)mainrow[14];
574
575 // try to warp to loaded position.
576 // => agent.warp is recommended over transform.position and
577 // avoids all kinds of weird bugs
578 // => warping might fail if we changed the world since last save
579 // so we reset to start position if not on navmesh
580 player.agent.Warp(position);
581 if (!player.agent.isOnNavMesh)
582 {
583 Transform start = NetworkManager.singleton.GetNearestStartPosition(position);
584 player.agent.Warp(start.position);
585 Debug.Log(player.name + " invalid position was reset");
586 }
587
588 LoadInventory(player);
589 LoadEquipment(player);
590 LoadSkills(player);
591 LoadBuffs(player);
592 LoadQuests(player);
593 LoadGuild(player);
594
595 // assign health / mana after max values were fully loaded
596 // (they depend on equipment, buffs, etc.)
597 player.health = health;
598 player.mana = mana;
599
600 // addon system hooks
601 Utils.InvokeMany(typeof(Database), null, "CharacterLoad_", player);
602
603 return go;
604 }
605 else Debug.LogError("no prefab found for class: " + className);
606 }
607 return null;
608 }
609
610 static void SaveInventory(Player player)
611 {
612 // inventory: remove old entries first, then add all new ones
613 // (we could use UPDATE where slot=... but deleting everything makes
614 // sure that there are never any ghosts)
615 ExecuteNonQuery("DELETE FROM character_inventory WHERE character=@character", new SqliteParameter("@character", player.name));
616 for (int i = 0; i < player.inventory.Count; ++i)
617 {
618 ItemSlot slot = player.inventory[i];
619 if (slot.amount > 0) // only relevant items to save queries/storage/time
620 ExecuteNonQuery("INSERT INTO character_inventory VALUES (@character, @slot, @name, @amount, @petHealth, @petLevel, @petExperience, @petAttackIV, @petAttackEV, @petSpecialAttackIV, @petSpecialAttackEV, @petDefenseIV, @petDefenseEV, @petSpecialDefenseIV, @petSpecialDefenseEV, @petHealthIV, @petHealthEV, @petHealthRegenIV, @petHealthRegenEV, @petStaminaIV, @petStaminaEV, @petStaminaRegenIV, @petStaminaRegenEV)",
621 new SqliteParameter("@character", player.name),
622 new SqliteParameter("@slot", i),
623 new SqliteParameter("@name", slot.item.name),
624 new SqliteParameter("@amount", slot.amount),
625 new SqliteParameter("@petHealth", slot.item.petHealth),
626 new SqliteParameter("@petLevel", slot.item.petLevel),
627 new SqliteParameter("@petExperience", slot.item.petExperience),
628 new SqliteParameter("@petAttackIV", slot.item.petAttackIV),
629 new SqliteParameter("@petAttackEV", slot.item.petAttackEV),
630 new SqliteParameter("@petSpecialAttackIV", slot.item.petSpecialAttackIV),
631 new SqliteParameter("@petSpecialAttackEV", slot.item.petSpecialAttackEV),
632 new SqliteParameter("@petDefenseIV", slot.item.petDefenseIV),
633 new SqliteParameter("@petDefenseEV", slot.item.petDefenseEV),
634 new SqliteParameter("@petSpecialDefenseIV", slot.item.petSpecialDefenseIV),
635 new SqliteParameter("@petSpecialDefenseEV", slot.item.petSpecialDefenseEV),
636 new SqliteParameter("@petHealthIV", slot.item.petHealthIV),
637 new SqliteParameter("@petHealthEV", slot.item.petHealthEV),
638 new SqliteParameter("@petHealthRegenIV", slot.item.petHealthRegenIV),
639 new SqliteParameter("@petHealthRegenEV", slot.item.petHealthRegenEV),
640 new SqliteParameter("@petStaminaIV", slot.item.petStaminaIV),
641 new SqliteParameter("@petStaminaEV", slot.item.petStaminaEV),
642 new SqliteParameter("@petStaminaRegenIV", slot.item.petStaminaRegenIV),
643 new SqliteParameter("@petStaminaRegenEV", slot.item.petStaminaRegenEV));
644 }
645 }
646
647 static void SaveEquipment(Player player)
648 {
649 // equipment: remove old entries first, then add all new ones
650 // (we could use UPDATE where slot=... but deleting everything makes
651 // sure that there are never any ghosts)
652 ExecuteNonQuery("DELETE FROM character_equipment WHERE character=@character", new SqliteParameter("@character", player.name));
653 for (int i = 0; i < player.equipment.Count; ++i)
654 {
655 ItemSlot slot = player.equipment[i];
656 if (slot.amount > 0) // only relevant equip to save queries/storage/time
657 ExecuteNonQuery("INSERT INTO character_equipment VALUES (@character, @slot, @name, @amount)",
658 new SqliteParameter("@character", player.name),
659 new SqliteParameter("@slot", i),
660 new SqliteParameter("@name", slot.item.name),
661 new SqliteParameter("@amount", slot.amount));
662 }
663 }
664
665 static void SaveSkills(Player player)
666 {
667 // skills: remove old entries first, then add all new ones
668 ExecuteNonQuery("DELETE FROM character_skills WHERE character=@character", new SqliteParameter("@character", player.name));
669 foreach (Skill skill in player.skills)
670 if (skill.level > 0) // only learned skills to save queries/storage/time
671 // castTimeEnd and cooldownEnd are based on Time.time, which
672 // will be different when restarting the server, so let's
673 // convert them to the remaining time for easier save & load
674 // note: this does NOT work when trying to save character data shortly
675 // before closing the editor or game because Time.time is 0 then.
676 ExecuteNonQuery("INSERT INTO character_skills VALUES (@character, @name, @level, @castTimeEnd, @cooldownEnd)",
677 new SqliteParameter("@character", player.name),
678 new SqliteParameter("@name", skill.name),
679 new SqliteParameter("@level", skill.level),
680 new SqliteParameter("@castTimeEnd", skill.CastTimeRemaining()),
681 new SqliteParameter("@cooldownEnd", skill.CooldownRemaining()));
682 }
683
684 static void SaveBuffs(Player player)
685 {
686 // buffs: remove old entries first, then add all new ones
687 ExecuteNonQuery("DELETE FROM character_buffs WHERE character=@character", new SqliteParameter("@character", player.name));
688 foreach (Buff buff in player.buffs)
689 // buffTimeEnd is based on Time.time, which will be different when
690 // restarting the server, so let's convert them to the remaining
691 // time for easier save & load
692 // note: this does NOT work when trying to save character data shortly
693 // before closing the editor or game because Time.time is 0 then.
694 ExecuteNonQuery("INSERT INTO character_buffs VALUES (@character, @name, @level, @buffTimeEnd)",
695 new SqliteParameter("@character", player.name),
696 new SqliteParameter("@name", buff.name),
697 new SqliteParameter("@level", buff.level),
698 new SqliteParameter("@buffTimeEnd", buff.BuffTimeRemaining()));
699 }
700
701 static void SaveQuests(Player player)
702 {
703 // quests: remove old entries first, then add all new ones
704 ExecuteNonQuery("DELETE FROM character_quests WHERE character=@character", new SqliteParameter("@character", player.name));
705 foreach (Quest quest in player.quests)
706 ExecuteNonQuery("INSERT INTO character_quests VALUES (@character, @name, @killed, @completed)",
707 new SqliteParameter("@character", player.name),
708 new SqliteParameter("@name", quest.name),
709 new SqliteParameter("@killed", quest.killed),
710 new SqliteParameter("@completed", Convert.ToInt32(quest.completed)));
711 }
712
713 // adds or overwrites character data in the database
714 public static void CharacterSave(Player player, bool online, bool useTransaction = true)
715 {
716 // only use a transaction if not called within SaveMany transaction
717 if (useTransaction) ExecuteNonQuery("BEGIN");
718
719 // online status:
720 // '' if offline (if just logging out etc.)
721 // current time otherwise
722 // -> this way it's fault tolerant because external applications can
723 // check if online != '' and if time difference < saveinterval
724 // -> online time is useful for network zones (server<->server online
725 // checks), external websites which render dynamic maps, etc.
726 // -> it uses the ISO 8601 standard format
727 string onlineString = online ? DateTime.UtcNow.ToString("s") : "";
728
729 ExecuteNonQuery("INSERT OR REPLACE INTO characters VALUES (@name, @account, @class, @x, @y, @z, @level, @health, @mana, @strength, @intelligence, @experience, @skillExperience, @gold, @coins, @online, 0)",
730 new SqliteParameter("@name", player.name),
731 new SqliteParameter("@account", player.account),
732 new SqliteParameter("@class", player.className),
733 new SqliteParameter("@x", player.transform.position.x),
734 new SqliteParameter("@y", player.transform.position.y),
735 new SqliteParameter("@z", player.transform.position.z),
736 new SqliteParameter("@level", player.level),
737 new SqliteParameter("@health", player.health),
738 new SqliteParameter("@mana", player.mana),
739 new SqliteParameter("@strength", player.strength),
740 new SqliteParameter("@intelligence", player.intelligence),
741 new SqliteParameter("@experience", player.experience),
742 new SqliteParameter("@skillExperience", player.skillExperience),
743 new SqliteParameter("@gold", player.gold),
744 new SqliteParameter("@coins", player.coins),
745 new SqliteParameter("@online", onlineString));
746
747 SaveInventory(player);
748 SaveEquipment(player);
749 SaveSkills(player);
750 SaveBuffs(player);
751 SaveQuests(player);
752
753 // addon system hooks
754 Utils.InvokeMany(typeof(Database), null, "CharacterSave_", player);
755
756 if (useTransaction) ExecuteNonQuery("END");
757 }
758
759 // save multiple characters at once (useful for ultra fast transactions)
760 public static void CharacterSaveMany(List<Player> players, bool online = true)
761 {
762 ExecuteNonQuery("BEGIN"); // transaction for performance
763 foreach (Player player in players)
764 CharacterSave(player, online, false);
765 ExecuteNonQuery("END");
766 }
767
768 // guilds //////////////////////////////////////////////////////////////////
769 public static bool GuildExists(string guild)
770 {
771 return ((long)ExecuteScalar("SELECT Count(*) FROM guild_info WHERE name=@name", new SqliteParameter("@name", guild))) == 1;
772 }
773
774 public static void SaveGuild(string guild, string notice, List<GuildMember> members)
775 {
776 ExecuteNonQuery("BEGIN"); // transaction for performance
777
778 // guild info
779 ExecuteNonQuery("INSERT OR REPLACE INTO guild_info VALUES (@guild, @notice)",
780 new SqliteParameter("@guild", guild),
781 new SqliteParameter("@notice", notice));
782
783 // members list
784 ExecuteNonQuery("DELETE FROM character_guild WHERE guild=@guild", new SqliteParameter("@guild", guild));
785 foreach (GuildMember member in members)
786 {
787 ExecuteNonQuery("INSERT INTO character_guild VALUES (@character, @guild, @rank)",
788 new SqliteParameter("@character", member.name),
789 new SqliteParameter("@guild", guild),
790 new SqliteParameter("@rank", member.rank));
791 }
792
793 ExecuteNonQuery("END");
794 }
795
796 public static void RemoveGuild(string guild)
797 {
798 ExecuteNonQuery("BEGIN"); // transaction for performance
799 ExecuteNonQuery("DELETE FROM guild_info WHERE name=@name", new SqliteParameter("@name", guild));
800 ExecuteNonQuery("DELETE FROM character_guild WHERE guild=@guild", new SqliteParameter("@guild", guild));
801 ExecuteNonQuery("END");
802 }
803
804 // item mall ///////////////////////////////////////////////////////////////
805 public static List<long> GrabCharacterOrders(string characterName)
806 {
807 // grab new orders from the database and delete them immediately
808 //
809 // note: this requires an orderid if we want someone else to write to
810 // the database too. otherwise deleting would delete all the new ones or
811 // updating would update all the new ones. especially in sqlite.
812 //
813 // note: we could just delete processed orders, but keeping them in the
814 // database is easier for debugging / support.
815 List<long> result = new List<long>();
816 List< List<object> > table = ExecuteReader("SELECT orderid, coins FROM character_orders WHERE character=@character AND processed=0", new SqliteParameter("@character", characterName));
817 foreach (List<object> row in table)
818 {
819 result.Add((long)row[1]);
820 ExecuteNonQuery("UPDATE character_orders SET processed=1 WHERE orderid=@orderid", new SqliteParameter("@orderid", (long)row[0]));
821 }
822 return result;
823 }
824}