9
3

CommandsDB.cpp 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316
  1. /*
  2. EQ2Emulator: Everquest II Server Emulator
  3. Copyright (C) 2007 EQ2EMulator Development Team (http://www.eq2emulator.net)
  4. This file is part of EQ2Emulator.
  5. EQ2Emulator is free software: you can redistribute it and/or modify
  6. it under the terms of the GNU General Public License as published by
  7. the Free Software Foundation, either version 3 of the License, or
  8. (at your option) any later version.
  9. EQ2Emulator is distributed in the hope that it will be useful,
  10. but WITHOUT ANY WARRANTY; without even the implied warranty of
  11. MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12. GNU General Public License for more details.
  13. You should have received a copy of the GNU General Public License
  14. along with EQ2Emulator. If not, see <http://www.gnu.org/licenses/>.
  15. */
  16. #ifdef WIN32
  17. #include <WinSock2.h>
  18. #include <windows.h>
  19. #endif
  20. #include <mysql.h>
  21. #include <assert.h>
  22. #include "../../common/Log.h"
  23. #include "../WorldDatabase.h"
  24. #include "Commands.h"
  25. #include "ConsoleCommands.h"
  26. map<int32, string>* WorldDatabase::GetSpawnTemplateListByName(const char* name)
  27. {
  28. LogWrite(COMMAND__DEBUG, 0, "Command", "Player listing spawn templates by template name ('%s')...", name);
  29. map<int32, string>* ret = 0;
  30. string template_name = "";
  31. Query query;
  32. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id, name FROM spawn_templates WHERE name RLIKE '%s' LIMIT 0,10", getSafeEscapeString(name).c_str());
  33. if(result && mysql_num_rows(result) > 0)
  34. {
  35. ret = new map<int32, string>;
  36. MYSQL_ROW row;
  37. while(result && (row = mysql_fetch_row(result)))
  38. {
  39. template_name = string(row[1]);
  40. (*ret)[atoul(row[0])] = template_name;
  41. LogWrite(COMMAND__DEBUG, 5, "Command", "\t%u. '%s'", atoul(row[0]), template_name.c_str());
  42. }
  43. }
  44. return ret;
  45. }
  46. map<int32, string>* WorldDatabase::GetSpawnTemplateListByID(int32 location_id)
  47. {
  48. LogWrite(COMMAND__DEBUG, 0, "Command", "Player listing spawn templates by LocaionID: %u...", location_id);
  49. map<int32, string>* ret = 0;
  50. string template_name = "";
  51. Query query;
  52. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id, name FROM spawn_templates WHERE spawn_location_id = %u", location_id);
  53. if(result && mysql_num_rows(result) > 0)
  54. {
  55. ret = new map<int32, string>;
  56. MYSQL_ROW row;
  57. while(result && (row = mysql_fetch_row(result)))
  58. {
  59. template_name = string(row[1]);
  60. (*ret)[atoul(row[0])] = template_name;
  61. LogWrite(COMMAND__DEBUG, 5, "Command", "\t%u. '%s'", atoul(row[0]), template_name.c_str());
  62. }
  63. }
  64. return ret;
  65. }
  66. int32 WorldDatabase::SaveSpawnTemplate(int32 placement_id, const char* template_name)
  67. {
  68. Query query;
  69. string str_name = getSafeEscapeString(template_name).c_str();
  70. LogWrite(COMMAND__DEBUG, 0, "Command", "Player saving spawn template '%s' for placement_id %u...", str_name.c_str(), placement_id);
  71. query.RunQuery2(Q_INSERT, "INSERT INTO spawn_templates (name, spawn_location_id) VALUES ('%s', %u)", str_name.c_str(), placement_id);
  72. if(query.GetErrorNumber() && query.GetError() && query.GetErrorNumber() < 0xFFFFFFFF){
  73. LogWrite(COMMAND__ERROR, 0, "Command", "Error in SaveSpawnTemplate query '%s': %s", query.GetQuery(), query.GetError());
  74. return 0;
  75. }
  76. int32 ret = query.GetLastInsertedID();
  77. LogWrite(COMMAND__DEBUG, 0, "Command", "Success! Returning TemplateID: %u...", ret);
  78. return ret;
  79. }
  80. bool WorldDatabase::RemoveSpawnTemplate(int32 template_id)
  81. {
  82. Query query;
  83. LogWrite(COMMAND__DEBUG, 0, "Command", "Player removing spawn template ID %u...", template_id);
  84. query.RunQuery2(Q_DELETE, "DELETE FROM spawn_templates WHERE id = %u", template_id);
  85. if(query.GetErrorNumber() && query.GetError() && query.GetErrorNumber() < 0xFFFFFFFF){
  86. LogWrite(COMMAND__ERROR, 0, "Command", "Error in RemoveSpawnTemplate query '%s': %s", query.GetQuery(), query.GetError());
  87. return false;
  88. }
  89. if (query.GetAffectedRows() > 0 )
  90. {
  91. LogWrite(COMMAND__DEBUG, 0, "Command", "Success! Removed spawn template ID %u...", template_id);
  92. return true;
  93. }
  94. return false;
  95. }
  96. int32 WorldDatabase::CreateSpawnFromTemplateByID(Client* client, int32 template_id)
  97. {
  98. Query query, query2, query3, query4, query5, query6;
  99. MYSQL_ROW row;
  100. int32 spawn_location_id = 0;
  101. float new_x = client->GetPlayer()->GetX();
  102. float new_y = client->GetPlayer()->GetY();
  103. float new_z = client->GetPlayer()->GetZ();
  104. float new_heading = client->GetPlayer()->GetHeading();
  105. LogWrite(COMMAND__DEBUG, 0, "Command", "Creating spawn point from templateID %u...", template_id);
  106. LogWrite(COMMAND__DEBUG, 5, "Command", "\tCoords: %.2f %.2f %.2f...", new_x, new_y, new_z);
  107. // find the spawn_location_id in the template we plan to duplicate
  108. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT spawn_location_id FROM spawn_templates WHERE id = %u", template_id);
  109. if (result && (row = mysql_fetch_row(result))) {
  110. if (row[0])
  111. spawn_location_id = atoi(row[0]);
  112. }
  113. if( spawn_location_id > 0 )
  114. {
  115. LogWrite(COMMAND__DEBUG, 5, "Command", "\tUsing LocationID: %u...", spawn_location_id);
  116. // insert a new spawn_location_name record
  117. string name = "TemplateGenerated";
  118. query2.RunQuery2(Q_INSERT, "INSERT INTO spawn_location_name (name) VALUES ('%s')", name.c_str());
  119. if(query2.GetErrorNumber() && query2.GetError() && query2.GetErrorNumber() < 0xFFFFFFFF){
  120. LogWrite(COMMAND__ERROR, 0, "Command", "Error in CreateSpawnFromTemplateByID query '%s': %s", query2.GetQuery(), query2.GetError());
  121. return 0;
  122. }
  123. int32 new_location_id = query2.GetLastInsertedID();
  124. LogWrite(COMMAND__DEBUG, 5, "Command", "Created new Spawn Location: '%s' (%u)", name.c_str(), new_location_id);
  125. // get all spawn_location_entries that match the templates spawn_location_id value and insert as new
  126. LogWrite(COMMAND__DEBUG, 5, "Command", "Finding existing spawn_location_entry(s) for location_id %u", spawn_location_id);
  127. MYSQL_RES* result2 = query3.RunQuery2(Q_SELECT, "SELECT spawn_id, spawnpercentage FROM spawn_location_entry WHERE spawn_location_id = %u", spawn_location_id);
  128. if(result2 && mysql_num_rows(result2) > 0){
  129. MYSQL_ROW row2;
  130. while(result2 && (row2 = mysql_fetch_row(result2)) && row2[0])
  131. {
  132. query4.RunQuery2(Q_INSERT, "INSERT INTO spawn_location_entry (spawn_id, spawn_location_id, spawnpercentage) VALUES (%u, %u, %i)",
  133. atoul(row2[0]), new_location_id, atoi(row2[1]));
  134. if(query4.GetErrorNumber() && query4.GetError() && query4.GetErrorNumber() < 0xFFFFFFFF){
  135. LogWrite(COMMAND__ERROR, 0, "Command", "Error in CreateSpawnFromTemplateByID query '%s': %s", query4.GetQuery(), query4.GetError());
  136. return 0;
  137. }
  138. LogWrite(COMMAND__DEBUG, 5, "Command", "Insert Entry for spawn_id %u, location_id %u, percentage %i", atoul(row2[0]), new_location_id, atoi(row2[1]));
  139. }
  140. }
  141. // get all spawn_location_placements that match the templates spawn_location_id value and insert as new
  142. // Note: /spawn templates within current zone_id only, because of spawn_id issues (cannot template an Antonic spawn in Commonlands)
  143. LogWrite(COMMAND__DEBUG, 5, "Command", "Finding existing spawn_location_placement(s) for location_id %u", spawn_location_id);
  144. MYSQL_RES* result3 = query5.RunQuery2(Q_SELECT, "SELECT zone_id, x_offset, y_offset, z_offset, respawn, expire_timer, expire_offset, grid_id FROM spawn_location_placement WHERE spawn_location_id = %u", spawn_location_id);
  145. if(result3 && mysql_num_rows(result3) > 0){
  146. MYSQL_ROW row3;
  147. while(result3 && (row3 = mysql_fetch_row(result3)) && row3[0])
  148. {
  149. query6.RunQuery2(Q_INSERT, "INSERT INTO spawn_location_placement (zone_id, spawn_location_id, x, y, z, heading, x_offset, y_offset, z_offset, respawn, expire_timer, expire_offset, grid_id) VALUES (%i, %u, %2f, %2f, %2f, %2f, %2f, %2f, %2f, %i, %i, %i, %u)",
  150. atoi(row3[0]), new_location_id, new_x, new_y, new_z, new_heading, atof(row3[1]), atof(row3[2]), atof(row3[3]), atoi(row3[4]), atoi(row3[5]), atoi(row3[6]), atoul(row3[7]));
  151. if(query6.GetErrorNumber() && query6.GetError() && query6.GetErrorNumber() < 0xFFFFFFFF){
  152. LogWrite(COMMAND__ERROR, 0, "Command", "Error in CreateSpawnFromTemplateByID query '%s': %s", query6.GetQuery(), query6.GetError());
  153. return 0;
  154. }
  155. LogWrite(COMMAND__DEBUG, 5, "Command", "Insert Placement at new coords for location_id %u", new_location_id);
  156. }
  157. }
  158. LogWrite(COMMAND__DEBUG, 0, "Command", "Success! New spawn(s) from TemplateID %u created from location %u", template_id, spawn_location_id);
  159. return new_location_id;
  160. }
  161. return 0;
  162. }
  163. int32 WorldDatabase::CreateSpawnFromTemplateByName(Client* client, const char* template_name)
  164. {
  165. Query query, query1, query2, query3, query4, query5, query6;
  166. MYSQL_ROW row;
  167. int32 template_id = 0;
  168. int32 spawn_location_id = 0;
  169. float new_x = client->GetPlayer()->GetX();
  170. float new_y = client->GetPlayer()->GetY();
  171. float new_z = client->GetPlayer()->GetZ();
  172. float new_heading = client->GetPlayer()->GetHeading();
  173. LogWrite(COMMAND__DEBUG, 0, "Command", "Creating spawn point from template '%s'...", template_name);
  174. LogWrite(COMMAND__DEBUG, 5, "Command", "\tCoords: %.2f %.2f %.2f...", new_x, new_y, new_z);
  175. // find the spawn_location_id in the template we plan to duplicate
  176. MYSQL_RES* result = query.RunQuery2(Q_SELECT, "SELECT id, spawn_location_id FROM spawn_templates WHERE name = '%s'", template_name);
  177. if (result && (row = mysql_fetch_row(result))) {
  178. if (row[0])
  179. {
  180. template_id = atoul(row[0]);
  181. spawn_location_id = atoi(row[1]);
  182. }
  183. }
  184. if( spawn_location_id > 0 )
  185. {
  186. LogWrite(COMMAND__DEBUG, 5, "Command", "\tUsing LocationID: %u...", spawn_location_id);
  187. // insert a new spawn_location_name record
  188. string name = "TemplateGenerated";
  189. query2.RunQuery2(Q_INSERT, "INSERT INTO spawn_location_name (name) VALUES ('%s')", name.c_str());
  190. if(query2.GetErrorNumber() && query2.GetError() && query2.GetErrorNumber() < 0xFFFFFFFF){
  191. LogWrite(COMMAND__ERROR, 0, "Command", "Error in CreateSpawnFromTemplateByID query '%s': %s", query2.GetQuery(), query2.GetError());
  192. return 0;
  193. }
  194. int32 new_location_id = query2.GetLastInsertedID();
  195. LogWrite(COMMAND__DEBUG, 5, "Command", "Created new Spawn Location: '%s' (%u)", name.c_str(), new_location_id);
  196. // get all spawn_location_entries that match the templates spawn_location_id value and insert as new
  197. LogWrite(COMMAND__DEBUG, 5, "Command", "Finding existing spawn_location_entry(s) for location_id %u", spawn_location_id);
  198. MYSQL_RES* result2 = query3.RunQuery2(Q_SELECT, "SELECT spawn_id, spawnpercentage FROM spawn_location_entry WHERE spawn_location_id = %u", spawn_location_id);
  199. if(result2 && mysql_num_rows(result2) > 0){
  200. MYSQL_ROW row2;
  201. while(result2 && (row2 = mysql_fetch_row(result2)) && row2[0])
  202. {
  203. query4.RunQuery2(Q_INSERT, "INSERT INTO spawn_location_entry (spawn_id, spawn_location_id, spawnpercentage) VALUES (%u, %u, %i)",
  204. atoul(row2[0]), new_location_id, atoi(row2[1]));
  205. if(query4.GetErrorNumber() && query4.GetError() && query4.GetErrorNumber() < 0xFFFFFFFF){
  206. LogWrite(COMMAND__ERROR, 0, "Command", "Error in CreateSpawnFromTemplateByID query '%s': %s", query4.GetQuery(), query4.GetError());
  207. return 0;
  208. }
  209. LogWrite(COMMAND__DEBUG, 5, "Command", "Insert Entry for spawn_id %u, location_id %u, percentage %i", atoul(row2[0]), new_location_id, atoi(row2[1]));
  210. }
  211. }
  212. // get all spawn_location_placements that match the templates spawn_location_id value and insert as new
  213. // Note: /spawn templates within current zone_id only, because of spawn_id issues (cannot template an Antonic spawn in Commonlands)
  214. LogWrite(COMMAND__DEBUG, 5, "Command", "Finding existing spawn_location_placement(s) for location_id %u", spawn_location_id);
  215. MYSQL_RES* result3 = query5.RunQuery2(Q_SELECT, "SELECT zone_id, x_offset, y_offset, z_offset, respawn, expire_timer, expire_offset, grid_id FROM spawn_location_placement WHERE spawn_location_id = %u", spawn_location_id);
  216. if(result3 && mysql_num_rows(result3) > 0){
  217. MYSQL_ROW row3;
  218. while(result3 && (row3 = mysql_fetch_row(result3)) && row3[0])
  219. {
  220. query6.RunQuery2(Q_INSERT, "INSERT INTO spawn_location_placement (zone_id, spawn_location_id, x, y, z, heading, x_offset, y_offset, z_offset, respawn, expire_timer, expire_offset, grid_id) VALUES (%i, %u, %2f, %2f, %2f, %2f, %2f, %2f, %2f, %i, %i, %i, %u)",
  221. atoi(row3[0]), new_location_id, new_x, new_y, new_z, new_heading, atof(row3[1]), atof(row3[2]), atof(row3[3]), atoi(row3[4]), atoi(row3[5]), atoi(row3[6]), atoul(row3[7]));
  222. if(query6.GetErrorNumber() && query6.GetError() && query6.GetErrorNumber() < 0xFFFFFFFF){
  223. LogWrite(COMMAND__ERROR, 0, "Command", "Error in CreateSpawnFromTemplateByID query '%s': %s", query6.GetQuery(), query6.GetError());
  224. return 0;
  225. }
  226. LogWrite(COMMAND__DEBUG, 5, "Command", "Insert Placement at new coords for location_id %u", new_location_id);
  227. }
  228. }
  229. LogWrite(COMMAND__DEBUG, 0, "Command", "Success! New spawn(s) from TemplateID %u created from location %u", template_id, spawn_location_id);
  230. return new_location_id;
  231. }
  232. return 0;
  233. }
  234. bool WorldDatabase::SaveZoneSafeCoords(int32 zone_id, float x, float y, float z, float heading)
  235. {
  236. Query query;
  237. LogWrite(COMMAND__DEBUG, 0, "Command", "Setting safe coords for zone %u (X: %.2f, Y: %.2f, Z: %.2f, H: %.2f)", zone_id, x, y, z, heading);
  238. query.RunQuery2(Q_UPDATE, "UPDATE zones SET safe_x = %f, safe_y = %f, safe_z = %f, safe_heading = %f WHERE id = %u", x, y, z, heading, zone_id);
  239. if(query.GetErrorNumber() && query.GetError() && query.GetErrorNumber() < 0xFFFFFFFF){
  240. LogWrite(DATABASE__ERROR, 0, "DBCore", "Error in SaveZoneSafeCoords query '%s': %s", query.GetQuery(), query.GetError());
  241. return false;
  242. }
  243. if (query.GetAffectedRows() > 0 )
  244. {
  245. LogWrite(COMMAND__DEBUG, 0, "Command", "Success! Set new safe coordinates in zone ID %u...", zone_id);
  246. return true;
  247. }
  248. LogWrite(COMMAND__ERROR, 0, "Command", "FAILED! Set new safe coordinates in zone ID %u...", zone_id);
  249. return false;
  250. }
  251. bool WorldDatabase::SaveSignZoneToCoords(int32 spawn_id, float x, float y, float z, float heading)
  252. {
  253. Query query;
  254. LogWrite(COMMAND__DEBUG, 0, "Command", "Setting Zone-To coords for Spawn ID %u (X: %.2f, Y: %.2f, Z: %.2f, H: %.2f)", spawn_id, x, y, z, heading);
  255. query.RunQuery2(Q_UPDATE, "UPDATE spawn_signs SET zone_x = %f, zone_y = %f, zone_z = %f, zone_heading = %f WHERE spawn_id = %u", x, y, z, heading, spawn_id);
  256. if(query.GetErrorNumber() && query.GetError() && query.GetErrorNumber() < 0xFFFFFFFF){
  257. LogWrite(DATABASE__ERROR, 0, "DBCore", "Error in SaveSignZoneToCoords query '%s': %s", query.GetQuery(), query.GetError());
  258. return false;
  259. }
  260. if (query.GetAffectedRows() > 0 )
  261. {
  262. LogWrite(COMMAND__DEBUG, 0, "Command", "Success! Set new Zone-To coordinates in zone ID %u...", spawn_id);
  263. return true;
  264. }
  265. LogWrite(COMMAND__ERROR, 0, "Command", "FAILED! Set new Zone-To coordinates in zone ID %u...", spawn_id);
  266. return false;
  267. }