ryzom/server/src/server_share/mysql_wrapper.cpp

   1 // Ryzom - MMORPG Framework <http://dev.ryzom.com/projects/ryzom/>
   2 // Copyright (C) 2010  Winch Gate Property Limited
   3 //
   4 // This program is free software: you can redistribute it and/or modify
   5 // it under the terms of the GNU Affero General Public License as
   6 // published by the Free Software Foundation, either version 3 of the
   7 // License, or (at your option) any later version.
   8 //
   9 // This program 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 Affero General Public License for more details.
  13 //
  14 // You should have received a copy of the GNU Affero General Public License
  15 // along with this program.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 #include "stdpch.h"
  18 #include <memory>
  19 #include "mysql_wrapper.h"
  20 #include <errmsg.h>
  21
  22 using namespace std;
  23 using namespace NLMISC;
  24 using namespace NLNET;
  25
  26 CVariable<bool>         MSWStrictMode("msw", "MSWStrictMode", "Set the strict mode on SQL request", true, 0, true);
  27 CVariable<uint32>       MSWRequestDuration("msw", "MSWRequestDuration", "Measure the duration of SQL request", 0, 1000);
  28 CVariable<bool>         MSWAutoReconnect("msw", "MSWAutoReconnect", "MYSQL_OPT_RECONNECT", true, 0, true);
  29
  30
  31 namespace MSW
  32 {
  33         std::string encodeDate(NLMISC::TTime date)
  34         {
  35                 time_t baseTime = time_t(date);
  36                 // convert UTC time_t into local time, including daillight adjustment
  37                 struct tm *converted = localtime(&baseTime);
  38
  39                 if (converted == NULL)
  40                 {
  41                         nldebug("Failed to convert %dl into a time structure", date);
  42                         return "0000-00-00 00:00:00";
  43                 }
  44
  45                 std::string str;
  46                 str = NLMISC::toString("%4u-%02u-%02u %u:%02u:%02u",
  47                         converted->tm_year+1900,
  48                         converted->tm_mon+1,
  49                         converted->tm_mday,
  50                         converted->tm_hour,
  51                         converted->tm_min,
  52                         converted->tm_sec);
  53
  54                 return str;
  55         }
  56
  57         const std::string &escapeString(const std::string &str, CConnection &dbCnx)
  58         {
  59                 static string buffer;
  60                 // reserve space in the buffer according to the mysql documentation
  61                 buffer.resize(str.size()*2+1);
  62                 unsigned long resultSize = mysql_escape_string((char*)buffer.data(), str.data(), (unsigned long)str.size());
  63
  64                 // now, resize to the real size
  65                 buffer.resize(resultSize);
  66
  67                 // job's done
  68                 return buffer;
  69         }
  70
  71         void CConnection::addOption(mysql_option option, const char *value)
  72         {
  73                 _Options[option] = value;
  74         }
  75
  76         void CConnection::clearOption(mysql_option option)
  77         {
  78                 _Options.erase(option);
  79         }
  80
  81
  82         bool CConnection::connect(const TParsedCommandLine &dbInfo)
  83         {
  84                 const TParsedCommandLine *dbHost= dbInfo.getParam("host");
  85                 const TParsedCommandLine *dbPort= dbInfo.getParam("port");
  86                 const TParsedCommandLine *dbUser= dbInfo.getParam("user");
  87                 const TParsedCommandLine *dbPassword= dbInfo.getParam("password");
  88                 const TParsedCommandLine *dbBase= dbInfo.getParam("base");
  89                 if (dbHost == NULL
  90                         || dbUser == NULL
  91                         || dbPassword == NULL
  92                         || dbBase == NULL)
  93                 {
  94                         nlwarning("LS : Invalid database configuration");
  95                         if (MSWStrictMode)
  96                         {
  97                                 nlstopex(("SQL Strict mode, the above error is fatal"));
  98                         }
  99                         return false;
 100                 }
 101                 uint port;
 102                 if (!dbPort || !NLMISC::fromString(dbPort->ParamValue, port)) // parse port
 103                 {
 104                         port = 0; // or use default
 105                 }
 106
 107                 // connect to the database
 108                 return connect(dbHost->ParamValue, dbUser->ParamValue, dbPassword->ParamValue, dbBase->ParamValue, port);
 109         }
 110
 111         bool CConnection::connect(const std::string &hostName, const std::string &userName, const std::string &password, const std::string &defaultDatabase, uint port)
 112         {
 113                 // store the connection info
 114                 _ConnHostName = hostName;
 115                 _ConnUserName = userName;
 116                 _ConnPassword = password;
 117                 _ConnDefaultDatabase = defaultDatabase;
 118                 _ConnPort = port;
 119
 120                 nlassert(!_Connected);
 121
 122                 if (MSWAutoReconnect)
 123                 {
 124                         addOption(MYSQL_OPT_RECONNECT, "1");
 125                 }
 126
 127                 return _connect();
 128         }
 129
 130         bool CConnection::_connect()
 131         {
 132                 _Connected = false;
 133
 134                 if (_MysqlContext != NULL)
 135                 {
 136                         mysql_close(_MysqlContext);
 137                 }
 138
 139                 // init the context
 140                 _MysqlContext = mysql_init(NULL);
 141
 142                 // set the options
 143                 TOptions::iterator first(_Options.begin()), last(_Options.end());
 144                 for (; first != last; ++first)
 145                 {
 146                         const mysql_option &option = first->first;
 147                         const char *value = first->second;
 148                         mysql_options(_MysqlContext, option, value);
 149                 }
 150
 151                 MYSQL *res = mysql_real_connect(_MysqlContext,
 152                                                                                 _ConnHostName.c_str(),
 153                                                                                 _ConnUserName.c_str(),
 154                                                                                 _ConnPassword.c_str(),
 155                                                                                 _ConnDefaultDatabase.c_str(),
 156                                                                                 _ConnPort,
 157                                                                                 NULL,
 158                                                                                 CLIENT_FOUND_ROWS);
 159
 160                 if (res == NULL)
 161                 {
 162                         nlwarning("Error during connection to database '%s:%s@%s:%d' :", _ConnUserName.c_str(), _ConnDefaultDatabase.c_str(), _ConnHostName.c_str(), _ConnPort);
 163                         nlwarning("Mysql_real_connect error :%s ", mysql_error(_MysqlContext));
 164                         // the connection failed !
 165                         mysql_close(_MysqlContext);
 166                         _MysqlContext = NULL;
 167                         if (MSWStrictMode)
 168                         {
 169                                 nlstopex(("SQL Strict mode, the above error is fatal"));
 170                         }
 171                         return false;
 172                 }
 173
 174                 _Connected = true;
 175                 return true;
 176         }
 177
 178
 179         void CConnection::closeConn()
 180         {
 181                 nlassert(_Connected);
 182
 183                 // the connection failed !
 184                 mysql_close(_MysqlContext);
 185
 186                 _Connected = 0;
 187                 _MysqlContext = NULL;
 188         }
 189
 190         bool CConnection::query(const std::string &queryString)
 191         {
 192                 H_AUTO(CConnection_query);
 193                 nlassert(_Connected);
 194
 195                 nldebug("Executing query : [%s%s", queryString.substr(0, 100).c_str(), 100<queryString.size() ? "]" : "");
 196                 for (uint i=100; i<queryString.size(); i+=100)
 197                         nldebug("\t%s%s", queryString.substr(i, 100).c_str(), i+100>queryString.size() ? "]" : "");
 198
 199                 TTime startDate = CTime::getLocalTime();
 200
 201                 int result = mysql_real_query(_MysqlContext, queryString.c_str(), (unsigned long)queryString.size());
 202                 if (result != 0)
 203                 {
 204                         // in all case, we try to reconnect
 205                         int merrno = mysql_errno(_MysqlContext);
 206                         //if (result == CR_SERVER_GONE_ERROR)
 207                         {
 208                                 // reconnect and retry the request
 209                                 nlinfo("%p Mysql error errno:%d result:%d : %s, try to reconnect...", _MysqlContext, merrno, result, mysql_error(_MysqlContext));
 210                                 if (_connect())
 211                                         result = mysql_real_query(_MysqlContext, queryString.c_str(), (unsigned long)queryString.size());
 212                                 else
 213                                 {
 214                                         nlwarning("Failed to reopen closed connection to send query '%s'", queryString.c_str());
 215                                         if (MSWStrictMode)
 216                                         {
 217                                                 nlstopex(("SQL Strict mode, the above error is fatal"));
 218                                         }
 219                                         TTime endDate = CTime::getLocalTime();
 220                                         MSWRequestDuration = uint32(endDate - startDate);
 221                                         return false;
 222                                 }
 223                         }
 224
 225                         if (result != 0)
 226                         {
 227                                 nlwarning("Mysql error errno:%d result:%d : %s", merrno, result, mysql_error(_MysqlContext));
 228                                 nlwarning("   in query '%s':", queryString.c_str());
 229                                 if (MSWStrictMode)
 230                                 {
 231                                         nlstopex(("SQL Strict mode, the above error is fatal"));
 232                                 }
 233                                 TTime endDate = CTime::getLocalTime();
 234                                 MSWRequestDuration = uint32(endDate - startDate);
 235                                 return false;
 236                         }
 237                 }
 238
 239                 TTime endDate = CTime::getLocalTime();
 240                 MSWRequestDuration = uint32(endDate - startDate);
 241
 242                 return true;
 243         }
 244
 245
 246         CUniquePtr<CStoreResult>                CConnection::storeResult()
 247         {
 248                 H_AUTO(CConnection_storeResult);
 249                 MYSQL_RES *res = mysql_store_result(_MysqlContext);
 250
 251                 CUniquePtr<CStoreResult> sr(new CStoreResult(res));
 252
 253                 return sr;
 254         }
 255
 256         CUniquePtr<CUseResult>          CConnection::useResult()
 257         {
 258                 H_AUTO(CConnection_useResult);
 259                 MYSQL_RES *res = mysql_use_result(_MysqlContext);
 260
 261                 CUniquePtr<CUseResult> sr(new CUseResult(res));
 262
 263                 return sr;
 264         }
 265
 266
 267
 268         void CResultBase::getDateField(uint32 fieldIndex, uint32 &time)
 269         {
 270                 const char *str = getRawField(fieldIndex);
 271
 272                 // read from a DATE_TIME field
 273                 MYSQL_FIELD *field = mysql_fetch_field_direct(_Result, fieldIndex);
 274                 nlassert(field != NULL);
 275                 switch (field->type)
 276                 {
 277                 case FIELD_TYPE_DATETIME:
 278                         {
 279                                 // format is 'YYYY-MM-DD HH:MM:SS'
 280                                 uint    y, m, d, h, mn, s;
 281                                 uint nbScanned = sscanf(str, "%u-%u-%u %u:%u:%u", &y, &m, &d, &h, &mn, &s);
 282                                 nlassert(nbScanned == 6);
 283                                 tm      myTm;
 284                                 myTm.tm_year = y-1900;
 285                                 myTm.tm_mon = m-1;
 286                                 myTm.tm_mday = d;
 287                                 myTm.tm_hour = h;
 288                                 myTm.tm_min = mn;
 289                                 myTm.tm_sec = s;
 290
 291                                 myTm.tm_isdst = -1; // let the C runtime determine daylight adjustment
 292                                 myTm.tm_wday = -1;
 293                                 myTm.tm_yday = -1;
 294                                 // Convert the local date into a UTC unix time
 295                                 uint32 t = (uint32)nl_mktime(&myTm);
 296
 297                                 time = t;
 298                         }
 299                         break;
 300                 case FIELD_TYPE_DATE:
 301                         {
 302                                 // format is 'YYYY-MM-DD'
 303                                 uint    y, m, d;
 304                                 uint nbScanned = sscanf(str, "%u-%u-%u", &y, &m, &d);
 305                                 nlassert(nbScanned == 3);
 306                                 tm      myTm;
 307                                 myTm.tm_year = y-1900;
 308                                 myTm.tm_mon = m-1;
 309                                 myTm.tm_mday = d;
 310                                 myTm.tm_hour = 0;
 311                                 myTm.tm_min = 0;
 312                                 myTm.tm_sec = 0;
 313
 314                                 myTm.tm_isdst = -1; // let the C runtime determine daylight adjustment
 315                                 myTm.tm_wday = -1;
 316                                 myTm.tm_yday = -1;
 317                                 // Convert the local date into a UTC unix time
 318                                 uint32 t = (uint32)nl_mktime(&myTm);
 319
 320                                 time = t;
 321                         }
 322                         break;
 323                 case FIELD_TYPE_TIME:
 324                         {
 325                                 // format is 'HH:MM:SS'
 326                                 uint    h, m, s;
 327                                 uint nbScanned = sscanf(str, "%u:%u:%u", &h, &m, &s);
 328                                 nlassert(nbScanned == 3);
 329                                 tm myTm;
 330                                 myTm.tm_year = 0;
 331                                 myTm.tm_mon = 0;
 332                                 myTm.tm_mday = 0;
 333                                 myTm.tm_hour = h;
 334                                 myTm.tm_min = m;
 335                                 myTm.tm_sec = s;
 336
 337                                 myTm.tm_isdst = -1; // let the C runtime determine daylight adjustment
 338                                 myTm.tm_wday = -1;
 339                                 myTm.tm_yday = -1;
 340                                 // Convert the local date into a UTC unix time
 341                                 uint32 t = (uint32)nl_mktime(&myTm);
 342
 343                                 time = t;
 344                         }
 345                         break;
 346                 case FIELD_TYPE_YEAR:
 347                         {
 348                                 // format is 'YYYY'
 349                                 uint    y;
 350                                 uint nbScanned = sscanf(str, "%u", &y);
 351                                 nlassert(nbScanned == 1);
 352                                 tm      myTm;
 353                                 myTm.tm_year = y-1900;
 354                                 myTm.tm_mon = 0;
 355                                 myTm.tm_mday = 0;
 356                                 myTm.tm_hour = 0;
 357                                 myTm.tm_min = 0;
 358                                 myTm.tm_sec = 0;
 359
 360                                 myTm.tm_isdst = -1; // let the C runtime determine daylight adjustment
 361                                 myTm.tm_wday = -1;
 362                                 myTm.tm_yday = -1;
 363                                 // Convert the local date into a UTC unix time
 364                                 uint32 t = (uint32)nl_mktime(&myTm);
 365
 366                                 time = t;
 367                         }
 368                         break;
 369                 default:
 370                         {
 371                                 // any other case, consider the field as a second unix time (seconds since 1970)
 372                                 // format is 'SSSSSSSSSS..'
 373                                 uint32 t;
 374                                 getField(fieldIndex, t);
 375                                 time = t;
 376                         }
 377                 }
 378         }
 379
 380 } // namespace MSW
 381
 382 namespace NOPE
 383 {
 384
 385         // allowed transition table
 386         bool AllowedTransition [os_nb_state][os_nb_state] =
 387         {
 388                                 //      transient       clean   dirty   removed released
 389 /*transient*/   {       true,           true,   true,   false,  false   },
 390 /*clean*/               {       false,          true,   true,   true,   true    },
 391 /*dirty*/               {       false,          true,   true,   true,   false   },
 392 /*removed*/             {       false,          false,  false,  false,  false   },
 393 /*released*/    {       false,          false,  false,  false,  false   },
 394         };
 395
 396         NLMISC_SAFE_SINGLETON_IMPL(CPersistentCache);
 397
 398         NLMISC_CATEGORISED_DYNVARIABLE("nope", bool, NOPEAllowReleaseDirtyObject, "Set to true to allow dirty object to be removed from cache (this mean some modified data are lost)")
 399         {
 400                 if (get)
 401                         *pointer = AllowedTransition[os_dirty][os_released];
 402                 else
 403                         AllowedTransition[os_dirty][os_released] = (*pointer);
 404         }
 405
 406 } // namespace NOPE