database.cpp

main 296 lines · 7.4 KB Raw
Alex Alex Commit Server upload 18/10/2026 23:38
1/**
2 * The Forgotten Server - a free and open-source MMORPG server emulator
3 * Copyright (C) 2019 Mark Samman <mark.samman@gmail.com>
4 *
5 * This program 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 2 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License along
16 * with this program; if not, write to the Free Software Foundation, Inc.,
17 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
18 */
19
20#include "otpch.h"
21
22#include "configmanager.h"
23#include "database.h"
24
25#include <mysql/errmsg.h>
26
27extern ConfigManager g_config;
28
29Database::~Database()
30{
31 if (handle != nullptr) {
32 mysql_close(handle);
33 }
34}
35
36bool Database::connect()
37{
38 // connection handle initialization
39 handle = mysql_init(nullptr);
40 if (!handle) {
41 std::cout << std::endl << "Failed to initialize MySQL connection handle." << std::endl;
42 return false;
43 }
44
45 // automatic reconnect
46 bool reconnect = true;
47 mysql_options(handle, MYSQL_OPT_RECONNECT, &reconnect);
48
49 // connects to database
50 if (!mysql_real_connect(handle, g_config.getString(ConfigManager::MYSQL_HOST).c_str(), g_config.getString(ConfigManager::MYSQL_USER).c_str(), g_config.getString(ConfigManager::MYSQL_PASS).c_str(), g_config.getString(ConfigManager::MYSQL_DB).c_str(), g_config.getNumber(ConfigManager::SQL_PORT), g_config.getString(ConfigManager::MYSQL_SOCK).c_str(), 0)) {
51 std::cout << std::endl << "MySQL Error Message: " << mysql_error(handle) << std::endl;
52 return false;
53 }
54
55 DBResult_ptr result = storeQuery("SHOW VARIABLES LIKE 'max_allowed_packet'");
56 if (result) {
57 maxPacketSize = result->getNumber<uint64_t>("Value");
58 }
59 return true;
60}
61
62bool Database::beginTransaction()
63{
64 if (!executeQuery("BEGIN")) {
65 return false;
66 }
67
68 databaseLock.lock();
69 return true;
70}
71
72bool Database::rollback()
73{
74 if (mysql_rollback(handle) != 0) {
75 std::cout << "[Error - mysql_rollback] Message: " << mysql_error(handle) << std::endl;
76 databaseLock.unlock();
77 return false;
78 }
79
80 databaseLock.unlock();
81 return true;
82}
83
84bool Database::commit()
85{
86 if (mysql_commit(handle) != 0) {
87 std::cout << "[Error - mysql_commit] Message: " << mysql_error(handle) << std::endl;
88 databaseLock.unlock();
89 return false;
90 }
91
92 databaseLock.unlock();
93 return true;
94}
95
96bool Database::executeQuery(const std::string& query)
97{
98 bool success = true;
99
100 // executes the query
101 databaseLock.lock();
102
103 while (mysql_real_query(handle, query.c_str(), query.length()) != 0) {
104 std::cout << "[Error - mysql_real_query] Query: " << query.substr(0, 256) << std::endl << "Message: " << mysql_error(handle) << std::endl;
105 auto error = mysql_errno(handle);
106 if (error != CR_SERVER_LOST && error != CR_SERVER_GONE_ERROR && error != CR_CONN_HOST_ERROR && error != 1053/*ER_SERVER_SHUTDOWN*/ && error != CR_CONNECTION_ERROR) {
107 success = false;
108 break;
109 }
110 std::this_thread::sleep_for(std::chrono::seconds(1));
111 }
112
113 MYSQL_RES* m_res = mysql_store_result(handle);
114 databaseLock.unlock();
115
116 if (m_res) {
117 mysql_free_result(m_res);
118 }
119
120 return success;
121}
122
123DBResult_ptr Database::storeQuery(const std::string& query)
124{
125 databaseLock.lock();
126
127 retry:
128 while (mysql_real_query(handle, query.c_str(), query.length()) != 0) {
129 std::cout << "[Error - mysql_real_query] Query: " << query << std::endl << "Message: " << mysql_error(handle) << std::endl;
130 auto error = mysql_errno(handle);
131 if (error != CR_SERVER_LOST && error != CR_SERVER_GONE_ERROR && error != CR_CONN_HOST_ERROR && error != 1053/*ER_SERVER_SHUTDOWN*/ && error != CR_CONNECTION_ERROR) {
132 break;
133 }
134 std::this_thread::sleep_for(std::chrono::seconds(1));
135 }
136
137 // we should call that every time as someone would call executeQuery('SELECT...')
138 // as it is described in MySQL manual: "it doesn't hurt" :P
139 MYSQL_RES* res = mysql_store_result(handle);
140 if (res == nullptr) {
141 std::cout << "[Error - mysql_store_result] Query: " << query << std::endl << "Message: " << mysql_error(handle) << std::endl;
142 auto error = mysql_errno(handle);
143 if (error != CR_SERVER_LOST && error != CR_SERVER_GONE_ERROR && error != CR_CONN_HOST_ERROR && error != 1053/*ER_SERVER_SHUTDOWN*/ && error != CR_CONNECTION_ERROR) {
144 databaseLock.unlock();
145 return nullptr;
146 }
147 goto retry;
148 }
149 databaseLock.unlock();
150
151 // retrieving results of query
152 DBResult_ptr result = std::make_shared<DBResult>(res);
153 if (!result->hasNext()) {
154 return nullptr;
155 }
156 return result;
157}
158
159std::string Database::escapeString(const std::string& s) const
160{
161 return escapeBlob(s.c_str(), s.length());
162}
163
164std::string Database::escapeBlob(const char* s, uint32_t length) const
165{
166 // the worst case is 2n + 1
167 size_t maxLength = (length * 2) + 1;
168
169 std::string escaped;
170 escaped.reserve(maxLength + 2);
171 escaped.push_back('\'');
172
173 if (length != 0) {
174 char* output = new char[maxLength];
175 mysql_real_escape_string(handle, output, s, length);
176 escaped.append(output);
177 delete[] output;
178 }
179
180 escaped.push_back('\'');
181 return escaped;
182}
183
184DBResult::DBResult(MYSQL_RES* res)
185{
186 handle = res;
187
188 size_t i = 0;
189
190 MYSQL_FIELD* field = mysql_fetch_field(handle);
191 while (field) {
192 listNames[field->name] = i++;
193 field = mysql_fetch_field(handle);
194 }
195
196 row = mysql_fetch_row(handle);
197}
198
199DBResult::~DBResult()
200{
201 mysql_free_result(handle);
202}
203
204std::string DBResult::getString(const std::string& s) const
205{
206 auto it = listNames.find(s);
207 if (it == listNames.end()) {
208 std::cout << "[Error - DBResult::getString] Column '" << s << "' does not exist in result set." << std::endl;
209 return std::string();
210 }
211
212 if (row[it->second] == nullptr) {
213 return std::string();
214 }
215
216 return std::string(row[it->second]);
217}
218
219const char* DBResult::getStream(const std::string& s, unsigned long& size) const
220{
221 auto it = listNames.find(s);
222 if (it == listNames.end()) {
223 std::cout << "[Error - DBResult::getStream] Column '" << s << "' doesn't exist in the result set" << std::endl;
224 size = 0;
225 return nullptr;
226 }
227
228 if (row[it->second] == nullptr) {
229 size = 0;
230 return nullptr;
231 }
232
233 size = mysql_fetch_lengths(handle)[it->second];
234 return row[it->second];
235}
236
237bool DBResult::hasNext() const
238{
239 return row != nullptr;
240}
241
242bool DBResult::next()
243{
244 row = mysql_fetch_row(handle);
245 return row != nullptr;
246}
247
248DBInsert::DBInsert(std::string query) : query(std::move(query))
249{
250 this->length = this->query.length();
251}
252
253bool DBInsert::addRow(const std::string& row)
254{
255 // adds new row to buffer
256 const size_t rowLength = row.length();
257 length += rowLength;
258 if (length > Database::getInstance().getMaxPacketSize() && !execute()) {
259 return false;
260 }
261
262 if (values.empty()) {
263 values.reserve(rowLength + 2);
264 values.push_back('(');
265 values.append(row);
266 values.push_back(')');
267 } else {
268 values.reserve(values.length() + rowLength + 3);
269 values.push_back(',');
270 values.push_back('(');
271 values.append(row);
272 values.push_back(')');
273 }
274 return true;
275}
276
277bool DBInsert::addRow(std::ostringstream& row)
278{
279 bool ret = addRow(row.str());
280 row.str(std::string());
281 return ret;
282}
283
284bool DBInsert::execute()
285{
286 if (values.empty()) {
287 return true;
288 }
289
290 // executes buffer
291 bool res = Database::getInstance().executeQuery(query + values);
292 values.clear();
293 length = query.length();
294 return res;
295}
296
Top